From f997e0122a903e382889fbdedec3029fc581405a Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Mon, 27 Feb 2012 15:15:10 -0500 Subject: [PATCH] break out holding code into materialized view otherwise it's really awkward to get at the information in the json-encoded array stored in a single column that hols the holding code, which we must break apart to build a year->month->day tree or a v.->no.->iss. tree or whatever. example sql to help me remember where I'm going (not a final version of anything): -- if nothing expanded: select sdist.display_grouping, siss.id, siss.label, smhc.value from serial.basic_summary sbsum join serial.distribution sdist on (sdist.id = sbsum.distribution) join serial.stream sstr on (sstr.distribution = sdist.id) join serial.item sitem on (sitem.stream = sstr.id) join serial.issuance siss on (siss.id = sitem.issuance) join serial.materialized_holding_code smhc on (smhc.issuance = siss.id) where smhc.subfield = 'i'; Signed-off-by: Lebbeous Fogle-Weekley --- .../src/perlmods/lib/OpenILS/Application/Serial.pm | 26 ++++++++- .../upgrade/XXXX.schema.serial-holding-groups.sql | 67 +++++++++++++++++++++- 2 files changed, 89 insertions(+), 4 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Serial.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Serial.pm index be4621efaa..95536db55b 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Serial.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Serial.pm @@ -61,6 +61,9 @@ my %MFHD_NAMES_BY_TAG = ( '853' => $MFHD_NAMES[0], my %MFHD_TAGS_BY_NAME = ( $MFHD_NAMES[0] => '853', $MFHD_NAMES[1] => '854', $MFHD_NAMES[2] => '855'); +my @MFHD_ENUM_SUBFIELDS = qw/abcdef/; # $g and $h intentionally omitted +my @MFHD_CHRON_SUBFIELDS = qw/ijklm/; + my $_strp_date = new DateTime::Format::Strptime(pattern => '%F'); # helper method for conforming dates to ISO8601 @@ -853,7 +856,20 @@ __PACKAGE__->register_method( ); sub grouped_holdings_for_summary { - my ($self, $client, $summary_type, $summary_id, $limit, $offset) = @_; + my ( + $self, $client, + $summary_type, $summary_id, $expand_path, $limits, $offsets + ) = @_; + + # fetch summary and flesh sdist. From that, get display_grouping (chron or enum) + + # get scap (lots of joins to get there) + + # get unique values from each field of received items' issuances' holding + # codes and build a tree to group them + + # only go as far as expand_path dictates in grouping issuances. + # scalar(@$expand_path) + 1, up to (num_grouping_fields - 1) } __PACKAGE__->register_method( @@ -865,7 +881,13 @@ __PACKAGE__->register_method( desc => q/Return a tree of holdings associated with a given summary grouped by all but the last of either chron or enum units./, params => [ - # XXX ? + { name => "summary_type", type => "string" }, + { name => "summary_id", type => "number" }, + { name => "expand_path", type => "array" }, + { name => "limits", type => "array", desc => + "This should be one element longer than expand_path" }, + { name => "offsets", type => "array", desc => + "This should be one element longer than expand_path" } ] } ); 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 becb69de62..b1eccd79c2 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 @@ -3,7 +3,7 @@ BEGIN; SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); ALTER TABLE serial.distribution - ADD COLUMN display_grouping TEXT NOT NULL DEFAULT 'enum' + 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? @@ -53,5 +53,68 @@ RETURNS INT[] AS $$ WHERE aou.id = $1 ORDER BY dd.path; $$ LANGUAGE SQL; +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 +); -COMMIT; +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. +# XXX may need to add a global flag that acts like 'ingest.reingest.force_on_same_marc' +if ($_TD->{new}{holding_code} eq $_TD->{old}{holding_code}) { + return; +} + +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 TRIGGER materialize_holding_code + AFTER INSERT OR UPDATE ON serial.issuance + FOR EACH ROW EXECUTE PROCEDURE serial.materialize_holding_code() ; + +-- XXX need to materialize_holding_code() for all existing rows in serial.issuance +-- at time of upgrde script execution as well. + +COMMIT'; -- 2.11.0