3 CREATE TABLE vandelay.import_bib_trash_group(
5 owner INT NOT NULL REFERENCES actor.org_unit(id),
6 label TEXT NOT NULL, --i18n
7 always_apply BOOLEAN NOT NULL DEFAULT FALSE,
8 CONSTRAINT vand_import_bib_trash_grp_owner_label UNIQUE (owner, label)
11 -- otherwise, the ALTER TABLE statement below
12 -- will fail with pending trigger events.
13 SET CONSTRAINTS ALL IMMEDIATE;
15 ALTER TABLE vandelay.import_bib_trash_fields
16 -- allow null-able for now..
17 ADD COLUMN grp INTEGER REFERENCES vandelay.import_bib_trash_group;
19 -- add any existing trash_fields to "Legacy" groups (one per unique field
20 -- owner) as part of the upgrade, since grp is now required.
21 -- note that vandelay.import_bib_trash_fields was never used before,
22 -- so in most cases this should be a no-op.
24 INSERT INTO vandelay.import_bib_trash_group (owner, label)
25 SELECT DISTINCT(owner), 'Legacy' FROM vandelay.import_bib_trash_fields;
27 UPDATE vandelay.import_bib_trash_fields field SET grp = tgroup.id
28 FROM vandelay.import_bib_trash_group tgroup
29 WHERE tgroup.owner = field.owner;
31 ALTER TABLE vandelay.import_bib_trash_fields
32 -- now that have values, we can make this non-null
33 ALTER COLUMN grp SET NOT NULL,
34 -- drop outdated constraint
35 DROP CONSTRAINT vand_import_bib_trash_fields_idx,
36 -- owner is implied by the grp
38 -- make grp+field unique
39 ADD CONSTRAINT vand_import_bib_trash_fields_once_per UNIQUE (grp, field);