Java tutorial
/** * Licensed to JumpMind Inc under one or more contributor * license agreements. See the NOTICE file distributed * with this work for additional information regarding * copyright ownership. JumpMind Inc licenses this file * to you under the GNU General Public License, version 3.0 (GPLv3) * (the "License"); you may not use this file except in compliance * with the License. * * You should have received a copy of the GNU General Public License, * version 3.0 (GPLv3) along with this library; if not, see * <http://www.gnu.org/licenses/>. * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ package org.jumpmind.symmetric.db; import static org.apache.commons.lang.StringUtils.isNotBlank; import java.io.IOException; import java.sql.Types; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.lang.StringUtils; import org.jumpmind.db.model.Column; import org.jumpmind.db.model.Database; import org.jumpmind.db.model.Table; import org.jumpmind.db.platform.IAlterDatabaseInterceptor; import org.jumpmind.db.platform.IDatabasePlatform; import org.jumpmind.db.platform.IDdlBuilder; import org.jumpmind.db.sql.ISqlResultsListener; import org.jumpmind.db.sql.ISqlTemplate; import org.jumpmind.db.sql.ISqlTransaction; import org.jumpmind.db.sql.LogSqlResultsListener; import org.jumpmind.db.sql.SqlException; import org.jumpmind.db.sql.SqlScript; import org.jumpmind.db.util.BinaryEncoding; import org.jumpmind.exception.IoException; import org.jumpmind.symmetric.Version; import org.jumpmind.symmetric.common.Constants; import org.jumpmind.symmetric.common.ParameterConstants; import org.jumpmind.symmetric.ext.IDatabaseUpgradeListener; import org.jumpmind.symmetric.io.data.DataEventType; import org.jumpmind.symmetric.model.Channel; import org.jumpmind.symmetric.model.Node; import org.jumpmind.symmetric.model.Trigger; import org.jumpmind.symmetric.model.TriggerHistory; import org.jumpmind.symmetric.model.TriggerRouter; import org.jumpmind.symmetric.service.IExtensionService; import org.jumpmind.symmetric.service.IParameterService; import org.jumpmind.util.AppUtils; import org.jumpmind.util.FormatUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /* * The abstract class for database dialects. */ abstract public class AbstractSymmetricDialect implements ISymmetricDialect { protected final Logger log = LoggerFactory.getLogger(getClass()); public static final int MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE = 50; protected IDatabasePlatform platform; protected AbstractTriggerTemplate triggerTemplate; protected IParameterService parameterService; protected IExtensionService extensionService; protected Boolean supportsGetGeneratedKeys; protected String databaseName; protected String driverVersion; protected String driverName; protected int databaseMajorVersion; protected int databaseMinorVersion; protected String databaseProductVersion; protected Set<String> sqlKeywords; protected boolean supportsTransactionViews = false; protected Map<String, String> sqlReplacementTokens = new HashMap<String, String>(); public AbstractSymmetricDialect() { } public AbstractSymmetricDialect(IParameterService parameterService, IDatabasePlatform platform) { this.parameterService = parameterService; this.platform = platform; log.info("The DbDialect being used is {}", this.getClass().getName()); buildSqlReplacementTokens(); ISqlTemplate sqlTemplate = this.platform.getSqlTemplate(); this.databaseMajorVersion = sqlTemplate.getDatabaseMajorVersion(); this.databaseMinorVersion = sqlTemplate.getDatabaseMinorVersion(); this.databaseName = sqlTemplate.getDatabaseProductName(); this.databaseProductVersion = sqlTemplate.getDatabaseProductVersion(); this.driverName = sqlTemplate.getDriverName(); this.driverVersion = sqlTemplate.getDriverVersion(); } public boolean requiresAutoCommitFalseToSetFetchSize() { return false; } protected void buildSqlReplacementTokens() { sqlReplacementTokens.put("selectDataUsingGapsSqlHint", ""); } public Map<String, String> getSqlReplacementTokens() { return sqlReplacementTokens; } /* * Provide a default implementation of this method using DDLUtils, * getMaxColumnNameLength() */ public int getMaxTriggerNameLength() { int max = getPlatform().getDatabaseInfo().getMaxColumnNameLength(); return max < MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE && max > 0 ? max : MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE; } public void verifyDatabaseIsCompatible() { } public void initTablesAndDatabaseObjects() { createOrAlterTablesIfNecessary(); createRequiredDatabaseObjects(); platform.resetCachedTableModel(); } protected String replaceTokens(String sql, String objectName) { String ddl = FormatUtils.replace("functionName", objectName, sql); ddl = FormatUtils.replace("version", Version.versionWithUnderscores(), ddl); ddl = FormatUtils.replace("defaultSchema", platform.getDefaultSchema(), ddl); return ddl; } protected boolean installed(String sql, String objectName) { return platform.getSqlTemplate().queryForInt(replaceTokens(sql, objectName)) > 0; } protected void install(String sql, String objectName) { sql = replaceTokens(sql, objectName); log.info("Installing SymmetricDS database object:\n{}", sql); platform.getSqlTemplate().update(sql); log.info("Just installed {}", objectName); } protected void uninstall(String sql, String objectName) { sql = replaceTokens(sql, objectName); platform.getSqlTemplate().update(sql); log.info("Just uninstalled {}", objectName); } public void dropTablesAndDatabaseObjects() { Database modelFromDatabase = readSymmetricSchemaFromDatabase(); platform.dropDatabase(modelFromDatabase, true); dropRequiredDatabaseObjects(); } final public boolean doesTriggerExist(String catalogName, String schema, String tableName, String triggerName) { if (StringUtils.isNotBlank(triggerName)) { try { return doesTriggerExistOnPlatform(catalogName, schema, tableName, triggerName); } catch (Exception ex) { log.warn("Could not figure out if the trigger exists. Assuming that is does not", ex); return false; } } else { return false; } } public abstract void dropRequiredDatabaseObjects(); public abstract void createRequiredDatabaseObjects(); abstract public BinaryEncoding getBinaryEncoding(); abstract protected boolean doesTriggerExistOnPlatform(String catalogName, String schema, String tableName, String triggerName); public String getTransactionTriggerExpression(String defaultCatalog, String defaultSchema, Trigger trigger) { return "null"; } public String createInitialLoadSqlFor(Node node, TriggerRouter trigger, Table table, TriggerHistory triggerHistory, Channel channel, String overrideSelectSql) { return triggerTemplate.createInitalLoadSql(node, trigger, table, triggerHistory, channel, overrideSelectSql) .trim(); } public String createPurgeSqlFor(Node node, TriggerRouter triggerRouter, TriggerHistory triggerHistory) { String sql = null; if (StringUtils.isEmpty(triggerRouter.getInitialLoadDeleteStmt())) { sql = String.format(parameterService.getString(ParameterConstants.INITIAL_LOAD_DELETE_FIRST_SQL), triggerRouter.qualifiedTargetTableName(triggerHistory)); } else { sql = triggerRouter.getInitialLoadDeleteStmt(); } return sql; } public String createCsvDataSql(Trigger trigger, TriggerHistory triggerHistory, Channel channel, String whereClause) { return triggerTemplate .createCsvDataSql( trigger, triggerHistory, platform.getTableFromCache(trigger.getSourceCatalogName(), trigger.getSourceSchemaName(), trigger.getSourceTableName(), false), channel, whereClause) .trim(); } public String createCsvPrimaryKeySql(Trigger trigger, TriggerHistory triggerHistory, Channel channel, String whereClause) { return triggerTemplate .createCsvPrimaryKeySql( trigger, triggerHistory, platform.getTableFromCache(trigger.getSourceCatalogName(), trigger.getSourceSchemaName(), trigger.getSourceTableName(), false), channel, whereClause) .trim(); } public Set<String> getSqlKeywords() { if (sqlKeywords == null) { this.sqlKeywords = this.platform.getSqlTemplate().getSqlKeywords(); } return sqlKeywords; } protected String getDropTriggerSql(StringBuilder sqlBuffer, String catalogName, String schemaName, String triggerName, String tableName) { schemaName = schemaName == null ? "" : (schemaName + "."); return "drop trigger " + schemaName + triggerName; } public void removeTrigger(StringBuilder sqlBuffer, String catalogName, String schemaName, String triggerName, String tableName) { String sql = getDropTriggerSql(sqlBuffer, catalogName, schemaName, triggerName, tableName); logSql(sql, sqlBuffer); if (parameterService.is(ParameterConstants.AUTO_SYNC_TRIGGERS)) { try { this.platform.getSqlTemplate().update(sql); } catch (Exception e) { log.warn("Tried to remove trigger using: {} and failed because: {}", sql, e.getMessage()); } } } final protected void logSql(String sql, StringBuilder sqlBuffer) { if (sqlBuffer != null && StringUtils.isNotBlank(sql)) { sqlBuffer.append(sql); sqlBuffer.append(System.getProperty("line.separator")); sqlBuffer.append(System.getProperty("line.separator")); } } /* * Create the configured trigger. The catalog will be changed to the source * schema if the source schema is configured. */ public void createTrigger(final StringBuilder sqlBuffer, final DataEventType dml, final Trigger trigger, final TriggerHistory hist, final Channel channel, final String tablePrefix, final Table table) { log.info("Creating {} trigger for {}", hist.getTriggerNameForDmlType(dml), table.getFullyQualifiedTableName()); String previousCatalog = null; String sourceCatalogName = table.getCatalog(); String defaultCatalog = platform.getDefaultCatalog(); String defaultSchema = platform.getDefaultSchema(); String triggerSql = triggerTemplate.createTriggerDDL(dml, trigger, hist, channel, tablePrefix, table, defaultCatalog, defaultSchema); String postTriggerDml = createPostTriggerDDL(dml, trigger, hist, channel, tablePrefix, table); if (parameterService.is(ParameterConstants.AUTO_SYNC_TRIGGERS)) { ISqlTransaction transaction = null; try { transaction = this.platform.getSqlTemplate() .startSqlTransaction(platform.getDatabaseInfo().isRequiresAutoCommitForDdl()); previousCatalog = switchCatalogForTriggerInstall(sourceCatalogName, transaction); try { log.debug("Running: {}", triggerSql); transaction.execute(triggerSql); } catch (SqlException ex) { log.info("Failed to create trigger: {}", triggerSql); throw ex; } if (StringUtils.isNotBlank(postTriggerDml)) { try { transaction.execute(postTriggerDml); } catch (SqlException ex) { log.info("Failed to create post trigger: {}", postTriggerDml); throw ex; } } transaction.commit(); } catch (SqlException ex) { transaction.rollback(); throw ex; } finally { try { if (sourceCatalogName != null && !sourceCatalogName.equalsIgnoreCase(previousCatalog)) { switchCatalogForTriggerInstall(previousCatalog, transaction); } } finally { transaction.close(); } } } logSql(triggerSql, sqlBuffer); logSql(postTriggerDml, sqlBuffer); } /* * Provide the option switch a connection's schema for trigger installation. */ protected String switchCatalogForTriggerInstall(String catalog, ISqlTransaction transaction) { return null; } protected String createPostTriggerDDL(DataEventType dml, Trigger trigger, TriggerHistory hist, Channel channel, String tablePrefix, Table table) { return triggerTemplate.createPostTriggerDDL(dml, trigger, hist, channel, tablePrefix, table, platform.getDefaultCatalog(), platform.getDefaultSchema()); } public String getCreateSymmetricDDL() { Database database = readSymmetricSchemaFromXml(); prefixConfigDatabase(database); IDdlBuilder builder = platform.getDdlBuilder(); return builder.createTables(database, true); } protected void prefixConfigDatabase(Database targetTables) { platform.prefixDatabase(parameterService.getTablePrefix(), targetTables); } public Table getTable(TriggerHistory triggerHistory, boolean useCache) { if (triggerHistory != null) { return platform.getTableFromCache(triggerHistory.getSourceCatalogName(), triggerHistory.getSourceSchemaName(), triggerHistory.getSourceTableName(), !useCache); } else { return null; } } /* * @return true if SQL was executed. */ public boolean createOrAlterTablesIfNecessary(String... tableNames) { try { log.info("Checking if SymmetricDS tables need created or altered"); Database modelFromXml = readSymmetricSchemaFromXml(); Database modelFromDatabase = readSymmetricSchemaFromDatabase(); if (tableNames != null && tableNames.length > 0) { tableNames = platform.alterCaseToMatchDatabaseDefaultCase(tableNames); modelFromXml.removeAllTablesExcept(tableNames); modelFromDatabase.removeAllTablesExcept(tableNames); } IDdlBuilder builder = platform.getDdlBuilder(); List<IAlterDatabaseInterceptor> alterDatabaseInterceptors = extensionService .getExtensionPointList(IAlterDatabaseInterceptor.class); IAlterDatabaseInterceptor[] interceptors = alterDatabaseInterceptors .toArray(new IAlterDatabaseInterceptor[alterDatabaseInterceptors.size()]); if (builder.isAlterDatabase(modelFromDatabase, modelFromXml, interceptors)) { String delimiter = platform.getDatabaseInfo().getSqlCommandDelimiter(); ISqlResultsListener resultsListener = new LogSqlResultsListener(log); List<IDatabaseUpgradeListener> databaseUpgradeListeners = extensionService .getExtensionPointList(IDatabaseUpgradeListener.class); String alterSql = builder.alterDatabase(modelFromDatabase, modelFromXml, interceptors); if (isNotBlank(alterSql)) { log.info("There are SymmetricDS tables that needed altered"); for (IDatabaseUpgradeListener listener : databaseUpgradeListeners) { String sql = listener.beforeUpgrade(this, this.parameterService.getTablePrefix(), modelFromDatabase, modelFromXml); SqlScript script = new SqlScript(sql, getPlatform().getSqlTemplate(), true, false, false, delimiter, null); script.setListener(resultsListener); script.execute(platform.getDatabaseInfo().isRequiresAutoCommitForDdl()); } log.debug("Alter SQL generated: {}", alterSql); SqlScript script = new SqlScript(alterSql, getPlatform().getSqlTemplate(), true, false, false, delimiter, null); script.setListener(resultsListener); script.execute(platform.getDatabaseInfo().isRequiresAutoCommitForDdl()); for (IDatabaseUpgradeListener listener : databaseUpgradeListeners) { String sql = listener.afterUpgrade(this, this.parameterService.getTablePrefix(), modelFromXml); script = new SqlScript(sql, getPlatform().getSqlTemplate(), true, false, false, delimiter, null); script.setListener(resultsListener); script.execute(platform.getDatabaseInfo().isRequiresAutoCommitForDdl()); } log.info("Done with auto update of SymmetricDS tables"); return true; } else { return false; } } else { return false; } } catch (RuntimeException ex) { throw ex; } catch (Exception ex) { throw new RuntimeException(ex); } } public Database readSymmetricSchemaFromXml() { try { Database database = merge(readDatabaseFromXml("/symmetric-schema.xml"), readDatabaseFromXml("/console-schema.xml")); prefixConfigDatabase(database); String extraTablesXml = parameterService.getString(ParameterConstants.AUTO_CONFIGURE_EXTRA_TABLES); if (StringUtils.isNotBlank(extraTablesXml)) { try { database = merge(database, readDatabaseFromXml(extraTablesXml)); } catch (Exception ex) { log.error("", ex); } } return database; } catch (RuntimeException ex) { throw ex; } catch (Exception ex) { throw new RuntimeException(ex); } } public Database readSymmetricSchemaFromDatabase() { return platform.readFromDatabase(readSymmetricSchemaFromXml().getTables()); } protected Database readDatabaseFromXml(String resourceName) throws IOException { try { return platform.readDatabaseFromXml(resourceName, true); } catch (IoException ex) { return new Database(); } } protected Database merge(Database... databases) { Database database = new Database(); if (databases != null) { for (Database db : databases) { Table[] tables = db.getTables(); for (Table table : tables) { database.addTable(table); } } } return database; } public IDatabasePlatform getPlatform() { return this.platform; } public String getName() { return databaseName; } public String getVersion() { return databaseMajorVersion + "." + databaseMinorVersion; } public int getMajorVersion() { return databaseMajorVersion; } public int getMinorVersion() { return databaseMinorVersion; } public String getProductVersion() { return databaseProductVersion; } public boolean supportsTransactionViews() { return supportsTransactionViews; } public long insertWithGeneratedKey(String sql, SequenceIdentifier sequenceId) { return insertWithGeneratedKey(sql, sequenceId, null, null); } public long insertWithGeneratedKey(final String sql, final SequenceIdentifier identifier, Object... args) { return platform.getSqlTemplate().insertWithGeneratedKey(sql, getSequenceKeyName(identifier), getSequenceKeyName(identifier), args, null); } public String getSequenceName(SequenceIdentifier identifier) { switch (identifier) { case REQUEST: return "sym_extract_r_st_request_id"; case DATA: return "sym_data_data_id"; case TRIGGER_HIST: return "sym_trigger_his_ger_hist_id"; } return null; } public String getSequenceKeyName(SequenceIdentifier identifier) { switch (identifier) { case REQUEST: return "request_id"; case DATA: return "data_id"; case TRIGGER_HIST: return "trigger_hist_id"; } return null; } @Deprecated public Column[] orderColumns(String[] columnNames, Table table) { Column[] unorderedColumns = table.getColumns(); Column[] orderedColumns = new Column[columnNames.length]; for (int i = 0; i < columnNames.length; i++) { String name = columnNames[i]; for (Column column : unorderedColumns) { if (column.getName().equalsIgnoreCase(name)) { orderedColumns[i] = column; break; } } } return orderedColumns; } public void disableSyncTriggers(ISqlTransaction transaction) { disableSyncTriggers(transaction, null); } public boolean supportsTransactionId() { return false; } public boolean isBlobSyncSupported() { return true; } public boolean isClobSyncSupported() { return true; } public boolean isTransactionIdOverrideSupported() { return true; } public String getEngineName() { return parameterService.getString(ParameterConstants.ENGINE_NAME); } public boolean supportsOpenCursorsAcrossCommit() { return true; } public String getInitialLoadTableAlias() { return "t"; } public String preProcessTriggerSqlClause(String sqlClause) { return sqlClause; } public void truncateTable(String tableName) { String quote = platform.getDdlBuilder().isDelimitedIdentifierModeOn() ? platform.getDatabaseInfo().getDelimiterToken() : ""; boolean success = false; int tryCount = 5; while (!success && tryCount > 0) { try { Table table = platform.getTableFromCache(tableName, false); if (table != null) { platform.getSqlTemplate() .update(String.format("truncate table %s%s%s", quote, table.getName(), quote)); success = true; } else { throw new RuntimeException(String.format("Could not find %s to trunate", tableName)); } } catch (SqlException ex) { log.warn("", ex); AppUtils.sleep(5000); tryCount--; } } } public boolean areDatabaseTransactionsPendingSince(long time) { throw new UnsupportedOperationException(); } public long getDatabaseTime() { try { String sql = "select current_timestamp from " + this.parameterService.getTablePrefix() + "_node_identity"; sql = FormatUtils.replaceTokens(sql, platform.getSqlScriptReplacementTokens(), false); Date dateTime = this.platform.getSqlTemplate().queryForObject(sql, java.util.Date.class); if (dateTime != null) { return dateTime.getTime(); } else { return System.currentTimeMillis(); } } catch (Exception ex) { log.error("", ex); return System.currentTimeMillis(); } } public String getSourceNodeExpression() { return null; } final public String getDataHasChangedCondition(Trigger trigger) { if (parameterService.is(ParameterConstants.TRIGGER_UPDATE_CAPTURE_CHANGED_DATA_ONLY)) { return getDbSpecificDataHasChangedCondition(trigger); } else { return Constants.ALWAYS_TRUE_CONDITION; } } protected String getDbSpecificDataHasChangedCondition(Trigger trigger) { return Constants.ALWAYS_TRUE_CONDITION; } public boolean needsToSelectLobData() { return false; } public boolean canGapsOccurInCapturedDataIds() { return true; } public String massageDataExtractionSql(String sql, Channel channel) { String textColumnExpression = parameterService .getString(ParameterConstants.DATA_EXTRACTOR_TEXT_COLUMN_EXPRESSION); if (isNotBlank(textColumnExpression)) { sql = sql.replace("d.old_data", textColumnExpression.replace("$(columnName)", "d.old_data")); sql = sql.replace("d.row_data", textColumnExpression.replace("$(columnName)", "d.row_data")); sql = sql.replace("d.pk_data", textColumnExpression.replace("$(columnName)", "d.pk_data")); } return sql; } public String getDriverName() { return driverName; } public String getDriverVersion() { return driverVersion; } public String massageForLob(String sql, Channel channel) { return sql; } public boolean escapesTemplatesForDatabaseInserts() { return false; } public String getMasterCollation() { return parameterService.getString(ParameterConstants.DB_MASTER_COLLATION, ""); } public boolean supportsBatchUpdates() { return true; } public void cleanupTriggers() { } public AbstractTriggerTemplate getTriggerTemplate() { return triggerTemplate; } protected void close(ISqlTransaction transaction) { if (transaction != null) { transaction.close(); } } public String getTablePrefix() { return parameterService.getTablePrefix(); } public String getTemplateNumberPrecisionSpec() { return null; } public int getSqlTypeForIds() { return Types.NUMERIC; } @Override public IParameterService getParameterService() { return parameterService; } public void setExtensionService(IExtensionService extensionService) { this.extensionService = extensionService; } }