View Javadoc

1   /*
2    * SymmetricDS is an open source database synchronization solution.
3    *   
4    * Copyright (C) Chris Henson <chenson42@users.sourceforge.net>,
5    *               Eric Long <erilong@users.sourceforge.net>
6    *
7    * This library is free software; you can redistribute it and/or
8    * modify it under the terms of the GNU Lesser General Public
9    * License as published by the Free Software Foundation; either
10   * version 3 of the License, or (at your option) any later version.
11   *
12   * This library is distributed in the hope that it will be useful,
13   * but WITHOUT ANY WARRANTY; without even the implied warranty of
14   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
15   * Lesser General Public License for more details.
16   *
17   * You should have received a copy of the GNU Lesser General Public
18   * License along with this library; if not, see
19   * <http://www.gnu.org/licenses/>.
20   */
21  
22  package org.jumpmind.symmetric.db;
23  
24  import java.io.IOException;
25  import java.io.InputStreamReader;
26  import java.io.StringReader;
27  import java.io.StringWriter;
28  import java.net.URL;
29  import java.sql.Connection;
30  import java.sql.DatabaseMetaData;
31  import java.sql.PreparedStatement;
32  import java.sql.ResultSet;
33  import java.sql.ResultSetMetaData;
34  import java.sql.SQLException;
35  import java.sql.Statement;
36  import java.sql.Types;
37  import java.util.ArrayList;
38  import java.util.Collection;
39  import java.util.HashMap;
40  import java.util.Iterator;
41  import java.util.List;
42  import java.util.Map;
43  
44  import org.apache.commons.collections.map.ListOrderedMap;
45  import org.apache.commons.lang.StringUtils;
46  import org.apache.commons.logging.Log;
47  import org.apache.commons.logging.LogFactory;
48  import org.apache.ddlutils.Platform;
49  import org.apache.ddlutils.io.DatabaseIO;
50  import org.apache.ddlutils.model.Column;
51  import org.apache.ddlutils.model.Database;
52  import org.apache.ddlutils.model.ForeignKey;
53  import org.apache.ddlutils.model.Index;
54  import org.apache.ddlutils.model.IndexColumn;
55  import org.apache.ddlutils.model.NonUniqueIndex;
56  import org.apache.ddlutils.model.Table;
57  import org.apache.ddlutils.model.UniqueIndex;
58  import org.apache.ddlutils.platform.DatabaseMetaDataWrapper;
59  import org.apache.ddlutils.platform.MetaDataColumnDescriptor;
60  import org.jumpmind.symmetric.common.ParameterConstants;
61  import org.jumpmind.symmetric.db.mssql.MsSqlDbDialect;
62  import org.jumpmind.symmetric.load.IColumnFilter;
63  import org.jumpmind.symmetric.model.DataEventType;
64  import org.jumpmind.symmetric.model.Node;
65  import org.jumpmind.symmetric.model.Trigger;
66  import org.jumpmind.symmetric.model.TriggerHistory;
67  import org.jumpmind.symmetric.service.IParameterService;
68  import org.springframework.dao.DataAccessException;
69  import org.springframework.jdbc.core.ConnectionCallback;
70  import org.springframework.jdbc.core.JdbcTemplate;
71  import org.springframework.jdbc.core.PreparedStatementCallback;
72  import org.springframework.jdbc.core.StatementCallback;
73  import org.springframework.jdbc.support.JdbcUtils;
74  import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
75  import org.springframework.transaction.TransactionStatus;
76  import org.springframework.transaction.support.TransactionCallback;
77  import org.springframework.transaction.support.TransactionCallbackWithoutResult;
78  import org.springframework.transaction.support.TransactionTemplate;
79  
80  abstract public class AbstractDbDialect implements IDbDialect {
81  
82      static final Log logger = LogFactory.getLog(AbstractDbDialect.class);
83  
84      public static final int MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE = 50;
85  
86      protected JdbcTemplate jdbcTemplate;
87  
88      protected Platform platform;
89  
90      protected Database cachedModel = new Database();
91  
92      protected SqlTemplate sqlTemplate;
93  
94      protected SQLErrorCodeSQLExceptionTranslator sqlErrorTranslator;
95  
96      private Map<Integer, String> _defaultSizes;
97  
98      private IParameterService parameterService;
99  
100     protected String tablePrefix;
101 
102     private int streamingResultsFetchSize;
103 
104     private Boolean supportsGetGeneratedKeys;
105 
106     protected TransactionTemplate transactionTemplate;
107 
108     private String databaseName;
109 
110     private int databaseMajorVersion;
111 
112     private int databaseMinorVersion;
113     
114     private String databaseProductVersion;
115 
116     private String identifierQuoteString;
117     
118     protected AbstractDbDialect() {
119         _defaultSizes = new HashMap<Integer, String>();
120         _defaultSizes.put(new Integer(1), "254");
121         _defaultSizes.put(new Integer(12), "254");
122         _defaultSizes.put(new Integer(-1), "254");
123         _defaultSizes.put(new Integer(-2), "254");
124         _defaultSizes.put(new Integer(-3), "254");
125         _defaultSizes.put(new Integer(-4), "254");
126         _defaultSizes.put(new Integer(4), "32");
127         _defaultSizes.put(new Integer(-5), "64");
128         _defaultSizes.put(new Integer(7), "7,0");
129         _defaultSizes.put(new Integer(6), "15,0");
130         _defaultSizes.put(new Integer(8), "15,0");
131         _defaultSizes.put(new Integer(3), "15,15");
132         _defaultSizes.put(new Integer(2), "15,15");
133     }
134 
135     public IColumnFilter getDatabaseColumnFilter() {
136         return null;
137     }
138 
139     public void prepareTableForDataLoad(Table table) {
140     }
141 
142     public void cleanupAfterDataLoad(Table table) {
143     }
144 
145     protected boolean allowsNullForIdentityColumn() {
146         return true;
147     }
148 
149     /***
150      * Provide a default implementation of this method using DDLUtils,
151      * getMaxColumnNameLength()
152      */
153     public int getMaxTriggerNameLength() {
154         int max = getPlatform().getPlatformInfo().getMaxColumnNameLength();
155         return max < MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE ? max : MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE;
156     }
157 
158     public void init(Platform pf) {
159         this.jdbcTemplate = new JdbcTemplate(pf.getDataSource());
160         this.platform = pf;
161         this.sqlErrorTranslator = new SQLErrorCodeSQLExceptionTranslator(pf.getDataSource());
162         this.identifierQuoteString = "\"";
163         jdbcTemplate.execute(new ConnectionCallback() {
164             public Object doInConnection(Connection c) throws SQLException, DataAccessException {
165                 DatabaseMetaData meta = c.getMetaData();
166                 databaseName = meta.getDatabaseProductName();
167                 databaseMajorVersion = meta.getDatabaseMajorVersion();
168                 databaseMinorVersion = meta.getDatabaseMinorVersion();
169                 databaseProductVersion = meta.getDatabaseProductVersion();
170                 return null;
171             }
172         });
173     }
174 
175     abstract protected void initForSpecificDialect();
176 
177     public void initConfigDb() {
178         initForSpecificDialect();
179         addPrefixAndCreateTablesIfNecessary(getConfigDdlDatabase());
180         createRequiredFunctions();
181     }
182 
183     final public boolean doesTriggerExist(String catalogName, String schema, String tableName, String triggerName) {
184         try {
185             return doesTriggerExistOnPlatform(catalogName, schema, tableName, triggerName);
186         } catch (Exception ex) {
187             logger.warn("Could not figure out if the trigger exists.  Assuming that is does not.", ex);
188             return false;
189         }
190     }
191 
192     protected void createRequiredFunctions() {
193         String[] functions = sqlTemplate.getFunctionsToInstall();
194         for (String funcName : functions) {
195             if (jdbcTemplate.queryForInt(sqlTemplate.getFunctionInstalledSql(funcName)) == 0) {
196                 jdbcTemplate.update(sqlTemplate.getFunctionSql(funcName));
197                 logger.info("Just installed " + funcName);
198             }
199         }
200     }
201 
202     public BinaryEncoding getBinaryEncoding() {
203         return BinaryEncoding.NONE;
204     }
205 
206     public boolean isBlobOverrideToBinary() {
207         return false;
208     }
209 
210     public boolean isDateOverrideToTimestamp() {
211         return false;
212     }
213 
214     abstract protected boolean doesTriggerExistOnPlatform(String catalogName, String schema, String tableName,
215             String triggerName);
216 
217     public String getTransactionTriggerExpression(Trigger trigger) {
218         return "null";
219     }
220 
221     public String createInitalLoadSqlFor(Node node, Trigger trigger) {
222         return sqlTemplate.createInitalLoadSql(
223                 node,
224                 this,
225                 trigger,
226                 getMetaDataFor(trigger.getSourceCatalogName(), trigger.getSourceSchemaName(), trigger
227                         .getSourceTableName(), true)).trim();
228     }
229 
230     public String createPurgeSqlFor(Node node, Trigger trigger, TriggerHistory hist) {
231         return sqlTemplate.createPurgeSql(node, this, trigger, hist);
232     }
233 
234     public String createCsvDataSql(Trigger trigger, String whereClause) {
235         return sqlTemplate.createCsvDataSql(
236                 trigger,
237                 getMetaDataFor(trigger.getSourceCatalogName(), trigger.getSourceSchemaName(), trigger
238                         .getSourceTableName(), true), whereClause).trim();
239     }
240 
241     public String createCsvPrimaryKeySql(Trigger trigger, String whereClause) {
242         return sqlTemplate.createCsvPrimaryKeySql(
243                 trigger,
244                 getMetaDataFor(trigger.getSourceCatalogName(), trigger.getSourceSchemaName(), trigger
245                         .getSourceTableName(), true), whereClause).trim();
246     }
247 
248     /***
249      * This method uses the ddlutil's model reader which uses the jdbc metadata
250      * to lookup up table metadata. <p/> Dialect may optionally override this
251      * method to more efficiently lookup up table metadata directly against
252      * information schemas.
253      */
254     public Table getMetaDataFor(String catalogName, String schemaName, String tableName, boolean useCache) {
255         Table retTable = cachedModel.findTable(tableName);
256         if (retTable == null || !useCache) {
257             synchronized (this.getClass()) {
258                 try {
259                     Table table = findTable(catalogName, schemaName, tableName);
260 
261                     if (retTable != null) {
262                         cachedModel.removeTable(retTable);
263                     }
264 
265                     if (table != null) {
266                         cachedModel.addTable(table);
267                     }
268 
269                     retTable = table;
270                 } catch (RuntimeException ex) {
271                     throw ex;
272                 } catch (Exception ex) {
273                     throw new RuntimeException(ex);
274                 }
275             }
276         }
277         return retTable;
278     }
279 
280     public Table findTable(String catalogName, String schemaName, final String tblName) throws Exception {
281         // if we don't provide a default schema or catalog, then on some
282         // databases multiple results
283         // will be found in the metadata from multiple schemas/catalogs
284         final String schema = StringUtils.isBlank(schemaName) ? getDefaultSchema() : schemaName;
285         final String catalog = StringUtils.isBlank(catalogName) ? getDefaultCatalog() : catalogName;
286         return (Table) jdbcTemplate.execute(new ConnectionCallback() {
287             public Object doInConnection(Connection c) throws SQLException, DataAccessException {
288                 Table table = null;
289                 DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper();
290                 metaData.setMetaData(c.getMetaData());
291                 metaData.setCatalog(catalog);
292                 metaData.setSchemaPattern(schema);
293                 metaData.setTableTypes(null);
294                 String tableName = tblName;
295                 if (storesUpperCaseNamesInCatalog()) {
296                     tableName = tblName.toUpperCase();
297                 } else if (storesLowerCaseNamesInCatalog()) {
298                     tableName = tblName.toLowerCase();
299                 }
300 
301                 ResultSet tableData = null;
302                 try {
303                     tableData = metaData.getTables(tableName);
304                     while (tableData != null && tableData.next()) {
305                         Map<String, Object> values = readColumns(tableData, initColumnsForTable());
306                         table = readTable(metaData, values);
307                     }
308                 } finally {
309                     JdbcUtils.closeResultSet(tableData);
310                 }
311                 
312                 makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table);
313                 
314                 return table;
315             }
316         });
317     }
318     
319     /***
320      * Treat tables with no primary keys as a table with all primary keys.
321      */
322     protected void makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(Table table) {
323         if (table != null && table.getPrimaryKeyColumns() != null && table.getPrimaryKeyColumns().length == 0) {
324             Column[] allCoumns = table.getColumns();
325             for (Column column : allCoumns) {
326                 column.setPrimaryKey(true);
327             }
328         }
329     }
330 
331     @SuppressWarnings("unchecked")
332     protected Table readTable(DatabaseMetaDataWrapper metaData, Map values) throws SQLException {
333         String tableName = (String) values.get("TABLE_NAME");
334         Table table = null;
335         if (tableName != null && tableName.length() > 0) {
336             table = new Table();
337             table.setName(tableName);
338             table.setType((String) values.get("TABLE_TYPE"));
339             table.setCatalog((String) values.get("TABLE_CAT"));
340             table.setSchema((String) values.get("TABLE_SCHEM"));
341             table.setDescription((String) values.get("REMARKS"));
342             table.addColumns(readColumns(metaData, tableName));
343             if (parameterService.is(ParameterConstants.AUTO_CREATE_SCHEMA_BEFORE_RELOAD)) {
344                 table.addIndices(readIndices(metaData, tableName));
345             }
346             Collection primaryKeys = readPrimaryKeyNames(metaData, tableName);
347             for (Iterator it = primaryKeys.iterator(); it.hasNext(); table.findColumn((String) it.next(), true)
348                     .setPrimaryKey(true))
349                 ;
350             
351             if (this instanceof MsSqlDbDialect) {
352                 determineAutoIncrementFromResultSetMetaData(table, table.getColumns());
353             }
354         }
355         return table;
356     }
357 
358     protected List<MetaDataColumnDescriptor> initColumnsForTable() {
359         List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>();
360         result.add(new MetaDataColumnDescriptor("TABLE_NAME", 12));
361         result.add(new MetaDataColumnDescriptor("TABLE_TYPE", 12, "UNKNOWN"));
362         result.add(new MetaDataColumnDescriptor("TABLE_CAT", 12));
363         result.add(new MetaDataColumnDescriptor("TABLE_SCHEM", 12));
364         result.add(new MetaDataColumnDescriptor("REMARKS", 12));
365         return result;
366     }
367 
368     protected List<MetaDataColumnDescriptor> initColumnsForColumn() {
369         List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>();
370         result.add(new MetaDataColumnDescriptor("COLUMN_DEF", 12));
371         result.add(new MetaDataColumnDescriptor("TABLE_NAME", 12));
372         result.add(new MetaDataColumnDescriptor("COLUMN_NAME", 12));
373         result.add(new MetaDataColumnDescriptor("TYPE_NAME", 12));
374         result.add(new MetaDataColumnDescriptor("DATA_TYPE", 4, new Integer(1111)));
375         result.add(new MetaDataColumnDescriptor("NUM_PREC_RADIX", 4, new Integer(10)));
376         result.add(new MetaDataColumnDescriptor("DECIMAL_DIGITS", 4, new Integer(0)));
377         result.add(new MetaDataColumnDescriptor("COLUMN_SIZE", 12));
378         result.add(new MetaDataColumnDescriptor("IS_NULLABLE", 12, "YES"));
379         result.add(new MetaDataColumnDescriptor("REMARKS", 12));
380         return result;
381     }
382 
383     protected List<MetaDataColumnDescriptor> initColumnsForPK() {
384         List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>();
385         result.add(new MetaDataColumnDescriptor("COLUMN_NAME", 12));
386         result.add(new MetaDataColumnDescriptor("TABLE_NAME", 12));
387         result.add(new MetaDataColumnDescriptor("PK_NAME", 12));
388         return result;
389     }
390 
391     @SuppressWarnings("unchecked")
392     protected Collection<Column> readColumns(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException {
393         ResultSet columnData = null;
394         try {
395             columnData = metaData.getColumns(tableName, null);
396             List<Column> columns = new ArrayList<Column>();
397             Map values = null;
398             for (; columnData.next(); columns.add(readColumn(metaData, values))) {
399                 values = readColumns(columnData, initColumnsForColumn());
400             }
401             return columns;
402         } finally {
403             JdbcUtils.closeResultSet(columnData);
404         }
405     }
406 
407     @SuppressWarnings("unchecked")
408     protected Column readColumn(DatabaseMetaDataWrapper metaData, Map values) throws SQLException {
409         Column column = new Column();
410         column.setName((String) values.get("COLUMN_NAME"));
411         column.setDefaultValue((String) values.get("COLUMN_DEF"));
412         String typeName = (String) values.get("TYPE_NAME");
413         // This is for Oracle's TIMESTAMP(9)
414         if (typeName != null && typeName.startsWith("TIMESTAMP")) {
415             column.setTypeCode(Types.TIMESTAMP);
416         } else {
417             column.setTypeCode(((Integer) values.get("DATA_TYPE")).intValue());
418         }
419 
420         column.setPrecisionRadix(((Integer) values.get("NUM_PREC_RADIX")).intValue());
421         String size = (String) values.get("COLUMN_SIZE");
422         int scale = ((Integer) values.get("DECIMAL_DIGITS")).intValue();
423         if (size == null)
424             size = (String) _defaultSizes.get(new Integer(column.getTypeCode()));
425         column.setSize(size);
426         if (scale != 0)
427             column.setScale(scale);
428         column.setRequired("NO".equalsIgnoreCase(((String) values.get("IS_NULLABLE")).trim()));
429         column.setDescription((String) values.get("REMARKS"));
430         return column;
431     }
432 
433     protected void determineAutoIncrementFromResultSetMetaData(Table table, final Column columnsToCheck[])
434             throws SQLException {
435         StringBuffer query;
436         if (columnsToCheck == null || columnsToCheck.length == 0) {
437             return;
438         }
439         query = new StringBuffer();
440         query.append("SELECT ");
441         for (int idx = 0; idx < columnsToCheck.length; idx++) {
442             if (idx > 0)
443                 query.append(",");
444             query.append("t.").append("\"").append(columnsToCheck[idx].getName()).append("\"");
445         }
446 
447         query.append(" FROM ");
448         if (table.getCatalog() != null && !table.getCatalog().trim().equals("")) {
449             query.append(table.getCatalog() + ".");
450         }
451         if (table.getSchema() != null && !table.getSchema().trim().equals("")) {
452             query.append(table.getSchema() + ".");
453         }
454         query.append("\"").append(table.getName()).append("\" t WHERE 1 = 0");
455 
456         final String finalQuery = query.toString();
457         jdbcTemplate.execute(new StatementCallback() {
458             public Object doInStatement(Statement stmt) throws SQLException, DataAccessException {
459                 ResultSet rs = stmt.executeQuery(finalQuery);
460                 ResultSetMetaData rsMetaData = rs.getMetaData();
461                 for (int idx = 0; idx < columnsToCheck.length; idx++)
462                     if (rsMetaData.isAutoIncrement(idx + 1))
463                         columnsToCheck[idx].setAutoIncrement(true);
464                 return null;
465             }
466         });
467     }
468 
469     @SuppressWarnings("unchecked")
470     protected Map<String, Object> readColumns(ResultSet resultSet, List columnDescriptors) throws SQLException {
471         HashMap<String, Object> values = new HashMap<String, Object>();
472         MetaDataColumnDescriptor descriptor;
473         for (Iterator it = columnDescriptors.iterator(); it.hasNext(); values.put(descriptor.getName(), descriptor
474                 .readColumn(resultSet)))
475             descriptor = (MetaDataColumnDescriptor) it.next();
476 
477         return values;
478     }
479 
480     @SuppressWarnings("unchecked")
481     protected Collection<String> readPrimaryKeyNames(DatabaseMetaDataWrapper metaData, String tableName)
482             throws SQLException {
483         ResultSet pkData = null;
484         try {
485             List<String> pks = new ArrayList<String>();
486             Map values;
487             for (pkData = metaData.getPrimaryKeys(tableName); pkData.next(); pks.add(readPrimaryKeyName(metaData,
488                     values))) {
489                 values = readColumns(pkData, initColumnsForPK());
490             }
491             return pks;
492         } finally {
493             JdbcUtils.closeResultSet(pkData);
494         }
495 
496     }
497 
498     @SuppressWarnings("unchecked")
499     protected String readPrimaryKeyName(DatabaseMetaDataWrapper metaData, Map values) throws SQLException {
500         return (String) values.get("COLUMN_NAME");
501     }
502 
503     @SuppressWarnings("unchecked")
504     protected List initColumnsForIndex() {
505         List result = new ArrayList();
506 
507         result.add(new MetaDataColumnDescriptor("INDEX_NAME", Types.VARCHAR));
508         // we're also reading the table name so that a model reader impl can
509         // filter manually
510         result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR));
511         result.add(new MetaDataColumnDescriptor("NON_UNIQUE", Types.BIT, Boolean.TRUE));
512         result.add(new MetaDataColumnDescriptor("ORDINAL_POSITION", Types.TINYINT, new Short((short) 0)));
513         result.add(new MetaDataColumnDescriptor("COLUMN_NAME", Types.VARCHAR));
514         result.add(new MetaDataColumnDescriptor("TYPE", Types.TINYINT));
515 
516         return result;
517     }
518 
519     @SuppressWarnings("unchecked")
520     protected Collection readIndices(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException {
521         Map indices = new ListOrderedMap();
522         ResultSet indexData = null;
523 
524         try {
525             indexData = metaData.getIndices(tableName, false, false);
526 
527             while (indexData.next()) {
528                 Map values = readColumns(indexData, initColumnsForIndex());
529 
530                 readIndex(metaData, values, indices);
531             }
532         } finally {
533             if (indexData != null) {
534                 indexData.close();
535             }
536         }
537         return indices.values();
538     }
539 
540     @SuppressWarnings("unchecked")
541     protected void readIndex(DatabaseMetaDataWrapper metaData, Map values, Map knownIndices) throws SQLException {
542         Short indexType = (Short) values.get("TYPE");
543 
544         // we're ignoring statistic indices
545         if ((indexType != null) && (indexType.shortValue() == DatabaseMetaData.tableIndexStatistic)) {
546             return;
547         }
548 
549         String indexName = (String) values.get("INDEX_NAME");
550 
551         if (indexName != null) {
552             Index index = (Index) knownIndices.get(indexName);
553 
554             if (index == null) {
555                 if (((Boolean) values.get("NON_UNIQUE")).booleanValue()) {
556                     index = new NonUniqueIndex();
557                 } else {
558                     index = new UniqueIndex();
559                 }
560 
561                 index.setName(indexName);
562                 knownIndices.put(indexName, index);
563             }
564 
565             IndexColumn indexColumn = new IndexColumn();
566 
567             indexColumn.setName((String) values.get("COLUMN_NAME"));
568             if (values.containsKey("ORDINAL_POSITION")) {
569                 indexColumn.setOrdinalPosition(((Short) values.get("ORDINAL_POSITION")).intValue());
570             }
571             index.addColumn(indexColumn);
572         }
573     }
574 
575     /***
576      * Create the configured trigger. The catalog will be changed to the source
577      * schema if the source schema is configured.
578      */
579     public void initTrigger(final DataEventType dml, final Trigger trigger, final TriggerHistory audit,
580             final String tablePrefix, final Table table) {
581         jdbcTemplate.execute(new ConnectionCallback() {
582             public Object doInConnection(Connection con) throws SQLException, DataAccessException {
583                 String sourceCatalogName = trigger.getSourceCatalogName();
584                 logger.info("Creating " + dml.toString() + " trigger for "
585                         + (sourceCatalogName != null ? (sourceCatalogName + ".") : "") + trigger.getSourceTableName());
586                 String previousCatalog = null;
587                 String defaultCatalog = getDefaultCatalog();
588                 String defaultSchema = getDefaultSchema();
589                 try {
590                     previousCatalog = switchCatalogForTriggerInstall(sourceCatalogName, con);
591                     Statement stmt = con.createStatement();
592                     String triggerSql = sqlTemplate.createTriggerDDL(AbstractDbDialect.this, dml, trigger, audit,
593                             tablePrefix, table, defaultCatalog, defaultSchema);
594                     try {
595                         stmt.executeUpdate(triggerSql);
596                     } catch (SQLException ex) {
597                         logger.error("Failed to create trigger: " + triggerSql);
598                         throw ex;
599                     }
600                     String postTriggerDml = createPostTriggerDDL(dml, trigger, audit, tablePrefix, table);
601                     if (postTriggerDml != null) {
602                         try {
603                             stmt.executeUpdate(postTriggerDml);
604                         } catch (SQLException ex) {
605                             logger.error("Failed to create post trigger: " + postTriggerDml);
606                             throw ex;
607                         }
608                     }
609                     stmt.close();
610 
611                 } finally {
612                     if (sourceCatalogName != null && !sourceCatalogName.equalsIgnoreCase(previousCatalog)) {
613                         switchCatalogForTriggerInstall(previousCatalog, con);
614                     }
615                 }
616                 return null;
617             }
618         });
619     }
620 
621     /***
622      * Provide the option switch a connection's schema for trigger installation.
623      */
624     protected String switchCatalogForTriggerInstall(String catalog, Connection c) throws SQLException {
625         return null;
626     }
627 
628     public String createPostTriggerDDL(DataEventType dml, Trigger config, TriggerHistory audit, String tablePrefix,
629             Table table) {
630         return sqlTemplate.createPostTriggerDDL(this, dml, config, audit, tablePrefix, table, getDefaultCatalog(),
631                 getDefaultSchema());
632     }
633 
634     public String getCreateSymmetricDDL() {
635         Database db = getConfigDdlDatabase();
636         prefixConfigDatabase(db);
637         return platform.getCreateTablesSql(db, true, true);
638     }
639 
640     public String getCreateTableSQL(Trigger trig) {
641         Table table = getMetaDataFor(null, trig.getSourceSchemaName(), trig.getSourceTableName(), true);
642         String sql = null;
643         try {
644             StringWriter buffer = new StringWriter();
645             platform.getSqlBuilder().setWriter(buffer);
646             platform.getSqlBuilder().createTable(cachedModel, table);
647             sql = buffer.toString();
648         } catch (IOException e) {
649         }
650         return sql;
651     }
652 
653     public String getCreateTableXML(Trigger trig) {
654         Table table = getMetaDataFor(null, trig.getSourceSchemaName(), trig.getSourceTableName(), true);
655         Database db = new Database();
656         db.setName(trig.getSourceSchemaName() != null ? trig.getSourceSchemaName() : getDefaultSchema() != null
657                 ? getDefaultSchema() : getDefaultCatalog());
658         db.addTable(table);
659         StringWriter buffer = new StringWriter();
660         DatabaseIO xmlWriter = new DatabaseIO();
661         xmlWriter.write(db, buffer);
662         // TODO: remove when these bugs are fixed in DdlUtils
663         return buffer.toString().replaceAll("&apos;", "").replaceAll("default=\"empty_blob//(//) *\"", "");
664     }
665 
666     public void createTables(String xml) {
667         StringReader reader = new StringReader(xml);
668         Database db = new DatabaseIO().read(reader);
669         platform.createTables(db, true, true);
670     }
671 
672     public boolean doesDatabaseNeedConfigured() {
673         return prefixConfigDatabase(getConfigDdlDatabase());
674     }
675 
676     protected boolean prefixConfigDatabase(Database targetTables) {
677         try {
678             String tblPrefix = this.tablePrefix + "_";
679 
680             Table[] tables = targetTables.getTables();
681 
682             boolean createTables = false;
683             for (Table table : tables) {
684                 table.setName(tblPrefix + table.getName());
685                 fixForeignKeys(table, tblPrefix, false);
686 
687                 if (getMetaDataFor(getDefaultCatalog(), getDefaultSchema(), table.getName(), false) == null) {
688                     createTables = true;
689                 }
690             }
691 
692             return createTables;
693         } catch (CloneNotSupportedException e) {
694             throw new RuntimeException(e);
695         }
696     }
697 
698     protected void addPrefixAndCreateTablesIfNecessary(Database targetTables) {
699         try {
700             boolean createTables = prefixConfigDatabase(targetTables);
701             if (createTables) {
702                 logger.info("About to create symmetric tables.");
703                 platform.createTables(targetTables, false, true);
704             } else {
705                 logger.info("No need to create symmetric tables.  They already exist.");
706             }
707         } catch (RuntimeException ex) {
708             throw ex;
709         } catch (Exception ex) {
710             throw new RuntimeException(ex);
711         }
712     }
713 
714     protected Database getConfigDdlDatabase() {
715         try {
716             return new DatabaseIO().read(new InputStreamReader(getConfigDdlXml().openStream()));
717         } catch (RuntimeException ex) {
718             throw ex;
719         } catch (Exception ex) {
720             throw new RuntimeException(ex);
721         }
722     }
723 
724     protected URL getConfigDdlXml() {
725         return AbstractDbDialect.class.getResource("/ddl-config.xml");
726     }
727 
728     protected void fixForeignKeys(Table table, String tablePrefix, boolean clone) throws CloneNotSupportedException {
729         ForeignKey[] keys = table.getForeignKeys();
730         for (ForeignKey key : keys) {
731             if (clone) {
732                 table.removeForeignKey(key);
733                 key = (ForeignKey) key.clone();
734                 table.addForeignKey(key);
735             }
736             String prefixedName = tablePrefix + key.getForeignTableName();
737             key.setForeignTableName(prefixedName);
738             key.setName(tablePrefix + key.getName());
739         }
740     }
741 
742     public Platform getPlatform() {
743         return this.platform;
744     }
745 
746     public String getName() {
747         return databaseName;
748     }
749 
750     public String getVersion() {
751         return databaseMajorVersion + "." + databaseMinorVersion;
752     }
753 
754     public int getMajorVersion() {
755         return databaseMajorVersion;
756     }
757 
758     public int getMinorVersion() {
759         return databaseMinorVersion;
760     }
761 
762     public String getProductVersion() {
763         return databaseProductVersion;
764     }
765 
766     public String replaceTemplateVariables(DataEventType dml, Trigger trigger, TriggerHistory history,
767             String targetString) {
768         return sqlTemplate.replaceTemplateVariables(this, dml, trigger, history, tablePrefix, getMetaDataFor(trigger
769                 .getSourceCatalogName(), trigger.getSourceSchemaName(), trigger.getSourceTableName(), true),
770                 getDefaultCatalog(), getDefaultSchema(), targetString);
771     }
772 
773     public boolean supportsGetGeneratedKeys() {
774         if (supportsGetGeneratedKeys == null) {
775             supportsGetGeneratedKeys = (Boolean) jdbcTemplate.execute(new ConnectionCallback() {
776                 public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
777                     return conn.getMetaData().supportsGetGeneratedKeys();
778                 }
779             });
780         }
781         return supportsGetGeneratedKeys;
782     }
783 
784     public String getSelectLastInsertIdSql(String sequenceName) {
785         throw new UnsupportedOperationException();
786     }
787 
788     public long insertWithGeneratedKey(final String sql, final SequenceIdentifier sequenceId) {
789         return insertWithGeneratedKey(sql, sequenceId, null);
790     }
791 
792     protected String getSequenceName(SequenceIdentifier identifier) {
793         switch (identifier) {
794         case OUTGOING_BATCH:
795             return "sym_outgoing_batch_batch_id";
796         case DATA:
797             return "sym_data_data_id";
798         case TRIGGER_HIST:
799             return "sym_trigger_his_ger_hist_id";
800         }
801         return null;
802     }
803 
804     public long insertWithGeneratedKey(final String sql, final SequenceIdentifier sequenceId,
805             final PreparedStatementCallback callback) {
806         return (Long) jdbcTemplate.execute(new ConnectionCallback() {
807             public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
808 
809                 long key = 0;
810                 PreparedStatement ps = null;
811                 try {
812                     boolean supportsGetGeneratedKeys = supportsGetGeneratedKeys();
813                     if (allowsNullForIdentityColumn()) {
814                         if (supportsGetGeneratedKeys) {
815                             ps = conn.prepareStatement(sql, new int[] { 1 });
816                         } else {
817                             ps = conn.prepareStatement(sql);
818                         }
819                     } else {
820                         String replaceSql = sql.replaceFirst("//(//w*,", "(").replaceFirst("//(null,", "(");
821                         if (supportsGetGeneratedKeys) {
822                             ps = conn.prepareStatement(replaceSql, Statement.RETURN_GENERATED_KEYS);
823                         } else {
824                             ps = conn.prepareStatement(replaceSql);
825                         }
826                     }
827                     ps.setQueryTimeout(jdbcTemplate.getQueryTimeout());
828                     if (callback != null) {
829                         callback.doInPreparedStatement(ps);
830                     }
831 
832                     ps.executeUpdate();
833 
834                     if (supportsGetGeneratedKeys) {
835                         ResultSet rs = null;
836                         try {
837                             rs = ps.getGeneratedKeys();
838                             if (rs.next()) {
839                                 key = rs.getLong(1);
840                             }
841                         } finally {
842                             JdbcUtils.closeResultSet(rs);
843                         }
844                     } else {
845                         Statement st = null;
846                         ResultSet rs = null;
847                         try {
848                             st = conn.createStatement();
849                             rs = st.executeQuery(getSelectLastInsertIdSql(getSequenceName(sequenceId)));
850                             if (rs.next()) {
851                                 key = rs.getLong(1);
852                             }
853                         } finally {
854                             JdbcUtils.closeResultSet(rs);
855                             JdbcUtils.closeStatement(st);
856                         }
857                     }
858                 } finally {
859                     JdbcUtils.closeStatement(ps);
860                 }
861                 return key;
862             }
863         });
864     }
865 
866     public Object createSavepoint() {
867         return transactionTemplate.execute(new TransactionCallback() {
868             public Object doInTransaction(TransactionStatus transactionstatus) {
869                 return transactionstatus.createSavepoint();
870             }
871         });
872     }
873 
874     public Object createSavepointForFallback() {
875         if (requiresSavepointForFallback()) {
876             return createSavepoint();
877         }
878         return null;
879     }
880 
881     public void rollbackToSavepoint(final Object savepoint) {
882         if (savepoint != null) {
883             transactionTemplate.execute(new TransactionCallbackWithoutResult() {
884                 protected void doInTransactionWithoutResult(TransactionStatus transactionstatus) {
885                     transactionstatus.rollbackToSavepoint(savepoint);
886                 }
887             });
888         }
889     }
890 
891     public void releaseSavepoint(final Object savepoint) {
892         if (savepoint != null) {
893             transactionTemplate.execute(new TransactionCallbackWithoutResult() {
894                 protected void doInTransactionWithoutResult(TransactionStatus transactionstatus) {
895                     transactionstatus.releaseSavepoint(savepoint);
896                 }
897             });
898         }
899     }
900 
901     public boolean requiresSavepointForFallback() {
902         return false;
903     }
904 
905     public boolean supportsTransactionId() {
906         return false;
907     }
908 
909     public boolean isBlobSyncSupported() {
910         return true;
911     }
912 
913     public boolean isClobSyncSupported() {
914         return true;
915     }
916 
917     public boolean isTransactionIdOverrideSupported() {
918         return true;
919     }
920 
921     public boolean storesUpperCaseNamesInCatalog() {
922         return false;
923     }
924 
925     public boolean storesLowerCaseNamesInCatalog() {
926         return false;
927     }
928 
929     public void setSqlTemplate(SqlTemplate sqlTemplate) {
930         this.sqlTemplate = sqlTemplate;
931     }
932 
933     public SQLErrorCodeSQLExceptionTranslator getSqlErrorTranslator() {
934         return sqlErrorTranslator;
935     }
936 
937     public void setTablePrefix(String tablePrefix) {
938         this.tablePrefix = tablePrefix;
939     }
940 
941     public int getStreamingResultsFetchSize() {
942         return streamingResultsFetchSize;
943     }
944 
945     public void setStreamingResultsFetchSize(int streamingResultsFetchSize) {
946         this.streamingResultsFetchSize = streamingResultsFetchSize;
947     }
948 
949     public JdbcTemplate getJdbcTemplate() {
950         return jdbcTemplate;
951     }
952 
953     public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
954         this.transactionTemplate = transactionTemplate;
955     }
956 
957     public String getEngineName() {
958         return parameterService.getString(ParameterConstants.ENGINE_NAME);
959     }
960 
961     public String getTablePrefix() {
962         return tablePrefix;
963     }
964 
965     public void setParameterService(IParameterService parameterService) {
966         this.parameterService = parameterService;
967     }
968 
969     public String getIdentifierQuoteString()
970     {
971         return identifierQuoteString;
972     }
973 
974 }