Metadata-Version: 2.4
Name: perfact-dbschematools
Version: 26.7.0
Summary: Tools for deploying necessary schema changes for a perfactema database
Author: Viktor Dick
Author-email: Viktor Dick <viktor.dick@perfact.de>
License: GPL-2.0-or-later
Classifier: Programming Language :: Python :: 3
Classifier: Operating System :: POSIX :: Linux
Classifier: Topic :: Database
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: oyaml
Requires-Dist: pglast
Requires-Dist: psycopg2
Requires-Dist: perfact-dbutils

# 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`

```yaml
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`:

```yaml
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`):

```yaml
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`

```yaml
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.
