From 0397d7274a300d5ca6a2e89e49661c47e37de1b9 Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 25 Mar 2010 16:06:03 +0000 Subject: [PATCH] capture multiple values (on pg 8.4) of an attr def git-svn-id: svn://svn.open-ils.org/ILS/trunk@15978 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 25 +++++++--- ....schema.acq.lineitem_attr_ingest_multivalue.sql | 58 ++++++++++++++++++++++ 3 files changed, 77 insertions(+), 8 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0212.schema.acq.lineitem_attr_ingest_multivalue.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index bb07d164d..665987db5 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -59,7 +59,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0211'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0212'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index d6f386e0f..cb8af11a9 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -939,11 +939,12 @@ Suggested vendor fields: vendor_identifier */ -CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$ +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 @@ -965,12 +966,22 @@ BEGIN SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id; END IF; - SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id; + xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,''); - 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 := 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; @@ -978,7 +989,7 @@ BEGIN RETURN NULL; END; -$$ LANGUAGE PLPGSQL; +$function$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$ BEGIN diff --git a/Open-ILS/src/sql/Pg/upgrade/0212.schema.acq.lineitem_attr_ingest_multivalue.sql b/Open-ILS/src/sql/Pg/upgrade/0212.schema.acq.lineitem_attr_ingest_multivalue.sql new file mode 100644 index 000000000..adaf41fa8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0212.schema.acq.lineitem_attr_ingest_multivalue.sql @@ -0,0 +1,58 @@ + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0212'); + +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$,''); + + 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 LOOP; + + RETURN NULL; +END; +$function$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.11.0