Rather than provide an abstract description of CQL syntax here is a set of examples that show how to use it.
{[],
insert(se_lt_x, [a-'A', b-'B', c-100])}
{[],
insert(se_lt_x, [a-'A', b-'B', c-100]),
identity(I)}
{[],
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.
{[],
se_lt_x :: [a-A, b-B]}
This query will either:
se_lt_x.b where se_lt_x.a = Ase_lt_x.a where se_lt_x.b = Bse_lt_x
{[],
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.
{[],
se_lt_x :: [a-A, c-C],
C > 10}
{[],
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
{[],
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.
\+ exists {[], se_lt_x :: [a-'Z']}
{[],
se_lt_x :: [count(c)-C]}
This will count the rows in table se_lt_x
{[],
se_lt_x :: [sum(c)-C]}
Sum the values of attribute c in table se_lt_x
{[],
se_lt_x :: [avg(c)-C]}
Calculate the mean of the values of attribute c in table se_lt_x
{[],
se_lt_x :: [max(c)-C]}
Calculate the maximum of the values of attribute c in table se_lt_x
{[],
se_lt_x :: [min(c)-C]}
Calculate the minimum of the values of attribute c in table se_lt_x
{[],
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
{[],
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)
{[],
se_lt_y :: [d-D,f-F],
F < sum(I,
se_lt_x :: [b-J1]
=*=
se_lt_z :: [h-J1, i-I])}
{[],
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)
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)
se_lt_x :: [a-J1, b-B] *== se_lt_y :: [d-J1, e-E]}
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.
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}
{[],
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 = ?)
{[],
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)
{[],
(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
{[],
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
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)
{[],
se_lt_z :: [i-I, j-J],
J \== {null}}
{[],
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
{[],
se_lt_z :: [h-H, i-I1]
=*=
se_lt_z :: [h-H, i-I2],
I1 \== I2}
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?
{[],
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
{[],
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)
{[],
se_lt_z :: [g-G, i-I],
G =~ 'A_'}
The operator =~ means LIKE. If you are using PostgreSQL,
it means ILIKE.
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
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)
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}
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'},
Expressions in WHERE restrictions are supported, for example:
{[],
se_lt_n :: [i-I, j-J, k-K],
J > 10 * (K / I) + 15},
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}
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.
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’.
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.
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.
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.