Changeset 6741


Ignore:
Timestamp:
Feb 17, 2015, 11:38:46 AM (7 years ago)
Author:
Nicklas Nordborg
Message:

References #1325: Lists of items (similar funcion as biomaterial lists)

Adding functionality to keep item lists up to date when deleting items from the database. Since this should be possible we must check/update two things:

  • The size of item lists must be decreased if an item in the list is deleted
  • The entry in the ItemListMembers table must be deleted


This is done in BasicItem.onBeforeCommit() for all Listable items.

Location:
trunk/src
Files:
3 edited

Legend:

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

    r6728 r6741  
    26242624  </query>
    26252625 
     2626  <query id="UPDATE_ITEMLIST_SIZE" type="SQL">
     2627    <sql>
     2628      UPDATE [ItemLists]
     2629      SET [size] = [size] + :delta
     2630      WHERE [member_type] = :memberType
     2631      AND [id] = ANY(
     2632        SELECT [list_id]
     2633        FROM [ItemListMembers]
     2634        WHERE [item_id] = :itemId
     2635      )
     2636    </sql>
     2637    <description>
     2638      An update query that changes the size for all item lists
     2639      where a given item is a member. Mainly used to decrease
     2640      the size when an item is deleted.
     2641    </description>
     2642  </query>
     2643 
     2644  <query id="DELETE_ITEMLIST_MEMBER" type="SQL">
     2645    <sql>
     2646      DELETE FROM [ItemListMembers]
     2647      WHERE [item_id] = :itemId
     2648      AND [list_id] = ANY(
     2649        SELECT [id]
     2650        FROM [ItemLists]
     2651        WHERE [member_type] = :memberType
     2652      )
     2653    </sql>
     2654    <description>
     2655      An update query that delete the list member entries
     2656      for all lists where the given item is a member.
     2657    </description>
     2658  </query>
     2659 
    26262660  <query id="GET_BIOMATERIAL_ON_PLATE" type="HQL">
    26272661    <sql>
  • trunk/src/core/net/sf/basedb/core/BasicItem.java

    r6465 r6741  
    433433      query.setInteger("toType", this.getType().getValue());
    434434      HibernateUtil.executeUpdate(query);
     435     
     436      // Make sure ItemList.size is up to date
     437      if (this instanceof Listable)
     438      {
     439        query = HibernateUtil.getPredefinedSQLQuery(session,
     440          "UPDATE_ITEMLIST_SIZE");
     441          /*
     442            UPDATE [ItemLists]
     443            SET [size] = [size] + :delta
     444            WHERE [member_type] = :memberType
     445            AND [id] = ANY(
     446              SELECT [list_id]
     447              FROM [ItemListMembers]
     448              WHERE [item_id] = :itemId
     449            )
     450          */
     451        query.setInteger("delta", -1);
     452        query.setInteger("itemId", getId());
     453        query.setInteger("memberType", getType().getValue());
     454        int numUpdated = HibernateUtil.executeUpdate(query);
     455        if (numUpdated > 0)
     456        {
     457          // Delete rows from ItemListMembers table
     458          query = HibernateUtil.getPredefinedSQLQuery(session,
     459            "DELETE_ITEMLIST_MEMBER");
     460            /*
     461              DELETE FROM [ItemListMembers]
     462              WHERE [item_id] = :itemId
     463              AND [list_id] = ANY(
     464                SELECT [id]
     465                FROM [ItemLists]
     466                WHERE [member_type] = :memberType
     467              )
     468            */
     469          query.setInteger("itemId", getId());
     470          query.setInteger("memberType", getType().getValue());
     471          int numMembers = query.executeUpdate();
     472        }
     473      }
    435474    }
    436475  }
  • trunk/src/test/TestItemList.java

    r6739 r6741  
    3535import net.sf.basedb.core.ItemResultList;
    3636import net.sf.basedb.core.Listable;
    37 import net.sf.basedb.core.Nameable;
    3837import net.sf.basedb.core.Permission;
    3938
     
    8988   
    9089    if (TestUtil.waitBeforeDelete()) TestUtil.waitForEnter();
     90   
     91    // Remove an item and check that the size property is correct
     92    TestFile.test_delete(f3);
     93    test_list_members(id7, 2);
     94   
    9195    test_remove_member(id1, bs1);
    9296    test_remove_member(id1, bs2);
    9397    test_list_members(id1, 1);
     98    if (TestUtil.waitBeforeDelete()) TestUtil.waitForEnter();
    9499   
    95100    // Standard test: Delete
     
    103108    TestFile.test_delete(f1);
    104109    TestFile.test_delete(f2);
    105     TestFile.test_delete(f3);
    106110    TestBioSource.test_delete(bs1);
    107111    TestBioSource.test_delete(bs2);
     
    244248      bl.getDescription()+"\t"+bl.getExternalId()+"\t"+bl.getSize());
    245249  }
    246   static void write_item(int i, Nameable bm)
     250  static void write_item(int i, Listable bm)
    247251    throws BaseException
    248252  {
     
    337341      for (int i = 0; i<l.size(); i++)
    338342      {
    339         write_item(i, (Nameable)l.get(i));
     343        write_item(i, l.get(i));
     344      }
     345      if (bl.getSize() != l.size())
     346      {
     347        String msg = "List size is " + bl.getSize() + " but query returned " + l.size() + " members";
     348        if (expectedResults >= 0)
     349        {
     350          msg += " (expected " + expectedResults +")";
     351        }
     352        throw new BaseException(msg);
    340353      }
    341354      if (expectedResults >= 0 && expectedResults != l.size())
Note: See TracChangeset for help on using the changeset viewer.