From 2ead8999ce419f3c85d218ed67c232a5f1687f24 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Wed, 13 Mar 2013 19:20:24 -0400 Subject: [PATCH] starting the pre-upgrade script afresh --- .../version-upgrade/pines-pre-2.1-2.2-upgrade.sql | 359 --------------------- 1 file changed, 359 deletions(-) diff --git a/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql b/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql index 665757a984..55dca4fa79 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql @@ -1,38 +1,5 @@ -- To be run before the 2.1.1-2.1.2 upgrade script --- remove replication schema -DROP SCHEMA _replication CASCADE; - --- For preventing invalid marcxml - possible supplement or replacement for biblio.check_marcxml_well_formed? --- Should be useful for flagging bad MARCXML that has slipped in over time - -CREATE OR REPLACE FUNCTION is_valid_marcxml(bibid BIGINT) RETURNS BOOL 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 $marc = spi_exec_query('SELECT marc FROM biblio.record_entry WHERE id = ' . $_[0]); -if (!$marc->{processed}) { - return 0; -} - -eval { - my $record = MARC::Record->new_from_xml($marc->{rows}[0]->{marc}); -}; -if ($@) { - elog(WARNING, $@); - return 0; -} - -return 1; - -$func$ LANGUAGE PLPERLU; - -- create a missing schema SELECT auditor.create_auditor ( 'serial', 'unit' ); CREATE INDEX aud_serial_unit_hist_creator_idx ON auditor.serial_unit_history ( creator ); @@ -47,329 +14,3 @@ UPDATE biblio.record_entry ALTER TABLE biblio.record_entry ENABLE TRIGGER ALL; --- disable triggers on asset.copy -ALTER TABLE asset.copy DISABLE TRIGGER ALL; - --- enable dependency tracking - --- DROP objects that might have existed from a prior run of 0526 --- Yes this is ironic. -DROP TABLE IF EXISTS config.db_patch_dependencies; -ALTER TABLE config.upgrade_log DROP COLUMN applied_to; -DROP FUNCTION evergreen.upgrade_list_applied_deprecates(TEXT); -DROP FUNCTION evergreen.upgrade_list_applied_supersedes(TEXT); - -BEGIN; - -INSERT INTO config.upgrade_log (version) VALUES ('0526'); --miker - -CREATE TABLE config.db_patch_dependencies ( - db_patch TEXT PRIMARY KEY, - supersedes TEXT[], - deprecates TEXT[] -); - -CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$ -DECLARE - fld TEXT; - cnt INT; -BEGIN - fld := TG_ARGV[1]; - EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW; - IF cnt > 0 THEN - RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME; - END IF; - RETURN NEW; -END; -$$ LANGUAGE PLPGSQL; - -CREATE TRIGGER no_overlapping_sups - BEFORE INSERT OR UPDATE ON config.db_patch_dependencies - FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes'); - -CREATE TRIGGER no_overlapping_deps - BEFORE INSERT OR UPDATE ON config.db_patch_dependencies - FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); - -ALTER TABLE config.upgrade_log - ADD COLUMN applied_to TEXT; - --- Provide a named type for patching functions -CREATE TYPE evergreen.patch AS (patch TEXT); - --- List applied db patches that are deprecated by (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ - SELECT DISTINCT l.version - FROM config.upgrade_log l - JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates) - WHERE d.db_patch = $1 -$$ LANGUAGE SQL; - --- List applied db patches that are superseded by (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ - SELECT DISTINCT l.version - FROM config.upgrade_log l - JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes) - WHERE d.db_patch = $1 -$$ LANGUAGE SQL; - --- List applied db patches that deprecates (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$ - SELECT db_patch - FROM config.db_patch_dependencies - WHERE ARRAY[$1]::TEXT[] && deprecates -$$ LANGUAGE SQL; - --- List applied db patches that supersedes (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$ - SELECT db_patch - FROM config.db_patch_dependencies - WHERE ARRAY[$1]::TEXT[] && supersedes -$$ LANGUAGE SQL; - --- Make sure that no deprecated or superseded db patches are currently applied -CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$ - SELECT COUNT(*) = 0 - FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x -$$ LANGUAGE SQL; - --- Raise an exception if there are, in fact, dep/sup confilct -CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$ -DECLARE - deprecates TEXT; - supersedes TEXT; -BEGIN - IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN - SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch); - SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch); - RAISE EXCEPTION ' -Upgrade script % can not be applied: - applied deprecated scripts % - applied superseded scripts % - deprecated by % - superseded by %', - my_db_patch, - ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)), - ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)), - evergreen.upgrade_list_applied_deprecated(my_db_patch), - evergreen.upgrade_list_applied_superseded(my_db_patch); - END IF; - - INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to); - RETURN TRUE; -END; -$$ LANGUAGE PLPGSQL; - -COMMIT; -BEGIN; - -INSERT INTO config.upgrade_log (version) VALUES ('0537'); --miker - -DROP FUNCTION evergreen.upgrade_deps_block_check(text,text); -DROP FUNCTION evergreen.upgrade_verify_no_dep_conflicts(text); -DROP FUNCTION evergreen.upgrade_list_applied_deprecated(text); -DROP FUNCTION evergreen.upgrade_list_applied_superseded(text); - --- List applied db patches that deprecates (and block the application of) my_db_patch -CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$ - SELECT db_patch - FROM config.db_patch_dependencies - WHERE ARRAY[$1]::TEXT[] && deprecates -$$ LANGUAGE SQL; - --- List applied db patches that supersedes (and block the application of) my_db_patch -CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$ - SELECT db_patch - FROM config.db_patch_dependencies - WHERE ARRAY[$1]::TEXT[] && supersedes -$$ LANGUAGE SQL; - --- Make sure that no deprecated or superseded db patches are currently applied -CREATE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$ - SELECT COUNT(*) = 0 - FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x -$$ LANGUAGE SQL; - --- Raise an exception if there are, in fact, dep/sup confilct -CREATE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$ -BEGIN - IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN - RAISE EXCEPTION ' -Upgrade script % can not be applied: - applied deprecated scripts % - applied superseded scripts % - deprecated by % - superseded by %', - my_db_patch, - ARRAY_ACCUM(evergreen.upgrade_list_applied_deprecates(my_db_patch)), - ARRAY_ACCUM(evergreen.upgrade_list_applied_supersedes(my_db_patch)), - evergreen.upgrade_list_applied_deprecated(my_db_patch), - evergreen.upgrade_list_applied_superseded(my_db_patch); - END IF; - - INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to); - RETURN TRUE; -END; -$$ LANGUAGE PLPGSQL; - -COMMIT; - --- adding 0587, which never made it into the 1.6.1-2.1 upgrade process :-/ - -BEGIN; - -SELECT evergreen.upgrade_deps_block_check('0587', :eg_version); - -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}; - -# Short-circuit if maintaining control numbers per MARC21 spec is not enabled -my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'"); -if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') { - return; -} - -# Get the control number identifier from an OU setting based on $_TD->{new}{owner} -my $ou_cni = 'EVRGRN'; - -my $owner; -if ($schema eq 'serial') { - $owner = $_TD->{new}{owning_lib}; -} else { - # are.owner and bre.owner can be null, so fall back to the consortial setting - $owner = $_TD->{new}{owner} || 1; -} - -my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)"); -if ($ous_rv->{processed}) { - $ou_cni = $ous_rv->{rows}[0]->{value}; - $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting" -} else { - # Fall back to the shortname of the OU if there was no OU setting - $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner"); - if ($ous_rv->{processed}) { - $ou_cni = $ous_rv->{rows}[0]->{shortname}; - } -} - -my ($create, $munge) = (0, 0); - -my @scns = $record->field('035'); - -foreach my $id_field ('001', '003') { - my $spec_value; - my @controls = $record->field($id_field); - - if ($id_field eq '001') { - $spec_value = $rec_id; - } else { - $spec_value = $ou_cni; - } - - # Create the 001/003 if none exist - if (scalar(@controls) == 1) { - # Only one field; check to see if we need to munge it - unless (grep $_->data() eq $spec_value, @controls) { - $munge = 1; - } - } else { - # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match - foreach my $control (@controls) { - $record->delete_field($control); - } - $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value)); - $create = 1; - } -} - -my $cn = $record->field('001')->data(); -# Special handling of OCLC numbers, often found in records that lack 003 -if ($cn =~ /^oc[nm]/) { - $cn =~ s/^oc[nm]0*(\d+)/$1/; - $record->field('003')->data('OCoLC'); - $create = 0; -} - -# Now, if we need to munge the 001, we will first push the existing 001/003 -# into the 035; but if the record did not have one (and one only) 001 and 003 -# to begin with, skip this process -if ($munge and not $create) { - - my $scn = "(" . $record->field('003')->data() . ")" . $cn; - - # Do not create duplicate 035 fields - unless (grep $_->subfield('a') eq $scn, @scns) { - $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn)); - } -} - -# Set the 001/003 and update the MARC -if ($create or $munge) { - $record->field('001')->data($rec_id); - $record->field('003')->data($ou_cni); - - my $xml = $record->as_xml_record(); - $xml =~ s/\n//sgo; - $xml =~ s/^<\?xml.+\?\s*>//go; - $xml =~ s/>\s+entityize() - # to avoid having to set PERL5LIB for PostgreSQL as well - - # If we are going to convert non-ASCII characters to XML entities, - # we had better be dealing with a UTF8 string to begin with - $xml = decode_utf8($xml); - - $xml = NFC($xml); - - # Convert raw ampersands to entities - $xml =~ s/&(?!\S+;)/&/gso; - - # Convert Unicode characters to entities - $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe; - - $xml =~ s/[\x00-\x1f]//go; - $_TD->{new}{marc} = $xml; - - return "MODIFY"; -} - -return; -$func$ LANGUAGE PLPERLU; - -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath) VALUES - (29, 'identifier', 'scn', oils_i18n_gettext(28, 'System Control Number', 'cmf', 'label'), 'marcxml', $$//marc:datafield[@tag='035']/marc:subfield[@code="a"]$$); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath) VALUES - (30, 'identifier', 'lccn', oils_i18n_gettext(28, 'LC Control Number', 'cmf', 'label'), 'marcxml', $$//marc:datafield[@tag='010']/marc:subfield[@code="a" or @code='z']$$); - ----- Far from perfect, but much faster than reingesting every record ---INSERT INTO metabib.identifier_field_entry(source, field, value) --- SELECT record, 29, value FROM metabib.full_rec WHERE tag = '035' AND subfield = 'a'; ---INSERT INTO metabib.identifier_field_entry(source, field, value) --- SELECT record, 30, value FROM metabib.full_rec WHERE tag = '010' AND subfield IN ('a', 'z'); - -COMMIT; -- 2.11.0