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.WaarpDatabaseNoConnectionException;
25 import org.waarp.common.database.exception.WaarpDatabaseSqlException;
26 import org.waarp.common.database.model.DbModelPostgresql;
27 import org.waarp.common.logging.SysErrLogger;
28 import org.waarp.openr66.database.data.DbHostAuth;
29 import org.waarp.openr66.database.data.DbHostConfiguration;
30 import org.waarp.openr66.database.data.DbTaskRunner;
31 import org.waarp.openr66.protocol.configuration.Configuration;
32 import org.waarp.openr66.protocol.configuration.PartnerConfiguration;
33 import org.waarp.openr66.protocol.utils.R66Versions;
34
35 import java.sql.SQLException;
36
37 import static org.waarp.common.database.DbConstant.*;
38
39
40
41
42 public class DbModelPostgresqlR66 extends DbModelPostgresql {
43
44
45
46
47
48 public DbModelPostgresqlR66() throws WaarpDatabaseNoConnectionException {
49
50 }
51
52
53
54
55
56
57 private int getServerVersion(final DbSession session)
58 throws WaarpDatabaseNoConnectionException {
59 int serverVersion = 0;
60 final DbRequest request = new DbRequest(session);
61 try {
62 request.select("SHOW server_version_num");
63 if (request.getNext()) {
64 serverVersion = request.getResultSet().getInt("server_version_num");
65 }
66 } catch (final WaarpDatabaseNoConnectionException e) {
67 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
68 } catch (final WaarpDatabaseSqlException e) {
69 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
70
71 } catch (final SQLException e) {
72 SysErrLogger.FAKE_LOGGER.syserr(e);
73 } finally {
74 request.close();
75 }
76 return serverVersion;
77 }
78
79 @Override
80 public final void createTables(final DbSession session)
81 throws WaarpDatabaseNoConnectionException {
82
83 final String createTableH2 = "CREATE TABLE IF NOT EXISTS ";
84 final String primaryKey = " PRIMARY KEY ";
85 final String notNull = " NOT NULL ";
86 final DbRequest request =
87 DbModelFactoryR66.subCreateTableMariaDbMySQLH2PostgreSQL(dbTypeResolver,
88 session,
89 createTableH2,
90 primaryKey,
91 notNull);
92 if (request == null) {
93 return;
94 }
95 try {
96
97 final long minimalValue = System.currentTimeMillis() + 1;
98 final StringBuilder action = new StringBuilder(
99 "CREATE SEQUENCE IF NOT EXISTS " + DbTaskRunner.fieldseq +
100 " MINVALUE " + (ILLEGALVALUE + 1) + " START WITH " + minimalValue);
101 SysErrLogger.FAKE_LOGGER.sysout(action);
102 try {
103 request.query(action.toString());
104 } catch (final WaarpDatabaseNoConnectionException e) {
105 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
106 return;
107 } catch (final WaarpDatabaseSqlException e) {
108 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
109
110 }
111 } finally {
112 request.close();
113 }
114
115 DbHostConfiguration.updateVersionDb(Configuration.configuration.getHostId(),
116 R66Versions.V3_1_0.getVersion());
117 }
118
119
120 @Override
121 public final boolean upgradeDb(final DbSession session, final String version)
122 throws WaarpDatabaseNoConnectionException {
123 if (PartnerConfiguration.isVersion2GEQVersion1(
124 R66Versions.V3_1_0.getVersion(), version)) {
125 return true;
126 }
127 String ifExists = "";
128 String ifNotExists = "";
129 final int serverVersion = getServerVersion(session);
130 if (serverVersion >= 90100) {
131 ifExists = " IF EXISTS ";
132 ifNotExists = " IF NOT EXISTS ";
133 }
134 if (PartnerConfiguration.isVersion2GEQVersion1(version,
135 R66Versions.V2_4_13.getVersion())) {
136 SysErrLogger.FAKE_LOGGER.sysout(
137 version + " to " + R66Versions.V2_4_13.getVersion() + "? " + true);
138 final String createTableH2 = "CREATE TABLE " + ifNotExists;
139 final String primaryKey = " PRIMARY KEY ";
140 final String notNull = " NOT NULL ";
141
142
143 final StringBuilder action =
144 new StringBuilder(createTableH2 + DbHostConfiguration.table + '(');
145 final DbHostConfiguration.Columns[] chcolumns =
146 DbHostConfiguration.Columns.values();
147 for (int i = 0; i < chcolumns.length - 1; i++) {
148 action.append(chcolumns[i].name())
149 .append(DBType.getType(DbHostConfiguration.dbTypes[i]))
150 .append(notNull).append(", ");
151 }
152 action.append(chcolumns[chcolumns.length - 1].name()).append(
153 DBType.getType(DbHostConfiguration.dbTypes[chcolumns.length - 1]))
154 .append(primaryKey).append(')');
155 SysErrLogger.FAKE_LOGGER.sysout(action);
156 final DbRequest request = new DbRequest(session);
157 try {
158 request.query(action.toString());
159 } catch (final WaarpDatabaseSqlException e) {
160 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
161 return false;
162 } finally {
163 request.close();
164 }
165 }
166 if (PartnerConfiguration.isVersion2GEQVersion1(version,
167 R66Versions.V2_4_17.getVersion())) {
168 SysErrLogger.FAKE_LOGGER.sysout(
169 version + " to " + R66Versions.V2_4_17.getVersion() + "? " + true);
170 final String command =
171 "DO $$ BEGIN " + "ALTER TABLE " + DbTaskRunner.table +
172 " ADD COLUMN " + DbTaskRunner.Columns.TRANSFERINFO.name() + ' ' +
173 DBType.getType(
174 DbTaskRunner.dbTypes[DbTaskRunner.Columns.TRANSFERINFO.ordinal()]) +
175 " DEFAULT '{}' NOT NULL; " +
176 "EXCEPTION WHEN duplicate_column THEN END $$";
177 final DbRequest request = new DbRequest(session);
178 try {
179 request.query(command);
180 } catch (final WaarpDatabaseSqlException e) {
181 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
182
183 } finally {
184 request.close();
185 }
186 }
187 if (PartnerConfiguration.isVersion2GEQVersion1(version,
188 R66Versions.V2_4_23.getVersion())) {
189 SysErrLogger.FAKE_LOGGER.sysout(
190 version + " to " + R66Versions.V2_4_23.getVersion() + "? " + true);
191 String command =
192 "DO $$ BEGIN " + "ALTER TABLE " + DbHostAuth.table + " ADD COLUMN " +
193 DbHostAuth.Columns.ISACTIVE.name() + ' ' + DBType.getType(
194 DbHostAuth.dbTypes[DbHostAuth.Columns.ISACTIVE.ordinal()]) +
195 " DEFAULT " + true + " NOT NULL; " +
196 "EXCEPTION WHEN duplicate_column THEN END $$";
197 DbRequest request = new DbRequest(session);
198 try {
199 request.query(command);
200 } catch (final WaarpDatabaseSqlException e) {
201 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
202
203 } finally {
204 request.close();
205 }
206 command =
207 "DO $$ BEGIN " + "ALTER TABLE " + DbHostAuth.table + " ADD COLUMN " +
208 DbHostAuth.Columns.ISPROXIFIED.name() + ' ' + DBType.getType(
209 DbHostAuth.dbTypes[DbHostAuth.Columns.ISPROXIFIED.ordinal()]) +
210 " DEFAULT " + false + " NOT NULL; " +
211 "EXCEPTION WHEN duplicate_column THEN END $$";
212 request = new DbRequest(session);
213 try {
214 request.query(command);
215 } catch (final WaarpDatabaseSqlException e) {
216 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
217
218 } finally {
219 request.close();
220 }
221 }
222 if (PartnerConfiguration.isVersion2GTVersion1(version,
223 R66Versions.V2_4_25.getVersion())) {
224 SysErrLogger.FAKE_LOGGER.sysout(
225 version + " to " + R66Versions.V2_4_25.getVersion() + "? " + true);
226 final String command =
227 "ALTER TABLE " + DbTaskRunner.table + " ALTER COLUMN " +
228 DbTaskRunner.Columns.FILENAME.name() + " TYPE " + DBType.getType(
229 DbTaskRunner.dbTypes[DbTaskRunner.Columns.FILENAME.ordinal()]) +
230 ',' + " ALTER COLUMN " + DbTaskRunner.Columns.FILENAME.name() +
231 " SET NOT NULL, " + " ALTER COLUMN " +
232 DbTaskRunner.Columns.ORIGINALNAME.name() + " TYPE " + DBType.getType(
233 DbTaskRunner.dbTypes[DbTaskRunner.Columns.ORIGINALNAME.ordinal()]) +
234 ',' + " ALTER COLUMN " + DbTaskRunner.Columns.FILENAME.name() +
235 " SET NOT NULL ";
236 final DbRequest request = new DbRequest(session);
237 try {
238 SysErrLogger.FAKE_LOGGER.sysout("Command: " + command);
239 request.query(command);
240 } catch (final WaarpDatabaseSqlException e) {
241 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
242 return false;
243 } finally {
244 request.close();
245 }
246 }
247 if (PartnerConfiguration.isVersion2GTVersion1(version,
248 R66Versions.V3_0_4.getVersion())) {
249 SysErrLogger.FAKE_LOGGER.sysout(
250 version + " to " + R66Versions.V3_0_4.getVersion() + "? " + true);
251 final DbRequest request = new DbRequest(session);
252
253 DbModelFactoryR66.upgradeTable30(dbTypeResolver, request,
254 " ALTER COLUMN ", " TYPE ", " ");
255 try {
256 final String command = "DROP INDEX " + ifExists + " IDX_RUNNER ";
257 try {
258 SysErrLogger.FAKE_LOGGER.sysout("Command: " + command);
259 request.query(command);
260 } catch (final WaarpDatabaseNoConnectionException e) {
261 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
262 return false;
263 } catch (final WaarpDatabaseSqlException e) {
264 SysErrLogger.FAKE_LOGGER.ignoreLog(e);
265
266 }
267 DbModelFactoryR66.createIndex30(dbTypeResolver, request);
268 } finally {
269 request.close();
270 }
271 }
272 DbHostConfiguration.updateVersionDb(Configuration.configuration.getHostId(),
273 R66Versions.V3_1_0.getVersion());
274 return true;
275 }
276
277
278 @Override
279 public final boolean needUpgradeDb(final DbSession session,
280 final String version, final boolean tryFix)
281 throws WaarpDatabaseNoConnectionException {
282
283 return DbModelFactoryR66.needUpgradeDbAllDb(dbTypeResolver, session,
284 version);
285 }
286
287 }