% This LaTeX document was generated using the LaTeX backend of PlDoc,
% The SWI-Prolog documentation system



\section{library(cql/cql): CQL - Constraint Query Language}

\label{sec:cql}

Note that CQL is currently in a state of flux. Features may be dropped
in future releases, and the generated SQL may change between releases.
In particular, \textit{runtime} mode is deprecated.

CQL is a Prolog interface to SQL databases. There are two modes: \textit{fully
compiled} and \textit{runtime}. The \textit{fully compiled} mode should be used
if possible due to the far greater compile time checking it provides.

\subsection{Warnings from CQL}

\label{sec:cql-warnings}

\subsubsection{CQL Comparisons with NULL}

\label{sec:cql-compare-null}

CQLv2 correctly compiles equality comparisons with NULL into the
appropriate expression at runtime. In CQLv1, executing

\begin{code}
A={null}, {[A], foo :: [a-A]}
\end{code}

would never succeed, regardless of the value of foo.a. This is no longer
the case: If A is \verb${null}$ then this will execute as \verb$SELECT .... WHERE a IS NULL$ and if A is not \{null\}, it will execute as \verb$SELECT .... WHERE a = ?$

See the section \textit{Removing null comparisions} for the dealing with the
common requirement to ignore comparisons with null.

\subsubsection{Avoid \predref{setof}{3} and \predref{bagof}{3} in CQL queries}

\label{sec:cql-and-setof}

It is generally not a good idea to wrap CQL inside a \predref{setof}{3} or a
\predref{bagof}{3} ... unless you are prepared to declare all the CQL variables
that are neither bound nor mentioned in the setof/bagof template. If you
want to sort, use \predref{findall}{3} followed by \predref{sort}{2}. Note that \predref{sort}{2} (like
\predref{setof}{3}) removes duplicates. If you don't want to remove duplicates, use
\predref{msort}{2}.

\subsection{CQL: Retrieved nulls have special logic to handle outer joins}

\label{sec:cql-null-outer-jpin}

In the course of executing a select query, the following rules are applied:

\begin{enumerate}
    \item Any selected attribute that is null does not bind its associated variable.
    \item Just before returning from the query any select variables that
are still free are bound to \{null\}.
\end{enumerate}

This is so we can handle outer joins. Consider this:

\begin{code}
x :: [a-A] *== y :: [a-A]
\end{code}

Assume x.a binds A to a non-null value. If there is no matching row in
\const{y}, then \verb$y.a = null$. If variable \arg{A} was truly shared the query could
never succeed. By not binding the variable associated with \verb$y.a$ the
query can succeed ( rule 1) and \arg{A} will be bound to the value in \verb$x.a$.

\subsection{CQL: Getting Started Quickly}

\label{sec:cql-quickstart}

Here is a simple example of a SQL SELECT from the table \verb$se_lt_x$

\begin{code}
test(A) :-
  format('About to call CQL with A=~w', [A]),
  {[],
  se_lt_x :: [a-A,
              b-B,
              c-C]},
  format('B=~w, C=~w', [B, C]).
\end{code}

\begin{itemize}
    \item The CQL is distinguished from the ordinary Prolog by appearing
in curly brackets
    \item Prolog variables which are ground when the CQL is executed will
appear in the resulting SQL as part of the WHERE clause
\end{itemize}

Comparisons can be done in-line e.g.

\begin{code}
[a-'ELSTON_M']
\end{code}

or with the \Sequal{} operator e.g.

\begin{code}
[a-A], A == 'ELSTON_M'.
\end{code}

\textbf{The single = operator means unify, not compare. Use = for unification,
not comparison}

FIXME: Unification is deprecated.

The operators \verb$=:=$ and \verb$\==$ are also available for numerical value
comparisons (they just translate to SQL \verb$=$ and \verb$<>$, so in fact you
could use them for string comparisons)

\subsection{Debugging CQL queries}

\label{sec:cql-debugging}

You can debug CQL using the meta-predicates \predref{\Squest}{1}, \predref{??}{2} and \predref{???}{3}:

\begin{code}
???{[], se_lt_x :: [a-A, b-_], A == 'ELSTON_M'}.
\end{code}

\begin{description}
    \item[\predref{\Squest}{1}] Display a summary form of the generated SQL before and
after the goal is called.

\begin{code}
[main]  CALL   SELECT slx_2.b, slx_2.a  FROM se_lt_x AS slx_2 WHERE slx_2.a = 'ELSTON_M'
[main]  EXIT   SELECT slx_2.b, slx_2.a  FROM se_lt_x AS slx_2 WHERE slx_2.a = 'ELSTON_M' (0.006963s, 0.01cpu, 3,899 inferences)
\end{code}

    \item[\predref{??}{1}] Display the exact query (and results) in a format which
can be executed directly by the DBMS (In this case, SQL Server) The
generated SQL may be significantly more complicated than expected, and
this can be used to debug the CQL compiler itself

\begin{code}
[main]  CALL
DECLARE @P0 VARCHAR(50);
SET @P0 = 'ELSTON_M';
SELECT slx_450.b,
       slx_450.a
FROM se_lt_x AS slx_450
WHERE slx_450.a = @P0 AND slx_450.a COLLATE Latin1_General_CS_AS = @P0
Result: se_lt_x.b = {null}
        se_lt_x.a = 'ELSTON_M'
 (0.003304s, 0.00cpu, 359 inferences)
\end{code}

    \item[\predref{???}{1}] Display simplified SQL before the goal is called and display
the results afterwards

\begin{code}
[main]  CALL
SELECT slx_450.b,
       slx_450.a
FROM se_lt_x AS slx_450
WHERE slx_450.a = 'ELSTON_M'
Result: se_lt_x.b = {null}
        se_lt_x.a = 'ELSTON_M'
 (0.003304s, 0.00cpu, 359 inferences)
\end{code}

\end{description}

\subsection{Prolog Variables in CQL queries}

\label{sec:cql-prolog-vars}

A Prolog variable can be simultaneously a \textit{SELECT} variable, a
\textit{JOIN} variable and a \textit{WHERE} variable as A is in the following
example:

\begin{code}
{[],
 se_lt_x :: [a-A, c-C]
 =*=
 se_lt_y :: [d-A, f-F],
 A == 'A4'}
\end{code}

which generates the following SQL

\begin{code}
SELECT
  x_192.a, x_192.c, y_73.d, y_73.f
 FROM
  se_lt_x x_192 INNER JOIN se_lt_y y_73 ON y_73.d=x_192.a
 WHERE   x_192.a = ? and y_73.d = ?
\end{code}

Note how \textbf{all the variables referenced in the query} are retrieved in
the SELECT. This is done to make the query \textit{Prolog-like}. This means
the retrieved row should behave like a Prolog fact so that when a query
succeeds all the variables become instantiated.

There is one notable exception however: \textbf{WHERE variables and JOIN
variables are not bound in aggregation selections}

FIXME: Is this still the case?

\begin{code}
sum_test :-
  {[],
   #se_lt_x :: [a-ValueA,
                sum(b)-Summation]
   =*=
   #se_lt_y :: [e-ValueB],

   ValueA == ValueB,   % Explicit join point

   group_by([ValueA])},

  writeln(ValueA-ValueB-Summation).
\end{code}

\begin{code}
'ELSTON_M'-_G375971-99450
true ;
\end{code}

\subsection{CQL Special Attributes}

\label{sec:cql-special-attrs}

The following attributes are automatically provided i.e if the attribute
is present in the table, CQL will automatically fill in the value:

\begin{enumerate}
    \item \textbf{generation_} Set to 0 on INSERT and incremented by 1 on each update
    \item \textbf{inserted_} Set to the current time at the time of the INSERT
transaction
    \item \textbf{inserted_by_} Set to the user ID corresponding to the access
token supplied to the transaction
    \item \textbf{updated_} Set to the current time at the time of the UPDATE
transaction. Note that updated_ is also set by an INSERT
    \item \textbf{updated_by_} Set to the user ID corresponding to the access
token supplied to the transaction. Note that updated_by_ is also
set by an INSERT
    \item \textbf{transaction_id_} Set to the transaction ID
\end{enumerate}

All the special attributes can be overridden by supplying the
attribute-value pair explicitly.

\subsection{CQL Examples}

\label{sec:cql-examples}

Rather than provide an abstract description of CQL syntax here is a set of
examples that show how to use it.

\subsubsection{CQL Simple INSERT}

\label{sec:cql-insert}

\begin{code}
{[],
 insert(se_lt_x, [a-'A', b-'B', c-100])}
\end{code}

\subsubsection{CQL Simple INSERT with retrieval of identity of the inserted}

\label{sec:cql-insert2}

\begin{code}
{[],
 insert(se_lt_x, [a-'A', b-'B', c-100]),
 identity(I)}
\end{code}

\subsubsection{CQL Simple DELETE}

\label{sec:cql-delete}

\begin{code}
{[],
 delete(se_lt_x, [x_pk-I])}
\end{code}

Note that the WHERE clause is part of the \predref{delete}{2} term unlike \textit{update}
where the WHERE clause is defined outside the \predref{update}{2} term. I could
have made delete consisent with update, but this would have required the
@ alias in the delete WHERE clause to identify the table where the rows
are to be deleted). This seems like overkill because a delete can in
fact refer to only one table anyway i.e. you can't identify rows to
delete via a JOIN.

\subsubsection{CQL Simple SELECT}

\label{sec:cql-select}

\begin{code}
{[],
 se_lt_x :: [a-A, b-B]}
\end{code}

This query will either:

\begin{itemize}
    \item If A is bound, and B are bound, fail if there are no such
rows, or succeed (without binding anything) the same number of
times as there are matching rows in se_lt_x.
    \item If A is bound and B is unbound, bind B to each of the values in
\verb$se_lt_x.b$ where \verb$se_lt_x.a = A$
    \item If B is bound and A is unbound, bind A to each of the values in
\verb$se_lt_x.a$ where \verb$se_lt_x.b = B$
    \item If \arg{A} and \arg{B} are both unbound, bind \arg{A} and \arg{B} to each of
the tuples in \verb$se_lt_x$
\end{itemize}

\subsubsection{CQL Simple UPDATE}

\label{sec:cql-update}

\begin{code}
{[],
 update(se_lt_x, [c-100]),
 @ :: [a-'A1'],
 row_count(N)}
\end{code}

This corresponds to \verb$UPDATE se_lt_x SET c=100 WHERE se_lt_x.a='A1'$.
The '@' is a special alias referring to the table that is being updated.
The \predref{row_count}{1} term gives the number or rows updated.

\subsubsection{CQL WHERE with arithmetic comparison}

\label{sec:cql-where-arith}

\begin{code}
{[],
 se_lt_x :: [a-A, c-C],
 C > 10}
\end{code}

\subsubsection{CQL Simple INNER JOIN}

\label{sec:cql-inner-join}

\begin{code}
{[],
 se_lt_x :: [a-J1, c-C]
  =*=
 se_lt_y :: [d-J1, f-F]}
\end{code}

The join is \verb$se_lt_x.a = se_lt_y.d$ because of the shared variable \arg{J1}.
\verb$se_lt_x.c$ will be returned in \arg{C} and \verb$se_lt_y.f$ will be returned in
\arg{F}

\subsubsection{CQL Arithmetic UPDATE with an INNER JOIN and a WHERE restriction}

\label{sec:cql-update-join-where}

\begin{code}
{[],
 update(se_lt_x, [c-(C + 2 * F + 20)]),
 @ :: [a-A, c-C] =*= se_lt_y :: [d-A, f-F],
 C < 100}
\end{code}

This joins the table being updated (table \verb$se_lt_x$) on table \verb$se_lt_y$
where \verb$se_lt_x.a = se_lt_y.a$ and where \verb$se_lt_x.c < 200$ then updates
each identified row \verb$se_lt_x.c$ with the specified expression.

\subsubsection{CQL: Confirm row does not exist}

\label{sec:cql-not-exists}

\begin{code}
\+ exists {[], se_lt_x :: [a-'Z']}
\end{code}

\subsubsection{CQL: Aggregation - Count}

\label{sec:cql-count}

\begin{code}
{[],
 se_lt_x :: [count(c)-C]}
\end{code}

This will count the rows in table se_lt_x

\subsubsection{CQL: Aggregation - Sum}

\label{sec:cql-sum}

\begin{code}
{[],
 se_lt_x :: [sum(c)-C]}
\end{code}

Sum the values of attribute c in table se_lt_x

\subsubsection{CQL: Aggregation - Average}

\label{sec:cql-avg}

\begin{code}
{[],
 se_lt_x :: [avg(c)-C]}
\end{code}

Calculate the mean of the values of attribute c in table se_lt_x

\subsubsection{CQL: Maximum Value}

\label{sec:cql-max}

\begin{code}
{[],
 se_lt_x :: [max(c)-C]}
\end{code}

Calculate the maximum of the values of attribute c in table se_lt_x

\subsubsection{CQL: Minimum Value}

\label{sec:cql-min}

\begin{code}
{[],
 se_lt_x :: [min(c)-C]}
\end{code}

Calculate the minimum of the values of attribute c in table se_lt_x

\subsubsection{CQL: Aggregation requiring GROUP BY}

\label{sec:cql-group-by}

\begin{code}
{[],
 se_lt_z :: [g-G, sum(i)-I],
 group_by([G])}
\end{code}

This will generate the \verb$GROUP BY SQL$ and sum \verb$se_lt_z.i$ for each
value of \verb$se_lt_z.g$

\subsubsection{CQL: INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query}

\label{sec:cql-agg-subquery}

\begin{code}
{[],
 se_lt_x :: [b-J1, a-A]
   =*=
 se_lt_z :: [h-J1, i-I, g-Z],
 I > min(Y, se_lt_y :: [f-Y, d-Z])}
\end{code}

The main query and the sub-query share variable Z. The generated SQL is:

\begin{code}
SELECT
  x37.a, z4.i, z4.g
 FROM
  se_lt_x x37 INNER JOIN se_lt_z z4 ON x37.b=z4.h and z4.h=x37.b
 WHERE
  z4.i > (SELECT min(y11.f) FROM se_lt_y y11 WHERE z4.g=y11.d)
\end{code}

\subsubsection{CQL: INNER JOIN in an aggregation sub-query}

\label{sec:cql-join-subquery}

\begin{code}
{[],
 se_lt_y :: [d-D,f-F],
 F < sum(I,
         se_lt_x :: [b-J1]
           =*=
         se_lt_z :: [h-J1, i-I])}
\end{code}

\subsubsection{CQL: Negation}

\label{sec:cql-negation}

\begin{code}
{[],
 se_lt_x :: [a-A, b-B],
 \+ exists se_lt_y :: [d-A]}
\end{code}

The generated SQL is:

\begin{code}
SELECT
  x39.a, x39.b
 FROM
  se_lt_x x39
 WHERE NOT EXISTS (SELECT * FROM se_lt_y y13 WHERE x39.a = y13.d)
\end{code}

\subsubsection{CQL: EXISTS}

\label{sec:cql-exists}

An exists restriction translates to a \verb$WHERE$ sub-query and is used to
say that "each row returned in the main query must satisfy some
condition expressed by another query".

\textbf{Example}

\begin{code}
{[],
 se_lt_x :: [a-A, b-B],
 exists se_lt_y :: [d-A]}
\end{code}

compiles to:

\begin{code}
SELECT
  x.b, x.a
FROM
  se_lt_x x
WHERE
  EXISTS (SELECT * FROM se_lt_y WHERE x.a = y.d)
\end{code}

\subsubsection{CQL: Left Outer Join}

\label{sec:cql-left-outer-join}

\begin{code}
se_lt_x :: [a-J1, b-B]
  *==
se_lt_y :: [d-J1, e-E]}
\end{code}

\subsubsection{CQL: List-based Restrictions}

\label{sec:cql-list-restrictions}

CQL supports query restrictions based on lists. Note that in both cases
\textbf{\Snequal{} \Snil{} and \Sequal{} \Snil{} are equivalent} despite the obvious logical
inconsistency.

FIXME: Can we make this behaviour be controlled by a flag? It IS quite useful, even if it is completely illogical

\begin{code}
{[], se_lt_x :: [a-Bar], Bar == []}
\end{code}

and

\begin{code}
{[], se_lt_x :: [a-Bar], Bar \== []}
\end{code}

both do \textbf{exactly} the same thing - they will not restrict the query
based on Bar. The second case seems to be logically consistent - all
things are not in the empty list.

\subsubsection{CQL: Compile time in-list constraint}

\label{sec:cql-list-compilation}

If your list is bound at compile-time, you can simply use it as the
attribute value in CQL, for example:

\begin{code}
{[], se_lt_x :: [a-['ELSTON_M', 'LILLEY_N']]}
\end{code}

This does not require the list to be \textbf{ground}, merely \textbf{bound}. For
example, this is not precluded:

\begin{code}
foo(V1, V2):-
    {[], se_lt_x :: [a-[V1, V2]]}.
\end{code}

If, however, your list is not bound at compile-time, you must wrap the
variable in \predref{list}{1}:

\begin{code}
Bar = [a,b,c],
{[], se_lt_x :: [bar-list(Bar)]}
\end{code}

If you write

\begin{code}
foo(V1):-
    {[], se_lt_x :: [a-V1]}.
\end{code}

and at runtime call \verb$foo([value1])$, you will get a type error.

\textit{Remember:} If the list of IN values is \textit{empty} then no restriction is
generated i.e.

\begin{code}
{[], se_lt_x :: [a-[], b-B}

is the exactly the same as

{[], se_lt_x :: [b-B}
\end{code}

\subsubsection{CQL: Disjunction resulting in OR in WHERE clause}

\label{sec:cql-disjunction}

\begin{code}
{[],
 se_lt_x :: [a-A, b-B, c-C],
 (C == 10 ; B == 'B2', C < 4)}
\end{code}

The generated SQL is:

\begin{code}
SELECT
  x.a, x.b, x.c
 FROM
  se_lt_x x
 WHERE
  ((x.b = ? AND x.c < ?) OR x.c = ?)
\end{code}

\subsubsection{CQL: Disjunction resulting in different joins (implemented as a SQL UNION)}

\label{sec:cql-disj-diff-joins}

\begin{code}
{[],
 se_lt_x :: [a-A, c-C]
 =*=
 (se_lt_y :: [d-A] ; se_lt_z :: [g-A])}
\end{code}

The generated SQL is:

\begin{code}
SELECT
  x43.c
 FROM
  (se_lt_x x43 INNER JOIN se_lt_z z6 ON x43.a=z6.g AND z6.g=x43.a)

UNION

SELECT
  x44.c
 FROM
  (se_lt_x x44 INNER JOIN se_lt_y y16 ON x44.a=y16.d AND y16.d=x44.a)
\end{code}

\subsubsection{CQL: Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries)}

\label{sec:cql-disj-attrs}

\begin{code}
{[],
 (se_lt_x :: [a-A, c-10]
 ;
 se_lt_y :: [d-A, f-25])}
\end{code}

The output variable A is bound to the value from two different
attributes and so the query is implemented as two separate ODBC queries

\subsubsection{CQL: ORDER BY}

\label{sec:cql-order-by}

\begin{code}
{[],
 se_lt_z :: [g-G, h-H],
 order_by([-G])}
\end{code}

The order_by specification is a list of "signed" variables. The example
above will order by se_lt_z.g descending

\subsubsection{CQL: DISTINCT}

\label{sec:cql-distinct}

Use \verb$distinct(ListOfVars)$ to specify which attributes you want to be
distinct:

\begin{code}
test_distinct :-
  findall(UserName,
          {[],
           se_lt_x :: [a-UserName,
                       c-Key],
           Key >= 7,
           distinct([UserName])},
          L),
  length(L, N),
  format('~w solutions~n', [N]).

CALL  : user:test_distinct/0
26 solutions
EXIT  : user:test_distinct/0 (0.098133s, 0.00cpu, 1,488 inferences)
\end{code}

\subsubsection{CQL: SELECT with NOT NULL restriction}

\label{sec:cql-select-not-null}

\begin{code}
{[],
 se_lt_z :: [i-I, j-J],
 J \== {null}}
\end{code}

\subsubsection{CQL: First N}

\label{sec:cql-top}

\begin{code}
{[],
 N = 3,
 se_lt_z :: [i-I],
 top(N),
 order_by([+I])}
\end{code}

This generates a TOP clause in SQL Server, and LIMIT clauses for
PostgreSQL and SQLite

\subsubsection{CQL: Self JOIN}

\label{sec:cql-self-join}

\begin{code}
{[],
 se_lt_z :: [h-H, i-I1]
  =*=
 se_lt_z :: [h-H, i-I2],
 I1 \== I2}
\end{code}

\subsubsection{CQL: Removing null comparisions}

\label{sec:cql-no-null-compare}

Use the ignore_if_null wrapper in your CQL to 'filter out' null input
values. This is a useful extension for creating user-designed searches.

\begin{code}
{[],
 se_lt_x :: [a-UserName,
             b-ignore_if_null(SearchKey),
             ...]}
\end{code}

At runtime, if SearchKey is bound to a value other than \{null\} then the
query will contain \verb$WHERE ... b = ?$. If, however, SearchKey is bound
to \verb${null}$, then this comparison will be omitted.

\textbf{Disjunctions}

In general, don't use ignore_if_null in disjunctions. Consider this
query:

\begin{code}
SearchKey = '%ELSTON%',
{[],
 se_lt_x :: [a-UserName,
             b-RealName],
 ( RealName =~ SearchKey
 ; UserName =~ SearchKey)}
\end{code}

The query means "find a user where the UserName contains ELSTON OR the
RealName contain ELSTON". If !SearchKey is \{null\} then RealName=\Stilde{} \{null\}
will fail, which is correct. If ignore_if_null was used, the test would
\textit{succeed}, which means the disjunction would always succeed i.e. the
query would contain no restriction, which is clearly not the intended
result. FIXME: Mike, what is this all about?

\subsubsection{CQL: Three table JOIN}

\label{sec:cql-three-table-join}

\begin{code}
{[],
 se_lt_x :: [a-A, c-C]
  =*=
 se_lt_y :: [d-A, f-F]
  =*=
 se_lt_z :: [i-F, g-G]}
\end{code}

The shared variable A joins \verb$se_lt_x$ and \verb$se_lt_y$; the shared variable
\arg{F} joins \verb$se_lt_y$ and \verb$se_lt_z$

\subsubsection{CQL: Three table JOIN with NOLOCK locking hint}

\label{sec:cql-three-nolock}

\begin{code}
{[],
 se_lt_x :: [a-A, c-C]
  =*=
 #se_lt_y :: [d-A, f-F]
  =*=
 #se_lt_z :: [i-F, g-G]}
\end{code}

The hash operator indicates the table that should be accessed WITH (NOLOCK)

\subsubsection{CQL: SELECT with LIKE}

\label{sec:cql-like}

\begin{code}
{[],
 se_lt_z :: [g-G, i-I],
 G =~ 'A_'}
\end{code}

The operator =\Stilde{} means LIKE. If you are using PostgreSQL, it means ILIKE.

\subsubsection{CQL: Writing exceptions directly to the database}

\label{sec:cql-write}

You can write an exception term directly to a varchar-type column in the
database. Note that it will be rendered as text using \Stilde{}p, and truncated
if necessary - so you certainly can't read it out again and expect to
get an exception! Example code:

\begin{code}
catch(process_message(Message),
      Exception,
      {[],
      update(some_table, [status-'ERROR',
                          status_comment-Exception]),
      @ :: [some_table_primary_key-PrimaryKey]}).
\end{code}

FIXME: This code is specific to my usage of CQL

\subsubsection{CQL: TOP N is Parametric}

\label{sec:cql-top-n-parametric}

You can pass the "N" is TOP N as a parameter (Subject to DBMS
compatibility. This works in SQL Server 2005 and later, and PostgreSQL 9
(possibly earlier versions) and SQLite3.

\begin{code}
N = 3,
findall(I,
        {[],
         se_lt_z :: [i-I], top(N), order_by([+I])},
        L)
\end{code}

\subsubsection{CQL: Using \predref{compile_time_goal}{1}}

\label{sec:cql-compile-time-goal}

You can include \verb$compile_time_goal(Goal)$ in your CQL. If you specify a
module, it will be used, otherwise the goal will be called in the
current module. Note that the goal is executed in-order - if you want to
use the bindings in your CQL, you must put the compile_time_goal before
them.

\textbf{Example 1}

\begin{code}
{[],
 se_lt_x :: [a-UserName,
             b-RealName,
             d-FavouriteColour],
   compile_time_goal(standard_batch_size_for_search(StandardBatchSize)),
   top(StandardBatchSize),
   order_by([+UserName]}
\end{code}

\textbf{Example 2}

\begin{code}
excellent_colours(['RED', 'BLUE']).

{[],
 se_lt_x :: [a-UserName,
             b-RealName,
             d-FavouriteColour],
 compile_time_goal(excellent_colours(Colours)),
 FavouriteColour == Colours}
\end{code}

\subsubsection{CQL: ON}

\label{sec:cql-on}

CQL supports both constant and shared variable join specifications. This
is particularly useful when specifying outer joins.

\textbf{Example}

\begin{code}
{[],
 se_lt_x :: [a-UserNameA,
             b-RealName,
             d-FavouriteColour]
 *==
 se_lt_x :: [a-UserNameB,
             e-FavouriteFood] on( UserNameA == UserNameB,
                                  FavouriteColour == FavouriteFood,
                                  FavouriteFood == 'ORANGE')}
\end{code}

All the CQL comparison operators, \verb$<, =<, ==, =~, \=~, \==, >=, >$ can
be used in ON specifications.

For example:

\begin{code}
{[],
 se_lt_z :: [i-J1, k-K]
 *==
 se_lt_x :: [c-J1, a-A, b-B] on A \== 'A1'},
\end{code}

\subsubsection{CQL: Expressions In Where Restrictions}

\label{sec:cql-expr-in-where}

Expressions in WHERE restrictions are supported, for example:

\begin{code}
{[],
 se_lt_n :: [i-I, j-J, k-K],
 J > 10 * (K / I) + 15},
\end{code}

\subsubsection{CQL: Explicitly avoid the "No WHERE restriction" message}

\label{sec:cql-no-where}

To avoid accidentally deleting or updating all rows in a table CQL
raises an exception if there is no WHERE restriction.

Sometimes however you really do need to delete or update all rows in a
table.

To support this requirement in a disciplined way (and to avoid the
creation of "dummy" WHERE restrictions) the keyword
\textbf{absence_of_where_restriction_is_deliberate} has been added. For
example:

\begin{code}
{[],
 update(se_lt_x, [c-10]),
        @ :: [],
        absence_of_where_restriction_is_deliberate}
\end{code}

\subsubsection{CQL: HAVING}

\label{sec:cql-having}

HAVING restrictions can be specified. For example:

\begin{code}
{[],
 se_lt_z :: [sum(i)-I,
             g-G],
 group_by([G]),
 having(I > 30)}
\end{code}

For a description of HAVING see
\url{http://en.wikipedia.org/wiki/Having_(SQL)}

There is one important difference between SQL HAVING and SQL WHERE
clauses. The SQL WHERE clause condition is tested against \textbf{each and
every} row of data, while the SQL HAVING clause condition is tested
against the \textit{groups and/or aggregates specified in the SQL GROUP BY
clause and/or the SQL SELECT column list}.

\subsubsection{CQL: INSERT and UPDATE value in-line formatting}

\label{sec:cql-inst-update-value}

INSERT and UPDATE values can be formatted in-line at runtime. For example:

\begin{code}
Suffix = 'NOGG',
cql_transaction(Schema, UserId,
                {[],
                insert(se_lt_x, [a-'A', b-'B', c-100, d-format('EGG_~w', [Suffix])])}),
\end{code}

will insert 'EGG_NOGG' into attribute 'd'.

\subsubsection{CQL: Negations in WHERE Clauses}

\label{sec:cql-neg-where}

You can specify negations in CQL WHERE clauses e.g.

\begin{code}
{[],
 se_lt_z :: [g-G, h-H, i-I],
 \+((G == 'A1', H == 'B1' ; G == 'D1', H == 'B3'))},
\end{code}

Note that, just like in Prolog, \Snot{} is a unary operator hence the
"double" brackets in the example above.

\subsubsection{CQL: Predicate-generated Attribute Values}

\label{sec:cql-gen-att-values}

It is possible to generate \textbf{compile time} attribute values by
specifying a \textit{predicate} which is executed when the CQL statement is
compiled.

The predicate must return the value you want as its last argument. You
specify the predicate where you would normally put the attribute value.
The predicate is specified \textit{with its output argument missing}.

\textbf{Example} - Using domain allowed values in a query.

In the following CQL statement the predicate \predref{cql_domain_allowed_value}{3}
is called within \predref{findall}{3} \textbf{at compile time} to generate a list of
domain values that restrict favourite_colour to be 'ORANGE' or 'PINK' or
'BLUE', or 'GREEN'.

\begin{code}
colour('ORANGE').
colour('PINK').
colour('BLUE').
colour('GREEN').

{[],
 se_lt_x :: [d-findall(Value,
                       permissible_colour(Value)),
             a-UserName]}
\end{code}

Note how \predref{findall}{3} is actually called by specifying \predref{findall}{2}.

There is not much point using predicate-generated attribute values in
compile-at-runtime CQL as you can always call the predicate to generate
the required values \textit{outside} the CQL statement.

\subsubsection{CQL: INSERT from SELECT}

\label{sec:cql-insert-from-select}

INSERT from SELECT is supported:

\begin{code}
Constant = 'MIKE',
{[],
 insert(se_lt_x1, [x_pk-Pk, a-A, b-B, c-C, d-Constant]),
 se_lt_x :: [x_pk-Pk, a-A, b-B, c-C, as(d)-Constant]}
\end{code}

which generates the following SQL:

\begin{code}
INSERT INTO se_lt_x1 (x_pk, a, b, c, d)
SELECT se_lt_x_955.x_pk, se_lt_x_955.a, se_lt_x_955.b, se_lt_x_955.c, ? AS d
  FROM se_lt_x lt_x_955
\end{code}

Note the use of the \verb$as(d)$ construct in the SELECT part of the CQL to
make the constant \textbf{'MIKE'} appear to come from the SELECT thus setting
\verb$lt_x1.d$ to \textbf{'MIKE'} in every row inserted.

\subsection{CQL: Hooks}

\label{sec:cql-hooks}

CQL provides a large number of hooks to fine-tune behaviour and allow
for customization. These are:

\subsubsection{CQL: Generated Code Hooks}

\label{sec:cql-code-hooks}

\begin{itemize}
    \item cql:\verb$cql_dependency_hook(+EntitySet, +Module)$ can be defined to be
notified when a given Module references a list of database
entities. This can be used to manage metadata/code dependency
    \item cql:\verb$cql_generated_sql_hook(+Filename, +LineNumber, +Goals)$ can be
defined to examine generated SQL. Use \verb$cql_sql_clause(+Goals, -SQL, -Parameters)$ to examine the goals
    \item cql:\verb$cql_index_suggestion_hook(+Index)$ can be defined if you are
interested in proposed indices for your schema. Note that this is
not very mature (yet)
\end{itemize}

\subsubsection{CQL: Data Representation Hooks}

\label{sec:cql-represent-hooks}

\begin{itemize}
    \item cql:\verb$cql_atomic_value_check_hook(+Value)$ can be defined to declare
new 'atomic' types (That is, types which can be written directly to
the database), such as a representation like \verb$boolean(true)$ for 1.
    \item cql:\verb$cql_check_value_hook(+Value)$ can be used to check that a value
is legal
    \item cql:\verb$application_value_to_odbc_value_hook(+OdbcDataType, +Schema, +TableName, +ColumnName, +Qualifiers, +ApplicationValue, -OdbcValue)$.
    \item cql:\verb$odbc_value_to_application_value_hook(+OdbcDataType, +Schema, +TableName, +ColumnName, +Domain, +OdbcValue, -ApplicationValue)$.
\end{itemize}

\subsubsection{CQL: Application Integration}

\label{sec:cql-appl-hooks}

\begin{itemize}
    \item cql:\verb$cql_access_token_hook(+AccessToken, -UserId)$ can be defined to
map the generic 'AccessToken' passed to \predref{cql_transaction}{3} to a user
ID. If not defined, the AccessToken is assumed to be the user ID.
This UserID is used in logging.
    \item cql:log_selects can be defined if you want to receive logging
information about selects. By default only update, delete and
insert are logged
    \item cql:\verb$cql_execution_hook(+Statement, +OdbcParameters, +OdbcParameterDataTypes, -Row)$ can be defined if you want to
implement the exeuction yourself (for example, to add extra
debugging)
    \item cql:\verb$cql_log_hook(+Topics, +Level, +Format, +Args)$ can be defined
to redirect CQL logging.

\begin{itemize}
    \item Levels is one of informational, warning, or error
    \item Topics is a list of topics. Currently the only lists possible
are \Snil{} and [\verb$debug(deadlocks)$]
\end{itemize}

    \item cql:\verb$sql_gripe_hook(+Level, +Format, +Args)$ is called when
suspect SQL is found by the SQL parser
    \item cql:\verb$cql_normalize_atom_hook(+DBMS, +ApplciationAtom, -DBMSAtom)$
can be used to create a map for atoms in a specific DBMS. For
example, your schema may have arbitrarily long table names, but
your DBMS may only allow names up to 64 bytes long. In this case,
you can create a scheme for mapping the application-level atom to
the DBMS. Other uses include deleting or normalizing illegal
characters in names
    \item cql:\verb$cql_error_hook(+ErrorId, +Format, +Args)$ can be defined to
generate a specific exception term from the given arguments. If not
defined (or if it does not throw an exception, or fails), you will
get \verb$cql_error(ErrorId, FormattedMessage)$.
    \item cql:\verb$cql_max_db_connections_hook(-Max)$
can be defined to limit the number of simultaneous connections each
thread will attempt to have
    \item cql:\verb$odbc_connection_complete_hook(+Schema, +Details, +Connection)$
can be hooked if you want to know every time a connection is made
    \item cql:\verb$cql_transaction_info_hook(+AccessToken, +Connection, +DBMS, +Goal, -Info)$
can be defined if you want to define any application-defined
information on a per-transaction level. This can be recovered via
\verb$database_transaction_query_info(?ThreadId, ?Goal, ?Info)$.
\end{itemize}

\subsubsection{CQL: Inline values}

\label{sec:cql-inline-hooks}

\begin{description}
    \infixtermitem{\Smodule}{\term{cql}{}}{\term{cql_inline_domain_value_hook}{+DomainName, +Value}}
can be defined if you want the given value to be 'inlined' into the
CQL (ie not supplied as a parameter). Great care must be taken to
avoid SQL injection attacks if this is used.
\end{description}

\subsubsection{CQL: Schema}

\label{sec:cql-shema-hook}

These define the schema. You MUST either define them, or include
\file{library(cql/cql_autoschema)} and add two directives to build the schema
automatically:

\begin{shortlist}
    \item \Sneck{}\verb$register_database_connection_details(+Schema, +ConnectionInfo)$.
    \item \Sneck{}\verb$build_schema(+Schema)$.
\end{shortlist}

Otherwise, you need to define at least \qpredref{cql}{default_schema}{1} and
\qpredref{cql}{dbms}{2}, and then as many of the other facts as needed for your
schema.

\begin{itemize}
    \item cql:\verb$default_schema(-Schema)$
MUST be defined. CQL autoschema will define this for you if you use
it.
    \item cql:\verb$dbms(+Schema, -DBMS)$
MUST be defined for every schema you use. CQL autoschema will define
this for you if you use it. DBMS must be one of the following:

\begin{shortlist}
    \item 'Microsoft SQL Server'
    \item 'PostgreSQL'
    \item 'SQLite'
\end{shortlist}

    \item cql:\verb$odbc_data_type(+Schema, +TableName, +ColumnName, +OdbcDataType)$.
    \item cql:\verb$primary_column_name(+Schema, +Tablename, +ColumnName)$.
    \item cql:database_attribute(+EntityType:table/view, +Schema:atom, +EntityName:atom, +ColumnName:atom, +DomainOrNativeType:atom, +AllowsNulls:\verb$allows_nulls(true/false)$, +IsIdentity:\verb$is_identity(true/false)$, +ColumnDefault).
    \item cql:\verb$database_domain(+DomainName, +OdbcDataType)$.
    \item cql:\verb$routine_return_type(+Schema, +RoutineName, +OdbcDataType)$.
    \item cql:\verb$database_constraint(+Schema, +EntityName, +ConstraintName, +Constraint)$.
\end{itemize}

\subsubsection{CQL: Event Processing and History}

\label{sec:cql-events-and-history}

CQL provides hooks for maintaining detailed history of data in the
database.

The hook predicates are:

\begin{shortlist}
    \item cql:\verb$cql_event_notification_table(+Schema, +TableName)$
    \item cql:\verb$cql_history_attribute(+Schema, +TableName, +ColumnName)$
    \item cql:\verb$cql_update_history_hook(+Schema, +TableName, +ColumnName, +PrimaryKeyColumnName, +PrimaryKeyValue, +ApplicationValueBefore, +ApplicationValueAfter, +AccessToken, +TransactionId, +TransactionTimestamp, +ThreadId, +Connection, +Goal)$.
    \item cql:\verb$process_database_events(+Events)$
\end{shortlist}

Event Processing and History recording can be suppressed for a
particular update/insert/delete statement by including the
_no_state_change_actions_9 directive.

For example

\begin{code}
{[],
 update(se_lt_x, [f-'LILAC']
 @ :: [a-'ELSTON_M'],
 no_state_change_actions,   % Don't want history to record this change
 row_count(RowCount)}
\end{code}

\subsubsection{CQL: Statistical Hooks}

\label{sec:cql-statistics-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:

\begin{code}
cql_statistic_monitored_attribute_hook(my_schema, my_table,
                                       my_table_status_column).
\end{code}

This will examine the domain for the column 'my_table_status_column',
and generate a statistic for each of
my_table::\verb$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::\verb$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
\predref{cql_statistic_monitored_attribute_change_hook}{5}, where the last argument
is a signed value indicating the number of changes to that particular
statistic.\vspace{0.7cm}

\begin{description}
    \predicate{cql_set_module_default_schema}{1}{+Schema}
Set the \arg{Schema} for a module

    \predicate{cql_get_module_default_schema}{2}{+Module, ?ModuleDefaultSchema}

    \predicate{cql_goal_expansion}{3}{?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

    \predicate{cql_runtime}{7}{+Schema, +IgnoreIfNullVariables, +CqlA, +CqlB, +VariableMap, +FileName, +LineNumber}

    \predicate{cql_temporary_column_name}{4}{?Schema, ?DataType, ?ColumnName, ?Type}

    \predicate{cql_show}{2}{:Goal, +Mode}
Called when \predref{\Squest}{1}, \predref{??}{1}, and \predref{???}{1} applied to CQL

\begin{arguments}
\arg{Goal} & goal term \\
\arg{Mode} & minimal ; explicit ; full \\
\end{arguments}

    \predicate{statistic_monitored_attribute}{3}{+Schema, +TableName, +ColumnName}

    \predicate[multifile]{dbms}{2}{+Schema, -DBMSName}
Determine the DBMS for a given \arg{Schema}.
Can be autoconfigured.

    \predicate[multifile]{odbc_data_type}{4}{+Schema, +TableSpec, +ColumnName, ?OdbcDataType}
\arg{OdbcDataType} must be a native SQL datatype, such as \verb$varchar(30)$ or \verb$decimal(10, 5)$
Can be autoconfigured.

    \predicate[multifile]{primary_key_column_name}{3}{+Schema, +TableName, -PrimaryKeyAttributeName}
Can be autoconfigured.

    \predicate[multifile]{routine_return_type}{3}{?Schema:atom, ?EntityName:atom, ?OdbcType}
Can be autoconfigured

    \predicate[nondet,multifile]{database_constraint}{4}{?Schema:atom, ?EntityName:atom, ?ConstraintName:atom, ?Constraint}
\arg{Constraint} is one of:

\begin{shortlist}
    \item \verb$primary_key(ColumnNames:list)$
    \item \verb$foreign_key(ForeignTableName:atom, ForeignColumnNames:list, ColumnNames:list)$
    \item \verb$unique(ColumnNames:list)$
    \item \verb$check(CheckClause)$
\end{shortlist}

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

    \predicate{attribute_domain}{4}{+Schema, +TableName, +ColumnName, -Domain}

    \predicate{database_identity}{3}{?Schema:atom, ?EntityName:atom, ?ColumnName:atom}

    \predicate{database_key}{5}{?Schema:atom, ?EntityName:atom, ?ConstraintName:atom, ?KeyColumnNames:list, ?KeyType}

\begin{arguments}
\arg{KeyColumnNames} & list of \textit{atom} in database-supplied order \\
\arg{KeyType} & \textit{identity} ; \textit{'primary key'} ; \textit{unique} \\
\end{arguments}

    \predicate[multifile]{cql_event_notification_table}{2}{+Schema, +TableName}

    \predicate[multifile]{cql_history_attribute}{3}{+Schema, +TableName, +ColumnName}

    \predicate[multifile]{sql_gripe_hook}{3}{+Level, +Format, +Args}
Called when something dubious is found by the SQL parser.

    \predicate{cql_normalize_name}{3}{+DBMS, +Name, -NormalizedName}
Normalize a name which is potentially longer than the \arg{DBMS} allows to a unique truncation

    \predicate[det]{register_database_connection_details}{2}{+Schema:atom, +ConnectionDetails}
This should be called once to register the database connection details.

\begin{arguments}
\arg{ConnectionDetails} & \verb$driver_string(DriverString)$ or \verb$dsn(Dsn, Username, Password)$ \\
\end{arguments}
\end{description}