Extended Query Description

Extended query parameter - Description conditions

Press button Add description in the Query parameter tool strip to insert a new query parameter condition to search for specific values in the description data. A control representing a single description data filter will be added at the end of the query parameter list. You may enter filter criteria for the description ID, Title text, Details text and AID text (alternate id). To enable a certain item, select the check box before the parameter text (picture below). For the numeric parameter ID you may enter a number and select the comparer by clicking the  button . Available comparers are = (equal), ≠ (not equal), < (less), > (greater), ≤ (less or equal) and ≥ (greater or equal).   

For the text parameters (title, details and AID) you may enter a search text and select one the matching options Contains, Starts with, Ends with or Exact match. Capitalization is not taken into account. For option Contains the database text must contain the search text. The options Starts with and Ends with only check the database text begin rsp. end for a match. Finally option Exact match requires identical texts. For option Exact match you may enter an empty text, which will match if in the database an empty text or a NULL value (if a NULL value is allowed) is present. For the other matching options an empty search text does not make sense because it will allways match. By checking the Invert option you may invert the search criteria. E.g. the option Exact match with empty search text and Invert will search for “text present” (“not empty string”). You may use the wildcards _, [] and [^] in your search text, but not %. % is automatically inserted in the correct places by selecting the matching option.

If you entered more than one condition as shown in the image above (ID and Title text), the default adjustment is that all conditions must match. This is indicated by the symbol behind the text Description data in the header line, which means “logical AND”. If you want to get a match for any of the entered conditions, you may click on the symbol to change it to “logical OR” (see image below). 

The next example shows how two description conditions are used to exclude a certain number range of the ID from the result list. The first condition restricts the result list to all IDs smaller than 343. The second condition adds all results with IDs larger than 426, which is achieved by a logical OR of the condition operator (). In this example it does not matter which one of the two conditions is marked as “AND” rsp. “OR”. If both conditions would be marked as “OR”, there would not be any restriction left except the project.  

Continue with:

• Extended query parameter
• Extended query parameter - Scope conditions
• Extended query parameter - Descriptor conditions
• Save and load extended query

Extended Query Descriptor

Extended query parameter - Descriptor conditions

Press button Add descriptor in the Query parameter tool strip to insert a new query condition for the search of specific values in the description summary data. A control representing a single descriptor filter will be added at the end of the query parameter list. Now you have to select the descriptor that shall be included in the search filter. This can be done by selecting the descriptor’s sequence number with combo box No.:, from an alphabetical descriptor list with combo box Descriptor: or by selecting from the descriptor tree with button (see picture below). 

After selecting the descriptor the query control shows query parameters depending on the descriptor type (see below). You may search for descriptions that do not have any data for the selected descriptor by checking option absent. The currently marked parameter control is shown with light blue background. You can delete the current query control by clicking on button Remove or all query controls by clicking on button in the Query parameter tool strip.

In the parameter control the descriptor type is indicated by symbols in the beginning of the first line ( categorical, quantitive, text or sequence). If additional information for the descriptor are available in the database, you can show them as bubble help by moving the mouse cursor over the symbol. If resource data are available for a descriptor, the symbol is displayed with coloured background and you can view them by double-clicking it (see image below).

Each parameter control has two main sections:

• At the left you find the section for the descriptor parameter, depending on its type (Categorical states, Statistical measures, Descriptive text or Molecular sequence). If this section is checked and no other data have been entered, the query filters for all descriptions that include any value for that descriptor, e.g. any descriptive text or any categorical state. You may search for descriptions without descriptor data by checking option no value. By entering additional query data, the filter becomes more restrictive.  

• At the right you find the section for the descriptor Status data. If this section is checked and no status value has been selected, the query filters for all descriptions that include any status data value for that descriptor. At the end of the Status data check box by default the symbol indicates that all selected data status values must be present in the matching descriptions. By checking dedicated status data values, the filter becomes more restrictive (logical AND). If you click on the symbol you can change it to (logical OR) to find matches for any of the selected status data values.  

If both sections are selected, matching descriptors must fulfill both conditions, i.e. the value must be present and the status data value must be set.

Notes

In the descriptive data you may enter free text notes to all text and sequence data. Furthermore individual notes may be entered for every selected categorical state and statistical measure. You may include additional query contitions for each of those notes, therefore the symbol is included at the appropriate places. To enter a query condition, click the button and a window as shown below will be opened.

You may enter a Search text that shall be part of the notes text. Capitalization is not taken into account. For option Contains the notes text must contain the search text. The options Starts with and Ends with only check the descriptor text begin rsp. end for a match. Finally option Exact match requires identical texts. Check the option Invert to invert the search criteria. You may use the wildcards _, [] and [^] in your search text, but not %. % is automatically inserted in the correct places by selecting the matching option.When you click OK the additional query condition will be active and the icon background changes to red (). If you want to remove the query condition, open the notes form, uncheck the Enable notes filter option and click OK.

For text and molecular sequence data exactly one note is possible for each descriptor value, therefore you find exactly one symbol for entering a query condition. For categorical and quantitative data you may enter a note for each single state rsp. statistical measure. Here you may search for notes of the specific state rsp. measure by clicking on the symbol located at the element. Additionally you may search for notes entered at any state or measure of the descriptor. Therefore an additional symbol is located in the header section besides the no value check box (see image above). 

Modifier and frequency

In categorical and quantitative descriptive data you may select modifier values for every selected categorical state and statistical measure. For categorical data additionally a frequency value may be selected. You may include additional query contitions for each of those modifier and frequency values, therefore the symbols (modifier) and (frequency) are included at the appropriate places. To enter a query condition, click the button and a window as shown below will be opened.

You may select the modifier rsp. frequency values for filtering from the recommended values of the current descriptor. Check the option Invert to invert the search criteria. A hint text explaining the resulting filter will be displayed in the lower area of the window. When you click OK the additional query condition will be active and the icon background changes to red (, ). If you want to remove the query condition, open the form, uncheck the Enable filter option and click OK.

Continue with:

• Extended query parameter
• Extended query parameter - Description conditions
• Extended query parameter - Scope conditions
• Descriptor conditions - Categorical states
• Descriptor conditions - Statistical measures
• Descriptor conditions - Descriptive text
• Descriptor conditions - Molecular sequence
• Save and load extended query

Extended Query Scope

Extended query parameter - Scope conditions

Press button Add scope in the Query parameter tool strip to insert a new query parameter condition to search for specific description scope data. A control representing a single description scope filter will be added at the end of the query parameter list. Now you have to select the Scope type that shall be included in the search filter. You have the choice between the general scope types ( Geographic area, Sampling plot, Reference, Observation, Specimen and Taxon name) and the project specific scope types (Other scope, Sex, Stage and Part). The project specific scope types are only shown if values have been entered in the database (see Editing the project). 

General scopes

After selecting the scope type a query control as shown in the image below is available. You may search for descriptions that do not have any scope of the selected type by checking option no value. Otherwise the two text search fields Scope text and Scope URI are available, which can be activated by checking the box before the text. 

You may enter a search text and select one the matching options Contains, Starts with, Ends with or Exact match. Capitalization is not taken into account. For option Contains the database text must contain the search text. The options Starts with and Ends with only check the database text begin rsp. end for a match. Finally option Exact match requires identical texts. For option Exact match you may enter an empty text, which will match if in the database an empty text or a NULL value (if a NULL value is allowed) is present. For the other matching options an empty search text does not make sense because it will allways match. By checking the Invert option you may invert the search criteria. E.g. the option Exact match with empty search text and Invert will search for “text present” (“not empty string”). 

With the query option Module filter you may build a item list from a linked database using a remore query. You may search for scopes that match one of the entries (operator ∈) or do not link any of the entries (operator ∉). You may select the operator by clicking the drop down button . To build the list click on the button to open a window where you can search for values within the linked module. A click on the button will show a list of the selected items, button the related database (see image below).

A click on the button switches the filter mode between link search and text search. For some scope types, e.g. Taxon names, additional operations are available (see image below). While for the standard operations all elements of the query result are entered into the item list, they will take the selected value as starting value and add synonym/hierarchy values.

Operator	Meaning	Example
∈	Search for entries with a list ...	Rosa | Rosa caninia | ...
∉	Search for entries not within a list	Rosa | Rosa caninia | ...
+H	search for entry including lower hierarchy	Picea | Picea abies | ...
+S	search for entry including synonyms	Picea abies | Pinus abies | ...
+HS	search for entry including lower hierarchy and synonyms	Picea | Picea abies | Pinus abies | ...
	Change filter mode between link and text	http://tnt.diversityworkbench.de/TaxonNames_Plants/4269 Picea abies L.

If you entered more than one condition, the default adjustment is that all conditions must match. This is indicated by the symbol behind the text Description scopes and the no value check box, which means “logical AND”. If you want to get a match for any of the entered conditions, you may click on the symbol to change it to “logical OR”. 

Project scopes

After selecting a project specific scope all available values inserted in the database will be listed (see image below). You may select one ore more of the avalable values. Furthermore you may check the invert option to search for description entries that do not include the specified scope value. 

If you entered more than one condition, the default adjustment is that all conditions must match. This is indicated by the symbol behind the text Description scopes and the no value check box, which means “logical AND”. If you want to get a match for any of the entered conditions, you may click on the symbol to change it to “logical OR” (see image below). 

The project specific scope values must be marked as “recommended” for each project. In the query control you will be shown every value, but the not recommended ones will be listed with an appropriate text shown in the image below. 

Continue with:

• Extended query parameter
• Extended query parameter - Description conditions
• Extended query parameter - Descriptor conditions
• Save and load extended query

Edit Description Continuous View

Editing the description - Continuous view tab

The continuous view tab provides an alternate view on the descriptive data. In the Summary data only the descriptors are included where data have been entered. At the right side there is a tool strip. The displayed buttons will change depending on the selected entry. The descriptor types categorical, quantitive, text and sequence are indicated by different colours and icons as shown in the example at the bottom. By pressing the button on the tool strip this example may be hidden. Descriptors that are marked as mandatory are displayed with light red text, descriptors that are inapplicable are greyed out. Additionally coloured background indicated possible problems, e.g. if an inapplicability rule has been ignored or a not allowed descriptor has been used. A tool tip text shows the error or warning reason when the mouse curser is moved over the element.

By pressing the button the tree view may be expanded to display the contained categorical states, quantitative measures or texts. By pressing the button the tree will be collapsed to descriptor level.

To insert a descriptor press , to delete a descriptor select the entry and press . If only a single value shall be removed, select the entry and use the button. All these functions can alternatively be accessed by the context menu by a right-click on the tree node.

If for a descriptor resources, e.g. pictures, are avaialble, this is indicated by the background color of the descriptor icon. Categorical states with resources are marked with icon (see first entries in picture above). The resources may be accessed by a right-click on the tree node and selecting context menu item View resources (see picture above).

Structured descriptor view

If for the project a structured descriptor tree is defined, i.e. a descriptor tree that contains at least one descriptor tree node, the Descriptor tree: drop-down list is shown below the descriptor tree (see image below left). If a descriptor tree is selected, the descriptor tree node hierarchy will be included in the output and the descriptors will be arranged accordingly. The names of the descriptor tree nodes and the descriptors will be reduced by parts contained in the supriour hierarchy nodes (see image below right).  

If for a descriptor tree node resources are available, this is indicated by the grey background color of the descriptor tree icon. The resources may be accessed by a right-click on the tree node and selecting context menu item View resources. Additional descriptor tree node information, e.g. an optional abbreviation or details text can be viewed as bubble help by placing the mouse cursor over the item.  

Editing summary data

A click on opens an edit window at the right part of the tab. This window provides the same functions as available in the descriptor view tab. The picture below shows the resource linked to a categorical state that can be opened by doule-clicking the state ID in the Categorical states section. 

Continue with:

• Edit descriptions - Main attributes tab
• Edit descriptions - Descriptor view tab
• Edit descriptions - Molecular sequence symbols
• Edit descriptions - Import sequence data
• Edit descriptions - Export sequence data
• Edit descriptions - Resource links tab
• Edit descriptions - Sample data tab

Edit Description Descriptor View

Editing the description - Descriptor view tab

The left part of the descriptor view tab shows all descriptors available for the description’s project. The entries in column “!” indicate some additional information about the descriptor:

• Descriptor is referenced in the summary data (●)
• Descriptor is referenced in the status data (○)
• Descriptor is mandatory, values must be specified (!)
• Descriptor is inapplicable because of other specified descriptors/categorical states (x)
• Descriptor is exclusive, only one categorical states shall be selected (e)

If resources are linked to a descriptor, the entry is marked by a square (□) behind its sequency number and a tool tip appears when the mouse cursor is moved over the “No.” column (see below). By double-clicking the sequence number a “Media view” window can be opened to view the resources.

If for the project descriptor trees are defined, the Descriptor tree: drop-down list and Restricted check box are shown below the descriptor list (see image below left). If a descriptor tree is selected, the names of the descriptor tree node hierarchy is included in the descriptor names (see image below right). This feature is useful, if several descriptors with ambigious names exist the can now be distinguished. E.g. two descriptors “colour”, one at descriptor tree node “Leaf”, the other at descriptor tree node “Blossom” will become “Leaf colour” and “Blossom colour”. If the descriptor name already includes the hierarchy, the duplicate parts will be filtered. In the mentioned example the descriptor names “Leaf colour” and “Blossom colour” will not be changed, if the descriptor tree is selected.

If additionally the Restricted option is set, only the descriptors included in the selected descriptor tree are displayed (see image below right, missing descriptor number 14). So you have the option to create several descriptor trees that include only special aspects of the description’s property (e.g. morphology, molecular biology and so on) to get a clearly arranged descriptor list. 

Editing summary data

The right part of the descriptor view tab shows at the bottom the Status data values of the selected descriptor. It is possible to select several values for each descriptor. In the “Notes” column additional information concerning the selected data status may be entered. By a double click on the “Notes” entry a separate edit window can be opened.

Depending on the descriptor type you can find at the top either Categorical states (see large picture above), Statistical measures, Descriptive text or Molecular sequence (see pictures below). In the “Notes” columns additional information concerning the specific categorical state rsp. statistical measure may be entered. By a double click on the “Notes” entry a separate edit window can be opened.

To enter a statistical measure value click in the “Value” field and enter the value, the check mark in field “x” will be set automatically. If recommended modifier values are defined (see “Editing the Descriptor” and “Editing the Project”), a value may be selected from the drop down list “Modifier”. If no modifier values are available, the appropriate table column may be hidden. In the “Notes” field an additional text may be added. By double-clicking the “Notes” field the Extended texteditor can be opened.

The descriptive text may be edited diretly in the window, which shows the pure text without any formatting. In the notes field an additional text may be entered. By double-clicking the text fields the Extended text editor can be opened. 

The molecular sequence window displays in the status line below the edit area the sequence type (“Nucleotide” or “Protein”), the symbol length (“Len:”) in case of proteine sequences, the gap symbol (“Gap:”) if defined, the long text of the actual element and the position (“Pos:”) (see image below). In case of three-letter proteine symbols the position is the number of the amino acid symbol, not the character position. By pressing the TAB key the cursor jumps to the next symbol. 

When characters are entered in the edit area that are recognized as valid symbols, they will automatically be formatted. One-letter symbols are converted to upper case characters, three-letter symbols are converted according the “Xxx” schema, e.g. “Ala” for “Alanine”. The sybols for the nucleotide bases "Adenine", "Cytosine", “Guanine”, "Thymine" and "Uracil" are dispayed with different colors. Ambiguity symbols and gap symbols are shown in grey color. If the ambiguity symbols have not been enabled in the descriptor data, they will be displayed with yellow background. Symbols that have not been recognized are displayed with red background. 

The molecular sequence window offers some additional functions available in the sequence menu. It may be opened by moving the mouse cursor over the control header (“Molecular sequence”, see window below left) or from the context menu (right-click on the sequence edit area, see windows below right). With menu item Import you may read the seqeunce data from a file and insert them to the description. With menu item Export you may export the actual sequence data to a file.  

By double-clicking the molecular sequence area or from sequence menu Open → Sequence a separate edit window may be opened (see image below). On the top of the window there is the drop-down menu Insert symbol: where a value may be selected from a list. After selecting the value press the [ENTER] key or click on to insert the selected symbol at the cursor position. 

To select a categorical state set a check mark in field “x” with a mouse click. If recommended modifier or frequency values are defined (see Editing the Descriptor and Editing the Project), values may be selected from the drop down lists “Modifier” rsp. “Frequency”. If no modifier or frequency values are available, the appropriate table column may be hidden. In the “Notes” field an additional text may be added. By double-clicking the “Notes” field the Extended text editor can be opened. 

If a “Modifier” or “Frequency” value is selected, the multiple specification of a dedicated categorical state is possible. A tool tip offers to add a row by doouble-click, if mouse cursor is moved over the “State” field (left picture below, picture in the midddle after double click). Multiple specifications of one categorical state are only allowed if they have been assigned different modifier and frequency values. Inputs offending these restrictions will be ignored an a cell error will indicate the problem (right picture below).

If resources are linked to a categorical state a tool tip appears when the mouse cursor is moved over the ID (left picture below). By double-clicking the ID a “Media view” window can be opened to view the resources (right picture below).

If more than one resources have been assigned to one entry, a slider will be visible at the bottom of the “Media view” window. To switch through the available resources the slider can be moved using the mouse or the right and left arrow keys (see below).

Continue with:

• Edit descriptions - Main attributes tab
• Edit descriptions - Molecular sequence symbols
• Edit descriptions - Import sequence data
• Edit descriptions - Export sequence data
• Edit descriptions - Continuous view tab
• Edit descriptions - Resource links tab
• Edit descriptions - Sample data tab

Edit Description HTML

Edit description in HTML form

With this form you can easily edit the description data in an HTML form. After starting a description query and selecting an entry choose Edit -> Edit as HTML form … from the menu or click on the button at the right side of the main Diversity Descriptions window. A window as shown below will be opened.

When you move the mouse cursor over embedded pictures, they are slightly magnified, by clicking on the resource, you may show it in full size (see below). Use button  Home to return to the HTML form. 

With drop down button Options you may insert some additional input fields in the form, e.g. item details or notes. With drop down button Scope you may insert input fields for the vasiour scope types and drop down button Color lets you select the background color. All that changes come into affect, when you reopen the form or redraw the HTML form using the Reload button.

After clicking the Reload button you will be asked if you want to discard all changes. By selecting No the HTML form data will be written into the local data store, otherwise all inputs will be reset. To exit the form and save all changes in the local data store, click the OK button. To ignore your changes, click the Cancel button and you will be asked if you want to discard all changes. The HTML form utility works completely on the local data store. I.e. if you edited the description data in the main form of Diversity Descriptions and start the HTLM form editor without saving the data, all changes are only present in the application dataset. To store the data in the database, click the save button of the main form!

Edit Description Molecular Sequences

Editing the description - Molecular sequence symbols

The editor for sequence data recognizes predefined symbols for nucleotide and protein sequences according the IUPAC definitions.

Nucleic acid symbols

The symbols with grey background are ambiguity symbols. The difference between “N” and a gap symbol (usually “-”, but any other symbol may be defined in the descriptor) is that a gap symbol represents an unspecified number of unknown symbols but “N” stands for exatly one nucleic acid. 

Amino acid symbols

The symbols with grey background are ambiguity symbols. The difference between “X” rsp. “Xaa” and a gap symbol (e.g. “—”, but any other symbol may be defined in the descriptor) is that a gap symbol represents an unspecified number of unknown symbols but “X” rsp. “Xaa” stand for exatly one amino acid. The amino acids “Selenocysteine” and Pyrrolysine" are non-standard amino acids that only occur in certain species. 

Continue with:

• Edit descriptions - Main attributes tab
• Edit descriptions - Descriptor view tab
• Edit descriptions - Import sequence data
• Edit descriptions - Export sequence data
• Edit descriptions - Continuous view tab
• Edit descriptions - Resource links tab
• Edit descriptions - Sample data tab

Edit Description Resources

Editing the description - Resource links tab

The resouce links tab allows assigment and inspection of resource links for the description. In the lower Resources part of the tab there are two tables. Ar the left the “Resource” table specifies the single resources, at the right the “Resource variant” table contains the associated URLs to images, video or sound resources.

To enter a new resource select the empty line at the end of the “Resource” table and click on the “Resource name” field. After entering the “Resource name” (leave the cell by pressing the TAB key) automatically a new value for the sequence number (“No.”) is initialized. The sequence number determines the display order in tables and may be changed manually. You may order the resource entries by clicking on the column header or by using the arrow buttons in the left tool strip ( and for up and down, and to shift the selected entry to top or bottom). After ordering the resources click button to renumber the entries starting with “1” and make the changes effective. In field “Ranking” you may enter a numeric value in range 0 … 10 expressing the suitability of the resource. In field “Role” you may select one of the offered values.

If you want to delete a resource, you have three choices:

1. Select the resource and press the button in the left tool strip.
2. Select the resource and press the Delete key on the keyboard.
3. Enter the sequence number column (“No.”) and remove the value. After leaving the cell the entry will be deleted.

In any case you will be asked if you want to delete the data row, if resource links are present. 

To view or edit the additional resource data and copyright information press on the button besides the resource table. In field License: and a link to the license text in the web may be entered, in field Rights: you may enter a copyright text. By pressing the button a browser window can be opened to navigate to the license page in the web. Furthermore you may enter a detailled text (field Details:), the resource language (field Language:) and set the Display embedded flag (see picture below). If any additional resource data have been entered, this will be indicated by a square (□) behind the resource name’s sequence number (see picture above).

To enter a resource link, first select the entry in the “Resource” table, then select the empty line at the end of the “Resource variant table”. By clicking on the “URL” field a web address may be entered. By double-clicking the “URL” field a browser window opens to navigate to the resource. For each resource several URLs to resource variants with different quality levels, e.g. different resolution, may be inserted. For each entry in the URL table a different value of “Variant” must be selected.

As an alternative to a resource URL a colour may be spcified in the format “color://#rrggbb” where “rrggbb” specifies a hexadecimal colour code. The colour can simply be seleced by clicking the button besides the resource variant table.

To remove a resource variant, select the entry and press the button in the right tool strip or the Delete key on the keyboard. Alternatively you may delete the “URL” entry in the resource variant table. 

The button besides the resource variant table allows you to view a resource URL in the system browser.

The upper part of the resource links tab consists of a preview window on the left side and the complete list of resource links. By selecting one of the links the preview is shown on the preview window. If you press button in the preview window, the resource is opened in a separate viewer window.

At the bottom of the resource links tab the media data of a selected URL are displayed. These data can be edited by double-clicking the “Type” entry in the resource variant table, by clicking at one of the media data value fields or the button. The edit dialog provides the possibility to access the URL to get available data. Additionally the values may be edited manually.   

Copy description resources to a descriptor

If you have write access to descriptors of the currently selected description’s project, you may copy a selected resource URL to a descriptor. After clicking the button a window will open to select one of the accessible descriptors (see image below). Remark: To see the copied resources in the Descriptorview or the Continuous view, you will have to reconnect to the database. 

Continue with::

• Edit descriptions - Main attributes tab
• Edit descriptions - Descriptor view tab
• Edit descriptions - Molecular sequence symbols
• Edit descriptions - Import sequence data
• Edit descriptions - Export sequence data
• Edit descriptions - Continuous view tab
• Edit descriptions - Sample data tab

Edit Description Samples

Editing the description - Sample data tab

The sample data tab allows assigment and inspection of sampling events and associated sampling units for the description. In the upper left Sampling events part of the tab there is a list of the sampling events stored for the description. By selecting a sampling event entry the Event data are shown in the upper right part of the tab. Here you may edit the event name, a detailled description, the date and time rsp. a date and time span and geographic data. To insert a new sampling event click on the button in the Sampling events section, to delete the selected sampling event and all stored sampling unit data press the button (see image above). 

The geographic area may be linked to an entry of a DiversityGazetteers database (see section Module related entry). If latitiude and longituede have not yet been entered, the coordinates of the selected DiversityGazetteers entry will be inserted. By pressing the button a window to select the coordinates by Google maps will be opened. Field Geo. datum allows entering short text concerning the geodetic datum. If coordinates are entered using Google maps a remark that WGS84 coordinates are used will be inserted.

Editing sampling unit data

For each sampling events one or several Sampling units may be entered. A sampling unit represents a tuple of values that belong together, e.g. because they represent a single specimen. For each descriptor, which is represented by the table column, a value may be entered in a sampling unit, which builds a single table line. The background colour of each table column indicates the type of sample data: categorical, quantitative, text or sequence . If the number of sampling units is displayed in field Units of the Sampling units section in the lower part of the tab. If sampling units are present, the number is displayed with yellow background (see image below).

To view the unit data, select a Descriptor tree and choose the descriptor sequence range (From descriptor … to descriptor) to restrict the number of columns displayed in the unit table. Finally press button and the sampling untis will be displayed (see image below). If you prefer to display the present sampling units automatically whenever you select a sampling event, click on the number behind Units. The background changes from yellow to red to indicate this mode. When you select a cell and resources are available for the selected descriptor column, the button will be shown. You may click it to view the descriptor resources. 

To insert a new sampling unit press button . By clicking on the free space at the beginning of a table line you mark the whole sampling unit (see picture below). Now you have the additional options to delete the unit by pressing button or to copy the data to a new sampling unit by pressing the button. Keep in mind that all unit data will be copied, even if you do not see all descriptor columns due to the display restrictions.

When you select the Sampling unit ID cell you may enter a collection specimen in the Unit specimen control below the units table. By clicking the button (see section Module related entry) you can link it to an entry in a DiversityCollection database. If the sampling unit is linked to a database entry in DiversityCollection, the text of the Unit specimen: control is shown with light yellow background. You may view the linked data by clicking the button or double clicking the Sampling unit ID cell.

To modify categorical sampling data enter the table cell and press the button within the cell. A control will be opened where you can select the categorical states and enter notes or modifier values (if defined) for each single state (see image below).

For all other sampling data the value can be entered directly in the table cell. Notes for the selected table cell are entered in the Notes: text box directly below the units table. For categorical, text and molecular sequence sample data a separate edit window can be opened by double-clicking the table cell. For molecular sequence data the tool strip buttons (import) and  (export) allow import and export from rsp. to dedicated file formats as described in sections Import sequencedata and Export sequence data.

Continue with:

• Edit descriptions - Main attributes tab
• Edit descriptions - Descriptor view tab
• Edit descriptions - Molecular sequence symbols
• Edit descriptions - Import sequence data
• Edit descriptions - Export sequence data
• Edit descriptions - Continuous view tab
• Edit descriptions - Resource links tab

Export Sequence

Editing the description - Export sequence data

With this form you can export data from the database to a file. Choose Export from the sequence menu to open the window for the export. Format specifies the file format, currently FASTA and FASTQ are supported.

In the Header settings you may specify if a new header shall be generated or if the notes shall be searched for a FASTA rsp. FASTQ header. In case of FASTA export you may specify in the Comments settings section if FASTA comment lines from the notes (starting with “;”) shall be inserted after the header, the whole notes shall be inserted as FASTA comments or if the notes shall be ignored. If Insert description comments is checked, two comment linsed wit the description and descriptor title will be generated.

In the Header preview section you may check and edit the resulting header, in the Sequence preview section the converted sequence in one-letter notation is displayed.

In case of FASTQ export the Comments settings are irrelevant. If the notes include a QUALITY STRING sections, it is inserted in the FASTQ output (see image below). If no quality string can be found or the Find FASTQ quality from notes has been unchecked, the lowest quaality value “!” is inserted.

Click on the button to select the file where the data shall be exported. By default the data are appended at the end of the selected file, check the Overwrite existing file option to overwrite it. Finally click the Save file button to write the data.

Continue with:

• Edit descriptions - Main attributes tab
• Edit descriptions - Descriptor view tab
• Edit descriptions - Molecular sequence symbols
• Edit descriptions - Import sequence data
• Edit descriptions - Continuous view tab
• Edit descriptions - Resource links tab
• Edit descriptions - Sample data tab

Edit Description Grid Copy

Description grid view - Copy or create descriptions

To copy one or more descriptions select the whole data rows by clicking in the area left from the “Description title” column. In the tool strip the copy button appears (see image below).

After clicking the button you may be asked if you want to copy description resources, too. Be aware that the copy operation is directly performed within the database. I.e. that local changes that have been done in the sources desrcriptions will not be included in the targets unless you save your data before copy (see image below, second and last line in descriptors 28 and 29). 

New description

To enter a new description, click on button and a new row will be appended (see image below). Same as for copy the new description is created directly within the database.

Continue with:

• Description grid view - Selection parameters
• Description grid view - Data editing
• Description grid view - Save or discard changes

Edit Description Grid Editing

Description grid view - Data editing

The first column of the description table shows the description titles, which are fixed. I.e. if you scroll the summary data to the right, the titles are still visible at the left. The other columns show the summary data, where the descriptor type is indicated by the background colour: categorical, quantitative, text or sequence. The column titles include the descriptor name, the mesurement unit in square bracket for quantitative descriptors and the descriptor’s sequence number (see image below).

When a summary cell in the description table is selected, the field Description: shows the description title and field Descriptor: shows the symbols according the descriptor type ( for categorical, for quantitative, for text and for sequence) and the descriptor title. If for a descriptor resources are available, this fact is indicated by a coloured symbol background. You may view them by a double-click on the descriptor title. If for a description resources are available, the symbol is displayed before the title. Double click in that symbol to view the resources. 

For the selected cell the summary data and the descriptor status data are displayed in the upper right area of the window (see image above). You may edit the data within these controls as described in section Editing the description - Descriptor view tab of this manual. For modified summary cells the text colour changes to blue and the description titles will be shown with yellow background in the table.

Changing the description title

To change the description title, double-click on it in field Description: to open a separate edit window. After comitting the changes with “OK” the description will be marked as modified (see images below).

Changing the summary data in the table

Categorical summary data

Categorical, states are specified by their sequence number. Multiples are separated by a forward slash ("/", see image below). Existing “notes” will be removed, if the corresponding categorical state is not specified in the input.

Quantitative summary data

If you did not check the option Separate quantitative measures, for quantitative summary data only the five quantitative measures  

can be specified within the summary table. The values must be specified in theis given order and separated by a forward slash ("/"). E.g. 1////100 means “Min=1, Max = 100”. If only one value without any slashes is entered, it is assumed than only the “Mean” value is specified. To indicate that values for other than the five mentioned statistical measures are present in the data, an asterisk ("*") is appended in the summary table (see image below, cell value 1////100 *).

If you checked the option Separate quantitative measures, for each statistical measure in the quantitative summary data a separate column will be allocated in the data grid view. I.e. you can direcly enter your values into the table (see image below).

Text descriptor and Molecular sequence data

For Text and sequence data the texts may be edited directly within the table. The corresponding “notes” will not be affected.

Changing values in a table column

It is possible to modify several values of a single table column. Therefore select the table entries that shall be edited - by selecting an entry and clicking the button in section Column action you may select the whole table column (see image below). Then select the required operation: “Insert”, “Append”, “Replace” or “Clear” and enter the new rsp. new and old value. After clicking the action button - depending on the selected Column action this is (Insert), (Append), (Replace) or (Clear). Be aware that this feature performs a pure text operation in the table columns. Especially for quantitative and categorical data columns the resulting data will be interpreted according to the rules described above an might lead to unsexpected results, if not designed ver carefully. 

Continue with:

• Description grid view - Selection parameters
• Description grid view - Copy or create descriptions
• Description grid view - Save or discard changes

Edit Description Grid Save

Description grid view - Save or discard changes

When you click in button “OK” the description grid view is closed and all changes are automatically saved in the database. If you did some changes and want to save them without leaving the form, simply click on (see image below). If you close the window, e.g. by clicking the “Cancel” button, and still have unsaved changes, you will be asked to save or discard the changes.

Undo all changes

You may discard all unsaved changes without leaving the form by clicking the button and the data will be reloaded form the database (see image below).

Edit Descriptor Modifier

Editing the descriptor - Modifier/Frequency tab

The Modifier/Frequency tab allows definition of modifier and frequency values. The sequence number column (“No.”) determines the display sequence in selection lists. You may order the entries by clicking on a column header. With the arrow buttons ( ) you can move the selected entries up or down within the table, clicking button or shifts the selected entries to the top respective bottom of the table. After ordering the entries click button to renumber the table entries (starting with “1” for the first table entry) and make the changes effective. After pressing button column “Use” shows the number of references in the database for each modifier rsp. frequency value.

To enter a new modifier or frequency value, select the empty line on the end of the table and click on the “Modifier” rsp. “Frequency” field to enter the new values. After entering the new entry name automatically a new value for “No.” is initialized. To delete an entry, select the entry and press the tool strip button, the Delete key or remove the value in the sequence number column (“No.”).

There is a number of predefined modifier and frequency values commonly used. These values can be accessed by pressing the  button, which opens one of the windows shown below.

 By checking of uncecking the values, they can easily be inserted or deleted from the modifier rsp. frequency list. Values that are already used in the database are marked by a grey text colour. When these values are marked to be removed from the list, you will be asked for each single value if it really shall be deleted after pressing OK. New values are inserted at the end of the list (see below).

You may order the modifier and frequency values according the sequence of the predefined values by pressing the  button, which opens one of the windows shown below. Values that are not in the predefined lists will be shifted to the end of the list.

Continue with:

• Edit descriptors - Main attributes tab
• Edit descriptors - Categorical states tab
• Edit descriptors - Descriptor tree tab
• Edit descriptors - Resource links tab

Edit Descriptor Resources

Editing the descriptor - Resource links tab

The resouce links tab allows assigment and inspection of resource links for the descriptor. In the lower Resources part of the tab there are two tables. Ar the left the “Resource” table specifies the single resources, at the right the “Resource variant” table contains the associated URLs to images, video or sound resources.

To enter a new resource select the empty line at the end of the “Resource” table and click on the “Resource name” field. After entering the “Resource name” (leave the cell by pressing the TAB key) automatically a new value for the sequence number (“No.”) is initialized. The sequence number determines the display order in tables and may be changed manually. You may order the resource entries by clicking on the column header or by using the arrow buttons in the left tool strip ( and for up and down, and to shift the selected entry to top or bottom). After ordering the resources click button to renumber the entries starting with “1” and make the changes effective. In field “Ranking” you may enter a numeric value in range 0 … 10 expressing the suitability of the resource. In field “Role” you may select one of the offered values.

If you want to delete a resource, you have three choices:

1. Select the resource and press the button in the left tool strip.
2. Select the resource and press the Delete key on the keyboard.
3. Enter the sequence number column (“No.”) and remove the value. After leaving the cell the entry will be deleted.

In any case you will be asked if you want to delete the data row, if resource links are present. 

To view or edit the additional resource data and copyright information press on the button besides the resource table. In field License: and a link to the license text in the web may be entered, in field Rights: you may enter a copyright text. By pressing the button a browser window can be opened to navigate to the license page in the web. Furthermore you may enter a detailled text (field Details:), the resource language (field Language:) and set the Display embedded flag (see picture below). If any additional resource data have been entered, this will be indicated by a square (□) behind the resource name’s sequence number.

To enter a resource link, first select the entry in the “Resource” table, then select the empty line at the end of the “Resource variant table”. By clicking on the “URL” field a web address may be entered. By double-clicking the “URL” field a browser window opens to navigate to the resource. For each resource several URLs to resource variants with different quality levels, e.g. different resolution, may be inserted. For each entry in the URL table a different value of “Variant” must be selected.

As an alternative to a resource URL a colour may be spcified in the format “color://#rrggbb” where “rrggbb” specifies a hexadecimal colour code. The colour can simply be seleced by clicking the button besides the resource variant table.

To remove a resource variant, select the entry and press the button in the right tool strip or the Delete key on the keyboard. Alternatively you may delete the “URL” entry in the resource variant table. 

The button besides the resource variant table allows you to view a resource URL in the system browser.

The upper part of the resource links tab consists of a preview window on the left side and the complete list of resource links. By selecting one of the links the preview is shown on the preview window. If you press button in the preview window, the resource is opened in a separate viewer window.

At the bottom of the resource links tab the media data of a selected URL are displayed. These data can be edited by double-clicking the “Type” entry in the resource variant table, by clicking at one of the media data value fields or the button. The edit dialog provides the possibility to access the URL to get available data. Alternatively the values may be edited manually.   

Continue with:

• Edit descriptors - Main attributes tab
• Edit descriptors - Categorical states tab
• Edit descriptors - Descriptor tree tab
• Edit descriptors - Modifier/Frequency tab

Edit Descriptor States

Editing the descriptor - Categorical states tab

The Categorical states tab is only enabled, if the descriptor type has been set to “Categorical”. The upper part of the state tab shows the table of categorical states. To enter a new state select the empty line at the end of the table and click on the “Categorical state” field. After entering the “Categorical state” name (leave the cell by pressing the TAB key) automatically a new value for the sequence number (“No.”) is initialized. The sequence number determines the display order in tables and may be changed manually. You may order the state entries by clicking on the column header or by using the arrow buttons in the tool strip right besides the states ( and for up and down, and to shift the selected entry to top or bottom). After ordering the states click button to renumber the entries starting with “1” and make the changes effective.

If you want to delete a categorical state, you have three choices:

1. Select the state and press the button in the tool strip right besides the states.
2. Select the state and press the Delete key on the keyboard.
3. Enter the sequence number column (“No.”) and remove the value. After leaving the cell the entry will be deleted.

In any case you will be asked if you want to delete the data row. If the categorical state is already used in any description items, the number of affected entries will be shown and you have the option to abort the action. 

If for a state at least one assigned resource is present, the state is marked by a square (□) behind its sequence number (see image above). After selecting the categorical state its assigned resources ar displayed in the lower part of the state tab, the State resource links. It allows management of resources linked to a categorical state marked in the upper table. For a detailled description take a look at the Resource links tab section.

Continue with:

• Edit descriptors - Main attributes tab
• Edit descriptors - Descriptor tree tab
• Edit descriptors - Modifier/Frequency tab
• Edit descriptors - Resource links tab

Edit Descriptor Trees

Editing the descriptor - Descriptor tree tab

The Descriptor tree tab provides the possibility for a structured assignment of the descriptors to the projects. The Descriptor tree consists of three node types, which show the administered projects, descriptor trees, descriptor tree nodes and descriptors. The tree parts’ colours are shown in the example at the bottom. By pressing the button on the tool strip at the right side the example may be hidden. The buttons displayed on the too strip will change depending on the selected entry.

The descriptor trees support three main functions:

• Assignment of descriptors to a certain project
• Selection of recommended Frequency/Modifier values and Statistical measures for a certain descriptor
• Structured arragement of the descriptors, e.g. for hierarchical selection lists and definition of descriptor subsets for description editing and export

Descriptor trees are the root of a tree and may only be appended at a project. To insert a new descriptor tree select a project and press . Descriptor tree nodes may be appended at a descriptor tree or another descriptor tree node. To insert a new descriptor tree node, select the parent and press . Descriptors may be appended to a descriptor tree or a descriptor tree node. To append a descriptor select the parent and press , to remove it select the descriptor and press . All these functions can alternatively be accessed by the context menu by a right click on the entry. The descriptor type is indicated by different icons. Categorical descriptors are marked as , quantitative descriptors as , text descriptors as and sequence descriptors as .

You may change the name of a Descriptor tree or a Descriptor tree node in three different ways:

1. Select the tree element (single click) and then click once on it.
2. Select the tree element (single click) and click on button in the tool bar on the right.
3. Right-click the tree element and select item Edit name from the context menu.

You may edit the name of the selected node within the tree. The Descriptor tree must be globally unique. The Descriptor tree node names must be unique with respect to their tree position, i.e. all nodes with the same parent within a descriptor tree must have distinguished names.

If a Descriptor tree or a Descriptor tree node does not contain any other element, it may be deleted by pressing  (delete descriptor tree) rsp. (delete descriptor tree node). Please be aware that in edit mode “Descriptor” the descriptor tree shows only the descriptor that as actually selected in the query panel! To get a complete overview of all descriptors assigned to a certain descriptor tree and to delete trees or nodes with all included objects, select edit mode “Project” (see Editprojects).

If projects and their associated descriptor trees cannot be changed, because the project has been set to “read-only” state, these parts are displayed with grey text color (see project “Schmetterlinge” in picture above).

Descriptor trees can be marked as “tree complete”, to indicate that they should contain all descriptors of the project. In this case the symbol of the descriptor tree changes from to . Setting and resetting the “tree complete” flag is described in the Edit projects section, where additional edit options are availabe.

When a new descriptor tree or descriptor tree node is inserted it gets a numeric name, which may be changed by clicking on the node or selecting from the context menu. Take care that the names of the discriptor trees must be unambiguous within the whole database! The names of the descriptor tree nodes must only be unambiguous within one parent.

If no descriptor tree is installed for a certain project, all descriptors, modifier/frequency values and statistical measures will be usable in the project’s descriptions. Anyway, if you export data or generate documents an do not have an appropriate descriptor tree, you might have missing data! If a project contains several descriptor trees, the available descriptors are the superset of the descriptors assigned to all contained trees.

Sorting of descriptor tree elements 

By pressing the button you can expand the tree view to display all elements, by pressing the tree will be collapsed to descriptor tree level. With the arrow buttons ( rsp. ) you can move descriptor tree nodes up or down rsp. to the top or the bottom. An additional ordering option is to select the descriptor tree node you want to move and click the button. The button’s back color changes to red and you may now select the new position in the tree.

In operation mode Edit projects you may take over the descriptor order from a descriptor tree to the descriptor sequence numbers of all project’s descriptors. This will affect the descriptor presentation for description data editing or document generation.  

Descriptor tree node details  

For Descriptor tree nodes you may enter an abbreviation a details text and assign resource data. Select the Descriptor tree node (single click) and click on button in the tool bar on the right. Alternatively you may right-click the Descriptor tree node and select Edit details from the context menu. An edit window as shown below will be opened.

If resources are assigned to a Descriptor tree node, its symbol is shown with grey background in the descriptor tree. You may view the resources by right-clicking it and selecting View resources from the context menu.  

Recommended statistical measures, modifiers and frequency values 

Click on in the tool bar to close the edit window in the right part of the tab or on  to reopen it. In the edit window you can select recommended modifier/frequency values and statistical measures. The recommended values can be administered for a selected descriptor or for a selected descriptor tree node. In the latter case selected values are inherited by all subordinate nodes, which will be indicated by the background colour if a subordinate node is selected in the descriptor tree. 

To define the available modifier and frequency values and their display sequence, please refer to the Modifier/Frequency tab. The statistical measure values are predefined, nevertheless their display sequence may be modified using the  button (see image above). A form as shown below will appear. With the arrow buttons ( ) you can move the selected entries up or down within the table, clicking button or shifts the selected entries to the top respective bottom of the table. After ordering the entries click button to renumber the table entries (starting with “1” for the first table entry) and make the changes effective.  

The picture below shows an example for a descriptor where the recommended statistical measures Minimum value, Maximum value and Mean are inherited by the superior node.  

For the contained descriptor only additional values may be selected, but the inherited values cannot be de-selected.

Continue with:

• Edit descriptors - Main attributes tab
• Edit descriptors - Categorical states tab
• Edit descriptors - Modifier/Frequency tab
• Edit descriptors - Resource links tab

Edit Project Dependencies

Editing the project - Descriptor applicability tab

The Descriptor applicability tab serves the administration of dependencies between categorical states and dependent descriptors. First a descriptor has to be selected either by its name in the alphabetically ordered drop down list or its sequence number (“No.”) in the first line of the descriptor dependencies tab. If for the selected project a descriptor tree is present, the  button allows selecting the descriptor form the descriptor tree (see picture below). 

The table Controlling states in the upper left part of the tab shows the categorical states if a categorical descriptor has been selected. When you have adusted the restrichted descriptor view, visible by the button in the toolbar of the Dependent descriptor section, only the actually set dependent descriptors are displayed here. You can switch to an alternate Full descriptorview as described below. For each categorical state Dependent descriptors can be inserted by pressing the button. A dialog window opens to select the descriptor either by its name, sequence number or using the descriptor tree (see picture below).

In column “Rule” the symbol indicates that the dependent descriptor is inapplicable if the controlling state is present in a description. The symbol indicates that the controlling state must be present in a description if the controlled descriptor shall be applicable. The rule can be toggled by clicking the symbol in the table or by pressing the corresponding button ( rsp. ) at the right. To remove a dependent descriptor select it and press the button.

In the lower part of the tab you find the States controlling this descriptor. You may double-click on the item to select navigate to it for editing. Alternatively you may edit the controlling descriptor states using the control buttons of the tool strip at the right. By double-clicking an entry in Dependent descriptors or in States controlling this descriptor you may navigate forwards and backwards in the chain of dependent descriptors.

If you want to get an overview of all the project’s descriptor dependencies or want to delete all dependencies, take a look at the Applicability tree section below.

Calculation of the descriptor applicability

To decide if a descriptor is applicable in a description, the following conditions are evaluated:

• Inapplicable-if rule: If only controlling states are present in a description, these make the controlled descriptor inapplicable. The character remains applicable if either no state at all, or any non-controlling states are present.
• Applicable-if rule: If any controlling state is present in a description, these make the controlled descriptor applicable. It is inapplicable if only non-controlling states are present.
• Special condition 1: If the controlling descriptor is inapplicable (through another applicability rule, or through an explicit “Not applicable” data status value), the controlled descriptor is always inapplicable as well.
• Special condition 2: If no data for the controlling descriptor are present in a description (data completely missing or only status is “Missing data” or “Not to be recorded”), the controlled descriptor always remains applicable.

Full descriptor view

By clicking the button in the toolbar of the Dependent descriptor section, you can chenge to the full descriptor view (see image below). In section Dependent descriptors all available project descriptors are shown. You may set a dependency by selecting the descriptor and clicking the button rsp. . To remove the dependency, click on button . Alternatively you may click on they symbol in the table column “Rule” to change the values in the sequence → -> .

If you select a values for the Descriptor tree, the descriptor list is restricted to the values included in that descriptor tree. Please be aware that existing dependencies might be hidden by such a selection! 

You can return to the restricted descriptor view by clicking on button .

Applicability tree

Instead of the States controlling this descriptor you may display a complete overview of the descriptor applicability rules by pressing the button in the Dependent descriptors section (see image below). By double-clicking an item in the Applicability tree you can open it for editing.

By pressing the button in the Applicability tree section you can delete all dependencies shown in the tree.

Continue with::

• Edit projects - Main attributes tab
• Edit projects - Descriptor tree tab
• Edit projects - Move/Copy descriptor trees
• Edit projects - Modifier/Frequency tab

Edit Project Modifier

Editing the project - Modifier/Frequency tab

The Modifier/Frequency tab allows definition of modifier and frequency values. The sequence number column (“No.”) determines the display sequence in selection lists. You may order the entries by clicking on a column header. With the arrow buttons ( ) you can move the selected entries up or down within the table, clicking button or shifts the selected entries to the top respective bottom of the table. After ordering the entries click button to renumber the table entries (starting with “1” for the first table entry) and make the changes effective. After pressing button column “Use” shows the number of references in the database for each modifier rsp. frequency value.

To enter a new modifier or frequency value, select the empty line on the end of the table and click on the “Modifier” rsp. “Frequency” field to enter the new values. After entering the new entry name automatically a new value for “No.” is initialized. To delete an entry, select the entry and press the tool strip button, the Delete key or remove the value in the sequence number column (“No.”).

There is a number of predefined modifier and frequency values commonly used. These values can be accessed by pressing the  button, which opens one of the windows shown below.

 By checking of uncecking the values, they can easily be inserted or deleted from the modifier rsp. frequency list. Values that are already used in the database are marked by a grey text colour. When these values are marked to be removed from the list, you will be asked for each single value if it really shall be deleted after pressing OK. New values are inserted at the end of the list (see below).

You may order the modifier and frequency values according the sequence of the predefined values by pressing the  button, which opens one of the windows shown below. Values that are not in the predefined lists will be shifted to the end of the list.

Continue with:

• Edit projects - Main attributes tab
• Edit projects - Descriptor applicability tab
• Edit projects - Descriptor tree tab
• Edit projects - Move/Copy descriptor trees

Edit Project Move Trees

Editing the project - Move or copy descriptor trees

Using the context menue, you may move or copy parts of a descriptor tree to other branches. Please note that for the move and copy operations the selected project, marked by its background colour in the descriptor tree, is assumed as the target project. You may copy descriptor trees or parts of projects with read-only access to your selected project. The move operation is certainly not possible in those cases. Note that by the copy operations only the tree structures will be copied, associated recommended statistical measures, modifiers and frequencies will not be copied! The move operations preserve those adjustments.

Remark: If you want to move or copy element from an other than the selected project, activate the “Show all projects” option by clicking the button in the tool strip. The icon background will be changed to red color to indicate that this option is active.

1. Move or copy a descriptor tree

Right click on the descriptor tree you want to move/copy. A context menu opens, that offers the options Copy ‘<tree name>’ to ‘<project name>’ rsp. Move ‘<tree name>’ to ‘project name’ (see picture below left). If you selected “Move”, the descriptor tree is removed from the original project and appended to your selected project. If you selected “Copy”, a copy of the tree will be appended to the selected project. In this case numeric IDs are appended to generate unique tree and tree node names (see picture below right). The copy function can be used to generate a clone of a descriptor tree in the selected project.

Additionally you may copy the contents of a descriptor tree to a descriptor tree node by clicking selecting the option Copy descriptor tree to descriptor tree node … rsp. Move descriptor tree to descriptor tree node … (see picture above left). A separate window opens to select the target node (see picture below left). Find the move result in the right picture below. Since the tree has been moved, the tree node names have not been changed. One duplicate descriptor has not been moved to the target node. 

2. Move or copy a descriptor tree node

Right click on the descriptor tree node you want to move/copy. A context menu opens, that offers the options Copy descriptor tree node … rsp. Move descriptor tree node … (see picture below left). After selecting one of there items a dialog windows opens where you may select the target descriptor tree or descriptor tree node (see picture below right).

If you selected “Move”, the descriptor tree node is removed from the original position and appended to the selected tree position. If you selected “Copy”, a copy of the tree node will be appended to the selected tree position. In this case numeric IDs are appended to generate unique tree node names (see picture below). The copy function can also be used to generate a clone of a descriptor tree node in the selected project.

3. Move or copy a descriptor

Right click on the descriptor you want to move/copy. A context menu opens, that offers the options Copy descriptor … rsp. Move descriptor … (see picture below left). After selecting one of there items a dialog windows opens where you may select the target descriptor tree or descriptor tree node (see picture below right).

If you selected “Move”, the descriptor is removed from the original position and appended to the selected tree position. If you selected “Copy”, a new descriptor node will be appended to the selected tree position (see picture below).

Continue with:

• Edit projects - Main attributes tab
• Edit projects - Descriptor applicability tab
• Edit projects - Descriptor tree tab
• Edit projects - Modifier/Frequency tab

Edit Project Trees

Editing the project - Descriptor tree tab

The Descriptor tree tab provides the possibility for a structured assignment of the descriptors to the projects. The Descriptor tree consists of three node types, which show the administered projects, descriptor trees, descriptor tree nodes and descriptors. The tree parts’ colours are shown in the example at the bottom. By pressing the button on the tool strip at the right side the example may be hidden. The buttons displayed on the too strip will change depending on the selected entry.

By default only the selected project. its subordinated projects and their descriptor trees are displayed. You may select the “Show all projects” option by clicking the button in the tool strip to show alle projects. To indicate that this option is active, the icon background will be changed to red color (see images below). Be aware that this option might slow down data loading.

The descriptor trees support three main functions:

• Assignment of descriptors to a certain project
• Selection of recommended Frequency/Modifier values and Statistical measures for a certain descriptor
• Structured arragement of the descriptors, e.g. for hierarchical selection lists and definition of descriptor subsets for description editing and export

Descriptor trees are the root of a tree and may only be appended at a project. To insert a new descriptor tree select a project and press , to delete it select the descriptor tree and press . Descriptor tree nodes may be appended at a descriptor tree or another descriptor tree node. To insert a new descriptor tree node, select the parent and press , to remove it select the descriptor tree node and press . Descriptors may be appended to a descriptor tree or a descriptor tree node. To append a descriptor select the parent and press , to remove it select the descriptor and press . All these functions can alternatively be accessed by the context menu by a right click on the entry. The descriptor type is indicated by different icons. Categorical descriptors are marked as , quantitative descriptors as , text descriptors as and sequence descriptors as .

You may change the name of a Descriptor tree or a Descriptor tree node in three different ways:

1. Select the tree element (single click) and then click once on it.
2. Select the tree element (single click) and click on button in the tool bar on the right.
3. Right-click the tree element and select item Edit name from the context menu.

You may edit the name of the selected node within the tree. The Descriptor tree must be globally unique. The Descriptor tree node names must be unique with respect to their tree position, i.e. all nodes with the same parent within a descriptor tree must have distinguished names.

If projects and their associated descriptor trees cannot be changed, because the project has been set to “read-only” state, these parts are displayed with grey text color (see project “Schmetterlinge” in picture above).

When a new descriptor tree or descriptor tree node is inserted it gets a numeric name, which may be changed by clicking on the node or selecting from the context menu. Take care that the names of the discriptor trees must be unambiguous within the whole database! The names of the descriptor tree nodes must only be unambiguous within one parent.

If no descriptor tree is installed for a certain project, all descriptors, modifier/frequency values and statistical measures will be usable in the project’s descriptions. Anyway, if you export data or generate documents an do not have an appropriate descriptor tree, you might have missing data! If a project contains several descriptor trees, the available descriptors are the superset of the descriptors assigned to all contained trees.

Editing the project descriptor assignemnts for a tree node 

When you use button to append a descriptor to a tree node, you will be offered all accessible descriptors in the database. In many cases, when you have already assigned all required descriptors to the project, you may wish to build some additional structured descriptor trees. If you select a descriptor tree or a descriptor tree node, the button is shown (see image below left). After clicking this button you will get a selection list with all descriptors assigned to the project. Descriptors that are included in the tree node are selected in the list (see image below right). You may easily change the selection and click “OK” to make the changes effective.

Sorting of descriptor tree elements 

By pressing the button you can expand the tree view to display all elements, by pressing the tree will be collapsed to descriptor tree level. With the arrow buttons ( rsp. ) you can move descriptors or descriptor tree nodes up or down rsp. to the top or the bottom. An additional ordering option is to select the descriptor tree node you want to move and click the button. The button’s back color changes to red and you may now select the new position in the tree. If you select a descriptor tree or a descriptor tree node, you may arrange the contained descriptors in alphabedical order by clicking the buttons (ascending) or (descending).

A descriptor tree may be marked as “tree complete” to indicate that this tree shall contain all descriptors for the project. This is done by selecting the descriptor tree node and clicking the button . To indicate that the tree is marked as complete the tree icon changes to (see picture below). 

The “tree complete” flag may be removed by clicking the button and the tree icon will be changed back to (see picture below).

If a descriptor tree is marked as “tree complete”, it can be used to take over the sorting of the descriptor nodes to the sequence number of the descriptors. But you must be aware that the descriptor’s sequence number (field “No.” in “Edit descriptor” main panel) is not project or tree dependent, i.e. that this operations will have influence on the dispolay sequence of all descriptions using these descriptors. Click on button (see picture above) to open the descriptor sorting window below.

The first column shows the descriptor names as sorted in the descriptor tree, the second column shows the current sequence number int the database and the last column shows the new sequence number. If the sequence number stays unchanged, the new sequence number is displayed with green background, otherewise with yellow background. You may click on a new sequence number and change its value manually. By clicking on the table headers, you may sort the table display according descriptor names, old or new sequence numbers. If a descriptor may not be changed, because it is assigned to a project where the user has no write access, the whole line is displayed with grey backgound and cannot be changed. Finally click on OK to accept or Cancel to reject the changes. 

An additional option available for complete trees is to copy missing descriptors with button . The descriptors available for a certain project include all descriptors assigned to all descriptor trees of the project and its sub-projects. In our example a new descriptor tree has been assigned to the upmost project (see image below left). After clicking the button all valid descriptors are inserted to the descriptor tree (see image below right). 

Descriptor tree node details  

For Descriptor tree nodes you may enter an abbreviation a details text and assign resource data. Select the Descriptor tree node (single click) and click on button in the tool bar on the right. Alternatively you may right-click the Descriptor tree node and select Edit details from the context menu. An edit window as shown below will be opened.

If resources are assigned to a Descriptor tree node, its symbol is shown with grey background in the descriptor tree. You may view the resources by right-clicking it and selecting View resources from the context menu.  

Recommended statistical measures, modifiers and frequency values 

Click on in the tool bar to close the edit window in the right part of the tab or on  to reopen it. In the edit window you can select recommended modifier/frequency values and statistical measures. The recommended values can be administered for a selected descriptor or for a selected descriptor tree node. In the latter case selected values are inherited by all subordinate nodes, which will be indicated by the background colour if a subordinate node is selected in the descriptor tree.  

To define the available modifier and frequency values and their display sequence, refer to the Modifier/Frequency tab. The statistical measure values are predefined, nevertheless their display sequence may be modified using the  button (see image above). A form as shown below will appear. With the arrow buttons ( ) you can move the selected entries up or down within the table, clicking button or shifts the selected entries to the top respective bottom of the table. After ordering the entries click button to renumber the table entries (starting with “1” for the first table entry) and make the changes effective.  

The picture below shows an example for a descriptor where the recommended statistical measures Mini mum value, Maximum value and Mean are inherited by the superior node. 

For the contained descriptor only additional values may be selected, but the inherited values cannot be de-selected.

Continue with:

• Edit projects - Main attributes tab
• Edit projects - Descriptor applicability tab
• Edit projects - Move/Copy descriptor trees
• Edit projects - Modifier/Frequency tab

Edit Sample Data Grid Editing

Sample data grid view - Data editing

The first column of the sampling unit table shows the sampling unit IDs titles, which are fixed. I.e. if you scroll the sample data to the right, the IDs are still visible at the left. The other columns show the sample data, where the descriptor type is indicated by the background colour: categorical, quantitative, text or sequence. The column titles include the descriptor name, the mesurement unit in square bracket for quantitative descriptors and the descriptor’s sequence number (see image below).

When a sample cell in the sampling unit table is selected, the field Sampl. event: shows the sampling event titles, field Description: shows the description title and field Descriptor: shows the symbols according the descriptor type ( for categorical, for quantitative, for text and for sequence) and the descriptor title. If for a descriptor resources are available, this fact is indicated by a coloured symbol background. You may view them by a double-click on the descriptor title. If for a description resources are available, the symbol is displayed before the title. Double click in that symbol to view the resources.

For the selected cell the sample data may be edited as described in section Editing the description - Sample data tab of this manual. For modified sample cells the text colour changes to blue and the sample unit IDs will be shown with yellow background in the table.

Changing the sampling event

To shift samling units to a different sampling event, select the whole data rows by clicking in the area left from the “Sampling unit ID” column. In the tool strip the set event button appears. After clicking the button a window for selecting the new parent sampling event will be opened (see image below).

Copy sampling units

To copy one or more sampling units select the whole data rows by clicking in the area left from the “Sampling unit ID” column. In the tool strip the copy button appears (see image below).

After clicking the button the copied line are inserte at the end of the tabel (see image below). 

Delete sampling units

To delete one or more sampling units select the whole data rows by clicking in the area left from the “Sampling unit ID” column. In the tool strip the copy button appears.

New sampling unit

To enter a new sampling, click on button and a new row will be appended. After clicking the button a window for selecting the sampling event will be opened.

Continue with:

• Sample data grid view - Selection parameters
• Sample data grid view - Save or discard changes

Edit Sample Data Grid Save

Sample data grid view - Save or discard changes

When you click in button “OK” the sample data grig view is closed and all changes are automatically saved in the database. If you did some changes and want to save them without leaving the form, simply click on (see image below). If you close the window, e.g. by clicking the “Cancel” button, and still have unsaved changes, you will be asked to save or discard the changes.

Undo all changes

You may discard all unsaved changes without leaving the form by clicking the button and the data will be reloaded form the database (see image below). 

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