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 DbModelOracle extends goldengate.common.database.model.DbModelOracle {
47
48
49
50
51
52
53
54 public DbModelOracle(String dbserver, String dbuser, String dbpasswd) throws GoldenGateDatabaseNoConnectionException {
55 super(dbserver, dbuser, dbpasswd);
56 }
57
58 @Override
59 public void createTables(DbSession session) throws GoldenGateDatabaseNoConnectionException {
60
61 String createTableH2 = "CREATE TABLE ";
62 String constraint = " CONSTRAINT ";
63 String primaryKey = " PRIMARY KEY ";
64 String notNull = " NOT NULL ";
65
66
67 String action = createTableH2 + DbMultipleMonitor.table + "(";
68 DbMultipleMonitor.Columns[] mcolumns = DbMultipleMonitor.Columns
69 .values();
70 for (int i = 0; i < mcolumns.length - 1; i ++) {
71 action += mcolumns[i].name() +
72 DBType.getType(DbMultipleMonitor.dbTypes[i]) + notNull +
73 ", ";
74 }
75 action += mcolumns[mcolumns.length - 1].name() +
76 DBType.getType(DbMultipleMonitor.dbTypes[mcolumns.length - 1]) +
77 notNull + ",";
78 action += constraint+" conf_pk "+primaryKey+"("+mcolumns[mcolumns.length - 1].name()+"))";
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 notNull + ",";
113 action += constraint+" conf_pk "+primaryKey+"("+ccolumns[ccolumns.length - 1].name()+"))";
114 System.out.println(action);
115 request = new DbRequest(session);
116 try {
117 request.query(action);
118 } catch (GoldenGateDatabaseNoConnectionException e) {
119 e.printStackTrace();
120 return;
121 } catch (GoldenGateDatabaseSqlException e) {
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 notNull + ",";
137 action += constraint+" host_pk "+primaryKey+"("+hcolumns[hcolumns.length - 1].name()+"))";
138 System.out.println(action);
139 try {
140 request.query(action);
141 } catch (GoldenGateDatabaseNoConnectionException e) {
142 e.printStackTrace();
143 return;
144 } catch (GoldenGateDatabaseSqlException e) {
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 notNull + ",";
160 action += constraint+" rule_pk "+primaryKey+"("+rcolumns[rcolumns.length - 1].name()+"))";
161 System.out.println(action);
162 try {
163 request.query(action);
164 } catch (GoldenGateDatabaseNoConnectionException e) {
165 e.printStackTrace();
166 return;
167 } catch (GoldenGateDatabaseSqlException e) {
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 return;
194 } finally {
195 request.close();
196 }
197
198 action = "CREATE INDEX IDX_RUNNER ON "+ DbTaskRunner.table + "(";
199 DbTaskRunner.Columns[] icolumns = DbTaskRunner.indexes;
200 for (int i = 0; i < icolumns.length-1; i ++) {
201 action += icolumns[i].name()+ ", ";
202 }
203 action += icolumns[icolumns.length-1].name()+ ")";
204 System.out.println(action);
205 try {
206 request.query(action);
207 } catch (GoldenGateDatabaseNoConnectionException e) {
208 e.printStackTrace();
209 return;
210 } catch (GoldenGateDatabaseSqlException e) {
211 return;
212 } finally {
213 request.close();
214 }
215
216
217 action = "CREATE SEQUENCE " + DbTaskRunner.fieldseq +
218 " MINVALUE " + (DbConstant.ILLEGALVALUE + 1)+
219 " START WITH " + (DbConstant.ILLEGALVALUE + 1);
220 System.out.println(action);
221 try {
222 request.query(action);
223 } catch (GoldenGateDatabaseNoConnectionException e) {
224 e.printStackTrace();
225 return;
226 } catch (GoldenGateDatabaseSqlException e) {
227 return;
228 } finally {
229 request.close();
230 }
231 }
232
233
234
235
236
237
238 @Override
239 public void resetSequence(DbSession session, long newvalue) throws GoldenGateDatabaseNoConnectionException {
240 String action = "DROP SEQUENCE " + DbTaskRunner.fieldseq;
241 String action2 = "CREATE SEQUENCE " + DbTaskRunner.fieldseq +
242 " MINVALUE " + (DbConstant.ILLEGALVALUE + 1)+
243 " START WITH " + (newvalue);
244 DbRequest request = new DbRequest(session);
245 try {
246 request.query(action);
247 request.query(action2);
248 } catch (GoldenGateDatabaseNoConnectionException e) {
249 e.printStackTrace();
250 return;
251 } catch (GoldenGateDatabaseSqlException e) {
252 e.printStackTrace();
253 return;
254 } finally {
255 request.close();
256 }
257
258 System.out.println(action);
259 }
260
261
262
263
264
265
266 @Override
267 public long nextSequence(DbSession dbSession)
268 throws GoldenGateDatabaseNoConnectionException,
269 GoldenGateDatabaseSqlException, GoldenGateDatabaseNoDataException {
270 long result = DbConstant.ILLEGALVALUE;
271 String action = "SELECT " + DbTaskRunner.fieldseq + ".NEXTVAL FROM DUAL";
272 DbPreparedStatement preparedStatement = new DbPreparedStatement(
273 dbSession);
274 try {
275 preparedStatement.createPrepareStatement(action);
276
277 preparedStatement.executeQuery();
278 if (preparedStatement.getNext()) {
279 try {
280 result = preparedStatement.getResultSet().getLong(1);
281 } catch (SQLException e) {
282 throw new GoldenGateDatabaseSqlException(e);
283 }
284 return result;
285 } else {
286 throw new GoldenGateDatabaseNoDataException(
287 "No sequence found. Must be initialized first");
288 }
289 } finally {
290 preparedStatement.realClose();
291 }
292 }
293 }