1.7 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 Simple INSERT

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

1.7.2 Simple INSERT with retrieval of identity of the inserted

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

1.7.3 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 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 where se_lt_x.a = A
• If B is bound and A is unbound, bind A to each of the values in se_lt_x.a where se_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 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 WHERE with arithmetic comparison

{[],
 se_lt_x :: [a-A, c-C],
 C > 10}

1.7.7 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 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 Confirm row does not exist

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

1.7.10 Aggregation - Count

{[],
 se_lt_x :: [count(c)-C]}

This will count the rows in table se_lt_x

1.7.11 Aggregation - Sum

{[],
 se_lt_x :: [sum(c)-C]}

Sum the values of attribute c in table se_lt_x

1.7.12 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 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 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 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 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 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 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 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 Left Outer Join

se_lt_x :: [a-J1, b-B]
  *==
se_lt_y :: [d-J1, e-E]}

1.7.21 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 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 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 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 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 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 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 SELECT with NOT NULL restriction

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

1.7.29 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 Self JOIN

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

1.7.31 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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.