Changeset 3086

Timestamp:

Jan 29, 2007, 2:45:24 PM (16 years ago)

Author:

Nicklas Nordborg

Message:

Fixes #466: Remaining Quantity not display in extracts

Location:

trunk/src/core

Files:

• common-queries.xml (modified) (3 diffs)
• net/sf/basedb/core/DbControl.java (modified) (9 diffs)
• net/sf/basedb/core/Install.java (modified) (1 diff)
• net/sf/basedb/core/MeasuredBioMaterial.java (modified) (1 diff)
• net/sf/basedb/core/Update.java (modified) (5 diffs)

trunk/src/core/common-queries.xml

@@ -1959 +1959 @@
     </sql>
     <description>
-      A Hibernate query that updates the remaining quantity on a
+      A Hibernate query that adds/removes the remaining quantity on a
       measuered biomaterial.
     </description>
   </query>
+
+  <query id="SET_REMAINING_QUANTITY" type="HQL">
+    <sql>
+      UPDATE MeasuredBioMaterialData mbm 
+      SET mbm.remainingQuantity = :remain
+      WHERE mbm = :bioMaterial
+    </sql>
+    <description>
+      A Hibernate query that sets the remaining quantity on a
+      measuered biomaterial.
+    </description>
+  </query>
+
 
   <query id="GET_ANYTOANY_FOR_NAME" type="HQL">
@@ -2499 +2512 @@
   </query>
 
-
   <query id="SET_MAX_MAPPING_ON_DATACUBE" type="HQL">
     <sql>
@@ -2511 +2523 @@
   </query>
 
+  <query id="GET_SUM_USED_QUANTITY_EVENTS" type="HQL">
+    <sql>
+      SELECT evt.bioMaterial, SUM(evt.usedQuantity)
+      FROM BioMaterialEventData evt
+      GROUP BY evt.bioMaterial
+    </sql>
+    <description>
+      A HQL query that loads the sum of the used quantity 
+      grouped by the biomaterial for all BioMaterialEvent:s.
+    </description>
+  </query>
+  
+  <query id="GET_SUM_USED_QUANTITY_SOURCES" type="HQL">
+    <sql>
+      SELECT index(src), SUM(src.usedQuantity)
+      FROM BioMaterialEventData evt
+      JOIN evt.sources src
+      GROUP BY index(src)
+    </sql>
+    <description>
+      A HQL query that loads the sum of the used quantity 
+      grouped by the biomaterial for all sources to
+      BioMaterialEvent:s.
+    </description>
+  </query>
+
 </predefined-queries>

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

@@ -34 +34 @@
 import net.sf.basedb.util.ClassUtil;
 
+import java.util.IdentityHashMap;
 import java.util.Iterator;
 import java.util.LinkedList;
@@ -133 +134 @@
     hSession = HibernateUtil.newSession();
     hTransaction = HibernateUtil.newTransaction(hSession);
-    itemCache = new HashMap<BasicData,BasicItem>();
+    itemCache = new IdentityHashMap<BasicData, BasicItem>();
     commitQueue = new LinkedHashMap<BasicItem,Transactional.Action>();
     isClosed = false;
@@ -368 +369 @@
           {
             HibernateUtil.saveData(hSession, data);
-            itemCache.put(data, item);
             item.onAfterInsert();
           }
@@ -377 +377 @@
             // Need flush so directories can be deleted recursiveley
             if (item instanceof Directory || item instanceof File) HibernateUtil.flush(hSession);
-            itemCache.remove(data);
           }
           if (action != Transactional.Action.UPDATE || transactional) afterCommitQueue.put(item, action);
@@ -652 +651 @@
       assert itemConstructor != null : "itemConstructor == null";     
       I item = itemConstructor.newInstance(constructorParams);
-      item.setDbControl(this);          
+      item.setDbControl(this);  
+      itemCache.put(data, item);
       return item;      
     }
@@ -776 +776 @@
         {
           item = c.newInstance(constructorParams);
+          itemCache.put(data, item);
         }
         catch (Exception ex)
@@ -791 +792 @@
     item.initPermissions(0, 0);
     item.checkPermission(Permission.READ);
-    itemCache.put(data, item);
     if (item instanceof Controlled)
     {
@@ -886 +886 @@
       }
       items.add(new SaveIf(item, before));
-      itemCache.put(item.getData(), item);
     }
   }
@@ -978 +977 @@
   /**
     Check if an item is attached to this <code>DbControl</code>.
-    An item is attached if it is found in either the item cache
-    or commit queue.
+    An item is attached if it is found in either the item cache (existing items 
+    only) or commit queue (new or existing items).
   */
   boolean isAttached(BasicItem item)
   {
     assert item != null : "item == null";
-    return itemCache.containsKey(item.getData()) || commitQueue.containsKey(item);
+    return commitQueue.containsKey(item) || (itemCache.containsKey(item.getData()) && item.isInDatabase());
   }
   

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

@@ -102 +102 @@
     method.
   */
-  public static final int NEW_SCHEMA_VERSION = 28;
+  public static final int NEW_SCHEMA_VERSION = 29;
 
   public static synchronized void createTables(boolean update, final ProgressReporter progress)

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

@@ -219 +219 @@
       // For unsaved, objects we must use the object itself
       Float remain = bioMaterial.getRemainingQuantity();
-      if (remain == null) return;
+      if (remain == null) remain = 0.0f;
       bioMaterial.setRemainingQuantity(remain-usedQuantity);
     }

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

@@ -29 +29 @@
 import java.sql.Statement;
 import java.util.Collections;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
 
 import org.hibernate.mapping.Table;
@@ -35 +37 @@
 import net.sf.basedb.core.data.DataCubeData;
 import net.sf.basedb.core.data.FormulaData;
+import net.sf.basedb.core.data.MeasuredBioMaterialData;
 import net.sf.basedb.core.data.PlateData;
 import net.sf.basedb.core.data.PlateMappingData;
@@ -338 +341 @@
       The update must query the dynamic database and set this value for
       all existing data cubes.
+    </td>
+  </tr>
+
+  <tr>
+    <td>29</td>
+    <td>
+      No schema change as such, but we need to verify and possibly update the
+      {@link net.sf.basedb.core.data.MeasuredBioMaterialData#getRemainingQuantity()}
+      since a bug may have caused it to have an incorrect value.
     </td>
   </tr>
@@ -484 +496 @@
       {
         if (progress != null) progress.display((int)(27*progress_factor), "--Updating schema version: " + schemaVersion + " -> 28...");
-        updateToSchemaVersion28(sc);
+        schemaVersion = updateToSchemaVersion28(sc);
+      }
+      
+      if (schemaVersion < 29)
+      {
+        if (progress != null) progress.display((int)(28*progress_factor), "--Updating schema version: " + schemaVersion + " -> 29...");
+        schemaVersion = updateToSchemaVersion29(session);
       }
       
       /*
-      if (schemaVersion < 29)
-      {
-        if (progress != null) progress.display((int)(28*progress_factor), "--Updating schema version: " + schemaVersion + " -> 29...");
-        schemaVersion = setSchemaVersionInTransaction(session, 29);
+      if (schemaVersion < 30)
+      {
+        if (progress != null) progress.display((int)(29*progress_factor), "--Updating schema version: " + schemaVersion + " -> 30...");
+        schemaVersion = setSchemaVersionInTransaction(session, 30);
         - or - 
-        schemaVersion = updateToSchemaVersion29(session);
+        schemaVersion = updateToSchemaVersion30(session);
       }
       ... etc... 
@@ -1051 +1069 @@
   }
 
-
+  /**
+    Vefify and update the remaining quantity of all biomaterials.
+    @return The new schema version (=29)
+  */
+  private static int updateToSchemaVersion29(org.hibernate.Session session)
+    throws BaseException
+  {
+    final int schemaVersion = 29;
+    
+    org.hibernate.Transaction tx = null;
+    try
+    {
+      tx = HibernateUtil.newTransaction(session);
+      
+      // For temporary storing the new remaining quantities
+      Map<MeasuredBioMaterialData, Float> remaining = new HashMap<MeasuredBioMaterialData, Float>(); 
+      
+      // Load sum of used quantity for all events
+      org.hibernate.Query eventQuery = HibernateUtil.getPredefinedQuery(session, 
+        "GET_SUM_USED_QUANTITY_EVENTS");
+      /*
+        SELECT evt.bioMaterial, SUM(evt.usedQuantity)
+        FROM BioMaterialEventData evt
+        GROUP BY evt.bioMaterial
+      */
+      // Load sum of used quantity for sources to all events
+      org.hibernate.Query sourcesQuery = HibernateUtil.getPredefinedQuery(session, 
+        "GET_SUM_USED_QUANTITY_SOURCES");
+        /*
+          SELECT index(src), SUM(src.usedQuantity)
+          FROM BioMaterialEventData evt
+          JOIN evt.sources src
+          GROUP BY index(src)
+         */
+      List<Object[]> events = HibernateUtil.loadList(Object[].class, eventQuery);
+      events.addAll(HibernateUtil.loadList(Object[].class, sourcesQuery));
+      
+      // Calculate the remaining quantity for each biomaterial
+      // o[0] = biomaterial, o[1] = sum of used quantity
+      for (Object[] o : events)
+      {
+        MeasuredBioMaterialData bioMaterial = (MeasuredBioMaterialData)o[0];
+        Double sumUsedQuantity = (Double)o[1];
+        if (sumUsedQuantity != null)
+        {
+          Float currentRemaining = remaining.get(bioMaterial);
+          if (currentRemaining == null) currentRemaining = 0.0f;
+          currentRemaining = currentRemaining - sumUsedQuantity.floatValue();
+          remaining.put(bioMaterial, currentRemaining);
+        }
+      }
+      
+      // Update the remaining quantity
+      org.hibernate.Query updateQuery = HibernateUtil.getPredefinedQuery(session, 
+        "SET_REMAINING_QUANTITY");
+      /*
+        UPDATE MeasuredBioMaterialData mbm 
+        SET mbm.remainingQuantity = :remain
+        WHERE mbm = :bioMaterial
+      */
+      for (Map.Entry<MeasuredBioMaterialData, Float> entry : remaining.entrySet())
+      {
+        MeasuredBioMaterialData bioMaterial = entry.getKey();
+        Float remainingQuantity = entry.getValue();
+        updateQuery.setEntity("bioMaterial", bioMaterial);
+        updateQuery.setFloat("remain", remainingQuantity);
+        updateQuery.executeUpdate();
+      }
+      
+      // Update the shcema version number
+      setSchemaVersion(session, schemaVersion);
+  
+      // Commit the changes
+      HibernateUtil.commit(tx);
+      log.info("updateToSchemaVersion29: OK");
+    }
+    catch (BaseException ex)
+    {
+      if (tx != null) HibernateUtil.rollback(tx); 
+      log.error("updateToSchemaVersion29: FAILED", ex);
+      throw ex;
+    }
+    return schemaVersion;
+  }
+  
   /**
     Adjust the existing items in the database to be compatible with the latest mappings.