dnl Copyright (C) 2015 The Meme Factory, Inc., http://www.meme.com/
dnl
dnl This file is part of Gombe-MI.
dnl
dnl Gombe-MI is free software; you can redistribute it and/or modify
dnl it under the terms of the GNU General Public License as published by
dnl the Free Software Foundation; either version 3 of the License, or
dnl (at your option) any later version.
dnl
dnl This program is distributed in the hope that it will be useful,
dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
dnl GNU General Public License for more details.
dnl
dnl You should have received a copy of the GNU General Public License
dnl along with Gombe-MI. If not, see .
dnl
dnl Run_integrity_queries function.
dnl
dnl Karl O. Pinc
dnl
dnl
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`macros.m4')
dnl
dnl plpgsql fragment to define columns returned
dnl
dnl Syntax: _return_cols()
dnl
dnl
changequote({,})
define({_return_cols},{
iwid integrity_warnings.iwid%TYPE
, iqname integrity_warnings.iqname%TYPE
, first_seen integrity_warnings.first_seen%TYPE
, last_seen integrity_warnings.last_seen%TYPE
, category integrity_warnings.category%TYPE
, warning_id integrity_warnings.warning_id%TYPE
, warning_message integrity_warnings.warning_message%TYPE
, notes integrity_warnings.notes%TYPE
})dnl
changequote(`,')dnl
dnl plpgsql fragment to put exception diagnostic info into vars.
dnl
dnl Syntax: _get_exception_info(msg, detail, hint, context)
dnl
dnl Input:
dnl names of variables in which to save exception info
dnl
changequote({,})
define({_get_exception_info},{
GET STACKED DIAGNOSTICS
$1 = MESSAGE_TEXT
, $2 = PG_EXCEPTION_DETAIL
, $3 = PG_EXCEPTION_HINT
, $4 =PG_EXCEPTION_CONTEXT;
IF $2 <> '' THEN
$1 := $1 || E'\n';
$2 := 'The detail is: ' || $2;
END IF;
IF $3 <> '' THEN
$2 := $2 || E'\n';
$3 := 'The hint is: ' || $3;
END IF;
$3 := $3 || E'\n';
IF $4 <> '' THEN
$4 := E'\nAdditional diagnostic information: ' || $4;
END IF;
})dnl
changequote(`,')dnl
CREATE OR REPLACE FUNCTION run_integrity_queries()
RETURNS TABLE(_return_cols())
LANGUAGE plpgsql
SECURITY INVOKER
gmi_function_set_search_path
AS $$
-- Run all queries in INTEGRITY_QUERIES.
--
-- GPL_notice(` --', `2015', `The Meme Factory, Inc. http://www.meme.com/')
--
-- Syntax: run_integrity_queries()
--
-- Arguments:
--
-- Remarks:
--
-- Bugs:
DECLARE
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM run_integrity_queries(
$RIQ$SELECT integrity_queries.iqname
FROM integrity_queries$RIQ$)';
END;
$$;
grant_func_priv(`run_integrity_queries()')
CREATE OR REPLACE FUNCTION run_integrity_queries(query TEXT)
RETURNS TABLE(_return_cols())
LANGUAGE plpgsql
SECURITY INVOKER
gmi_function_set_search_path
AS $$
-- Run all queries in INTEGRITY_QUERIES.
--
-- GPL_notice(` --', `2015', `The Meme Factory, Inc. http://www.meme.com/')
--
-- Syntax: run_integrity_queries(query)
--
-- Arguments:
-- query A query returning 1 column, of INTEGRITY_QUERY.IQName values.
--
-- Remarks:
--
-- Bugs:
DECLARE
this_iqname integrity_queries.iqname%TYPE;
saved_query integrity_queries.query%TYPE;
id TEXT;
msg TEXT;
my_message_text TEXT;
my_pg_exception_detail TEXT;
my_pg_exception_hint TEXT;
my_pg_exception_context TEXT;
exception_caught BOOLEAN := FALSE;
BEGIN
-- Run the supplied query and iterate over the saved queries.
FOR this_iqname IN EXECUTE query LOOP
-- Save all the existing warning ids for the saved query
CREATE TEMP TABLE _riq_warning_ids AS
SELECT integrity_warnings.warning_id
FROM integrity_warnings
WHERE integrity_warnings.iqname = this_iqname;
CREATE UNIQUE INDEX _riq_warning_ids_warning_id
ON _riq_warning_ids (warning_id);
-- Get the saved query
SELECT integrity_queries.query
INTO saved_query
FROM integrity_queries
WHERE integrity_queries.iqname = this_iqname;
IF NOT FOUND THEN
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error in run_integrity_queries(TEXT)'
, DETAIL = 'Supplied query returned a value ('
|| textualize(`this_iqname')
|| ') that is not an existing'
|| ' INTEGRITY_QUERIES.IQName value';
RETURN;
END IF;
BEGIN
FOR id, msg IN EXECUTE saved_query LOOP
-- Remember that we processed the id.
DELETE FROM _riq_warning_ids
WHERE _riq_warning_ids.warning_id = id;
IF FOUND THEN
-- Update the existing integrity_warnings row.
UPDATE integrity_warnings
SET last_seen = localtimestamp
WHERE integrity_warnings.iqname = this_iqname
AND integrity_warnings.warning_id = id;
ELSE
-- Make a new integrity_warnings row.
INSERT INTO integrity_warnings
(iqname, first_seen, last_seen, warning_id, warning_message)
VALUES (this_iqname, localtimestamp, localtimestamp, id, msg);
END IF;
END LOOP;
EXCEPTION
-- We want all of these to trap most that could go wrong when a
-- user-supplied bit of sql is executed:
-- data_exception (22000) bad expressions (divide by 0, etc.)
-- integrity_constraint_violation (23000) violating insert/update
-- invalid_sql_statement_name (26000)
-- sql_routine_exception (2F000)
-- syntax_error_or_access_rule_violation (42000)
WHEN feature_not_supported
OR sql_statement_not_yet_complete
OR data_exception
OR integrity_constraint_violation
OR invalid_sql_statement_name
OR sql_routine_exception
OR syntax_error_or_access_rule_violation THEN
_get_exception_info(my_message_text,
my_pg_exception_detail,
my_pg_exception_hint,
my_pg_exception_context)
exception_caught := TRUE;
RAISE EXCEPTION data_exception USING
MESSAGE = 'run_integrity_queries:'
|| ' Error executing stored INTEGRITY_QUERIES.Query'
, DETAIL = 'Problem in the saved query with IQName = ('
|| this_iqname || E'):\n'
|| 'The error is: ' || my_message_text
|| my_pg_exception_detail
|| my_pg_exception_hint
|| E'The query is:\n'
|| saved_query
|| my_pg_exception_context;
END;
-- Note that we've processed the saved query.
UPDATE integrity_queries
SET last_run = localtimestamp
, first_run = COALESCE(first_run, localtimestamp)
WHERE integrity_queries.iqname = this_iqname;
-- Delete old query results that are not found this time.
DELETE FROM integrity_warnings
USING _riq_warning_ids
WHERE integrity_warnings.iqname = this_iqname
AND integrity_warnings.warning_id = _riq_warning_ids.warning_id;
-- Cleanup
DROP TABLE _riq_warning_ids CASCADE;
-- Return the rows found with their new values.
RETURN QUERY
SELECT integrity_warnings.iwid
, integrity_warnings.iqname
, integrity_warnings.first_seen
, integrity_warnings.last_seen
, integrity_warnings.category
, integrity_warnings.warning_id
, integrity_warnings.warning_message
, integrity_warnings.notes
FROM integrity_warnings
WHERE integrity_warnings.iqname = this_iqname
AND integrity_warnings.resolved IS NULL
AND (integrity_warnings.deferred_to IS NULL
OR integrity_warnings.deferred_to <= localtimestamp);
END LOOP; -- Over iqname
EXCEPTION
-- We want all of these to trap most that could go wrong when a
-- user-supplied bit of sql is executed:
-- data_exception (22000) bad expressions (divide by 0, etc.)
-- integrity_constraint_violation (23000) violating insert/update
-- invalid_sql_statement_name (26000)
-- sql_routine_exception (2F000)
-- syntax_error_or_access_rule_violation (42000)
WHEN feature_not_supported
OR sql_statement_not_yet_complete
OR data_exception
OR integrity_constraint_violation
OR invalid_sql_statement_name
OR sql_routine_exception
OR syntax_error_or_access_rule_violation THEN
IF exception_caught THEN
GET STACKED DIAGNOSTICS
my_message_text = MESSAGE_TEXT
, my_pg_exception_detail = PG_EXCEPTION_DETAIL;
RAISE EXCEPTION data_exception USING
MESSAGE = my_message_text
, DETAIL = my_pg_exception_detail;
ELSE
_get_exception_info(my_message_text,
my_pg_exception_detail,
my_pg_exception_hint,
my_pg_exception_context)
RAISE EXCEPTION data_exception USING
MESSAGE = 'run_integrity_queries:'
|| ' Error executing supplied query argument'
, DETAIL = E'\nThe error is: ' || my_message_text
|| my_pg_exception_detail
|| my_pg_exception_hint
|| E'The query is:\n'
|| query
|| my_pg_exception_context;
END IF;
-- WHEN OTHERS THEN
-- RAISE EXCEPTION data_exception USING
-- MESSAGE = 'SQLSTATE ' || SQLSTATE || ': SQLERRM ' || SQLERRM;
END;
$$;
grant_func_priv(`run_integrity_queries(TEXT)')