1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package goldengate.common.database.model;
22
23 import goldengate.common.logging.GgInternalLogger;
24 import goldengate.common.logging.GgInternalLoggerFactory;
25
26 import java.sql.Connection;
27 import java.sql.DriverManager;
28 import java.sql.SQLException;
29 import java.sql.Types;
30
31 import org.jboss.netty.util.Timer;
32
33 import oracle.jdbc.pool.OracleConnectionPoolDataSource;
34
35 import goldengate.common.database.DbAdmin;
36 import goldengate.common.database.DbConnectionPool;
37 import goldengate.common.database.DbConstant;
38 import goldengate.common.database.DbPreparedStatement;
39 import goldengate.common.database.DbRequest;
40 import goldengate.common.database.DbSession;
41 import goldengate.common.database.data.DbDataModel;
42 import goldengate.common.database.exception.GoldenGateDatabaseNoConnectionException;
43 import goldengate.common.database.exception.GoldenGateDatabaseNoDataException;
44 import goldengate.common.database.exception.GoldenGateDatabaseSqlException;
45
46
47
48
49
50
51 public abstract class DbModelOracle extends DbModelAbstract {
52
53
54
55 private static final GgInternalLogger logger = GgInternalLoggerFactory
56 .getLogger(DbModelOracle.class);
57
58 public static DbType type = DbType.Oracle;
59
60 protected static OracleConnectionPoolDataSource oracleConnectionPoolDataSource;
61 protected static DbConnectionPool pool;
62
63
64
65
66 @Override
67 public DbType getDbType() {
68 return type;
69 }
70
71
72
73
74
75
76
77
78
79
80 public DbModelOracle(String dbserver, String dbuser, String dbpasswd, Timer timer, long delay) throws GoldenGateDatabaseNoConnectionException {
81 this();
82
83 try {
84 oracleConnectionPoolDataSource = new OracleConnectionPoolDataSource();
85 } catch (SQLException e) {
86
87 oracleConnectionPoolDataSource = null;
88 return;
89 }
90 oracleConnectionPoolDataSource.setURL(dbserver);
91 oracleConnectionPoolDataSource.setUser(dbuser);
92 oracleConnectionPoolDataSource.setPassword(dbpasswd);
93 pool = new DbConnectionPool(oracleConnectionPoolDataSource, timer, delay);
94 logger.warn("Some info: MaxConn: "+pool.getMaxConnections()+" LogTimeout: "+pool.getLoginTimeout()
95 + " ForceClose: "+pool.getTimeoutForceClose());
96 }
97
98
99
100
101
102
103
104
105
106 public DbModelOracle(String dbserver, String dbuser, String dbpasswd) throws GoldenGateDatabaseNoConnectionException {
107 this();
108
109 try {
110 oracleConnectionPoolDataSource = new OracleConnectionPoolDataSource();
111 } catch (SQLException e) {
112
113 oracleConnectionPoolDataSource = null;
114 return;
115 }
116 oracleConnectionPoolDataSource.setURL(dbserver);
117 oracleConnectionPoolDataSource.setUser(dbuser);
118 oracleConnectionPoolDataSource.setPassword(dbpasswd);
119 pool = new DbConnectionPool(oracleConnectionPoolDataSource);
120 logger.warn("Some info: MaxConn: "+pool.getMaxConnections()+" LogTimeout: "+pool.getLoginTimeout()
121 + " ForceClose: "+pool.getTimeoutForceClose());
122 }
123
124
125
126
127
128
129 protected DbModelOracle() throws GoldenGateDatabaseNoConnectionException {
130 if (DbModelFactory.classLoaded) {
131 return;
132 }
133 try {
134 DriverManager
135 .registerDriver(new oracle.jdbc.OracleDriver());
136 DbModelFactory.classLoaded = true;
137 } catch (SQLException e) {
138
139 logger.error("Cannot register Driver " + type.name()+ "\n"+e.getMessage());
140 DbSession.error(e);
141 throw new GoldenGateDatabaseNoConnectionException(
142 "Cannot load database drive:" + type.name(), e);
143 }
144 }
145
146 @Override
147 public void releaseResources() {
148 try {
149 if (pool != null) {
150 pool.dispose();
151 pool = null;
152 }
153 } catch (SQLException e) {
154 }
155 }
156
157 @Override
158 public int currentNumberOfPooledConnections() {
159 if (pool != null)
160 return pool.getActiveConnections();
161 return DbAdmin.getNbConnection();
162 }
163
164 @Override
165 public Connection getDbConnection(String server, String user, String passwd)
166 throws SQLException {
167 if (pool == null) {
168 return super.getDbConnection(server, user, passwd);
169 }
170 return pool.getConnection();
171 }
172
173 protected static enum DBType {
174 CHAR(Types.CHAR, " CHAR(3) "),
175 VARCHAR(Types.VARCHAR, " VARCHAR2(254) "),
176 LONGVARCHAR(Types.LONGVARCHAR, " CLOB "),
177 BIT(Types.BIT, " CHAR(1) "),
178 TINYINT(Types.TINYINT, " SMALLINT "),
179 SMALLINT(Types.SMALLINT, " SMALLINT "),
180 INTEGER(Types.INTEGER, " INTEGER "),
181 BIGINT(Types.BIGINT, " NUMBER(38,0) "),
182 REAL(Types.REAL, " REAL "),
183 DOUBLE(Types.DOUBLE, " DOUBLE PRECISION "),
184 VARBINARY(Types.VARBINARY, " BLOB "),
185 DATE(Types.DATE, " DATE "),
186 TIMESTAMP(Types.TIMESTAMP, " TIMESTAMP ");
187
188 public int type;
189
190 public String constructor;
191
192 private DBType(int type, String constructor) {
193 this.type = type;
194 this.constructor = constructor;
195 }
196
197 public static String getType(int sqltype) {
198 switch (sqltype) {
199 case Types.CHAR:
200 return CHAR.constructor;
201 case Types.VARCHAR:
202 return VARCHAR.constructor;
203 case Types.LONGVARCHAR:
204 return LONGVARCHAR.constructor;
205 case Types.BIT:
206 return BIT.constructor;
207 case Types.TINYINT:
208 return TINYINT.constructor;
209 case Types.SMALLINT:
210 return SMALLINT.constructor;
211 case Types.INTEGER:
212 return INTEGER.constructor;
213 case Types.BIGINT:
214 return BIGINT.constructor;
215 case Types.REAL:
216 return REAL.constructor;
217 case Types.DOUBLE:
218 return DOUBLE.constructor;
219 case Types.VARBINARY:
220 return VARBINARY.constructor;
221 case Types.DATE:
222 return DATE.constructor;
223 case Types.TIMESTAMP:
224 return TIMESTAMP.constructor;
225 default:
226 return null;
227 }
228 }
229 }
230
231 @Override
232 public void createTables(DbSession session) throws GoldenGateDatabaseNoConnectionException {
233
234 String createTableH2 = "CREATE TABLE ";
235 String constraint = " CONSTRAINT ";
236 String primaryKey = " PRIMARY KEY ";
237 String notNull = " NOT NULL ";
238
239
240 String action = createTableH2 + DbDataModel.table + "(";
241 DbDataModel.Columns[] ccolumns = DbDataModel.Columns
242 .values();
243 for (int i = 0; i < ccolumns.length - 1; i ++) {
244 action += ccolumns[i].name() +
245 DBType.getType(DbDataModel.dbTypes[i]) + notNull +
246 ", ";
247 }
248 action += ccolumns[ccolumns.length - 1].name() +
249 DBType.getType(DbDataModel.dbTypes[ccolumns.length - 1]) +
250 notNull + ",";
251 action += constraint+" conf_pk "+primaryKey+"("+ccolumns[ccolumns.length - 1].name()+"))";
252 logger.warn(action);
253 DbRequest request = new DbRequest(session);
254 try {
255 request.query(action);
256 } catch (GoldenGateDatabaseNoConnectionException e) {
257 logger.warn("CreateTables Error", e);
258 return;
259 } catch (GoldenGateDatabaseSqlException e) {
260 return;
261 } finally {
262 request.close();
263 }
264
265 action = "CREATE INDEX IDX_RUNNER ON "+ DbDataModel.table + "(";
266 DbDataModel.Columns[] icolumns = DbDataModel.indexes;
267 for (int i = 0; i < icolumns.length-1; i ++) {
268 action += icolumns[i].name()+ ", ";
269 }
270 action += icolumns[icolumns.length-1].name()+ ")";
271 logger.warn(action);
272 try {
273 request.query(action);
274 } catch (GoldenGateDatabaseNoConnectionException e) {
275 logger.warn("CreateTables Error", e);
276 return;
277 } catch (GoldenGateDatabaseSqlException e) {
278 return;
279 } finally {
280 request.close();
281 }
282
283
284 action = "CREATE SEQUENCE " + DbDataModel.fieldseq +
285 " MINVALUE " + (DbConstant.ILLEGALVALUE + 1)+
286 " START WITH " + (DbConstant.ILLEGALVALUE + 1);
287 logger.warn(action);
288 try {
289 request.query(action);
290 } catch (GoldenGateDatabaseNoConnectionException e) {
291 logger.warn("CreateTable Error", e);
292 return;
293 } catch (GoldenGateDatabaseSqlException e) {
294 return;
295 } finally {
296 request.close();
297 }
298 }
299
300
301
302
303
304
305 @Override
306 public void resetSequence(DbSession session, long newvalue) throws GoldenGateDatabaseNoConnectionException {
307 String action = "DROP SEQUENCE " + DbDataModel.fieldseq;
308 String action2 = "CREATE SEQUENCE " + DbDataModel.fieldseq +
309 " MINVALUE " + (DbConstant.ILLEGALVALUE + 1)+
310 " START WITH " + (newvalue);
311 DbRequest request = new DbRequest(session);
312 try {
313 request.query(action);
314 request.query(action2);
315 } catch (GoldenGateDatabaseNoConnectionException e) {
316 logger.warn("ResetSequence Error", e);
317 return;
318 } catch (GoldenGateDatabaseSqlException e) {
319 logger.warn("ResetSequence Error", e);
320 return;
321 } finally {
322 request.close();
323 }
324
325 logger.warn(action);
326 }
327
328
329
330
331
332
333 @Override
334 public long nextSequence(DbSession dbSession)
335 throws GoldenGateDatabaseNoConnectionException,
336 GoldenGateDatabaseSqlException, GoldenGateDatabaseNoDataException {
337 long result = DbConstant.ILLEGALVALUE;
338 String action = "SELECT " + DbDataModel.fieldseq + ".NEXTVAL FROM DUAL";
339 DbPreparedStatement preparedStatement = new DbPreparedStatement(
340 dbSession);
341 try {
342 preparedStatement.createPrepareStatement(action);
343
344 preparedStatement.executeQuery();
345 if (preparedStatement.getNext()) {
346 try {
347 result = preparedStatement.getResultSet().getLong(1);
348 } catch (SQLException e) {
349 throw new GoldenGateDatabaseSqlException(e);
350 }
351 return result;
352 } else {
353 throw new GoldenGateDatabaseNoDataException(
354 "No sequence found. Must be initialized first");
355 }
356 } finally {
357 preparedStatement.realClose();
358 }
359 }
360
361
362
363
364 @Override
365 protected String validConnectionString() {
366 return "select 1 from dual";
367 }
368
369
370
371
372 @Override
373 public String limitRequest(String allfields, String request, int nb) {
374 return "select "+allfields+" from ( "+request+" ) where rownum <= "+nb;
375 }
376 }