From 11471dbdded3147e5047d7b4de0ee0882d43b321 Mon Sep 17 00:00:00 2001 From: scottmk Date: Fri, 23 Jul 2010 17:38:40 +0000 Subject: [PATCH] Add procedure action.apply_fieldset. M Open-ILS/src/sql/Pg/090.schema.action.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0350.schema.action-apply-fieldset.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@17030 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 123 ++++++++++++++++- .../0350.schema.action-apply-fieldset.sql | 126 ++++++++++++++++++ 3 files changed, 249 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0350.schema.action-apply-fieldset.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 9d7e169c91..64539f0a3f 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0349'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0350'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index f13424b8c2..be8c36860c 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -813,5 +813,126 @@ END; $func$ LANGUAGE PLPGSQL; -COMMIT; +CREATE OR REPLACE FUNCTION action.apply_fieldset( + fieldset_id IN INT, -- id from action.fieldset + table_name IN TEXT, -- table to be updated + pkey_name IN TEXT, -- name of primary key column in that table + query IN TEXT -- query constructed by qstore (for query-based + -- fieldsets only; otherwise null +) +RETURNS TEXT AS $$ +DECLARE + statement TEXT; + fs_status TEXT; + fs_pkey_value TEXT; + fs_query TEXT; + sep CHAR; + status_code TEXT; + msg TEXT; + update_count INT; + cv RECORD; +BEGIN + -- Sanity checks + IF fieldset_id IS NULL THEN + RETURN 'Fieldset ID parameter is NULL'; + END IF; + IF table_name IS NULL THEN + RETURN 'Table name parameter is NULL'; + END IF; + IF pkey_name IS NULL THEN + RETURN 'Primary key name parameter is NULL'; + END IF; + -- + statement := 'UPDATE ' || table_name || ' SET'; + -- + SELECT + status, + quote_literal( pkey_value ) + INTO + fs_status, + fs_pkey_value + FROM + action.fieldset + WHERE + id = fieldset_id; + -- + IF fs_status IS NULL THEN + RETURN 'No fieldset found for id = ' || fieldset_id; + ELSIF fs_status = 'APPLIED' THEN + RETURN 'Fieldset ' || fieldset_id || ' has already been applied'; + END IF; + -- + sep := ''; + FOR cv IN + SELECT col, + val + FROM action.fieldset_col_val + WHERE fieldset = fieldset_id + LOOP + statement := statement || sep || ' ' || cv.col + || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' ); + sep := ','; + END LOOP; + -- + IF sep = '' THEN + RETURN 'Fieldset ' || fieldset_id || ' has no column values defined'; + END IF; + -- + -- Add the WHERE clause. This differs according to whether it's a + -- single-row fieldset or a query-based fieldset. + -- + IF query IS NULL AND fs_pkey_value IS NULL THEN + RETURN 'Incomplete fieldset: neither a primary key nor a query available'; + ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN + fs_query := rtrim( query, ';' ); + statement := statement || ' WHERE ' || pkey_name || ' IN ( ' + || fs_query || ' );'; + ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN + statement := statement || ' WHERE ' || pkey_name || ' = ' + || fs_pkey_value || ';'; + ELSE -- both are not null + RETURN 'Ambiguous fieldset: both a primary key and a query provided'; + END IF; + -- + -- Execute the update + -- + BEGIN + EXECUTE statement; + GET DIAGNOSTICS update_count = ROW_COUNT; + -- + IF UPDATE_COUNT > 0 THEN + status_code := 'APPLIED'; + msg := NULL; + ELSE + status_code := 'ERROR'; + msg := 'No eligible rows found for fieldset ' || fieldset_id; + END IF; + EXCEPTION WHEN OTHERS THEN + status_code := 'ERROR'; + msg := 'Unable to apply fieldset ' || fieldset_id + || ': ' || sqlerrm; + END; + -- + -- Update fieldset status + -- + UPDATE action.fieldset + SET status = status_code, + applied_time = now() + WHERE id = fieldset_id; + -- + RETURN msg; +END; +$$ LANGUAGE plpgsql; +COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$ +/** + * Applies a specified fieldset, using a supplied table name and primary + * key name. The query parameter should be non-null only for + * query-based fieldsets. + * + * Returns NULL if successful, or an error message if not. + */ +$$; + + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0350.schema.action-apply-fieldset.sql b/Open-ILS/src/sql/Pg/upgrade/0350.schema.action-apply-fieldset.sql new file mode 100644 index 0000000000..e9f7ffc53d --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0350.schema.action-apply-fieldset.sql @@ -0,0 +1,126 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0350'); -- Scott McKellar + +CREATE OR REPLACE FUNCTION action.apply_fieldset( + fieldset_id IN INT, -- id from action.fieldset + table_name IN TEXT, -- table to be updated + pkey_name IN TEXT, -- name of primary key column in that table + query IN TEXT -- query constructed by qstore (for query-based + -- fieldsets only; otherwise null +) +RETURNS TEXT AS $$ +DECLARE + statement TEXT; + fs_status TEXT; + fs_pkey_value TEXT; + fs_query TEXT; + sep CHAR; + status_code TEXT; + msg TEXT; + update_count INT; + cv RECORD; +BEGIN + -- Sanity checks + IF fieldset_id IS NULL THEN + RETURN 'Fieldset ID parameter is NULL'; + END IF; + IF table_name IS NULL THEN + RETURN 'Table name parameter is NULL'; + END IF; + IF pkey_name IS NULL THEN + RETURN 'Primary key name parameter is NULL'; + END IF; + -- + statement := 'UPDATE ' || table_name || ' SET'; + -- + SELECT + status, + quote_literal( pkey_value ) + INTO + fs_status, + fs_pkey_value + FROM + action.fieldset + WHERE + id = fieldset_id; + -- + IF fs_status IS NULL THEN + RETURN 'No fieldset found for id = ' || fieldset_id; + ELSIF fs_status = 'APPLIED' THEN + RETURN 'Fieldset ' || fieldset_id || ' has already been applied'; + END IF; + -- + sep := ''; + FOR cv IN + SELECT col, + val + FROM action.fieldset_col_val + WHERE fieldset = fieldset_id + LOOP + statement := statement || sep || ' ' || cv.col + || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' ); + sep := ','; + END LOOP; + -- + IF sep = '' THEN + RETURN 'Fieldset ' || fieldset_id || ' has no column values defined'; + END IF; + -- + -- Add the WHERE clause. This differs according to whether it's a + -- single-row fieldset or a query-based fieldset. + -- + IF query IS NULL AND fs_pkey_value IS NULL THEN + RETURN 'Incomplete fieldset: neither a primary key nor a query available'; + ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN + fs_query := rtrim( query, ';' ); + statement := statement || ' WHERE ' || pkey_name || ' IN ( ' + || fs_query || ' );'; + ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN + statement := statement || ' WHERE ' || pkey_name || ' = ' + || fs_pkey_value || ';'; + ELSE -- both are not null + RETURN 'Ambiguous fieldset: both a primary key and a query provided'; + END IF; + -- + -- Execute the update + -- + BEGIN + EXECUTE statement; + GET DIAGNOSTICS update_count = ROW_COUNT; + -- + IF UPDATE_COUNT > 0 THEN + status_code := 'APPLIED'; + msg := NULL; + ELSE + status_code := 'ERROR'; + msg := 'No eligible rows found for fieldset ' || fieldset_id; + END IF; + EXCEPTION WHEN OTHERS THEN + status_code := 'ERROR'; + msg := 'Unable to apply fieldset ' || fieldset_id + || ': ' || sqlerrm; + END; + -- + -- Update fieldset status + -- + UPDATE action.fieldset + SET status = status_code, + applied_time = now() + WHERE id = fieldset_id; + -- + RETURN msg; +END; +$$ LANGUAGE plpgsql; + +COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$ +/** + * Applies a specified fieldset, using a supplied table name and primary + * key name. The query parameter should be non-null only for + * query-based fieldsets. + * + * Returns NULL if successful, or an error message if not. + */ +$$; + +COMMIT; -- 2.43.2