Open-ILS/src/sql/Pg/012.schema.vandelay.sql

   1 DROP SCHEMA vandelay CASCADE;
   2
   3 BEGIN;
   4
   5 CREATE SCHEMA vandelay;
   6
   7 CREATE TABLE vandelay.queue (
   8         id                              BIGSERIAL       PRIMARY KEY,
   9         owner                   INT                     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  10         name                    TEXT            NOT NULL,
  11         complete                BOOL            NOT NULL DEFAULT FALSE,
  12         queue_type              TEXT            NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')),
  13         CONSTRAINT vand_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
  14 );
  15
  16 CREATE TABLE vandelay.queued_record (
  17     id                  BIGSERIAL                   PRIMARY KEY,
  18     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
  19     import_time TIMESTAMP WITH TIME ZONE,
  20         purpose         TEXT                                            NOT NULL DEFAULT 'import' CHECK (purpose IN ('import','overlay')),
  21     marc                TEXT                        NOT NULL
  22 );
  23
  24
  25
  26 /* Bib stuff at the top */
  27 ----------------------------------------------------
  28
  29 CREATE TABLE vandelay.bib_attr_definition (
  30         id                      SERIAL  PRIMARY KEY,
  31         code            TEXT    UNIQUE NOT NULL,
  32         description     TEXT,
  33         xpath           TEXT    NOT NULL,
  34         remove          TEXT    NOT NULL DEFAULT '',
  35         ident           BOOL    NOT NULL DEFAULT FALSE
  36 );
  37
  38 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
  39 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
  40 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
  41 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
  42 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$);
  43 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$);
  44 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
  45 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);
  46 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident ) VALUES ('eg_identifier','Identifier','//*[@tag="901"]/*[@code="c"]', TRUE);
  47 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident ) VALUES ('eg_tcn','Identifier','//*[@tag="901"]/*[@code="a"]', TRUE);
  48 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
  49 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, remove ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]',$r$\D$r$);
  50 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
  51
  52
  53 CREATE TABLE vandelay.bib_queue (
  54         queue_type      TEXT            NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'),
  55         CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
  56 ) INHERITS (vandelay.queue);
  57 ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id);
  58
  59 CREATE TABLE vandelay.queued_bib_record (
  60         queue           INT             NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  61         bib_source      INT             REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
  62         imported_as     INT             REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
  63 ) INHERITS (vandelay.queued_record);
  64 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
  65
  66 CREATE TABLE vandelay.queued_bib_record_attr (
  67         id                      BIGSERIAL       PRIMARY KEY,
  68         record          BIGINT          NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
  69         field           INT                     NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
  70         attr_value      TEXT            NOT NULL
  71 );
  72
  73 CREATE TABLE vandelay.bib_match (
  74         id                              BIGSERIAL       PRIMARY KEY,
  75         field_type              TEXT            NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
  76         matched_attr    INT                     REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  77         queued_record   BIGINT          REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  78         eg_record               BIGINT          REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
  79 );
  80
  81 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
  82 DECLARE
  83     value   TEXT;
  84     atype   TEXT;
  85     adef    RECORD;
  86 BEGIN
  87     FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
  88
  89         SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
  90         IF (value IS NOT NULL AND value <> '') THEN
  91             INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
  92         END IF;
  93
  94     END LOOP;
  95
  96     RETURN NULL;
  97 END;
  98 $$ LANGUAGE PLPGSQL;
  99
 100 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
 101 DECLARE
 102     attr    RECORD;
 103     eg_rec  RECORD;
 104 BEGIN
 105     FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP
 106
 107                 -- All numbers? check for an id match
 108                 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
 109                 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
 110                         INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
 111                         END LOOP;
 112                 END IF;
 113
 114                 -- Looks like an ISBN? check for an isbn match
 115                 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
 116                 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
 117                                 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
 118                                 IF FOUND THEN
 119                                 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
 120                                 END IF;
 121                         END LOOP;
 122
 123                         -- subcheck for isbn-as-tcn
 124                     FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
 125                             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
 126                 END LOOP;
 127                 END IF;
 128
 129                 -- check for an OCLC tcn_value match
 130                 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
 131                     FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
 132                             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
 133                 END LOOP;
 134                 END IF;
 135
 136                 -- check for a direct tcn_value match
 137         FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
 138             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
 139         END LOOP;
 140
 141     END LOOP;
 142
 143     RETURN NULL;
 144 END;
 145 $func$ LANGUAGE PLPGSQL;
 146
 147 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
 148 BEGIN
 149     DELETE FROM vandelay.queued_bib_record_attr WHERE lineitem = OLD.id;
 150     IF TG_OP = 'UPDATE' THEN
 151         RETURN NEW;
 152     END IF;
 153     RETURN OLD;
 154 END;
 155 $$ LANGUAGE PLPGSQL;
 156
 157 CREATE TRIGGER cleanup_bib_trigger
 158     BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
 159     FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
 160
 161 CREATE TRIGGER ingest_bib_trigger
 162     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
 163     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
 164
 165 CREATE TRIGGER zz_match_bibs_trigger
 166     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
 167     FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
 168
 169
 170 /* Authority stuff down here */
 171 ---------------------------------------
 172 CREATE TABLE vandelay.authority_attr_definition (
 173         id                      SERIAL  PRIMARY KEY,
 174         code            TEXT    UNIQUE NOT NULL,
 175         description     TEXT,
 176         xpath           TEXT    NOT NULL,
 177         remove          TEXT    NOT NULL DEFAULT '',
 178         ident           BOOL    NOT NULL DEFAULT FALSE
 179 );
 180 INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);
 181
 182 CREATE TABLE vandelay.authority_queue (
 183         queue_type      TEXT            NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
 184         CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
 185 ) INHERITS (vandelay.queue);
 186 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
 187
 188 CREATE TABLE vandelay.queued_authority_record (
 189         queue           INT     NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 190         imported_as     INT     REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
 191 ) INHERITS (vandelay.queued_record);
 192 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
 193
 194 CREATE TABLE vandelay.queued_authority_record_attr (
 195         id                      BIGSERIAL       PRIMARY KEY,
 196         record          BIGINT          NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
 197         field           INT                     NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
 198         attr_value      TEXT            NOT NULL
 199 );
 200
 201 CREATE TABLE vandelay.authority_match (
 202         id                              BIGSERIAL       PRIMARY KEY,
 203         matched_attr    INT                     REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 204         queued_record   BIGINT          REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 205         eg_record               BIGINT          REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
 206 );
 207
 208 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
 209 DECLARE
 210     value   TEXT;
 211     atype   TEXT;
 212     adef    RECORD;
 213 BEGIN
 214     FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
 215
 216         SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
 217         IF (value IS NOT NULL AND value <> '') THEN
 218             INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
 219         END IF;
 220
 221     END LOOP;
 222
 223     RETURN NULL;
 224 END;
 225 $$ LANGUAGE PLPGSQL;
 226
 227 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
 228 BEGIN
 229     DELETE FROM vandelay.queued_authority_record_attr WHERE lineitem = OLD.id;
 230     IF TG_OP = 'UPDATE' THEN
 231         RETURN NEW;
 232     END IF;
 233     RETURN OLD;
 234 END;
 235 $$ LANGUAGE PLPGSQL;
 236
 237 CREATE TRIGGER cleanup_authority_trigger
 238     BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
 239     FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
 240
 241 CREATE TRIGGER ingest_authority_trigger
 242     AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
 243     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
 244
 245 COMMIT;
 246