1 DROP SCHEMA vandelay CASCADE;
5 CREATE SCHEMA vandelay;
7 CREATE TABLE vandelay.queue (
8 id BIGSERIAL PRIMARY KEY,
9 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
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)
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')),
26 /* Bib stuff at the top */
27 ----------------------------------------------------
29 CREATE TABLE vandelay.bib_attr_definition (
30 id SERIAL PRIMARY KEY,
31 code TEXT UNIQUE NOT NULL,
34 remove TEXT NOT NULL DEFAULT '',
35 ident BOOL NOT NULL DEFAULT FALSE
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]');
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);
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);
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
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
81 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
87 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
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);
100 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
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
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);
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;
119 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
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);
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);
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);
145 $func$ LANGUAGE PLPGSQL;
147 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
149 DELETE FROM vandelay.queued_bib_record_attr WHERE lineitem = OLD.id;
150 IF TG_OP = 'UPDATE' THEN
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();
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();
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();
170 /* Authority stuff down here */
171 ---------------------------------------
172 CREATE TABLE vandelay.authority_attr_definition (
173 id SERIAL PRIMARY KEY,
174 code TEXT UNIQUE NOT NULL,
177 remove TEXT NOT NULL DEFAULT '',
178 ident BOOL NOT NULL DEFAULT FALSE
180 INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);
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);
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);
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
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
208 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
214 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
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);
227 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
229 DELETE FROM vandelay.queued_authority_record_attr WHERE lineitem = OLD.id;
230 IF TG_OP = 'UPDATE' THEN
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();
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();