Overview

Perseid provides utilities to maintain a PostgreSQL database schema. Like its ancestor Pyrseas, its purpose is to enhance and follow through on the concepts of the Andromeda Project. Note that in this context, and in general in this document, a schema refers to a logical definition of the objects (tables, functions, etc.) in a database, not to the SQL SCHEMA (also known in PostgreSQL as a namespace).

Whereas Andromeda expected the database designer, administrator or developer to provide a single YAML specification file of the database to be created, Perseid allows the development database to be created using the familiar SQL CREATE statements. The user can then run the dbtoyaml utility to generate the YAML specification from the database. The spec can then be stored in any desired version control (VCS) repository. Similarly, she can add columns or modify tables or other objects using SQL ALTER statements and regenerate the YAML spec with dbtoyaml.

When ready to create or upgrade a test or production database, the yamltodb utility can be used with the YAML spec as input, to generate a script of SQL CREATE or ALTER statements to modify the database so that it matches the input specification.

Availability and Installation

The dbtoyaml and yamltodb utilities are standalone command-line executables. Once installed, they should be made available in your PATH environment variable.

For instructions on building and installing from source, see the Developer Guide.

Use Cases

The following sections discuss the main scenarios where Perseid tools may be helpful.

Version Control

The case for implementing a tool to facilitate version control over SQL databases was made in a couple of blog posts: Version Control, Part 1: Pre-SQL and Version Control, Part 2: SQL Databases. In summary, SQL data definition commands are generally incompatible with traditional version control approaches which usually require comparisons (diffs) between revisions of source files.

The Perseid version control tools are not designed to be the ultimate SQL database version control solution. Instead, they are aimed at assisting two or more DBAs or developers in sharing changes to the underlying database as they implement a database application. The Perseid tools may even be used by a single designer or DBA in conjunction with a distributed VCS to quickly explore alternative designs. The tools can also help to share changes with a conventional QA team, but may require additional controls for final releases and production installations.

Supplementary Actions

In many instances, a database schema needs to be supplemented by other tools such as to export and import rarely-modified data tables or to facilitate auditing of changes to the data. Perseid plans to implement those Pyrseas tools or facilities, in a subsequent release.