diff options
Diffstat (limited to 'src/pq/versioning.sql')
-rw-r--r-- | src/pq/versioning.sql | 293 |
1 files changed, 293 insertions, 0 deletions
diff --git a/src/pq/versioning.sql b/src/pq/versioning.sql new file mode 100644 index 000000000..116f409b7 --- /dev/null +++ b/src/pq/versioning.sql | |||
@@ -0,0 +1,293 @@ | |||
1 | -- LICENSE AND COPYRIGHT | ||
2 | -- | ||
3 | -- Copyright (C) 2010 Hubert depesz Lubaczewski | ||
4 | -- | ||
5 | -- This program is distributed under the (Revised) BSD License: | ||
6 | -- L<http://www.opensource.org/licenses/bsd-license.php> | ||
7 | -- | ||
8 | -- Redistribution and use in source and binary forms, with or without | ||
9 | -- modification, are permitted provided that the following conditions | ||
10 | -- are met: | ||
11 | -- | ||
12 | -- * Redistributions of source code must retain the above copyright | ||
13 | -- notice, this list of conditions and the following disclaimer. | ||
14 | -- | ||
15 | -- * Redistributions in binary form must reproduce the above copyright | ||
16 | -- notice, this list of conditions and the following disclaimer in the | ||
17 | -- documentation and/or other materials provided with the distribution. | ||
18 | -- | ||
19 | -- * Neither the name of Hubert depesz Lubaczewski's Organization | ||
20 | -- nor the names of its contributors may be used to endorse or | ||
21 | -- promote products derived from this software without specific | ||
22 | -- prior written permission. | ||
23 | -- | ||
24 | -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" | ||
25 | -- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE | ||
26 | -- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE | ||
27 | -- DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE | ||
28 | -- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL | ||
29 | -- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR | ||
30 | -- SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER | ||
31 | -- CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, | ||
32 | -- OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE | ||
33 | -- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. | ||
34 | -- | ||
35 | -- Code origin: https://gitlab.com/depesz/Versioning/blob/master/install.versioning.sql | ||
36 | -- | ||
37 | -- | ||
38 | -- # NAME | ||
39 | -- | ||
40 | -- **Versioning** - simplistic take on tracking and applying changes to databases. | ||
41 | -- | ||
42 | -- # DESCRIPTION | ||
43 | -- | ||
44 | -- This project strives to provide simple way to manage changes to | ||
45 | -- database. | ||
46 | -- | ||
47 | -- Instead of making changes on development server, then finding | ||
48 | -- differences between production and development, deciding which ones | ||
49 | -- should be installed on production, and finding a way to install them - | ||
50 | -- you start with writing diffs themselves! | ||
51 | -- | ||
52 | -- # INSTALLATION | ||
53 | -- | ||
54 | -- To install versioning simply run install.versioning.sql in your database | ||
55 | -- (all of them: production, stage, test, devel, ...). | ||
56 | -- | ||
57 | -- # USAGE | ||
58 | -- | ||
59 | -- In your files with patches to database, put whole logic in single | ||
60 | -- transaction, and use \_v.\* functions - usually \_v.register_patch() at | ||
61 | -- least to make sure everything is OK. | ||
62 | -- | ||
63 | -- For example. Let's assume you have patch files: | ||
64 | -- | ||
65 | -- ## 0001.sql: | ||
66 | -- | ||
67 | -- ``` | ||
68 | -- create table users (id serial primary key, username text); | ||
69 | -- ``` | ||
70 | -- | ||
71 | -- ## 0002.sql: | ||
72 | -- | ||
73 | -- ``` | ||
74 | -- insert into users (username) values ('depesz'); | ||
75 | -- ``` | ||
76 | -- To change it to use versioning you would change the files, to this | ||
77 | -- state: | ||
78 | -- | ||
79 | -- 0000.sql: | ||
80 | -- | ||
81 | -- ``` | ||
82 | -- BEGIN; | ||
83 | -- select _v.register_patch('000-base', NULL, NULL); | ||
84 | -- create table users (id serial primary key, username text); | ||
85 | -- COMMIT; | ||
86 | -- ``` | ||
87 | -- | ||
88 | -- ## 0002.sql: | ||
89 | -- | ||
90 | -- ``` | ||
91 | -- BEGIN; | ||
92 | -- select _v.register_patch('001-users', ARRAY['000-base'], NULL); | ||
93 | -- insert into users (username) values ('depesz'); | ||
94 | -- COMMIT; | ||
95 | -- ``` | ||
96 | -- | ||
97 | -- This will make sure that patch 001-users can only be applied after | ||
98 | -- 000-base. | ||
99 | -- | ||
100 | -- # AVAILABLE FUNCTIONS | ||
101 | -- | ||
102 | -- ## \_v.register_patch( TEXT ) | ||
103 | -- | ||
104 | -- Registers named patch, or dies if it is already registered. | ||
105 | -- | ||
106 | -- Returns integer which is id of patch in \_v.patches table - only if it | ||
107 | -- succeeded. | ||
108 | -- | ||
109 | -- ## \_v.register_patch( TEXT, TEXT[] ) | ||
110 | -- | ||
111 | -- Same as \_v.register_patch( TEXT ), but checks is all given patches (given as | ||
112 | -- array in second argument) are already registered. | ||
113 | -- | ||
114 | -- ## \_v.register_patch( TEXT, TEXT[], TEXT[] ) | ||
115 | -- | ||
116 | -- Same as \_v.register_patch( TEXT, TEXT[] ), but also checks if there are no conflicts with preexisting patches. | ||
117 | -- | ||
118 | -- Third argument is array of names of patches that conflict with current one. So | ||
119 | -- if any of them is installed - register_patch will error out. | ||
120 | -- | ||
121 | -- ## \_v.unregister_patch( TEXT ) | ||
122 | -- | ||
123 | -- Removes information about given patch from the versioning data. | ||
124 | -- | ||
125 | -- It doesn't remove objects that were created by this patch - just removes | ||
126 | -- metainformation. | ||
127 | -- | ||
128 | -- ## \_v.assert_user_is_superuser() | ||
129 | -- | ||
130 | -- Make sure that current patch is being loaded by superuser. | ||
131 | -- | ||
132 | -- If it's not - it will raise exception, and break transaction. | ||
133 | -- | ||
134 | -- ## \_v.assert_user_is_not_superuser() | ||
135 | -- | ||
136 | -- Make sure that current patch is not being loaded by superuser. | ||
137 | -- | ||
138 | -- If it is - it will raise exception, and break transaction. | ||
139 | -- | ||
140 | -- ## \_v.assert_user_is_one_of(TEXT, TEXT, ... ) | ||
141 | -- | ||
142 | -- Make sure that current patch is being loaded by one of listed users. | ||
143 | -- | ||
144 | -- If ```current_user``` is not listed as one of arguments - function will raise | ||
145 | -- exception and break the transaction. | ||
146 | |||
147 | BEGIN; | ||
148 | |||
149 | -- This file adds versioning support to database it will be loaded to. | ||
150 | -- It requires that PL/pgSQL is already loaded - will raise exception otherwise. | ||
151 | -- All versioning "stuff" (tables, functions) is in "_v" schema. | ||
152 | |||
153 | -- All functions are defined as 'RETURNS SETOF INT4' to be able to make them to RETURN literally nothing (0 rows). | ||
154 | -- >> RETURNS VOID<< IS similar, but it still outputs "empty line" in psql when calling. | ||
155 | CREATE SCHEMA IF NOT EXISTS _v; | ||
156 | COMMENT ON SCHEMA _v IS 'Schema for versioning data and functionality.'; | ||
157 | |||
158 | CREATE TABLE IF NOT EXISTS _v.patches ( | ||
159 | patch_name TEXT PRIMARY KEY, | ||
160 | applied_tsz TIMESTAMPTZ NOT NULL DEFAULT now(), | ||
161 | applied_by TEXT NOT NULL, | ||
162 | requires TEXT[], | ||
163 | conflicts TEXT[] | ||
164 | ); | ||
165 | COMMENT ON TABLE _v.patches IS 'Contains information about what patches are currently applied on database.'; | ||
166 | COMMENT ON COLUMN _v.patches.patch_name IS 'Name of patch, has to be unique for every patch.'; | ||
167 | COMMENT ON COLUMN _v.patches.applied_tsz IS 'When the patch was applied.'; | ||
168 | COMMENT ON COLUMN _v.patches.applied_by IS 'Who applied this patch (PostgreSQL username)'; | ||
169 | COMMENT ON COLUMN _v.patches.requires IS 'List of patches that are required for given patch.'; | ||
170 | COMMENT ON COLUMN _v.patches.conflicts IS 'List of patches that conflict with given patch.'; | ||
171 | |||
172 | 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 $$ | ||
173 | DECLARE | ||
174 | t_text TEXT; | ||
175 | t_text_a TEXT[]; | ||
176 | i INT4; | ||
177 | BEGIN | ||
178 | -- Thanks to this we know only one patch will be applied at a time | ||
179 | LOCK TABLE _v.patches IN EXCLUSIVE MODE; | ||
180 | |||
181 | SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name; | ||
182 | IF FOUND THEN | ||
183 | RAISE EXCEPTION 'Patch % is already applied!', in_patch_name; | ||
184 | END IF; | ||
185 | |||
186 | t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE patch_name = any( in_conflicts ) ); | ||
187 | IF array_upper( t_text_a, 1 ) IS NOT NULL THEN | ||
188 | RAISE EXCEPTION 'Versioning patches conflict. Conflicting patche(s) installed: %.', array_to_string( t_text_a, ', ' ); | ||
189 | END IF; | ||
190 | |||
191 | IF array_upper( in_requirements, 1 ) IS NOT NULL THEN | ||
192 | t_text_a := '{}'; | ||
193 | FOR i IN array_lower( in_requirements, 1 ) .. array_upper( in_requirements, 1 ) LOOP | ||
194 | SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_requirements[i]; | ||
195 | IF NOT FOUND THEN | ||
196 | t_text_a := t_text_a || in_requirements[i]; | ||
197 | END IF; | ||
198 | END LOOP; | ||
199 | IF array_upper( t_text_a, 1 ) IS NOT NULL THEN | ||
200 | RAISE EXCEPTION 'Missing prerequisite(s): %.', array_to_string( t_text_a, ', ' ); | ||
201 | END IF; | ||
202 | END IF; | ||
203 | |||
204 | 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, '{}' ) ); | ||
205 | RETURN; | ||
206 | END; | ||
207 | $$ language plpgsql; | ||
208 | 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.'; | ||
209 | |||
210 | CREATE OR REPLACE FUNCTION _v.register_patch( TEXT, TEXT[] ) RETURNS setof INT4 AS $$ | ||
211 | SELECT _v.register_patch( $1, $2, NULL ); | ||
212 | $$ language sql; | ||
213 | COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[] ) IS 'Wrapper to allow registration of patches without conflicts.'; | ||
214 | CREATE OR REPLACE FUNCTION _v.register_patch( TEXT ) RETURNS setof INT4 AS $$ | ||
215 | SELECT _v.register_patch( $1, NULL, NULL ); | ||
216 | $$ language sql; | ||
217 | COMMENT ON FUNCTION _v.register_patch( TEXT ) IS 'Wrapper to allow registration of patches without requirements and conflicts.'; | ||
218 | |||
219 | CREATE OR REPLACE FUNCTION _v.unregister_patch( IN in_patch_name TEXT, OUT versioning INT4 ) RETURNS setof INT4 AS $$ | ||
220 | DECLARE | ||
221 | i INT4; | ||
222 | t_text_a TEXT[]; | ||
223 | BEGIN | ||
224 | -- Thanks to this we know only one patch will be applied at a time | ||
225 | LOCK TABLE _v.patches IN EXCLUSIVE MODE; | ||
226 | |||
227 | t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE in_patch_name = ANY( requires ) ); | ||
228 | IF array_upper( t_text_a, 1 ) IS NOT NULL THEN | ||
229 | RAISE EXCEPTION 'Cannot uninstall %, as it is required by: %.', in_patch_name, array_to_string( t_text_a, ', ' ); | ||
230 | END IF; | ||
231 | |||
232 | DELETE FROM _v.patches WHERE patch_name = in_patch_name; | ||
233 | GET DIAGNOSTICS i = ROW_COUNT; | ||
234 | IF i < 1 THEN | ||
235 | RAISE EXCEPTION 'Patch % is not installed, so it can''t be uninstalled!', in_patch_name; | ||
236 | END IF; | ||
237 | |||
238 | RETURN; | ||
239 | END; | ||
240 | $$ language plpgsql; | ||
241 | 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.'; | ||
242 | |||
243 | CREATE OR REPLACE FUNCTION _v.assert_patch_is_applied( IN in_patch_name TEXT ) RETURNS TEXT as $$ | ||
244 | DECLARE | ||
245 | t_text TEXT; | ||
246 | BEGIN | ||
247 | SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name; | ||
248 | IF NOT FOUND THEN | ||
249 | RAISE EXCEPTION 'Patch % is not applied!', in_patch_name; | ||
250 | END IF; | ||
251 | RETURN format('Patch %s is applied.', in_patch_name); | ||
252 | END; | ||
253 | $$ language plpgsql; | ||
254 | COMMENT ON FUNCTION _v.assert_patch_is_applied( TEXT ) IS 'Function that can be used to make sure that patch has been applied.'; | ||
255 | |||
256 | CREATE OR REPLACE FUNCTION _v.assert_user_is_superuser() RETURNS TEXT as $$ | ||
257 | DECLARE | ||
258 | v_super bool; | ||
259 | BEGIN | ||
260 | SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user; | ||
261 | IF v_super THEN | ||
262 | RETURN 'assert_user_is_superuser: OK'; | ||
263 | END IF; | ||
264 | RAISE EXCEPTION 'Current user is not superuser - cannot continue.'; | ||
265 | END; | ||
266 | $$ language plpgsql; | ||
267 | 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.'; | ||
268 | |||
269 | CREATE OR REPLACE FUNCTION _v.assert_user_is_not_superuser() RETURNS TEXT as $$ | ||
270 | DECLARE | ||
271 | v_super bool; | ||
272 | BEGIN | ||
273 | SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user; | ||
274 | IF v_super THEN | ||
275 | RAISE EXCEPTION 'Current user is superuser - cannot continue.'; | ||
276 | END IF; | ||
277 | RETURN 'assert_user_is_not_superuser: OK'; | ||
278 | END; | ||
279 | $$ language plpgsql; | ||
280 | 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.'; | ||
281 | |||
282 | CREATE OR REPLACE FUNCTION _v.assert_user_is_one_of(VARIADIC p_acceptable_users TEXT[] ) RETURNS TEXT as $$ | ||
283 | DECLARE | ||
284 | BEGIN | ||
285 | IF current_user = any( p_acceptable_users ) THEN | ||
286 | RETURN 'assert_user_is_one_of: OK'; | ||
287 | END IF; | ||
288 | RAISE EXCEPTION 'User is not one of: % - cannot continue.', p_acceptable_users; | ||
289 | END; | ||
290 | $$ language plpgsql; | ||
291 | 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.'; | ||
292 | |||
293 | COMMIT; | ||