From bddf6ae4dfc330d9dcdb50cf0d0fcb1bd949ba3f Mon Sep 17 00:00:00 2001 From: dbs Date: Fri, 6 May 2011 02:32:03 +0000 Subject: [PATCH] Amalgamate 2.0.1 through 2.0.6 updates into monster upgrade script All on top of our already bastardized 1.6.1-2.0 script. Should really check to see if that was a finalized 1.6.1-2.0 script that our bastardized version was based on... git-svn-id: svn://svn.open-ils.org/ILS-Contrib/conifer/branches/rel_2_0@1442 6d9bc8c9-1ec2-4278-b937-99fde70a366f --- src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 1190 ++++++++++++++++++++++++++++++++--- 1 file changed, 1110 insertions(+), 80 deletions(-) diff --git a/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index 451eea8673..7ba8ff80e2 100644 --- a/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -9059,15 +9059,23 @@ CREATE TABLE acq.claim_event ( CREATE INDEX claim_event_claim_date_idx ON acq.claim_event( claim, event_date ); -CREATE OR REPLACE FUNCTION actor.usr_purge_data( +DROP FUNCTION IF EXISTS actor.usr_purge_data(INT, INT); +CREATE FUNCTION actor.usr_purge_data( src_usr IN INTEGER, - dest_usr IN INTEGER + specified_dest_usr IN INTEGER ) RETURNS VOID AS $$ DECLARE suffix TEXT; renamable_row RECORD; + dest_usr INTEGER; BEGIN + IF specified_dest_usr IS NULL THEN + dest_usr := 1; -- Admin user on stock installs + ELSE + dest_usr := specified_dest_usr; + END IF; + UPDATE actor.usr SET active = FALSE, card = NULL, @@ -9376,6 +9384,9 @@ BEGIN END; $$ LANGUAGE plpgsql; +INSERT INTO config.upgrade_log(version) VALUES ('0480'); +INSERT INTO config.upgrade_log(version) VALUES ('0481'); + COMMENT ON FUNCTION actor.usr_purge_data(INT, INT) IS $$ /** * Finds rows dependent on a given row in actor.usr and either deletes them @@ -17033,9 +17044,12 @@ CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$ use strict; use MARC::Record; use MARC::File::XML (BinaryEncoding => 'UTF-8'); +use MARC::Charset; use Encode; use Unicode::Normalize; +MARC::Charset->assume_unicode(1); + my $record = MARC::Record->new_from_xml($_TD->{new}{marc}); my $schema = $_TD->{table_schema}; my $rec_id = $_TD->{new}{id}; @@ -18247,12 +18261,12 @@ CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func $func$ LANGUAGE PLPERLU; -CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -18262,9 +18276,9 @@ BEGIN SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( av.id ), trans - FROM + FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) GROUP BY 1,2,6; @@ -18278,12 +18292,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -18293,9 +18307,9 @@ BEGIN SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( av.id ), trans - FROM + FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) GROUP BY 1,2,6; @@ -18309,12 +18323,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ -DECLARE - ans RECORD; +CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; trans INT; -BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -18326,8 +18340,8 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) GROUP BY 1,2,6; IF NOT FOUND THEN @@ -18340,12 +18354,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -18357,8 +18371,8 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) GROUP BY 1,2,6; IF NOT FOUND THEN @@ -18371,19 +18385,19 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ BEGIN IF staff IS TRUE THEN IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid ); END IF; ELSE IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid ); END IF; END IF; @@ -18391,12 +18405,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -18408,7 +18422,7 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) JOIN metabib.metarecord_source_map m ON (m.source = av.record) GROUP BY 1,2,6; @@ -18423,12 +18437,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -18440,7 +18454,7 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) JOIN metabib.metarecord_source_map m ON (m.source = av.record) GROUP BY 1,2,6; @@ -18455,12 +18469,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ -DECLARE - ans RECORD; +CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -18472,8 +18486,8 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) JOIN metabib.metarecord_source_map m ON (m.source = cn.record) GROUP BY 1,2,6; @@ -18487,12 +18501,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -18504,8 +18518,8 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) JOIN metabib.metarecord_source_map m ON (m.source = cn.record) GROUP BY 1,2,6; @@ -18519,19 +18533,19 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ BEGIN IF staff IS TRUE THEN IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid ); END IF; ELSE IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid ); END IF; END IF; @@ -18568,30 +18582,6 @@ ALTER TABLE reporter.report RENAME COLUMN recurance TO recurrence; UPDATE reporter.template SET data = REGEXP_REPLACE(data, E'^(.*)recuring(.*)$', E'\\1recurring\\2') WHERE data LIKE '%recuring%'; UPDATE reporter.template SET data = REGEXP_REPLACE(data, E'^(.*)recurance(.*)$', E'\\1recurrence\\2') WHERE data LIKE '%recurance%'; --- Need to recreate this view with DISTINCT calls to ARRAY_ACCUM, thus avoiding duplicated ISBN and ISSN values -CREATE OR REPLACE VIEW reporter.old_super_simple_record AS -SELECT r.id, - r.fingerprint, - r.quality, - r.tcn_source, - r.tcn_value, - FIRST(title.value) AS title, - FIRST(author.value) AS author, - ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher, - ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT 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; - --- Correct the ISSN array definition for reporter.simple_record - CREATE OR REPLACE VIEW reporter.simple_record AS SELECT r.id, s.metarecord, @@ -18607,8 +18597,8 @@ SELECT r.id, 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_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, + ARRAY_ACCUM( 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, @@ -18629,6 +18619,27 @@ SELECT r.id, LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.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, + FIRST(title.value) AS title, + FIRST(author.value) AS author, + ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher, + ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate, + ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, + ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) 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; + 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; @@ -18691,8 +18702,11 @@ CREATE OR REPLACE FUNCTION authority.normalize_heading( TEXT ) RETURNS TEXT AS $ use utf8; use MARC::Record; use MARC::File::XML (BinaryEncoding => 'UTF8'); + use MARC::Charset; use UUID::Tiny ':std'; + MARC::Charset->assume_unicode(1); + my $xml = shift() or return undef; my $r; @@ -18725,7 +18739,7 @@ CREATE OR REPLACE FUNCTION authority.normalize_heading( TEXT ) RETURNS TEXT AS $ # Default to "No attempt to code" if the leader is horribly broken my $fixed_field = $r->field('008'); my $thes_char = '|'; - if ($fixed_field) { + if ($fixed_field) { $thes_char = substr($fixed_field->data(), 11, 1) || '|'; } @@ -18747,13 +18761,13 @@ CREATE OR REPLACE FUNCTION authority.normalize_heading( TEXT ) RETURNS TEXT AS $ $auth_txt .= '‡' . $sf->[0] . ' ' . $sf->[1]; } } - - # Perhaps better to parameterize the spi and pass as a parameter - $auth_txt =~ s/'//go; - + if ($auth_txt) { - my $result = spi_exec_query("SELECT public.naco_normalize('$auth_txt') AS norm_text"); + my $stmt = spi_prepare('SELECT public.naco_normalize($1) AS norm_text', 'TEXT'); + my $result = spi_exec_prepared($stmt, $auth_txt); my $norm_txt = $result->{rows}[0]->{norm_text}; + spi_freeplan($stmt); + undef($stmt); return $head->tag() . "_" . $thes_code . " " . $norm_txt; } @@ -19020,6 +19034,27 @@ WHERE NOT EXISTS ( SELECT 1 FROM acq.lineitem_marc_attr_definition WHERE code = 'upc' ); +-- '@@' auto-placeholder barcode support +CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$ +BEGIN + IF NEW.barcode LIKE '@@%' THEN + NEW.barcode := '@@' || NEW.id; + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE TRIGGER autogenerate_placeholder_barcode + BEFORE INSERT OR UPDATE ON asset.copy + FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode(); + +-- We defined the same trigger on the parent table asset.copy +-- but we need to define it on child tables explicitly as well +CREATE TRIGGER autogenerate_placeholder_barcode + BEFORE INSERT OR UPDATE ON serial.unit + FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode() +; + COMMIT; -- Some operations go outside of the transaction, because they may @@ -19081,7 +19116,7 @@ DROP INDEX IF EXISTS authority.unique_by_heading_and_thesaurus; \qecho If the following CREATE INDEX fails, It will be necessary to do some \qecho data cleanup as described in the comments. -CREATE UNIQUE INDEX unique_by_heading_and_thesaurus +CREATE INDEX unique_by_heading_and_thesaurus ON authority.record_entry (authority.normalize_heading(marc)) WHERE deleted IS FALSE or deleted = FALSE; @@ -19148,6 +19183,1001 @@ INSERT INTO action_trigger.event_definition ( -- Speed up item-age browse axis (new books feed) CREATE INDEX cp_create_date ON asset.copy (create_date); +INSERT INTO config.upgrade_log (version) VALUES ('2.0.2'); +INSERT INTO config.upgrade_log (version) VALUES ('0484'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0485'); -- dbs + +INSERT INTO config.upgrade_log (version) VALUES ('2.0.3'); +INSERT INTO config.upgrade_log (version) VALUES ('0490'); -- miker + +INSERT INTO config.upgrade_log (version) VALUES ('0492'); --miker + +INSERT INTO config.upgrade_log (version) VALUES ('0494'); -- dbs + +INSERT INTO config.upgrade_log (version) VALUES ('2.0.4'); +INSERT INTO config.upgrade_log (version) VALUES ('0498'); + +UPDATE config.metabib_field + SET xpath = $$//mods32:mods/mods32:subject$$ + WHERE field_class = 'subject' AND name = 'complete'; + +UPDATE config.metabib_field + SET xpath = $$//marc:datafield[@tag='099']$$ + WHERE field_class = 'identifier' AND name = 'bibcn'; + +INSERT INTO config.upgrade_log (version) VALUES ('0496'); -- dbs + +UPDATE config.metabib_field + SET xpath = $$//marc:datafield[@tag='024' and @ind1='1']/marc:subfield[@code='a' or @code='z']$$ + WHERE field_class = 'identifier' AND name = 'upc'; + +UPDATE config.metabib_field + SET xpath = $$//marc:datafield[@tag='024' and @ind1='2']/marc:subfield[@code='a' or @code='z']$$ + WHERE field_class = 'identifier' AND name = 'ismn'; + +UPDATE config.metabib_field + SET xpath = $$//marc:datafield[@tag='024' and @ind1='3']/marc:subfield[@code='a' or @code='z']$$ + WHERE field_class = 'identifier' AND name = 'ean'; + +UPDATE config.metabib_field + SET xpath = $$//marc:datafield[@tag='024' and @ind1='0']/marc:subfield[@code='a' or @code='z']$$ + WHERE field_class = 'identifier' AND name = 'isrc'; + +UPDATE config.metabib_field + SET xpath = $$//marc:datafield[@tag='024' and @ind1='4']/marc:subfield[@code='a' or @code='z']$$ + WHERE field_class = 'identifier' AND name = 'sici'; + +-- Rather than polluting the public schema with general Evergreen +-- functions, carve out a dedicated schema +CREATE SCHEMA evergreen; + +-- Replace all uses of PostgreSQL's built-in LOWER() function with +-- a more locale-savvy PLPERLU evergreen.lowercase() function +CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$ + return lc(shift); +$$ LANGUAGE PLPERLU STRICT IMMUTABLE; + +-- update actor.usr_address indexes +DROP INDEX IF EXISTS actor.actor_usr_addr_street1_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_street2_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_city_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_state_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_post_code_idx; + +CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1)); +CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2)); +CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city)); +CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state)); +CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code)); + +-- update actor.usr indexes +DROP INDEX IF EXISTS actor.actor_usr_first_given_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_second_given_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_family_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_email_idx; +DROP INDEX IF EXISTS actor.actor_usr_day_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_evening_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_other_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_ident_value_idx; +DROP INDEX IF EXISTS actor.actor_usr_ident_value2_idx; + +CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name)); +CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name)); +CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name)); +CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email)); +CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone)); +CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone)); +CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone)); +CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value)); +CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2)); + +-- update actor.card indexes +DROP INDEX IF EXISTS actor.actor_card_barcode_evergreen_lowercase_idx; +CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode)); + +CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$ +DECLARE + attr RECORD; + attr_def RECORD; + eg_rec RECORD; + id_value TEXT; + exact_id BIGINT; +BEGIN + + DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; + + SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1; + + IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN + id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove); + + IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN + SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted; + SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1; + IF exact_id IS NOT NULL THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id); + END IF; + END IF; + END IF; + + IF exact_id IS NULL THEN + 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 + + -- All numbers? check for an id match + IF (attr.attr_value ~ $r$^\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- Looks like an ISBN? check for an isbn match + IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN + FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP + PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE; + IF FOUND THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record); + END IF; + END LOOP; + + -- subcheck for isbn-as-tcn + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for an OCLC tcn_value match + IF (attr.attr_value ~ $r$^o\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for a direct tcn_value match + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + + -- check for a direct item barcode match + FOR eg_rec IN + SELECT DISTINCT b.* + FROM biblio.record_entry b + JOIN asset.call_number cn ON (cn.record = b.id) + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE + LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + + END LOOP; + END IF; + + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0499'); + +CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$ + # Created after looking at the Koha C4::ClassSortRoutine::Generic module, + # thus could probably be considered a derived work, although nothing was + # directly copied - but to err on the safe side of providing attribution: + # Copyright (C) 2007 LibLime + # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar) + # Licensed under the GPL v2 or later + + use strict; + use warnings; + + # Converts the callnumber to uppercase + # Strips spaces from start and end of the call number + # Converts anything other than letters, digits, and periods into spaces + # Collapses multiple spaces into a single underscore + my $callnum = uc(shift); + $callnum =~ s/^\s//g; + $callnum =~ s/\s$//g; + # NOTE: this previously used underscores, but this caused sorting issues + # for the "before" half of page 0 on CN browse, sorting CNs containing a + # decimal before "whole number" CNs + $callnum =~ s/[^A-Z0-9_.]/ /g; + $callnum =~ s/ {2,}/ /g; + + return $callnum; +$func$ LANGUAGE PLPERLU; + +UPDATE asset.call_number SET id = id; + +INSERT INTO config.upgrade_log (version) VALUES ('0500'); + +CREATE OR REPLACE FUNCTION evergreen.change_db_setting(setting_name TEXT, settings TEXT[]) RETURNS VOID AS $$ +BEGIN +EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET ' || quote_ident(setting_name) || ' = ' || array_to_string(settings, ','); +END; +$$ LANGUAGE plpgsql; + +SELECT evergreen.change_db_setting('search_path', ARRAY['public','pg_catalog']); + +INSERT INTO config.upgrade_log (version) VALUES ('2.0.5'); +INSERT INTO config.upgrade_log (version) VALUES ('0502'); -- dbwells + +-- Dewey fields +UPDATE asset.call_number_class + SET field = '080ab,082ab,092abef' + WHERE id = 2 +; + +-- LC fields +UPDATE asset.call_number_class + SET field = '050ab,055ab,090abef' + WHERE id = 3 +; + +INSERT INTO config.upgrade_log (version) VALUES ('0505'); --miker + +CREATE OR REPLACE FUNCTION force_unicode_normal_form(string TEXT, form TEXT) RETURNS TEXT AS $func$ +use Unicode::Normalize 'normalize'; +return normalize($_[1],$_[0]); # reverse the params +$func$ LANGUAGE PLPERLU; + +UPDATE metabib.facet_entry SET value = force_unicode_normal_form(value,'NFC'); + +CREATE OR REPLACE FUNCTION facet_force_nfc() RETURNS TRIGGER AS $$ +BEGIN + NEW.value := force_unicode_normal_form(NEW.value,'NFC'); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER facet_force_nfc_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE facet_force_nfc(); + + +INSERT INTO config.upgrade_log (version) VALUES ('0506'); -- miker + +ALTER FUNCTION actor.org_unit_descendants( INT, INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_descendants( INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_ancestors( INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_full_path ( INT ) ROWS 2; +ALTER FUNCTION actor.org_unit_full_path ( INT, INT ) ROWS 2; +ALTER FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_common_ancestors ( INT, INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_ancestor_setting( TEXT, INT ) ROWS 1; +ALTER FUNCTION permission.grp_ancestors ( INT ) ROWS 1; +ALTER FUNCTION permission.usr_perms ( INT ) ROWS 10; +ALTER FUNCTION permission.usr_has_perm_at_nd ( INT, TEXT) ROWS 1; +ALTER FUNCTION permission.usr_has_perm_at_all_nd ( INT, TEXT ) ROWS 1; +ALTER FUNCTION permission.usr_has_perm_at ( INT, TEXT ) ROWS 1; +ALTER FUNCTION permission.usr_has_perm_at_all ( INT, TEXT ) ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE grp_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT pgt.parent, gad.distance+1 + FROM permission.grp_tree pgt JOIN grp_ancestors_distance gad ON (pgt.id = gad.id) + WHERE pgt.parent IS NOT NULL + ) + SELECT * FROM grp_ancestors_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE grp_descendants_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT pgt.id, gdd.distance+1 + FROM permission.grp_tree pgt JOIN grp_descendants_distance gdd ON (pgt.parent = gdd.id) + ) + SELECT * FROM grp_descendants_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE org_unit_descendants_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.id, oudd.distance+1 + FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON (ou.parent_ou = oudd.id) + ) + SELECT * FROM org_unit_descendants_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ + WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent_ou, ouad.distance+1 + FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent_ou IS NOT NULL + ) + SELECT * FROM org_unit_ancestors_distance; +$$ LANGUAGE SQL STABLE ROWS 1; + +INSERT INTO config.upgrade_log (version) VALUES ('2.0.6'); +INSERT INTO config.upgrade_log (version) VALUES ('0508'); -- gmc + +CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$ +DECLARE + use_id_for_tcn BOOLEAN; + norm_tcn_value TEXT; + norm_tcn_source TEXT; +BEGIN + -- Remove any existing 901 fields before we insert the authoritative one + NEW.marc := REGEXP_REPLACE(NEW.marc, E']*?tag="901".+?', '', 'g'); + + IF TG_TABLE_SCHEMA = 'biblio' THEN + -- Set TCN value to record ID? + SELECT enabled FROM config.global_flag INTO use_id_for_tcn + WHERE name = 'cat.bib.use_id_for_tcn'; + + IF use_id_for_tcn = 't' THEN + NEW.tcn_value := NEW.id; + norm_tcn_value := NEW.tcn_value; + ELSE + -- yes, ampersands can show up in tcn_values ... + norm_tcn_value := REGEXP_REPLACE(NEW.tcn_value, E'&(?!\\S+;)', '&', 'g'); + END IF; + -- ... and TCN sources + -- FIXME we have here yet another (stub) version of entityize + norm_tcn_source := REGEXP_REPLACE(NEW.tcn_source, E'&(?!\\S+;)', '&', 'g'); + + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || norm_tcn_value || E'' || + '' || norm_tcn_source || E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + CASE WHEN NEW.owner IS NOT NULL THEN '' || NEW.owner || E'' ELSE '' END || + CASE WHEN NEW.share_depth IS NOT NULL THEN '' || NEW.share_depth || E'' ELSE '' END || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'authority' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'serial' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + '' || NEW.owning_lib || E'' || + CASE WHEN NEW.record IS NOT NULL THEN '' || NEW.record || E'' ELSE '' END || + E'\\1' + ); + ELSE + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0509'); -- gmc + +CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$ + SELECT REPLACE(REPLACE(REPLACE($1, + '&', '&'), + '<', '<'), + '>', '>'); +$$ LANGUAGE SQL IMMUTABLE; + +CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$ +DECLARE + use_id_for_tcn BOOLEAN; +BEGIN + -- Remove any existing 901 fields before we insert the authoritative one + NEW.marc := REGEXP_REPLACE(NEW.marc, E']*?tag="901".+?', '', 'g'); + + IF TG_TABLE_SCHEMA = 'biblio' THEN + -- Set TCN value to record ID? + SELECT enabled FROM config.global_flag INTO use_id_for_tcn + WHERE name = 'cat.bib.use_id_for_tcn'; + + IF use_id_for_tcn = 't' THEN + NEW.tcn_value := NEW.id; + END IF; + + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || evergreen.xml_escape(NEW.tcn_value) || E'' || + '' || evergreen.xml_escape(NEW.tcn_source) || E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + CASE WHEN NEW.owner IS NOT NULL THEN '' || NEW.owner || E'' ELSE '' END || + CASE WHEN NEW.share_depth IS NOT NULL THEN '' || NEW.share_depth || E'' ELSE '' END || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'authority' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'serial' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + '' || NEW.owning_lib || E'' || + CASE WHEN NEW.record IS NOT NULL THEN '' || NEW.record || E'' ELSE '' END || + E'\\1' + ); + ELSE + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0510'); -- miker + +SELECT evergreen.change_db_setting('search_path', ARRAY['evergreen','public','pg_catalog']); + +-- Fix function breakage due to short search path +CREATE OR REPLACE FUNCTION evergreen.force_unicode_normal_form(string TEXT, form TEXT) RETURNS TEXT AS $func$ +use Unicode::Normalize 'normalize'; +return normalize($_[1],$_[0]); # reverse the params +$func$ LANGUAGE PLPERLU; + +CREATE OR REPLACE FUNCTION evergreen.facet_force_nfc() RETURNS TRIGGER AS $$ +BEGIN + NEW.value := force_unicode_normal_form(NEW.value,'NFC'); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +DROP TRIGGER facet_force_nfc_tgr ON metabib.facet_entry; + +CREATE TRIGGER facet_force_nfc_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE evergreen.facet_force_nfc(); + +DROP FUNCTION IF EXISTS public.force_unicode_normal_form (TEXT,TEXT); +DROP FUNCTION IF EXISTS public.facet_force_nfc (); + +CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$ + SELECT REPLACE(REPLACE(REPLACE($1, + '&', '&'), + '<', '<'), + '>', '>'); +$$ LANGUAGE SQL IMMUTABLE; + +CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$ +DECLARE + use_id_for_tcn BOOLEAN; +BEGIN + -- Remove any existing 901 fields before we insert the authoritative one + NEW.marc := REGEXP_REPLACE(NEW.marc, E']*?tag="901".+?', '', 'g'); + + IF TG_TABLE_SCHEMA = 'biblio' THEN + -- Set TCN value to record ID? + SELECT enabled FROM config.global_flag INTO use_id_for_tcn + WHERE name = 'cat.bib.use_id_for_tcn'; + + IF use_id_for_tcn = 't' THEN + NEW.tcn_value := NEW.id; + END IF; + + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || evergreen.xml_escape(NEW.tcn_value) || E'' || + '' || evergreen.xml_escape(NEW.tcn_source) || E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + CASE WHEN NEW.owner IS NOT NULL THEN '' || NEW.owner || E'' ELSE '' END || + CASE WHEN NEW.share_depth IS NOT NULL THEN '' || NEW.share_depth || E'' ELSE '' END || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'authority' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'serial' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + '' || NEW.owning_lib || E'' || + CASE WHEN NEW.record IS NOT NULL THEN '' || NEW.record || E'' ELSE '' END || + E'\\1' + ); + ELSE + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +DROP TRIGGER b_maintain_901 ON biblio.record_entry; +DROP TRIGGER b_maintain_901 ON authority.record_entry; +DROP TRIGGER b_maintain_901 ON serial.record_entry; + +CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901(); +CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901(); +CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901(); + +DROP FUNCTION IF EXISTS public.maintain_901 (); + +INSERT INTO config.upgrade_log (version) VALUES ('0511'); -- miker + +CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$ +DECLARE + copy_id BIGINT; +BEGIN + EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW; + PERFORM * FROM asset.copy WHERE id = copy_id; + IF NOT FOUND THEN + RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id; + END IF; + RETURN NULL; +END; +$F$ LANGUAGE PLPGSQL; + +CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy'); + +INSERT INTO config.upgrade_log (version) VALUES ('0516'); + +CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$ + SELECT extract_marc_field('acq.lineitem', $1, $2, $3); +$$ LANGUAGE SQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0517'); --miker + +INSERT INTO config.upgrade_log (version) VALUES ('0520'); --dbs +INSERT INTO config.upgrade_log (version) VALUES ('0521'); --dbs + +CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$ +DECLARE + uris TEXT[]; + uri_xml TEXT; + uri_label TEXT; + uri_href TEXT; + uri_use TEXT; + uri_owner_list TEXT[]; + uri_owner TEXT; + uri_owner_id INT; + uri_id INT; + uri_cn_id INT; + uri_map_id INT; +BEGIN + + -- Clear any URI mappings and call numbers for this bib. + -- This leads to acn / auricnm inflation, but also enables + -- old acn/auricnm's to go away and for bibs to be deleted. + FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP + DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id; + DELETE FROM asset.call_number WHERE id = uri_cn_id; + END LOOP; + + uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml); + IF ARRAY_UPPER(uris,1) > 0 THEN + FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP + -- First we pull info out of the 856 + uri_xml := uris[i]; + + uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1]; + uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1]; + uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL; + + -- Get the distinct list of libraries wanting to use + SELECT ARRAY_ACCUM( + DISTINCT REGEXP_REPLACE( + x, + $re$^.*?\((\w+)\).*$$re$, + E'\\1' + ) + ) INTO uri_owner_list + FROM UNNEST( + oils_xpath( + '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()', + uri_xml + ) + )x; + + IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN + + -- look for a matching uri + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + IF uri_use IS NULL THEN + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active; + ELSE + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + END IF; + END IF; + + FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP + uri_owner := uri_owner_list[j]; + + SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner; + CONTINUE WHEN NOT FOUND; + + -- we need a call number to link through + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + IF NOT FOUND THEN + INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label) + VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##'); + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + END IF; + + -- now, link them if they're not already + SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; + IF NOT FOUND THEN + INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); + END IF; + + END LOOP; + + END IF; + + END LOOP; + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0528'); -- dbs + +CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$ + + use MARC::Record; + use MARC::File::XML (BinaryEncoding => 'UTF-8'); + use MARC::Charset; + + MARC::Charset->assume_unicode(1); + + my $xml = shift; + my $r = MARC::Record->new_from_xml( $xml ); + + return undef unless ($r); + + my $id = shift() || $r->subfield( '901' => 'c' ); + $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/; + return undef unless ($id); # We need an ID! + + my $tmpl = MARC::Record->new(); + $tmpl->encoding( 'UTF-8' ); + + my @rule_fields; + for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record + + my $tag = $field->tag; + my $i1 = $field->indicator(1); + my $i2 = $field->indicator(2); + my $sf = join '', map { $_->[0] } $field->subfields; + my @data = map { @$_ } $field->subfields; + + my @replace_them; + + # Map the authority field to bib fields it can control. + if ($tag >= 100 and $tag <= 111) { # names + @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700); + } elsif ($tag eq '130') { # uniform title + @replace_them = qw/130 240 440 730 830/; + } elsif ($tag >= 150 and $tag <= 155) { # subjects + @replace_them = ($tag + 500); + } elsif ($tag >= 180 and $tag <= 185) { # floating subdivisions + @replace_them = qw/100 400 600 700 800 110 410 610 710 810 111 411 611 711 811 130 240 440 730 830 650 651 655/; + } else { + next; + } + + # Dummy up the bib-side data + $tmpl->append_fields( + map { + MARC::Field->new( $_, $i1, $i2, @data ) + } @replace_them + ); + + # Construct some 'replace' rules + push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them; + } + + # Insert the replace rules into the template + $tmpl->append_fields( + MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) ) + ); + + $xml = $tmpl->as_xml_record; + $xml =~ s/^<\?.+?\?>$//mo; + $xml =~ s/\n//sgo; + $xml =~ s/>\s+ 'UTF-8'); + use MARC::Charset; + use strict; + + MARC::Charset->assume_unicode(1); + + my $target_xml = shift; + my $source_xml = shift; + my $field_spec = shift; + my $force_add = shift || 0; + + my $target_r = MARC::Record->new_from_xml( $target_xml ); + my $source_r = MARC::Record->new_from_xml( $source_xml ); + + return $target_xml unless ($target_r && $source_r); + + my @field_list = split(',', $field_spec); + + my %fields; + for my $f (@field_list) { + $f =~ s/^\s*//; $f =~ s/\s*$//; + if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) { + my $field = $1; + $field =~ s/\s+//; + my $sf = $2; + $sf =~ s/\s+//; + my $match = $3; + $match =~ s/^\s*//; $match =~ s/\s*$//; + $fields{$field} = { sf => [ split('', $sf) ] }; + if ($match) { + my ($msf,$mre) = split('~', $match); + if (length($msf) > 0 and length($mre) > 0) { + $msf =~ s/^\s*//; $msf =~ s/\s*$//; + $mre =~ s/^\s*//; $mre =~ s/\s*$//; + $fields{$field}{match} = { sf => $msf, re => qr/$mre/ }; + } + } + } + } + + for my $f ( keys %fields) { + if ( @{$fields{$f}{sf}} ) { + for my $from_field ($source_r->field( $f )) { + my @tos = $target_r->field( $f ); + if (!@tos) { + next if (exists($fields{$f}{match}) and !$force_add); + my @new_fields = map { $_->clone } $source_r->field( $f ); + $target_r->insert_fields_ordered( @new_fields ); + } else { + for my $to_field (@tos) { + if (exists($fields{$f}{match})) { + next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf})); + } + my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}}; + $to_field->add_subfields( @new_sf ); + } + } + } + } else { + my @new_fields = map { $_->clone } $source_r->field( $f ); + $target_r->insert_fields_ordered( @new_fields ); + } + } + + $target_xml = $target_r->as_xml_record; + $target_xml =~ s/^<\?.+?\?>$//mo; + $target_xml =~ s/\n//sgo; + $target_xml =~ s/>\s+ 'UTF-8'); + use MARC::Charset; + use strict; + + MARC::Charset->assume_unicode(1); + + my $xml = shift; + my $r = MARC::Record->new_from_xml( $xml ); + + return $xml unless ($r); + + my $field_spec = shift; + my @field_list = split(',', $field_spec); + + my %fields; + for my $f (@field_list) { + $f =~ s/^\s*//; $f =~ s/\s*$//; + if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) { + my $field = $1; + $field =~ s/\s+//; + my $sf = $2; + $sf =~ s/\s+//; + my $match = $3; + $match =~ s/^\s*//; $match =~ s/\s*$//; + $fields{$field} = { sf => [ split('', $sf) ] }; + if ($match) { + my ($msf,$mre) = split('~', $match); + if (length($msf) > 0 and length($mre) > 0) { + $msf =~ s/^\s*//; $msf =~ s/\s*$//; + $mre =~ s/^\s*//; $mre =~ s/\s*$//; + $fields{$field}{match} = { sf => $msf, re => qr/$mre/ }; + } + } + } + } + + for my $f ( keys %fields) { + for my $to_field ($r->field( $f )) { + if (exists($fields{$f}{match})) { + next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf})); + } + + if ( @{$fields{$f}{sf}} ) { + $to_field->delete_subfield(code => $fields{$f}{sf}); + } else { + $r->delete_field( $to_field ); + } + } + } + + $xml = $r->as_xml_record; + $xml =~ s/^<\?.+?\?>$//mo; + $xml =~ s/\n//sgo; + $xml =~ s/>\s+ 'UTF-8'); +use MARC::Charset; + +MARC::Charset->assume_unicode(1); + +my $xml = shift; +my $r = MARC::Record->new_from_xml( $xml ); + +return_next( { tag => 'LDR', value => $r->leader } ); + +for my $f ( $r->fields ) { + if ($f->is_control_field) { + return_next({ tag => $f->tag, value => $f->data }); + } else { + for my $s ($f->subfields) { + return_next({ + tag => $f->tag, + ind1 => $f->indicator(1), + ind2 => $f->indicator(2), + subfield => $s->[0], + value => $s->[1] + }); + + if ( $f->tag eq '245' and $s->[0] eq 'a' ) { + my $trim = $f->indicator(2) || 0; + return_next({ + tag => 'tnf', + ind1 => $f->indicator(1), + ind2 => $f->indicator(2), + subfield => 'a', + value => substr( $s->[1], $trim ) + }); + } + } + } +} + +return undef; + +$func$ LANGUAGE PLPERLU; + +CREATE OR REPLACE FUNCTION authority.flatten_marc ( TEXT ) RETURNS SETOF authority.full_rec AS $func$ + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); +use MARC::Charset; + +MARC::Charset->assume_unicode(1); + +my $xml = shift; +my $r = MARC::Record->new_from_xml( $xml ); + +return_next( { tag => 'LDR', value => $r->leader } ); + +for my $f ( $r->fields ) { + if ($f->is_control_field) { + return_next({ tag => $f->tag, value => $f->data }); + } else { + for my $s ($f->subfields) { + return_next({ + tag => $f->tag, + ind1 => $f->indicator(1), + ind2 => $f->indicator(2), + subfield => $s->[0], + value => $s->[1] + }); + + } + } +} + +return undef; + +$func$ LANGUAGE PLPERLU; + +INSERT INTO config.upgrade_log (version) VALUES ('0529'); + +INSERT INTO config.org_unit_setting_type +( name, label, description, datatype ) VALUES +( 'circ.user_merge.delete_addresses', + 'Circ: Patron Merge Address Delete', + 'Delete address(es) of subordinate user(s) in a patron merge', + 'bool' +); + +INSERT INTO config.org_unit_setting_type +( name, label, description, datatype ) VALUES +( 'circ.user_merge.delete_cards', + 'Circ: Patron Merge Barcode Delete', + 'Delete barcode(s) of subordinate user(s) in a patron merge', + 'bool' +); + +INSERT INTO config.org_unit_setting_type +( name, label, description, datatype ) VALUES +( 'circ.user_merge.deactivate_cards', + 'Circ: Patron Merge Deactivate Card', + 'Mark barcode(s) of subordinate user(s) in a patron merge as inactive', + 'bool' +); + -- Speed up call number browsing CREATE INDEX asset_call_number_label_sortkey_browse ON asset.call_number(oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE deleted IS FALSE OR deleted = FALSE; -- 2.11.0