Diversity Exsiccatae
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 Exsiccata
An Exsiccata is a series of specimen issued in a publication like form.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExsiccataID | int | ID of the exsiccata (primary key) | NO | - |
| Version | int | The version of the datasetDefault value: (1) | YES | - |
| ExsAbbreviation | nvarchar (255) | Std. abbreviation (authors/editors, std. abbrev. of title) | NO | - |
| ExsTitle | nvarchar (255) | Exact title (create a new entry if title spelling changed considerably, link entries using PrecededBy/SupersededBy) | NO | - |
| EditingInstitution | nvarchar (255) | Issuing/Editing institution (If no editors are known, at least this must be entered) | YES | - |
| EditingLocationOri | nvarchar (255) | Original spelling of city/country where the exsiccata has been issued | YES | - |
| EditingLocationCurrent | nvarchar (255) | Current spelling of city/country where the exsiccata has been issued | YES | - |
| EditingLocationEnglish | nvarchar (255) | Current English spelling of city/country where the exsiccata has been issued | YES | - |
| PrecededBy | int | An earlier exsiccata publication appeared under different title or editors (Reference to entry in this database) | YES | Refers to table Exsiccata |
| ExsNumberFirst | nvarchar (20) | If known: number of the first exsiccata that was issued by the current editors under the current title | YES | - |
| ExsNumberLast | nvarchar (20) | If known: number of the last exssiccata that was issued by the current editors under the current title | YES | - |
| ExsPublYearFirst | nvarchar (20) | If known: publication year of the first exsiccata that was issued by the current editors under the current title | YES | - |
| ExsPublYearLast | nvarchar (80) | If known: publication year of the last exsiccata that was issued by the current editors under the current title | YES | - |
| Notes | nvarchar (2000) | Annotation, please add your name and a date for each annotation | YES | - |
| Problems | nvarchar (MAX) | Please explain here if you have a problem, this field will later be checked | YES | - |
| Fungi | smallint | check if exsiccata covers fungal species (default) 1 or -1= Yes it does, 0=No it does not, Null/empty = don’t know | YES | - |
| Algae | smallint | check if exsiccata covers Algae/Cyanophyta | YES | - |
| Bryophytes | smallint | check if exsiccata covers mosses/bryophytes | YES | - |
| Pteridophytes | smallint | check if exsiccata covers pteridophytes | YES | - |
| HigherPlants | smallint | check if exsiccata covers higher plants | YES | - |
| Zoocecidia | smallint | check if exsiccata covers galls on plants caused by animals | YES | - |
| Educational | smallint | Educational purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don’t know | YES | - |
| Scientific | smallint | Scientific purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don’t know | YES | - |
| GroupSpecific | smallint | Exsiccata is restricted to a taxonomic entity or ecologically delimited group, e.g. family or lichens on leaves, 1 or -1= Yes, 0=No, Null/empty = don’t know | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
| Bacteria | smallint | check if exsiccata covers pteridophytes | YES | - |
trgInsExsiccata
set last changes in ProjectProxy
Table ExsiccataEditor
The editors that published an exsiccatal series.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExsiccataID | int | Reference to ExsiccataID in Exsiccata (foreign Key) | NO | Refers to table Exsiccata |
| Name | nvarchar (255) | Enter as last name, comma, first names, use blanks after comma and period | NO | - |
| EditorAgentURI | varchar (255) | The URI of the Agent, e.g. as stored within the module DiversityAgents | YES | - |
| Sequence | datetime | (automatically set during entry, to maintain the sequence of editors)Default value: getdate() | NO | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
Depending on:
- Exsiccata
trgInsExsiccataEditor
setting the version in the main table
Table ExsiccataExamples
Examples for an exsiccatal series.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExsiccataID | int | Reference to ExsiccataID in Exsiccata (foreign Key) | NO | Refers to table Exsiccata |
| ExampleID | int | A number that defines the sequence in which the examples have been added. | NO | - |
| ImageFile | nvarchar (255) | Example: Path and file name for an image of the label of the exsiccata example issue | YES | - |
| ResourceURI | varchar (255) | Optionally: The URI under which the image is recoded in e.g. DiversityResources. | YES | - |
| PublishedName | nvarchar (255) | Example: Published taxon name of example issue | YES | - |
| CollectionName | nvarchar (255) | Example: Name or Acronym of collection where the example issue is available. Default is M for München | YES | - |
| Number | nvarchar (50) | Example: Number of example issue within the exsiccata series | YES | - |
| StorageLocation | nvarchar (255) | Example: Deposited under (scientific name indicating a storage location) | YES | - |
| CollectionSpecimen | varchar (255) | Optionally: The Display text of the example specimen as recorded in e.g. DiversityCollection | YES | - |
| CollectionSpecimenURI | varchar (255) | Optionally: The URI under which the example specimen is recorded in e.g. DiversityCollection | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
Depending on:
- Exsiccata
trgInsExsiccataExamples
setting the version in the main table
Table ExsiccataReference
The references where an exsiccatal series is listed.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExsiccataID | int | Reference to ExsiccataID in Exsiccata (foreign Key) | NO | Refers to table Exsiccata |
| ReferenceTitle | nvarchar (255) | The title of the publication where information on the exiccata was published. Note this is only a cached value where ReferenceURI is present | NO | - |
| ReferenceURI | varchar (255) | URI (e.g. LSID) of reference where information on the exsiccata was published, e.g. referring to the module DiversityReferences | YES | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
Depending on:
- Exsiccata
trgInsExsiccataReference
setting the version in the main table
Table ExsiccataRegion
The geographic regions that are covered by the specimen within an exsiccatal series.
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExsiccataID | int | Reference to ExsiccataID in Exsiccata (foreign Key) | NO | Refers to table Exsiccata |
| Region | nvarchar (50) | A region where the specimens within the exsiccata were collected. | NO | - |
| LogUpdatedWhen | datetime | The last time when this dataset was updatedDefault value: getdate() | YES | - |
| LogUpdatedBy | nvarchar (50) | ID of the person to update this data set lastDefault value: [dbo].UserID | YES | - |
Depending on:
- Exsiccata
trgInsExsiccataRegion
setting the version in the main table
VIEWS
View View_Coverage
Coverage and purpose columns of table Exsiccata
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ExsiccataID | int | ID of the exsiccata (primary key) | NO |
| Coverage | varchar (27) | Combined coverage columns of table Exsiccata | NO |
| Purpose | varchar (11) | Combined Purpose columns of table Exsiccata | NO |
Depending on:
- Exsiccata
View ViewBaseURL
Content of function BaseURL() for linked server
| Column | Data type | Description | Nullable |
|---|---|---|---|
| BaseURL | varchar (255) | Content of function BaseURL() | YES |
Depending on:
- BaseURL
View ViewDiversityWorkbenchModule
Content of function DiversityWorkbenchModule() for linked server
| Column | Data type | Description | Nullable |
|---|---|---|---|
| DiversityWorkbenchModule | nvarchar (50) | Content of function DiversityWorkbenchModule() | YES |
Depending on:
- DiversityWorkbenchModule
FUNCTIONS
Function BaseURL
Returns the basic URL for the database
DataType: varchar (255)
Function DiversityWorkbenchModule
Name of the module
DataType: nvarchar (50)
Function PrivacyConsentInfo
Provides a link to common information about the DiversityWorkbench
DataType: varchar (900)
Function StableIdentifier
The stable identifier published for a exsiccata
DataType: varchar (500)
| Parameter | DataType | Description |
|---|---|---|
| @ExsiccataID | int | ID of the exsiccata |
Depending on:
- Exsiccata
- StableIdentifierBase
Function StableIdentifierBase
DataType: varchar (255)
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) | The ID of the user, Primary key |
Depending on:
- UserProxy
Function Version
Version of the database
DataType: nvarchar (8)
Function VersionClient
Minimal version of the client compatible with the database
DataType: nvarchar (11)
PROCEDURES
Procedure procCopyExsiccata
Copy a exsiccata
| Parameter | DataType | Description |
|---|---|---|
| @ExsiccataID | int | ID of the new exsiccata |
| @OriginalExsiccataID | int | ID of the original exsiccata |
| @ExsTitle | nvarchar (255) | Title of the new exsiccata |
| @ExsAbbreviation | nvarchar (255) | Abbreviation of the new exsiccata |
Depending on:
- Exsiccata
- ExsiccataEditor
- ExsiccataExamples
- ExsiccataReference
- ExsiccataRegion
Procedure procSetVersionExsiccata
Setting the version of a dataset
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | ID of the exsiccata |
Depending on:
- Exsiccata
- ProjectProxy
- UserProxy
ROLES
Role DiversityWorkbenchEditor
Role with permission to edit the data
| Permissions | SELECT | INSERT | UPDATE | DELETE | EXECUTE | Type |
|---|---|---|---|---|---|---|
| Exsiccata | • | • | TABLE | |||
| Exsiccata_log | TABLE | |||||
| ExsiccataEditor | • | • | • | TABLE | ||
| ExsiccataEditor_log | TABLE | |||||
| ExsiccataExamples | • | • | • | TABLE | ||
| ExsiccataExamples_log | TABLE | |||||
| ExsiccataReference | • | • | • | TABLE | ||
| ExsiccataReference_log | TABLE | |||||
| ExsiccataRegion | • | • | • | TABLE | ||
| ExsiccataRegion_log | TABLE | |||||
| View_Coverage | VIEW | |||||
| ViewBaseURL | VIEW | |||||
| ViewDiversityWorkbenchModule | VIEW | |||||
| BaseURL | FUNCTION | |||||
| DiversityWorkbenchModule | FUNCTION | |||||
| PrivacyConsentInfo | FUNCTION | |||||
| StableIdentifier | FUNCTION | |||||
| StableIdentifierBase | FUNCTION | |||||
| UserID | FUNCTION | |||||
| UserName | FUNCTION | |||||
| Version | FUNCTION | |||||
| VersionClient | FUNCTION | |||||
| procCopyExsiccata | • | PROCEDURE | ||||
| procSetVersionExsiccata | • | PROCEDURE |
Role DiversityWorkbenchUser
Role restricted to ReadOnly
| Permissions | SELECT | INSERT | UPDATE | DELETE | EXECUTE | Type |
|---|---|---|---|---|---|---|
| Exsiccata | • | TABLE | ||||
| Exsiccata_log | TABLE | |||||
| ExsiccataEditor | • | TABLE | ||||
| ExsiccataEditor_log | TABLE | |||||
| ExsiccataExamples | • | TABLE | ||||
| ExsiccataExamples_log | TABLE | |||||
| ExsiccataReference | • | TABLE | ||||
| ExsiccataReference_log | TABLE | |||||
| ExsiccataRegion | • | TABLE | ||||
| ExsiccataRegion_log | TABLE | |||||
| View_Coverage | • | VIEW | ||||
| ViewBaseURL | • | VIEW | ||||
| ViewDiversityWorkbenchModule | • | VIEW | ||||
| BaseURL | • | FUNCTION | ||||
| DiversityWorkbenchModule | • | FUNCTION | ||||
| PrivacyConsentInfo | • | FUNCTION | ||||
| StableIdentifier | • | FUNCTION | ||||
| StableIdentifierBase | • | FUNCTION | ||||
| UserID | • | FUNCTION | ||||
| UserName | • | FUNCTION | ||||
| Version | • | FUNCTION | ||||
| VersionClient | • | FUNCTION | ||||
| procCopyExsiccata | PROCEDURE | |||||
| procSetVersionExsiccata | PROCEDURE |