CREATE TABLE SHARDING_ALGORITHMS
(
   ID                   INT NOT NULL,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(50) NOT NULL COMMENT '名称',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   TEMPLATE_ID          INT NOT NULL COMMENT '模板',
   CONFIG_ID            INT NOT NULL COMMENT '规则',
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_ALGORITHMS COMMENT '算法表';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_ALGORITHMS
(
   CODE
);

CREATE TABLE SHARDING_CONFIG
(
   ID                   INT NOT NULL AUTO_INCREMENT,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(50) NOT NULL COMMENT '名称',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_CONFIG COMMENT '规则配置表';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_CONFIG
(
   CODE
);

CREATE TABLE SHARDING_DATA_SOURCE
(
   ID                   INT NOT NULL,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(50) NOT NULL COMMENT '名称',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   TEMPLATE_ID          INT NOT NULL COMMENT '模板',
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_DATA_SOURCE COMMENT '数据源配置表';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_DATA_SOURCE
(
   CODE
);

CREATE TABLE SHARDING_PROPERTIES
(
   ID                   INT NOT NULL,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(1000) NOT NULL COMMENT '值',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   CONFIG_ID            INT COMMENT '配置',
   TEMPLATE_ID          INT NOT NULL COMMENT '模板',
   ENTITY_ID            INT NOT NULL COMMENT '归属ID',
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_PROPERTIES COMMENT '配置表';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_PROPERTIES
(
   CODE,
   ENTITY_ID
);

CREATE TABLE SHARDING_TABLE
(
   ID                   INT NOT NULL,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(50) NOT NULL COMMENT '名称',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   LOGIC_TABLE_NAME     VARCHAR(255) NOT NULL COMMENT '逻辑表名称',
   ACTUAL_DATA_NODES    VARCHAR(1000) NOT NULL COMMENT '物理表表达式',
   ALGORITHM_ID         INT COMMENT '算法',
   SHARDING_COLUMN      VARCHAR(255) COMMENT '本片字段',
   TEMPLATE_ID          INT NOT NULL COMMENT '模板',
   CONFIG_ID            INT NOT NULL COMMENT '规则',
   DATA_SOURCE_ID       INT,
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_TABLE COMMENT '表规则配置表';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_TABLE
(
   CODE
);

CREATE TABLE SHARDING_TEMPLATE
(
   ID                   INT NOT NULL AUTO_INCREMENT,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(50) NOT NULL COMMENT '名称',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   JSON                 TEXT COMMENT '模板内容',
   TYPE_ID              INT NOT NULL COMMENT '模板类型',
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_TEMPLATE COMMENT '模板表，配置各类操作模板数据';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_TEMPLATE
(
   CODE
);

CREATE TABLE SHARDING_TEMPLATE_TYPE
(
   ID                   INT NOT NULL AUTO_INCREMENT,
   CODE                 VARCHAR(50) NOT NULL COMMENT '编码',
   NAME                 VARCHAR(50) NOT NULL COMMENT '名称',
   VERSION              INT NOT NULL DEFAULT 1,
   ENABLE               INT NOT NULL DEFAULT 0 COMMENT '生效',
   CREATE_TIME          DATETIME NOT NULL,
   DESCRIPTION          VARCHAR(255) COMMENT '描述',
   PRIMARY KEY (ID)
);

ALTER TABLE SHARDING_TEMPLATE_TYPE COMMENT '模板类型表';

CREATE UNIQUE INDEX CODE_UNIQUE ON SHARDING_TEMPLATE_TYPE
(
   CODE
);

ALTER TABLE SHARDING_ALGORITHMS ADD CONSTRAINT FK_ALGORITHMS_RULE FOREIGN KEY (CONFIG_ID)
      REFERENCES SHARDING_CONFIG (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_ALGORITHMS ADD CONSTRAINT FK_ALGORITHMS_TEMPLATE FOREIGN KEY (TEMPLATE_ID)
      REFERENCES SHARDING_TEMPLATE (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_DATA_SOURCE ADD CONSTRAINT FK_DATA_SOURCE_TEMPLATE FOREIGN KEY (TEMPLATE_ID)
      REFERENCES SHARDING_TEMPLATE (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_PROPERTIES ADD CONSTRAINT FK_PROPERTIES_RULE FOREIGN KEY (CONFIG_ID)
      REFERENCES SHARDING_CONFIG (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_PROPERTIES ADD CONSTRAINT FK_PROPERTIES_TEMPLATE FOREIGN KEY (TEMPLATE_ID)
      REFERENCES SHARDING_TEMPLATE (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_TABLE ADD CONSTRAINT FK_TABLE_ALGORITHMS FOREIGN KEY (ALGORITHM_ID)
      REFERENCES SHARDING_ALGORITHMS (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_TABLE ADD CONSTRAINT FK_TABLE_DATASOURCE FOREIGN KEY (DATA_SOURCE_ID)
      REFERENCES SHARDING_DATA_SOURCE (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_TABLE ADD CONSTRAINT FK_TABLE_RULE_CONFIG_RULE FOREIGN KEY (CONFIG_ID)
      REFERENCES SHARDING_CONFIG (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_TABLE ADD CONSTRAINT FK_TABLE_RULE_CONFIG_TEMPLATE FOREIGN KEY (TEMPLATE_ID)
      REFERENCES SHARDING_TEMPLATE (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE SHARDING_TEMPLATE ADD CONSTRAINT FK_TEMPLATE_TYPE FOREIGN KEY (TYPE_ID)
      REFERENCES SHARDING_TEMPLATE_TYPE (ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

