Annotation Database: Difference between revisions
(Style upgrades.) |
(Style updates.) |
||
| Line 1: | Line 1: | ||
= Database Software = | |||
The database runs on [https://www.postgresql.org/ PostgreSQL] with the [https://postgis.net/ PostGIS] spatial extension. The database and Web server ([https://www.nginx.com/ Nginx]) are hosted on a cloud-based server running the [https://www.debian.org/ Debian Linux] operating system. | The database runs on [https://www.postgresql.org/ PostgreSQL] with the [https://postgis.net/ PostGIS] spatial extension. The database and Web server ([https://www.nginx.com/ Nginx]) are hosted on a cloud-based server running the [https://www.debian.org/ 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 [https://www.visual-paradigm.com/ Visual Paradigm] software, but is now updated by the incremental application of [https://gitlab.com/rskelly/msea-rov-db/-/tree/main/model/ddl DDL] by a script, [https://gitlab.com/rskelly/msea-rov-db/-/blob/main/model/db_manager.py db_manager.py] (see below). The database was developed alongside the Website backend using Django but the Django models do not [https://docs.djangoproject.com/en/4.2/ref/models/options/#managed 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. | The database is relational and normalized which maximizes data integrity while minimizing duplication. The database was originally designed using the industry-standard [https://www.visual-paradigm.com/ Visual Paradigm] software, but is now updated by the incremental application of [https://gitlab.com/rskelly/msea-rov-db/-/tree/main/model/ddl DDL] by a script, [https://gitlab.com/rskelly/msea-rov-db/-/blob/main/model/db_manager.py db_manager.py] (see below). The database was developed alongside the Website backend using Django but the Django models do not [https://docs.djangoproject.com/en/4.2/ref/models/options/#managed 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. | ||
| Line 11: | Line 9: | ||
Full database documentation, generated from the production database, can be found here: [[Database 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 the database: | A few tools that can be useful for navigating the database: | ||
| Line 17: | Line 15: | ||
* [https://dbeaver.io/ DBeaver] | * [https://dbeaver.io/ DBeaver] | ||
= Database Upgrades = | |||
As above, the database is upgraded by running a script, which applies the necessary DDL files. | As above, the database is upgraded by running a script, which applies the necessary DDL files. | ||
Revision as of 00:24, 10 November 2023
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 backend using Django but 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.
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 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 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.