Changeset 5853

Timestamp:

Nov 7, 2011, 2:50:41 PM (10 years ago)

Author:

Nicklas Nordborg

Message:

References #1630: Migrate from MySQL to PostgreSQL

Forgot to add the 'Migration.java' file in the last checkin...

Parts of the import functionality has been done. It should create a new empty database, and import the static part of the database. The dynamic part is not imported, and no foreign keys or indexes are created.

Location:

trunk

Files:

• bin/migrate.sh (added)
• src/core/net/sf/basedb/core/HibernateUtil.java (modified) (3 diffs)
• src/core/net/sf/basedb/core/Install.java (modified) (7 diffs)
• src/core/net/sf/basedb/core/Migration.java (added)
• src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java (modified) (2 diffs)
• src/core/net/sf/basedb/core/dbengine/DbEngine.java (modified) (1 diff)
• src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java (modified) (2 diffs)
• src/core/net/sf/basedb/core/dbengine/TableInfo.java (modified) (2 diffs)
• src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java (modified) (6 diffs)
• src/install/net/sf/basedb/install/InitDB.java (modified) (4 diffs)

trunk/src/core/net/sf/basedb/core/HibernateUtil.java

@@ -592 +592 @@
     @return The schemaVersion that is currently installed
   */
-  static int createStaticTables(boolean update, ProgressReporter progress)
+  static int createStaticTables(SchemaGenerator.Mode mode, ProgressReporter progress)
     throws BaseException
   {
@@ -598 +598 @@
 
     int currentSchemaVersion = Install.NEW_SCHEMA_VERSION;
-    if (update)
+    if (mode == SchemaGenerator.Mode.UPDATE)
     {
       currentSchemaVersion = Application.getSchemaVersion();        
@@ -627 +627 @@
     try
     {
-      SchemaGenerator schemaGenerator = new SchemaGenerator(cfg, dialect, dbEngine, update, progress);
+      SchemaGenerator schemaGenerator = new SchemaGenerator(cfg, dialect, dbEngine, mode, progress);
       session = HibernateUtil.newSession();
       tx = HibernateUtil.newTransaction(session);

trunk/src/core/net/sf/basedb/core/Install.java

@@ -63 +63 @@
 import net.sf.basedb.core.data.MappingCoordinate;
 import net.sf.basedb.core.data.PluginTypeData;
+import net.sf.basedb.core.hibernate.SchemaGenerator;
 
 import net.sf.basedb.core.SessionControl;
@@ -119 +120 @@
   public static final int NEW_SCHEMA_VERSION = Integer.valueOf(100).intValue();
   
-  public static synchronized int createTables(boolean update, ProgressReporter progress,
+  public static synchronized int createTables(SchemaGenerator.Mode mode, ProgressReporter progress,
     String rootLogin, String rootPassword)
     throws BaseException
@@ -129 +130 @@
       Application.start(true, false, false);
       
-      if (update)
+      if (mode == SchemaGenerator.Mode.UPDATE)
       {
         Update.verifyRootLoginAndPassword(rootLogin, rootPassword);
@@ -135 +136 @@
       
       if (progress != null) progress.display(0, "Building database...");
-      schemaVersion = HibernateUtil.createStaticTables(update, progress);
+      schemaVersion = HibernateUtil.createStaticTables(mode, progress);
       message = "Database built successfully.";
     }
@@ -159 +160 @@
     will not be modified.
     
-    @param update TRUE if the run is an update, FALSE otherwise. 
+    @param mode The installation mode
     @param progress An object implementing the {@link ProgressReporter}
       interface
@@ -166 +167 @@
     @throws BaseException This exception is thrown if there is an error
   */
-  public static synchronized void initDatabase(boolean update, ProgressReporter progress, String rootLogin, String rootPassword)
+  public static synchronized void initDatabase(SchemaGenerator.Mode mode, ProgressReporter progress, String rootLogin, String rootPassword)
     throws BaseException
   {
@@ -178 +179 @@
       Application.start(false, false, false);
       session = HibernateUtil.newSession();
+      boolean update = mode == SchemaGenerator.Mode.UPDATE;
   
       // SchemaVersion

trunk/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java

@@ -23 +23 @@
 
 import java.util.regex.Pattern;
+
+import net.sf.basedb.core.hibernate.SchemaGenerator;
 
 import org.hibernate.dialect.Dialect;
@@ -193 +195 @@
     statements that doesn't supply a default value for the new column
     when updating.
-    @since 2.16
+    @since 3.1
   */
   @Override
   public String inspectSchemaGenerationSQL(String sql, Dialect dialect,
-      boolean update)
+      SchemaGenerator.Mode mode)
   {
     String addColumn = dialect.getAddColumnString();
-    if (update && sql.contains("alter table") && sql.contains(addColumn) && 
+    if (mode == SchemaGenerator.Mode.UPDATE && 
+        sql.contains("alter table") && sql.contains(addColumn) && 
         sql.contains("not null") && !sql.contains("default"))
     {

trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

@@ -363 +363 @@
     @param sql The original SQL statment as generated by Hibernate
     @param dialect The Hibernate dialect currently in use
-    @param update TRUE if we are updating an existing database,
-      FALSE if we are creating a new one
+    @param mode The installation mode
     @return The SQL statement which may have been modified or null
       to skip executing the statment
-    @since 2.16
-  */
-  public String inspectSchemaGenerationSQL(String sql, Dialect dialect, boolean update);
+    @since 3.1
+  */
+  public String inspectSchemaGenerationSQL(String sql, Dialect dialect, SchemaGenerator.Mode mode);
   
 }

trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

@@ -23 +23 @@
 
 import java.util.Set;
+
+import net.sf.basedb.core.hibernate.SchemaGenerator;
 
 import org.hibernate.dialect.Dialect;
@@ -243 +245 @@
     with unique names. PostgreSQL users should always run the ./updateindexes.sh
     tool which will create the missing indexes.
-    @since 2.16
+    @since 3.1
   */
   @Override
   public String inspectSchemaGenerationSQL(String sql, Dialect dialect,
-      boolean update)
-  {
-    sql = super.inspectSchemaGenerationSQL(sql, dialect, update);
+      SchemaGenerator.Mode mode)
+  {
+    sql = super.inspectSchemaGenerationSQL(sql, dialect, mode);
     if (sql != null && sql.startsWith("create index"))
     {
       sql = null;
     }
+    
+    if (sql != null && mode == SchemaGenerator.Mode.MIGRATE)
+    {
+      if (sql.startsWith("alter table") && sql.contains("add constraint") && sql.contains("foreign key"))
+      {
+        // Skip foreign key creation
+        sql = null;
+      }
+      else if (sql.contains("unique"))
+      {
+        // Do not add "unique" contstraints
+        // Variant 1: ... , unique (column, column) ...
+        sql = sql.replaceAll(", unique \\([^)]*\\)", "");
+        // Variant 2: ... column ... unique, ....
+        sql = sql.replaceAll("unique,", ",");
+      }
+      
+    }
+    
     return sql;
   }

trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java

@@ -79 +79 @@
   {
     List<Table> tables = new LinkedList<Table>();
-    ResultSet result = metaData.getTables(catalog, schema, null, null);
+    ResultSet result = metaData.getTables(catalog, schema, null, new String[] { "TABLE" });
     while (result.next())
     {
@@ -85 +85 @@
       String tableCatalog = result.getString("TABLE_CAT");
       String tableSchema = result.getString("TABLE_SCHEM");
+      
+      //System.out.println(tableName + ":" + result.getString("TABLE_TYPE"));
+      
       Table table = new Table();
       table.setName(tableName);

trunk/src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java

@@ -56 +56 @@
   private final Dialect dialect;
   private final DbEngine dbEngine;
-  private final boolean update;
+  private final Mode mode;
   private final ProgressReporter progress;
 
@@ -65 +65 @@
     @param dialect The dialect of the database we are connecting to
     @param dbEngine The DbEngine for the database we are connecting to
-    @param update TRUE if we are updating an existing database, FALSE if
-      we are creating a new one
+    @param mode The installation mode
     @param progress An optional progress reporter
   */
-  public SchemaGenerator(Configuration cfg, Dialect dialect, DbEngine dbEngine, boolean update, ProgressReporter progress)
+  public SchemaGenerator(Configuration cfg, Dialect dialect, DbEngine dbEngine, Mode mode, ProgressReporter progress)
   {
     this.cfg = cfg;
     this.dialect = dialect;
     this.dbEngine = dbEngine;
-    this.update = update;
+    this.mode = mode;
     this.progress = progress;
   }
@@ -94 +93 @@
     {
       String[] allSql = null;
-      if (update)
+      if (mode == Mode.UPDATE)
       {
         log.info("Fetching database metadata");
@@ -109 +108 @@
       for (String sql : allSql)
       {
-        String actualSql = dbEngine.inspectSchemaGenerationSQL(sql, dialect, update);
+        String actualSql = dbEngine.inspectSchemaGenerationSQL(sql, dialect, mode);
         if (actualSql != null)
         {
@@ -118 +117 @@
           log.debug("Executing: " + actualSql);
           stmt.executeUpdate(actualSql);
+          //System.out.println(actualSql);
+          if (progress != null)
+          {
+            if (numDone % interval == 0)
+            {
+              progress.display((100 * numDone) / numStatements, "--" + StringUtil.trimString(actualSql, 45));
+            }
+            else
+            {
+              progress.append(".");
+            }
+          }
         }
         else
         {
           log.debug("Ignoring: " + sql);
-        }
-        if (progress != null)
-        {
-          if ((numDone % interval == 0) && (actualSql != null))
-          {
-            progress.display((100 * numDone) / numStatements, "--" + StringUtil.trimString(actualSql, 45));
-          }
-          else
-          {
-            progress.append(".");
-          }
         }
         numDone++;
@@ -158 +158 @@
   }
 
+  /**
+    The installation mode.
+    @since 3.1
+  */
+  public static enum Mode
+  {
+    /**
+      Creating a fresh installation.
+    */
+    INSTALL,
+    /**
+      Updating an existing installation.
+    */
+    UPDATE,
+    /**
+      Creating a minimal database schema for migration. The
+      schema should be created without foreign key and 
+      other constraints.
+    */
+    MIGRATE
+  }
+  
 }

trunk/src/install/net/sf/basedb/install/InitDB.java

@@ -36 +36 @@
 import net.sf.basedb.core.Update;
 import net.sf.basedb.core.Version;
+import net.sf.basedb.core.hibernate.SchemaGenerator;
 import net.sf.basedb.util.ConsoleProgressReporter;
 import net.sf.basedb.util.ChainedProgressReporter;
@@ -67 +68 @@
         String rootLogin = args.length == 3 ? args[1] : "root";
         
-        boolean update = "update".equals(cmd);
+        SchemaGenerator.Mode mode = "update".equals(cmd) ? 
+            SchemaGenerator.Mode.UPDATE : SchemaGenerator.Mode.INSTALL;
         ChainedProgressReporter progress = new ChainedProgressReporter(new ConsoleProgressReporter());
         progress.setRange(0, 30);
         showDbInfo(progress);
-        int schemaVersion = Install.createTables(update, progress, rootLogin, rootPassword);
+        int schemaVersion = Install.createTables(mode, progress, rootLogin, rootPassword);
         progress.setRange(30, 50);
-        if (update)
+        if (mode == SchemaGenerator.Mode.UPDATE)
         {
           if (schemaVersion < 99) 
@@ -85 +87 @@
         }
         progress.setRange(50, 75);
-        Install.initDatabase(update, progress, rootLogin, rootPassword);
+        Install.initDatabase(mode, progress, rootLogin, rootPassword);
         progress.setRange(75, 90);
-        if (update) Update.updateDatabase(progress, rootLogin, rootPassword);
+        if (mode == SchemaGenerator.Mode.UPDATE)
+        {
+          Update.updateDatabase(progress, rootLogin, rootPassword);
+        }
         progress.setRange(95, 100);
         progress.display(0, "Installing web application...");
@@ -141 +146 @@
         if (export)
         {
-          Application.start(false);
           Migration.exportAll(path, new ConsoleProgressReporter(false));
         }
         else
         {
+          ChainedProgressReporter progress = new ChainedProgressReporter(new ConsoleProgressReporter());
           // Create empty database without indexes and foreign keys
+          progress.setRange(0, 5);
+          Install.createTables(SchemaGenerator.Mode.MIGRATE, progress, null, null);
+          progress.append("\n");
+
           // Import all data
+          progress = new ChainedProgressReporter(new ConsoleProgressReporter(false));
+          progress.setRange(5, 90);
+          Migration.importAll(path, progress);
+          
           // Add indexes, foreign keys, etc. to database
         }