Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/hsqldb/HsqlDbTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/hsqldb/HsqlDbTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/hsqldb/HsqlDbTriggerTemplate.java (revision ) @@ -28,7 +28,7 @@ public class HsqlDbTriggerTemplate extends AbstractTriggerTemplate { public HsqlDbTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); + super(symmetricDialect); emptyColumnTemplate = "''" ; stringColumnTemplate = "case when $(tableAlias)\"$(columnName)\" is null then '''' else concat(concat(''\"'',replace(replace($(tableAlias)\"$(columnName)\",''\\'',''\\\\''),''\"'',''\\\"'')),''\"'') end" ; numberColumnTemplate = "case when $(tableAlias)\"$(columnName)\" is null then '''' else concat(concat(''\"'',cast($(tableAlias)\"$(columnName)\" as varchar(50))),''\"'') end" ; @@ -43,30 +43,37 @@ newColumnPrefix = "NEW_" ; sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, -"CREATE TABLE $(triggerName)_CONFIG (CONDITION_SQL LONGVARCHAR, INSERT_DATA_SQL LONGVARCHAR); " + -" INSERT INTO $(triggerName)_CONFIG values( " + -" 'select count(*) from $(virtualOldNewTable) where $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)', " + -" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" (select ''$(targetTableName)'',''I'',$(triggerHistoryId),$(columns), ''$(channelName)'', $(txIdExpression), sym_get_session(''node_value''), $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))' " + -" ); " + +"CREATE TABLE $(triggerName)_CONFIG (CONDITION_SQL LONGVARCHAR, INSERT_DATA_SQL LONGVARCHAR); " + +" INSERT INTO $(triggerName)_CONFIG values( " + +" 'select count(*) from $(virtualOldNewTable) where $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)', " + +" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + +" (select ''$(targetTableName)'',''I'',$(triggerHistoryId),$(columns), ''$(channelName)'', $(txIdExpression), sym_get_session(''node_value''), $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))' " + +" ); " + " CREATE TRIGGER $(triggerName) AFTER INSERT ON $(tableName) FOR EACH ROW QUEUE 0 CALL \"org.jumpmind.symmetric.db.hsqldb.HsqlDbTrigger\"; " ); - sqlTemplates.put("updateTriggerTemplate" , + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(INSERT_TRIGGER_TEMPLATE)); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, -"CREATE TABLE $(triggerName)_CONFIG (CONDITION_SQL LONGVARCHAR, INSERT_DATA_SQL LONGVARCHAR); " + -" INSERT INTO $(triggerName)_CONFIG values( " + -" 'select count(*) from $(virtualOldNewTable) where $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)', " + -" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" (select ''$(targetTableName)'',''U'',$(triggerHistoryId),$(oldKeys),$(columns),$(oldColumns), ''$(channelName)'', $(txIdExpression), sym_get_session(''node_value''), $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + -" ); " + +"CREATE TABLE $(triggerName)_CONFIG (CONDITION_SQL LONGVARCHAR, INSERT_DATA_SQL LONGVARCHAR); " + +" INSERT INTO $(triggerName)_CONFIG values( " + +" 'select count(*) from $(virtualOldNewTable) where $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)', " + +" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + +" (select ''$(targetTableName)'',''U'',$(triggerHistoryId),$(oldKeys),$(columns),$(oldColumns), ''$(channelName)'', $(txIdExpression), sym_get_session(''node_value''), $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + +" ); " + " CREATE TRIGGER $(triggerName) AFTER UPDATE ON $(tableName) FOR EACH ROW QUEUE 0 CALL \"org.jumpmind.symmetric.db.hsqldb.HsqlDbTrigger\"; " ); - sqlTemplates.put("deleteTriggerTemplate" , + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(UPDATE_TRIGGER_TEMPLATE)); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, -"CREATE TABLE $(triggerName)_CONFIG (CONDITION_SQL LONGVARCHAR, INSERT_DATA_SQL LONGVARCHAR); " + -" INSERT INTO $(triggerName)_CONFIG values( " + -" 'select count(*) from $(virtualOldNewTable) where $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)', " + -" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" (select ''$(targetTableName)'',''D'',$(triggerHistoryId),$(oldKeys),$(oldColumns),''$(channelName)'', $(txIdExpression), sym_get_session(''node_value''), $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + -" ); " + +"CREATE TABLE $(triggerName)_CONFIG (CONDITION_SQL LONGVARCHAR, INSERT_DATA_SQL LONGVARCHAR); " + +" INSERT INTO $(triggerName)_CONFIG values( " + +" 'select count(*) from $(virtualOldNewTable) where $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)', " + +" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + +" (select ''$(targetTableName)'',''D'',$(triggerHistoryId),$(oldKeys),$(oldColumns),''$(channelName)'', $(txIdExpression), sym_get_session(''node_value''), $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + +" ); " + " CREATE TRIGGER $(triggerName) AFTER DELETE ON $(tableName) FOR EACH ROW QUEUE 0 CALL \"org.jumpmind.symmetric.db.hsqldb.HsqlDbTrigger\"; " ); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(DELETE_TRIGGER_TEMPLATE)); + sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); } \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/SymmetricAdmin.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/SymmetricAdmin.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/SymmetricAdmin.java (revision ) @@ -84,7 +84,7 @@ private static final String CMD_SYNC_TRIGGERS = "sync-triggers"; private static final String CMD_EXPORT_PROPERTIES = "export-properties"; - + private static final String CMD_UNINSTALL = "uninstall"; private static final String CMD_SEND_SQL = "send-sql"; @@ -171,7 +171,7 @@ + Message.get("SymAdmin.Cmd." + cmd); new HelpFormatter().printWrapped(pw, 79, 25, text); } - + private void printHelpCommand(CommandLine line) { String[] args = line.getArgs(); if (args.length > 1) { @@ -223,7 +223,7 @@ } } - + @Override protected void buildOptions(Options options) { @@ -248,7 +248,7 @@ exportDefaultProperties(line, args); return true; } else if (cmd.equals(CMD_CREATE_WAR)) { - generateWar(line, args); + generateWar(args); return true; } else if (cmd.equals(CMD_EXPORT_SYM_TABLES)) { exportSymTables(line, args); @@ -388,12 +388,16 @@ } } - private void generateWar(CommandLine line, List args) throws Exception { + private void generateWar(List args) throws Exception { String warFileName = popArg(args, "Filename"); final File workingDirectory = new File("../.war"); FileUtils.deleteDirectory(workingDirectory); FileUtils.copyDirectory(new File("../web"), workingDirectory); FileUtils.copyDirectory(new File("../conf"), new File(workingDirectory, "WEB-INF/classes")); + FileUtils.moveFile( + new File(workingDirectory, "WEB-INF/classes/symmetric-extensions.xml"), + new File(workingDirectory, "WEB-INF/classes/conf/symmetric-extensions.xml") + ); if (propertiesFile != null && propertiesFile.exists()) { FileUtils.copyFile(propertiesFile, new File(workingDirectory, "WEB-INF/classes/symmetric.properties")); @@ -428,7 +432,7 @@ private void createSymTables() { getSymmetricEngine().setupDatabase(true); } - + private void uninstall(CommandLine line, List args) { getSymmetricEngine().uninstall(); } Index: symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractSymmetricDialect.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractSymmetricDialect.java (revision 7927) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractSymmetricDialect.java (revision ) @@ -94,16 +94,16 @@ protected Set sqlKeywords; protected boolean supportsTransactionViews = false; - + protected List databaseUpgradeListeners = new ArrayList(); - + protected List alterDatabaseInterceptors = new ArrayList(); - + protected Map sqlReplacementTokens = new HashMap(); public AbstractSymmetricDialect() { } - + public AbstractSymmetricDialect(IParameterService parameterService, IDatabasePlatform platform) { this.parameterService = parameterService; this.platform = platform; @@ -111,7 +111,7 @@ log.info("The DbDialect being used is {}", this.getClass().getName()); buildSqlReplacementTokens(); - + ISqlTemplate sqlTemplate = this.platform.getSqlTemplate(); this.databaseMajorVersion = sqlTemplate.getDatabaseMajorVersion(); this.databaseMinorVersion = sqlTemplate.getDatabaseMinorVersion(); @@ -119,17 +119,17 @@ 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 getSqlReplacementTokens() { return sqlReplacementTokens; } @@ -143,7 +143,7 @@ return max < MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE && max > 0 ? max : MAX_SYMMETRIC_SUPPORTED_TRIGGER_SIZE; } - + public void verifyDatabaseIsCompatible() { } @@ -152,34 +152,34 @@ 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; + 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); 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(); + Database modelFromDatabase = readSymmetricSchemaFromDatabase(); platform.dropDatabase(modelFromDatabase, true); - dropRequiredDatabaseObjects(); + dropRequiredDatabaseObjects(); } final public boolean doesTriggerExist(String catalogName, String schema, String tableName, @@ -196,9 +196,9 @@ return false; } } - + public abstract void dropRequiredDatabaseObjects(); - + public abstract void createRequiredDatabaseObjects(); abstract public BinaryEncoding getBinaryEncoding(); @@ -217,8 +217,8 @@ .trim(); } - public String createPurgeSqlFor(Node node, TriggerRouter triggerRouter, TriggerHistory triggerHistory) { - String sql = null; + 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), @@ -255,7 +255,7 @@ } return sqlKeywords; } - + protected String getDropTriggerSql(StringBuilder sqlBuffer, String catalogName, String schemaName, String triggerName, String tableName) { schemaName = schemaName == null ? "" : (schemaName + "."); @@ -328,7 +328,8 @@ } transaction.commit(); } catch (SqlException ex) { + if (null != transaction) - transaction.rollback(); + transaction.rollback(); throw ex; } finally { try { @@ -337,7 +338,8 @@ switchCatalogForTriggerInstall(previousCatalog, transaction); } } finally { + if (null != transaction) - transaction.close(); + transaction.close(); } } @@ -412,14 +414,14 @@ return null; } } - + /* * @return true if SQL was executed. */ public boolean createOrAlterTablesIfNecessary(String... tableNames) { - try { + try { log.info("Checking if SymmetricDS tables need created or altered"); - + Database modelFromXml = readSymmetricSchemaFromXml(); Database modelFromDatabase = readSymmetricSchemaFromDatabase(); @@ -430,12 +432,12 @@ } IDdlBuilder builder = platform.getDdlBuilder(); - + IAlterDatabaseInterceptor[] interceptors = alterDatabaseInterceptors.toArray(new IAlterDatabaseInterceptor[alterDatabaseInterceptors.size()]); if (builder.isAlterDatabase(modelFromDatabase, modelFromXml, interceptors)) { log.info("There are SymmetricDS tables that needed altered"); String delimiter = platform.getDatabaseInfo().getSqlCommandDelimiter(); - + ISqlResultsListener resultsListener = new LogSqlResultsListener(log); for (IDatabaseUpgradeListener listener : databaseUpgradeListeners) { @@ -480,7 +482,7 @@ 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)) { @@ -490,7 +492,7 @@ log.error(ex.getMessage(), ex); } } - + return database; } catch (RuntimeException ex) { throw ex; @@ -498,7 +500,7 @@ throw new RuntimeException(ex); } } - + public Database readSymmetricSchemaFromDatabase() { return platform.readFromDatabase(readSymmetricSchemaFromXml().getTables()); } @@ -738,7 +740,7 @@ public void cleanupTriggers() { } - + public void addAlterDatabaseInterceptor(IAlterDatabaseInterceptor interceptor) { alterDatabaseInterceptors.add(interceptor); } @@ -760,11 +762,11 @@ public String getTablePrefix() { return parameterService.getTablePrefix(); } - + public String getTemplateNumberPrecisionSpec() { return null; } - + public int getSqlTypeForIds() { return Types.NUMERIC; } Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java (revision ) @@ -34,96 +34,130 @@ public class SqlAnywhereTriggerTemplate extends AbstractTriggerTemplate { - public SqlAnywhereTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"') + '\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + cast($(tableAlias).\"$(columnName)\" as varchar) + '\"') end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + replace(convert(varchar,$(tableAlias).\"$(columnName)\",23),'T',' ') + '\"') end" ; - clobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(origTableAlias).\"$(columnName)\" as varchar(16384)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - blobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + base64_encode($(origTableAlias).\"$(columnName)\") + '\"' end" ; - booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "+" ; - newTriggerValue = "inserted" ; - oldTriggerValue = "deleted" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) for insert as " + -" begin " + -" declare @DataRow varchar(16384) " + + "create trigger $(triggerName) on $(schemaName)$(tableName) for insert as " + + " begin " + + " declare @DataRow varchar(16384) " + -" $(declareNewKeyVariables) " + + " $(declareNewKeyVariables) "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor for " + -" $(if:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + -" $(end:containsBlobClobColumns) " + -" open DataCursor " + -" fetch next DataCursor into @DataRow $(newKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate()) " + -" fetch next DataCursor into @DataRow $(newKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate DataCursor " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor for " + + " $(if:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + + " $(end:containsBlobClobColumns) " + + " open DataCursor " + + " fetch next DataCursor into @DataRow $(newKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate()) " + + " fetch next DataCursor into @DataRow $(newKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate DataCursor " + -" end " + -" $(custom_on_insert_text) " + -" end " ); - - sqlTemplates.put("updateTriggerTemplate" , + " end "; + private static final String SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER = + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) for update as " + -" begin " + -" declare @DataRow varchar(16384) " + -" declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(16384) " + -" $(declareOldKeyVariables) " + + "create trigger $(triggerName) on $(schemaName)$(tableName) for update as " + + " begin " + + " declare @DataRow varchar(16384) " + + " declare @OldPk varchar(2000) " + + " declare @OldDataRow varchar(16384) " + + " $(declareOldKeyVariables) " + -" $(declareNewKeyVariables) " + + " $(declareNewKeyVariables) "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor for " + -" $(if:containsBlobClobColumns) " + -" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " + -" $(end:containsBlobClobColumns) " + -" open DataCursor " + -" fetch next DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate())" + -" fetch next DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate DataCursor " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor for " + + " $(if:containsBlobClobColumns) " + + " select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " + + " $(end:containsBlobClobColumns) " + + " open DataCursor " + + " fetch next DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate())" + + " fetch next DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate DataCursor " + -" end " + -" $(custom_on_update_text) " + -" end " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) for delete as " + -" begin " + -" declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(16384) " + + "create trigger $(triggerName) on $(schemaName)$(tableName) for delete as " + + " begin " + + " declare @OldPk varchar(2000) " + + " declare @OldDataRow varchar(16384) " + -" $(declareOldKeyVariables) " + + " $(declareOldKeyVariables) "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor for " + -" select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + -" open DataCursor " + -" fetch next DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate())" + -" fetch next DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate DataCursor " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor for " + + " select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + + " open DataCursor " + + " fetch next DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate())" + + " fetch next DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate DataCursor " + -" end " + -" $(custom_on_delete_text) " + -" end " ); + " end "; + + public SqlAnywhereTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"') + '\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + cast($(tableAlias).\"$(columnName)\" as varchar) + '\"') end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + replace(convert(varchar,$(tableAlias).\"$(columnName)\",23),'T',' ') + '\"') end" ; + clobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(origTableAlias).\"$(columnName)\" as varchar(16384)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + blobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + base64_encode($(origTableAlias).\"$(columnName)\") + '\"' end" ; + booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "+" ; + newTriggerValue = "inserted" ; + oldTriggerValue = "deleted" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE , + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + SQL_ANYWHERE_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java (revision 7927) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java (revision ) @@ -49,14 +49,19 @@ protected static final String ORIG_TABLE_ALIAS = "orig"; - static final String INSERT_TRIGGER_TEMPLATE = "insertTriggerTemplate"; + public static final String INSERT_TRIGGER_TEMPLATE = "insertTriggerTemplate"; - static final String UPDATE_TRIGGER_TEMPLATE = "updateTriggerTemplate"; + public static final String UPDATE_TRIGGER_TEMPLATE = "updateTriggerTemplate"; - static final String DELETE_TRIGGER_TEMPLATE = "deleteTriggerTemplate"; + public static final String DELETE_TRIGGER_TEMPLATE = "deleteTriggerTemplate"; static final String INITIAL_LOAD_SQL_TEMPLATE = "initialLoadSqlTemplate"; + protected static final String CUSTOM_ON_INSERT_TEXT = " $(custom_on_insert_text) \n"; + protected static final String CUSTOM_ON_UPDATE_TEXT = " $(custom_on_update_text) \n"; + protected static final String CUSTOM_BEFORE_BODY = "CustomBeforeBody"; + protected static final String CUSTOM_ON_DELETE_TEXT = " $(custom_on_delete_text) \n"; + protected Map sqlTemplates; protected String emptyColumnTemplate = "''"; @@ -241,15 +246,19 @@ } public String createTriggerDDL(DataEventType dml, Trigger trigger, TriggerHistory history, - Channel channel, String tablePrefix, Table originalTable, String defaultCatalog, - String defaultSchema) { + Channel channel, String tablePrefix, Table originalTable, String defaultCatalog, + String defaultSchema) { Table table = originalTable.copyAndFilterColumns(history.getParsedColumnNames(), history.getParsedPkColumnNames(), true); - String ddl = sqlTemplates.get(dml.name().toLowerCase() + "TriggerTemplate"); + String dmlName = dml.name().toLowerCase(); + String ddl = sqlTemplates.get( + dmlName + "TriggerTemplate" + + (trigger.isCustomAfterExtraction(dmlName) ? "" : CUSTOM_BEFORE_BODY) + ); if (dml.getDmlType().equals(DmlType.UPDATE) && trigger.isUseHandleKeyUpdates()) { - String temp = sqlTemplates.get(dml.name().toLowerCase() + "HandleKeyUpdates" + "TriggerTemplate"); + String temp = sqlTemplates.get(dmlName + "HandleKeyUpdates" + "TriggerTemplate"); if (StringUtils.trimToNull(temp)!=null) { ddl=temp; } @@ -320,10 +329,21 @@ ddl = FormatUtils.replace("custom_on_insert_text", trigger.getCustomOnInsertText()==null ? "" : trigger.getCustomOnInsertText(), ddl); + + ddl = FormatUtils.replace("custom_on_insert_after_extract", + String.valueOf(trigger.isCustomOnInsertAfterExtraction() ? 1 : 0), ddl); + ddl = FormatUtils.replace("custom_on_update_text", trigger.getCustomOnUpdateText()==null ? "" : trigger.getCustomOnUpdateText(), ddl); + + ddl = FormatUtils.replace("custom_on_update_after_extract", + String.valueOf(trigger.isCustomOnUpdateAfterExtraction() ? 1 : 0), ddl); + ddl = FormatUtils.replace("custom_on_delete_text", trigger.getCustomOnDeleteText()==null ? "" : trigger.getCustomOnDeleteText(), ddl); + + ddl = FormatUtils.replace("custom_on_delete_after_extract", + String.valueOf(trigger.isCustomOnDeleteAfterExtraction() ? 1 : 0), ddl); ddl = FormatUtils.replace("dataHasChangedCondition", symmetricDialect .preProcessTriggerSqlClause(symmetricDialect.getDataHasChangedCondition(trigger)), @@ -552,7 +572,7 @@ } columnsText.append(columnName); } else { - columnsText.append("null"); + columnsText.append("null"); } columnsText.append(","); } @@ -589,7 +609,7 @@ case Types.CHAR: case Types.NCHAR: case Types.VARCHAR: - case ColumnTypes.NVARCHAR: + case ColumnTypes.NVARCHAR: templateToUse = stringColumnTemplate; break; case ColumnTypes.SQLXML: Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java (revision ) @@ -52,9 +52,9 @@ static final String SQL_SELECT_TRANSACTIONS = "select min(start_time) from gv$transaction"; static final String SQL_OBJECT_INSTALLED = "select count(*) from user_source where line = 1 and (((type = 'FUNCTION' or type = 'PACKAGE') and name=upper('$(functionName)')) or (name||'_'||type=upper('$(functionName)')))" ; - + static final String SQL_DROP_FUNCTION = "DROP FUNCTION $(functionName)"; - + public OracleSymmetricDialect(IParameterService parameterService, IDatabasePlatform platform) { super(parameterService, platform); this.triggerTemplate = new OracleTriggerTemplate(this); @@ -67,7 +67,7 @@ } } } - + @Override protected void buildSqlReplacementTokens() { super.buildSqlReplacementTokens(); @@ -75,23 +75,23 @@ sqlReplacementTokens.put("selectDataUsingGapsSqlHint", "/*+ index(d SYM_IDX_D_CHANNEL_ID) */"); } } - + @Override protected boolean doesTriggerExistOnPlatform(String catalog, String schema, String tableName, String triggerName) { return platform.getSqlTemplate().queryForInt("select count(*) " + SQL_SELECT_TRIGGERS, - new Object[] { triggerName, tableName }) > 0; - } - + new Object[] { triggerName, tableName }) > 0; + } + @Override protected String getDropTriggerSql(StringBuilder sqlBuffer, String catalogName, String schemaName, String triggerName, String tableName) { return "drop trigger " + triggerName; - } + } @Override public void createTrigger(StringBuilder sqlBuffer, DataEventType dml, Trigger trigger, - TriggerHistory history, Channel channel, String tablePrefix, Table table) { + TriggerHistory history, Channel channel, String tablePrefix, Table table) { try { super.createTrigger(sqlBuffer, dml, trigger, history, channel, parameterService.getTablePrefix(), table); @@ -111,7 +111,7 @@ throw ex; } } - + @Override public void createRequiredDatabaseObjects() { String blobToClob = this.parameterService.getTablePrefix() + "_" + "blob2clob"; @@ -169,7 +169,7 @@ + " procedure setNodeValue (node_id IN varchar); " + " end "+getSymmetricPackageName()+"; "; install(sql, pkgPackage); - + sql = "CREATE OR REPLACE package body $(functionName) as " + " procedure setValue(a IN number) is " + " begin " @@ -219,11 +219,11 @@ if (installed(SQL_OBJECT_INSTALLED, triggerDisabled)) { uninstall(SQL_DROP_FUNCTION, triggerDisabled); } - + String wkt2geom = this.parameterService.getTablePrefix() + "_" + "wkt2geom"; if (installed(SQL_OBJECT_INSTALLED, wkt2geom)) { uninstall(SQL_DROP_FUNCTION, wkt2geom); - } + } String pkgPackage = this.parameterService.getTablePrefix() + "_" + "pkg"; if (installed(SQL_OBJECT_INSTALLED, pkgPackage)) { Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/derby/DerbyTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/derby/DerbyTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/derby/DerbyTriggerTemplate.java (revision ) @@ -54,50 +54,56 @@ otherColumnTemplate = null; sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, -"CREATE TRIGGER $(triggerName) \n" + -" AFTER INSERT ON $(schemaName)$(tableName) \n" + -" REFERENCING NEW AS NEW \n" + -" FOR EACH ROW MODE DB2SQL \n" + +"CREATE TRIGGER $(triggerName) \n" + +" AFTER INSERT ON $(schemaName)$(tableName) \n" + +" REFERENCING NEW AS NEW \n" + +" FOR EACH ROW MODE DB2SQL \n" + " call $(prefixName)_save_data( \n" + -" case when $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then 1 else 0 end, \n" + -" '$(defaultSchema)', '$(prefixName)', '$(targetTableName)', \n" + -" '$(channelName)', 'I', $(triggerHistoryId), \n" + -" $(txIdExpression), \n" + -" $(externalSelect), \n" + -" '$(columnNames)', \n" + +" case when $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then 1 else 0 end, \n" + +" '$(defaultSchema)', '$(prefixName)', '$(targetTableName)', \n" + +" '$(channelName)', 'I', $(triggerHistoryId), \n" + +" $(txIdExpression), \n" + +" $(externalSelect), \n" + +" '$(columnNames)', \n" + " '$(pkColumnNames)') \n" ); - + - sqlTemplates.put("updateTriggerTemplate" , + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(INSERT_TRIGGER_TEMPLATE)); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, -"CREATE TRIGGER $(triggerName) \n" + -" AFTER UPDATE ON $(schemaName)$(tableName) \n" + -" REFERENCING OLD AS OLD NEW AS NEW \n" + -" FOR EACH ROW MODE DB2SQL \n" + -" call $(prefixName)_save_data( \n" + -" case when $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then 1 else 0 end, \n" + -" '$(defaultSchema)', '$(prefixName)', '$(targetTableName)', \n" + -" '$(channelName)', 'U', $(triggerHistoryId), \n" + -" $(txIdExpression), \n" + -" $(externalSelect), \n" + -" '$(columnNames)', \n" + +"CREATE TRIGGER $(triggerName) \n" + +" AFTER UPDATE ON $(schemaName)$(tableName) \n" + +" REFERENCING OLD AS OLD NEW AS NEW \n" + +" FOR EACH ROW MODE DB2SQL \n" + +" call $(prefixName)_save_data( \n" + +" case when $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then 1 else 0 end, \n" + +" '$(defaultSchema)', '$(prefixName)', '$(targetTableName)', \n" + +" '$(channelName)', 'U', $(triggerHistoryId), \n" + +" $(txIdExpression), \n" + +" $(externalSelect), \n" + +" '$(columnNames)', \n" + " '$(pkColumnNames)') \n" ); - + - sqlTemplates.put("deleteTriggerTemplate" , + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(UPDATE_TRIGGER_TEMPLATE)); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, -"CREATE TRIGGER $(triggerName) \n" + -" AFTER DELETE ON $(schemaName)$(tableName) \n" + -" REFERENCING OLD AS OLD \n" + -" FOR EACH ROW MODE DB2SQL \n" + -" call $(prefixName)_save_data( \n" + -" case when $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then 1 else 0 end, \n" + -" '$(defaultSchema)', '$(prefixName)', '$(targetTableName)', \n" + -" '$(channelName)', 'D', $(triggerHistoryId), \n" + -" $(txIdExpression), \n" + -" $(externalSelect), \n" + -" '$(columnNames)', \n" + +"CREATE TRIGGER $(triggerName) \n" + +" AFTER DELETE ON $(schemaName)$(tableName) \n" + +" REFERENCING OLD AS OLD \n" + +" FOR EACH ROW MODE DB2SQL \n" + +" call $(prefixName)_save_data( \n" + +" case when $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then 1 else 0 end, \n" + +" '$(defaultSchema)', '$(prefixName)', '$(targetTableName)', \n" + +" '$(channelName)', 'D', $(triggerHistoryId), \n" + +" $(txIdExpression), \n" + +" $(externalSelect), \n" + +" '$(columnNames)', \n" + " '$(pkColumnNames)') \n" ); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(DELETE_TRIGGER_TEMPLATE)); - + //@formatter:on } \ No newline at end of file Index: symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java (revision 7927) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java (revision ) @@ -107,7 +107,7 @@ private Date lastUpdateTime; private Object cacheLock = new Object(); - + /** * Cache the history for performance. History never changes and does not * grow big so this should be OK. @@ -211,7 +211,7 @@ saveTrigger(trigger); } } - + public void createTriggersOnChannelForTables(String channelId, Set tables, String lastUpdateBy) { for (Table table : tables) { @@ -1227,7 +1227,7 @@ for (Trigger trigger : triggers) { String sourceCatalogName = trigger.getSourceCatalogName() != null ? trigger.getSourceCatalogName() : platform.getDefaultCatalog(); String sourceSchemaName = trigger.getSourceSchemaName() != null ? trigger.getSourceSchemaName() : platform.getDefaultSchema(); - if (trigger.getSourceTableName().equals(table.getName()) + if (trigger.getSourceTableName().equals(table.getName()) && sourceCatalogName.equals(table.getCatalog()) && sourceSchemaName.equals(table.getSchema())) { return true; } else if (ignoreCase && trigger.getSourceTableName().equalsIgnoreCase(table.getName()) @@ -1283,7 +1283,7 @@ } } } - + public void syncTrigger(Trigger trigger, ITriggerCreationListener listener, boolean force) { syncTrigger(trigger, listener, force, true); } @@ -1333,7 +1333,7 @@ TriggerReBuildReason reason = TriggerReBuildReason.NEW_TRIGGERS; String errorMessage = null; - + if (verifyInDatabase) { Channel channel = configurationService.getChannel(trigger.getChannelId()); if (channel == null) { @@ -1722,13 +1722,30 @@ if (!StringUtils.isBlank(text)) { trigger.setCustomOnInsertText(text); } + + text = rs.getString("custom_on_insert_after_extract"); + if (!StringUtils.isBlank(text)) { + trigger.setCustomOnInsertAfterExtraction(Boolean.valueOf(text.trim())); + } + text = rs.getString("custom_on_update_text"); if (!StringUtils.isBlank(text)) { trigger.setCustomOnUpdateText(text); } + + text = rs.getString("custom_on_update_after_extract"); + if (!StringUtils.isBlank(text)) { + trigger.setCustomOnUpdateAfterExtraction(Boolean.valueOf(text.trim())); + } + text = rs.getString("custom_on_delete_text"); if (!StringUtils.isBlank(text)) { trigger.setCustomOnDeleteText(text); + } + + text = rs.getString("custom_on_delete_after_extract"); + if (!StringUtils.isBlank(text)) { + trigger.setCustomOnDeleteAfterExtraction(Boolean.valueOf(text.trim())); } condition = rs.getString("external_select"); @@ -1800,7 +1817,7 @@ public Map getFailedTriggers() { return this.failureListener.getFailures(); } - + public TriggerHistory findTriggerHistoryForGenericSync() { String triggerTableName = TableConstants.getTableName(tablePrefix, TableConstants.SYM_TRIGGER); Index: symmetric-core/src/main/java/org/jumpmind/symmetric/model/Trigger.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/model/Trigger.java (revision 7927) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/model/Trigger.java (revision ) @@ -88,10 +88,16 @@ private String customOnUpdateText; + private boolean customOnUpdateAfterExtraction = true; + private String customOnInsertText; + private boolean customOnInsertAfterExtraction = true; + private String customOnDeleteText; + private boolean customOnDeleteAfterExtraction = true; + private String excludedColumnNames = null; private String syncKeyNames = null; @@ -549,14 +555,20 @@ hashedValue += customOnUpdateText.hashCode(); } + hashedValue += Boolean.valueOf(customOnUpdateAfterExtraction).hashCode(); + if (null != customOnInsertText) { hashedValue += customOnInsertText.hashCode(); } + hashedValue += Boolean.valueOf(customOnInsertAfterExtraction).hashCode(); + if (null != customOnDeleteText) { hashedValue += customOnDeleteText.hashCode(); } + hashedValue += Boolean.valueOf(customOnDeleteAfterExtraction).hashCode(); + if (null != excludedColumnNames) { hashedValue += excludedColumnNames.hashCode(); } @@ -632,4 +644,47 @@ } } + public boolean isCustomOnUpdateAfterExtraction() { + + return customOnUpdateAfterExtraction; + } + + public void setCustomOnUpdateAfterExtraction(boolean customOnUpdateAfterExtraction) { + + this.customOnUpdateAfterExtraction = customOnUpdateAfterExtraction; + } + + public boolean isCustomOnInsertAfterExtraction() { + + return customOnInsertAfterExtraction; + } + + public void setCustomOnInsertAfterExtraction(boolean customOnInsertAfterExtraction) { + + this.customOnInsertAfterExtraction = customOnInsertAfterExtraction; + } + + public boolean isCustomOnDeleteAfterExtraction() { + + return customOnDeleteAfterExtraction; + } + + public void setCustomOnDeleteAfterExtraction(boolean customOnDeleteAfterExtraction) { + + this.customOnDeleteAfterExtraction = customOnDeleteAfterExtraction; + } + + public boolean isCustomAfterExtraction(String dmlName) { + + if ("insert".equals(dmlName)) + return isCustomOnInsertAfterExtraction(); + + if ("update".equals(dmlName)) + return isCustomOnUpdateAfterExtraction(); + + if ("delete".equals(dmlName)) + return isCustomOnDeleteAfterExtraction(); + + return true; + } } \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java (revision ) @@ -27,104 +27,141 @@ public class OracleTriggerTemplate extends AbstractTriggerTemplate { - public OracleTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - // @formatter:off - - emptyColumnTemplate = "''" ; - stringColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, $(oracleToClob)'', '\"'||replace(replace($(oracleToClob)$(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"')" ; - geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then to_clob('') else '\"'||replace(replace(SDO_UTIL.TO_WKTGEOMETRY($(tableAlias).\"$(columnName)\"),'\\','\\\\'),'\"','\\\"')||'\"' end"; - numberColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', '\"'||cast($(tableAlias).\"$(columnName)\" as number("+symmetricDialect.getTemplateNumberPrecisionSpec()+"))||'\"')" ; - datetimeColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.FF3')),'\"'))" ; - dateTimeWithTimeZoneColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),'\"'))" ; - timeColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS')),'\"'))" ; - dateColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS')),'\"'))" ; - clobColumnTemplate = "decode(dbms_lob.getlength($(tableAlias).\"$(columnName)\"), null, to_clob(''), '\"'||replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"')" ; - blobColumnTemplate = "decode(dbms_lob.getlength($(tableAlias).\"$(columnName)\"), null, to_clob(''), '\"'||sym_blob2clob($(tableAlias).\"$(columnName)\")||'\"')" ; - booleanColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', '\"'||cast($(tableAlias).\"$(columnName)\" as number("+symmetricDialect.getTemplateNumberPrecisionSpec()+"))||'\"')" ; - triggerConcatCharacter = "||" ; - newTriggerValue = ":new" ; - oldTriggerValue = ":old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create or replace trigger $(triggerName) \n" + -" after insert on $(schemaName)$(tableName) \n" + + "create or replace trigger $(triggerName) \n" + + " after insert on $(schemaName)$(tableName) \n" + -" for each row begin \n" + + " for each row begin \n"; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then \n" + -" insert into $(defaultSchema)$(prefixName)_data \n" + -" (table_name, event_type, trigger_hist_id, row_data, channel_id, \n" + -" transaction_id, source_node_id, external_data, create_time) \n" + -" values( \n" + -" '$(targetTableName)', \n" + -" 'I', \n" + -" $(triggerHistoryId), \n" + -" $(oracleToClob)$(columns), \n" + -" '$(channelName)', \n" + -" $(txIdExpression), \n" + -" sym_pkg.disable_node_id, \n" + -" $(externalSelect), \n" + -" CURRENT_TIMESTAMP \n" + -" ); \n" + + " if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then \n" + + " insert into $(defaultSchema)$(prefixName)_data \n" + + " (table_name, event_type, trigger_hist_id, row_data, channel_id, \n" + + " transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + + " '$(targetTableName)', \n" + + " 'I', \n" + + " $(triggerHistoryId), \n" + + " $(oracleToClob)$(columns), \n" + + " '$(channelName)', \n" + + " $(txIdExpression), \n" + + " sym_pkg.disable_node_id, \n" + + " $(externalSelect), \n" + + " CURRENT_TIMESTAMP \n" + + " ); \n" + -" end if; \n" + -" $(custom_on_insert_text) \n" + -" end; \n"); + " end if; \n"; - sqlTemplates.put("updateTriggerTemplate" , + private static final String TRIGGER_TEMPLATE_FOOTER = + " end; \n"; + + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create or replace trigger $(triggerName) after update on $(schemaName)$(tableName) \n" + + "create or replace trigger $(triggerName) after update on $(schemaName)$(tableName) \n" + -" for each row begin \n" + + " for each row begin \n"; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" declare \n" + -" var_row_data $(oracleLobType); \n" + -" var_old_data $(oracleLobType); \n" + -" begin \n" + -" if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then \n" + -" select $(oracleToClob)$(columns) into var_row_data from dual; \n" + -" select $(oracleToClob)$(oldColumns) into var_old_data from dual; \n" + -" if $(dataHasChangedCondition) then \n" + -" insert into $(defaultSchema)$(prefixName)_data \n" + -" (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + -" values( \n" + -" '$(targetTableName)', \n" + -" 'U', \n" + -" $(triggerHistoryId), \n" + -" $(oldKeys), \n" + -" var_row_data, \n" + -" var_old_data, \n" + -" '$(channelName)', \n" + -" $(txIdExpression), \n" + -" sym_pkg.disable_node_id, \n" + -" $(externalSelect), \n" + -" CURRENT_TIMESTAMP \n" + -" ); \n" + -" end if; \n" + -" end if; \n" + + " declare \n" + + " var_row_data $(oracleLobType); \n" + + " var_old_data $(oracleLobType); \n" + + " begin \n" + + " if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then \n" + + " select $(oracleToClob)$(columns) into var_row_data from dual; \n" + + " select $(oracleToClob)$(oldColumns) into var_old_data from dual; \n" + + " if $(dataHasChangedCondition) then \n" + + " insert into $(defaultSchema)$(prefixName)_data \n" + + " (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + + " '$(targetTableName)', \n" + + " 'U', \n" + + " $(triggerHistoryId), \n" + + " $(oldKeys), \n" + + " var_row_data, \n" + + " var_old_data, \n" + + " '$(channelName)', \n" + + " $(txIdExpression), \n" + + " sym_pkg.disable_node_id, \n" + + " $(externalSelect), \n" + + " CURRENT_TIMESTAMP \n" + + " ); \n" + + " end if; \n" + + " end if; \n" + -" end; \n" + -" $(custom_on_update_text) \n" + -" end; \n" ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end; \n"; + private static final String UPDATE_TRIGGER_TEMPLATE_FOOTER = + " end; \n"; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create or replace trigger $(triggerName) after delete on $(schemaName)$(tableName) \n" + + "create or replace trigger $(triggerName) after delete on $(schemaName)$(tableName) \n" + -" for each row begin \n" + + " for each row begin \n"; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then \n" + -" insert into $(defaultSchema)$(prefixName)_data \n" + -" (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + -" values( \n" + -" '$(targetTableName)', \n" + -" 'D', \n" + -" $(triggerHistoryId), \n" + -" $(oldKeys), \n" + -" $(oracleToClob)$(oldColumns), \n" + -" '$(channelName)', \n" + -" $(txIdExpression), \n" + -" sym_pkg.disable_node_id, \n" + -" $(externalSelect), \n" + -" CURRENT_TIMESTAMP \n" + -" ); \n" + + " if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then \n" + + " insert into $(defaultSchema)$(prefixName)_data \n" + + " (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + + " '$(targetTableName)', \n" + + " 'D', \n" + + " $(triggerHistoryId), \n" + + " $(oldKeys), \n" + + " $(oracleToClob)$(oldColumns), \n" + + " '$(channelName)', \n" + + " $(txIdExpression), \n" + + " sym_pkg.disable_node_id, \n" + + " $(externalSelect), \n" + + " CURRENT_TIMESTAMP \n" + + " ); \n" + -" end if; \n" + -" $(custom_on_delete_text) \n" + -" end; \n" ); + " end if; \n"; + + public OracleTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + // @formatter:off + + emptyColumnTemplate = "''" ; + stringColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, $(oracleToClob)'', '\"'||replace(replace($(oracleToClob)$(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"')" ; + geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then to_clob('') else '\"'||replace(replace(SDO_UTIL.TO_WKTGEOMETRY($(tableAlias).\"$(columnName)\"),'\\','\\\\'),'\"','\\\"')||'\"' end"; + numberColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', '\"'||cast($(tableAlias).\"$(columnName)\" as number("+symmetricDialect.getTemplateNumberPrecisionSpec()+"))||'\"')" ; + datetimeColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.FF3')),'\"'))" ; + dateTimeWithTimeZoneColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),'\"'))" ; + timeColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS')),'\"'))" ; + dateColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS')),'\"'))" ; + clobColumnTemplate = "decode(dbms_lob.getlength($(tableAlias).\"$(columnName)\"), null, to_clob(''), '\"'||replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"')" ; + blobColumnTemplate = "decode(dbms_lob.getlength($(tableAlias).\"$(columnName)\"), null, to_clob(''), '\"'||sym_blob2clob($(tableAlias).\"$(columnName)\")||'\"')" ; + booleanColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', '\"'||cast($(tableAlias).\"$(columnName)\" as number("+symmetricDialect.getTemplateNumberPrecisionSpec()+"))||'\"')" ; + triggerConcatCharacter = "||" ; + newTriggerValue = ":new" ; + oldTriggerValue = ":old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + UPDATE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(oracleToClob)$(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-assemble/src/docbook/databases.xml IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-assemble/src/docbook/databases.xml (revision 7927) +++ symmetric-assemble/src/docbook/databases.xml (revision ) @@ -675,8 +675,8 @@ Columns of type DATETIME are accurate to 1/300th of a second, which means that the last digit of the milliseconds portion will end with 0, 3, or 6. - An incoming DATETIME synced from another database will also have its millisconds rounded to one of these digits + An incoming DATETIME synced from another database will also have its milliseconds rounded to one of these digits - (0 and 1 become 0; 2, 3, and 4 become 3; 5, 6, 7, and 8 become 6; 9 becomes 10). + (0 and 1 become 0; 2, 3, and 4 become 3; 5, 6, 7, and 8 become 6; 9 becomes 10). If DATETIME is used as the primary key or as one of the columns to detect a conflict, then conflict resolution could fail unless the milliseconds are rounded in the same fashion on the source system. @@ -686,6 +686,8 @@ will install triggers that could overwrite already existing triggers on the database. New triggers created after SymmetricDS is installed will overwrite the SymmetricDS triggers. Custom trigger text can be added to the SymmetricDS triggers by modifying CUSTOM_ON_INSERT_TEXT, CUSTOM_ON_UPDATE_TEXT, and CUSTOM_ON_DELETE_TEXT on the table. + The ordering of the execution of extraction and custom text can be controlled by CUSTOM_ON_INSERT_AFTER_EXTRACT, + CUSTOM_ON_UPDATE_AFTER_EXTRACT, and CUSTOM_ON_DELETE_AFTER_EXTRACT, respectively.
Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/informix/InformixTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/informix/InformixTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/informix/InformixTriggerTemplate.java (revision ) @@ -27,75 +27,113 @@ public class InformixTriggerTemplate extends AbstractTriggerTemplate { - public InformixTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "rtrim(case when $(tableAlias).$(columnName) is null then '' else '\"' || replace(replace($(tableAlias).$(columnName), '\\', '\\\\'), '\"', '\\\"') || '\"' end)" ; - numberColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' else '\"' || $(tableAlias).$(columnName) || '\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' else '\"' || $(tableAlias).$(columnName) || '\"' end" ; - clobColumnTemplate = "''" ; - blobColumnTemplate = "''" ; - booleanColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' when $(tableAlias).$(columnName) then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "||" ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) insert on $(schemaName)$(tableName) " + + "create trigger $(triggerName) insert on $(schemaName)$(tableName) " + -" referencing new as new " + + " referencing new as new "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" for each row when ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) ( " + -" insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'I', " + -" $(triggerHistoryId), " + -" $(columns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT " + + " for each row when ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) ( " + + " insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'I', " + + " $(triggerHistoryId), " + + " $(columns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT " + -" )); $(custom_on_insert_text) " ); - - sqlTemplates.put("updateTriggerTemplate" , + " )); "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) update on $(schemaName)$(tableName) " + + "create trigger $(triggerName) update on $(schemaName)$(tableName) " + -" referencing old as old new as new " + + " referencing old as old new as new "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" for each row when ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) ( " + -" insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'U', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(columns), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT " + + " for each row when ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) ( " + + " insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'U', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(columns), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT " + -" )); $(custom_on_update_text) " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " ));"; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) delete on $(schemaName)$(tableName) " + + "create trigger $(triggerName) delete on $(schemaName)$(tableName) " + -" referencing old as old " + + " referencing old as old "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" for each row when ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) ( " + -" insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'D', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT " + + " for each row when ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) ( " + + " insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'D', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT " + -" )); $(custom_on_delete_text) " ); + " ));"; + + public InformixTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "rtrim(case when $(tableAlias).$(columnName) is null then '' else '\"' || replace(replace($(tableAlias).$(columnName), '\\', '\\\\'), '\"', '\\\"') || '\"' end)" ; + numberColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' else '\"' || $(tableAlias).$(columnName) || '\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' else '\"' || $(tableAlias).$(columnName) || '\"' end" ; + clobColumnTemplate = "''" ; + blobColumnTemplate = "''" ; + booleanColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' when $(tableAlias).$(columnName) then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "||" ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + ); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + ); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + ); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + ); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + ); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java (revision ) @@ -34,172 +34,214 @@ public class MsSqlTriggerTemplate extends AbstractTriggerTemplate { - public MsSqlTriggerTemplate() { - super(); - } - - public MsSqlTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - - // @formatter:off - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(max),$(tableAlias).\"$(columnName)\") $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(max),$(tableAlias).\"$(columnName)\".STAsText()) $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar, $(tableAlias).\"$(columnName)\",2) + '\"') end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar,$(tableAlias).\"$(columnName)\",121) + '\"') end" ; - clobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(origTableAlias).\"$(columnName)\" as varchar(max)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - blobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace($(defaultCatalog)dbo.sym_base64_encode(CONVERT(VARBINARY(max), $(origTableAlias).\"$(columnName)\")),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "+" ; - newTriggerValue = "inserted" ; - oldTriggerValue = "deleted" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) after insert as " + -" begin " + -" declare @NCT int " + -" set @NCT = @@OPTIONS & 512 " + -" set nocount on " + -" declare @TransactionId varchar(1000) " + -" declare @DataRow varchar(max) " + -" $(declareNewKeyVariables) " + -" if (@@TRANCOUNT > 0) begin " + -" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + + "create trigger $(triggerName) on $(schemaName)$(tableName) after insert as " + + " begin " + + " declare @NCT int " + + " set @NCT = @@OPTIONS & 512 " + + " set nocount on " + + " declare @TransactionId varchar(1000) " + + " declare @DataRow varchar(max) " + + " $(declareNewKeyVariables) " + + " if (@@TRANCOUNT > 0) begin " + + " select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + -" end " + + " end "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor local for " + -" $(if:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + -" $(end:containsBlobClobColumns) " + -" open DataCursor " + -" fetch next from DataCursor into @DataRow $(newKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp) " + -" fetch next from DataCursor into @DataRow $(newKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate DataCursor " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor local for " + + " $(if:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + + " $(end:containsBlobClobColumns) " + + " open DataCursor " + + " fetch next from DataCursor into @DataRow $(newKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp) " + + " fetch next from DataCursor into @DataRow $(newKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate DataCursor " + -" end " + -" $(custom_on_insert_text) " + + " end "; + private static final String MS_SQL_TRIGGER_TEMPLATE_FOOTER = -" if (@NCT = 0) set nocount off " + + " if (@NCT = 0) set nocount off " + -" end " ); - - sqlTemplates.put("updateTriggerTemplate" , + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) after update as " + -" begin " + -" declare @NCT int " + -" set @NCT = @@OPTIONS & 512 " + -" set nocount on " + -" declare @TransactionId varchar(1000) " + -" declare @DataRow varchar(max) " + -" declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(max) " + -" $(declareOldKeyVariables) " + -" $(declareNewKeyVariables) " + -" if (@@TRANCOUNT > 0) begin " + -" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + + "create trigger $(triggerName) on $(schemaName)$(tableName) after update as " + + " begin " + + " declare @NCT int " + + " set @NCT = @@OPTIONS & 512 " + + " set nocount on " + + " declare @TransactionId varchar(1000) " + + " declare @DataRow varchar(max) " + + " declare @OldPk varchar(2000) " + + " declare @OldDataRow varchar(max) " + + " $(declareOldKeyVariables) " + + " $(declareNewKeyVariables) " + + " if (@@TRANCOUNT > 0) begin " + + " select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + -" end " + + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor local for " + -" $(if:containsBlobClobColumns) " + -" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " + -" $(end:containsBlobClobColumns) " + -" open DataCursor " + -" fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" + -" fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate DataCursor " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor local for " + + " $(if:containsBlobClobColumns) " + + " select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " + + " $(end:containsBlobClobColumns) " + + " open DataCursor " + + " fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" + + " fetch next from DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate DataCursor " + -" end " + -" $(custom_on_update_text) " + -" if (@NCT = 0) set nocount off " + -" end " ); - - sqlTemplates.put("updateHandleKeyUpdatesTriggerTemplate" , + " end "; + private static final String UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE = + "updateHandleKeyUpdatesTriggerTemplate"; + private static final String UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) after update as " + -" begin " + -" declare @NCT int " + -" set @NCT = @@OPTIONS & 512 " + -" set nocount on " + -" declare @TransactionId varchar(1000) " + -" declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(max) " + -" declare @DataRow varchar(max) " + -" $(declareOldKeyVariables) " + -" $(declareNewKeyVariables) " + -" " + -" if (@@TRANCOUNT > 0) begin " + -" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + -" end " + + "create trigger $(triggerName) on $(schemaName)$(tableName) after update as " + + " begin " + + " declare @NCT int " + + " set @NCT = @@OPTIONS & 512 " + + " set nocount on " + + " declare @TransactionId varchar(1000) " + + " declare @OldPk varchar(2000) " + + " declare @OldDataRow varchar(max) " + + " declare @DataRow varchar(max) " + + " $(declareOldKeyVariables) " + + " $(declareNewKeyVariables) " + + " " + + " if (@@TRANCOUNT > 0) begin " + + " select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + + " end " + -" if ($(syncOnIncomingBatchCondition)) begin " + + " if ($(syncOnIncomingBatchCondition)) begin "; + private static final String HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_BODY = -" declare DeleteCursor cursor local for " + -" select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + -" declare InsertCursor cursor local for " + -" $(if:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + -" $(end:containsBlobClobColumns) " + -" open DeleteCursor " + -" open InsertCursor " + -" fetch next from DeleteCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + -" fetch next from InsertCursor into @DataRow $(newKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" + -" fetch next from DeleteCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + -" fetch next from InsertCursor into @DataRow $(newKeyVariables) " + -" end " + -" close DeleteCursor " + -" close InsertCursor " + -" deallocate DeleteCursor " + -" deallocate InsertCursor " + + " declare DeleteCursor cursor local for " + + " select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + + " declare InsertCursor cursor local for " + + " $(if:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + + " $(end:containsBlobClobColumns) " + + " open DeleteCursor " + + " open InsertCursor " + + " fetch next from DeleteCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + + " fetch next from InsertCursor into @DataRow $(newKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" + + " fetch next from DeleteCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + + " fetch next from InsertCursor into @DataRow $(newKeyVariables) " + + " end " + + " close DeleteCursor " + + " close InsertCursor " + + " deallocate DeleteCursor " + + " deallocate InsertCursor " + -" end " + -" $(custom_on_update_text) " + -" if (@NCT = 0) set nocount off " + -" end " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) after delete as " + -" begin " + -" declare @NCT int " + -" set @NCT = @@OPTIONS & 512 " + -" set nocount on " + -" declare @TransactionId varchar(1000) " + -" declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(max) " + -" $(declareOldKeyVariables) " + -" if (@@TRANCOUNT > 0) begin " + -" select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + + "create trigger $(triggerName) on $(schemaName)$(tableName) after delete as " + + " begin " + + " declare @NCT int " + + " set @NCT = @@OPTIONS & 512 " + + " set nocount on " + + " declare @TransactionId varchar(1000) " + + " declare @OldPk varchar(2000) " + + " declare @OldDataRow varchar(max) " + + " $(declareOldKeyVariables) " + + " if (@@TRANCOUNT > 0) begin " + + " select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 " + -" end " + + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor local for " + -" select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + -" open DataCursor " + -" fetch next from DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + -" while @@FETCH_STATUS = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" + -" fetch next from DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate DataCursor " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor local for " + + " select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + + " open DataCursor " + + " fetch next from DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + + " while @@FETCH_STATUS = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)dbo.sym_node_disabled(), $(externalSelect), current_timestamp)" + + " fetch next from DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate DataCursor " + -" end " + -" $(custom_on_delete_text) " + -" if (@NCT = 0) set nocount off " + -" end " ); + " end "; + + public MsSqlTriggerTemplate() { + super(); + } + + public MsSqlTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + + // @formatter:off + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(max),$(tableAlias).\"$(columnName)\") $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(max),$(tableAlias).\"$(columnName)\".STAsText()) $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar, $(tableAlias).\"$(columnName)\",2) + '\"') end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar,$(tableAlias).\"$(columnName)\",121) + '\"') end" ; + clobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(origTableAlias).\"$(columnName)\" as varchar(max)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + blobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace($(defaultCatalog)dbo.sym_base64_encode(CONVERT(VARBINARY(max), $(origTableAlias).\"$(columnName)\")),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "+" ; + newTriggerValue = "inserted" ; + oldTriggerValue = "deleted" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE , + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE, + UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_HEADER + + HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_BODY + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + MS_SQL_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/interbase/InterbaseTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/interbase/InterbaseTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/interbase/InterbaseTriggerTemplate.java (revision ) @@ -27,110 +27,144 @@ public class InterbaseTriggerTemplate extends AbstractTriggerTemplate { - public InterbaseTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_escape($(tableAlias).\"$(columnName)\") || '\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; - clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_escape($(tableAlias).\"$(columnName)\") || '\"' end" ; - blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_hex($(tableAlias).\"$(columnName)\") || '\"' end" ; - triggerConcatCharacter = "||" ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) for $(schemaName)$(tableName) after insert as " + -" declare variable id integer; " + -" declare variable sync_triggers_disabled varchar(30); " + -" declare variable sync_node_disabled varchar(30); " + + "create trigger $(triggerName) for $(schemaName)$(tableName) after insert as " + + " declare variable id integer; " + + " declare variable sync_triggers_disabled varchar(30); " + + " declare variable sync_node_disabled varchar(30); " + -" begin " + + " begin "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" select context_value from $(prefixName)_context where id = 'sync_triggers_disabled' into :sync_triggers_disabled; " + -" if ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) then " + -" begin " + -" select context_value from $(prefixName)_context where id = 'sync_node_disabled' into :sync_node_disabled; " + -" select gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1) from rdb$database into :id; " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (data_id, table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" :id, " + -" '$(targetTableName)', " + -" 'I', " + -" $(triggerHistoryId), " + -" $(columns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" :sync_node_disabled, " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " select context_value from $(prefixName)_context where id = 'sync_triggers_disabled' into :sync_triggers_disabled; " + + " if ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) then " + + " begin " + + " select context_value from $(prefixName)_context where id = 'sync_node_disabled' into :sync_node_disabled; " + + " select gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1) from rdb$database into :id; " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (data_id, table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " :id, " + + " '$(targetTableName)', " + + " 'I', " + + " $(triggerHistoryId), " + + " $(columns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " :sync_node_disabled, " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end " + -" $(custom_on_insert_text) " + -" end " ); - - sqlTemplates.put("updateTriggerTemplate" , + " end "; + private static final String INTERBASE_TRIGGER_TEMPLATE_FOOTER = + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) for $(schemaName)$(tableName) after update as " + -" declare variable id integer; " + -" declare variable sync_triggers_disabled varchar(30); " + -" declare variable sync_node_disabled varchar(30); " + + "create trigger $(triggerName) for $(schemaName)$(tableName) after update as " + + " declare variable id integer; " + + " declare variable sync_triggers_disabled varchar(30); " + + " declare variable sync_node_disabled varchar(30); " + -" begin " + + " begin "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" select context_value from $(prefixName)_context where id = 'sync_triggers_disabled' into :sync_triggers_disabled; " + -" if ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) then " + -" begin " + -" select context_value from $(prefixName)_context where id = 'sync_node_disabled' into :sync_node_disabled; " + -" select gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1) from rdb$database into :id; " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (data_id, table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" :id, " + -" '$(targetTableName)', " + -" 'U', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(columns), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" :sync_node_disabled, " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " select context_value from $(prefixName)_context where id = 'sync_triggers_disabled' into :sync_triggers_disabled; " + + " if ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) then " + + " begin " + + " select context_value from $(prefixName)_context where id = 'sync_node_disabled' into :sync_node_disabled; " + + " select gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1) from rdb$database into :id; " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (data_id, table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " :id, " + + " '$(targetTableName)', " + + " 'U', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(columns), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " :sync_node_disabled, " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end " + -" $(custom_on_update_text) " + -" end " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) for $(schemaName)$(tableName) after delete as " + -" declare variable id integer; " + -" declare variable sync_triggers_disabled varchar(30); " + -" declare variable sync_node_disabled varchar(30); " + + "create trigger $(triggerName) for $(schemaName)$(tableName) after delete as " + + " declare variable id integer; " + + " declare variable sync_triggers_disabled varchar(30); " + + " declare variable sync_node_disabled varchar(30); " + -" begin " + + " begin "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" select context_value from $(prefixName)_context where id = 'sync_triggers_disabled' into :sync_triggers_disabled; " + -" if ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) then " + -" begin " + -" select context_value from $(prefixName)_context where id = 'sync_node_disabled' into :sync_node_disabled; " + -" select gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1) from rdb$database into :id; " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (data_id, table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" :id, " + -" '$(targetTableName)', " + -" 'D', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" :sync_node_disabled, " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " select context_value from $(prefixName)_context where id = 'sync_triggers_disabled' into :sync_triggers_disabled; " + + " if ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) then " + + " begin " + + " select context_value from $(prefixName)_context where id = 'sync_node_disabled' into :sync_node_disabled; " + + " select gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1) from rdb$database into :id; " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (data_id, table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " :id, " + + " '$(targetTableName)', " + + " 'D', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " :sync_node_disabled, " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end " + -" $(custom_on_delete_text) " + -" end " ); + " end "; + + public InterbaseTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_escape($(tableAlias).\"$(columnName)\") || '\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; + clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_escape($(tableAlias).\"$(columnName)\") || '\"' end" ; + blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_hex($(tableAlias).\"$(columnName)\") || '\"' end" ; + triggerConcatCharacter = "||" ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE , + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + INTERBASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + INTERBASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + INTERBASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + INTERBASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE , + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + INTERBASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + INTERBASE_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select sym_rtrim($(columns))||'' from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/firebird/FirebirdTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/firebird/FirebirdTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/firebird/FirebirdTriggerTemplate.java (revision ) @@ -27,98 +27,134 @@ public class FirebirdTriggerTemplate extends AbstractTriggerTemplate { - public FirebirdTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - - // @formatter:off - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || REPLACE(REPLACE($(tableAlias).\"$(columnName)\", '\\', '\\\\'), '\"', '\\\"') || '\"' end"; - clobColumnTemplate = stringColumnTemplate; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; - blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_hex($(tableAlias).\"$(columnName)\") || '\"' end" ; - triggerConcatCharacter = "||" ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) for $(schemaName)$(tableName) after insert as \n" + -" declare variable id bigint; \n" + + "create trigger $(triggerName) for $(schemaName)$(tableName) after insert as \n" + + " declare variable id bigint; \n" + -" begin \n" + + " begin \n"; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) then \n" + -" begin \n" + -" id = gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1); \n" + -" insert into $(defaultSchema)$(prefixName)_data \n" + -" (data_id, table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + -" values( \n" + -" :id, \n" + -" '$(targetTableName)', \n" + -" 'I', \n" + -" $(triggerHistoryId), \n" + -" $(columns), \n" + -" '$(channelName)', \n" + -" $(txIdExpression), \n" + -" rdb$get_context('USER_SESSION', 'sync_node_disabled'), \n" + -" $(externalSelect), \n" + -" CURRENT_TIMESTAMP \n" + -" ); \n" + + " if ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) then \n" + + " begin \n" + + " id = gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1); \n" + + " insert into $(defaultSchema)$(prefixName)_data \n" + + " (data_id, table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + + " :id, \n" + + " '$(targetTableName)', \n" + + " 'I', \n" + + " $(triggerHistoryId), \n" + + " $(columns), \n" + + " '$(channelName)', \n" + + " $(txIdExpression), \n" + + " rdb$get_context('USER_SESSION', 'sync_node_disabled'), \n" + + " $(externalSelect), \n" + + " CURRENT_TIMESTAMP \n" + + " ); \n" + -" end \n" + -" $(custom_on_insert_text) \n" + -" end \n" ); - sqlTemplates.put("updateTriggerTemplate" , + " end \n"; + private static final String FIREBIRD_TRIGGER_TEMPLATE_FOOTER = + " end \n"; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) for $(schemaName)$(tableName) after update as \n" + -" declare variable id bigint; \n" + + "create trigger $(triggerName) for $(schemaName)$(tableName) after update as \n" + + " declare variable id bigint; \n" + -" begin \n" + + " begin \n"; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) then \n" + -" begin \n" + -" id = gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1); \n" + -" insert into $(defaultSchema)$(prefixName)_data \n" + -" (data_id, table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + -" values( \n" + -" :id, \n" + -" '$(targetTableName)', \n" + -" 'U', \n" + -" $(triggerHistoryId), \n" + -" $(oldKeys), \n" + -" $(columns), \n" + -" $(oldColumns), \n" + -" '$(channelName)', \n" + -" $(txIdExpression), \n" + -" rdb$get_context('USER_SESSION', 'sync_node_disabled'), \n" + -" $(externalSelect), \n" + -" CURRENT_TIMESTAMP \n" + -" ); \n" + + " if ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) then \n" + + " begin \n" + + " id = gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1); \n" + + " insert into $(defaultSchema)$(prefixName)_data \n" + + " (data_id, table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + + " :id, \n" + + " '$(targetTableName)', \n" + + " 'U', \n" + + " $(triggerHistoryId), \n" + + " $(oldKeys), \n" + + " $(columns), \n" + + " $(oldColumns), \n" + + " '$(channelName)', \n" + + " $(txIdExpression), \n" + + " rdb$get_context('USER_SESSION', 'sync_node_disabled'), \n" + + " $(externalSelect), \n" + + " CURRENT_TIMESTAMP \n" + + " ); \n" + -" end \n" + -" $(custom_on_update_text) \n" + -" end \n" ); - sqlTemplates.put("deleteTriggerTemplate" , + " end \n"; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) for $(schemaName)$(tableName) after delete as \n" + -" declare variable id bigint; \n" + + "create trigger $(triggerName) for $(schemaName)$(tableName) after delete as \n" + + " declare variable id bigint; \n" + -" begin \n" + + " begin \n"; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) then \n" + -" begin \n" + -" id = gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1); \n" + -" insert into $(defaultSchema)$(prefixName)_data \n" + -" (data_id, table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + -" values( \n" + -" :id, \n" + -" '$(targetTableName)', \n" + -" 'D', \n" + -" $(triggerHistoryId), \n" + -" $(oldKeys), \n" + -" $(oldColumns), \n" + -" '$(channelName)', \n" + -" $(txIdExpression), \n" + -" rdb$get_context('USER_SESSION', 'sync_node_disabled'), \n" + -" $(externalSelect), \n" + -" CURRENT_TIMESTAMP \n" + -" ); \n" + + " if ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) then \n" + + " begin \n" + + " id = gen_id($(defaultSchema)GEN_$(prefixName)_data_data_id, 1); \n" + + " insert into $(defaultSchema)$(prefixName)_data \n" + + " (data_id, table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + + " :id, \n" + + " '$(targetTableName)', \n" + + " 'D', \n" + + " $(triggerHistoryId), \n" + + " $(oldKeys), \n" + + " $(oldColumns), \n" + + " '$(channelName)', \n" + + " $(txIdExpression), \n" + + " rdb$get_context('USER_SESSION', 'sync_node_disabled'), \n" + + " $(externalSelect), \n" + + " CURRENT_TIMESTAMP \n" + + " ); \n" + -" end \n" + -" $(custom_on_delete_text) \n" + -" end \n" ); + " end \n"; + + public FirebirdTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + + // @formatter:off + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || REPLACE(REPLACE($(tableAlias).\"$(columnName)\", '\\', '\\\\'), '\"', '\\\"') || '\"' end"; + clobColumnTemplate = stringColumnTemplate; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(tableAlias).\"$(columnName)\" || '\"' end" ; + blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || sym_hex($(tableAlias).\"$(columnName)\") || '\"' end" ; + triggerConcatCharacter = "||" ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + FIREBIRD_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + FIREBIRD_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE , + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + FIREBIRD_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + FIREBIRD_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE , + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + FIREBIRD_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + FIREBIRD_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlTriggerTemplate.java (revision ) @@ -27,133 +27,158 @@ public class PostgreSqlTriggerTemplate extends AbstractTriggerTemplate { - public PostgreSqlTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - //@formatter:off - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - xmlColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - arrayColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || cast(cast($(tableAlias).\"$(columnName)\" as numeric) as varchar) || '\"' end" ; - dateColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS') || '\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US') || '\"' end" ; - dateTimeWithTimeZoneColumnTemplate = - "case when $(tableAlias).\"$(columnName)\" is null then '' else " + - " case " + - " when extract(timezone_hour from $(tableAlias).\"$(columnName)\") <= 0 and " + - " extract(timezone_minute from $(tableAlias).\"$(columnName)\") <= 0 then " + - " '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US ')||'-'|| " + - " lpad(cast(abs(extract(timezone_hour from $(tableAlias).\"$(columnName)\")) as varchar),2,'0')||':'|| " + - " lpad(cast(abs(extract(timezone_minute from $(tableAlias).\"$(columnName)\")) as varchar), 2, '0') || '\"' " + - " when extract(timezone_hour from $(tableAlias).\"$(columnName)\") = 0 and " + - " extract(timezone_minute from $(tableAlias).\"$(columnName)\") >= 0 then " + - " '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| " + - " lpad(cast(round(extract(timezone_hour from $(tableAlias).\"$(columnName)\")) as varchar),2,'0')||':'|| " + - " lpad(cast(round(extract(timezone_minute from $(tableAlias).\"$(columnName)\")) as varchar), 2, '0') || '\"' " + - " else " + - " '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| " + - " lpad(cast(extract(timezone_hour from $(tableAlias).\"$(columnName)\") as varchar),2,'0')||':'|| " + - " lpad(cast(extract(timezone_minute from $(tableAlias).\"$(columnName)\") as varchar), 2, '0') || '\"' " + - " end " + - "end "; - clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || pg_catalog.encode($(tableAlias).\"$(columnName)\", 'base64') || '\"' end" ; - wrappedBlobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(defaultSchema)$(prefixName)_largeobject($(tableAlias).\"$(columnName)\") || '\"' end" ; - booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "||" ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - otherColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || cast($(tableAlias).\"$(columnName)\" as varchar) || '\"' end" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String POSTRESQL_TRIGGER_TEMPLATE_HEADER = -"create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + + "create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + -" begin " + + " begin "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" '$(targetTableName)', " + -" 'I', " + -" $(triggerHistoryId), " + -" $(columns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " '$(targetTableName)', " + + " 'I', " + + " $(triggerHistoryId), " + + " $(columns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_insert_text) " + + " end if; "; + private static final String POSTRESQL_TRIGGER_TEMPLATE_FOOTER = -" return null; " + -" end; " + + " return null; " + + " end; " + -" $function$ language plpgsql; " ); - - sqlTemplates.put("insertPostTriggerTemplate" , -"create trigger $(triggerName) after insert on $(schemaName)$(tableName) " + -" for each row execute procedure $(schemaName)f$(triggerName)(); " ); - - sqlTemplates.put("updateTriggerTemplate" , -"create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + -" begin " + + " $function$ language plpgsql; "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" '$(targetTableName)', " + -" 'U', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(columns), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " '$(targetTableName)', " + + " 'U', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(columns), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_update_text) " + -" return null; " + -" end; " + -" $function$ language plpgsql; " ); - - sqlTemplates.put("updatePostTriggerTemplate" , -"create trigger $(triggerName) after update on $(schemaName)$(tableName) " + -" for each row execute procedure $(schemaName)f$(triggerName)(); " ); - - sqlTemplates.put("deleteTriggerTemplate" , -"create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + -" begin " + + " end if; "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" '$(targetTableName)', " + -" 'D', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " '$(targetTableName)', " + + " 'D', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_delete_text) " + -" return null; " + -" end; " + -" $function$ language plpgsql; " ); + " end if; "; - sqlTemplates.put("deletePostTriggerTemplate" , -"create trigger $(triggerName) after delete on $(schemaName)$(tableName) " + -" for each row execute procedure $(schemaName)f$(triggerName)(); " ); + public PostgreSqlTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + //@formatter:off + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + xmlColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + arrayColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || cast(cast($(tableAlias).\"$(columnName)\" as numeric) as varchar) || '\"' end" ; + dateColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS') || '\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US') || '\"' end" ; + dateTimeWithTimeZoneColumnTemplate = + "case when $(tableAlias).\"$(columnName)\" is null then '' else " + + " case " + + " when extract(timezone_hour from $(tableAlias).\"$(columnName)\") <= 0 and " + + " extract(timezone_minute from $(tableAlias).\"$(columnName)\") <= 0 then " + + " '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US ')||'-'|| " + + " lpad(cast(abs(extract(timezone_hour from $(tableAlias).\"$(columnName)\")) as varchar),2,'0')||':'|| " + + " lpad(cast(abs(extract(timezone_minute from $(tableAlias).\"$(columnName)\")) as varchar), 2, '0') || '\"' " + + " when extract(timezone_hour from $(tableAlias).\"$(columnName)\") = 0 and " + + " extract(timezone_minute from $(tableAlias).\"$(columnName)\") >= 0 then " + + " '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| " + + " lpad(cast(round(extract(timezone_hour from $(tableAlias).\"$(columnName)\")) as varchar),2,'0')||':'|| " + + " lpad(cast(round(extract(timezone_minute from $(tableAlias).\"$(columnName)\")) as varchar), 2, '0') || '\"' " + + " else " + + " '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.US ')||'+'|| " + + " lpad(cast(extract(timezone_hour from $(tableAlias).\"$(columnName)\") as varchar),2,'0')||':'|| " + + " lpad(cast(extract(timezone_minute from $(tableAlias).\"$(columnName)\") as varchar), 2, '0') || '\"' " + + " end " + + "end "; + clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || pg_catalog.encode($(tableAlias).\"$(columnName)\", 'base64') || '\"' end" ; + wrappedBlobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(defaultSchema)$(prefixName)_largeobject($(tableAlias).\"$(columnName)\") || '\"' end" ; + booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "||" ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + otherColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || cast($(tableAlias).\"$(columnName)\" as varchar) || '\"' end" ; + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE , + POSTRESQL_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + POSTRESQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + POSTRESQL_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + POSTRESQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put("insertPostTriggerTemplate", postTriggerTemplate("insert")); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + POSTRESQL_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + POSTRESQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + POSTRESQL_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + POSTRESQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put("updatePostTriggerTemplate", postTriggerTemplate("update")); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + POSTRESQL_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + POSTRESQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + POSTRESQL_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + POSTRESQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put("deletePostTriggerTemplate", postTriggerTemplate("delete")); + sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); + } + + private String postTriggerTemplate(String changeType) { + + return + "create trigger $(triggerName) after " + changeType + " on $(schemaName)$(tableName) " + + " for each row execute procedure $(schemaName)f$(triggerName)(); "; } @Override \ No newline at end of file Index: symmetric-core/src/main/java/org/jumpmind/symmetric/db/sqlite/SqliteTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/db/sqlite/SqliteTriggerTemplate.java (revision 7927) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/db/sqlite/SqliteTriggerTemplate.java (revision ) @@ -27,6 +27,36 @@ public class SqliteTriggerTemplate extends AbstractTriggerTemplate { + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) after insert on $(schemaName)$(tableName) \n" + + "for each row \n" + + " when ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) \n" + + " begin \n"; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + " '$(targetTableName)', \n" + " 'I', \n" + + " $(triggerHistoryId), \n" + + " $(columns), \n" + " '$(channelName)', null,(select context_value from $(prefixName)_context where id = 'sync_node_disabled'), \n" + + " $(externalSelect), \n" + " strftime('%Y-%m-%d %H:%M:%f','now','localtime') \n" + " ); \n"; + private static final String SQL_LITE_TRIGGER_TEMPLATE_FOOTER = "end"; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) after update on $(schemaName)$(tableName) \n" + + "for each row \n" + + " when ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) \n" + + " begin \n"; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + " '$(targetTableName)', \n" + " 'U', \n" + + " $(triggerHistoryId), \n" + " $(oldKeys), \n" + + " $(columns), \n" + " $(oldColumns), \n" + + " '$(channelName)', null,(select context_value from $(prefixName)_context where id = 'sync_node_disabled'), \n" + " $(externalSelect), \n" + + " strftime('%Y-%m-%d %H:%M:%f','now','localtime') \n" + " ); \n"; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) after delete on $(schemaName)$(tableName) \n" + + "for each row \n" + + " when ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) \n" + + " begin \n"; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" + + " values( \n" + " '$(targetTableName)', \n" + " 'D', \n" + + " $(triggerHistoryId), \n" + " $(oldKeys), \n" + + " $(oldColumns), \n" + " '$(channelName)', null,(select context_value from $(prefixName)_context where id = 'sync_node_disabled'), \n" + + " $(externalSelect), \n" + " strftime('%Y-%m-%d %H:%M:%f','now','localtime') \n" + " ); \n"; + public SqliteTriggerTemplate(AbstractSymmetricDialect symmetricDialect) { super(symmetricDialect); @@ -43,48 +73,42 @@ blobColumnTemplate = "case when $(tableAlias).$(columnName) is null then '' else '\"' || replace(replace(hex($(tableAlias).$(columnName)),'\\','\\\\'),'\"','\\\"') || '\"' end "; sqlTemplates = new HashMap(); - sqlTemplates - .put("insertTriggerTemplate", - "create trigger $(triggerName) after insert on $(schemaName)$(tableName) \n" - + "for each row \n" - + " when ($(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)) \n" - + " begin \n" - + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" - + " values( \n" + " '$(targetTableName)', \n" + " 'I', \n" - + " $(triggerHistoryId), \n" - + " $(columns), \n" + " '$(channelName)', null,(select context_value from $(prefixName)_context where id = 'sync_node_disabled'), \n" - + " $(externalSelect), \n" + " strftime('%Y-%m-%d %H:%M:%f','now','localtime') \n" + " ); \n" - + " $(custom_on_insert_text) \n" - + "end"); - sqlTemplates - .put("updateTriggerTemplate", - "create trigger $(triggerName) after update on $(schemaName)$(tableName) \n" - + "for each row \n" - + " when ($(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)) \n" - + " begin \n" - + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" - + " values( \n" + " '$(targetTableName)', \n" + " 'U', \n" - + " $(triggerHistoryId), \n" + " $(oldKeys), \n" - + " $(columns), \n" + " $(oldColumns), \n" - + " '$(channelName)', null,(select context_value from $(prefixName)_context where id = 'sync_node_disabled'), \n" + " $(externalSelect), \n" - + " strftime('%Y-%m-%d %H:%M:%f','now','localtime') \n" + " ); \n" - + " $(custom_on_insert_text) \n" - + "end "); + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + " " + CUSTOM_ON_INSERT_TEXT + " \n" + + SQL_LITE_TRIGGER_TEMPLATE_FOOTER); - sqlTemplates - .put("deleteTriggerTemplate", - "create trigger $(triggerName) after delete on $(schemaName)$(tableName) \n" - + "for each row \n" - + " when ($(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)) \n" - + " begin \n" - + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) \n" - + " values( \n" + " '$(targetTableName)', \n" + " 'D', \n" - + " $(triggerHistoryId), \n" + " $(oldKeys), \n" - + " $(oldColumns), \n" + " '$(channelName)', null,(select context_value from $(prefixName)_context where id = 'sync_node_disabled'), \n" - + " $(externalSelect), \n" + " strftime('%Y-%m-%d %H:%M:%f','now','localtime') \n" + " ); \n" - + " $(custom_on_insert_text) \n" - + "end"); + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + " " + CUSTOM_ON_INSERT_TEXT + " \n" + + INSERT_TRIGGER_TEMPLATE_BODY + + SQL_LITE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + " " + CUSTOM_ON_UPDATE_TEXT + " \n" + + SQL_LITE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + " " + CUSTOM_ON_UPDATE_TEXT + " \n" + + UPDATE_TRIGGER_TEMPLATE_BODY + + SQL_LITE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + " " + CUSTOM_ON_DELETE_TEXT + " \n" + + SQL_LITE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + " " + CUSTOM_ON_DELETE_TEXT + " \n" + + DELETE_TRIGGER_TEMPLATE_BODY + + SQL_LITE_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate", "select $(columns) from $(schemaName)$(tableName) t where $(whereClause)"); Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/MySqlTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/MySqlTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/MySqlTriggerTemplate.java (revision ) @@ -27,89 +27,123 @@ public class MySqlTriggerTemplate extends AbstractTriggerTemplate { - public MySqlTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',replace(replace($(tableAlias).`$(columnName)`,'\\\\','\\\\\\\\'),'\"','\\\\\"'),'\"'))" ; - geometryColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',replace(replace(astext($(tableAlias).`$(columnName)`),'\\\\','\\\\\\\\'),'\"','\\\\\"'),'\"'))" ; - numberColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',cast($(tableAlias).`$(columnName)` as char),'\"'))" ; - datetimeColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',cast($(tableAlias).`$(columnName)` as char),'\"'))" ; - clobColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',replace(replace($(tableAlias).`$(columnName)`,'\\\\','\\\\\\\\'),'\"','\\\\\"'),'\"'))" ; - blobColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',hex($(tableAlias).`$(columnName)`),'\"'))" ; - booleanColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',cast($(tableAlias).`$(columnName)` as unsigned),'\"'))" ; - triggerConcatCharacter = "," ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) after insert on $(schemaName)$(tableName) " + + "create trigger $(triggerName) after insert on $(schemaName)$(tableName) " + -" for each row begin " + + " for each row begin "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'I', " + -" $(triggerHistoryId), " + -" concat($(columns) " + -" ), " + -" '$(channelName)', $(txIdExpression), @sync_node_disabled, " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'I', " + + " $(triggerHistoryId), " + + " concat($(columns) " + + " ), " + + " '$(channelName)', $(txIdExpression), @sync_node_disabled, " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_insert_text) " + -" end " ); - - sqlTemplates.put("updateTriggerTemplate" , + " end if; "; + private static final String MYSQL_TRIGGER_TEMPLATE_FOOTER = + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) after update on $(schemaName)$(tableName) " + -" for each row begin " + -" DECLARE var_row_data mediumtext character set utf8; " + + "create trigger $(triggerName) after update on $(schemaName)$(tableName) " + + " for each row begin " + + " DECLARE var_row_data mediumtext character set utf8; " + -" DECLARE var_old_data mediumtext character set utf8; " + + " DECLARE var_old_data mediumtext character set utf8; "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + -" set var_row_data = concat($(columns)); " + -" set var_old_data = concat($(oldColumns)); " + -" if $(dataHasChangedCondition) then " + -" insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'U', " + -" $(triggerHistoryId), " + -" concat($(oldKeys) " + -" ), " + -" var_row_data, " + -" var_old_data, " + -" '$(channelName)', $(txIdExpression), @sync_node_disabled, " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + -" end if; " + + " if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + + " set var_row_data = concat($(columns)); " + + " set var_old_data = concat($(oldColumns)); " + + " if $(dataHasChangedCondition) then " + + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'U', " + + " $(triggerHistoryId), " + + " concat($(oldKeys) " + + " ), " + + " var_row_data, " + + " var_old_data, " + + " '$(channelName)', $(txIdExpression), @sync_node_disabled, " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + + " end if; " + -" end if; " + -" $(custom_on_update_text) " + -" end " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end if; "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) after delete on $(schemaName)$(tableName) " + + "create trigger $(triggerName) after delete on $(schemaName)$(tableName) " + -" for each row begin " + + " for each row begin "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'D', " + -" $(triggerHistoryId), " + -" concat($(oldKeys) " + -" ), " + -" concat($(oldColumns) " + -" ), " + -" '$(channelName)', $(txIdExpression), @sync_node_disabled, " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'D', " + + " $(triggerHistoryId), " + + " concat($(oldKeys) " + + " ), " + + " concat($(oldColumns) " + + " ), " + + " '$(channelName)', $(txIdExpression), @sync_node_disabled, " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_delete_text) " + -" end " ); + " end if; "; + + public MySqlTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',replace(replace($(tableAlias).`$(columnName)`,'\\\\','\\\\\\\\'),'\"','\\\\\"'),'\"'))" ; + geometryColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',replace(replace(astext($(tableAlias).`$(columnName)`),'\\\\','\\\\\\\\'),'\"','\\\\\"'),'\"'))" ; + numberColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',cast($(tableAlias).`$(columnName)` as char),'\"'))" ; + datetimeColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',cast($(tableAlias).`$(columnName)` as char),'\"'))" ; + clobColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',replace(replace($(tableAlias).`$(columnName)`,'\\\\','\\\\\\\\'),'\"','\\\\\"'),'\"'))" ; + blobColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',hex($(tableAlias).`$(columnName)`),'\"'))" ; + booleanColumnTemplate = "if($(tableAlias).`$(columnName)` is null,'',concat('\"',cast($(tableAlias).`$(columnName)` as unsigned),'\"'))" ; + triggerConcatCharacter = "," ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE , + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + MYSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + MYSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE , + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + MYSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + MYSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE , + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + MYSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + MYSQL_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select concat($(columns)) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/h2/H2TriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/h2/H2TriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/h2/H2TriggerTemplate.java (revision ) @@ -29,7 +29,7 @@ public H2TriggerTemplate(ISymmetricDialect symmetricDialect) { super(symmetricDialect); - + emptyColumnTemplate = "''''" ; stringColumnTemplate = "case when $(tableAlias)\"$(columnName)\" is null then '''' else ''\"''||replace(replace($(tableAlias)\"$(columnName)\",''\\'',''\\\\''),''\"'',''\\\"'')||''\"'' end" ; numberColumnTemplate = "case when $(tableAlias)\"$(columnName)\" is null then '''' else ''\"''||cast($(tableAlias)\"$(columnName)\" as varchar(50))||''\"'' end" ; @@ -44,30 +44,39 @@ newColumnPrefix = "NEW_" ; sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, -"CREATE TABLE $(schemaName)$(triggerName)_CONFIG (CONDITION_SQL CLOB, INSERT_DATA_SQL CLOB); " + -" INSERT INTO $(schemaName)$(triggerName)_CONFIG values( " + -" 'select count(*) from $(virtualOldNewTable) where $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)', " + -" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" (select ''$(targetTableName)'',''I'',$(triggerHistoryId),$(columns), ''$(channelName)'', $(txIdExpression), @node_value, $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))' " + -" ); " + +"CREATE TABLE $(schemaName)$(triggerName)_CONFIG (CONDITION_SQL CLOB, INSERT_DATA_SQL CLOB); " + +" INSERT INTO $(schemaName)$(triggerName)_CONFIG values( " + +" 'select count(*) from $(virtualOldNewTable) where $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition)', " + +" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + +" (select ''$(targetTableName)'',''I'',$(triggerHistoryId),$(columns), ''$(channelName)'', $(txIdExpression), @node_value, $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))' " + +" ); " + " CREATE TRIGGER $(schemaName)$(triggerName) AFTER INSERT ON $(schemaName)$(tableName) FOR EACH ROW CALL \"org.jumpmind.symmetric.db.h2.H2Trigger\"; " ); - sqlTemplates.put("updateTriggerTemplate" , + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(INSERT_TRIGGER_TEMPLATE)); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, -"CREATE TABLE $(schemaName)$(triggerName)_CONFIG (CONDITION_SQL CLOB, INSERT_DATA_SQL CLOB); " + -" INSERT INTO $(schemaName)$(triggerName)_CONFIG values( " + -" 'select count(*) from $(virtualOldNewTable) where $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)', " + -" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" (select ''$(targetTableName)'',''U'',$(triggerHistoryId),$(oldKeys),$(columns),$(oldColumns), ''$(channelName)'', $(txIdExpression), @node_value, $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + -" ); " + +"CREATE TABLE $(schemaName)$(triggerName)_CONFIG (CONDITION_SQL CLOB, INSERT_DATA_SQL CLOB); " + +" INSERT INTO $(schemaName)$(triggerName)_CONFIG values( " + +" 'select count(*) from $(virtualOldNewTable) where $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition)', " + +" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + +" (select ''$(targetTableName)'',''U'',$(triggerHistoryId),$(oldKeys),$(columns),$(oldColumns), ''$(channelName)'', $(txIdExpression), @node_value, $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + +" ); " + " CREATE TRIGGER $(schemaName)$(triggerName) AFTER UPDATE ON $(schemaName)$(tableName) FOR EACH ROW CALL \"org.jumpmind.symmetric.db.h2.H2Trigger\"; " ); - sqlTemplates.put("deleteTriggerTemplate" , + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(UPDATE_TRIGGER_TEMPLATE)); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, -"CREATE TABLE $(schemaName)$(triggerName)_CONFIG (CONDITION_SQL CLOB, INSERT_DATA_SQL CLOB); " + -" INSERT INTO $(schemaName)$(triggerName)_CONFIG values( " + -" 'select count(*) from $(virtualOldNewTable) where $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)', " + -" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" (select ''$(targetTableName)'',''D'',$(triggerHistoryId),$(oldKeys),$(oldColumns),''$(channelName)'', $(txIdExpression), @node_value, $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + -" ); " + +"CREATE TABLE $(schemaName)$(triggerName)_CONFIG (CONDITION_SQL CLOB, INSERT_DATA_SQL CLOB); " + +" INSERT INTO $(schemaName)$(triggerName)_CONFIG values( " + +" 'select count(*) from $(virtualOldNewTable) where $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition)', " + +" 'insert into $(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + +" (select ''$(targetTableName)'',''D'',$(triggerHistoryId),$(oldKeys),$(oldColumns),''$(channelName)'', $(txIdExpression), @node_value, $(externalSelect), CURRENT_TIMESTAMP from $(virtualOldNewTable))'" + +" ); " + " CREATE TRIGGER $(schemaName)$(triggerName) AFTER DELETE ON $(schemaName)$(tableName) FOR EACH ROW CALL \"org.jumpmind.symmetric.db.h2.H2Trigger\"; " ); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, sqlTemplates.get(DELETE_TRIGGER_TEMPLATE)); + sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); } \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/GreenplumTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/GreenplumTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/GreenplumTriggerTemplate.java (revision ) @@ -27,113 +27,138 @@ public class GreenplumTriggerTemplate extends AbstractTriggerTemplate { - public GreenplumTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - xmlColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - arrayColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || cast($(tableAlias).\"$(columnName)\" as varchar) || '\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.MS') || '\"' end" ; - timeColumnTemplate = null; - dateColumnTemplate = null; - clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; - blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || pg_catalog.encode($(tableAlias).\"$(columnName)\", 'base64') || '\"' end" ; - wrappedBlobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(defaultSchema)$(prefixName)_largeobject($(tableAlias).\"$(columnName)\") || '\"' end" ; - booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "||" ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - otherColumnTemplate = null; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String GREENPLUM_TRIGGER_TEMPLATE_HEADER = -"create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + + "create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + -" begin " + + " begin "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" '$(targetTableName)', " + -" 'I', " + -" $(triggerHistoryId), " + -" $(columns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " '$(targetTableName)', " + + " 'I', " + + " $(triggerHistoryId), " + + " $(columns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_insert_text) " + + " end if; "; + private static final String GREENPLUM_TRIGGER_TEMPLATE_FOOTER = -" return null; " + -" end; " + + " return null; " + + " end; " + -" $function$ language plpgsql; " ); - - sqlTemplates.put("insertPostTriggerTemplate" , -"create trigger $(triggerName) after insert on $(schemaName)$(tableName) " + -" for each row execute procedure $(schemaName)f$(triggerName)(); " ); - - sqlTemplates.put("updateTriggerTemplate" , -"create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + -" begin " + + " $function$ language plpgsql; "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" '$(targetTableName)', " + -" 'U', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(columns), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " '$(targetTableName)', " + + " 'U', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(columns), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_update_text) " + -" return null; " + -" end; " + -" $function$ language plpgsql; " ); - sqlTemplates.put("updatePostTriggerTemplate" , -"create trigger $(triggerName) after update on $(schemaName)$(tableName) " + -" for each row execute procedure $(schemaName)f$(triggerName)(); " ); - - sqlTemplates.put("deleteTriggerTemplate" , -"create or replace function $(schemaName)f$(triggerName)() returns trigger as $function$ " + -" begin " + + " end if; "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values( " + -" '$(targetTableName)', " + -" 'D', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(defaultSchema)$(prefixName)_node_disabled(), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values( " + + " '$(targetTableName)', " + + " 'D', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(defaultSchema)$(prefixName)_node_disabled(), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_delete_text) " + -" return null; " + -" end; " + -" $function$ language plpgsql; " ); + " end if; "; - sqlTemplates.put("deletePostTriggerTemplate" , -"create trigger $(triggerName) after delete on $(schemaName)$(tableName) " + -" for each row execute procedure $(schemaName)f$(triggerName)(); " ); + public GreenplumTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + xmlColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + arrayColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar),$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || cast($(tableAlias).\"$(columnName)\" as varchar) || '\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.MS') || '\"' end" ; + timeColumnTemplate = null; + dateColumnTemplate = null; + clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",$$\\$$,$$\\\\$$),'\"',$$\\\"$$) || '\"' end" ; + blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || pg_catalog.encode($(tableAlias).\"$(columnName)\", 'base64') || '\"' end" ; + wrappedBlobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || $(defaultSchema)$(prefixName)_largeobject($(tableAlias).\"$(columnName)\") || '\"' end" ; + booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "||" ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + otherColumnTemplate = null; + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE , + GREENPLUM_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + GREENPLUM_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + GREENPLUM_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + GREENPLUM_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put("insertPostTriggerTemplate", postTriggerTemplate("insert")); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE , + GREENPLUM_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + GREENPLUM_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + GREENPLUM_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + GREENPLUM_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put("updatePostTriggerTemplate", postTriggerTemplate("update")); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE , + GREENPLUM_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + GREENPLUM_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + GREENPLUM_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + GREENPLUM_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put("deletePostTriggerTemplate", postTriggerTemplate("delete")); + sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); + } + + private String postTriggerTemplate(String changeType) { + + return "create trigger $(triggerName) after " + changeType + " on $(schemaName)$(tableName) " + + " for each row execute procedure $(schemaName)f$(triggerName)(); "; } } \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/hsqldb2/HsqlDb2TriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/hsqldb2/HsqlDb2TriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/hsqldb2/HsqlDb2TriggerTemplate.java (revision ) @@ -27,82 +27,116 @@ public class HsqlDb2TriggerTemplate extends AbstractTriggerTemplate { - public HsqlDb2TriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||$(tableAlias).\"$(columnName)\"||'\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||$(tableAlias).\"$(columnName)\"||'\"' end" ; - clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"' end" ; - blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then ''else '\"'||rawtohex($(tableAlias).\"$(columnName)\")||'\"' end" ; - booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "||" ; - newTriggerValue = "newrow" ; - oldTriggerValue = "oldrow" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) after insert on $(schemaName)$(tableName) " + -" referencing new row as newrow " + + "create trigger $(triggerName) after insert on $(schemaName)$(tableName) " + + " referencing new row as newrow " + -" for each row begin atomic " + + " for each row begin atomic "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'I', " + -" $(triggerHistoryId), " + -" $(columns), " + -" '$(channelName)', $(txIdExpression), $(prefixName)_get_session('node_value'), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'I', " + + " $(triggerHistoryId), " + + " $(columns), " + + " '$(channelName)', $(txIdExpression), $(prefixName)_get_session('node_value'), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_insert_text) " + -" end " ); - - sqlTemplates.put("updateTriggerTemplate" , + " end if; "; + private static final String HSQL_TRIGGER_TEMPLATE_FOOTER = + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) after update on $(schemaName)$(tableName) " + -" referencing new row as newrow " + -" old row as oldrow " + + "create trigger $(triggerName) after update on $(schemaName)$(tableName) " + + " referencing new row as newrow " + + " old row as oldrow " + -" for each row begin atomic " + + " for each row begin atomic "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'U', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(columns), " + -" $(oldColumns), " + -" '$(channelName)', $(txIdExpression), $(prefixName)_get_session('node_value'), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'U', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(columns), " + + " $(oldColumns), " + + " '$(channelName)', $(txIdExpression), $(prefixName)_get_session('node_value'), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_update_text) " + -" end " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end if; "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) after delete on $(schemaName)$(tableName) " + -" referencing old row as oldrow " + + "create trigger $(triggerName) after delete on $(schemaName)$(tableName) " + + " referencing old row as oldrow " + -" for each row begin atomic " + + " for each row begin atomic "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + -" insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + -" values( " + -" '$(targetTableName)', " + -" 'D', " + -" $(triggerHistoryId), " + -" $(oldKeys), " + -" $(oldColumns), " + -" '$(channelName)', $(txIdExpression), $(prefixName)_get_session('node_value'), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP " + -" ); " + + " if $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + + " insert into $(defaultCatalog)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time)" + + " values( " + + " '$(targetTableName)', " + + " 'D', " + + " $(triggerHistoryId), " + + " $(oldKeys), " + + " $(oldColumns), " + + " '$(channelName)', $(txIdExpression), $(prefixName)_get_session('node_value'), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP " + + " ); " + -" end if; " + -" $(custom_on_delete_text) " + -" end " ); + " end if; "; + + public HsqlDb2TriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||$(tableAlias).\"$(columnName)\"||'\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||$(tableAlias).\"$(columnName)\"||'\"' end" ; + clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"'||replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"')||'\"' end" ; + blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then ''else '\"'||rawtohex($(tableAlias).\"$(columnName)\")||'\"' end" ; + booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "||" ; + newTriggerValue = "newrow" ; + oldTriggerValue = "oldrow" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + HSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + HSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + HSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + HSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + HSQL_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + HSQL_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java (revision ) @@ -36,136 +36,160 @@ public class AseTriggerTemplate extends AbstractTriggerTemplate { - public AseTriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - - String quote = symmetricDialect.getPlatform() - .getDatabaseInfo().getDelimiterToken(); - quote = quote == null ? "\"" : quote; - - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' else '\"' + str_replace(str_replace($(tableAlias)." + quote + "$(columnName)" + quote + ",'\\','\\\\'),'\"','\\\"') + '\"' end" ; - numberColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' else ('\"' + convert(varchar,$(tableAlias)." + quote + "$(columnName)" + quote + ") + '\"') end" ; - datetimeColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' else ('\"' + str_replace(convert(varchar,$(tableAlias)." + quote + "$(columnName)" + quote + ",102),'.','-') + ' ' + right('00'+convert(varchar,datepart(HOUR,$(tableAlias)." + quote + "$(columnName)" + quote + ")),2)+':'+right('00'+convert(varchar,datepart(MINUTE,$(tableAlias)." + quote + "$(columnName)" + quote + ")),2)+':'+right('00'+convert(varchar,datepart(SECOND,$(tableAlias)." + quote + "$(columnName)" + quote + ")),2)+'.'+right('000'+convert(varchar,datepart(MILLISECOND,$(tableAlias)." + quote + "$(columnName)" + quote + ")),3) + '\"') end" ; - clobColumnTemplate = "case when datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ") is null or datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ")=0 then '' else '\"' + str_replace(str_replace(cast($(origTableAlias)." + quote + "$(columnName)" + quote + " as varchar(16384)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - blobColumnTemplate = "case when $(origTableAlias)." + quote + "$(columnName)" + quote + " is null then '' else '\"' + bintostr(convert(varbinary(16384),$(origTableAlias)." + quote + "$(columnName)" + quote + ")) + '\"' end" ; - imageColumnTemplate = "case when datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ") is null or datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ")=0 then '' else '\"' + bintostr(convert(varbinary(16384),$(origTableAlias)." + quote + "$(columnName)" + quote + ")) + '\"' end" ; - booleanColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' when $(tableAlias)." + quote + "$(columnName)" + quote + " = 1 then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "+" ; - newTriggerValue = "inserted" ; - oldTriggerValue = "deleted" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"create trigger $(triggerName) on $(schemaName)$(tableName) for insert as " + -" begin " + + "create trigger $(triggerName) on $(schemaName)$(tableName) for insert as " + + " begin " + -" set nocount on " + + " set nocount on "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" declare @clientapplname varchar(50) " + -" select @clientapplname = clientapplname from master.dbo.sysprocesses where spid = @@spid " + -" declare @txid varchar(50) " + -" if (@@TRANCOUNT > 0) begin " + -" select @txid = convert(varchar, starttime, 20) + '.' + convert(varchar, loid) from master.dbo.systransactions where spid = @@spid " + -" end " + -" declare @clientname varchar(50) " + -" select @clientname = clientname from master.dbo.sysprocesses where spid = @@spid and clientapplname = 'SymmetricDS' " + -" declare @DataRow varchar(16384) " + -" $(declareNewKeyVariables) " + -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor for " + -" $(if:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + -" $(end:containsBlobClobColumns) " + -" open DataCursor " + -" fetch DataCursor into @DataRow $(newKeyVariables) " + -" while @@sqlstatus = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', @txid, @clientname, $(externalSelect), getdate()) " + -" fetch DataCursor into @DataRow $(newKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate cursor DataCursor " + + " declare @clientapplname varchar(50) " + + " select @clientapplname = clientapplname from master.dbo.sysprocesses where spid = @@spid " + + " declare @txid varchar(50) " + + " if (@@TRANCOUNT > 0) begin " + + " select @txid = convert(varchar, starttime, 20) + '.' + convert(varchar, loid) from master.dbo.systransactions where spid = @@spid " + + " end " + + " declare @clientname varchar(50) " + + " select @clientname = clientname from master.dbo.sysprocesses where spid = @@spid and clientapplname = 'SymmetricDS' " + + " declare @DataRow varchar(16384) " + + " $(declareNewKeyVariables) " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor for " + + " $(if:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) where $(syncOnInsertCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns) $(newKeyNames) from inserted where $(syncOnInsertCondition) " + + " $(end:containsBlobClobColumns) " + + " open DataCursor " + + " fetch DataCursor into @DataRow $(newKeyVariables) " + + " while @@sqlstatus = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', @txid, @clientname, $(externalSelect), getdate()) " + + " fetch DataCursor into @DataRow $(newKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate cursor DataCursor " + -" end " + -" $(custom_on_insert_text) " + + " end "; + private static final String ASE_TRIGGER_TEMPLATE_FOOTER = -" set nocount off " + + " set nocount off " + -" end " ); - - - - - sqlTemplates.put("updateTriggerTemplate" , -"create trigger $(triggerName) on $(schemaName)$(tableName) for update as " + + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) on $(schemaName)$(tableName) for update as " + -" begin " + + " begin " + -" set nocount on " + -" declare @DataRow varchar(16384) " + + " set nocount on "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = " declare @DataRow varchar(16384) " + -" declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(16384) " + -" declare @clientapplname varchar(50) " + -" select @clientapplname = clientapplname from master.dbo.sysprocesses where spid = @@spid " + -" declare @txid varchar(50) " + -" if (@@TRANCOUNT > 0) begin " + -" select @txid = convert(varchar, starttime, 20) + '.' + convert(varchar, loid) from master.dbo.systransactions where spid = @@spid " + -" end " + -" declare @clientname varchar(50) " + -" select @clientname = clientname from master.dbo.sysprocesses where spid = @@spid and clientapplname = 'SymmetricDS' " + -" $(declareOldKeyVariables) " + -" $(declareNewKeyVariables) " + -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor for " + -" $(if:containsBlobClobColumns) " + -" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" + -" $(else:containsBlobClobColumns) " + -" select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " + -" $(end:containsBlobClobColumns) " + -" open DataCursor " + -" fetch DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + -" while @@sqlstatus = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', @txid, @clientname, $(externalSelect), getdate())" + -" fetch DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate cursor DataCursor " + + " declare @OldPk varchar(2000) " + + " declare @OldDataRow varchar(16384) " + + " declare @clientapplname varchar(50) " + + " select @clientapplname = clientapplname from master.dbo.sysprocesses where spid = @@spid " + + " declare @txid varchar(50) " + + " if (@@TRANCOUNT > 0) begin " + + " select @txid = convert(varchar, starttime, 20) + '.' + convert(varchar, loid) from master.dbo.systransactions where spid = @@spid " + + " end " + + " declare @clientname varchar(50) " + + " select @clientname = clientname from master.dbo.sysprocesses where spid = @@spid and clientapplname = 'SymmetricDS' " + + " $(declareOldKeyVariables) " + + " $(declareNewKeyVariables) " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor for " + + " $(if:containsBlobClobColumns) " + + " select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join $(schemaName)$(tableName) $(origTableAlias) on $(tableNewPrimaryKeyJoin) inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition)" + + " $(else:containsBlobClobColumns) " + + " select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames) from inserted inner join deleted on $(oldNewPrimaryKeyJoin) where $(syncOnUpdateCondition) " + + " $(end:containsBlobClobColumns) " + + " open DataCursor " + + " fetch DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + + " while @@sqlstatus = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', @txid, @clientname, $(externalSelect), getdate())" + + " fetch DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate cursor DataCursor " + -" end " + -" $(custom_on_update_text) " + -" set nocount off " + -" end " ); - - - sqlTemplates.put("deleteTriggerTemplate" , -"create trigger $(triggerName) on $(schemaName)$(tableName) for delete as " + + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) on $(schemaName)$(tableName) for delete as " + -" begin " + + " begin " + -" set nocount on " + -" declare @OldPk varchar(2000) " + + " set nocount on "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = " declare @OldPk varchar(2000) " + -" declare @OldDataRow varchar(16384) " + -" declare @clientapplname varchar(50) " + -" select @clientapplname = clientapplname from master.dbo.sysprocesses where spid = @@spid " + -" declare @txid varchar(50) " + -" if (@@TRANCOUNT > 0) begin " + -" select @txid = convert(varchar, starttime, 20) + '.' + convert(varchar, loid) from master.dbo.systransactions where spid = @@spid " + -" end " + -" declare @clientname varchar(50) " + -" select @clientname = clientname from master.dbo.sysprocesses where spid = @@spid and clientapplname = 'SymmetricDS' " + -" $(declareOldKeyVariables) " + -" if ($(syncOnIncomingBatchCondition)) begin " + -" declare DataCursor cursor for " + -" select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + -" open DataCursor " + -" fetch DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + -" while @@sqlstatus = 0 begin " + -" insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', @txid, @clientname, $(externalSelect), getdate())" + -" fetch DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " + -" end " + -" close DataCursor " + -" deallocate cursor DataCursor " + + " declare @OldDataRow varchar(16384) " + + " declare @clientapplname varchar(50) " + + " select @clientapplname = clientapplname from master.dbo.sysprocesses where spid = @@spid " + + " declare @txid varchar(50) " + + " if (@@TRANCOUNT > 0) begin " + + " select @txid = convert(varchar, starttime, 20) + '.' + convert(varchar, loid) from master.dbo.systransactions where spid = @@spid " + + " end " + + " declare @clientname varchar(50) " + + " select @clientname = clientname from master.dbo.sysprocesses where spid = @@spid and clientapplname = 'SymmetricDS' " + + " $(declareOldKeyVariables) " + + " if ($(syncOnIncomingBatchCondition)) begin " + + " declare DataCursor cursor for " + + " select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + + " open DataCursor " + + " fetch DataCursor into @OldPk, @OldDataRow $(oldKeyVariables) " + + " while @@sqlstatus = 0 begin " + + " insert into $(defaultCatalog)$(defaultSchema)$(prefixName)_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', @txid, @clientname, $(externalSelect), getdate())" + + " fetch DataCursor into @OldPk,@OldDataRow $(oldKeyVariables) " + + " end " + + " close DataCursor " + + " deallocate cursor DataCursor " + -" end " + -" $(custom_on_delete_text) " + -" set nocount off " + -" end " ); + " end "; + + public AseTriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + + String quote = symmetricDialect.getPlatform() + .getDatabaseInfo().getDelimiterToken(); + quote = quote == null ? "\"" : quote; + + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' else '\"' + str_replace(str_replace($(tableAlias)." + quote + "$(columnName)" + quote + ",'\\','\\\\'),'\"','\\\"') + '\"' end" ; + numberColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' else ('\"' + convert(varchar,$(tableAlias)." + quote + "$(columnName)" + quote + ") + '\"') end" ; + datetimeColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' else ('\"' + str_replace(convert(varchar,$(tableAlias)." + quote + "$(columnName)" + quote + ",102),'.','-') + ' ' + right('00'+convert(varchar,datepart(HOUR,$(tableAlias)." + quote + "$(columnName)" + quote + ")),2)+':'+right('00'+convert(varchar,datepart(MINUTE,$(tableAlias)." + quote + "$(columnName)" + quote + ")),2)+':'+right('00'+convert(varchar,datepart(SECOND,$(tableAlias)." + quote + "$(columnName)" + quote + ")),2)+'.'+right('000'+convert(varchar,datepart(MILLISECOND,$(tableAlias)." + quote + "$(columnName)" + quote + ")),3) + '\"') end" ; + clobColumnTemplate = "case when datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ") is null or datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ")=0 then '' else '\"' + str_replace(str_replace(cast($(origTableAlias)." + quote + "$(columnName)" + quote + " as varchar(16384)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + blobColumnTemplate = "case when $(origTableAlias)." + quote + "$(columnName)" + quote + " is null then '' else '\"' + bintostr(convert(varbinary(16384),$(origTableAlias)." + quote + "$(columnName)" + quote + ")) + '\"' end" ; + imageColumnTemplate = "case when datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ") is null or datalength($(origTableAlias)." + quote + "$(columnName)" + quote + ")=0 then '' else '\"' + bintostr(convert(varbinary(16384),$(origTableAlias)." + quote + "$(columnName)" + quote + ")) + '\"' end" ; + booleanColumnTemplate = "case when $(tableAlias)." + quote + "$(columnName)" + quote + " is null then '' when $(tableAlias)." + quote + "$(columnName)" + quote + " = 1 then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "+" ; + newTriggerValue = "inserted" ; + oldTriggerValue = "deleted" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + ASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + ASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE , + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + ASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + ASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + ASE_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + ASE_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); Index: symmetric-core/src/main/resources/symmetric-schema.xml IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/resources/symmetric-schema.xml (revision 7927) +++ symmetric-core/src/main/resources/symmetric-schema.xml (revision ) @@ -255,9 +255,12 @@ - - - + + + + + + \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/db2/Db2TriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/db2/Db2TriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/db2/Db2TriggerTemplate.java (revision ) @@ -27,94 +27,128 @@ public class Db2TriggerTemplate extends AbstractTriggerTemplate { - public Db2TriggerTemplate(ISymmetricDialect symmetricDialect) { - super(symmetricDialect); - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"') || '\"' end" ; - xmlColumnTemplate = null; - arrayColumnTemplate = null; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || trim(char($(tableAlias).\"$(columnName)\")) || '\"' end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || rtrim(char(year(timestamp_iso($(tableAlias).\"$(columnName)\"))))||'-'||substr(digits(month(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||'-'||substr(digits(day(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||' '||substr(digits(hour(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||':'||substr(digits(minute(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||':'||substr(digits(second(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||'.'||rtrim(char(microsecond(timestamp_iso($(tableAlias).\"$(columnName)\")))) || '\"' end" ; - timeColumnTemplate = null; - dateColumnTemplate = null; - clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar(32672)),'\\','\\\\'),'\"','\\\"') || '\"' end" ; - blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || hex(cast($(tableAlias).\"$(columnName)\" as varchar(16336) for bit data)) || '\"' end" ; - wrappedBlobColumnTemplate = null; - booleanColumnTemplate = null; - triggerConcatCharacter = "||" ; - newTriggerValue = "new" ; - oldTriggerValue = "old" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - otherColumnTemplate = null; - - sqlTemplates = new HashMap(); - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = -"CREATE TRIGGER $(schemaName)$(triggerName) " + -" AFTER INSERT ON $(schemaName)$(tableName) " + -" REFERENCING NEW AS NEW " + -" FOR EACH ROW MODE DB2SQL " + -" BEGIN ATOMIC " + + "CREATE TRIGGER $(schemaName)$(triggerName) " + + " AFTER INSERT ON $(schemaName)$(tableName) " + + " REFERENCING NEW AS NEW " + + " FOR EACH ROW MODE DB2SQL " + + " BEGIN ATOMIC " + -" IF $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then " + + " IF $(syncOnInsertCondition) and $(syncOnIncomingBatchCondition) then "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = -" INSERT into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" VALUES('$(targetTableName)', 'I', $(triggerHistoryId), " + -" $(columns), " + -" '$(channelName)', $(txIdExpression), $(sourceNodeExpression), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP); " + + " INSERT into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " VALUES('$(targetTableName)', 'I', $(triggerHistoryId), " + + " $(columns), " + + " '$(channelName)', $(txIdExpression), $(sourceNodeExpression), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP); " + -" END IF; " + -" $(custom_on_insert_text) " + -" END " ); - - sqlTemplates.put("updateTriggerTemplate" , + " END IF; "; + private static final String DB2_TRIGGER_TEMPLATE_FOOTER = + " END "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = -"CREATE TRIGGER $(schemaName)$(triggerName) " + -" AFTER UPDATE ON $(schemaName)$(tableName) " + -" REFERENCING OLD AS OLD NEW AS NEW " + -" FOR EACH ROW MODE DB2SQL " + + "CREATE TRIGGER $(schemaName)$(triggerName) " + + " AFTER UPDATE ON $(schemaName)$(tableName) " + + " REFERENCING OLD AS OLD NEW AS NEW " + + " FOR EACH ROW MODE DB2SQL " + -" BEGIN ATOMIC " + + " BEGIN ATOMIC "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = -" DECLARE var_row_data VARCHAR(16336); " + -" DECLARE var_old_data VARCHAR(16336); " + -" IF $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + -" SET var_row_data = $(columns); " + -" SET var_old_data = $(oldColumns); " + -" IF $(dataHasChangedCondition) THEN " + -" INSERT into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" VALUES('$(targetTableName)', 'U', $(triggerHistoryId), " + -" $(oldKeys), " + -" var_row_data, " + -" var_old_data, " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(sourceNodeExpression), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP); " + -" END IF; " + + " DECLARE var_row_data VARCHAR(16336); " + + " DECLARE var_old_data VARCHAR(16336); " + + " IF $(syncOnUpdateCondition) and $(syncOnIncomingBatchCondition) then " + + " SET var_row_data = $(columns); " + + " SET var_old_data = $(oldColumns); " + + " IF $(dataHasChangedCondition) THEN " + + " INSERT into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, pk_data, row_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " VALUES('$(targetTableName)', 'U', $(triggerHistoryId), " + + " $(oldKeys), " + + " var_row_data, " + + " var_old_data, " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(sourceNodeExpression), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP); " + + " END IF; " + -" END IF; " + -" $(custom_on_update_text) " + -" END " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " END IF; "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = -"CREATE TRIGGER $(schemaName)$(triggerName) " + -" AFTER DELETE ON $(schemaName)$(tableName) " + -" REFERENCING OLD AS OLD " + -" FOR EACH ROW MODE DB2SQL " + + "CREATE TRIGGER $(schemaName)$(triggerName) " + + " AFTER DELETE ON $(schemaName)$(tableName) " + + " REFERENCING OLD AS OLD " + + " FOR EACH ROW MODE DB2SQL " + -" BEGIN ATOMIC " + + " BEGIN ATOMIC "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = -" IF $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + -" INSERT into $(defaultSchema)$(prefixName)_data " + -" (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + -" VALUES ('$(targetTableName)', 'D', $(triggerHistoryId), " + -" $(oldKeys), " + -" $(oldColumns), " + -" '$(channelName)', " + -" $(txIdExpression), " + -" $(sourceNodeExpression), " + -" $(externalSelect), " + -" CURRENT_TIMESTAMP); " + + " IF $(syncOnDeleteCondition) and $(syncOnIncomingBatchCondition) then " + + " INSERT into $(defaultSchema)$(prefixName)_data " + + " (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) " + + " VALUES ('$(targetTableName)', 'D', $(triggerHistoryId), " + + " $(oldKeys), " + + " $(oldColumns), " + + " '$(channelName)', " + + " $(txIdExpression), " + + " $(sourceNodeExpression), " + + " $(externalSelect), " + + " CURRENT_TIMESTAMP); " + -" END IF; " + -" $(custom_on_delete_text) " + -" END " ); + " END IF; "; + + public Db2TriggerTemplate(ISymmetricDialect symmetricDialect) { + super(symmetricDialect); + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace($(tableAlias).\"$(columnName)\",'\\','\\\\'),'\"','\\\"') || '\"' end" ; + xmlColumnTemplate = null; + arrayColumnTemplate = null; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || trim(char($(tableAlias).\"$(columnName)\")) || '\"' end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || rtrim(char(year(timestamp_iso($(tableAlias).\"$(columnName)\"))))||'-'||substr(digits(month(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||'-'||substr(digits(day(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||' '||substr(digits(hour(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||':'||substr(digits(minute(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||':'||substr(digits(second(timestamp_iso($(tableAlias).\"$(columnName)\"))),9)||'.'||rtrim(char(microsecond(timestamp_iso($(tableAlias).\"$(columnName)\")))) || '\"' end" ; + timeColumnTemplate = null; + dateColumnTemplate = null; + clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar(32672)),'\\','\\\\'),'\"','\\\"') || '\"' end" ; + blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' || hex(cast($(tableAlias).\"$(columnName)\" as varchar(16336) for bit data)) || '\"' end" ; + wrappedBlobColumnTemplate = null; + booleanColumnTemplate = null; + triggerConcatCharacter = "||" ; + newTriggerValue = "new" ; + oldTriggerValue = "old" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + otherColumnTemplate = null; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + DB2_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + DB2_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE, + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + DB2_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + DB2_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + DB2_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_DELETE_TEXT + + DELETE_TRIGGER_TEMPLATE_BODY + + DB2_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " ); \ No newline at end of file Index: symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterServiceSqlMap.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterServiceSqlMap.java (revision 7927) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterServiceSqlMap.java (revision ) @@ -77,6 +77,7 @@ + " t.name_for_delete_trigger,t.name_for_insert_trigger,t.name_for_update_trigger, " + " t.sync_on_insert_condition,t.sync_on_update_condition,t.sync_on_delete_condition, " + " t.custom_on_insert_text,t.custom_on_update_text,t.custom_on_delete_text, " + + " t.custom_on_insert_after_extract,t.custom_on_update_after_extract,t.custom_on_delete_after_extract, " + " t.tx_id_expression,t.external_select,t.create_time as t_create_time, " + " t.last_update_time as t_last_update_time, t.last_update_by as t_last_update_by "); \ No newline at end of file Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql2000/MsSql2000TriggerTemplate.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql2000/MsSql2000TriggerTemplate.java (revision 7927) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql2000/MsSql2000TriggerTemplate.java (revision ) @@ -27,27 +27,7 @@ public class MsSql2000TriggerTemplate extends MsSqlTriggerTemplate { - public MsSql2000TriggerTemplate(ISymmetricDialect symmetricDialect) { - this.symmetricDialect = symmetricDialect; - - // @formatter:off - emptyColumnTemplate = "''" ; - stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(8000),$(tableAlias).\"$(columnName)\") $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(8000),$(tableAlias).\"$(columnName)\".STAsText()) $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar, $(tableAlias).\"$(columnName)\",2) + '\"') end" ; - datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar,$(tableAlias).\"$(columnName)\",121) + '\"') end" ; - clobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(origTableAlias).\"$(columnName)\" as varchar(8000)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - blobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace($(defaultCatalog)dbo.sym_base64_encode(CONVERT(VARBINARY(8000), $(origTableAlias).\"$(columnName)\")),'\\','\\\\'),'\"','\\\"') + '\"' end" ; - booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ; - triggerConcatCharacter = "+" ; - newTriggerValue = "inserted" ; - oldTriggerValue = "deleted" ; - oldColumnPrefix = "" ; - newColumnPrefix = "" ; - - sqlTemplates = new HashMap(); - - sqlTemplates.put("insertTriggerTemplate" , + private static final String INSERT_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) on $(schemaName)$(tableName) after insert as " + " begin " + " declare @NCT int " + @@ -58,7 +38,8 @@ " $(declareNewKeyVariables) " + " if (@@TRANCOUNT > 0) begin " + " execute sp_getbindtoken @TransactionId output; " + - " end " + + " end "; + private static final String INSERT_TRIGGER_TEMPLATE_BODY = " if ($(syncOnIncomingBatchCondition)) begin " + " declare DataCursor cursor local for " + " $(if:containsBlobClobColumns) " + @@ -75,12 +56,11 @@ " end " + " close DataCursor " + " deallocate DataCursor " + - " end " + - " $(custom_on_insert_text) " + + " end "; + private static final String MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER = " if (@NCT = 0) set nocount off " + - " end " ); - - sqlTemplates.put("updateTriggerTemplate" , + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) on $(schemaName)$(tableName) after update as " + " begin " + " declare @NCT int " + @@ -94,7 +74,8 @@ " $(declareNewKeyVariables) " + " if (@@TRANCOUNT > 0) begin " + " execute sp_getbindtoken @TransactionId output; " + - " end " + + " end "; + private static final String UPDATE_TRIGGER_TEMPLATE_BODY = " if ($(syncOnIncomingBatchCondition)) begin " + " declare DataCursor cursor local for " + " $(if:containsBlobClobColumns) " + @@ -111,12 +92,10 @@ " end " + " close DataCursor " + " deallocate DataCursor " + - " end " + - " $(custom_on_update_text) " + - " if (@NCT = 0) set nocount off " + - " end " ); - - sqlTemplates.put("updateHandleKeyUpdatesTriggerTemplate" , + " end "; + private static final String UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE = + "updateHandleKeyUpdatesTriggerTemplate"; + private static final String UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) on $(schemaName)$(tableName) after update as " + " begin " + " declare @NCT int " + @@ -131,7 +110,8 @@ " if (@@TRANCOUNT > 0) begin " + " execute sp_getbindtoken @TransactionId output; " + " end " + - " if ($(syncOnIncomingBatchCondition)) begin " + + " if ($(syncOnIncomingBatchCondition)) begin "; + private static final String UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_BODY = " declare DeleteCursor cursor local for " + " select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + " declare InsertCursor cursor local for " + @@ -154,12 +134,8 @@ " close InsertCursor " + " deallocate DeleteCursor " + " deallocate InsertCursor " + - " end " + - " $(custom_on_update_text) " + - " if (@NCT = 0) set nocount off " + - " end " ); - - sqlTemplates.put("deleteTriggerTemplate" , + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_HEADER = "create trigger $(triggerName) on $(schemaName)$(tableName) after delete as " + " begin " + " declare @NCT int " + @@ -171,7 +147,8 @@ " $(declareOldKeyVariables) " + " if (@@TRANCOUNT > 0) begin " + " execute sp_getbindtoken @TransactionId output; " + - " end " + + " end "; + private static final String DELETE_TRIGGER_TEMPLATE_BODY = " if ($(syncOnIncomingBatchCondition)) begin " + " declare DataCursor cursor local for " + " select $(oldKeys), $(oldColumns) $(oldKeyNames) from deleted where $(syncOnDeleteCondition) " + @@ -184,10 +161,75 @@ " end " + " close DataCursor " + " deallocate DataCursor " + - " end " + - " $(custom_on_delete_text) " + - " if (@NCT = 0) set nocount off " + - " end " ); + " end "; + + public MsSql2000TriggerTemplate(ISymmetricDialect symmetricDialect) { + this.symmetricDialect = symmetricDialect; + + // @formatter:off + emptyColumnTemplate = "''" ; + stringColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(8000),$(tableAlias).\"$(columnName)\") $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(convert(varchar(8000),$(tableAlias).\"$(columnName)\".STAsText()) $(masterCollation),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + numberColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar, $(tableAlias).\"$(columnName)\",2) + '\"') end" ; + datetimeColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else ('\"' + convert(varchar,$(tableAlias).\"$(columnName)\",121) + '\"') end" ; + clobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(origTableAlias).\"$(columnName)\" as varchar(8000)),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + blobColumnTemplate = "case when $(origTableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace($(defaultCatalog)dbo.sym_base64_encode(CONVERT(VARBINARY(8000), $(origTableAlias).\"$(columnName)\")),'\\','\\\\'),'\"','\\\"') + '\"' end" ; + booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ; + triggerConcatCharacter = "+" ; + newTriggerValue = "inserted" ; + oldTriggerValue = "deleted" ; + oldColumnPrefix = "" ; + newColumnPrefix = "" ; + + sqlTemplates = new HashMap(); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE, + INSERT_TRIGGER_TEMPLATE_HEADER + + INSERT_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_INSERT_TEXT + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(INSERT_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + INSERT_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_INSERT_TEXT + + INSERT_TRIGGER_TEMPLATE_BODY + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE , + UPDATE_TRIGGER_TEMPLATE_HEADER + + UPDATE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_TRIGGER_TEMPLATE_BODY + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE, + UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_HEADER + + UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_UPDATE_TEXT + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_HEADER + + CUSTOM_ON_UPDATE_TEXT + + UPDATE_HANDLE_KEY_UPDATES_TRIGGER_TEMPLATE_BODY + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE , + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); + + sqlTemplates.put(DELETE_TRIGGER_TEMPLATE + CUSTOM_BEFORE_BODY, + DELETE_TRIGGER_TEMPLATE_HEADER + + DELETE_TRIGGER_TEMPLATE_BODY + + CUSTOM_ON_DELETE_TEXT + + MS_SQL_2000_TRIGGER_TEMPLATE_FOOTER); sqlTemplates.put("initialLoadSqlTemplate" , "select $(columns) from $(schemaName)$(tableName) t where $(whereClause) " );