minor typo fixes; table creation ordering
[Evergreen.git] / 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','Accession Number','//*[@tag="001"]', TRUE);
46 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident ) VALUES ('eg_tcn','TCN Value','//*[@tag="901"]/*[@code="a"]', TRUE);
47 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident ) VALUES ('eg_tcn_source','TCN Source','//*[@tag="901"]/*[@code="b"]', TRUE);
48 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, ident ) VALUES ('eg_identifier','Internal ID','//*[@tag="901"]/*[@code="c"]', TRUE);
49 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
50 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath, remove ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]',$r$\D$r$);
51 INSERT INTO vandelay.bib_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
52
53
54 -- Each TEXT field (other than 'name') should hold an XPath predicate for pulling the data needed
55 -- DROP TABLE vandelay.import_item_attr_definition CASCADE;
56 CREATE TABLE vandelay.import_item_attr_definition (
57     id              BIGSERIAL   PRIMARY KEY,
58     owner           INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
59     name            TEXT        NOT NULL,
60     tag             TEXT        NOT NULL,
61     keep            BOOL        NOT NULL DEFAULT FALSE,
62     owning_lib      TEXT,
63     circ_lib        TEXT,
64     call_number     TEXT,
65     copy_number     TEXT,
66     status          TEXT,
67     location        TEXT,
68     circulate       TEXT,
69     deposit         TEXT,
70     deposit_amount  TEXT,
71     ref             TEXT,
72     holdable        TEXT,
73     price           TEXT,
74     barcode         TEXT,
75     circ_modifier   TEXT,
76     circ_as_type    TEXT,
77     alert_message   TEXT,
78     opac_visible    TEXT,
79     pub_note_title  TEXT,
80     pub_note        TEXT,
81     priv_note_title TEXT,
82     priv_note       TEXT,
83         CONSTRAINT vand_import_item_attr_def_idx UNIQUE (owner,name)
84 );
85
86 INSERT INTO vandelay.import_item_attr_definition (
87     owner, name, tag, owning_lib, circ_lib, location,
88     call_number, circ_modifier, barcode, price, copy_number,
89     circulate, ref, holdable, opac_visible, status
90 ) VALUES (
91     1,
92     'Evergreen 852 export format',
93     '852',
94     '[@code = "b"][1]',
95     '[@code = "b"][2]',
96     'c',
97     'j',
98     'g',
99     'p',
100     'y',
101     't',
102     '[@code = "x" and text() = "ncirculating"]',
103     '[@code = "x" and text() = "reference"]',
104     '[@code = "x" and text() = "holdable"]',
105     '[@code = "x" and text() = "visible"]',
106     'z'
107 );
108
109 INSERT INTO vandelay.import_item_attr_definition (
110     owner,
111     name,
112     tag,
113     owning_lib,
114     location,
115     call_number,
116     circ_modifier,
117     barcode,
118     price,
119     status
120 ) VALUES (
121     1,
122     'Unicorn Import format -- 999',
123     '999',
124     'm',
125     'l',
126     'a',
127     't',
128     'i',
129     'p',
130     'k'
131 );
132
133 CREATE TABLE vandelay.bib_queue (
134         queue_type          TEXT        NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'),
135         item_attr_def   TEXT    REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
136         CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
137 ) INHERITS (vandelay.queue);
138 ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id);
139
140 CREATE TABLE vandelay.queued_bib_record (
141         queue           INT             NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
142         bib_source      INT             REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
143         imported_as     INT             REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
144 ) INHERITS (vandelay.queued_record);
145 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
146
147 CREATE TABLE vandelay.queued_bib_record_attr (
148         id                      BIGSERIAL       PRIMARY KEY,
149         record          BIGINT          NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
150         field           INT                     NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
151         attr_value      TEXT            NOT NULL
152 );
153
154 CREATE TABLE vandelay.bib_match (
155         id                              BIGSERIAL       PRIMARY KEY,
156         field_type              TEXT            NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
157         matched_attr    INT                     REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
158         queued_record   BIGINT          REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
159         eg_record               BIGINT          REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
160 );
161
162 -- DROP TABLE vandelay.import_item CASCADE;
163 CREATE TABLE vandelay.import_item (
164     id              BIGSERIAL   PRIMARY KEY,
165     record          BIGINT      NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE,
166     definition      BIGINT      NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE,
167     owning_lib      INT,
168     circ_lib        INT,
169     call_number     TEXT,
170     copy_number     INT,
171     status          INT,
172     location        INT,
173     circulate       BOOL,
174     deposit         BOOL,
175     deposit_amount  NUMERIC(8,2),
176     ref             BOOL,
177     holdable        BOOL,
178     price           NUMERIC(8,2),
179     barcode         TEXT,
180     circ_modifier   TEXT,
181     circ_as_type    TEXT,
182     alert_message   TEXT,
183     pub_note        TEXT,
184     priv_note       TEXT,
185     opac_visible    BOOL
186 );
187  
188 CREATE TABLE vandelay.import_bib_trash_fields (
189     id              BIGSERIAL   PRIMARY KEY,
190     owner           INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
191     field           TEXT        NOT NULL,
192         CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
193 );
194
195 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
196
197     use MARC::Record;
198     use MARC::File::XML;
199
200     my $xml = shift;
201     my $field_spec = shift;
202
203     my $r = MARC::Record->new_from_xml( $xml );
204     $r->delete_field( $_ ) for ( $r->field( $field_spec ) );
205
206     $xml = $r->as_xml_record;
207     $xml =~ s/^<\?.+?\?>$//mo;
208     $xml =~ s/\n//sgo;
209     $xml =~ s/>\s+</></sgo;
210
211     return $xml;
212
213 $_$ LANGUAGE PLPERLU;
214
215
216 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
217 DECLARE
218
219     owning_lib      TEXT;
220     circ_lib        TEXT;
221     call_number     TEXT;
222     copy_number     TEXT;
223     status          TEXT;
224     location        TEXT;
225     circulate       TEXT;
226     deposit         TEXT;
227     deposit_amount  TEXT;
228     ref             TEXT;
229     holdable        TEXT;
230     price           TEXT;
231     barcode         TEXT;
232     circ_modifier   TEXT;
233     circ_as_type    TEXT;
234     alert_message   TEXT;
235     opac_visible    TEXT;
236     pub_note        TEXT;
237     priv_note       TEXT;
238
239     attr_def        RECORD;
240     tmp_attr_set    RECORD;
241     attr_set        vandelay.import_item%ROWTYPE;
242
243     xpath           TEXT;
244
245 BEGIN
246
247     SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
248
249     IF FOUND THEN
250
251         attr_set.definition := attr_def.id; 
252     
253         -- Build the combined XPath
254     
255         owning_lib :=
256             CASE
257                 WHEN attr_def.owning_lib IS NULL THEN 'null()'
258                 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
259                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
260             END;
261     
262         circ_lib :=
263             CASE
264                 WHEN attr_def.circ_lib IS NULL THEN 'null()'
265                 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
266                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
267             END;
268     
269         call_number :=
270             CASE
271                 WHEN attr_def.call_number IS NULL THEN 'null()'
272                 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
273                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
274             END;
275     
276         copy_number :=
277             CASE
278                 WHEN attr_def.copy_number IS NULL THEN 'null()'
279                 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
280                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
281             END;
282     
283         status :=
284             CASE
285                 WHEN attr_def.status IS NULL THEN 'null()'
286                 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
287                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
288             END;
289     
290         location :=
291             CASE
292                 WHEN attr_def.location IS NULL THEN 'null()'
293                 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
294                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
295             END;
296     
297         circulate :=
298             CASE
299                 WHEN attr_def.circulate IS NULL THEN 'null()'
300                 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
301                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
302             END;
303     
304         deposit :=
305             CASE
306                 WHEN attr_def.deposit IS NULL THEN 'null()'
307                 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
308                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
309             END;
310     
311         deposit_amount :=
312             CASE
313                 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
314                 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
315                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
316             END;
317     
318         ref :=
319             CASE
320                 WHEN attr_def.ref IS NULL THEN 'null()'
321                 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
322                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
323             END;
324     
325         holdable :=
326             CASE
327                 WHEN attr_def.holdable IS NULL THEN 'null()'
328                 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
329                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
330             END;
331     
332         price :=
333             CASE
334                 WHEN attr_def.price IS NULL THEN 'null()'
335                 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
336                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
337             END;
338     
339         barcode :=
340             CASE
341                 WHEN attr_def.barcode IS NULL THEN 'null()'
342                 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
343                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
344             END;
345     
346         circ_modifier :=
347             CASE
348                 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
349                 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
350                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
351             END;
352     
353         circ_as_type :=
354             CASE
355                 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
356                 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
357                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
358             END;
359     
360         alert_message :=
361             CASE
362                 WHEN attr_def.alert_message IS NULL THEN 'null()'
363                 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
364                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
365             END;
366     
367         opac_visible :=
368             CASE
369                 WHEN attr_def.opac_visible IS NULL THEN 'null()'
370                 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
371                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
372             END;
373
374         pub_note :=
375             CASE
376                 WHEN attr_def.pub_note IS NULL THEN 'null()'
377                 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
378                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
379             END;
380         priv_note :=
381             CASE
382                 WHEN attr_def.priv_note IS NULL THEN 'null()'
383                 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
384                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
385             END;
386     
387     
388         xpath := 
389             owning_lib      || '|' || 
390             circ_lib        || '|' || 
391             call_number     || '|' || 
392             copy_number     || '|' || 
393             status          || '|' || 
394             location        || '|' || 
395             circulate       || '|' || 
396             deposit         || '|' || 
397             deposit_amount  || '|' || 
398             ref             || '|' || 
399             holdable        || '|' || 
400             price           || '|' || 
401             barcode         || '|' || 
402             circ_modifier   || '|' || 
403             circ_as_type    || '|' || 
404             alert_message   || '|' || 
405             pub_note        || '|' || 
406             priv_note       || '|' || 
407             opac_visible;
408
409         -- RAISE NOTICE 'XPath: %', xpath;
410         
411         FOR tmp_attr_set IN
412                 SELECT  *
413                   FROM  xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
414                             AS t( id BIGINT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
415                                   dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
416                                   circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
417         LOOP
418     
419             tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
420             tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
421
422             tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
423             tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
424     
425             SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
426             SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
427             SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
428     
429             SELECT  id INTO attr_set.location
430               FROM  asset.copy_location
431               WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
432                     AND owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
433     
434             attr_set.circulate      :=
435                 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
436                 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
437
438             attr_set.deposit        :=
439                 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
440                 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
441
442             attr_set.holdable       :=
443                 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
444                 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
445
446             attr_set.opac_visible   :=
447                 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
448                 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
449
450             attr_set.ref            :=
451                 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
452                 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
453     
454             attr_set.copy_number    := tmp_attr_set.cnum::INT; -- INT,
455             attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
456             attr_set.price          := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
457     
458             attr_set.call_number    := tmp_attr_set.cn; -- TEXT
459             attr_set.barcode        := tmp_attr_set.bc; -- TEXT,
460             attr_set.circ_modifier  := tmp_attr_set.circ_mod; -- TEXT,
461             attr_set.circ_as_type   := tmp_attr_set.circ_as; -- TEXT,
462             attr_set.alert_message  := tmp_attr_set.amessage; -- TEXT,
463             attr_set.pub_note       := tmp_attr_set.note; -- TEXT,
464             attr_set.priv_note      := tmp_attr_set.pnote; -- TEXT,
465             attr_set.alert_message  := tmp_attr_set.amessage; -- TEXT,
466     
467             RETURN NEXT attr_set;
468     
469         END LOOP;
470     
471     END IF;
472
473 END;
474 $$ LANGUAGE PLPGSQL;
475
476
477 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
478 DECLARE
479     value   TEXT;
480     atype   TEXT;
481     adef    RECORD;
482 BEGIN
483     FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
484
485         SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
486         IF (value IS NOT NULL AND value <> '') THEN
487             INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
488         END IF;
489
490     END LOOP;
491
492     RETURN NULL;
493 END;
494 $$ LANGUAGE PLPGSQL;
495
496 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
497 DECLARE
498     queue_rec   RECORD;
499     item_rule   RECORD;
500     item_data   vandelay.import_item%ROWTYPE;
501 BEGIN
502
503     SELECT * INTO queue_rec FROM vandelay.bib_queue WHERE id = NEW.queue;
504
505     FOR item_rule IN SELECT r.* FROM actor.org_unit_ancestors( queue_rec.owner ) o JOIN vandelay.import_item_attr_definition r ON ( r.owner = o.id ) LOOP
506         FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, item_rule.id::BIGINT ) LOOP
507             INSERT INTO vandelay.import_item (
508                 record,
509                 definition,
510                 owning_lib,
511                 circ_lib,
512                 call_number,
513                 copy_number,
514                 status,
515                 location,
516                 circulate,
517                 deposit,
518                 deposit_amount,
519                 ref,
520                 holdable,
521                 price,
522                 barcode,
523                 circ_modifier,
524                 circ_as_type,
525                 alert_message,
526                 pub_note,
527                 priv_note,
528                 opac_visible
529             ) VALUES (
530                 NEW.id,
531                 item_data.definition,
532                 item_data.owning_lib,
533                 item_data.circ_lib,
534                 item_data.call_number,
535                 item_data.copy_number,
536                 item_data.status,
537                 item_data.location,
538                 item_data.circulate,
539                 item_data.deposit,
540                 item_data.deposit_amount,
541                 item_data.ref,
542                 item_data.holdable,
543                 item_data.price,
544                 item_data.barcode,
545                 item_data.circ_modifier,
546                 item_data.circ_as_type,
547                 item_data.alert_message,
548                 item_data.pub_note,
549                 item_data.priv_note,
550                 item_data.opac_visible
551             );
552         END LOOP;
553     END LOOP;
554
555     RETURN NULL;
556 END;
557 $func$ LANGUAGE PLPGSQL;
558
559 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
560 DECLARE
561     attr    RECORD;
562     eg_rec  RECORD;
563 BEGIN
564     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
565
566                 -- All numbers? check for an id match
567                 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
568                 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
569                         INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
570                         END LOOP;
571                 END IF;
572
573                 -- Looks like an ISBN? check for an isbn match
574                 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
575                 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
576                                 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
577                                 IF FOUND THEN
578                                 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
579                                 END IF;
580                         END LOOP;
581
582                         -- subcheck for isbn-as-tcn
583                     FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
584                             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
585                 END LOOP;
586                 END IF;
587
588                 -- check for an OCLC tcn_value match
589                 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
590                     FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
591                             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
592                 END LOOP;
593                 END IF;
594
595                 -- check for a direct tcn_value match
596         FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
597             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
598         END LOOP;
599
600     END LOOP;
601
602     RETURN NULL;
603 END;
604 $func$ LANGUAGE PLPGSQL;
605
606 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
607 BEGIN
608     DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
609     DELETE FROM vandelay.import_item WHERE record = OLD.id;
610
611     IF TG_OP = 'UPDATE' THEN
612         RETURN NEW;
613     END IF;
614     RETURN OLD;
615 END;
616 $$ LANGUAGE PLPGSQL;
617
618 CREATE TRIGGER cleanup_bib_trigger
619     BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
620     FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
621
622 CREATE TRIGGER ingest_bib_trigger
623     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
624     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
625
626 CREATE TRIGGER ingest_item_trigger
627     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
628     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
629
630 CREATE TRIGGER zz_match_bibs_trigger
631     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
632     FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
633
634
635 /* Authority stuff down here */
636 ---------------------------------------
637 CREATE TABLE vandelay.authority_attr_definition (
638         id                      SERIAL  PRIMARY KEY,
639         code            TEXT    UNIQUE NOT NULL,
640         description     TEXT,
641         xpath           TEXT    NOT NULL,
642         remove          TEXT    NOT NULL DEFAULT '',
643         ident           BOOL    NOT NULL DEFAULT FALSE
644 );
645 INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);
646
647 CREATE TABLE vandelay.authority_queue (
648         queue_type      TEXT            NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
649         CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
650 ) INHERITS (vandelay.queue);
651 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
652
653 CREATE TABLE vandelay.queued_authority_record (
654         queue           INT     NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
655         imported_as     INT     REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
656 ) INHERITS (vandelay.queued_record);
657 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
658
659 CREATE TABLE vandelay.queued_authority_record_attr (
660         id                      BIGSERIAL       PRIMARY KEY,
661         record          BIGINT          NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
662         field           INT                     NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
663         attr_value      TEXT            NOT NULL
664 );
665
666 CREATE TABLE vandelay.authority_match (
667         id                              BIGSERIAL       PRIMARY KEY,
668         matched_attr    INT                     REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
669         queued_record   BIGINT          REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
670         eg_record               BIGINT          REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
671 );
672
673 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
674 DECLARE
675     value   TEXT;
676     atype   TEXT;
677     adef    RECORD;
678 BEGIN
679     FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
680
681         SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
682         IF (value IS NOT NULL AND value <> '') THEN
683             INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
684         END IF;
685
686     END LOOP;
687
688     RETURN NULL;
689 END;
690 $$ LANGUAGE PLPGSQL;
691
692 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
693 BEGIN
694     DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
695     IF TG_OP = 'UPDATE' THEN
696         RETURN NEW;
697     END IF;
698     RETURN OLD;
699 END;
700 $$ LANGUAGE PLPGSQL;
701
702 CREATE TRIGGER cleanup_authority_trigger
703     BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
704     FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
705
706 CREATE TRIGGER ingest_authority_trigger
707     AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
708     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
709
710 COMMIT;
711