Diversity Collection Cache
TABLES
The following objects are not included:
- Logging tables
- Enumeration tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
Table Agent
The main table with the data of the agent
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
The basic URL as defined in the module database |
NO |
- |
AgentID |
int |
Unique ID for the Agent (= Primary key) |
NO |
- |
AgentURI |
varchar (255) |
The link to the dataset, i. e. the BaseURL + the AgentID |
YES |
- |
AgentParentID |
int |
The AgentID of the superior agent if agents are organized within a hierarchy |
YES |
- |
AgentName |
nvarchar (200) |
The whole name of the agent as shown e.g. for selection in an user interface. For persons this entry will be generated as follows: LastName, FirstNames, AgentTitle |
NO |
- |
AgentTitle |
nvarchar (50) |
The title of the agent, e.g. Dr., Prof. |
YES |
- |
GivenName |
nvarchar (255) |
The first names of the agent (if a person) or the name of e.g. an institution |
YES |
- |
GivenNamePostfix |
nvarchar (50) |
Variable part of name, correctly placed at end of given names |
YES |
- |
InheritedNamePrefix |
nvarchar (50) |
Variable part of name, correctly placed at the beginning of the inherited names |
YES |
- |
InheritedName |
nvarchar (255) |
The last names of the agent (if a person) |
YES |
- |
InheritedNamePostfix |
nvarchar (50) |
Additions after inherited name, like generation (Jr., III.) or names of religious orders |
YES |
- |
Abbreviation |
nvarchar (50) |
Abbreviation of the agent |
YES |
- |
AgentType |
nvarchar (50) |
The type of the agent, e.g. person, company |
YES |
- |
AgentRole |
nvarchar (255) |
The role of an agent esp. a person within an organization. e.g. “Database Administrator” or “Curator” |
YES |
- |
AgentGender |
nvarchar (50) |
The gender resp. sex of the agent |
YES |
- |
Description |
nvarchar (1000) |
A description of the agent |
YES |
- |
OriginalSpelling |
nvarchar (200) |
Name as originally written in e.g. chinese or cyrillic letters |
YES |
- |
Notes |
nvarchar (MAX) |
Notes about the agent |
YES |
- |
ValidFromDate |
datetime |
The date of the begin of the exsistence of the agent, e.g. the birthday of a person or the founding of an institution, calculated from ValidFromDay, - Month and -Year |
YES |
- |
ValidUntilDate |
datetime |
The date of the end of the exsistence of the agent, e.g. death of a person or closing of an institute, calculated from ValidUntilDay, - Month and -Year |
YES |
- |
SynonymToAgentID |
int |
The AgentID of the agent which was selected as a replacement for the current agent, e.g. if to equal datasets were imported from different sources |
YES |
- |
ProjectID |
int |
The ID of the project in the module database containing the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
SourceView |
nvarchar (200) |
The name of the source view of the data |
NO |
- |
The contact information resp. addresses of the agents
Column |
Data type |
Description |
Nullable |
Relation |
AgentID |
int |
Refers to the ID of Agent (= Foreign key and part of primary key) |
NO |
- |
DisplayOrder |
tinyint |
Display order of records in user interface. DisplayOrder 1 corresponds to the preferred address (= part of primary key) |
NO |
- |
AddressType |
nvarchar (50) |
Type of the adress, e.g. private |
YES |
- |
Country |
nvarchar (255) |
Country of the address |
YES |
- |
City |
nvarchar (255) |
City of the address |
YES |
- |
PostalCode |
nvarchar (50) |
ZIP or postcode of the address (usually output before or after the city) |
YES |
- |
Streetaddress |
nvarchar (255) |
Usually street name and number, but may also contain post office box |
YES |
- |
Address |
nvarchar (255) |
Free text postal address of the agent |
YES |
- |
Telephone |
nvarchar (50) |
Phone number, including area code |
YES |
- |
CellularPhone |
nvarchar (50) |
The number of a mobile telephone device of the agent |
YES |
- |
Telefax |
nvarchar (50) |
Fax number, including area code |
YES |
- |
Email |
nvarchar (255) |
E-mail address of the agent |
YES |
- |
URI |
nvarchar (255) |
URI pointing to a homepage containing further information |
YES |
- |
Notes |
nvarchar (MAX) |
Notes about this address |
YES |
- |
ValidFrom |
datetime |
The date when this address became valid as date according to ISO 8601 |
YES |
- |
ValidUntil |
datetime |
The date of the expiration of the validity of this address as date according to ISO 8601 |
YES |
- |
SourceView |
nvarchar (200) |
The name of the source view of the data |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table AgentIdentifier
Column |
Data type |
Description |
Nullable |
Relation |
AgentID |
int |
- |
NO |
- |
Identifier |
nvarchar (190) |
- |
NO |
- |
IdentifierURI |
varchar (500) |
- |
YES |
- |
Type |
nvarchar (50) |
- |
YES |
- |
Notes |
nvarchar (MAX) |
- |
YES |
- |
SourceView |
varchar (128) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table AgentImage
Column |
Data type |
Description |
Nullable |
Relation |
AgentID |
int |
- |
NO |
- |
URI |
varchar (255) |
- |
NO |
- |
Description |
nvarchar (MAX) |
- |
YES |
- |
Type |
nvarchar (50) |
- |
YES |
- |
Sequence |
int |
- |
YES |
- |
SourceView |
varchar (128) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table AgentSource
The sources for the data from a module database accessed via a view defined in the cache database
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
the name of the view retrieving the data from the database |
NO |
- |
Source |
nvarchar (500) |
The name of the source, e.g. the name of the project as defined in the source module |
YES |
- |
SourceID |
int |
The ID of the source, e.g. the ID of the project as defined in the source module |
YES |
- |
LinkedServerName |
nvarchar (500) |
If the source is located on a linked server, the name of the linked server |
YES |
- |
DatabaseName |
nvarchar (50) |
The name of the database where the data are taken from |
YES |
- |
Subsets |
nvarchar (500) |
Subsets of a source: The names of the tables included in the transfer separted by " |
" |
YES |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the source should be included in a schedule based data transfer |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the data |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Version |
int |
-Default value: (0) |
YES |
- |
Table AgentSourceTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
SourceView as defined in table AgentSource |
NO |
Refers to table AgentSource |
Target |
nvarchar (200) |
The targets of the projects, i.e. the Postgres databases where the data should be transferred to |
NO |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Table AgentSourceView
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
AgentID |
int |
- |
NO |
- |
SourceView |
nvarchar (128) |
The name of the source view of the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
Table bcpPostgresTableDefinition
Column |
Data type |
Description |
Nullable |
Relation |
SchemaName |
varchar (200) |
- |
NO |
- |
TableName |
varchar (200) |
- |
NO |
- |
ColumnName |
varchar (200) |
- |
NO |
- |
DataType |
varchar (50) |
- |
YES |
- |
OrdinalPositon |
int |
- |
YES |
- |
Table Gazetteer
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (255) |
- |
NO |
- |
NameID |
int |
- |
NO |
- |
Name |
nvarchar (400) |
- |
NO |
- |
LanguageCode |
nvarchar (50) |
- |
YES |
- |
PlaceID |
int |
- |
NO |
- |
PlaceType |
nvarchar (50) |
- |
YES |
- |
PreferredName |
nvarchar (400) |
- |
NO |
- |
PreferredNameID |
int |
- |
NO |
- |
PreferredNameLanguageCode |
nvarchar (50) |
- |
YES |
- |
ProjectID |
int |
- |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
SourceView |
nvarchar (200) |
The name of the source view of the data |
NO |
- |
NameURI |
varchar (255) |
- |
YES |
- |
ExternalNameID |
nvarchar (50) |
- |
YES |
- |
ExternalDatabaseID |
int |
- |
YES |
- |
Table GazetteerExternalDatabase
Column |
Data type |
Description |
Nullable |
Relation |
ExternalDatabaseID |
int |
- |
NO |
- |
ExternalDatabaseName |
nvarchar (60) |
- |
NO |
- |
ExternalDatabaseVersion |
nvarchar (255) |
- |
NO |
- |
ExternalAttribute_NameID |
nvarchar (255) |
- |
YES |
- |
ExternalAttribute_PlaceID |
nvarchar (255) |
- |
YES |
- |
ExternalCoordinatePrecision |
nvarchar (255) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table GazetteerSource
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
the name of the view retrieving the data from the database |
NO |
- |
Source |
nvarchar (500) |
- |
YES |
- |
SourceID |
int |
- |
YES |
- |
LinkedServerName |
nvarchar (500) |
If the source is located on a linked server, the name of the linked server |
YES |
- |
DatabaseName |
nvarchar (50) |
The name of the database where the data are taken from |
YES |
- |
Subsets |
nvarchar (500) |
Subsets of a source: The names of the tables included in the transfer separted by " |
" |
YES |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the source should be included in a schedule based data transfer |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the data |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Version |
int |
-Default value: (0) |
YES |
- |
Table GazetteerSourceTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
SourceView as defined in table GazetteerSource |
NO |
Refers to table GazetteerSource |
Target |
nvarchar (200) |
The targets of the projects, i.e. the Postgres databases where the data should be transferred to |
NO |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Table GazetteerSourceView
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
NameID |
int |
- |
NO |
- |
SourceView |
nvarchar (128) |
The name of the source view of the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
Table ProjectPublished
The projects published via the cache database (Details about the projects are defined in DiversityProjects)
Column |
Data type |
Description |
Nullable |
Relation |
ProjectID |
int |
ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
NO |
- |
Project |
nvarchar (50) |
The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) |
YES |
- |
CoordinatePrecision |
tinyint |
Optional reduction of the precision of the coordinates within the project |
YES |
- |
ProjectURI |
varchar (255) |
The URI of the project, e.g. as provided by the module DiversityProjects. |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
LastUpdatedBy |
nvarchar (50) |
The user reponsible for the last update. |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transfer |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Restriction |
nvarchar (MAX) |
An additional restriction of the content of the published data |
YES |
- |
Table ProjectTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
ProjectID |
int |
ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
NO |
Refers to table ProjectPublished |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
TargetID |
int |
The ID of the server, relates to table Target |
NO |
Refers to table Target |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
UseBulkTransfer |
bit |
If the bulk transfer should be used for the transfer of data |
YES |
- |
Table ProjectTargetPackage
Packages for projects as documented in the table Package in the Postgres database
Column |
Data type |
Description |
Nullable |
Relation |
ProjectID |
int |
Refers to ProjectID in table ProjectTarget |
NO |
Refers to table ProjectTarget |
TargetID |
int |
Referes to TargetID in table ProjectTarget |
NO |
Refers to table ProjectTarget |
Package |
nvarchar (50) |
Package installed for this project target |
NO |
- |
Table ProjectTargetPackageAddOn
The installed add-ons for packages
Column |
Data type |
Description |
Nullable |
Relation |
ProjectID |
int |
Refers to ProjectID in table ProjectTarget |
NO |
Refers to table ProjectTargetPackage |
TargetID |
int |
Referes to TargetID in table ProjectTarget |
NO |
Refers to table ProjectTargetPackage |
Package |
nvarchar (50) |
Package installed for this project target |
NO |
Refers to table ProjectTargetPackage |
AddOn |
nvarchar (50) |
Add-on installed for this package |
NO |
- |
Table ProjectTransfer
The transfers of data of a project
Column |
Data type |
Description |
Nullable |
Relation |
ProjectID |
int |
ID of the project, part of PK |
NO |
Refers to table ProjectPublished |
TransferDate |
datetime |
Date of the transfer. Part of PKDefault value: getdate() |
NO |
- |
ResponsibleUserID |
int |
The ID of the user as stored in table UserProxy of the source database, responsible for the transferDefault value: (-1) |
YES |
- |
TargetID |
int |
If the transfer regards a postgres database, the ID of the target (= Postgres database) as stored in table Target |
YES |
Refers to table Target |
Package |
nvarchar (50) |
If the transfer regards a package, the name of the package, otherwise empty |
YES |
- |
Settings |
nvarchar (MAX) |
The versions, number of transfered data etc. of the objects concerned by the transfer [format: JSON] |
YES |
- |
Table ReferenceRelator
Column |
Data type |
Description |
Nullable |
Relation |
RefID |
int |
- |
NO |
- |
Role |
nvarchar (3) |
- |
NO |
- |
Sequence |
int |
- |
NO |
- |
Name |
nvarchar (255) |
- |
NO |
- |
AgentURI |
varchar (255) |
- |
YES |
- |
SortLabel |
nvarchar (255) |
- |
YES |
- |
Address |
nvarchar (1000) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table ReferenceTitle
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
RefType |
nvarchar (10) |
- |
NO |
- |
RefID |
int |
- |
NO |
- |
RefDescription_Cache |
nvarchar (255) |
- |
NO |
- |
Title |
nvarchar (4000) |
- |
NO |
- |
DateYear |
smallint |
- |
YES |
- |
DateMonth |
smallint |
- |
YES |
- |
DateDay |
smallint |
- |
YES |
- |
DateSuppl |
nvarchar (255) |
- |
NO |
- |
SourceTitle |
nvarchar (4000) |
- |
NO |
- |
SeriesTitle |
nvarchar (255) |
- |
NO |
- |
Periodical |
nvarchar (255) |
- |
NO |
- |
Volume |
nvarchar (255) |
- |
NO |
- |
Issue |
nvarchar (255) |
- |
NO |
- |
Pages |
nvarchar (255) |
- |
NO |
- |
Publisher |
nvarchar (255) |
- |
NO |
- |
PublPlace |
nvarchar (255) |
- |
NO |
- |
Edition |
smallint |
- |
YES |
- |
DateYear2 |
smallint |
- |
YES |
- |
DateMonth2 |
smallint |
- |
YES |
- |
DateDay2 |
smallint |
- |
YES |
- |
DateSuppl2 |
nvarchar (255) |
- |
NO |
- |
ISSN_ISBN |
nvarchar (18) |
- |
NO |
- |
Miscellaneous1 |
nvarchar (255) |
- |
NO |
- |
Miscellaneous2 |
nvarchar (255) |
- |
NO |
- |
Miscellaneous3 |
nvarchar (255) |
- |
NO |
- |
UserDef1 |
nvarchar (4000) |
- |
NO |
- |
UserDef2 |
nvarchar (4000) |
- |
NO |
- |
UserDef3 |
nvarchar (4000) |
- |
NO |
- |
UserDef4 |
nvarchar (4000) |
- |
NO |
- |
UserDef5 |
nvarchar (4000) |
- |
NO |
- |
WebLinks |
nvarchar (4000) |
- |
NO |
- |
LinkToPDF |
nvarchar (4000) |
- |
NO |
- |
LinkToFullText |
nvarchar (4000) |
- |
NO |
- |
RelatedLinks |
nvarchar (4000) |
- |
NO |
- |
LinkToImages |
nvarchar (4000) |
- |
NO |
- |
SourceRefID |
int |
- |
YES |
- |
Language |
nvarchar (25) |
- |
NO |
- |
CitationText |
nvarchar (1000) |
- |
NO |
- |
CitationFrom |
nvarchar (255) |
- |
NO |
- |
LogInsertedWhen |
datetime |
-Default value: getdate() |
YES |
- |
ProjectID |
int |
- |
NO |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
ReferenceURI |
varchar (255) |
- |
YES |
- |
AuthorsCache |
nvarchar (1000) |
- |
YES |
- |
Table ReferenceTitleSource
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
the name of the view retrieving the data from the database |
NO |
- |
Source |
nvarchar (500) |
- |
YES |
- |
SourceID |
int |
- |
YES |
- |
LinkedServerName |
nvarchar (500) |
If the source is located on a linked server, the name of the linked server |
YES |
- |
DatabaseName |
nvarchar (50) |
The name of the database where the data are taken from |
YES |
- |
Subsets |
nvarchar (500) |
Subsets of a source: The names of the tables included in the transfer separted by " |
" |
YES |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the source should be included in a schedule based data transfer |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the data |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Version |
int |
-Default value: (0) |
YES |
- |
Table ReferenceTitleSourceTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
SourceView as defined in table ReferenceSource |
NO |
Refers to table ReferenceTitleSource |
Target |
nvarchar (200) |
The targets of the projects, i.e. the Postgres databases where the data should be transferred to |
NO |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Table ReferenceTitleSourceView
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
RefID |
int |
- |
NO |
- |
SourceView |
nvarchar (128) |
The name of the source view of the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
Table SamplingPlot
Holds cached data from DiversitySamplingPlots as base for other procedures.
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
PlotID |
int |
- |
NO |
- |
PartOfPlotID |
int |
- |
YES |
- |
PlotURI |
nvarchar (255) |
- |
YES |
- |
PlotIdentifier |
nvarchar (500) |
- |
YES |
- |
PlotGeography_Cache |
nvarchar (MAX) |
- |
YES |
- |
PlotDescription |
nvarchar (MAX) |
- |
YES |
- |
PlotType |
nvarchar (50) |
- |
YES |
- |
CountryCache |
nvarchar (50) |
- |
YES |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
ProjectID |
int |
- |
YES |
- |
SourceView |
nvarchar (200) |
The source of the data,i.e. the name of the view in the database |
NO |
- |
Table SamplingPlotLocalisation
Column |
Data type |
Description |
Nullable |
Relation |
PlotID |
int |
- |
NO |
- |
LocalisationSystemID |
int |
- |
NO |
- |
Location1 |
nvarchar (255) |
- |
YES |
- |
Location2 |
nvarchar (255) |
- |
YES |
- |
LocationAccuracy |
nvarchar (50) |
- |
YES |
- |
LocationNotes |
nvarchar (MAX) |
- |
YES |
- |
Geography |
nvarchar (MAX) |
- |
YES |
- |
AverageAltitudeCache |
float |
- |
YES |
- |
AverageLatitudeCache |
float |
- |
YES |
- |
AverageLongitudeCache |
float |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table SamplingPlotProperty
Column |
Data type |
Description |
Nullable |
Relation |
PlotID |
int |
- |
NO |
- |
PropertyID |
int |
- |
NO |
- |
DisplayText |
nvarchar (255) |
- |
YES |
- |
PropertyURI |
varchar (255) |
- |
YES |
- |
PropertyHierarchyCache |
nvarchar (MAX) |
- |
YES |
- |
PropertyValue |
nvarchar (255) |
- |
YES |
- |
Notes |
nvarchar (MAX) |
- |
YES |
- |
AverageValueCache |
float |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table SamplingPlotSource
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
the name of the view retrieving the data from the database |
NO |
- |
Source |
nvarchar (50) |
- |
YES |
- |
SourceID |
int |
- |
YES |
- |
LinkedServerName |
nvarchar (400) |
If the source is located on a linked server, the name of the linked server |
YES |
- |
DatabaseName |
nvarchar (400) |
The name of the database where the data are taken from |
YES |
- |
Subsets |
nvarchar (500) |
List of additional data transferred into the cache database separated by |
|
YES |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the source should be included in a schedule based data transfer |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the dataDefault value: getdate() |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Version |
int |
-Default value: (0) |
YES |
- |
Table SamplingPlotSourceTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
SourceView as defined in table SamplingPlotSource |
NO |
Refers to table SamplingPlotSource |
Target |
nvarchar (200) |
The targets of the projects, i.e. the Postgres databases where the data should be transferred to |
NO |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project dataDefault value: getdate() |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Table SamplingPlotSourceView
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
PlotID |
int |
- |
NO |
- |
SourceView |
nvarchar (128) |
The name of the source view of the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
Table ScientificTerm
Holds cached data from DiversityScientificTerms as base for other procedures.
Column |
Data type |
Description |
Nullable |
Relation |
RepresentationURI |
nvarchar (255) |
- |
NO |
- |
DisplayText |
nvarchar (255) |
- |
YES |
- |
HierarchyCache |
varchar (900) |
- |
YES |
- |
HierarchyCacheDown |
nvarchar (900) |
- |
YES |
- |
RankingTerm |
nvarchar (200) |
- |
YES |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
SourceView |
nvarchar (200) |
The source of the data,i.e. the name of the view in the database |
NO |
- |
RepresentationID |
int |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table ScientificTermSource
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
the name of the view retrieving the data from the database |
NO |
- |
Source |
nvarchar (50) |
- |
YES |
- |
SourceID |
int |
- |
YES |
- |
LinkedServerName |
nvarchar (400) |
If the source is located on a linked server, the name of the linked server |
YES |
- |
DatabaseName |
nvarchar (400) |
The name of the database where the data are taken from |
YES |
- |
Subsets |
nvarchar (500) |
List of additional data transferred into the cache database separated by |
|
YES |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the source should be included in a schedule based data transfer |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the data |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Version |
int |
-Default value: (0) |
YES |
- |
Table ScientificTermSourceTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
SourceView as defined in table ScientificTermSource |
NO |
Refers to table ScientificTermSource |
Target |
nvarchar (200) |
The targets of the projects, i.e. the Postgres databases where the data should be transferred to |
NO |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Table ScientificTermSourceView
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
RepresentationID |
int |
- |
NO |
- |
SourceView |
nvarchar (128) |
The name of the source view of the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
Table SourceTransfer
The transfers of data of a source
Column |
Data type |
Description |
Nullable |
Relation |
Source |
nvarchar (50) |
The type of the source, e.g. Taxa, part of PK |
NO |
- |
SourceView |
nvarchar (200) |
The name of the main view for the data defined for retrieving the data from the source. Part of PK |
NO |
- |
TransferDate |
datetime |
Date of the transfer, part of PKDefault value: getdate() |
NO |
- |
ResponsibleUserID |
int |
The ID of the user as stored in table UserProxy of the source database, responsible for the transferDefault value: (-1) |
YES |
- |
TargetID |
int |
If the transfer regards a postgres database, the ID of the target (= Postgres database) as stored in table Target |
YES |
Refers to table Target |
Settings |
nvarchar (MAX) |
The versions, number of transfered data etc. of the objects concerned by the transfer [format: JSON] |
YES |
- |
Table Target
The postgres databases as targets for the data
Column |
Data type |
Description |
Nullable |
Relation |
TargetID |
int |
ID of the target on a postgres server, PK |
NO |
- |
Server |
nvarchar (255) |
Name or IP of the Server |
NO |
- |
Port |
smallint |
Port for accessing the server |
NO |
- |
DatabaseName |
nvarchar (255) |
The name of the database |
NO |
- |
TransferDirectory |
varchar (500) |
Directory on the Postgres server used for the transfer of data |
YES |
- |
BashFile |
varchar (500) |
BashFile on the Postgres server used for conversion of the data |
YES |
- |
MountPoint |
varchar (50) |
Mount point name of the transfer folder |
YES |
- |
Table TaxonAnalysis
Column |
Data type |
Description |
Nullable |
Relation |
NameID |
int |
- |
NO |
- |
ProjectID |
int |
- |
NO |
Refers to table TaxonList |
AnalysisID |
int |
- |
NO |
Refers to table TaxonAnalysisCategory |
AnalysisValue |
nvarchar (MAX) |
- |
YES |
- |
Notes |
nvarchar (MAX) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonAnalysisCategory
Column |
Data type |
Description |
Nullable |
Relation |
AnalysisID |
int |
- |
NO |
- |
AnalysisParentID |
int |
- |
YES |
- |
DisplayText |
nvarchar (50) |
- |
YES |
- |
Description |
nvarchar (MAX) |
- |
YES |
- |
AnalysisURI |
varchar (255) |
- |
YES |
- |
ReferenceTitle |
nvarchar (600) |
- |
YES |
- |
ReferenceURI |
varchar (255) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
Refers to table TaxonSynonymySource |
SortingID |
int |
- |
YES |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonAnalysisCategoryValue
Column |
Data type |
Description |
Nullable |
Relation |
AnalysisID |
int |
- |
NO |
Refers to table TaxonAnalysisCategory |
AnalysisValue |
nvarchar (255) |
- |
NO |
- |
Description |
nvarchar (500) |
- |
YES |
- |
DisplayText |
nvarchar (50) |
- |
YES |
- |
DisplayOrder |
smallint |
- |
YES |
- |
Notes |
nvarchar (500) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonCommonName
Column |
Data type |
Description |
Nullable |
Relation |
NameID |
int |
- |
NO |
- |
CommonName |
nvarchar (300) |
- |
NO |
- |
LanguageCode |
varchar (2) |
- |
NO |
- |
CountryCode |
varchar (2) |
- |
NO |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonList
Column |
Data type |
Description |
Nullable |
Relation |
ProjectID |
int |
- |
NO |
- |
Project |
nvarchar (50) |
- |
NO |
- |
DisplayText |
nvarchar (50) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
Refers to table TaxonSynonymySource |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonNameExternalDatabase
Column |
Data type |
Description |
Nullable |
Relation |
ExternalDatabaseID |
int |
- |
NO |
- |
ExternalDatabaseName |
nvarchar (800) |
- |
YES |
- |
ExternalDatabaseVersion |
nvarchar (255) |
- |
YES |
- |
Rights |
nvarchar (500) |
- |
YES |
- |
ExternalDatabaseAuthors |
nvarchar (200) |
- |
YES |
- |
ExternalDatabaseURI |
nvarchar (300) |
- |
YES |
- |
ExternalDatabaseInstitution |
nvarchar (300) |
- |
YES |
- |
ExternalAttribute_NameID |
nvarchar (255) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
Refers to table TaxonSynonymySource |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonNameExternalID
Column |
Data type |
Description |
Nullable |
Relation |
NameID |
int |
- |
NO |
- |
ExternalDatabaseID |
int |
- |
NO |
- |
ExternalNameURI |
varchar (255) |
- |
YES |
- |
SourceView |
nvarchar (200) |
- |
NO |
- |
BaseURL |
varchar (500) |
- |
NO |
- |
Table TaxonSynonymy
Holds cached data from DiversityTaxonNames as base for other procedures.
Column |
Data type |
Description |
Nullable |
Relation |
NameID |
int |
- |
NO |
- |
BaseURL |
varchar (255) |
- |
NO |
- |
TaxonName |
nvarchar (255) |
- |
YES |
- |
AcceptedNameID |
int |
- |
YES |
- |
AcceptedName |
nvarchar (255) |
- |
YES |
- |
TaxonomicRank |
nvarchar (50) |
- |
YES |
- |
SpeciesGenusNameID |
int |
- |
YES |
- |
GenusOrSupragenericName |
nvarchar (200) |
- |
YES |
- |
NameParentID |
int |
- |
YES |
- |
TaxonNameSinAuthor |
nvarchar (2000) |
- |
YES |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |
ProjectID |
int |
- |
YES |
- |
AcceptedNameSinAuthor |
nvarchar (2000) |
- |
YES |
- |
NameURI |
varchar (255) |
- |
YES |
- |
SourceView |
nvarchar (200) |
The source of the data, e.g. the name of the database |
NO |
Refers to table TaxonSynonymySource |
Table TaxonSynonymySource
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
- |
NO |
- |
Source |
nvarchar (500) |
- |
YES |
- |
SourceID |
int |
- |
YES |
- |
LinkedServerName |
nvarchar (500) |
If the source is located on a linked server, the name of the linked server |
YES |
- |
DatabaseName |
nvarchar (50) |
- |
YES |
- |
Subsets |
nvarchar (500) |
List of additional data transferred into the cache database separated by |
|
YES |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the source should be included in a schedule based data transfer |
YES |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the data |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: ‘0’ |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Version |
int |
-Default value: (0) |
YES |
- |
Table TaxonSynonymySourceTarget
The targets of the projects, i.e. the Postgres databases
Column |
Data type |
Description |
Nullable |
Relation |
SourceView |
nvarchar (200) |
SourceView as defined in table TaxonSynonymySource |
NO |
Refers to table TaxonSynonymySource |
Target |
nvarchar (200) |
The targets of the projects, i.e. the Postgres databases where the data should be transferred to |
NO |
- |
LastUpdatedWhen |
datetime |
The date of the last update of the project data |
YES |
- |
TransferProtocol |
nvarchar (MAX) |
The protocol created during the transfer of the data |
YES |
- |
IncludeInTransfer |
bit |
If the project should be included in a schedule based data transferDefault value: (1) |
YES |
- |
CompareLogDate |
bit |
If the log dates of the transferred data should be compared to decide if data are transferredDefault value: (0) |
YES |
- |
TransferDays |
varchar (7) |
The days the transfer should be done, coded as integer values with Sunday = 0 up to Saturday = 6Default value: (0) |
YES |
- |
TransferTime |
time |
The time when the transfer should be executedDefault value: ‘00:00:00.00’ |
YES |
- |
TransferIsExecutedBy |
nvarchar (500) |
If any transfer of the data is active |
YES |
- |
TransferErrors |
nvarchar (MAX) |
Errors that occurred during the data transfers |
YES |
- |
LastCheckedWhen |
datetime |
The date and time when the last check for the need of an update of the content occurred |
YES |
- |
Table TaxonSynonymySourceView
Column |
Data type |
Description |
Nullable |
Relation |
BaseURL |
varchar (500) |
- |
NO |
- |
NameID |
int |
- |
NO |
- |
SourceView |
nvarchar (128) |
The name of the source view of the data |
NO |
- |
LogInsertedWhen |
smalldatetime |
Date and time when record was first entered (typed or imported) into this system.Default value: getdate() |
YES |
- |