*
*/
-DROP SCHEMA reporter CASCADE;
+DROP SCHEMA IF EXISTS reporter CASCADE;
BEGIN;
owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
name TEXT NOT NULL,
- description TEXT NOT NULL,
+ description TEXT NOT NULL DEFAULT '',
data TEXT NOT NULL,
folder INT NOT NULL REFERENCES reporter.template_folder (id) DEFERRABLE INITIALLY DEFERRED
);
series_title.value AS series_title,
series_statement.value AS series_statement,
summary.value AS summary,
- ARRAY_ACCUM( SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
- ARRAY_ACCUM( REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
+ ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
+ ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
- LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
- LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
+ LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND (publisher.tag = '260' OR (publisher.tag = '264' AND publisher.ind2 = '1')) AND publisher.subfield = 'b')
+ LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND (pubdate.tag = '260' OR (pubdate.tag = '264' AND pubdate.ind2 = '1')) AND pubdate.subfield = 'c')
LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
-SELECT r.id,
- r.fingerprint,
- r.quality,
- r.tcn_source,
- r.tcn_value,
- title.value AS title,
- FIRST(author.value) AS author,
- publisher.value AS publisher,
- SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
- ARRAY_ACCUM( DISTINCT SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
- ARRAY_ACCUM( DISTINCT SUBSTRING(issn.value FROM $$^\S+$$) ) AS issn
- FROM biblio.record_entry r
- LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
- LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
- LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
- LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
- LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
- LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
- GROUP BY 1,2,3,4,5,6,8,9;
+SELECT r.id,
+ r.fingerprint,
+ r.quality,
+ r.tcn_source,
+ r.tcn_value,
+ evergreen.oils_json_to_text(d.title) AS title,
+ evergreen.oils_json_to_text(d.author) AS author,
+ evergreen.oils_json_to_text(d.publisher) AS publisher,
+ evergreen.oils_json_to_text(d.pubdate) AS pubdate,
+ CASE WHEN d.isbn = 'null'
+ THEN NULL
+ ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.isbn::JSON)))
+ END AS isbn,
+ CASE WHEN d.issn = 'null'
+ THEN NULL
+ ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.issn::JSON)))
+ END AS issn
+ FROM biblio.record_entry r
+ JOIN metabib.wide_display_entry d ON (r.id = d.source);
CREATE TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.old_super_simple_record WHERE 1=0;
ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id);
SELECT reporter.simple_rec_update($1, TRUE);
$$ LANGUAGE SQL;
-CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$
-DECLARE
- r_id BIGINT;
- deleted BOOL;
+CREATE OR REPLACE FUNCTION reporter.simple_rec_trigger () RETURNS TRIGGER AS $func$
BEGIN
- IF TG_OP IN ('DELETE') THEN
- r_id := OLD.record;
- deleted := TRUE;
- ELSE
- r_id := NEW.record;
- deleted := FALSE;
- END IF;
-
- PERFORM reporter.simple_rec_update(r_id, deleted);
-
- IF deleted THEN
- RETURN OLD;
+ IF TG_OP = 'DELETE' THEN
+ PERFORM reporter.simple_rec_delete(NEW.id);
ELSE
- RETURN NEW;
+ PERFORM reporter.simple_rec_update(NEW.id);
END IF;
+ RETURN NEW;
END;
-$$ LANGUAGE PLPGSQL;
-
---
--- Disabling this by default for now, but we'll keep it around
---
---CREATE TRIGGER zzz_update_materialized_simple_record_tgr
--- AFTER INSERT OR UPDATE OR DELETE ON metabib.real_full_rec
--- FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync();
-
-CREATE OR REPLACE FUNCTION reporter.simple_rec_bib_sync () RETURNS TRIGGER AS $$
-BEGIN
- IF NEW.deleted THEN
- DELETE FROM reporter.materialized_simple_record WHERE id = NEW.id;
- RETURN NEW;
- ELSE
- RETURN NEW;
- END IF;
-END;
-$$ LANGUAGE PLPGSQL;
-
-CREATE TRIGGER zzz_update_materialized_simple_rec_delete_tgr
- AFTER UPDATE ON biblio.record_entry
- FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_bib_sync();
-
+$func$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$
- DROP TRIGGER IF EXISTS zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec;
+ DROP TRIGGER IF EXISTS bbb_simple_rec_trigger ON biblio.record_entry;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$
(id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn)
SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record;
- CREATE TRIGGER zzz_update_materialized_simple_record_tgr
- AFTER INSERT OR UPDATE OR DELETE ON metabib.real_full_rec
- FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync();
+ CREATE TRIGGER bbb_simple_rec_trigger
+ AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry
+ FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger();
$$ LANGUAGE SQL;
END AS "type"
FROM action.circulation;
-CREATE OR REPLACE VIEW reporter.hold_request_record AS
-SELECT id,
- target,
- hold_type,
- CASE
- WHEN hold_type = 'T'
- THEN target
- WHEN hold_type = 'V'
- THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
- WHEN hold_type = 'C'
- THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
- WHEN hold_type = 'M'
- THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
- END AS bib_record
- FROM action.hold_request ahr;
+-- rhrr needs to be a real table, so it can be fast. To that end, we use
+-- a materialized view updated via a trigger.
+CREATE TABLE reporter.hold_request_record AS
+SELECT id,
+ target,
+ hold_type,
+ CASE
+ WHEN hold_type = 'T'
+ THEN target
+ WHEN hold_type = 'I'
+ THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
+ WHEN hold_type = 'V'
+ THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
+ WHEN hold_type IN ('C','R','F')
+ THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
+ WHEN hold_type = 'M'
+ THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
+ WHEN hold_type = 'P'
+ THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
+ END AS bib_record
+ FROM action.hold_request ahr;
+
+CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id);
+CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record);
+
+ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx;
+
+CREATE OR REPLACE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
+BEGIN
+ IF TG_OP = 'INSERT' THEN
+ INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
+ SELECT NEW.id,
+ NEW.target,
+ NEW.hold_type,
+ CASE
+ WHEN NEW.hold_type = 'T'
+ THEN NEW.target
+ WHEN NEW.hold_type = 'I'
+ THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
+ WHEN NEW.hold_type = 'V'
+ THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
+ WHEN NEW.hold_type IN ('C','R','F')
+ THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
+ WHEN NEW.hold_type = 'M'
+ THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
+ WHEN NEW.hold_type = 'P'
+ THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
+ END AS bib_record;
+ ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
+ UPDATE reporter.hold_request_record
+ SET target = NEW.target,
+ hold_type = NEW.hold_type,
+ bib_record = CASE
+ WHEN NEW.hold_type = 'T'
+ THEN NEW.target
+ WHEN NEW.hold_type = 'I'
+ THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
+ WHEN NEW.hold_type = 'V'
+ THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
+ WHEN NEW.hold_type IN ('C','R','F')
+ THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
+ WHEN NEW.hold_type = 'M'
+ THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
+ WHEN NEW.hold_type = 'P'
+ THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
+ END
+ WHERE id = NEW.id;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request
+ FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper();
CREATE OR REPLACE VIEW reporter.xact_billing_totals AS
SELECT b.xact,
projects / evergreen-equinox.git / blobdiff