Cache Database Basic steps

Basic steps for publication of data via the cache database

1 - Create the cache database

To create a cache database as shown in the chapter Creation of thecache databasee you need to be a system administrator (s. Login administration). After this step the cache database should be available and you can create a Postgres database as final target of your data. To grant access to the cache database for other users, see chapter Login administrationof the cache databases.

2 - Create a Postgres database

The final formatting of the data e.g. for publication via webservice are performed in a Postgres database. If no server providing Postgres is available, you may install Postgres on your local machine (see https://www.postgresql.org/ for further information). The creation and administration of a Postgres database is described in chapter Administration of the Postgres cache databasess. To grant access to the Postgres cache database for other users, see chapter Login administration of thecache databases.  

3 - Insert sources for taxonomic names, collection specimen, references etc.

This step is optional and depends upon the availability of a source for e.g. taxonomic names. You may either use sources from your local server or the public available sources provided by tnt.diversityworkbench.de (turn to http://www.snsb.info for further information). The needed settings are described in chapter Sources from othermodules.  

4 - Insert a project

The data published in the cache database are organized according to the projects. Add a project as shown in chapter Projects in the cache database. Check the Mapping of IDs in the source database and make sure that the data within this project are not withheld from publication and that the ranges you want to publish are set properly (see chapter Restrictions for the datatransfer into the cache database).  

5 - Transfer the data

The final transfer of the data is described in chapter Sources forother modules and chapter Transfer of the data.

6 - Publish or export the data

To export the data or prepare them for publication according to the specifications of webservices etc. the data frequently need to be formatted. This is done with packages as described in chapter Administration of the Packages.

Cache Database Create

Create the cache database

To create a cache database you need to be a system administrator (s. Login administration). To create the cache database, choose Data → Cache database … from the menu. If so far no cache database exists, you will be asked if a new one should be generated. After the generation of the cache database a window as shown below will open.

Click on the Update button to update the database to the latest version. A window as shown below will open. Click on Start update to execute all the scripts needed for the latest version of the database.

To grant access to the cache database for other users, see chapter Login administration of the cache databases.  

You should now close an re-open the cache database window. During transfer to the cache database the project metadata are read from a local Diversity Projects database, if in DiversityDescriptions the project is linked to it. By default the name of this “project definitions” database is assumed to be “DiversityProjects” and the same postfix as the DiversityDescriptions database. If this assumption is not fulfilled, you will find the button Please check function ProjectsDatabase (see image below).

After clicking the button, a message window might inform you about Projects databases you cannot access due to insufficient access rights (see image below).

After closing the message window you find all accessible projects database located on the same database server as your DiversityDescriptions database (see image below). In columns “Fitting projects” and “Non fitting projects” you see the number of projects with fitting rsp. not fitting links to the lsited DiversityProjects database. Click on the database you want ti use for the cache transfer. If not fitting projects are present, the button  may be used to see more details.

After selecting a database click on button OK.

If there are non fitting links in your projects database, you will find the button Check missing links for ProjectsDatabase (see image below). Click this button to view the details. Afterward you will have get the projects selection window as shown above.

You may now continue with the Administration of the Postgres cachedatabases or insert Sources from other modules. Anyway, close and re-open the cache database window before you insert Projects in the cachedatabase.

Cache Database Id Mapping

Mapping of IDs for the data transfer into the cache database

In the Diversity Descriptions database the main tables Description, Descriptor and CategoricalState have a numeric “id” as key, which is set by the MS SQL server when the dataset ist crated. All relations between the tables are set by using these unique values. For various reasons those internal IDs are not seen as suitable for all purposes of the cache database. Therefore a mapping of the internal IDs is performed before the transfer of data to the cache database.

Default mapping

The default mapping of the IDs is mainly oriented on the way the states and characters are identified in the widely spread DELTA standard:

• Descriptors are numbered starting with “1” in ascending order and transferred into the cache database table CacheCharacter. The order is determined by the descriptor parameter “display_order” and the alphabetical order. The original “id” is stored as “CharID” in the target table, the descriptor number as “CID”.
• CategoricalStates are numbered for each character starting with “1” in ascending order and transferred into the cache database table CacheState. The order is determined by the categorical state parameter “display_order” and the alphabetical order. The original “id” is stored as “StateID” in the target table, the categorical state number as “CS”.
• Since the categorical state numbers (“CS”) are not unique, each state is identified, e.g. in a description item, by the character and state number (“CID”, “CS”).
• Descriptions are numbered starting with “1” in ascending order and transferred into the cache database table CacheItem. The order is determined by the alphabetical order. The original “id” is stored as “ItemID” in the target table, the description number as “IID”. As an alternative the “IID” may be derived from the field “alternate_id” of the “Description” table (see following section.

The mapping data are stored related to the project in the tables CacheMappingDescriptor, CacheMappingState and CacheMappingDescription of the original database.  

Mapping adjustment

To set the mapping adjustments, click on the  button (see below).

A window as shown below will open.

If no option is selected, the default mapping algorithm described above will be performed for every transfer to the cache database. Any changes, e.g. insertion of a descriptor or re-arrangement of categorical states, will affect the “CID”, “CS” and “IID” of the cache database.

If option Keep fixed IDs after first export is selected, the default mapping algorithm described above will be performed only for the first transfer to the cache database. Any changes, e.g. insertion of a descriptor or re-arrangement of categorical states, will NOT affect the “CID”, “CS” and “IID” of the cache database. New elements will get a number higher than the last one present. If an element is deleted, this will result in “missing” numbers in the cache database. Pure re-arrangements will have no effect.

The last option Take “IID” from “AID” field only affects the description mapping. By default the descriptions are numbered in alphabetical order. If this option is chosen, it is tried to use the field “alternate_id” (“AID” in the GUI) as item number. Preconditions are that the “AID” is a pure number and that the values are unique. If the “AID” is not supplied or an alpha-numeric string or if the number is already occupied, a new ascending value will be set. By using this option a foreign numbering scheme may be used for the cache database. When selecting this option you might want select Re-initialize “IID” fields to build the description mapping at the next cache transfer. 

Cache Database Infrastructure

Infrastructure for the cache database

For the administration of the data that are published via the cache database, certain tables as shown below are used. These are either placed in the schema dbo or a schema named according to the published project, e.g. Project_Test for a project with the name “Test”. Additionally some basic data are stored in dedicated tables of the main DiversityDescriptions database.

Tables in the main database

In the main DiversityDescriptions database there is a number of tables holding the cache database name, information about datawithholding and mapping information of the IDs. This information is needed to restore the cache database in case of loss. For the database diagram take a look at the database section of this manual.  

• CacheDatabase
• CacheMappingDescription
• CacheMappingDescriptor
• CacheMappingState
• CacheProject

Central tables in the cache database

There are a number of tables placed in the schema dbo that are accessible by all projects.

Published project tables

The central published project tables contain the information about the projects that are published together with the target (Postgres) databases and the packages including optional add-ons into which they had been transferred. This information is used to ensure a recovery in case of a loss of the targets.

• ProjectPublished
• ProjectTarget
• ProjectTargetPackage
• ProjectTransfer
• Target

Source tables

To access sources from other modules (e.g. DiversityReferences) there are tables for the storage of the principal access to the modules and a number of tables containing the data (depending on the module).

Access tables

These tables contain the principal access like the name of the view defined to access the data. The example below lists the tables defined for the module DiversityReferences, but there are corresponding tables for every module accessed by the cache database. 

• ReferenceTitleSource
• ReferenceTitleSourceTarget
• ReferenceTitleSourceView

Data tables

These tables contain the data provided by the module and therefore depend on the module. The example below lists the tables defined for the module DiversityReferences, but there are corresponding tables for every module accessed by the cache database. Since data from various source databases may be acumulated in the cache database, in general all the data tables include the BaseURL as part of their keys to ensure unambiguousness.  

• ReferenceTitle
• ReferenceRelator

To access the data in the source database for the module views are generated by the client. The name of these views are composed according to the name of the database and the project to ensure a unique name. Furthermore letters are appended to identify subordinated tables. These are stored in the table “<module>Source” and are used by the client for a transfer of the data from the module database into the tables in the cache database. The example below lists the view names for the module DiversityReferences. In this example the source database “DiversityReferences_Test” and project “DALI” result in the main table name. By appending “_R” the view name for subordinated table “ReferenceRelator” is built. This gives the views References_Test_DALI and References_Test_DALI_R.

Project tables in the cache database

These tables contain the data of the projects with every project having its own schema. The tables correspond to the tables in the main database of the module with according the following assignment. In the third columns the views of the cache database are listed to access the DiversityDescriptions data. The view access besides the main tables listed in the second table column and the ID mapping tables. For the summary data (CacheDescription) additionally subordinated tables, e.g. Modifier, are accessed to resolve relations in DiversityDescriptions as simple character strings.

Besides the tables mentioned above, the auxilliary tables ProjectLockedDescriptor and ProjectLockedScope contain the descriptor IDs and scope types that shall be excluded from the transfer to the cache database. The auxilliary tables ProjectPublishedTranslation and ProjectPublishedDescriptorTree contain language codes of translations (columns label, wording and detail of source tables Descriptor, CategoricalState and Description) and the descriptor tree IDs that shall be included in the cache database transfer. Together with the extended query parameter, which are stored in the columns FilterCommand and FilterParameter of the table ProjectPublished, they build the transferrestrictions of the cache database. Finally, in table CacheMetadata some data from the DiversityProjects database are stored.

• CacheCharacter
• CacheCharacterTree
• CacheCharacterTreeNode
• CacheDescription
• CacheItem
• CacheMetadata
• CacheResource
• CacheScope
• CacheState
• CacheTranslation
• ProjectLockedDescriptor
• ProjectLockedScope
• ProjectPublishedDescriptorTree
• ProjectPublishedTranslation

The main tables CacheItem, CacheCharacter and CacheState have a numeric key (ItemID, CharID and StateID), which is identical to the unique key in the main database. However, in the cache database the main adress attributes are IID, CID and CS. CID and SD are in principle the descriptor and categorical state sequence numbers, where the mapping algorith guarantees unique ascending values. In this adressing schema a single state is identified by the combination of CID and CS.

Additionally in table CacheState the recommended statistical measures of quantitative descriptors are inclueded, where the measure code (e.g. “Min”, “Max” or “Mean”) is inserted in CS. In table CacheDescription, which holds the single descriptor or data status values, the CID and CS are specified for a specific categorical state. For quantitative data in CS the measurement type is identified by the measure code. For text and molecular sequence data CS is supplied with the fixed texts “TE” and “MS”. In case of descriptor status data CS is set NULL. Instead the data status code is inserted in column Status.  

Project procedures for the data transfer into the project tables

For every project table there is a set of procedures that transfers the data from the main database into the cache table. The names of these procedures are procPublish + the name of the target table without “Cache” e.g. procPublishCharacter for the transfer of the data into the table CacheCharacter. The first steps of the data transfer perform an update the ID mapping tables in the main database. This is done in the procedures procPublishMappingItem, procPublishCharacter and procPublishState, which call dedicated procedures in the DiversityDescriptions database. As mentioned above, the original IDs (ItemID, CharID and StateID) are stored together resulting mapped IDs (IID, CID and CS) in the cache database tables. To view the mapping information, the views CacheMappingItem, CacheMappingCharacter and CacheMappingState select the appropriate values from the cache tables.

List of tables mentioned above

Table ProjectPublished

The projects published via the cache database (Details about the projects are defined in DiversityProjects)

Table ProjectTarget

The targets of the projects, i.e. the Postgres databases

Table ProjectTargetPackage

Packages for projects as documented in the table Package in the Postgres database

Table ProjectTransfer

The transfers of data of a project

Table ReferenceRelator

Table ReferenceTitle

Table ReferenceTitleSource

Table ReferenceTitleSourceTarget

The targets of the projects, i.e. the Postgres databases

Table ReferenceTitleSourceView

Table Target

The postgres databases as targets for the data

Table CacheCharacter

Character (= descriptors, features) define variables

Table CacheCharacterTree

The descriptor trees

Table CacheCharacterTreeNode

The character tree nodes

Table CacheDescription

The description data in the database

Table CacheItem

The description item in the database

Table CacheMetadata

Table CacheResource

The available resources

Table CacheScope

The scope of the description

Table CacheState

The states available for characters

Table CacheTranslation

The available translations

Table ProjectLockedDescriptor

The descriptors (=characters) that shall not be published

Table ProjectLockedScope

The scope types that shall not be published

Table ProjectPublishedDescriptorTree

The descriptor tree IDs that shall be published

Table ProjectPublishedTranslation

The translation languages that shall be published

Cache Database Logins

Cache database - User administration

There are two roles in the cache database with a general access to the data: CacheAdmin (for the administration and transfer of the data) and CacheUser (with read only access). The data in the cache databases are organized in projects. Therefore for every project you find one additional role: CacheAdmin_Project_… with the right to transfer of the corresponding project to the SQL-Server cache database. You find the project specific roles in the SQL-Server cache database, in the Postgres database only CachAdmin and CacheUser are available.

To administrate the logins in the SQL-Server database, go to the Update and Sources tab and click on the button of the cache database to open a window as shown below. To administrate the access for other logins, you have to be a System administator. For further details please see the chapter about the login administration for the main database.

To view the access rights of a selected role click on the button to open a window as shown below.

Postgres database

To handle the logins and user groups on the Postgres database server, go to the Update and Sources tab and click on the button of the postgres database. A window as shown below will open, where you can create and delete logins and groups. For the logins you can change their membership in groups and their properties (see below). On the left you find 2 lists, with the upper list containing the logins and the list below with the groups resp. roles. For the logins you can set the general properties as shown below. The login postgres is created with the installation of the database and is the login for the administration of the database including the updates etc. For details about predefined properties like “Is superuser”, please turn to the Postgresdocumentation. 

In the Membership in groups area you can define the groups in which the login is a member (see below). 

For the groups you can change their membership in other groups and their permissions (see below). 

Cache Database Packages

Administration of the Packages

The formatting of the data according to the specifications of webservices etc. is done with packages. There is a growing list of packages provided with the software. For new packages either turn to the developers or create a package of your own. The packages are realised as tables, view, functions etc. reading the data in the tables without any changes to the data. They therefore can be inserted and removed without any effects on the original data. The naming of the objects within a package follow the schema [Name of the package]_… as shown in the images below.

To administrate the packages installed within one project, click on the button (see above). A window as shown below will open listing all available packages.

Click on the button to establish the selected package. To ensure the current version of the package, click on the Update to vers. … button (see below).

A window will open where all the needed scripts will be listed. For packages keeping the data in their own tables like NaviKey it may be necessary to adapt the timeout for database commands. Click on the button and enter an appropriate value. For large amounts of data the value 0 is recommended, which means infinite.

With button you may select locked characters to restrict the exported data for the current package. This option might be useful, if for a certain target, some characters make no sense. E.g. for the NaviKey application information like the “data record revision” is irrelevant. For other targets it may be published, because it is no secret information.    

To remove a package use the button and the button to get information about the package. For some packages the button indicates that modified data may be published by using an Add-On (see below).

A package (e.g. NaviKey) may contain e.g. tables or materialized views. These need an update after the data have been transferred to the Postgres database. Click on the Transfer button to update the package data. A window as shown below will open, listing the transfer steps for the package. Choose the steps that should be transferred and click on the Start transfer button to transfer the data.

After closing the transfer window you may inspect the data provided by the package by click on the button of the package. Some packages or add-ons may install depend schemas in the database. In this case you will get a window to select the schema for display.

A window as shown below will open listing all package related objects. For every object (table, view, column, … ) the description is shown in the lower part of the window.

To export the contents of a package, click on the button. A window as shown below will open listing the main views of the package. You can export the data as XML (creating a directory with one xml file for every view) or as SQLite database (creating a SQLite database containing the tables).

Cache Database Packages Add On

Administration of the Add-Ons for Packages

For certain packages there are add-ons available to adapt a package to the special specifications of e.g. a project. To add an add-on, click on the button as shown below. A window as shown below will open listing all available add-ons.

A window as shown below will open listing all available add-ons.

Certain add-ons require the selection of an add-on parameter. E.g. the Navikey_Language add-on requires the selection of the published translation language code. In this cases an additional selection window will be shown (see image below). Remark: Selecting the add-on parameter and therefore the installation of the add-on might require that data have been trasferred to the Postgres database before! 

After the add-on has been added, you need to update it to the current version of the add on with a click on the Upd. to vers. … button (see below). A window will open where all the needed scripts will be listed. Some add-ons are exclusive, meaning that no further add-ons can be added and further updates of the package are organized via the add-on as shown below. To remove an add-on you have to remove the package with a click on the button. 

An Add-on defines a version of the package it is compatible with. Add-ons can not be removed as they perform changes in the package. To remove an add-on, remove the package and reinstall it. If a package containing an Add-on needs an update you have to remove the package as well and reinstall it.

With a click on the first button besides the package you can generate a description of all objects in the package (see below).

With a click on the second button besides the add-on you can view its description (see below).

Cache Database Postgres

Administration of the Postgres cache databases

To create a Postgres cache database, you must be connected with a server running Postgres  (Version 10 or above). To connect to a server, click on the button and enter the connection parameters. If no cache database has been created so far, you will get a message that no database is available, otherwise you may select the database as shown below. After entering valid login data to the Postgres server, at least the button will be available (see image below). Click on the button to create a new cache database (see below). You will be asked for the name of the database and a short description. After the database was created, you have to update the database to the current version. Click on the Update button to open a window listing all needed scripts. To run these scripts just click on the Start update button. After the update the database is ready to take your data. Subsequent updates may become necessary with new versions of the database. To remove the current database from the server, just click on the button. In the image on the right you see a screenshot from the tool pgAdmin 4 . You may use this tool to inspect your data and administrate the database independent from DiversityDescriptions. Please keep in mind, that any changes you insert on this level may disable your database from being used by DiversityDescriptions as a sink for your cache data. The data are organized in schemas, with public as the default schema. Here you find functions for marking the database as a module of the Diversity Workbench and the version of the database. The tables in this schema are e.g. TaxonSynonymy where the data derived from DiversityTaxonNames are stored and ReferenceTitle where the data derived from DiversityReferences are stored. For every project a separate schema is created (here Project_LIASlight). The project schemas contain 2 functions for the ID of the project and the version. The data are stored in the tables while the packages in their greater part are realized as views and functions extracting and converting the data from the tables according to their requirements.  If you connect to a Postgres database for the very first time, you must use an account with sufficient rights to create databases, e.g. "postgres". After performing the first database update the role "CacheAdmin" is available, which has sufficient rights for any cache database operation. To create new logins and grant access to the Postgres cache database for other users, see chapter Login administration of the cache databases .

Cache Database Postgres Database

Infrastructure for the postgres database

At the PostgreSQL server the tables are either placed in the schema public or a schema named according to the published project. In general the tables in postgres have the same strucure as in the Microsoft SQL cache database, so the transfer to postgres is mainly a pure copy of their contents. Only some data types are subtituted by their equivalents in postgres. Therefore you may refer to the cachedatabase documentation concerning these tables. Some additional tables, views or even schemas and appropriate transfer functions may be introduced by the packages and their add-ons.

In schema public the support functions diversityworkbenchmodule and version provide the module name “DiversityDescriptionsCache” and the postgres database version (e.g. “01.00.01”) as strings to support processing and the database update. In the published project schemas the support functions projectid and version provide the corresponding integer values.   

All schemas, functions, tables and views are owned by role CacheAdmin. The additional role CacheUser grants read access to all elements rsp. execution rights to the support functions.

Source tables

The data tables of the source modules (e.g. DiversityReferences) are transfered as copies to the schema public. I.e. the access tables ending with “Source”, “SourceTarget” and “SourceView”, which serve administrative purposes, are omitted. In the example for the module DiversityReferences, shown in the databaseinfrastructure page, only the tables ReferenceTitle and ReferenceRelator are tranferred to postgres.

Project tables

The project tables in postgres are simple copies of the cache database tables. They are placed in a schema named according to the published project, e.g. Project_Test for a project with the name “Test”. For details refer to the cache database documentation:

• CacheCharacter
• CacheCharacterTree
• CacheCharacterTreeNode
• CacheDescription
• CacheItem
• CacheMetadata
• CacheScope
• CacheState
• CacheTranslation

The view ProjectLanguage provides access to the ProjectLanguageCode stored in table CacheMetadata. The view TranlationLanguage provides access to the LanguageCode of available translations stored in table CacheTranslation.  

Packages and Add-Ons

The formatting of the data according to the specifications of webservices etc. is done with packages. The packages are realized as tables, views, functions etc. reading the data in the tables without any changes to the data. They therefore can be inserted and removed without any effects on the original data. The naming of the objects within a package follow the schema [Name of the package]_… . Each package provides a database command file to create the required objects. Transfer of data is done by calling dedicated transfer functions.

For certain packages there are add-ons available to adapt a package to the special specifications of e.g. a project. Each add-on provides a database command file to create the required objects. The traditional way of realizing an add-on is to modify some transfer functions of the package. Therefore only one of those add-ons can be installed for a package. A more flexible approach of handling add-ons is to build a dedicated add-on transfer function and store its name in the administration table. For package transfer there a transger step is defined that reads all associated add-ons from the database and calls their transfer functions in a defined order. With this approach insertion of multiple compatible add-ons may be realized.   

 For the administration of packages and add-ons four tables are used in the postgres database:

• Package
• PackageAddOn
• PackageLockedCharacter
• PackagePublicTable
• PackageSchema

In table Package each installed package, its version and description are stored. After data transfer to a package transfer date and time are stored there, too. Add-on data like its version are stored in table PackageAddOn. Some add-ons may require a parameter that has to be selected during installation and ist stored in table column Parameter. If the “non-traditional” approach of realizing add-ons using dedicated transfer functions is used, this table provides optional support columns.

The table PackagePublicTable offers support, if a package or add-on needs to provide data in the schema public. A traditional way to realize this feature is to mark the package as “using schema public”. As a consequence the package can only be created in one project of the whole database. When the package is removed, all objects in schemad public with the prefix “[Name of the package]_” are dropped.

An alternative is that a package or add-on inserts the public table name in PackagePublicTable. Furthermore the public table must have a column where the source schema name is included. If the package is removed, all entries of the public table with matching package name and source schema will be deleted. If the public table is empty, the table itself will be dropped. An example is package NaviKey that enters all published schemas in the table NaviKey_Schema. This table is used by the REST service to provide data to the application DiversityNaviKey.  

The table PackageSchema offers support, if a package or add-on needs to provide data in dependent schemas. For example the add-on NaviKey_Translations provides access to all available translated data of the package NaviKey. Therefore for each available language code a dependent schema named [language code]_Project_[Name of the project] (e.g. de_Project_LIASlight for the german translation) is created with views to the data in the master schema. Each dependent schema is inserted in table PackageSchema (and the public table NaviKey_Schema for the REST service). When the package is removed, all dependent schemas will be dropped, too.

Available Packages and Add-Ons

Currently the following packages and add-ons are available:

Tables for Packages and Add-Ons

Table Package

Table PackageAddOn

Table PackageLockedCharacter

Stores for each schema the CIDs that shall not included in the package.

Table PackagePublicTable

Stores tables in schema public where data of the package are inserted. Data in this table are inserted by the package and/or Add-Ons.

Table PackageSchema

Stores dependent schemas where data of the package are inserted. Data in this table are inserted by the package and/or Add-Ons.

Cache Database Projects

Projects in the cache database

The data transferred into the cache database are always transferred according to a project they belong to. Choose Data → Cache database … from the menu and select the tab Projects. If no projects were added so far the window will appear like shown below.

To add a new project for the transfer into the cache database, click on the Add project button. In the area below a new entry as shown below will appear. The area on the right shows the number of datasets in the project in the source database together with the date of the last update. To ensure the separation of the data between the projects, DiversityDescriptions creates a separate schema for every project named Project_[name of the project] together with needed roles, tables etc..

Before transferring data you have to update the project schema to the latest version, indicated by the appearance of an update button . Click on the button to open a window as shown below. Click on the Start update button to update the schema to the latest version. For adding a project and performing the database update you need to be a system administrator (s. Login administration). 

After the update the database is ready to transfer data into. 

But before starting the cache transfer you should take a look on the ID mapping, data withholding and data restrictions. The first two items are stored in the descriptions database, the latter in the cache database.

With the ID mapping you can determine how description items, descriptors and categorical states shall be identified in the cach database and how changes are handled in subsequent cache transfers. Click on the button to edit the ID mapping behaviour for the data of the project (see below).

If any descriptors are marked with the data status Data withheld, you have the options to exclude the whole description the export, to hide only the marked descriptor data or to export the whole dataset. Click on the button to edit the data withholding behaviour for the data of the project (see below).

Besides the restrictions in the source database, you can set further data restrictions for this transfer. Click on the button and choose the data restrictions for the cache transfer (see below).

To transfer the data you have 3 options as described in the Transfer chapter.

Afterwards the number and date of the transferred data are visible as shown below.

To inspect the transferred data use the View content button. A window as shown below will open where all tables containing the data of the project are listed.

Click on the button to filter the content. A window as shown below will open. Choose the column for the filter, the operator (e.g. = ) and the filter value (see below).

Now click on the button to add the filter criteria to the table filter. You may add as many criteria as needed (see below). With the button you can clear the filter..

Before you can transfer the data into the Postgresdatabase, you have to connect to the Postgres database and click on the button to establish the project and run necessary updates . After the project is established and up to date, use the button to transfer the data in the Postgres area (see below).

If a project is exported into another Postgres database on the same server, these databases will be listed underneath the Postgres block (see image below). For an overview of all target Postgres databases click on the button.

If the target is placed on the current server, the text will appear in black instead of grey (see below). Packages, if administered, will be listed in the table as well.

In some cases, when a cache database has been deleted on the current Postgres server, there might still be some administrative information left. In this case the target is shown in red and you have the option to delete the administrative data for that target (see below).

In the Postgres database you can install packages to adapt the data to any needed format.

Cache Database Restrictions

Restrictions for the data transfer into the cache database

The restrictions of the published data are defined in the main database via projects and data withholding . In the cache database further restrictions can be set for every project. You may set a filter on description data to restrict the Descriptions, select the Descriptors, the Scopes and the Translations that are transferred.

To set the restrictions, click on the  button (see below).

A window as shown in the following sections will open.

Descriptions tab

The Descriptions filter works the same way as the Extended query. Use buttons Add description, Add scope or Add descriptor to insert filter items for description, scope or descriptor criteria and set the filter conditions. With button Remove filter item you may remove the currently selected filter item. In the lower right corner Matches: shows you the current count of description items that match the adjusted filter criteria. In the lower center part the resulting SQL filter is shown.

Descriptors tab

With the Descriptors filter you can select the descriptors for which summary data shall be transferred. Use button the arrow buttons  << ,   <  ,   >   and  >>  for moving the entries between the Not published and Published list. Click button Transfer existing to move all descriptors that are used within the database to the Published list. With button Transfer from tree you may move all descriptors connected to the selected Descriptor tree to the Published list. Option Show sequence numbers includes the descriptor sequence numbers in the lists. If you do not only want to suppress the descriptive data but also want to hide the descriptors and their state from the cache database, select the option Omit descriptors.

Note: With this option you may keep the selected descriptors completely away from the cache database. If you want to suppress certain descriptors only for selected targets, there is an additional possibility to control the export based on the Packages (refer to “locked characters”).

Scopes tab

With the Scope filter you can select the scope type for description scopes that shall be transferred. Use button the arrow buttons  << ,   <  ,   >   and  >>  for moving the entries between the Not published and Published list. Click button Transfer existing to move all scope types that are used within the database to the Published list.

Translations tab

With the Translation filter you can select the translation languages for description items, descriptors and categorical states that shall be transferred. Use button the arrow buttons  << ,   <  ,   >   and  >>  for moving the entries between the Not published and Published list. Click button Transfer existing to move all translation languages that are present within the database to the Published list. Use the check boxes Descriptions, Descriptors and States to select the tables where translations shall be published. By default, i.e. without explicit adjustment no tranlations will be transferred to the cache database.

Descriptor trees tab

With the Descriptor tee filter you can select the descriptor trees that shall be transferred. Use button the arrow buttons  << ,   <  ,   >   and  >>  for moving the entries between the Not published and Published list. By default, i.e. without explicit adjustment no descriptor trees will be transferred to the cache database.

Resources tab

With the Resources filter you can select the resource links that shall be transferred. You have to select at least the Entity types and the Resource types that shall be published. By default, i.e. without explicit adjustment no resources will be transferred to the cache database.

If you select Publish all from section Resource types, all available resources that either are URLs (starting with “http://” or “https://”) or color codes (starting with “color://#”) are included in the transfer. This general restriction ignores resources that are located on your local machine and therefore not generally reachable. If you select the explicit types Images or Colors, the resource variant types must be set correctly, e.g. “image/jpeg”. This can be done for an individual resource in the corresponding edit panel by retrieving the media data from the source. A more comfortable way to get those data for a large amount of resources is to use the database maintenance tool.

With the check boxes Restrict role you may select role values that shall be transferred. With Restrict ranking you may select the minimum ranking value of published resources. If you select the value “0”, the ranking values will be ignored, i.e. even unranked resources will be published.   

Cache Database Sources

Sources from other modules

To provide details from other modules like DiversityTaxonNames, DiversityCollection, DiversityReferences etc. in the cached data, this information is transferred into the cache database together with the data from DiversityDescriptions database. Use the button to add a source for the data you need. You may include data from a local or a linked database. The data for the cache database are provided via views that will be created for you. With the button you can list the sources used in the projects to get a hint of what may be needed. The tables and view for the sources are placed in the schema dbo in the SQL-Server cache database and public in the Postgres cache databases. In case a new version for the source is available you get a notation like  Needs recreation to version 2 . In this case you must remove the source with a click on the button and use the button to recreate the source. For the inclusion of sources from services like Catalogue of Life see the chapter Sources from webservices.

Subsets

The sources of some module provide additional subsets to the main data. To select the subsets that should be transferred to the cache database, click on the button. In the upcoming dialog (see the example for DiversityTaxonNames below) select those subsets you want to transfer in addition to the basic name list.

Transfer

Single manual transfer

To transfer the data of a single source into the ** ** cache database use the button and the buttons to inspect the content.

To transfer the data into the Postgres database, you have to connect with a database first. Use the button to transfer the data from the ** ** cache database into the  Postgres database. The button will transfer the data of all sources in the list into the cache database (see below).

Scheduled transfer

To transfer the data of all sources into the ** ** cache database and all available Postgres databases, use the scheduled transfer as backgroundprocess. With this transfer all available Postgres databases will be included in the data transfer. These targets are indicated underneath the Postgres block (see image below).

If the target is placed on the current server, the text will appear in black as shown below.

Removal

With the button you can remove the sources together with the views (see above).

Orphaned data

In some cases, e.g. if a datasource is removed from the ** ** cache database and there is no active connection to the Postgres database, the corresponding data will stay in the postgres database. These orphaned data might disturb subsequent data transfers, if they overlap with the remaining data. Their source view and number of datasets are shown in section “Source views not present in the SQL-Server database” (see below). Click on button to delete the orphaned data. 

Cache Database Transfer

Transfer of the data

To transfer the data you have 3 options:

• Single transfer : Transfer data of a single project
• Bulk transfer : Transfer data of all projects and sources selected for the schedule based transfer

With the resp. button you can decide if the data should be checked for updates. If this option is active ( ) the program will compare the contents and decide if a transfer is needed. If a transfer is needed, this will be indicated with a red border of the transfer button . If you transferred only a part of the data this will be indicated by a thin red border for the current session . The context menu of the button View differences will show the accession numbers of the datasets with changes after the last transfer (see below).

Competing transfer

If a competing transfer is active for the same step, this will be indicated as shown below. While this transfer is active, any further transfer for this step will be blocked.

If this competing transfer is due to e.g. a crash and is not active any more, you have to get rid of the block to preceed with the transfer of the date. To do so you have to reset the status of the transfer. Check the scheduler as shown below. This will activate the button.

Now click on the button to open the window for setting the scheduler options as shown below.

To finally remove the block by the Active transfer, click on the button. This will remove the block and you can preceed with the transfer of the data.

Single transfer

To transfer the data for a certain project, click on the button in the Cache- or Postgres data range (see below).

A window as shown below will open, where all data ranges for the transfer will be listed. With the  button you can set the timeout for the transfer of the data where 0 means infinite and is recommended for large amounts of data. With the button you can switch on resp. of the generation of a report. Click on the Start transfer button to start the transfer.

After the data are transferred, the number and data are visible as shown below.

After the data are transferred successful transfers as indicated by an error by . The reason for the failure is shown if you click on the button. For the transfer to the Postgres database the number in the source and the target will be listed as shown below indicating deviating numbers of the data. For the detection of certain errors it may help to activate the logging as described in the chapter TransferSettings. To inspect the first 100 lines of the transferred data click on the button.

Bulk transfer

To transfer the data for all projects selected for the schedule based transfer, click on the button in the cache- or Postgres data range (see below).  

DiversityDescriptions_local		DiversityDescriptionsCache_local		DiversityDescriptionsCache_2 on 127.0.0.1, 5555 as postgres

Together with the transfer of the data, reports will be generated and stored in the reports directory. Click on the button in the Timer area to open this directory. To inspect data in the default schemas (dbo for SQL-Server and public for Postgres ) outside the project schemata, use the buttons shown in the image above.

Transfer as background process

Transfer data from database to cache database and all Postgres databases

To transfer the data as a background process use the following arguments:

• CacheTransfer
• Server of the SQL-server database
• Port of SQL-server
• Database with the source data
• Server for Postgres cache database
• Port for Postgres server
• Name of the Postgres cache database
• Name of Postgres user
• Password of Postgres user

For example:

C:\DiversityWorkbench\DiversityDescriptions> DiversityDescriptions.exe CacheTransfer snsb.diversityworkbench.de 5432 DiversityDescriptions 127.0.0.1 5555 DiversityDescriptionsCache PostgresUser myPostgresPassword

The application will transfer the data according to the settings, generate a protocol as described above and quit automatically after the transfer of the data. The user starting the process needs a Windows authentication with access to the SQL-Server database and proper rights to transfer the data. The sources and projects within DiversityCollection will be transferred according to the settings (inclusion, filter, days and time). The transfer will be documented in report files. Click on the button to access these files. For a simulation of this transfer click on the Transfer all data according to the settings  button at the top of the form. This will ignore the time restrictions as defined in the settings and will start an immediate transfer of all selected data.

To generate transfer reports and document every step performed by the software during the transfer of the data use a different first argument:

• CacheTransferWithLogging
• …

C:\DiversityWorkbench\DiversityDescriptions> DiversityDescriptions.exe CacheTransferWithLogging snsb.diversityworkbench.de 5432 DiversityDescriptions 127.0.0.1 5555 DiversityDescriptionsCache PostgresUser myPostgresPassword

To transfer only the data from the main database into the cache database use a different first argument:

• CacheTransferCacheDB
• …

To transfer only the data from the cache database into the postgres database use a different first argument:

• CacheTransferPostgres
• …

The remaining arguments correspond to the list above. The generated report files are located in the directory …/ReportsCacheDB and the single steps are witten into the file DiversityCollectionError.log.

History

For every transfer of the data along the pipeline, the settings (e.g. version of the databases) the number of transferred data and additional information are stored. Click on the  button in the respective part to get a list of all previous transfers together with these data (see below).

Cache Database Transfer Protocol

Protocol of the transfers in the cache database

The protocol of any transfer is written into the error log of the application. To examine the messages of the transfers within the cache database you may for every single step click on the button in the transfer window in case an error occurred. In the main window the protocol for all transfers is shown in the   Protocol  part. Choose Data -> Cache database … from the menu and select the tab Protocol. A window will appear like shown below.

A click on the button Requery protocol (error log) requeries resp. shows the protocol. The option Show lines will show the line numbers of the protocol and the option Restrict to Failure will restrict the output to failures and errors stored in the protocol. By default both options are seleted and the number of lines is restricted to 1000. In case of longer protocols change the number of lines for the output. The button Open as textfile will show the protocol in the default editor of your computer. The button Clear protocol (error log) will clear the protocol.

Cache Database Transfer Settings

Settings for the transfer of the projects in the cache database

To edit the general settings for the transfer, click on the button in the main form. A window as shown below will open. Here you can set the timeout for the transfer in minutes. The value 0 means that no time limit is set and the program should try infinite to transfer the data. Furthermore you can set the parameters for the transfer of the data in chunks. If the amount of data is above a certain threshold, it is faster to divide the data into smaller chunks. The threshold for transfer into the cache database and into the Postgres database can be set as shown below, together with the maximal size of the chunks. To return to the default vales click the button Set default values.

Logging

For the detection of certain errors it may help to log the events of the transfer by activating the logging: → . The logging is set per application, not per database. So to detect errors in a transfer started by a scheduler on a server, you have to activate the logging in the application started by the server.

Scheduled transfer

The scheduled transfer is meant to be launched on a server on a regular basis, e.g. once a week, once a day, every hour etc.. The transfer of the data via the scheduled transfer will take place according to the settings. This means the program will check if the next planned time for a data transfer is passed and only than start to transfer the data. To include a source in the schedule, check the selector for the scheduler. To set the time and days scheduled for a transfer, click on the button. A window as shown below will open where you can select the time and the day(s) of the week when the transfer should be executed.

The planned points in time are shown in the form as shown below.

The protocol of the last transfer can be seen as in the window above or if you click on the button. If an error occurred this can be inspected with a click no the button.

If another transfer on the same source has been started, no further transfer will be started. In the program this competing transfer is shown as below.

You can remove this block with a click on the < button. In opening window (see below) click on the button. This will as well remove error messages from previous transfers.

A further option for restriction of the transfers is the comparison of the date when the last transfer has been executed. Click on the button to change it to . In this state the program will compare the dates of the transfers and execute the transfer only if new data are available.

Cache Database Webservices

Sources from webservices

To provide details for datasets linked to webservices like Catalogueof Life etc. in the cached data, this information can be transferred into the cache database together with the data from the DiversityCollection database. Use the button to add the webservice you need.

Transfer

To transfer the data of a webservice into the ** ** cache database use the button. A window a shown below will open where you must select all projects related to the webservice you selected. **You must transfer the projects into the  ** cache database before transferring the webservice data! Only entries found within these projects will be transferred.

Use the buttons to inspect the content. Names that are already present will not be transferred again. So if you need these values to be updated you have to remove it (see below) and add it again. Links that refer to lists of names instead of single names will not be inserted. These links will be stored in the Errorlog to enable their inspection.

To transfer the data into the Postgres database, you have to connect with a database first. Use the button to transfer the data from the ** ** cache database into the  Postgres database and the buttons to inspect the content.

Removal

With the button you can remove the sources together with the views (see above).

Export CSV

Export CSV

Notes:

• The Export CSV function provides a direct copy of selected database table as tabulator separated text file. If you want to generate flles that give a strutured overview of descriptors or description data, you should prefer the Export … Lists or the Export Wizard (coming soon).
• The Export CSV function requires the “Bulk Copy” tool, which is part of a local Microsoft SQL Server installation. If it is not available on your computer, you will get an error message after opening the window. Search for the “bcp-utility” to get information about a free download and installation of the “Bulk Copy” tool.

To export the tables of the database in the a tabulator, comma or semicolon separated format, choose Data → Export → Export CSV … from the menu. A window as shown below will open where you can select the tables to be exported in sections Selection criteria and in the Tables for export.

To start the export click on the Start export button. By default the data will be exported into a directory <resources directory>\Export\<database_name>. Click on the button to select a different target directory before starting export.

After export the tables are marked with green backgound, if table schema and data were exported successfully. If only the data were exported, this is marked with yellow background, if nothing was exported, the background is red. A detailled export report can be viewd by a click on the export result file name.  

Export Questionnaires

Export questionnaires

With this form you can export description data from the database to HTML forms. You can open the generated HTML files, edit the data in the form and re-import the changes by using the import questionairedata function. Choose Data -> Export → Export questionnaires … from the menu to open the window for the export.

In the Export project section all projects of the database are shown as a tree. Select here the project that shall be exported. In case of hierarchically organized projects the subordinated projects will be included for export, if the Include child projects option is checked. You may pass a description list to the form by starting a query in mode “Edit descriptions”. If all descriptions in the list belong to the same project, you have the option to select single descriptions for export. In this case the Export project section shows the button  to switch to the list view (see below).

In the Expot descriptions section you find all description titles that have been passed to the export form (see below). You may select all entries by clicking the all button, deselect all entries by clicking the none button or toggle your selection by clicking the swap button. By clicking the button you will return to the Export project view.

The Withheld data section allows control over export of datasets that contain entries with data status “Data withheld”. Option Supress whole description (default) excludes all descriptions form export where at least on descriptor is marked with “Data withheld”. Option Hide withheld descriptor excludes only the corresponding descriptor data from the description. Option Export withheld data does not exclude any data from export.

Options 

The Options section allows the selection of a Descriptor tree to determine the sequence and selection of descriptors for output. If a structured descriptor tree is selected, the first level descriptor tree nodes will be inserted as headers to structure the document. If option Adapt descriptor names is checked, the descriptor names will be prefixed with the headers from the derscriptor tree. In this case you may specify a string to connect the names in the texrt box at the right of the option. If Generate index page is checked, an alphabetically sorted index with links to the individual description pages will be generated.

You may Hide descriptor status data in the generated forms. Furthermore output of the ordinal numbers for descriptors rsp. categorical states may be suppressed by using the options Hide descriptor numbers rsp. Hide state numbers.  With options Include notes and Include modifier/frequency you can control if notes and modifier or frequency values shall be included in the generated questionaires. With Include details a text field for editing the item details will be inserted.

By default a font type with serifes is used for the HTML output, select Sans serif font for an alternative. The colors of several HTML elements may be adapted to the personal preferences by clicking on button . With Text rows you can adjust the size of text boxex used for text and molecular sequence descriptors. Option Scrollable states generates an alternative layout for categorical descriptors, where the state values are arranged in scroll boxes. 

Check Include resources to include images for descriptions, descriptors, categorical states and descriptor tree nodes in the questionnaire. In the generated HTML questionnaire the images will be zoomed by a dedicated factor, when the mouse cursor is moved over it. The zoom factors may be adjusted by clicking on button . If you check the option Insert fields for new resources, the specified number of input fields for entering new description resources (name and URL) will be inserted in the forms (see image below on the left). 

Check Include scopes to include a section for scope data in the questionnare. The scope values of the types “Sex”, “Stage”, “Part” and “Other scopes”, which may be administered in the Edit project section, are included as check boxes. For the other (general) scope types input boxes will be generated, where new values may be entered or existing values can be edited. If a scope value is linked to a database entry, e.g. of a DiversityTaxonNames database, it cannot be modified. In this case only a fixed text with the reference to the target database will be inserted in the questionnaire.

By clicking on button you may open an option window where you can change this default behaviour. You may adjust for which scope types an input box for a new scope value shall be inserted or if scopes that cannot be modified shall be displayed (see image above on the right).

See below an example of a questionnaire where imput fields for a new description resource and all general scope types are present..

If for the selected projects translations are stored in the database, you may chose the Export language. If for any element no translation in the chosen export language is present, automatically the original text will be used. With field Language settings you can control the presentation of floating point values in the output, in field Send answer to you may enter the mail address to return the results. 

Test

To check the export, click on the Test export button. In the Output preview section an empty form for entering a new item will be displayed (see picture below). The file name is generated as <resources directory>\Questionnaires\<Database name>_<Project>.txt. This default setting may be changed by editing the File name or by navigating at the target location by pressing the button besides the file name. Button opens the form in an external web browser.

Since the generated form can be opened with any modern web broswer, you may distribute it to easily collect item data by people that do not have access to the Diversity Descriptions database. Even if the form is published as a web page, the collected data stay completele local in the user’s web browser and are not uploaded to any server. To get the  data into the database, they must be “downloaded” form the form and sent back to a database editor for import (see item Send reply below).

Export

Before starting the export, the export file name should be checked. The file name is generated as <resources directory>\Questionaires\<Database name>_<Project>.html. This default setting may be changed by editing the File name or by navigating at the target location by pressing the button besides the file name text box. During export a number of HTML files will be generated. The document for entering a new item has the specified file name. For entries present in the database the description id is appended. Furthermore an index file with postfix “_Index” will be generated, where you may navigated to each exported questionnaire.

To generate the HTML files press the Start export button. During export the icon of the button changes to  and you may abort processing by clicking the button. Button opens the form visible in the Output preview section in an external web browser (see image above).

Send reply

When you opened a HTML form in the web browser and modified data, you may download them as a text file for databaseimport. At the bottom of the HTML form click the button Download revised description (see image below). Data collection is completely done in the user’s local web browser, nothing is uploaded to a server. Since data collection is done using javascript, please take care that the script is not blocked by the web browser.

List Export

List export

There are several exports for tabulator-separated text files:

Export descriptors list: Export descriptor data as tabulator separated text file.

Export descriptions list: Export description data as tabulator separated text file.

Export sample data list: Export sample data as tabulator separated text file.

Export resource data list: Export resource data as tabulator separated text file for data review and possible re-import of modified data.

Export translations list: Export translations as tabulator separated text file for data review and possible re-import of modified data.

Matrix Export Wizard

Matrix export wizard for tab separated lists

With this form you can export the descriptor and description data from the database to a tabulator separated text file. The output includes the database keys. Furthermore you have the option to create rsp. update import mapping data and generate an matrix import schema. Therefore you may correct the data, e.g. by using a spreadsheet program and re-import the changes by using the matrix import wizard. Choose Data → Export -> Matrix wizard … from the menu to open the window for the export.

In the Export project section all projects of the database are shown as a tree. Select here the project that shall be exported. In case of hierarchically organized projects the subordinated projects will be included for export, if the Include child projects option is checked. You may pass a description list to the form by starting a query in mode “Edit descriptions”. Now you have the option to select single descriptions for export. In this case the Export project section shows the button  to switch to the list view (see below).

In the Export descriptions section you find all description titles that have been passed to the export form (see below). You may select all entries by clicking the all button, deselect all entries by clicking the none button or toggle your selection by clicking the swap button. By clicking the button you will return to the Export project view.

The Withheld data section allows control over export of datasets that contain entries with data status “Data withheld”. Option Supress whole description (default) excludes all descriptions form export where at least on descriptor is marked with “Data withheld”. Option Hide withheld descriptor excludes only the corresponding descriptor data from the description. Option Export withheld data does not exclude any data from export.

The Options section allows the selection of the Descriptor tree: and descriptor sequence number bounds (From descriptor: and to descriptor:) for restriction of output table columns.

If you select option Use sequence, the descriptor state sequence numbers will be inserted into the output table instead of the state names. These sequence numbers will be inserted into the selected import session (see below) for a later re-import of the data. If you export the descriptive data to edit them with a spreadsheet tool, e.g. Microsoft Excel, you have to identify the active categorical states by their sequence number. 

To include all exported data in quotes, check option “quotes”. Select Trim end to remove white characters (e.g. blank or word wrap) at the end of texts. By specifying the State separator: (default , ) you determine how multiple categorical state values will be concatenated in the table cells. By changing the selected Lang. settings: you may adapt the output of floating point numbers or date and time fields to your needs.

Import session

The section Import session: is relevant if you want to edit the description data in a separate spreadsheet programme and re-import the edited data using the Matrix Import Wizard. To select an import session cick on button Select and a window as shown below will be opened. You may either select an existing import session, which will be updated with the exported data, or create a new one.  

During generation of the matrix data file the relevant data for re-import will be stored in the selected import session. Additionally an xml import schema file will be generated as  <resources directory>\Export\Matrix_<Database name>_<Project>_Schema.xml. If you do not require the data for re-import, simply do not select an import session or click on button to cancel an existing selection. 

Export

Before starting the export, the export file name should be checked. The file name is generated as <resources directory>\Export\Matrix_<Database name>_<Project>.txt. This default setting may be changed by editing the File name or by navigating at the target location by pressing the button besides the file name. To generate an output without BOM, release the button.  

To check the export, click on the Test export button. In the Output preview data grid the first few lines will be displayed (see picture above). To generate the table file press the Start export button. During test and export the icon of the button changes to  and you may abort processing by clicking the button.

Check EML

Check EML file

With this form you can check if an XML file is compliant to the EML2.1.1 or EML 2.2.0 schema. Choose Data -> File operations -> Check EML file … from the menu. After opening the window shown below the schema files will be automatically loaded. You may select the schema that shall be used with combo box Schema version (see image below). Starting with DiversityDescriptions v. 4.3.5 the EML export will be done using EML 2.2.0.

In the window click on the button to select the file you want to check. The check results will be diplayed in the center part of the window. By clicking the reload button  you can start a new check (see image below).  

Check SDD

Check SDD file

With this form you can check if an XML file is compliant to the SDD 1.1rev 5 schema. Choose Data -> File operations -> Check SDD file … from the menu. After opening the window shown below the schema files will be automatically loaded.

In the window click on the button to select the file you want to check. The check results will be diplayed in the center part of the window. If you generated a SDD file using Diversity Descriptions with deactivated Comptible option, the check result may show warnings for elements with missing schema information. You may check the option Include specific schema extensions, then the Diversity Descriptions specific schema definitions will be included. By clicking the reload button  or selecting another file you can start a new check (see image below).  

Convert DELTA

Convert DELTA file to SDD or EML

With this form you can directly convert data from a file in DELTA format into an XML file according schema SDD 1.1 rev5. No connection to a database is needed for the conversion. Choose Data -> File operations -> Convert data file → DELTA to SDD … from the menu to open the window. In the window click on the button to select the file with the data you want to convert. If the Multi-file option is selected before pressing the button, a folder selection window opens to select the folder where the DELTA files are located. For muti-file processing currently the files “chars”, “items”, “specs” and “extra” are evaluated. If during analysis any problem occurs, you may click on the button to reload the file and re-initialize the window.

The contents of the file will be shown in the upper part of the File tree tab page. If special characters are not displayed corretly, try a different Encoding setting, e.g. “ANSI”, and reload the document using the button.

The Insert “variable” state controls the handling of the DELTA state “V” for categorical summary data. If possible, a categorical state “variable” is inserted to the descriptor data and set in the summary data, when the state “V” is present in the description data.

If the Check strings for illegal characters  option is checked, all string literals that shall be exported from database are scanned for illegal non-printable characters and matches are replaced by a double exclamation mark ("‼"). Activating this option may increase the analysis processing time.

In the file tree you may deselect entries that shall not be converted. Use that option very carefully, because if you deselect entries that are being referenced by other parts of the input tree, e.g. descriptors referenced by descriptions, the analysis step might become erronous!

If during reading of the files expressions cannot be interpreted, suspicious entries are maked with yellow background (warning) in the file tree. When you move the mouse curser over the marked entries, you get additional information as tool tip or the tree node text itself tells the problem (see example below).  

Analysis

To analyse the data in the file click on the Analyse data button. During the analysis the program checks the dependencies between the different parts of the data and builds up an analysis tree in the lower part of the window. The analysis tree contains all data in a suitable format for the final step. During data analysis the icon of the button changes to  and you may abort processing by clicking the button. 

In the Analysis settings section (see image below) you set the document’s Language. You man change the display and sorting of the entries in the Language combo box from “<code> - <description>” to “<description> - <code>” (and back) by clicking the button . If you need language codes that are not included in the list, click the button. For more details see Edit language codes.

The Insert “variable” state controls the handling of the DELTA state “V” for categorical summary data. If possible, a categorical state “variable” is inserted to the descriptor data and set in the summary data, when the state “V” is present in the description data.

If the Check strings for illegal characters  option is checked, all string literals that shall be exported from database are scanned for illegal non-printable characters and matches are replaced by a double exclamation mark ("‼"). Activating this option may increase the analysis processing time.

In DELTA text in angle bracket (<text>) usually denotes comments, which are by default imported into the “Details” fields of the database. In the lower parts of the Analysis settings you may adjust a different handling for description, descriptor and categorical state items. 

• For DELTA comments in descriptions you may Move comments to details (default) or Keep comments in description titles.
• For DELTA comments in descriptors you may Move comments to details (default), Move comments to notes or Keep comments in descriptor titles. 
• For DELTA comments in categorical states you may Move comments to details (default) or Keep comments in categorical state titles.

After changing one of these settings click on the Analyse data button to make the changes effective.

After analysis a message window informs you if any warnings or errors occured. You can find detailled error and warning information at the file and/or analysis trees by entries with red text (error) or yellow background (warning). When you move the mouse curser over the marked entries, you get additional information as bubble help or the tree node text itself tells the problem (see example below). By clicking on the status text besides the progress bar, you can open an analysis protocol (see below, right). 

If an analysis error occured, you are not able to proceed. You will first have to correct the problem, e.g. by excluding the erronous descriptor in the example above (after reloading the file). If a warning occured, it might not cause further problems, but you should take a closer look if the converted data will be correct.

Write data

Pressing the Generate file button in the Write SDD group box opens a window to select the target XML file. By default the target file has the same name as the DELTA file, followed by the extension “.xml”. The Comptible option controls generation of files with most possible compatibility to the SDD standard. On the other hand some data might not be present in the generated file, if this option is activated.

As an additional option you may generate file according the EMLschema, which consists of a data table (tabulator separated text file) and an XML file that contains the metadata including column descriptions. Click on the Generate file button in the Write EML group box. The generated file names will have the endings "_EML_DataTable.txt" and "_EML_Metadata.xml".

Pressing the drop down button EML settings in the Write EML group box opens the EML writer options. You can chose to include a special sign for empty column values or set the columns values in quotes (see left image below). Furthermore you may shose the column separator (tab stop rsp. comma) an decide if multiple categorical states shall be inserted as separate data columns. If you already generated EML files, the used settings will be automatically saved and you may restore them using the option Load last write settings. Finally click button EML settings to close the option panel. 

Handling of special DELTA states

In the DELTA format the special states “-” (not applicable), “U” (unknown) and “V” (variable) are available for categorical and quantitative characters. These states are treated in the folloging manner during import:

• “-” (not applicable)
  The data status “Not applicable” is set.
• “U” (unknown)
  The data status “Data unavailable” is set.
• “V” (variable)
  The data status “Not interpreterable” is set.

Convert SDD

Convert SDD file to DELTA or EML

With this form you can directly convert data from a file in XML file according schema SDD 1.1 rev5 into a DELTA file. No connection to a database is needed for the conversion. Choose Data → File operations -> Convert data file → SDD to DELTA … from the menu to open the window. In the window click on the button to select the file with the data you want to convert. If during analysis any problem occurs, you may click on the button to reload the file and re-initialize the window.

The contents of the file will be shown in the upper part of the File tree tab page. In the Analysis settings part you find the documents’ default language. If additional laguages are contained in the document, you may select one of them as the new language of the DELTA file. By checking Import translations you select all additional document languages for the analysis step. This option is automatically pre-selected if more than one language has been found in the file. In the bottom part of the window you find the actual processing state.

In the file tree you may deselect entries that shall not be imported into the database. Use that option very carefully, because if you deselect entries that are being referenced by other parts of the input tree, e.g. descriptors referenced by descriptions, the analysis step might become erronous!

Analysis

To analyse the data in the file click on the Analyse data button. During the analysis the program checks the dependencies between the different parts of the data and builds up an analysis tree in the lower part of the window. The analysis tree contains all data in a suitable format for the final step. During data analysis the icon of the button changes to  and you may abort processing by clicking the button. 

After analysis a message window informs you if any warnings or errors occured. You can find detailled error and warning information at the file and/or analysis trees by entries with red text (error) or yellow background (warning). When you move the mouse curser over the marked entries, you get additional information as tool tip or the tree node text itself tells the problem (see examples below). By clicking on the status text besides the progress bar, you can open an analysis protocol (see below, right). 

If an analysis error occured, you are not able to proceed. You will first have to correct the problem, e.g. by excluding the erronous descriptor in the example above (after reloading the file). If a warning occured, it might not cause further problems, but you should take a closer look if the converted data will be correct.

Write data

Pressing the Generate file button in the Write Delta group box opens a window to select the target delta file. By default the target file has the same name as the SDD file, followed by the extension “.dat”. The Comptible option controls generation of files with most possible compatibility to the DELTA standard. On the other hand some data might not be present in the generated file, if this option is activated.

As an additional option you may generate file according the EMLschema, which consists of a data table (tabulator separated text file) and an XML file that contains the metadata including column descriptions. Click on the Generate file button in the Write EML group box. The generated file names will have the endings "_EML_DataTable.txt" and "_EML_Metadata.xml".

Pressing the drop down button DELTA settings in the Write DELTA group box opens the DELTA writer options. You can chose to include some detail text and notes in the DELTA output (see left image below). For descriptions, descriptors or categorical states the details will be appended as DELTA comments (included in angle brackets “< … >”) to the respective titles. The notes will be appended as DELTA comments of the corresponding summary data. If you already generated DELTA files, the used settings will be automatically saved and you may restore them using the option Load last write settings. Finally click button DELTA settings to close the option panel. 

Pressing the drop down button EML settings in the Write EML group box opens the EML writer options. You can chose to include a special sign for empty column values or set the columns values in quotes (see right image above). Furthermore you may shose the column separator (tab stop rsp. comma) an decide if multiple categorical states shall be inserted as separate data columns. If you already generated EML files, the used settings will be automatically saved and you may restore them using the option Load last write settings. Finally click button EML settings to close the option panel. 

Handling of special sequence data

While SDD can handle molecular sequence data, for DELTA export these data will be exported as text data. To preserve the sequence specific descriptor data, they will be inserted into the text character as a special comment with the format, e.g. “#6. Sequence descriptor <[SequenceCharacter][ST:N][SL:1][GS:-][/SequenceCharacter]>/”.

If the analysis tree includes sample data, they will be included as items at the end of the DELTA file. The naming of those spetial items will be <description name> - <event name> - Unit <number>. Sampling event data will not be included in the DELTA file.

Matrix Wizard

Matrix import wizard for tab separated lists

The table oriented import wizard works fine if you have separate lists for descriptor and description data. Usually this type of tables is generated by an export of data from a database. A typical example for that cases is described in the import wizardtutorial. If no dedicated application for collecting description data is available, most commonly a spreadsheet program like MS Excel or Open Office Calc is used to collect the description data. Typically the table columns represent a single character (=descriptor) and the table rows represent the items (=description or sample data). Importing data from such a “matrix” into Diversity Descriptions with the table oriented import wizard usually requires a lot of manual adaptions. Therefore the specialized “Matrix Wizard” was designed to import the most important descriptor and description data in a single import step. 

As usual you should create a new project and install a descriptor tree to collect the dedicated descriptors. Then choose Data → Import → Import wizard → Matrix wizard … from the menu. As know from the import wizard, a window to create or select a import session will be shown.

After selecting or creating an import session a window as shown below will open that will lead you through the import of the data. 

With the selection box Target: you may select which data shall be imported: 

•  Select Description to import summary data
•  Select Sampling event to import sample data

Remark: Example files and XML schemas to import summary or sample data using the matrix wizard are provided in the tutorialfiles or may be downloaded from the Diversity Descriptions example file repository. Find the example data in folders “Biomass as description” and “Biomass as sample” 

Wizard

Import wizard for tab separated lists

With this import routines, you can import data from text files (as tab-separated lists) into the database. For a comprehensive real-life example that shows many features of the import wizard take a look at the import wizard tutorial. 

Choose Data → Import -> Import wizard and then the type of data that should be imported, e.g. Import descriptors … from the menu. If you did not use the import wizard before, the following window is shown to create a new import session.

In section Session project the projects with write access are listed for selection. In section Session description you should enter a detailled text description. If already an import session is present in the database, the window below will be shown where you may select the session. You may select one of the offered sessions or create a new one by selecting Create new import session. 

After selecting or creating an import session a window as shown below will open that will lead you through the import of the data. The window is separated in 3 areas. On the left side you see a list of possible data related import steps according to the type of data you choosed for the import. On the right side you see the list of currently selected import steps. In the middle part the details of the selected import steps are shown.

Choosing the File

As a first step, choose the File from where the data should be imported. The currently supported format is tab-separated text. Then choose the Encoding of the file, e.g. Unicode. The Start line and End line will automatically be set according to your data. You may change these to restrict the data lines that should be imported. The not imported parts in the file are indicated as shown below with a gray background. If the First line contains the column definition this line will not be imported as well. If your data contains e.g. date information or floating point values, where notations differ between countries (e.g. 3.14 - 3,14), choose the Language / Country to ensure a correct interpretation of your data. Finally you can select a prepared Schema (see chapter Schema below) for the import.

Choosing the data ranges

In the selection list on the left side of the window (see below) all possible import steps for the data are listed according to the type of data you want to import.

Certain tables can be imported in parallel. To add parallels click on the button (see below). To remove parallels, use the button. Only selected ranges will appear in the list of the steps on the right (see below).

To import information of logging columns like who created and changed the data, click on button in the header line. This will include an additional substeps for every step containing the logging columns (see below). If you do not import these data, they will be automatically filled by default values like the current time and user.

Attaching data

You can either import your data as new data or Attach them to data in the database. Select the import step Attachment from the list. All tables that are selected and contain columns at which you can attach data are listed (see below). Either choose the first option Import as new data or one of the columns the attachment columns offered like “id” in the table “Descriptor” in the example below.

If you select a column for attachment, this column will be marked with a blue backgroud (see below and chapter Table data).

Merging data

You can either import your data as new data or Merge them wih data in the database. Select the import step Merge from the list. For every table you can choose between Insert, Merge, Update and Attach (see below).

The Insert option will import the data from the file independent of existing data in the database.

The Merge option will compare the data from the file with those in the database according to the Key columns (see below). If no matching data are found in the database, the data from the file will be imported, otherwise the data will be updated..

The Update option will compare the data from the file with those in the database according to the Key columns. Only matching data found in the database will be updated.

The Attach option will compare the data from the file with those in the database according to the Key columns. The found data will not be changed, but used as a reference data in depending tables. 

Table data

To set the source for the columns in the file, select the step of a table listed underneath the Merge step. Some columns may be grouped below the table name as shown for the Descriptor table. 

Click on one of the subordinated column groups and in the central part of the window the data columns avaialble for importing will be listed in the central part of the window. In the example shown below the column is used to attach the new data to data in the database.

All columns that have not been grouped beneath the table may be accessed by selecting the table ste itself. In the example shown below table Descriptor was selected to supply the “data_entry_note” column for import.

A reminder in the header line will show you what actions are still needed to import the data into the table:

• Please select at least one column   = No column has been selected so far.
• Please select at least one decisive column   = If data will be imported depends on the content of decisive colums, so at least one must be selected.
• Please select the position in the file   = The position in the file must be given if the data for a column should be taken from the file.
• Please select at least one column for comparision   = For all merge types other than insert columns for comparision with data in the database are needed.
• From file or For all   = For every you have to decide whether the data are taken from the file or a value is entered for all
• Please select a value from the list   = You have to select a value from the provided list
• Please enter a value   = You have to enter a value used for all datasets

The handling of the columns in described in the chapter columns.

Testing

To test if all requirements for the import are met use the Testing step. You can use a certain line in the file for you test and than click on the Test data in line: button. If there are still unmet requirements, these will be listed in a window as shown below.

If finally all requirements are met, the testing function will try to write the data into the database and display you any errors that occurred as shown below. All datasets marked with a red backgroud, produced some error.  

To see the list of all errors, double click in the error list window in the header line (see below).

If finally no errors are left, your data are ready for import. The colors in the table nodes in the tree indicate the handling of the datasets: INSERT, MERGE, UPDATE, No difference. Attach, No data. The colors of the table colums indicate whether a colums is decisive , a key column or an attachment column.  

If you suspect, that the import file contains data allready present in the database, you may test this an extract only the missing lines in a new file. Choose the attachment column (see chapter Attaching data) and click on the button Check for allready present data. The data allready present in the database will be marked red (see below). Click on the button Save missing data a text file to store the data not present in the database in a new file for the import. 

If you happen to get a file with a content as shown below, you may have seleted the wrong encoding or the encoding is incompatible. Please try to save the original file as UTF8 and select this encoding for the import. 

Import

With the last step you can finally start to import the data into the database. If you want to repeat the import with the same settings and data of the same structure, you can save a schema of the current settings (see below).

Tables

CollectionSpecimen (CollectionSpecimen)
Parent: CollectionEvent
Merge handling: Insert

IdentificationUnit_1 (IdentificationUnit)
Parent: CollectionSpecimen
Merge handling: Merge

IdentificationUnitAnalysis_1_1 (IdentificationUnitAnalysis)
Parent: IdentificationUnit_1
Merge handling: Update

 Lines that could not be imported will be marked with a red background while imported lines are marked green (see below).

If you want to save lines that produce errors during the import in a separate file, use the Save failed lines option. The protocol of the import will contain all settings acording to the used schema and an overview containing the number of inserted, updated, unchanged and failed lines (see below).

Description

A description of the schema may be included in the schema itself or with a click on the button generated as a separate file. This file will be located in a separate directory Description to avoid confusion with import schemas. An example for a description file is shown below, containing common settings, the treatment of the file columns and interface settings as defined in the schema.

File columns

Interface settings