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