1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package openr66.database.model;
22
23 import goldengate.common.database.DbPreparedStatement;
24 import goldengate.common.database.DbRequest;
25 import goldengate.common.database.DbSession;
26 import goldengate.common.database.exception.GoldenGateDatabaseException;
27 import goldengate.common.database.exception.GoldenGateDatabaseNoConnectionException;
28 import goldengate.common.database.exception.GoldenGateDatabaseNoDataException;
29 import goldengate.common.database.exception.GoldenGateDatabaseSqlException;
30
31 import java.sql.SQLException;
32 import java.util.concurrent.locks.ReentrantLock;
33
34 import openr66.database.DbConstant;
35 import openr66.database.data.DbConfiguration;
36 import openr66.database.data.DbHostAuth;
37 import openr66.database.data.DbMultipleMonitor;
38 import openr66.database.data.DbRule;
39 import openr66.database.data.DbTaskRunner;
40 import openr66.protocol.configuration.Configuration;
41
42
43
44
45
46
47 public class DbModelMysql extends goldengate.common.database.model.DbModelMysql {
48
49
50
51
52
53
54
55 public DbModelMysql(String dbserver, String dbuser, String dbpasswd) throws GoldenGateDatabaseNoConnectionException {
56 super(dbserver, dbuser, dbpasswd);
57 }
58 private final ReentrantLock lock = new ReentrantLock();
59
60 @Override
61 public void createTables(DbSession session) throws GoldenGateDatabaseNoConnectionException {
62
63 String createTableH2 = "CREATE TABLE IF NOT EXISTS ";
64 String primaryKey = " PRIMARY KEY ";
65 String notNull = " NOT NULL ";
66
67
68 String action = createTableH2 + DbMultipleMonitor.table + "(";
69 DbMultipleMonitor.Columns[] mcolumns = DbMultipleMonitor.Columns
70 .values();
71 for (int i = 0; i < mcolumns.length - 1; i ++) {
72 action += mcolumns[i].name() +
73 DBType.getType(DbMultipleMonitor.dbTypes[i]) + notNull +
74 ", ";
75 }
76 action += mcolumns[mcolumns.length - 1].name() +
77 DBType.getType(DbMultipleMonitor.dbTypes[mcolumns.length - 1]) +
78 primaryKey + ")";
79 System.out.println(action);
80 DbRequest request = new DbRequest(session);
81 try {
82 request.query(action);
83 } catch (GoldenGateDatabaseNoConnectionException e) {
84 e.printStackTrace();
85 return;
86 } catch (GoldenGateDatabaseSqlException e) {
87 e.printStackTrace();
88 return;
89 } finally {
90 request.close();
91 }
92 DbMultipleMonitor multipleMonitor = new DbMultipleMonitor(session,
93 Configuration.configuration.HOST_ID,0,0,0);
94 try {
95 if (!multipleMonitor.exist())
96 multipleMonitor.insert();
97 } catch (GoldenGateDatabaseException e1) {
98 e1.printStackTrace();
99 }
100
101
102 action = createTableH2 + DbConfiguration.table + "(";
103 DbConfiguration.Columns[] ccolumns = DbConfiguration.Columns
104 .values();
105 for (int i = 0; i < ccolumns.length - 1; i ++) {
106 action += ccolumns[i].name() +
107 DBType.getType(DbConfiguration.dbTypes[i]) + notNull +
108 ", ";
109 }
110 action += ccolumns[ccolumns.length - 1].name() +
111 DBType.getType(DbConfiguration.dbTypes[ccolumns.length - 1]) +
112 primaryKey + ")";
113 System.out.println(action);
114 request = new DbRequest(session);
115 try {
116 request.query(action);
117 } catch (GoldenGateDatabaseNoConnectionException e) {
118 e.printStackTrace();
119 return;
120 } catch (GoldenGateDatabaseSqlException e) {
121 e.printStackTrace();
122 return;
123 } finally {
124 request.close();
125 }
126
127
128 action = createTableH2 + DbHostAuth.table + "(";
129 DbHostAuth.Columns[] hcolumns = DbHostAuth.Columns.values();
130 for (int i = 0; i < hcolumns.length - 1; i ++) {
131 action += hcolumns[i].name() +
132 DBType.getType(DbHostAuth.dbTypes[i]) + notNull + ", ";
133 }
134 action += hcolumns[hcolumns.length - 1].name() +
135 DBType.getType(DbHostAuth.dbTypes[hcolumns.length - 1]) +
136 primaryKey + ")";
137 System.out.println(action);
138 try {
139 request.query(action);
140 } catch (GoldenGateDatabaseNoConnectionException e) {
141 e.printStackTrace();
142 return;
143 } catch (GoldenGateDatabaseSqlException e) {
144 e.printStackTrace();
145 return;
146 } finally {
147 request.close();
148 }
149
150
151 action = createTableH2 + DbRule.table + "(";
152 DbRule.Columns[] rcolumns = DbRule.Columns.values();
153 for (int i = 0; i < rcolumns.length - 1; i ++) {
154 action += rcolumns[i].name() +
155 DBType.getType(DbRule.dbTypes[i]) + ", ";
156 }
157 action += rcolumns[rcolumns.length - 1].name() +
158 DBType.getType(DbRule.dbTypes[rcolumns.length - 1]) +
159 primaryKey + ")";
160 System.out.println(action);
161 try {
162 request.query(action);
163 } catch (GoldenGateDatabaseNoConnectionException e) {
164 e.printStackTrace();
165 return;
166 } catch (GoldenGateDatabaseSqlException e) {
167 e.printStackTrace();
168 return;
169 } finally {
170 request.close();
171 }
172
173
174 action = createTableH2 + DbTaskRunner.table + "(";
175 DbTaskRunner.Columns[] acolumns = DbTaskRunner.Columns.values();
176 for (int i = 0; i < acolumns.length; i ++) {
177 action += acolumns[i].name() +
178 DBType.getType(DbTaskRunner.dbTypes[i]) + notNull + ", ";
179 }
180
181 action += " CONSTRAINT runner_pk " + primaryKey + "(";
182 for (int i = DbTaskRunner.NBPRKEY; i > 1; i--) {
183 action += acolumns[acolumns.length - i].name() + ",";
184 }
185 action += acolumns[acolumns.length - 1].name() + "))";
186 System.out.println(action);
187 try {
188 request.query(action);
189 } catch (GoldenGateDatabaseNoConnectionException e) {
190 e.printStackTrace();
191 return;
192 } catch (GoldenGateDatabaseSqlException e) {
193 e.printStackTrace();
194 return;
195 } finally {
196 request.close();
197 }
198
199 action = "CREATE INDEX IDX_RUNNER ON "+ DbTaskRunner.table + "(";
200 DbTaskRunner.Columns[] icolumns = DbTaskRunner.indexes;
201 for (int i = 0; i < icolumns.length-1; i ++) {
202 action += icolumns[i].name()+ ", ";
203 }
204 action += icolumns[icolumns.length-1].name()+ ")";
205 System.out.println(action);
206 try {
207 request.query(action);
208 } catch (GoldenGateDatabaseNoConnectionException e) {
209 e.printStackTrace();
210 return;
211 } catch (GoldenGateDatabaseSqlException e) {
212 return;
213 } finally {
214 request.close();
215 }
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237 action = "CREATE TABLE Sequences (name VARCHAR(22) NOT NULL PRIMARY KEY,"+
238 "seq BIGINT NOT NULL)";
239 System.out.println(action);
240 try {
241 request.query(action);
242 } catch (GoldenGateDatabaseNoConnectionException e) {
243 e.printStackTrace();
244 return;
245 } catch (GoldenGateDatabaseSqlException e) {
246 e.printStackTrace();
247 return;
248 } finally {
249 request.close();
250 }
251 action = "INSERT INTO Sequences (name, seq) VALUES ('"+DbTaskRunner.fieldseq+"', "+
252 (DbConstant.ILLEGALVALUE + 1)+")";
253 System.out.println(action);
254 try {
255 request.query(action);
256 } catch (GoldenGateDatabaseNoConnectionException e) {
257 e.printStackTrace();
258 return;
259 } catch (GoldenGateDatabaseSqlException e) {
260 e.printStackTrace();
261 return;
262 } finally {
263 request.close();
264 }
265 }
266
267
268
269
270
271
272 @Override
273 public void resetSequence(DbSession session, long newvalue) throws GoldenGateDatabaseNoConnectionException {
274 String action = "UPDATE Sequences SET seq = " + newvalue+
275 " WHERE name = '"+ DbTaskRunner.fieldseq + "'";
276 DbRequest request = new DbRequest(session);
277 try {
278 request.query(action);
279 } catch (GoldenGateDatabaseNoConnectionException e) {
280 e.printStackTrace();
281 return;
282 } catch (GoldenGateDatabaseSqlException e) {
283 e.printStackTrace();
284 return;
285 } finally {
286 request.close();
287 }
288 System.out.println(action);
289 }
290
291
292
293
294
295
296 @Override
297 public synchronized long nextSequence(DbSession dbSession)
298 throws GoldenGateDatabaseNoConnectionException,
299 GoldenGateDatabaseSqlException, GoldenGateDatabaseNoDataException {
300 lock.lock();
301 try {
302 long result = DbConstant.ILLEGALVALUE;
303 String action = "SELECT seq FROM Sequences WHERE name = '" +
304 DbTaskRunner.fieldseq + "' FOR UPDATE";
305 DbPreparedStatement preparedStatement = new DbPreparedStatement(
306 dbSession);
307 try {
308 dbSession.conn.setAutoCommit(false);
309 } catch (SQLException e1) {
310 }
311 try {
312 preparedStatement.createPrepareStatement(action);
313
314 preparedStatement.executeQuery();
315 if (preparedStatement.getNext()) {
316 try {
317 result = preparedStatement.getResultSet().getLong(1);
318 } catch (SQLException e) {
319 throw new GoldenGateDatabaseSqlException(e);
320 }
321 } else {
322 throw new GoldenGateDatabaseNoDataException(
323 "No sequence found. Must be initialized first");
324 }
325 } finally {
326 preparedStatement.realClose();
327 }
328 action = "UPDATE Sequences SET seq = "+(result+1)+
329 " WHERE name = '"+DbTaskRunner.fieldseq+"'";
330 try {
331 preparedStatement.createPrepareStatement(action);
332
333 preparedStatement.executeUpdate();
334 } finally {
335 preparedStatement.realClose();
336 }
337 return result;
338 } finally {
339 try {
340 dbSession.conn.setAutoCommit(true);
341 } catch (SQLException e1) {
342 }
343 lock.unlock();
344 }
345 }
346 }