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 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
282
283
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
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
509
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
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
663 return buffer.toString().replaceAll("'", "").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 }