The initial dbdeploy eclass.

dbdeploy.eclass (text/plain), 10.68 KB, created by Bishop Bettini on 2010-04-14 21:33:43 UTC

# vi: sts ts=4 sw=4 tw=132 fdm=marker:
# Copyright 2003-2010 ideacode, Inc.
# \seealso https://ideacode.fogbugz.com/default.asp?W1
#

# dbdeploy is a strategy for migrating a database from earlier to later versions through the use of delta SQL files.  Originally
# implemented for Java, with a later evolution in Phing, the strategy is documented at:
# \see http://dbdeploy.com/documentation/
#
# Each delta has a change number, and when that numbered changed is applied to a given DB, the number is remembered in a changelog
# table.  Thus, a change will be applied only once, to a given database, and the history of applied changes can be easily queried
# within the database itself.
#
# {{{ Usage
#
# This eclass has one public function -- dbdeploy -- that has two primary functions:
# A. Output SQL to list changes applied to a database
# B. Output SQL to apply changes to a database, specifically:
#    1. Takes the list of already applied change numbers, one per line, on STDIN
#    2. Looks through a directory containing SQL files and figures out which need to be run
#    3. Outputs the SQL to apply on STDOUT
#
# Taking these together, you use this class as follows:
# \code
# einfo "Migrating database using delta files"
# eindent
#     dbdeploy --list                                       | # get the list of already applied deltas
#     mysql -u "me" --password="my password" -D "myDB" -NB  |
#     dbdeploy --apply "mysql5" "/path/to/the/deltas/"      | # get the SQL for the unapplied changes
#     mysql -u "me" --password="my password" -D "myDB" -NB
#     assert "Error migrating database."
# eoutdent
# \endcode
#
# This class is not concerned with the mechanics of reading from, or writing to, your database.  You are responsible for piping
# the output of this command to your database, using the correct flags for the command-line interface (CLI) to your database.
# For proper operation, your database CLI must:
#    * Print results using tab as the column separator, with each row on a new line.
#    * Omit column headers.
#
# If your database CLI doesn't support these operations directly, then you should use UNIX utilities to transform the output into
# this format.
#
# }}}
# {{{ Requirements of the delta files
#
# Your delta files must match the file glob [0-9]*.sql
# That is, a file name starting with a number and ending in ".sql".  Leading numbers are interpreted as the change number for
# that particular delta.  Anything from the last number to the ".sql" is interpreted as the "description" for that change.
# For example:
#   1234 Add foo column to bar table.sql
# Has a change number of "1234" and a description of "Add foo column to bar table".
#
# You must include a non-word character after the change number.  This can be simply the dot from the extension (eg, "1234.sql")
# or it can be a space, hyphen, or other character (eg, "1234 This is my change.sql").  Non-word characters are any character
# other than a letter, number, or underscore.
#
# Deltas that do not meet this requirement will *not* be applied.
#
# }}}
# {{{ Rollback and undo
#
# Each delta is wrapped in a transactional procedure, and the procedure is run and dropped.  If any error, warning, or non-success
# condition occurs during the procedure, a rollback happens.  If your database or tables do not support transactions (eg, MyISAM
# tables in a MySQL DB), then the rollback will not work.  In that case, you supply an "undo" section in your deltas.
#
# The dbdeploy method to supply an undo is via the --//@UNDO tag.  All lines up to this marker, or end of file, whichever comes
# first, are treated as the "do".  All lines after this tag are considered the "undo".  Each undo is also wrapped in a procedure
# run and dropped.  The procedure looks for the rollback condition and only actual runs the undo when needed.
#
# In the case that a rollback/undo happens, the deployment immediately stops and an error is raised, indicating the change number
# that had the problem.
#
# }}}
# {{{ Delta sets
#
# You may find need to separate changes into distinct groups.  A common separation is that of DDL and DML changes.  Changes in
# the DDL (Data Definition Language) delta set alter the *structure* of the database.  Changes in the DML (Data Manipulation
# Language) delta set alter the *contents* of the database.
# 
# To change the delta set, pass the -d or --deltaset flag to dbdeploy, giving it the delta set name to use, for example:
# \code
# dbdeploy --apply --deltaset "TestData" /path/to/test/data/deltas/
# \endcode
#
# If you do not pass --deltaset, a delta set name of "Main" is assumed.
#
# Note that change numbers are unique within a given delta set.
#
# }}}

# {{{ dbdeploy()

function dbdeploy() {
	# {{{ usage()

	function usage() {
		[ -n "$1" ] && eerror $1
		echo "
dbdeploy is an eclass for migrating a database from earlier to later versions
through the use of delta SQL files.

USAGE: $0 --list
       $0 --apply [--deltaset <name>] dbms /path/to/deltas/

WHERE:
	dbms is the type of database you're using, and can be one of:
	    * mysql5

This eclass implements two functions:
A. Output SQL to list changes applied to a database
B. Output SQL to apply changes to a database, specifically:
   1. Takes the list of already applied change numbers, one per line, on STDIN
   2. Looks through a directory containing SQL files and figures out which need to be run
   3. Outputs the SQL to apply on STDOUT

This eclass is not concerned with the mechanics of reading from, or writing to,
your database.  You are responsible for piping to a helper program to access your
database.  For example:
	dbdeploy --list                                     |
	mysql -u 'me' --password='my password' -D myDB -NB  |
	dbdeploy --apply mysql5 ./deltas/                   |
	mysql -u 'me' --password='my password' -D myDB -NB

" >&2
	}

	# }}}
	# {{{ list()

	function list() {
		echo "SELECT change_number FROM changelog WHERE delta_set='$1';"
	}

	# }}}
	# {{{ apply()

	function apply() {
		local deltaSet=$1
		local dbmsType=$2
		local deltaDir=$3

		# check the delta directory
		if [ -z "$deltaDir" ]; then
			usage "Must supply a directory containing SQL delta files as first non-option argument"
			return 1
		elif [ ! -d "$deltaDir" -o ! -r "$deltaDir" ]; then
			usage "Given argument is not a directory"
			return 1
		fi

		# check the dbms type
		if [ -z "$dbmsType" ]; then
			usage "Must indicate the DBMS type"
			return 1
		fi

		# do any dbms initialization
		local func="_dbdeploy_${dbmsType}_setup"
		type $func 2>/dev/null | grep -q 'is a function' && $func

		# read the list of already finished change numbers, sorting out to just the unique numbers
		local appliedCN=$(cat | grep '^[0-9][0-9]*$' | sort -u)
		local filterOptions="-v"
		if [ -z "$appliedCN" ]; then
			filterOptions=""
		fi

		# walk through all the unique delta files, skipping any that are finished
		command ls -1 "$deltaDir"/[0-9]*.sql         | # files starting with a number
		command sed "s+$deltaDir/++g"                | # with the full path removed
		command grep $filterOptions -wF "$appliedCN" | # only those files that haven't already been applied
		command sort -u                              | # no duplicates
		command tr '\n' '\0'                         | # and to protect our files that have white space in them
		while read -d $'\0' newDeltaFile
		do
			# this delta file has not been applied

			# define some variables we'll be using
			local who=$(whoami)
			local num=$(echo "$newDeltaFile" | command grep -o '^[0-9][0-9]*')
			local dsc=$(echo "$newDeltaFile" | command sed -e 's/^[0-9]*//g' -e 's/\.sql$//g' -e 's/^\s*//g' -e 's/\s*$//g')

			# get the do (all before --//@UNDO or end of file, whichever comes first) and undo part (all after --//@UNDO) of the delta
			local doPart=$(command cat "$deltaDir"/"$newDeltaFile" | command sed -n '1,/^--\/\/@UNDO/p' | command sed '/^--\/\/@UNDO/d')
			local undoPart=$(command cat "$deltaDir"/"$newDeltaFile" | command sed -n '/^--\/\/@UNDO/,$p' | command sed '/^--\/\/@UNDO/d')

			# delegate to the dbms deployment
			_dbdeploy_${dbmsType}_delta "$newDeltaFile" "$num" "$dsc" "$who" "$doPart" "$undoPart"
		done

		# do any dbms finalization
		local func="_dbdeploy_${dbmsType}_teardown"
		type $func 2>/dev/null | grep -q 'is a function' && $func
	}

	# }}}

	local action=''
	local deltaSet='Main'
	local deltaDir=''
	local dbmsType=''

	# walk through the command line
	while [ 0 -lt $# ]; do
		case $1 in
		# action
		'--apply')
			action='apply'
		    ;;
		'--list')
			action='list'
		    ;;

		# delta set
		'--deltaset')
			shift
			if [ 0 -lt $# ]; then
				deltaSet=$1
				shift
			else
				eerror "Delta set option (--deltaset) requires one argument"
				return 1
			fi
		    ;;

		# dbms type and directory
		*) 
			case $1 in
			# add matching dbms here, separated by pipe ('|')
			'mysql5')
			    dbmsType=$1
			    ;;
			*)
				deltaDir=$1
				;;
			esac
		    ;;
		esac
		shift
	done

	# dispatch based on action
	if [ "apply" = "$action" ]; then
		apply "$deltaSet" "$dbmsType" "$deltaDir"
	elif [ "list" = "$action" ]; then
		list "$deltaSet"
	else
		usage "You must supply an action: either --list to list applied changes, or --apply to apply changes"
		return 1
	fi

	return 0
}

# }}}
# {{{ mysql5 implementation
function _dbdeploy_mysql5_setup() {
	echo "
CREATE TABLE IF NOT EXISTS changelog (
  change_number BIGINT       NOT NULL,
  delta_set     VARCHAR(10)  NOT NULL,
  start_dt      TIMESTAMP    NOT NULL,
  complete_dt   TIMESTAMP        NULL,
  applied_by    VARCHAR(100) NOT NULL,
  description   VARCHAR(500)     NULL,

  CONSTRAINT PKchangelog PRIMARY KEY (change_number, delta_set)
);
"
	return 0
}
function _dbdeploy_mysql5_delta() {
	local deltaFile=$1
	local num=$2
	local dsc=$3
	local who=$4
	local doPart=$5
	local undoPart=$6

	echo "-- BEGIN: Delta SQL from file $deltaFile"
	if [ -n "$doPart" ]; then
		echo "DELIMITER //
DROP PROCEDURE IF EXISTS doDelta$num //
CREATE PROCEDURE doDelta$num()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
  BEGIN
	ROLLBACK;
    DELETE FROM changelog WHERE change_number=$num AND delta_set='$deltaSet';
	$undoPart
	SELECT 'There was an error in change number $num ($deltaFile). Had to undo it and subsequent changes were not run.' AS 'Error';
	CALL __ignoreThisError_itIsBecauseThereWasAnErrorInChangeNumber$num;
  END;
  START TRANSACTION;
  INSERT INTO changelog (change_number, delta_set, applied_by, description) VALUES ($num, '$deltaSet', CONCAT('$who using ', USER()), '$dsc');

  $doPart

  UPDATE changelog SET complete_dt=NOW() WHERE change_number=$num AND delta_set='$deltaSet';
  COMMIT;
  SELECT 'Applied change number $num' AS 'Info';
END //
DELIMITER ;
CALL doDelta$num;
DROP PROCEDURE doDelta$num;"
	else
		echo "-- No action found in file $deltaFile"
	fi
	echo "-- END: Delta SQL from file $deltaFile"
	echo ""

	return 0
}

# }}}