View Issue Details

IDProjectCategoryView StatusLast Update
0001354SymmetricDSBugpublic2013-08-26 06:55
ReportersnpeAssigned Toabrougher 
Prioritynormal 
Status assignedResolutionopen 
Product Version3.5.1 
Target VersionFixed in Version 
Summary0001354: SQL Anywhere crashes with big table
DescriptionSymmetricDS creates a trigger as in attached file (bigtrigger.sql). The trigger uses a query (bigquery.sql) that crashes SQL Anywhere.
You can try just to create the table (crbigtable.sql) and execute the query (bigquery.sql).

I have tested SQL Anywhere 11.0.1, SQL Anywhere 12.0.1 and SQL Anywhere 16.0.
SQL Anywhere 16.0 doesn't crash with this table and original T-SQL triggers. Seems that this Sybase bug is fixed in 16.0.

Attached is a patch (sqlanywherecrash.diff) that works in the following way:

1) The patch creates triggers using Watcom SQL that doesn't require the FROM clause.
It also uses the ORDER clause in the trigger body so the tables can have other trigers with the same type (hardcoded to total 100 triggers)

2) Second part of the patch fixes initial load by changing the initialLoadSQL command so the SymmetricDS calls a simple SQL query (select col1, col2, ..., colN from table) and creates csv string after the query.

I think that it will also improve performance, because Watcom SQL doesn't create temporary tables (inserted and deleted).
RE initial load, Java will, probably, create the csv string faster than database that have to execute a relative complex query with different functions. Other databases can use the second part of patch in order to improve performance of initial loading. I'm not tested the performance, it is just my opinion.
Steps To Reproduce- create table using attached script (crbigtable.sql)
- configure SymmetricDS
- try to insert something in SQL Anywhere, for instance

insert into bigtable(id) values(1);

When configuring SymmetricDS once, the database will crash even SymmetricDS isn't started.
Additional InformationI have used very big database (11.0.1) to testing (cca 1GB). Re 12.01 and 16.0, I have tested just mentioned bigtable.

Activities

snpe

2013-07-27 22:21

reporter  

crbigtable.sql (7,074 bytes)

snpe

2013-07-27 22:23

reporter  

bigtrigger.sql (33,303 bytes)

snpe

2013-07-27 22:23

reporter  

bigquery.sql (30,396 bytes)

snpe

2013-07-27 22:23

reporter  

sqlanywherecrash.diff (35,606 bytes)
Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java
===================================================================
--- symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java	(revision 7584)
+++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java	(working copy)
@@ -20,122 +20,133 @@
  */
 package org.jumpmind.symmetric.db.sqlanywhere;
 
+import java.sql.Types;
 import java.util.HashMap;
 
+import org.apache.commons.lang.NotImplementedException;
+import org.apache.commons.lang.StringUtils;
 import org.jumpmind.db.model.Column;
 import org.jumpmind.db.model.Table;
+import org.jumpmind.symmetric.common.Constants;
 import org.jumpmind.symmetric.db.AbstractTriggerTemplate;
 import org.jumpmind.symmetric.db.ISymmetricDialect;
 import org.jumpmind.symmetric.io.data.DataEventType;
 import org.jumpmind.symmetric.model.Channel;
+import org.jumpmind.symmetric.model.Node;
 import org.jumpmind.symmetric.model.Trigger;
 import org.jumpmind.symmetric.model.TriggerHistory;
+import org.jumpmind.symmetric.model.TriggerRouter;
+import org.jumpmind.symmetric.util.SymmetricUtils;
 import org.jumpmind.util.FormatUtils;
 
 public class SqlAnywhereTriggerTemplate extends AbstractTriggerTemplate {
 
+	private static final String QUOTE = "\"";
+	static final String INITIAL_LOAD_SQL_TEMPLATE = "initialLoadSqlTemplate";
+
     public SqlAnywhereTriggerTemplate(ISymmetricDialect symmetricDialect) {
-        super(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" ;
+        clobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(cast($(tableAlias).\"$(columnName)\" as varchar(16384)),'\\','\\\\'),'\"','\\\"') + '\"' end" ;
+        blobColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' else '\"' + replace(replace(dbo.sym_base64_encode($(tableAlias).\"$(columnName)\"),'\\','\\\\'),'\"','\\\"') + '\"' end" ;
         booleanColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then '' when $(tableAlias).\"$(columnName)\" = 1 then '\"1\"' else '\"0\"' end" ;
         triggerConcatCharacter = "+" ;
         newTriggerValue = "inserted" ;
-        oldTriggerValue = "deleted" ;
+        oldTriggerValue = "orig" ;
         oldColumnPrefix = "" ;
         newColumnPrefix = "" ;
 
         sqlTemplates = new HashMap<String,String>();
         sqlTemplates.put("insertTriggerTemplate" ,
-"create trigger $(triggerName) on $(schemaName)$(tableName) for insert as                                                                                                                               " +
-"                                begin                                                                                                                                                                  " +
-"                                  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 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                                                                                                                                                                    " );
-
+"create trigger $(triggerName) after insert order $(triggerOrder) on $(schemaName)$(tableName)\n" +
+"referencing new as inserted\n" +
+"for each row\n" +	
+"begin\n" + 
+"    declare @DataRow varchar(16384);\n" + 
+"    $(declareNewKeyVariables)\n" +
+"    declare DataCursor cursor for\n" + 
+"           $(if:containsBlobClobColumns)" + 
+"       select $(columns) $(newKeyNames);\n" +
+"           $(else:containsBlobClobColumns)" + 
+"       select $(columns) $(newKeyNames);\n" +
+"            $(end:containsBlobClobColumns)" + 
+"    if ($(syncOnIncomingBatchCondition)) then\n" +
+"      open DataCursor;\n" +
+"      fetch next DataCursor into @DataRow $(newKeyVariables);\n" +
+"      while @@FETCH_STATUS = 0 LOOP\n" +
+"        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)\n" +
+"          values('$(targetTableName)','I', $(triggerHistoryId), @DataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate());\n" +
+"        fetch next DataCursor into @DataRow $(newKeyVariables);\n" +
+"    END LOOP;\n" +
+"    close DataCursor;\n" +
+"    deallocate DataCursor;\n" +
+"    end if;\n" +
+"  end;\n");
+        
         sqlTemplates.put("updateTriggerTemplate" ,
-"create trigger $(triggerName) on $(schemaName)$(tableName) for update as                                                                                                                               " +
-"                                begin                                                                                                                                                                  " +
-"                                  declare @DataRow varchar(16384)                                                                                                                                      " +
-"                                  declare @OldPk varchar(2000)                                                                                                                                         " +
-"                                  declare @OldDataRow varchar(16384)                                                                                                                                   " +
-"                                  $(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 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                                                                                                                                                                  " );
-
+"create trigger $(triggerName) after update order $(triggerOrder) on $(schemaName)$(tableName)\n" + 
+"referencing old as orig new as inserted\n" +
+"for each row\n" +	
+"  begin\n" + 
+"    declare @DataRow varchar(16384);\n" + 
+"    declare @OldPk varchar(2000);\n" + 
+"    declare @OldDataRow varchar(16384);\n" + 
+"    $(declareOldKeyVariables)\n" + 
+"    $(declareNewKeyVariables)\n" + 
+"    declare DataCursor cursor for\n" + 
+"    $(if:containsBlobClobColumns)" + 
+"        select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames);\n" +
+"    $(else:containsBlobClobColumns)" + 
+"        select $(columns), $(oldKeys), $(oldColumns) $(oldKeyNames) $(newKeyNames);\n" +
+"    $(end:containsBlobClobColumns)" +
+"    if ($(syncOnIncomingBatchCondition)) then\n" +
+"      open DataCursor;\n" + 
+"      fetch next DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables);\n" + 
+"      while @@FETCH_STATUS = 0 LOOP\n" +
+"        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)\n" + 
+"          values('$(targetTableName)','U', $(triggerHistoryId), @DataRow, @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate());\n" + 
+"        fetch next DataCursor into @DataRow, @OldPk, @OldDataRow $(oldKeyVariables) $(newKeyVariables);\n" + 
+"      END LOOP;\n" + 
+"      close DataCursor;\n" + 
+"      deallocate DataCursor;\n" + 
+"    end if;\n" + 
+"  end;\n" );
         sqlTemplates.put("deleteTriggerTemplate" ,
-"create trigger $(triggerName) on $(schemaName)$(tableName) for delete as                                                                                                                               " +
-"                                begin                                                                                                                                                                  " +
-"                                  declare @OldPk varchar(2000)                                                                                                                                         " +
-"                                  declare @OldDataRow varchar(16384)                                                                                                                                   " +
-"                                  $(declareOldKeyVariables)                                                                                                                                            " +
-"                                  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                                                                                                                                                                    " );
-
+"create trigger $(triggerName) after delete order $(triggerOrder) on $(schemaName)$(tableName)\n" +
+"referencing old as orig\n" +
+"for each row\n" +	        
+"  begin\n" + 
+"    declare @OldPk varchar(2000);\n" + 
+"    declare @OldDataRow varchar(16384);\n" + 
+"    $(declareOldKeyVariables)" + 
+"    declare DataCursor cursor for\n" + 
+"      select $(oldKeys), $(oldColumns) $(oldKeyNames);\n" +
+"    if ($(syncOnIncomingBatchCondition)) then\n" + 
+"      open DataCursor;\n" + 
+"      fetch next DataCursor into @OldPk, @OldDataRow $(oldKeyVariables);\n" + 
+"      while @@FETCH_STATUS = 0 LOOP\n" + 
+"        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)\n" + 
+"          values('$(targetTableName)','D', $(triggerHistoryId), @OldPk, @OldDataRow, '$(channelName)', $(txIdExpression), $(defaultCatalog)$(defaultSchema)$(prefixName)_node_disabled(0), $(externalSelect), getdate());\n" + 
+"        fetch next DataCursor into @OldPk,@OldDataRow $(oldKeyVariables);\n" + 
+"      END LOOP;\n" + 
+"      close DataCursor;\n" + 
+"      deallocate DataCursor;\n" + 
+"    end if;\n" + 
+"  end;\n" );
         sqlTemplates.put("initialLoadSqlTemplate" ,
 "select $(columns) from $(schemaName)$(tableName) t where $(whereClause)                                                                                                                                " );
     }
-
+    
     @Override
     protected String replaceTemplateVariables(DataEventType dml, Trigger trigger,
             TriggerHistory history, Channel channel, String tablePrefix, Table originalTable, Table table,
             String defaultCatalog, String defaultSchema, String ddl) {
-        ddl =  super.replaceTemplateVariables(dml, trigger, history, channel, tablePrefix, originalTable, table,
+    	ddl =  super.replaceTemplateVariables(dml, trigger, history, channel, tablePrefix, originalTable, table,
                 defaultCatalog, defaultSchema, ddl);
-        Column[] columns = table.getPrimaryKeyColumns();
+    	Column[] columns = table.getPrimaryKeyColumns();
         ddl = FormatUtils.replace("declareOldKeyVariables",
                 buildKeyVariablesDeclare(columns, "old"), ddl);
         ddl = FormatUtils.replace("declareNewKeyVariables",
@@ -143,4 +154,126 @@
         return ddl;
     }
 
+    @Override
+    public String createInitalLoadSql(Node node, TriggerRouter triggerRouter, Table originalTable,
+            TriggerHistory triggerHistory, Channel channel, String overrideSelectSql) {
+    	Table table = originalTable.copyAndFilterColumns(triggerHistory.getParsedColumnNames(),
+                triggerHistory.getParsedPkColumnNames(), true);
+        
+        String sql = sqlTemplates.get(INITIAL_LOAD_SQL_TEMPLATE);
+        Column[] columns = symmetricDialect.orderColumns(triggerHistory.getParsedColumnNames(),
+                table);
+        StringBuilder builder = new StringBuilder();
+        
+        for (int i = 0; i < columns.length; i++) {
+        	builder.append(QUOTE);
+			builder.append(columns[i].getName());
+			builder.append(QUOTE);
+			if (i < columns.length -1) {
+				builder.append(",");
+			}
+		}
+        	
+        String columnsText = builder.toString();
+
+        sql = FormatUtils.replace("columns", columnsText, sql);
+        sql = FormatUtils.replace("whereClause",
+                        StringUtils.isBlank(triggerRouter.getInitialLoadSelect()) ? Constants.ALWAYS_TRUE_CONDITION
+                                : triggerRouter.getInitialLoadSelect(), sql);
+        sql = FormatUtils.replace("tableName", SymmetricUtils.quote(symmetricDialect, table.getName()), sql);
+        sql = FormatUtils.replace("schemaName",
+                triggerHistory == null ? getSourceTablePrefix(triggerRouter.getTrigger())
+                        : getSourceTablePrefix(triggerHistory), sql);
+        sql = FormatUtils.replace(
+                "primaryKeyWhereString",
+                getPrimaryKeyWhereString(symmetricDialect.getInitialLoadTableAlias(),
+                        table.hasPrimaryKey() ? table.getPrimaryKeyColumns() : table.getColumns()),
+                sql);
+
+        // Replace these parameters to give the initiaLoadContition a chance to
+        // reference the node that is being loaded
+        sql = FormatUtils.replace("groupId", node.getNodeGroupId(), sql);
+        sql = FormatUtils.replace("externalId", node.getExternalId(), sql);
+        sql = FormatUtils.replace("nodeId", node.getNodeId(), sql);
+        sql = replaceDefaultSchemaAndCatalog(sql);
+        sql = FormatUtils.replace("prefixName", symmetricDialect.getTablePrefix(), sql);
+        return sql;
+	}
+
+	@Override
+	public String createCsvDataSql(Trigger trigger,
+			TriggerHistory triggerHistory, Table originalTable,
+			Channel channel, String whereClause) {
+		// TODO Auto-generated method stub
+		return super.createCsvDataSql(trigger, triggerHistory, originalTable, channel,
+				whereClause);
+	}
+
+	protected String buildKeyVariablesDeclare(Column[] columns, String prefix) {
+        String text = "";
+        for (int i = 0; i < columns.length; i++) {
+            text += "declare @" + prefix + "pk" + i + " ";
+            switch (columns[i].getMappedTypeCode()) {
+                case Types.TINYINT:
+                case Types.SMALLINT:
+                case Types.INTEGER:
+                case Types.BIGINT:
+                    text += "bigint";
+                    break;
+                case Types.NUMERIC:
+                case Types.DECIMAL:
+                    text += "decimal";
+                    break;
+                case Types.FLOAT:
+                case Types.REAL:
+                case Types.DOUBLE:
+                    text += "float";
+                    break;
+                case Types.CHAR:
+                case Types.VARCHAR:
+                case Types.LONGVARCHAR:
+                    text += "varchar(1000)";
+                    break;
+                case Types.DATE:
+                    text += "date";
+                    break;
+                case Types.TIME:
+                    text += "time";
+                    break;
+                case Types.TIMESTAMP:
+                    text += "datetime";
+                    break;
+                case Types.BOOLEAN:
+                case Types.BIT:
+                    text += "bit";
+                    break;
+                case Types.CLOB:
+                    text += "varchar(max)";
+                    break;
+                case Types.BLOB:
+                case Types.BINARY:
+                case Types.VARBINARY:
+                case Types.LONGVARBINARY:
+                // FIXME	
+                case -10: // SQL-Server ntext binary type 
+                    text += "varbinary(max)";
+                    break;
+                case Types.OTHER:
+                    text += "varbinary(max)";
+                    break;
+                default:
+                    if (columns[i].getJdbcTypeName() != null
+                            && columns[i].getJdbcTypeName().equalsIgnoreCase("interval")) {
+                        text += "interval";
+                        break;
+                    }
+                    throw new NotImplementedException(columns[i] + " is of type "
+                            + columns[i].getMappedType());
+            }
+            text += ";\n";
+        }
+
+        return text;
+    }
+
 }
\ No newline at end of file
Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java
===================================================================
--- symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java	(revision 7584)
+++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereSymmetricDialect.java	(working copy)
@@ -27,6 +27,8 @@
 import java.sql.SQLException;
 import java.sql.Statement;
 
+import org.apache.commons.lang.StringUtils;
+import org.jumpmind.db.model.Table;
 import org.jumpmind.db.platform.IDatabasePlatform;
 import org.jumpmind.db.sql.IConnectionCallback;
 import org.jumpmind.db.sql.ISqlTransaction;
@@ -37,9 +39,12 @@
 import org.jumpmind.symmetric.common.ParameterConstants;
 import org.jumpmind.symmetric.db.AbstractSymmetricDialect;
 import org.jumpmind.symmetric.db.ISymmetricDialect;
+import org.jumpmind.symmetric.io.data.DataEventType;
+import org.jumpmind.symmetric.model.Channel;
 import org.jumpmind.symmetric.model.Trigger;
 import org.jumpmind.symmetric.model.TriggerHistory;
 import org.jumpmind.symmetric.service.IParameterService;
+import org.jumpmind.util.FormatUtils;
 
 /*
  * Sybase dialect was tested with jconn4 JDBC driver.
@@ -272,4 +277,85 @@
         return true;
     }
 
+    public void createTrigger(final StringBuilder sqlBuffer, final DataEventType dml,
+            final Trigger trigger, final TriggerHistory hist, final Channel channel,
+            final String tablePrefix, final Table table) {
+        log.info("Creating {} trigger for {}", hist.getTriggerNameForDmlType(dml),
+                table.getFullyQualifiedTableName());
+
+        String previousCatalog = null;
+        String sourceCatalogName = trigger.getSourceCatalogName();
+        String defaultCatalog = platform.getDefaultCatalog();
+        String defaultSchema = platform.getDefaultSchema();
+
+        String triggerSql = triggerTemplate.createTriggerDDL(dml, trigger, hist, channel,
+                tablePrefix, table, defaultCatalog, defaultSchema);
+
+        String postTriggerDml = createPostTriggerDDL(dml, trigger, hist, channel, tablePrefix,
+                table);
+
+        if (parameterService.is(ParameterConstants.AUTO_SYNC_TRIGGERS)) {
+            ISqlTransaction transaction = null;
+            try {
+                transaction = this.platform.getSqlTemplate().startSqlTransaction();
+                previousCatalog = switchCatalogForTriggerInstall(sourceCatalogName, transaction);
+
+                int i = 1;
+                // FIXME
+				while (i < 100) {
+					try {
+						String sql = FormatUtils.replace("triggerOrder",
+								new Integer(i++).toString(), triggerSql);
+						log.debug("Running: {}", sql);
+						transaction.execute(sql);
+						break;
+					} catch (SqlException ex) {
+						String message = ex.getMessage();
+						if (message != null && message.contains("SQL Anywhere Error -271")) {
+							continue;
+						}
+						log.error("Failed to create trigger: {}", triggerSql);
+						throw ex;
+					}
+				}
+
+                if (StringUtils.isNotBlank(postTriggerDml)) {
+                	i = 1;
+                    // FIXME
+					while (i < 100) {
+						try {
+							String sql = FormatUtils.replace("triggerOrder", new Integer(i++).toString(),postTriggerDml);
+							transaction.execute(sql);
+						} catch (SqlException ex) {
+							String message = ex.getMessage();
+							if (message != null && message.contains("SQL Anywhere Error -271")) {
+								continue;
+							}
+							log.error("Failed to create post trigger: {}", postTriggerDml);
+							throw ex;
+						}
+					}
+                }
+                transaction.commit();
+            } catch (SqlException ex) {
+                transaction.rollback();
+                throw ex;
+            } finally {
+                try {
+                    if (sourceCatalogName != null
+                            && !sourceCatalogName.equalsIgnoreCase(previousCatalog)) {
+                        switchCatalogForTriggerInstall(previousCatalog, transaction);
+                    }
+                } finally {
+                    transaction.close();
+                }
+
+            }
+        }
+
+        logSql(triggerSql, sqlBuffer);
+        logSql(postTriggerDml, sqlBuffer);
+
+    }
+
 }
Index: symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java
===================================================================
--- symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java	(revision 7584)
+++ symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java	(working copy)
@@ -30,6 +30,7 @@
 import java.util.Date;
 import java.util.HashMap;
 import java.util.HashSet;
+import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
@@ -1438,7 +1439,34 @@
                     overrideSelectSql);
             this.cursor = sqlTemplate.queryForCursor(initialLoadSql, new ISqlRowMapper<Data>() {
                 public Data mapRow(Row rs) {
-                    String csvRow = rs.stringValue();
+                	String csvRow;
+					if (DatabaseNamesConstants.SQLANYWHERE
+							.equals(symmetricDialect.getPlatform().getName())) {
+						StringBuilder builder = new StringBuilder();
+						Set<String> keys = rs.keySet();
+						Iterator<String> keyIterator = keys.iterator();
+						while (keyIterator.hasNext()) {
+							String key = keyIterator.next();
+							Object value = rs.get(key);
+							if (value != null) {
+							    builder.append("\"");
+								String replaced = value.toString();
+								replaced = replaced
+										.replace("\r\n", "\n")
+										.replace("\\", "\\\\")
+										.replace("\"", "\\\"");
+								builder.append(replaced);
+								builder.append("\"");
+							}
+							if (keyIterator.hasNext()) {
+								builder.append(",");
+							}
+						}
+						csvRow = builder.toString();
+					} else {
+						csvRow = rs.stringValue();
+					}
+
                     int commaCount = StringUtils.countMatches(csvRow, ",");
                     if (expectedCommaCount <= commaCount) {
                         Data data = new Data(0, null, csvRow, DataEventType.INSERT, triggerHistory
sqlanywherecrash.diff (35,606 bytes)

abrougher

2013-08-20 10:01

developer   ~0000373

Last edited: 2013-08-20 10:13

View 2 revisions

The initial load sql code does not support blob columns. Blob columns are never encoded to base64. datetime columns are also not going to be parsed correctly. It looks like this could work with some more development.

Can you explain the hard coded 100 triggers? Why do we want to create 100 triggers?

snpe

2013-08-26 06:55

reporter   ~0000375

There isn't hard coded 100 triggers.
This code is when a table/object already has the trigger of the same type. The code just increases the order clause in trigger declaration. This way we can have 100 triggers of the same type on one table, but just one is symmetricds triggers.
We can select some other number even unlimited, but, IMO, unlimited isn't recommended.

Issue History

Date Modified Username Field Change
2013-07-27 22:21 snpe New Issue
2013-07-27 22:21 snpe File Added: crbigtable.sql
2013-07-27 22:23 snpe File Added: bigtrigger.sql
2013-07-27 22:23 snpe File Added: bigquery.sql
2013-07-27 22:23 snpe File Added: sqlanywherecrash.diff
2013-07-29 08:24 abrougher Assigned To => abrougher
2013-07-29 08:24 abrougher Status new => assigned
2013-08-20 10:01 abrougher Note Added: 0000373
2013-08-20 10:13 abrougher Note Edited: 0000373 View Revisions
2013-08-26 06:55 snpe Note Added: 0000375