Changeset 5703


Ignore:
Timestamp:
Aug 19, 2011, 3:41:57 PM (10 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:
2 deleted
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/common-queries.xml

    r5695 r5703  
    28692869  </query>
    28702870
     2871  <query id="DROP_FOREIGNKEY" type="SQL">
     2872    <sql>
     2873      ALTER TABLE [{1}] DROP FOREIGN KEY [{2}]
     2874    </sql>
     2875    <description>
     2876      An SQL query that drops a foreign key from a table.
     2877    </description>
     2878  </query>
     2879
     2880  <query id="DROP_TABLE" type="SQL">
     2881    <sql>
     2882      DROP TABLE [{1}]
     2883    </sql>
     2884    <description>
     2885      An SQL query that drops a table.
     2886    </description>
     2887  </query>
     2888
     2889  <query id="CONVERT_LABELEDEXTRACTS_TO_EXTRACTS" type="SQL">
     2890    <sql>
     2891      UPDATE [BioMaterials]
     2892      SET [discriminator] = 3
     2893      WHERE [discriminator] = 4
     2894    </sql>
     2895    <description>
     2896      An SQL query that convert labeled extract to extract.
     2897    </description>
     2898  </query>
     2899
     2900  <query id="GET_HYBRIDIZATIONS" type="SQL">
     2901    <sql>
     2902      SELECT
     2903        [id], [version],
     2904        [arrayslide_id], [num_arrays],
     2905        [annotationset_id], [name],
     2906        [description], [removed],
     2907        [itemkey_id], [projectkey_id],
     2908        [owner]
     2909      FROM
     2910        [Hybridizations]
     2911    </sql>
     2912    <description>
     2913      An SQL query that load all BASE 2.17 hybridizations.
     2914    </description>
     2915  </query>
     2916
     2917  <query id="UPDATE_BIOASSAYID_FROM_HYBID" type="SQL">
     2918    <sql>
     2919      UPDATE [BioMaterialEvents]
     2920      SET [physicalbioassay_id] = :bioAssayId
     2921      WHERE [hybridization_id] = :hybId
     2922    </sql>
     2923    <description>
     2924      An SQL query that sets the physicalbioassay_id
     2925      for a given hybridization_id
     2926    </description>
     2927  </query>
     2928
     2929  <query id="GET_SCANS" type="SQL">
     2930    <sql>
     2931      SELECT
     2932        [id], [version],
     2933        [entry_date], [hybridization_id],
     2934        [hardware_id], [protocol_id],
     2935        [annotationset_id], [name],
     2936        [description], [removed],
     2937        [itemkey_id], [projectkey_id],
     2938        [owner]
     2939      FROM
     2940        [Scans]
     2941    </sql>
     2942    <description>
     2943      An SQL query that load all BASE 2.17 scans.
     2944    </description>
     2945  </query>
     2946
     2947  <query id="COPY_BIOMATERIALEVENTSOURCES" type="SQL">
     2948    <sql>
     2949      INSERT INTO [BioMaterialEventSources2]
     2950      (
     2951        [version],
     2952        [biomaterial_id], [event_id],
     2953        [used_quantity], [position]
     2954      )
     2955      SELECT
     2956        0,
     2957        [biomaterial_id], [event_id],
     2958        [used_quantity], [dummy]
     2959      FROM [BioMaterialEventSources]
     2960    </sql>
     2961    <description>
     2962      An SQL query that load all BASE 2.17 biomaterial
     2963      event sources.
     2964    </description>
     2965  </query>
     2966
     2967  <query id="SET_PARENTTYPE_ON_BIOMATERIALS_WITH_ONE_PARENT" type="SQL">
     2968    <sql>
     2969      UPDATE [BioMaterials]
     2970      SET [parent_type]=
     2971        CASE [discriminator]
     2972        WHEN 2 THEN 201
     2973        WHEN 3 THEN 202
     2974        WHEN 4 THEN 203
     2975        END
     2976      WHERE [parent_id] IS NOT NULL
     2977    </sql>
     2978    <description>
     2979      An SQL query that sets the subtype of labeled extracts
     2980    </description>
     2981  </query>
     2982
     2983  <query id="SET_LABELEDEXTRACT_SUBTYPE" type="SQL">
     2984    <sql>
     2985      UPDATE [BioMaterials]
     2986      SET [subtype_id]=:subtype
     2987      WHERE [label_id] IS NOT NULL
     2988    </sql>
     2989    <description>
     2990      An SQL query that sets the subtype of labeled extracts
     2991    </description>
     2992  </query>
     2993
     2994  <query id="SET_SUBTYPE_ON_ALL" type="SQL">
     2995    <sql>
     2996      UPDATE [{1}]
     2997      SET [subtype_id]=:subtype
     2998    </sql>
     2999    <description>
     3000      An SQL query that sets the subtype
     3001      of all items in a table
     3002    </description>
     3003  </query>
     3004
     3005  <query id="UPDATE_ANNOTATIONSET_ITEM" type="SQL">
     3006    <sql>
     3007      UPDATE [AnnotationSets]
     3008      SET [item_id] = :newId, [item_type] = {2}
     3009      WHERE [item_id] = :oldId AND [item_type] = {1}
     3010    </sql>
     3011    <description>
     3012      An SQL query that update the item_type and item_id
     3013      on annotation set.
     3014    </description>
     3015  </query>
     3016
     3017  <query id="UPDATE_ANYTOANY_FROMITEM" type="SQL">
     3018    <sql>
     3019      UPDATE [AnyToAny]
     3020      SET [from_id] = :newId, [from_type] = {2}
     3021      WHERE [from_id] = :oldId AND [from_type] = {1}
     3022    </sql>
     3023    <description>
     3024      An SQL query that update the from_type and from_id
     3025      on any-to-any.
     3026    </description>
     3027  </query>
     3028 
     3029  <query id="UPDATE_ANYTOANY_TOITEM" type="SQL">
     3030    <sql>
     3031      UPDATE [AnyToAny]
     3032      SET [to_id] = :newId, [to_type] = {2}
     3033      WHERE [to_id] = :oldId AND [to_type] = {1}
     3034    </sql>
     3035    <description>
     3036      An SQL query that update the to_type and to_id
     3037      on any-to-any.
     3038    </description>
     3039  </query>
     3040 
     3041  <query id="UPDATE_CHANGEHISTORYDETAILS_ITEM" type="SQL">
     3042    <sql>
     3043      UPDATE [ChangeHistoryDetails]
     3044      SET [item_id] = :newId, [item_type] = {2}
     3045      WHERE [item_id] = :oldId AND [item_type] = {1}
     3046    </sql>
     3047    <description>
     3048      An SQL query that update the item_type and item_id
     3049      on change history details.
     3050    </description>
     3051  </query>
     3052 
     3053  <query id="UPDATE_ITEMVALUES_ITEM" type="SQL">
     3054    <sql>
     3055      UPDATE [ItemValues]
     3056      SET [data_class_id] = :newId, [data_class] = :newClass
     3057      WHERE [data_class_id] = :oldId AND [data_class] = :oldClass
     3058    </sql>
     3059    <description>
     3060      An SQL query that update the data_class_id and data_class
     3061      on item values.
     3062    </description>
     3063  </query>
     3064
     3065  <query id="UPDATE_ITEMVALUES_CLASS" type="SQL">
     3066    <sql>
     3067      UPDATE [ItemValues]
     3068      SET [data_class] = :newClass
     3069      WHERE [data_class] = :oldClass
     3070    </sql>
     3071    <description>
     3072      An SQL query that update the data_class
     3073      on item values.
     3074    </description>
     3075  </query>
     3076
     3077  <query id="UPDATE_ITEMTYPE" type="SQL">
     3078    <sql>
     3079      UPDATE [{1}]
     3080      SET [{2}] = {4}
     3081      WHERE [{2}] = {3}
     3082    </sql>
     3083    <description>
     3084      An SQL query that update the item-type column on
     3085      a generic table.
     3086    </description>
     3087  </query>
     3088
     3089  <query id="DELETE_PLUGINGUICONTEXTS_ITEMTYPE" type="SQL">
     3090    <sql>
     3091      DELETE FROM PluginDefinitionGuiContexts
     3092      WHERE item_type={1}
     3093    </sql>
     3094    <description>
     3095      An SQL query that delete plug-in gui contexts for
     3096      a given item type.
     3097    </description>
     3098  </query>
     3099
    28713100  <query id="GET_SUBTYPABLE_ITEMS_FOR_SUBTYPE_OF_CLASS" type="HQL">
    28723101    <sql>
  • trunk/src/core/net/sf/basedb/core/Item.java

    r5685 r5703  
    294294    410),
    295295
     296  /*
     297    IMPORTANT! Do not use the following codes since they were used in BASE 2:
     298    204 = LABELEDEXTRACT
     299  */
    296300  /**
    297301    The item is a {@link BioMaterialEvent}.
     
    302306    The item is a {@link Tag}.
    303307  */
    304   TAG(206, "Tag", "lbl", Tag.class, TagData.class, DefinedPermissions.shareable,
     308  TAG(206, "Tag", "tag", Tag.class, TagData.class, DefinedPermissions.shareable,
    305309    440),
    306310
     
    420424    550),
    421425
    422  
     426  /*
     427    IMPORTANT! Do not use the following codes since they were used in BASE 2:
     428    261 = HYBRIDIZATION
     429    262 = SCAN
     430    263 = IMAGE
     431  */
    423432  /**
    424433    The item is a {@link PhysicalBioAssay}.
    425434  */
    426   PHYSICALBIOASSAY(261, "Physical bioassay", "pba", PhysicalBioAssay.class, PhysicalBioAssayData.class, DefinedPermissions.shareable,
     435  PHYSICALBIOASSAY(267, "Physical bioassay", "pba", PhysicalBioAssay.class, PhysicalBioAssayData.class, DefinedPermissions.shareable,
    427436    350),
    428437
     
    430439    The item is a {@link DerivedBioAssay}.
    431440  */
    432   DERIVEDBIOASSAY(267, "Derived bioassay", "dba", DerivedBioAssay.class, DerivedBioAssayData.class, DefinedPermissions.shareable,
     441  DERIVEDBIOASSAY(268, "Derived bioassay", "dba", DerivedBioAssay.class, DerivedBioAssayData.class, DefinedPermissions.shareable,
    433442    330),
    434443   
  • trunk/src/core/net/sf/basedb/core/Update.java

    r5630 r5703  
    2323package net.sf.basedb.core;
    2424
     25import java.sql.Connection;
     26import java.sql.DatabaseMetaData;
     27import java.sql.SQLException;
    2528import java.util.ArrayList;
    2629import java.util.Arrays;
     30import java.util.Date;
     31import java.util.HashMap;
     32import java.util.Iterator;
    2733import java.util.List;
    2834import java.util.Map;
    2935
    30 
     36import org.hibernate.mapping.Table;
     37
     38
     39import net.sf.basedb.core.data.AnnotationSetData;
     40import net.sf.basedb.core.data.ArraySlideData;
    3141import net.sf.basedb.core.data.ContextData;
     42import net.sf.basedb.core.data.DerivedBioAssayData;
     43import net.sf.basedb.core.data.HardwareData;
     44import net.sf.basedb.core.data.ItemKeyData;
     45import net.sf.basedb.core.data.PhysicalBioAssayData;
     46import net.sf.basedb.core.data.ProjectKeyData;
    3247import net.sf.basedb.core.data.PropertyFilterData;
     48import net.sf.basedb.core.data.ProtocolData;
    3349import net.sf.basedb.core.data.SchemaVersionData;
     50import net.sf.basedb.core.data.UserData;
     51import net.sf.basedb.core.dbengine.TableInfo;
     52import net.sf.basedb.core.dbengine.TableInfo.ForeignKeyInfo;
     53import net.sf.basedb.core.hibernate.JdbcWork;
    3454import net.sf.basedb.util.Values;
    3555
     
    103123      session = HibernateUtil.newSession();
    104124      int schemaVersion = getSchemaVersion(session);
    105        
     125     
     126      if (schemaVersion == 99)
     127      {
     128        // Final update to BASE 3
     129        if (progress != null) progress.display((int)(1*progress_factor), "--Updating schema version: " + schemaVersion + " -> 100...");
     130        schemaVersion = updateToSchemaVersion100(session);
     131
     132      }
     133     
    106134      sc.logout();
    107135      if (progress != null) progress.display(100, "Database updated successfully.");
     
    189217    update is executed again.
    190218   
    191       @param update FALSE if it is an installation. TRUE if it is an update.
    192219      @param progress An object implementing the {@link ProgressReporter}
    193220      interface
     
    196223      @throws BaseException
    197224  */
    198   public static synchronized void adjustExistingItems(boolean update, ProgressReporter progress, String rootLogin, String rootPassword)
     225  public static synchronized void adjustExistingItems(ProgressReporter progress, String rootLogin, String rootPassword)
    199226    throws BaseException
    200227  {
    201     if (update)
    202     {
    203       org.hibernate.Transaction tx = null;
    204       org.hibernate.Session session = null;
    205       try
    206       {
    207         Config.setProperty("extensions.disabled", "true");
    208         Application.start(false, false, false);
    209 
    210         // Test root user account
    211         SessionControl sc = Application.newSessionControl(null, null, null);
    212         sc.login(rootLogin, rootPassword, null, false);
    213         if (sc.getLoggedInUserId() != SystemItems.getId(User.ROOT))
    214         {
    215           throw new PermissionDeniedException("User '" + rootLogin + "' is not the root account.");
    216         }
    217        
    218         session = HibernateUtil.newSession();
    219         int schemaVersion = getSchemaVersion(session);
    220         tx = HibernateUtil.newTransaction(session);
    221        
    222         //  Commit the changes
    223         HibernateUtil.commit(tx);
    224         log.info("adjustExistingItems: OK");         
    225       }
    226       catch (BaseException ex)
    227       {
    228         if (tx != null) HibernateUtil.rollback(tx);
    229         if (progress != null) progress.display(100, "The adjustment of the existing items failed: " + ex.getMessage()+"\n");
    230         log.info("adjustExistingItems: FAILED");
    231         throw ex;
    232       }
    233       finally
    234       {
    235         if (session != null) HibernateUtil.close(session);
    236         Application.stop();
    237       }
    238     }
    239   }
    240  
    241   public static synchronized void updateToBase3(ProgressReporter progress, String rootLogin, String rootPassword)
    242     throws BaseException
    243   {
    244 
     228    org.hibernate.Transaction tx = null;
    245229    org.hibernate.Session session = null;
    246     org.hibernate.Query query = null;
    247     try
    248     {
    249       progress.display(0, "Updating to BASE 3.0...");
     230    try
     231    {
    250232      Config.setProperty("extensions.disabled", "true");
    251233      Application.start(false, false, false);
     
    261243      session = HibernateUtil.newSession();
    262244      int schemaVersion = getSchemaVersion(session);
     245      tx = HibernateUtil.newTransaction(session);
     246     
     247      //  Commit the changes
     248      HibernateUtil.commit(tx);
     249      log.info("adjustExistingItems: OK");         
     250    }
     251    catch (BaseException ex)
     252    {
     253      if (tx != null) HibernateUtil.rollback(tx);
     254      if (progress != null) progress.display(100, "The adjustment of the existing items failed: " + ex.getMessage()+"\n");
     255      log.info("adjustExistingItems: FAILED");
     256      throw ex;
     257    }
     258    finally
     259    {
     260      if (session != null) HibernateUtil.close(session);
     261      Application.stop();
     262    }
     263  }
     264 
     265  /**
     266    Set subtypes for hybridizations, scans,
     267    labeled extracts and labels moved from BASE 2.17
     268   
     269    @return The new schema version (=100)
     270  */
     271  private static int updateToSchemaVersion100(org.hibernate.Session session)
     272    throws BaseException
     273  {
     274    final int schemaVersion = 100;
     275    org.hibernate.Transaction tx = null;
     276    try
     277    {
     278      tx = HibernateUtil.newTransaction(session);
     279 
     280      // Labeled extract all have a non-null label_id column
     281      org.hibernate.Query query = HibernateUtil.getPredefinedSQLQuery(session,
     282        "SET_LABELEDEXTRACT_SUBTYPE");
     283      /*
     284        UPDATE BioMaterials SET subtype_id=?
     285        WHERE label_id NOT IS NULL
     286      */
     287      query.setInteger("subtype", SystemItems.getId(Extract.LABELED));
     288      HibernateUtil.executeUpdate(query);
     289
     290      // All tags are labels
     291      query = HibernateUtil.getPredefinedSQLQuery(session,
     292        "SET_SUBTYPE_ON_ALL", "Labels");
     293      // UPDATE Labels SET subtype_id=?
     294      query.setInteger("subtype", SystemItems.getId(Tag.LABEL));
     295      HibernateUtil.executeUpdate(query);
     296     
     297      // All physical bioassays are hybridizations
     298      query = HibernateUtil.getPredefinedSQLQuery(session,
     299        "SET_SUBTYPE_ON_ALL", "PhysicalBioAssays");
     300      // UPDATE PhysicalBioassays SET subtype_id=?
     301      query.setInteger("subtype", SystemItems.getId(PhysicalBioAssay.HYBRIDIZATION));
     302      HibernateUtil.executeUpdate(query);
     303     
     304      // All derived bioassays are scans
     305      query = HibernateUtil.getPredefinedSQLQuery(session,
     306        "SET_SUBTYPE_ON_ALL", "DerivedBioAssays");
     307      // UPDATE DerivedBioAssays SET subtype_id=?
     308      query.setInteger("subtype", SystemItems.getId(DerivedBioAssay.SCAN));
     309      HibernateUtil.executeUpdate(query);
     310     
     311      // Update the shcema version number
     312      setSchemaVersion(session, schemaVersion);
     313 
     314      // Commit the changes
     315      HibernateUtil.commit(tx);
     316      log.info("updateToSchemaVersion100: OK");
     317    }
     318    catch (BaseException ex)
     319    {
     320      if (tx != null) HibernateUtil.rollback(tx);
     321      log.error("updateToSchemaVersion100: FAILED", ex);
     322      throw ex;
     323    }
     324    return schemaVersion;
     325  }
     326
     327 
     328  public static synchronized void updateToBase3(ProgressReporter progress, String rootLogin, String rootPassword)
     329    throws Exception
     330  {
     331
     332    org.hibernate.Session session = null;
     333    org.hibernate.Query query = null;
     334    try
     335    {
     336      progress.display(0, "Updating to BASE 3.0...");
     337      Config.setProperty("extensions.disabled", "true");
     338      Application.start(false, false, false);
     339
     340      // Test root user account
     341      SessionControl sc = Application.newSessionControl(null, null, null);
     342      sc.login(rootLogin, rootPassword, null, false);
     343      if (sc.getLoggedInUserId() != SystemItems.getId(User.ROOT))
     344      {
     345        throw new PermissionDeniedException("User '" + rootLogin + "' is not the root account.");
     346      }
     347     
     348      session = HibernateUtil.newSession();
     349      int schemaVersion = getSchemaVersion(session);
    263350     
    264351      // 1: copy data to new tables/columns
    265 
    266       // PluginDefinitions and PluginTypes jar_path should be copied to jar_file but only keep filename
     352      // #1592: PluginDefinitions and PluginTypes jar_path should be copied to jar_file but only keep filename
    267353      progress.display(10, "--Updating plug-in paths");
    268354      copyJarPathToJarFile(session, "PluginDefinitions");
     
    271357      cleanContextFromProperty(session, Item.PLUGINTYPE, "jarPath", "jarFile");
    272358 
    273       // Four properties removed from DataFileType
     359      // #1598: Four properties removed from DataFileType
    274360      cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorClass", null);
    275361      cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorJarPath", null);
    276362      cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderClass", null);
    277363      cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderJarPath", null);
     364
     365      // #1153
     366      progress.display(20, "--Re-linking parent biomaterials");
     367      fixBioMaterialParents(session);
     368
     369      progress.display(30, "--Converting labeled extracts to extracts");
     370      fixLabeledExtracts(session);
     371     
     372      progress.display(40, "--Converting hybridizations to bioassays");
     373      Map<Integer, Integer> hybMap = copyHybridizations(session);
     374     
     375      progress.display(50, "--Converting scans to bioassays");
     376      Map<Integer, Integer> scanMap = copyScans(session, hybMap);
     377     
     378      progress.display(60, "--Re-linking rawbioassays to parents");
     379      //fixRawBioAssays(session);
    278380     
    279381      // 2: remove unused columns/tables
    280       progress.display(90, "--Dropping old database objects");
     382      progress.display(70, "--Dropping old database objects");
     383      // #1592
    281384      dropColumn(session, "PluginDefinitions", "jar_path", progress);
    282385      dropColumn(session, "PluginTypes", "jar_path", progress);
    283386      dropColumn(session, "JobAgentSettings", "jar_path", progress);
     387      // #1589
    284388      dropColumn(session, "ArrayDesigns", "affy_chip", progress);
     389      // #1598
    285390      dropColumn(session, "DataFileTypes", "validator_class", progress);
    286391      dropColumn(session, "DataFileTypes", "validator_jarpath", progress);
     
    288393      dropColumn(session, "DataFileTypes", "extractor_jarpath", progress);
    289394     
    290       setSchemaVersionInTransaction(session, 100);
     395      // #1597
     396      dropColumn(session, "Files", "filetype_id", progress);
     397      dropColumn(session, "MimeTypes", "filetype_id", progress);
     398      dropColumn(session, "DataFileTypes", "filetype_id", progress);
     399      dropColumn(session, "Protocols", "protocoltype_id", progress);
     400      dropColumn(session, "PlateEventTypes", "protocoltype_id", progress);
     401      dropColumn(session, "Hardware", "hardwaretype_id", progress);
     402      dropColumn(session, "Software", "softwaretype_id", progress);
     403      dropTable(session, "FileTypes", progress);
     404      dropTable(session, "ProtocolTypes", progress);
     405      dropTable(session, "HardwareTypes", progress);
     406      dropTable(session, "SoftwareTypes", progress);
     407     
     408      // #1153
     409      dropColumn(session, "RawBioAssays", "scan_id", progress);
     410      dropColumn(session, "RawBioAssays", "array_num", progress);
     411      dropColumn(session, "BioMaterials", "pooled", progress);
     412      dropColumn(session, "BioMaterialEvents", "hybridization_id", progress);
     413      dropTable(session, "Images", progress);
     414      dropTable(session, "Scans", progress);
     415      dropTable(session, "Hybridizations", progress);
     416      dropTable(session, "BioMaterialEventSources", progress);
     417     
     418      setSchemaVersionInTransaction(session, 99);
    291419      log.info("updateToBase3: OK");
    292420    }
    293     catch (BaseException ex)
     421    catch (Exception ex)
    294422    {
    295423      progress.display(100, "Update to BASE 3 failed: " + ex.getMessage()+"\n");
     
    339467  }
    340468
     469  private static TableInfo getTableInfo(org.hibernate.Session session, final String tableName)
     470    throws SQLException
     471  {
     472    TableInfo info = HibernateUtil.doJdbcWork(session,
     473   
     474        new JdbcWork<TableInfo>()
     475        {
     476
     477          private TableInfo info;
     478          @Override
     479          public void execute(Connection c)
     480            throws SQLException
     481          {
     482            DatabaseMetaData metaData = c.getMetaData();
     483            Table t = new Table(tableName);
     484            info = new TableInfo(t, metaData);
     485           
     486          }
     487
     488          @Override
     489          public TableInfo getResult()
     490          {
     491            return info;
     492          }}
     493   
     494    );
     495    return info;
     496  }
     497 
    341498  private static void dropColumn(org.hibernate.Session session, String tableName, String columnName, ProgressReporter progress)
     499    throws SQLException
    342500  {
    343501    org.hibernate.Transaction tx = null;
     
    346504    {
    347505      log.debug("Dropping column: " + tableName + "." + columnName);
     506      tx = HibernateUtil.newTransaction(session);
     507     
     508      // Drop foreign keys where this column is used
     509      TableInfo info = getTableInfo(session, tableName);
     510      for (ForeignKeyInfo fk : info.getForeignKeys())
     511      {
     512        if (fk.getFkColumns().contains(columnName))
     513        {
     514          log.debug("Dropping foreign key: " + tableName + "." + fk.getName() + "=" + fk.getFkColumns());
     515          query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_FOREIGNKEY", tableName, fk.getName());
     516          query.executeUpdate();
     517        }
     518      }
     519     
    348520      progress.display(90, "  --alter table " + tableName + " drop column " + columnName);
    349       tx = HibernateUtil.newTransaction(session);
    350521      query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_COLUMN", tableName, columnName);
    351522      query.executeUpdate();
     
    359530  }
    360531
     532  private static void dropTable(org.hibernate.Session session, String tableName, ProgressReporter progress)
     533  {
     534    org.hibernate.Transaction tx = null;
     535    org.hibernate.Query query = null;
     536    try
     537    {
     538      log.debug("Dropping table: " + tableName);
     539      progress.display(90, "  --drop table " + tableName);
     540      tx = HibernateUtil.newTransaction(session);
     541      query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_TABLE", tableName);
     542      query.executeUpdate();
     543      HibernateUtil.commit(tx);
     544    }
     545    catch (BaseException ex)
     546    {
     547      if (tx != null) HibernateUtil.rollback(tx);
     548      throw ex;
     549    }
     550  }
     551
     552 
    361553  private static void cleanContextFromProperty(org.hibernate.Session session, Item itemType, String propertyName, String replacementPropertyName)
    362554  {
     
    445637  }
    446638 
     639  @SuppressWarnings("unchecked")
     640  private static <T> T load(org.hibernate.Session session, Class<T> clazz, Object id)
     641  {
     642    if (id == null) return null;
     643    return (T)session.load(clazz, (Integer)id);
     644  }
     645 
     646  private static void fixLabeledExtracts(org.hibernate.Session session)
     647  {
     648    org.hibernate.Transaction tx = null;
     649    org.hibernate.Query query = null;
     650    try
     651    {
     652      tx = HibernateUtil.newTransaction(session);
     653      query = HibernateUtil.getPredefinedSQLQuery(session, "CONVERT_LABELEDEXTRACTS_TO_EXTRACTS");
     654      query.executeUpdate();
     655     
     656      fixSoftLinks(session, 204, Item.EXTRACT.getValue(), 
     657        "net.sf.basedb.core.data.LabeledExtractData",
     658        "net.sf.basedb.core.data.ExtractData",
     659        null);
     660     
     661      HibernateUtil.commit(tx);
     662    }
     663    catch (BaseException ex)
     664    {
     665      if (tx != null) HibernateUtil.rollback(tx);
     666      throw ex;
     667    }
     668   
     669  }
     670 
     671  /**
     672    Copy from 'Hybridizations' table to 'PhysicalBioAssays' table.
     673    Fix all "soft links" related to "HYBRIDIZATION", so that they
     674    now use "PHYSICALBIOASSAY" instead.
     675    @return A Map with oldId -> newId for hybridizations
     676  */
     677  private static Map<Integer, Integer> copyHybridizations(org.hibernate.Session session)
     678  {
     679    org.hibernate.Transaction tx = null;
     680    Map<Integer, Integer> idMap = new HashMap<Integer, Integer>();
     681    try
     682    {
     683      tx = HibernateUtil.newTransaction(session);
     684      org.hibernate.Query hybQuery =
     685        HibernateUtil.getPredefinedSQLQuery(session, "GET_HYBRIDIZATIONS");
     686      org.hibernate.Query pbaQuery =
     687        HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_BIOASSAYID_FROM_HYBID");
     688      // UPDATE BioMaterialEvents
     689      // SET physicalbioassay_id = ? WHERE hybridization_id=?
     690      Iterator it = hybQuery.list().iterator();
     691      while (it.hasNext())
     692      {
     693        Object[] row = (Object[])it.next();
     694        Integer hybId = (Integer)row[0];
     695        PhysicalBioAssayData bioAssay = new PhysicalBioAssayData();
     696        bioAssay.setArraySlide(load(session, ArraySlideData.class, row[2]));
     697        bioAssay.setSize((Integer)row[3]);
     698        bioAssay.setAnnotationSet(load(session, AnnotationSetData.class, row[4]));
     699        bioAssay.setName((String)row[5]);
     700        bioAssay.setDescription((String)row[6]);
     701        bioAssay.setRemoved((Boolean)row[7]);
     702        bioAssay.setItemKey(load(session, ItemKeyData.class, row[8]));
     703        bioAssay.setProjectKey(load(session, ProjectKeyData.class, row[9]));
     704        bioAssay.setOwner(load(session, UserData.class, row[10]));
     705       
     706        session.save(bioAssay);
     707       
     708        idMap.put(hybId, bioAssay.getId());
     709        pbaQuery.setInteger("hybId", hybId);
     710        pbaQuery.setInteger("bioAssayId", bioAssay.getId());
     711        pbaQuery.executeUpdate();
     712      }
     713     
     714      fixSoftLinks(session, 261, Item.PHYSICALBIOASSAY.getValue(), 
     715        "net.sf.basedb.core.data.HybridizationData",
     716        "net.sf.basedb.core.data.PhysicalBioAssayData",
     717        idMap);
     718     
     719      HibernateUtil.commit(tx);
     720    }
     721    catch (BaseException ex)
     722    {
     723      if (tx != null) HibernateUtil.rollback(tx);
     724      throw ex;
     725    }
     726    return idMap;
     727  }
     728 
     729  /**
     730    Copy from 'Scans' table to 'DerivedBioAssays' table.
     731    Fix all "soft links" related to "SCAN", so that they
     732    now use "DERIVEDBIOASSAY" instead.
     733   
     734    TODO: Copy from 'Images' to xxxx
     735   
     736    @param hybMap A Map with oldId -> newId for hybridizations
     737    @return A Map with oldId -> newId for scans
     738  */
     739  private static Map<Integer, Integer> copyScans(org.hibernate.Session session, Map<Integer, Integer> hybMap)
     740  {
     741    org.hibernate.Transaction tx = null;
     742    Map<Integer, Integer> idMap = new HashMap<Integer, Integer>();
     743    try
     744    {
     745      tx = HibernateUtil.newTransaction(session);
     746      org.hibernate.Query scanQuery =
     747        HibernateUtil.getPredefinedSQLQuery(session, "GET_SCANS");
     748      Iterator it = scanQuery.list().iterator();
     749      while (it.hasNext())
     750      {
     751        Object[] row = (Object[])it.next();
     752        Integer scanId = (Integer)row[0];
     753        DerivedBioAssayData bioAssay = new DerivedBioAssayData();
     754        bioAssay.setEntryDate((Date)row[2]);
     755        bioAssay.setPhysicalBioAssay(load(session, PhysicalBioAssayData.class, hybMap.get(row[3])));
     756        bioAssay.setHardware(load(session, HardwareData.class, row[4]));
     757        bioAssay.setProtocol(load(session, ProtocolData.class, row[5]));
     758        bioAssay.setAnnotationSet(load(session, AnnotationSetData.class, row[6]));
     759        bioAssay.setName((String)row[7]);
     760        bioAssay.setDescription((String)row[8]);
     761        bioAssay.setRemoved((Boolean)row[9]);
     762        bioAssay.setItemKey(load(session, ItemKeyData.class, row[10]));
     763        bioAssay.setProjectKey(load(session, ProjectKeyData.class, row[11]));
     764        bioAssay.setOwner(load(session, UserData.class, row[12]));
     765       
     766        session.save(bioAssay);
     767       
     768        idMap.put(scanId, bioAssay.getId());
     769      }
     770     
     771      fixSoftLinks(session, 262, Item.DERIVEDBIOASSAY.getValue(), 
     772        "net.sf.basedb.core.data.ScanData",
     773        "net.sf.basedb.core.data.DerivedBioAssayData",
     774        idMap);
     775     
     776      HibernateUtil.commit(tx);
     777    }
     778    catch (BaseException ex)
     779    {
     780      if (tx != null) HibernateUtil.rollback(tx);
     781      throw ex;
     782    }
     783    return idMap;
     784  }
     785
     786  /**
     787    Copy from 'BioMaterialEventSources' to 'BioMaterialEventSources2'
     788    table.
     789   
     790    Set the parent type property on all biomaterials.
     791   
     792  */
     793  private static void fixBioMaterialParents(org.hibernate.Session session)
     794  {
     795    org.hibernate.Transaction tx = null;
     796    try
     797    {
     798      tx = HibernateUtil.newTransaction(session);
     799
     800      // Copy to BioMaterialEventSources2
     801      org.hibernate.Query query = HibernateUtil.getPredefinedSQLQuery(session,
     802          "COPY_BIOMATERIALEVENTSOURCES");
     803      query.executeUpdate();
     804
     805      query = HibernateUtil.getPredefinedSQLQuery(session,
     806        "SET_PARENTTYPE_ON_BIOMATERIALS_WITH_ONE_PARENT");
     807      query.executeUpdate();
     808     
     809      HibernateUtil.commit(tx);
     810    }
     811    catch (BaseException ex)
     812    {
     813      if (tx != null) HibernateUtil.rollback(tx);
     814      throw ex;
     815    }
     816   
     817  }
     818 
     819  /**
     820    Fix all "soft links" in the BASE database. Soft links are links that
     821    are an item_type + item_id pair (or a class name + id)
     822    @param oldType The old item type code
     823    @param newType The new item type code (must be different from the old or the update may
     824      be incorrect)
     825    @param idMap Map with old -> new id values (null or empty is accepted)
     826  */
     827  private static void fixSoftLinks(org.hibernate.Session session,
     828    int oldType, int newType, String oldDataClass, String newDataClass,
     829    Map<Integer, Integer> idMap)
     830  {
     831    if (oldType == newType)
     832    {
     833      throw new AssertionError("Can't fix links when oldType==newType: " + oldType);
     834    }
     835   
     836    String oldTypeS = Integer.toString(oldType);
     837    String newTypeS = Integer.toString(newType);
     838
     839    if (idMap != null && idMap.size() > 0)
     840    {
     841      // Queries that need to update ID of each individual item
     842      List<org.hibernate.Query> queries = new ArrayList<org.hibernate.Query>();
     843     
     844      // UPDATE AnnotationSets SET item_id=?, item_type={2} WHERE item_id=? and item_type={1}
     845      queries.add(HibernateUtil.getPredefinedSQLQuery(session,
     846        "UPDATE_ANNOTATIONSET_ITEM", oldTypeS, newTypeS));
     847     
     848      // UPDATE AnyToAny SET from_id=?, from_type={2} WHERE from_id=? and from_type={1}
     849      queries.add(HibernateUtil.getPredefinedSQLQuery(session,
     850        "UPDATE_ANYTOANY_FROMITEM", oldTypeS, newTypeS));
     851     
     852      // UPDATE AnyToAny SET to_id=?, to_type={2} WHERE to_id=? and to_type={1}
     853      queries.add(HibernateUtil.getPredefinedSQLQuery(session,
     854        "UPDATE_ANYTOANY_TOITEM", oldTypeS, newTypeS));
     855 
     856      // UPDATE ChangeHistoryDetails SET item_id=?, item_type={2} WHERE item_id=? and item_type={1}
     857      queries.add(HibernateUtil.getPredefinedSQLQuery(session,
     858        "UPDATE_CHANGEHISTORYDETAILS_ITEM", oldTypeS, newTypeS));
     859     
     860      // UPDATE ItemValues SET data_class_id=?, data_class=? WHERE data_class_id=? AND data_class=?
     861      org.hibernate.Query q = HibernateUtil.getPredefinedSQLQuery(session,
     862          "UPDATE_ITEMVALUES_ITEM");
     863      q.setString("oldClass", oldDataClass);
     864      q.setString("newClass", newDataClass);
     865      queries.add(q);
     866     
     867      for (org.hibernate.Query query : queries)
     868      {
     869        for (Map.Entry<Integer, Integer> entry : idMap.entrySet())
     870        {
     871          query.setInteger("oldId", entry.getKey());
     872          query.setInteger("newId", entry.getValue());
     873          query.executeUpdate();
     874        }
     875      }
     876    }
     877    else
     878    {
     879      // Assume 1-1 mapping on ID and modify the item type only
     880     
     881      // UPDATE AnyToAny SET from_type={2} WHERE from_type={1}
     882      HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     883        "AnyToAny", "from_type", oldTypeS, newTypeS).executeUpdate();
     884      // UPDATE AnyToAny SET to_type={2} WHERE to_type={1}
     885      HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     886        "AnyToAny", "to_type", oldTypeS, newTypeS).executeUpdate();
     887      // UPDATE ChangeHistoryDetails SET item_type={2} WHERE item_type={1}
     888      HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     889        "ChangeHistoryDetails", "item_type", oldTypeS, newTypeS).executeUpdate();
     890     
     891      // UPDATE ItemValues SET data_class_id=?, data_class=? WHERE data_class_id=? AND data_class=?
     892      org.hibernate.Query q = HibernateUtil.getPredefinedSQLQuery(session,
     893          "UPDATE_ITEMVALUES_CLASS");
     894      q.setString("oldClass", oldDataClass);
     895      q.setString("newClass", newDataClass);
     896      q.executeUpdate();
     897    }
     898
     899    // Queries that work on the item type only
     900    // UPDATE AnnotationSet SET item_type={2} WHERE item_type={1}
     901    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     902      "AnnotationSets", "item_type", oldTypeS, newTypeS).executeUpdate();
     903    // UPDATE AnnotationTypeItems SET item_type={2} WHERE item_type={1}
     904    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     905      "AnnotationTypeItems", "item_type", oldTypeS, newTypeS).executeUpdate();
     906    // UPDATE BioMaterialLists SET member_type={2} WHERE member_type={1}
     907    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     908      "BioMaterialLists", "member_type", oldTypeS, newTypeS).executeUpdate();
     909    // UPDATE BioPlateTypes SET biomaterial_type={2} WHERE biomaterial_type={1}
     910    HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_ITEMTYPE",
     911      "BioPlateTypes", "biomaterial_type", oldTypeS, newTypeS).executeUpdate();
     912    // DELETE FROM PluginDefinitionGuiContexts WHERE item_type={1}
     913    HibernateUtil.getPredefinedSQLQuery(session,
     914      "DELETE_PLUGINGUICONTEXTS_ITEMTYPE", oldTypeS).executeUpdate();
     915   
     916  }
     917 
    447918}
  • trunk/src/install/net/sf/basedb/install/InitDB.java

    r5621 r5703  
    7474        if (update)
    7575        {
    76           if (schemaVersion == 90)
     76          if (schemaVersion < 100)
    7777          {
    7878            Update.updateToBase3(progress, rootLogin, rootPassword);
     
    8080          else
    8181          {
    82             Update.adjustExistingItems(update, progress, rootLogin, rootPassword);
     82            Update.adjustExistingItems(progress, rootLogin, rootPassword);
    8383          }
    8484        }
Note: See TracChangeset for help on using the changeset viewer.