· 6 years ago · Jun 04, 2019, 03:44 PM
1--
2-- Title: Migrate old workflow details into act_hi_varinst
3-- Database: PostgreSQL
4-- Since: V4.2 Schema 6080
5--
6-- Please contact support@alfresco.com if you need assistance with the upgrade.
7--
8-- Migrate old workflow details into act_hi_varinst
9
10--ASSIGN:START_INDEX=VALUE_
11SELECT VALUE_ FROM ACT_GE_PROPERTY WHERE NAME_ = 'next.dbid';
12
13CREATE SEQUENCE VARINST_ID_SEQ START ${START_INDEX};
14
15--count the current items in act_hi_varinst, before migration
16--ASSIGN:INITIAL_ROW_COUNT=ROW_COUNT
17select count(*) as ROW_COUNT from ACT_HI_VARINST;
18
19-- increment the value to be used by the indexes with 1;
20select nextval('VARINST_ID_SEQ');
21
22-- insert from act_hi_detail into act_hi_varinst, the id will be generated starting from the next.dbid
23-- only the most recent version of a variable must by migrated
24-- the most recent version of a variable is considered to be the one with the highest revision and timestamp
25
26CREATE INDEX ACT_IDX_HI_DETAIL_TMP ON ACT_HI_DETAIL (PROC_INST_ID_, NAME_, REV_, time_);
27
28CREATE TABLE ACT_HI_DETAIL_TMP (
29 PROC_INST_ID_ varchar(64),
30 NAME_ varchar(255) NOT NULL,
31 REV_ int4,
32 TIME_ timestamp NOT NULL
33);
34
35CREATE INDEX ACT_HI_DETAIL_TMP_IDX ON ACT_HI_DETAIL_TMP(PROC_INST_ID_, NAME_, REV_, TIME_);
36
37INSERT INTO ACT_HI_DETAIL_TMP
38 SELECT
39 PROC_INST_ID_,
40 NAME_,
41 MAX(REV_),
42 MAX(time_)
43 FROM ACT_HI_DETAIL
44 GROUP BY PROC_INST_ID_, NAME_;
45
46CREATE TABLE ACT_HI_VARINST_TMP (LIKE ACT_HI_VARINST INCLUDING DEFAULTS INCLUDING INDEXES);
47
48INSERT INTO ACT_HI_VARINST_TMP(
49 ID_,
50 PROC_INST_ID_,
51 EXECUTION_ID_,
52 TASK_ID_,
53 NAME_,
54 VAR_TYPE_,
55 REV_,
56 BYTEARRAY_ID_,
57 DOUBLE_,
58 LONG_,
59 TEXT_,
60 TEXT2_
61 )
62SELECT
63 nextval('VARINST_ID_SEQ'),
64 AHD.PROC_INST_ID_,
65 AHD.EXECUTION_ID_,
66 AHD.TASK_ID_,
67 AHD.NAME_,
68 AHD.VAR_TYPE_,
69 AHD.REV_,
70 AHD.BYTEARRAY_ID_,
71 AHD.DOUBLE_,
72 AHD.LONG_,
73 AHD.TEXT_,
74 AHD.TEXT2_
75FROM ACT_HI_DETAIL AHD
76INNER JOIN ACT_HI_DETAIL_TMP AS DETAIL
77 ON AHD.PROC_INST_ID_ = DETAIL.PROC_INST_ID_
78 AND AHD.NAME_ = DETAIL.NAME_
79 AND AHD.REV_ = DETAIL.REV_
80 AND AHD.TIME_ = DETAIL.TIME_
81LEFT OUTER JOIN ACT_HI_VARINST as VAR
82 ON AHD.PROC_INST_ID_ = VAR.PROC_INST_ID_
83 WHERE VAR.PROC_INST_ID_ IS NULL;
84
85
86INSERT INTO ACT_HI_VARINST(
87 ID_,
88 PROC_INST_ID_,
89 EXECUTION_ID_,
90 TASK_ID_,
91 NAME_,
92 VAR_TYPE_,
93 REV_,
94 BYTEARRAY_ID_,
95 DOUBLE_,
96 LONG_,
97 TEXT_,
98 TEXT2_
99)
100 SELECT
101 ID_,
102 PROC_INST_ID_,
103 EXECUTION_ID_,
104 TASK_ID_,
105 NAME_,
106 VAR_TYPE_,
107 REV_,
108 BYTEARRAY_ID_,
109 DOUBLE_,
110 LONG_,
111 TEXT_,
112 TEXT2_
113 FROM
114 ACT_HI_VARINST_TMP;
115
116--update act_ge_property
117--ASSIGN:TOTAL_ROW_COUNT=ROW_COUNT
118select count(*) as ROW_COUNT from ACT_HI_VARINST;
119--increase the next.dbid value so that following ids will be created starting with the new value
120update ACT_GE_PROPERTY set VALUE_ = VALUE_::integer + ${TOTAL_ROW_COUNT} - ${INITIAL_ROW_COUNT} where NAME_ = 'next.dbid';
121
122--revision is currently increased each time a block id is reserved, so we're simulating this behaviour
123update ACT_GE_PROPERTY set REV_ = VALUE_::integer / 100 + 1 where NAME_ = 'next.dbid';
124
125DROP SEQUENCE IF EXISTS VARINST_ID_SEQ;
126DROP TABLE ACT_HI_DETAIL_TMP;
127DROP TABLE ACT_HI_VARINST_TMP;
128DROP INDEX ACT_IDX_HI_DETAIL_TMP;
129--
130-- Record script finish
131--
132DELETE FROM alf_applied_patch WHERE id = 'patch.db-v4.2-migrate-activiti-workflows';
133INSERT INTO alf_applied_patch
134 (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)
135 VALUES
136 (
137 'patch.db-v4.2-migrate-activiti-workflows', 'Manually executed script upgrade V4.2: migrate-activiti-workflows',
138 0, 6080, -1, 6081, null, 'UNKNOWN', ${TRUE}, ${TRUE}, 'Script completed'
139 );