View Javadoc

1   /**
2      This file is part of GoldenGate Project (named also GoldenGate or GG).
3   
4      Copyright 2009, Frederic Bregier, and individual contributors by the @author
5      tags. See the COPYRIGHT.txt in the distribution for a full listing of
6      individual contributors.
7   
8      All GoldenGate Project is free software: you can redistribute it and/or 
9      modify it under the terms of the GNU General Public License as published 
10     by the Free Software Foundation, either version 3 of the License, or
11     (at your option) any later version.
12  
13     GoldenGate is distributed in the hope that it will be useful,
14     but WITHOUT ANY WARRANTY; without even the implied warranty of
15     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16     GNU General Public License for more details.
17  
18     You should have received a copy of the GNU General Public License
19     along with GoldenGate .  If not, see <http://www.gnu.org/licenses/>.
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   * MySQL Database Model implementation
49   * @author Frederic Bregier
50   *
51   */
52  public abstract class DbModelMysql extends DbModelAbstract {
53      /**
54       * Internal Logger
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      /* (non-Javadoc)
65       * @see goldengate.common.database.model.DbModel#getDbType()
66       */
67      @Override
68      public DbType getDbType() {
69          return type;
70      }
71      
72      /**
73       * Create the object and initialize if necessary the driver
74       * @param dbserver
75       * @param dbuser
76       * @param dbpasswd
77       * @param timer
78       * @param delay
79       * @throws GoldenGateDatabaseNoConnectionException
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          // Create a pool with no limit
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       * Create the object and initialize if necessary the driver
94       * @param dbserver
95       * @param dbuser
96       * @param dbpasswd
97       * @throws GoldenGateDatabaseNoConnectionException
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         // Create a pool with no limit
106         pool = new DbConnectionPool(mysqlConnectionPoolDataSource); 
107         logger.warn("Some info: MaxConn: "+pool.getMaxConnections()+" LogTimeout: "+pool.getLoginTimeout()
108                 + " ForceClose: "+pool.getTimeoutForceClose());
109     }
110     /**
111      * Create the object and initialize if necessary the driver
112      * @throws GoldenGateDatabaseNoConnectionException
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          // SQLException
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         // Create tables: configuration, hosts, rules, runner, cptrunner
219         String createTableH2 = "CREATE TABLE IF NOT EXISTS ";
220         String primaryKey = " PRIMARY KEY ";
221         String notNull = " NOT NULL ";
222 
223         // Example
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         // Index Example
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         // example sequence
268         /*
269          * # Table to handle any number of sequences:
270             CREATE TABLE Sequences (
271               name VARCHAR(22) NOT NULL,
272               seq INT UNSIGNED NOT NULL,  # (or BIGINT)
273               PRIMARY KEY name
274             );
275 
276             # Create a Sequence:
277             INSERT INTO Sequences (name, seq) VALUES (?, 0);
278             # Drop a Sequence:
279             DELETE FROM Sequences WHERE name = ?;
280 
281             # Get a sequence number:
282             UPDATE Sequences
283               SET seq = LAST_INSERT_ID(seq + 1)
284               WHERE name = ?;
285             $seq = $db->LastInsertId();
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      * (non-Javadoc)
319      *
320      * @see openr66.database.model.DbModel#resetSequence()
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      * (non-Javadoc)
343      *
344      * @see openr66.database.model.DbModel#nextSequence()
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                 // Limit the search
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                 // Limit the search
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     /* (non-Javadoc)
399      * @see goldengate.common.database.model.DbModelAbstract#validConnectionString()
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 }