Cache Database Infrastructure

Infrastructure within 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. 

Central tables

There are a number of tables placed in the schema dbo that are accessible by all projects. 

Project tables

The central 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.

• ProjectPublished
• ProjectTarget
• ProjectTargetPackage
• ProjectTransfer
• Target

Source tables

To access sources from other modules (e.g. DiversityAgents) 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 DiversityAgents, but there are corresponding tables for every module accessed by the cache database. 

• AgentSource
• AgentSourceTarget

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 DiversityAgents, but there are corresponding tables for every module accessed by the cache database. 

• Agent
• AgentContactInformation

To access the data in the module there are views generated by the client. The name of these views are composed according to the name of the database, the server and the project to ensure a unique name. These are stored in the table AgentSource 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 views for the module DiversityAgents. 

• Agents_BSMcollectors
• Agents_BSMcollectors_C

Project tables

These tables contain the data of the projects with every project having its own schema. These tables correspond to the tables in the main database of the module with certain limitations (no logging columns, internal notes etc.)

• CacheAnalysis
• CacheAnnotation
• CacheCollection
• CacheCollectionAgent
• CacheCollectionEvent
• CacheCollectionEventLocalisation
• CacheCollectionEventProperty
• CacheCollectionExternalDatasource
• CacheCollectionSpecimen
• CacheCollectionSpecimenImage
• CacheCollectionSpecimenPart
• CacheCollectionSpecimenProcessing
• CacheCollectionSpecimenReference
• CacheCollectionSpecimenRelation
• CacheExternalIdentifier
• CacheIdentification
• CacheIdentificationUnit
• CacheIdentificationUnitAnalysis
• CacheIdentificationUnitGeoAnalysis
• CacheIdentificationUnitInPart
• CacheLocalisationSystem
• CacheMetadata
• CacheProcessing
• CacheProjectAgent
• CacheProjectAgentRole
• CacheProjectReference
• ProjectAnalysis
• ProjectMaterialCategory
• ProjectTaxonomicGroup

Project procedures for the data transfer into the project tables

For every project table there is a procedure that transfers the data from the main database into the cache table. The names of these procedures are procPublish + the name of the table in the main database e.g. procPublishAnalysis for the transfer from the table Analysis into the table CacheAnalysis.