- Documentation
- Reference manual
- Packages
- Constraint Query Language A high level interface to SQL databases
- library(cql/cql): CQL - Constraint Query Language
- Warnings from CQL
- CQL: Retrieved nulls have special logic to handle outer joins
- CQL: Getting Started Quickly
- Debugging CQL queries
- Prolog Variables in CQL queries
- CQL Special Attributes
- CQL Examples
- CQL Simple INSERT
- CQL Simple INSERT with retrieval of identity of the inserted
- CQL Simple DELETE
- CQL Simple SELECT
- CQL Simple UPDATE
- CQL WHERE with arithmetic comparison
- CQL Simple INNER JOIN
- CQL Arithmetic UPDATE with an INNER JOIN and a WHERE restriction
- CQL: Confirm row does not exist
- CQL: Aggregation - Count
- CQL: Aggregation - Sum
- CQL: Aggregation - Average
- CQL: Maximum Value
- CQL: Minimum Value
- CQL: Aggregation requiring GROUP BY
- CQL: INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query
- CQL: INNER JOIN in an aggregation sub-query
- CQL: Negation
- CQL: EXISTS
- CQL: Left Outer Join
- CQL: List-based Restrictions
- CQL: Compile time in-list constraint
- CQL: Disjunction resulting in OR in WHERE clause
- CQL: Disjunction resulting in different joins (implemented as a SQL UNION)
- CQL: Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries)
- CQL: ORDER BY
- CQL: DISTINCT
- CQL: SELECT with NOT NULL restriction
- CQL: First N
- CQL: Self JOIN
- CQL: Removing null comparisions
- CQL: Three table JOIN
- CQL: Three table JOIN with NOLOCK locking hint
- CQL: SELECT with LIKE
- CQL: Writing exceptions directly to the database
- CQL: TOP N is Parametric
- CQL: Using compile_time_goal/1
- CQL: ON
- CQL: Expressions In Where Restrictions
- CQL: Explicitly avoid the "No WHERE restriction" message
- CQL: HAVING
- CQL: INSERT and UPDATE value in-line formatting
- CQL: Negations in WHERE Clauses
- CQL: Predicate-generated Attribute Values
- CQL: INSERT from SELECT
- CQL: Hooks
- library(cql/cql): CQL - Constraint Query Language
- Constraint Query Language A high level interface to SQL databases
1.7 CQL Examples
Rather than provide an abstract description of CQL syntax here is a set of examples that show how to use it.
1.7.1 CQL Simple INSERT
{[], insert(se_lt_x, [a-'A', b-'B', c-100])}
1.7.2 CQL Simple INSERT with retrieval of identity of the inserted
{[], insert(se_lt_x, [a-'A', b-'B', c-100]), identity(I)}
1.7.3 CQL Simple DELETE
{[], delete(se_lt_x, [x_pk-I])}
Note that the WHERE clause is part of the delete/2 term unlike update where the WHERE clause is defined outside the 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.
1.7.4 CQL Simple SELECT
{[], se_lt_x :: [a-A, b-B]}
This query will either:
- 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.
- If A is bound and B is unbound, bind B to each of the values in
se_lt_x.b
wherese_lt_x.a = A
- If B is bound and A is unbound, bind A to each of the values in
se_lt_x.a
wherese_lt_x.b = B
- If A and B are both unbound, bind A
and B to each of the tuples in
se_lt_x
1.7.5 CQL Simple UPDATE
{[], update(se_lt_x, [c-100]), @ :: [a-'A1'], row_count(N)}
This corresponds to 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 row_count/1 term gives
the number or rows updated.
1.7.6 CQL WHERE with arithmetic comparison
{[], se_lt_x :: [a-A, c-C], C > 10}
1.7.7 CQL Simple INNER JOIN
{[], se_lt_x :: [a-J1, c-C] =*= se_lt_y :: [d-J1, f-F]}
The join is se_lt_x.a = se_lt_y.d
because of the shared
variable J1.
se_lt_x.c
will be returned in C and se_lt_y.f
will be returned in
F
1.7.8 CQL Arithmetic UPDATE with an INNER JOIN and a WHERE restriction
{[], update(se_lt_x, [c-(C + 2 * F + 20)]), @ :: [a-A, c-C] =*= se_lt_y :: [d-A, f-F], C < 100}
This joins the table being updated (table se_lt_x
) on
table se_lt_y
where se_lt_x.a = se_lt_y.a
and
where se_lt_x.c < 200
then updates each identified row se_lt_x.c
with the specified expression.
1.7.9 CQL: Confirm row does not exist
\+ exists {[], se_lt_x :: [a-'Z']}
1.7.10 CQL: Aggregation - Count
{[], se_lt_x :: [count(c)-C]}
This will count the rows in table se_lt_x
1.7.11 CQL: Aggregation - Sum
{[], se_lt_x :: [sum(c)-C]}
Sum the values of attribute c in table se_lt_x
1.7.12 CQL: Aggregation - Average
{[], se_lt_x :: [avg(c)-C]}
Calculate the mean of the values of attribute c in table se_lt_x
1.7.13 CQL: Maximum Value
{[], se_lt_x :: [max(c)-C]}
Calculate the maximum of the values of attribute c in table se_lt_x
1.7.14 CQL: Minimum Value
{[], se_lt_x :: [min(c)-C]}
Calculate the minimum of the values of attribute c in table se_lt_x
1.7.15 CQL: Aggregation requiring GROUP BY
{[], se_lt_z :: [g-G, sum(i)-I], group_by([G])}
This will generate the GROUP BY SQL
and sum se_lt_z.i
for each value of se_lt_z.g
1.7.16 CQL: INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query
{[], 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])}
The main query and the sub-query share variable Z. The generated SQL is:
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)
1.7.17 CQL: INNER JOIN in an aggregation sub-query
{[], se_lt_y :: [d-D,f-F], F < sum(I, se_lt_x :: [b-J1] =*= se_lt_z :: [h-J1, i-I])}
1.7.18 CQL: Negation
{[], se_lt_x :: [a-A, b-B], \+ exists se_lt_y :: [d-A]}
The generated SQL is:
SELECT x39.a, x39.b FROM se_lt_x x39 WHERE NOT EXISTS (SELECT * FROM se_lt_y y13 WHERE x39.a = y13.d)
1.7.19 CQL: EXISTS
An exists restriction translates to a WHERE
sub-query
and is used to say that "each row returned in the main query must
satisfy some condition expressed by another query".
Example
{[], se_lt_x :: [a-A, b-B], exists se_lt_y :: [d-A]}
compiles to:
SELECT x.b, x.a FROM se_lt_x x WHERE EXISTS (SELECT * FROM se_lt_y WHERE x.a = y.d)
1.7.20 CQL: Left Outer Join
se_lt_x :: [a-J1, b-B] *== se_lt_y :: [d-J1, e-E]}
1.7.21 CQL: List-based Restrictions
CQL supports query restrictions based on lists. Note that in both
cases
\==
[]
and ==
[]
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
{[], se_lt_x :: [a-Bar], Bar == []}
and
{[], se_lt_x :: [a-Bar], Bar \== []}
both do 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.
1.7.22 CQL: Compile time in-list constraint
If your list is bound at compile-time, you can simply use it as the attribute value in CQL, for example:
{[], se_lt_x :: [a-['ELSTON_M', 'LILLEY_N']]}
This does not require the list to be ground, merely bound. For example, this is not precluded:
foo(V1, V2):- {[], se_lt_x :: [a-[V1, V2]]}.
If, however, your list is not bound at compile-time, you must wrap the variable in list/1:
Bar = [a,b,c], {[], se_lt_x :: [bar-list(Bar)]}
If you write
foo(V1):- {[], se_lt_x :: [a-V1]}.
and at runtime call foo([value1])
, you will get a type
error.
Remember: If the list of IN values is empty then no restriction is generated i.e.
{[], se_lt_x :: [a-[], b-B} is the exactly the same as {[], se_lt_x :: [b-B}
1.7.23 CQL: Disjunction resulting in OR in WHERE clause
{[], se_lt_x :: [a-A, b-B, c-C], (C == 10 ; B == 'B2', C < 4)}
The generated SQL is:
SELECT x.a, x.b, x.c FROM se_lt_x x WHERE ((x.b = ? AND x.c < ?) OR x.c = ?)
1.7.24 CQL: Disjunction resulting in different joins (implemented as a SQL UNION)
{[], se_lt_x :: [a-A, c-C] =*= (se_lt_y :: [d-A] ; se_lt_z :: [g-A])}
The generated SQL is:
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)
1.7.25 CQL: Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries)
{[], (se_lt_x :: [a-A, c-10] ; se_lt_y :: [d-A, f-25])}
The output variable A is bound to the value from two different attributes and so the query is implemented as two separate ODBC queries
1.7.26 CQL: ORDER BY
{[], se_lt_z :: [g-G, h-H], order_by([-G])}
The order_by specification is a list of "signed" variables. The example above will order by se_lt_z.g descending
1.7.27 CQL: DISTINCT
Use distinct(ListOfVars)
to specify which attributes you
want to be distinct:
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)
1.7.28 CQL: SELECT with NOT NULL restriction
{[], se_lt_z :: [i-I, j-J], J \== {null}}
1.7.29 CQL: First N
{[], N = 3, se_lt_z :: [i-I], top(N), order_by([+I])}
This generates a TOP clause in SQL Server, and LIMIT clauses for PostgreSQL and SQLite
1.7.30 CQL: Self JOIN
{[], se_lt_z :: [h-H, i-I1] =*= se_lt_z :: [h-H, i-I2], I1 \== I2}
1.7.31 CQL: Removing null comparisions
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.
{[], se_lt_x :: [a-UserName, b-ignore_if_null(SearchKey), ...]}
At runtime, if SearchKey is bound to a value other than {null} then
the query will contain WHERE ... b = ?
. If, however,
SearchKey is bound to {null}
, then this comparison will be
omitted.
Disjunctions
In general, don't use ignore_if_null in disjunctions. Consider this query:
SearchKey = '%ELSTON%', {[], se_lt_x :: [a-UserName, b-RealName], ( RealName =~ SearchKey ; UserName =~ SearchKey)}
The query means "find a user where the UserName contains ELSTON OR
the RealName contain ELSTON". If !SearchKey is {null} then RealName=~
{null}
will fail, which is correct. If ignore_if_null was used, the test would
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?
1.7.32 CQL: Three table JOIN
{[], se_lt_x :: [a-A, c-C] =*= se_lt_y :: [d-A, f-F] =*= se_lt_z :: [i-F, g-G]}
The shared variable A joins se_lt_x
and se_lt_y
;
the shared variable
F joins se_lt_y
and se_lt_z
1.7.33 CQL: Three table JOIN with NOLOCK locking hint
{[], se_lt_x :: [a-A, c-C] =*= #se_lt_y :: [d-A, f-F] =*= #se_lt_z :: [i-F, g-G]}
The hash operator indicates the table that should be accessed WITH (NOLOCK)
1.7.34 CQL: SELECT with LIKE
{[], se_lt_z :: [g-G, i-I], G =~ 'A_'}
The operator =~
means LIKE. If you are using PostgreSQL,
it means ILIKE.
1.7.35 CQL: Writing exceptions directly to the database
You can write an exception term directly to a varchar-type column in
the database. Note that it will be rendered as text using ~
p,
and truncated if necessary - so you certainly can't read it out again
and expect to get an exception! Example code:
catch(process_message(Message), Exception, {[], update(some_table, [status-'ERROR', status_comment-Exception]), @ :: [some_table_primary_key-PrimaryKey]}).
FIXME: This code is specific to my usage of CQL
1.7.36 CQL: TOP N is 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.
N = 3, findall(I, {[], se_lt_z :: [i-I], top(N), order_by([+I])}, L)
1.7.37 CQL: Using compile_time_goal/1
You can include 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.
Example 1
{[], se_lt_x :: [a-UserName, b-RealName, d-FavouriteColour], compile_time_goal(standard_batch_size_for_search(StandardBatchSize)), top(StandardBatchSize), order_by([+UserName]}
Example 2
excellent_colours(['RED', 'BLUE']). {[], se_lt_x :: [a-UserName, b-RealName, d-FavouriteColour], compile_time_goal(excellent_colours(Colours)), FavouriteColour == Colours}
1.7.38 CQL: ON
CQL supports both constant and shared variable join specifications. This is particularly useful when specifying outer joins.
Example
{[], se_lt_x :: [a-UserNameA, b-RealName, d-FavouriteColour] *== se_lt_x :: [a-UserNameB, e-FavouriteFood] on( UserNameA == UserNameB, FavouriteColour == FavouriteFood, FavouriteFood == 'ORANGE')}
All the CQL comparison operators, <, =<, ==, =~, \=~, \==, >=, >
can be used in ON specifications.
For example:
{[], se_lt_z :: [i-J1, k-K] *== se_lt_x :: [c-J1, a-A, b-B] on A \== 'A1'},
1.7.39 CQL: Expressions In Where Restrictions
Expressions in WHERE restrictions are supported, for example:
{[], se_lt_n :: [i-I, j-J, k-K], J > 10 * (K / I) + 15},
1.7.40 CQL: Explicitly avoid the "No WHERE restriction" message
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 absence_of_where_restriction_is_deliberate has been added. For example:
{[], update(se_lt_x, [c-10]), @ :: [], absence_of_where_restriction_is_deliberate}
1.7.41 CQL: HAVING
HAVING restrictions can be specified. For example:
{[], se_lt_z :: [sum(i)-I, g-G], group_by([G]), having(I > 30)}
For a description of HAVING see 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 each and every row of data, while the SQL HAVING clause condition is tested against the groups and/or aggregates specified in the SQL GROUP BY clause and/or the SQL SELECT column list.
1.7.42 CQL: INSERT and UPDATE value in-line formatting
INSERT and UPDATE values can be formatted in-line at runtime. For example:
Suffix = 'NOGG', cql_transaction(Schema, UserId, {[], insert(se_lt_x, [a-'A', b-'B', c-100, d-format('EGG_~w', [Suffix])])}),
will insert’EGG_NOGG’into attribute’d’.
1.7.43 CQL: Negations in WHERE Clauses
You can specify negations in CQL WHERE clauses e.g.
{[], se_lt_z :: [g-G, h-H, i-I], \+((G == 'A1', H == 'B1' ; G == 'D1', H == 'B3'))},
Note that, just like in Prolog, \+
is a unary operator
hence the "double" brackets in the example above.
1.7.44 CQL: Predicate-generated Attribute Values
It is possible to generate compile time attribute values by specifying a 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 with its output argument missing.
Example - Using domain allowed values in a query.
In the following CQL statement the predicate cql_domain_allowed_value/3 is called within findall/3 at compile time to generate a list of domain values that restrict favourite_colour to be’ORANGE’or’PINK’or’BLUE’, or’GREEN’.
colour('ORANGE'). colour('PINK'). colour('BLUE'). colour('GREEN'). {[], se_lt_x :: [d-findall(Value, permissible_colour(Value)), a-UserName]}
Note how findall/3 is actually called by specifying 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 outside the CQL statement.
1.7.45 CQL: INSERT from SELECT
INSERT from SELECT is supported:
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]}
which generates the following SQL:
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
Note the use of the as(d)
construct in the SELECT part
of the CQL to make the constant ’MIKE’ appear to come
from the SELECT thus setting
lt_x1.d
to ’MIKE’ in every row inserted.