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 several databases on the server:

msea stores the annotation database.
specimen stores the specimen database.
pmls stores the PMLS database.
intertidal stores the intertidal database.

This wiki resides in the wiki schema on in the msea but should be moved in the future.

Annotation Database Deployment and Upgrades

The annotation database definition consists of a baseline schema dump and a series of versioned upgrade files. The version information is stored in the table public.db_version.

The db_manager.py script in the model folder applies the necessary upgrades. The usage is

   python db_manage.py [-p] [-c] <config file>

The -p switch applies permissions to any new entities (schemas, tables, views, etc.) and the -c switch commits the changes. Without -c the script performs a "dry run" to confirm that the updates will complete successfully before permanent upgrades are applied.

The script will read the version table and only apply scripts after the last upgrade version. The version is parsed from the filename itself, which will be in the form, db_<major version>_<minor version>_<patch>_<sequence>_<description>.sql, where:

major version is the major version of the database.
minor version is the minor version.
patch is the patch version.
sequence sequence is a number that determines the order in which files for a specific version are applied.
description is a very short string giving a hint about what's in the file. An example of such a file would be:

   db_2_1_11_0_status_detail_name.sql

Where the version is 2.1.11; this file would be applied first in a sequence of files with the same version; and the purpose of the file has something to do with the status_detail table.

The upgrade script is in the model folder, and the version files are in model/ddl.

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:

Annotation Database

Contents