3 SELECT evergreen.upgrade_deps_block_check('0836', :eg_version);
5 CREATE TABLE config.floating_group (
7 name TEXT UNIQUE NOT NULL,
8 manual BOOL NOT NULL DEFAULT FALSE
11 CREATE TABLE config.floating_group_member (
12 id SERIAL PRIMARY KEY,
13 floating_group INT NOT NULL REFERENCES config.floating_group (id),
14 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
15 stop_depth INT NOT NULL DEFAULT 0,
17 exclude BOOL NOT NULL DEFAULT FALSE
20 CREATE OR REPLACE FUNCTION evergreen.can_float( copy_floating_group integer, from_ou integer, to_ou integer ) RETURNS BOOL AS $f$
22 float_member config.floating_group_member%ROWTYPE;
26 -- Grab the shared OU depth. If this is less than the stop depth later we ignore the entry.
27 SELECT INTO shared_ou_depth max(depth) FROM actor.org_unit_common_ancestors( from_ou, to_ou ) aou JOIN actor.org_unit_type aout ON aou.ou_type = aout.id;
28 -- Grab the to ou depth. If this is greater than max depth we ignore the entry.
29 SELECT INTO to_ou_depth depth FROM actor.org_unit aou JOIN actor.org_unit_type aout ON aou.ou_type = aout.id WHERE aou.id = to_ou;
30 -- Grab float members that apply. We don't care what we get beyond wanting excluded ones first.
31 SELECT INTO float_member *
33 config.floating_group_member cfgm
34 JOIN actor.org_unit aou ON cfgm.org_unit = aou.id
35 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
37 cfgm.floating_group = copy_floating_group
38 AND to_ou IN (SELECT id FROM actor.org_unit_descendants(aou.id))
39 AND cfgm.stop_depth <= shared_ou_depth
40 AND (cfgm.max_depth IS NULL OR to_ou_depth <= max_depth)
43 -- If we found something then we want to return the opposite of the exclude flag
45 RETURN NOT float_member.exclude;
47 -- Otherwise no floating.
52 INSERT INTO config.floating_group(name) VALUES ('Everywhere');
53 INSERT INTO config.floating_group_member(floating_group, org_unit) VALUES (1, 1);
55 -- We need to drop these before we can update asset.copy
56 DROP VIEW auditor.asset_copy_lifecycle;
57 DROP VIEW auditor.serial_unit_lifecycle;
59 -- Update the appropriate auditor tables
60 ALTER TABLE auditor.asset_copy_history
61 ALTER COLUMN floating DROP DEFAULT,
62 ALTER COLUMN floating DROP NOT NULL,
63 ALTER COLUMN floating TYPE int USING CASE WHEN floating THEN 1 ELSE NULL END;
64 ALTER TABLE auditor.serial_unit_history
65 ALTER COLUMN floating DROP DEFAULT,
66 ALTER COLUMN floating DROP NOT NULL,
67 ALTER COLUMN floating TYPE int USING CASE WHEN floating THEN 1 ELSE NULL END;
69 -- Update asset.copy itself (does not appear to trigger update triggers!)
70 ALTER TABLE asset.copy
71 ALTER COLUMN floating DROP DEFAULT,
72 ALTER COLUMN floating DROP NOT NULL,
73 ALTER COLUMN floating TYPE int USING CASE WHEN floating THEN 1 ELSE NULL END;
75 ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_floating_fkey FOREIGN KEY (floating) REFERENCES config.floating_group (id) DEFERRABLE INITIALLY DEFERRED;
77 -- Update asset.copy_template too
78 ALTER TABLE asset.copy_template
79 ALTER COLUMN floating TYPE int USING CASE WHEN floating THEN 1 ELSE NULL END;
80 ALTER TABLE asset.copy_template ADD CONSTRAINT asset_copy_template_floating_fkey FOREIGN KEY (floating) REFERENCES config.floating_group (id) DEFERRABLE INITIALLY DEFERRED;
82 INSERT INTO permission.perm_list( code, description) VALUES
83 ('ADMIN_FLOAT_GROUPS', 'Allows administration of floating groups');
85 -- And lets just update all auditors to re-create those lifecycle views
86 SELECT auditor.update_auditors();