Diversity workbench

Collection Hierarchy (upcoming version)

Extracting hierarchies can be a time-consuming process in certain cases. To optimize the performance of queries involving collection hierarchies, we use a combination of Collection and CollectionClosure tables. This method is based on the Closure Table Pattern, which is widely used for representing hierarchical data in relational databases.

The Collection and CollectionClosure table

The Collection Table:

  • Represents the main entities in the hierarchy.
  • Stores basic information about each node (e.g., CollectionID, CollectionParentID, CollectionName, etc.).
  • Contains a CollectionParentID column to define direct parent-child relationships.

The CollectionClosure Table:

  • Represents all ancestor-descendant relationships in the hierarchy.
  • Stores the depth of each relationship (e.g., direct parent-child = depth 1, grandparent-grandchild = depth 2).
  • Allows efficient querying of hierarchical data.

Updating process for the Collection and CollectionClosure tables

Maintaining the integrity and consistency of the hierarchy during updates is critical. To achieve this, we use three triggers (INSERT, UPDATE, and DELETE) on the Collection table. These triggers automatically update the CollectionClosure table to reflect changes in the hierarchy.

The triggers are defined on the Collection table and handle the following operations:

  • INSERT Trigger: Handles the insertion of new collections, ensuring that both root and child relationships are added to the CollectionClosure table.
  • UPDATE Trigger: Handles updates to the ParentID of a collection, updating the hierarchy to reflect the new parent-child relationships.
  • DELETE Trigger: Handles the deletion of collections, removing all relationships involving the deleted collection and its descendants.

INSERT Trigger

The INSERT trigger ensures that when a new collection is added to the Collection table:

  • A self-referencing row is added to the CollectionClosure table (CollectionID | CollectionID | 0).
  • If the new collection has a parent, all ancestor-descendant relationships are added to the CollectionClosure table.
CREATE TRIGGER trg_InsertCollectionUpdateCollectionClosure
ON Collection
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Insert self-referencing row
    INSERT INTO CollectionClosure (AncestorID, DescendantID, Depth)
    SELECT 
        i.CollectionID, -- AncestorID
        i.CollectionID, -- DescendantID
        0               -- Depth
    FROM INSERTED i;
    -- Insert parent-child relationships
    INSERT INTO CollectionClosure (AncestorID, DescendantID, Depth)
    SELECT 
        p.AncestorID,   -- AncestorID
        i.CollectionID, -- DescendantID
        p.Depth + 1     -- Depth
    FROM CollectionClosure p
    INNER JOIN INSERTED i ON p.DescendantID = i.CollectionParentID;
END;

UPDATE Trigger

The UPDATE trigger ensures that when the ParentID of a collection is updated:

  • All old relationships involving the collection and its descendants are removed from the CollectionClosure table.
  • New relationships are added to reflect the updated parent-child hierarchy.
CREATE TRIGGER trg_UpdateCollectionUpdateCollectionClosure
ON Collection
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- Delete old relationships
    DELETE FROM CollectionClosure
    WHERE DescendantID IN (
        SELECT DescendantID
        FROM CollectionClosure
        WHERE AncestorID IN (SELECT CollectionID FROM DELETED)
    )
    AND AncestorID IN (
        SELECT AncestorID
        FROM CollectionClosure
        WHERE DescendantID IN (SELECT CollectionID FROM DELETED)
        AND AncestorID != DescendantID
    );
    -- Insert new relationships
    INSERT INTO CollectionClosure (AncestorID, DescendantID, Depth)
    SELECT 
        supertree.AncestorID, -- New ancestor
        subtree.DescendantID, -- Descendant
        supertree.Depth + subtree.Depth + 1 -- New depth
    FROM CollectionClosure AS supertree
    CROSS JOIN CollectionClosure AS subtree
    INNER JOIN INSERTED i ON subtree.AncestorID = i.CollectionID
    WHERE supertree.DescendantID = i.ParentID;
END;

DELETE Trigger

The DELETE trigger ensures that when a collection is deleted:

  • All relationships involving the deleted collection and its descendants are removed from the CollectionClosure table.
CREATE TRIGGER trg_DeleteCollection
ON Collection
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- Delete relationships for the deleted collection and its descendants
    DELETE FROM CollectionClosure
    WHERE DescendantID IN (
        SELECT DescendantID
        FROM CollectionClosure
        WHERE AncestorID IN (SELECT CollectionID FROM DELETED)
    );
END;

Considerations

  • The triggers ensure that the CollectionClosure table is always updated consistently without requiring explicit calls from the application.

  • Ensure that the ParentID column does not allow circular references, as this can cause infinite loops in the hierarchy. The Client DC checks for loops before inserting a Collection.

Access rights to Collection

By default, all users have read access to all collections, e.g., in the selection lists. The CollectionManager can also edit collections via the menu item “Management - Collections,” etc.

CollectionManager

Collections for which they have editing rights can be assigned to them via Management - Collection - CollectionManager. If a collection has child collections, the rights are inherited, i.e., if a user has rights for the parent collection, they automatically also have rights for the child collections.

Only Administrators can assign editing rights.

Also see CollectionManager

The CollectionManager Table is updated via two triggers (trg_InsertCollectionManager and trg_DeleteCollectionManager), which are designed to automatically manage entries in the CollectionManager table whenever rows are inserted into or deleted from the Collection table. This ensures that the CollectionManager table remains consistent with the Collection table.

ALTER TRIGGER [dbo].[trg_InsertCollectionManager]
ON [dbo].[Collection]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Insert into CollectionManager for the user who inserted the row
    INSERT INTO CollectionManager (LoginName, AdministratingCollectionID)
    SELECT 
        SUSER_SNAME(), -- Gets the username of the user performing the insert
        i.CollectionID
    FROM 
        INSERTED i;
END;
ALTER TRIGGER [dbo].[trg_DeleteCollectionManager]
ON [dbo].[Collection]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- Delete from CollectionManager where the CollectionID matches the deleted CollectionID
    DELETE FROM CollectionManager
    WHERE AdministratingCollectionID IN (
        SELECT d.CollectionID
        FROM DELETED d
    );
END;

CollectionUser

The second table, CollectionUser, is used like a ’lock list,’ meaning it contains entries for users and collections to which a user has explicit read access. In this case, the user has access only to these collections. If there are no entries for a user in this table, they have read access to all collections.

Also see CollectionUser**

Implemented in:

DC

CollectionHierarchy