8.1.0 TO 8.1.1.sql
23.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
/*
change.sql 说明
1.数据库相关修改添加到对应的 change.sql 文件,无需调整其他 sql 文件
2.sql 语句验证正确后再提交
*/
-- 20221206 jjx 同步mysql脚本
ALTER TABLE PORTAL_SYS_COLUMN ADD STYLE_ CLOB;
COMMENT ON COLUMN PORTAL_SYS_COLUMN.STYLE_ IS 'CSS样式';
ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_TYPE_ INT DEFAULT 0;
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_TYPE_ IS '票据入参方式,0-头部参数;1-url参数';
ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_KEY_ NVARCHAR2(128);
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_KEY_ IS '票据入参KEY';
ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_EXPIRE_ INT DEFAULT 3600;
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_EXPIRE_ IS '票据过期时间,单位秒';
ALTER TABLE PORTAL_BIZ_SYSTEM ADD TOKEN_API_INFO CLOB;
COMMENT ON COLUMN PORTAL_BIZ_SYSTEM.TOKEN_API_INFO IS '认证接口信息Json';
ALTER TABLE PORTAL_INTERFACE_MANAGER ADD SOURCE_TYPE_ INT DEFAULT 0;
COMMENT ON COLUMN PORTAL_INTERFACE_MANAGER.SOURCE_TYPE_ IS '接口来源,0-连接;1-服务';
ALTER TABLE PORTAL_CONNECTION_MANAGER ADD INIT_FLAG_ INT DEFAULT 0;
COMMENT ON COLUMN PORTAL_CONNECTION_MANAGER.INIT_FLAG_ IS '是否初始化数据,0或null-否;1-是';
ALTER TABLE FORM_CUSTOM_QUERY ADD API_ALIAS_ NVARCHAR2(64);
COMMENT ON COLUMN FORM_CUSTOM_QUERY.API_ALIAS_ IS '集成管理接口别名';
ALTER TABLE FORM_CUSTOM_DIALOG ADD API_ALIAS_ NVARCHAR2(64);
COMMENT ON COLUMN FORM_CUSTOM_DIALOG.API_ALIAS_ IS '集成管理接口别名';
-- 2022-12-13 JJX BPM_TASK表中添加字段
ALTER TABLE BPM_TASK ADD TARGET_NODE_ NVARCHAR2(255);
COMMENT ON COLUMN BPM_TASK.TARGET_NODE_ IS '回到本节点';
-- 2022-12-14 liangjc 迁移流程推演-增加表
create table BPM_NODE_SETTING
(
ID_ NVARCHAR2(255) not null primary key,
CREATE_BY_ NVARCHAR2(64) default NULL,
CREATE_TIME_ DATE,
CREATE_ORG_ID_ NVARCHAR2(64) default NULL,
UPDATE_BY_ NVARCHAR2(64) default NULL,
UPDATE_TIME_ DATE default NULL,
TENANT_ID_ NVARCHAR2(64) default NULL,
NODE_ID_ NVARCHAR2(64) default NULL,
DEF_ID NVARCHAR2(64) default NULL,
FORECAST_ NUMBER default NULL
);
comment on table BPM_NODE_SETTING is '流程定义节点设置表';
comment on column BPM_NODE_SETTING.ID_ is '主键';
comment on column BPM_NODE_SETTING.CREATE_BY_ is '创建人ID';
comment on column BPM_NODE_SETTING.CREATE_TIME_ is '创建时间';
comment on column BPM_NODE_SETTING.CREATE_ORG_ID_ is '创建者所属组织ID';
comment on column BPM_NODE_SETTING.UPDATE_BY_ is '更新人ID';
comment on column BPM_NODE_SETTING.UPDATE_TIME_ is '更新时间';
comment on column BPM_NODE_SETTING.TENANT_ID_ is '租户ID';
comment on column BPM_NODE_SETTING.NODE_ID_ is '节点id';
comment on column BPM_NODE_SETTING.DEF_ID is '流程定义id';
comment on column BPM_NODE_SETTING.FORECAST_ is '审批预测值(1为同意,2为反对,默认为1)';
-- 2022-12-14 liangjc 审批历史表增加字段
alter table BPM_CHECK_OPINION add VARS_ CLOB;
comment on column BPM_CHECK_OPINION.VARS_ is '流程变量';
-- url菜单增加配置认证接口 update by ouyanggaolong20221223
alter table PORTAL_SYS_MENU add TOKEN_ENABLED_ int default 0;
comment on column PORTAL_SYS_MENU.TOKEN_ENABLED_ is '是否追加token,0-否;1-是';
alter table PORTAL_SYS_MENU add TOKEN_TYPE_ int default 0;
comment on column PORTAL_SYS_MENU.TOKEN_TYPE_ is '获取模式,0-当前用户;1-接口获取';
alter table PORTAL_SYS_MENU add TOKEN_KEY_ NVARCHAR2(128);
comment on column PORTAL_SYS_MENU.TOKEN_KEY_ is '票据入参KEY';
alter table PORTAL_SYS_MENU add TOKEN_API_ CLOB;
comment on column PORTAL_SYS_MENU.TOKEN_API_ is '认证接口信息Json,base64编码';
-- 2022-11-30 liangjc 用户自定义待办事项表增加收藏列表排序 和 租户id字段
alter table PORTAL_CUSTOM_TODO add MARK_SN_ int;
comment on column PORTAL_CUSTOM_TODO.MARK_SN_ is '收藏列表排序字段';
alter table PORTAL_CUSTOM_TODO add TENANT_ID_ NVARCHAR2(64);
comment on column PORTAL_CUSTOM_TODO.TENANT_ID_ is '租户ID';
-- 2022-12-29 修改任务名称长度
ALTER TABLE BPM_TASK MODIFY NAME_ NVARCHAR2(100);
-- 2023-1-3 jjx 迁移待办中心
alter table BPM_CHECK_OPINION add QUALFIEDS_ACCOUNT_ clob;
comment on column BPM_CHECK_OPINION.QUALFIEDS_ACCOUNT_ is '有审批资格用户帐号串';
alter table BPM_CHECK_OPINION add AUDITOR_ACCOUNT_ NVARCHAR2(255);
comment on column BPM_CHECK_OPINION.AUDITOR_ACCOUNT_ is '实际审判人';
create table BPM_FLOW_MQ_LOG
(
ID_ NVARCHAR2(64) not null primary key,
TYPE_ NVARCHAR2(40) not null,
TITLE_ NVARCHAR2(300),
CONTENT_ CLOB,
STATUS_ INT not null,
TRY_TIME_ INT,
EXCEPTION_ NVARCHAR2(2000),
LOG_TIME_ DATE,
TARGET_ NVARCHAR2(64),
PROC_INST_ID_ NVARCHAR2(64),
TASK_ID_ NVARCHAR2(64),
OWNER_ID_ NVARCHAR2(64),
OWNER_NAME_ NVARCHAR2(64),
TENANT_ID_ NVARCHAR2(64),
YEARS_PARTITION_ INT
);
comment on column BPM_FLOW_MQ_LOG.ID_ is '主键';
comment on column BPM_FLOW_MQ_LOG.TYPE_ is '类型';
comment on column BPM_FLOW_MQ_LOG.TITLE_ is '标题';
comment on column BPM_FLOW_MQ_LOG.CONTENT_ is '内容';
comment on column BPM_FLOW_MQ_LOG.STATUS_ is '状态';
comment on column BPM_FLOW_MQ_LOG.TRY_TIME_ is '重试次数';
comment on column BPM_FLOW_MQ_LOG.EXCEPTION_ is '异常信息';
comment on column BPM_FLOW_MQ_LOG.LOG_TIME_ is '日志记录时间';
comment on column BPM_FLOW_MQ_LOG.TARGET_ is '日志目标类型';
comment on column BPM_FLOW_MQ_LOG.PROC_INST_ID_ is '实例ID';
comment on column BPM_FLOW_MQ_LOG.TASK_ID_ is '任务ID';
comment on column BPM_FLOW_MQ_LOG.OWNER_ID_ is '任务所属人ID';
comment on column BPM_FLOW_MQ_LOG.OWNER_NAME_ is '任务所属人姓名';
comment on column BPM_FLOW_MQ_LOG.TENANT_ID_ is '租户ID';
CREATE INDEX IDX_YEARS_PARTITION_ ON BPM_FLOW_MQ_LOG (YEARS_PARTITION_);
CREATE INDEX IDX_PROC_INST_ID_ ON BPM_FLOW_MQ_LOG (PROC_INST_ID_);
CREATE INDEX idx_bpm_flow_message_type ON BPM_FLOW_MQ_LOG (TYPE_);
-- 2023-1-3 jjx 更新移动端待办列表配置
UPDATE PORTAL_SYS_COLUMN SET "TEMPLATE_HTML" = 'PGRpdj4KICA8ZGl2ICBjbGFzcz0idG9kby1oZWFkZXIiIHN0eWxlPSJkaXNwbGF5OmZsZXg7anVzdGlmeS1jb250ZW50OiBzcGFjZS1iZXR3ZWVuO2hlaWdodDogMS4wNjY3cmVtO2FsaWduLWl0ZW1zOiBjZW50ZXI7cGFkZGluZzowIDAuMzJyZW07Ij4KICAgIDxodC1oNCBpbmxpbmU+5oiR55qE5b6F5YqePC9odC1oND4KICAgIDxzcGFuICBjbGFzcz0iaGVhZGVyLXJpZ2h0X19idG4iICBAY2xpY2s9IiRyb3V0ZXIucHVzaCgnL3dvcmsvbXlNYXR0ZXJzJykiPgogICAgICDmm7TlpJo8aSBjbGFzcz0iZWwtaWNvbi1hcnJvdy1yaWdodCI+PC9pPgogICAgPC9zcGFuPgogIDwvZGl2PgogIDx1bCBjbGFzcz0idG9kby1jb250ZW50X191bCIgc3R5bGU9InBhZGRpbmc6MCAwLjMycmVtOyI+CiAgICA8bGkgY2xhc3M9InRvZG8tY29udGVudF9faXRlbSIgc3R5bGU9ImRpc3BsYXk6ZmxleDtmbGV4LWRpcmVjdGlvbjogY29sdW1uOyIgdi1mb3I9IihpdGVtLGluZGV4KSBpbiBjdXJyZW50U2hvd0RhdGEiIDprZXk9Iml0ZW0uaWQiPgogICAgICA8cCBzdHlsZT0iY29sb3I6IzIyMjIyMjtmb250LXNpemU6MC40cmVtO2ZvbnQtd2VpZ2h0OiBib2xkO21hcmdpbjowLjIxMzNyZW0gMDt3aGl0ZS1zcGFjZTogbm93cmFwO292ZXJmbG93OiBoaWRkZW47dGV4dC1vdmVyZmxvdzogZWxsaXBzaXM7IiBAY2xpY2s9ImdvVG9EZXRhaWwoaXRlbSkiPnt7aXRlbS5zdWJqZWN0fX08L3A+CiAgICAgIDxkaXYgY2xhc3M9ImNvbnRlbnQtaXRlbV9fYm90dG9tIiBzdHlsZT0iZGlzcGxheTpmbGV4O2p1c3RpZnktY29udGVudDogc3BhY2UtYmV0d2Vlbjtib3JkZXItYm90dG9tOjFweCBzb2xpZCAjRUVFRUVFO3BhZGRpbmctYm90dG9tOjAuMzJyZW0iIDpzdHlsZT0iaW5kZXggPT09IDQgPyAnYm9yZGVyLWJvdHRvbTpub25lJzonJyI+CiAgICAgICAgPGRpdiBjbGFzcz0iY29udGVudC1pdGVtX19ib3R0b20tbGVmdCI+CiAgICAgICAgICA8c3BhbiBzdHlsZT0iY29sb3I6Izk5OTtmb250LXNpemU6MC4zMnJlbSI+PGkgY2xhc3M9Imljb24teW9uZ2h1MSIgc3R5bGU9ImZvbnQtc2l6ZTowLjM3MzNyZW07cGFkZGluZy1yaWdodDowLjA1MzNyZW0iPjwvaT57e2l0ZW0uY3JlYXRvcn19PC9zcGFuPgogICAgICAgICAgPHNwYW4gc3R5bGU9ImNvbG9yOiM5OTk7Zm9udC1zaXplOjAuMzJyZW07cGFkZGluZy1sZWZ0OjAuNTMzM3JlbSI+PGkgY2xhc3M9Imljb24tdGltZS1jaXJjbGUiIHN0eWxlPSJmb250LXNpemU6MC4zNzMzcmVtO3BhZGRpbmctcmlnaHQ6MC4wNTMzcmVtIj48L2k+e3tmb3JtYXRlRGF0ZShpdGVtLmNyZWF0ZVRpbWUpfX08L3NwYW4+CiAgICAgICAgPC9kaXY+CiAgICAgICAgPGRpdiBzdHlsZT0iY29sb3I6IzQwOUVGRjtmb250LXNpemU6MC4zMnJlbSI+e3tmaWx0ZXJEYXRlKGl0ZW0uY3JlYXRlVGltZSl9fTwvZGl2PgogICAgICA8L2Rpdj4KICAgIDwvbGk+CiAgPC91bD4KPC9kaXY+'
WHERE ALIAS = 'wddbmobile';
UPDATE PORTAL_SYS_COLUMN SET
"TEMPLATE_JAVA_SCRIPT_" = 'ewogIGNvbXB1dGVkOnsKICAgIGN1cnJlbnRTaG93RGF0YSgpewogICAgICBsZXQgZGF0YSA9IFtdCiAgICAgIGlmKHRoaXMuZGF0YSYmdGhpcy5kYXRhLnJvd3MmJnRoaXMuZGF0YS5yb3dzLmxlbmd0aCl7CiAgICAgICAgZGF0YSA9IHRoaXMuZGF0YS5yb3dzLnNwbGljZSgwLDUpCiAgICAgIH0KICAgICAgcmV0dXJuIGRhdGEKICAgIH0sCiAgIAogIH0sCiAgICBtb3VudGVkKCl7CiAgICAgCiAgICB9LAogICAgIG1ldGhvZHM6IHsKICAgICAgIGdvVG9EZXRhaWwocm93KXsKICAgICAgICAgaWYgKHJvdy5zdGF0dXMgPT0gJ1NIQVJFJyB8fCByb3cuc3RhdHVzID09ICdCQUNLU0hBUkUnKSB7CiAgICAgICAgICAgIGlmIChyb3cuaWRlbnRpdHlMaXN0ICYmIHJvdy5pZGVudGl0eUxpc3QubGVuZ3RoID09IDEpIHsKICAgICAgICAgICAgICB0aGlzLiRyb3V0ZXIucHVzaCgKICAgICAgICAgICAgICAgICcvdGFzay8nICsgcm93LmlkICsgJy8nICsgcm93LmlkZW50aXR5TGlzdFswXS5pZAogICAgICAgICAgICAgICkKICAgICAgICAgICAgfSBlbHNlIHsKICAgICAgICAgICAgICBsZXQgaHRtbCA9CiAgICAgICAgICAgICAgICAnPHNlbGVjdCByZWY9JyArCiAgICAgICAgICAgICAgICBNYXRoLnJhbmRvbSgpICsKICAgICAgICAgICAgICAgICcgaWQ9ImFwcHJvdmFsTGVhZGVyU2VsZWN0IiBzdHlsZT0id2lkdGg6IDIwMHB4O2hlaWdodDogMzBweDtib3JkZXItcmFkaXVzOiA0cHg7bWFyZ2luOiAyMHB4IDcwcHg7IiA+PG9wdGlvbiB2YWx1ZT0iIj7or7fpgInmi6k8L29wdGlvbj4nCiAgICAgICAgICAgICAgcm93LmlkZW50aXR5TGlzdC5mb3JFYWNoKChpZGVudGl0eSkgPT4gewogICAgICAgICAgICAgICAgaHRtbCArPQogICAgICAgICAgICAgICAgICAnPG9wdGlvbiB2YWx1ZT0iJyArCiAgICAgICAgICAgICAgICAgIGlkZW50aXR5LmlkICsKICAgICAgICAgICAgICAgICAgJyI+JyArCiAgICAgICAgICAgICAgICAgIGlkZW50aXR5Lm5hbWUgKwogICAgICAgICAgICAgICAgICAnPC9vcHRpb24+JwogICAgICAgICAgICAgIH0pCiAgICAgICAgICAgICAgaHRtbCArPSAnPC9zZWxlY3Q+JwogICAgICAgICAgICAgIGxldCB0aGlzXyA9IHRoaXMKICAgICAgICAgICAgICB0aGlzLiRhbGVydChodG1sLCAn6K+36YCJ5oup5LiA5Liq6aKG5a+85Luj5Li65a6h5om5JywgewogICAgICAgICAgICAgICAgZGFuZ2Vyb3VzbHlVc2VIVE1MU3RyaW5nOiB0cnVlLAogICAgICAgICAgICAgICAgYmVmb3JlQ2xvc2U6IGZ1bmN0aW9uIChhY3Rpb24sIGluc3RhbmNlLCBkb25lKSB7CiAgICAgICAgICAgICAgICAgIGlmIChhY3Rpb24gPT0gJ2NvbmZpcm0nKSB7CiAgICAgICAgICAgICAgICAgICAgbGV0IHNlID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQoJ2FwcHJvdmFsTGVhZGVyU2VsZWN0JykKICAgICAgICAgICAgICAgICAgICBpZiAoc2Uuc2VsZWN0ZWRJbmRleCA9PSAwKSB7CiAgICAgICAgICAgICAgICAgICAgICBOb3RpZnkoewogICAgICAgICAgICAgICAgICAgICAgICB0eXBlOiAnd2FybmluZycsCiAgICAgICAgICAgICAgICAgICAgICAgIG1lc3NhZ2U6ICfor7fpgInmi6nku6PkuLrlrqHmibnnmoTpooblr7wnLAogICAgICAgICAgICAgICAgICAgICAgfSkKICAgICAgICAgICAgICAgICAgICB9IGVsc2UgewogICAgICAgICAgICAgICAgICAgICAgaW5zdGFuY2UuY2xvc2UoKQogICAgICAgICAgICAgICAgICAgICAgdGhpc18uJHJvdXRlci5wdXNoKAogICAgICAgICAgICAgICAgICAgICAgICAnL3Rhc2svJyArIHJvdy5pZCArICcvJyArIHNlW3NlLnNlbGVjdGVkSW5kZXhdLnZhbHVlCiAgICAgICAgICAgICAgICAgICAgICApCiAgICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgICB9IGVsc2UgewogICAgICAgICAgICAgICAgICAgIGluc3RhbmN' WHERE ALIAS = 'wddbmobile';
UPDATE PORTAL_SYS_COLUMN SET
"TEMPLATE_JAVA_SCRIPT_" = TEMPLATE_JAVA_SCRIPT_ ||'lLmNsb3NlKCkKICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICB9KQogICAgICAgICAgICB9CiAgICAgICAgICB9IGVsc2UgaWYgKAogICAgICAgICAgICByb3cuc3RhdHVzID09ICdBR0VOVCcgJiYKICAgICAgICAgICAgcm93Lm93bmVySWQgIT0gcm93LmFzc2lnbmVlSWQgJiYKICAgICAgICAgICAgcm93Lm93bmVySWQgPT0gdGhpcy4kc3RvcmUuc3RhdGUubG9naW4uY3VycmVudFVzZXIudXNlcklkCiAgICAgICAgICApIHsKICAgICAgICAgICAgLy/lp5TmiZjku7vliqEKICAgICAgICAgICAgdGhpcy4kcm91dGVyLnB1c2goJy9pbnN0UmVhZC8nICsgcm93LnByb2NJbnN0SWQgKyAnL2FnZW50JykKICAgICAgICAgIH0gZWxzZSB7CiAgICAgICAgICAgIHRoaXMuJHJvdXRlci5wdXNoKCcvdGFzay8nICsgcm93LmlkICsgJy8wJykKICAgICAgICAgIH0KICAgICAgIH0sCiAgICAgIGZpbHRlckRhdGUoZGF0ZSkgewogICAgICAgIHZhciBjdXJyZW50RGF0ZSA9IG5ldyBEYXRlKCkuZ2V0VGltZSgpCiAgICAgICAgZGF0ZSA9IG5ldyBEYXRlKGRhdGUucmVwbGFjZSgvLS9nLCAnLycpKS5nZXRUaW1lKCkKICAgICAgICBpZiAoZGF0ZSAhPSB1bmRlZmluZWQpIHsKICAgICAgICAgIHZhciB0b3RhbCA9IChjdXJyZW50RGF0ZSAtIGRhdGUpIC8gMTAwMAogICAgICAgICAgdmFyIGRheSA9IHBhcnNlSW50KHRvdGFsIC8gKDI0ICogNjAgKiA2MCkpIC8v6K6h566X5pW05pWw5aSp5pWwCiAgICAgICAgICB2YXIgYWZ0ZXJEYXkgPSB0b3RhbCAtIGRheSAqIDI0ICogNjAgKiA2MCAvL+WPluW+l+eul+WHuuWkqeaVsOWQjuWJqeS9meeahOenkuaVsAogICAgICAgICAgdmFyIGhvdXIgPSBwYXJzZUludChhZnRlckRheSAvICg2MCAqIDYwKSkgLy/orqHnrpfmlbTmlbDlsI/ml7bmlbAKICAgICAgICAgIHZhciBhZnRlckhvdXIgPSB0b3RhbCAtIGRheSAqIDI0ICogNjAgKiA2MCAtIGhvdXIgKiA2MCAqIDYwIC8v5Y+W5b6X566X5Ye65bCP5pe25pWw5ZCO5Ymp5L2Z55qE56eS5pWwCiAgICAgICAgICB2YXIgbWluID0gcGFyc2VJbnQoYWZ0ZXJIb3VyIC8gNjApIC8v6K6h566X5pW05pWw5YiGCiAgICAgICAgICB2YXIgYWZ0ZXJNaW4gPSBwYXJzZUludCgKICAgICAgICAgICAgdG90YWwgLSBkYXkgKiAyNCAqIDYwICogNjAgLSBob3VyICogNjAgKiA2MCAtIG1pbiAqIDYwIC8v5Y+W5b6X566X5Ye65YiG5ZCO5Ymp5L2Z55qE56eS5pWwCiAgICAgICAgICApCiAgICAgICAgICB2YXIgdmFsID0gJycKICAgICAgICAgIGlmIChkYXkgIT0gMCkgewogICAgICAgICAgICByZXR1cm4gKHZhbCArPSBkYXkgKyAnZCcpCiAgICAgICAgICB9IGVsc2UgaWYgKGhvdXIgIT0gMCkgewogICAgICAgICAgICByZXR1cm4gKHZhbCArPSBob3VyICsgJ2gnKQogICAgICAgICAgfSBlbHNlIGlmIChtaW4gIT0gMCkgewogICAgICAgICAgICByZXR1cm4gKHZhbCArPSBtaW4gKyAnbScpCiAgICAgICAgICB9IGVsc2UgaWYgKGFmdGVyTWluICE9IDApIHsKICAgICAgICAgICAgcmV0dXJuICh2YWwgKz0gYWZ0ZXJNaW4gKyAncycpCiAgICAgICAgICB9CiAgICAgICAgfQogICAgICB9LAogICAgfSwKICAgIAp9' WHERE ALIAS = 'wddbmobile';
-- 2023-1-3 jjx 增加diy组件表
CREATE TABLE FORM_CUSTOM_COMPONENT (
"ID_" NVARCHAR2(255),
"NAME_" NVARCHAR2(255),
"ALIAS_" NVARCHAR2(255),
"TENANT_ID_" NVARCHAR2(255),
"CREATE_BY_" NVARCHAR2(255),
"UPDATE_BY_" NVARCHAR2(255),
"CREATE_ORG_ID_" NVARCHAR2(255),
"CREATE_TIME_" DATE,
"UPDATE_TIME_" DATE,
"TEMPLATE_" CLOB,
"JS_CODE_" CLOB,
"DATA_CODE_" CLOB,
PRIMARY KEY ("ID_")
);
-- 2023-1-3 jjx 增加diy组件菜单
INSERT INTO PORTAL_SYS_MENU (ID_, PARENT_ID_, NAME_, ALIAS_, ACTIVE_TAB_, MENU_ICON_, OPENED_, SN_, TABS_STYLE_, PATH_, HREF_, TENANT_ID_) VALUES ('1550294793437974528', '3631013', 'DIY组件', 'formCustomComponentManager', NULL, NULL, NULL, '1', NULL, '-1.1.3441002.3631013.1550294793437974528.', NULL, '-1');
-- 2023-1-3 jjx 添加UDS同步记录表
CREATE TABLE "UC_BATCH_SYNC" (
"ID_" NVARCHAR2(64) NOT NULL,
"EXECUTE_TIME_" DATE,
"DATA_IDS_" CLOB,
"STATUS_" INT DEFAULT 0,
"CREATE_TIME_" DATE,
"UPDATE_TIME_" DATE,
"CREATE_BY_" NVARCHAR2(64),
"CREATE_ORG_ID_" NVARCHAR2(64),
"UPDATE_BY_" NVARCHAR2(64),
PRIMARY KEY ("ID_")
);
COMMENT ON COLUMN UC_BATCH_SYNC.EXECUTE_TIME_ IS '当前时间';
COMMENT ON COLUMN UC_BATCH_SYNC.DATA_IDS_ IS '同步数据ID,json格式;调用函数名和入参。';
COMMENT ON COLUMN UC_BATCH_SYNC.STATUS_ IS '状态,0-正在同步;1-已提交;2-已回滚';
COMMENT ON TABLE UC_BATCH_SYNC IS 'UDS同步记录表';
DROP INDEX IDX_JOB_CODE_UNQ;
CREATE UNIQUE INDEX IDX_JOB_CODE_UNQ ON UC_ORG_JOB ("CODE_","TENANT_ID_","IS_DELE_");
DROP INDEX IDX_JOB_CODE_UNQ;
CREATE UNIQUE INDEX IDX_JOB_CODE_UNQ ON UC_ORG_JOB ("CODE_","TENANT_ID_","IS_DELE_");
-- 2023年1月5日 jjx 门户管理表新增管理员id、管理员名称字段
alter table PORTAL_SYS_LAYOUT_MANAGE add MANAGER_ID_ NVARCHAR2(32);
comment on column PORTAL_SYS_LAYOUT_MANAGE.MANAGER_ID_ is '管理员ID';
alter table PORTAL_SYS_LAYOUT_MANAGE add MANAGER_NAME_ NVARCHAR2(32);
comment on column PORTAL_SYS_LAYOUT_MANAGE.MANAGER_NAME_ is '管理员名称';
-- 2023年1月9日 jjx 增加扩展jar包类型
ALTER TABLE BPM_EXTEND_JAR ADD TYPE_ INT DEFAULT 0;
COMMENT ON COLUMN BPM_EXTEND_JAR.TYPE_ IS 'jar包类型。0:script脚本扩展,1:用户数据同步';
DROP INDEX IDX_ROLE_CODE;
CREATE UNIQUE INDEX IDX_ROLE_CODE ON UC_ROLE ("CODE_","TENANT_ID_","IS_DELE_");
-- uc_org、uc_org_post和uc_role唯一索引增加删除标志 update by 欧阳高龙20230111
DROP INDEX IDX_ORG_CODE_UNQ;
CREATE UNIQUE INDEX IDX_ORG_CODE_UNQ ON UC_ORG ("CODE_","TENANT_ID_","IS_DELE_");
DROP INDEX IDX_POST_CODE_UNQ;
CREATE UNIQUE INDEX IDX_POST_CODE_UNQ ON UC_ORG_POST ("CODE_","TENANT_ID_","IS_DELE_");
DROP INDEX IDX_ROLE_CODE;
CREATE UNIQUE INDEX IDX_ROLE_CODE ON UC_ROLE ("CODE_","TENANT_ID_","IS_DELE_");
-- 增加用户数据同步定时任务 update by 欧阳高龙20230111
insert into qrtz_job_details VALUES(
'quartzScheduler', '用户数据同步', '-1', '用户数据同步定时任务,结合外部扩展jar包上传', 'com.hotent.job.job.UserSyncJob', '1', '1', '0', '0',null);
-- 同步mysql脚本 20230201 jjx
ALTER TABLE UC_PARAMS MODIFY ("JSON_" NVARCHAR2(1700));
CREATE TABLE UC_MATRIX_AUTH (
ID_ NVARCHAR2(64) NOT NULL,
MATRIX_ID_ NVARCHAR2(64),
USER_ID_ NVARCHAR2(64),
CREATE_TIME_ DATE,
CREATE_BY_ NVARCHAR2(64),
UPDATE_TIME_ DATE,
UPDATE_BY_ NVARCHAR2(64),
PRIMARY KEY (ID_)
);
CREATE INDEX IDX_MATRIX_AUTH_USERID ON UC_MATRIX_AUTH (USER_ID_);
COMMENT ON COLUMN UC_MATRIX_AUTH.MATRIX_ID_ IS '关系矩阵ID';
COMMENT ON COLUMN UC_MATRIX_AUTH.USER_ID_ IS '管理员ID';
COMMENT ON TABLE UC_MATRIX_AUTH IS '关系矩阵管理员';
ALTER TABLE BPM_FORM_MODIFY_RECORD ADD BO_ALIAS_ NVARCHAR2(64);
COMMENT ON COLUMN BPM_FORM_MODIFY_RECORD.BO_ALIAS_ IS 'bo别名';
-- 2023-02-03 huangtp 新增门户管理授权表
create table PORTAL_SYS_LAYOUT_AUTH
(
ID_ NVARCHAR2(64) not null constraint PORTAL_SYS_LAYOUT_AUTH_PK
primary key,
LAYOUT_ID_ NVARCHAR2(64),
USER_ID_ NVARCHAR2(64),
CREATE_TIME_ DATE,
CREATE_BY_ NVARCHAR2(64),
UPDATE_TIME_ DATE,
UPDATE_BY_ NVARCHAR2(64),
TENANT_ID_ NVARCHAR2(32) default '-1'
);
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.LAYOUT_ID_ IS '门户ID';
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.USER_ID_ IS '管理员ID';
COMMENT ON TABLE PORTAL_SYS_LAYOUT_AUTH IS '门户管理授权表';
-- 2023-02-04 huangtp 新增门户管理授权表用户名称、用户账号字段
ALTER TABLE PORTAL_SYS_LAYOUT_AUTH ADD user_name_ NVARCHAR2(64);
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.user_name_ IS '用户名称';
ALTER TABLE PORTAL_SYS_LAYOUT_AUTH ADD account_ NVARCHAR2(64);
COMMENT ON COLUMN PORTAL_SYS_LAYOUT_AUTH.account_ IS '用户账号';
-- 2023-2-6 liangjc 门户,新闻公告,栏目初始化栏目的权限为所有人
INSERT INTO portal_sys_auth_user ( ID_, AUTHORIZE_ID_, RIGHT_TYPE_, OBJ_TYPE_, TENANT_ID_ )
SELECT
id,
id,
'everyone',
'layoutManage',
TENANT_ID_
FROM
portal_sys_layout_manage
WHERE
ID NOT IN ( SELECT AUTHORIZE_ID_ FROM portal_sys_auth_user )
AND ID NOT IN ( SELECT id_ FROM portal_sys_auth_user );
INSERT INTO portal_sys_auth_user ( ID_, AUTHORIZE_ID_, RIGHT_TYPE_, OBJ_TYPE_, TENANT_ID_ )
SELECT
id_,
id_,
'everyone',
'newsNotice',
TENANT_ID_
FROM
PORTAL_NEWS_NOTICE_
WHERE
ID_ NOT IN ( SELECT AUTHORIZE_ID_ FROM portal_sys_auth_user )
AND ID_ NOT IN ( SELECT id_ FROM portal_sys_auth_user );
INSERT INTO portal_sys_auth_user ( ID_, AUTHORIZE_ID_, RIGHT_TYPE_, OBJ_TYPE_, TENANT_ID_ )
SELECT
ID,
ID,
'everyone',
'indexColumn',
TENANT_ID_
FROM
portal_sys_column
WHERE
ID NOT IN ( SELECT AUTHORIZE_ID_ FROM portal_sys_auth_user )
AND ID NOT IN ( SELECT id_ FROM portal_sys_auth_user );
ALTER TABLE portal_meeting_config ADD VERSION_ INT DEFAULT 1;
comment on column portal_meeting_config.VERSION_ is '版本号';
-- 2023-02-13 huangtp 关系矩阵授权表加上用户账号
ALTER TABLE UC_MATRIX_AUTH ADD ACCOUNT_ NVARCHAR2(64) DEFAULT '' NOT NULL;
comment on column UC_MATRIX_AUTH.ACCOUNT_ is '用户账号';
-- 2023-02-15 jjx 同步mysql脚本
UPDATE portal_sys_menu SET ACTIVE_TAB_='bizSystemManager' where ID_='1437738312683724800';
UPDATE portal_sys_menu SET PARENT_ID_='1437738312683724800',
PATH_='-1.1.1437982148582154240.1437738312683724800.1451456425861406720.',
SN_=2,
TYPE_='catalog'
where ID_='1451456425861406720';
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_, token_Enabled_, TOKEN_TYPE_, TOKEN_KEY_, TOKEN_API_)
VALUES ('1625684930068721664', '1437738312683724800', '外部系统管理', 'bizSystemManager', '', '', NULL, 1, '', '-1.1.1437982148582154240.1437738312683724800.1625684930068721664.', '', '-1', '', 'catalog', 1, 0, 0, NULL, NULL);
ALTER TABLE PORTAL_SYS_COLUMN ADD IS_INIT_ SMALLINT;
COMMENT ON COLUMN PORTAL_SYS_COLUMN.IS_INIT_ IS '是否初始化栏目(新增租户给租户初始化)';
update PORTAL_SYS_COLUMN set IS_INIT_=1 where TENANT_ID_ = -1;
-- 2023-02-16 liangjc oracle缺失催办模板脚本补充
INSERT INTO PORTAL_SYS_MSG_TPL (ID_,NAME_,KEY_,TYPE_KEY_,IS_DEFAULT_,SUBJECT_,PLAIN_,HTML_,CREATE_BY_,CREATE_TIME_,CREATE_ORG_ID_,UPDATE_BY_,UPDATE_TIME_,SMS_TEMPLATE_NO_,VOICE_TEMPLATE_NO_,TENANT_ID_)
VALUES('22','催办模板', 'promoter-default', 'promoter', 1,'催办提醒','${taskSubject}','<p><a href="${baseUrl}/${frontBaseUrl}/matter/approvalForm?taskId=${taskId}&instId=${instId}&isGetApprovalBtn=true">${taskSubject}</a></p>', NULL,NULL,NULL, NULL,NULL,NULL,NULL,'-1');
INSERT INTO portal_meeting_config(ID_, BIND_FLOW_, FLOW_KEY_, FLOW_NAME_, PUBLISH_SCHEDULE_, FIELD_MAPPING_, CREATE_BY_, UPDATE_BY_, CREATE_TIME_, UPDATE_TIME_, CREATE_ORG_ID_, TENANT_ID_, DIC_CODE_, DIC_NAME_, COLUMN_ID_) VALUES ('1', 0, NULL, NULL, 1, NULL, '1', '1', NULL, NULL, NULL, '-1', NULL, NULL, NULL);
UPDATE PORTAL_SYS_PROPERTIES SET VALUE = 'bHfSv3x1fi+lJABUhmU8OeY08mPnTHxMWR1WRuHJKS+YRaSDGCyf8BgNKnk/fKbQ6GB5pHWcTo+Pv7lHddu4nR0yd0ZumquJU67O2r7qQsRL1iat2BjwCcvWIvV9R9fD7wB1PAmt1cZJVyEcqqR9MNjH1+rLhv0r/XXVUTo8DLRgFO3zWkBGqJVpKU2LKa6RKdIxeR7A5MkP3NcLLIjxF8Q4FgCrt+ocxpIvkSv9vGUagCMJdv+DO/5w3IHLiGBuOx9OzWcXxklfhuxxfPLRFBqnbSBjHl43GzCH4AAWNgoXvGWkdvJSqkb2sgme/tKrP6YXNuVGj5hluovbLbMRT8e4Ta8uqn9OB51DypxPdukgSkLw3QBa12S3TSQVXrx9GeM8skAX42N32OKxLj7R7TAcvC+iMPwVq8TFkhb3SQhI8iSTmm78T/LmjcDT0H4hF7xlpHbyUqpG9rIJnv7Sqxl20VdLRQ8aM8QwNIdsOhKhgsYfdoBs+eD/Fk0xqjSabUpuU1ePW3KNbmprxbg1wC+kD+SwF+UcjNcjIj6/7nVMRp5f3d8yyR0pQRSz5l3SkTgstjiLwzfngj73eizXn6WRZuGdSbqdMLHsVKVViJRClggscn8OTNdmwbVFdsEFlOpXb2v/2yaBGiWdmSfM282c37fPM7mFxGFz7nHh990gSkLw3QBa12S3TSQVXrx9DJnpKUGaQZHEzYWjEXVBLkdRpTqcFOPjRAEJjpVi3clSiAkfCZcGKRp6Ugwf6/Bn1ngz9wERdjLlLlf56GNFhUM3DSSxmT1rpmA0JJwNkBZ1mhgLjr5CkGep7eJ+qkoj5ykvtUR0THrV3TG0eMcch5ZUn4cXHCr/HhX/I/u+L/7SMLoCvgrkO4WQDqi/O42dzJUUF1VkdguJAASw8IY05i7+bP1+bL1NFi2ARuuuyV5EAINZJYa8n5LfIpZv87HEnz91kPExDYznMnNhuNUD+5YU6OQbK/UImWRvwChDashsEsI7yUeORtjd6AkNFzZHIzC0n3F8I9otlNpxSvdtO8ttmc6ttrqa99d+/rA18IKaxcE1ERlK4A3vMjTreiu/eeqaSI3PHJrupfaZLP9BcvyGZaK54FZTLZKrjkJ4DjsGPMG0HCW/5zl801p+af8b6Sh7IQkaY67Jnd2cPH9CeB4+bIcJ77yUxR8RVnKjNsgHwMzxjUDjXjruC1tssi/8' WHERE ALIAS = 'taskListFiledConf';
INSERT INTO PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628285933335953408', 'en', 'eip.common.import', 'import', '1', NULL, NULL, NULL, '-1');
INSERT INTO PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628285933373702144', 'zh-TW', 'eip.common.import', '導入', '1', NULL, NULL, NULL, '-1');
INSERT INTO PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_)VALUES ('1628285933411450880', 'zh-CN', 'eip.common.import', '导入', '1', NULL, NULL, NULL, '-1');
INSERT INTO PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628286057118253056', 'en', 'eip.common.export', 'export', '1', NULL, NULL, NULL, '-1');
INSERT INTO PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_)VALUES ('1628286057156001792', 'zh-TW', 'eip.common.export', '導出', '1', NULL, NULL, NULL, '-1');
INSERT INTO PORTAL_I18N_MESSAGE(ID_, TYPE_, KEY_, VALUE_, CREATE_BY_, CREATE_ORG_ID_, UPDATE_BY_, UPDATE_TIME_, TENANT_ID_) VALUES ('1628286057189556224', 'zh-CN', 'eip.common.export', '导出', '1', NULL, NULL, NULL, '-1');
ALTER TABLE "PORTAL_BIZ_SYSTEM" ADD PRIMARY KEY ("ID_");
ALTER TABLE "PORTAL_BIZ_SYSTEM_AUTH" ADD PRIMARY KEY ("ID_");
ALTER TABLE "PORTAL_MSG_SEND_LOG" ADD PRIMARY KEY ("ID_");
ALTER TABLE "PORTAL_URL_PERMISSION" ADD PRIMARY KEY ("ID");
ALTER TABLE "BPM_INST_DELETE_LOG" ADD PRIMARY KEY ("ID_");
ALTER TABLE "FORM_DATA_TEMPLATE_IMPORT_LOG" ADD PRIMARY KEY ("ID_");
-- 2023-3-6 liangjc 会议室配置默认不需要绑定流程脚本补充
update portal_meeting_config set BIND_FLOW_=0;