Diversity workbench

Collection Hierarchy

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

Project - DwbServices

External Web Services - Integrating Third-Party Data Sources (since version 4.6)

The DWBServices project is designed to interact with a variety of taxonomic and geographic web services. It provides a unified interface for querying, retrieving, and processing data from external APIs. The framework abstracts the complexities of individual web services, enabling easy integration and consistent interaction across the DWB modules.

The architecture of the project is designed to support a modular and extensible system for interacting with taxonomic and geographic web services. It leverages dependency injection for service management, configuration-based customization, and abstract base classes to define common behaviors for services. At the moment the system is divided into two main domains: Taxonomic Services and Geo Services, each with their own abstractions (TaxonomicWebservice, GeoService) and entities (TaxonomicEntity, GeoEntity). The class DwbServiceProviderAccessor acts as a central access point for retrieving specific service implementations based on the service type. The architecture ensures scalability, maintainability, and separation of concerns, making it easy to integrate new services.

Adding a new webservice to a DWB Client

To incorporate a new web service into the DiversityCollection or similar projects, follow these steps:

1. Add new folder under TaxonomicServices or GeoServices.

2. Create Entity Classes: Define entity classes inheriting from TaxonomicEntity or GeoEntity to represent the service’s data structure.

3. Implement Search and Result Models: Create search criteria and result classes inheriting from TaxonomicSearchCriteria/GeoSearchCriteria and TaxonomicSearchResult/GeoSearchResult.

4. Implement the Service: Create a new service class inheriting from TaxonomicWebservice or GeoService. Implement required methods like DwbApiQueryUrlString, GetDwbApiSearchModel, and GetDwbApiDetailModel.

5. Add the Service to Enums and Dictionaries: Add the new service to DwbServiceEnums.DwbService. If the service is a taxonomic service, also add it to the TaxonomicServiceInfoDictionary with its relevant configuration details (e.g., name, URL, dataset key).

6. Register the Service in Program.cs: Add the service to the ConfigureServices method:

     services.AddHttpClient<NewWebservice>();

7. For the DC Client: Update FormRemoteQuery.InitWebserviceControl(): Add the new service to the initialization logic of the web service control in the FormRemoteQuery class.

8. For the DC Client: Update FormSpreadsheet.FixedSourceSetConnection: Ensure the new service is included in the logic for fixed source set connections in the FormSpreadsheet class.

9. Handle Authentication (if required): If the service requires authentication via a bearer token, override the CallWebServiceAsync methods from DwbWebservice and implement token-based authentication within these methods. For an example, refer to the MycobankWebservice implementation.

10. Update Configuration: Add the new service’s base address and other settings to the configuration file.

11. Test the Integration: Validate the service’s functionality by testing its API calls and data mappings.