Changeset 5854

Timestamp:

Nov 8, 2011, 1:43:27 PM (11 years ago)

Author:

Nicklas Nordborg

Message:

References #1630: Migrate from MySQL to PostgreSQL

The import step now import all data and create missing foreign keys and unique indexes. The resulting database should be usable in a BASE installation. There are still some error handling to implement.

Location:

trunk

Files:

• bin/migrate.bat (added)
• src/core/net/sf/basedb/core/HibernateUtil.java (modified) (1 diff)
• src/core/net/sf/basedb/core/Migration.java (modified) (14 diffs)
• src/core/net/sf/basedb/core/VirtualTable.java (modified) (1 diff)
• src/core/net/sf/basedb/core/dbengine/TableInfo.java (modified) (2 diffs)
• src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java (modified) (4 diffs)
• src/install/net/sf/basedb/install/InitDB.java (modified) (1 diff)

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

@@ -628 +628 @@
     {
       SchemaGenerator schemaGenerator = new SchemaGenerator(cfg, dialect, dbEngine, mode, progress);
+      schemaGenerator.setCreateMissingUniqueConstraints(true);
       session = HibernateUtil.newSession();
       tx = HibernateUtil.newTransaction(session);

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

@@ -43 +43 @@
 import java.util.SortedSet;
 import java.util.TreeSet;
-
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import net.sf.basedb.core.data.VirtualDbData;
 import net.sf.basedb.core.dbengine.DbEngine;
+import net.sf.basedb.core.dbengine.PostgresDbEngine;
 import net.sf.basedb.core.dbengine.TableInfo;
 import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo;
 import net.sf.basedb.core.hibernate.JdbcWork;
+import net.sf.basedb.core.hibernate.SchemaGenerator;
 import net.sf.basedb.util.FileUtil;
 import net.sf.basedb.util.RegexpFileFilter;
@@ -59 +64 @@
 
 /**
-  Class for migrating a database from MySQL to 
-  PostgreSQL. The {@link #exportAll(String, ProgressReporter)} method
-  only works on a MySQL database and the {@link #importAll(String, ProgressReporter)}
+  Class for migrating a database to PostgreSQL. 
+  The {@link #exportAll(String, ProgressReporter)} method should work on 
+  both MySQL and PostgreSQL (but is only tested regularly with MySQL). 
+  The export generates data that is suitable for importing into
+  PostgreSQL using the COPY command. The {@link #importAll(String, ProgressReporter)} 
   only works on a PostgreSQL database.
 
@@ -109 +116 @@
       if (tx != null) HibernateUtil.rollback(tx);
       if (session != null) HibernateUtil.close(session);
+      Application.stop();
     }
   }
@@ -145 +153 @@
       if (tx != null) HibernateUtil.rollback(tx);
       if (session != null) HibernateUtil.close(session);
+      Application.stop();
+    }
+  }
+  
+  public static void createMissingConstraints(ProgressReporter progress)
+  {
+    try
+    {
+      Application.start(false, false, false);
+      progress.display(0, "Creating constraints...");
+      HibernateUtil.createStaticTables(SchemaGenerator.Mode.UPDATE, progress);
+    }
+    finally
+    {
+      Application.stop();
     }
   }
@@ -196 +219 @@
   // -------------------
   
-  
+  /**
+    Perform the export. In principle we support all databases that BASE supports,
+    but it may depend on the actual underlying SQL column types. The export
+    generates files that can be imported into PostgreSQL only.
+  */
   private void doExport(Connection connection)
     throws SQLException, IOException
@@ -203 +230 @@
     DatabaseMetaData metaData = connection.getMetaData();
     DbEngine engine = HibernateUtil.getDbEngine();
+    
+    String staticCatalog = null;
+    String staticSchema = null;
+    String dynamicCatalog = Application.getDynamicCatalog();
+    String dynamicSchema = Application.getDynamicSchema();
+    
+    if (engine instanceof PostgresDbEngine)
+    {
+      staticSchema = "public";
+    }
 
     // Load information about all tables in the static and dynamic databases
-    progress.display(0, "Loading tables...\n");
+    progress.display(0, "Counting tables...\n");
     final int TICK_INTERVAL = 1000;
     long nextTick = System.currentTimeMillis() + TICK_INTERVAL;
-    List<Table> staticTables = TableInfo.listTables(metaData, null, null);
+    List<Table> staticTables = TableInfo.listTables(metaData, staticCatalog, staticSchema);
     progress.display(0, staticTables.size() + " tables in static database\n");
 
-    List<Table> dynamicTables = TableInfo.listTables(metaData, 
-        Application.getDynamicCatalog(), Application.getDynamicSchema());
-    progress.display(0, dynamicTables.size() + " tables in static database");
+    List<Table> dynamicTables = TableInfo.listTables(metaData, dynamicCatalog, dynamicSchema);
+    progress.display(0, dynamicTables.size() + " tables in dynamic database\n");
+    progress.display(0, "Counting rows...");
     
     // Get metadata (eg. column names+types) 
@@ -244 +281 @@
     }
 
-    progress.append("\n");
-    progress.display(0, "Found " + tables.size() + " tables with " + 
-        Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n"
-        );
+    progress.append(" found " + Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n");
     
     
@@ -315 +349 @@
       writer.flush();
       writer.close();
-
-    }
-  }
-  
+    }
+  }
+  
+  /**
+    Perform the import. Importing is supported on PostgreSQL only.
+  */
   private void doImport(Connection connection)
     throws SQLException, IOException
@@ -325 +361 @@
     DbEngine engine = HibernateUtil.getDbEngine();
 
+    if (!(engine instanceof PostgresDbEngine))
+    {
+      throw new UnsupportedOperationException("Importing is supported for PostgreSQL only");
+    }
+    
     // Load information about all tables in the static and dynamic databases
-    progress.display(0, "Loading tables...\n");
+    progress.display(0, "Loading tables...");
     final int TICK_INTERVAL = 1000;
     long nextTick = System.currentTimeMillis() + TICK_INTERVAL;
@@ -355 +396 @@
     // And then the dynamic tables
     // Since they don't exists in the database yet, we need to scan the files
+    Pattern dynamicFilePattern = Pattern.compile("d(\\d+)(.+)\\.data");
     List<File> dynamicDataFiles = FileUtil.findFiles(directory, 
-        new RegexpFileFilter("d\\d+.*", null));
+        new RegexpFileFilter(dynamicFilePattern, null));
     progress.display(0, dynamicDataFiles.size() + " files with dynamic data");
-    
     for (File f : dynamicDataFiles)
     {
@@ -364 +405 @@
     }
     
-    
-    progress.append("\n");
     progress.display(0, "Found " + (tables.size()+dynamicDataFiles.size()) + " files with " + 
         Values.formatBytes(totalFileSize, 2) + " data\n"
@@ -382 +421 @@
       }
     }
+    // Keep track of the max id used in all tables (so that we can set the 'hibernate_sequence')
     int maxGlobalId = 0;
     
@@ -390 +430 @@
     {
       File importFile = getDataFile(table);
-      String tableName = table.getTable().getName();
+      if (importFile.length() > 0)
+      {
+        String tableName = table.getTable().getName();
+        progress.display((int)((totalImported * 100) / totalFileSize), 
+            tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "...");
+        
+        // Get columns in sorted order
+        SortedSet<ColumnInfo> columns =  new TreeSet<ColumnInfo>();
+        columns.addAll(table.getColumns());
+        
+        // Create COPY (<columns>) FROM '<filename>'
+        StringBuilder copy = new StringBuilder("COPY ");
+        copy.append(table.getFullQuotedTableName(engine));
+        copy.append(" (");
+        String separator = "";
+        boolean hasIdColumn = false;
+        for (ColumnInfo c : columns)
+        {
+          copy.append(separator);
+          copy.append(engine.getQuotedName(c.getName()));
+          separator = ",";
+          hasIdColumn |= "id".equals(c.getName());
+        }
+        copy.append(") FROM '").append(importFile.getAbsolutePath() + "'");
+    
+        // Import the data
+        st.executeUpdate(copy.toString());
+        
+        // Get max id (if 'id' column exists)
+        if (hasIdColumn)
+        {
+          String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine);
+          Query query = HibernateUtil.createSqlQuery(session, sql);
+          Integer maxId = HibernateUtil.loadData(Integer.class, query);
+          if (maxId != null) // empty tables have no max id
+          {
+            if (sequenceGenerators.containsKey(tableName))
+            {
+              // Set the value for the special sequence generator
+              sql = "SELECT setval('" + sequenceGenerators.get(tableName) + "', " + maxId + ")";
+              st.execute(sql);
+            }
+            else
+            {
+              // Global 'hibernate_sequence'
+              if (maxGlobalId < maxId) maxGlobalId = maxId;
+            }
+          }
+        }
+        
+        // Done with this table...
+        totalImported += importFile.length();
+        progress.append("done");
+      }
+    }
+    
+    // Set the value for the 'hibernate_sequence' generator
+    String sql = "SELECT setval('hibernate_sequence', " + maxGlobalId + ")";
+    st.execute(sql);
+    
+    // Import data for each table in the dynamic database
+    String dynamicCatalog = HibernateUtil.quote(Application.getDynamicCatalog());
+    String dynamicSchema = HibernateUtil.quote(Application.getDynamicSchema());
+    for (File dynamicDataFile : dynamicDataFiles)
+    {
+      Matcher m = dynamicFilePattern.matcher(dynamicDataFile.getName());
+      if (!m.matches())
+      {
+        // Should never happen since we use the same filter to match files at the beginning
+        throw new AssertionError("File is not a dynamic data file: " + dynamicDataFile.getAbsolutePath());
+      }
+      int virtualDbId = Values.getInt(m.group(1));
+      String tableType = m.group(2).toLowerCase();
+      
+      // Load the VirtualDb object that is managing the dynamic data table
+      VirtualDbData vdbData = HibernateUtil.loadData(session, VirtualDbData.class, virtualDbId);
+      VirtualDb vdb = new VirtualDb(vdbData);
+
+      // Find the VirtualTable for this file
+      VirtualTable vTable = null;
+      String tableName = null;
+      for (VirtualTable vt : VirtualTable.values())
+      {
+        tableName = vt.getTableName(vdb); 
+        if (tableName.toLowerCase().endsWith(tableType))
+        {
+          vTable = vt;
+          break;
+        }
+      }
+
+      if (vTable == null)
+      {
+        throw new ItemNotFoundException("No virtual table found for file: " + dynamicDataFile.getAbsolutePath());
+      }
+      
       progress.display((int)((totalImported * 100) / totalFileSize), 
-          tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "...");
+          tableName + ": importing " + Values.formatBytes(dynamicDataFile.length(), 2) + "...");
+      
+      // Create the virtual table
+      HibernateUtil.createVirtualTable(vdb, vTable);
       
       // Get columns in sorted order
-      SortedSet<ColumnInfo> columns =  new TreeSet<ColumnInfo>();
-      columns.addAll(table.getColumns());
+      SortedSet<String> columns =  new TreeSet<String>();
+      for (VirtualColumn col : vTable.getColumns(vdb.getRawDataType()))
+      {
+        columns.add(col.getColumn());
+      }
       
-      // Create COPY (<columns>) FROM '<filename>'
+      // Create COPY <table> (<columns>) FROM '<filename>'
       StringBuilder copy = new StringBuilder("COPY ");
-      copy.append(table.getFullQuotedTableName(engine));
+      if (dynamicCatalog != null)
+      {
+        copy.append(dynamicCatalog).append(".");
+      }
+      if (dynamicSchema != null)
+      {
+        copy.append(dynamicSchema).append(".");
+      }
+      copy.append(engine.getQuotedName(tableName));
       copy.append(" (");
       String separator = "";
       boolean hasIdColumn = false;
-      for (ColumnInfo c : columns)
+      for (String col : columns)
       {
         copy.append(separator);
-        copy.append(engine.getQuotedName(c.getName()));
+        copy.append(engine.getQuotedName(col));
         separator = ",";
-        hasIdColumn |= "id".equals(c.getName());
-      }
-      copy.append(") FROM '").append(importFile.getAbsolutePath() + "'");
+      }
+      copy.append(") FROM '").append(dynamicDataFile.getAbsolutePath() + "'");
   
       // Import the data
       st.executeUpdate(copy.toString());
       
-      // Get max id (if 'id' column exists)
-      if (hasIdColumn)
-      {
-        String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine);
-        Query query = HibernateUtil.createSqlQuery(session, sql);
-        Integer maxId = HibernateUtil.loadData(Integer.class, query);
-        if (maxId != null) // empty tables have no max id
-        {
-          if (sequenceGenerators.containsKey(tableName))
-          {
-            // Set the value for the special sequence generator
-            sql = "SELECT setval('" + sequenceGenerators.get(tableName) + "', " + maxId + ")";
-            st.execute(sql);
-          }
-          else
-          {
-            // Global 'hibernate_sequence'
-            if (maxGlobalId < maxId) maxGlobalId = maxId;
-          }
-        }
-      }
-      
       // Done with this table...
-      totalImported += importFile.length();
-      progress.append("done\n");
-    }
-    
-    // Set the value for the 'hibernate_sequence' generator
-    String sql = "SELECT setval('hibernate_sequence', " + maxGlobalId + ")";
-    st.execute(sql);
-    
-    // Import data for each table in the dynamic database
-    for (File f : dynamicDataFiles)
-    {
-      
+      totalImported += dynamicDataFile.length();
+      progress.append("done");
     }
     
@@ -571 +687 @@
     return new File(directory, table.getTable().getName().toLowerCase()+".data");
   }
+  
+  
 }
 

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

@@ -260 +260 @@
   public String getTableName(VirtualDb vdb)
   {
-    return nameTemplate.replace("#", Integer.toString(vdb.getId()));
+    return nameTemplate.replace("#", Integer.toString(vdb == null ? 0 : vdb.getId()));
   }
   

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

@@ -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);
@@ -96 +93 @@
     return tables;
   }
-
   
   private final Table table;

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

@@ -23 +23 @@
 
 import java.sql.Connection;
+import java.sql.DatabaseMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Set;
 
 import net.sf.basedb.core.ProgressReporter;
 import net.sf.basedb.core.StringUtil;
 import net.sf.basedb.core.dbengine.DbEngine;
+import net.sf.basedb.core.dbengine.TableInfo;
+import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo;
+import net.sf.basedb.core.dbengine.TableInfo.IndexInfo;
 
 import org.hibernate.cfg.Configuration;
 import org.hibernate.dialect.Dialect;
 import org.hibernate.jdbc.Work;
+import org.hibernate.mapping.Table;
 import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
 
@@ -58 +69 @@
   private final Mode mode;
   private final ProgressReporter progress;
+  private boolean createMissingUniqueConstraints;
 
   /**
@@ -92 +104 @@
     try
     {
-      String[] allSql = null;
+      List<String> allSql = new ArrayList<String>();
       if (mode == Mode.UPDATE)
       {
         log.info("Fetching database metadata");
         DatabaseMetadata meta = new DatabaseMetadata(connection, dialect);
-        allSql = cfg.generateSchemaUpdateScript(dialect, meta);
+        allSql.addAll(Arrays.asList(cfg.generateSchemaUpdateScript(dialect, meta)));
       }
       else
       {
-        allSql = cfg.generateSchemaCreationScript(dialect);
-      }
-      int numStatements = allSql.length;
+        allSql.addAll(Arrays.asList(cfg.generateSchemaCreationScript(dialect)));
+      }
+      
+      if (createMissingUniqueConstraints && mode == Mode.UPDATE)
+      {
+        Iterator<Table> tables = (Iterator<Table>)cfg.getTableMappings();
+        DatabaseMetaData metaData = connection.getMetaData();
+        while (tables.hasNext())
+        {
+          Table table = tables.next();
+          TableInfo tiDb = new TableInfo(table, metaData);
+          TableInfo tiHib = new TableInfo(table, dialect);
+          
+          // Single-column unique constraints are not found in the indexes collection
+          for (ColumnInfo ci : tiHib.getColumns())
+          {
+            if (!ci.isUnique()) continue; // with next column
+            
+            // Check if the constraint exists in the db
+            Set<String> columns = Collections.singleton(ci.getName());
+            String dbName = tiDb.findIndexName(null, columns);
+            if (dbName != null) continue; // with the next column
+            
+            // Generate SQL to create the unique constraint
+            String constraintName = table.getName() +"_" + ci.getName();
+            String createSql = dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(),
+                table.getName(), constraintName, columns, ci.isUnique());
+            allSql.add(createSql);
+          }
+          
+          for (IndexInfo ii : tiHib.getIndexes())
+          {
+            if (!ii.isUnique()) continue; // with the next index
+            
+            // Check if the index exists in the db
+            String dbName = tiDb.findIndexName(ii.getName(), ii.getColumns());
+            if (dbName != null) continue; // with the next index
+            
+            // Generate SQL to create the unique constraint
+            String constraintName = table.getName() +"_" + ii.getName();
+            String createSql = dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(),
+                table.getName(), constraintName, ii.getColumns(), ii.isUnique());
+            allSql.add(createSql);
+          }
+        }
+      }
+      
+      int numStatements = allSql.size();
       int numDone = 0;
       int interval = Math.max(1, numStatements / 30);
@@ -158 +215 @@
   }
 
+  /**
+    Set this option to let the schema generator create unique constraints
+    on columns that doesn't have them already. This is only supported
+    in UPDATE mode.
+    @since 3.1
+  */
+  public void setCreateMissingUniqueConstraints(boolean createMissingUniqueConstraints)
+  {
+    this.createMissingUniqueConstraints = createMissingUniqueConstraints;
+  }
+  
   /**
     The installation mode.

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

@@ -146 +146 @@
         if (export)
         {
-          Migration.exportAll(path, new ConsoleProgressReporter(false));
+          ProgressReporter progress = new ConsoleProgressReporter(false);
+          progress.display(0, "Starting export...\n");
+          ChainedProgressReporter chained = new ChainedProgressReporter(progress);
+          chained.setRange(1, 99);
+          Migration.exportAll(path, chained);
+          progress.display(100, "Export completed successfully!\n");
         }
         else
         {
-          ChainedProgressReporter progress = new ChainedProgressReporter(new ConsoleProgressReporter());
+          ProgressReporter progress = new ConsoleProgressReporter();
+          ChainedProgressReporter chained = new ChainedProgressReporter(progress);
+          progress.display(0, "Starting import...");
           // Create empty database without indexes and foreign keys
-          progress.setRange(0, 5);
-          Install.createTables(SchemaGenerator.Mode.MIGRATE, progress, null, null);
-          progress.append("\n");
+          chained.setRange(1, 5);
+          Install.createTables(SchemaGenerator.Mode.MIGRATE, chained, null, null);
 
           // Import all data
-          progress = new ChainedProgressReporter(new ConsoleProgressReporter(false));
-          progress.setRange(5, 90);
-          Migration.importAll(path, progress);
+          chained.setRange(6, 90);
+          Migration.importAll(path, chained);
           
           // Add indexes, foreign keys, etc. to database
+          chained.setRange(91, 99);
+          Migration.createMissingConstraints(chained);
+          progress.display(100, "Import completed successfully!\n");
         }
         Application.stop();