pyrseas

Pyrseas is a utility for managing PostgreSQL database schemas using YAML/JSON as a source of truth. It enables three primary workflows:

1. Schema Export: Describe a PostgreSQL database schema as YAML/JSON metadata, which is ideal for version control.
2. Schema Verification & Migration: Compare a live database against a YAML/JSON specification and generate the SQL statements required to modify the database to match that specification.
3. Schema Augmentation: Generate an augmented YAML description of a database by combining its existing catalogs with an augmentation specification.

Pyrseas is a utility for managing PostgreSQL database schemas using YAML. It allows you to:

1. Describe schemas: Export a PostgreSQL database schema (tables and other metadata) as a YAML file, which is ideal for version control.
2. Verify schemas: Compare a live database schema against a YAML/JSON specification or another database.
3. Evolve schemas: Generate the SQL statements required to modify an existing database so that it matches a target YAML/JSON specification.
4. Augment descriptions: Generate an augmented YAML description of a database by combining its catalogs with an augmentation specification.

Pyrseas provides a workflow for maintaining PostgreSQL database schemas using YAML specifications. This allows you to bridge the gap between traditional SQL development and version-controlled schema management.

Core Workflow

1. Generate Specification: Use dbtoyaml to inspect an existing database (created via standard SQL CREATE or ALTER statements) and generate a YAML specification file.
2. Version Control: Store the generated YAML spec in a Version Control System (VCS).
3. Apply Changes: Use yamltodb to take a YAML specification as input and generate the necessary SQL CREATE or ALTER statements to upgrade a target database (test or production) to match that specification.
4. Augment Schemas: Use dbaugment to add custom attributes, such as updated columns or trigger functions, to existing schemas.

Pyrseas provides tools for handling schema requirements that go beyond basic table definitions:

• dbaugment: Used to standardize or add custom attributes to a schema. For example, you can use it to add an updated column to multiple tables along with the trigger functions required to maintain that column automatically.
• datacopy: A configuration parameter used to facilitate the import and export of rarely-modified data (like codes-descriptions tables) that must accompany a schema.

The spec file is a YAML document that defines how tables should be augmented. It consists of two main parts:

1. augmenter section: Contains global configuration for the augmentation process, such as custom column definitions.
2. Schema sections: Organized by schema name (e.g., schema public:), containing table-specific instructions.

Supported Table Options

• audit_columns: Specifies how audit trail columns (like modification timestamps) should be added to the table.

Example Specification

This example configures a custom modified_date column and applies different audit settings to tables t1 and t3 in the public schema:

augmenter:
  columns:
    modified_date:
      not_null: true
      type: date
schema public:
  table t1:
    audit_columns: default
  table t3:
    audit_columns: modified_only

Pyrseas specifies each column or expression in an index or partition separately within the YAML definitions, rather than using a single CREATE INDEX statement.

While this approach is used for both regular columns and expressions, it may not be satisfactory for highly complex cases involving mixed expressions and columns. This is a known area of ongoing development.

The AugmentDatabase class (from the pyrseas.augmentdb module) is used to augment existing database schemas. It extends the standard Database class by adding an AugDicts object (adb) alongside the standard Dicts object (db).

While db contains the existing database schemas (tables, etc.) queried from system catalogs, adb specifies which schemas should be augmented and contains the configuration objects for the augmentation process.

To perform augmentation, use the apply method or initialize the database using from_augmap.

Pyrseas' dbaugment tool provides predefined combinations of columns and triggers that can be added to tables via the config.yaml configuration file to implement audit trails. These combinations automate the tracking of row creation and modification metadata.

Available audit combinations:

• created_date_only: Adds a created_date column defaulting to CURRENT_DATE.
• modified_only: Adds a modified_timestamp column managed by a trigger named {table_name}_20_audit_modified_only.
• default: The standard audit level. Adds modified_by_user and modified_timestamp columns, managed by a trigger named {table_name}_20_audit_default.
• full: The most comprehensive audit level. Adds created_ and modified_ columns for user, IP address, and timestamp, managed by a trigger named {table_name}_20_audit_full.

Pyrseas uses a class hierarchy to represent various PostgreSQL types and domains. All specific type classes derive from DbType, which in turn represents a SQL type or domain as defined in the Postgres pg_type catalog.

• DbType: The base class for all SQL types/domains.
• BaseType: Represents a Postgres user-defined base type.
• Composite: Represents a standalone composite type (row type).
• Enum: Represents an enumerated type.
• Domain: Represents a SQL DOMAIN.
• Range: Represents a Postgres range type.
• TypeDict: A specialized dictionary (DbObjectDict) used to manage collections of domains and enums in a database.

Pyrseas uses a layered configuration system where settings from later levels override earlier ones. If a parameter is defined in multiple places, the order of precedence (from lowest to highest) is:

1. System Configuration: Distributed with the library.
2. User Configuration: Personal settings stored in your home directory.
3. Repository Configuration: Project-specific settings stored in a VCS repository.
4. Command Line Configuration: Settings provided via the --config flag.
5. Command Line Options: Individual flags/options provided directly to a command.

Any configuration item specified directly on the command line takes precedence over all configuration files.