%entities; %constants; ]>

For the most part, database integrity checks are built into the system and it should not be possible to put invalid data into the database. However in some cases, whether for reasons of complexity or for some other reason, some database integrity problems are not caught by the system. There are also questionable cases; situations where the acceptability of the data is dependent upon circumstances. In these cases it is useful for the system to provide a warning and allow the user to decide whether a problem really exists. It is for these reasons that the warning sub-system exists. The warning sub-system provides a means by which the system can be supplied with arbitrary queries which validate data integrity. These queries are stored and, when activated, report arbitrary problems with the database's data integrity, either warning conditions or errors. Errors are always reported when the warning system is activated. Individual warnings reported by the supplied queries are then manually sorted into one of the following categories: unclassified (the default), labeled resolved, or deferred until a later date. When the warning system is activated unclassified warnings are reported, resolved warnings are not reported, and deferred warnings are not reported until the current date reaches the deferral date. Unlike the database integrity checks built into the rest of the system which report problems immediately as data is inserted into the database, the warning system does nothing until activated. The warning system is activated by use of one of the supplied functions, causing one or more of the stored queries to check the state of the database.

This section provides an overview of the data structures used by the warning sub-system.

All date plus time values (timestamps) have a one second precision. Fractions of a second are not recorded.

The INTEGRITY_QUERIES contains one row for every query used to search for database integrity issues. The value cannot be before the value. Use &postgresql;'s &dollar_quoting; when inserting queries into INTEGRITY_QUERIES using INSERT statements. This avoids problems that would otherwise arise involving the use of quote characters inside quoted strings. INSERT INTO integrity_queries (iqname, error, type, query) VALUES('mycheck', false, 'bdate', $$SELECT 'Bad birthdate: ' || mytable.id || ', ' || mytable.birthdate AS id , 'Id (' || mytable.id || ') has a birthdate (' || mytable.birthdate || ') before 1950' AS msg FROM mytable WHERE mytable.birthdate < '1950-01-01'$$ );

A unique name for the query. The IQName value cannot be changed. &emptytext; ¬null; &nospaces; The IQName value may contain no more than &warn_iqname_len; characters.

A Boolean value. &true; when the query finds conditions that are errors, &false; when the query finds conditions that are warnings. See (and the ) for more on warnings and errors. ¬null;

Code classifying the query. The legal values for this column are defined by the support table. ¬null;

Date and time the query was first run by the warning sub-system. &null; if the query has never been run.

Date and time the query was most recently run by the warning sub-system. &null; if the query has never been run.

A query which checks for database integrity violations. The query need not end in a semi-colon. The query must return 2 columns, both of type TEXT.

The first column is used as an id. It must contain a unique value. (Unique per results returned by the given query). The value must also be constant; repeated runs of the query which find the same problem must return a consistent value. The system can not enforce the requirement that the first column be consistent over repeated runs of the query. If the query does not satisfy this requirement the warning sub-system will generate duplicates of previously reported problems. The value of the first column may not be &null; or the empty string. Guidelines for the value of the first column are that it should be human readable and relatively short. It should probably contain id values in order to ensure uniqueness, but only those that will not change over time. The value of this first column may need to be typed in or otherwise referenced by a person in order to make notes regarding the problem or to change the problem's status.

The second column contains a message describing the discovered database integrity problem. It should contain a complete description of the problem and may be as verbose as necessary. The value of the second column may not be &null; or the empty string.

A textual comment regards the query. This may be as verbose as necessary. &emptytext; This column may contain &null; when no comment is desired.

The INTEGRITY_WARNINGS table contains one row for every database integrity problem discovered by the queries in . It's purpose is twofold. It provides an efficient way to list data integrity problems, without having to execute the potentially complex queries which discover the problems. But it's main purpose is to allow warnings, i.e. those problems discovered by the queries saved in rows having a &false; value, to be resolved -- permanently marked as acceptable conditions. Resolved warnings can be safely ignored thereafter, and since the warning sub-system automatically ignores resolved warnings those responsible for maintaining database integrity need not repeatedly concern themselves with resolved conditions. To resolve a warning place a timestamp in the column. Data integrity errors can not be resolved, the erroneous data condition must be fixed -- INTEGRITY_WARNINGS rows must have a &null; value when the row has a related to an row having a &true; value. The value, the value, and the value cannot be before the value. A resolved warning cannot be deferred -- either or , or both, must be &null;. To remove an . value and add a . value without raising an error either update both values in the same UPDATE statement or first set the value to &null; and then the value to something non-&null;. The warning id generated by the stored query must be unique per query -- the combination of INTEGRITY_WARNINGS. and INTEGRITY_WARNINGS. must be unique.

An integer uniquely identifying the row containing the result of a database integrity query. The IWID value cannot be changed.

The . value identifying the query which produced the result.

Date and time the query result was first produced by the warning sub-system. ¬null;

Date and time the query result was most recently produced by the warning sub-system. ¬null;

Date and time the query result was resolved; i.e. marked not a concern. The warning sub-system does not display resolved results, although of course the INTEGRITY_WARNINGS table can always be manually queried. The value of this column is &null; both when the query result is a data integrity error and when it is a data integrity warning that has not yet been resolved.

Date and time before which the warning sub-system will not display the result. Use of this column allows resolution of data integrity problems to be deferred, and hence not clutter up the output of the warning sub-system with noise that might hide other problems. When this column is &null; the warning sub-system will display the query result.

Code classifying the query result. The legal values for this column are defined by the support table. This column may be &null; when the query result is unclassified.

This is a unique, unique per query that is, identifier for the query result. It is the first column produced by the related .. ¬null; Once given a value, the content of this column may not be altered except by a username with administrative authority.

This is the message, the second column, produced by the .. Once given a value, the content of this column may not be altered except by a username with administrative authority.

Any textual notes regarding this particular query result. This column may be &null; when there are no such notes. &emptytext;

IQTYPES contains one row for every code used to classify database integrity queries. Classification may be by the type of data integrity problem the related queries are designed to uncover, by who is responsible for resolving the discovered problems, or any other desired classification scheme.

The IQTYPES table is keyed by the IQType column. No more than &warn_iqtype_len; characters are allowed in the key. &nospaces;

None.

WARNING_REMARKS contains one row for every code used to classify or explain sets of database integrity problems, problems discovered by the warning sub-system's queries. Codes may be used as needed, whether to organize reported problems pending resolution, to describe the circumstances which resolve an issue, or to serve other purposes.

The WARNING_REMARKS table is keyed by the WRID column. No more than &warn_wrid_len; characters are allowed in the key. &nospaces;

None.

The warning sub-system is activated by using one of it's functions. Of course the table may always be queried manually, but this does not discover any new problems. All of the warning sub-system functions are designed to be used in the FROM clause of SELECT statements, as if they were tables. Indeed the functions look like tables to the SELECT statement, tables that look exactly like -- except that the and columns are missing. The difference between querying on the table directly and querying using the warning sub-system's functions is that the functions update the content of the table by executing the the queries in table. Also, the functions never return rows where the underlying row has a non-&null; value or a time and date that has not yet been reached. All timestamps, date plus time values, which the warning sub-system updates in the and tables are set to the date and time at which program execution started. So when, say, , is run, all of the new timestamp values in the and rows touched by the execution are identical. Various warning sub-system functions (or versions of the same function) are supplied to allow easy selection of which queries in which rows are to be executed, whether all or only some. As with a regular table, the order in which rows are returned by the warning sub-system's functions is indeterminate. If you wish to ensure a specific ordering an ORDER BY clause must be used. run_integrity_queries run_integrity_queries execute one or more of the queries stored in the INTEGRITY_QUERIES table TABLE (iwid, iqname, first_seen, last_seen, category, warning_id, warning_message, notes) run_integrity_queries TABLE (iwid, iqname, first_seen, last_seen, category, warning_id, warning_message, notes) run_integrity_queries TEXT iq_query The text of an SQL query. The query must return a single column of . values. A function which runs the queries stored in the table, returns the output of the stored queries, and stores the results in the table. Because the function returns rows and columns it must be invoked in the FROM clause of a SELECT statement. (See the Examples below.) The function may be called in one of two ways. When called with no arguments all of the queries in are run. When called with the text of an SQL query, a query which returns a single column containing . values, the function runs only those queries. Use &postgresql;'s &dollar_quoting; when supplying a query to run_integrity_queries(). The function returns a set of columns with multiple rows, a table. So it must be used in the FROM clause of a SELECT statement. The columns returned by the function are the columns of the table, excepting the column and the column. The rows returned by the function are those of the newly updated table, excepting those rows with a non-&null; column or those rows with a value that is in the future. Only those rows that are related to the executed queries (in ) are returned. So, when called with no arguments the function returns all warnings that have not been resolved and all errors. When called with a query that selects specific to execute, only the unresolved warnings and errors discovered by the executed are returned. Running an . does more than add new rows to the table. The . column is updated with a new timestamp as is the . value of all rows with values matching that of the executed query where the value matches the value returned in the first column of the executed query. Further, if an existing row matches the value of the executed query but there is no corresponding value returned by the executed query then the row is deleted. This empties the table of errors and warnings that no longer apply to the current state of the database. This happens to warnings regardless of whether or not the warning is resolved. If significant research has gone into the resolution of a warning condition that is expected, for whatever reason, to be absent from the database and then re-appear then care should be taken to record this research somewhere other than in the table. The row corresponding to the warning condition in may be automatically deleted by the warning sub-system when the warning condition is temporarily absent from the database content. The following example runs all the queries in , displays all the errors and all the unresolved warnings (unless the error or warning has been deferred), ordered first by the name of the query, within that showing newer problems first, and within that ordered by warning id. SELECT * FROM run_integrity_queries() AS problems ORDER BY problems.iqname , problems.first_seen desc , problems.warning_id; The following example runs a single saved query with an . of mycheck and displays any of these sorts of problems found, ordered as in the previous example. This example also demonstrates how to use &dollar_quoting; to give a query to run_integrity_queries and thereby avoid problems having to do with trying to nest regular quotes. SELECT * FROM run_integrity_queries($$SELECT 'mycheck'$$) AS problems ORDER BY problems.iqname , problems.first_seen desc , problems.warning_id; The following example runs all the queries of the bdate type and displays any of these sorts of problems found, ordered as in the previous example. This example also demonstrates how to use &dollar_quoting; to give a query to run_integrity_queries and thereby avoid problems having to do with trying to nest regular quotes. SELECT * FROM run_integrity_queries( $$SELECT integrity_queries.iqname FROM integrity_queries WHERE integrity_queries.type = 'bdate'$$ ) AS problems ORDER BY problems.iqname , problems.first_seen desc , problems.warning_id;