1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.waarp.openr66.database.model;
21
22 import org.waarp.common.database.DbRequest;
23 import org.waarp.common.database.DbSession;
24 import org.waarp.common.database.exception.WaarpDatabaseException;
25 import org.waarp.common.database.exception.WaarpDatabaseNoConnectionException;
26 import org.waarp.common.database.exception.WaarpDatabaseSqlException;
27 import org.waarp.common.database.model.DbModelOracle;
28 import org.waarp.common.logging.SysErrLogger;
29 import org.waarp.openr66.database.data.DbConfiguration;
30 import org.waarp.openr66.database.data.DbHostAuth;
31 import org.waarp.openr66.database.data.DbHostConfiguration;
32 import org.waarp.openr66.database.data.DbMultipleMonitor;
33 import org.waarp.openr66.database.data.DbRule;
34 import org.waarp.openr66.database.data.DbTaskRunner;
35 import org.waarp.openr66.protocol.configuration.Configuration;
36 import org.waarp.openr66.protocol.configuration.PartnerConfiguration;
37 import org.waarp.openr66.protocol.utils.R66Versions;
38
39 import java.sql.Types;
40
41 import static org.waarp.common.database.DbConstant.*;
42
43
44
45
46 public class DbModelOracleR66 extends DbModelOracle {
47
48
49
50
51
52
53
54
55
56 public DbModelOracleR66(final String dbserver, final String dbuser,
57 final String dbpasswd)
58 throws WaarpDatabaseNoConnectionException {
59 super(dbserver, dbuser, dbpasswd);
60 }
61
62 @Override
63 public final void createTables(final DbSession session)
64 throws WaarpDatabaseNoConnectionException {
65
66 final String createTableH2 = "CREATE TABLE ";
67 final String constraint = " CONSTRAINT ";
68 final String primaryKey = " PRIMARY KEY ";
69 final String notNull =
70 " ";
71
72
73 StringBuilder action =
74 new StringBuilder(createTableH2 + DbMultipleMonitor.table + '(');
75 final DbMultipleMonitor.Columns[] mcolumns =
76 DbMultipleMonitor.Columns.values();
77 for (int i = 0; i < mcolumns.length - 1; i++) {
78 action.append(mcolumns[i].name())
79 .append(DBType.getType(DbMultipleMonitor.dbTypes[i]))
80 .append(notNull).append(", ");
81 }
82 action.append(mcolumns[mcolumns.length - 1].name()).append(
83 DBType.getType(DbMultipleMonitor.dbTypes[mcolumns.length - 1]))
84 .append(notNull).append(',');
85 action.append(constraint + " multimonit_pk " + primaryKey + '(')
86 .append(mcolumns[mcolumns.length - 1].name()).append("))");
87 SysErrLogger.FAKE_LOGGER.sysout(action);
88 DbRequest request = new DbRequest(session);
89 try {
90 request.query(action.toString());
91 } catch (final WaarpDatabaseNoConnectionException e) {
92 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
93 return;
94 } catch (final WaarpDatabaseSqlException e) {
95 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
96
97 } finally {
98 request.close();
99 }
100 try {
101 final DbMultipleMonitor multipleMonitor =
102 new DbMultipleMonitor(Configuration.configuration.getHostId(), 0, 0,
103 0);
104 if (!multipleMonitor.exist()) {
105 multipleMonitor.insert();
106 }
107 } catch (final WaarpDatabaseException e1) {
108 SysErrLogger.FAKE_LOGGER.ignoreLog(e1);
109 }
110
111
112 action = new StringBuilder(createTableH2 + DbConfiguration.table + '(');
113 final DbConfiguration.Columns[] ccolumns = DbConfiguration.Columns.values();
114 for (int i = 0; i < ccolumns.length - 1; i++) {
115 action.append(ccolumns[i].name())
116 .append(DBType.getType(DbConfiguration.dbTypes[i])).append(notNull)
117 .append(", ");
118 }
119 action.append(ccolumns[ccolumns.length - 1].name())
120 .append(DBType.getType(DbConfiguration.dbTypes[ccolumns.length - 1]))
121 .append(notNull).append(',');
122 action.append(constraint + " conf_pk " + primaryKey + '(')
123 .append(ccolumns[ccolumns.length - 1].name()).append("))");
124 SysErrLogger.FAKE_LOGGER.sysout(action);
125 request = new DbRequest(session);
126 try {
127 request.query(action.toString());
128 } catch (final WaarpDatabaseNoConnectionException e) {
129 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
130 return;
131 } catch (final WaarpDatabaseSqlException e) {
132 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
133
134 } finally {
135 request.close();
136 }
137
138
139 action = new StringBuilder(createTableH2 + DbHostConfiguration.table + '(');
140 final DbHostConfiguration.Columns[] chcolumns =
141 DbHostConfiguration.Columns.values();
142 for (int i = 0; i < chcolumns.length - 1; i++) {
143 action.append(chcolumns[i].name())
144 .append(DBType.getType(DbHostConfiguration.dbTypes[i]))
145 .append(notNull).append(", ");
146 }
147 action.append(chcolumns[chcolumns.length - 1].name()).append(
148 DBType.getType(DbHostConfiguration.dbTypes[chcolumns.length - 1]))
149 .append(notNull).append(',');
150 action.append(constraint + " hostconf_pk " + primaryKey + '(')
151 .append(chcolumns[chcolumns.length - 1].name()).append("))");
152 SysErrLogger.FAKE_LOGGER.sysout(action);
153 request = new DbRequest(session);
154 try {
155 request.query(action.toString());
156 } catch (final WaarpDatabaseNoConnectionException e) {
157 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
158 return;
159 } catch (final WaarpDatabaseSqlException e) {
160 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
161
162 } finally {
163 request.close();
164 }
165
166
167 action = new StringBuilder(createTableH2 + DbHostAuth.table + '(');
168 final DbHostAuth.Columns[] hcolumns = DbHostAuth.Columns.values();
169 for (int i = 0; i < hcolumns.length - 1; i++) {
170 action.append(hcolumns[i].name())
171 .append(DBType.getType(DbHostAuth.dbTypes[i])).append(notNull)
172 .append(", ");
173 }
174 action.append(hcolumns[hcolumns.length - 1].name())
175 .append(DBType.getType(DbHostAuth.dbTypes[hcolumns.length - 1]))
176 .append(notNull).append(',');
177 action.append(constraint + " host_pk " + primaryKey + '(')
178 .append(hcolumns[hcolumns.length - 1].name()).append("))");
179 SysErrLogger.FAKE_LOGGER.sysout(action);
180 try {
181 request.query(action.toString());
182 } catch (final WaarpDatabaseNoConnectionException e) {
183 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
184 return;
185 } catch (final WaarpDatabaseSqlException e) {
186 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
187
188 } finally {
189 request.close();
190 }
191
192
193 action = new StringBuilder(createTableH2 + DbRule.table + '(');
194 final DbRule.Columns[] rcolumns = DbRule.Columns.values();
195 for (int i = 0; i < rcolumns.length - 1; i++) {
196 action.append(rcolumns[i].name())
197 .append(DBType.getType(DbRule.dbTypes[i])).append(", ");
198 }
199 action.append(rcolumns[rcolumns.length - 1].name())
200 .append(DBType.getType(DbRule.dbTypes[rcolumns.length - 1]))
201 .append(notNull).append(',');
202 action.append(constraint + " rule_pk " + primaryKey + '(')
203 .append(rcolumns[rcolumns.length - 1].name()).append("))");
204 SysErrLogger.FAKE_LOGGER.sysout(action);
205 try {
206 request.query(action.toString());
207 } catch (final WaarpDatabaseNoConnectionException e) {
208 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
209 return;
210 } catch (final WaarpDatabaseSqlException e) {
211 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
212
213 } finally {
214 request.close();
215 }
216
217
218 action = new StringBuilder(createTableH2 + DbTaskRunner.table + '(');
219 final DbTaskRunner.Columns[] acolumns = DbTaskRunner.Columns.values();
220 for (int i = 0; i < acolumns.length; i++) {
221 action.append(acolumns[i].name())
222 .append(DBType.getType(DbTaskRunner.dbTypes[i])).append(notNull);
223 if (DbTaskRunner.dbTypes[i] == Types.TIMESTAMP) {
224 action.append(" DEFAULT CURRENT_TIMESTAMP(3)");
225 }
226 action.append(", ");
227 }
228
229 action.append(constraint + " runner_pk " + primaryKey + '(');
230 for (int i = 0; i < DbTaskRunner.PRIMARY_KEY.length - 1; i++) {
231 action.append(DbTaskRunner.PRIMARY_KEY[i]).append(',');
232 }
233 action.append(DbTaskRunner.PRIMARY_KEY[DbTaskRunner.PRIMARY_KEY.length - 1])
234 .append("))");
235 SysErrLogger.FAKE_LOGGER.sysout(action);
236 try {
237 request.query(action.toString());
238 } catch (final WaarpDatabaseNoConnectionException e) {
239 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
240 return;
241 } catch (final WaarpDatabaseSqlException e) {
242 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
243
244 } finally {
245 request.close();
246 }
247
248 try {
249 request = new DbRequest(session);
250 DbModelFactoryR66.createIndex30(dbTypeResolver, request);
251 } finally {
252 request.close();
253 }
254
255
256 final long minimalValue = System.currentTimeMillis() + 1;
257 action = new StringBuilder(
258 "CREATE SEQUENCE " + DbTaskRunner.fieldseq + " MINVALUE " +
259 (ILLEGALVALUE + 1) + " START WITH " + minimalValue);
260 SysErrLogger.FAKE_LOGGER.sysout(action);
261 try {
262 request.query(action.toString());
263 } catch (final WaarpDatabaseNoConnectionException e) {
264 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
265 return;
266 } catch (final WaarpDatabaseSqlException e) {
267 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
268 return;
269 } finally {
270 request.close();
271 }
272
273 DbHostConfiguration.updateVersionDb(Configuration.configuration.getHostId(),
274 R66Versions.V3_1_0.getVersion());
275 }
276
277 @Override
278 public final boolean upgradeDb(final DbSession session, final String version)
279 throws WaarpDatabaseNoConnectionException {
280 if (PartnerConfiguration.isVersion2GEQVersion1(
281 R66Versions.V3_1_0.getVersion(), version)) {
282 return true;
283 }
284 if (PartnerConfiguration.isVersion2GEQVersion1(version,
285 R66Versions.V2_4_13.getVersion())) {
286 SysErrLogger.FAKE_LOGGER.sysout(
287 version + " to " + R66Versions.V2_4_13.getVersion() + "? " + true);
288 final String createTableH2 = "CREATE TABLE ";
289 final String constraint = " CONSTRAINT ";
290 final String primaryKey = " PRIMARY KEY ";
291 final String notNull = " NOT NULL ";
292
293
294 final StringBuilder action =
295 new StringBuilder(createTableH2 + DbHostConfiguration.table + '(');
296 final DbHostConfiguration.Columns[] chcolumns =
297 DbHostConfiguration.Columns.values();
298 for (int i = 0; i < chcolumns.length - 1; i++) {
299 action.append(chcolumns[i].name())
300 .append(DBType.getType(DbHostConfiguration.dbTypes[i]))
301 .append(notNull).append(", ");
302 }
303 action.append(chcolumns[chcolumns.length - 1].name()).append(
304 DBType.getType(DbHostConfiguration.dbTypes[chcolumns.length - 1]))
305 .append(notNull).append(',');
306 action.append(constraint + " hostconf_pk " + primaryKey + '(')
307 .append(chcolumns[chcolumns.length - 1].name()).append("))");
308 SysErrLogger.FAKE_LOGGER.sysout(action);
309 final DbRequest request = new DbRequest(session);
310 try {
311 request.query(action.toString());
312 } catch (final WaarpDatabaseSqlException e) {
313 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
314 return false;
315 } finally {
316 request.close();
317 }
318 }
319 if (PartnerConfiguration.isVersion2GEQVersion1(version,
320 R66Versions.V2_4_17.getVersion())) {
321 SysErrLogger.FAKE_LOGGER.sysout(
322 version + " to " + R66Versions.V2_4_17.getVersion() + "? " + true);
323 final String command = "ALTER TABLE " + DbTaskRunner.table + " ADD ( " +
324 DbTaskRunner.Columns.TRANSFERINFO.name() + ' ' +
325 DBType.getType(
326 DbTaskRunner.dbTypes[DbTaskRunner.Columns.TRANSFERINFO.ordinal()]) +
327 " DEFAULT '{}' NOT NULL ) ";
328 final DbRequest request = new DbRequest(session);
329 try {
330 request.query(command);
331 } catch (final WaarpDatabaseSqlException e) {
332 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
333
334 } finally {
335 request.close();
336 }
337 }
338 if (PartnerConfiguration.isVersion2GEQVersion1(version,
339 R66Versions.V2_4_23.getVersion())) {
340 SysErrLogger.FAKE_LOGGER.sysout(
341 version + " to " + R66Versions.V2_4_23.getVersion() + "? " + true);
342 String command = "ALTER TABLE " + DbHostAuth.table + " ADD ( " +
343 DbHostAuth.Columns.ISACTIVE.name() + ' ' +
344 DBType.getType(
345 DbHostAuth.dbTypes[DbHostAuth.Columns.ISACTIVE.ordinal()]) +
346 " DEFAULT " + true + " NOT NULL ) ";
347 DbRequest request = new DbRequest(session);
348 try {
349 request.query(command);
350 } catch (final WaarpDatabaseSqlException e) {
351 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
352
353 } finally {
354 request.close();
355 }
356 command = "ALTER TABLE " + DbHostAuth.table + " ADD ( " +
357 DbHostAuth.Columns.ISPROXIFIED.name() + ' ' + DBType.getType(
358 DbHostAuth.dbTypes[DbHostAuth.Columns.ISPROXIFIED.ordinal()]) +
359 " DEFAULT " + false + " NOT NULL ) ";
360 request = new DbRequest(session);
361 try {
362 request.query(command);
363 } catch (final WaarpDatabaseSqlException e) {
364 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
365
366 } finally {
367 request.close();
368 }
369 }
370 if (PartnerConfiguration.isVersion2GTVersion1(version,
371 R66Versions.V2_4_25.getVersion())) {
372 SysErrLogger.FAKE_LOGGER.sysout(
373 version + " to " + R66Versions.V2_4_25.getVersion() + "? " + true);
374 final String command =
375 "ALTER TABLE " + DbTaskRunner.table + " MODIFY ( " +
376 DbTaskRunner.Columns.FILENAME.name() + ' ' + DBType.getType(
377 DbTaskRunner.dbTypes[DbTaskRunner.Columns.FILENAME.ordinal()]) +
378 " NOT NULL, " + DbTaskRunner.Columns.ORIGINALNAME.name() + ' ' +
379 DBType.getType(
380 DbTaskRunner.dbTypes[DbTaskRunner.Columns.ORIGINALNAME.ordinal()]) +
381 " NOT NULL )";
382 final DbRequest request = new DbRequest(session);
383 try {
384 SysErrLogger.FAKE_LOGGER.sysout("Command: " + command);
385 request.query(command);
386 } catch (final WaarpDatabaseSqlException e) {
387 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
388 return false;
389 } finally {
390 request.close();
391 }
392 }
393 if (PartnerConfiguration.isVersion2GTVersion1(version,
394 R66Versions.V3_0_4.getVersion())) {
395 SysErrLogger.FAKE_LOGGER.sysout(
396 version + " to " + R66Versions.V3_0_4.getVersion() + "? " + true);
397 final DbRequest request = new DbRequest(session);
398
399 DbModelFactoryR66.upgradeTable30(dbTypeResolver, request, " MODIFY ", " ",
400 " ");
401 try {
402 final String command = "DROP INDEX IF EXISTS IDX_RUNNER ";
403 try {
404 SysErrLogger.FAKE_LOGGER.sysout("Command: " + command);
405 request.query(command);
406 } catch (final WaarpDatabaseNoConnectionException e) {
407 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
408 return false;
409 } catch (final WaarpDatabaseSqlException e) {
410 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
411
412 }
413 DbModelFactoryR66.createIndex30(dbTypeResolver, request);
414 } finally {
415 request.close();
416 }
417 }
418 DbHostConfiguration.updateVersionDb(Configuration.configuration.getHostId(),
419 R66Versions.V3_1_0.getVersion());
420 return true;
421 }
422
423 @Override
424 public final boolean needUpgradeDb(final DbSession session,
425 final String version, final boolean tryFix)
426 throws WaarpDatabaseNoConnectionException {
427
428 return DbModelFactoryR66.needUpgradeDbAllDb(dbTypeResolver, session,
429 version);
430 }
431
432 }