install.versioning.sql (6967B)
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 IF NOT EXISTS _v; 10 COMMENT ON SCHEMA _v IS 'Schema for versioning data and functionality.'; 11 12 13 CREATE TABLE IF NOT EXISTS _v.patches ( 14 patch_name TEXT PRIMARY KEY, 15 applied_tsz TIMESTAMPTZ NOT NULL DEFAULT now(), 16 applied_by TEXT NOT NULL, 17 requires TEXT[], 18 conflicts TEXT[] 19 ); 20 COMMENT ON TABLE _v.patches IS 'Contains information about what patches are currently applied on database.'; 21 COMMENT ON COLUMN _v.patches.patch_name IS 'Name of patch, has to be unique for every patch.'; 22 COMMENT ON COLUMN _v.patches.applied_tsz IS 'When the patch was applied.'; 23 COMMENT ON COLUMN _v.patches.applied_by IS 'Who applied this patch (PostgreSQL username)'; 24 COMMENT ON COLUMN _v.patches.requires IS 'List of patches that are required for given patch.'; 25 COMMENT ON COLUMN _v.patches.conflicts IS 'List of patches that conflict with given patch.'; 26 27 28 CREATE OR REPLACE FUNCTION _v.register_patch( IN in_patch_name TEXT, IN in_requirements TEXT[] DEFAULT NULL, in_conflicts TEXT[] DEFAULT NULL, OUT versioning INT4 ) RETURNS setof INT4 AS $$ 29 DECLARE 30 t_text TEXT; 31 t_text_a TEXT[]; 32 i INT4; 33 BEGIN 34 -- Thanks to this we know only one patch will be applied at a time 35 LOCK TABLE _v.patches IN EXCLUSIVE MODE; 36 37 SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name; 38 IF FOUND THEN 39 RAISE EXCEPTION 'Patch % is already applied!', in_patch_name; 40 END IF; 41 42 t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE patch_name = any( in_conflicts ) ); 43 IF array_upper( t_text_a, 1 ) IS NOT NULL THEN 44 RAISE EXCEPTION 'Versioning patches conflict. Conflicting patche(s) installed: %.', array_to_string( t_text_a, ', ' ); 45 END IF; 46 47 IF array_upper( in_requirements, 1 ) IS NOT NULL THEN 48 t_text_a := '{}'; 49 FOR i IN array_lower( in_requirements, 1 ) .. array_upper( in_requirements, 1 ) LOOP 50 SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_requirements[i]; 51 IF NOT FOUND THEN 52 t_text_a := t_text_a || in_requirements[i]; 53 END IF; 54 END LOOP; 55 IF array_upper( t_text_a, 1 ) IS NOT NULL THEN 56 RAISE EXCEPTION 'Missing prerequisite(s): %.', array_to_string( t_text_a, ', ' ); 57 END IF; 58 END IF; 59 60 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, '{}' ) ); 61 RETURN; 62 END; 63 $$ language plpgsql; 64 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.'; 65 66 67 CREATE OR REPLACE FUNCTION _v.try_register_patch( IN in_patch_name TEXT, IN in_requirements TEXT[] DEFAULT NULL, in_conflicts TEXT[] DEFAULT NULL ) RETURNS BOOL AS $$ 68 declare 69 temptext TEXT; 70 BEGIN 71 PERFORM _v.register_patch( in_patch_name, in_requirements, in_conflicts ); 72 RETURN true; 73 EXCEPTION 74 WHEN raise_exception THEN 75 GET STACKED DIAGNOSTICS temptext := MESSAGE_TEXT; 76 RAISE WARNING '%', temptext; 77 RETURN false; 78 END; 79 $$ language plpgsql; 80 COMMENT ON FUNCTION _v.try_register_patch( TEXT, TEXT[], TEXT[] ) IS 'Function to register patches in database, returns true if it worked, false otherwise.'; 81 82 83 CREATE OR REPLACE FUNCTION _v.unregister_patch( IN in_patch_name TEXT, OUT versioning INT4 ) RETURNS setof INT4 AS $$ 84 DECLARE 85 i INT4; 86 t_text_a TEXT[]; 87 BEGIN 88 -- Thanks to this we know only one patch will be applied at a time 89 LOCK TABLE _v.patches IN EXCLUSIVE MODE; 90 91 t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE in_patch_name = ANY( requires ) ); 92 IF array_upper( t_text_a, 1 ) IS NOT NULL THEN 93 RAISE EXCEPTION 'Cannot uninstall %, as it is required by: %.', in_patch_name, array_to_string( t_text_a, ', ' ); 94 END IF; 95 96 DELETE FROM _v.patches WHERE patch_name = in_patch_name; 97 GET DIAGNOSTICS i = ROW_COUNT; 98 IF i < 1 THEN 99 RAISE EXCEPTION 'Patch % is not installed, so it can''t be uninstalled!', in_patch_name; 100 END IF; 101 102 RETURN; 103 END; 104 $$ language plpgsql; 105 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.'; 106 107 108 CREATE OR REPLACE FUNCTION _v.assert_patch_is_applied( IN in_patch_name TEXT ) RETURNS TEXT as $$ 109 DECLARE 110 t_text TEXT; 111 BEGIN 112 SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name; 113 IF NOT FOUND THEN 114 RAISE EXCEPTION 'Patch % is not applied!', in_patch_name; 115 END IF; 116 RETURN format('Patch %s is applied.', in_patch_name); 117 END; 118 $$ language plpgsql; 119 COMMENT ON FUNCTION _v.assert_patch_is_applied( TEXT ) IS 'Function that can be used to make sure that patch has been applied.'; 120 121 122 CREATE OR REPLACE FUNCTION _v.assert_user_is_superuser() RETURNS TEXT as $$ 123 DECLARE 124 v_super bool; 125 BEGIN 126 SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user; 127 IF v_super THEN 128 RETURN 'assert_user_is_superuser: OK'; 129 END IF; 130 RAISE EXCEPTION 'Current user is not superuser - cannot continue.'; 131 END; 132 $$ language plpgsql; 133 COMMENT ON FUNCTION _v.assert_user_is_superuser() IS 'Function that can be used to make sure that patch is being applied using superuser account.'; 134 135 136 CREATE OR REPLACE FUNCTION _v.assert_user_is_not_superuser() RETURNS TEXT as $$ 137 DECLARE 138 v_super bool; 139 BEGIN 140 SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user; 141 IF v_super THEN 142 RAISE EXCEPTION 'Current user is superuser - cannot continue.'; 143 END IF; 144 RETURN 'assert_user_is_not_superuser: OK'; 145 END; 146 $$ language plpgsql; 147 COMMENT ON FUNCTION _v.assert_user_is_not_superuser() IS 'Function that can be used to make sure that patch is being applied using normal (not superuser) account.'; 148 149 150 CREATE OR REPLACE FUNCTION _v.assert_user_is_one_of(VARIADIC p_acceptable_users TEXT[] ) RETURNS TEXT as $$ 151 DECLARE 152 BEGIN 153 IF current_user = any( p_acceptable_users ) THEN 154 RETURN 'assert_user_is_one_of: OK'; 155 END IF; 156 RAISE EXCEPTION 'User is not one of: % - cannot continue.', p_acceptable_users; 157 END; 158 $$ language plpgsql; 159 COMMENT ON FUNCTION _v.assert_user_is_one_of(TEXT[]) IS 'Function that can be used to make sure that patch is being applied by one of defined users.'; 160 161 162 COMMIT;