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

Diversity projects

Login Administration

To administrate the logins on the database server, their permissions and roles respectively as well as access to projects choose Administration - Database - Logins ... from the menu. A window will open as shown below.

To set the website where information about details concerning the General Data Protection Regulation are shown, click on the button.   

To see the current activity on the server click on the button. A window as shown below will open listing all user related processes on the server.

To administrate the  linked servers, click on the button.   

To send a feedback click on the button.   

Statistics

To see the activity of a login click on the button. A window will open as shown below listing all databases and tables together with the time span (From - To) and the number of data sets where any activity of the current login has been found.

Creation of login

To create a new login click on the button. A window will open as shown below. A login that should be able to create new logins must to be a System administrator.

Here you can enter the name of the new login, the password and the information about the user which will be stored in a DiversityAgents database. You may either create a new entry in this database or select an existing one: Click on the button to search for a name in the database (see below).

Copy a login

To copy a login including all permissions etc. into a new login, select the original login in the list and click on the button.

Edit a login

To edit the access for a login on the server select the login in the list. If a login should be disabled , uncheck the enabled checkbox (see below).

All databases on the server will be listed with the current database showing a yellow background. The databases where the login has [no access] will be listed in [gray] while the databases accessible for a login are black.

Access of a login to a database

To allow the access to a database select the database from the list and choose database as shown below.

The state and date of the privacy consent according to the General Data Protection Regulation is shown in dependence of the selected database.

Roles of a login in a database

Use the > and < buttons to add or remove roles for the login in the database (see below).

To see the detailed permissions of a role, select it in the list of [Available] roles and click on the button. A window as shown below will open listing all objects in the database the role has permissions for (see below).

As a database owner you can edit the permissions and role memberships with the and buttons. Please keep in mind that any change of the permissions may cause serious troubles and should only be used for testing and bug fixing. The final setting of the permissions should be performed by a proper update script of the database. For every action you will get the code that is to be included in an update script (see below).

Projects for a login in a database

Depending on the database you can edit the list of projects accessible for a login (see below). Projects are related to the module DiversityProjects. To get additional information about a project select it in the list and click on the button. 

Starting with database version 02.05.35 next to the projects with full access, a second list provides projects with [Read Only] access (see image below). Use the and buttons to move projects between Accessible and Read Only. If a project is set on [Read Only] a user can still add annotations. Starting with version 4.3.219 a project as a whole can be locked with the restriction of access to read only. For more details see chapter Project administration.

To load additional projects click on the Load projects button. A window will open as shown below. Projects already in the database will be listed in green, missing projects in red (see below). Check all projects you need in your database and click the Start download button.

To see an overview of the users within a project select one of the project in either list and click on the corresponding button . A window as shown below will open listing all users and their roles with access to the selected project.

To add or remove a role for a login, select the corresponding field and choose or from the context menu (see below).

Settings of a login in a database

Depending on the database you can edit the settings of a login as shown below.

If you wish to use settings already defined for another login, click on the Search template button. A window (see below) will open where you can choose among the settings defined for logins in the database.

Overview for a login

If you want to see an overview of all permissions and project for a login, click on the button. A window as shown below will open. It lists all modules and their databases, the roles, accessible projects and read only projects for a login. 

To copy the permissions and projects of the current login to another login, select the login where the settings should be copied to from the list at the base of the window and click on the button to copy the settings for all databases or the button to copy the settings of the selected database into this login. 

Overview for a database

If you see an overview of all user and roles in a database, click on the button. A window a shown below will open. It lists all user, roles and projects in the database. 

To remove a user, select it in the list and click on the button. 

Correction of logins

If you select one of the databases, at the base a button may appear. This indicates that there are windows logins listed where the name of the login does not match the logins of the server. This may happen if e.g. a database was moved from one server to another. To correct this, click on the button. A list of deviating logins will be shown, that can be corrected automatically.

If logins with the same name but different server are found, one of them has to be deleted to make the correction possible. You will get a list where you can select those that should be removed.

Select the duplicate logins that should be removed and click OK.

To find users within the database that have no valid login, click on the button. A window as shown below will open, listing the users without a login. Select those that should be removed and click OK. This will include a removal from the collection managers.

Diversity projects

Security

A user may be in several groups with diverse rights in the database. Here certain higher groups have all rights of lower groups in addition to special rights for the higher group, e.g. the group User may only read data of certain tables while Typist has the rights of User and additionally may edit the data in certain tables - see overview below.

Summarized overview of some of the groups and their permissions as an example for the module DiversityCollection

To place a user in one of the groups, select Administration - Database - Logins... from the menu. In the window that will open select a login and a database. The roles available in the selected database will be listed as shown below. Use the > and < buttons to add or remove roles for the login in the database (see below).

To see the detailed permissions of a role, select it in the list of [Available] roles and click on the button. A window as shown below will open listing all objects in the database the role has permissions for (see below).

If you are an Administrator you may add a user to one of these groups.

Any user may have access to several projects.

Diversity projects

Project access for user

The accessibility of projects for users can have 4 different states:

• No access: The current user has no access to the project
• Accessible: The current user has access to the project
• [Read only]: The current user has read only access to the project
• [Locked]: The project is locked. Any user can either none or read only access to the project

To allow the current user access projects use the [ > ] button for the selected project resp. the [ >> ] button for all projects. To revoke access for the current user use the [ < ]  button for the selected project resp. the [ << ] button for all projects. To change the access for a project to read only use the button and the button to remove a project from the read only list.

Locking of a project

To lock a selected project use the button. For all users the project will be removed from the accessible or read only list and transferred to the locked list. This is only allowed for a database owner (dbo). Please make sure that you really want to lock a project. Any dataset related to this project will be set to read only for all users. For an introduction, please see the a short tutorial .

To remove the locked state of a project, select the project in the No access list and click on the  button. The selected project will be moved from the locked list into the read only list for those users that had access to the project.

Retrieval of projects from DiversityProjects

Details of the projects within the DiversityWorkbench are stored in the database DiversityProjects. To access further information on a project click on the  button. To edit details in projects you require the application DiversityProjects.exe in your application directory and access to the database DiversityProjects. To synchronize the projects listed in DiversityProjects you may use the synchronize function in the user administration window as shown below. If DiversityProjects is not available, you may create a new project by clicking the button. If DiversityProjects is available, use the synchronize function .

Diversity projects

Update Client

Replace the files and folders of your installation of DiversityCollection with the files you received by e-mail or downloaded from the DiversityWorkbench portal. The database will not be influenced by this replacement. After starting the new software you need to transfer the settings of the previous version. When you start the program and connect to a database, the program will check if it is compatible with the database or if the database needs an update. In any of these cases an update entry in the menu will appear. If a new version of the client is available, this menu will contain an update client … entry. Click on it to open the webpage where you may download the client as shown below.

Diversity projects

Update Database

Update database to current version

If you are the owner of the database (Database role = dbo) and the database needs to be updated, the menu will contain an update database … entry. Select this entry to open a window as shown below to run the provided update scripts, delivered with the client software. These scripts need to run consecutively, so e.g. to update from version 2.5.1 to 2.5.4 you either have to run the script DiversityCollectionUpdate_020501_To_020504 or the scripts DiversityCollectionUpdate_020501_To_020502, DiversityCollectionUpdate_020502_To_020503 and DiversityCollectionUpdate_020503_To_020504. The program will guide you through these steps and check for the scripts. All you need to do is click the Start update button. 

Update of all databases on a server

If you are database owner and have a windows login to the database server (user=dbo), you have the option to update all DiversityCollection databases on this server by starting the application with command line parameters. Open a command line window, navigate to the program directory and start DiversityCollection with the keyword “UpdateDatebase”, the server name (or IP address) and the port number:

DiversityCollection.exe UpdateDatabase 127.0.0.1 5432

The program will connect to the server and update all available databases to the current version. If you want to exclude dedicated databases from update, create a text file named “ExcludeDb.txt” that contains the excluded database names - each name in a separate line - and locate it in the resources directory. The update will be done in the background without opening a program window. When the update processing is finished, an overview of the performed actions will be written into the protocol file “Updatereport.log” in directory resources/Updates.