/* change.sql 说明 1.数据库相关修改添加到对应的 change.sql 文件，无需调整其他 sql 文件 2.sql 语句验证正确后再提交 */ -- yjj 2022-04-19 增加表单列表数据导入日志记录表 CREATE TABLE FORM_DATA_TEMPLATE_IMPORT_LOG ( "ID_" NVARCHAR2(64) NOT NULL, "IMPORT_ACCOUNT_" NVARCHAR2(255) DEFAULT NULL, "IMPORT_NAME_" NVARCHAR2(255) DEFAULT NULL, "IMPORT_TIME_" DATE DEFAULT NULL, "STATUS_" CHAR(1) DEFAULT NULL, "TENANT_ID_" NVARCHAR2(64) DEFAULT NULL, "REASON_" CLOB, "TEMP_ALIAS_" NVARCHAR2(64) ); COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.ID_ IS '主键'; COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.IMPORT_ACCOUNT_ IS '导入人账号'; COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.IMPORT_NAME_ IS '导入人名称'; COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.IMPORT_TIME_ IS '导入时间'; COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.STATUS_ IS '导入状态 1-导入成功 0-导入失败'; COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.TENANT_ID_ IS '租户id'; COMMENT ON COLUMN FORM_DATA_TEMPLATE_IMPORT_LOG.TEMP_ALIAS_ IS '导入表单列表别名'; COMMENT ON TABLE FORM_DATA_TEMPLATE_IMPORT_LOG IS '表单列表导入日志记录'; -- 2022-04-24 yijj 增加关联表sql字段 ALTER TABLE FORM_DATA_TEMPLATE ADD JOIN_TABLES_SQL_ CLOB; COMMENT ON COLUMN FORM_DATA_TEMPLATE.JOIN_TABLES_SQL_ IS '关联表SQL'; -- 黎扬贵 2022-05-07 新增任务表索引 CREATE INDEX "IDX_BPM_TASK_OWNER_ID_" ON "BPM_TASK" ("OWNER_ID_"); CREATE INDEX "IDX_BPM_TASK_ASSIGNEE_ID_" ON "BPM_TASK" ("ASSIGNEE_ID_"); -- 黎扬贵 2022-05-07 新增任务候选人表 索引 CREATE INDEX "IDX_TASKCANDIDATE_EXECUTOR" ON "BPM_TASK_CANDIDATE" ("EXECUTOR_"); CREATE INDEX "IDX_TASKCANDIDATE_TYPE" ON "BPM_TASK_CANDIDATE" ("TYPE_"); -- 欧阳高龙 2022-05-09 组织与外部通讯录关系表 CREATE TABLE "UC_ORG_UNITE" ( "ID_" NVARCHAR2(64) NOT NULL, "ORG_ID_" NVARCHAR2(64) DEFAULT NULL, "WX_WORK_ID_" NVARCHAR2(100) DEFAULT NULL, "DINGTALK_ID_" NVARCHAR2(100) DEFAULT NULL, "FLYBOOK_ID_" NVARCHAR2(100) DEFAULT NULL, "DELETE_FLAG_" NUMBER(1,0) DEFAULT 0 NOT NULL, "UPDATE_TIME_" DATE, "CREATE_TIME_" DATE, "TENANT_ID_" NVARCHAR2(64) DEFAULT NULL ); COMMENT ON COLUMN "UC_ORG_UNITE"."ID_" IS '主键'; COMMENT ON COLUMN "UC_ORG_UNITE"."ORG_ID_" IS '平台组织id'; COMMENT ON COLUMN "UC_ORG_UNITE"."WX_WORK_ID_" IS '企业微信组织id'; COMMENT ON COLUMN "UC_ORG_UNITE"."DINGTALK_ID_" IS '阿里钉钉组织id'; COMMENT ON COLUMN "UC_ORG_UNITE"."FLYBOOK_ID_" IS '飞书组织id'; COMMENT ON COLUMN "UC_ORG_UNITE"."DELETE_FLAG_" IS '是否删除'; COMMENT ON COLUMN "UC_ORG_UNITE"."UPDATE_TIME_" IS '更新时间'; COMMENT ON COLUMN "UC_ORG_UNITE"."CREATE_TIME_" IS '创建时间'; COMMENT ON COLUMN "UC_ORG_UNITE"."TENANT_ID_" IS '租户ID'; COMMENT ON TABLE "UC_ORG_UNITE" IS '组织与外部通讯录关系表'; -- Primary Key structure for table UC_ORG_UNITE ALTER TABLE "UC_ORG_UNITE" ADD CONSTRAINT "SYS_C00143854" PRIMARY KEY ("ID_"); -- 欧阳高龙 2022-05-09 增加外部通讯录同步使用到的配置信息 ALTER TABLE UC_USER_UNITE ADD "FLYBOOK_ID_" NVARCHAR2(100) DEFAULT NULL; comment on column UC_USER_UNITE.FLYBOOK_ID_ is '飞书用户ID'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_FLAG_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_FLAG_ is '是否同步组织架构'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_SCOPE_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_SCOPE_ is '同步范围，0-全部；1-仅用户；2-部分'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_ORG_ID_" NVARCHAR2(64) DEFAULT NULL; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_ORG_ID_ is '同步的组织的ID'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_ORG_" NVARCHAR2(64) DEFAULT NULL; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_ORG_ is '同步组织'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_DIRECTION_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_DIRECTION_ is '同步方式，拉取或上传'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "AUTO_SYNC_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE; comment on column PORTAL_SYS_EXTERNAL_UNITE.AUTO_SYNC_ is '同步方法，是否自动同步'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_FREQUENCY_" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_FREQUENCY_ is '同步频率，0-每周；1-每月；2-每小时'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "SYNC_STARTING_TIME" DATE; comment on column PORTAL_SYS_EXTERNAL_UNITE.SYNC_STARTING_TIME is '同步起始时间'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "AGENT_NAME_" NVARCHAR2(64) DEFAULT NULL; comment on column PORTAL_SYS_EXTERNAL_UNITE.AGENT_NAME_ is '应用名称'; ALTER TABLE PORTAL_SYS_EXTERNAL_UNITE ADD "ALIAS_" NVARCHAR2(64) DEFAULT NULL; comment on column PORTAL_SYS_EXTERNAL_UNITE.ALIAS_ is '应用别名'; -- 2022-04-24 yijj 增加角色授权菜单设置为是否本级及下级 ALTER TABLE PORTAL_SYS_ROLE_AUTH ADD IS_OPEN_ NUMBER(*,0) DEFAULT NULL; COMMENT ON COLUMN PORTAL_SYS_ROLE_AUTH.IS_OPEN_ IS '是否本级及下级，1-是本级及下级，0-不是本级及下级'; -- 2022-7-22 liangjc修改栏目类型的类型 增加js脚本字段 ALTER TABLE portal_sys_column ADD TEMPLATE_JAVA_SCRIPT_ CLOB default null ; alter table portal_sys_column rename column COL_TYPE to name_tmp; alter table portal_sys_column add COL_TYPE NVARCHAR2(50); update portal_sys_column set COL_TYPE=trim(name_tmp); alter table portal_sys_column drop column name_tmp; -- 2022-7-25 liangjc portal_sys_column 增加长度 ALTER TABLE portal_sys_column MODIFY DATA_PARAM NVARCHAR2(2000); -- 2022-07-22 liangjc迁移 个人待办事项表（三门核电） CREATE TABLE "PORTAL_CUSTOM_TODO" ( "ID_" NVARCHAR2(64) NOT NULL, "USER_ID_" NVARCHAR2(64) NOT NULL, "TASK_NAME_" NVARCHAR2(255), "MARK_" NVARCHAR2(255), "STATUS_" NVARCHAR2(1), "REMARK_" NVARCHAR2(2000), "IS_REPEAT_" NVARCHAR2(1), "REPEAT_RATE_" NUMBER, "REPEAT_TYPE_" NVARCHAR2(1), "REPEAT_START_TIME_" DATE, "CREATE_TIME_" DATE, "COMPLETE_TIME_" DATE, "DEAD_LINE_" DATE, "UPDATE_TIME_" DATE, "SN_" NUMBER, primary key (ID_) ); COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."ID_" IS '主键'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."USER_ID_" IS '用户id'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."TASK_NAME_" IS '任务事项'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."MARK_" IS '标记'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."STATUS_" IS '状态。0:未完成，1：已完成'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REMARK_" IS '备注'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."IS_REPEAT_" IS '是否重复任务。0:否，1:是'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REPEAT_RATE_" IS '重复频率'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REPEAT_TYPE_" IS '重复类型：1:年。2:月，3:日，4:小时，5:分钟'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."REPEAT_START_TIME_" IS '重复任务开始时间'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."CREATE_TIME_" IS '创建时间'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."COMPLETE_TIME_" IS '完成时间'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."DEAD_LINE_" IS '截止时间'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."UPDATE_TIME_" IS '更新时间'; COMMENT ON COLUMN "PORTAL_CUSTOM_TODO"."SN_" IS '排序号'; COMMENT ON TABLE "PORTAL_CUSTOM_TODO" IS '用户自定义待办事项表'; -- 2022-7-26 liangjc 获取微信公众号素材列表 INSERT INTO qrtz_job_details(SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA) VALUES ('quartzScheduler', '获取微信公众号素材列表', '-1', '获取微信公众号素材列表', 'com.hotent.column.job.PullWeixinMaterialJob', '1', '1', '0', '0', HEXTORAW('ACED0005737200156F72672E71756172747A2E4A6F62446174614D61709FB083E8BFA9B0CB020000787200266F72672E71756172747A2E7574696C732E537472696E674B65794469727479466C61674D61708208E8C3FBC55D280200015A0013616C6C6F77735472616E7369656E74446174617872001D6F72672E71756172747A2E7574696C732E4469727479466C61674D617013E62EAD28760ACE0200025A000564697274794C00036D617074000F4C6A6176612F7574696C2F4D61703B787000737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F40000000000010770800000010000000007800')); -- 2022-7-29 liangjc portal_sys_column 增加长度 alter table portal_sys_column rename column DATA_PARAM to name_tmp; alter table portal_sys_column add DATA_PARAM CLOB; update portal_sys_column set DATA_PARAM=trim(name_tmp); alter table portal_sys_column drop column name_tmp; -- 2022-8-1 liangjc portal_sys_layout_manage 增加栏目间隔 ALTER TABLE portal_sys_layout_manage ADD GUTTER_ number default null; comment on column portal_sys_layout_manage.GUTTER_ is '栏目间隔'; -- 2022-8-1 liangjc 会议室相关表迁移 CREATE TABLE "PORTAL_MEETING_APPLY" ( "ID_" NVARCHAR2(64) NOT NULL, "SUBJECT_" NVARCHAR2(300), "MEETING_NAME_" NVARCHAR2(64), "MEETING_ID_" NVARCHAR2(64), "MEETING_OWNER_" NVARCHAR2(64), "OWNER_OWNER_ID_" NVARCHAR2(64), "MEETING_DEP_" NVARCHAR2(64), "MEETING_DEP_ID_" NVARCHAR2(64), "PARTICIPANTS_" CLOB, "PARTICIPANTS_ID_" CLOB, "DEPARTMENT_" NVARCHAR2(1000), "DEPARTMENT_ID_" NVARCHAR2(1000), "OUTSIDERS_" NVARCHAR2(600), "ATTENDANCE_" NUMBER(11,0), "MEETING_NOTES_" CLOB, "FILES_" CLOB, "APPLY_DESC_" CLOB, "START_TIME_" TIMESTAMP (0), "END_TIME_" TIMESTAMP (0), "BIND_FLOW_" NUMBER(6,0), "FLOW_KEY_" NVARCHAR2(64), "FLOW_NAME_" NVARCHAR2(64), "PUBLISH_SCHEDULE_" NUMBER(6,0), "STATUS_" NVARCHAR2(20), "SERVICE_" NVARCHAR2(600), "PRO_INST_ID_" NVARCHAR2(64), "CREATE_BY_" NVARCHAR2(64), "UPDATE_BY_" NVARCHAR2(64), "CREATE_TIME_" TIMESTAMP (0), "UPDATE_TIME_" TIMESTAMP (0), "CREATE_ORG_ID_" NVARCHAR2(64), "TENANT_ID_" NVARCHAR2(64), PRIMARY KEY ("ID_") ); COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."ID_" IS '主键'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."SUBJECT_" IS '会议主题'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_NAME_" IS '会议室名称'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_ID_" IS '会议室id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_OWNER_" IS '会议申请人'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."OWNER_OWNER_ID_" IS '会议申请人id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_DEP_" IS '所属部门'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_DEP_ID_" IS '所属部门id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PARTICIPANTS_" IS '参会人员'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PARTICIPANTS_ID_" IS '参会人员id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."DEPARTMENT_" IS '参会部门'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."DEPARTMENT_ID_" IS '参会部门id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."OUTSIDERS_" IS '外部人员'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."ATTENDANCE_" IS '参会人数'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."MEETING_NOTES_" IS '会议说明'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."FILES_" IS '相关附件'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."APPLY_DESC_" IS '申请说明'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."START_TIME_" IS '开始时间'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."END_TIME_" IS '结束时间'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."BIND_FLOW_" IS '是否绑定流程'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."FLOW_KEY_" IS '流程key'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."FLOW_NAME_" IS '流程名称'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PUBLISH_SCHEDULE_" IS '发布到日程'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."STATUS_" IS '申请状态'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."SERVICE_" IS '所需服务'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."PRO_INST_ID_" IS '流程实例id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."CREATE_BY_" IS '创建人id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."UPDATE_BY_" IS '更新人id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."CREATE_TIME_" IS '创建时间'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."UPDATE_TIME_" IS '更新时间'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."CREATE_ORG_ID_" IS '创建人所属部门id'; COMMENT ON COLUMN "PORTAL_MEETING_APPLY"."TENANT_ID_" IS '租户id'; COMMENT ON TABLE "PORTAL_MEETING_APPLY" IS '会议室申请'; CREATE TABLE "PORTAL_MEETING_CONFIG" ( "ID_" VARCHAR(64) NOT NULL, "BIND_FLOW_" NUMBER(6,0), "FLOW_KEY_" NVARCHAR2(64), "FLOW_NAME_" NVARCHAR2(64), "PUBLISH_SCHEDULE_" NUMBER(6,0), "FIELD_MAPPING_" CLOB, "CREATE_BY_" NVARCHAR2(64), "UPDATE_BY_" NVARCHAR2(64), "CREATE_TIME_" TIMESTAMP (0), "UPDATE_TIME_" TIMESTAMP (0), "CREATE_ORG_ID_" NVARCHAR2(64), "TENANT_ID_" NVARCHAR2(64), "DIC_CODE_" NVARCHAR2(64), "DIC_NAME_" NVARCHAR2(64), "COLUMN_ID_" NVARCHAR2(64), PRIMARY KEY ("ID_") ); COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."ID_" IS '主键'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."BIND_FLOW_" IS '是否绑定流程'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."FLOW_KEY_" IS '流程key'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."FLOW_NAME_" IS '流程名称'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."PUBLISH_SCHEDULE_" IS '发布到日程'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."FIELD_MAPPING_" IS '流程实例ID'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."CREATE_BY_" IS '创建人id'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."UPDATE_BY_" IS '更新人id'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."CREATE_TIME_" IS '创建时间'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."UPDATE_TIME_" IS '更新时间'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."CREATE_ORG_ID_" IS '创建人所属部门id'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."TENANT_ID_" IS '租户id'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."DIC_CODE_" IS '会议室设备字典编码'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."DIC_NAME_" IS '会议室设备字典名称'; COMMENT ON COLUMN "PORTAL_MEETING_CONFIG"."COLUMN_ID_" IS 'lan'; COMMENT ON TABLE "PORTAL_MEETING_CONFIG" IS '会议室配置'; CREATE TABLE "PORTAL_MEETING_ROOM" ( "ID_" NVARCHAR2(64) NOT NULL, "NAME_" NVARCHAR2(64), "ALIAS_" NVARCHAR2(64), "TYPE_" NVARCHAR2(64), "CAPACITY_" NUMBER(11,0), "ADDRESS_" NVARCHAR2(600), "FLOOR_ROOM_NUMBER_" NVARCHAR2(64), "EQUIPMENT_" CLOB, "STATUS_" NVARCHAR2(20), "DESC_" CLOB, "CREATE_BY_" NVARCHAR2(64), "UPDATE_BY_" NVARCHAR2(64), "CREATE_TIME_" TIMESTAMP (0), "UPDATE_TIME_" TIMESTAMP (0), "CREATE_ORG_ID_" NVARCHAR2(64), "TENANT_ID_" NVARCHAR2(64), PRIMARY KEY ("ID_") ); COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."ID_" IS '主键'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."NAME_" IS '会议室名称'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."ALIAS_" IS '会议室别名'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."TYPE_" IS '会议室类型'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CAPACITY_" IS '会议室容量'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."ADDRESS_" IS '会议室地址'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."FLOOR_ROOM_NUMBER_" IS '楼层房间号'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."EQUIPMENT_" IS '包含设备'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."STATUS_" IS '会议室状态（可用、不可用）'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."DESC_" IS '会议室说明描述'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CREATE_BY_" IS '创建人id'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."UPDATE_BY_" IS '更新人id'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CREATE_TIME_" IS '创建时间'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."UPDATE_TIME_" IS '更新时间'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."CREATE_ORG_ID_" IS '创建人所属部门id'; COMMENT ON COLUMN "PORTAL_MEETING_ROOM"."TENANT_ID_" IS '租户id'; COMMENT ON TABLE "PORTAL_MEETING_ROOM" IS '会议室管理'; -- 2022-8-4 xiejun uc_user 增加双因素验证密钥 alter table uc_user add TWO_VERIFY_SECRET_ NVARCHAR2(50) default null; COMMENT ON COLUMN uc_user.TWO_VERIFY_SECRET_ IS '双因素验证绑定密钥'; CREATE TABLE UC_ONE_TIME_CODE( ID_ NVARCHAR2(64) NOT NULL PRIMARY KEY , CODE_ NVARCHAR2(50) NOT NULL , USER_ID_ NVARCHAR2(64) NOT NULL, IS_USED_ NUMBER(1) DEFAULT 0, TENANT_ID_ NVARCHAR2(64), CREATE_TIME_ DATE NOT NULL , CREATOR_ NVARCHAR2(32) NOT NULL, CREATE_BY_ NVARCHAR2(64) NOT NULL, IS_DELE_ NUMBER(1) default 0 not null ); comment on table UC_ONE_TIME_CODE is '双因素验证一次性码表'; comment on column UC_ONE_TIME_CODE.ID_ is '主键'; comment on column UC_ONE_TIME_CODE.CODE_ is '一次性验证码'; comment on column UC_ONE_TIME_CODE.USER_ID_ is '所属用户ID'; comment on column UC_ONE_TIME_CODE.IS_USED_ is '是否已使用;0:未使用 1:已使用'; comment on column UC_ONE_TIME_CODE.TENANT_ID_ is '租户id'; comment on column UC_ONE_TIME_CODE.CREATE_TIME_ is '创建时间'; comment on column UC_ONE_TIME_CODE.CREATOR_ is '创建人'; comment on column UC_ONE_TIME_CODE.CREATE_BY_ is '创建人ID'; comment on column UC_ONE_TIME_CODE.IS_DELE_ is '是否删除;0否 1是'; -- 2022-8-19 liangjc portal_user_layout_config 增加创建时间字段 alter table PORTAL_USER_LAYOUT_CONFIG add CREATE_TIME_ DATE; comment on column PORTAL_USER_LAYOUT_CONFIG.CREATE_TIME_ is '创建时间'; -- 2022-8-26 jjx 增加双因子认证系统属性默认配置 INSERT INTO "PORTAL_SYS_PROPERTIES"("ID", "NAME", "ALIAS", "GROUP_", "VALUE", "ENCRYPT", "DESCRIPTION", "UPDATE_TIME_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "TENANT_ID_") VALUES ('8', '双因素认证', 'twoVerifyConf', NULL, 'false', '0', '双因子验证系统配置', NULL, '1', TO_DATE('2022-08-26 10:47:04', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL); -- 2022-8-26 jjx 月活跃限制表 create table BPM_BLOOM ( ID_ NVARCHAR2(64) not null constraint BPM_BLOOM_PK primary key, BLOOM_BYTES_ clob, CODE_ NVARCHAR2(255) ); comment on table BPM_BLOOM is '布隆过滤器数据表'; comment on column BPM_BLOOM.ID_ is '主键'; comment on column BPM_BLOOM.BLOOM_BYTES_ is '布隆过滤器的数据'; comment on column BPM_BLOOM.CODE_ is '编码，做唯一记录识别'; -- 2022-08-29 yijj BO定义表增加后端校验json字段 ALTER TABLE "FORM_BO_DEF" ADD ("VERIFY_JSON_" CLOB); COMMENT ON COLUMN "FORM_BO_DEF"."VERIFY_JSON_" IS '后端接口校验json'; -- 2022-8-30 liangjc 删除模板管理菜单 delete from portal_sys_menu where ALIAS_='formTemplateList'; -- 2022-8-30 liangjc 增加元件管理关联查询 对话框 流水号 分类 ，默认分类初始化 INSERT INTO portal_sys_type_group VALUES ('16', 'Query_TYPE', '元件关联查询', 1, 16, 1, NULL, NULL, NULL, '1', NULL); INSERT INTO portal_sys_type_group VALUES ('17', 'DIALOG_TYPE', '元件对话框', 1, 17, 1, NULL, NULL, NULL, '1', NULL); INSERT INTO portal_sys_type_group VALUES ('18', 'IDENTITY_MANAGER_TYPE', '元件流水号', 1, 18, 1, NULL, NULL, NULL, '1', NULL); INSERT INTO portal_sys_type(ID_, TYPE_GROUP_KEY_, NAME_, TYPE_KEY_, STRU_TYPE_, PARENT_ID_, DEPTH_, PATH_, IS_LEAF_, OWNER_ID_, SN_, CREATE_BY_, CREATE_TIME_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, ICON_, TENANT_ID_) VALUES ('16100', 'Query_TYPE', '默认分类', 'default', '1', '16', 1, '16.16100.', 'Y', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '-1'); INSERT INTO portal_sys_type(ID_, TYPE_GROUP_KEY_, NAME_, TYPE_KEY_, STRU_TYPE_, PARENT_ID_, DEPTH_, PATH_, IS_LEAF_, OWNER_ID_, SN_, CREATE_BY_, CREATE_TIME_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, ICON_, TENANT_ID_) VALUES ('17100', 'DIALOG_TYPE', '默认分类', 'default', '1', '17', 1, '17.17100.', 'Y', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '-1'); INSERT INTO portal_sys_type(ID_, TYPE_GROUP_KEY_, NAME_, TYPE_KEY_, STRU_TYPE_, PARENT_ID_, DEPTH_, PATH_, IS_LEAF_, OWNER_ID_, SN_, CREATE_BY_, CREATE_TIME_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, ICON_, TENANT_ID_) VALUES ('18100', 'IDENTITY_MANAGER_TYPE', '默认分类', 'default', '1', '18', 1, '18.18100.', 'Y', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '-1'); -- 2022-8-31 liangjc 增加元件管理关联查询 对话框 流水号 分类字段 ALTER TABLE form_custom_query ADD TYPE_ID_ NVARCHAR2(64); ALTER TABLE form_custom_query ADD TYPE_NAME_ NVARCHAR2(64); ALTER TABLE form_custom_dialog ADD TYPE_ID_ NVARCHAR2(64); ALTER TABLE form_custom_dialog ADD TYPE_NAME_ NVARCHAR2(64); ALTER TABLE portal_sys_identity ADD TYPE_ID_ NVARCHAR2(64); ALTER TABLE portal_sys_identity ADD TYPE_NAME_ NVARCHAR2(64); comment on column form_custom_query.TYPE_ID_ is '分类id'; comment on column form_custom_query.TYPE_NAME_ is '分类名称'; comment on column form_custom_dialog.TYPE_ID_ is '分类id'; comment on column form_custom_dialog.TYPE_NAME_ is '分类名称'; comment on column portal_sys_identity.TYPE_ID_ is '分类id'; comment on column portal_sys_identity.TYPE_NAME_ is '分类名称'; -- 2022-8-31 liangjc form_url_form增加所属系统编码字段 ALTER TABLE form_url_form ADD SYS_CODE_ NVARCHAR2(50); comment on column form_url_form.SYS_CODE_ is '所属系统编码'; -- 2022-8-31 liangjc form_url_form系统编码字段修复 update form_url_form t1,portal_biz_system t2 set t1.SYS_CODE_ = t2.SYS_CODE_ where t1.SYS_ID_=t2.ID_ and t1.SYS_CODE_ is null; -- 2022-9-1 jjx 添加应用端安全设置页面--执行后需要手动在菜单管理中执行下更新动作 INSERT INTO "PORTAL_SYS_MENU"("ID_", "PARENT_ID_", "NAME_", "ALIAS_", "ACTIVE_TAB_", "MENU_ICON_", "OPENED_", "SN_", "TABS_STYLE_", "PATH_", "HREF_", "TENANT_ID_", "ROUTE_EXTEND_", "TYPE_", "IS_REFRESH_") VALUES ('1584788054295793664', '1441341228585766912', '安全设置', 'security', NULL, NULL, NULL, '4', NULL, '-1.3.1441341228585766912.1584788054295793664.', NULL, '-1', '{"path":"security","component":"@/views/personal/security","redirect":"","alwaysShow":false,"caseSensitive":false,"meta":{"title":"安全设置","isChildren":true,"icon":""}}', 'page', '1'); -- 2022-9-1 liangjc邮件增加smtpHost，sendPort字段 ALTER TABLE uc_tenant_mail_server ADD SMTP_HOST_ NVARCHAR2(128); ALTER TABLE uc_tenant_mail_server ADD SEND_PORT_ NVARCHAR2(20); comment on column uc_tenant_mail_server.SMTP_HOST_ is 'smt主机'; comment on column uc_tenant_mail_server.SEND_PORT_ is '邮件服务器的端口'; --2022-9-2 菜单增加是否刷新字段和国际化temp字段 ALTER TABLE "PORTAL_SYS_MENU" ADD ("IS_REFRESH_" NUMBER); COMMENT ON COLUMN "PORTAL_SYS_MENU"."IS_REFRESH_" IS '是否刷新，默认刷新， 1刷新0不刷新'; --2022-9-2 设置我的任务和流程启动默认不刷新 UPDATE portal_sys_menu SET IS_REFRESH_ = '0' WHERE ALIAS_ in ('myTask','startProcess') -- 2022-9-5 liangjc表单模板增加汇总区域字段 ALTER TABLE FORM_DATA_TEMPLATE ADD SUMMARY_REGION_HTML_ CLOB; COMMENT ON COLUMN FORM_DATA_TEMPLATE.SUMMARY_REGION_HTML_ IS '汇总区域'; -- 2022-9-5 liangjc字段长度调整 ALTER TABLE PORTAL_SYS_POPUP MODIFY NAME_ NVARCHAR2(200); -- 2022-9-6 liangjc字段长度调整 ALTER TABLE BPM_TASK_COMMU MODIFY OPINION_ NVARCHAR2(1500); ALTER TABLE BPM_PRO_CPTO MODIFY OPINION_ NVARCHAR2(1500); -- 2022-9-5 yijj 增加会签数据 entity对象 用户组类型 ALTER TABLE "BPM_TASK_SIGNDATA" ADD ("IDENTITY_TYPE_" NVARCHAR2(64)); COMMENT ON COLUMN "BPM_TASK_SIGNDATA"."IDENTITY_TYPE_" IS '用户组类型'; -- 2022-09-19 yijj 修改菜单别名 UPDATE portal_sys_menu SET ALIAS_ = 'Index' WHERE ID_ ='1441359958220918784'; UPDATE portal_sys_menu SET ALIAS_ = 'NewMatter' WHERE ID_ ='1441342209381814272'; UPDATE portal_sys_menu SET ALIAS_ = 'MyTask' WHERE ID_ ='1441342471236407296'; UPDATE portal_sys_menu SET ALIAS_ = 'Request' WHERE ID_ ='1441342583597617152'; UPDATE portal_sys_menu SET ALIAS_ = 'CirculateMatter' WHERE ID_ ='1443153816786980864'; UPDATE portal_sys_menu SET ALIAS_ = 'Setting' WHERE ID_ ='1441938397294415872'; UPDATE portal_sys_menu SET ALIAS_ = 'Message' WHERE ID_ ='1494487013470261248'; UPDATE portal_sys_menu SET ALIAS_ = 'EmailCenter' WHERE ID_ ='1441938698676129792'; UPDATE portal_sys_menu SET ALIAS_ = 'AppCenter' WHERE ID_ ='1441941148611039232'; -- 2022-9-23 liangjc 表单设计、用户管理、组织管理 tab标签样式与其他模块保持一致 update portal_sys_menu set TABS_STYLE_='' where ID_ in ('3391046','3441027','3441023'); -- jjx 2022-10-08 租户域名 ALTER TABLE UC_TENANT_MANAGE ADD BASE_URL_ NVARCHAR2(128); COMMENT ON COLUMN UC_TENANT_MANAGE.BASE_URL_ IS '租户域名' -- 2022-10-8 liangjc 数据视图 增加汇总区域字段 ALTER TABLE FORM_QUERY_VIEW ADD SUMMARY_REGION_HTML_ CLOB; COMMENT ON COLUMN FORM_QUERY_VIEW.SUMMARY_REGION_HTML_ IS '汇总区域'; -- 2022-10-12 liangjc 修改人员脚本getUserById入参 update bpm_multi_script set ARGUMENT_='[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"ID"}]' where ID_=1338377219818524672; -- jjx 2022-10-08 删除栏目中的不兼容的配置 DELETE FROM PORTAL_SYS_COLUMN WHERE ID = '1267788861858254848'; DELETE FROM PORTAL_SYS_COLUMN WHERE ID = '1267788537730830336' -- jjx 2022-10-19 修改人员脚本内提示信息 UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织ID","paraCt":"base:org-selector","paraCtBindKey":"code"},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"角色别名","paraCt":"base:role-selector","paraCtBindKey":"code"}]' WHERE ID_ = '1338367741324824576'; UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"汇报线类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]' WHERE ID_ = '12'; UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"汇报线类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]' WHERE ID_ = '13'; UPDATE bpm_multi_script SET METHOD_DESC_ = '获取逐级审批(获取主负责人)' WHERE ID_ = '1338375124944031744'; UPDATE bpm_multi_script SET METHOD_DESC_ = 'sql查询用户ID(固定值SQL要用\" \"包裹)' WHERE ID_ = '1338379764150439936'; delete from bpm_multi_script where ID_ = '1338384186821709824'; UPDATE bpm_multi_script SET METHOD_DESC_ = '获取上一节点用户的上级部门（主）的负责人' WHERE ID_ = '16'; UPDATE bpm_multi_script SET METHOD_DESC_ = '获取上一点节用户所在部门的（主）负责人' WHERE ID_ = '18'; UPDATE bpm_multi_script SET METHOD_DESC_ = '通过下属管理获取上一节点用户的上级' WHERE ID_ = '22'; UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg0","paraType":"java.lang.Boolean","paraDesc":"是否主负责人(值为true或false)","paraCt":"null","$$hashKey":"object:2340","multiSelect":false},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"维度编码（不传为默认维度）","multiSelect":false,"paraCt":"base:dem-selector","paraCtBindKey":"code","paraCtBindName":"code"}] ' WHERE ID_ = '16'; UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg0","paraType":"java.lang.Boolean","paraDesc":"是否主负责人(值为true或false)","paraCt":"","$$hashKey":"object:1955","multiSelect":false},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"维度编码（不传为默认维度）","multiSelect":false,"paraCt":"base:dem-selector","paraCtBindKey":"code","paraCtBindName":"code"}]' WHERE ID_ = '17'; UPDATE bpm_multi_script SET ARGUMENT_ = '[{"paraName":"arg0","paraType":"boolean","paraDesc":"是否主负责人(值为true或false)","paraCt":"","$$hashKey":"object:1387","multiSelect":false},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"维度编码（不传为默认维度）","multiSelect":false,"paraCt":"base:dem-selector","paraCtBindKey":"code","paraCtBindName":"code"}]' WHERE ID_ = '18'; UPDATE BPM_MULTI_SCRIPT SET METHOD_DESC_ = '获取上一节点用户的上级部门（主）的负责人(主组织生效）' WHERE ID_ = '16'; -- 2022-10-25 jjx 修改字段长度 alter table BPM_EXE_STACK modify TARGET_NODE_ VARCHAR2(200); alter table BPM_INST_DELETE_LOG modify CONTENT_ VARCHAR2(550); -- 2022-10-26 liangjc修改发布新闻公告和轮播图到栏目默认高度 update PORTAL_NEWS_TREE_ set COL_HEIGHT_=432 where COL_HEIGHT_ in (100,0); -- 2022-10-27 jjx修改菜单名称 UPDATE "PORTAL_SYS_MENU" SET "NAME_" = '工具管理' WHERE "ALIAS_" = 'synergy'; UPDATE "PORTAL_SYS_MENU" SET "NAME_" = '协同办公' WHERE "ALIAS_" = 'portalNewsNoticeManager'; -- 2022-10-28 liangjc portal_news_notice_ 字段FILE_增加长度 alter table portal_news_notice_ rename column FILE_ to name_tmp; alter table portal_news_notice_ add FILE_ CLOB; update portal_news_notice_ set FILE_=trim(name_tmp); alter table portal_news_notice_ drop column name_tmp; -- 2022-10-28 liangjc增加新闻公告已阅待阅表 CREATE TABLE portal_news_notice_read ( ID_ NVARCHAR2(64) NOT NULL, PORTAL_NEWS_NOTICE_ID_ NVARCHAR2(64) NOT NULL, USER_ID_ NVARCHAR2(64), TENANT_ID_ NVARCHAR2(64), PRIMARY KEY (ID_) ); CREATE INDEX IDX_PORTAL_NEWS_NOTICE_ID ON portal_news_notice_read (PORTAL_NEWS_NOTICE_ID_); CREATE INDEX IDX_USER_ID ON portal_news_notice_read (USER_ID_); COMMENT ON COLUMN portal_news_notice_read.PORTAL_NEWS_NOTICE_ID_ IS '新闻id'; COMMENT ON COLUMN portal_news_notice_read.USER_ID_ IS '用户id'; COMMENT ON TABLE portal_news_notice_read IS '新闻阅读记录'; -- 2022-11-1 jjx 栏目 增加分类 INSERT INTO "PORTAL_SYS_TYPE"("ID_", "TYPE_GROUP_KEY_", "NAME_", "TYPE_KEY_", "STRU_TYPE_", "PARENT_ID_", "DEPTH_", "PATH_", "IS_LEAF_", "OWNER_ID_", "SN_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "UPDATE_TIME_", "ICON_", "TENANT_ID_") VALUES ('1565643948817477632', 'INDEX_COLUMN_TYPE', '应用端', 'yyd', '0', '2', '1', '2.1587717367729848320.', 'Y', '0', '0', '1', TO_DATE('2022-10-31 14:56:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, NULL, '-1'); INSERT INTO "PORTAL_SYS_TYPE"("ID_", "TYPE_GROUP_KEY_", "NAME_", "TYPE_KEY_", "STRU_TYPE_", "PARENT_ID_", "DEPTH_", "PATH_", "IS_LEAF_", "OWNER_ID_", "SN_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "UPDATE_TIME_", "ICON_", "TENANT_ID_") VALUES ('1565643975514222592', 'INDEX_COLUMN_TYPE', '管理端', 'gld', '0', '2', '1', '2.1587717401984729088.', 'Y', '0', '0', '1', TO_DATE('2022-10-31 14:56:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, NULL, '-1'); INSERT INTO "PORTAL_SYS_TYPE"("ID_", "TYPE_GROUP_KEY_", "NAME_", "TYPE_KEY_", "STRU_TYPE_", "PARENT_ID_", "DEPTH_", "PATH_", "IS_LEAF_", "OWNER_ID_", "SN_", "CREATE_BY_", "CREATE_TIME_", "CREATE_ORG_ID_", "UPDATE_BY_", "UPDATE_TIME_", "ICON_", "TENANT_ID_") VALUES ('1586975403547820032', 'INDEX_COLUMN_TYPE', '移动端', 'ydd', '0', '2', '1', '2.1587717439670550528.', 'Y', '0', '0', '1', TO_DATE('2022-10-31 14:56:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, NULL, NULL, '-1'); -- 2022-10-26 jason 驳回的节点 alter table BPM_PRO_STATUS add RECYCLE_NODE_ VARCHAR2(2) default '0'; comment on column BPM_PRO_STATUS.RECYCLE_NODE_ is '驳回按流程图执行回收的节点 0默认值 1因为驳回按照流程图执行目标节点之后的节点为1，如果重新运行到该节点变为0'; -- 2022-11-2 jjx 轮播图长度修改 alter table PORTAL_NEWS_NOTICE_ modify ROTATING_DISPLAY_PICTURES_ VARCHAR2(550); -- 2022-11-2 liangjc 增加 发布菜单源与菜单关联关系 CREATE TABLE portal_sys_auth_menu ( ID_ NVARCHAR2(64) NOT NULL, AUTHORIZE_ID_ NVARCHAR2(64), MENU_ID_ NVARCHAR2(64), TENANT_ID_ NVARCHAR2(64), PRIMARY KEY (ID_) ); COMMENT ON COLUMN portal_sys_auth_menu.AUTHORIZE_ID_ IS '发布菜单源主表ID'; COMMENT ON COLUMN portal_sys_auth_menu.MENU_ID_ IS '菜单id'; COMMENT ON TABLE portal_sys_auth_menu IS '发布菜单源与菜单关联关系'; -- 2022-11-6 jjx portal_sys_column别名字段索引改为唯一索引 drop index ALIAS_TENANT_ID__INDEX; CREATE INDEX "ALIAS_TENANT_ID__INDEX" ON "PORTAL_SYS_COLUMN" ("ALIAS", "TENANT_ID_");