Diversity SamplingPlots
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
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LocalisationSystemID | int | Unique ID for the localisation system (= Primary key) | NO | - |
| LocalisationSystemParentID | int | LocalisationSystemID of the superior LocalisationSystem | YES | Refers to table LocalisationSystem |
| LocalisationSystemName | nvarchar (100) | Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS | NO | - |
| DefaultAccuracyOfLocalisation | nvarchar (50) | The default for the accuracy of values that can be reached with this method | YES | - |
| DefaultMeasurementUnit | nvarchar (50) | The default measurement unit for the localisation system, e.g. m, geograpic coordinates | YES | - |
| ParsingMethodName | nvarchar (50) | Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table SamplingPlotLocalisation | YES | - |
| DisplayText | nvarchar (50) | Short abbreviated description of the localisation system as displayed in the user interface | YES | - |
| DisplayEnable | bit | Specifies if this item is enabled to be used within the database. LocalisationSystems can be disabled to avoid seeing them, but to keep the definition for the future. | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| Description | nvarchar (255) | Description of the localisation method | YES | - |
| DisplayTextLocation1 | nvarchar (50) | Short abbreviated description of the attribute Location1 in the table CollectionGeography as displayed in the user interface | YES | - |
| DescriptionLocation1 | nvarchar (255) | Description of the attribute Location1 in the table CollectionGeography as displayed in the user interface | YES | - |
| DisplayTextLocation2 | nvarchar (50) | Short abbreviated description of the attribute Location2 in the table CollectionGeography as displayed in the user interface | YES | - |
| DescriptionLocation2 | nvarchar (255) | Description of the attribute Location2 in the table CollectionGeography as displayed in the user interface | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
| LogCreatedBy | nvarchar (50) | Name of user who first entered (typed or imported) the data.Default value: user_name() | YES | - |
| LogCreatedWhen | datetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of user who last updated the data.Default value: user_name() | YES | - |
| LogUpdatedWhen | datetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
Table ProjectUser
The projects a user has access to
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LoginName | nvarchar (50) | The login of the user | NO | - |
| ProjectID | int | The ID of the project the user has access to | NO | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | YES | - |
Table Property
Properties of the site of the sampling plot as stored in DiversityScientificTerms
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PropertyID | int | The ID of the sampling property, Primary key | NO | - |
| PropertyParentID | int | LocalisationSystemID of the superior LocalisationSystem | YES | Refers to table Property |
| PropertyName | nvarchar (100) | Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS | NO | - |
| DefaultAccuracyOfProperty | nvarchar (50) | The default for the accuracy of values that can be reached with this method | YES | - |
| DefaultMeasurementUnit | nvarchar (50) | The default measurement unit for measured properties | YES | - |
| ParsingMethodName | nvarchar (50) | Internal value, specifying a programming method used for parsing text in fields Location1/Location2 in table CollectionLocalisation | NO | - |
| DisplayText | nvarchar (50) | Short abbreviated description of the localisation system as displayed in the user interface | YES | - |
| DisplayEnabled | bit | Specifies if this item is enabled to be used within the database. LocalisationSystems can be disabled to avoid seeing them, but to keep the definition for the future. | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| Description | nvarchar (255) | Description of the localisation method | YES | - |
| LogCreatedBy | nvarchar (50) | Name of user who first entered (typed or imported) the data.Default value: user_name() | YES | - |
| LogCreatedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database.Default value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of user who last updated the data.Default value: user_name() | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated.Default value: getdate() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | YES | - |
| PropertyURI | varchar (1000) | The link to the entry in a DiversityScientificTerms database | YES | - |
Table ReplicationPublisher
For data replication - the links to publishers
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| DatabaseName | varchar (255) | The name of the publishing database | NO | - |
| Server | varchar (255) | The name or address of the server where the publishing database is located | NO | - |
| Port | smallint | The port used by the server | YES | - |
| LogCreatedWhen | datetime | Point in time when this data set was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Name of the creator of this data setDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | Point in time when this data set was updated lastDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of the person to update this data set lastDefault value: user_name() | YES | - |
Table SamplingPlot
A sampling plot with its geographical position and geometry
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO | - |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key | YES | Refers to table SamplingPlot |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot | NO | - |
| PlotGeography_Cache | geography | The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints | YES | - |
| PlotGeometry_Cache | geometry (MAX) | The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints | YES | - |
| PlotDescription | nvarchar (MAX) | A short text, describing the sampling plot | YES | - |
| InternalNotes | nvarchar (MAX) | Internal notes regarding the sampling plot, not published on the web | YES | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| PlotType | nvarchar (50) | The type of the plot, e.g. trap | YES | - |
| CountryCache | nvarchar (50) | The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table SamplingPlotLocalisation
The geographic localisation of a sampling plot
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlotID | int | Refers to the ID of SamplingPlot (= Foreign key and part of primary key) | NO | Refers to table SamplingPlot |
| LocalisationSystemID | int | Refers to the ID of LocalisationSystem (= Foreign key and part of primary key) | NO | Refers to table LocalisationSystem |
| Location1 | nvarchar (255) | Either a named location selected from a thesaurus (e. g. ‘Germany, Bavaria, Kleindingharting’) or altitude range or other values (e. g. 100-200 m) | YES | - |
| Location2 | nvarchar (255) | Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus | YES | - |
| LocationAccuracy | nvarchar (50) | The accuracy of the determination of this locality | YES | - |
| LocationNotes | nvarchar (MAX) | Notes on the location | YES | - |
| DeterminationDate | smalldatetime | Date of the determination of the geographical localisation | YES | - |
| DistanceToLocation | varchar (50) | Distance from the specified place to the real location of the sampling plot (m) | YES | - |
| DirectionToLocation | varchar (50) | Direction from the specified place to the real location of the sampling plot (Degrees rel. to north) | YES | - |
| ResponsibleName | nvarchar (255) | The name of the agent (person or organization) responsible for this entry. | YES | - |
| ResponsibleAgentURI | varchar (255) | URI of the person or organisation responsible for the data (see e.g. module DiversityAgents) | YES | - |
| Geography | geography | The geography of the localisation | YES | - |
| AverageAltitudeCache | float | Calculated altitude as parsed from the location fields | YES | - |
| AverageLatitudeCache | float | Calculated latitude as parsed from the location fields | YES | - |
| AverageLongitudeCache | float | Calculated longitude as parsed from the location fields | YES | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- LocalisationSystem
- SamplingPlot
trgInsSamplingPlotLocalisation
Table SamplingPlotProperty
Properties of the site of a sampling plot
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlotID | int | The ID of the sampling plot, foreign key and part of primary key | NO | Refers to table SamplingPlot |
| PropertyID | int | ID of the property to which the sampling plot property belongs | NO | Refers to table Property |
| PropertyURI | varchar (255) | URI referring to an external datasource e.g. DiversityTerminology | NO | - |
| DisplayText | nvarchar (255) | The text for the property as shown e.g. in a user interface | YES | - |
| PropertyHierarchyCache | nvarchar (MAX) | A cached text of the complete name of the descriptor including superior categories if present | YES | - |
| PropertyValue | nvarchar (255) | The value of a captured feature e.g. temperature, pH, vegetation etc. If there is a range this is the lower or first value | YES | - |
| ResponsibleName | nvarchar (255) | The name of the agent (person or organization) responsible for this entry. | YES | - |
| ResponsibleAgentURI | varchar (255) | URI of the person or organisation responsible for the data (see e.g. module DiversityAgents) | YES | - |
| Notes | nvarchar (MAX) | Notes about the property of the colletion site. | YES | - |
| AverageValueCache | float | For numeric values - a cached average value according to the | YES | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | YES | - |
Depending on:
- Property
- SamplingPlot
Table SamplingPlotResource
Resouces of the sampling plot, e.g. images
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlotID | int | The ID of the sampling plot, foreign key and part of primary key | NO | Refers to table SamplingPlot |
| ResourceURI | varchar (255) | URI referring to the file or link of the resource | NO | - |
| ResponsibleName | nvarchar (255) | The name of the agent (person or organization) responsible for this entry. | YES | - |
| ResponsibleAgentURI | varchar (255) | URI of the person or organisation responsible for the data (see e.g. module DiversityAgents) | YES | - |
| Notes | nvarchar (MAX) | Notes about the resource. | YES | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | YES | - |
Depending on:
- SamplingPlot
Table SamplingProject
The projects to which a sampling plot is related
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlotID | int | The ID of the sampling plot, foreign key and part of primary key | NO | Refers to table SamplingPlot |
| ProjectID | int | ID of the project to which the sampling plot belongs (Projects are defined in DiversityProjects) | NO | - |
| LogCreatedWhen | datetime | The time when this dataset was createdDefault value: getdate() | YES | - |
| LogCreatedBy | nvarchar (50) | Who created this datasetDefault value: user_name() | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | Who was the last to update this datasetDefault value: user_name() | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- SamplingPlot
VIEWS
View PlotID_UserAvailable
IDs of the plots a user has access to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO |
Depending on:
- ProjectUser
- SamplingPlot
- SamplingProject
View ProjectList
The list of the projects a user has access to
| Column | Data type | Description | Nullable |
|---|---|---|---|
| Project | nvarchar (50) | The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) | YES |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO |
Depending on:
- ProjectProxy
- ProjectUser
View SamplingPlotGeoInfo
Geographical properties of a plot extracted from column PlotGeography_Cache including up to 4 hierarchy levels
| Column | Data type | Description | Nullable |
|---|---|---|---|
| Latitude | float | Latitude of the center of the plot | YES |
| Longitude | float | Longitude of the center of the plot | YES |
| Accuracy angle | varchar (32) | maximum angle between the center and the circumference as string | YES |
| AccuracyAngle | float | maximum angle between the center and the circumference as numeric value | YES |
| AreaM2 | float | Area in square meters | YES |
| Area | varchar (34) | Area including unit | YES |
| Geography | nvarchar (MAX) | Geography of the plot or a parent plot | YES |
| CountryCache | nvarchar (50) | The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation | YES |
| PlotID | int | The ID of the sampling plot, Primary key | NO |
Depending on:
- SamplingPlot
View ViewBaseURL
Providing the BaseURL for linked servers
| Column | Data type | Description | Nullable |
|---|---|---|---|
| BaseURL | varchar (255) | Content of function BaseURL | YES |
Depending on:
- BaseURL
View ViewDiversityWorkbenchModule
Provides the name of the DiversityWorkbench module as defined in function DiversityWorkbenchModule
| Column | Data type | Description | Nullable |
|---|---|---|---|
| DiversityWorkbenchModule | nvarchar (50) | The name of the DiversityWorkbench module | YES |
Depending on:
- DiversityWorkbenchModule
View ViewLocality
Gazetteer data for query
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | Refers to the ID of SamplingPlot (= Foreign key and part of primary key) | NO |
| Locality | nvarchar (255) | Content of column Location1 | YES |
Depending on:
- LocalisationSystem
- SamplingPlotLocalisation
View ViewSamplingPlot
Providing the content of table SamplingPlot for linked servers with conversion of geography tables to text
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key | YES |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot | NO |
| PlotGeography_Cache | nvarchar (MAX) | The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints | YES |
| PlotGeometry_Cache | nvarchar (MAX) | The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints | YES |
| PlotDescription | nvarchar (MAX) | A short text, describing the sampling plot | YES |
| InternalNotes | nvarchar (MAX) | Internal notes regarding the sampling plot, not published on the web | YES |
| CountryCache | nvarchar (50) | The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation | YES |
| PlotType | nvarchar (50) | The type of the plot, e.g. trap | YES |
Depending on:
- SamplingPlot
View ViewSamplingPlotAllLocalisations
Providing the result of SamplingPlotAllLocalisations for linked servers
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key | YES |
| LocalisationSystemID | int | Unique ID for the localisation system (= Primary key) | NO |
| Location1 | nvarchar (255) | Either a named location selected from a thesaurus (e. g. ‘Germany, Bavaria, Kleindingharting’) or altitude range or other values (e. g. 100-200 m) | YES |
| Location2 | nvarchar (255) | Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus | YES |
| DisplayText | nvarchar (255) | Short abbreviated description of the localisation system as displayed in the user interface | YES |
Depending on:
- SamplingPlotAllLocalisations
View ViewSamplingPlotGeoInfo
Providing the result of SamplingPlotGeoInfo for linked servers
| Column | Data type | Description | Nullable |
|---|---|---|---|
| Latitude | float | Latitude of the center of the plot | YES |
| Longitude | float | Longitude of the center of the plot | YES |
| Accuracy angle | varchar (32) | maximum angle between the center and the circumference as string | YES |
| AccuracyAngle | float | maximum angle between the center and the circumference as numeric value | YES |
| AreaM2 | float | Area in square meters | YES |
| Area | varchar (34) | Area including unit | YES |
| Geography | nvarchar (MAX) | Geography of the plot or a parent plot | YES |
| CountryCache | nvarchar (50) | The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation | YES |
| PlotID | int | The ID of the sampling plot, Primary key | NO |
| URL | varchar (285) | URL as retrieved from BaseURL + PlotID | YES |
Depending on:
- SamplingPlot
- ViewBaseURL
View ViewSamplingPlotHierarchy5
Basic for for ViewSamplingPlotHierarchy
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO |
| DisplayText | nvarchar (3015) | - | NO |
Depending on:
- SamplingPlot
View ViewSamplingPlotList
Providing the result of SamplingPlotList for linked servers
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key | YES |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot | NO |
| PlotGeography_Cache | nvarchar (MAX) | The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints | YES |
| PlotGeometry_Cache | nvarchar (MAX) | The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints | YES |
| PlotDescription | nvarchar (MAX) | A short text, describing the sampling plot | YES |
| InternalNotes | nvarchar (MAX) | Internal notes regarding the sampling plot, not published on the web | YES |
| DisplayText | nvarchar (MAX) | The combined Identifiers of the whole hierarchy related to a plot | NO |
Depending on:
- SamplingPlotList
View ViewSamplingPlotListForCurrentProject
Providing the result of SamplingPlotListForCurrentProject for linked servers
| Column | Data type | Description | Nullable |
|---|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key | NO |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key | YES |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot | NO |
| PlotGeography_Cache | nvarchar (MAX) | The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints | YES |
| PlotGeometry_Cache | nvarchar (MAX) | The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints | YES |
| PlotDescription | nvarchar (MAX) | A short text, describing the sampling plot | YES |
| InternalNotes | nvarchar (MAX) | Internal notes regarding the sampling plot, not published on the web | YES |
| DisplayText | nvarchar (MAX) | The combined Identifiers of the whole hierarchy related to a plot | NO |
| PlotType | nvarchar (50) | The type of the plot, e.g. trap | YES |
| CountryCache | nvarchar (50) | The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation | YES |
| ProjectID | int | The ID of the project the user has access to | NO |
Depending on:
- SamplingPlotListForCurrentProject
View ViewTest
| Column | Data type | Description | Nullable |
|---|---|---|---|
| spid | smallint | - | NO |
| ecid | smallint | - | NO |
| status | nchar (30) | - | NO |
| loginame | nvarchar (128) | - | YES |
| hostname | nchar (128) | - | NO |
| blk | char (5) | - | YES |
| dbname | nvarchar (128) | - | YES |
| cmd | nchar (16) | - | NO |
| request_id | int | - | NO |
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
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| LocalisationSystemID | int | Unique ID for the localisation system (= Primary key) |
| Location1 | nvarchar (255) | Either a named location selected from a thesaurus (e. g. ‘Germany, Bavaria, Kleindingharting’) or altitude range or other values (e. g. 100-200 m) |
| Location2 | nvarchar (255) | Corresponding value to Location1 e.g. ID or URI of gazetteer or thesaurus |
| DisplayText | nvarchar (255) | Short abbreviated description of the localisation system as displayed in the user interface |
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
| Column | DataType | Description |
|---|---|---|
| PropertyID | int | ID of the property to which the sampling plot property belongs |
| PlotID | int | The ID of the sampling plot, Primary key |
| PropertyURI | varchar (255) | URI referring to an external datasource e.g. DiversityTerminology |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| DisplayText | nvarchar (255) | The text for the property as shown e.g. in a user interface |
| PropertyHierarchyCache | nvarchar (MAX) | A cached text of the complete name of the descriptor including superior categories if present |
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.
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | PlotID for which the children should be returned |
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot |
Depending on:
- SamplingPlot
Function SamplingPlotGeography
Returns a table that lists all plots togeher with the geography possibly derived from the parent plot.
| Parameter | DataType | Description |
|---|---|---|
| @PlotIDs | varchar (8000) | List of comma separated PlotIDs for which the geography should be returned |
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| Geography | geography (MAX) | Content of column PlotGeography_Cache if available within the hierarchy |
Depending on:
- SamplingPlot
Function SamplingPlotHierarchy
Returns a table that lists all the plots related to the given plot
| Parameter | DataType | Description |
|---|---|---|
| @PlotID | int | The ID of the sampling plot, Primary key |
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot |
Depending on:
- SamplingPlot
- SamplingPlotChildNodes
Function SamplingPlotList
Returns a table that lists all the SamplingPlot items related to the given SamplingPlot.
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot |
| PlotGeography_Cache | geography (MAX) | The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints |
| PlotGeometry_Cache | geometry (MAX) | The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints |
| PlotDescription | nvarchar (MAX) | A short text, describing the sampling plot |
| InternalNotes | nvarchar (MAX) | Internal notes regarding the sampling plot, not published on the web |
| DisplayText | nvarchar (MAX) | The combined Identifiers of the whole hierarchy related to a plot |
Depending on:
- SamplingPlot
Function SamplingPlotListForCurrentProject
Returns a table that lists all the SamplingPlot items related to the given project.
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot |
| PlotGeography_Cache | geography (MAX) | The cached geography of the plot as geographical entry, e.g. as a polygon derived from the geography in table SamplingPlotPoints |
| PlotGeometry_Cache | geometry (MAX) | The cached geometry of a sampling plot, e.g. as a polygon derived from the geometry in table SamplingPlotPoints |
| PlotDescription | nvarchar (MAX) | A short text, describing the sampling plot |
| InternalNotes | nvarchar (MAX) | Internal notes regarding the sampling plot, not published on the web |
| DisplayText | nvarchar (MAX) | The combined Identifiers of the whole hierarchy related to a plot |
| PlotType | nvarchar (50) | The type of the plot, e.g. trap |
| CountryCache | nvarchar (50) | The country where the collection sampling plot is located. Cached value derived from an entry in SamplingPlotLocalisation |
| ProjectID | int | The ID of the project the user has access to |
Depending on:
- DefaultProjectID
- ProjectList
- SamplingPlot
- SamplingProject
Function SamplingPlotSuperiorNodes
Returns a table that lists all the plots related to the given plot
| Parameter | DataType | Description |
|---|---|---|
| @PlotID | int | The ID of the sampling plot, Primary key |
| Column | DataType | Description |
|---|---|---|
| PlotID | int | The ID of the sampling plot, Primary key |
| PartOfPlotID | int | The ID of the plot of which the current plot is a part of, Foreign key |
| PlotIdentifier | nvarchar (500) | The identifier or name of the plot |
| PlotHierarchyCache | nvarchar (4000) | The combined Identifiers of the whole hierarchy related to a 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)
| Parameter | DataType | Description |
|---|---|---|
| @ID | varchar (10) | ID of the user |
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
| Parameter | DataType | Description |
|---|---|---|
| @PlotID | int | The ID of the sampling plot, Primary key |
| @OriginalPlotID | int | The ID of the original plot |
| @PlotIdentifier | nvarchar (50) | reserved for future, not used in current version |
Depending on:
- SamplingPlot
- SamplingPlotLocalisation
- SamplingPlotProperty
- SamplingPlotResource
- SamplingProject
Procedure ProcSamplingPlotLocalisationAll
Depending on:
- LocalisationSystem
- SamplingPlot
- SamplingPlotLocalisation
Procedure SetUserProjects
Create database user and assign training projects
| Parameter | DataType | Description |
|---|---|---|
| @User | varchar (50) | database user to assign training projects for |
Depending on:
- UserProxy
ROLES
Role DataManager
Permissions as Editor but with permission to delete data in table SamplingPlot
| Permissions | SELECT | INSERT | UPDATE | DELETE | EXECUTE | Type |
|---|---|---|---|---|---|---|
| LocalisationSystem | Diversity Workbench User | TABLE | ||||
| LocalisationSystem_log | TABLE | |||||
| ProjectUser | Diversity Workbench User | TABLE | ||||
| Property | Diversity Workbench Editor | TABLE | ||||
| Property_log | TABLE | |||||
| ReplicationPublisher | Diversity Workbench Editor | TABLE | ||||
| SamplingPlot | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | • | TABLE | |
| SamplingPlot_log | TABLE | |||||
| SamplingPlotLocalisation | Diversity Workbench User | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |
| SamplingPlotLocalisation_log | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |||
| SamplingPlotProperty | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |
| SamplingPlotProperty_log | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |||
| SamplingPlotResource | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |
| SamplingPlotResource_log | TABLE | |||||
| SamplingProject | Diversity Workbench User | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |
| SamplingProject_log | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |||
| PlotID_UserAvailable | Diversity Workbench User | VIEW | ||||
| ProjectList | Diversity Workbench User | VIEW | ||||
| SamplingPlotGeoInfo | Diversity Workbench User | VIEW | ||||
| ViewBaseURL | Diversity Workbench User | VIEW | ||||
| ViewDiversityWorkbenchModule | Diversity Workbench User | VIEW | ||||
| ViewLocality | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlot | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotAllLocalisations | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotGeoInfo | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotHierarchy5 | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotList | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotListForCurrentProject | Diversity Workbench User | VIEW | ||||
| ViewTest | VIEW | |||||
| BaseURL | Diversity Workbench User | FUNCTION | ||||
| DefaultProjectID | Diversity Workbench User | FUNCTION | ||||
| DiversityWorkbenchModule | Diversity Workbench User | FUNCTION | ||||
| PrivacyConsentInfo | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotAllLocalisations | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotAllProperties | Diversity Workbench Editor | FUNCTION | ||||
| SamplingPlotChildNodes | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotGeography | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotHierarchy | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotList | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotListForCurrentProject | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotSuperiorNodes | Diversity Workbench User | FUNCTION | ||||
| UserID | Diversity Workbench User | FUNCTION | ||||
| UserName | Diversity Workbench User | FUNCTION | ||||
| Version | Diversity Workbench User | FUNCTION | ||||
| VersionClient | Diversity Workbench User | FUNCTION | ||||
| procGetSiteProperties | PROCEDURE | |||||
| procInsertSamplingPlotCopy | Diversity Workbench Editor | PROCEDURE | ||||
| ProcSamplingPlotLocalisationAll | PROCEDURE | |||||
| SetUserProjects | PROCEDURE | |||||
| Inheriting from roles: |
- DiversityWorkbenchEditor
Role DiversityWorkbenchAdministrator
Role with full permission to the content
| Permissions | SELECT | INSERT | UPDATE | DELETE | EXECUTE | Type |
|---|---|---|---|---|---|---|
| LocalisationSystem | Diversity Workbench User | TABLE | ||||
| LocalisationSystem_log | • | • | TABLE | |||
| ProjectUser | • | • | • | TABLE | ||
| Property | Diversity Workbench Editor | • | • | • | TABLE | |
| Property_log | • | • | TABLE | |||
| ReplicationPublisher | • | • | • | • | TABLE | |
| SamplingPlot | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | • | TABLE | |
| SamplingPlot_log | TABLE | |||||
| SamplingPlotLocalisation | Diversity Workbench User | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |
| SamplingPlotLocalisation_log | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |||
| SamplingPlotProperty | Diversity Workbench Editor | • | • | • | TABLE | |
| SamplingPlotProperty_log | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |||
| SamplingPlotResource | Diversity Workbench Editor | • | • | • | TABLE | |
| SamplingPlotResource_log | TABLE | |||||
| SamplingProject | Diversity Workbench User | Diversity Workbench Editor | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |
| SamplingProject_log | Diversity Workbench Editor | Diversity Workbench Editor | TABLE | |||
| PlotID_UserAvailable | Diversity Workbench User | VIEW | ||||
| ProjectList | Diversity Workbench User | VIEW | ||||
| SamplingPlotGeoInfo | Diversity Workbench User | VIEW | ||||
| ViewBaseURL | Diversity Workbench User | VIEW | ||||
| ViewDiversityWorkbenchModule | Diversity Workbench User | VIEW | ||||
| ViewLocality | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlot | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotAllLocalisations | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotGeoInfo | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotHierarchy5 | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotList | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotListForCurrentProject | Diversity Workbench User | VIEW | ||||
| ViewTest | VIEW | |||||
| BaseURL | Diversity Workbench User | FUNCTION | ||||
| DefaultProjectID | Diversity Workbench User | FUNCTION | ||||
| DiversityWorkbenchModule | Diversity Workbench User | FUNCTION | ||||
| PrivacyConsentInfo | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotAllLocalisations | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotAllProperties | Diversity Workbench Editor | FUNCTION | ||||
| SamplingPlotChildNodes | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotGeography | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotHierarchy | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotList | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotListForCurrentProject | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotSuperiorNodes | Diversity Workbench User | FUNCTION | ||||
| UserID | Diversity Workbench User | FUNCTION | ||||
| UserName | Diversity Workbench User | FUNCTION | ||||
| Version | Diversity Workbench User | FUNCTION | ||||
| VersionClient | Diversity Workbench User | FUNCTION | ||||
| procGetSiteProperties | PROCEDURE | |||||
| procInsertSamplingPlotCopy | Diversity Workbench Editor | PROCEDURE | ||||
| ProcSamplingPlotLocalisationAll | PROCEDURE | |||||
| SetUserProjects | PROCEDURE | |||||
| Inheriting from roles: |
- DiversityWorkbenchEditor
Role DiversityWorkbenchEditor
Role for editing the content
| Permissions | SELECT | INSERT | UPDATE | DELETE | EXECUTE | Type |
|---|---|---|---|---|---|---|
| LocalisationSystem | Diversity Workbench User | TABLE | ||||
| LocalisationSystem_log | TABLE | |||||
| ProjectUser | Diversity Workbench User | TABLE | ||||
| Property | • | TABLE | ||||
| Property_log | TABLE | |||||
| ReplicationPublisher | • | TABLE | ||||
| SamplingPlot | • | • | • | TABLE | ||
| SamplingPlot_log | TABLE | |||||
| SamplingPlotLocalisation | Diversity Workbench User | • | • | • | TABLE | |
| SamplingPlotLocalisation_log | • | • | TABLE | |||
| SamplingPlotProperty | • | • | • | • | TABLE | |
| SamplingPlotProperty_log | • | • | TABLE | |||
| SamplingPlotResource | • | • | • | • | TABLE | |
| SamplingPlotResource_log | TABLE | |||||
| SamplingProject | Diversity Workbench User | • | • | • | TABLE | |
| SamplingProject_log | • | • | TABLE | |||
| PlotID_UserAvailable | Diversity Workbench User | VIEW | ||||
| ProjectList | Diversity Workbench User | VIEW | ||||
| SamplingPlotGeoInfo | Diversity Workbench User | VIEW | ||||
| ViewBaseURL | Diversity Workbench User | VIEW | ||||
| ViewDiversityWorkbenchModule | Diversity Workbench User | VIEW | ||||
| ViewLocality | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlot | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotAllLocalisations | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotGeoInfo | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotHierarchy5 | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotList | Diversity Workbench User | VIEW | ||||
| ViewSamplingPlotListForCurrentProject | Diversity Workbench User | VIEW | ||||
| ViewTest | VIEW | |||||
| BaseURL | Diversity Workbench User | FUNCTION | ||||
| DefaultProjectID | Diversity Workbench User | FUNCTION | ||||
| DiversityWorkbenchModule | Diversity Workbench User | FUNCTION | ||||
| PrivacyConsentInfo | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotAllLocalisations | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotAllProperties | • | FUNCTION | ||||
| SamplingPlotChildNodes | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotGeography | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotHierarchy | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotList | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotListForCurrentProject | Diversity Workbench User | FUNCTION | ||||
| SamplingPlotSuperiorNodes | Diversity Workbench User | FUNCTION | ||||
| UserID | Diversity Workbench User | FUNCTION | ||||
| UserName | Diversity Workbench User | FUNCTION | ||||
| Version | Diversity Workbench User | FUNCTION | ||||
| VersionClient | Diversity Workbench User | FUNCTION | ||||
| procGetSiteProperties | PROCEDURE | |||||
| procInsertSamplingPlotCopy | • | PROCEDURE | ||||
| ProcSamplingPlotLocalisationAll | PROCEDURE | |||||
| SetUserProjects | PROCEDURE | |||||
| Inheriting from roles: |
- DiversityWorkbenchUser
Role DiversityWorkbenchUser
Role with read only access
| Permissions | SELECT | INSERT | UPDATE | DELETE | EXECUTE | Type |
|---|---|---|---|---|---|---|
| LocalisationSystem | • | TABLE | ||||
| LocalisationSystem_log | TABLE | |||||
| ProjectUser | • | TABLE | ||||
| Property | TABLE | |||||
| Property_log | TABLE | |||||
| ReplicationPublisher | TABLE | |||||
| SamplingPlot | • | TABLE | ||||
| SamplingPlot_log | TABLE | |||||
| SamplingPlotLocalisation | • | TABLE | ||||
| SamplingPlotLocalisation_log | TABLE | |||||
| SamplingPlotProperty | • | TABLE | ||||
| SamplingPlotProperty_log | TABLE | |||||
| SamplingPlotResource | TABLE | |||||
| SamplingPlotResource_log | TABLE | |||||
| SamplingProject | • | TABLE | ||||
| SamplingProject_log | TABLE | |||||
| PlotID_UserAvailable | • | VIEW | ||||
| ProjectList | • | VIEW | ||||
| SamplingPlotGeoInfo | • | VIEW | ||||
| ViewBaseURL | • | VIEW | ||||
| ViewDiversityWorkbenchModule | • | VIEW | ||||
| ViewLocality | • | VIEW | ||||
| ViewSamplingPlot | • | VIEW | ||||
| ViewSamplingPlotAllLocalisations | • | VIEW | ||||
| ViewSamplingPlotGeoInfo | • | VIEW | ||||
| ViewSamplingPlotHierarchy5 | • | VIEW | ||||
| ViewSamplingPlotList | • | VIEW | ||||
| ViewSamplingPlotListForCurrentProject | • | VIEW | ||||
| ViewTest | VIEW | |||||
| BaseURL | • | FUNCTION | ||||
| DefaultProjectID | • | FUNCTION | ||||
| DiversityWorkbenchModule | • | FUNCTION | ||||
| PrivacyConsentInfo | • | FUNCTION | ||||
| SamplingPlotAllLocalisations | • | FUNCTION | ||||
| SamplingPlotAllProperties | • | FUNCTION | ||||
| SamplingPlotChildNodes | • | FUNCTION | ||||
| SamplingPlotGeography | • | FUNCTION | ||||
| SamplingPlotHierarchy | • | FUNCTION | ||||
| SamplingPlotList | • | FUNCTION | ||||
| SamplingPlotListForCurrentProject | • | FUNCTION | ||||
| SamplingPlotSuperiorNodes | • | FUNCTION | ||||
| UserID | • | FUNCTION | ||||
| UserName | • | FUNCTION | ||||
| Version | • | FUNCTION | ||||
| VersionClient | • | FUNCTION | ||||
| procGetSiteProperties | PROCEDURE | |||||
| procInsertSamplingPlotCopy | PROCEDURE | |||||
| ProcSamplingPlotLocalisationAll | PROCEDURE | |||||
| SetUserProjects | PROCEDURE |