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
33 import openr66.database.DbConstant;
34 import openr66.database.data.DbConfiguration;
35 import openr66.database.data.DbHostAuth;
36 import openr66.database.data.DbMultipleMonitor;
37 import openr66.database.data.DbRule;
38 import openr66.database.data.DbTaskRunner;
39 import openr66.protocol.configuration.Configuration;
40
41
42
43
44
45
46 public class DbModelH2 extends goldengate.common.database.model.DbModelH2 {
47
48
49
50
51
52
53
54 public DbModelH2(String dbserver, String dbuser, String dbpasswd) throws GoldenGateDatabaseNoConnectionException {
55 super(dbserver, dbuser, dbpasswd);
56 }
57 @Override
58 public void createTables(DbSession session) throws GoldenGateDatabaseNoConnectionException {
59
60 String createTableH2 = "CREATE TABLE IF NOT EXISTS ";
61 String primaryKey = " PRIMARY KEY ";
62 String notNull = " NOT NULL ";
63
64
65 String action = createTableH2 + DbMultipleMonitor.table + "(";
66 DbMultipleMonitor.Columns[] mcolumns = DbMultipleMonitor.Columns
67 .values();
68 for (int i = 0; i < mcolumns.length - 1; i ++) {
69 action += mcolumns[i].name() +
70 DBType.getType(DbMultipleMonitor.dbTypes[i]) + notNull +
71 ", ";
72 }
73 action += mcolumns[mcolumns.length - 1].name() +
74 DBType.getType(DbMultipleMonitor.dbTypes[mcolumns.length - 1]) +
75 primaryKey + ")";
76 System.out.println(action);
77 DbRequest request = new DbRequest(session);
78 try {
79 request.query(action);
80 } catch (GoldenGateDatabaseNoConnectionException e) {
81 e.printStackTrace();
82 return;
83 } catch (GoldenGateDatabaseSqlException e) {
84 e.printStackTrace();
85 return;
86 } finally {
87 request.close();
88 }
89 DbMultipleMonitor multipleMonitor = new DbMultipleMonitor(session,
90 Configuration.configuration.HOST_ID,0,0,0);
91 try {
92 if (!multipleMonitor.exist())
93 multipleMonitor.insert();
94 } catch (GoldenGateDatabaseException e1) {
95 e1.printStackTrace();
96 }
97
98
99 action = createTableH2 + DbConfiguration.table + "(";
100 DbConfiguration.Columns[] ccolumns = DbConfiguration.Columns
101 .values();
102 for (int i = 0; i < ccolumns.length - 1; i ++) {
103 action += ccolumns[i].name() +
104 DBType.getType(DbConfiguration.dbTypes[i]) + notNull +
105 ", ";
106 }
107 action += ccolumns[ccolumns.length - 1].name() +
108 DBType.getType(DbConfiguration.dbTypes[ccolumns.length - 1]) +
109 primaryKey + ")";
110 System.out.println(action);
111 request = new DbRequest(session);
112 try {
113 request.query(action);
114 } catch (GoldenGateDatabaseNoConnectionException e) {
115 e.printStackTrace();
116 return;
117 } catch (GoldenGateDatabaseSqlException e) {
118 e.printStackTrace();
119 return;
120 } finally {
121 request.close();
122 }
123
124
125 action = createTableH2 + DbHostAuth.table + "(";
126 DbHostAuth.Columns[] hcolumns = DbHostAuth.Columns.values();
127 for (int i = 0; i < hcolumns.length - 1; i ++) {
128 action += hcolumns[i].name() +
129 DBType.getType(DbHostAuth.dbTypes[i]) + notNull + ", ";
130 }
131 action += hcolumns[hcolumns.length - 1].name() +
132 DBType.getType(DbHostAuth.dbTypes[hcolumns.length - 1]) +
133 primaryKey + ")";
134 System.out.println(action);
135 try {
136 request.query(action);
137 } catch (GoldenGateDatabaseNoConnectionException e) {
138 e.printStackTrace();
139 return;
140 } catch (GoldenGateDatabaseSqlException e) {
141 e.printStackTrace();
142 return;
143 } finally {
144 request.close();
145 }
146
147
148 action = createTableH2 + DbRule.table + "(";
149 DbRule.Columns[] rcolumns = DbRule.Columns.values();
150 for (int i = 0; i < rcolumns.length - 1; i ++) {
151 action += rcolumns[i].name() +
152 DBType.getType(DbRule.dbTypes[i]) + ", ";
153 }
154 action += rcolumns[rcolumns.length - 1].name() +
155 DBType.getType(DbRule.dbTypes[rcolumns.length - 1]) +
156 primaryKey + ")";
157 System.out.println(action);
158 try {
159 request.query(action);
160 } catch (GoldenGateDatabaseNoConnectionException e) {
161 e.printStackTrace();
162 return;
163 } catch (GoldenGateDatabaseSqlException e) {
164 e.printStackTrace();
165 return;
166 } finally {
167 request.close();
168 }
169
170
171 action = createTableH2 + DbTaskRunner.table + "(";
172 DbTaskRunner.Columns[] acolumns = DbTaskRunner.Columns.values();
173 for (int i = 0; i < acolumns.length; i ++) {
174 action += acolumns[i].name() +
175 DBType.getType(DbTaskRunner.dbTypes[i]) + notNull + ", ";
176 }
177
178 action += " CONSTRAINT runner_pk " + primaryKey + "(";
179 for (int i = DbTaskRunner.NBPRKEY; i > 1; i--) {
180 action += acolumns[acolumns.length - i].name() + ",";
181 }
182 action += acolumns[acolumns.length - 1].name() + "))";
183 System.out.println(action);
184 try {
185 request.query(action);
186 } catch (GoldenGateDatabaseNoConnectionException e) {
187 e.printStackTrace();
188 return;
189 } catch (GoldenGateDatabaseSqlException e) {
190 e.printStackTrace();
191 return;
192 } finally {
193 request.close();
194 }
195
196 action = "CREATE INDEX IF NOT EXISTS IDX_RUNNER ON "+ DbTaskRunner.table + "(";
197 DbTaskRunner.Columns[] icolumns = DbTaskRunner.indexes;
198 for (int i = 0; i < icolumns.length-1; i ++) {
199 action += icolumns[i].name()+ ", ";
200 }
201 action += icolumns[icolumns.length-1].name()+ ")";
202 System.out.println(action);
203 try {
204 request.query(action);
205 } catch (GoldenGateDatabaseNoConnectionException e) {
206 e.printStackTrace();
207 return;
208 } catch (GoldenGateDatabaseSqlException e) {
209 return;
210 } finally {
211 request.close();
212 }
213
214
215 action = "CREATE SEQUENCE IF NOT EXISTS " + DbTaskRunner.fieldseq +
216 " START WITH " + (DbConstant.ILLEGALVALUE + 1);
217 System.out.println(action);
218 try {
219 request.query(action);
220 } catch (GoldenGateDatabaseNoConnectionException e) {
221 e.printStackTrace();
222 return;
223 } catch (GoldenGateDatabaseSqlException e) {
224 e.printStackTrace();
225 return;
226 } finally {
227 request.close();
228 }
229 }
230
231
232
233
234
235
236 @Override
237 public void resetSequence(DbSession session, long newvalue) throws GoldenGateDatabaseNoConnectionException {
238 String action = "ALTER SEQUENCE " + DbTaskRunner.fieldseq +
239 " RESTART WITH " + newvalue;
240 DbRequest request = new DbRequest(session);
241 try {
242 request.query(action);
243 } catch (GoldenGateDatabaseNoConnectionException e) {
244 e.printStackTrace();
245 return;
246 } catch (GoldenGateDatabaseSqlException e) {
247 e.printStackTrace();
248 return;
249 } finally {
250 request.close();
251 }
252 System.out.println(action);
253 }
254
255
256
257
258
259
260 @Override
261 public long nextSequence(DbSession dbSession)
262 throws GoldenGateDatabaseNoConnectionException,
263 GoldenGateDatabaseSqlException, GoldenGateDatabaseNoDataException {
264 long result = DbConstant.ILLEGALVALUE;
265 String action = "SELECT NEXTVAL('" + DbTaskRunner.fieldseq + "')";
266 DbPreparedStatement preparedStatement = new DbPreparedStatement(
267 dbSession);
268 try {
269 preparedStatement.createPrepareStatement(action);
270
271 preparedStatement.executeQuery();
272 if (preparedStatement.getNext()) {
273 try {
274 result = preparedStatement.getResultSet().getLong(1);
275 } catch (SQLException e) {
276 throw new GoldenGateDatabaseSqlException(e);
277 }
278 return result;
279 } else {
280 throw new GoldenGateDatabaseNoDataException(
281 "No sequence found. Must be initialized first");
282 }
283 } finally {
284 preparedStatement.realClose();
285 }
286 }
287 }