Changeset 5733


Ignore:
Timestamp:
Sep 12, 2011, 2:39:30 PM (10 years ago)
Author:
Nicklas Nordborg
Message:

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

  • Parent derived bioassay for raw bioassays
  • Perent extract for raw bioassays
  • Set allow_multiple=false for all existing platform file types
Location:
trunk/src/core
Files:
3 edited

Legend:

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

    r5730 r5733  
    30263026  </query>
    30273027
     3028  <query id="GET_RAWBIOASSAYS" type="SQL">
     3029    <sql>
     3030      SELECT
     3031        [id], [scan_id],
     3032        [array_num]
     3033      FROM [RawBioAssays]
     3034      WHERE NOT [scan_id] IS NULL
     3035    </sql>
     3036    <description>
     3037      An SQL query that load scan and array num for all raw bioassays
     3038      that has a scan parent.
     3039    </description>
     3040  </query>
     3041
     3042  <query id="FIND_EXTRACT_FOR_RAWBIOASSAY_POSITION" type="SQL">
     3043    <sql>
     3044      SELECT bm2.biomaterial_id
     3045      FROM [DerivedBioAssays] dba
     3046      INNER JOIN [BioMaterialEvents] bme ON bme.[physicalbioassay_id] = dba.[bioassay_id]
     3047      INNER JOIN [BioMaterialEventSources2] bm2 ON bm2.[event_id]=bme.[id]
     3048      WHERE bm2.[position] = :position AND dba.[id] = :derivedBioAssayId
     3049    </sql>
     3050    <description>
     3051      An SQL query that tries to find the extract id given a
     3052      derived bioassay and the bioassay position.
     3053    </description>
     3054  </query>
     3055 
     3056  <query id="UPDATE_RAWBIOASSAY" type="SQL">
     3057    <sql>
     3058      UPDATE [RawBioAssays]
     3059      SET [bioassay_id] = :parentBioAssayId,
     3060      [extract_id] = :extractId
     3061      WHERE [id] = :rawBioAssayId
     3062    </sql>
     3063    <description>
     3064      An SQL query that update the parent bioassay and extract
     3065      for a given raw bioassay.
     3066    </description>
     3067  </query>
     3068
    30283069
    30293070  <query id="COPY_BIOMATERIALEVENTSOURCES" type="SQL">
     
    32813322    </description>
    32823323  </query>
     3324   
    32833325 
    32843326</predefined-queries>
  • trunk/src/core/net/sf/basedb/core/Install.java

    r5728 r5733  
    25642564      {
    25652565        log.info("createPlatform: EXISTS [externalId="+externalId+"]");
     2566       
     2567        for (PlatformFileTypeData fileType : platform.getFileTypes().values())
     2568        {
     2569          for (PlatformFT ft : fileTypes)
     2570          {
     2571            if (ft.fileType.equals(fileType.getDataFileType()))
     2572            {
     2573              fileType.setAllowMultiple(ft.multiple);
     2574              fileType.setRequired(ft.required);
     2575            }
     2576          }
     2577        }
     2578       
    25662579        HibernateUtil.commit(tx);
    25672580      }
  • trunk/src/core/net/sf/basedb/core/Update.java

    r5731 r5733  
    319319      query.setString("externalId", "tmp.image");
    320320      DataFileTypeData tmpType = HibernateUtil.loadData(DataFileTypeData.class, query);
    321       query.setString("externalId", DataFileType.MICROARRAY_IMAGE);
    322       DataFileTypeData imgType = HibernateUtil.loadData(DataFileTypeData.class, query);
    323       query = HibernateUtil.getPredefinedQuery(session, "UPDATE_FILESETMEMBER_FILETYPE");
    324       // UPDATE FileSetMemberData mbr SET mbr.dataFileType = :newType WHERE mbr.dataFileType = :oldType
    325       query.setInteger("newType", imgType.getId());
    326       query.setInteger("oldType", tmpType.getId());
    327       query.executeUpdate();
     321      if (tmpType != null)
     322      {
     323        query.setString("externalId", DataFileType.MICROARRAY_IMAGE);
     324        DataFileTypeData imgType = HibernateUtil.loadData(DataFileTypeData.class, query);
     325        query = HibernateUtil.getPredefinedQuery(session, "UPDATE_FILESETMEMBER_FILETYPE");
     326        // UPDATE FileSetMemberData mbr SET mbr.dataFileType = :newType WHERE mbr.dataFileType = :oldType
     327        query.setInteger("newType", imgType.getId());
     328        query.setInteger("oldType", tmpType.getId());
     329        query.executeUpdate();
     330      }
    328331     
    329332      // Remove the temporary data file type
     
    396399      progress.display(15, "--Removing unique constraint on FileSetMembers");
    397400      dropUniqueIndex(session, "FileSetMembers", "fileset_id", "datafiletype_id");
     401      fixPlatformFileTypes(session);
    398402     
    399403      // #1153
     
    416420     
    417421      progress.display(40, "--Re-linking rawbioassays to parents");
    418       //fixRawBioAssays(session);
     422      fixRawBioAssays(session, scanMap);
     423      cleanContextFromProperty(session, Item.RAWBIOASSAY, "scan", "parentBioAssay", false);
    419424     
    420425      // #1597
     
    859864    Copy from 'Scans' table to 'DerivedBioAssays' table.
    860865    Fix all "soft links" related to "SCAN", so that they
    861     now use "DERIVEDBIOASSAY" instead.
    862    
    863     TODO: Copy from 'Images' to xxxx
     866    now use "DERIVEDBIOASSAY" instead. Copy from 'Images'
     867    to `FileSetMembers`. Use a temporary file type that
     868    is later (in updateToSchemaVersion100) changed to the
     869    "Microarray image" type.
    864870   
    865871    @param hybMap A Map with oldId -> newId for hybridizations
     
    951957
    952958  /**
     959    Find the correct parent derived bioassay and extract for
     960    each raw bioassay using the old scan_id column.
     961   
     962    @param scanMap A Map with oldId -> newId for scans
     963  */
     964  private static void fixRawBioAssays(org.hibernate.Session session, Map<Integer, Integer> scanMap)
     965  {
     966    org.hibernate.Transaction tx = null;
     967    Map<Integer, Integer> idMap = new HashMap<Integer, Integer>();
     968    try
     969    {
     970      tx = HibernateUtil.newTransaction(session);
     971      DataFileTypeData fileType = null;
     972      org.hibernate.Query rawQuery =
     973        HibernateUtil.getPredefinedSQLQuery(session, "GET_RAWBIOASSAYS");
     974      org.hibernate.Query extractQuery =
     975        HibernateUtil.getPredefinedSQLQuery(session, "FIND_EXTRACT_FOR_RAWBIOASSAY_POSITION");
     976      org.hibernate.Query setQuery =
     977        HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_RAWBIOASSAY");
     978      Iterator it = rawQuery.list().iterator();
     979      while (it.hasNext())
     980      {
     981        Object[] row = (Object[])it.next();
     982        Integer rawId = (Integer)row[0];
     983        Integer scanId = (Integer)row[1];
     984        Integer arrayNum = (Integer)row[2];
     985
     986        Integer parentId = scanMap.get(scanId);
     987        Integer extractId = null;
     988       
     989        extractQuery.setInteger("derivedBioAssayId", parentId);
     990        extractQuery.setInteger("position", arrayNum);
     991        List<Integer> extracts = HibernateUtil.loadList(Integer.class, extractQuery, null);
     992        if (extracts.size() == 1) extractId = extracts.get(0);
     993       
     994        setQuery.setInteger("rawBioAssayId", rawId);
     995        setQuery.setInteger("parentBioAssayId", parentId);
     996        setQuery.setParameter("extractId", extractId, Type.INT.getTypeWrapper().getHibernateType());
     997        setQuery.executeUpdate();
     998      }
     999     
     1000      HibernateUtil.commit(tx);
     1001    }
     1002    catch (BaseException ex)
     1003    {
     1004      if (tx != null) HibernateUtil.rollback(tx);
     1005      throw ex;
     1006    }
     1007  }
     1008
     1009 
     1010  /**
    9531011    Copy from 'ProtocolTypes' table to 'ItemSubtypes' table. Fix
    9541012    "hard links" in 'Protocols' and 'PlateEventTypes' tables.
     
    11971255  }
    11981256
     1257  /**
     1258    Set PlatformFileTypes.allow_multiple to false for all with a null value
     1259  */
     1260  private static void fixPlatformFileTypes(org.hibernate.Session session)
     1261  {
     1262    org.hibernate.Transaction tx = null;
     1263    try
     1264    {
     1265      tx = HibernateUtil.newTransaction(session);
     1266
     1267      HibernateUtil.createQuery(session, "UPDATE PlatformFileTypeData SET allow_multiple=false").executeUpdate();
     1268     
     1269      HibernateUtil.commit(tx);
     1270    }
     1271    catch (BaseException ex)
     1272    {
     1273      if (tx != null) HibernateUtil.rollback(tx);
     1274      throw ex;
     1275    }
     1276   
     1277   
     1278  }
    11991279 
    12001280  /**
Note: See TracChangeset for help on using the changeset viewer.