PerFact DBSchemaTools

Tools for deploying necessary schema changes for a perfactema database.

It aims to gradually replace DatabaseModifications by a schema definition delivered in the form of yaml files in a file system structure, so we don't define the steps to perform to arrive at some required schema, but the target schema itself. It also allows introspecting the current database and checking which (additive) change are to be performed to arrive at the correct schema.

The maintainers of this project are:

• Alexander Rolfes alexander.rolfes@perfact.de
• Ján Jockusch jan.jockusch@perfact.de
• Viktor Dick viktor.dick@perfact.de

Technical details

The DBSchemaTools come bundled with an executable perfact-dbschema which is available in /usr/bin. This allows several actions to be performed (check out --help):

• patch: Compare the current DB against the definitions provided in the schema definition paths and extend the current DB as required.
• add: Adds an entity to a schema definition path
• dump: Dumps the full database into a schema definition path. Currently only for debugging.

Additionally, a wrapper perfact-dbschema-add is provided with a sudoers file that allows the user zope to execute it as user perfact on a typical PerFact system. This is intended to be used by the table manager while we transition more and more functionality over.

On a PerFact template system, the package perfact-dbutils-zope4 additionally provides a wrapper perfact-dbschema-patch. This is also used by the zodbsync configuration to make sure that any code deployment runs through the three steps:

1. Check the schema definitions as recognized by perfact-dbschematools and execute them, before changing anything inside Zope.
2. Play back DatabaseModifications and related paths, and trigger their application.
3. Play back any remaining code changes, i.e. the code that depends on the new schema.

A schema definition path is usually something like /opt/perfact/dbutils-zoperepo/__schema__ or a similar path somewhere in /var/lib/perfact/zodbsync/layers for an activated layer package. It contains a folder structure mostly oriented along tables, containing YAML files (and in the future probably also other file formats, when we include function definitions) that describe the entities that we require to be present in the database.

In preparation for a future situation where we can develop for multiple layers on the same system, the main executable perfact-dbschema requires the path to be explicitly named, making its arguments somewhat clumsy at times. Usually, you should not have to interact with it directly, relying on the table manager and on the wrapper perfact-dbschema-patch instead.

CLI Reference

The executable perfact-dbschema provides three subcommands. Use perfact-dbschema --help or perfact-dbschema <subcommand> --help for current usage details.

• patch — Compare the schema definitions against the current database and apply all necessary changes.
• add — Add an entity (table, column, or view) to a schema definition path. Supports --return-patch to print the corresponding SQL without executing it.
• dump — Write the current database schema into a schema definition path. Intended for debugging or for retrieving the canonical form of expressions (e.g. after the first patch of a generated column).

---

YAML format

Schema definitions live in a directory structure rooted at the schema definition path.

Tables — tables/{table}/main.yml

Content is an empty YAML document (---). The mere existence of the file registers the table.

Columns — tables/{table}/columns/{column}.yml

type: text           # required — PostgreSQL column type
default: now()       # optional — SQL default expression

Common type aliases are accepted and normalised (bigint → int8, boolean → bool, integer → int4, etc.).

For default, Python scalars written directly in YAML are accepted and normalised automatically:

YAML	Stored as
default: true	true
default: 42	42
default: 3.14	3.14
default: null	removes any existing default
default: "'some text'"	'some text' (SQL string literal)

If default is omitted entirely, any existing database default is preserved.

Foreign keys — for columns following the naming convention {src}_{tgt}_id:

type: int8
fk: cascade          # one of: cascade, set null, restrict

Foreign key constraints are additive: once created they are never removed, even if fk is later omitted from the YAML.

Generated columns (GENERATED ALWAYS AS):

type: int8
generated: "pdordlc_seqnum / 10"

generated and default are mutually exclusive. The expression must be valid PostgreSQL. After the first patch, use dump to retrieve the canonical (PostgreSQL-normalised) form of the expression and update the YAML accordingly to avoid spurious change detection on subsequent runs.

Changing the expression of an existing generated column is not possible without dropping the column first — the tool will log a warning and skip the column if a mismatch is detected.

Views — views/{view}.yml or tables/{table}/views/{view}.yml

viewdef: "SELECT id, name FROM mytable WHERE active"

Views are recreated automatically when their definition changes. Dependency ordering between views is resolved automatically.

---

Usage from other packages

As of version 26.2.0, perfact-dbschematools no longer uses a virtual environment and can therefore be imported in other Python packages. A package that deploys some schema definitions that it requires to be executed to run can therefore do something like this at startup:

from perfact.dbschematools import patch
patch('/path/to/my/schema/definition', my_connection_string)

Configuration

The default configuration file /etc/pefact/dbschematools/config.yml currently only describes the database connection string.

FAQs

I tried deploying a code state that uses schema definitions and it failed

• Is perfact-dbutils-zope4 up to date? If you deployed using the layer package perfact-zope-layers-complete, the dependency should be set correctly so the necessary versions are installed, but if you only deployed using a git checkout, you will need to update the packages accordingly.
• Does the configuration of perfact-dbschematools contain the correct connection string?
• If you skipped the failures and continued the deployment, but fixed the cause, you can execute perfact-dbschema-patch to retry.
• Afterwards, you want to retry applying the DatabaseModifications.

During development, a column I created should have a different type after all

• Edit the file in __schema__/tables/<table>/columns/<column>.yml
• Execute perfact-dbschema-patch.
• If the column can not be converted automatically due to completely different types, you will need to first delete the column manually.