From 389a7ddf4d4655bff2005a582e0677123ee41955 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Wed, 7 Mar 2012 14:02:47 -0500 Subject: [PATCH] everything in upgrade script that belongs ported to baseline schema Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/002.schema.config.sql | 1 + Open-ILS/src/sql/Pg/020.schema.functions.sql | 29 +++++++ Open-ILS/src/sql/Pg/210.schema.serials.sql | 89 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 16 +++- .../upgrade/XXXX.schema.serial-holding-groups.sql | 2 +- 5 files changed, 135 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 112a50b1a6..244dcec2ed 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -46,6 +46,7 @@ INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor'); INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry'); INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only'); +INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code'); CREATE TABLE config.global_flag ( label TEXT NOT NULL diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index ca4306eb2e..de70132ead 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -221,6 +221,35 @@ CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS FROM actor.org_unit_ancestors($2); $$ LANGUAGE SQL STABLE ROWS 1; +-- 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 ROWS 1; + CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$ SELECT COUNT(id)::INT FROM ( SELECT id FROM actor.org_unit_combined_ancestors($1, $2) diff --git a/Open-ILS/src/sql/Pg/210.schema.serials.sql b/Open-ILS/src/sql/Pg/210.schema.serials.sql index 29617cd968..8a82141e66 100644 --- a/Open-ILS/src/sql/Pg/210.schema.serials.sql +++ b/Open-ILS/src/sql/Pg/210.schema.serials.sql @@ -105,6 +105,8 @@ CREATE TABLE serial.distribution ( REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, label TEXT NOT NULL, + display_grouping TEXT NOT NULL DEFAULT 'chron' + CHECK (display_grouping IN ('enum', 'chron')), receive_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, receive_unit_template INT REFERENCES asset.copy_template (id) @@ -326,5 +328,92 @@ CREATE TABLE serial.index_summary ( ); CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution); +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 ; + + +CREATE TABLE serial.materialized_holding_code ( + id BIGSERIAL PRIMARY KEY, + issuance INTEGER NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE, + holding_type TEXT NOT NULL, + ind1 TEXT, + ind2 TEXT, + 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, holding_type, ind1, ind2, subfield, value + ) VALUES ($1, $2, $3, $4, $5, $6) + }, qw{INT TEXT TEXT TEXT CHAR TEXT} +); + +foreach ($field->subfields) { + spi_exec_prepared( + $istmt, + $_TD->{new}{id}, + $_TD->{new}{holding_type}, + $field->indicator(1), + $field->indicator(2), + $_->[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() ; COMMIT; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 48964754e4..0c71c9d367 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -4625,6 +4625,21 @@ INSERT into config.org_unit_setting_type 'description' ), 'bool', null) +,( 'serial.default_display_grouping', 'serial' + oils_i18n_gettext( + 'serial.default_display_grouping', + 'Default display grouping for serials distributions presented in the OPAC.', + 'coust', + 'label' + ), + oils_i18n_gettext( + 'serial.default_display_grouping', + 'Default display grouping for serials distributions presented in the OPAC. This can be "enum" or "chron".', + 'coust', + 'description' + ), + 'string', null) + ; UPDATE config.org_unit_setting_type @@ -10041,7 +10056,6 @@ INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,dat ), 'string' ); - SELECT setval( 'config.sms_carrier_id_seq', 1000 ); INSERT INTO config.sms_carrier VALUES diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql index 5aaf1fe06e..f27fc79078 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.serial-holding-groups.sql @@ -65,7 +65,7 @@ RETURNS INT[] AS $$ FROM actor.org_unit aou JOIN descendant_depth dd USING (id) WHERE aou.id = $1 ORDER BY dd.path; -$$ LANGUAGE SQL; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE TABLE serial.materialized_holding_code ( id BIGSERIAL PRIMARY KEY, -- 2.11.0