1 -- DROP objects that might have existed from a prior run of 0526
3 DROP TABLE IF EXISTS config.db_patch_dependencies;
4 ALTER TABLE config.upgrade_log DROP COLUMN applied_to;
5 DROP FUNCTION evergreen.upgrade_list_applied_deprecates(TEXT);
6 DROP FUNCTION evergreen.upgrade_list_applied_supersedes(TEXT);
10 INSERT INTO config.upgrade_log (version) VALUES ('0526'); --miker
12 CREATE TABLE config.db_patch_dependencies (
13 db_patch TEXT PRIMARY KEY,
18 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
24 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
26 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
32 CREATE TRIGGER no_overlapping_sups
33 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
34 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
36 CREATE TRIGGER no_overlapping_deps
37 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
38 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
40 ALTER TABLE config.upgrade_log
41 ADD COLUMN applied_to TEXT;
43 -- Provide a named type for patching functions
44 CREATE TYPE evergreen.patch AS (patch TEXT);
46 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
47 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
48 SELECT DISTINCT l.version
49 FROM config.upgrade_log l
50 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
54 -- List applied db patches that are superseded by (and block the application of) my_db_patch
55 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
56 SELECT DISTINCT l.version
57 FROM config.upgrade_log l
58 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
62 -- List applied db patches that deprecates (and block the application of) my_db_patch
63 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$
65 FROM config.db_patch_dependencies
66 WHERE ARRAY[$1]::TEXT[] && deprecates
69 -- List applied db patches that supersedes (and block the application of) my_db_patch
70 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
72 FROM config.db_patch_dependencies
73 WHERE ARRAY[$1]::TEXT[] && supersedes
76 -- Make sure that no deprecated or superseded db patches are currently applied
77 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
79 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
81 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
83 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
85 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
88 -- Raise an exception if there are, in fact, dep/sup confilct
89 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
94 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
95 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
96 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
98 Upgrade script % can not be applied:
99 applied deprecated scripts %
100 applied superseded scripts %
104 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
105 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
106 evergreen.upgrade_list_applied_deprecated(my_db_patch),
107 evergreen.upgrade_list_applied_superseded(my_db_patch);
110 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);