============================= metapensiero.sphinx.patchdb ============================= Collects and applies scripts embedded in a reST document ======================================================== :version: 0.3 :author: Lele Gaifax :license: GPLv3 Building and maintaining the schema of a database is always a challenge. It may quickly become a nightmare when dealing with even moderately complex databases, in a distribuited development environment. You have new features going in, and fixes here and there, that keeps accumulating in the development `branch`. You also have several already deployed instances of the database you wanna upgrade now and then. In my experience, it's very difficult to impossible to come up with a completely automated solution, for several reasons: * comparison between different releases of a database schema is tricky * actual contents of the database must be preserved * some changes require specific recipes to upgrade the data * any automated solution hide some detail, by definition: I need complete control, to be able to create temporary tables and/or procedures for example I tried, and wrote myself, several different approaches to the problem, and this package is my latest and most satisfying effort: it builds on top of `docutils`_ and `Sphinx`_, with the side advantage that you get a quite nice and good documentation of the whole architecture: `literate database scheming`! .. _docutils: http://docutils.sourceforge.net/ .. _sphinx: http://sphinx.pocoo.org/intro.html How it works ------------ The package contains two distinct pieces: a `Sphinx`_ extension and the ``patchdb`` command line tool. The extension implements a new `ReST` directive able to embed a `script` in the document: when processed by the ``sphinx-build`` tool, all the scripts will be collected in an external file, configurable. The ``patchdb`` tool takes that script collection and determines which scripts need to be applied to some database, and the right order. It keeps and maintains a single very simple table within the database, where it records the last version of each script it successfully execute, so that it won't reexecute the same script (actually, a particular `revision` of it) twice. So, on the development side you simply write (and document!) each piece, and when it comes the time of deploying current state you distribute just the script collection (a single file, usually in `YAML`_ format, or a ``pickle`` archive) to the end points where the database instances live, and execute ``patchdb`` against each one. .. _yaml: http://yaml.org/ Scripts ------- The basic building block is a `script`, an arbitrary sequence of statements written in some language (currently, either ``Python``, ``SQL`` or ``Shell``), augmented with some metadata such as the `scriptid`, possibly a longer `description`, its `revision` and so on. As a complete example of the syntax, consider the following:: .. script:: My first script :description: Full example of a script :revision: 2 :depends: Other script@4 :preceeds: Yet another :language: python print("Yeah!") This will introduce a script globally identified by `My first script`, written in ``Python``: this is its second release, and its execution must be constrained such that it happens *after* the execution of the fourth revision of `Other script` and *before* `Yet another`. The dependencies may be a comma separated list of script ids, such as:: .. script:: Create master table CREATE TABLE some_table (id INTEGER PRIMARY KEY, tt_id INTEGER) .. script:: Create target table CREATE TABLE target_table (id INTEGER PRIMARY KEY) .. script:: Add foreign key to some_table :depends: Create master table, Create target table ALTER TABLE some_table ADD CONSTRAINT fk_master_target FOREIGN KEY (tt_id) REFERENCES target_table (id) Indipendently from the order these scripts appear in the documentation, the third script will execute only after the first two get successfully applied to the database. Just for illustration purposes, the same effect could be achieved with:: .. script:: Create master table :preceeds: Add foreign key to some_table CREATE TABLE some_table (id INTEGER PRIMARY KEY, tt_id INTEGER) .. script:: Create target table CREATE TABLE target_table (id INTEGER PRIMARY KEY) .. script:: Add foreign key to some_table :depends: Create target table ALTER TABLE some_table ADD CONSTRAINT fk_master_target FOREIGN KEY (tt_id) REFERENCES target_table (id) Patches ------- A `patch` is a particular flavour of script, one that specify a `brings` dependency list. Imagine that the example above was the first version of the database, and that the current version looks like the following:: .. script:: Create master table :revision: 2 CREATE TABLE some_table ( id INTEGER PRIMARY KEY, description VARCHAR(80), tt_id INTEGER ) that is, ``some_table`` now contains one more field, ``description``. We need an upgrade path from the first revision of the table to the second:: .. script:: Add a description to the master table :depends: Create master table@1 :brings: Create master table@2 ALTER TABLE some_table ADD COLUMN description VARCHAR(80) When ``patchdb`` examines the database status, it will execute one *or* the other. If the script `Create master table` isn't executed yet (for example when operating on a new database), it will take the former script (the one that creates the table from scratch). Otherwise, if the database "contains" revision 1 (and not higher than 1) of the script, it will execute the latter, bumping up the revision number.