LP#1152753: make it possible for serial units to be added to copy buckets
authorMike Rylander <mrylander@gmail.com>
Mon, 24 Apr 2017 16:40:37 +0000 (12:40 -0400)
committerDan Wells <dbw2@calvin.edu>
Fri, 1 Sep 2017 16:47:19 +0000 (12:47 -0400)
This patch replaces the baseline asset.copy.id fkey constraints with ones
that understand inheritance, and change all existing contstraints to do the
same via upgrade script.

To test
-------
Create some serial units and verify that they can be added to a copy
bucket.

Signed-off-by: Mike Rylander <mrylander@gmail.com>
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
Signed-off-by: Kathy Lussier <klussier@masslnc.org>
Signed-off-by: Dan Wells <dbw2@calvin.edu>
Open-ILS/src/sql/Pg/070.schema.container.sql
Open-ILS/src/sql/Pg/800.fkeys.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql [new file with mode: 0644]

index 32dfc6a..b3a3056 100644 (file)
@@ -56,17 +56,29 @@ CREATE TABLE container.copy_bucket_item (
                                        ON UPDATE CASCADE
                                        DEFERRABLE
                                        INITIALLY DEFERRED,
-       target_copy     INT     NOT NULL
-                               REFERENCES asset."copy" (id)
-                                       ON DELETE CASCADE
-                                       ON UPDATE CASCADE
-                                       DEFERRABLE
-                                       INITIALLY DEFERRED,
+       target_copy     INT     NOT NULL,
     pos         INT,
        create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 );
 CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket);
 
+CREATE OR REPLACE FUNCTION evergreen.container_copy_bucket_item_target_copy_inh_fkey() RETURNS TRIGGER AS $f$
+BEGIN
+        PERFORM 1 FROM asset.copy WHERE id = NEW.target_copy;
+        IF NOT FOUND THEN
+                RAISE foreign_key_violation USING MESSAGE = FORMAT(
+                        $$Referenced asset.copy id not found, target_copy:%s$$, NEW.target_copy
+                );
+        END IF;
+        RETURN NEW;
+END;
+$f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+
+CREATE CONSTRAINT TRIGGER inherit_copy_bucket_item_target_copy_fkey
+        AFTER UPDATE OR INSERT OR DELETE ON container.copy_bucket_item
+        DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.container_copy_bucket_item_target_copy_inh_fkey();
+
+
 CREATE TABLE container.copy_bucket_item_note (
     id      SERIAL      PRIMARY KEY,
     item    INT         NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
index 7d10125..8ae7d26 100644 (file)
@@ -108,10 +108,40 @@ ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_call_number_fkey FOREIGN KEY
 ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
 
-ALTER TABLE vandelay.import_item ADD CONSTRAINT imported_as_fkey FOREIGN KEY (imported_as) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED;
+CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$
+BEGIN
+        PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as;
+        IF NOT FOUND THEN
+                RAISE foreign_key_violation USING MESSAGE = FORMAT(
+                        $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as
+                );
+        END IF;
+        RETURN NEW;
+END;
+$f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+
+CREATE CONSTRAINT TRIGGER inherit_import_item_imported_as_fkey
+        AFTER UPDATE OR INSERT OR DELETE ON vandelay.import_item
+        DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.vandelay_import_item_imported_as_inh_fkey();
+
 ALTER TABLE vandelay.bib_queue ADD CONSTRAINT match_bucket_fkey FOREIGN KEY (match_bucket) REFERENCES container.biblio_record_entry_bucket(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
 
-ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_copy_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED;
+CREATE OR REPLACE FUNCTION evergreen.asset_copy_note_owning_copy_inh_fkey() RETURNS TRIGGER AS $f$
+BEGIN
+        PERFORM 1 FROM asset.copy WHERE id = NEW.owning_copy;
+        IF NOT FOUND THEN
+                RAISE foreign_key_violation USING MESSAGE = FORMAT(
+                        $$Referenced asset.copy id not found, owning_copy:%s$$, NEW.owning_copy
+                );
+        END IF;
+        RETURN NEW;
+END;
+$f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+
+CREATE CONSTRAINT TRIGGER inherit_asset_copy_note_copy_fkey
+        AFTER UPDATE OR INSERT OR DELETE ON asset.copy_note
+        DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_note_owning_copy_inh_fkey();
+
 ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
 
 ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_owning_lib_fkey FOREIGN KEY (owning_lib) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED;
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.inheritance-constraint-trigger.sql
new file mode 100644 (file)
index 0000000..94f4000
--- /dev/null
@@ -0,0 +1,44 @@
+BEGIN;
+
+DO $temp$
+DECLARE
+       r RECORD;
+BEGIN
+
+       FOR r IN SELECT t.table_schema AS sname,
+                       t.table_name AS tname,
+                       t.column_name AS colname,
+                       t.constraint_name
+                 FROM  information_schema.referential_constraints ref
+                       JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name)
+                 WHERE ref.unique_constraint_schema = 'asset'
+                       AND ref.unique_constraint_name = 'copy_pkey'
+       LOOP
+
+               EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';';
+
+               EXECUTE '
+                       CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$
+                       BEGIN
+                               PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||';
+                               IF NOT FOUND THEN
+                                       RAISE foreign_key_violation USING MESSAGE = FORMAT(
+                                               $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||'
+                                       );
+                               END IF;
+                               RETURN NEW;
+                       END;
+                       $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+               ';
+
+               EXECUTE '
+                       CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||'
+                               AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||'
+                               DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey();
+               ';
+       END LOOP;
+END
+$temp$;
+
+COMMIT;
+