用于将PostgreSQL表(及其相关数据)分解为单独模式表的实用程序。
项目描述
pgexplode
一个用于将PostgreSQL表(及其相关数据)分解为单独模式表的实用程序。
示例
假设在一个名为 exploder_test
的数据库中存在以下表结构和数据
CREATE TABLE tenant (id serial PRIMARY KEY, slug varchar);
CREATE TABLE related (id serial PRIMARY KEY, tenant_id integer NOT NULL REFERENCES tenant(id), value varchar);
INSERT INTO tenant (id, slug) VALUES (1, 'alpha'), (2, 'beta');
INSERT INTO related (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')
;
运行以下命令
python -m pgexplode -d exploder_test -t tenant -s slug
将创建两个模式,alpha
和 beta
,并按以下方式复制表数据
+ alpha
~ tenant: 1
~ related: 3
+ beta
~ tenant: 1
~ related: 5
输出/调试SQL
将 --sql
标志添加到上述命令中将输出正在运行的SQL,这有助于调整或调试。
-- alpha
DROP SCHEMA IF EXISTS "alpha" CASCADE;
CREATE SCHEMA "alpha";
CREATE TABLE "alpha".tenant (LIKE public.tenant INCLUDING ALL);
INSERT INTO "alpha".tenant (SELECT * FROM tenant WHERE id = 1);
CREATE TABLE "alpha".related (LIKE public.related INCLUDING ALL);
INSERT INTO "alpha".related (SELECT related.* FROM related JOIN tenant ON related.tenant_id = tenant.id WHERE tenant.id = 1);
CREATE SEQUENCE "alpha".related_id_seq;
ALTER SEQUENCE "alpha".related_id_seq OWNED BY "alpha".related.id;
ALTER TABLE "alpha".related ALTER id SET DEFAULT nextval('alpha.related_id_seq'::regclass);
CREATE SEQUENCE "alpha".tenant_id_seq;
ALTER SEQUENCE "alpha".tenant_id_seq OWNED BY "alpha".tenant.id;
ALTER TABLE "alpha".tenant ALTER id SET DEFAULT nextval('alpha.tenant_id_seq'::regclass);
ALTER TABLE "alpha".related ADD CONSTRAINT related_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES "alpha".tenant (id);
-- beta
DROP SCHEMA IF EXISTS "beta" CASCADE;
CREATE SCHEMA "beta";
CREATE TABLE "beta".tenant (LIKE public.tenant INCLUDING ALL);
INSERT INTO "beta".tenant (SELECT * FROM tenant WHERE id = 2);
CREATE TABLE "beta".related (LIKE public.related INCLUDING ALL);
INSERT INTO "beta".related (SELECT related.* FROM related JOIN tenant ON related.tenant_id = tenant.id WHERE tenant.id = 2);
CREATE SEQUENCE "beta".related_id_seq;
ALTER SEQUENCE "beta".related_id_seq OWNED BY "beta".related.id;
ALTER TABLE "beta".related ALTER id SET DEFAULT nextval('beta.related_id_seq'::regclass);
CREATE SEQUENCE "beta".tenant_id_seq;
ALTER SEQUENCE "beta".tenant_id_seq OWNED BY "beta".tenant.id;
ALTER TABLE "beta".tenant ALTER id SET DEFAULT nextval('beta.tenant_id_seq'::regclass);
ALTER TABLE "beta".related ADD CONSTRAINT related_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES "beta".tenant (id);
您可以看到,除了简单地复制每个表之外,pgexplode
还确保新表有自己的序列以供序列列使用,并且表在新模式中正确重新键。
项目详情
下载文件
下载适用于您平台的应用程序。如果您不确定要选择哪个,请了解有关 安装包 的更多信息。
源分发
此版本没有提供源分发文件。请参阅 生成分发存档的教程。
构建分发
pgexplode-0.2.0-py3-none-any.whl (6.0 kB 查看哈希值)