View Issue Details

IDProjectCategoryView StatusLast Update
0001795SymmetricDSBugpublic2014-07-12 17:31
ReporterfabricerdAssigned Tochenson 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.6.1 
Target Version3.6.2Fixed in Version3.6.2 
Summary0001795: DDL modification in schema different than symetricds one
DescriptionScenario is a postgresql db with public and replication schemas
(symmetric user altered search_path to replication)
Using send schema to clients works for table creation in the public schema but other operations like add columns or alter index or foreign key seems to fail because no catalog or schema included in the sql request
Additional InformationI tried to make a sample patch with my case AbstractDdlBuilder and PostgreSqlDdlBuilder to show you how i quickly tried to solve my problem
TagsNo tags attached.

Activities

fabricerd

2014-07-07 08:39

reporter  

AbstractDdlBuilder.java.patch (11,784 bytes)
Index: src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java
===================================================================
--- src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java	(revision 8436)
+++ src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java	(working copy)
@@ -889,7 +889,7 @@
     protected void processChange(Database currentModel, Database desiredModel,
             CopyColumnValueChange change, StringBuilder ddl) {
         ddl.append("UPDATE ");
-        printlnIdentifier(getTableName(change.getChangedTable().getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(change.getChangedTable()));
         ddl.append(" SET ");
         printIdentifier(getColumnName(change.getTargetColumn()), ddl);
         ddl.append("=");
@@ -902,8 +902,26 @@
     protected boolean writeAlterColumnDataType(ColumnDataTypeChange change, StringBuilder ddl) {
         return false;
     }
-
+    
     /**
+	 * The fully qualified table name shorten
+     * @param Table
+     *            
+     * @return 
+     */
+    public String getFullyQualifiedTableNameShorten(Table table) {
+    	String result="";
+        // TODO: use getDelimitedIdentifier() around catalog/schema, but we'll need to get the case right
+        if (StringUtils.isNotBlank(table.getCatalog())) {
+            result+=table.getCatalog().concat(".");
+        }
+        if (StringUtils.isNotBlank(table.getSchema())) {
+        	result+=table.getSchema().concat(".");
+        }
+        result+=getDelimitedIdentifier(getTableName(table.getName()));
+        return result;
+    }
+    /**
      * Creates a temporary table object that corresponds to the given table.
      * Database-specific implementations may redefine this method if e.g. the
      * database directly supports temporary tables. The default implementation
@@ -933,7 +951,7 @@
     public void restoreTableFromBackup(Table backupTable, Table targetTable,
             LinkedHashMap<Column, Column> columnMap, StringBuilder ddl) {
         ddl.append("DELETE FROM ");
-        printIdentifier(getTableName(targetTable.getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(targetTable));
         printEndOfStatement(ddl);
         writeCopyDataStatement(backupTable, targetTable, columnMap, ddl);
     }
@@ -1041,7 +1059,7 @@
     public void writeCopyDataStatement(Table sourceTable, Table targetTable,
             LinkedHashMap<Column, Column> columnMap, StringBuilder ddl) {
         ddl.append("INSERT INTO ");
-        printIdentifier(getTableName(targetTable.getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(targetTable));
         ddl.append(" (");
         for (Iterator<Column> columnIt = columnMap.values().iterator(); columnIt.hasNext();) {
             printIdentifier(getColumnName((Column) columnIt.next()), ddl);
@@ -1060,7 +1078,7 @@
             }
         }
         ddl.append(" FROM ");
-        printIdentifier(getTableName(sourceTable.getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(sourceTable));
         printEndOfStatement(ddl);
     }
 
@@ -1298,7 +1316,7 @@
      */
     protected void dropTable(Table table, StringBuilder ddl, boolean temporary, boolean recreate) {
         ddl.append("DROP TABLE ");
-        printIdentifier(getTableName(table.getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(table));
         printEndOfStatement(ddl);
     }
 
@@ -1335,7 +1353,7 @@
         StringBuffer buffer = new StringBuffer("INSERT INTO ");
         boolean addComma = false;
 
-        buffer.append(getDelimitedIdentifier(getTableName(table.getName())));
+        buffer.append(getFullyQualifiedTableNameShorten(table));
         buffer.append(" (");
 
         for (int idx = 0; idx < table.getColumnCount(); idx++) {
@@ -1399,7 +1417,7 @@
         StringBuffer buffer = new StringBuffer("UPDATE ");
         boolean addSep = false;
 
-        buffer.append(getDelimitedIdentifier(getTableName(table.getName())));
+        buffer.append(table.getFullyQualifiedTableName());
         buffer.append(" SET ");
 
         for (int idx = 0; idx < table.getColumnCount(); idx++) {
@@ -1460,7 +1478,7 @@
         StringBuffer buffer = new StringBuffer("DELETE FROM ");
         boolean addSep = false;
 
-        buffer.append(getDelimitedIdentifier(getTableName(table.getName())));
+        buffer.append(getFullyQualifiedTableNameShorten(table));
         if ((pkValues != null) && !pkValues.isEmpty()) {
             buffer.append(" WHERE ");
             for (Iterator<Map.Entry<String, Object>> it = pkValues.entrySet().iterator(); it
@@ -1646,7 +1664,7 @@
      */
     protected void writeTableComment(Table table, StringBuilder ddl) {
         printComment("-----------------------------------------------------------------------", ddl);
-        printComment(getTableName(table.getName()), ddl);
+        printComment(getFullyQualifiedTableNameShorten(table), ddl);
         printComment("-----------------------------------------------------------------------", ddl);
         println(ddl);
     }
@@ -1660,7 +1678,8 @@
      */
     protected void writeTableAlterStmt(Table table, StringBuilder ddl) {
         ddl.append("ALTER TABLE ");
-        printlnIdentifier(getTableName(table.getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(table));
+        println(ddl);
         printIndent(ddl);
     }
 
@@ -1669,14 +1688,8 @@
      */
     protected void writeTableCreationStmt(Table table, StringBuilder ddl) {
         ddl.append("CREATE TABLE ");
-        // TODO: use getDelimitedIdentifier() around catalog/schema, but we'll need to get the case right
-        if (StringUtils.isNotBlank(table.getCatalog())) {
-            ddl.append(table.getCatalog()).append(".");
-        }
-        if (StringUtils.isNotBlank(table.getSchema())) {
-            ddl.append(table.getSchema()).append(".");
-        }
-        printlnIdentifier(getTableName(table.getName()), ddl);
+        //TODO Check side effect with shortened name table
+        ddl.append(getFullyQualifiedTableNameShorten(table));
         println("(", ddl);
 
         writeColumns(table, ddl);
@@ -2204,7 +2217,8 @@
             StringBuilder ddl) {
         if ((primaryKeyColumns.length > 0) && shouldGeneratePrimaryKeys(primaryKeyColumns)) {
             ddl.append("ALTER TABLE ");
-            printlnIdentifier(getTableName(table.getName()), ddl);
+            ddl.append(table.getFullyQualifiedTableName());
+            println(ddl);
             printIndent(ddl);
             ddl.append("ADD CONSTRAINT ");
             printIdentifier(getConstraintName(null, table, "PK", null), ddl);
@@ -2302,7 +2316,7 @@
                 ddl.append(" INDEX ");
                 printIdentifier(getIndexName(index), ddl);
                 ddl.append(" ON ");
-                printIdentifier(getTableName(table.getName()), ddl);
+                ddl.append(getFullyQualifiedTableNameShorten(table));
                 ddl.append(" (");
 
                 for (int idx = 0; idx < index.getColumnCount(); idx++) {
@@ -2372,7 +2386,7 @@
         printIdentifier(getIndexName(index), ddl);
         if (!databaseInfo.isAlterTableForDropUsed()) {
             ddl.append(" ON ");
-            printIdentifier(getTableName(table.getName()), ddl);
+            ddl.append(table.getFullyQualifiedTableName());
         }
         printEndOfStatement(ddl);
     }
@@ -2396,6 +2410,14 @@
                 ddl.append("FOREIGN KEY (");
                 writeLocalReferences(key, ddl);
                 ddl.append(") REFERENCES ");
+                // TODO: use getDelimitedIdentifier() around catalog/schema, but we'll need to get the case right
+                // TODO : suppose to use same catalog and schema than table because catalog and schema are not present in key.getforeignTable object
+                if (StringUtils.isNotBlank(table.getCatalog())) {
+                    ddl.append(table.getCatalog()).append(".");
+                }
+                if (StringUtils.isNotBlank(table.getSchema())) {
+                    ddl.append(table.getSchema()).append(".");
+                }
                 printIdentifier(getTableName(key.getForeignTableName()), ddl);
                 ddl.append(" (");
                 writeForeignReferences(key, ddl);
@@ -2425,6 +2447,14 @@
             ddl.append(" FOREIGN KEY (");
             writeLocalReferences(key, ddl);
             ddl.append(") REFERENCES ");
+            // TODO: use getDelimitedIdentifier() around catalog/schema, but we'll need to get the case right
+            // TODO : use same catalog and schema than table because catalog and schema are not present in table object
+            if (StringUtils.isNotBlank(table.getCatalog())) {
+                ddl.append(table.getCatalog()).append(".");
+            }
+            if (StringUtils.isNotBlank(table.getSchema())) {
+                ddl.append(table.getSchema()).append(".");
+            }
             printIdentifier(getTableName(key.getForeignTableName()), ddl);
             ddl.append(" (");
             writeForeignReferences(key, ddl);
Index: src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java
===================================================================
--- src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java	(revision 8436)
+++ src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java	(working copy)
@@ -108,7 +108,7 @@
     @Override
     protected void dropTable(Table table, StringBuilder ddl, boolean temporary, boolean recreate) {
         ddl.append("DROP TABLE ");
-        printIdentifier(getTableName(table.getName()), ddl);
+        ddl.append(getFullyQualifiedTableNameShorten(table));
         ddl.append(" CASCADE");
         printEndOfStatement(ddl);
         if (!temporary && !recreate) {
@@ -300,15 +300,13 @@
 
     protected void processChange(Database currentModel, Database desiredModel,
             PrimaryKeyChange change, StringBuilder ddl) {
-        ddl.append("ALTER TABLE ");
-        printlnIdentifier(getTableName(change.getChangedTable().getName()), ddl);
+        writeTableAlterStmt(change.getChangedTable(), ddl);
         printIndent(ddl);
         ddl.append(" DROP CONSTRAINT ");
         printIdentifier(change.getChangedTable().getPrimaryKeyConstraintName(), ddl);
         printEndOfStatement(ddl);
 
-        ddl.append("ALTER TABLE ");
-        printlnIdentifier(getTableName(change.getChangedTable().getName()), ddl);
+        writeTableAlterStmt(change.getChangedTable(), ddl);
         printIndent(ddl);
         ddl.append(" ADD ");
         writePrimaryKeyStmt(change.getChangedTable(), change.getNewPrimaryKeyColumns(), ddl);
@@ -321,8 +319,7 @@
      */
     protected void processChange(Database currentModel, Database desiredModel,
             AddColumnChange change, StringBuilder ddl) {
-        ddl.append("ALTER TABLE ");
-        printlnIdentifier(getTableName(change.getChangedTable().getName()), ddl);
+        writeTableAlterStmt(change.getChangedTable(), ddl);
         printIndent(ddl);
         ddl.append(" ADD COLUMN ");
         writeColumn(change.getChangedTable(), change.getNewColumn(), ddl);
@@ -335,8 +332,7 @@
      */
     protected void processChange(Database currentModel, Database desiredModel,
             RemoveColumnChange change, StringBuilder ddl) {
-        ddl.append("ALTER TABLE ");
-        printlnIdentifier(getTableName(change.getChangedTable().getName()), ddl);
+        writeTableAlterStmt(change.getChangedTable(), ddl);
         printIndent(ddl);
         ddl.append("DROP COLUMN ");
         printIdentifier(getColumnName(change.getColumn()), ddl);

Related Changesets

SymmetricDS: master dca71591

2014-07-07 10:46:44

chenson

Details Diff
0001795: DDL modification in schema different than symetricds one
0001795
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java Diff File

SymmetricDS: master da876a20

2014-07-10 16:03:33

chenson

Details Diff
0001795: DDL modification alters need to include catalog/schema
0001795
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/ase/AseDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/db2/Db2DdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/derby/DerbyDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/firebird/FirebirdDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/h2/H2DdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/hsqldb/HsqlDbDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/hsqldb2/HsqlDb2DdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/informix/InformixDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/interbase/InterbaseDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mssql/MsSql2000DdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/sqlanywhere/SqlAnywhereDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/sqlite/SqliteDdlBuilder.java Diff File

SymmetricDS: master ba5fb8cf

2014-07-10 16:44:22

chenson

Details Diff
0001795: DDL modification alters need to include catalog/schema
0001795
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/ase/AseDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/sqlanywhere/SqlAnywhereDdlBuilder.java Diff File

SymmetricDS: master f4339330

2014-07-10 19:07:13

chenson

Details Diff
0001795: DDL modification alters need to include catalog/schema
0001795
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java Diff File

SymmetricDS: master b91291fd

2014-07-11 14:03:25

chenson

Details Diff
0001795: DDL modification alters need to include catalog/schema
0001795
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2014-07-07 08:39 fabricerd New Issue
2014-07-07 08:39 fabricerd File Added: AbstractDdlBuilder.java.patch
2014-07-07 10:46 chenson Fixed in Version => 3.6.2
2014-07-07 10:46 chenson Target Version => 3.6.2
2014-07-07 10:56 chenson Status new => resolved
2014-07-07 10:56 chenson Resolution open => fixed
2014-07-07 10:56 chenson Assigned To => chenson
2014-07-07 11:00 chenson Changeset attached => SymmetricDS trunk r8440
2014-07-10 17:00 chenson Changeset attached => SymmetricDS trunk r8453
2014-07-10 17:00 chenson Changeset attached => SymmetricDS trunk r8454
2014-07-10 20:00 chenson Changeset attached => SymmetricDS trunk r8455
2014-07-11 15:00 chenson Changeset attached => SymmetricDS trunk r8462
2014-07-12 17:31 chenson Status resolved => closed
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master b91291fd
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master f4339330
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master ba5fb8cf
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master da876a20
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master dca71591