Changeset 5703

Timestamp:

Aug 19, 2011, 3:41:57 PM (12 years ago)

Author:

Nicklas Nordborg

Message:

References #1591: Upgrade script for BASE 2.17 --> 3.0

Update biomaterials, hybridizations and scans. Still missing some cases for re-linking pooled biomaterials.

Dropping lots of a database columns and tables that are not needed in BASE 3.

Location:

trunk/src

Files:

• core/common-queries.xml (modified) (1 diff)
• core/net/sf/basedb/core/Item.java (modified) (4 diffs)
• core/net/sf/basedb/core/Update.java (modified) (11 diffs)
• install/net/sf/basedb/install/InitDB.java (modified) (2 diffs)
• test/TestInstall.java (deleted)
• test/TestUpdate.java (deleted)

trunk/src/core/common-queries.xml

@@ -2869 +2869 @@
   </query>
 
+  <query id="DROP_FOREIGNKEY" type="SQL">
+    <sql>
+      ALTER TABLE [{1}] DROP FOREIGN KEY [{2}]
+    </sql>
+    <description>
+      An SQL query that drops a foreign key from a table.
+    </description>
+  </query>
+
+  <query id="DROP_TABLE" type="SQL">
+    <sql>
+      DROP TABLE [{1}]
+    </sql>
+    <description>
+      An SQL query that drops a table.
+    </description>
+  </query>
+
+  <query id="CONVERT_LABELEDEXTRACTS_TO_EXTRACTS" type="SQL">
+    <sql>
+      UPDATE [BioMaterials]
+      SET [discriminator] = 3
+      WHERE [discriminator] = 4
+    </sql>
+    <description>
+      An SQL query that convert labeled extract to extract.
+    </description>
+  </query>
+
+  <query id="GET_HYBRIDIZATIONS" type="SQL">
+    <sql>
+      SELECT 
+        [id], [version], 
+        [arrayslide_id], [num_arrays], 
+        [annotationset_id], [name], 
+        [description], [removed], 
+        [itemkey_id], [projectkey_id], 
+        [owner]
+      FROM 
+        [Hybridizations]
+    </sql>
+    <description>
+      An SQL query that load all BASE 2.17 hybridizations.
+    </description>
+  </query>
+
+  <query id="UPDATE_BIOASSAYID_FROM_HYBID" type="SQL">
+    <sql>
+      UPDATE [BioMaterialEvents]
+      SET [physicalbioassay_id] = :bioAssayId
+      WHERE [hybridization_id] = :hybId
+    </sql>
+    <description>
+      An SQL query that sets the physicalbioassay_id
+      for a given hybridization_id
+    </description>
+  </query>
+
+  <query id="GET_SCANS" type="SQL">
+    <sql>
+      SELECT 
+        [id], [version], 
+        [entry_date], [hybridization_id], 
+        [hardware_id], [protocol_id], 
+        [annotationset_id], [name], 
+        [description], [removed], 
+        [itemkey_id], [projectkey_id], 
+        [owner]
+      FROM 
+        [Scans]
+    </sql>
+    <description>
+      An SQL query that load all BASE 2.17 scans.
+    </description>
+  </query>
+
+  <query id="COPY_BIOMATERIALEVENTSOURCES" type="SQL">
+    <sql>
+      INSERT INTO [BioMaterialEventSources2]
+      (
+        [version], 
+        [biomaterial_id], [event_id],
+        [used_quantity], [position]
+      )
+      SELECT 
+        0, 
+        [biomaterial_id], [event_id], 
+        [used_quantity], [dummy]
+      FROM [BioMaterialEventSources]
+    </sql>
+    <description>
+      An SQL query that load all BASE 2.17 biomaterial
+      event sources.
+    </description>
+  </query>
+
+  <query id="SET_PARENTTYPE_ON_BIOMATERIALS_WITH_ONE_PARENT" type="SQL">
+    <sql>
+      UPDATE [BioMaterials] 
+      SET [parent_type]=
+        CASE [discriminator]
+        WHEN 2 THEN 201
+        WHEN 3 THEN 202
+        WHEN 4 THEN 203
+        END
+      WHERE [parent_id] IS NOT NULL
+    </sql>
+    <description>
+      An SQL query that sets the subtype of labeled extracts
+    </description>
+  </query>
+
+  <query id="SET_LABELEDEXTRACT_SUBTYPE" type="SQL">
+    <sql>
+      UPDATE [BioMaterials] 
+      SET [subtype_id]=:subtype 
+      WHERE [label_id] IS NOT NULL
+    </sql>
+    <description>
+      An SQL query that sets the subtype of labeled extracts
+    </description>
+  </query>
+
+  <query id="SET_SUBTYPE_ON_ALL" type="SQL">
+    <sql>
+      UPDATE [{1}] 
+      SET [subtype_id]=:subtype
+    </sql>
+    <description>
+      An SQL query that sets the subtype 
+      of all items in a table
+    </description>
+  </query>
+
+  <query id="UPDATE_ANNOTATIONSET_ITEM" type="SQL">
+    <sql>
+      UPDATE [AnnotationSets]
+      SET [item_id] = :newId, [item_type] = {2}
+      WHERE [item_id] = :oldId AND [item_type] = {1}
+    </sql>
+    <description>
+      An SQL query that update the item_type and item_id
+      on annotation set.
+    </description>
+  </query>
+
+  <query id="UPDATE_ANYTOANY_FROMITEM" type="SQL">
+    <sql>
+      UPDATE [AnyToAny]
+      SET [from_id] = :newId, [from_type] = {2}
+      WHERE [from_id] = :oldId AND [from_type] = {1}
+    </sql>
+    <description>
+      An SQL query that update the from_type and from_id
+      on any-to-any.
+    </description>
+  </query>
+  
+  <query id="UPDATE_ANYTOANY_TOITEM" type="SQL">
+    <sql>
+      UPDATE [AnyToAny]
+      SET [to_id] = :newId, [to_type] = {2}
+      WHERE [to_id] = :oldId AND [to_type] = {1}
+    </sql>
+    <description>
+      An SQL query that update the to_type and to_id
+      on any-to-any.
+    </description>
+  </query>
+  
+  <query id="UPDATE_CHANGEHISTORYDETAILS_ITEM" type="SQL">
+    <sql>
+      UPDATE [ChangeHistoryDetails]
+      SET [item_id] = :newId, [item_type] = {2}
+      WHERE [item_id] = :oldId AND [item_type] = {1}
+    </sql>
+    <description>
+      An SQL query that update the item_type and item_id
+      on change history details.
+    </description>
+  </query>
+  
+  <query id="UPDATE_ITEMVALUES_ITEM" type="SQL">
+    <sql>
+      UPDATE [ItemValues]
+      SET [data_class_id] = :newId, [data_class] = :newClass
+      WHERE [data_class_id] = :oldId AND [data_class] = :oldClass
+    </sql>
+    <description>
+      An SQL query that update the data_class_id and data_class
+      on item values.
+    </description>
+  </query>
+
+  <query id="UPDATE_ITEMVALUES_CLASS" type="SQL">
+    <sql>
+      UPDATE [ItemValues]
+      SET [data_class] = :newClass
+      WHERE [data_class] = :oldClass
+    </sql>
+    <description>
+      An SQL query that update the data_class
+      on item values.
+    </description>
+  </query>
+
+  <query id="UPDATE_ITEMTYPE" type="SQL">
+    <sql>
+      UPDATE [{1}]
+      SET [{2}] = {4}
+      WHERE [{2}] = {3}
+    </sql>
+    <description>
+      An SQL query that update the item-type column on
+      a generic table.
+    </description>
+  </query>
+
+  <query id="DELETE_PLUGINGUICONTEXTS_ITEMTYPE" type="SQL">
+    <sql>
+      DELETE FROM PluginDefinitionGuiContexts 
+      WHERE item_type={1}
+    </sql>
+    <description>
+      An SQL query that delete plug-in gui contexts for
+      a given item type.
+    </description>
+  </query>
+
   <query id="GET_SUBTYPABLE_ITEMS_FOR_SUBTYPE_OF_CLASS" type="HQL">
     <sql>

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

@@ -294 +294 @@
     410),
 
+  /*
+    IMPORTANT! Do not use the following codes since they were used in BASE 2:
+    204 = LABELEDEXTRACT
+  */
   /**
     The item is a {@link BioMaterialEvent}.
@@ -302 +306 @@
     The item is a {@link Tag}.
   */
-  TAG(206, "Tag", "lbl", Tag.class, TagData.class, DefinedPermissions.shareable,
+  TAG(206, "Tag", "tag", Tag.class, TagData.class, DefinedPermissions.shareable,
     440),
 
@@ -420 +424 @@
     550),
 
-  
+  /*
+    IMPORTANT! Do not use the following codes since they were used in BASE 2:
+    261 = HYBRIDIZATION
+    262 = SCAN
+    263 = IMAGE
+  */
   /**
     The item is a {@link PhysicalBioAssay}.
   */
-  PHYSICALBIOASSAY(261, "Physical bioassay", "pba", PhysicalBioAssay.class, PhysicalBioAssayData.class, DefinedPermissions.shareable,
+  PHYSICALBIOASSAY(267, "Physical bioassay", "pba", PhysicalBioAssay.class, PhysicalBioAssayData.class, DefinedPermissions.shareable,
     350),
 
@@ -430 +439 @@
     The item is a {@link DerivedBioAssay}.
   */
-  DERIVEDBIOASSAY(267, "Derived bioassay", "dba", DerivedBioAssay.class, DerivedBioAssayData.class, DefinedPermissions.shareable,
+  DERIVEDBIOASSAY(268, "Derived bioassay", "dba", DerivedBioAssay.class, DerivedBioAssayData.class, DefinedPermissions.shareable,
     330),
     

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

@@ -23 +23 @@
 package net.sf.basedb.core;
 
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
 
-
+import org.hibernate.mapping.Table;
+
+
+import net.sf.basedb.core.data.AnnotationSetData;
+import net.sf.basedb.core.data.ArraySlideData;
 import net.sf.basedb.core.data.ContextData;
+import net.sf.basedb.core.data.DerivedBioAssayData;
+import net.sf.basedb.core.data.HardwareData;
+import net.sf.basedb.core.data.ItemKeyData;
+import net.sf.basedb.core.data.PhysicalBioAssayData;
+import net.sf.basedb.core.data.ProjectKeyData;
 import net.sf.basedb.core.data.PropertyFilterData;
+import net.sf.basedb.core.data.ProtocolData;
 import net.sf.basedb.core.data.SchemaVersionData;
+import net.sf.basedb.core.data.UserData;
+import net.sf.basedb.core.dbengine.TableInfo;
+import net.sf.basedb.core.dbengine.TableInfo.ForeignKeyInfo;
+import net.sf.basedb.core.hibernate.JdbcWork;
 import net.sf.basedb.util.Values;
 
@@ -103 +123 @@
       session = HibernateUtil.newSession();
       int schemaVersion = getSchemaVersion(session);
-        
+      
+      if (schemaVersion == 99)
+      {
+        // Final update to BASE 3
+        if (progress != null) progress.display((int)(1*progress_factor), "--Updating schema version: " + schemaVersion + " -> 100...");
+        schemaVersion = updateToSchemaVersion100(session);
+
+      }
+      
       sc.logout();
       if (progress != null) progress.display(100, "Database updated successfully.");
@@ -189 +217 @@
     update is executed again.
     
-      @param update FALSE if it is an installation. TRUE if it is an update.
       @param progress An object implementing the {@link ProgressReporter}
       interface
@@ -196 +223 @@
       @throws BaseException
   */
-  public static synchronized void adjustExistingItems(boolean update, ProgressReporter progress, String rootLogin, String rootPassword)
+  public static synchronized void adjustExistingItems(ProgressReporter progress, String rootLogin, String rootPassword)
     throws BaseException
   {
-    if (update)
-    {
-      org.hibernate.Transaction tx = null;
-      org.hibernate.Session session = null;
-      try
-      {
-        Config.setProperty("extensions.disabled", "true");
-        Application.start(false, false, false);
-
-        // Test root user account
-        SessionControl sc = Application.newSessionControl(null, null, null);
-        sc.login(rootLogin, rootPassword, null, false);
-        if (sc.getLoggedInUserId() != SystemItems.getId(User.ROOT))
-        {
-          throw new PermissionDeniedException("User '" + rootLogin + "' is not the root account.");
-        }
-        
-        session = HibernateUtil.newSession();
-        int schemaVersion = getSchemaVersion(session);
-        tx = HibernateUtil.newTransaction(session);
-        
-        //  Commit the changes
-        HibernateUtil.commit(tx);
-        log.info("adjustExistingItems: OK");          
-      }
-      catch (BaseException ex)
-      {
-        if (tx != null) HibernateUtil.rollback(tx);
-        if (progress != null) progress.display(100, "The adjustment of the existing items failed: " + ex.getMessage()+"\n");
-        log.info("adjustExistingItems: FAILED");
-        throw ex;
-      }
-      finally
-      {
-        if (session != null) HibernateUtil.close(session);
-        Application.stop();
-      }
-    }
-  }
-  
-  public static synchronized void updateToBase3(ProgressReporter progress, String rootLogin, String rootPassword)
-    throws BaseException
-  {
-
+    org.hibernate.Transaction tx = null;
     org.hibernate.Session session = null;
-    org.hibernate.Query query = null;
-    try
-    {
-      progress.display(0, "Updating to BASE 3.0...");
+    try
+    {
       Config.setProperty("extensions.disabled", "true");
       Application.start(false, false, false);
@@ -261 +243 @@
       session = HibernateUtil.newSession();
       int schemaVersion = getSchemaVersion(session);
+      tx = HibernateUtil.newTransaction(session);
+      
+      //  Commit the changes
+      HibernateUtil.commit(tx);
+      log.info("adjustExistingItems: OK");          
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      if (progress != null) progress.display(100, "The adjustment of the existing items failed: " + ex.getMessage()+"\n");
+      log.info("adjustExistingItems: FAILED");
+      throw ex;
+    }
+    finally
+    {
+      if (session != null) HibernateUtil.close(session);
+      Application.stop();
+    }
+  }
+  
+  /**
+    Set subtypes for hybridizations, scans, 
+    labeled extracts and labels moved from BASE 2.17
+    
+    @return The new schema version (=100)
+  */
+  private static int updateToSchemaVersion100(org.hibernate.Session session)
+    throws BaseException
+  {
+    final int schemaVersion = 100;
+    org.hibernate.Transaction tx = null;
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+  
+      // Labeled extract all have a non-null label_id column
+      org.hibernate.Query query = HibernateUtil.getPredefinedSQLQuery(session, 
+        "SET_LABELEDEXTRACT_SUBTYPE");
+      /*
+        UPDATE BioMaterials SET subtype_id=? 
+        WHERE label_id NOT IS NULL
+      */
+      query.setInteger("subtype", SystemItems.getId(Extract.LABELED));
+      HibernateUtil.executeUpdate(query);
+
+      // All tags are labels
+      query = HibernateUtil.getPredefinedSQLQuery(session, 
+        "SET_SUBTYPE_ON_ALL", "Labels");
+      // UPDATE Labels SET subtype_id=?
+      query.setInteger("subtype", SystemItems.getId(Tag.LABEL));
+      HibernateUtil.executeUpdate(query);
+      
+      // All physical bioassays are hybridizations
+      query = HibernateUtil.getPredefinedSQLQuery(session, 
+        "SET_SUBTYPE_ON_ALL", "PhysicalBioAssays");
+      // UPDATE PhysicalBioassays SET subtype_id=?
+      query.setInteger("subtype", SystemItems.getId(PhysicalBioAssay.HYBRIDIZATION));
+      HibernateUtil.executeUpdate(query);
+      
+      // All derived bioassays are scans
+      query = HibernateUtil.getPredefinedSQLQuery(session, 
+        "SET_SUBTYPE_ON_ALL", "DerivedBioAssays");
+      // UPDATE DerivedBioAssays SET subtype_id=?
+      query.setInteger("subtype", SystemItems.getId(DerivedBioAssay.SCAN));
+      HibernateUtil.executeUpdate(query);
+      
+      // Update the shcema version number
+      setSchemaVersion(session, schemaVersion);
+  
+      // Commit the changes
+      HibernateUtil.commit(tx);
+      log.info("updateToSchemaVersion100: OK");
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx); 
+      log.error("updateToSchemaVersion100: FAILED", ex);
+      throw ex;
+    }
+    return schemaVersion;
+  }
+
+  
+  public static synchronized void updateToBase3(ProgressReporter progress, String rootLogin, String rootPassword)
+    throws Exception
+  {
+
+    org.hibernate.Session session = null;
+    org.hibernate.Query query = null;
+    try
+    {
+      progress.display(0, "Updating to BASE 3.0...");
+      Config.setProperty("extensions.disabled", "true");
+      Application.start(false, false, false);
+
+      // Test root user account
+      SessionControl sc = Application.newSessionControl(null, null, null);
+      sc.login(rootLogin, rootPassword, null, false);
+      if (sc.getLoggedInUserId() != SystemItems.getId(User.ROOT))
+      {
+        throw new PermissionDeniedException("User '" + rootLogin + "' is not the root account.");
+      }
+      
+      session = HibernateUtil.newSession();
+      int schemaVersion = getSchemaVersion(session);
       
       // 1: copy data to new tables/columns
-
-      // PluginDefinitions and PluginTypes jar_path should be copied to jar_file but only keep filename
+      // #1592: PluginDefinitions and PluginTypes jar_path should be copied to jar_file but only keep filename
       progress.display(10, "--Updating plug-in paths");
       copyJarPathToJarFile(session, "PluginDefinitions");
@@ -271 +357 @@
       cleanContextFromProperty(session, Item.PLUGINTYPE, "jarPath", "jarFile");
   
-      // Four properties removed from DataFileType
+      // #1598: Four properties removed from DataFileType
       cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorClass", null);
       cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorJarPath", null);
       cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderClass", null);
       cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderJarPath", null);
+
+      // #1153
+      progress.display(20, "--Re-linking parent biomaterials");
+      fixBioMaterialParents(session);
+
+      progress.display(30, "--Converting labeled extracts to extracts");
+      fixLabeledExtracts(session);
+      
+      progress.display(40, "--Converting hybridizations to bioassays");
+      Map<Integer, Integer> hybMap = copyHybridizations(session);
+      
+      progress.display(50, "--Converting scans to bioassays");
+      Map<Integer, Integer> scanMap = copyScans(session, hybMap);
+      
+      progress.display(60, "--Re-linking rawbioassays to parents");
+      //fixRawBioAssays(session);
       
       // 2: remove unused columns/tables
-      progress.display(90, "--Dropping old database objects");
+      progress.display(70, "--Dropping old database objects");
+      // #1592
       dropColumn(session, "PluginDefinitions", "jar_path", progress);
       dropColumn(session, "PluginTypes", "jar_path", progress);
       dropColumn(session, "JobAgentSettings", "jar_path", progress);
+      // #1589
       dropColumn(session, "ArrayDesigns", "affy_chip", progress);
+      // #1598
       dropColumn(session, "DataFileTypes", "validator_class", progress);
       dropColumn(session, "DataFileTypes", "validator_jarpath", progress);
@@ -288 +393 @@
       dropColumn(session, "DataFileTypes", "extractor_jarpath", progress);
       
-      setSchemaVersionInTransaction(session, 100);
+      // #1597
+      dropColumn(session, "Files", "filetype_id", progress);
+      dropColumn(session, "MimeTypes", "filetype_id", progress);
+      dropColumn(session, "DataFileTypes", "filetype_id", progress);
+      dropColumn(session, "Protocols", "protocoltype_id", progress);
+      dropColumn(session, "PlateEventTypes", "protocoltype_id", progress);
+      dropColumn(session, "Hardware", "hardwaretype_id", progress);
+      dropColumn(session, "Software", "softwaretype_id", progress);
+      dropTable(session, "FileTypes", progress);
+      dropTable(session, "ProtocolTypes", progress);
+      dropTable(session, "HardwareTypes", progress);
+      dropTable(session, "SoftwareTypes", progress);
+      
+      // #1153
+      dropColumn(session, "RawBioAssays", "scan_id", progress);
+      dropColumn(session, "RawBioAssays", "array_num", progress);
+      dropColumn(session, "BioMaterials", "pooled", progress);
+      dropColumn(session, "BioMaterialEvents", "hybridization_id", progress);
+      dropTable(session, "Images", progress);
+      dropTable(session, "Scans", progress);
+      dropTable(session, "Hybridizations", progress);
+      dropTable(session, "BioMaterialEventSources", progress);
+      
+      setSchemaVersionInTransaction(session, 99);
       log.info("updateToBase3: OK");
     }
-    catch (BaseException ex)
+    catch (Exception ex)
     {
       progress.display(100, "Update to BASE 3 failed: " + ex.getMessage()+"\n");
@@ -339 +467 @@
   }
 
+  private static TableInfo getTableInfo(org.hibernate.Session session, final String tableName)
+    throws SQLException
+  {
+    TableInfo info = HibernateUtil.doJdbcWork(session, 
+    
+        new JdbcWork<TableInfo>()
+        {
+
+          private TableInfo info;
+          @Override
+          public void execute(Connection c) 
+            throws SQLException
+          {
+            DatabaseMetaData metaData = c.getMetaData();
+            Table t = new Table(tableName);
+            info = new TableInfo(t, metaData);
+            
+          }
+
+          @Override
+          public TableInfo getResult()
+          {
+            return info;
+          }}
+    
+    );
+    return info;
+  }
+  
   private static void dropColumn(org.hibernate.Session session, String tableName, String columnName, ProgressReporter progress)
+    throws SQLException
   {
     org.hibernate.Transaction tx = null;
@@ -346 +504 @@
     {
       log.debug("Dropping column: " + tableName + "." + columnName);
+      tx = HibernateUtil.newTransaction(session);
+      
+      // Drop foreign keys where this column is used
+      TableInfo info = getTableInfo(session, tableName);
+      for (ForeignKeyInfo fk : info.getForeignKeys())
+      {
+        if (fk.getFkColumns().contains(columnName))
+        {
+          log.debug("Dropping foreign key: " + tableName + "." + fk.getName() + "=" + fk.getFkColumns());
+          query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_FOREIGNKEY", tableName, fk.getName());
+          query.executeUpdate();
+        }
+      }
+      
       progress.display(90, "  --alter table " + tableName + " drop column " + columnName);
-      tx = HibernateUtil.newTransaction(session);
       query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_COLUMN", tableName, columnName);
       query.executeUpdate();
@@ -359 +530 @@
   }
 
+  private static void dropTable(org.hibernate.Session session, String tableName, ProgressReporter progress)
+  {
+    org.hibernate.Transaction tx = null;
+    org.hibernate.Query query = null;
+    try
+    {
+      log.debug("Dropping table: " + tableName);
+      progress.display(90, "  --drop table " + tableName);
+      tx = HibernateUtil.newTransaction(session);
+      query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_TABLE", tableName);
+      query.executeUpdate();
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+  }
+
+  
   private static void cleanContextFromProperty(org.hibernate.Session session, Item itemType, String propertyName, String replacementPropertyName)
   {
@@ -445 +637 @@
   }
   
+  @SuppressWarnings("unchecked")
+  private static <T> T load(org.hibernate.Session session, Class<T> clazz, Object id)
+  {
+    if (id == null) return null;
+    return (T)session.load(clazz, (Integer)id);
+  }
+  
+  private static void fixLabeledExtracts(org.hibernate.Session session)
+  {
+    org.hibernate.Transaction tx = null;
+    org.hibernate.Query query = null;
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+      query = HibernateUtil.getPredefinedSQLQuery(session, "CONVERT_LABELEDEXTRACTS_TO_EXTRACTS");
+      query.executeUpdate();
+      
+      fixSoftLinks(session, 204, Item.EXTRACT.getValue(),  
+        "net.sf.basedb.core.data.LabeledExtractData",
+        "net.sf.basedb.core.data.ExtractData",
+        null);
+      
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+    
+  }
+  
+  /**
+    Copy from 'Hybridizations' table to 'PhysicalBioAssays' table. 
+    Fix all "soft links" related to "HYBRIDIZATION", so that they
+    now use "PHYSICALBIOASSAY" instead.
+    @return A Map with oldId -> newId for hybridizations
+  */
+  private static Map<Integer, Integer> copyHybridizations(org.hibernate.Session session)
+  {
+    org.hibernate.Transaction tx = null;
+    Map<Integer, Integer> idMap = new HashMap<Integer, Integer>();
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+      org.hibernate.Query hybQuery = 
+        HibernateUtil.getPredefinedSQLQuery(session, "GET_HYBRIDIZATIONS");
+      org.hibernate.Query pbaQuery =
+        HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_BIOASSAYID_FROM_HYBID");
+      // UPDATE BioMaterialEvents 
+      // SET physicalbioassay_id = ? WHERE hybridization_id=?
+      Iterator it = hybQuery.list().iterator();
+      while (it.hasNext())
+      {
+        Object[] row = (Object[])it.next();
+        Integer hybId = (Integer)row[0];
+        PhysicalBioAssayData bioAssay = new PhysicalBioAssayData();
+        bioAssay.setArraySlide(load(session, ArraySlideData.class, row[2]));
+        bioAssay.setSize((Integer)row[3]);
+        bioAssay.setAnnotationSet(load(session, AnnotationSetData.class, row[4]));
+        bioAssay.setName((String)row[5]);
+        bioAssay.setDescription((String)row[6]);
+        bioAssay.setRemoved((Boolean)row[7]);
+        bioAssay.setItemKey(load(session, ItemKeyData.class, row[8]));
+        bioAssay.setProjectKey(load(session, ProjectKeyData.class, row[9]));
+        bioAssay.setOwner(load(session, UserData.class, row[10]));
+        
+        session.save(bioAssay);
+        
+        idMap.put(hybId, bioAssay.getId());
+        pbaQuery.setInteger("hybId", hybId);
+        pbaQuery.setInteger("bioAssayId", bioAssay.getId());
+        pbaQuery.executeUpdate();
+      }
+      
+      fixSoftLinks(session, 261, Item.PHYSICALBIOASSAY.getValue(),  
+        "net.sf.basedb.core.data.HybridizationData",
+        "net.sf.basedb.core.data.PhysicalBioAssayData",
+        idMap);
+      
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+    return idMap;
+  }
+  
+  /**
+    Copy from 'Scans' table to 'DerivedBioAssays' table. 
+    Fix all "soft links" related to "SCAN", so that they
+    now use "DERIVEDBIOASSAY" instead.
+    
+    TODO: Copy from 'Images' to xxxx
+    
+    @param hybMap A Map with oldId -> newId for hybridizations
+    @return A Map with oldId -> newId for scans
+  */
+  private static Map<Integer, Integer> copyScans(org.hibernate.Session session, Map<Integer, Integer> hybMap)
+  {
+    org.hibernate.Transaction tx = null;
+    Map<Integer, Integer> idMap = new HashMap<Integer, Integer>();
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+      org.hibernate.Query scanQuery = 
+        HibernateUtil.getPredefinedSQLQuery(session, "GET_SCANS");
+      Iterator it = scanQuery.list().iterator();
+      while (it.hasNext())
+      {
+        Object[] row = (Object[])it.next();
+        Integer scanId = (Integer)row[0];
+        DerivedBioAssayData bioAssay = new DerivedBioAssayData();
+        bioAssay.setEntryDate((Date)row[2]);
+        bioAssay.setPhysicalBioAssay(load(session, PhysicalBioAssayData.class, hybMap.get(row[3])));
+        bioAssay.setHardware(load(session, HardwareData.class, row[4]));
+        bioAssay.setProtocol(load(session, ProtocolData.class, row[5]));
+        bioAssay.setAnnotationSet(load(session, AnnotationSetData.class, row[6]));
+        bioAssay.setName((String)row[7]);
+        bioAssay.setDescription((String)row[8]);
+        bioAssay.setRemoved((Boolean)row[9]);
+        bioAssay.setItemKey(load(session, ItemKeyData.class, row[10]));
+        bioAssay.setProjectKey(load(session, ProjectKeyData.class, row[11]));
+        bioAssay.setOwner(load(session, UserData.class, row[12]));
+        
+        session.save(bioAssay);
+        
+        idMap.put(scanId, bioAssay.getId());
+      }
+      
+      fixSoftLinks(session, 262, Item.DERIVEDBIOASSAY.getValue(),  
+        "net.sf.basedb.core.data.ScanData",
+        "net.sf.basedb.core.data.DerivedBioAssayData",
+        idMap);
+      
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+    return idMap;
+  }
+
+  /**
+    Copy from 'BioMaterialEventSources' to 'BioMaterialEventSources2'
+    table.
+    
+    Set the parent type property on all biomaterials.
+    
+  */
+  private static void fixBioMaterialParents(org.hibernate.Session session)
+  {
+    org.hibernate.Transaction tx = null;
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+
+      // Copy to BioMaterialEventSources2
+      org.hibernate.Query query = HibernateUtil.getPredefinedSQLQuery(session, 
+          "COPY_BIOMATERIALEVENTSOURCES");
+      query.executeUpdate();
+
+      query = HibernateUtil.getPredefinedSQLQuery(session, 
+        "SET_PARENTTYPE_ON_BIOMATERIALS_WITH_ONE_PARENT");
+      query.executeUpdate();
+      
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+    
+  }
+  
+  /**
+    Fix all "soft links" in the BASE database. Soft links are links that
+    are an item_type + item_id pair (or a class name + id)
+    @param oldType The old item type code
+    @param newType The new item type code (must be different from the old or the update may
+      be incorrect)
+    @param idMap Map with old -> new id values (null or empty is accepted)
+  */
+  private static void fixSoftLinks(org.hibernate.Session session, 
+    int oldType, int newType, String oldDataClass, String newDataClass,
+    Map<Integer, Integer> idMap)
+  {
+    if (oldType == newType) 
+    {
+      throw new AssertionError("Can't fix links when oldType==newType: " + oldType);
+    }
+    
+    String oldTypeS = Integer.toString(oldType);
+    String newTypeS = Integer.toString(newType);
+
+    if (idMap != null && idMap.size() > 0)
+    {
+      // Queries that need to update ID of each individual item
+      List<org.hibernate.Query> queries = new ArrayList<org.hibernate.Query>();
+      
+      // UPDATE AnnotationSets SET item_id=?, item_type={2} WHERE item_id=? and item_type={1}
+      queries.add(HibernateUtil.getPredefinedSQLQuery(session, 
+        "UPDATE_ANNOTATIONSET_ITEM", oldTypeS, newTypeS));
+      
+      // UPDATE AnyToAny SET from_id=?, from_type={2} WHERE from_id=? and from_type={1}
+      queries.add(HibernateUtil.getPredefinedSQLQuery(session, 
+        "UPDATE_ANYTOANY_FROMITEM", oldTypeS, newTypeS));
+      
+      // UPDATE AnyToAny SET to_id=?, to_type={2} WHERE to_id=? and to_type={1}
+      queries.add(HibernateUtil.getPredefinedSQLQuery(session, 
+        "UPDATE_ANYTOANY_TOITEM", oldTypeS, newTypeS));
+  
+      // UPDATE ChangeHistoryDetails SET item_id=?, item_type={2} WHERE item_id=? and item_type={1}
+      queries.add(HibernateUtil.getPredefinedSQLQuery(session, 
+        "UPDATE_CHANGEHISTORYDETAILS_ITEM", oldTypeS, newTypeS));
+      
+      // UPDATE ItemValues SET data_class_id=?, data_class=? WHERE data_class_id=? AND data_class=?
+      org.hibernate.Query q = HibernateUtil.getPredefinedSQLQuery(session, 
+          "UPDATE_ITEMVALUES_ITEM");
+      q.setString("oldClass", oldDataClass);
+      q.setString("newClass", newDataClass);
+      queries.add(q);
+      
+      for (org.hibernate.Query query : queries)
+      {
+        for (Map.Entry<Integer, Integer> entry : idMap.entrySet())
+        {
+          query.setInteger("oldId", entry.getKey());
+          query.setInteger("newId", entry.getValue());
+          query.executeUpdate();
+        }
+      }
+    }
+    else
+    {
+      // Assume 1-1 mapping on ID and modify the item type only
+      
+      // UPDATE AnyToAny SET from_type={2} WHERE from_type={1}
+      HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+        "AnyToAny", "from_type", oldTypeS, newTypeS).executeUpdate();
+      // UPDATE AnyToAny SET to_type={2} WHERE to_type={1}
+      HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+        "AnyToAny", "to_type", oldTypeS, newTypeS).executeUpdate();
+      // UPDATE ChangeHistoryDetails SET item_type={2} WHERE item_type={1}
+      HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+        "ChangeHistoryDetails", "item_type", oldTypeS, newTypeS).executeUpdate();
+      
+      // UPDATE ItemValues SET data_class_id=?, data_class=? WHERE data_class_id=? AND data_class=?
+      org.hibernate.Query q = HibernateUtil.getPredefinedSQLQuery(session, 
+          "UPDATE_ITEMVALUES_CLASS");
+      q.setString("oldClass", oldDataClass);
+      q.setString("newClass", newDataClass);
+      q.executeUpdate();
+    }
+
+    // Queries that work on the item type only
+    // UPDATE AnnotationSet SET item_type={2} WHERE item_type={1}
+    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+      "AnnotationSets", "item_type", oldTypeS, newTypeS).executeUpdate();
+    // UPDATE AnnotationTypeItems SET item_type={2} WHERE item_type={1}
+    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+      "AnnotationTypeItems", "item_type", oldTypeS, newTypeS).executeUpdate();
+    // UPDATE BioMaterialLists SET member_type={2} WHERE member_type={1}
+    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+      "BioMaterialLists", "member_type", oldTypeS, newTypeS).executeUpdate();
+    // UPDATE BioPlateTypes SET biomaterial_type={2} WHERE biomaterial_type={1}
+    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE", 
+      "BioPlateTypes", "biomaterial_type", oldTypeS, newTypeS).executeUpdate();
+    // DELETE FROM PluginDefinitionGuiContexts WHERE item_type={1}
+    HibernateUtil.getPredefinedSQLQuery(session, 
+      "DELETE_PLUGINGUICONTEXTS_ITEMTYPE", oldTypeS).executeUpdate();
+    
+  }
+  
 }

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

@@ -74 +74 @@
         if (update)
         {
-          if (schemaVersion == 90) 
+          if (schemaVersion < 100) 
           {
             Update.updateToBase3(progress, rootLogin, rootPassword);
@@ -80 +80 @@
           else
           {
-            Update.adjustExistingItems(update, progress, rootLogin, rootPassword);
+            Update.adjustExistingItems(progress, rootLogin, rootPassword);
           }
         }