jnpf-database/Java微服务/jnpf_nacos_kingbase.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
);
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)
    );