Annotation Database

Software

The database runs on PostgreSQL with the PostGIS spatial extension. The database and Web server (Nginx) are hosted on a cloud-based server running the Debian Linux operating system.

Organisation

There are two databases on the server, msea and msea_stage. The former is the production database. The latter is a copy of the production database used for staging changes and upgrades: once the upgrades are applied and tested successfully, they can be applied to the production database.

This wiki resides on the production database in the wiki schema.

Design

The database is relational and normalized which maximizes data integrity while minimizing duplication. It was originally designed using the industry-standard Visual Paradigm software, but is now updated by the incremental application of DDL by a script, db_manager.py (see below). The database was developed alongside the Website backend using Django but the Django models do not manage the table structure directly in the usual way: the database is intended to be framework-agnostic, so the Django models must be manually updated to agree with the database structure. Nevertheless, it is sometimes necessary to apply migrations using the Django manager.

Since the database is inherently spatial, the PostGIS spatial extension is installed and all spatial entities are stored using PostGIS geometry columns with the spherical geography type, which handles spatial representation and calculations on the spheroid better than the standard Cartesian geometry type.

Connecting

There are a variety of scripts for accessing, importing and managing data, in addition to the Website itself, which is based on Django. All of these load connection parameters from configuration files in the configs directory in the user's home directory, whether on Windows or Linux. Typically, the configuration files are accessed by a path similar to ~/configs/db_prod.conf, were ~ is the standard shorthand for the user's home directory and db_prod.conf is the file containing connection parameters for the production database's non-administrative user. A standard set of files in the configs might look like:

• ~/configs/db_prod.conf -- The production database non-administrative user. Used by the Website to access data.
• ~/configs/db_prod_admin.conf -- The production database administrative user. Used by db_manager.py and other scripts that can change the structure of the database or its contents.
• ~/configs/db_stage.conf -- Similar to db_prod.conf but used for the staging database.
• ~/configs/db_stage_admin.conf -- Similar to db_prod_admin.conf but used for the staging database.
• ~/configs/db_dev.conf -- Used on the developer's local machine for access to a development database.

The configuration files will contain colon-delimited pairs of strings on each line:

   host:localhost
   port:5432
   dbname:[database name]
   username:[user name]
   password:[password]

The database name will be one of msea_dev for the local development database (though the developer can choose any name); msea for the production database; or msea_stage for the staging database.

Upgrades

As above, the database is upgraded by running a script, which applies the necessary DDL files.

With no switches, the script will perform a dry run, applying the DDL to the database but not committing the changes. If it succeeds, the upgrade can be applied permanently by using the -c switch. The script checks a table to determine the current database version and applies upgrades from that version forward (the versions are encoded in the DDL file names). If the version is given as an argument, upgrades are applied only up to that version (inclusive). The final argument to the script is the path to a configuration file which contains colon-delimited database connection parameters.

An example invocation of the script for a dry run is,

   ./db_manager.py ~/configs/db_prod_admin.conf

To apply the changes permanently, one would invoke,

  ./db_manager.py -c -p ~/configs/db_prod_admin.conf

In both of these invocations, the configuration file is for the production database with admin permissions (which are required to alter the structure). The -c flag commits the changes and the -p flag applies the correct permissions to any created entities.

Backups

A cron job runs a backup on the production database every night, saving a compressed dump to a designated folder. The backup is actually performed by the db_maintenance.sh script, which is installed by the database install script, install.sh. More information can be found on the Deployment page.

Entity Documentation

Full database documentation, generated from the production database, can be found here: Database Entity Documentation.

Helpful Database Tools

A few tools that can be useful for navigating and visualising the database:

• pgAdmin
• DBeaver