Index: symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/AbstractService.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/AbstractService.java (revision 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/AbstractService.java (revision ) @@ -68,18 +68,21 @@ protected String tablePrefix; + protected final boolean customTriggerBeforeBody; + private ISqlMap sqlMap; - + private Set logOnce = new HashSet(); public AbstractService(IParameterService parameterService, ISymmetricDialect symmetricDialect) { this.symmetricDialect = symmetricDialect; this.parameterService = parameterService; this.tablePrefix = parameterService.getTablePrefix(); + this.customTriggerBeforeBody = parameterService.getCustomTriggerBeforeBody(); this.platform = symmetricDialect.getPlatform(); this.sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate(); } - + protected Date maxDate(Date... dates) { Date date = null; if (dates != null) { @@ -91,7 +94,7 @@ } } } - + return date; } @@ -140,7 +143,7 @@ for (String table : tables) { map.put(table, String.format("%s%s%s", tablePrefix, StringUtils.isNotBlank(tablePrefix) ? "_" : "", table)); - } + } return map; } @@ -169,11 +172,11 @@ transaction.close(); } } - + protected Set toNodeIds(Set nodes) { return toNodeIds(nodes, null); } - + protected Set toNodeIds(Set nodes, Set nodeIds) { nodeIds = nodeIds == null ? new HashSet(nodes.size()) : nodeIds; for (Node node : nodes) { @@ -199,7 +202,7 @@ } return adminTool; } - + protected String buildBatchWhere(List nodeIds, List channels, List statuses) { boolean containsErrorStatus = statuses.contains(OutgoingBatch.Status.ER) @@ -225,31 +228,31 @@ where.append(" and "); } where.append("(status in (:STATUSES)"); - + if (containsErrorStatus) { - where.append(" or error_flag = 1 "); + where.append(" or error_flag = 1 "); } - + if (containsIgnoreStatus) { - where.append(" or ignore_count > 0 "); + where.append(" or ignore_count > 0 "); } - + where.append(")"); needsAnd = true; } - + if (where.length() > 0) { where.insert(0, " where "); } return where.toString(); } - + /** * Try a configured number of times to get the ACK through. */ protected void sendAck(Node remote, Node local, NodeSecurity localSecurity, - List list, ITransportManager transportManager) throws IOException { + List list, ITransportManager transportManager) throws IOException { Exception error = null; int sendAck = -1; int numberOfStatusSendRetries = parameterService @@ -278,9 +281,9 @@ } } } - } - - + } + + protected List readAcks(List batches, IOutgoingWithResponseTransport transport, ITransportManager transportManager, IAcknowledgeService acknowledgeService) throws IOException { @@ -333,14 +336,14 @@ return batchAcks; } - + protected void logOnce(String message) { if (!logOnce.contains(message)) { logOnce.add(message); log.info(message); } } - + protected boolean isStreamClosedByClient(Exception ex) { if (ExceptionUtils.indexOfType(ex, EOFException.class) >= 0) { return true; @@ -349,6 +352,6 @@ } } - + } \ No newline at end of file Index: symmetric-core/src/main/java/org/jumpmind/symmetric/AbstractSymmetricEngine.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/AbstractSymmetricEngine.java (revision 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/AbstractSymmetricEngine.java (revision ) @@ -198,14 +198,14 @@ protected ISequenceService sequenceService; protected IExtensionPointManager extensionPointManger; - + protected IGroupletService groupletService; protected IStagingManager stagingManager; protected INodeCommunicationService nodeCommunicationService; - - protected IFileSyncService fileSyncService; + + protected IFileSyncService fileSyncService; protected Date lastRestartTime = null; @@ -244,7 +244,7 @@ public void setDeploymentType(String deploymentType) { this.deploymentType = deploymentType; } - + protected abstract SecurityServiceType getSecurityServiceType(); protected void init() { @@ -253,7 +253,9 @@ TypedProperties properties = this.propertiesFactory.reload(); this.platform = createDatabasePlatform(properties); this.parameterService = new ParameterService(platform, propertiesFactory, properties.get( - ParameterConstants.RUNTIME_CONFIG_TABLE_PREFIX, "sym")); + ParameterConstants.RUNTIME_CONFIG_TABLE_PREFIX, "sym"), Boolean.valueOf( + properties.get(ParameterConstants.CUSTOM_TRIGGER_BEFORE_BODY, "false") + )); MDC.put("engineName", this.parameterService.getEngineName()); @@ -438,9 +440,9 @@ /** * Give the end user the option to provide a script that will load a * registration server with an initial SymmetricDS setup. - * + * * Look first on the file system, then in the classpath for the SQL file. - * + * * @return true if the script was executed */ protected boolean loadFromScriptIfProvided() { @@ -584,24 +586,24 @@ symmetricDialect.getDriverVersion() }); return started; } - - + + public synchronized void uninstall() { - + log.warn("Attempting an uninstall of all SymmetricDS database objects from the database"); - + stop(); - + try { - - Table table = platform.readTableFromDatabase(null, null, TableConstants.getTableName(parameterService.getTablePrefix(), TableConstants.SYM_TRIGGER_ROUTER)); + + Table table = platform.readTableFromDatabase(null, null, TableConstants.getTableName(parameterService.getTablePrefix(), TableConstants.SYM_TRIGGER_ROUTER)); if (table != null) { - + List grouplets = groupletService.getGrouplets(true); for (Grouplet grouplet : grouplets) { groupletService.deleteGrouplet(grouplet); } - + List triggerRouters = triggerRouterService.getTriggerRouters(); for (TriggerRouter triggerRouter : triggerRouters) { triggerRouterService.deleteTriggerRouter(triggerRouter); @@ -612,7 +614,7 @@ triggerRouterService.deleteRouter(triggerRouter.getRouter()); } } - + table = platform.readTableFromDatabase(null, null, TableConstants.getTableName( parameterService.getTablePrefix(), TableConstants.SYM_CONFLICT)); if (table != null) { @@ -650,8 +652,8 @@ // this should remove all triggers because we have removed all the // trigger configuration - triggerRouterService.syncTriggers(); - + triggerRouterService.syncTriggers(); + } catch (SqlException ex) { /* * Log at debug level, because the system could be partially installed which @@ -659,23 +661,23 @@ */ log.debug("Error while trying to uninstall", ex); } - + // remove any additional triggers that may remain because they were not in trigger history - symmetricDialect.cleanupTriggers(); - + symmetricDialect.cleanupTriggers(); + symmetricDialect.dropTablesAndDatabaseObjects(); - + // force cache to be cleared nodeService.deleteIdentity(); - + parameterService.setDatabaseHasBeenInitialized(false); - + log.warn("Finished uninstalling SymmetricDS database objects from the database"); - - } + + } public synchronized void stop() { - + log.info("Stopping SymmetricDS externalId={} version={} database={}", new Object[] { parameterService == null ? "?" : parameterService.getExternalId(), Version.version(), symmetricDialect == null? "?":symmetricDialect.getName() }); @@ -688,7 +690,7 @@ if (nodeCommunicationService != null) { nodeCommunicationService.stop(); } - + started = false; starting = false; } @@ -729,7 +731,7 @@ public NodeStatus getNodeStatus() { return nodeService.getNodeStatus(); } - + public void removeAndCleanupNode(String nodeId) { log.warn("Removing node {}", nodeId); nodeService.deleteNode(nodeId, false); @@ -737,7 +739,7 @@ outgoingBatchService.markAllAsSentForNode(nodeId, true); log.warn("Marking incoming batch records as Ok for {}", nodeId); incomingBatchService.markIncomingBatchesOk(nodeId); - log.warn("Done removing node {}", nodeId); + log.warn("Done removing node {}", nodeId); } public RemoteNodeStatuses pull() { @@ -794,7 +796,7 @@ } else if (Constants.PLEASE_SET_ME.equals(getParameterService().getNodeGroupId())) { log.warn("Please set the group.id for the node"); } else if (Constants.PLEASE_SET_ME.equals(getParameterService().getExternalId())) { - log.warn("Please set the external.id for the node"); + log.warn("Please set the external.id for the node"); } else if (node != null && (!node.getExternalId().equals(getParameterService().getExternalId()) || !node .getNodeGroupId().equals(getParameterService().getNodeGroupId()))) { @@ -811,11 +813,11 @@ ParameterConstants.OFFLINE_NODE_DETECTION_PERIOD_MINUTES, ParameterConstants.HEARTBEAT_SYNC_ON_PUSH_PERIOD_SEC); } else if (node != null && Version.isOlderThanVersion(Version.version(), node.getSymmetricVersion())) { - log.warn("SymmetricDS does not support automatic downgrading. The current version running version of {} is older than the last running version of {}", + log.warn("SymmetricDS does not support automatic downgrading. The current version running version of {} is older than the last running version of {}", Version.version(), node.getSymmetricVersion()); } else { if (node != null && Version.isOlderThanVersion(node.getSymmetricVersion(), Version.version())) { - log.debug("The current version of {} is newer than the last running version of {}", + log.debug("The current version of {} is newer than the last running version of {}", Version.version(), node.getSymmetricVersion()); } configurationValid = true; @@ -840,7 +842,7 @@ MDC.put("engineName", getEngineName()); registrationService.openRegistration(nodeGroupId, externalId); } - + public void clearCaches() { getTriggerRouterService().clearCache(); getParameterService().rereadParameters(); @@ -994,7 +996,7 @@ public INodeCommunicationService getNodeCommunicationService() { return nodeCommunicationService; } - + public IGroupletService getGroupletService() { return groupletService; } @@ -1011,7 +1013,7 @@ /** * Register this instance of the engine so it can be found by other * processes in the JVM. - * + * * @see #findEngineByUrl(String) */ private void registerHandleToEngine() { @@ -1053,11 +1055,11 @@ public T getDataSource() { return (T) getSymmetricDialect().getPlatform().getDataSource(); } - + public IDatabasePlatform getDatabasePlatform() { return getSymmetricDialect().getPlatform(); } - + public IFileSyncService getFileSyncService() { return fileSyncService; } 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 7859) +++ 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, TriggerHistory oldHistory) { schemaName = schemaName == null ? "" : (schemaName + "."); @@ -288,8 +288,8 @@ * schema if the source schema is configured. */ public void createTrigger(final StringBuilder sqlBuffer, final DataEventType dml, - final Trigger trigger, final TriggerHistory hist, final Channel channel, + final Trigger trigger, final TriggerHistory hist, final Channel channel, - final String tablePrefix, final Table table) { + final String tablePrefix, final Table table, boolean customTriggerBeforeBody) { log.info("Creating {} trigger for {}", hist.getTriggerNameForDmlType(dml), table.getFullyQualifiedTableName()); @@ -299,7 +299,7 @@ String defaultSchema = platform.getDefaultSchema(); String triggerSql = triggerTemplate.createTriggerDDL(dml, trigger, hist, channel, - tablePrefix, table, defaultCatalog, defaultSchema); + tablePrefix, table, defaultCatalog, defaultSchema, customTriggerBeforeBody); String postTriggerDml = createPostTriggerDDL(dml, trigger, hist, channel, tablePrefix, table); @@ -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/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 7859) +++ 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/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 7859) +++ 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/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 7859) +++ 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,26 +75,26 @@ 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, TriggerHistory oldHistory) { 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, boolean customTriggerBeforeBody) { try { super.createTrigger(sqlBuffer, dml, trigger, history, channel, - parameterService.getTablePrefix(), table); + parameterService.getTablePrefix(), table, customTriggerBeforeBody); } catch (SqlException ex) { if (ex.getErrorCode() == 4095) { try { @@ -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/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 7859) +++ 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-core/src/main/java/org/jumpmind/symmetric/service/impl/ParameterService.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/ParameterService.java (revision 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/ParameterService.java (revision ) @@ -50,20 +50,27 @@ private ISqlTemplate sqlTemplate; private Date lastUpdateTime; + private boolean customTriggerBeforeBody; public ParameterService(IDatabasePlatform platform, ITypedPropertiesFactory factory, - String tablePrefix) { + String tablePrefix, boolean customTriggerBeforeBody) { this.tablePrefix = tablePrefix; this.factory = factory; this.sql = new ParameterServiceSqlMap(platform, AbstractService.createSqlReplacementTokens( tablePrefix, platform.getDatabaseInfo().getDelimiterToken())); this.sqlTemplate = platform.getSqlTemplate(); + this.customTriggerBeforeBody = customTriggerBeforeBody; } public String getTablePrefix() { return this.tablePrefix; } + public boolean getCustomTriggerBeforeBody() { + + return this.customTriggerBeforeBody; + } + public boolean refreshFromDatabase() { Date date = sqlTemplate.queryForObject(sql.getSql("selectMaxLastUpdateTime"), Date.class); if (date != null) { @@ -113,6 +120,7 @@ } } + @Override protected TypedProperties rereadDatabaseParameters(String externalId, String nodeGroupId) { final TypedProperties properties = new TypedProperties(); sqlTemplate.query(sql.getSql("selectParametersSql"), new ISqlRowMapper() { @@ -133,6 +141,7 @@ } + @Override protected TypedProperties rereadApplicationParameters() { TypedProperties p = this.factory.reload(); p.putAll(systemProperties); 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 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java (revision ) @@ -49,13 +49,18 @@ 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 TRIGGER_TEMPLATE_FOOTER = " end; \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; @@ -241,13 +246,15 @@ } public String createTriggerDDL(DataEventType dml, Trigger trigger, TriggerHistory history, - Channel channel, String tablePrefix, Table originalTable, String defaultCatalog, + Channel channel, String tablePrefix, Table originalTable, String defaultCatalog, - String defaultSchema) { + String defaultSchema, boolean customTriggerBeforeBody) { Table table = originalTable.copyAndFilterColumns(history.getParsedColumnNames(), history.getParsedPkColumnNames(), true); - String ddl = sqlTemplates.get(dml.name().toLowerCase() + "TriggerTemplate"); + String ddl = sqlTemplates.get( + dml.name().toLowerCase() + "TriggerTemplate" + (customTriggerBeforeBody ? CUSTOM_BEFORE_BODY : "") + ); if (dml.getDmlType().equals(DmlType.UPDATE) && trigger.isUseHandleKeyUpdates()) { String temp = sqlTemplates.get(dml.name().toLowerCase() + "HandleKeyUpdates" + "TriggerTemplate"); if (StringUtils.trimToNull(temp)!=null) { @@ -552,7 +559,7 @@ } columnsText.append(columnName); } else { - columnsText.append("null"); + columnsText.append("null"); } columnsText.append(","); } @@ -589,7 +596,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/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 7859) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java (revision ) @@ -27,104 +27,137 @@ 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"); - - sqlTemplates.put("updateTriggerTemplate" , + " end if; \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-core/src/main/java/org/jumpmind/symmetric/service/IParameterService.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/service/IParameterService.java (revision 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/IParameterService.java (revision ) @@ -36,23 +36,23 @@ public interface IParameterService { public BigDecimal getDecimal(String key); - + public BigDecimal getDecimal(String key, BigDecimal defaulVal); public boolean is(String key); - + public boolean is(String key, boolean defaultVal); public int getInt(String key); - + public int getInt(String key, int defaultVal); public long getLong(String key); - + public long getLong(String key, long defaultVal); public String getString(String key); - + public String getString(String key, String defaultVal); public void saveParameter(String key, Object paramValue, String lastUpdateBy); @@ -60,23 +60,23 @@ public void saveParameter(String externalId, String nodeGroupId, String key, Object paramValue, String lastUpdateBy); public void saveParameters(String externalId, String nodeGroupId, Map parameters, String lastUpdateBy); - + public void deleteParameter(String externalId, String nodeGroupId, String key); public void rereadParameters(); public Date getLastTimeParameterWereCached(); - + public List getDatabaseParametersFor(String paramKey); - + public TypedProperties getDatabaseParametersByNodeGroupId(String nodeGroupId); public TypedProperties getAllParameters(); public void setParameterFilter(IParameterFilter f); - + public boolean isRegistrationServer(); - + public boolean refreshFromDatabase(); /** @@ -88,7 +88,7 @@ * Get the external id for this instance */ public String getExternalId(); - + public Map getReplacementValues(); /** @@ -103,9 +103,10 @@ public String getSyncUrl(); public String getTablePrefix(); - + public String getEngineName(); - + public String getTempDirectory(); - + + public boolean getCustomTriggerBeforeBody(); } \ No newline at end of file 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 7859) +++ 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-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 7859) +++ 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) " ); 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 7859) +++ 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/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 7859) +++ 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,29 +43,29 @@ 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(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(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("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 7859) +++ 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),'.','-') + ' ' + convert(varchar,$(tableAlias)." + quote + "$(columnName)" + quote + ",108) + '\"') 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),'.','-') + ' ' + convert(varchar,$(tableAlias)." + quote + "$(columnName)" + quote + ",108) + '\"') 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-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 7859) +++ 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-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java (revision 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java (revision ) @@ -182,6 +182,7 @@ public final static String TRIGGER_UPDATE_CAPTURE_CHANGED_DATA_ONLY = "trigger.update.capture.changed.data.only.enabled"; public final static String TRIGGER_CREATE_BEFORE_INITIAL_LOAD = "trigger.create.before.initial.load.enabled"; + public static final String CUSTOM_TRIGGER_BEFORE_BODY = "trigger.custom.trigger.before.body"; public final static String DB_METADATA_IGNORE_CASE = "db.metadata.ignore.case"; public final static String DB_NATIVE_EXTRACTOR = "db.native.extractor"; \ 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 7859) +++ 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-core/src/test/java/org/jumpmind/symmetric/service/impl/MockParameterService.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/test/java/org/jumpmind/symmetric/service/impl/MockParameterService.java (revision 7859) +++ symmetric-core/src/test/java/org/jumpmind/symmetric/service/impl/MockParameterService.java (revision ) @@ -31,23 +31,23 @@ public class MockParameterService extends AbstractParameterService implements IParameterService { private Properties properties = new Properties(); - + public MockParameterService() { } - + public boolean refreshFromDatabase() { return false; } - + public MockParameterService(Properties properties) { this.properties = properties; } - + public boolean isRegistrationServer() { return false; } - + public void saveParameter(String key, Object paramValue, String lastUpdateBy) { } @@ -72,6 +72,11 @@ return "sym"; } + public boolean getCustomTriggerBeforeBody() { + + return false; + } + @Override protected TypedProperties rereadApplicationParameters() { return new TypedProperties(properties); 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 7859) +++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/derby/DerbyTriggerTemplate.java (revision ) @@ -54,50 +54,50 @@ 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(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(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) " ); - + //@formatter:on } \ 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 7859) +++ 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/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 7859) +++ 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"; @@ -174,7 +174,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) { @@ -221,7 +221,7 @@ } } - + @Override protected void buildOptions(Options options) { @@ -246,7 +246,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); @@ -383,12 +383,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")); @@ -423,7 +427,7 @@ private void createSymTables() { getSymmetricEngine().setupDatabase(true); } - + private void uninstall(CommandLine line, List args) { getSymmetricEngine().uninstall(); } 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 7859) +++ 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,29 +44,29 @@ 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(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(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("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/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 7859) +++ 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-core/src/main/java/org/jumpmind/symmetric/db/ISymmetricDialect.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/java/org/jumpmind/symmetric/db/ISymmetricDialect.java (revision 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/db/ISymmetricDialect.java (revision ) @@ -44,8 +44,8 @@ public interface ISymmetricDialect { public void createTrigger(StringBuilder sqlBuffer, DataEventType dml, - Trigger trigger, TriggerHistory hist, Channel channel, + Trigger trigger, TriggerHistory hist, Channel channel, - String tablePrefix, Table table); + String tablePrefix, Table table, boolean customTriggerBeforeBody); /* * Get the name of this symmetric instance. This can be set in @@ -65,10 +65,10 @@ public void dropTablesAndDatabaseObjects(); public boolean createOrAlterTablesIfNecessary(String... tables); - + public void dropRequiredDatabaseObjects(); - - public void createRequiredDatabaseObjects(); + + public void createRequiredDatabaseObjects(); public IDatabasePlatform getPlatform(); \ 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 7859) +++ 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-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 7859) +++ 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/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 7859) +++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java (revision ) @@ -1273,7 +1273,7 @@ } } } - + public void syncTrigger(Trigger trigger, ITriggerCreationListener listener, boolean force) { syncTrigger(trigger, listener, force, true); } @@ -1323,7 +1323,7 @@ TriggerReBuildReason reason = TriggerReBuildReason.NEW_TRIGGERS; String errorMessage = null; - + if (verifyInDatabase) { Channel channel = configurationService.getChannel(trigger.getChannelId()); if (channel == null) { @@ -1507,7 +1507,7 @@ symmetricDialect .createTrigger(sqlBuffer, dmlType, trigger, hist, configurationService.getChannel(trigger.getChannelId()), - tablePrefix, table); + tablePrefix, table, customTriggerBeforeBody); if (triggerRemoved) { statisticManager.incrementTriggersRebuiltCount(1); } else { Index: symmetric-core/src/main/resources/symmetric-default.properties IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- symmetric-core/src/main/resources/symmetric-default.properties (revision 7859) +++ symmetric-core/src/main/resources/symmetric-default.properties (revision ) @@ -139,7 +139,7 @@ # Tags: database db.sql.query.timeout.seconds=300 -# If set to true forces database columns that contain character data to be read as bytes (bypassing JDBC driver character encoding) +# If set to true forces database columns that contain character data to be read as bytes (bypassing JDBC driver character encoding) # so the raw values be encoded using the system default character set (usually UTF8). This property was added to bypass MySQL character # encoding so the raw data can be converted to utf8 directly. # @@ -1158,3 +1158,6 @@ # Tags: other # Type: boolean bsh.load.filter.handles.missing.tables=false + + +trigger.custom.trigger.before.body=false \ No newline at end of file