commit 03ab222a9c2020975a3ec85bd98196978e4024b8
Author: Hubert depesz Lubaczewski <depesz@depesz.com>
Date: Sun, 22 Aug 2010 15:34:12 +0200
first commit
Diffstat:
8 files changed, 237 insertions(+), 0 deletions(-)
diff --git a/CHANGES b/CHANGES
@@ -0,0 +1,2 @@
+2010-08-22 :
+- Initial release to github.com : http://github.com/depesz/Versioning
diff --git a/LICENSE b/LICENSE
@@ -0,0 +1,34 @@
+LICENSE AND COPYRIGHT
+
+Copyright (C) 2010 Hubert depesz Lubaczewski
+
+This program is distributed under the (Revised) BSD License:
+L<http://www.opensource.org/licenses/bsd-license.php>
+
+Redistribution and use in source and binary forms, with or without
+modification, are permitted provided that the following conditions
+are met:
+
+* Redistributions of source code must retain the above copyright
+notice, this list of conditions and the following disclaimer.
+
+* Redistributions in binary form must reproduce the above copyright
+notice, this list of conditions and the following disclaimer in the
+documentation and/or other materials provided with the distribution.
+
+* Neither the name of Hubert depesz Lubaczewski's Organization
+nor the names of its contributors may be used to endorse or promote
+products derived from this software without specific prior written
+permission.
+
+THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
+"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
+LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
+OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
+SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
+LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
+DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
+THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
+OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
diff --git a/README b/README
@@ -0,0 +1,18 @@
+Versioning
+
+This project strives to provide simple way to manage changes to
+database.
+
+Instead of making changes on development server, then finding
+differences between production and development, deciding which ones
+should be installed on production, and finding a way to install them -
+you start with writing diffs themselves!
+
+More detailed description is (so far) in here:
+http://www.depesz.com/index.php/2010/08/22/versioning/
+
+To install versioning simply run install.versioning.sql in your database
+(all of them: production, stage, test, devel, ...).
+
+If you'd like to suggest new functionality or ask anything - please use
+contact form on http://www.depesz.com/
diff --git a/install.versioning.sql b/install.versioning.sql
@@ -0,0 +1,93 @@
+BEGIN;
+
+-- This file adds versioning support to database it will be loaded to.
+-- It requires that PL/pgSQL is already loaded - will raise exception otherwise.
+-- All versioning "stuff" (tables, functions) is in "_v" schema.
+
+-- All functions are defined as 'RETURNS SETOF INT4' to be able to make them to RETURN literaly nothing (0 rows).
+-- >> RETURNS VOID<< IS similar, but it still outputs "empty line" in psql when calling.
+CREATE SCHEMA _v;
+COMMENT ON SCHEMA _v IS 'Schema for versioning data and functionality.';
+
+CREATE TABLE _v.patches (
+ patch_name TEXT PRIMARY KEY,
+ applied_tsz TIMESTAMPTZ NOT NULL DEFAULT now(),
+ applied_by TEXT NOT NULL,
+ requires TEXT[],
+ conflicts TEXT[]
+);
+COMMENT ON TABLE _v.patches IS 'Contains information about what patches are currently applied on database.';
+COMMENT ON COLUMN _v.patches.patch_name IS 'Name of patch, has to be unique for every patch.';
+COMMENT ON COLUMN _v.patches.applied_tsz IS 'When the patch was applied.';
+COMMENT ON COLUMN _v.patches.applied_by IS 'Who applied this patch (PostgreSQL username)';
+COMMENT ON COLUMN _v.patches.requires IS 'List of patches that are required for given patch.';
+COMMENT ON COLUMN _v.patches.conflicts IS 'List of patches that conflict with given patch.';
+
+CREATE OR REPLACE FUNCTION _v.register_patch( IN in_patch_name TEXT, IN in_requirements TEXT[], in_conflicts TEXT[], OUT versioning INT4 ) RETURNS setof INT4 AS $$
+DECLARE
+ t_text TEXT;
+ t_text_a TEXT[];
+ i INT4;
+BEGIN
+ -- Thanks to this we know only one patch will be applied at a time
+ LOCK TABLE _v.patches IN EXCLUSIVE MODE;
+
+ SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name;
+ IF FOUND THEN
+ RAISE EXCEPTION 'Patch % is already applied!', in_patch_name;
+ END IF;
+
+ t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE patch_name = any( in_conflicts ) );
+ IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
+ RAISE EXCEPTION 'Versioning patches conflict. Conflicting patche(s) installed: %.', array_to_string( t_text_a, ', ' );
+ END IF;
+
+ IF array_upper( in_requirements, 1 ) IS NOT NULL THEN
+ t_text_a := '{}';
+ FOR i IN array_lower( in_requirements, 1 ) .. array_upper( in_requirements, 1 ) LOOP
+ SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_requirements[i];
+ IF NOT FOUND THEN
+ t_text_a := t_text_a || t_text;
+ END IF;
+ END LOOP;
+ IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
+ RAISE EXCEPTION 'Missing prerequisite(s): %.', array_to_string( t_text_a, ', ' );
+ END IF;
+ END IF;
+
+ INSERT INTO _v.patches (patch_name, applied_tsz, applied_by, requires, conflicts ) VALUES ( in_patch_name, now(), current_user, coalesce( in_requirements, '{}' ), coalesce( in_conflicts, '{}' ) );
+ RETURN;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[], TEXT[] ) IS 'Function to register patches in database. Raises exception if there are conflicts, prerequisites are not installed or the migration has already been installed.';
+
+CREATE OR REPLACE FUNCTION _v.register_patch( TEXT ) RETURNS setof INT4 AS $$
+ SELECT _v.register_patch( $1, NULL, NULL );
+$$ language sql;
+COMMENT ON FUNCTION _v.register_patch( TEXT ) IS 'Wrapper to allow registration of patches without requirements and conflicts.';
+
+CREATE OR REPLACE FUNCTION _v.unregister_patch( IN in_patch_name TEXT, OUT versioning INT4 ) RETURNS setof INT4 AS $$
+DECLARE
+ i INT4;
+ t_text_a TEXT[];
+BEGIN
+ -- Thanks to this we know only one patch will be applied at a time
+ LOCK TABLE _v.patches IN EXCLUSIVE MODE;
+
+ t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE in_patch_name = ANY( requires ) );
+ IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
+ RAISE EXCEPTION 'Cannot uninstall %, as it is required by: %.', in_patch_name, array_to_string( t_text_a, ', ' );
+ END IF;
+
+ DELETE FROM _v.patches WHERE patch_name = in_patch_name;
+ GET DIAGNOSTICS i = ROW_COUNT;
+ IF i < 1 THEN
+ RAISE EXCEPTION 'Patch % is not installed, so it can''t be uninstalled!', in_patch_name;
+ END IF;
+
+ RETURN;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.unregister_patch( TEXT ) IS 'Function to unregister patches in database. Dies if the patch is not registered, or if unregistering it would break dependencies.';
+
+COMMIT;
diff --git a/t/00-load.sql.inc b/t/00-load.sql.inc
@@ -0,0 +1,2 @@
+-- load pgtap - change next line to point to correct path for your system!
+\i /home/pgdba/work/share/postgresql/contrib/pgtap.sql
diff --git a/t/01-objects.sql b/t/01-objects.sql
@@ -0,0 +1,23 @@
+BEGIN;
+ -- load pgtap - change next line to point to correct path for your system!
+ \i t/00-load.sql.inc
+
+ SELECT plan(11);
+
+ SELECT has_schema( '_v', 'There should be schema _v for versioning to work.' );
+ SELECT has_table( '_v', 'patches', 'There should be _v.patches table for versioning to work.' );
+
+ SELECT has_column( '_v', 'patches', 'patch_name', '_v.patches should have patch_name column.' );
+ SELECT has_column( '_v', 'patches', 'applied_tsz', '_v.patches should have applied_tsz column.' );
+ SELECT has_column( '_v', 'patches', 'applied_by', '_v.patches should have applied_by column.' );
+ SELECT has_column( '_v', 'patches', 'requires', '_v.patches should have requires column.' );
+ SELECT has_column( '_v', 'patches', 'conflicts', '_v.patches should have conflicts column.' );
+
+ SELECT has_function( '_v', 'register_patch', ARRAY[ 'text', 'text[]', 'text[]' ], 'register_patch(text, text[], text[]) should exist to be able to register patches' );
+ SELECT has_function( '_v', 'register_patch', ARRAY[ 'text' ], 'register_patch(text) should exist to be able to register patches (in simpler way)' );
+ SELECT has_function( '_v', 'unregister_patch', ARRAY[ 'text' ], 'unregister_patch(text) should exist to be able to unregister patches that are no longer needed' );
+
+ SELECT is( ( SELECT count(*) FROM _v.patches ), 0::bigint, 'When running tests _v.patches table should be empty to prevent bad interactions between patches and tests.' );
+
+ROLLBACK;
+
diff --git a/t/02-functionality.sql b/t/02-functionality.sql
@@ -0,0 +1,59 @@
+BEGIN;
+ -- load pgtap - change next line to point to correct path for your system!
+ \i t/00-load.sql.inc
+
+ SELECT plan(11);
+
+ SELECT is( ( SELECT count(*) FROM _v.patches ), 0::bigint, 'When running tests _v.patches table should be empty to prevent bad interactions between patches and tests.' );
+
+ SELECT lives_ok(
+ $$SELECT _v.register_patch( 'first_patch' )$$,
+ 'Installation of patch without dependencies AND conflicts.'
+ );
+
+ SELECT results_eq(
+ 'SELECT patch_name, applied_tsz, applied_by, requires, conflicts FROM _v.patches',
+ $$SELECT 'first_patch'::text as patch_name, now() as applied_tsz, current_user::TEXT as applied_by, '{}'::TEXT[] as requires, '{}'::TEXT[] as conflicts$$,
+ 'Sanity check if patch is correctly saved.'
+ );
+
+ SELECT lives_ok(
+ $$SELECT _v.register_patch( 'second_patch', ARRAY[ 'first_patch' ], NULL )$$,
+ 'Installation of patch with dependencies.'
+ );
+ SELECT lives_ok(
+ $$SELECT _v.register_patch( 'third_patch', ARRAY[ 'first_patch', 'second_patch' ], ARRAY[ 'bad_patch' ] )$$,
+ 'Installation of patch with dependencies and conflict.'
+ );
+ SELECT throws_matching(
+ $$SELECT _v.register_patch( 'fourth_patch', ARRAY[ 'bad_patch' ], ARRAY[ 'another' ] )$$,
+ 'Missing prerequisite',
+ 'Installation of patch without meeting its requirements.'
+ );
+ SELECT throws_matching(
+ $$SELECT _v.register_patch( 'fifth_patch', NULL, ARRAY[ 'first_patch' ] )$$,
+ 'Versioning patches conflict.',
+ 'Installation of patch with conflicting patch installed'
+ );
+ SELECT throws_matching(
+ $$SELECT _v.register_patch( 'first_patch' )$$,
+ 'already applied',
+ 'Installation of patch that is already installed.'
+ );
+ SELECT throws_matching(
+ $$SELECT _v.unregister_patch( 'first_patch' )$$,
+ 'it is required',
+ 'De-installation of patch that is required BY something ELSE.'
+ );
+ SELECT throws_matching(
+ $$SELECT _v.unregister_patch( 'bad_patch' )$$,
+ 'is not installed',
+ 'De-installation of patch that is not installed'
+ );
+ SELECT lives_ok(
+ $$SELECT _v.unregister_patch( 'third_patch' )$$,
+ 'De-Installation of patch.'
+ );
+
+ROLLBACK;
+
diff --git a/uninstall.versioning.sql b/uninstall.versioning.sql
@@ -0,0 +1,6 @@
+BEGIN;
+
+-- This file removes versioning support from database.
+DROP SCHEMA _v CASCADE;
+
+COMMIT;
