Changeset 5733

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:

• common-queries.xml (modified) (2 diffs)
• net/sf/basedb/core/Install.java (modified) (1 diff)
• net/sf/basedb/core/Update.java (modified) (6 diffs)

trunk/src/core/common-queries.xml

@@ -3026 +3026 @@
   </query>
 
+  <query id="GET_RAWBIOASSAYS" type="SQL">
+    <sql>
+      SELECT
+        [id], [scan_id],
+        [array_num]
+      FROM [RawBioAssays]
+      WHERE NOT [scan_id] IS NULL
+    </sql>
+    <description>
+      An SQL query that load scan and array num for all raw bioassays
+      that has a scan parent.
+    </description>
+  </query>
+
+  <query id="FIND_EXTRACT_FOR_RAWBIOASSAY_POSITION" type="SQL">
+    <sql>
+      SELECT bm2.biomaterial_id
+      FROM [DerivedBioAssays] dba
+      INNER JOIN [BioMaterialEvents] bme ON bme.[physicalbioassay_id] = dba.[bioassay_id]
+      INNER JOIN [BioMaterialEventSources2] bm2 ON bm2.[event_id]=bme.[id]
+      WHERE bm2.[position] = :position AND dba.[id] = :derivedBioAssayId
+    </sql>
+    <description>
+      An SQL query that tries to find the extract id given a
+      derived bioassay and the bioassay position.
+    </description>
+  </query>
+  
+  <query id="UPDATE_RAWBIOASSAY" type="SQL">
+    <sql>
+      UPDATE [RawBioAssays]
+      SET [bioassay_id] = :parentBioAssayId, 
+      [extract_id] = :extractId
+      WHERE [id] = :rawBioAssayId
+    </sql>
+    <description>
+      An SQL query that update the parent bioassay and extract
+      for a given raw bioassay.
+    </description>
+  </query>
+
 
   <query id="COPY_BIOMATERIALEVENTSOURCES" type="SQL">
@@ -3281 +3322 @@
     </description>
   </query>
+    
   
 </predefined-queries>

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

@@ -2564 +2564 @@
       {
         log.info("createPlatform: EXISTS [externalId="+externalId+"]");
+        
+        for (PlatformFileTypeData fileType : platform.getFileTypes().values())
+        {
+          for (PlatformFT ft : fileTypes)
+          {
+            if (ft.fileType.equals(fileType.getDataFileType()))
+            {
+              fileType.setAllowMultiple(ft.multiple);
+              fileType.setRequired(ft.required);
+            }
+          }
+        }
+        
         HibernateUtil.commit(tx);
       }

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

@@ -319 +319 @@
       query.setString("externalId", "tmp.image");
       DataFileTypeData tmpType = HibernateUtil.loadData(DataFileTypeData.class, query);
-      query.setString("externalId", DataFileType.MICROARRAY_IMAGE);
-      DataFileTypeData imgType = HibernateUtil.loadData(DataFileTypeData.class, query);
-      query = HibernateUtil.getPredefinedQuery(session, "UPDATE_FILESETMEMBER_FILETYPE");
-      // UPDATE FileSetMemberData mbr SET mbr.dataFileType = :newType WHERE mbr.dataFileType = :oldType
-      query.setInteger("newType", imgType.getId());
-      query.setInteger("oldType", tmpType.getId());
-      query.executeUpdate();
+      if (tmpType != null)
+      {
+        query.setString("externalId", DataFileType.MICROARRAY_IMAGE);
+        DataFileTypeData imgType = HibernateUtil.loadData(DataFileTypeData.class, query);
+        query = HibernateUtil.getPredefinedQuery(session, "UPDATE_FILESETMEMBER_FILETYPE");
+        // UPDATE FileSetMemberData mbr SET mbr.dataFileType = :newType WHERE mbr.dataFileType = :oldType
+        query.setInteger("newType", imgType.getId());
+        query.setInteger("oldType", tmpType.getId());
+        query.executeUpdate();
+      }
       
       // Remove the temporary data file type
@@ -396 +399 @@
       progress.display(15, "--Removing unique constraint on FileSetMembers");
       dropUniqueIndex(session, "FileSetMembers", "fileset_id", "datafiletype_id");
+      fixPlatformFileTypes(session);
       
       // #1153
@@ -416 +420 @@
       
       progress.display(40, "--Re-linking rawbioassays to parents");
-      //fixRawBioAssays(session);
+      fixRawBioAssays(session, scanMap);
+      cleanContextFromProperty(session, Item.RAWBIOASSAY, "scan", "parentBioAssay", false);
       
       // #1597
@@ -859 +864 @@
     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
+    now use "DERIVEDBIOASSAY" instead. Copy from 'Images'
+    to `FileSetMembers`. Use a temporary file type that
+    is later (in updateToSchemaVersion100) changed to the
+    "Microarray image" type.
     
     @param hybMap A Map with oldId -> newId for hybridizations
@@ -951 +957 @@
 
   /**
+    Find the correct parent derived bioassay and extract for 
+    each raw bioassay using the old scan_id column. 
+    
+    @param scanMap A Map with oldId -> newId for scans
+  */
+  private static void fixRawBioAssays(org.hibernate.Session session, Map<Integer, Integer> scanMap)
+  {
+    org.hibernate.Transaction tx = null;
+    Map<Integer, Integer> idMap = new HashMap<Integer, Integer>();
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+      DataFileTypeData fileType = null;
+      org.hibernate.Query rawQuery = 
+        HibernateUtil.getPredefinedSQLQuery(session, "GET_RAWBIOASSAYS");
+      org.hibernate.Query extractQuery = 
+        HibernateUtil.getPredefinedSQLQuery(session, "FIND_EXTRACT_FOR_RAWBIOASSAY_POSITION");
+      org.hibernate.Query setQuery = 
+        HibernateUtil.getPredefinedSQLQuery(session, "UPDATE_RAWBIOASSAY");
+      Iterator it = rawQuery.list().iterator();
+      while (it.hasNext())
+      {
+        Object[] row = (Object[])it.next();
+        Integer rawId = (Integer)row[0];
+        Integer scanId = (Integer)row[1];
+        Integer arrayNum = (Integer)row[2];
+
+        Integer parentId = scanMap.get(scanId);
+        Integer extractId = null;
+        
+        extractQuery.setInteger("derivedBioAssayId", parentId);
+        extractQuery.setInteger("position", arrayNum);
+        List<Integer> extracts = HibernateUtil.loadList(Integer.class, extractQuery, null);
+        if (extracts.size() == 1) extractId = extracts.get(0);
+        
+        setQuery.setInteger("rawBioAssayId", rawId);
+        setQuery.setInteger("parentBioAssayId", parentId);
+        setQuery.setParameter("extractId", extractId, Type.INT.getTypeWrapper().getHibernateType());
+        setQuery.executeUpdate();
+      }
+      
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+  }
+
+  
+  /**
     Copy from 'ProtocolTypes' table to 'ItemSubtypes' table. Fix
     "hard links" in 'Protocols' and 'PlateEventTypes' tables.
@@ -1197 +1255 @@
   }
 
+  /**
+    Set PlatformFileTypes.allow_multiple to false for all with a null value
+  */
+  private static void fixPlatformFileTypes(org.hibernate.Session session)
+  {
+    org.hibernate.Transaction tx = null;
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+
+      HibernateUtil.createQuery(session, "UPDATE PlatformFileTypeData SET allow_multiple=false").executeUpdate();
+      
+      HibernateUtil.commit(tx);
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx);
+      throw ex;
+    }
+    
+    
+  }
   
   /**