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

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