-- ---------------------------- -- 表名：config_info -- ---------------------------- CREATE TABLE config_info ( id SERIAL PRIMARY KEY, data_id varchar(255) NOT NULL, group_id varchar(128), content text NOT NULL, md5 varchar(32), gmt_create timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', gmt_modified timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', src_user text, src_ip varchar(20), app_name varchar(128), tenant_id varchar(128) DEFAULT '', c_desc varchar(256), c_use varchar(64), effect varchar(64), type varchar(64), c_schema text, encrypted_data_key text ); COMMENT ON TABLE config_info IS 'config_info'; COMMENT ON COLUMN config_info.id IS 'id'; COMMENT ON COLUMN config_info.data_id IS 'data_id'; COMMENT ON COLUMN config_info.group_id IS ''; COMMENT ON COLUMN config_info.content IS 'content'; COMMENT ON COLUMN config_info.md5 IS 'md5'; COMMENT ON COLUMN config_info.gmt_create IS '创建时间'; COMMENT ON COLUMN config_info.gmt_modified IS '修改时间'; COMMENT ON COLUMN config_info.src_user IS 'source user'; COMMENT ON COLUMN config_info.src_ip IS 'source ip'; COMMENT ON COLUMN config_info.app_name IS ''; COMMENT ON COLUMN config_info.tenant_id IS '租户字段'; COMMENT ON COLUMN config_info.c_desc IS ''; COMMENT ON COLUMN config_info.c_use IS ''; COMMENT ON COLUMN config_info.effect IS ''; COMMENT ON COLUMN config_info.type IS ''; COMMENT ON COLUMN config_info.c_schema IS ''; COMMENT ON COLUMN config_info.encrypted_data_key IS '秘钥'; -- ---------------------------- -- Records of config_info -- ---------------------------- -- ---------------------------- -- 表名：config_info_aggr -- ---------------------------- CREATE TABLE config_info_aggr ( id SERIAL PRIMARY KEY, data_id varchar(255) NOT NULL, group_id varchar(128) NOT NULL, datum_id varchar(255) NOT NULL, content text NOT NULL, gmt_modified timestamp NOT NULL, app_name varchar(128), tenant_id varchar(128) DEFAULT '' ); COMMENT ON TABLE config_info_aggr IS '增加租户字段'; COMMENT ON COLUMN config_info_aggr.id IS 'id'; COMMENT ON COLUMN config_info_aggr.data_id IS 'data_id'; COMMENT ON COLUMN config_info_aggr.group_id IS 'group_id'; COMMENT ON COLUMN config_info_aggr.datum_id IS 'datum_id'; COMMENT ON COLUMN config_info_aggr.content IS '内容'; COMMENT ON COLUMN config_info_aggr.gmt_modified IS '修改时间'; COMMENT ON COLUMN config_info_aggr.app_name IS ''; COMMENT ON COLUMN config_info_aggr.tenant_id IS '租户字段'; -- ---------------------------- -- 表名：config_info_beta -- ---------------------------- CREATE TABLE config_info_beta ( id SERIAL PRIMARY KEY, data_id varchar(255) NOT NULL, group_id varchar(128) NOT NULL, app_name varchar(128), content text NOT NULL, beta_ips varchar(1024), md5 varchar(32), gmt_create timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', gmt_modified timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', src_user text, src_ip varchar(20), tenant_id varchar(128) DEFAULT '', encrypted_data_key text NOT NULL ); COMMENT ON TABLE config_info_beta IS 'config_info_beta'; COMMENT ON COLUMN config_info_beta.id IS 'id'; COMMENT ON COLUMN config_info_beta.data_id IS 'data_id'; COMMENT ON COLUMN config_info_beta.group_id IS 'group_id'; COMMENT ON COLUMN config_info_beta.app_name IS 'app_name'; COMMENT ON COLUMN config_info_beta.content IS 'content'; COMMENT ON COLUMN config_info_beta.beta_ips IS 'betaIps'; COMMENT ON COLUMN config_info_beta.md5 IS 'md5'; COMMENT ON COLUMN config_info_beta.gmt_create IS '创建时间'; COMMENT ON COLUMN config_info_beta.gmt_modified IS '修改时间'; COMMENT ON COLUMN config_info_beta.src_user IS 'source user'; COMMENT ON COLUMN config_info_beta.src_ip IS 'source ip'; COMMENT ON COLUMN config_info_beta.tenant_id IS '租户字段'; COMMENT ON COLUMN config_info_beta.encrypted_data_key IS '秘钥'; -- ---------------------------- -- 表名：config_info_tag -- ---------------------------- CREATE TABLE config_info_tag ( id SERIAL PRIMARY KEY, data_id varchar(255) NOT NULL, group_id varchar(128) NOT NULL, tenant_id varchar(128) DEFAULT '', tag_id varchar(128) NOT NULL, app_name varchar(128), content text NOT NULL, md5 varchar(32), gmt_create timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', gmt_modified timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', src_user text, src_ip varchar(20) ); COMMENT ON TABLE config_info_tag IS 'config_info_tag'; COMMENT ON COLUMN config_info_tag.id IS 'id'; COMMENT ON COLUMN config_info_tag.data_id IS 'data_id'; COMMENT ON COLUMN config_info_tag.group_id IS 'group_id'; COMMENT ON COLUMN config_info_tag.tenant_id IS 'tenant_id'; COMMENT ON COLUMN config_info_tag.tag_id IS 'tag_id'; COMMENT ON COLUMN config_info_tag.app_name IS 'app_name'; COMMENT ON COLUMN config_info_tag.content IS 'content'; COMMENT ON COLUMN config_info_tag.md5 IS 'md5'; COMMENT ON COLUMN config_info_tag.gmt_create IS '创建时间'; COMMENT ON COLUMN config_info_tag.gmt_modified IS '修改时间'; COMMENT ON COLUMN config_info_tag.src_user IS 'source user'; COMMENT ON COLUMN config_info_tag.src_ip IS 'source ip'; -- ---------------------------- -- 表名：config_tags_relation -- ---------------------------- CREATE TABLE config_tags_relation ( id bigint NOT NULL, tag_name varchar(128) NOT NULL, tag_type varchar(64), data_id varchar(255) NOT NULL, group_id varchar(128) NOT NULL, tenant_id varchar(128) DEFAULT '', nid SERIAL PRIMARY KEY ); COMMENT ON TABLE config_tags_relation IS 'config_tag_relation'; COMMENT ON COLUMN config_tags_relation.id IS 'id'; COMMENT ON COLUMN config_tags_relation.tag_name IS 'tag_name'; COMMENT ON COLUMN config_tags_relation.tag_type IS 'tag_type'; COMMENT ON COLUMN config_tags_relation.data_id IS 'data_id'; COMMENT ON COLUMN config_tags_relation.group_id IS 'group_id'; COMMENT ON COLUMN config_tags_relation.tenant_id IS 'tenant_id'; COMMENT ON COLUMN config_tags_relation.nid IS ''; -- ---------------------------- -- 表名：group_capacity -- ---------------------------- CREATE TABLE group_capacity ( id SERIAL PRIMARY KEY, group_id varchar(128) NOT NULL DEFAULT '', quota integer NOT NULL DEFAULT '0', usage integer NOT NULL DEFAULT '0', max_size integer NOT NULL DEFAULT '0', max_aggr_count integer NOT NULL DEFAULT '0', max_aggr_size integer NOT NULL DEFAULT '0', max_history_count integer NOT NULL DEFAULT '0', gmt_create timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', gmt_modified timestamp NOT NULL DEFAULT '2010-05-05 00:00:00' ); COMMENT ON TABLE group_capacity IS '集群、各Group容量信息表'; COMMENT ON COLUMN group_capacity.id IS '主键ID'; COMMENT ON COLUMN group_capacity.group_id IS 'Group ID，空字符表示整个集群'; COMMENT ON COLUMN group_capacity.quota IS '配额，0表示使用默认值'; COMMENT ON COLUMN group_capacity.usage IS '使用量'; COMMENT ON COLUMN group_capacity.max_size IS '单个配置大小上限，单位为字节，0表示使用默认值'; COMMENT ON COLUMN group_capacity.max_aggr_count IS '聚合子配置最大个数，，0表示使用默认值'; COMMENT ON COLUMN group_capacity.max_aggr_size IS '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值'; COMMENT ON COLUMN group_capacity.max_history_count IS '最大变更历史数量'; COMMENT ON COLUMN group_capacity.gmt_create IS '创建时间'; COMMENT ON COLUMN group_capacity.gmt_modified IS '修改时间'; -- ---------------------------- -- 表名：his_config_info -- ---------------------------- CREATE TABLE his_config_info ( id bigint NOT NULL, nid SERIAL PRIMARY KEY, data_id varchar(255) NOT NULL, group_id varchar(128) NOT NULL, app_name varchar(128), content text NOT NULL, md5 varchar(32), gmt_create timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', gmt_modified timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', src_user text, src_ip varchar(20), op_type character(10), tenant_id varchar(128) DEFAULT '', encrypted_data_key text ); COMMENT ON TABLE his_config_info IS '多租户改造'; COMMENT ON COLUMN his_config_info.id IS ''; COMMENT ON COLUMN his_config_info.nid IS ''; COMMENT ON COLUMN his_config_info.data_id IS ''; COMMENT ON COLUMN his_config_info.group_id IS ''; COMMENT ON COLUMN his_config_info.app_name IS 'app_name'; COMMENT ON COLUMN his_config_info.content IS ''; COMMENT ON COLUMN his_config_info.md5 IS ''; COMMENT ON COLUMN his_config_info.gmt_create IS ''; COMMENT ON COLUMN his_config_info.gmt_modified IS ''; COMMENT ON COLUMN his_config_info.src_user IS ''; COMMENT ON COLUMN his_config_info.src_ip IS ''; COMMENT ON COLUMN his_config_info.op_type IS ''; COMMENT ON COLUMN his_config_info.tenant_id IS '租户字段'; COMMENT ON COLUMN his_config_info.encrypted_data_key IS '秘钥'; -- ---------------------------- -- 表名：permissions -- ---------------------------- CREATE TABLE permissions ( role varchar(50) NOT NULL, resource varchar(512) NOT NULL, action varchar(8) NOT NULL ); COMMENT ON TABLE permissions IS ''; COMMENT ON COLUMN permissions.role IS ''; COMMENT ON COLUMN permissions.resource IS ''; COMMENT ON COLUMN permissions.action IS ''; -- ---------------------------- -- 表名：roles -- ---------------------------- CREATE TABLE roles ( username varchar(50) NOT NULL, role varchar(50) NOT NULL ); COMMENT ON TABLE roles IS ''; COMMENT ON COLUMN roles.username IS ''; COMMENT ON COLUMN roles.role IS ''; DELETE FROM roles; INSERT INTO roles (role,username) VALUES ('ROLE_ADMIN','nacos'); -- ---------------------------- -- 表名：tenant_capacity -- ---------------------------- CREATE TABLE tenant_capacity ( id SERIAL PRIMARY KEY, tenant_id varchar(128) NOT NULL DEFAULT '', quota integer NOT NULL DEFAULT '0', usage integer NOT NULL DEFAULT '0', max_size integer NOT NULL DEFAULT '0', max_aggr_count integer NOT NULL DEFAULT '0', max_aggr_size integer NOT NULL DEFAULT '0', max_history_count integer NOT NULL DEFAULT '0', gmt_create timestamp NOT NULL DEFAULT '2010-05-05 00:00:00', gmt_modified timestamp NOT NULL DEFAULT '2010-05-05 00:00:00' ); COMMENT ON TABLE tenant_capacity IS '租户容量信息表'; COMMENT ON COLUMN tenant_capacity.id IS '主键ID'; COMMENT ON COLUMN tenant_capacity.tenant_id IS 'Tenant ID'; COMMENT ON COLUMN tenant_capacity.quota IS '配额，0表示使用默认值'; COMMENT ON COLUMN tenant_capacity.usage IS '使用量'; COMMENT ON COLUMN tenant_capacity.max_size IS '单个配置大小上限，单位为字节，0表示使用默认值'; COMMENT ON COLUMN tenant_capacity.max_aggr_count IS '聚合子配置最大个数'; COMMENT ON COLUMN tenant_capacity.max_aggr_size IS '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值'; COMMENT ON COLUMN tenant_capacity.max_history_count IS '最大变更历史数量'; COMMENT ON COLUMN tenant_capacity.gmt_create IS '创建时间'; COMMENT ON COLUMN tenant_capacity.gmt_modified IS '修改时间'; -- ---------------------------- -- 表名：tenant_info -- ---------------------------- CREATE TABLE tenant_info ( id SERIAL PRIMARY KEY, kp varchar(128) NOT NULL, tenant_id varchar(128) DEFAULT '', tenant_name varchar(128) DEFAULT '', tenant_desc varchar(256), create_source varchar(32), gmt_create bigint NOT NULL, gmt_modified bigint NOT NULL ); COMMENT ON TABLE tenant_info IS 'tenant_info'; COMMENT ON COLUMN tenant_info.id IS 'id'; COMMENT ON COLUMN tenant_info.kp IS 'kp'; COMMENT ON COLUMN tenant_info.tenant_id IS 'tenant_id'; COMMENT ON COLUMN tenant_info.tenant_name IS 'tenant_name'; COMMENT ON COLUMN tenant_info.tenant_desc IS 'tenant_desc'; COMMENT ON COLUMN tenant_info.create_source IS 'create_source'; COMMENT ON COLUMN tenant_info.gmt_create IS '创建时间'; COMMENT ON COLUMN tenant_info.gmt_modified IS '修改时间'; -- ---------------------------- -- Records of tenant_info -- ---------------------------- INSERT INTO "public"."tenant_info" VALUES (1, '1', '69c4eecb-05bd-4041-81fe-1473f95f578c', 'develop', '开发环境', 'nacos', 1683359420265, 1683359420265); INSERT INTO "public"."tenant_info" VALUES (7, '1', '1e017954-eb52-4d21-a843-0286d9013cf3', 'staging', '测试环境', 'nacos', 1683359955156, 1683359955156); INSERT INTO "public"."tenant_info" VALUES (8, '1', 'f50f4948-3ca2-48af-8bb1-1a47950b503d', 'pre', '预发环境', 'nacos', 1683359976752, 1683359976752); INSERT INTO "public"."tenant_info" VALUES (9, '1', '3baec428-9669-486c-b359-a76f7a1f1ac7', 'production', '生产环境', 'nacos', 1683359987767, 1683359987767); -- ---------------------------- -- 表名：users -- ---------------------------- CREATE TABLE users ( username varchar(50) NOT NULL PRIMARY KEY, password varchar(500) NOT NULL, enabled varchar(15) NOT NULL ); COMMENT ON TABLE users IS ''; COMMENT ON COLUMN users.username IS ''; COMMENT ON COLUMN users.password IS ''; COMMENT ON COLUMN users.enabled IS ''; DELETE FROM users; INSERT INTO users (password,enabled,username) VALUES ('$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu','true','nacos'); -- 唯一索引 CREATE UNIQUE INDEX uk_configinfo_datagrouptenant ON config_info(data_id,group_id,tenant_id); CREATE UNIQUE INDEX uk_configinfoaggr_datagrouptenantdatum ON config_info_aggr(data_id,group_id,tenant_id,datum_id); CREATE UNIQUE INDEX uk_configinfoaggr_datagrouptenantdatum_nottenant ON config_info_aggr(data_id,group_id,datum_id); CREATE UNIQUE INDEX uk_configinfobeta_datagrouptenant ON config_info_beta(data_id,group_id,tenant_id); CREATE UNIQUE INDEX uk_configinfobeta_datagrouptenant_nottenant ON config_info_beta(data_id,group_id); CREATE UNIQUE INDEX uk_configinfotag_datagrouptenanttag ON config_info_tag(data_id,group_id,tenant_id,tag_id); CREATE UNIQUE INDEX uk_configinfotag_datagrouptenanttag_nottenant ON config_info_tag(data_id,group_id,tag_id); CREATE UNIQUE INDEX uk_configtagrelation_configidtag ON config_tags_relation(id,tag_name,tag_type); CREATE UNIQUE INDEX uk_group_id ON group_capacity(group_id); CREATE UNIQUE INDEX uk_role_permission ON permissions(role,resource,action); CREATE UNIQUE INDEX uk_username_role ON roles(username,role); CREATE UNIQUE INDEX uk_tenant_id ON tenant_capacity(tenant_id); CREATE UNIQUE INDEX uk_tenant_info_kptenantid ON tenant_info(kp,tenant_id); CREATE TABLE IF NOT EXISTS public.global_table ( xid VARCHAR(128) NOT NULL, transaction_id BIGINT, status SMALLINT NOT NULL, application_id VARCHAR(32), transaction_service_group VARCHAR(32), transaction_name VARCHAR(128), timeout INT, begin_time BIGINT, application_data VARCHAR(2000), gmt_create TIMESTAMP(0), gmt_modified TIMESTAMP(0), CONSTRAINT pk_global_table PRIMARY KEY (xid) ); CREATE INDEX idx_global_table_status_gmt_modified ON public.global_table (status, gmt_modified); CREATE INDEX idx_global_table_transaction_id ON public.global_table (transaction_id); -- the table to store BranchSession data CREATE TABLE IF NOT EXISTS public.branch_table ( branch_id BIGINT NOT NULL, xid VARCHAR(128) NOT NULL, transaction_id BIGINT, resource_group_id VARCHAR(32), resource_id VARCHAR(256), branch_type VARCHAR(8), status SMALLINT, client_id VARCHAR(64), application_data VARCHAR(2000), gmt_create TIMESTAMP(6), gmt_modified TIMESTAMP(6), CONSTRAINT pk_branch_table PRIMARY KEY (branch_id) ); CREATE INDEX idx_branch_table_xid ON public.branch_table (xid); -- the table to store lock data CREATE TABLE IF NOT EXISTS public.lock_table ( row_key VARCHAR(128) NOT NULL, xid VARCHAR(128), transaction_id BIGINT, branch_id BIGINT NOT NULL, resource_id VARCHAR(256), table_name VARCHAR(32), pk VARCHAR(128), status SMALLINT NOT NULL DEFAULT 0, gmt_create TIMESTAMP(0), gmt_modified TIMESTAMP(0), CONSTRAINT pk_lock_table PRIMARY KEY (row_key) ); comment on column public.lock_table.status is '0:locked ,1:rollbacking'; CREATE INDEX idx_lock_table_branch_id ON public.lock_table (branch_id); CREATE INDEX idx_lock_table_xid ON public.lock_table (xid); CREATE INDEX idx_lock_table_status ON public.lock_table (status); CREATE TABLE distributed_lock ( lock_key VARCHAR(20) NOT NULL, lock_value VARCHAR(20) NOT NULL, expire BIGINT NOT NULL, CONSTRAINT pk_distributed_lock_table PRIMARY KEY (lock_key) ); INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('AsyncCommitting', ' ', 0); INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('RetryCommitting', ' ', 0); INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('RetryRollbacking', ' ', 0); INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('TxTimeoutCheck', ' ', 0); CREATE TABLE IF NOT EXISTS vgroup_table ( vGroup VARCHAR(255), namespace VARCHAR(255), cluster VARCHAR(255), PRIMARY KEY (vGroup) );