jnpf-database/Java微服务/jnpf_nacos_postgre.sql

-- ----------------------------
-- 表名：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 NOT NULL
);
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 '秘钥';
DELETE FROM config_info;

-- ----------------------------
-- 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" int8 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" int4 NOT NULL DEFAULT '0',
	"usage" int4 NOT NULL DEFAULT '0',
	"max_size" int4 NOT NULL DEFAULT '0',
	"max_aggr_count" int4 NOT NULL DEFAULT '0',
	"max_aggr_size" int4 NOT NULL DEFAULT '0',
	"max_history_count" int4 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" int8 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" char(10),
	"tenant_id" varchar(128) DEFAULT '',
	"encrypted_data_key" text NOT NULL
);
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 '秘钥';
DELETE FROM his_config_info;
-- ----------------------------
-- 表名：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" int4 NOT NULL DEFAULT '0',
	"usage" int4 NOT NULL DEFAULT '0',
	"max_size" int4 NOT NULL DEFAULT '0',
	"max_aggr_count" int4 NOT NULL DEFAULT '0',
	"max_aggr_size" int4 NOT NULL DEFAULT '0',
	"max_history_count" int4 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" int8 NOT NULL,
	"gmt_modified" int8 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', 1683354450804, 1683354572008);
INSERT INTO "public"."tenant_info" VALUES (2, '1', '1e017954-eb52-4d21-a843-0286d9013cf3', 'staging', '测试环境', 'nacos', 1683354580995, 1683354588437);
INSERT INTO "public"."tenant_info" VALUES (3, '1', 'f50f4948-3ca2-48af-8bb1-1a47950b503d', 'pre', '预发环境', 'nacos', 1683354598546, 1683354598546);
INSERT INTO "public"."tenant_info" VALUES (4, '1', '3baec428-9669-486c-b359-a76f7a1f1ac7', 'production', '生产环境', 'nacos', 1683354608785, 1683354608785);
-- ----------------------------
-- 表名：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_configinfobeta_datagrouptenant ON config_info_beta(data_id,group_id,tenant_id);
CREATE UNIQUE INDEX uk_configinfotag_datagrouptenanttag ON config_info_tag(data_id,group_id,tenant_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)
    );