Cache Database Infrastructure
Infrastructure for the cache database
For the administration of the data that are published via the cache database, certain tables as shown below are used. These are either placed in the schema dbo or a schema named according to the published project, e.g. Project_Test for a project with the name “Test”. Additionally some basic data are stored in dedicated tables of the main DiversityDescriptions database.
Tables in the main database
In the main DiversityDescriptions database there is a number of tables holding the cache database name, information about datawithholding and mapping information of the IDs. This information is needed to restore the cache database in case of loss. For the database diagram take a look at the database section of this manual.
Central tables in the cache database
There are a number of tables placed in the schema dbo that are accessible by all projects.
Published project tables
The central published project tables contain the information about the projects that are published together with the target (Postgres) databases and the packages including optional add-ons into which they had been transferred. This information is used to ensure a recovery in case of a loss of the targets.
Source tables
To access sources from other modules (e.g. DiversityReferences) there are tables for the storage of the principal access to the modules and a number of tables containing the data (depending on the module).
Access tables
These tables contain the principal access like the name of the view defined to access the data. The example below lists the tables defined for the module DiversityReferences, but there are corresponding tables for every module accessed by the cache database.
Data tables
These tables contain the data provided by the module and therefore depend on the module. The example below lists the tables defined for the module DiversityReferences, but there are corresponding tables for every module accessed by the cache database. Since data from various source databases may be acumulated in the cache database, in general all the data tables include the BaseURL as part of their keys to ensure unambiguousness.
To access the data in the source database for the module views are generated by the client. The name of these views are composed according to the name of the database and the project to ensure a unique name. Furthermore letters are appended to identify subordinated tables. These are stored in the table “<module>Source” and are used by the client for a transfer of the data from the module database into the tables in the cache database. The example below lists the view names for the module DiversityReferences. In this example the source database “DiversityReferences_Test” and project “DALI” result in the main table name. By appending “_R” the view name for subordinated table “ReferenceRelator” is built. This gives the views References_Test_DALI and References_Test_DALI_R.
Project tables in the cache database
These tables contain the data of the projects with every project having its own schema. The tables correspond to the tables in the main database of the module with according the following assignment. In the third columns the views of the cache database are listed to access the DiversityDescriptions data. The view access besides the main tables listed in the second table column and the ID mapping tables. For the summary data (CacheDescription) additionally subordinated tables, e.g. Modifier, are accessed to resolve relations in DiversityDescriptions as simple character strings.
| Table in cache database | Tables in DiversityDescriptions | Views in cache database |
| CacheCharacter | Descriptor | ViewCacheCharacter |
| CacheCharacterTree | DescriptorTree | ViewCacheCharacterTree |
| CacheCharacterTreeNode | DescriptorTreeNode | ViewCacheCharacterTreeNode |
| CacheDescription | CategoricalSummaryData, QuantitativeSummaryData, TextDescriptorData, MolecularSequenceData, DescriptorStatusData |
ViewCacheCategorical, ViewCacheQuantitative, ViewCacheText, ViewCacheSequence, ViewCacheStatusData |
| CacheItem | Description | ViewCacheItem |
| CacheResource | Resource, ResourceVariant |
ViewCacheResourceCharacter, ViewCacheResourceCharacterTreeNode, ViewCacheResourceItem, ViewCacheResourceState |
| CacheScope | DescriptionScope | ViewCacheScope |
| CacheState | CategoricalState | ViewCacheState, ViewCacheMeasure |
| CacheTranslation | Translation | ViewCacheTranslationCharacter, ViewCacheTranslationState, ViewCacheTranslationItem |
Besides the tables mentioned above, the auxilliary tables ProjectLockedDescriptor and ProjectLockedScope contain the descriptor IDs and scope types that shall be excluded from the transfer to the cache database. The auxilliary tables ProjectPublishedTranslation and ProjectPublishedDescriptorTree contain language codes of translations (columns label, wording and detail of source tables Descriptor, CategoricalState and Description) and the descriptor tree IDs that shall be included in the cache database transfer. Together with the extended query parameter, which are stored in the columns FilterCommand and FilterParameter of the table ProjectPublished, they build the transferrestrictions of the cache database. Finally, in table CacheMetadata some data from the DiversityProjects database are stored.
- CacheCharacter
- CacheCharacterTree
- CacheCharacterTreeNode
- CacheDescription
- CacheItem
- CacheMetadata
- CacheResource
- CacheScope
- CacheState
- CacheTranslation
- ProjectLockedDescriptor
- ProjectLockedScope
- ProjectPublishedDescriptorTree
- ProjectPublishedTranslation
The main tables CacheItem, CacheCharacter and CacheState have a numeric key (ItemID, CharID and StateID), which is identical to the unique key in the main database. However, in the cache database the main adress attributes are IID, CID and CS. CID and SD are in principle the descriptor and categorical state sequence numbers, where the mapping algorith guarantees unique ascending values. In this adressing schema a single state is identified by the combination of CID and CS.
Additionally in table CacheState the recommended statistical measures of quantitative descriptors are inclueded, where the measure code (e.g. “Min”, “Max” or “Mean”) is inserted in CS. In table CacheDescription, which holds the single descriptor or data status values, the CID and CS are specified for a specific categorical state. For quantitative data in CS the measurement type is identified by the measure code. For text and molecular sequence data CS is supplied with the fixed texts “TE” and “MS”. In case of descriptor status data CS is set NULL. Instead the data status code is inserted in column Status.
Project procedures for the data transfer into the project tables
For every project table there is a set of procedures that transfers the data from the main database into the cache table. The names of these procedures are procPublish + the name of the target table without “Cache” e.g. procPublishCharacter for the transfer of the data into the table CacheCharacter. The first steps of the data transfer perform an update the ID mapping tables in the main database. This is done in the procedures procPublishMappingItem, procPublishCharacter and procPublishState, which call dedicated procedures in the DiversityDescriptions database. As mentioned above, the original IDs (ItemID, CharID and StateID) are stored together resulting mapped IDs (IID, CID and CS) in the cache database tables. To view the mapping information, the views CacheMappingItem, CacheMappingCharacter and CacheMappingState select the appropriate values from the cache tables.
List of tables mentioned above
Table ProjectPublished
The projects published via the cache database (Details about the projects are defined in DiversityProjects)
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO |
| Project | nvarchar (255) | The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) | YES |
| ProjectURI | varchar (255) | The URI of the project, e.g. as provided by the module DiversityProjects. | YES |
| FilterCommand | varchar (MAX) | The SQL command to select description IDs to be transferred to the cache database. | YES |
| FilterDescription | nvarchar (MAX) | The XML description of the filter command to transferred description IDs. | YES |
| CoordinatePrecision | tinyint | Optional reduction of the precision of the coordinates within the project | YES |
| LastUpdatedWhen | datetime | The date of the last update of the project
data Default value: getdate() |
YES |
| LastUpdatedBy | nvarchar (50) | The user reponsible for the last
update. Default value: suser_sname() |
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 transferred Default value: (0) |
YES |
| TransferDays | varchar (7) | The days the transfer should be done, coded
as integer values with Sunday = 0 up to Saturday = 6 Default value: '0' |
YES |
| TransferTime | time | The time when the transfer should be
executed Default 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 |
| TranslationDescription | bit | If the description item translation should be
included in the data transfer Default value: (1) |
NO |
| TranslationDescriptor | bit | If the descriptor translation should be
included in the data transfer Default value: (1) |
NO |
| TranslationCategoricalState | bit | If the categorical state translation should
be included in the data transfer Default value: (1) |
NO |
| OmitLockedDescriptors | bit | If the locked descriptors and their states
shall be omitted in the data transfer Default value: (0) |
NO |
| ResourceDescription | bit | If the description item resources should be
included in the data transfer Default value: (0) |
NO |
| ResourceDescriptorTreeNode | bit | If the descriptor tree node resources should
be included in the data transfer Default value: (0) |
NO |
| ResourceDescriptor | bit | If the descriptor resources should be
included in the data transfer Default value: (0) |
NO |
| ResourceCategoricalState | bit | If the categorical state resources should be
included in the data transfer Default value: (0) |
NO |
| ResourceTypeAll | bit | If all resource types should be included in
the data transfer Default value: (0) |
NO |
| ResourceTypeImage | bit | If the resource type "image" should be
included in the data transfer Default value: (0) |
NO |
| ResourceTypeColor | bit | If the resource type "color" should be
included in the data transfer Default value: (0) |
NO |
| ResourceRankingRestriction | tinyint | The minimum resource ranking (0-10) that
should be included in the data transfer Default value: (0) |
NO |
| ResourceRoleRestriction | varchar (255) | The resource roles that should be included in
the data transfer Default value: NULL |
YES |
Table ProjectTarget
The targets of the projects, i.e. the Postgres databases
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | 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 transfer Default value: (1) |
YES |
| CompareLogDate | bit | If the log dates of the transferred data
should be compared to decide if data are transferred Default value: (0) |
YES |
| TransferDays | varchar (7) | The days the transfer should be done, coded
as integer values with Sunday = 0 up to Saturday = 6 Default value: (0) |
YES |
| TransferTime | time | The time when the transfer should be
executed Default 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 |
Table ProjectTargetPackage
Packages for projects as documented in the table Package in the Postgres database
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ProjectID | int | Refers to ProjectID in table ProjectTarget | NO |
| TargetID | int | Referes to TargetID in table ProjectTarget | NO |
| Package | nvarchar (50) | Package installed for this project target | NO |
Table ProjectTransfer
The transfers of data of a project
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ProjectID | int | ID of the project, part of PK | NO |
| TransferDate | datetime | Date of the transfer. Part of PK Default value: getdate() |
NO |
| ResponsibleUserID | int | The ID of the user as stored in table
UserProxy of the source database, responsible for the transfer Default 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 |
| 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 |
|---|---|---|---|
| BaseURL | varchar (500) | - | NO |
| 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 (128) | - | NO |
Table ReferenceTitle
| Column | Data type | Description | Nullable |
|---|---|---|---|
| BaseURL | varchar (500) | - | NO |
| RefType | nvarchar (10) | - | NO |
| RefID | int | - | NO |
| ProjectID | int | - | YES |
| 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 |
| ReplaceWithRefID | int | - | YES |
| CitationText | nvarchar (1000) | - | NO |
| CitationFrom | nvarchar (255) | - | NO |
| LogInsertedWhen | smalldatetime | - Default value: getdate() |
YES |
| SourceView | nvarchar (128) | - | NO |
| ReferenceURI | varchar (500) | - | YES |
| AuthorsCache | varchar (1000) | - | YES |
Table ReferenceTitleSource
| Column | Data type | Description | Nullable |
|---|---|---|---|
| SourceView | nvarchar (128) | 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 |
| CompareLogDate | bit | If the log dates of the transferred data
should be compared to decide if data are transferred Default value: (0) |
YES |
| TransferDays | varchar (7) | The days the transfer should be done, coded
as integer values with Sunday = 0 up to Saturday = 6 Default value: '0' |
YES |
| TransferTime | time | The time when the transfer should be
executed Default 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 |
| LastUpdatedWhen | datetime | The date of the last update of the data Default value: getdate() |
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 |
|---|---|---|---|
| SourceView | nvarchar (128) | SourceView as defined in table ReferenceSource | NO |
| Target | nvarchar (255) | 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 Default 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 transfer Default value: (1) |
YES |
| CompareLogDate | bit | If the log dates of the transferred data
should be compared to decide if data are transferred Default value: (0) |
YES |
| TransferDays | varchar (7) | The days the transfer should be done, coded
as integer values with Sunday = 0 up to Saturday = 6 Default value: (0) |
YES |
| TransferTime | time | The time when the transfer should be
executed Default 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 |
|---|---|---|---|
| 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 Target
The postgres databases as targets for the data
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TargetID | int | ID of the target on a postgres server, PK | NO |
| Server | nvarchar (255) | Name of IP of the Server | NO |
| Port | smallint | Port for accessing the server | NO |
| DatabaseName | nvarchar (255) | The name of the database | NO |
Table CacheCharacter
Character (= descriptors, features) define variables
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CharID | int | Database-internal ID of this record (primary key) | NO |
| CID | smallint | A positive number defining the sequence in which characters are displayed | NO |
| CharName | nvarchar (255) | Short label (or name) of character | NO |
| Notes | nvarchar (MAX) | Additional detail text explaining or commenting on the character definition | YES |
| CharWording | nvarchar (255) | Optional separate wording for natural
language generation (CharName will be used if this is missing) Default value: NULL |
YES |
| Unit | nvarchar (255) | A measurement unit (mm, inch, kg, °C, m/s
etc.) or dimensionless scaling factor Default value: NULL |
YES |
| UnitIsPrefix | tinyint | Set to 1 if the measurement unit precedes the
value Default value: '0' |
NO |
| Subclass | nvarchar (255) | The four character subclasses of SDD are all
combined here in one entity and distinguished by this attribute
("categorical", "quantitative", "text" or "sequence") Default value: 'categorical' |
NO |
| Mandatory | tinyint | Is the scoring of this descriptor mandatory
(required) in each item? Default value: '0' |
NO |
| Exclusive | tinyint | Applicable to categorical charactors only. If
usually exclusive = 1, then by default the user interface allows only
entering one state. Nevertheless, multiple states in the data are
valid. Default value: '0' |
NO |
| ValuesAreInteger | tinyint | Set to 1 if the values are integer Default value: '0' |
NO |
| Reliability | tinyint | How reliable and consistent are repeated
measurements or scorings of the character by different observers and on
different objects? (0-10) Default value: '5' |
NO |
| Availability | tinyint | How available is the character or concept for
identification? (0-10) Default value: '5' |
NO |
| SequenceType | nvarchar (255) | Type of molecular sequence, "Nucleotide" or
"Protein". The value "Nucleotide" covers RNA and DNA sequences Default value: 'Nucleotide' |
NO |
| SymbolLength | tinyint | The number of letters in each symbol.
Nucleotides are always codes with 1-letter symbols, but proteins may use
1 or 3-letter codes (e.g. A or Ala for alanine) Default value: '1' |
NO |
| GapSymbol | nvarchar (3) | A string identifying the "gap" symbol used in
aligned sequences. The gap symbol must always be symbol_length
long Default value: NULL |
YES |
| NumStates | smallint | A positive number specifying the number of
states for this character Default value: '0' |
NO |
| StateCollection | nvarchar (255) | Handling of multiple values: OrSet/AndSet:
unordered set combined with or/and, OrSeq/AndSeq: ordered sequence
combined with or/and, WithSeq: example is “green with brown”, Between:
an example is “oval to elliptic” Default value: 'OrSet' |
NO |
| Order | int | Display order Default value: (0) |
NO |
Table CacheCharacterTree
The descriptor trees
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CharTreeID | int | Database-internal ID of this record (primary key) | NO |
| CharTreeName | nvarchar (255) | Descriptor tree name | NO |
Table CacheCharacterTreeNode
The character tree nodes
| Column | Data type | Description | Nullable |
|---|---|---|---|
| CharTreeNodeID | int | Database-internal ID of this record (primary key) | NO |
| CharTreeID | int | Reference to the character tree to which the node belongs (foreign key) | NO |
| ParentTreeNodeID | int | Reference to the superior character tree node
- NULL if the tree node is on top level (foreign key) Default value: NULL |
YES |
| CharTreeNodeName | nvarchar (255) | Character tree node name - NULL if node
references a character Default value: NULL |
YES |
| CID | smallint | Reference to the character to which the node
belongs - NULL if the tree node is no leaf (foreign key) Default value: NULL |
YES |
| Order | int | Display order Default value: (0) |
NO |
Table CacheDescription
The description data in the database
| Column | Data type | Description | Nullable |
|---|---|---|---|
| DescrID | int | Database-internal ID of this record (primary key) | NO |
| IID | int | Reference to the description item to which the data belong | NO |
| CID | smallint | Reference to the character to which the data belong | NO |
| CS | varchar (18) | Reference to the state to which the data
belong. Null if data status is specified Default value: NULL |
YES |
| Status | varchar (16) | Data status of the character as 16 letter
code. Null if CS is specified Default value: NULL |
YES |
| Modifier | nvarchar (255) | Modifier value of description item. Relevant
for categorical and quantitative charaters Default value: NULL |
YES |
| Frequency | nvarchar (255) | Frequency value of description item. Relevant
for categorical charaters Default value: NULL |
YES |
| X | float | Numeric value of description item. Relevant for quantitative charaters | YES |
| TXT | nvarchar (MAX) | Text value of description item. Relevant for text and molecular sequence charaters | YES |
| Notes | nvarchar (MAX) | Additional text explaining or commenting on the description item. Relevant for all charaters | YES |
Table CacheItem
The description item in the database
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ItemID | int | Database-internal ID of this record (primary key) | NO |
| IID | int | A positive number defining the sequence in which items are displayed | NO |
| ItemName | nvarchar (255) | Short label (or name) of description item | NO |
| Notes | nvarchar (MAX) | Additional detail text explaining or commenting on the description item definition | YES |
| ItemWording | nvarchar (255) | Optional separate wording for natural
language generation (ItemName will be used if this is missing) Default value: NULL |
YES |
Table CacheMetadata
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ProjectID | int | - | NO |
| ProjectTitle | nvarchar (400) | - | YES |
| ProjectTitleCode | nvarchar (254) | - | YES |
| StableIdentifier | nvarchar (500) | - | YES |
| TechnicalContactName | nvarchar (254) | - | YES |
| TechnicalContactEmail | nvarchar (254) | - | YES |
| TechnicalContactPhone | nvarchar (254) | - | YES |
| TechnicalContactAddress | nvarchar (254) | - | YES |
| ContentContactName | nvarchar (254) | - | YES |
| ContentContactEmail | nvarchar (254) | - | YES |
| ContentContactPhone | nvarchar (254) | - | YES |
| ContentContactAddress | nvarchar (254) | - | YES |
| OtherProviderUDDI | nvarchar (254) | - | YES |
| DatasetTitle | nvarchar (254) | - | YES |
| DatasetDetails | nvarchar (MAX) | - | YES |
| DatasetCoverage | nvarchar (254) | - | YES |
| DatasetURI | nvarchar (254) | - | YES |
| DatasetIconURI | nvarchar (254) | - | YES |
| DatasetVersionMajor | nvarchar (254) | - | YES |
| DatasetCreators | nvarchar (254) | - | YES |
| DatasetContributors | nvarchar (254) | - | YES |
| DatasetGUID | nvarchar (254) | - | YES |
| DateCreated | nvarchar (254) | - | YES |
| DateModified | nvarchar (254) | - | YES |
| SourceID | nvarchar (254) | - | YES |
| SourceInstitutionID | nvarchar (254) | - | YES |
| OwnerOrganizationName | nvarchar (254) | - | YES |
| OwnerOrganizationAbbrev | nvarchar (254) | - | YES |
| OwnerContactPerson | nvarchar (254) | - | YES |
| OwnerContactRole | nvarchar (254) | - | YES |
| OwnerAddress | nvarchar (254) | - | YES |
| OwnerTelephone | nvarchar (254) | - | YES |
| OwnerEmail | nvarchar (254) | - | YES |
| OwnerURI | nvarchar (254) | - | YES |
| OwnerLogoURI | nvarchar (254) | - | YES |
| IPRText | nvarchar (254) | - | YES |
| IPRDetails | nvarchar (254) | - | YES |
| IPRURI | nvarchar (254) | - | YES |
| CopyrightText | nvarchar (254) | - | YES |
| CopyrightDetails | nvarchar (254) | - | YES |
| CopyrightURI | nvarchar (254) | - | YES |
| TermsOfUseText | nvarchar (500) | - | YES |
| TermsOfUseDetails | nvarchar (254) | - | YES |
| TermsOfUseURI | nvarchar (254) | - | YES |
| DisclaimersText | nvarchar (254) | - | YES |
| DisclaimersDetails | nvarchar (254) | - | YES |
| DisclaimersURI | nvarchar (254) | - | YES |
| LicenseText | nvarchar (254) | - | YES |
| LicensesDetails | nvarchar (254) | - | YES |
| LicenseURI | nvarchar (254) | - | YES |
| AcknowledgementsText | nvarchar (254) | - | YES |
| AcknowledgementsDetails | nvarchar (254) | - | YES |
| AcknowledgementsURI | nvarchar (254) | - | YES |
| CitationsText | nvarchar (254) | - | YES |
| CitationsDetails | nvarchar (254) | - | YES |
| CitationsURI | nvarchar (254) | - | YES |
| RecordBasis | nvarchar (254) | - | YES |
| KindOfUnit | nvarchar (254) | - | YES |
| HigherTaxonRank | nvarchar (254) | - | YES |
| TaxonomicGroup | nvarchar (254) | - | YES |
| BaseURL | varchar (254) | - | YES |
| RecordURI | nvarchar (500) | - | YES |
| ProjectLanguageCode | nvarchar (3) | - | YES |
Table CacheResource
The available resources
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ResourceID | int | Database-internal ID of this record (primary key) | NO |
| EntityID | int | Database-internal ID of the referenced record | NO |
| TargetTable | nvarchar (255) | Name of the target table: "State", "Chararcter", "CharacterTreeNode" or "Item" (primary key) | NO |
| ResourceName | nvarchar (255) | Short label (or name) of resource | NO |
| Ranking | smallint | Ranking of the resource; range: 0 to 10 Default value: NULL |
YES |
| Role | nvarchar (255) | Role of the resource ("unknown"=role not known or not specified; "diagnostic"=optimized for identification; "iconic"=icon/thumbnail, needs text; "normative"=defines a resource object; "primary"=display always, informative without text; "secondary"=display only on request) | NO |
| IPRText | nvarchar (255) | The license text of the resource Default value: NULL |
YES |
| IPRURI | nvarchar (255) | The license URI of the resource Default value: NULL |
YES |
| MimeType | nvarchar (255) | The type of the resource as MIME type like
"image/jpeg"; color as "color/hexrgb" Default value: NULL |
YES |
| URL | nvarchar (500) | The URL of the resource Default value: NULL |
YES |
| Order | int | Display order Default value: (0) |
NO |
Table CacheScope
The scope of the description
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ScopeId | int | Database-internal ID of this record (primary key) | NO |
| IID | int | Reference to the description item to which these data belong | NO |
| ScopeType | nvarchar (255) | Scope type ("GeographicArea", "Citation", "Observation", "Specimen", "TaxonName", "OtherConcept", "Stage", "Part" or "Sex") | NO |
| ScopeName | nvarchar (255) | Short label (or name) of scope | NO |
| DwbURI | nvarchar (500) | Reference to DiversityWorkbench
component Default value: NULL |
YES |
Table CacheState
The states available for characters
| Column | Data type | Description | Nullable |
|---|---|---|---|
| StateID | int | Database-internal ID of this record (primary key) | NO |
| CID | smallint | Reference to the character to which the state belongs (foreign key) | NO |
| CS | varchar (18) | A short string identifying the states in relation to its character | NO |
| StateName | nvarchar (255) | Short label (or name) of state | NO |
| Notes | nvarchar (MAX) | Additional detail text explaining or commenting on the state definition | YES |
| StateWording | nvarchar (255) | Optional separate wording for natural
language generation (StateName will be used if this is missing) Default value: NULL |
YES |
| MinValue | float | Applicable to quantitative characters only;
in support of a plausibility check for values. Example: for tree height
this could be 0, i.e. only positive values allowed Default value: '-1e308' |
NO |
| MaxValue | float | Applicable to quantitative characters only;
in support of a plausibility check for values. Example: for tree height
this could be 99 Default value: '1e308' |
NO |
| Order | int | Display order Default value: (0) |
NO |
Table CacheTranslation
The available translations
| Column | Data type | Description | Nullable |
|---|---|---|---|
| TranslationID | int | Database-internal ID of this record (primary key) | NO |
| EntityID | int | Database-internal ID of the referenced record | NO |
| LanguageCode | nvarchar (3) | Three-character language code of the translation | NO |
| SourceColumn | nvarchar (255) | Name of the original table column | NO |
| TargetTable | nvarchar (255) | Name of the target table: “State”, “Character” or “Item” | NO |
| Text | nvarchar (MAX) | Translated text | YES |
Table ProjectLockedDescriptor
The descriptors (=characters) that shall not be published
| Column | Data type | Description | Nullable |
|---|---|---|---|
| DescriptorID | int | Database-internal descriptor ID of descriptive data that shall no be published (primary key) | NO |
Table ProjectLockedScope
The scope types that shall not be published
| Column | Data type | Description | Nullable |
|---|---|---|---|
| ScopeType | nvarchar (255) | Scope types that shall not be pulished (primary key) | NO |
Table ProjectPublishedDescriptorTree
The descriptor tree IDs that shall be published
| Column | Data type | Description | Nullable |
|---|---|---|---|
| DescriptorTreeID | int | IDs of descriptor trees that shall be published (primary key) | NO |
Table ProjectPublishedTranslation
The translation languages that shall be published
| Column | Data type | Description | Nullable |
|---|---|---|---|
| LanguageCode | nvarchar (3) | Three-letter language codes of translations that shall be published (primary key) | NO |