/* change.sql 说明 1.数据库相关修改添加到对应的 change.sql 文件，无需调整其他 sql 文件 2.sql 语句验证正确后再提交 */ -- wanghb 2020-12-02 新增短链接管理表 CREATE TABLE "PORTAL_SHORTURL_MANAGE" ( "ID_" NVARCHAR2(255) NOT NULL, "URL_" NCLOB NOT NULL, "url_desc_" NCLOB, "SHORT_URL_" NVARCHAR2(255), "create_by_" NVARCHAR2(255), "create_time_" DATE, "lose_time_" DATE, "create_org_id_" NVARCHAR2(255), "update_by_" NVARCHAR2(255), "update_time_" DATE, "ENABLED_" NVARCHAR2(1), "tenant_id_" NVARCHAR2(64) ); COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."ID_" IS '主键'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."URL_" IS '原链接'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."url_desc_" IS '原链接描述'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."SHORT_URL_" IS '短链接'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."create_by_" IS '创建人'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."create_time_" IS '创建时间'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."lose_time_" IS '失效时间'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."create_org_id_" IS '创建人组织id'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."update_by_" IS '更新人'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."update_time_" IS '更新时间'; COMMENT ON COLUMN "PORTAL_SHORTURL_MANAGE"."ENABLED_" IS '是否启用.1:启用，2：禁用'; COMMENT ON TABLE "PORTAL_SHORTURL_MANAGE" IS '短连接管理表'; ALTER TABLE "PORTAL_SHORTURL_MANAGE" ADD CONSTRAINT "SYS_C0038309" PRIMARY KEY ("ID_"); -- zjq 2020-12-04 修改流程附件上传配置表的ALIYUN_OSS_ENDPOINT_字段注释 COMMENT ON COLUMN "PORTAL_FLOW_UPLOAD_PROPERTIES"."ALIYUN_OSS_ENDPOINT_" IS '访问域名 endpoint'; -- 赵祥云 2020-12-11 表单历史记录表新增 表单Json数据字段 ALTER TABLE form_history_record ADD ( FORM_EXPAND_ CLOB DEFAULT NULL); COMMENT ON COLUMN form_history_record.FORM_EXPAND_ IS '表单Json数据字段'; -- 曾善铜 2020-12-14 人员脚本、常用脚本添加 insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338367741324824576, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getByCompanyRole', '找这个二级组织底下拥有这个角色的人员', 'java.util.Set', '[{"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"}]', 1, 2, null); insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338375124944031744, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getLeaderStep', '获取逐级审批', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.Integer","paraDesc":"级别"},{"paraName":"arg1","paraType":"boolean","paraDesc":"是否需要上级（true，false）"}]', 1, 2, null); insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338377219818524672, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getUserById', '根据ID获取审批人', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"ID"},{"paraName":"arg1","paraType":"java.util.List","paraDesc":"填写NULL"},{"paraName":"arg2","paraType":"java.lang.String","paraDesc":""}]', 1, 2, null); insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338379764150439936, null, null, 'com.hotent.runtime.script.UserRelScript', 'userRelScript', 'getUserFromBusSql', 'sql查询用户ID', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"查询SQL，返回结果只有一列"}]', 1, 2, null); insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338381406014935040, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getLeaderStepByOrgId', '根据组织ID获取逐级审批', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织ID","paraCt":"base:org-selector","paraCtBindKey":"id"},{"paraName":"arg1","paraType":"java.lang.Integer","paraDesc":"组织级别"}]', 1, 2, null); insert into bpm_multi_script (ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) values (1338384186821709824, null, null, 'com.hotent.runtime.script.UserScript', 'userScript', 'getFgUsers', '获取分管审批人', 'java.util.Set', '[{"paraName":"arg0","paraType":"java.lang.String","paraDesc":"组织Id","paraCt":"base:org-selector","paraCtBindKey":"id"},{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"\\"组织扩展参数别名\\""},{"paraName":"arg2","paraType":"java.lang.String","paraDesc":"\\"用户扩展参数别名\\""}]', 1, 2, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338364268277534720, '判断当前用户是否拥有该角色', 'return scriptImpl.isCurrentInRole('''');', '系统脚本', '参数：角色别名', null, 1, '', null, null, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338334576715632640, '执行sql', 'return scriptImpl.executeSql('''');', '系统脚本', '参数：sql(String)，参数中加入<#id#>，在流程中会替换成表单数据主键。返回值只有一行一列(String)', null, 1, '', 1, null, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338326415497629696, '获取当前用户主岗位编码', 'return scriptImpl.getCurrentMainPostId();', '系统脚本', '无参数', null, 1, '', 1, null, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338326248547553280, '获取当前用户主岗位ID', 'return scriptImpl.getCurrentMainPostId();', '系统脚本', '无参数', null, 1, '', 1, null, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338326076463648768, '获取当前用户主岗位名称', 'return scriptImpl.getCurrentMainPostName();', '系统脚本', '无参数', null, 1, '', 1, null, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338321870394626048, '根据级别获取当前用户组织ID', 'return orgScript.getParamMainOrgId('''');', '系统脚本', 'param:grade(String)', null, 1, '', 1, null, null); insert into bpm_script (ID_, NAME_, SCRIPT_, CATEGORY_, MEMO_, CREATE_TIME_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) values (1338310603730718720, '根据组织ID和参数判断组织是否有该参数', 'return orgScript.isOrgHasParamKey('''','''');', '系统脚本', '组织ID(String)，组织参数(String)', null, 1, '', 1, null, null); -- zjq 2020-12-22 form_data_template表添加RESET_TEMP_字段 ALTER TABLE FORM_DATA_TEMPLATE ADD (RESET_TEMP_ NUMBER); COMMENT ON COLUMN FORM_DATA_TEMPLATE.RESET_TEMP_ IS '是否需要初始化模板 1: 是 0: 否'; -- 雷健 2020-12-23 分库部署下执行 删除汇报线选择器的SQL delete from FORM_CUSTOM_DIALOG where ALIAS_='reportLine'; -- 分库部署调整汇报线相关的人员脚本 delete from BPM_MULTI_SCRIPT where ID_='12' or ID_='13'; INSERT INTO BPM_MULTI_SCRIPT(ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) VALUES ('12', NULL, NULL, 'com.hotent.runtime.script.UserRelScript', 'userRelScript', 'getByRelPreNode', '通过上一节点执行人获取汇报线上级 人员列表', 'java.util.Set', '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]', 1, 2, '-1'); INSERT INTO BPM_MULTI_SCRIPT(ID_, ALIAS_NAME_, ALIAS_DESC_, CLASS_NAME_, CLASS_INS_NAME_, METHOD_NAME_, METHOD_DESC_, RETURN_TYPE_, ARGUMENT_, ENABLE_, TYPE_, TENANT_ID_) VALUES ('13', NULL, NULL, 'com.hotent.runtime.script.UserRelScript', 'userRelScript', 'getByRelStartUser', '通过发起人获取汇报线上级 人员列表', 'java.util.Set', '[{"paraName":"arg1","paraType":"java.lang.String","paraDesc":"类型编码","paraCt":"","paraCtBindName":"ID_","paraCtBindKey":"","multiSelect":false}]', 1, 2, '-1'); -- JJX 2020-12-23 删除一个初始化的用来显示新闻的配置（分库会报错，去掉不影响使用） delete from form_custom_chart where ID_ = 1330698555119243264; --zjq 2020-12-25 添加水印分配流程表 CREATE TABLE "BPM_WATERMARK_PROCESS" ( "ID_" VARCHAR2(64) NOT NULL, "PROC_DEF_ID_" VARCHAR2(64), "PROC_DEF_KEY_" VARCHAR2(128), "PROC_DEF_NAME_" VARCHAR2(128), "WATERMARK_ID_" VARCHAR2(64), "TENANT_ID_" VARCHAR2(64), PRIMARY KEY ("ID_") ); COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."ID_" IS 'ID'; COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."PROC_DEF_ID_" IS '流程ID'; COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."PROC_DEF_KEY_" IS '流程Key'; COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."PROC_DEF_NAME_" IS '流程名称'; COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."WATERMARK_ID_" IS '水印ID'; COMMENT ON COLUMN "BPM_WATERMARK_PROCESS"."TENANT_ID_" IS '租户ID'; COMMENT ON TABLE "BPM_WATERMARK_PROCESS" IS '水印分配流程'; -- jjx 2021-1-13 删除多余的系统帮助配置 delete from PORTAL_SYS_COLUMN where ID = '1325681915189137408' -- 2021-01-15 wanghb 新增丢失的流程自动发起配置表建表sql CREATE TABLE BPM_AUTO_START_CONF ( ID_ VARCHAR2(64) NOT NULL, DEF_KEY_ VARCHAR2(64), START_USER_ CLOB, FORM_DATA_ CLOB, TRIGGER_ VARCHAR2(512), TENANT_ID_ VARCHAR2(64), CONSTRAINT PK_BPM_AUTO_START_CONF PRIMARY KEY (ID_) ); -- zengst 2020-01-15 修改短链接管理表的列名(双引号会导致通过列名查找时报错) alter table PORTAL_SHORTURL_MANAGE rename column "url_desc_" to URL_DESC_ / alter table PORTAL_SHORTURL_MANAGE rename column "create_by_" to CREATE_BY_ / alter table PORTAL_SHORTURL_MANAGE rename column "create_time_" to CREATE_TIME_ / alter table PORTAL_SHORTURL_MANAGE rename column "lose_time_" to LOSE_TIME_ / alter table PORTAL_SHORTURL_MANAGE rename column "create_org_id_" to CREATE_ORG_ID_ / alter table PORTAL_SHORTURL_MANAGE rename column "update_by_" to UPDATE_BY_ / alter table PORTAL_SHORTURL_MANAGE rename column "update_time_" to UPDATE_TIME_ / alter table PORTAL_SHORTURL_MANAGE rename column "tenant_id_" to TENANT_ID_ / -- lingpj 2021-01-15 添加催办表遗漏的字段 ALTER TABLE BPM_TASK_REMINDER ADD ( DURATION_SCRIPT_ CLOB DEFAULT NULL); COMMENT ON COLUMN BPM_TASK_REMINDER.DURATION_SCRIPT_ IS '时长脚本'; -- qiuxd 2020-1-16 催办表字段添加 ALTER TABLE UC_HOLIDAY_TIME ADD TYPE_ INT; COMMENT ON COLUMN UC_HOLIDAY_TIME.TYPE_ IS '假期类型'; ALTER TABLE BPM_TASK_REMINDER ADD DURATION_ INT; COMMENT ON COLUMN BPM_TASK_REMINDER.DURATION_ IS '时长'; ALTER TABLE BPM_TASK_REMINDER ADD SEND_DURATION_ INT; COMMENT ON COLUMN BPM_TASK_REMINDER.SEND_DURATION_ IS '发送时长'; ALTER TABLE BPM_TASK_REMINDER ADD DATE_TYPE_ VARCHAR2(20); COMMENT ON COLUMN BPM_TASK_REMINDER.DATE_TYPE_ IS '日期类型'; ALTER TABLE BPM_TASK_REMINDER ADD TYPE_SCRIPT_ VARCHAR2(1000); COMMENT ON COLUMN BPM_TASK_REMINDER.TYPE_SCRIPT_ IS '类型脚本'; ALTER TABLE BPM_TASK_REMINDER ADD REL_TIME_SCRIPT_ VARCHAR2(1000); COMMENT ON COLUMN BPM_TASK_REMINDER.REL_TIME_SCRIPT_ IS '相对时间脚本'; -- jjx 待办部门信息编辑无响应 update portal_sys_column set data_param = '[{"name":"jsb","type":"string","mode":"0","value":"\"1306170252345872384\""}]' where id = '1311223536270053376'; update portal_sys_column set data_param = '[{"name":"pageBean","type":"string","mode":"0","value":"{\"page\":1,\"pageSize\":7,\"total\":0,\"showTotal\":false}"},{"name":"querys","type":"string","mode":"0","value":"[{\"property\": \"inst.CREATE_ORG_PATH_\", \"value\": \"1306170252345872384\", \"group\": \"orgId\", \"operation\": \"LIKE\"}]"}]' where id = '1311220566576992256';