NAME

SQL::PatchDAG - A minimal DB schema patch manager

SYNOPSIS

Code

 use DBI;
 use SQL::PatchDAG;
 my $dbh = DBI->connect( ... );

 # setup:
 my $applied = $dbh->selectcol_arrayref( 'SELECT name FROM schemapatch' );
 my $patches = SQL::PatchDAG->from( 'patches', applied => $applied );

 # consistency check prior to application start:
 $patches->die_if_not_matching;

 # application of missing patches:
 while ( my ( $name, $fn, $sql ) = $patches->get_next_unapplied ) {
   print $fn, "\n";
   $dbh->begin_work;
   $dbh->do( $sql );
   $dbh->do( 'INSERT INTO schemapatch (name) VALUES (?)', undef, $name );
   $dbh->commit;
 }

 # helper script for creating new patches:
 $patches->run( @ARGV );

patches/schemapatch.sql

 -- preceding-patches = schemapatch
 CREATE TABLE schemapatch ( name VARCHAR(255) PRIMARY KEY );

DESCRIPTION

This module manages a directory containing SQL files that must be run in a particular order. This order is specified implicitly by the contents of the files: each of them must contain a dependency declaration, which the module provides code to help maintain. This provides a merge-friendly way to introduce schema patches in a code base across multiple branches.

Patch application itself is up the caller. The module does not talk to a database.

INTERFACE

new

Takes a list of key/value pairs and returns an instance with that configuration.

Typically you will use the from constructor rather than calling new directly.

The following parameters are available:

dir

The name of the directory containing the SQL files.

binmode

The binmode to apply to filehandles when opening patch files.

Defaults to :unix.

applied

A reference to an array listing the names of the patches which have been applied.

Defaults to an empty array.

patches

A reference to hash of arrays, in which each key is the name of a known patch and its value is the list of patches it depends on.

Defaults to an empty hash.

from

Takes a directory name and a set of key/value pairs and returns an instance with that configuration. It will read the directory and parse the dependencies from each SQL file to populate the set of known patches.

Patches are expected to be have an .sql extension. The full basename of a patch is taken as its patch name.

Additionally, the directory may contain files with an .ignore extension, which is a convenience feature for switching branches during development. Normally if you create and apply a patch on one branch and then switch to another branch, your application will no longer start because the database contains a patch which is not in the patch directory on that branch. This is annoying if the application would work (mostly when the patch makes no incompatible changes to your schema and only adds things to it). To allow the application to start despite having applied the patch, you can create an ignore file with the same basename as the patch. When "from" finds such a file, an extraneous patch of the same name will be ignored rather than causing an error. (It is a good idea to add patches/*.ignore to your VCS ignore file to avoid accidentally committing these files.)

die_if_not_matching

Throws an error if the sets of applied and known patches are not the same.

get_next_unapplied

Returns either the name, filename and contents of the next patch to apply, or the empty list if there is no patch to apply.

Throws an error if there are extraneous patches.

create

Takes the name of a patch to create and a flag indicating whether to recreate an existing patch. Returns the path to the (re)created patch file.

An appropriate dependency declaration is computed and written to the file automatically.

When creating a new patch, the patch must not exist.

When recreating an existing patch, the patch must exist. Its dependecies are recomputed as if the patch had not existed and are rewritten to the file, but the rest of its contents is preserved.

run

Takes a list of paramters, and either calls "open" or "create" accordingly and then execs $EDITOR on the path returned, or else outputs a usage message and exits.

You would normally pass @ARGV to this method.

patches

Returns the list of known patches.

applied

Returns the list of applied patches.

grep_unknown

Takes and returns a list of patch names, filtering out the ones which have been found in the patch directory.

grep_unapplied

Takes and returns a list of patch names, filtering out the ones which have already been applied.

deps_of

Takes a patch name and returns its dependencies as a list.

dir

Returns the value of the dir attribute.

binmode

Returns the value of the binmode attribute.

readdir

Reads the patch directory and returns its contents as a list.

open

Takes the name of a patch to open and a flag indicating whether to open it read-only or read/write. Returns the path to the patch and a filehandle with the configured binmode applied.

The filename must consist of only [a-z0-9_-] and may not begin with a [-].

When opening a patch read-only, it must already exist; when opening it read-write, it may be created.

AUTHOR

Aristotle Pagaltzis <pagaltzis@gmx.de>

COPYRIGHT AND LICENSE

This software is copyright (c) 2023 by Aristotle Pagaltzis.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm SQL::PatchDAG

perl -MCPAN -e shell
install SQL::PatchDAG