Changeset 6084

Timestamp:

Aug 16, 2012, 11:06:41 AM (11 years ago)

Author:

Nicklas Nordborg

Message:

References #1707: Make it possible for a derived bioassay to have multiple physical bioassays as parents

Implemented update script. Fixed queries used by the table exporter so that exporting the child and parent items work.

Location:

trunk

Files:

• src/core/common-queries.xml (modified) (1 diff)
• src/core/net/sf/basedb/core/Install.java (modified) (1 diff)
• src/core/net/sf/basedb/core/Update.java (modified) (6 diffs)
• www/views/derivedbioassays/index.jsp (modified) (2 diffs)
• www/views/physicalbioassays/index.jsp (modified) (1 diff)

trunk/src/core/common-queries.xml

@@ -3390 +3390 @@
     </description>
   </query>
+
+  <query id="COPY_PARENT_PHYSICAL_BIOASSAYS" type="SQL">
+    <sql>
+      INSERT INTO [ParentPhysicalBioAssays] ([physicalbioassay_id], [derivedbioassay_id])
+      SELECT [bioassay_id], [id] FROM [DerivedBioAssays]
+    </sql>
+    <description>
+      An SQL query that copy the parent physical bioassays
+      to the new (in BASE 3.2) ParentPhysicalBioAssays table
+    </description>
+  </query>
+
+
+  <query id="COPY_PARENT_DERIVED_BIOASSAYS" type="SQL">
+    <sql>
+      INSERT INTO [ParentDerivedBioAssays] ([parentbioassay_id], [derivedbioassay_id])
+      SELECT [parent_id], [id] FROM [DerivedBioAssays] WHERE NOT [parent_id] IS NULL
+    </sql>
+    <description>
+      An SQL query that copy the parent derived bioassays
+      to the new (in BASE 3.2) ParentDerivedBioAssays table
+    </description>
+  </query>
+
+  <query id="SET_ISROOT_ON_DERIVED_BIOASSAYS" type="SQL">
+    <sql>
+      UPDATE [DerivedBioAssays] SET [is_root] = ([parent_id] IS NULL)
+    </sql>
+    <description>
+      An SQL query that set the is_root property on DerivedBioAssays table.
+    </description>
+  </query>
+
   
 </predefined-queries>

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

@@ -119 +119 @@
     method.
   */
-  public static final int NEW_SCHEMA_VERSION = Integer.valueOf(109).intValue();
+  public static final int NEW_SCHEMA_VERSION = Integer.valueOf(110).intValue();
   
   public static synchronized int createTables(SchemaGenerator.Mode mode, ProgressReporter progress,

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

@@ -151 +151 @@
   </tr>
   <tr>
-    <td>109</td>
+    <td>109 and 110</td>
     <td>
       Added {@link DerivedBioAssayData#getPhysicalBioAssays()} and {@link DerivedBioAssayData#getParents()}
       and removed <code>DerivedBioAssayData.getPhysicalBioAssay()</code> and 
-      <code>DerivedBioAssayData.getParent()</code>. Existing derived bioassays are updated.
+      <code>DerivedBioAssayData.getParent()</code>. Existing derived bioassays are updated in a two-step
+      procedure. The first step is to copy existing information to the new tables and the second step is
+      to drop columns that are no longer needed.
     </td>
   </tr>
@@ -250 +252 @@
       if (schemaVersion < 109)
       {
-        if (progress != null) progress.display((int)(progress_current), "--Updating schema version: " + schemaVersion + " -> 109...");
-        schemaVersion = updateToSchemaVersion109(session);
+        if (progress != null) progress.display((int)(progress_current), "--Updating schema version: " + schemaVersion + " -> 110...");
+        schemaVersion = updateToSchemaVersion110(session, schemaVersion);
         progress_current += progress_step;
       }
@@ -626 +628 @@
     old columns in the DerivedBioAssays table.
     
-    @return The new schema version (=109)
+    @return The new schema version (=110)
   */
-  private static int updateToSchemaVersion109(org.hibernate.Session session)
+  private static int updateToSchemaVersion110(org.hibernate.Session session, int currentSchemaVersion)
     throws BaseException
   {
-    final int schemaVersion = 109;
-    try
-    {
-  
-      // TODO - 1707
-      
+    final int schemaVersion = 110;
+    try
+    {
+      if (currentSchemaVersion < 109)
+      {
+        // First step is to copy existing information to the new tables
+        org.hibernate.Transaction tx = null;
+        try
+        {
+          tx = HibernateUtil.newTransaction(session);
+          
+          // Copy parent physical and derived bioassay information to the
+          // new tables: ParentPhysicalBioAssays and ParentDerivedBioAssays
+          org.hibernate.Query query = HibernateUtil.getPredefinedSQLQuery(session, "COPY_PARENT_PHYSICAL_BIOASSAYS");
+          /*
+            INSERT INTO ParentPhysicalBioAssays (physicalbioassay_id, derivedbioassay_id)
+            SELECT bioassay_id, id FROM DerivedBioAssays
+          */
+          query.executeUpdate();
+          query = HibernateUtil.getPredefinedSQLQuery(session, "COPY_PARENT_DERIVED_BIOASSAYS");
+          /*
+            INSERT INTO ParentDerivedBioAssays (parentbioassay_id, derivedbioassay_id)
+            SELECT parent_id, id FROM DerivedBioAssays WHERE NOT parent_id IS NULL
+          */
+          query.executeUpdate();
+          
+          // Set is_root flag on derived bioassays
+          query = HibernateUtil.getPredefinedSQLQuery(session, "SET_ISROOT_ON_DERIVED_BIOASSAYS");
+          /*
+            UPDATE DerivedBioAssays SET is_root = (parent_id IS NULL)
+          */
+          query.executeUpdate();
+          
+          // Remove table filters, etc. that use the old properties
+          cleanContextFromProperty(session, Item.DERIVEDBIOASSAY, "physicalBioAssay", null, true);
+          cleanContextFromProperty(session, Item.DERIVEDBIOASSAY, "parent", null, true);
+          
+          setSchemaVersion(session, 109);
+  
+          // Commit the changes
+          HibernateUtil.commit(tx);
+        }
+        finally
+        {
+          if (tx != null && !tx.wasCommitted()) HibernateUtil.rollback(tx);
+        }
+      }
+      
+      // Second step is to drop the old columns that are no longer used in BASE 3.2
+      dropColumn(session, "DerivedBioAssays", "parent_id", null);
+      dropColumn(session, "DerivedBioAssays", "bioassay_id", null);
+        
       // Update the schema version number
       setSchemaVersionInTransaction(session, schemaVersion);
   
-      log.info("updateToSchemaVersion109: OK");
-    }
-    catch (BaseException ex)
-    {
-      log.error("updateToSchemaVersion109: FAILED", ex);
+      log.info("updateToSchemaVersion110: OK");
+    }
+    catch (SQLException ex)
+    {
+      log.error("updateToSchemaVersion110: FAILED", ex);
+      throw new BaseException(ex);
+    }
+    catch (RuntimeException ex)
+    {
+      log.error("updateToSchemaVersion110: FAILED", ex);
       throw ex;
     }
@@ -957 +1010 @@
       }
       
-      progress.display(90, "  --alter table " + tableName + " drop column " + columnName);
+      if (progress != null)
+      {
+        progress.display(90, "  --alter table " + tableName + " drop column " + columnName);
+      }
       query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_COLUMN", tableName, columnName);
       query.executeUpdate();
@@ -1118 +1174 @@
     {
       log.debug("Cleaning context for item: " + itemType + "; property=" + propertyName + "; replacement=" + replacementPropertyName);
-      tx = HibernateUtil.newTransaction(session);
+      tx = session.getTransaction().isActive() ? null : HibernateUtil.newTransaction(session);
+      
       query = HibernateUtil.createQuery(session, "select ctx from ContextData ctx where ctx.itemType = " + itemType.getValue());
       List<ContextData> contexts = HibernateUtil.loadList(ContextData.class, query, null);
@@ -1148 +1205 @@
         // Filter
         Map<String, PropertyFilterData> filters = ctx.getPropertyFilters();
-        PropertyFilterData propertyFilter = filters.remove(propertyName);
-        if (propertyFilter != null && replacementPropertyName != null && !forceRemoveFilter)
-        {
-          // Replace with new property
-          filters.put(replacementPropertyName, propertyFilter);
-        }
-        
-      }
-      
-      
-      HibernateUtil.commit(tx);
-    }
-    catch (BaseException ex)
-    {
+        List<String> filterProperties = new ArrayList<String>(filters.keySet());
+        for (String property : filterProperties)
+        {
+          if (property.startsWith(propertyName))
+          {
+            PropertyFilterData old = filters.remove(property);
+            if (replacementPropertyName != null)
+            {
+              filters.put(replacementPropertyName + property.substring(propertyName.length()), old);
+            }
+          }
+        }
+      }
+      
+      // Only commit if we started a new transaction
+      if (tx != null) HibernateUtil.commit(tx);
+    }
+    catch (RuntimeException ex)
+    {
+      // Only rollback if we started the transaction
       if (tx != null) HibernateUtil.rollback(tx);
       throw ex;

trunk/www/views/derivedbioassays/index.jsp

@@ -83 +83 @@
   {
     // Register formatters
+    cc.setObject("export.formatter.&physicalBioAssays(name)", new NameableFormatter());
+    cc.setObject("export.formatter.&parents(name)", new NameableFormatter());
     cc.setObject("export.formatter.&children(name)", new NameableFormatter());
     cc.setObject("export.formatter.&rawBioAssays(name)", new NameableFormatter());
@@ -88 +90 @@
     // Register dataloaders
     String bioassayParameter = "bioassay";
+    // Physical bioassays
+    ItemQuery<PhysicalBioAssay> physicalQuery = PhysicalBioAssay.getQuery();
+    physicalQuery.include(cc.getInclude());
+    physicalQuery.join(Hql.innerJoin("derivedBioAssays", "dba"));
+    physicalQuery.restrict(Restrictions.eq(Hql.alias("dba"), Expressions.parameter(bioassayParameter)));
+    physicalQuery.order(Orders.asc(Hql.property("name")));
+    cc.setObject("export.dataloader.&physicalBioAssays(name)", new ItemQueryLoader(physicalQuery, bioassayParameter));
+
+    // Parent bioassays
+    ItemQuery<DerivedBioAssay> parentQuery = DerivedBioAssay.getQuery();
+    parentQuery.include(cc.getInclude());
+    parentQuery.join(Hql.innerJoin("children", "c"));
+    parentQuery.restrict(Restrictions.eq(Hql.alias("c"), Expressions.parameter(bioassayParameter)));
+    parentQuery.order(Orders.asc(Hql.property("name")));
+    cc.setObject("export.dataloader.&parents(name)", new ItemQueryLoader(parentQuery, bioassayParameter));
+
     // Child bioassays
-    ItemQuery<DerivedBioAssay> dbasQuery = DerivedBioAssay.getQuery();
-    dbasQuery.include(cc.getInclude());
-    dbasQuery.restrict(Restrictions.eq(Hql.property("parent"), Expressions.parameter(bioassayParameter)));
-    dbasQuery.order(Orders.asc(Hql.property("name")));
-    cc.setObject("export.dataloader.&children(name)", new ItemQueryLoader(dbasQuery, bioassayParameter));
+    ItemQuery<DerivedBioAssay> childQuery = DerivedBioAssay.getQuery();
+    childQuery.include(cc.getInclude());
+    childQuery.join(Hql.innerJoin("parents", "p"));
+    childQuery.restrict(Restrictions.eq(Hql.alias("p"), Expressions.parameter(bioassayParameter)));
+    childQuery.order(Orders.asc(Hql.property("name")));
+    cc.setObject("export.dataloader.&children(name)", new ItemQueryLoader(childQuery, bioassayParameter));
     
     // Child raw bioassays

trunk/www/views/physicalbioassays/index.jsp

@@ -96 +96 @@
     ItemQuery<DerivedBioAssay> dbasQuery = DerivedBioAssay.getQuery();
     dbasQuery.include(cc.getInclude());
-    dbasQuery.restrict(Restrictions.eq(Hql.property("physicalBioAssay"), Expressions.parameter(bioassayParameter)));
-    dbasQuery.restrict(Restrictions.eq(Hql.property("parent"), null));
+    dbasQuery.join(Hql.innerJoin("physicalBioAssays", "pba"));
+    dbasQuery.restrict(Restrictions.eq(Hql.alias("pba"), Expressions.parameter(bioassayParameter)));
+    dbasQuery.restrict(Restrictions.eq(Hql.property("root"), Expressions.bool(true)));
     dbasQuery.order(Orders.asc(Hql.property("name")));
     cc.setObject("export.dataloader.&rootDerivedBioAssays(name)", new ItemQueryLoader(dbasQuery, bioassayParameter));