Jira-ID: CRNT-26320

Provided new auditTrail command options for migration from an existing MySQL schema to a sequence based one.

git-svn-id: http://emeafrazerg/svn/ipp/product/trunk/stardust/engine@59973 8100b5e0-4d52-466c-ae9c-bdeccbdeaf6b

3 files changed, 388 insertions, 5 deletions

diff --git a/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/cli/sysconsole/AlterAuditTrailCommand.java b/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/cli/sysconsole/AlterAuditTrailCommand.java
index 54cb9ef..d029b9a 100644
--- a/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/cli/sysconsole/AlterAuditTrailCommand.java
+++ b/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/cli/sysconsole/AlterAuditTrailCommand.java
@@ -25,16 +25,17 @@ import org.eclipse.stardust.common.error.PublicException;
 import org.eclipse.stardust.common.log.LogManager;

 import org.eclipse.stardust.common.log.Logger;

 import org.eclipse.stardust.common.utils.console.Options;

+import org.eclipse.stardust.engine.cli.sysconsole.consistency.AuditTrailConsistencyChecker;
+import org.eclipse.stardust.engine.cli.sysconsole.consistency.SharedDocumentDataConsistencyCheck;
 import org.eclipse.stardust.engine.cli.sysconsole.utils.Utils;
 import org.eclipse.stardust.engine.core.persistence.Predicates;

 import org.eclipse.stardust.engine.core.persistence.QueryExtension;

+import org.eclipse.stardust.engine.core.persistence.jdbc.DBMSKey;
 import org.eclipse.stardust.engine.core.persistence.jdbc.Session;

 import org.eclipse.stardust.engine.core.persistence.jdbc.SessionFactory;

+import org.eclipse.stardust.engine.core.persistence.jdbc.SessionProperties;
 import org.eclipse.stardust.engine.core.runtime.beans.*;

 import org.eclipse.stardust.engine.core.runtime.beans.removethis.KernelTweakingProperties;
-import org.eclipse.stardust.engine.cli.sysconsole.consistency.AuditTrailConsistencyChecker;
-import org.eclipse.stardust.engine.cli.sysconsole.consistency.SharedDocumentDataConsistencyCheck;
-import org.eclipse.stardust.engine.cli.sysconsole.utils.Utils;
 

 

 /**

@@ -48,6 +49,10 @@ public class AlterAuditTrailCommand extends AuditTrailCommand
    private static final String LOCKTABLE_ENABLE = "enableLockTables";

    private static final String LOCKTABLE_VERIFY = "verifyLockTables";

    private static final String LOCKTABLE_DROP = "dropLockTables";

+   
+   private static final String SEQ_TABLE_ENABLE = "enableSequenceTable";
+   private static final String SEQ_TABLE_VERIFY = "verifySequenceTable";
+   private static final String SEQ_TABLE_DROP = "dropSequenceTable";
 

    private static final String PARTITION_CREATE = "createPartition";

    private static final String PARTITIONS_LIST = "listPartitions";

@@ -77,6 +82,13 @@ public class AlterAuditTrailCommand extends AuditTrailCommand
             "Verifies existence of proxy locking tables and their consistency.", false);

       argTypes.register("-" + LOCKTABLE_DROP, "-dlt", LOCKTABLE_DROP,

             "Drops any existing proxy locking tables.", false);

+      
+      argTypes.register("-" + SEQ_TABLE_ENABLE, "-est", SEQ_TABLE_ENABLE,
+            "Creates 'sequence' table, 'next_sequence_value_for' function and synchronizes table content.", false);
+      argTypes.register("-" + SEQ_TABLE_VERIFY, "-vst", SEQ_TABLE_VERIFY,
+            "Verifies existence of 'sequence' table and their consistency.", false);
+      argTypes.register("-" + SEQ_TABLE_DROP, "-dst", SEQ_TABLE_DROP,
+            "Drops existing 'sequence' table tables.", false);
 

       argTypes.register("-" + PARTITION_CREATE, "-cp", PARTITION_CREATE,

             "Creates a new partition with the given ID, if no partition having this ID currently exists.", true);

@@ -107,7 +119,8 @@ public class AlterAuditTrailCommand extends AuditTrailCommand
       argTypes.addExclusionRule(//

             new String[] { LOCKTABLE_ENABLE, LOCKTABLE_VERIFY, LOCKTABLE_DROP,//

                   DATACLUSTER_ENABLE, DATACLUSTER_VERIFY, DATACLUSTER_DROP,//

-                  PARTITION_CREATE, PARTITION_DROP, PARTITIONS_LIST, AUDITTRAIL_CHECK_CONSISTENCY }, true);
+                  PARTITION_CREATE, PARTITION_DROP, PARTITIONS_LIST, AUDITTRAIL_CHECK_CONSISTENCY,//
+                  SEQ_TABLE_ENABLE, SEQ_TABLE_VERIFY, SEQ_TABLE_DROP}, true);
       argTypes.addExclusionRule(//

             new String[] { LOCKTABLE_ENABLE, LOCKTABLE_VERIFY, LOCKTABLE_DROP,//

                   DATACLUSTER_VERIFY, DATACLUSTER_DROP,//

@@ -410,6 +423,85 @@ public class AlterAuditTrailCommand extends AuditTrailCommand
       return optionHandled;
    }
 
+   private boolean doRunSequenceTableOptions(Map options)
+   {
+      boolean optionHandled = false;
+      String dbType = Parameters.instance().getString(
+            SessionProperties.DS_NAME_AUDIT_TRAIL + SessionProperties.DS_TYPE_SUFFIX);
+      if (DBMSKey.MYSQL_SEQ.getId().equalsIgnoreCase(dbType))
+      {
+         final String password = (String) globalOptions.get("password");
+         optionHandled = true;
+         if (options.containsKey(SEQ_TABLE_ENABLE))
+         {
+            boolean skipDdl = options.containsKey(AUDITTRAIL_SKIPDDL);
+            boolean skipDml = options.containsKey(AUDITTRAIL_SKIPDML);
+            if (!skipDdl)
+            {
+               print("Creating 'sequence' table and 'next_sequence_value_for' function.");
+            }
+            if (!skipDml)
+            {
+               print("Synchronizing 'sequence' table content.");
+            }
+            try
+            {
+               SchemaHelper.alterAuditTrailCreateSequenceTable(password, skipDdl,
+                     skipDml, spoolDevice);
+            }
+            catch (SQLException e)
+            {
+               trace.warn("", e);
+               throw new PublicException("SQL Exception occured: " + e.getMessage());
+            }
+            if (!skipDdl)
+            {
+               print("'sequence' table and 'next_sequence_value_for' function created.");
+            }
+            if (!skipDml)
+            {
+               print("'sequence' table synchronized.");
+            }
+         }
+         else if (options.containsKey(SEQ_TABLE_VERIFY))
+         {
+            optionHandled = true;
+            print("Verifying existence of 'sequence' table and its consistency.");
+            try
+            {
+               SchemaHelper.alterAuditTrailVerifySequenceTable(password);
+            }
+            catch (SQLException e)
+            {
+               trace.warn("", e);
+               throw new PublicException("SQL Exception occured: " + e.getMessage());
+            }
+            print("Verification of 'sequence' table and its consistency done.");
+         }
+         else if (options.containsKey(SEQ_TABLE_DROP))
+         {
+            optionHandled = true;
+            print("Dropping 'sequence' table from Infinity schema.");
+            try
+            {
+               SchemaHelper.alterAuditTrailDropSequenceTable(password, spoolDevice);
+            }
+            catch (SQLException e)
+            {
+               trace.warn("", e);
+               throw new PublicException("SQL Exception occured: " + e.getMessage());
+            }
+            print("'sequence' table dropped.");
+         }
+      }
+      else
+      {
+         print("Invalid audittrail type. 'AuditTrail.Type in carnot.properties' "
+               + "or global option 'dbtype' has to be set to 'MYSQL_SEQ'.");
+      }
+      return optionHandled;
+   }
+
    public int doRun(Map options)

    {

       if (globalOptions.containsKey("dbtype"))

@@ -439,7 +531,8 @@ public class AlterAuditTrailCommand extends AuditTrailCommand
       }

 
       if ( !doRunLockingTableOptions(options) && !doRunDataClusterOptions(options)

-            && !doRunPartitionOptions(options) && !doRunCheckConsistencyOptions(options))
+            && !doRunPartitionOptions(options) && !doRunCheckConsistencyOptions(options)
+            && !doRunSequenceTableOptions(options))
       {

          print("Unknown option for command auditTrail.");

       }

diff --git a/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/persistence/jdbc/DDLManager.java b/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/persistence/jdbc/DDLManager.java
index 7af72d9..995dda3 100644
--- a/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/persistence/jdbc/DDLManager.java
+++ b/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/persistence/jdbc/DDLManager.java
@@ -46,6 +46,8 @@ import org.eclipse.stardust.engine.core.struct.beans.StructuredDataValueBean;
  */

 public class DDLManager

 {

+   private static final String SEQUENCE_TABLE_NAME = "sequence";
+
    public static final Logger trace = LogManager.getLogger(DDLManager.class);

 

    private DBDescriptor dbDescriptor;

@@ -484,6 +486,171 @@ public class DDLManager
                type.getName() + "'.", x);

       }

    }

+   
+   public void createSequenceTable(String schemaName, Connection connection,
+         PrintStream spoolFile) throws SQLException
+   {
+      if (!containsTable(schemaName, SEQUENCE_TABLE_NAME, connection))
+      {
+         // create table sequence
+         executeOrSpoolStatement(
+               dbDescriptor.getCreateGlobalPKSequenceStatementString(schemaName),
+               connection, spoolFile);
+
+         // create next_sequence_value_for function
+         executeOrSpoolStatement(
+               dbDescriptor.getCreateSequenceStoredProcedureStatementString(schemaName),
+               connection, spoolFile);
+      }
+   }
+   
+   public void synchronizeSequenceTable(String schemaName, Connection connection,
+         PrintStream spoolFile) throws SQLException
+   {
+      if (containsTable(schemaName, SEQUENCE_TABLE_NAME, connection))
+      {
+         // synchronize sequence table with the current primary key value
+         Collection<Class> persistentClasses = SchemaHelper
+               .getPersistentClasses(dbDescriptor);
+         for (Class clazz : persistentClasses)
+         {
+            TypeDescriptor typeManager = TypeDescriptor.get(clazz);
+            if (typeManager.hasPkSequence())
+            {
+               String tableName = schemaName + "." + SEQUENCE_TABLE_NAME;
+               StringBuilder selectMaxOidSQLString = new StringBuilder();
+               selectMaxOidSQLString
+                     .append("(SELECT max(x) FROM (SELECT max(oid) AS x FROM ");
+               selectMaxOidSQLString.append(schemaName);
+               selectMaxOidSQLString.append(".");
+               selectMaxOidSQLString.append(typeManager.getTableName());
+               selectMaxOidSQLString.append(" UNION SELECT 0) AS max)");
+               StringBuilder selectSequenceTableEntrySQLString = new StringBuilder();
+               selectSequenceTableEntrySQLString.append("(SELECT name FROM ");
+               selectSequenceTableEntrySQLString.append(tableName);
+               selectSequenceTableEntrySQLString.append(" seq WHERE seq.name='");
+               selectSequenceTableEntrySQLString.append(typeManager.getPkSequence());
+               selectSequenceTableEntrySQLString.append("')");
+               Statement existsStmt = null;
+               ResultSet resultSet = null;
+               try
+               {
+                  existsStmt = connection.createStatement();
+                  existsStmt.execute(selectSequenceTableEntrySQLString.toString());
+                  resultSet = existsStmt.getResultSet();
+                  if (resultSet.next())
+                  {
+                     executeOrSpoolStatement(
+                           "UPDATE " + tableName + " SET value=" + selectMaxOidSQLString
+                                 + "WHERE name='" + typeManager.getPkSequence() + "'",
+                           connection, spoolFile);
+                  }
+                  else
+                  {
+                     executeOrSpoolStatement("INSERT INTO " + tableName + " VALUES ('"
+                           + typeManager.getPkSequence() + "', " + selectMaxOidSQLString
+                           + ")", connection, spoolFile);
+                  }
+               }
+               finally
+               {
+                  QueryUtils.closeStatementAndResultSet(existsStmt, resultSet);
+               }
+
+            }
+         }
+      }
+   }
+   
+   public void dropSequenceTable(String schemaName, Connection connection,
+         PrintStream spoolFile) throws SQLException
+   {
+      if (containsTable(schemaName, SEQUENCE_TABLE_NAME, connection))
+      {
+         // drop table sequence
+         executeOrSpoolStatement(
+               dbDescriptor.getDropGlobalPKSequenceStatementString(schemaName),
+               connection, spoolFile);
+
+         // drop next_sequence_value_for function
+         executeOrSpoolStatement(
+               dbDescriptor.getDropSequenceStoredProcedureStatementString(schemaName),
+               connection, spoolFile);
+      }
+   }
+   
+   public void verifySequenceTable(Connection connection, String schemaName)
+         throws SQLException
+   {
+      if (!containsTable(schemaName, SEQUENCE_TABLE_NAME, connection))
+      {
+         String message = "Table '" + SEQUENCE_TABLE_NAME + "' does not exist.";
+         System.out.println(message);
+         trace.warn(message);
+      }
+      else
+      {
+         boolean inconsistent = false;
+         Collection<Class> persistentClasses = SchemaHelper
+               .getPersistentClasses(dbDescriptor);
+         for (Class clazz : persistentClasses)
+         {
+            TypeDescriptor typeManager = TypeDescriptor.get(clazz);
+            if (typeManager.hasPkSequence())
+            {
+               Statement verifyStmt = null;
+               ResultSet resultSet = null;
+               StringBuilder verifySQLString = new StringBuilder();
+               verifySQLString.append("SELECT name, value FROM ");
+               verifySQLString.append(schemaName);
+               verifySQLString.append(".");
+               verifySQLString.append(SEQUENCE_TABLE_NAME);
+               verifySQLString.append(" seq WHERE seq.name='");
+               verifySQLString.append(typeManager.getPkSequence());
+               verifySQLString
+                     .append("' AND seq.value=(SELECT max(x) FROM (SELECT max(oid) AS x FROM ");
+               verifySQLString.append(schemaName);
+               verifySQLString.append(".");
+               verifySQLString.append(typeManager.getTableName());
+               verifySQLString.append(" UNION SELECT 0) AS max)");
+               try
+               {
+                  verifyStmt = connection.createStatement();
+                  verifyStmt.execute(verifySQLString.toString());
+                  resultSet = verifyStmt.getResultSet();
+                  if (!resultSet.next())
+                  {
+                     inconsistent = true;
+                  }
+               }
+               catch (SQLException e)
+               {
+                  String message = "Couldn't verify sequence table '"
+                        + SEQUENCE_TABLE_NAME + "'." + " Reason: " + e.getMessage();
+                  System.out.println(message);
+                  trace.warn(message, e);
+               }
+               finally
+               {
+                  QueryUtils.closeStatementAndResultSet(verifyStmt, resultSet);
+               }
+            }
+         }
+         if (inconsistent)
+         {
+            String message = "Table " + SEQUENCE_TABLE_NAME
+                  + " is not consistent. Synchronization is required.";
+            System.out.println(message);
+            trace.warn(message);
+         }
+         else
+         {
+            String message = "Table " + SEQUENCE_TABLE_NAME + " is consistent.";
+            System.out.println(message);
+            trace.info(message);
+         }
+      }
+   }
 

    public void createLockTableForClass(String schemaName, Class type,

          Connection connection, PrintStream spoolFile, String statementDelimiter)

@@ -2803,4 +2970,5 @@ public class DDLManager
          return sqlType;

       }

    }

+
 }

diff --git a/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/runtime/beans/SchemaHelper.java b/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/runtime/beans/SchemaHelper.java
index 184d419..7da986d 100644
--- a/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/runtime/beans/SchemaHelper.java
+++ b/stardust-engine-core/src/main/java/org/eclipse/stardust/engine/core/runtime/beans/SchemaHelper.java
@@ -728,6 +728,128 @@ public class SchemaHelper
          new PropertyPersistor(name, defaultValue);

       }

    }

+   
+   public static void alterAuditTrailCreateSequenceTable(String sysconPassword,
+         boolean skipDdl, boolean skipDml, PrintStream spoolFile) throws SQLException
+   {
+      Session session = (Session) SessionFactory.getSession(SessionFactory.AUDIT_TRAIL);
+      Map locals = new HashMap();
+      locals.put(SessionFactory.AUDIT_TRAIL + SessionProperties.DS_SESSION_SUFFIX,
+            session);
+      try
+      {
+         ParametersFacade.pushLayer(locals);
+         verifySysopPassword(session, sysconPassword);
+         DBDescriptor dbDescriptor = session.getDBDescriptor();
+         DDLManager ddlManager = new DDLManager(dbDescriptor);
+         final String schemaName = Parameters.instance().getString(
+               SessionFactory.AUDIT_TRAIL + SessionProperties.DS_SCHEMA_SUFFIX,
+               Parameters.instance().getString(
+                     SessionFactory.AUDIT_TRAIL + SessionProperties.DS_USER_SUFFIX));
+
+         if (!skipDdl)
+         {
+            if (null != spoolFile)
+            {
+               spoolFile
+                     .println("/* DDL-statements for creation of 'sequence' table and 'next_sequence_value_for' function */");
+            }
+            ddlManager
+                  .createSequenceTable(schemaName, session.getConnection(), spoolFile);
+         }
+         if (!skipDml)
+         {
+            if (null != spoolFile)
+            {
+               spoolFile
+                     .println("/* DML-statements for synchronization of 'sequence' table */");
+            }
+
+            ddlManager.synchronizeSequenceTable(schemaName, session.getConnection(),
+                  spoolFile);
+         }
+
+         if (null != spoolFile)
+         {
+            spoolFile.println();
+            spoolFile.println("commit;");
+         }
+         session.save();
+      }
+      finally
+      {
+         ParametersFacade.popLayer();
+      }
+   }
+   
+   public static void alterAuditTrailDropSequenceTable(String sysconPassword,
+         PrintStream spoolFile) throws SQLException
+   {
+      Session session = SessionFactory.createSession(SessionFactory.AUDIT_TRAIL);
+      Map locals = new HashMap();
+      locals.put(SessionFactory.AUDIT_TRAIL + SessionProperties.DS_SESSION_SUFFIX,
+            session);
+      try
+      {
+         ParametersFacade.pushLayer(locals);
+         verifySysopPassword(session, sysconPassword);
+         DBDescriptor dbDescriptor = session.getDBDescriptor();
+         DDLManager ddlManager = new DDLManager(dbDescriptor);
+         Connection connection = session.getConnection();
+
+         final String schemaName = Parameters.instance().getString(
+               SessionFactory.AUDIT_TRAIL + SessionProperties.DS_SCHEMA_SUFFIX,
+               Parameters.instance().getString(
+                     SessionFactory.AUDIT_TRAIL + SessionProperties.DS_USER_SUFFIX));
+
+         if (null != spoolFile)
+         {
+            spoolFile
+                  .println("/* DDL-statements for dropping 'sequence' table and 'next_sequence_value_for' function */");
+         }
+         ddlManager.dropSequenceTable(schemaName, connection, spoolFile);
+
+         if (null != spoolFile)
+         {
+            spoolFile.println();
+            spoolFile.println("commit;");
+         }
+         session.save();
+      }
+      finally
+      {
+         ParametersFacade.popLayer();
+      }
+   }
+   
+   public static void alterAuditTrailVerifySequenceTable(String sysconPassword)
+         throws SQLException
+   {
+      Session session = SessionFactory.createSession(SessionFactory.AUDIT_TRAIL);
+      Map locals = new HashMap();
+      locals.put(SessionFactory.AUDIT_TRAIL + SessionProperties.DS_SESSION_SUFFIX,
+            session);
+      try
+      {
+         ParametersFacade.pushLayer(locals);
+         verifySysopPassword(session, sysconPassword);
+
+         DBDescriptor dbDescriptor = session.getDBDescriptor();
+         DDLManager ddlManager = new DDLManager(dbDescriptor);
+
+         final String schemaName = Parameters.instance().getString(
+               SessionFactory.AUDIT_TRAIL + SessionProperties.DS_SCHEMA_SUFFIX,
+               Parameters.instance().getString(
+                     SessionFactory.AUDIT_TRAIL + SessionProperties.DS_USER_SUFFIX));
+
+         ddlManager.verifySequenceTable(session.getConnection(), schemaName);
+         session.save();
+      }
+      finally
+      {
+         ParametersFacade.popLayer();
+      }
+   }
 

    public static void alterAuditTrailCreateLockingTables(String sysconPassword)

          throws SQLException