-DROP SCHEMA reporter CASCADE;
+/*
+ * Copyright (C) 2004-2008 Georgia Public Library Service
+ * Copyright (C) 2007-2008 Equinox Software, Inc.
+ * Mike Rylander <miker@esilibrary.com>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ */
+
+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)
+ folder INT NOT NULL REFERENCES reporter.template_folder (id) DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX rpt_tmpl_owner_idx ON reporter.template (owner);
CREATE INDEX rpt_tmpl_fldr_idx ON reporter.template (folder);
description TEXT NOT NULL DEFAULT '',
template INT NOT NULL REFERENCES reporter.template (id) DEFERRABLE INITIALLY DEFERRED,
data TEXT NOT NULL,
- folder INT NOT NULL REFERENCES reporter.report_folder (id),
+ folder INT NOT NULL REFERENCES reporter.report_folder (id) DEFERRABLE INITIALLY DEFERRED,
recur BOOL NOT NULL DEFAULT FALSE,
- recurance INTERVAL
+ recurrence INTERVAL
);
CREATE INDEX rpt_rpt_owner_idx ON reporter.report (owner);
CREATE INDEX rpt_rpt_fldr_idx ON reporter.report (folder);
CREATE TABLE reporter.schedule (
id SERIAL PRIMARY KEY,
report INT NOT NULL REFERENCES reporter.report (id) DEFERRABLE INITIALLY DEFERRED,
- folder INT NOT NULL REFERENCES reporter.output_folder (id),
+ folder INT NOT NULL REFERENCES reporter.output_folder (id) DEFERRABLE INITIALLY DEFERRED,
runner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
run_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
start_time TIMESTAMP WITH TIME ZONE,
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( SUBSTRING(issn.value FROM $$^\S+$$) ) 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')
LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
- WHERE r.deleted IS FALSE
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
-CREATE OR REPLACE VIEW reporter.super_simple_record AS
-SELECT r.id,
- r.fingerprint,
- r.quality,
- r.tcn_source,
- r.tcn_value,
- title.value AS title,
- author.value AS author,
- publisher.value AS publisher,
- SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
- ARRAY_ACCUM( SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
- ARRAY_ACCUM( 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 = '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 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')
- WHERE r.deleted IS FALSE
- GROUP BY 1,2,3,4,5,6,7,8,9;
+CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
+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);
+
+CREATE VIEW reporter.super_simple_record AS SELECT * FROM reporter.materialized_simple_record;
+
+CREATE OR REPLACE FUNCTION reporter.simple_rec_update (r_id BIGINT, deleted BOOL) RETURNS BOOL AS $$
+BEGIN
+
+ DELETE FROM reporter.materialized_simple_record WHERE id = r_id;
+
+ IF NOT deleted THEN
+ INSERT INTO reporter.materialized_simple_record SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record WHERE id = r_id;
+ END IF;
+
+ RETURN TRUE;
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION reporter.simple_rec_update (r_id BIGINT) RETURNS BOOL AS $$
+ SELECT reporter.simple_rec_update($1, FALSE);
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION reporter.simple_rec_delete (r_id BIGINT) RETURNS BOOL AS $$
+ SELECT reporter.simple_rec_update($1, TRUE);
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION reporter.simple_rec_trigger () RETURNS TRIGGER AS $func$
+BEGIN
+ IF TG_OP = 'DELETE' THEN
+ PERFORM reporter.simple_rec_delete(NEW.id);
+ ELSE
+ PERFORM reporter.simple_rec_update(NEW.id);
+ END IF;
+
+ RETURN NEW;
+END;
+$func$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$
+ 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 $$
+
+ 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)
+ SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record;
+
+ 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;
+
+CREATE OR REPLACE FUNCTION reporter.refresh_materialized_simple_record () RETURNS VOID AS $$
+ SELECT reporter.disable_materialized_simple_record_trigger();
+ SELECT reporter.enable_materialized_simple_record_trigger();
+$$ LANGUAGE SQL;
CREATE OR REPLACE VIEW reporter.demographic AS
SELECT u.id,
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;
-
-CREATE OR REPLACE VIEW reporter.xact_billed_totals AS
+-- 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,
SUM( CASE WHEN b.voided THEN 0 ELSE amount END ) as unvoided,
SUM( CASE WHEN b.voided THEN amount ELSE 0 END ) as voided,
FROM money.payment b
GROUP BY 1;
+CREATE OR REPLACE VIEW reporter.overdue_circs AS
+SELECT *
+ FROM "action".circulation
+ WHERE checkin_time is null
+ AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL)
+ AND due_date < now();
+
+CREATE OR REPLACE VIEW reporter.overdue_reports AS
+ SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time
+ FROM reporter.schedule s
+ JOIN reporter.report r ON r.id = s.report
+ JOIN actor.usr u ON s.runner = u.id
+ JOIN actor.card c ON c.id = u.card
+ WHERE s.start_time IS NULL AND s.run_time < now();
+
+CREATE OR REPLACE VIEW reporter.pending_reports AS
+ SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time
+ FROM reporter.schedule s
+ JOIN reporter.report r ON r.id = s.report
+ JOIN actor.usr u ON s.runner = u.id
+ JOIN actor.card c ON c.id = u.card
+ WHERE s.start_time IS NULL;
+
+CREATE OR REPLACE VIEW reporter.currently_running AS
+ SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time
+ FROM reporter.schedule s
+ JOIN reporter.report r ON r.id = s.report
+ JOIN actor.usr u ON s.runner = u.id
+ JOIN actor.card c ON c.id = u.card
+ WHERE s.start_time IS NOT NULL AND s.complete_time IS NULL;
+
COMMIT;
projects / evergreen-equinox.git / blobdiff