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
);
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' 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 (publisher.tag = '264' AND publisher.ind2 = '1')) AND pubdate.subfield = 'c')
+ 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')
r.quality,
r.tcn_source,
r.tcn_value,
- FIRST(title.value) AS title,
- FIRST(author.value) AS author,
- STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
- STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
- CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
+ 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 ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
+ ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.isbn::JSON)))
END AS isbn,
- CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
+ CASE WHEN d.issn = 'null'
THEN NULL
- ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
+ ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.issn::JSON)))
END 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' 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')
- GROUP BY 1,2,3,4,5;
+ 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);
CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$
- DELETE FROM reporter.materialized_simple_record;
+ TRUNCATE TABLE reporter.materialized_simple_record;
INSERT INTO reporter.materialized_simple_record
(id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn)
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 = '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;
+-- 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