/* change.sql 说明 1.数据库相关修改添加到对应的 change.sql 文件，无需调整其他 sql 文件 2.sql 语句验证正确后再提交 */ -- wanghb 2020-12-02 新增短链接管理表 CREATE TABLE "portal_shorturl_manage" ( "ID_" varchar(255) COLLATE "pg_catalog"."default" NOT NULL, "URL_" varchar(2000) COLLATE "pg_catalog"."default" NOT NULL, "url_desc_" varchar(2000) COLLATE "pg_catalog"."default", "SHORT_URL_" varchar(255) COLLATE "pg_catalog"."default", "create_by_" varchar(255) COLLATE "pg_catalog"."default", "create_time_" timestamp(6), "lose_time_" timestamp(6), "create_org_id_" varchar(255) COLLATE "pg_catalog"."default", "update_by_" varchar(255) COLLATE "pg_catalog"."default", "update_time_" timestamp(6), "ENABLED_" varchar(1) COLLATE "pg_catalog"."default", "tenant_id_" varchar(64) COLLATE "pg_catalog"."default" ); 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 "portal_shorturl_manage_pkey" 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 COLUMN FORM_EXPAND_ text 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 COLUMN "reset_temp_" int2; COMMENT ON COLUMN "form_data_template"."reset_temp_" IS '是否需要初始化模板 1: 是 0: 否'; -- JJX 2020-12-23 删除一个初始化的用来显示新闻的配置（分库会报错，去掉不影响使用） delete from form_custom_chart where ID_ = '1330698555119243264'; -- 雷健 2020-12-23 分库部署下执行 删除汇报线选择器的SQL delete from FORM_CUSTOM_DIALOG where ALIAS_='reportLine'; -- 雷健 2020-12-23 分库部署调整汇报线相关的人员脚本 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'); --zjq 2020-12-25 添加水印分配流程表 CREATE TABLE "bpm_watermark_process" ( "id_" varchar(64) COLLATE "pg_catalog"."default" NOT NULL, "proc_def_id_" varchar(64) COLLATE "pg_catalog"."default", "proc_def_key_" varchar(128) COLLATE "pg_catalog"."default", "proc_def_name_" varchar(128) COLLATE "pg_catalog"."default", "watermark_id_" varchar(64) COLLATE "pg_catalog"."default", "tenant_id_" varchar(64) COLLATE "pg_catalog"."default", CONSTRAINT "bpm_watermark_process_pkey" PRIMARY KEY ("id_") ) ; ALTER TABLE "bpm_watermark_process" OWNER TO "postgres"; 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_ VARCHAR(64) NOT NULL, DEF_KEY_ VARCHAR(64) , START_USER_ TEXT , FORM_DATA_ TEXT , TRIGGER_ VARCHAR(512) , TENANT_ID_ VARCHAR(64) , PRIMARY KEY (ID_) ); -- zengst 2020-01-15 修改短链接管理表的列名(双引号会导致通过列名查找时报错) alter table portal_shorturl_manage rename column "ID_" to id_; alter table portal_shorturl_manage rename column "URL_" to url_; alter table portal_shorturl_manage rename column "SHORT_URL_" to short_url_; alter table portal_shorturl_manage rename column "ENABLED_" to enabled_; -- lingpj 2021-01-15 添加催办表遗漏的字段 ALTER TABLE BPM_TASK_REMINDER ADD COLUMN DURATION_SCRIPT_ TEXT DEFAULT NULL; COMMENT ON COLUMN BPM_TASK_REMINDER.DURATION_SCRIPT_ IS '时长脚本'; -- qiuxd 2020-1-16 催办表字段添加 ALTER TABLE UC_HOLIDAY_TIME ADD COLUMN TYPE_ INT; COMMENT ON COLUMN UC_HOLIDAY_TIME.TYPE_ IS '假期类型'; ALTER TABLE BPM_TASK_REMINDER ADD COLUMN DURATION_ INT; COMMENT ON COLUMN BPM_TASK_REMINDER.DURATION_ IS '时长'; ALTER TABLE BPM_TASK_REMINDER ADD COLUMN SEND_DURATION_ INT; COMMENT ON COLUMN BPM_TASK_REMINDER.SEND_DURATION_ IS '发送时长'; ALTER TABLE BPM_TASK_REMINDER ADD COLUMN DATE_TYPE_ VARCHAR(20); COMMENT ON COLUMN BPM_TASK_REMINDER.DATE_TYPE_ IS '日期类型'; ALTER TABLE BPM_TASK_REMINDER ADD COLUMN TYPE_SCRIPT_ VARCHAR(1000); COMMENT ON COLUMN BPM_TASK_REMINDER.TYPE_SCRIPT_ IS '类型脚本'; ALTER TABLE BPM_TASK_REMINDER ADD COLUMN REL_TIME_SCRIPT_ VARCHAR(1000); COMMENT ON COLUMN BPM_TASK_REMINDER.REL_TIME_SCRIPT_ IS '相对时间脚本';