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