.. -*- coding: utf-8 -*-
.. :Project: PatchDB
.. :Created: Sat Aug 22 16:19:15 2009 +0000
.. :Author: Lele Gaifax <lele@metapensiero.it>
.. :License: GNU General Public License version 3 or later
.. :Copyright: © 2009, 2010, 2012, 2013, 2014, 2015, 2016, 2017, 2018 Lele Gaifax
..
=============================
metapensiero.sphinx.patchdb
=============================
Collects and applies scripts embedded in a reST document
========================================================
:version: 2.0
:author: Lele Gaifax <lele@metapensiero.it>
: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 hides 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
.. contents::
.. [*] Just to mention a few alternatives:
`Alembic <https://pypi.python.org/pypi/alembic>`_
Written on top of SQLAlchemy_ by the same author: it does not help when you need to
manage something outside SA knowledge (stored procedures, permissions, …)
`Sqlibrist <https://pypi.python.org/pypi/sqlibrist>`_
Some similarities with PatchDB, very young, Django integration.
`Sqitch <http://sqitch.org/>`_
Quite good, *although* Perl based…
See the `schema migration <https://en.wikipedia.org/wiki/Schema_migration>`_ page on
Wikipedia for further details.
----
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 creates and maintains a single very simple table within the database (unsurprisingly named
``patchdb``), 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 `AXON`_, `JSON`_ or `YAML`_ format, or a ``pickle`` archive, see `storage formats`_
below) to the end points where the database instances live, and execute ``patchdb`` against
each one.
.. _yaml: http://yaml.org/
.. _json: http://json.org/
.. _axon: http://intellimath.bitbucket.org/axon/
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::
.. patchdb:script:: My first script
:description: Full example of a script
:revision: 2
:depends: Other script@4
:preceeds: Yet another
:language: python
:conditions: python_2_x
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 sequence of statements may be specified either as the *content* of the directive **or**
loaded from an external file, so the previous script could be written as::
.. patchdb:script:: My first script
:description: Full example of a script
:revision: 2
:depends: Other script@4
:preceeds: Yet another
:language: python
:conditions: python_2_x
:file: python_script.py
``SQL`` scripts may be composed by multiple statements, separated by a standalone ``;;``
marker, as in::
.. patchdb:script:: Create and populate
CREATE TABLE foo (id integer, value varchar(20))
;;
INSERT INTO foo (id, value) VALUES (1, 'bar')
Another special marker is ``;;INCLUDE:``, that can be used to include the content of an
external file, more flexibly than with the ``file`` option above. The previous example could be
written as::
.. patchdb:script:: Create and populate
;;INCLUDE: create_table.sql
;;
;;INCLUDE: populate_table.sql
where the two statements are loaded respectively from ``create_table.sql`` and
``populate_table.sql``. The ``;;INCLUDE:`` marker is expanded recursively, so that another way
to say the very same thing is::
.. patchdb:script:: Create and populate
;;INCLUDE: create_and_populate.sql
with ``create_and_populate.sql`` containing::
;;INCLUDE: create_table.sql
;;
;;INCLUDE: populate_table.sql
As another concrete example where this can be very useful, consider the case when you need to
replace an existing function with one having a different signature for the output parameters,
something that for example PostgreSQL does not allow. You could then say::
.. patchdb:script:: Some function
:revision: 2
:file: some_function.sql
.. patchdb:script:: Upgrade some function to revision 2
:depends: Some function@1
:brings: Some function@2
DROP FUNCTION some_function(int, OUT int)
;;
;;INCLUDE: some_function.sql
Conditions
++++++++++
The example shows also an usage of the conditions, allowing more than one variant of a script
like::
.. patchdb:script:: My first script (py3)
:description: Full example of a script
:revision: 2
:depends: Other script@4
:preceeds: Yet another
:language: python
:conditions: python_3_x
print("Yeah!")
The value of the ``:conditions:`` option may be a single paragraph, containing a comma
separated list of conditions, or alternatively a `bullet list`_.
.. _bullet list:
http://docutils.sourceforge.net/docs/ref/rst/restructuredtext.html#bullet-lists
As another use case of this feature, the following snippet declares the same table for two
different databases::
.. patchdb:script:: Simple table (PostgreSQL)
:language: sql
:mimetype: text/x-postgresql
:conditions: postgres
:file: postgresql/simple.sql
.. patchdb:script:: Simple table (MySQL)
:language: sql
:mimetype: text/x-mysql
:conditions: mysql
:file: mysql/simple.sql
As you can see, the content of the script can be conveniently stored in an external file, and
the particular dialect specified with the ``:mimetype:`` option, so it will be properly
highlighted by Pygments.
Such conditions may also be arbitrarily defined on the command line, so you can have for
example::
.. patchdb:script:: Configure for production
:language: sql
:conditions: PRODUCTION
UPDATE configuration SET is_production = true
and then add the option ``--assert PRODUCTION`` when it is the case.
A condition can be `negated`, prepending a ``!`` to its name::
.. patchdb:script:: Configure for production
:language: sql
:conditions: !PRODUCTION
UPDATE configuration SET is_production = false
Variables
+++++++++
Another way to influence a script effect is by using *variables*: a script may contain one or
more references to arbitrary variables using the syntax ``{{VARNAME}}``, that **must** be
defined at application time, using the ``--define VARNAME=VALUE`` command line option.
Alternatively with the syntax ``{{name=default}}`` the reference can set the default value for
the variable, that can be overridden from the command line.
As an example, you can have the following script::
.. patchdb:script:: Create table and give read-only rights to the web user
:language: sql
CREATE TABLE foo (id INTEGER)
;;
GRANT SELECT ON TABLE foo TO {{WEB=www}}
;;
GRANT ALL ON TABLE foo TO {{ADMIN}}
To apply it, you must specify the value for the ``ADMIN`` variable, with something like
``--define ADMIN=$USER``.
The variable name must be an *identifier* (that is, at least an alphabetic letter possibly
followed by alphanumerics or underscores), while its value may contain whitespaces, letters or
digits.
If the name starts with ``ENV_``, the value is looked up in the process `environment`. In the
following example, the name of the user is taken from the the ``USER`` environment variable
(that must be present), while the password comes from the ``PASSWORD`` environment entry or, if
not set, from the specified default::
.. patchdb:script:: Insert a default user name
:language: sql
INSERT INTO users (name, password) VALUES ('{{ENV_USER}}', '{{ENV_PASSWORD=password}}')
Note that you can override the environment using an explicit ``--define`` option on the command
line, for example with ``--define ENV_PASSWORD=foobar``.
Dependencies
++++++++++++
.. _master-table:
The dependencies (that is, the *options* ``:brings:``, ``:depends:``, ``:drops::`` and
``:preceeds:``) may be a paragraph containing a comma separated list of script ids, such as::
.. patchdb:script:: Create master table
CREATE TABLE some_table (id INTEGER PRIMARY KEY, tt_id INTEGER)
.. patchdb:script:: Create target table
CREATE TABLE target_table (id INTEGER PRIMARY KEY)
.. patchdb: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)
.. warning:: This implies that the referenced ``scriptid``\ s **cannot** include a comma.
Alternatively, they can be entered as a `bullet list`_, so the last script above can be written
also as::
.. patchdb: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)
With this syntax you can reference a ``scriptid`` containing a comma.
Independently from the order these scripts appear in the documentation, the third script will
execute only after the first two are successfully applied to the database. As you can notice,
most of the options are optional: by default, ``:language:`` is ``sql``, ``:revision:`` is
``1``, the ``:description:`` is taken from the title (that is, the script ID), while
``:depends:`` and ``:preceeds:`` are empty.
Just for illustration purposes, the same effect could be achieved with::
.. patchdb:script:: Create master table
:preceeds: Add foreign key to some_table
CREATE TABLE some_table (id INTEGER PRIMARY KEY, tt_id INTEGER)
.. patchdb:script:: Create target table
CREATE TABLE target_table (id INTEGER PRIMARY KEY)
.. patchdb: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)
Errors handling
+++++++++++++++
By default ``patchdb`` stops when it fails to apply one script. Some time you may want to relax
that rule, for example when operating on a database that was created with other methods so you
cannot relay on the existence of a specific script to make the decision. In such cases, the
option ``:onerror:`` may be used::
.. patchdb:script:: Remove obsoleted tables and functions
:onerror: ignore
DROP TABLE foo
;;
DROP FUNCTION initialize_record_foo()
When ``:onerror:`` is set to `ignore`, each statement in the script is executed and if an error
occurs it is ignored and ``patchdb`` proceeds with the next one. On good databases like
PostgreSQL and SQLite where even DDL statements are transactional, each statement is executed
in a nested subtransaction, so subsequent errors do not ruin the effect of correctly applied
previous statements.
Another possible setting of this option is `skip`: in this case, whenever an error occurs the
effect of the whole script is undone and it is considered as applied. For example, assuming
that the old version of ``SomeProcedure`` accepted a single argument and the new one requires
two of them, you could do something like the following::
.. patchdb:script:: Fix stored procedure signature
:onerror: skip
SELECT somecol FROM SomeProcedure(NULL, NULL)
;;
ALTER PROCEDURE SomeProcedure(p_first INTEGER, p_second INTEGER)
RETURNS (somecol INTEGER) AS
BEGIN
somecol = p_first * p_second;
SUSPEND;
END
Patches
~~~~~~~
A `patch` is a particular flavour of script, one that specifies a `brings` or a `drops`
dependency list. Imagine that the `example above`__ was the first version of the database, and
that the current version looks like the following::
.. patchdb: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::
.. patchdb: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.
__ master-table_
Obsoleted patches
+++++++++++++++++
Another peculiarity of this kind of scripts is that they may references `non existing scripts`
without producing warnings or errors.
The rationale is that in the database evolution a given script may be removed, possibly
replaced by a different one by some succeeding patch. Consider the case when you once had a
table called ``customers``::
.. patchdb:script:: Create table customers
:revision: 2
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(80),
street_address VARCHAR(80),
city VARCHAR(80),
telephone_number VARCHAR(80)
)
.. patchdb:script:: Add telephone number to customers table
:depends: Create table customers@1
:brings: Create table customers@2
ALTER TABLE customers ADD COLUMN telephone_number VARCHAR(80)
and then the need for multiple addresses arose thus you decided to split it in two distinct
relations, a ``persons`` and a ``person_addresses``::
.. patchdb:script:: Create table persons
CREATE TABLE persons (
id SERIAL PRIMARY KEY,
name VARCHAR(80)
)
.. patchdb:script:: Create table person_addresses
:depends: Create table persons
CREATE TABLE person_addresses (
id SERIAL PRIMARY KEY,
person_id INTEGER REFERENCES persons (id),
street_address VARCHAR(80),
city VARCHAR(80),
telephone_number VARCHAR(80)
)
.. patchdb:script:: Migrate from customers to persons and person_addresses
:depends:
- Create table customers@2
- Create table persons
- Create table person_addresses
:drops:
- Create table customers
- Add telephone number to customers table
INSERT INTO persons (id, name) SELECT id, name FROM customers
;;
INSERT INTO person_addresses (person_id, street_address, city, telephone_number)
SELECT id, street_address, city, telephone_number
FROM customers
;;
DROP TABLE customers
At that point the script that introduced the original ``customers`` table disappeared from the
documentation, but you most probably want to keep the migration patch around for a while, at
least until you are sure all your production databases got upgraded.
.. hint:: In the HTML output, missing dependencies such as the ``"Create table customers"``
above are marked with a ``strike`` class. In order to actually get a visual effect
you need to define the ``CSS`` style for that, for example putting the following in
the Sphinx configuration::
html_context = {
'css_files': [
'_static/theme_overrides.css',
],
}
and something like
::
span.strike {
text-decoration: line-through;
}
in ``_static/theme_overrides.css``.
Run-always scripts
~~~~~~~~~~~~~~~~~~
Yet another variant of scripts, which get applied always, **every time** ``patchdb`` is
executed. This kind may be used to perform arbitrary operations, either at the start or at the
end of the ``patchdb`` session::
.. patchdb:script:: Say hello
:language: python
:always: first
print("Hello!")
.. patchdb:script:: Say goodbye
:language: python
:always: last
print("Goodbye!")
Fake data domains
+++++++++++++++++
As a special case that uses this kind of script, the following example illustrate an
`approximation` of the `data domains` with MySQL, that lacks them::
.. patchdb:script:: Define data domains (MySQL)
:language: sql
:mimetype: text/x-mysql
:conditions: mysql
:always: first
CREATE DOMAIN bigint_t bigint
;;
CREATE DOMAIN `Boolean_t` char(1)
.. patchdb:script:: Create some table (MySQL)
:language: sql
:mimetype: text/x-mysql
:conditions: mysql
:always: first
CREATE TABLE `some_table` (
`ID` bigint_t NOT NULL,
, `FLAG` `Boolean_t`
, PRIMARY KEY (`ID`)
)
.. warning:: This is just a dirty hack, based on relatively simple search and replace: don't
take it seriously, use a better database if you really need `data domains`!
.. note:: This works also with SQLite.
Placeholders
~~~~~~~~~~~~
Another feature is that the definition of the database, that is the collection of the scripts
that actually define its schema, may be splitted on multiple Sphinx environments: the use case
is when you have a complex application, composed by multiple modules, each of them requiring
its own set of DB objects.
A script is considered a `placeholder` when it has an empty body: it won't be ever applied, but
instead its presence in the database will be asserted. In this way, one Sphinx environment
could contain the following script::
.. patchdb:script:: Create table a
CREATE TABLE a (
id INTEGER NOT NULL PRIMARY KEY
, value INTEGER
)
and another documentation set could extend that with::
.. patchdb:script:: Create table a
:description: Place holder
.. patchdb:script:: Create unique index on value
:depends: Create table a
CREATE UNIQUE INDEX on_value ON a (value)
The second set can be applied **only** after the former one is.
Usage
-----
Collecting patches
~~~~~~~~~~~~~~~~~~
To use it, first of all you must register the extension within the Sphinx environment, adding
the full name of the package to the ``extensions`` list in the file ``conf.py``, for example::
# Add any Sphinx extension module names here, as strings.
extensions = ['metapensiero.sphinx.patchdb']
The other required bit of customization is the location of the `on disk scripts storage`,
i.e. the path of the file that will contain the information about every found script: this is
kept separated from the documentation itself because you will probably deploy it on production
servers just to update their database.
.. _storage formats:
.. topic:: Storage formats
If the filename ends with ``.json`` it will contain a ``JSON`` formatted array, if it ends
with ``.yaml`` the information will be dumped in ``YAML``, if it ends with ``.axon`` the
dump will be formatted using ``AXON``, otherwise it will be a Python ``pickle``. I usually
prefer ``AXON``, ``JSON`` or ``YAML``, because those formats are more VCs friendly and open
to human inspection. These days I tend to use ``AXON`` for this kind of things as it is
slightly more readable and more VCs friendly than ``JSON``, while ``YAML`` is very slow.
The location may be set in the same ``conf.py`` as above, like::
# Location of the external storage
patchdb_storage = '…/dbname.json'
Otherwise, you can set it using the ``-D`` option of the ``sphinx-build`` command, so that you
can easily share its definition with other rules in a ``Makefile``. I usually put the following
snippet at the beginning of the ``Makefile`` created by ``sphinx-quickstart``::
TOPDIR ?= ..
STORAGE ?= $(TOPDIR)/database.json
SPHINXOPTS = -D patchdb_storage=$(STORAGE)
At this point, executing the usual ``make html`` will update the scripts archive: that file
contains everything is needed to update the database either local or remote; in other words,
running Sphinx (or even having it installed) is **not** required to update a database.
Updating the database
~~~~~~~~~~~~~~~~~~~~~
The other side of the coin is managed by the ``patchdb`` tool, that digests the scripts archive
and is able to determine which of the scripts are not already applied and eventually does that,
in the right order.
When your database does already exist and you are just starting using ``patchdb`` you may need
to force the initial state with the following command::
patchdb --assume-already-applied --postgresql "dbname=test" database.json
that will just update the `patchdb` table registering current revision of all the missing
scripts, without executing them.
You can inspect what will be done, that is obtain the list of not already applied patches, with
a command like::
patchdb --dry-run --postgresql "dbname=test" database.json
The `database.json` archive can be sent to the production machines (in some cases I put it in a
*production* branch of the repository and use the version control tool to update the remote
machines, in other I simply used ``scp`` or ``rsync`` based solutions). Another way is to
include it in some package and then use the syntax ``some.package:path/database.json``.
The scripts may even come from several different archives (see `placeholders`_ above)::
patchdb --postgresql "dbname=test" app.db.base:pdb.json app.db.auth:pdb.json
Automatic backup
~~~~~~~~~~~~~~~~
In particular in development mode, I find it useful to have a simple way of going back to a
previous state and retry the upgrade, either to test different upgrade paths or to fix silly
typos in the new patches.
Since version 2.3 ``patchdb`` has a new option, ``--backups-dir``, that controls an automatic
backup facility: at each execution, before proceeding with applying missing patches,
*regardless* whether there are any, by default it takes a backup of the current database and
keeps a simple index of these snapshots.
The option defaults to the system-wide temporary directory (usually ``/tmp`` on POSIX systems):
if you you don't need the automatic backup (a reasonable production system should have a
different approach to taking such snapshots), specify ``None`` as argument to the option.
With the ``patchdb-states`` tool you obtain a list of the available snapshots, or restore any
previous one::
$ patchdb-states list
[lun 18 apr 2016 08:24:48 CEST] bc5c5527ece6f11da529858d5ac735a8 <create first table@1>
[lun 18 apr 2016 10:27:11 CEST] 693fd245ad9e5f4de0e79549255fbd6e <update first table@1>
$ patchdb-states restore --sqlite /tmp/quicktest.sqlite 693fd245ad9e5f4de0e79549255fbd6e
[I] Creating patchdb table
[I] Restored SQLite database /tmp/quicktest.sqlite from /tmp/693fd245ad9e5f4de0e79549255fbd6e
$ patchdb-states clean -k 1
Removed /tmp/bc5c5527ece6f11da529858d5ac735a8
Kept most recent 1 snapshot
Supported databases
~~~~~~~~~~~~~~~~~~~
As of version 2, ``patchdb`` can operate on the following databases:
* Firebird (requires fdb_)
* MySQL (requires PyMySQL_ by default, see option ``--driver`` to select a different one)
* PostgreSQL (requires psycopg2_)
* SQLite (uses the standard library ``sqlite3`` module)
.. _fdb: https://pypi.python.org/pypi/fdb
.. _PyMySQL: https://pypi.python.org/pypi/PyMySQL
.. _psycopg2: https://pypi.python.org/pypi/psycopg2
.. _SQLAlchemy: http://www.sqlalchemy.org/
Example development Makefile snippet
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The following is a snippet that I usually put in my outer ``Makefile``::
export TOPDIR := $(CURDIR)
DBHOST := localhost
DBPORT := 5432
DBNAME := dbname
DROPDB := dropdb --host=$(DBHOST) --port=$(DBPORT) --if-exists
CREATEDB := createdb --host=$(DBHOST) --port=$(DBPORT) --encoding=UTF8
STORAGE := $(TOPDIR)/$(DBNAME).json
DSN := host=$(DBHOST) port=$(DBPORT) dbname=$(DBNAME)
PUP := $(PATCHDB) --postgresql="$(DSN)" --log-file=$(DBNAME).log $(STORAGE)
# Build the Sphinx documentation
doc:
$(MAKE) -C doc STORAGE=$(STORAGE) html
$(STORAGE): doc
# Show what is missing
missing-patches: $(STORAGE)
$(PUP) --dry-run
# Upgrade the database to the latest revision
database: $(STORAGE)
$(PUP)
# Remove current database and start from scratch
scratch-database:
$(DROPDB) $(DBNAME)
$(CREATEDB) $(DBNAME)
$(MAKE) database
Quick example
-------------
The following shell session illustrates the basics:
.. code-block:: shell
python3 -m venv patchdb-session
cd patchdb-session
source bin/activate
pip install metapensiero.sphinx.patchdb[dev]
yes n | sphinx-quickstart --project PatchDB-Quick-Test \
--author JohnDoe \
-v 1 --release 1 \
--language en \
--master index --suffix .rst \
--makefile --no-batchfile \
pdb-qt
cd pdb-qt
echo "extensions = ['metapensiero.sphinx.patchdb']" >> conf.py
echo "patchdb_storage = 'pdb-qt.json'" >> conf.py
echo "
.. patchdb:script:: My first script
:depends: Yet another
:language: python
print('world!')
.. patchdb:script:: Yet another
:language: python
print('Hello')
" >> index.rst
make html
patchdb --sqlite /tmp/pdb-qt.sqlite --dry-run pdb-qt.json
At the end you should get something like::
Would apply script "yet another@1"
Would apply script "my first script@1"
100% (2 of 2) |########################################| Elapsed Time: 0:00:00 Time: 0:00:00
Removing the ``--dry-run``::
$ patchdb --sqlite /tmp/pdb-qt.sqlite pdb-qt.json
Hello
world!
Done, applied 2 scripts
100% (2 of 2) |########################################| Elapsed Time: 0:00:00 Time: 0:00:00
Once again::
$ patchdb --sqlite /tmp/pdb-qt.sqlite pdb-qt.json
Done, applied 0 scripts
Changes\ [#]_
-------------
3.7 (2019-12-20)
~~~~~~~~~~~~~~~~
* Catch dependency error when a patch brings a script to a revision higher than its current
3.6 (2019-12-19)
~~~~~~~~~~~~~~~~
* Now Python scripts receive a reference to the current patch manager, so they are able to
execute arbitrary scripts already in the storage
3.5 (2019-06-21)
~~~~~~~~~~~~~~~~
* Now it's an hard error when a patch brings an unknown script: when it does, it's either
obsoleted or there is a typo somewhere
3.4 (2019-03-31)
~~~~~~~~~~~~~~~~
* Nothing new, minor glitch in the release procedure
3.3 (2019-03-31)
~~~~~~~~~~~~~~~~
* Lift the constraint on sqlparse version, allow use of recently released 0.3.0.
3.2 (2018-03-03)
~~~~~~~~~~~~~~~~
* Use `python-rapidjson`__ if available
__ https://pypi.org/project/python-rapidjson/
3.1 (2017-11-30)
~~~~~~~~~~~~~~~~
* Fix glitch in the logic that determine whether a patch script is still valid
* Use enlighten__ to show the progress bar: the ``--verbose`` option is gone, now is the
default mode
__ https://pypi.org/project/enlighten/
3.0 (2017-11-06)
~~~~~~~~~~~~~~~~
* Python 3 only\ [#]_
* New execution logic, hopefully fixing circular dependencies error in case of multiple non
trivial pending migrations
.. [#] Previous changes are here__.
__ https://gitlab.com/metapensiero/metapensiero.sphinx.patchdb/blob/master/OLDERCHANGES.rst
.. [#] If you are still using Python 2, either stick with version 2.27, or fetch `this
commit`__ from the repository.
__ https://gitlab.com/metapensiero/metapensiero.sphinx.patchdb/commit/f9fc5f5d50a381eaf9f003d7006cc46382842c18
Raw data
{
"_id": null,
"home_page": "https://gitlab.com/metapensiero/metapensiero.sphinx.patchdb.git",
"name": "metapensiero.sphinx.patchdb",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Lele Gaifax",
"author_email": "lele@metapensiero.it",
"download_url": "https://files.pythonhosted.org/packages/fe/67/4b5a00643683a21bf10f4de6ad77fd392591575530ab2eda67ce81ba8c88/metapensiero.sphinx.patchdb-3.7.tar.gz",
"platform": "",
"description": ".. -*- coding: utf-8 -*-\n.. :Project: PatchDB\n.. :Created: Sat Aug 22 16:19:15 2009 +0000\n.. :Author: Lele Gaifax <lele@metapensiero.it>\n.. :License: GNU General Public License version 3 or later\n.. :Copyright: \u00a9 2009, 2010, 2012, 2013, 2014, 2015, 2016, 2017, 2018 Lele Gaifax\n..\n\n=============================\n metapensiero.sphinx.patchdb\n=============================\n\nCollects and applies scripts embedded in a reST document\n========================================================\n\n:version: 2.0\n:author: Lele Gaifax <lele@metapensiero.it>\n:license: GPLv3\n\nBuilding and maintaining the schema of a database is always a challenge. It may quickly become\na nightmare when dealing with even moderately complex databases, in a distribuited development\nenvironment. You have new features going in, and fixes here and there, that keeps accumulating\nin the development `branch`. You also have several already deployed instances of the database\nyou wanna upgrade now and then.\n\nIn my experience, it's very difficult to impossible to come up with a completely automated\nsolution, for several reasons:\n\n* comparison between different releases of a database schema is tricky\n\n* actual contents of the database must be preserved\n\n* some changes require specific recipes to upgrade the data\n\n* any automated solution hides some detail, by definition: I need complete control, to be able\n to create temporary tables and/or procedures for example\n\nI tried, and wrote myself, several different approaches to the problem\\ [*]_, and this package\nis my latest and most satisfying effort: it builds on top of `docutils`_ and `Sphinx`_, with\nthe side advantage that you get a quite nice and good documentation of the whole architecture:\n`literate database scheming`!\n\n.. _docutils: http://docutils.sourceforge.net/\n.. _sphinx: http://sphinx.pocoo.org/intro.html\n\n\n.. contents::\n\n.. [*] Just to mention a few alternatives:\n\n `Alembic <https://pypi.python.org/pypi/alembic>`_\n Written on top of SQLAlchemy_ by the same author: it does not help when you need to\n manage something outside SA knowledge (stored procedures, permissions, \u2026)\n\n `Sqlibrist <https://pypi.python.org/pypi/sqlibrist>`_\n Some similarities with PatchDB, very young, Django integration.\n\n `Sqitch <http://sqitch.org/>`_\n Quite good, *although* Perl based\u2026\n\n See the `schema migration <https://en.wikipedia.org/wiki/Schema_migration>`_ page on\n Wikipedia for further details.\n\n----\n\nHow it works\n------------\n\nThe package contains two distinct pieces: a `Sphinx`_ extension and the ``patchdb`` command\nline tool.\n\nThe extension implements a new `ReST` directive able to embed a `script` in the document: when\nprocessed by the ``sphinx-build`` tool, all the scripts will be collected in an external file,\nconfigurable.\n\nThe ``patchdb`` tool takes that script collection and determines which scripts need to be\napplied to some database, and the right order.\n\nIt creates and maintains a single very simple table within the database (unsurprisingly named\n``patchdb``), where it records the last version of each script it successfully execute, so that\nit won't reexecute the same script (actually, a particular `revision` of it) twice.\n\nSo, on the development side you simply write (and document!) each piece, and when it comes the\ntime of deploying current state you distribute just the script collection (a single file,\nusually in `AXON`_, `JSON`_ or `YAML`_ format, or a ``pickle`` archive, see `storage formats`_\nbelow) to the end points where the database instances live, and execute ``patchdb`` against\neach one.\n\n.. _yaml: http://yaml.org/\n.. _json: http://json.org/\n.. _axon: http://intellimath.bitbucket.org/axon/\n\n\nScripts\n~~~~~~~\n\nThe basic building block is a `script`, an arbitrary sequence of statements written in some\nlanguage (currently, either ``Python``, ``SQL`` or ``Shell``), augmented with some metadata\nsuch as the `scriptid`, possibly a longer `description`, its `revision` and so on.\n\nAs a complete example of the syntax, consider the following::\n\n .. patchdb:script:: My first script\n :description: Full example of a script\n :revision: 2\n :depends: Other script@4\n :preceeds: Yet another\n :language: python\n :conditions: python_2_x\n\n print \"Yeah!\"\n\nThis will introduce a script globally identified by `My first script`, written in ``Python``:\nthis is its second release, and its execution must be constrained such that it happens\n**after** the execution of the fourth revision of `Other script` and **before** `Yet another`.\n\nThe sequence of statements may be specified either as the *content* of the directive **or**\nloaded from an external file, so the previous script could be written as::\n\n .. patchdb:script:: My first script\n :description: Full example of a script\n :revision: 2\n :depends: Other script@4\n :preceeds: Yet another\n :language: python\n :conditions: python_2_x\n :file: python_script.py\n\n``SQL`` scripts may be composed by multiple statements, separated by a standalone ``;;``\nmarker, as in::\n\n .. patchdb:script:: Create and populate\n\n CREATE TABLE foo (id integer, value varchar(20))\n ;;\n INSERT INTO foo (id, value) VALUES (1, 'bar')\n\nAnother special marker is ``;;INCLUDE:``, that can be used to include the content of an\nexternal file, more flexibly than with the ``file`` option above. The previous example could be\nwritten as::\n\n .. patchdb:script:: Create and populate\n\n ;;INCLUDE: create_table.sql\n ;;\n ;;INCLUDE: populate_table.sql\n\nwhere the two statements are loaded respectively from ``create_table.sql`` and\n``populate_table.sql``. The ``;;INCLUDE:`` marker is expanded recursively, so that another way\nto say the very same thing is::\n\n .. patchdb:script:: Create and populate\n\n ;;INCLUDE: create_and_populate.sql\n\nwith ``create_and_populate.sql`` containing::\n\n ;;INCLUDE: create_table.sql\n ;;\n ;;INCLUDE: populate_table.sql\n\nAs another concrete example where this can be very useful, consider the case when you need to\nreplace an existing function with one having a different signature for the output parameters,\nsomething that for example PostgreSQL does not allow. You could then say::\n\n .. patchdb:script:: Some function\n :revision: 2\n :file: some_function.sql\n\n .. patchdb:script:: Upgrade some function to revision 2\n :depends: Some function@1\n :brings: Some function@2\n\n DROP FUNCTION some_function(int, OUT int)\n ;;\n ;;INCLUDE: some_function.sql\n\n\nConditions\n++++++++++\n\nThe example shows also an usage of the conditions, allowing more than one variant of a script\nlike::\n\n .. patchdb:script:: My first script (py3)\n :description: Full example of a script\n :revision: 2\n :depends: Other script@4\n :preceeds: Yet another\n :language: python\n :conditions: python_3_x\n\n print(\"Yeah!\")\n\nThe value of the ``:conditions:`` option may be a single paragraph, containing a comma\nseparated list of conditions, or alternatively a `bullet list`_.\n\n.. _bullet list:\n http://docutils.sourceforge.net/docs/ref/rst/restructuredtext.html#bullet-lists\n\nAs another use case of this feature, the following snippet declares the same table for two\ndifferent databases::\n\n .. patchdb:script:: Simple table (PostgreSQL)\n :language: sql\n :mimetype: text/x-postgresql\n :conditions: postgres\n :file: postgresql/simple.sql\n\n .. patchdb:script:: Simple table (MySQL)\n :language: sql\n :mimetype: text/x-mysql\n :conditions: mysql\n :file: mysql/simple.sql\n\nAs you can see, the content of the script can be conveniently stored in an external file, and\nthe particular dialect specified with the ``:mimetype:`` option, so it will be properly\nhighlighted by Pygments.\n\nSuch conditions may also be arbitrarily defined on the command line, so you can have for\nexample::\n\n .. patchdb:script:: Configure for production\n :language: sql\n :conditions: PRODUCTION\n\n UPDATE configuration SET is_production = true\n\nand then add the option ``--assert PRODUCTION`` when it is the case.\n\nA condition can be `negated`, prepending a ``!`` to its name::\n\n .. patchdb:script:: Configure for production\n :language: sql\n :conditions: !PRODUCTION\n\n UPDATE configuration SET is_production = false\n\n\nVariables\n+++++++++\n\nAnother way to influence a script effect is by using *variables*: a script may contain one or\nmore references to arbitrary variables using the syntax ``{{VARNAME}}``, that **must** be\ndefined at application time, using the ``--define VARNAME=VALUE`` command line option.\nAlternatively with the syntax ``{{name=default}}`` the reference can set the default value for\nthe variable, that can be overridden from the command line.\n\nAs an example, you can have the following script::\n\n .. patchdb:script:: Create table and give read-only rights to the web user\n :language: sql\n\n CREATE TABLE foo (id INTEGER)\n ;;\n GRANT SELECT ON TABLE foo TO {{WEB=www}}\n ;;\n GRANT ALL ON TABLE foo TO {{ADMIN}}\n\nTo apply it, you must specify the value for the ``ADMIN`` variable, with something like\n``--define ADMIN=$USER``.\n\nThe variable name must be an *identifier* (that is, at least an alphabetic letter possibly\nfollowed by alphanumerics or underscores), while its value may contain whitespaces, letters or\ndigits.\n\nIf the name starts with ``ENV_``, the value is looked up in the process `environment`. In the\nfollowing example, the name of the user is taken from the the ``USER`` environment variable\n(that must be present), while the password comes from the ``PASSWORD`` environment entry or, if\nnot set, from the specified default::\n\n .. patchdb:script:: Insert a default user name\n :language: sql\n\n INSERT INTO users (name, password) VALUES ('{{ENV_USER}}', '{{ENV_PASSWORD=password}}')\n\nNote that you can override the environment using an explicit ``--define`` option on the command\nline, for example with ``--define ENV_PASSWORD=foobar``.\n\n\nDependencies\n++++++++++++\n\n.. _master-table:\n\nThe dependencies (that is, the *options* ``:brings:``, ``:depends:``, ``:drops::`` and\n``:preceeds:``) may be a paragraph containing a comma separated list of script ids, such as::\n\n .. patchdb:script:: Create master table\n\n CREATE TABLE some_table (id INTEGER PRIMARY KEY, tt_id INTEGER)\n\n .. patchdb:script:: Create target table\n\n CREATE TABLE target_table (id INTEGER PRIMARY KEY)\n\n .. patchdb:script:: Add foreign key to some_table\n :depends: Create master table, Create target table\n\n ALTER TABLE some_table\n ADD CONSTRAINT fk_master_target\n FOREIGN KEY (tt_id) REFERENCES target_table (id)\n\n.. warning:: This implies that the referenced ``scriptid``\\ s **cannot** include a comma.\n\nAlternatively, they can be entered as a `bullet list`_, so the last script above can be written\nalso as::\n\n .. patchdb:script:: Add foreign key to some_table\n :depends:\n - Create master table\n - Create target table\n\n ALTER TABLE some_table\n ADD CONSTRAINT fk_master_target\n FOREIGN KEY (tt_id) REFERENCES target_table (id)\n\nWith this syntax you can reference a ``scriptid`` containing a comma.\n\nIndependently from the order these scripts appear in the documentation, the third script will\nexecute only after the first two are successfully applied to the database. As you can notice,\nmost of the options are optional: by default, ``:language:`` is ``sql``, ``:revision:`` is\n``1``, the ``:description:`` is taken from the title (that is, the script ID), while\n``:depends:`` and ``:preceeds:`` are empty.\n\nJust for illustration purposes, the same effect could be achieved with::\n\n .. patchdb:script:: Create master table\n :preceeds: Add foreign key to some_table\n\n CREATE TABLE some_table (id INTEGER PRIMARY KEY, tt_id INTEGER)\n\n .. patchdb:script:: Create target table\n\n CREATE TABLE target_table (id INTEGER PRIMARY KEY)\n\n .. patchdb:script:: Add foreign key to some_table\n :depends: Create target table\n\n ALTER TABLE some_table\n ADD CONSTRAINT fk_master_target\n FOREIGN KEY (tt_id) REFERENCES target_table (id)\n\n\nErrors handling\n+++++++++++++++\n\nBy default ``patchdb`` stops when it fails to apply one script. Some time you may want to relax\nthat rule, for example when operating on a database that was created with other methods so you\ncannot relay on the existence of a specific script to make the decision. In such cases, the\noption ``:onerror:`` may be used::\n\n .. patchdb:script:: Remove obsoleted tables and functions\n :onerror: ignore\n\n DROP TABLE foo\n ;;\n DROP FUNCTION initialize_record_foo()\n\nWhen ``:onerror:`` is set to `ignore`, each statement in the script is executed and if an error\noccurs it is ignored and ``patchdb`` proceeds with the next one. On good databases like\nPostgreSQL and SQLite where even DDL statements are transactional, each statement is executed\nin a nested subtransaction, so subsequent errors do not ruin the effect of correctly applied\nprevious statements.\n\nAnother possible setting of this option is `skip`: in this case, whenever an error occurs the\neffect of the whole script is undone and it is considered as applied. For example, assuming\nthat the old version of ``SomeProcedure`` accepted a single argument and the new one requires\ntwo of them, you could do something like the following::\n\n .. patchdb:script:: Fix stored procedure signature\n :onerror: skip\n\n SELECT somecol FROM SomeProcedure(NULL, NULL)\n ;;\n ALTER PROCEDURE SomeProcedure(p_first INTEGER, p_second INTEGER)\n RETURNS (somecol INTEGER) AS\n BEGIN\n somecol = p_first * p_second;\n SUSPEND;\n END\n\n\nPatches\n~~~~~~~\n\nA `patch` is a particular flavour of script, one that specifies a `brings` or a `drops`\ndependency list. Imagine that the `example above`__ was the first version of the database, and\nthat the current version looks like the following::\n\n .. patchdb:script:: Create master table\n :revision: 2\n\n CREATE TABLE some_table (\n id INTEGER PRIMARY KEY,\n description VARCHAR(80),\n tt_id INTEGER\n )\n\nthat is, ``some_table`` now contains one more field, ``description``.\n\nWe need an upgrade path from the first revision of the table to the second::\n\n .. patchdb:script:: Add a description to the master table\n :depends: Create master table@1\n :brings: Create master table@2\n\n ALTER TABLE some_table ADD COLUMN description VARCHAR(80)\n\nWhen ``patchdb`` examines the database status, it will execute one *or* the other. If the\nscript `Create master table` isn't executed yet (for example when operating on a new database),\nit will take the former script (the one that creates the table from scratch). Otherwise, if\nthe database \"contains\" revision 1 (and not higher than 1) of the script, it will execute the\nlatter, bumping up the revision number.\n\n__ master-table_\n\n\nObsoleted patches\n+++++++++++++++++\n\nAnother peculiarity of this kind of scripts is that they may references `non existing scripts`\nwithout producing warnings or errors.\n\nThe rationale is that in the database evolution a given script may be removed, possibly\nreplaced by a different one by some succeeding patch. Consider the case when you once had a\ntable called ``customers``::\n\n .. patchdb:script:: Create table customers\n :revision: 2\n\n CREATE TABLE customers (\n id SERIAL PRIMARY KEY,\n name VARCHAR(80),\n street_address VARCHAR(80),\n city VARCHAR(80),\n telephone_number VARCHAR(80)\n )\n\n .. patchdb:script:: Add telephone number to customers table\n :depends: Create table customers@1\n :brings: Create table customers@2\n\n ALTER TABLE customers ADD COLUMN telephone_number VARCHAR(80)\n\nand then the need for multiple addresses arose thus you decided to split it in two distinct\nrelations, a ``persons`` and a ``person_addresses``::\n\n .. patchdb:script:: Create table persons\n\n CREATE TABLE persons (\n id SERIAL PRIMARY KEY,\n name VARCHAR(80)\n )\n\n .. patchdb:script:: Create table person_addresses\n :depends: Create table persons\n\n CREATE TABLE person_addresses (\n id SERIAL PRIMARY KEY,\n person_id INTEGER REFERENCES persons (id),\n street_address VARCHAR(80),\n city VARCHAR(80),\n telephone_number VARCHAR(80)\n )\n\n .. patchdb:script:: Migrate from customers to persons and person_addresses\n :depends:\n - Create table customers@2\n - Create table persons\n - Create table person_addresses\n :drops:\n - Create table customers\n - Add telephone number to customers table\n\n INSERT INTO persons (id, name) SELECT id, name FROM customers\n ;;\n INSERT INTO person_addresses (person_id, street_address, city, telephone_number)\n SELECT id, street_address, city, telephone_number\n FROM customers\n ;;\n DROP TABLE customers\n\nAt that point the script that introduced the original ``customers`` table disappeared from the\ndocumentation, but you most probably want to keep the migration patch around for a while, at\nleast until you are sure all your production databases got upgraded.\n\n.. hint:: In the HTML output, missing dependencies such as the ``\"Create table customers\"``\n above are marked with a ``strike`` class. In order to actually get a visual effect\n you need to define the ``CSS`` style for that, for example putting the following in\n the Sphinx configuration::\n\n html_context = {\n 'css_files': [\n '_static/theme_overrides.css',\n ],\n }\n\n and something like\n\n ::\n\n span.strike {\n text-decoration: line-through;\n }\n\n in ``_static/theme_overrides.css``.\n\n\nRun-always scripts\n~~~~~~~~~~~~~~~~~~\n\nYet another variant of scripts, which get applied always, **every time** ``patchdb`` is\nexecuted. This kind may be used to perform arbitrary operations, either at the start or at the\nend of the ``patchdb`` session::\n\n .. patchdb:script:: Say hello\n :language: python\n :always: first\n\n print(\"Hello!\")\n\n .. patchdb:script:: Say goodbye\n :language: python\n :always: last\n\n print(\"Goodbye!\")\n\n\nFake data domains\n+++++++++++++++++\n\nAs a special case that uses this kind of script, the following example illustrate an\n`approximation` of the `data domains` with MySQL, that lacks them::\n\n .. patchdb:script:: Define data domains (MySQL)\n :language: sql\n :mimetype: text/x-mysql\n :conditions: mysql\n :always: first\n\n CREATE DOMAIN bigint_t bigint\n ;;\n CREATE DOMAIN `Boolean_t` char(1)\n\n .. patchdb:script:: Create some table (MySQL)\n :language: sql\n :mimetype: text/x-mysql\n :conditions: mysql\n :always: first\n\n CREATE TABLE `some_table` (\n `ID` bigint_t NOT NULL,\n , `FLAG` `Boolean_t`\n\n , PRIMARY KEY (`ID`)\n )\n\n.. warning:: This is just a dirty hack, based on relatively simple search and replace: don't\n take it seriously, use a better database if you really need `data domains`!\n\n.. note:: This works also with SQLite.\n\n\nPlaceholders\n~~~~~~~~~~~~\n\nAnother feature is that the definition of the database, that is the collection of the scripts\nthat actually define its schema, may be splitted on multiple Sphinx environments: the use case\nis when you have a complex application, composed by multiple modules, each of them requiring\nits own set of DB objects.\n\nA script is considered a `placeholder` when it has an empty body: it won't be ever applied, but\ninstead its presence in the database will be asserted. In this way, one Sphinx environment\ncould contain the following script::\n\n .. patchdb:script:: Create table a\n\n CREATE TABLE a (\n id INTEGER NOT NULL PRIMARY KEY\n , value INTEGER\n )\n\nand another documentation set could extend that with::\n\n .. patchdb:script:: Create table a\n :description: Place holder\n\n .. patchdb:script:: Create unique index on value\n :depends: Create table a\n\n CREATE UNIQUE INDEX on_value ON a (value)\n\nThe second set can be applied **only** after the former one is.\n\n\nUsage\n-----\n\nCollecting patches\n~~~~~~~~~~~~~~~~~~\n\nTo use it, first of all you must register the extension within the Sphinx environment, adding\nthe full name of the package to the ``extensions`` list in the file ``conf.py``, for example::\n\n # Add any Sphinx extension module names here, as strings.\n extensions = ['metapensiero.sphinx.patchdb']\n\nThe other required bit of customization is the location of the `on disk scripts storage`,\ni.e. the path of the file that will contain the information about every found script: this is\nkept separated from the documentation itself because you will probably deploy it on production\nservers just to update their database.\n\n.. _storage formats:\n\n.. topic:: Storage formats\n\n If the filename ends with ``.json`` it will contain a ``JSON`` formatted array, if it ends\n with ``.yaml`` the information will be dumped in ``YAML``, if it ends with ``.axon`` the\n dump will be formatted using ``AXON``, otherwise it will be a Python ``pickle``. I usually\n prefer ``AXON``, ``JSON`` or ``YAML``, because those formats are more VCs friendly and open\n to human inspection. These days I tend to use ``AXON`` for this kind of things as it is\n slightly more readable and more VCs friendly than ``JSON``, while ``YAML`` is very slow.\n\nThe location may be set in the same ``conf.py`` as above, like::\n\n # Location of the external storage\n patchdb_storage = '\u2026/dbname.json'\n\nOtherwise, you can set it using the ``-D`` option of the ``sphinx-build`` command, so that you\ncan easily share its definition with other rules in a ``Makefile``. I usually put the following\nsnippet at the beginning of the ``Makefile`` created by ``sphinx-quickstart``::\n\n TOPDIR ?= ..\n STORAGE ?= $(TOPDIR)/database.json\n\n SPHINXOPTS = -D patchdb_storage=$(STORAGE)\n\nAt this point, executing the usual ``make html`` will update the scripts archive: that file\ncontains everything is needed to update the database either local or remote; in other words,\nrunning Sphinx (or even having it installed) is **not** required to update a database.\n\n\nUpdating the database\n~~~~~~~~~~~~~~~~~~~~~\n\nThe other side of the coin is managed by the ``patchdb`` tool, that digests the scripts archive\nand is able to determine which of the scripts are not already applied and eventually does that,\nin the right order.\n\nWhen your database does already exist and you are just starting using ``patchdb`` you may need\nto force the initial state with the following command::\n\n patchdb --assume-already-applied --postgresql \"dbname=test\" database.json\n\nthat will just update the `patchdb` table registering current revision of all the missing\nscripts, without executing them.\n\nYou can inspect what will be done, that is obtain the list of not already applied patches, with\na command like::\n\n patchdb --dry-run --postgresql \"dbname=test\" database.json\n\nThe `database.json` archive can be sent to the production machines (in some cases I put it in a\n*production* branch of the repository and use the version control tool to update the remote\nmachines, in other I simply used ``scp`` or ``rsync`` based solutions). Another way is to\ninclude it in some package and then use the syntax ``some.package:path/database.json``.\n\nThe scripts may even come from several different archives (see `placeholders`_ above)::\n\n patchdb --postgresql \"dbname=test\" app.db.base:pdb.json app.db.auth:pdb.json\n\n\nAutomatic backup\n~~~~~~~~~~~~~~~~\n\nIn particular in development mode, I find it useful to have a simple way of going back to a\nprevious state and retry the upgrade, either to test different upgrade paths or to fix silly\ntypos in the new patches.\n\nSince version 2.3 ``patchdb`` has a new option, ``--backups-dir``, that controls an automatic\nbackup facility: at each execution, before proceeding with applying missing patches,\n*regardless* whether there are any, by default it takes a backup of the current database and\nkeeps a simple index of these snapshots.\n\nThe option defaults to the system-wide temporary directory (usually ``/tmp`` on POSIX systems):\nif you you don't need the automatic backup (a reasonable production system should have a\ndifferent approach to taking such snapshots), specify ``None`` as argument to the option.\n\nWith the ``patchdb-states`` tool you obtain a list of the available snapshots, or restore any\nprevious one::\n\n $ patchdb-states list\n [lun 18 apr 2016 08:24:48 CEST] bc5c5527ece6f11da529858d5ac735a8 <create first table@1>\n [lun 18 apr 2016 10:27:11 CEST] 693fd245ad9e5f4de0e79549255fbd6e <update first table@1>\n\n $ patchdb-states restore --sqlite /tmp/quicktest.sqlite 693fd245ad9e5f4de0e79549255fbd6e\n [I] Creating patchdb table\n [I] Restored SQLite database /tmp/quicktest.sqlite from /tmp/693fd245ad9e5f4de0e79549255fbd6e\n\n $ patchdb-states clean -k 1\n Removed /tmp/bc5c5527ece6f11da529858d5ac735a8\n Kept most recent 1 snapshot\n\n\nSupported databases\n~~~~~~~~~~~~~~~~~~~\n\nAs of version 2, ``patchdb`` can operate on the following databases:\n\n* Firebird (requires fdb_)\n* MySQL (requires PyMySQL_ by default, see option ``--driver`` to select a different one)\n* PostgreSQL (requires psycopg2_)\n* SQLite (uses the standard library ``sqlite3`` module)\n\n.. _fdb: https://pypi.python.org/pypi/fdb\n.. _PyMySQL: https://pypi.python.org/pypi/PyMySQL\n.. _psycopg2: https://pypi.python.org/pypi/psycopg2\n.. _SQLAlchemy: http://www.sqlalchemy.org/\n\n\nExample development Makefile snippet\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nThe following is a snippet that I usually put in my outer ``Makefile``::\n\n export TOPDIR := $(CURDIR)\n DBHOST := localhost\n DBPORT := 5432\n DBNAME := dbname\n DROPDB := dropdb --host=$(DBHOST) --port=$(DBPORT) --if-exists\n CREATEDB := createdb --host=$(DBHOST) --port=$(DBPORT) --encoding=UTF8\n STORAGE := $(TOPDIR)/$(DBNAME).json\n DSN := host=$(DBHOST) port=$(DBPORT) dbname=$(DBNAME)\n PUP := $(PATCHDB) --postgresql=\"$(DSN)\" --log-file=$(DBNAME).log $(STORAGE)\n\n # Build the Sphinx documentation\n doc:\n $(MAKE) -C doc STORAGE=$(STORAGE) html\n\n $(STORAGE): doc\n\n # Show what is missing\n missing-patches: $(STORAGE)\n $(PUP) --dry-run\n\n # Upgrade the database to the latest revision\n database: $(STORAGE)\n $(PUP)\n\n # Remove current database and start from scratch\n scratch-database:\n $(DROPDB) $(DBNAME)\n $(CREATEDB) $(DBNAME)\n $(MAKE) database\n\n\nQuick example\n-------------\n\nThe following shell session illustrates the basics:\n\n.. code-block:: shell\n\n python3 -m venv patchdb-session\n cd patchdb-session\n source bin/activate\n pip install metapensiero.sphinx.patchdb[dev]\n yes n | sphinx-quickstart --project PatchDB-Quick-Test \\\n --author JohnDoe \\\n -v 1 --release 1 \\\n --language en \\\n --master index --suffix .rst \\\n --makefile --no-batchfile \\\n pdb-qt\n cd pdb-qt\n echo \"extensions = ['metapensiero.sphinx.patchdb']\" >> conf.py\n echo \"patchdb_storage = 'pdb-qt.json'\" >> conf.py\n echo \"\n .. patchdb:script:: My first script\n :depends: Yet another\n :language: python\n\n print('world!')\n\n .. patchdb:script:: Yet another\n :language: python\n\n print('Hello')\n \" >> index.rst\n make html\n patchdb --sqlite /tmp/pdb-qt.sqlite --dry-run pdb-qt.json\n\nAt the end you should get something like::\n\n Would apply script \"yet another@1\"\n Would apply script \"my first script@1\"\n 100% (2 of 2) |########################################| Elapsed Time: 0:00:00 Time: 0:00:00\n\nRemoving the ``--dry-run``::\n\n $ patchdb --sqlite /tmp/pdb-qt.sqlite pdb-qt.json\n Hello\n world!\n\n Done, applied 2 scripts\n 100% (2 of 2) |########################################| Elapsed Time: 0:00:00 Time: 0:00:00\n\nOnce again::\n\n $ patchdb --sqlite /tmp/pdb-qt.sqlite pdb-qt.json\n Done, applied 0 scripts\n\n\nChanges\\ [#]_\n-------------\n\n3.7 (2019-12-20)\n~~~~~~~~~~~~~~~~\n\n* Catch dependency error when a patch brings a script to a revision higher than its current\n\n\n3.6 (2019-12-19)\n~~~~~~~~~~~~~~~~\n\n* Now Python scripts receive a reference to the current patch manager, so they are able to\n execute arbitrary scripts already in the storage\n\n\n3.5 (2019-06-21)\n~~~~~~~~~~~~~~~~\n\n* Now it's an hard error when a patch brings an unknown script: when it does, it's either\n obsoleted or there is a typo somewhere\n\n\n3.4 (2019-03-31)\n~~~~~~~~~~~~~~~~\n\n* Nothing new, minor glitch in the release procedure\n\n\n3.3 (2019-03-31)\n~~~~~~~~~~~~~~~~\n\n* Lift the constraint on sqlparse version, allow use of recently released 0.3.0.\n\n\n3.2 (2018-03-03)\n~~~~~~~~~~~~~~~~\n\n* Use `python-rapidjson`__ if available\n\n __ https://pypi.org/project/python-rapidjson/\n\n\n3.1 (2017-11-30)\n~~~~~~~~~~~~~~~~\n\n* Fix glitch in the logic that determine whether a patch script is still valid\n\n* Use enlighten__ to show the progress bar: the ``--verbose`` option is gone, now is the\n default mode\n\n __ https://pypi.org/project/enlighten/\n\n\n3.0 (2017-11-06)\n~~~~~~~~~~~~~~~~\n\n* Python 3 only\\ [#]_\n\n* New execution logic, hopefully fixing circular dependencies error in case of multiple non\n trivial pending migrations\n\n\n.. [#] Previous changes are here__.\n\n __ https://gitlab.com/metapensiero/metapensiero.sphinx.patchdb/blob/master/OLDERCHANGES.rst\n\n.. [#] If you are still using Python 2, either stick with version 2.27, or fetch `this\n commit`__ from the repository.\n\n __ https://gitlab.com/metapensiero/metapensiero.sphinx.patchdb/commit/f9fc5f5d50a381eaf9f003d7006cc46382842c18",
"bugtrack_url": null,
"license": "GPLv3+",
"summary": "Extract scripts from a reST document and apply them in order.",
"version": "3.7",
"project_urls": {
"Homepage": "https://gitlab.com/metapensiero/metapensiero.sphinx.patchdb.git"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "fe674b5a00643683a21bf10f4de6ad77fd392591575530ab2eda67ce81ba8c88",
"md5": "93cb5775bcc58bbd52d30c9a4a233be6",
"sha256": "33b5ed81108362b15a838a35b658fca12580c70cd6498450fa3848a28d4a5e71"
},
"downloads": -1,
"filename": "metapensiero.sphinx.patchdb-3.7.tar.gz",
"has_sig": false,
"md5_digest": "93cb5775bcc58bbd52d30c9a4a233be6",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 81256,
"upload_time": "2019-12-20T10:04:06",
"upload_time_iso_8601": "2019-12-20T10:04:06.044828Z",
"url": "https://files.pythonhosted.org/packages/fe/67/4b5a00643683a21bf10f4de6ad77fd392591575530ab2eda67ce81ba8c88/metapensiero.sphinx.patchdb-3.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2019-12-20 10:04:06",
"github": false,
"gitlab": true,
"bitbucket": false,
"codeberg": false,
"gitlab_user": "metapensiero",
"gitlab_project": "metapensiero.sphinx.patchdb",
"lcname": "metapensiero.sphinx.patchdb"
}
JSON