Changeset 5857


Ignore:
Timestamp:
Nov 9, 2011, 12:52:33 PM (10 years ago)
Author:
Nicklas Nordborg
Message:

References #1630: Migrate from MySQL to PostgreSQL

Testing on the data from the demo server found a few issues:

  • The unique index creation at the end of the migration was interfering with the regular update script. It tried to create indexes on tables that did not yet exist. So this has been refactored, and the migration now uses the regular update first, and then a special mode for the unique index creation.


  • File-only platforms (raw data types) were not loaded and resulted in a NullPointerException? if experiments using that platform had tables in the dynamic database. The platform raw data types are now re-initialized after the static database has been populated.


  • Added some boolean flag to the different modes, since it will make it easier to use different checks if we add more modes in the future.
Location:
trunk/src/core/net/sf/basedb/core
Files:
6 edited

Legend:

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

    r5833 r5857  
    551551        Keyring.init();
    552552        HibernateUtil.testTransactions();
    553         RawDataTypes.initPlatforms();
     553        RawDataTypes.initPlatforms(null);
    554554 
    555555        // Initialize extensions system
  • trunk/src/core/net/sf/basedb/core/HibernateUtil.java

    r5854 r5857  
    598598
    599599    int currentSchemaVersion = Install.NEW_SCHEMA_VERSION;
    600     if (mode == SchemaGenerator.Mode.UPDATE)
     600    if (mode.verifySchemaVersion())
    601601    {
    602602      currentSchemaVersion = Application.getSchemaVersion();       
     
    615615      }
    616616    }
    617     else
     617   
     618    if (mode.requireEmptyDatabase())
    618619    {
    619620      if (!isEmptyDatabase())
     
    628629    {
    629630      SchemaGenerator schemaGenerator = new SchemaGenerator(cfg, dialect, dbEngine, mode, progress);
    630       schemaGenerator.setCreateMissingUniqueConstraints(true);
    631631      session = HibernateUtil.newSession();
    632632      tx = HibernateUtil.newTransaction(session);
  • trunk/src/core/net/sf/basedb/core/Migration.java

    r5855 r5857  
    5555import net.sf.basedb.core.hibernate.JdbcWork;
    5656import net.sf.basedb.core.hibernate.SchemaGenerator;
     57import net.sf.basedb.util.ChainedProgressReporter;
    5758import net.sf.basedb.util.FileUtil;
    5859import net.sf.basedb.util.RegexpFileFilter;
     
    159160  }
    160161 
     162  /**
     163    Create unique constraints that are missing after the migration.
     164    @param progress A progress reporter
     165  */
    161166  public static void createMissingConstraints(ProgressReporter progress)
    162167  {
     
    165170      Application.start(false, false, false);
    166171      progress.display(0, "Creating constraints...");
    167       HibernateUtil.createStaticTables(SchemaGenerator.Mode.UPDATE, progress);
     172      ChainedProgressReporter chained = new ChainedProgressReporter(progress);
     173      chained.setRange(5, 50);
     174      HibernateUtil.createStaticTables(SchemaGenerator.Mode.UPDATE, chained);
     175      chained.setRange(55, 100);
     176      HibernateUtil.createStaticTables(SchemaGenerator.Mode.UNIQUE_INDEXES, chained);
    168177    }
    169178    finally
     
    518527    st.execute(sql);
    519528   
     529    // We need information about file-only platforms when importing dynamic data
     530    RawDataTypes.initPlatforms(session);
     531
    520532    // Import data for each table in the dynamic database
    521533    String dynamicCatalog = HibernateUtil.quote(Application.getDynamicCatalog());
  • trunk/src/core/net/sf/basedb/core/RawDataTypes.java

    r5679 r5857  
    7373  private static Set<RawDataType> all = null;
    7474 
    75   static PlatformData generic = null;
    76  
    7775  /**
    7876    The DTD which is used to validate the XML file.
     
    9290    if (isInitialised) return;
    9391    rawDataTypes = new TreeMap<String, RawDataType>();
     92    platformTypes = new HashMap<String, RawDataType>();
    9493    all = new TreeSet<RawDataType>();
    9594    loadRawDataTypesFile();
     
    101100    @since 2.5
    102101  */
    103   static synchronized void initPlatforms()
    104   {
    105     org.hibernate.Session session = null;
     102  static synchronized void initPlatforms(org.hibernate.Session session)
     103  {
    106104    org.hibernate.Transaction tx = null;
    107105   
     
    109107    {
    110108      platformTypes = new HashMap<String, RawDataType>();
    111       session = HibernateUtil.newSession();
    112       tx = HibernateUtil.newTransaction(session);
     109      if (session == null)
     110      {
     111        session = HibernateUtil.newSession();
     112        tx = HibernateUtil.newTransaction(session);
     113      }
    113114     
    114115      org.hibernate.Query query = HibernateUtil.createQuery(session,
     
    126127      }
    127128
    128       query = HibernateUtil.getPredefinedQuery(session,
    129         "GET_PLATFORM_FOR_EXTERNAL_ID");
    130       query.setString("externalId", Platform.GENERIC);
    131       generic = HibernateUtil.loadData(PlatformData.class, query);
    132129    }
    133130    finally
    134131    {
    135       if (tx != null) HibernateUtil.commit(tx);
    136       if (session != null) HibernateUtil.close(session);
     132      if (tx != null)
     133      {
     134        HibernateUtil.commit(tx);
     135        HibernateUtil.close(session);
     136      }
    137137    }
    138138  }
     
    225225    if (id.startsWith("platform."))
    226226    {
    227       return getPlatformRawDataType(id);
     227      return getPlatformRawDataType(null, id);
    228228    }
    229229    else if (id.startsWith("variant."))
    230230    {
    231       return getVariantRawDataType(id);
     231      return getVariantRawDataType(null, id);
    232232    }
    233233    else
     
    254254  }
    255255 
    256   private static RawDataType getPlatformRawDataType(String id)
     256  private static RawDataType getPlatformRawDataType(org.hibernate.Session session, String id)
    257257  {
    258258    RawDataType rdt = platformTypes.get(id);
    259259    if (rdt != null) return rdt;
    260260   
    261     org.hibernate.Session session = null;
    262261    org.hibernate.Transaction tx = null;
    263262    try
    264263    {
    265       session = HibernateUtil.newSession();
    266       tx = HibernateUtil.newTransaction(session);
     264      if (session == null)
     265      {
     266        session = HibernateUtil.newSession();
     267        tx = HibernateUtil.newTransaction(session);
     268      }
    267269      org.hibernate.Query query = HibernateUtil.getPredefinedQuery(session,
    268270        "GET_PLATFORM_FOR_EXTERNAL_ID");
     
    275277    finally
    276278    {
    277       if (tx != null) HibernateUtil.commit(tx);
    278       if (session != null) HibernateUtil.close(session);
     279      if (tx != null)
     280      {
     281        HibernateUtil.commit(tx);
     282        HibernateUtil.close(session);
     283      }
    279284    }
    280285    return rdt;
    281286  }
    282287 
    283   private static RawDataType getVariantRawDataType(String id)
     288  private static RawDataType getVariantRawDataType(org.hibernate.Session session, String id)
    284289  {
    285290    RawDataType rdt = platformTypes.get(id);
    286291    if (rdt != null) return rdt;
    287292   
    288     org.hibernate.Session session = null;
    289293    org.hibernate.Transaction tx = null;
    290294    try
    291295    {
    292       session = HibernateUtil.newSession();
    293       tx = HibernateUtil.newTransaction(session);
     296      if (session == null)
     297      {
     298        session = HibernateUtil.newSession();
     299        tx = HibernateUtil.newTransaction(session);
     300      }
    294301      org.hibernate.Query query = HibernateUtil.getPredefinedQuery(session,
    295302        "GET_PLATFORMVARIANT_FOR_EXTERNAL_ID");
     
    302309    finally
    303310    {
    304       if (tx != null) HibernateUtil.commit(tx);
    305       if (session != null) HibernateUtil.close(session);
     311      if (tx != null)
     312      {
     313        HibernateUtil.commit(tx);
     314        HibernateUtil.close(session);
     315      }
    306316    }
    307317    return rdt;
  • trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java

    r5855 r5857  
    9696  private final Table table;
    9797  private Dialect dialect;
     98  private boolean existsInDb;
    9899 
    99100  private Set<ColumnInfo> columns;
     
    124125  {
    125126    this(table);
     127   
     128    ResultSet tables = metaData.getTables(table.getCatalog(), table.getSchema(), table.getName(), null);
     129    existsInDb = tables.next();
     130    tables.close();
     131   
     132    if (!existsInDb) return;
    126133   
    127134    // Load column information
     
    136143      columns.add(new ColumnInfo(name, type, sqlType, size, isNullable, false));
    137144    }
     145    sqlColumns.close();
    138146   
    139147    // Load primary key information
     
    147155    }
    148156    pkInfo = new PrimaryKeyInfo(pkName, pkColumns);
     157    sqlPK.close();
    149158   
    150159    // Load foreign keys
     
    168177      fk.refColumns.add(refColumn);
    169178    }
     179    sqlFK.close();
    170180   
    171181    // Load indexes
     
    187197      ii.columns.add(column);
    188198    }
     199    sqlIndexes.close();
    189200  }
    190201 
     
    303314  {
    304315    return table;
     316  }
     317 
     318  /**
     319    Check if the table exists in the database or not. This value is only
     320    meaningful if the TableInfo object was created from JDBC metadata
     321    using {@link #TableInfo(Table, DatabaseMetaData)}.
     322    @since 3.1
     323  */
     324  public boolean existsInDb()
     325  {
     326    return existsInDb;
    305327  }
    306328 
  • trunk/src/core/net/sf/basedb/core/hibernate/SchemaGenerator.java

    r5854 r5857  
    6969  private final Mode mode;
    7070  private final ProgressReporter progress;
    71   private boolean createMissingUniqueConstraints;
    7271
    7372  /**
     
    10099    log.info("Dialect: " + dialect);
    101100    log.info("DbEngine: " + dbEngine);
     101    log.info("Mode: " + mode);
    102102   
    103103    Statement stmt = connection.createStatement();
     
    109109        log.info("Fetching database metadata");
    110110        DatabaseMetadata meta = new DatabaseMetadata(connection, dialect);
     111        log.info("Generating schema update script");
    111112        allSql.addAll(Arrays.asList(cfg.generateSchemaUpdateScript(dialect, meta)));
    112113      }
    113       else
    114       {
     114      else if (mode == Mode.INSTALL || mode == Mode.MIGRATE)
     115      {
     116        log.info("Generating schema creation script");
    115117        allSql.addAll(Arrays.asList(cfg.generateSchemaCreationScript(dialect)));
    116118      }
    117      
    118       if (createMissingUniqueConstraints && mode == Mode.UPDATE)
    119       {
     119      else if (mode == Mode.UNIQUE_INDEXES)
     120      {
     121        log.info("Fetching database metadata");
     122        // Get all tables mapped in Hibernate
    120123        Iterator<Table> tables = (Iterator<Table>)cfg.getTableMappings();
    121124        DatabaseMetaData metaData = connection.getMetaData();
     
    123126        {
    124127          Table table = tables.next();
     128
     129          // Load metadata from the database
    125130          TableInfo tiDb = new TableInfo(table, metaData);
     131          if (!tiDb.existsInDb()) continue; // with next table
     132         
     133          log.debug("Checking table: " + table.getName());
     134          // Load metadata from Hibernate
    126135          TableInfo tiHib = new TableInfo(table, dialect);
    127136         
    128137          // Single-column unique constraints are not found in the indexes collection
     138          // so we check each column first
    129139          for (ColumnInfo ci : tiHib.getColumns())
    130140          {
     
    143153          }
    144154         
     155          // Multi-column indexes are found in the indexes collection
    145156          for (IndexInfo ii : tiHib.getIndexes())
    146157          {
     
    158169          }
    159170        }
     171      }
     172      else
     173      {
     174        throw new UnsupportedOperationException(mode.name());
    160175      }
    161176     
     
    216231
    217232  /**
    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  
    228   /**
    229233    The installation mode.
    230234    @since 3.1
     
    235239      Creating a fresh installation.
    236240    */
    237     INSTALL,
     241    INSTALL(true, false),
    238242    /**
    239243      Updating an existing installation.
    240244    */
    241     UPDATE,
     245    UPDATE(false, true),
    242246    /**
    243247      Creating a minimal database schema for migration. The
     
    245249      other constraints.
    246250    */
    247     MIGRATE
     251    MIGRATE(true, false),
     252    /**
     253      Create missing unique indexes. This mode is used as the final
     254      step after migration since the unique indexes are not created
     255      by UPDATE.
     256    */
     257    UNIQUE_INDEXES(false, true);
     258   
     259    private final boolean requireEmptyDatabase;
     260    private final boolean verifySchemaVersion;
     261    private Mode(boolean requireEmptyDatabase, boolean verifySchemaVersion)
     262    {
     263      this.requireEmptyDatabase = requireEmptyDatabase;
     264      this.verifySchemaVersion = verifySchemaVersion;
     265    }
     266   
     267    /**
     268      Does this installation mode require an empty database or not?
     269    */
     270    public boolean requireEmptyDatabase()
     271    {
     272      return requireEmptyDatabase;
     273    }
     274   
     275    /**
     276      Does this installation mode require a check against the schema version
     277      of the intalled database or not?
     278    */
     279    public boolean verifySchemaVersion()
     280    {
     281      return verifySchemaVersion;
     282    }
    248283  }
    249284 
Note: See TracChangeset for help on using the changeset viewer.