Fixed issue 24682. Three performance improvements have been added:
authorAntonio Moreno <antonio.moreno@openbravo.com>
Tue, 03 Sep 2013 18:29:22 +0200
changeset 458 912c4ac086e0
parent 457 adbb2dbfdf95
child 459 1ee907d016ed
Fixed issue 24682. Three performance improvements have been added:
- Now oncreatedefault testing expressions will be executed with a LIMIT statement, to improve performance.
- Now 'DELETE FROM' statements executed for 'on delete cascade' foreign keys will be issued only for those foreign keys which are either part of an AD table, or point to an AD table.
- Now only foreign keys for AD tables, or which point to an AD table, will be dropped and recreated.
src/org/apache/ddlutils/Platform.java
src/org/apache/ddlutils/platform/PlatformImplBase.java
src/org/apache/ddlutils/platform/SqlBuilder.java
src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
src/org/openbravo/ddlutils/task/AlterDatabaseDataAll.java
--- a/src/org/apache/ddlutils/Platform.java	Fri Aug 16 15:13:17 2013 +0200
+++ b/src/org/apache/ddlutils/Platform.java	Tue Sep 03 18:29:22 2013 +0200
@@ -1289,6 +1289,9 @@
   public void disableAllFK(Connection connection, Database model, boolean continueOnError)
       throws DatabaseOperationException;
 
+  public void disableDatasetFK(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) throws DatabaseOperationException;
+
   /**
    * Enable all Foreign key.
    * 
@@ -1302,6 +1305,9 @@
   public boolean enableAllFK(Connection connection, Database model, boolean continueOnError)
       throws DatabaseOperationException;
 
+  public boolean enableDatasetFK(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) throws DatabaseOperationException;
+
   public void disableAllFK(Database model, boolean continueOnError, Writer writer)
       throws DatabaseOperationException;
 
@@ -1351,14 +1357,25 @@
 
   public void deleteInvalidConstraintRows(Database model, boolean continueOnError);
 
+  public void deleteInvalidConstraintRows(Database model, OBDataset dataset, boolean continueOnError);
+
   public void deleteAllInvalidConstraintRows(Database model, boolean continueOnError);
 
+  public void deleteAllInvalidConstraintRows(Database model, OBDataset dataset,
+      boolean continueOnError);
+
   public void deleteInvalidConstraintRows(Connection connection, Database model,
       boolean continueOnError);
 
+  public void deleteInvalidConstraintRows(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError);
+
   public void deleteAllInvalidConstraintRows(Connection connection, Database model,
       boolean continueOnError);
 
+  public void deleteAllInvalidConstraintRows(Connection connection, Database model,
+      OBDataset dataset, boolean continueOnError);
+
   public void applyConfigScript(Database database, Vector<Change> changes);
 
   public ModelLoader getModelLoader();
--- a/src/org/apache/ddlutils/platform/PlatformImplBase.java	Fri Aug 16 15:13:17 2013 +0200
+++ b/src/org/apache/ddlutils/platform/PlatformImplBase.java	Tue Sep 03 18:29:22 2013 +0200
@@ -735,19 +735,19 @@
   public void createAllFKs(Database model, boolean continueOnError) {
     Connection connection = borrowConnection();
 
-      try {
-        String sql;
-        StringWriter buffer = new StringWriter();
-
-        getSqlBuilder().setWriter(buffer);
-        getSqlBuilder().createExternalForeignKeys(model);
-        sql = buffer.toString();
-        evaluateBatch(connection, sql, continueOnError);
-      } catch (IOException e) {
-        // won't happen because we're using a string writer
-      }
-
-      returnConnection(connection);
+    try {
+      String sql;
+      StringWriter buffer = new StringWriter();
+
+      getSqlBuilder().setWriter(buffer);
+      getSqlBuilder().createExternalForeignKeys(model);
+      sql = buffer.toString();
+      evaluateBatch(connection, sql, continueOnError);
+    } catch (IOException e) {
+      // won't happen because we're using a string writer
+    }
+
+    returnConnection(connection);
   }
 
   /**
@@ -1622,11 +1622,12 @@
             // to the default value, and this is not correct.
             return !prop.getColumn().isAutoIncrement();
           } else {
-            /* original logic: if a property has no value, but the column has a default value
-             * then do skip it, so the default value is used
+            /*
+             * original logic: if a property has no value, but the column has a default value then
+             * do skip it, so the default value is used
              */
-            return !prop.getColumn().isAutoIncrement() &&
-                (prop.getColumn().getDefaultValue() == null);
+            return !prop.getColumn().isAutoIncrement()
+                && (prop.getColumn().getDefaultValue() == null);
           }
         }
       }
@@ -2557,6 +2558,12 @@
     throw new DatabaseOperationException("Error: Operation not supported");
   }
 
+  @Override
+  public void disableDatasetFK(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) throws DatabaseOperationException {
+    disableAllFK(connection, model, continueOnError);
+  }
+
   /**
    * {@inheritDoc}
    */
@@ -2573,6 +2580,12 @@
     throw new DatabaseOperationException("Error: Operation not supported");
   }
 
+  @Override
+  public boolean enableDatasetFK(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) throws DatabaseOperationException {
+    return enableAllFK(connection, model, continueOnError);
+  }
+
   /**
    * {@inheritDoc}
    */
@@ -3009,38 +3022,57 @@
   }
 
   public void deleteInvalidConstraintRows(Database model, boolean continueOnError) {
+    deleteInvalidConstraintRows(model, null, continueOnError);
+  }
+
+  public void deleteInvalidConstraintRows(Database model, OBDataset dataset, boolean continueOnError) {
 
     Connection connection = borrowConnection();
-    deleteInvalidConstraintRows(connection, model, continueOnError);
+    deleteInvalidConstraintRows(connection, model, dataset, continueOnError);
     returnConnection(connection);
 
   }
 
   public void deleteInvalidConstraintRows(Connection connection, Database model,
       boolean continueOnError) {
+    deleteInvalidConstraintRows(connection, model, null, continueOnError);
+  }
+
+  public void deleteInvalidConstraintRows(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) {
 
     StringWriter buffer = new StringWriter();
 
     getSqlBuilder().setWriter(buffer);
-    getSqlBuilder().deleteInvalidConstraintRows(model, true);
+    getSqlBuilder().deleteInvalidConstraintRows(model, dataset, true);
     evaluateBatch(connection, buffer.toString(), continueOnError);
   }
 
   public void deleteAllInvalidConstraintRows(Database model, boolean continueOnError) {
+    deleteAllInvalidConstraintRows(model, null, continueOnError);
+  }
+
+  public void deleteAllInvalidConstraintRows(Database model, OBDataset dataset,
+      boolean continueOnError) {
 
     Connection connection = borrowConnection();
-    deleteAllInvalidConstraintRows(connection, model, continueOnError);
+    deleteAllInvalidConstraintRows(connection, model, dataset, continueOnError);
     returnConnection(connection);
 
   }
 
   public void deleteAllInvalidConstraintRows(Connection connection, Database model,
       boolean continueOnError) {
+    deleteAllInvalidConstraintRows(connection, model, null, continueOnError);
+  }
+
+  public void deleteAllInvalidConstraintRows(Connection connection, Database model,
+      OBDataset dataset, boolean continueOnError) {
 
     StringWriter buffer = new StringWriter();
 
     getSqlBuilder().setWriter(buffer);
-    getSqlBuilder().deleteInvalidConstraintRows(model, false);
+    getSqlBuilder().deleteInvalidConstraintRows(model, dataset, false);
     evaluateBatch(connection, buffer.toString(), continueOnError);
   }
 
@@ -3373,12 +3405,16 @@
     }
   }
 
+  public String limitOneRow() {
+    return "";
+  }
+
   public boolean validateOnCreateDefault(Connection connection, String onCreateDefault, Table table) {
     if (onCreateDefault.startsWith("'"))
       return true;
     try {
       PreparedStatement st = connection.prepareStatement("SELECT (" + onCreateDefault + ") FROM "
-          + table.getName());
+          + table.getName() + limitOneRow());
       st.executeQuery();
     } catch (Exception e) {
       return false;
--- a/src/org/apache/ddlutils/platform/SqlBuilder.java	Fri Aug 16 15:13:17 2013 +0200
+++ b/src/org/apache/ddlutils/platform/SqlBuilder.java	Tue Sep 03 18:29:22 2013 +0200
@@ -112,6 +112,7 @@
 import org.apache.ddlutils.translation.Translation;
 import org.apache.ddlutils.util.CallbackClosure;
 import org.apache.ddlutils.util.MultiInstanceofPredicate;
+import org.openbravo.ddlutils.util.OBDataset;
 
 /**
  * This class is a collection of Strategy methods for creating the DDL required to create and drop
@@ -479,8 +480,9 @@
       dropTables(database);
     }
 
-    /* skip adding not null constraints when creating tables
-     * as they would need to be dropped for data loading again
+    /*
+     * skip adding not null constraints when creating tables as they would need to be dropped for
+     * data loading again
      */
     if (params == null) {
       params = new CreationParameters();
@@ -495,7 +497,7 @@
       writeExternalIndicesCreateStmt(table);
     }
 
-    // we don't write any foreign keys as those will be only activated after the data loading 
+    // we don't write any foreign keys as those will be only activated after the data loading
 
     // Write the sequences
     for (int idx = 0; idx < database.getSequenceCount(); idx++) {
@@ -569,7 +571,8 @@
     }
   }
 
-  public void deleteInvalidConstraintRows(Database model, boolean onlyOnDeleteCascade) {
+  public void deleteInvalidConstraintRows(Database model, OBDataset dataset,
+      boolean onlyOnDeleteCascade) {
 
     try {
       // We will now delete the rows in tables which have a foreign key
@@ -577,7 +580,19 @@
       // with "on delete cascade" whose parent has been deleted
       for (int i = 0; i < model.getTableCount(); i++) {
         Table table = model.getTable(i);
-        ForeignKey[] fksTable = table.getForeignKeys();
+        ForeignKey[] fksTable;
+        if (dataset == null || dataset.getTable(table.getName()) != null) {
+          fksTable = table.getForeignKeys();
+        } else {
+          List<ForeignKey> fks = new ArrayList<ForeignKey>();
+          for (int j = 0; j < table.getForeignKeyCount(); j++) {
+            ForeignKey fk = table.getForeignKey(j);
+            if (dataset.getTable(fk.getForeignTableName()) != null) {
+              fks.add(fk);
+            }
+          }
+          fksTable = fks.toArray(new ForeignKey[0]);
+        }
         for (int j = 0; j < fksTable.length; j++) {
           ForeignKey fk = fksTable[j];
           Table parentTable = fk.getForeignTable();
@@ -2727,9 +2742,9 @@
     if (parameters != null && parameters.containsKey("OB_OptimizeNotNull")) {
       try {
         Table clonedTable = (Table) table.clone();
-          for (Column col : clonedTable.getColumns()) {
-            col.setRequired(false);
-          }
+        for (Column col : clonedTable.getColumns()) {
+          col.setRequired(false);
+        }
         writeColumns(clonedTable);
       } catch (CloneNotSupportedException e) {
         // will not happen as clone() is implemented on Table class
@@ -3503,7 +3518,7 @@
    * @param key
    *          The foreign key
    */
-  protected void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey key)
+  public void writeExternalForeignKeyCreateStmt(Database database, Table table, ForeignKey key)
       throws IOException {
     if (key.getForeignTableName() == null) {
       _log.warn("Foreign key table is null for key " + key);
@@ -3596,7 +3611,7 @@
    * @param foreignKey
    *          The foreign key
    */
-  protected void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey)
+  public void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey)
       throws IOException {
     writeTableAlterStmt(table);
     print("DROP CONSTRAINT ");
--- a/src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java	Fri Aug 16 15:13:17 2013 +0200
+++ b/src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java	Tue Sep 03 18:29:22 2013 +0200
@@ -404,4 +404,9 @@
 
     return inconsistentObjects;
   }
+
+  @Override
+  public String limitOneRow() {
+    return "where rownum<2";
+  }
 }
--- a/src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java	Fri Aug 16 15:13:17 2013 +0200
+++ b/src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java	Tue Sep 03 18:29:22 2013 +0200
@@ -37,7 +37,9 @@
 import org.apache.ddlutils.PlatformInfo;
 import org.apache.ddlutils.dynabean.SqlDynaProperty;
 import org.apache.ddlutils.model.Database;
+import org.apache.ddlutils.model.ForeignKey;
 import org.apache.ddlutils.model.Function;
+import org.apache.ddlutils.model.Table;
 import org.apache.ddlutils.model.Trigger;
 import org.apache.ddlutils.platform.PlatformImplBase;
 import org.apache.ddlutils.translation.CommentFilter;
@@ -292,6 +294,38 @@
     }
   }
 
+  @Override
+  public void disableDatasetFK(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) throws DatabaseOperationException {
+
+    try {
+      StringWriter buffer = new StringWriter();
+      getSqlBuilder().setWriter(buffer);
+      for (int i = 0; i < model.getTableCount(); i++) {
+        Table table = model.getTable(i);
+        if (dataset.getTable(table.getName()) != null) {
+          getSqlBuilder().dropExternalForeignKeys(table);
+        } else {
+          for (int j = 0; j < table.getForeignKeyCount(); j++) {
+            ForeignKey fk = table.getForeignKey(j);
+            if (dataset.getTable(fk.getForeignTableName()) != null) {
+              getSqlBuilder().writeExternalForeignKeyDropStmt(table, fk);
+            }
+          }
+        }
+      }
+      evaluateBatchRealBatch(connection, buffer.toString(), continueOnError);
+      /*
+       * PreparedStatementpstmt=connection.prepareStatement(
+       * "update pg_class set reltriggers=0 WHERE PG_CLASS.RELNAMESPACE IN (SELECT PG_NAMESPACE.OID FROM PG_NAMESPACE WHERE PG_NAMESPACE.NSPNAME = CURRENT_SCHEMA())"
+       * ); pstmt.executeUpdate(); pstmt.close();
+       */
+    } catch (Exception e) {
+      e.printStackTrace();
+      throw new DatabaseOperationException("Error while disabling foreign key ", e);
+    }
+  }
+
   /**
    * {@inheritDoc}
    */
@@ -321,6 +355,42 @@
     }
   }
 
+  @Override
+  public boolean enableDatasetFK(Connection connection, Database model, OBDataset dataset,
+      boolean continueOnError) throws DatabaseOperationException {
+
+    try {
+      StringWriter buffer = new StringWriter();
+      getSqlBuilder().setWriter(buffer);
+      for (int i = 0; i < model.getTableCount(); i++) {
+        Table table = model.getTable(i);
+        if (dataset.getTable(table.getName()) != null) {
+          getSqlBuilder().createExternalForeignKeys(model, table);
+        } else {
+          for (int j = 0; j < table.getForeignKeyCount(); j++) {
+            ForeignKey fk = table.getForeignKey(j);
+            if (dataset.getTable(fk.getForeignTableName()) != null) {
+              getSqlBuilder().writeExternalForeignKeyCreateStmt(model, table, fk);
+            }
+          }
+        }
+      }
+      int numErrors = evaluateBatchRealBatch(connection, buffer.toString(), continueOnError);
+      if (numErrors > 0) {
+        return false;
+      }
+      return true;
+      /*
+       * PreparedStatementpstmt=connection.prepareStatement(
+       * "update pg_class set reltriggers = (SELECT count(*) from pg_trigger where pg_class.oid=tgrelid) WHERE PG_CLASS.RELNAMESPACE IN (SELECT PG_NAMESPACE.OID FROM PG_NAMESPACE WHERE PG_NAMESPACE.NSPNAME = CURRENT_SCHEMA())"
+       * ); pstmt.executeUpdate(); pstmt.close();
+       */
+    } catch (Exception e) {
+      e.printStackTrace();
+      throw new DatabaseOperationException("Error while enabling foreign key ", e);
+    }
+  }
+
   /**
    * {@inheritDoc}
    */
@@ -533,4 +603,9 @@
       returnConnection(connection);
     }
   }
+
+  @Override
+  public String limitOneRow() {
+    return "LIMIT 1";
+  }
 }
--- a/src/org/openbravo/ddlutils/task/AlterDatabaseDataAll.java	Fri Aug 16 15:13:17 2013 +0200
+++ b/src/org/openbravo/ddlutils/task/AlterDatabaseDataAll.java	Tue Sep 03 18:29:22 2013 +0200
@@ -159,7 +159,7 @@
       DBSMOBUtil.getInstance().moveModuleDataFromInstTables(platform, db, null);
       getLog().info("Disabling foreign keys");
       final Connection connection = platform.borrowConnection();
-      platform.disableAllFK(connection, originaldb, !isFailonerror());
+      platform.disableDatasetFK(connection, originaldb, ad, !isFailonerror());
       getLog().info("Disabling triggers");
       platform.disableAllTriggers(connection, db, !isFailonerror());
       platform.disableNOTNULLColumns(db, ad);
@@ -181,7 +181,7 @@
       getLog().info("Updating Application Dictionary data...");
       platform.alterData(connection, db, dataComparator.getChanges());
       getLog().info("Removing invalid rows.");
-      platform.deleteInvalidConstraintRows(db, !isFailonerror());
+      platform.deleteInvalidConstraintRows(db, ad, !isFailonerror());
       getLog().info("Recreating Primary Keys");
       List changes = platform.alterTablesRecreatePKs(oldModel, db, !isFailonerror());
       getLog().info("Executing oncreatedefault statements for mandatory columns");
@@ -193,7 +193,7 @@
           changes, null);
 
       getLog().info("Enabling Foreign Keys and Triggers");
-      boolean fksEnabled = platform.enableAllFK(connection, originaldb, !isFailonerror());
+      boolean fksEnabled = platform.enableDatasetFK(connection, originaldb, ad, !isFailonerror());
       boolean triggersEnabled = platform.enableAllTriggers(connection, db, !isFailonerror());
 
       // execute the post-script
@@ -328,8 +328,8 @@
   }
 
   /**
-   * Functionality for deleting data during create.database was removed.
-   * Function is kept to not require lock-step update of dbsm.jar & build-create.xml
+   * Functionality for deleting data during create.database was removed. Function is kept to not
+   * require lock-step update of dbsm.jar & build-create.xml
    */
   @Deprecated
   public void setFilter(String filter) {