Infrastructure for the postgres database

At the PostgreSQL server the tables are either placed in the schema public or a schema named according to the published project. In general the tables in postgres have the same strucure as in the Microsoft SQL cache database, so the transfer to postgres is mainly a pure copy of their contents. Only some data types are subtituted by their equivalents in postgres. Therefore you may refer to the cache database documentation concerning these tables. Some additional tables, views or even schemas and appropriate transfer functions may be introduced by the packages and their add-ons.

In schema public the support functions diversityworkbenchmodule and version provide the module name "DiversityDescriptionsCache" and the postgres database version (e.g. "01.00.01") as strings to support processing and the database update. In the published project schemas the support functions projectid and version provide the corresponding integer values.   

All schemas, functions, tables and views are owned by role CacheAdmin. The additional role CacheUser grants read access to all elements rsp. execution rights to the support functions.

Source tables

The data tables of the source modules (e.g. DiversityReferences) are transfered as copies to the schema public. I.e. the access tables ending with "Source", "SourceTarget" and "SourceView", which serve administrative purposes, are omitted. In the example for the module DiversityReferences, shown in the database infrastructure page, only the tables ReferenceTitle and ReferenceRelator are tranferred to postgres.

Project tables

The project tables in postgres are simple copies of the cache database tables. They are placed in a schema named according to the published project, e.g. Project_Test for a project with the name "Test". For details refer to the cache database documentation:

• CacheCharacter
  
• CacheCharacterTree
  
• CacheCharacterTreeNode
  
• CacheDescription
  
• CacheItem
  
• CacheMetadata
  
• CacheScope
  
• CacheState
  
• CacheTranslation
  

The view ProjectLanguage provides access to the ProjectLanguageCode stored in table CacheMetadata. The view TranlationLanguage provides access to the LanguageCode of available translations stored in table CacheTranslation.  

Packages and Add-Ons

The formatting of the data according to the specifications of webservices etc. is done with packages. The packages are realized as tables, views, functions etc. reading the data in the tables without any changes to the data. They therefore can be inserted and removed without any effects on the original data. The naming of the objects within a package follow the schema [Name of the package]_... . Each package provides a database command file to create the required objects. Transfer of data is done by calling dedicated transfer functions.

For certain packages there are add-ons available to adapt a package to the special specifications of e.g. a project. Each add-on provides a database command file to create the required objects. The traditional way of realizing an add-on is to modify some transfer functions of the package. Therefore only one of those add-ons can be installed for a package. A more flexible approach of handling add-ons is to build a dedicated add-on transfer function and store its name in the administration table. For package transfer there a transger step is defined that reads all associated add-ons from the database and calls their transfer functions in a defined order. With this approach insertion of multiple compatible add-ons may be realized.   

 For the administration of packages and add-ons four tables are used in the postgres database:

• Package
  
• PackageAddOn
  
• PackageLockedCharacter
  
• PackagePublicTable
  
• PackageSchema
  

In table Package each installed package, its version and description are stored. After data transfer to a package transfer date and time are stored there, too. Add-on data like its version are stored in table PackageAddOn. Some add-ons may require a parameter that has to be selected during installation and ist stored in table column Parameter. If the "non-traditional" approach of realizing add-ons using dedicated transfer functions is used, this table provides optional support columns.

The table PackagePublicTable offers support, if a package or add-on needs to provide data in the schema public. A traditional way to realize this feature is to mark the package as "using schema public". As a consequence the package can only be created in one project of the whole database. When the package is removed, all objects in schemad public with the prefix "[Name of the package]_" are dropped.

An alternative is that a package or add-on inserts the public table name in PackagePublicTable. Furthermore the public table must have a column where the source schema name is included. If the package is removed, all entries of the public table with matching package name and source schema will be deleted. If the public table is empty, the table itself will be dropped. An example is package NaviKey that enters all published schemas in the table NaviKey_Schema. This table is used by the REST service to provide data to the application DiversityNaviKey.  

The table PackageSchema offers support, if a package or add-on needs to provide data in dependent schemas. For example the add-on NaviKey_Translations provides access to all available translated data of the package NaviKey. Therefore for each available language code a dependent schema named [language code]_Project_[Name of the project] (e.g. de_Project_LIASlight for the german translation) is created with views to the data in the master schema. Each dependent schema is inserted in table PackageSchema (and the public table NaviKey_Schema for the REST service). When the package is removed, all dependent schemas will be dropped, too.

Available Packages and Add-Ons

Currently the following packages and add-ons are available:

Tables for Packages and Add-Ons