gocept.rdbmanagement 1.0

Recipe for managing RDB schemas

This is a zc.buildout recipe that helps managing schema updates on relational databases.

It currently only supports PostgreSQL.

Defining a managed database

A managed database has to be created (CREATEDB) outside of this recipe. The recipe only takes care of initialising an existing database and upgrading the schema subsequently.

Part definitions look like:

[managed_db]
recipe = gocept.rdbmanagement
dbname = mydatabase
eggs = projectegg
schema = projectegg.schemadir

host = localhost
user = username
password = apassword

Where

dbname

is the name of the PostgreSQL database to work with

eggs

is a list of egg requirements that should be activated before looking up the schema directory resource path

schema

is a setuptools resource path that is a managed schema directory

Managed schema directories

A schema directory contains a set of SQL and Python files that are used to manage the schema for a database. A typical directory looks like this:

$ ls schemadir
__init__.py
init.sql
precondition3.py
update1.sql
update2.sql
update3.sql

Notice: A managed schema directory has to be a Python package.

init.sql

• Creates the schema beginning from an empty database.

• After init.sql the current generation will be set to the highest generation number as available from the update scripts. Therefore the init.sql always creates a current database schema and no updates will be run.

updateX.sql

• Update scripts MUST start with a BEGIN statement and end with a COMMIT statement.

• Update script X will be run from a database at generation X-1.

preconditionX.py

• Script must contain a function called precondition which takes one parameter which is an open DB-API2-connection to the database.

• Precondition script X will be run on a database at generation X-1.

• Precondition script X has to raise an exception to indicate that the database MUST not be updated to generation X.

Changes

1.0 (2015-07-06)

• Implement ability to use a password when accessing the database, requiring at least PostgreSQL 9.0 for this feature.

• Require at least Python 2.7.

0.1.2

• allow newer versions than exactely psycopg2==2.0.6

0.1.1

• first public release