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