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