secure-producting-grid-manage/sql/init.sql

--================================== 以DBA帐号执行 ==================================
-- 创建表空间(建议就取数据库名称)
CREATE TABLESPACE "SECURE_PRODUCTING" DATAFILE 'SECURE_PRODUCTING.DBF' SIZE 1024 AUTOEXTEND ON;

-- 创建用户，并指定默认表空间。(会默认创建用户名同名的模式，所以用户名建议和表空间名称一致)
CREATE USER "SECURE_PRODUCTING" IDENTIFIED BY "SECURE_PRODUCTING.2024" DEFAULT TABLESPACE "SECURE_PRODUCTING" DEFAULT INDEX TABLESPACE "SECURE_PRODUCTING";

-- 赋予用户DBA权限
GRANT "DBA" TO "SECURE_PRODUCTING";

-- 创建表：SYS_OSS_CONFIG - 对象存储配置
CREATE TABLE "SECURE_PRODUCTING"."SYS_OSS_CONFIG"(
	"OSS_CONFIG_ID" BIGINT NOT NULL,
	"CONFIG_KEY" VARCHAR(50) NOT NULL DEFAULT '',
	"ACCESS_KEY" VARCHAR(200) NULL DEFAULT NULL,
	"SECRET_KEY" VARCHAR(200) NULL DEFAULT NULL,
	"BUCKET_NAME" VARCHAR(200) NULL DEFAULT NULL,
	"PREFIX" VARCHAR(200) NULL DEFAULT NULL,
	"ENDPOINT" VARCHAR(200) NULL DEFAULT NULL,
	"DOMAIN" VARCHAR(200) NULL DEFAULT NULL,
	"IS_HTTPS" CHAR NULL DEFAULT NULL,
	"REGION" VARCHAR(200) NULL DEFAULT NULL,
	"ACCESS_POLICY" CHAR NOT NULL DEFAULT '',
	"STATUS" CHAR NULL DEFAULT NULL,
	"EXT1" VARCHAR(200) NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("OSS_CONFIG_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_OSS_CONFIG" IS '对象存储配置';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."OSS_CONFIG_ID" IS '主建';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."CONFIG_KEY" IS '配置key';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."ACCESS_KEY" IS 'accessKey';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."SECRET_KEY" IS '秘钥';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."BUCKET_NAME" IS '桶名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."PREFIX" IS '前缀';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."ENDPOINT" IS '访问站点';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."DOMAIN" IS '自定义域名';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."IS_HTTPS" IS '是否https（Y=是,N=否）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."REGION" IS '域';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."ACCESS_POLICY" IS '桶权限类型(0=private 1=public 2=custom)';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."STATUS" IS '是否默认（0=是,1=否）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."EXT1" IS '扩展字段';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OSS_CONFIG"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_DICT_TYPE - 字典类型
CREATE TABLE "SECURE_PRODUCTING"."SYS_DICT_TYPE"(
	"DICT_ID" BIGINT NOT NULL,
	"DICT_NAME" VARCHAR(100) NULL DEFAULT NULL,
	"DICT_TYPE" VARCHAR(100) NULL DEFAULT NULL,
	"STATUS" CHAR NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("DICT_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_DICT_TYPE" IS '字典类型';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."DICT_ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."DICT_NAME" IS '字典名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."DICT_TYPE" IS '字典类型';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."STATUS" IS '状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_TYPE"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_DICT_DATA - 字典数据
CREATE TABLE "SECURE_PRODUCTING"."SYS_DICT_DATA"(
	"DICT_CODE" BIGINT NOT NULL DEFAULT 0,
	"DICT_SORT" INT NULL DEFAULT NULL,
	"DICT_LABEL" VARCHAR(100) NULL DEFAULT NULL,
	"DICT_VALUE" VARCHAR(100) NULL DEFAULT NULL,
	"DICT_TYPE" VARCHAR(100) NULL DEFAULT NULL,
	"CSS_CLASS" VARCHAR(100) NULL DEFAULT NULL,
	"LIST_CLASS" VARCHAR(100) NULL DEFAULT NULL,
	"IS_DEFAULT" CHAR NULL DEFAULT NULL,
	"STATUS" CHAR NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("DICT_CODE")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_DICT_DATA" IS '字典数据';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."DICT_CODE" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."DICT_SORT" IS '字典排序';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."DICT_LABEL" IS '字典标签';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."DICT_VALUE" IS '字典键值';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."DICT_TYPE" IS '字典类型';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."CSS_CLASS" IS '样式属性（其他样式扩展）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."LIST_CLASS" IS '表格回显样式';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."IS_DEFAULT" IS '是否默认（Y是 N否）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."STATUS" IS '状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DICT_DATA"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_CONFIG - 参数配置
CREATE TABLE "SECURE_PRODUCTING"."SYS_CONFIG"(
	"CONFIG_ID" BIGINT NOT NULL,
	"CONFIG_NAME" VARCHAR(100) NULL DEFAULT NULL,
	"CONFIG_KEY" VARCHAR(100) NULL DEFAULT NULL,
	"CONFIG_VALUE" VARCHAR(500) NULL DEFAULT NULL,
	"CONFIG_TYPE" CHAR NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("CONFIG_ID")
);
-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_CONFIG" IS '参数配置';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CONFIG_ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CONFIG_NAME" IS '参数名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CONFIG_KEY" IS '参数键名';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CONFIG_VALUE" IS '参数键值';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CONFIG_TYPE" IS '系统内置（Y是 N否）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_CONFIG"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：QUARTZ_TASK_INFO - 定时任务信息
CREATE TABLE "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"(
	"ID" BIGINT NOT NULL,
	"VERSION" BIGINT NOT NULL DEFAULT 0,
	"TASK_NO" VARCHAR(50) NOT NULL DEFAULT '',
	"TASK_NAME" VARCHAR(200) NOT NULL DEFAULT '',
	"SCHEDULER_RULE" VARCHAR(50) NOT NULL DEFAULT '',
	"STATUS" TINYINT NOT NULL DEFAULT 0,
	"EXECUTOR" VARCHAR(200) NOT NULL DEFAULT '',
	"FROZEN_TIME" BIGINT NOT NULL DEFAULT 0,
	"UNFROZEN_TIME" BIGINT NOT NULL DEFAULT 0,
	"SEND_TYPE" VARCHAR(50) NOT NULL DEFAULT '',
	"URL" VARCHAR(50) NOT NULL DEFAULT '',
	"EXECUTE_PARAMETER" VARCHAR(2000) NOT NULL DEFAULT '',
	"TIME_KEY" VARCHAR(50) NOT NULL DEFAULT '',
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_QUARTZ_TASK_INFO"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."QUARTZ_TASK_INFO" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."QUARTZ_TASK_INFO" IS '定时任务信息';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."VERSION" IS '版本号：需要乐观锁控制';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."TASK_NO" IS '任务编号';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."TASK_NAME" IS '任务名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."SCHEDULER_RULE" IS '定时规则表达式';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."STATUS" IS '冻结状态';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."EXECUTOR" IS '执行者(job方法名)';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."FROZEN_TIME" IS '冻结时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."UNFROZEN_TIME" IS '解冻时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."SEND_TYPE" IS '发送方式';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."URL" IS '请求地址';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."EXECUTE_PARAMETER" IS '执行参数';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."TIME_KEY" IS '执行时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_INFO"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：QUARTZ_TASK_ERROR - 定时任务错误信息
CREATE TABLE "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"(
	"ID" BIGINT NOT NULL,
	"TASK_EXECUTE_RECORD_ID" VARCHAR(50) NOT NULL DEFAULT '',
	"ERROR_KEY" VARCHAR(2000) NOT NULL DEFAULT '',
	"ERROR_VALUE" TEXT NOT NULL DEFAULT '',
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引
CREATE INDEX "IDX_QUARTZ_TASK_ERROR_CREATE_TIME" ON "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"("CREATE_TIME");

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_QUARTZ_TASK_ERROR"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR" IS '定时任务错误信息';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."TASK_EXECUTE_RECORD_ID" IS '任务执行记录Id';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."ERROR_KEY" IS '关键字';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."ERROR_VALUE" IS '详细信息';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_ERROR"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：QUARTZ_TASK_RECORD - 定时任务执行情况记录
CREATE TABLE "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"(
	"ID" BIGINT NOT NULL,
	"TASK_NO" VARCHAR(50) NOT NULL DEFAULT '',
	"TASK_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"EXECUTE_PARAMETER" VARCHAR(2000) NOT NULL DEFAULT '',
	"START_TIME" BIGINT NOT NULL DEFAULT 0,
	"END_TIME" BIGINT NOT NULL DEFAULT 0,
	"EXECUTE_TIMES" BIGINT NOT NULL DEFAULT 0,
	"TASK_STATUS" INT NOT NULL DEFAULT 0,
	"FAIL_REASON" VARCHAR(8000) NOT NULL DEFAULT '',
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引
CREATE INDEX "IDX_QUARTZ_TASK_RECORD_CREATE_TIME" ON "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"("CREATE_TIME");
CREATE INDEX "IDX_QUARTZ_TASK_RECORD_TASK_NO" ON "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"("TASK_NO");

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_QUARTZ_TASK_RECORD"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD" IS '定时任务执行情况记录';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."TASK_NO" IS '任务编号';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."TASK_NAME" IS '任务名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."EXECUTE_PARAMETER" IS '执行参数';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."START_TIME" IS '开始执行时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."END_TIME" IS '执行结束时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."EXECUTE_TIMES" IS '执行耗时(ms)';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."TASK_STATUS" IS '任务状态';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."FAIL_REASON" IS '失败错误描述';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."QUARTZ_TASK_RECORD"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_DEPT - 部门
CREATE TABLE "SECURE_PRODUCTING"."SYS_DEPT"(
	"DEPT_ID" BIGINT NOT NULL,
	"PARENT_ID" BIGINT NULL DEFAULT NULL,
	"ANCESTORS" VARCHAR(500) NULL DEFAULT NULL,
	"DEPT_NAME" VARCHAR(100) NULL DEFAULT NULL,
	"ORDER_NUM" INT NULL DEFAULT NULL,
	"LEADER" VARCHAR(50) NULL DEFAULT NULL,
	"PHONE" VARCHAR(50) NULL DEFAULT NULL,
	"EMAIL" VARCHAR(50) NULL DEFAULT NULL,
	"STATUS" CHAR NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"GUID" VARCHAR(50) NULL DEFAULT NULL,
	"ALL_PATH_NAME" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("DEPT_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_DEPT" IS '部门表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."DEPT_ID" IS '部门id';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."PARENT_ID" IS '父部门id';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."ANCESTORS" IS '祖级列表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."DEPT_NAME" IS '部门名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."ORDER_NUM" IS '显示顺序';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."LEADER" IS '负责人';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."PHONE" IS '联系电话';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."EMAIL" IS '邮箱';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."STATUS" IS '部门状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."GUID" IS 'h4aguid';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."ALL_PATH_NAME" IS 'h4a全路径';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_DEPT"."IS_DELETE" IS '是否删除(逻辑删除)';

-- 创建表：SYS_POST - 岗位信息
CREATE TABLE "SECURE_PRODUCTING"."SYS_POST"(
	"POST_ID" BIGINT NOT NULL,
	"POST_CODE" VARCHAR(50) NOT NULL DEFAULT '',
	"POST_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"POST_SORT" INT NOT NULL DEFAULT 0,
	"STATUS" CHAR NOT NULL DEFAULT '',
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("POST_ID")
);
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_POST" IS '岗位信息表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."POST_ID" IS '岗位ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."POST_CODE" IS '岗位编码';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."POST_NAME" IS '岗位名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."POST_SORT" IS '显示顺序';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."STATUS" IS '状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_POST"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_MENU - 菜单权限表
CREATE TABLE "SECURE_PRODUCTING"."SYS_MENU"(
	"MENU_ID" BIGINT NOT NULL,
	"GUID" VARCHAR(50) NULL DEFAULT NULL,
	"MENU_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"PARENT_ID" BIGINT NULL DEFAULT NULL,
	"ORDER_NUM" INT NULL DEFAULT NULL,
	"PATH" VARCHAR(200) NULL DEFAULT NULL,
	"COMPONENT" VARCHAR(200) NULL DEFAULT NULL,
	"QUERY_PARAM" VARCHAR(200) NULL DEFAULT NULL,
	"IS_FRAME" INT NULL DEFAULT NULL,
	"IS_CACHE" INT NULL DEFAULT NULL,
	"MENU_TYPE" CHAR NULL DEFAULT NULL,
	"VISIBLE" CHAR NULL DEFAULT NULL,
	"STATUS" CHAR NULL DEFAULT NULL,
	"PERMS" VARCHAR(500) NULL DEFAULT NULL,
	"ICON" VARCHAR(100) NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("MENU_ID")
);
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_MENU" IS '菜单权限表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."MENU_ID" IS '菜单ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."GUID" IS 'h4aguid';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."MENU_NAME" IS '菜单名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."PARENT_ID" IS '父菜单ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."ORDER_NUM" IS '显示顺序';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."PATH" IS '路由地址';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."COMPONENT" IS '组件路径';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."QUERY_PARAM" IS '路由参数';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."IS_FRAME" IS '是否为外链（0是 1否）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."IS_CACHE" IS '是否缓存（0缓存 1不缓存）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."MENU_TYPE" IS '菜单类型（M目录 C菜单 F按钮）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."VISIBLE" IS '显示状态（0显示 1隐藏）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."STATUS" IS '菜单状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."PERMS" IS '权限标识';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."ICON" IS '菜单图标';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_MENU"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_ROLE - 角色信息表
CREATE TABLE "SECURE_PRODUCTING"."SYS_ROLE"(
	"ROLE_ID" VARCHAR(50) NOT NULL DEFAULT '',
	"GUID" VARCHAR(50) NULL DEFAULT NULL,
	"ROLE_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"ROLE_KEY" VARCHAR(100) NOT NULL DEFAULT '',
	"ROLE_SORT" INT NOT NULL DEFAULT 0,
	"DATA_SCOPE" CHAR NULL DEFAULT NULL,
	"MENU_CHECK_STRICTLY" TINYINT NULL DEFAULT NULL,
	"DEPT_CHECK_STRICTLY" TINYINT NULL DEFAULT NULL,
	"STATUS" CHAR NOT NULL DEFAULT '',
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ROLE_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_ROLE" IS '角色信息表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."ROLE_ID" IS '角色ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."ROLE_NAME" IS '角色名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."ROLE_KEY" IS '角色权限字符串';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."ROLE_SORT" IS '显示顺序';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."DATA_SCOPE" IS '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."MENU_CHECK_STRICTLY" IS '菜单树选择项是否关联显示';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."DEPT_CHECK_STRICTLY" IS '部门树选择项是否关联显示';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."STATUS" IS '角色状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."GUID" IS 'h4aguid';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE"."IS_DELETE" IS '是否删除(逻辑删除)';

-- 创建表：SYS_ROLE_MENU - 角色和菜单关联表
CREATE TABLE "SECURE_PRODUCTING"."SYS_ROLE_MENU"(
	"ROLE_ID" BIGINT NOT NULL,
	"MENU_ID" BIGINT NOT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ROLE_ID","MENU_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_ROLE_MENU" IS '角色和菜单关联表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE_MENU"."ROLE_ID" IS '角色ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE_MENU"."MENU_ID" IS '菜单ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE_MENU"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_ROLE_DEPT - 角色和部门关联表
CREATE TABLE "SECURE_PRODUCTING"."SYS_ROLE_DEPT"(
	"ROLE_ID" BIGINT NOT NULL,
	"DEPT_ID" BIGINT NOT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ROLE_ID","DEPT_ID")
);


-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_ROLE_DEPT" IS '角色和部门关联表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE_DEPT"."ROLE_ID" IS '角色ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE_DEPT"."DEPT_ID" IS '部门ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_ROLE_DEPT"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_USER - 用户信息表
CREATE TABLE "SECURE_PRODUCTING"."SYS_USER"(
	"USER_ID" BIGINT NOT NULL,
	"USER_GUID" VARCHAR(50) NULL DEFAULT NULL,
	"DEPT_ID" BIGINT NULL DEFAULT NULL,
	"USER_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"NICK_NAME" VARCHAR(100) NOT NULL DEFAULT '',
	"USER_TYPE" VARCHAR(50) NULL DEFAULT NULL,
	"EMAIL" VARCHAR(50) NULL DEFAULT NULL,
	"PHONENUMBER" VARCHAR(50) NULL DEFAULT NULL,
	"SEX" CHAR NULL DEFAULT NULL,
	"AVATAR" VARCHAR(100) NULL DEFAULT NULL,
	"PASSWORD" VARCHAR(100) NULL DEFAULT NULL,
	"STATUS" CHAR NULL DEFAULT NULL,
	"LOGIN_IP" VARCHAR(200) NULL DEFAULT NULL,
	"LOGIN_DATE" TIMESTAMP(0) NULL DEFAULT NULL,
	"CREATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"CREATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"UPDATE_BY" VARCHAR(50) NULL DEFAULT NULL,
	"UPDATE_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"REMARK" VARCHAR(500) NULL DEFAULT NULL,
	"PERSON_ID" VARCHAR(50) NULL DEFAULT NULL,
	"ORG_KE_ID" VARCHAR(50) NULL DEFAULT NULL,
	"ORG_KE_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"ORG_CHU_ID" VARCHAR(50) NULL DEFAULT NULL,
	"ORG_CHU_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"RANK_CODE" VARCHAR(50) NULL DEFAULT NULL,
	"RANK_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"ORG_TYPE" VARCHAR(50) NULL DEFAULT NULL,
	"LOGIN_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"SELECTED" VARCHAR(50) NULL DEFAULT NULL,
	"EXPIRATION_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"SLIDE_LINE" VARCHAR(50) NULL DEFAULT NULL,
	"ALL_PATH_NAME" VARCHAR(500) NULL DEFAULT NULL,
	"GLOBAL_SORT" VARCHAR(200) NULL DEFAULT NULL,
	"PARENT_GUID" VARCHAR(50) NULL DEFAULT NULL,
	"IS_MESSAGE" CHAR NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("USER_ID")
);

-- 索引

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_SYS_USER"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."SYS_USER" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_USER" IS '用户信息表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."USER_ID" IS '用户ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."USER_GUID" IS 'h4aguid';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."DEPT_ID" IS '部门ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."USER_NAME" IS '用户账号';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."NICK_NAME" IS '用户昵称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."USER_TYPE" IS '用户类型（sys_user系统用户）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."EMAIL" IS '用户邮箱';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."PHONENUMBER" IS '手机号码';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."SEX" IS '用户性别（0男 1女 2未知）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."AVATAR" IS '头像地址';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."PASSWORD" IS '密码';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."STATUS" IS '帐号状态（0正常 1停用）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."LOGIN_IP" IS '最后登录IP';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."LOGIN_DATE" IS '最后登录时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."UPDATE_BY" IS '更新者';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."PERSON_ID" IS '工号';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."ORG_KE_ID" IS '科级部门ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."ORG_KE_NAME" IS '科级部门名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."ORG_CHU_ID" IS '处级部门ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."ORG_CHU_NAME" IS '处级部门名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."RANK_CODE" IS '职级';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."RANK_NAME" IS '职务名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."ORG_TYPE" IS '机构类型';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."LOGIN_NAME" IS '登录名';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."SELECTED" IS '是否选择';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."EXPIRATION_TIME" IS '过期时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."SLIDE_LINE" IS '是否为兼职';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."ALL_PATH_NAME" IS '人员完整路径';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."GLOBAL_SORT" IS '人员完整路径ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."PARENT_GUID" IS '直接部门GUID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."IS_MESSAGE" IS '是否开启短信推送';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_USER_ROLE - 用户和角色关联表
CREATE TABLE "SECURE_PRODUCTING"."SYS_USER_ROLE"(
	"USER_ID" BIGINT NOT NULL,
	"ROLE_ID" BIGINT NOT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("USER_ID","ROLE_ID")
);


-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_USER_ROLE" IS '用户和角色关联表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER_ROLE"."USER_ID" IS '用户ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER_ROLE"."ROLE_ID" IS '角色ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER_ROLE"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_USER_POST - 用户与岗位关联表
CREATE TABLE "SECURE_PRODUCTING"."SYS_USER_POST"(
	"USER_ID" BIGINT NOT NULL,
	"POST_ID" BIGINT NOT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("USER_ID","POST_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_USER_POST" IS '用户与岗位关联表';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER_POST"."USER_ID" IS '用户ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER_POST"."POST_ID" IS '岗位ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_USER_POST"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_OPER_LOG - 操作日志记录
CREATE TABLE "SECURE_PRODUCTING"."SYS_OPER_LOG"(
	"OPER_ID" BIGINT NOT NULL,
	"TITLE" VARCHAR(50) NULL DEFAULT NULL,
	"BUSINESS_TYPE" INT NULL DEFAULT NULL,
	"METHOD" VARCHAR(100) NULL DEFAULT NULL,
	"REQUEST_METHOD" VARCHAR(50) NULL DEFAULT NULL,
	"OPERATOR_TYPE" INT NULL DEFAULT NULL,
	"OPER_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"DEPT_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"OPER_URL" VARCHAR(200) NULL DEFAULT NULL,
	"OPER_IP" VARCHAR(200) NULL DEFAULT NULL,
	"OPER_LOCATION" VARCHAR(200) NULL DEFAULT NULL,
	"OPER_PARAM" VARCHAR(2000) NULL DEFAULT NULL,
	"JSON_RESULT" VARCHAR(4000) NULL DEFAULT NULL,
	"STATUS" INT NULL DEFAULT NULL,
	"ERROR_MSG" VARCHAR(2000) NULL DEFAULT NULL,
	"OPER_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("OPER_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_OPER_LOG" IS '操作日志记录';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_ID" IS '日志主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."TITLE" IS '模块标题';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."BUSINESS_TYPE" IS '业务类型（0其它 1新增 2修改 3删除）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."METHOD" IS '方法名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."REQUEST_METHOD" IS '请求方式';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPERATOR_TYPE" IS '操作类别（0其它 1后台用户 2手机端用户）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_NAME" IS '操作人员';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."DEPT_NAME" IS '部门名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_URL" IS '请求URL';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_IP" IS '主机地址';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_LOCATION" IS '操作地点';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_PARAM" IS '请求参数';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."JSON_RESULT" IS '返回参数';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."STATUS" IS '操作状态（0正常 1异常）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."ERROR_MSG" IS '错误消息';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."OPER_TIME" IS '操作时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_OPER_LOG"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_LOGININFOR - 系统访问记录
CREATE TABLE "SECURE_PRODUCTING"."SYS_LOGININFOR"(
	"INFO_ID" BIGINT NOT NULL,
	"USER_NAME" VARCHAR(50) NULL DEFAULT NULL,
	"IPADDR" VARCHAR(200) NULL DEFAULT NULL,
	"LOGIN_LOCATION" VARCHAR(200) NULL DEFAULT NULL,
	"BROWSER" VARCHAR(50) NULL DEFAULT NULL,
	"OS" VARCHAR(50) NULL DEFAULT NULL,
	"STATUS" CHAR NULL DEFAULT NULL,
	"MSG" VARCHAR(200) NULL DEFAULT NULL,
	"LOGIN_TIME" TIMESTAMP(0) NULL DEFAULT NULL,
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("INFO_ID")
);

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_LOGININFOR" IS '系统访问记录';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."INFO_ID" IS '访问ID';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."USER_NAME" IS '用户账号';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."IPADDR" IS '登录IP地址';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."LOGIN_LOCATION" IS '登录地点';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."BROWSER" IS '浏览器类型';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."OS" IS '操作系统';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."STATUS" IS '登录状态（0成功 1失败）';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."MSG" IS '提示消息';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."LOGIN_TIME" IS '访问时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_LOGININFOR"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SYS_TOTAL_STATISTICS - 业务总数据统计
CREATE TABLE "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"(
	"ID" BIGINT NOT NULL,
	"STATISTICS_TYPE" TINYINT NOT NULL DEFAULT 0,
	"STATISTICS_NAME" VARCHAR(200) NOT NULL DEFAULT '',
	"STATISTICS_VALUE" BIGINT NOT NULL DEFAULT 0,
	"STATISTICS_TIME" VARCHAR(100) NOT NULL DEFAULT '',
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引
CREATE INDEX "IDX_SYS_TOTAL_STATISTICS_STATISTICS_TYPE" ON "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"("STATISTICS_TYPE");
CREATE INDEX "IX_SYS_TOTAL_STATISTICS_CREATE_TIME" ON "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"("CREATE_TIME");

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_SYS_TOTAL_STATISTICS"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS" IS '业务总数据统计';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."ID" IS '主键id';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."STATISTICS_TYPE" IS '统计业务类型 EStatisticsType';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."STATISTICS_NAME" IS '统计业务名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."STATISTICS_VALUE" IS '统计值';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."STATISTICS_TIME" IS '最后统计时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SYS_TOTAL_STATISTICS"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：STATISTICS_USER_LOGIN - 统计-用户登录数量
CREATE TABLE "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"(
	"ID" BIGINT NOT NULL,
	"YEAR" VARCHAR(50) NOT NULL DEFAULT '',
	"MONTH" VARCHAR(50) NOT NULL DEFAULT '',
	"DATE" VARCHAR(50) NOT NULL DEFAULT '',
	"DATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"LOGIN_COUNT" BIGINT NOT NULL DEFAULT 0,
	"LOGIN_COUNT_DISTINCT" BIGINT NOT NULL DEFAULT 0,
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引
CREATE INDEX "IDX_STATISTICS_USER_LOGIN_CREATE_TIME" ON "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"("CREATE_TIME");
CREATE INDEX "IDX_STATISTICS_USER_LOGIN_DATE_TIME" ON "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"("DATE_TIME");

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_STATISTICS_USER_LOGIN"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN" IS '统计-用户登录数量';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."YEAR" IS '年份';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."MONTH" IS '月份 yyyy-MM';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."DATE" IS '日期 yyyy-MM-dd';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."DATE_TIME" IS 'DATE对应的时间戳 yyyy-MM-dd 00:00:00';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."LOGIN_COUNT" IS '用户登录数量';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."LOGIN_COUNT_DISTINCT" IS '有效用户登录数量';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."STATISTICS_USER_LOGIN"."IS_DELETE" IS '是否删除(逻辑删除)';


-- 创建表：SP_POLICY_CATEGORY - 政策法规分类
CREATE TABLE "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"(
	"ID" BIGINT NOT NULL,
	"CATEGORY_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"REMARK" VARCHAR(1000) NOT NULL DEFAULT '',
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引
CREATE INDEX "IDX_SP_POLICY_CATEGORY_CREATE_TIME" ON "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"("CREATE_TIME");

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_SP_POLICY_CATEGORY"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."SP_POLICY_CATEGORY" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SP_POLICY_CATEGORY" IS '政策法规分类';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"."ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"."CATEGORY_NAME" IS '分类名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"."REMARK" IS '描述';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_CATEGORY"."IS_DELETE" IS '是否删除(逻辑删除)';

-- 创建表：SP_POLICY_PUBLISH - 政策法规发布信息
CREATE TABLE "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"(
	"ID" BIGINT NOT NULL,
	"CATEGORY_ID" BIGINT NOT NULL,
	"CATEGORY_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"TITLE" VARCHAR(200) NOT NULL DEFAULT '',
	"FILE_PATH" VARCHAR(500) NOT NULL DEFAULT '',
	"FILE_PDF_PATH" VARCHAR(500) NOT NULL DEFAULT '',
	"REMARK" VARCHAR(1000) NOT NULL DEFAULT '',
	"CREATE_TIME" BIGINT NOT NULL DEFAULT 0,
	"UPDATE_TIME" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	"CREATE_BY" VARCHAR(50) NOT NULL DEFAULT '',
	"CREATE_BY_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"UPDATE_BY" VARCHAR(50) NOT NULL DEFAULT '',
	"UPDATE_BY_NAME" VARCHAR(50) NOT NULL DEFAULT '',
	"IS_DELETE" TINYINT NOT NULL DEFAULT 0,
	-- 设置主键
	PRIMARY KEY("ID")
);

-- 索引
CREATE INDEX "IDX_SP_POLICY_PUBLISH_CATEGORY_ID" ON "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"("CATEGORY_ID");
CREATE INDEX "IDX_SP_POLICY_PUBLISH_CREATE_TIME" ON "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"("CREATE_TIME");

-- 触发器：自动赋值最后修改时间
CREATE OR REPLACE TRIGGER "TG_SP_POLICY_PUBLISH"
BEFORE INSERT OR UPDATE ON "SECURE_PRODUCTING"."SP_POLICY_PUBLISH" FOR EACH ROW
BEGIN
  :new.UPDATE_TIME=CURRENT_TIMESTAMP();
END;

-- 字段描述
COMMENT ON TABLE "SECURE_PRODUCTING"."SP_POLICY_PUBLISH" IS '政策法规发布信息';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."ID" IS '主键';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."CATEGORY_ID" IS '所属分类';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."CATEGORY_NAME" IS '所属分类名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."TITLE" IS '标题';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."FILE_PATH" IS '政策文件转pdf路径';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."FILE_PDF_PATH" IS '';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."REMARK" IS '备注';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."CREATE_TIME" IS '数据创建时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."CREATE_BY" IS '数据创建人';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."CREATE_BY_NAME" IS '数据创建人名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."UPDATE_BY" IS '最后更新人';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."UPDATE_BY_NAME" IS '最后更新人名称';
COMMENT ON COLUMN "SECURE_PRODUCTING"."SP_POLICY_PUBLISH"."IS_DELETE" IS '是否删除(逻辑删除)';