1 package edu.internet2.middleware.grouper.app.upgradeTasks;
2
3 import edu.internet2.middleware.grouper.GrouperSession;
4 import edu.internet2.middleware.grouper.app.loader.OtherJobBase.OtherJobInput;
5 import edu.internet2.middleware.grouper.ddl.GrouperDdlUtils;
6 import edu.internet2.middleware.grouper.exception.GrouperSessionException;
7 import edu.internet2.middleware.grouper.misc.GrouperSessionHandler;
8 import edu.internet2.middleware.grouper.misc.GrouperVersion;
9 import edu.internet2.middleware.grouperClient.jdbc.GcDbAccess;
10
11 public class UpgradeTaskV27 implements UpgradeTasksInterface {
12
13 @Override
14 public GrouperVersion versionIntroduced() {
15 return GrouperVersion.valueOfIgnoreCase("5.14.0");
16 }
17
18 @Override
19 public boolean upgradeTaskIsDdl() {
20 return true;
21 }
22
23 @Override
24 public boolean runOnNewInstall() {
25 return true;
26 }
27
28 @Override
29 public boolean doesUpgradeTaskHaveDdlWorkToDo() {
30
31 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp")) {
32 return true;
33 }
34
35 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp_utc")) {
36 return true;
37 }
38
39 return false;
40
41 }
42
43 @Override
44 public void updateVersionFromPrevious(OtherJobInput otherJobInput) {
45 GrouperSession.internal_callbackRootGrouperSession(new GrouperSessionHandler() {
46
47 @Override
48 public Object callback(GrouperSession grouperSession) throws GrouperSessionException {
49
50 if (GrouperDdlUtils.isPostgres()) {
51
52 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp")) {
53 new GcDbAccess().sql("""
54 CREATE FUNCTION grouper_to_timestamp(bigint) RETURNS timestamp AS $$
55 DECLARE
56 timestamp_value bigint;
57 BEGIN
58 timestamp_value := CASE
59 WHEN $1 > 100000000000000 THEN $1 / 1000000
60 ELSE $1 / 1000
61 END;
62 RETURN to_timestamp(timestamp_value);
63 END;
64 $$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;
65 """).executeSql();
66 if (otherJobInput != null) {
67 otherJobInput.getHib3GrouperLoaderLog().addInsertCount(1);
68 otherJobInput.getHib3GrouperLoaderLog().appendJobMessage(", added function grouper_to_timestamp");
69 }
70 }
71
72 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp_utc")) {
73 new GcDbAccess().sql("""
74 CREATE FUNCTION grouper_to_timestamp_utc(bigint) RETURNS timestamp AS $$
75 DECLARE
76 timestamp_value bigint;
77 BEGIN
78 timestamp_value := CASE
79 WHEN $1 > 100000000000000 THEN $1 / 1000000
80 ELSE $1 / 1000
81 END;
82 RETURN to_timestamp(timestamp_value) AT TIME ZONE 'UTC';
83 END;
84 $$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;
85 """).executeSql();
86 if (otherJobInput != null) {
87 otherJobInput.getHib3GrouperLoaderLog().addInsertCount(1);
88 otherJobInput.getHib3GrouperLoaderLog().appendJobMessage(", added function grouper_to_timestamp_utc");
89 }
90 }
91 } else if (GrouperDdlUtils.isMysql()) {
92
93 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp")) {
94 new GcDbAccess().sql("""
95 CREATE FUNCTION grouper_to_timestamp(input BIGINT)
96 RETURNS DATETIME
97 DETERMINISTIC
98 NO SQL
99 BEGIN
100 DECLARE timestamp_value BIGINT;
101
102 IF input > 100000000000000 THEN
103 SET timestamp_value = input / 1000000;
104 ELSE
105 SET timestamp_value = input / 1000;
106 END IF;
107
108 RETURN FROM_UNIXTIME(timestamp_value);
109 END; -- function grouper_to_timestamp
110 """).executeSql();
111 if (otherJobInput != null) {
112 otherJobInput.getHib3GrouperLoaderLog().addInsertCount(1);
113 otherJobInput.getHib3GrouperLoaderLog().appendJobMessage(", created function grouper_to_timestamp if it didn't exist");
114 }
115 }
116
117 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp_utc")) {
118 new GcDbAccess().sql("""
119 CREATE FUNCTION IF NOT EXISTS grouper_to_timestamp_utc(input BIGINT)
120 RETURNS DATETIME
121 DETERMINISTIC
122 NO SQL
123 BEGIN
124 DECLARE timestamp_value BIGINT;
125
126 IF input > 100000000000000 THEN
127 SET timestamp_value = input / 1000000;
128 ELSE
129 SET timestamp_value = input / 1000;
130 END IF;
131
132 RETURN CONVERT_TZ(FROM_UNIXTIME(timestamp_value), @@session.time_zone,'+00:00');
133 END; -- function grouper_to_timestamp_utc
134 """).executeSql();
135 if (otherJobInput != null) {
136 otherJobInput.getHib3GrouperLoaderLog().addInsertCount(1);
137 otherJobInput.getHib3GrouperLoaderLog().appendJobMessage(", created function grouper_to_timestamp_utc if it didn't exist");
138 }
139 }
140
141 } else if (GrouperDdlUtils.isOracle()) {
142
143 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp")) {
144 new GcDbAccess().sql("""
145 CREATE FUNCTION grouper_to_timestamp(input IN NUMBER)
146 RETURN TIMESTAMP IS
147 timestamp_value NUMBER;
148 BEGIN
149 IF input > 100000000000000 THEN
150 timestamp_value := input / 1000000;
151 ELSE
152 timestamp_value := input / 1000;
153 END IF;
154 RETURN (timestamp '1970-01-01 00:00:00.000 UTC' + numtodsinterval(timestamp_value,'SECOND')) AT TIME ZONE SESSIONTIMEZONE;
155 END; -- function
156 """).executeSql();
157 if (otherJobInput != null) {
158 otherJobInput.getHib3GrouperLoaderLog().addInsertCount(1);
159 otherJobInput.getHib3GrouperLoaderLog().appendJobMessage(", replaced function grouper_to_timestamp");
160 }
161 }
162
163 if (!GrouperDdlUtils.doesFunctionExist("grouper_to_timestamp_utc")) {
164 new GcDbAccess().sql("""
165 CREATE OR REPLACE FUNCTION grouper_to_timestamp_utc(input IN NUMBER)
166 RETURN TIMESTAMP IS
167 timestamp_value NUMBER;
168 BEGIN
169 IF input > 100000000000000 THEN
170 timestamp_value := input / 1000000;
171 ELSE
172 timestamp_value := input / 1000;
173 END IF;
174 RETURN (timestamp '1970-01-01 00:00:00.000 UTC' + numtodsinterval(timestamp_value,'SECOND')) AT TIME ZONE 'UTC';
175 END; -- function
176 """).executeSql();
177 if (otherJobInput != null) {
178 otherJobInput.getHib3GrouperLoaderLog().addInsertCount(1);
179 otherJobInput.getHib3GrouperLoaderLog().appendJobMessage(", replaced function grouper_to_timestamp_utc");
180 }
181 }
182 }
183
184 return null;
185 }
186 });
187 }
188
189 }