我有一个数据库我想转换为使用UUID作为
postgresql中的主键.
我有大约30个表与深层多级关联.是否有一种“简单”的方法将所有当前ID转换为UUID？
从这个：https://coderwall.com/p/n_0awq,我可以看到我可以在迁移中改变表格.我在想这样的事情：
for client in Client.all
# Retrieve children
underwritings = client.underwritings
# Change primary key
execute 'ALTER TABLE clients ALTER COLUMN id TYPE uuid;'
execute 'ALTER TABLE clients ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
# Get new id - is this already generated?
client_id = client.id
for underwriting in underwritings
locations = underwriting.locations
other_record = underwriting.other_records...
execute 'ALTER TABLE underwritings ALTER COLUMN id TYPE uuid;'
execute 'ALTER TABLE underwritings ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
underwriting.client_id = client_id
underwriting.saved
underwriting_id = underwriting.id
for location in locations
buildings = location.buildings
execute 'ALTER TABLE locations ALTER COLUMN id TYPE uuid;'
execute 'ALTER TABLE locations ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
location.undewriting_id = underwriting_id
location.save
location_id = location.id
for building in buildings
...
end
end
for other_record in other_records
...
end
...
...
end
end
问题：
>这会有用吗？
>有更简单的方法吗？
>只要在更改主键之前检索子记录,是否可以正确检索子记录？
>一旦调用alter table,是否已经生成了新的主键？
非常感谢您提供帮助或提示.
解决方法
我觉得这些很乏味.可以使用直接查询Postgresql来转换表与现有数据.
对于主键：
ALTER TABLE students
ALTER COLUMN id DROP DEFAULT,ALTER COLUMN id SET DATA TYPE UUID USING (uuid(lpad(replace(text(id),'-',''),32,'0'))),ALTER COLUMN id SET DEFAULT uuid_generate_v4()
对于其他参考：
ALTER TABLE students
ALTER COLUMN city_id SET DATA TYPE UUID USING (uuid(lpad(replace(text(city_id),'0')))
上面的左边用零填充整数值并转换为UUID.这种方法不需要id映射,如果需要,可以检索旧id.
由于没有数据复制,这种方法非常快.
要处理这些更复杂的多态关联案例,请使用https://github.com/kreatio-sw/webdack-uuid_migration.这个gem为ActiveRecord :: Migration添加了额外的帮助,以简化这些迁移.