From 88edc2e5bcd3d9ac3fcffd4daca98d09627b0a37 Mon Sep 17 00:00:00 2001 From: gmc Date: Tue, 2 Nov 2010 13:02:10 +0000 Subject: [PATCH] don't break up title and author when ingesting acquisitions MARC Note that this can be done more cleanly once oils_xpath groks XPATH 2.0 functions, in particular string-join. Signed-off-by: Galen Charlton git-svn-id: svn://svn.open-ils.org/ILS/trunk@18574 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 99 ++++++++++--------- Open-ILS/src/sql/Pg/200.schema.acq.sql | 37 ++++--- ...hema.acq-tweak-lineitem-attr-splitting.sql | 68 +++++++++++++ 4 files changed, 148 insertions(+), 58 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 32b31c1fb9..3ceb7f0049 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0454'); -- dbs +INSERT INTO config.upgrade_log (version) VALUES ('0455'); -- gmc CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index a2216a00d7..5dbe40096e 100644 --- a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -14777,53 +14777,64 @@ CREATE TABLE acq.claim_policy_action ( CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$ DECLARE - value TEXT; - atype TEXT; - prov INT; - pos INT; - adef RECORD; - xpath_string TEXT; + value TEXT; + atype TEXT; + prov INT; + pos INT; + adef RECORD; + xpath_string TEXT; BEGIN - FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP - - SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid; - - IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN - IF (atype = 'lineitem_provider_attr_definition') THEN - SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id; - CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider; - END IF; - - IF (atype = 'lineitem_provider_attr_definition') THEN - SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id; - ELSIF (atype = 'lineitem_marc_attr_definition') THEN - SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id; - ELSIF (atype = 'lineitem_generated_attr_definition') THEN - SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id; - END IF; - + FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP + + SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid; + + IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN + IF (atype = 'lineitem_provider_attr_definition') THEN + SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id; + CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider; + END IF; + + IF (atype = 'lineitem_provider_attr_definition') THEN + SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id; + ELSIF (atype = 'lineitem_marc_attr_definition') THEN + SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id; + ELSIF (atype = 'lineitem_generated_attr_definition') THEN + SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id; + END IF; + xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,''); - - pos := 1; - - LOOP - SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; - - IF (value IS NOT NULL AND value <> '') THEN - INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) - VALUES (NEW.id, adef.id, atype, adef.code, value); - ELSE - EXIT; + + IF (adef.code = 'title' OR adef.code = 'author') THEN + -- title and author should not be split + -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use + -- string-join in the xpath and remove this special case + SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; + IF (value IS NOT NULL AND value <> '') THEN + INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) + VALUES (NEW.id, adef.id, atype, adef.code, value); END IF; - - pos := pos + 1; - END LOOP; - - END IF; - - END LOOP; - - RETURN NULL; + ELSE + pos := 1; + + LOOP + SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; + + IF (value IS NOT NULL AND value <> '') THEN + INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) + VALUES (NEW.id, adef.id, atype, adef.code, value); + ELSE + EXIT; + END IF; + + pos := pos + 1; + END LOOP; + END IF; + + END IF; + + END LOOP; + + RETURN NULL; END; $function$ LANGUAGE PLPGSQL; diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index bb08fbef57..6184c01b32 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -1073,20 +1073,31 @@ BEGIN xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,''); - pos := 1; - - LOOP - SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; - + IF (adef.code = 'title' OR adef.code = 'author') THEN + -- title and author should not be split + -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use + -- string-join in the xpath and remove this special case + SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; IF (value IS NOT NULL AND value <> '') THEN - INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) - VALUES (NEW.id, adef.id, atype, adef.code, value); - ELSE - EXIT; - END IF; - - pos := pos + 1; - END LOOP; + INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) + VALUES (NEW.id, adef.id, atype, adef.code, value); + END IF; + ELSE + pos := 1; + + LOOP + SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; + + IF (value IS NOT NULL AND value <> '') THEN + INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) + VALUES (NEW.id, adef.id, atype, adef.code, value); + ELSE + EXIT; + END IF; + + pos := pos + 1; + END LOOP; + END IF; END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql b/Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql new file mode 100644 index 0000000000..ba34c3eba9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql @@ -0,0 +1,68 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0455'); -- gmc + +CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$ +DECLARE + value TEXT; + atype TEXT; + prov INT; + pos INT; + adef RECORD; + xpath_string TEXT; +BEGIN + FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP + + SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid; + + IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN + IF (atype = 'lineitem_provider_attr_definition') THEN + SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id; + CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider; + END IF; + + IF (atype = 'lineitem_provider_attr_definition') THEN + SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id; + ELSIF (atype = 'lineitem_marc_attr_definition') THEN + SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id; + ELSIF (atype = 'lineitem_generated_attr_definition') THEN + SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id; + END IF; + + xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,''); + + IF (adef.code = 'title' OR adef.code = 'author') THEN + -- title and author should not be split + -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use + -- string-join in the xpath and remove this special case + SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; + IF (value IS NOT NULL AND value <> '') THEN + INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) + VALUES (NEW.id, adef.id, atype, adef.code, value); + END IF; + ELSE + pos := 1; + + LOOP + SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; + + IF (value IS NOT NULL AND value <> '') THEN + INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value) + VALUES (NEW.id, adef.id, atype, adef.code, value); + ELSE + EXIT; + END IF; + + pos := pos + 1; + END LOOP; + END IF; + + END IF; + + END LOOP; + + RETURN NULL; +END; +$function$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2