BEGIN; 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;