Migrations

In .NET 8, when a migration attempts to create a table that already exists in the database, the behavior depends on the state of the migration history and the existing database schema:

Initial Migration

When you apply the initial migration, Entity Framework Core (EF Core) creates the specified tables based on your model. If the table already exists in the database, EF Core will not re-create it. It only creates tables that are not present.

Subsequent Migrations

For subsequent migrations (e.g., adding columns, modifying schema), EF Core generates migration scripts based on the difference between the current model and the previous migration’s snapshot. If a table is already in the database and corresponds to the model, EF Core will not attempt to create it again. However, if the table structure differs from the model (e.g., missing columns), EF Core will generate migration scripts to update the schema.

Migration History

EF Core maintains a special table called __EFMigrationsHistory (or __migrations_History in some databases). This table tracks which migrations have been applied to the database. If a migration has already been applied (recorded in this table), EF Core will skip creating the corresponding tables.

Rollback (Down) Method

The Down method in a migration handles rolling back changes. If you need to undo a migration, the Down method drops the corresponding tables. For example, if you remove a column in a migration, the Down method will drop that column.

Manual Truncation or Deletion

Be cautious when manually truncating or deleting tables (including the __EFMigrationsHistory table). If the migration history is lost, EF Core may treat subsequent migrations as initial migrations, leading to re-creation of existing tables. In summary, EF Core is designed to be aware of the existing database schema and avoid unnecessary table creation. Ensure that the migration history is intact, and avoid manual truncation of the migration history table to prevent unexpected behavior during migrations

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

Diversity workbench

Keywords

Keywords in Hugo

Die Schlüsselwörter werden zum Test in der Datei keywords_ori_dwb erfasst und dann angepasst in die Datei keywords_dwb kopiert. Hugo übersetzt die dort angegebenen Adressen. Ein Service auf dem Apacheserver übersetzt dies dann in eine Datei keywords.txt mit Paaren aus Schlüsselwort und dem zugehörigen Link. Dies wird dann als Service zur Verfügung gestellt und kann von den Applikationen benutzt werden.

Keywords in den Applikationen

• die Formulare benötigen einen Helpprovider
• die Keywords werden als Property vermittelt durch den Helpprovider direkt beim Formular oder in den controls eingetragen
• vorhandene Keywords müssen angepasst werden e.g. Collection → collection_dc. Dazu im Designer-File nach helpProvider.SetHelpKeyword suchen und dort die Keywords anpassen.

Event handler

Bei der ersten Verwendung von F1 in einem Formular werden durch den KeyDown-Handler des Formulars das Formular und die enthaltenen Controls sofern ein Keyword eingetragen wurde mit einem Eventhandler versorgt die dann für den Aufruf des Manuals sorgen.

Da die Funktion erst beim KeyDown im Formular gestartet wird und asynchron ist bremst sie den Start der Applikationen nicht.

aktuelle Probleme:

• Beim allerersten Aufruf des Manuals wird auch die Hauptseite für das Formular geöffnet sofern dort ein Keyword hinterlegt ist

Beispiel für Umsetzung anhand DiversityCollection_GUC_8 im Formular FormArtCode:

Code im Formular

in the constructor or in the properties of the form

this.KeyPreview = true;

im Code

link the KeyDown event of the form to the Form_KeyDown event

#region Manual

/// <summary>
/// Adding event deletates to form and controls
/// </summary>
/// <returns></returns>
private async System.Threading.Tasks.Task InitManual()
{
    try
    {

        DiversityWorkbench.DwbManual.Hugo manual = new Hugo(this.helpProvider, this);
        if (manual != null)
        {
            await manual.addKeyDownF1ToForm();
        }
    }
    catch (Exception ex) { DiversityWorkbench.ExceptionHandling.WriteToErrorLogFile(ex); }
}

/// <summary>
/// ensure that init is only done once
/// </summary>
private bool _InitManualDone = false;


/// <summary>
/// KeyDown of the form adding event deletates to form and controls within the form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void Form_KeyDown(object sender, KeyEventArgs e)
{
    try
    {
        if (!_InitManualDone)
        {
            await this.InitManual();
            _InitManualDone = true;
        }
    }
    catch (Exception ex) { MessageBox.Show(ex.Message); }
}

#endregion

In der Workbench aufgerufene Forms

Forms die von den Modulen in der Workbench aufgerufen werden enthalten in obigem Teil zusätzlich eine Funktion zum setzen des Keywords:

public void setKeyword(string Keyword)
{
    this.helpProvider.SetHelpKeyword(this, Keyword);
}

falls diese Formulare weiter abhängige Formulare aufrufen dann kann dies wie unten am Beispiel des Spreadsheets gelöst werden:

public void setKeyword(string Keyword)
{
    this.helpProvider.SetHelpKeyword(this, Keyword);
    this.setKeyword(KeywordTarget.Spreadsheet, Keyword);
}

public void setKeyword(string Keyword, KeywordTarget keywordTarget)
{
    this.setKeyword(keywordTarget, Keyword);

    switch (keywordTarget)
    {
        case KeywordTarget.Map:
            System.Collections.Generic.List<System.Windows.Forms.Control> Controls = new List<System.Windows.Forms.Control>();
            Controls.Add(this.buttonSetMapIcons);
            Controls.Add(this.buttonShowMap);
            Controls.Add(this.toolStripMap);
            ...
            foreach (System.Windows.Forms.Control C in Controls)
            {
                this.helpProvider.SetHelpKeyword(C, Keyword);
            }
            break;
        case KeywordTarget.MapLegend:
            ...
            break;
        case KeywordTarget.MapColors:
        case KeywordTarget.MapSymbols:
            ...
            break;
    }
}

public enum KeywordTarget
{
    Map,
    MapLegend,
    MapColors,
    MapSymbols,
    Spreadsheet
}

private System.Collections.Generic.Dictionary<KeywordTarget, string> _KeywordTargets = new Dictionary<KeywordTarget, string>();
private void setKeyword(KeywordTarget Target, string Keyword)
{
    if (this._KeywordTargets == null)
        this._KeywordTargets = new Dictionary<KeywordTarget, string>();
    if (this._KeywordTargets.ContainsKey(Target))
        this._KeywordTargets[Target] = Keyword;
    else
        this._KeywordTargets.Add(Target, Keyword);
}

private string getKeyword(KeywordTarget Target)
{
    if (this._KeywordTargets == null)
        this._KeywordTargets = new Dictionary<KeywordTarget, string>();
    if (this._KeywordTargets.ContainsKey(Target))
        return this._KeywordTargets[Target];
    else
        return "";
}

Zentraler code in e.g. Klasse Manual

#region Adding functions

/// <summary>
/// If the control contains a keyword related to the helpprovider of the form
/// </summary>
/// <param name="control"></param>
/// <param name="helpProvider"></param>
/// <returns></returns>
private bool IsControlLinkedToHelpKeyword(Control control, HelpProvider helpProvider) 
{ 
    string helpKeyword = helpProvider.GetHelpKeyword(control); 
    return !string.IsNullOrEmpty(helpKeyword); 
}

/// <summary>
/// If the form contains a keyword related to the helpprovider
/// </summary>
/// <param name="form"></param>
/// <param name="helpProvider"></param>
/// <returns></returns>
private bool IsFormLinkedToHelpKeyword(Form form, HelpProvider helpProvider)
{
    string helpKeyword = helpProvider.GetHelpKeyword(form);
    return !string.IsNullOrEmpty(helpKeyword);
}

/// <summary>
/// Constructor
/// </summary>
/// <param name="helpProvider">The helpprovider of the form</param>
/// <param name="form">The form where the event handlers should be added</param>
public Manual(HelpProvider helpProvider, Form form)
{
    _helpProvider = helpProvider;
    _form = form;
}

/// <summary>
/// HelpProvider of the form
/// </summary>
private HelpProvider _helpProvider;

/// <summary>
/// the form containing the HelpProvider
/// </summary>
private System.Windows.Forms.Form _form;

/// <summary>
/// adding the event delegates to form and controls
/// </summary>
/// <returns></returns>
public async Task addKeyDownF1ToForm()
{
    try
    {
        if (_form != null && _helpProvider != null)
        {
            if (IsFormLinkedToHelpKeyword((Form)_form, _helpProvider))
            {
                _form.KeyUp += new KeyEventHandler(form_KeyDown);
            }
            foreach (System.Windows.Forms.Control C in _form.Controls)
            {
                await addKeyDownF1ToControls(C);
            }
        }
    }
    catch (Exception ex) { MessageBox.Show(ex.Message); }
}

/// <summary>
/// Adding Event delegates to the controls
/// </summary>
/// <param name="Cont">the control to which the delegate should be added it a keyword is present</param>
/// <returns></returns>
private async Task addKeyDownF1ToControls(System.Windows.Forms.Control Cont)
{
    try
    {
        foreach (System.Windows.Forms.Control C in Cont.Controls)
        {
            if (IsControlLinkedToHelpKeyword(C, _helpProvider))
            {
                C.KeyDown += new KeyEventHandler(control_KeyDown);
            }
            await addKeyDownF1ToControls(C);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

/// <summary>
/// Opening the manual if F1 is pressed within the form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void form_KeyDown(object sender, KeyEventArgs e)
{
    try
    {
        if (!e.Handled && e.KeyCode == Keys.F1) 
        {
            string Keyword = _helpProvider.GetHelpKeyword(_form);
            OpenManual(Keyword);
        }
    }
    catch (Exception ex) { MessageBox.Show(ex.Message); }
}


/// <summary>
/// Opening the manual if F1 is pressed within the control
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void control_KeyDown(object sender, KeyEventArgs e)
{
    try
    {
        if (e.KeyCode == Keys.F1)
        {
            string Keyword = _helpProvider.GetHelpKeyword((System.Windows.Forms.Control)sender);
            OpenManual(Keyword);
            e.Handled = true;
        }
    }
    catch (Exception ex) { MessageBox.Show(ex.Message); }
}

#endregion

zentrale Funktionen

• holen keyword Verzeichnis via HtmlAgilityPack
• öffnen Webseite anhand von Keyword

vorerst mit HtmlAgilityPack gelöst. Service hierzu kommt noch

#region HtmlAgilityPack

/// <summary>
/// Prefix for websites
/// </summary>
private static readonly string HugoManualUrlPrefix = "https://www.diversityworkbench.de";

/// <summary>
/// The URL of the site containing the keywords
/// </summary>
private static readonly string _UrlForKeywords = "https://www.diversityworkbench.de/manual/dwb_latest/diversityworkbench/keywords_dwb/index.html";

private static Dictionary<string, string> _KeywordsFromHugo;

/// <summary>
/// The dictionary of the keyword/Links scraped from the website in the Hugo manual
/// </summary>
private static Dictionary<string, string> KeywordsFromHugo
{
    get
    {
        if (_KeywordsFromHugo == null)
        {
            HtmlWeb HtmlWeb = new HtmlWeb();
            HtmlAgilityPack.HtmlDocument htmlDocument = HtmlWeb.Load(_UrlForKeywords);
            var Links = htmlDocument.DocumentNode.SelectNodes("//html//body//div//main//div//article//ul//a");
            if (Links != null)
            {
                _KeywordsFromHugo = new Dictionary<string, string>();
                foreach (var Link in Links)
                {
                    string Key = Link.InnerText;
                    string URL = HugoManualUrlPrefix + Link.GetAttributeValue("href", string.Empty);
                    if (Key != null && Key.Length > 0 && 
                        URL != null && URL.Length > 0 && 
                        !_KeywordsFromHugo.ContainsKey(Key))
                    {
                        _KeywordsFromHugo.Add(Key, URL);
                    }
                }
            }
        }
        return _KeywordsFromHugo;
    }
}

/// <summary>
/// Open a manual site
/// </summary>
/// <param name="keyword">The keyword linked to the site in the manual</param>
public void OpenHugoManual(string keyword)
{
    if (KeywordsFromHugo == null) { return; }
    if (KeywordsFromHugo.ContainsKey(keyword))
    {
        string Link = DiversityCollection_GUC.Hugo.Manual.KeywordsFromHugo[keyword];
        try
        {
            if (Link.Length > 0)
            {
                Process.Start(new ProcessStartInfo
                {
                    FileName = Link,
                    UseShellExecute = true
                });
            }
        }
        catch (Exception ex) { MessageBox.Show("Error opening URL: " + ex.Message); }
    }
}



#endregion

graph TD;
    Start[Open form]
    Handler["Creation of handlers"]
    Manual[Open the manual]
    F1[Click F1 in form or control]
    Scrape[Using the<br>***HtmlAgilityPack***<br>to scrape the dictionary<br>for the keywords<br>from the<br>Hugo keyword site<br>at first call]
    Key[Use keyword to get link]
    Start --> | using F1 in the form for the first time | Handler 
    Handler --> Scrape
    F1 --> Scrape
    Scrape --> Key
    Key --> Manual

Alternative nicht verwendete Optionen

Im frontmatter der Seiten können für einzelne Kapitel Schlüsselwörter hinterlegt werden:

---
title: ...
linktitle: ...
keywords: 
    - Nachweis_GUC
    - Fundort_GUC
---

Was wir benötigen ist ein Schlüsselwort in der Applikation und dazu ein Link zum Manual. Aus den CHM Dateien lassen sich die bereits jetzt vorhandenen Schlüssel extrahieren (Außer den von Wolfgang umgebauten Modulen DSP und DG).

Eine Alternative zum manuellen Aufbau wäre ein Scan aller vorhandenen Seiten und die Extraktion der verwendeten Links.

Options

Links in manual

Examlpe for GUC

• artcode_guc
• download_guc
• internals_guc
• nachweis_guc
• tk25_guc

damit werden automatisch die korrekten Links im html erzeugt und könnten dann extrahiert werden

Aktuellen Liste: keywords_dwb

Links extracted from chm from DA

Contact_DA modules/diversitycollection/Contact_DA Agent_DA modules/diversitycollection/Agent_DA Archive_DA modules/diversitycollection/Archive_DA Backup_DA modules/diversitycollection/Backup_DA Contact_DA modules/diversitycollection/Contact_DA DataWithholding_DA modules/diversitycollection/DataWithholding_DA AgentDisplayTypes_DA modules/diversitycollection/AgentDisplayTypes_DA Descriptors_DA modules/diversitycollection/Descriptors_DA ExternalData_DA modules/diversitycollection/ExternalData_DA Feedback_DA modules/diversitycollection/Feedback_DA Hierarchy_DA modules/diversitycollection/Hierarchy_DA History_DA modules/diversitycollection/History_DA Images_DA modules/diversitycollection/Images_DA …

hier müssen die Links noch angepasst werden. Insofern noch nicht wirklich brauchbar

Project - DwbServices

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

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.