Skip to content
Newer
Older
100644 98 lines (83 sloc) 4.62 KB
03ab222 @depesz first commit
authored
1 BEGIN;
2
3 -- This file adds versioning support to database it will be loaded to.
4 -- It requires that PL/pgSQL is already loaded - will raise exception otherwise.
5 -- All versioning "stuff" (tables, functions) is in "_v" schema.
6
7 -- All functions are defined as 'RETURNS SETOF INT4' to be able to make them to RETURN literaly nothing (0 rows).
8 -- >> RETURNS VOID<< IS similar, but it still outputs "empty line" in psql when calling.
9 CREATE SCHEMA _v;
10 COMMENT ON SCHEMA _v IS 'Schema for versioning data and functionality.';
11
12 CREATE TABLE _v.patches (
13 patch_name TEXT PRIMARY KEY,
14 applied_tsz TIMESTAMPTZ NOT NULL DEFAULT now(),
15 applied_by TEXT NOT NULL,
16 requires TEXT[],
17 conflicts TEXT[]
18 );
19 COMMENT ON TABLE _v.patches IS 'Contains information about what patches are currently applied on database.';
20 COMMENT ON COLUMN _v.patches.patch_name IS 'Name of patch, has to be unique for every patch.';
21 COMMENT ON COLUMN _v.patches.applied_tsz IS 'When the patch was applied.';
22 COMMENT ON COLUMN _v.patches.applied_by IS 'Who applied this patch (PostgreSQL username)';
23 COMMENT ON COLUMN _v.patches.requires IS 'List of patches that are required for given patch.';
24 COMMENT ON COLUMN _v.patches.conflicts IS 'List of patches that conflict with given patch.';
25
26 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 $$
27 DECLARE
28 t_text TEXT;
29 t_text_a TEXT[];
30 i INT4;
31 BEGIN
32 -- Thanks to this we know only one patch will be applied at a time
33 LOCK TABLE _v.patches IN EXCLUSIVE MODE;
34
35 SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name;
36 IF FOUND THEN
37 RAISE EXCEPTION 'Patch % is already applied!', in_patch_name;
38 END IF;
39
40 t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE patch_name = any( in_conflicts ) );
41 IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
42 RAISE EXCEPTION 'Versioning patches conflict. Conflicting patche(s) installed: %.', array_to_string( t_text_a, ', ' );
43 END IF;
44
45 IF array_upper( in_requirements, 1 ) IS NOT NULL THEN
46 t_text_a := '{}';
47 FOR i IN array_lower( in_requirements, 1 ) .. array_upper( in_requirements, 1 ) LOOP
48 SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_requirements[i];
49 IF NOT FOUND THEN
50 t_text_a := t_text_a || t_text;
51 END IF;
52 END LOOP;
53 IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
54 RAISE EXCEPTION 'Missing prerequisite(s): %.', array_to_string( t_text_a, ', ' );
55 END IF;
56 END IF;
57
58 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, '{}' ) );
59 RETURN;
60 END;
61 $$ language plpgsql;
62 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.';
63
30ec6d1 @theory Add alias with dependencies but not conflicts.
theory authored
64 CREATE OR REPLACE FUNCTION _v.register_patch( TEXT, TEXT[] ) RETURNS setof INT4 AS $$
65 SELECT _v.register_patch( $1, $2, NULL );
66 $$ language sql;
67 COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[] ) IS 'Wrapper to allow registration of patches without conflicts.';
03ab222 @depesz first commit
authored
68 CREATE OR REPLACE FUNCTION _v.register_patch( TEXT ) RETURNS setof INT4 AS $$
69 SELECT _v.register_patch( $1, NULL, NULL );
70 $$ language sql;
71 COMMENT ON FUNCTION _v.register_patch( TEXT ) IS 'Wrapper to allow registration of patches without requirements and conflicts.';
72
73 CREATE OR REPLACE FUNCTION _v.unregister_patch( IN in_patch_name TEXT, OUT versioning INT4 ) RETURNS setof INT4 AS $$
74 DECLARE
75 i INT4;
76 t_text_a TEXT[];
77 BEGIN
78 -- Thanks to this we know only one patch will be applied at a time
79 LOCK TABLE _v.patches IN EXCLUSIVE MODE;
80
81 t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE in_patch_name = ANY( requires ) );
82 IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
83 RAISE EXCEPTION 'Cannot uninstall %, as it is required by: %.', in_patch_name, array_to_string( t_text_a, ', ' );
84 END IF;
85
86 DELETE FROM _v.patches WHERE patch_name = in_patch_name;
87 GET DIAGNOSTICS i = ROW_COUNT;
88 IF i < 1 THEN
89 RAISE EXCEPTION 'Patch % is not installed, so it can''t be uninstalled!', in_patch_name;
90 END IF;
91
92 RETURN;
93 END;
94 $$ language plpgsql;
95 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.';
96
97 COMMIT;
Something went wrong with that request. Please try again.