Annotation Database
The database is a complex piece of software with many working parts.
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.
Database Design
The database is relational and normalized which maximizes data integrity while minimizing duplication. The database 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 back end using Django, the Django models do not manage the table structure directly: the database is intended to be framework-agnostic, so the Django models must be manually updated to agree with the database structure.
Helpful Tools
A few tools that can be useful for navigating the database:
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. If it succeeds, the upgrade can be applied permanently. 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.