Diversity Sampling Plots

Spreadsheets

The spreadsheets similar to the grids provide a tabular access to the data. In addition you can search and filter data independent from the main form. For an introduction see two short tutorials (overview), (tables and columns). To open a spreadsheet, choose Grid - Spreadsheets - and then one of the provided versions, e.g. Organisms from the menu. A window as shown below will open where the data are marked and organized according to the database tables. If values in one of the tables are missing, this will be indicated with a grey background. The colors correspond to the overview for the database.

Start

To use any of the spreadsheets as a starting window, click on the  button. Now the application will directly open the spreadsheet with the same parameters when you close it. 

Database

To change to another database, click on the button and choose the database as described here. Please keep in mind that the main form will connect to the new selected database. 

Project

If a user has read only projects available, a button will appear in front of the project. Click on this button to change to the list of projects with read only access. The window will change into the Read only mode (see below). To return to the list of projects with write access, just click no the button again. After changing the source for the project list, the project label will blink with red to remind you, to select a project from the list. 

Getting the data

The data are always restricted to one project, selected in the corresponding field (see image above) and restricted to the first top lines as specified in the Max. res. field. To change to the next or previous block of data, use the resp. button. 

The program will organize the data in blocks indicated by the colors as shown above. In the example above the first block contains 2  Organisms all belonging to the block starting with  Hildesheim... for the Event and M-0014196 for the Specimen. The next block then starts with  Hildesheim... indicated by a change in the color containing 2 Organisms. For most of the spreadsheets, the presence of the Event in the data is a prerequisit. As long as you do not sort or filter the data (see below), these blocks will be consistent with the data. As soon as you filter or sort the data, these blocks may be split and may not correspond to the data blocks in the database any more. In the example below you see the effect of sorting by the column Taxon on breaking up the blocks. E.g. the block for the specimen M-0036950 is split into 2.

SQL

The query used for the retrieval of the data can be accessed with a click on the SQL button.

Filtering

To filter the content of a table you can apply a filter either on the whole table or on each of the columns of the table.

Table filter

This filter will apply on the whole table. Click in the black box next to the filter area and select one of the filter options as shown below.

• ♦: Filled (all visible columns)
• ◊: Empty (all visible columns)
• •: Data in table do exist
• Ø: Data in table do not exist

Tutorial:

Column filter

Tutorial:

This filter will apply on a single column of a table. To set a filter click in the field underneath the column name. A window as shown below will open. With a click on the add button you can enter a value from the current content of the column as filter.

Here you set the sorting (see below), enter the search string and choose among several ways of comparison with the contents in the database:

• =: The content must be exactly like the given value
• ≠: The content must be different to the given value
• ~: The content must be similar to the given value, use wildcards % for any string or _ for a single character
• ¬: The content must NOT be similar to the given value, use wildcards % for any string or _ for a single character
• <: The content must be smaller than the given value
• >: The content must be bigger than the given value
• |: The content must be in a given list of values
• ∉: The content must NOT be in a given list of values

For columns linked to modules of the DiversityWorkbench there may be additional options. E.g. for a column linked to DiversityTaxonNames you get 3 additional options:

• +H: Include lower Hierarchy
• +S: Include Synonyms
• +H+S: Include lower Hierarchy and Synonyms

After selecting one of these options, you will be asked for the database and the project where the data should be taken from. In the next step the corresponding names as retrieved from the selected source will be listed (see below). To change the filter click on the button and on the delete button to remove it.

Sorting

↓↑ Tutorial:

The data can be sorted by each visible column. If you click into the field underneath the column name a window will open as described above. To change between the modes of sorting, just click on the current sorting. The modes are:

• -: Not sorted
• ↑: Sorted in ascending sequence
• ↓: Sorted in descending sequence

After all parameters are set, the sort mode and filter settings will be shown in the field underneath the column name (see image below). The number indicates the sequence within the sorting columns.

Timeout

After all parameters are set, click on the button. If for any reason you get a timeout, meaning the query for the data takes too long, either simplify your query or adapt the time for the query after a click on the button. If you do not want to restrict the time for the queries, set the value to 0. The default value is 30 seconds. 

Read only mode

<> Tutorial:

The formatting of the sheet including the color indication of tables and data blocks is rather time consuming. If these are not needed, e.g. for exporting the data, you can switch to the Read only mode for higher performance. For projects with read only access (see above), the mode is automatically set to Read only. 

Editing

To edit the data, just change them. If data are missing, type or select a value in the corresponding field. To remove data, click on the x field. This will remove all entries dependent on this entry that means all data right from the entry in the same line resp. block and any depending data. If there are any depending data, a window will open as shown below listing these data. Click OK if you want to remove the selected data including all depending data as shown in the window.

To change the content of several values in a column, either select the whole column with a click on the  button or select the fields manually. To avoid the pop-up of data-entry windows e.g. for columns with a lookup list, click no the button. Once you have selected the contents to change, select the mode of change:

• Prepend: Insert the given value at the beginning of the content
• Append: Append the given value at the end of the content
• Overwrite: Remove current content and insert given value
• Replace: Replace a string in the content with the given value
• Clear: Remove the content

After selecting the change mode, enter the text in the field where necessary and click on the corresponding button to perform the changes.

With the context menu (mark area and right click) you can Remove data (for single data fields with a list behind), Tranfer or Copy the content into the clipboard and Insert the content of the clipboard into the selected cells. Tutorial:

Locked columns

Columns that can not be edited are indicated with a lighter background color of the header and a gray text of title and content. These columns contain e.g. database generated content or data from lookup tables.

Inclusion of RowGUID

By default the primary key can not be changed in the spreadsheet. In some cases you may want to change parts of the primary key. To do this, you must include the column RowGUID in the list of selected columns. The program will ensure that the changed data do not collide with existing data, that is having the same primary key. See an example in a short tutorial:

Fixed sources

Tutorial:

For columns that are linked to a remote module, you can fix the source for the query. If the source is fixed, the query will directly contact the source as shown in the image below. To set resp. change the source, click on the button. A window will open where you can select either a webservice or a source database together with a project within the database. To remove the source, click on the button (only visible if a source is present).

To see an overview for the fixed sources, click on the button. A window will open where all sources are listed with the source for the current column marked with a yellow background. 

A "right-click" on the button in the main form will show the settings as well and with a click on the button you can set the connection for the selected column. If a column depends on values from the data as for example the relations to the module DiversityTaxonNames with a dependence on the taxonomic group, the relation will change with the selected row, depending on the data retrieved from the selected row. If there is no connection to the related module defined, the button will appear gray and if the column has no relation to a module, the button will appear like

If the source is not fixed the query will start as described here.

Settings

- To change the data shown in the query results, either click on the header of the tables, e.g. Event for that table. A window as shown below will open where you can select or deselect columns, change their headers etc. Red columns need to be filled (either by the database, the program or the user). In the Alias field you can rename the columns to your preferences. The button will show a description of the columns. For columns linked to a module, the current settings for the link will be shown in addition to the description. To display a column in the sheet use the checkbox . If a column is needed, but should be hidden, select the checkbox. Displayed columns are indicated by a yellow background while hidden columns get a light yellow background. Certain columns are required (indicated by a purple color) and can not be removed e.g. if they are involved in a link to a remote module like in the example below the columns FamilyCache and OrderCache. Columns linked to a module are indicated by a blue color.

The button allows you to include one of the tables missing in the sheet (see image below). After selection of the table, select the column(s) you want to include in the sheet as shown in the image above.

To store resp. load settings, use the resp. button. If a setting file has been corrupted click on the delete button to remove it and restart with factory settings. The spreadsheet will always start with the previous settings. The location where the settings are stored are set under Administration - Resources directory ... . For an introduction see a short tutorial .

Column width

Tutorial:

The width of the columns can be set manually (in the filter area) or automatically with a click on the button. A window will open where you can choose and set the parameters for setting the column width.

Adding

Tutorial:

To add new data, use the area underneath the data table. You can enter preset values that will be used for the new data. If the new data should be added to existing entries, select the corresponding row. Then click on the + field of the datarange that should be inserted. If preset values are given, all corresponding tables will be filled together with the new inserted data. Defaults for the responsibles can be set under Administration - Customize Display. If a column is linked to a remote module, the corresponding columns will be filled together with the selection of the link, so e.g. if you select a link for a taxonomic name to a DiversityTaxonNames the columns for the taxon and if available the familiy etc. will be filled as well. To remove a linked value, select it a choose Remove from the context menu (right click).

Export

Tutorial:

To export the data as a tab separated text file (UTF8), click on the button. You will be asked if you want to include the hidden columns. These are the primary keys you may need for statistic evaluations etc.

Feedback

To send a feedback to the developer of the software, create a screenshot and click on the  button. 

Details

Tutorial:

To see all details of a dataset, select the dataset in the sheet and click on the DWB button. A window, corresponding to the main form will open, showing all data related to the selected specimen. 

Image

Tutorial:

If an image is present a preview will be shown in the right upper area of the window. Click on it to see the image in a separate form. The images are restricted to the first image of one source, so e.g. in the organisms sheet only the first image of the specimen linked with this organism will be shown while images of e.g. linked to none or another organism or the collection event will not be addressed. 

Diversity Sampling Plots

Table Editors

For the data selected in the main window the table editors offer a direct access to the tables of the database. The menu Data - Table editors provide an editor for selected tables within the database.

A window with the content of the table will open. Columns with a gray background can not be edited here. Columns with a light gray background are linked to the contents of lookup tables where you can change according to the contents of these tables.

Select Set timeout ... from the menu to increase the default timeout from 5 seconds to a higher value, e.g. for greater amounts of data.  

Editing

You can either edit the contents of the table directly or perform changes to any number of marked fields. To mark a whole column use the  button. Once you have selected the contents to change, select one of the modes of change that appear in the upper left corner. The modes of change are:

• Insert: Insert the given value at the beginning of the content
• Append: Append the given value at the end of the content
• Replace: Replace a string in the content with the given value
• Clear: Remove the content

After selecting the change mode, enter the text in the field where necessary and click on the corresponding button to perform the changes

Deleting

If you select whole lines, a delete button will appear. Click on it to remove the selected datasets from the database.

Filtering

To filter the content of the table, click in the column that should be used for filtering. Then choose the mode of comparision:

• = : The content must be exactly like the given value
• ~ : The content must contain the given value
• ≠ : The content must be different to the given value

If you want the filtering to be case sensitive, choose the a<>A option. After all parameters are set, click on the filter button. To undo the filtering, click on the button. This will reset the data to the last saved version. If you want your changes to be saved, click the button before you reset the filtering. If you close the window all changes so far will be saved automatically. So if you do not want to save your changes, click on the button before closing the window.

Export

To export the data as a tab separated text file, click on the button. The file will be automatically saved in the client-folder.  

To export the data in a SQLite database click on the button. The data will be exported into the SQLite database DiversityAgentTables.sqlite in the folder Export in your application directory. If you want to save previous exports, please rename the SQLite database or copy it to a different directory.  

Log data

To see the log data of the table, you can click on the button. The content of the log table can not be changed, but is read only.  

Diversity Sampling Plots

Import wizard

This tutorial demostrates the import of a small file into the database. The following data should be imported (the example file is included in the software):   At the end of this tutorial you will have imported several datasets and practiced most of the possibilities provided by the import wizard. The import is done in 2 steps to demonstrate the attachment functionallity of the wizard.

 Step1 - Import of the collection events

Choose Data → Import → Wizard Sampling plot … from the menu. A window as shown below will open. that will lead you through the import of the data. The window is separated in 3 areas. On the left side, you see a list of possible data related import steps according to the type of data you choosed for the import. On the right side you see the list of currently selected import steps. In the middle part the details of the selected import steps are shown.

Choosing the File

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

Choosing the data ranges

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

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

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

Attaching data

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

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

Merging data

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

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

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

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

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

Table data

To set the source for the columns in the file, select the step of a table listed underneath the Merge step. All columns available for importing data will be listed in the central part of the window. In the example shown below, the first column is used to attach the new data to data in the database.

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

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

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

Testing

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

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

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

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

Import

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

Tables

CollectionSpecimen (CollectionSpecimen)
Parent: CollectionEvent
Merge handling: Insert

IdentificationUnit_1 (IdentificationUnit)
Parent: CollectionSpecimen
Merge handling: Merge

IdentificationUnitAnalysis_1_1 (IdentificationUnitAnalysis)
Parent: IdentificationUnit_1
Merge handling: Update

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

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

Diversity Sampling Plots

Backup

The chapter below refers to the module DiversityCollection but is valid for DiversityAgents as well

If you want to create a backup of your database, there are 2 options. You may either export the data as csv files to your local computer or you may create a backup on the server.

Export data as csv

To export your data as csv files to your local computer, choose Data → Export → CSV(bcp) ... from the menu. A window will open as shown below, where you can select the tables that should be exported. Click on the Start Export button to export your data. If you choose the option as shown below 2 files will be created for every table. The first file (*.csv) contains the data while the second file (*.xml) contains the structure of the table.

Create backup on the server

To create a backup of your database on the server, choose Data → Backup database from the menu. This will create a SQL-Server backup on the server where the database is located. Ensure that there is enough space on the server.

Another option is to create a direct copy of the database files on the server. For this you have to use the functions provided by SQL-Server. However, you need administration rights for the database you want to create a backup of. Open the Enterprise Manager for SQL-Server, choose the database and detach it from the server as shown in the image below.

After detaching the database, you can save a copy of the ..._Data.MDF file to keep it as a backup.

After storing the backup you have to reattach the database.

A dialog will appear where you have to select the original database file in your directory.

Diversity Sampling Plots

Export Wizard

The export wizard provides a possibility to export the data selected in the main form. The data are exported as tab separated text file. The export may include transformations of the data as well as information provided by linked modules and webservices. Choose Data - Export - Export wizard from the menu and then select one of the export targets (Event, Specimen, ...). For a short introduction see the tutorial.  

Adding tables

There are the following ways to add tables:

• One parallel table
• Several parallel tables according to selected data
• Dependent table

All options will include the depending tables as defined for the default table. The option for several tables will add as many tables as there are found in the data.

If you added parallel tables, you should set the sequence of the datasets within these tables: For the columns that should be used for sorting the data, set the ordering sequence to a value > 0 and choose if the ordering sequence should be ascending or descending .

Adding related values

Certain columns in the database may provide information linked to another table or a module resp. webservice . Click on the button to add a linked value.

Adding and editing file columns

To add columns to the exported file, use the buttons. In the textbox at the top of the file column, you can change the header for the column. To change the position of a file column use the resp. button. To fuse a column with the previous column, click in the gray bar on the left side of the column that will change to for fused columns. To remove a file column, use the delete button. Pre- and postfixes for the columns can directly be entered in the corresponding fields. To apply transformations on the data click on the button.  

Filter

To filter the exported data, use the filter function. Click on the button and enter the text for the filter. Only data matching the filter string will be exported. If a filter is set, the button will have a red background to remind you of the filter. The filter may be set for any number of columns you need for the restriction of the exported data.  

Rowfilter

This filter in contrast to the filter above strictly applies to the row according to the sequence of the data. For an explanation see a short tutorial .

Test

To test the export choose the Test tab, set the number of lines that should be included in the test and click on the Test export button. To inspect the result in a separate window, click on the button.

SQL

If you want to inspect the SQL commands created during the test check this option. To see the generated SQL click on the SQL button after the Test export. A window containing all commands including their corresponding tables will be shown.

Export

To export your data to a file, choose the Export tab. If you want to store the file in different place use the button to choose the directory and edit the name of the file if necessary. Check the include a schema option if you want to save a schema together with your export. To start the export, click on the Export data   button. To open the exported file, use the button.

Export to SQLite

To export your data into a SQLite database, choose the Export to SQLite tab. You may change the preset name of the database in order to keep previous exports. Otherwise you overwrite previous exports with the same filename. To start the export, click on the Export data   button. To view the exported data, use the button.

Schema

To handle the settings of your export, choose the Schema tab. To load a predefined schema, click on the open button. To reset the settings to the default, click on the undo button. To save the current schema click on the save button. With the button you can inspect the schema in a separate window.

Diversity Sampling Plots

TABLES, VIEWS, FUNCTIONS, PROCEDURES, ROLES

The following objects are not included:

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

TABLES

Table LocalisationSystem

The geographic localisation systems, e.g. coordinates

Table ProjectUser

The projects a user has access to

Table Property

Properties of the site of the sampling plot as stored in DiversityScientificTerms

Table ReplicationPublisher

For data replication - the links to publishers

Table SamplingPlot

A sampling plot with its geographical position and geometry

Table SamplingPlotLocalisation

The geographic localisation of a sampling plot

Depending on:

• LocalisationSystem
• SamplingPlot

trgInsSamplingPlotLocalisation

Table SamplingPlotProperty

Properties of the site of a sampling plot

Depending on:

• Property
• SamplingPlot

Table SamplingPlotResource

Resouces of the sampling plot, e.g. images

Depending on:

• SamplingPlot

Table SamplingProject

The projects to which a sampling plot is related

Depending on:

• SamplingPlot

VIEWS

View PlotID_UserAvailable

IDs of the plots a user has access to

Depending on:

• ProjectUser
• SamplingPlot
• SamplingProject

View ProjectList

The list of the projects a user has access to

Depending on:

• ProjectProxy
• ProjectUser

View SamplingPlotGeoInfo

Geographical properties of a plot extracted from column PlotGeography_Cache including up to 4 hierarchy levels

Depending on:

• SamplingPlot

View ViewBaseURL

Providing the BaseURL for linked servers

Depending on:

• BaseURL

View ViewDiversityWorkbenchModule

Provides the name of the DiversityWorkbench module as defined in function DiversityWorkbenchModule

Depending on:

• DiversityWorkbenchModule

View ViewLocality

Gazetteer data for query

Depending on:

• LocalisationSystem
• SamplingPlotLocalisation

View ViewSamplingPlot

Providing the content of table SamplingPlot for linked servers with conversion of geography tables to text

Depending on:

• SamplingPlot

View ViewSamplingPlotAllLocalisations

Providing the result of SamplingPlotAllLocalisations for linked servers

Depending on:

• SamplingPlotAllLocalisations

View ViewSamplingPlotGeoInfo

Providing the result of SamplingPlotGeoInfo for linked servers

Depending on:

• SamplingPlot
• ViewBaseURL

View ViewSamplingPlotHierarchy5

Basic for for ViewSamplingPlotHierarchy

Depending on:

• SamplingPlot

View ViewSamplingPlotList

Providing the result of SamplingPlotList for linked servers

Depending on:

• SamplingPlotList

View ViewSamplingPlotListForCurrentProject

Providing the result of SamplingPlotListForCurrentProject for linked servers

Depending on:

• SamplingPlotListForCurrentProject

View ViewTest

FUNCTIONS

Function BaseURL

Provides the basic address for accessing the database

DataType: varchar (255)

Function DefaultProjectID

The ID of the current project selected by a user or first project available

DataType: int

Depending on:

• ProjectUser
• UserProxy

Function DiversityWorkbenchModule

The name of the module within the DWB

DataType: nvarchar (50)

Function PrivacyConsentInfo

The website providing information concerning the privacy consent

DataType: varchar (900)

Function SamplingPlotAllLocalisations

Returns a table that lists all the localisations of a plot included those inherited from a parent plot

Depending on:

• LocalisationSystem
• SamplingPlot
• SamplingPlotLocalisation

Function SamplingPlotAllProperties

Returns a table that lists all the properties of a plot included those inherited from a parent plot

Depending on:

• SamplingPlot
• SamplingPlotProperty

Function SamplingPlotChildNodes

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

Depending on:

• SamplingPlot

Function SamplingPlotGeography

Returns a table that lists all plots togeher with the geography possibly derived from the parent plot.

Depending on:

• SamplingPlot

Function SamplingPlotHierarchy

Returns a table that lists all the plots related to the given plot

Depending on:

• SamplingPlot
• SamplingPlotChildNodes

Function SamplingPlotList

Returns a table that lists all the SamplingPlot items related to the given SamplingPlot.

Depending on:

• SamplingPlot

Function SamplingPlotListForCurrentProject

Returns a table that lists all the SamplingPlot items related to the given project.

Depending on:

• DefaultProjectID
• ProjectList
• SamplingPlot
• SamplingProject

Function SamplingPlotSuperiorNodes

Returns a table that lists all the plots related to the given plot

Depending on:

• SamplingPlot

Function UserID

ID of the User as stored in table UserProxy

DataType: int

Depending on:

• UserProxy

Function UserName

Name of the User as stored in table UserProxy

DataType: nvarchar (50)

Depending on:

• UserProxy

Function Version

The version of the database

DataType: nvarchar (8)

Function VersionClient

The version of the client compatible with the database

DataType: nvarchar (11)

PROCEDURES

Procedure procGetSiteProperties

Depending on:

• SamplingPlot
• SamplingPlotProperty

Procedure procInsertSamplingPlotCopy

Creation of a copy of a dataset in table SamplingProject including the content in depending tables

Depending on:

• SamplingPlot
• SamplingPlotLocalisation
• SamplingPlotProperty
• SamplingPlotResource
• SamplingProject

Procedure ProcSamplingPlotLocalisationAll

Depending on:

• LocalisationSystem
• SamplingPlot
• SamplingPlotLocalisation

Procedure SetUserProjects

Create database user and assign training projects

Depending on:

• UserProxy

ROLES

Role DataManager

Permissions as Editor but with permission to delete data in table SamplingPlot

• DiversityWorkbenchEditor

Role DiversityWorkbenchAdministrator

Role with full permission to the content

• DiversityWorkbenchEditor

Role DiversityWorkbenchEditor

Role for editing the content

• DiversityWorkbenchUser

Role DiversityWorkbenchUser

Role with read only access

Diversity Sampling Plots

No-SQL Interface

JSON Cache

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

Table JsonCache

Content of table JsonCache restricted to public available data

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

Interface in clients

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

Update

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

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

Summary

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

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

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

Diversity Sampling Plots

Documentation

Tools for the database documentation

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

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

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

Select the objects that should be included in the documentation:

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

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

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

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

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

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

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

HTML, MediaWiki, JSP-Wiki

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