用于将PostgreSQL表(和任何相关数据)分解为单独模式的实用工具。
pgexplode的Python项目详细描述
PG爆炸
一个实用程序,用于将PostgreSQL表(和任何相关数据)分解为单独的模式。在
示例
想象一下在一个名为exploder_test
的数据库中有以下表结构和数据:
CREATETABLEtenant(idserialPRIMARYKEY,slugvarchar);CREATETABLErelated(idserialPRIMARYKEY,tenant_idintegerNOTNULLREFERENCEStenant(id),valuevarchar);INSERTINTOtenant(id,slug)VALUES(1,'alpha'),(2,'beta');INSERTINTOrelated(tenant_id,value)VALUES(1,'alpha-value-1'),(1,'alpha-value-2'),(1,'alpha-value-3'),(2,'beta-value-1'),(2,'beta-value-2'),(2,'beta-value-3'),(2,'beta-value-4'),(2,'beta-value-5');
运行以下命令:
^{pr2}$将创建两个架构alpha
和beta
,并复制表数据,如下所示:
+ alpha
~ tenant: 1
~ related: 3
+ beta
~ tenant: 1
~ related: 5
输出/调试SQL
向上面的命令添加一个--sql
标志将输出正在运行的SQL,这在调整或时非常有用
调试:
-- alphaDROPSCHEMAIFEXISTS"alpha"CASCADE;CREATESCHEMA"alpha";CREATETABLE"alpha".tenant(LIKEpublic.tenantINCLUDINGALL);INSERTINTO"alpha".tenant(SELECT*FROMtenantWHEREid=1);CREATETABLE"alpha".related(LIKEpublic.relatedINCLUDINGALL);INSERTINTO"alpha".related(SELECTrelated.*FROMrelatedJOINtenantONrelated.tenant_id=tenant.idWHEREtenant.id=1);CREATESEQUENCE"alpha".related_id_seq;ALTERSEQUENCE"alpha".related_id_seqOWNEDBY"alpha".related.id;ALTERTABLE"alpha".relatedALTERidSETDEFAULTnextval('alpha.related_id_seq'::regclass);CREATESEQUENCE"alpha".tenant_id_seq;ALTERSEQUENCE"alpha".tenant_id_seqOWNEDBY"alpha".tenant.id;ALTERTABLE"alpha".tenantALTERidSETDEFAULTnextval('alpha.tenant_id_seq'::regclass);ALTERTABLE"alpha".relatedADDCONSTRAINTrelated_tenant_id_fkeyFOREIGNKEY(tenant_id)REFERENCES"alpha".tenant(id);-- betaDROPSCHEMAIFEXISTS"beta"CASCADE;CREATESCHEMA"beta";CREATETABLE"beta".tenant(LIKEpublic.tenantINCLUDINGALL);INSERTINTO"beta".tenant(SELECT*FROMtenantWHEREid=2);CREATETABLE"beta".related(LIKEpublic.relatedINCLUDINGALL);INSERTINTO"beta".related(SELECTrelated.*FROMrelatedJOINtenantONrelated.tenant_id=tenant.idWHEREtenant.id=2);CREATESEQUENCE"beta".related_id_seq;ALTERSEQUENCE"beta".related_id_seqOWNEDBY"beta".related.id;ALTERTABLE"beta".relatedALTERidSETDEFAULTnextval('beta.related_id_seq'::regclass);CREATESEQUENCE"beta".tenant_id_seq;ALTERSEQUENCE"beta".tenant_id_seqOWNEDBY"beta".tenant.id;ALTERTABLE"beta".tenantALTERidSETDEFAULTnextval('beta.tenant_id_seq'::regclass);ALTERTABLE"beta".relatedADDCONSTRAINTrelated_tenant_id_fkeyFOREIGNKEY(tenant_id)REFERENCES"beta".tenant(id);
您可以看到除了简单地创建每个表的副本之外,pgexplode
还确保新表具有
序列表和它们自己的列被正确地键控。在
- 项目
标签: