Diversity Projects

TABLES, VIEWS, FUNCTIONS, PROCEDURES, ROLES

The following objects are not included:

• Logging tables
• Enumeration tables
• System objects
• Objects marked as obsolete
• Previous versions of objects

TABLES

Table Project

Information on the project

Depending on:

• ProjectType_Enum

Table ProjectAgent

The persons or institutions involved in the project

Depending on:

• Project
• ProjectAgentType_Enum

Table ProjectAgentRole

The role of the person or institution involved in the project

Depending on:

• ProjectAgent
• ProjectAgentRole_Enum

Table ProjectArchive

Archives of the project

Depending on:

• Project
• ProjectArchiveFormat_Enum
• ProjectArchiveMimeType_Enum

Table ProjectArchiveIdentifier

Identifier of archives of the project

Depending on:

• ProjectArchive
• ProjectIdentifier

Table ProjectDescriptor

Object names, event names, keywords, etc., providing indexing information for a resource.

Depending on:

• Project
• ProjectDescriptorElement

Table ProjectDescriptorElement

Examples of descriptor elements (= concepts for variables) are keyword, taxon name, pathogen name, host name, or host feature. Association with ResourceCollections is defined in ProjectDescriptorAssociation, relations in Res.DescriptorElementRelation.

Table ProjectIdentifier

External identifiers for the project, e.g. a DOI

Depending on:

• Project
• ProjectIdentifierType

Table ProjectIdentifierType

The type of an external identier, e.g. DOI

Table ProjectLicense

The Licenses within a project

Depending on:

• Project

Table ProjectReference

The references related to the project

Depending on:

• Project

Table ProjectResource

Resources of the project

Depending on:

• Project

Table ProjectSetting

Settings of a project

Depending on:

• Project
• Setting

Table ProjectUser

The projects available for a user

Depending on:

• Project
• UserProxy

Table Setting

The settings used within the projects

VIEWS

View Project_Core

Provides all columus of table Project restricted to the projects accessible for a user as set in table ProjectUser with the exception of the dbo

Depending on:

• Project
• ProjectList

View ProjectAgent_Core

Content of table ProjectAgent restricted to user accessible datasets

Depending on:

• ProjectAgent
• ProjectList

View ProjectResource_Core

Content of table ProjectResource restricted to those accessible for a user

Depending on:

• ProjectList
• ProjectResource

View View_ProjectDescriptor

Project descriptors for query

Depending on:

• ProjectDescriptor
• ProjectDescriptorElement

View View_ProjectSetting

Project settings for query

Depending on:

• ProjectSetting
• Setting

View ViewBaseURL

Value of the function BaseURL provided via a view for usage e.g. by linked servers

Depending on:

• BaseURL

View ViewDataCitation

The citation of published data from the project; references of the data publication of the project data package

Depending on:

• ProjectReference

View ViewDiversityWorkbenchModule

Value of the function DiversityWorkbenchModule provided via a view for usage e.g. by linked servers

Depending on:

• DiversityWorkbenchModule

View ViewLiteratureReference

The references related to the project; references of literature publications where information on the project and/ or on project dataset(s) is given

Depending on:

• ProjectReference

View ViewProject

Content of table Project including the StableIdentifier

Depending on:

• Project
• StableIdentifier

FUNCTIONS

Function BaseURL

Provides the basic address for accessing the database

DataType: varchar (255)

Function DiversityWorkbenchModule

Provides the name of the DiversityWorkbench module

DataType: nvarchar (50)

Function PrivacyConsentInfo

Providing common information about the storage and processing of personal data within the DiversityWorkbench

DataType: varchar (900)

Function ProjectChildNodes

Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item.

Depending on:

• Project
• ProjectChildNodes

Function ProjectDataLastChanges

DataType: datetime

Depending on:

• Project

Function ProjectHierarchy

Returns a table that lists all the items related to the given item

Depending on:

• Project
• ProjectChildNodes

Function ProjectList

The list of Projects accessible for the current user

Depending on:

• Project
• ProjectUser

Function ProjectParents

Returns a table that lists all the parents of the given project

Depending on:

• Project

Function SettingHierarchyAll

Returns a table that lists all the settings including a display text with the whole hierarchy

Depending on:

• Setting

Function SettingHierarchyChildren

Returns a table that lists all the settings depending on a setting

Depending on:

• Setting

Function SettingHierarchyNonProject

Returns a table that lists all the settings including a display text with the whole hierarchy that are not included in a project so far

Depending on:

• ProjectSetting
• SettingHierarchyAll

Function SettingHierarchyProjectAll

Returns a table that lists all the settings for a project including those inherited from superior projects.

Depending on:

• ProjectParents
• ProjectSetting
• SettingHierarchyAll

Function SettingsForProject

Returns a table that lists all the settings for a project including those inherited from parent projects including formatting options. Used for Metadata retrieval by DiversityCollection cache databases

Depending on:

• SettingHierarchyProjectAll

Function StableIdentifier

The stable identifier published for a project

DataType: varchar (500)

Depending on:

• Project

Function UserID

ID of the User as stored in table UserProxy

DataType: int

Depending on:

• UserProxy

Function Version

Version of the database

DataType: nvarchar (8)

Function VersionClient

Version of the client software compatible with the version of the database

DataType: nvarchar (11)

PROCEDURES

Procedure procInsertProjectCopy

Copy project including all depending data

Depending on:

• Project
• ProjectAgent
• ProjectLicense
• ProjectReference
• ProjectResource
• ProjectSetting
• ProjectUser

Procedure SetUserProjects

Depending on:

• UserProxy

ROLES

Role DiversityWorkbenchAdministrator

Database Role for administrative actions within the database.

• DiversityWorkbenchEditor

Role DiversityWorkbenchEditor

Database Role with read/write accesss to the database.

• DiversityWorkbenchUser

Role DiversityWorkbenchUser

Database Role with reading accesss to the database.

Diversity Projects

Enumeration tables

The following objects are not included:

• Logging tables
• System objects
• Objects marked as obsolete
• Previous versions of objects

Table

Table Language_Enum

Language codes used within the database

Table ProjectAgentRole_Enum

Roles of the agents

Dependent tables:

• ProjectAgentRole

Table ProjectAgentType_Enum

Types of the agents

Dependent tables:

• ProjectAgent

Table ProjectArchiveFormat_Enum

Standards for the created archives, e.g. EML

Dependent tables:

• ProjectArchive

Table ProjectArchiveMimeType_Enum

The MIME (accoridig to the Multipurpose Internet Mail Extensions specification) types of the created archives

Dependent tables:

• ProjectArchive

Table ProjectType_Enum

Type of the project, e.g. corresponding to a DWB module

Dependent tables:

• Project

DiversityProjects

Model 2.1.24

Introduction:

DiversityProjects is part of the Diversity Workbench. Within this framework the application DiversityProject is the module in which the projects are managed. Projects are used e.g. to bundle specimens or observation in virtual collections. It also allows to model a project hierarchy, but the main use is to manage a project’s metadata and to define the access to the data associated with a project. A project is the basis for the data publication pipelines in Diversity Workbench and the metadata of a project is for example included in the data publication.

All other modules, e.g. DiversityCollection, access DiversityProjects to retrieve the project metadata. DiversityProjects, in turn, accesses other modules such as DiversityAgents to store and link information about e.g. project participants.

The modules communicate with each other to provide their services for the other modules.

Data management sectors of the application

Information model

The following sections define the information model used in DiversityProjects. First the ER-diagram is presented to give an overview over the relations between the entities/tables. Then the structure is separately documented for each table with textual information which attributes are stored in which column.

ER-Diagram

Overview over all entities and relations used in the database model

TABLES

Table Project

Information on the project

Depending on:

• ProjectType_Enum

Table ProjectAgent

The persons or institutions involved in the project

Depending on:

• Project
• ProjectAgentType_Enum

Table ProjectAgentRole

The role of the person or institution involved in the project

Depending on:

• ProjectAgent
• ProjectAgentRole_Enum

Table ProjectArchive

Archives of the project

Depending on:

• Project
• ProjectArchiveFormat_Enum
• ProjectArchiveMimeType_Enum

Table ProjectArchiveIdentifier

Identifier of archives of the project

Depending on:

• ProjectArchive
• ProjectIdentifier

Table ProjectDescriptor

Object names, event names, keywords, etc., providing indexing information for a resource.

Depending on:

• Project
• ProjectDescriptorElement

Table ProjectDescriptorElement

Examples of descriptor elements (= concepts for variables) are keyword, taxon name, pathogen name, host name, or host feature. Association with ResourceCollections is defined in ProjectDescriptorAssociation, relations in Res.DescriptorElementRelation.

Table ProjectIdentifier

External identifiers for the project, e.g. a DOI

Depending on:

• Project
• ProjectIdentifierType

Table ProjectIdentifierType

The type of an external identier, e.g. DOI

Table ProjectLicense

The Licenses within a project

Depending on:

• Project

Table ProjectReference

The references related to the project

Depending on:

• Project

Table ProjectResource

Resources of the project

Depending on:

• Project

Table ProjectSetting

Settings of a project

Depending on:

• Project
• Setting

Table ProjectUser

The projects available for a user

Depending on:

• Project
• UserProxy

Table Setting

The settings used within the projects

Diversity Projects

No-SQL Interface

JSON Cache

Databases of the modules provid a No-SQL interface where the data of the main table and the depending tables are merged as JSON.

Table JsonCache

Content of table JsonCache restricted to public available data

procFillJsonCache is started by an update trigger trgUpd… of the main table in the database

Interface in clients

All modules provide data via a cache table. In the header of the clients you can inspect the content of the JsonCache with a click on the button. For modules with a difference between local and public data, you can inspect the content of the public data with a click of the right mouse button.

Update

Apart of the update via the Trigger (see below) you can update the JsonCache via the update button underneath the button.

To update the JsonCache for the whole database select Administration - JsonCache… from the menu. a window as shown below will open where you can update the JsonCache for single datasets or the whole database.

Summary

graph TD;
    TaxonName[Main table in database] 
    trgUpdTaxonName[trgUpd.. of main table in database]
    TaxonName --> |Update in table| trgUpdTaxonName
    proc[Procedure procFillJsonCache setting the content in table JsonCache]
    trgUpdTaxonName --> proc

graph TD;    
    Mainform[Main form]
    ButtonShow[Button show JsonCache of current dataset]
    Mainform --> ButtonShow
    Left[Show Data]
    ButtonShow --> |Left click| Left

graph TD;    
    Mainform[Main form]
    Admin[Administration menu]
    Mainform --> Admin
    Cache[JsonCache...]
    Admin --> Cache
    Adminform[Administration form]
    Cache --> Adminform
    AdminUpdateSingle[Update single dataset]
    Adminform --> AdminUpdateSingle
    AdminUpdateDB[Update for whole database] 
    Adminform --> AdminUpdateDB

Diversity Agents

Documentation

Tools for the database documentation

These are the tools to describe the parts of the database and create documentations of the structure. To use these tools, choose Administation - Database - Documentation… from the menu. A window will open as shown below.

Click on the List objects button to list the objects of the database. With the  button resp. button you can select resp. deselect the types in the type selection and the object in the list.

Select the objects that should be listed all button resp. none button you can select resp. deselect the types in the type selection and the object in the list.

Select the objects that should be included in the documentation:

• Tables
  • Trigger
• Views
• Roles
• Functions and procedures
• Context

… and exclude the objects that should not be included in the documentation:

• Logging tables
• Enumeration tables
• Old versions of objects
• System objects
• Deprecated objects

The button Set default seletion will select all items in the list without:

• System objects
• Older version of an object indicated by the number at the last position
• Logging tables
• Enumeration tables
• Objects with a description starting with e.g. outdated, deprecated, obsolete etc.
• HTML options:
  • include index for objects
  • include NULL / NOT NULL
  • include relations and dependencies
  • include Description
  • exclude standard trigger
  • exclude definition
  • include permissions for *_Enum etc.
  • exclude obsolete columns
  • exclude columns starting or ending with the given strings
  • include list of tables that are depending on a table

The buttons Add to seletion and  Remove from seletion  will use the given strings with * as wildcard to add resp. remove items from the selection.

With the Context  option you can show or hide the context area for the html and media wiki tab as shown above.

HTML, MediaWiki, JSP-Wiki

To create a documentation choose among the provided options and click on the button Create ... documentation to create the documentation in one of the available formats (HTML, MediaWiki, JSP-Wiki).