Changeset 5854


Ignore:
Timestamp:
Nov 8, 2011, 1:43:27 PM (10 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:
1 added
6 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/net/sf/basedb/core/HibernateUtil.java

    r5853 r5854  
    628628    {
    629629      SchemaGenerator schemaGenerator = new SchemaGenerator(cfg, dialect, dbEngine, mode, progress);
     630      schemaGenerator.setCreateMissingUniqueConstraints(true);
    630631      session = HibernateUtil.newSession();
    631632      tx = HibernateUtil.newTransaction(session);
  • trunk/src/core/net/sf/basedb/core/Migration.java

    r5853 r5854  
    4343import java.util.SortedSet;
    4444import java.util.TreeSet;
    45 
     45import java.util.regex.Matcher;
     46import java.util.regex.Pattern;
     47
     48import net.sf.basedb.core.data.VirtualDbData;
    4649import net.sf.basedb.core.dbengine.DbEngine;
     50import net.sf.basedb.core.dbengine.PostgresDbEngine;
    4751import net.sf.basedb.core.dbengine.TableInfo;
    4852import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo;
    4953import net.sf.basedb.core.hibernate.JdbcWork;
     54import net.sf.basedb.core.hibernate.SchemaGenerator;
    5055import net.sf.basedb.util.FileUtil;
    5156import net.sf.basedb.util.RegexpFileFilter;
     
    5964
    6065/**
    61   Class for migrating a database from MySQL to
    62   PostgreSQL. The {@link #exportAll(String, ProgressReporter)} method
    63   only works on a MySQL database and the {@link #importAll(String, ProgressReporter)}
     66  Class for migrating a database to PostgreSQL.
     67  The {@link #exportAll(String, ProgressReporter)} method should work on
     68  both MySQL and PostgreSQL (but is only tested regularly with MySQL).
     69  The export generates data that is suitable for importing into
     70  PostgreSQL using the COPY command. The {@link #importAll(String, ProgressReporter)}
    6471  only works on a PostgreSQL database.
    6572
     
    109116      if (tx != null) HibernateUtil.rollback(tx);
    110117      if (session != null) HibernateUtil.close(session);
     118      Application.stop();
    111119    }
    112120  }
     
    145153      if (tx != null) HibernateUtil.rollback(tx);
    146154      if (session != null) HibernateUtil.close(session);
     155      Application.stop();
     156    }
     157  }
     158 
     159  public static void createMissingConstraints(ProgressReporter progress)
     160  {
     161    try
     162    {
     163      Application.start(false, false, false);
     164      progress.display(0, "Creating constraints...");
     165      HibernateUtil.createStaticTables(SchemaGenerator.Mode.UPDATE, progress);
     166    }
     167    finally
     168    {
     169      Application.stop();
    147170    }
    148171  }
     
    196219  // -------------------
    197220 
    198  
     221  /**
     222    Perform the export. In principle we support all databases that BASE supports,
     223    but it may depend on the actual underlying SQL column types. The export
     224    generates files that can be imported into PostgreSQL only.
     225  */
    199226  private void doExport(Connection connection)
    200227    throws SQLException, IOException
     
    203230    DatabaseMetaData metaData = connection.getMetaData();
    204231    DbEngine engine = HibernateUtil.getDbEngine();
     232   
     233    String staticCatalog = null;
     234    String staticSchema = null;
     235    String dynamicCatalog = Application.getDynamicCatalog();
     236    String dynamicSchema = Application.getDynamicSchema();
     237   
     238    if (engine instanceof PostgresDbEngine)
     239    {
     240      staticSchema = "public";
     241    }
    205242
    206243    // Load information about all tables in the static and dynamic databases
    207     progress.display(0, "Loading tables...\n");
     244    progress.display(0, "Counting tables...\n");
    208245    final int TICK_INTERVAL = 1000;
    209246    long nextTick = System.currentTimeMillis() + TICK_INTERVAL;
    210     List<Table> staticTables = TableInfo.listTables(metaData, null, null);
     247    List<Table> staticTables = TableInfo.listTables(metaData, staticCatalog, staticSchema);
    211248    progress.display(0, staticTables.size() + " tables in static database\n");
    212249
    213     List<Table> dynamicTables = TableInfo.listTables(metaData,
    214         Application.getDynamicCatalog(), Application.getDynamicSchema());
    215     progress.display(0, dynamicTables.size() + " tables in static database");
     250    List<Table> dynamicTables = TableInfo.listTables(metaData, dynamicCatalog, dynamicSchema);
     251    progress.display(0, dynamicTables.size() + " tables in dynamic database\n");
     252    progress.display(0, "Counting rows...");
    216253   
    217254    // Get metadata (eg. column names+types)
     
    244281    }
    245282
    246     progress.append("\n");
    247     progress.display(0, "Found " + tables.size() + " tables with " +
    248         Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n"
    249         );
     283    progress.append(" found " + Values.formatNumber(totalRows / 1000000f, 1) + " million data rows\n");
    250284   
    251285   
     
    315349      writer.flush();
    316350      writer.close();
    317 
    318     }
    319   }
    320  
     351    }
     352  }
     353 
     354  /**
     355    Perform the import. Importing is supported on PostgreSQL only.
     356  */
    321357  private void doImport(Connection connection)
    322358    throws SQLException, IOException
     
    325361    DbEngine engine = HibernateUtil.getDbEngine();
    326362
     363    if (!(engine instanceof PostgresDbEngine))
     364    {
     365      throw new UnsupportedOperationException("Importing is supported for PostgreSQL only");
     366    }
     367   
    327368    // Load information about all tables in the static and dynamic databases
    328     progress.display(0, "Loading tables...\n");
     369    progress.display(0, "Loading tables...");
    329370    final int TICK_INTERVAL = 1000;
    330371    long nextTick = System.currentTimeMillis() + TICK_INTERVAL;
     
    355396    // And then the dynamic tables
    356397    // Since they don't exists in the database yet, we need to scan the files
     398    Pattern dynamicFilePattern = Pattern.compile("d(\\d+)(.+)\\.data");
    357399    List<File> dynamicDataFiles = FileUtil.findFiles(directory,
    358         new RegexpFileFilter("d\\d+.*", null));
     400        new RegexpFileFilter(dynamicFilePattern, null));
    359401    progress.display(0, dynamicDataFiles.size() + " files with dynamic data");
    360    
    361402    for (File f : dynamicDataFiles)
    362403    {
     
    364405    }
    365406   
    366    
    367     progress.append("\n");
    368407    progress.display(0, "Found " + (tables.size()+dynamicDataFiles.size()) + " files with " +
    369408        Values.formatBytes(totalFileSize, 2) + " data\n"
     
    382421      }
    383422    }
     423    // Keep track of the max id used in all tables (so that we can set the 'hibernate_sequence')
    384424    int maxGlobalId = 0;
    385425   
     
    390430    {
    391431      File importFile = getDataFile(table);
    392       String tableName = table.getTable().getName();
     432      if (importFile.length() > 0)
     433      {
     434        String tableName = table.getTable().getName();
     435        progress.display((int)((totalImported * 100) / totalFileSize),
     436            tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "...");
     437       
     438        // Get columns in sorted order
     439        SortedSet<ColumnInfo> columns =  new TreeSet<ColumnInfo>();
     440        columns.addAll(table.getColumns());
     441       
     442        // Create COPY (<columns>) FROM '<filename>'
     443        StringBuilder copy = new StringBuilder("COPY ");
     444        copy.append(table.getFullQuotedTableName(engine));
     445        copy.append(" (");
     446        String separator = "";
     447        boolean hasIdColumn = false;
     448        for (ColumnInfo c : columns)
     449        {
     450          copy.append(separator);
     451          copy.append(engine.getQuotedName(c.getName()));
     452          separator = ",";
     453          hasIdColumn |= "id".equals(c.getName());
     454        }
     455        copy.append(") FROM '").append(importFile.getAbsolutePath() + "'");
     456   
     457        // Import the data
     458        st.executeUpdate(copy.toString());
     459       
     460        // Get max id (if 'id' column exists)
     461        if (hasIdColumn)
     462        {
     463          String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine);
     464          Query query = HibernateUtil.createSqlQuery(session, sql);
     465          Integer maxId = HibernateUtil.loadData(Integer.class, query);
     466          if (maxId != null) // empty tables have no max id
     467          {
     468            if (sequenceGenerators.containsKey(tableName))
     469            {
     470              // Set the value for the special sequence generator
     471              sql = "SELECT setval('" + sequenceGenerators.get(tableName) + "', " + maxId + ")";
     472              st.execute(sql);
     473            }
     474            else
     475            {
     476              // Global 'hibernate_sequence'
     477              if (maxGlobalId < maxId) maxGlobalId = maxId;
     478            }
     479          }
     480        }
     481       
     482        // Done with this table...
     483        totalImported += importFile.length();
     484        progress.append("done");
     485      }
     486    }
     487   
     488    // Set the value for the 'hibernate_sequence' generator
     489    String sql = "SELECT setval('hibernate_sequence', " + maxGlobalId + ")";
     490    st.execute(sql);
     491   
     492    // Import data for each table in the dynamic database
     493    String dynamicCatalog = HibernateUtil.quote(Application.getDynamicCatalog());
     494    String dynamicSchema = HibernateUtil.quote(Application.getDynamicSchema());
     495    for (File dynamicDataFile : dynamicDataFiles)
     496    {
     497      Matcher m = dynamicFilePattern.matcher(dynamicDataFile.getName());
     498      if (!m.matches())
     499      {
     500        // Should never happen since we use the same filter to match files at the beginning
     501        throw new AssertionError("File is not a dynamic data file: " + dynamicDataFile.getAbsolutePath());
     502      }
     503      int virtualDbId = Values.getInt(m.group(1));
     504      String tableType = m.group(2).toLowerCase();
     505     
     506      // Load the VirtualDb object that is managing the dynamic data table
     507      VirtualDbData vdbData = HibernateUtil.loadData(session, VirtualDbData.class, virtualDbId);
     508      VirtualDb vdb = new VirtualDb(vdbData);
     509
     510      // Find the VirtualTable for this file
     511      VirtualTable vTable = null;
     512      String tableName = null;
     513      for (VirtualTable vt : VirtualTable.values())
     514      {
     515        tableName = vt.getTableName(vdb);
     516        if (tableName.toLowerCase().endsWith(tableType))
     517        {
     518          vTable = vt;
     519          break;
     520        }
     521      }
     522
     523      if (vTable == null)
     524      {
     525        throw new ItemNotFoundException("No virtual table found for file: " + dynamicDataFile.getAbsolutePath());
     526      }
     527     
    393528      progress.display((int)((totalImported * 100) / totalFileSize),
    394           tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "...");
     529          tableName + ": importing " + Values.formatBytes(dynamicDataFile.length(), 2) + "...");
     530     
     531      // Create the virtual table
     532      HibernateUtil.createVirtualTable(vdb, vTable);
    395533     
    396534      // Get columns in sorted order
    397       SortedSet<ColumnInfo> columns =  new TreeSet<ColumnInfo>();
    398       columns.addAll(table.getColumns());
     535      SortedSet<String> columns =  new TreeSet<String>();
     536      for (VirtualColumn col : vTable.getColumns(vdb.getRawDataType()))
     537      {
     538        columns.add(col.getColumn());
     539      }
    399540     
    400       // Create COPY (<columns>) FROM '<filename>'
     541      // Create COPY <table> (<columns>) FROM '<filename>'
    401542      StringBuilder copy = new StringBuilder("COPY ");
    402       copy.append(table.getFullQuotedTableName(engine));
     543      if (dynamicCatalog != null)
     544      {
     545        copy.append(dynamicCatalog).append(".");
     546      }
     547      if (dynamicSchema != null)
     548      {
     549        copy.append(dynamicSchema).append(".");
     550      }
     551      copy.append(engine.getQuotedName(tableName));
    403552      copy.append(" (");
    404553      String separator = "";
    405554      boolean hasIdColumn = false;
    406       for (ColumnInfo c : columns)
     555      for (String col : columns)
    407556      {
    408557        copy.append(separator);
    409         copy.append(engine.getQuotedName(c.getName()));
     558        copy.append(engine.getQuotedName(col));
    410559        separator = ",";
    411         hasIdColumn |= "id".equals(c.getName());
    412       }
    413       copy.append(") FROM '").append(importFile.getAbsolutePath() + "'");
     560      }
     561      copy.append(") FROM '").append(dynamicDataFile.getAbsolutePath() + "'");
    414562 
    415563      // Import the data
    416564      st.executeUpdate(copy.toString());
    417565     
    418       // Get max id (if 'id' column exists)
    419       if (hasIdColumn)
    420       {
    421         String sql = "SELECT max(id) FROM " + table.getFullQuotedTableName(engine);
    422         Query query = HibernateUtil.createSqlQuery(session, sql);
    423         Integer maxId = HibernateUtil.loadData(Integer.class, query);
    424         if (maxId != null) // empty tables have no max id
    425         {
    426           if (sequenceGenerators.containsKey(tableName))
    427           {
    428             // Set the value for the special sequence generator
    429             sql = "SELECT setval('" + sequenceGenerators.get(tableName) + "', " + maxId + ")";
    430             st.execute(sql);
    431           }
    432           else
    433           {
    434             // Global 'hibernate_sequence'
    435             if (maxGlobalId < maxId) maxGlobalId = maxId;
    436           }
    437         }
    438       }
    439      
    440566      // Done with this table...
    441       totalImported += importFile.length();
    442       progress.append("done\n");
    443     }
    444    
    445     // Set the value for the 'hibernate_sequence' generator
    446     String sql = "SELECT setval('hibernate_sequence', " + maxGlobalId + ")";
    447     st.execute(sql);
    448    
    449     // Import data for each table in the dynamic database
    450     for (File f : dynamicDataFiles)
    451     {
    452      
     567      totalImported += dynamicDataFile.length();
     568      progress.append("done");
    453569    }
    454570   
     
    571687    return new File(directory, table.getTable().getName().toLowerCase()+".data");
    572688  }
     689 
     690 
    573691}
    574692
  • trunk/src/core/net/sf/basedb/core/VirtualTable.java

    r5384 r5854  
    260260  public String getTableName(VirtualDb vdb)
    261261  {
    262     return nameTemplate.replace("#", Integer.toString(vdb.getId()));
     262    return nameTemplate.replace("#", Integer.toString(vdb == null ? 0 : vdb.getId()));
    263263  }
    264264 
  • trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java

    r5853 r5854  
    8585      String tableCatalog = result.getString("TABLE_CAT");
    8686      String tableSchema = result.getString("TABLE_SCHEM");
    87      
    88       //System.out.println(tableName + ":" + result.getString("TABLE_TYPE"));
    89      
    9087      Table table = new Table();
    9188      table.setName(tableName);
     
    9693    return tables;
    9794  }
    98 
    9995 
    10096  private final Table table;
  • trunk/src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java

    r5853 r5854  
    2323
    2424import java.sql.Connection;
     25import java.sql.DatabaseMetaData;
    2526import java.sql.SQLException;
    2627import java.sql.Statement;
     28import java.util.ArrayList;
     29import java.util.Arrays;
     30import java.util.Collections;
     31import java.util.Iterator;
     32import java.util.List;
     33import java.util.Set;
    2734
    2835import net.sf.basedb.core.ProgressReporter;
    2936import net.sf.basedb.core.StringUtil;
    3037import net.sf.basedb.core.dbengine.DbEngine;
     38import net.sf.basedb.core.dbengine.TableInfo;
     39import net.sf.basedb.core.dbengine.TableInfo.ColumnInfo;
     40import net.sf.basedb.core.dbengine.TableInfo.IndexInfo;
    3141
    3242import org.hibernate.cfg.Configuration;
    3343import org.hibernate.dialect.Dialect;
    3444import org.hibernate.jdbc.Work;
     45import org.hibernate.mapping.Table;
    3546import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
    3647
     
    5869  private final Mode mode;
    5970  private final ProgressReporter progress;
     71  private boolean createMissingUniqueConstraints;
    6072
    6173  /**
     
    92104    try
    93105    {
    94       String[] allSql = null;
     106      List<String> allSql = new ArrayList<String>();
    95107      if (mode == Mode.UPDATE)
    96108      {
    97109        log.info("Fetching database metadata");
    98110        DatabaseMetadata meta = new DatabaseMetadata(connection, dialect);
    99         allSql = cfg.generateSchemaUpdateScript(dialect, meta);
     111        allSql.addAll(Arrays.asList(cfg.generateSchemaUpdateScript(dialect, meta)));
    100112      }
    101113      else
    102114      {
    103         allSql = cfg.generateSchemaCreationScript(dialect);
    104       }
    105       int numStatements = allSql.length;
     115        allSql.addAll(Arrays.asList(cfg.generateSchemaCreationScript(dialect)));
     116      }
     117     
     118      if (createMissingUniqueConstraints && mode == Mode.UPDATE)
     119      {
     120        Iterator<Table> tables = (Iterator<Table>)cfg.getTableMappings();
     121        DatabaseMetaData metaData = connection.getMetaData();
     122        while (tables.hasNext())
     123        {
     124          Table table = tables.next();
     125          TableInfo tiDb = new TableInfo(table, metaData);
     126          TableInfo tiHib = new TableInfo(table, dialect);
     127         
     128          // Single-column unique constraints are not found in the indexes collection
     129          for (ColumnInfo ci : tiHib.getColumns())
     130          {
     131            if (!ci.isUnique()) continue; // with next column
     132           
     133            // Check if the constraint exists in the db
     134            Set<String> columns = Collections.singleton(ci.getName());
     135            String dbName = tiDb.findIndexName(null, columns);
     136            if (dbName != null) continue; // with the next column
     137           
     138            // Generate SQL to create the unique constraint
     139            String constraintName = table.getName() +"_" + ci.getName();
     140            String createSql = dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(),
     141                table.getName(), constraintName, columns, ci.isUnique());
     142            allSql.add(createSql);
     143          }
     144         
     145          for (IndexInfo ii : tiHib.getIndexes())
     146          {
     147            if (!ii.isUnique()) continue; // with the next index
     148           
     149            // Check if the index exists in the db
     150            String dbName = tiDb.findIndexName(ii.getName(), ii.getColumns());
     151            if (dbName != null) continue; // with the next index
     152           
     153            // Generate SQL to create the unique constraint
     154            String constraintName = table.getName() +"_" + ii.getName();
     155            String createSql = dbEngine.getCreateIndexSql(table.getCatalog(), table.getSchema(),
     156                table.getName(), constraintName, ii.getColumns(), ii.isUnique());
     157            allSql.add(createSql);
     158          }
     159        }
     160      }
     161     
     162      int numStatements = allSql.size();
    106163      int numDone = 0;
    107164      int interval = Math.max(1, numStatements / 30);
     
    158215  }
    159216
     217  /**
     218    Set this option to let the schema generator create unique constraints
     219    on columns that doesn't have them already. This is only supported
     220    in UPDATE mode.
     221    @since 3.1
     222  */
     223  public void setCreateMissingUniqueConstraints(boolean createMissingUniqueConstraints)
     224  {
     225    this.createMissingUniqueConstraints = createMissingUniqueConstraints;
     226  }
     227 
    160228  /**
    161229    The installation mode.
  • trunk/src/install/net/sf/basedb/install/InitDB.java

    r5853 r5854  
    146146        if (export)
    147147        {
    148           Migration.exportAll(path, new ConsoleProgressReporter(false));
     148          ProgressReporter progress = new ConsoleProgressReporter(false);
     149          progress.display(0, "Starting export...\n");
     150          ChainedProgressReporter chained = new ChainedProgressReporter(progress);
     151          chained.setRange(1, 99);
     152          Migration.exportAll(path, chained);
     153          progress.display(100, "Export completed successfully!\n");
    149154        }
    150155        else
    151156        {
    152           ChainedProgressReporter progress = new ChainedProgressReporter(new ConsoleProgressReporter());
     157          ProgressReporter progress = new ConsoleProgressReporter();
     158          ChainedProgressReporter chained = new ChainedProgressReporter(progress);
     159          progress.display(0, "Starting import...");
    153160          // Create empty database without indexes and foreign keys
    154           progress.setRange(0, 5);
    155           Install.createTables(SchemaGenerator.Mode.MIGRATE, progress, null, null);
    156           progress.append("\n");
     161          chained.setRange(1, 5);
     162          Install.createTables(SchemaGenerator.Mode.MIGRATE, chained, null, null);
    157163
    158164          // Import all data
    159           progress = new ChainedProgressReporter(new ConsoleProgressReporter(false));
    160           progress.setRange(5, 90);
    161           Migration.importAll(path, progress);
     165          chained.setRange(6, 90);
     166          Migration.importAll(path, chained);
    162167         
    163168          // Add indexes, foreign keys, etc. to database
     169          chained.setRange(91, 99);
     170          Migration.createMissingConstraints(chained);
     171          progress.display(100, "Import completed successfully!\n");
    164172        }
    165173        Application.stop();
Note: See TracChangeset for help on using the changeset viewer.