From: Mike Rylander Date: Mon, 2 Apr 2012 14:21:32 +0000 (-0400) Subject: Stamping upgrade script for serials holdings display improvements X-Git-Url: http://git.equinoxoli.org/?p=transitory.git;a=commitdiff_plain;h=a52131145f41fe3f19f5c4ae5ccf4cad392c1cf0 Stamping upgrade script for serials holdings display improvements Signed-off-by: Mike Rylander --- diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 4c4379a..58d8db7 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -87,7 +87,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0699', :eg_version); -- phasefx/miker +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0700', :eg_version); -- senator/dbwells/miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql b/Open-ILS/src/sql/Pg/upgrade/0700.schema.serial-holding-groups.sql similarity index 98% rename from Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql rename to Open-ILS/src/sql/Pg/upgrade/0700.schema.serial-holding-groups.sql index 27246a1..077a64f 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0700.schema.serial-holding-groups.sql @@ -1,6 +1,6 @@ BEGIN; -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); +SELECT evergreen.upgrade_deps_block_check('0700', :eg_version); INSERT INTO config.internal_flag (name, value, enabled) VALUES ( 'serial.rematerialize_on_same_holding_code', NULL, FALSE diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.1-2.2-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.1-2.2-upgrade-db.sql index 2236318..aa3f94d 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/2.1-2.2-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/2.1-2.2-upgrade-db.sql @@ -15099,4 +15099,156 @@ INSERT INTO config.org_unit_setting_type ( name, label, description, datatype, g ); +SELECT evergreen.upgrade_deps_block_check('0700', :eg_version); + +INSERT INTO config.internal_flag (name, value, enabled) VALUES ( + 'serial.rematerialize_on_same_holding_code', NULL, FALSE +); + +INSERT INTO config.org_unit_setting_type ( + name, label, grp, description, datatype +) VALUES ( + 'serial.default_display_grouping', + 'Default display grouping for serials distributions presented in the OPAC.', + 'serial', + 'Default display grouping for serials distributions presented in the OPAC. This can be "enum" or "chron".', + 'string' +); + +ALTER TABLE serial.distribution + ADD COLUMN display_grouping TEXT NOT NULL DEFAULT 'chron' + CHECK (display_grouping IN ('enum', 'chron')); + +-- why didn't we just make one summary table in the first place? +CREATE VIEW serial.any_summary AS + SELECT + 'basic' AS summary_type, id, distribution, + generated_coverage, textual_holdings, show_generated + FROM serial.basic_summary + UNION + SELECT + 'index' AS summary_type, id, distribution, + generated_coverage, textual_holdings, show_generated + FROM serial.index_summary + UNION + SELECT + 'supplement' AS summary_type, id, distribution, + generated_coverage, textual_holdings, show_generated + FROM serial.supplement_summary ; + + +-- Given the IDs of two rows in actor.org_unit, *the second being an ancestor +-- of the first*, return in array form the path from the ancestor to the +-- descendant, with each point in the path being an org_unit ID. This is +-- useful for sorting org_units by their position in a depth-first (display +-- order) representation of the tree. +-- +-- This breaks with the precedent set by actor.org_unit_full_path() and others, +-- and gets the parameters "backwards," but otherwise this function would +-- not be very usable within json_query. +CREATE OR REPLACE FUNCTION actor.org_unit_simple_path(INT, INT) +RETURNS INT[] AS $$ + WITH RECURSIVE descendant_depth(id, path) AS ( + SELECT aou.id, + ARRAY[aou.id] + FROM actor.org_unit aou + JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type) + WHERE aou.id = $2 + UNION ALL + SELECT aou.id, + dd.path || ARRAY[aou.id] + FROM actor.org_unit aou + JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type) + JOIN descendant_depth dd ON (dd.id = aou.parent_ou) + ) SELECT dd.path + FROM actor.org_unit aou + JOIN descendant_depth dd USING (id) + WHERE aou.id = $1 ORDER BY dd.path; +$$ LANGUAGE SQL STABLE; + +CREATE TABLE serial.materialized_holding_code ( + id BIGSERIAL PRIMARY KEY, + issuance INTEGER NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE, + subfield CHAR, + value TEXT +); + +CREATE OR REPLACE FUNCTION serial.materialize_holding_code() RETURNS TRIGGER +AS $func$ +use strict; + +use MARC::Field; +use JSON::XS; + +# Do nothing if holding_code has not changed... + +if ($_TD->{new}{holding_code} eq $_TD->{old}{holding_code}) { + # ... unless the following internal flag is set. + + my $flag_rv = spi_exec_query(q{ + SELECT * FROM config.internal_flag + WHERE name = 'serial.rematerialize_on_same_holding_code' AND enabled + }, 1); + return unless $flag_rv->{processed}; +} + + +my $holding_code = (new JSON::XS)->decode($_TD->{new}{holding_code}); + +my $field = new MARC::Field('999', @$holding_code); # tag doesnt matter + +my $dstmt = spi_prepare( + 'DELETE FROM serial.materialized_holding_code WHERE issuance = $1', + 'INT' +); +spi_exec_prepared($dstmt, $_TD->{new}{id}); + +my $istmt = spi_prepare( + q{ + INSERT INTO serial.materialized_holding_code ( + issuance, subfield, value + ) VALUES ($1, $2, $3) + }, qw{INT CHAR TEXT} +); + +foreach ($field->subfields) { + spi_exec_prepared( + $istmt, + $_TD->{new}{id}, + $_->[0], + $_->[1] + ); +} + +return; + +$func$ LANGUAGE 'plperlu'; + +CREATE INDEX assist_holdings_display + ON serial.materialized_holding_code (issuance, subfield); + +CREATE TRIGGER materialize_holding_code + AFTER INSERT OR UPDATE ON serial.issuance + FOR EACH ROW EXECUTE PROCEDURE serial.materialize_holding_code() ; + +-- starting here, we materialize all existing holding codes. + +UPDATE config.internal_flag + SET enabled = TRUE + WHERE name = 'serial.rematerialize_on_same_holding_code'; + +UPDATE serial.issuance SET holding_code = holding_code; + +UPDATE config.internal_flag + SET enabled = FALSE + WHERE name = 'serial.rematerialize_on_same_holding_code'; + +-- finish holding code materialization process + +-- fix up missing holding_code fields from serial.issuance +UPDATE serial.issuance siss + SET holding_type = scap.type + FROM serial.caption_and_pattern scap + WHERE scap.id = siss.caption_and_pattern AND siss.holding_type IS NULL; + COMMIT;