1.8.7 CQL: Statistical Hooks

CQL has hooks to enable in-memory statistics to be tracked for database tables. Using this hook, it's possible to monitor the number of rows in a table with a particular value in a particular column.

Often the kind of statistics of interest are’how many rows in this table are in ERROR’or’how many in this table are at NEW’? While it may be possible to maintain these directly in any code which updates tables, it can be difficult to ensure all cases are accounted for, and requires developers to remember which attributes are tracked.

To ensure that all (CQL-originated) updates to statuses are captured, it's possible to use the CQL hook system to update them automatically. Define add a fact like:

cql_statistic_monitored_attribute_hook(my_schema, my_table,
                                       my_table_status_column).

This will examine the domain for the column’my_table_status_column’, and generate a statistic for each of my_table::my_table_status_column(xxx), where xxx is each possible allowed value for the domain. Code will be automatically generated to trap updates to this specific column, and maintain the state. This way, if you are interested in the number of rows in my_table which have a status of’NEW’, you can look at my_table::my_table_status_column('NEW'), without having to manage the state directly. CQL update statements which affect the status will automatically maintain the statistics.

The calculations are vastly simpler than the history mechanism, so as to keep performance as high as possible. For inserts, there is no cost to monitoring the table (the insert simply increments the statistic if the transaction completes). For deletes, the delete query is first run as a select, aggregating on the monitored columns to find the number of deletes for each domain allowed value. This means that a delete of millions of rows might requires a select returning only a single row for statistics purposes. For updates, the delete code is run, then the insert calculation is done, multiplied by the number of rows affected by the update.

In all cases, CQL ends up calling cql_statistic_monitored_attribute_change_hook/5, where the last argument is a signed value indicating the number of changes to that particular statistic.

cql_set_module_default_schema(+Schema)

Set the Schema for a module

cql_get_module_default_schema(+Module, ?ModuleDefaultSchema)

cql_goal_expansion(?Schema, ?Cql, ?GoalExpansion)

Expand at compile time if the first term is a list of unbound input variables

Expand at runtime if the first term is compile_at_runtime

cql_runtime(+Schema, +IgnoreIfNullVariables, +CqlA, +CqlB, +VariableMap, +FileName, +LineNumber)

cql_temporary_column_name(?Schema, ?DataType, ?ColumnName, ?Type)

cql_show(:Goal, +Mode)

Called when ?/1, ??/1, and ???/1 applied to CQL

`Goal`	goal term
`Mode`	minimal ; explicit ; full

statistic_monitored_attribute(+Schema, +TableName, +ColumnName)

[multifile]dbms(+Schema, -DBMSName)

Determine the DBMS for a given Schema. Can be autoconfigured.

[multifile]odbc_data_type(+Schema, +TableSpec, +ColumnName, ?OdbcDataType)

OdbcDataType must be a native SQL datatype, such as varchar(30) or decimal(10, 5) Can be autoconfigured.

[multifile]primary_key_column_name(+Schema, +TableName, -PrimaryKeyAttributeName)

Can be autoconfigured.

[multifile]routine_return_type(?Schema:atom, ?EntityName:atom, ?OdbcType)

Can be autoconfigured

[nondet,multifile]database_constraint(?Schema:atom, ?EntityName:atom, ?ConstraintName:atom, ?Constraint)

Constraint is one of:

primary_key(ColumnNames:list)
foreign_key(ForeignTableName:atom, ForeignColumnNames:list, ColumnNames:list)
unique(ColumnNames:list)
check(CheckClause)

In theory this can be autoconfigured too, but I have not written the code for it yet

attribute_domain(+Schema, +TableName, +ColumnName, -Domain)

database_identity(?Schema:atom, ?EntityName:atom, ?ColumnName:atom)

database_key(?Schema:atom, ?EntityName:atom, ?ConstraintName:atom, ?KeyColumnNames:list, ?KeyType)

`KeyColumnNames`	list of atom in database-supplied order
`KeyType`	identity ; ’primary key’ ; unique

[multifile]cql_event_notification_table(+Schema, +TableName)

[multifile]cql_history_attribute(+Schema, +TableName, +ColumnName)

[multifile]sql_gripe_hook(+Level, +Format, +Args)

Called when something dubious is found by the SQL parser.

cql_normalize_name(+DBMS, +Name, -NormalizedName)

Normalize a name which is potentially longer than the DBMS allows to a unique truncation

[det]register_database_connection_details(+Schema:atom, +ConnectionDetails)

This should be called once to register the database connection details.

ConnectionDetails driver_string(DriverString) or dsn(Dsn, Username, Password)

Bibliography

Draxler, 1991: C. Draxler. Accessing relational and NF^2 databases through database set predicates. In Geraint A. Wiggins, Chris Mellish, and Tim Duncan, editors, ALPUK91: Proceedings of the 3rd UK Annual Conference on Logic Programming, Edinburgh 1991, Workshops in Computing, pages 156--173. Springer-Verlag, 1991.

Index

?
attribute_domain/4
cql_event_notification_table/2
cql_get_module_default_schema/2
cql_goal_expansion/3
cql_history_attribute/3
cql_normalize_name/3
cql_runtime/7
cql_set_module_default_schema/1
cql_show/2
cql_temporary_column_name/4
database_constraint/4
database_identity/3
database_key/5
dbms/2
odbc_data_type/4
primary_key_column_name/3
register_database_connection_details/2
routine_return_type/3
sql_gripe_hook/3
statistic_monitored_attribute/3