跳转到主要内容

用于将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

将创建两个模式,alphabeta,并按以下方式复制表数据

+ 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 查看哈希值)

Python 3