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.

Restricted Access to Entities

Some entities, such as Abalone observations, should be hidden from the public. The database includes a system of group-based user access restrictions which apply to every entity in the rov.event table.

Restrictions are configured in the shared.restriction table, which provides a pattern and key column. The key is the property within the event record that should be matched by the pattern. The pattern is a string suitable for use with the SQL ILIKE operator, which is a case-insensitive substring match. The ILIKE operator accepts the wildcard character %, which matches any string. For example, the pattern, "abalone" matches the string, "abalone", exactly, while the pattern, "%abalone%" will match any string with the word "abalone" in it. For example, "This is an abalone!" matches the latter pattern, but not the former.

Restrictions are defined through the label mapping tools, which add entries to the shared.restrictiontable . This table is linked to public.auth_group through the shared.restriction_group table, to create a link between the restriction and the user. The rov.event table is therefore linked to each user via the restriction configuration. If no restriction matches a given event, it is open to all users.

The restrictions are slow to compute through these joins, so they are compiled into the rov.event_group table by the procedure compile_event_restrictions. When a user queries the rov.event table, the permissions are determined by calling the get_restricted_events(integer) function, which determines the accessibility of a list of events by the given user ID. Publicly-accessible queries should use this function rather than querying rov.event directly. The Django Event model is modified to require it.

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