View source with raw comments or as raw
    1/*  Part of SWI-Prolog
    2
    3    Author:        Mike Elston
    4                   Matt Lilley
    5    E-mail:        matt.s.lilley@gmail.com
    6    WWW:           http://www.swi-prolog.org
    7    Copyright (c)  2014-2015, Mike Elston, Matt Lilley
    8    All rights reserved.
    9
   10    Redistribution and use in source and binary forms, with or without
   11    modification, are permitted provided that the following conditions
   12    are met:
   13
   14    1. Redistributions of source code must retain the above copyright
   15       notice, this list of conditions and the following disclaimer.
   16
   17    2. Redistributions in binary form must reproduce the above copyright
   18       notice, this list of conditions and the following disclaimer in
   19       the documentation and/or other materials provided with the
   20       distribution.
   21
   22    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   23    "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   24    LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
   25    FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
   26    COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
   27    INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
   28    BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
   29    LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
   30    CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
   31    LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
   32    ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
   33    POSSIBILITY OF SUCH DAMAGE.
   34*/
   35
   36/*  PostgreSQL is a trademark of the PostgreSQL Global Development Group.
   37    Microsoft, SQL Server, and Windows are either registered trademarks or
   38    trademarks of Microsoft Corporation in the United States and/or other
   39    countries. SQLite is a registered trademark of Hipp, Wyrick & Company,
   40    Inc in the United States. All other trademarks or registered trademarks
   41    are the property of their respective owners.
   42*/
   43
   44:-module(cql,
   45         [
   46          cql_execute/1,
   47          cql_error/3,
   48          cql_data_type/10,
   49          cql_get_module_default_schema/2,
   50          cql_goal_expansion/3,
   51          cql_event_notification_table/2,
   52          cql_history_attribute/3,
   53          cql_identity/3,
   54          cql_odbc_select_statement/4,
   55          cql_odbc_state_change_statement/7,
   56          cql_portray/2,
   57          cql_var_check/1,
   58          cql_post_state_change_select_sql/4,
   59          cql_pre_state_change_select_sql/7,
   60          cql_runtime/7,
   61          cql_update_history_hook/14,
   62          cql_set_module_default_schema/1,
   63          cql_show/2,
   64          cql_state_change_statistics_sql/8,
   65          cql_statement_location/2,
   66          cql_temporary_column_name/4,
   67          cql_log/4,
   68          cql_normalize_name/3,
   69          cql_sql_clause/3,
   70          default_schema/1,
   71          odbc_execute_with_statistics/4,
   72          cql_access_token_to_user_id/2,
   73          dbms/2,
   74          odbc_data_type/4,
   75          attribute_domain/4,
   76          database_identity/3,
   77          database_key/5,
   78          primary_key_column_name/3,
   79          statistic_monitored_attribute/3,
   80          domain_database_data_type/2,
   81          database_attribute/8,
   82          database_domain/2,
   83          routine_return_type/3,
   84          database_constraint/4,
   85          in_line_format/4,
   86          row_count/2,
   87          sql_gripe/3,
   88          op(400, xfy, (::)),
   89          op(900, fy,  exists),
   90          op(750, yfx, *==),
   91          op(750, yfx, =*=),
   92          op(750, yfx, ==*),
   93          op(740, yfx, on),
   94          op(700, xfx, =~),
   95          op(700, xfx, \=~),
   96          op(200, fy, #),
   97          op(920, fy, ???),
   98          op(920, fy, ??),
   99          op(920, fy, ?)
  100	  ]).  101
  102:-license(swipl).  103
  104:-use_module(library(chr)).  105:-use_module(library(dcg/basics)).  106:-use_module(library(cql/sql_parser)).  107:-use_module(library(cql/sql_tokenizer)).  108:-use_module(library(cql/sql_write)).  109:-use_module(library(cql/sql_keywords)).  110:-use_module(library(cql/cql_database)).  111:-reexport(cql_database, [register_database_connection_details/2,
  112                          cql_transaction/3]).

CQL - Constraint Query Language

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, runtime mode is deprecated.

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

Warnings

Comparisons with NULL

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

A={null}, {[A], foo :: [a-A]}

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

See the section Removing null comparisions for the dealing with the common requirement to ignore comparisons with null.

Avoid setof/3 and bagof/3

It is generally not a good idea to wrap CQL inside a setof/3 or a 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 findall/3 followed by sort/2. Note that sort/2 (like setof/3) removes duplicates. If you don't want to remove duplicates, use msort/2.

Retrieved nulls have special logic to handle outer joins

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

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

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

x :: [a-A] *== y :: [a-A]

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

Getting Started Quickly

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

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]).

Comparisons can be done in-line e.g.

[a-'ELSTON_M']

or with the == operator e.g.

[a-A], A == 'ELSTON_M'.

The single = operator means unify, not compare. Use = for unification, not comparison

FIXME: Unification is deprecated.

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

Debugging

You can debug CQL using the meta-predicates ?/1, ??/2 and ???/3:

???{[], se_lt_x :: [a-A, b-_], A == 'ELSTON_M'}.
?/1
Display a summary form of the generated SQL before and after the goal is called.
[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)
??/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
[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)
???/1
Display simplified SQL before the goal is called and display the results afterwards
[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)

Prolog Variables

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

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

which generates the following SQL

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 = ?

Note how all the variables referenced in the query are retrieved in the SELECT. This is done to make the query 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: WHERE variables and JOIN variables are not bound in aggregation selections

FIXME: Is this still the case?

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).
'ELSTON_M'-_G375971-99450
true ;

Special Attributes

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

  1. generation_ Set to 0 on INSERT and incremented by 1 on each update
  2. inserted_ Set to the current time at the time of the INSERT transaction
  3. inserted_by_ Set to the user ID corresponding to the access token supplied to the transaction
  4. updated_ Set to the current time at the time of the UPDATE transaction. Note that updated_ is also set by an INSERT
  5. 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
  6. transaction_id_ Set to the transaction ID

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

Examples

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

Simple INSERT

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

Simple INSERT with retrieval of identity of the inserted

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

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.

Simple SELECT

{[],
 se_lt_x :: [a-A, b-B]}

This query will either:

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.

WHERE with arithmetic comparison

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

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

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.

Confirm row does not exist

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

Aggregation - Count

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

This will count the rows in table se_lt_x

Aggregation - Sum

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

Sum the values of attribute c in table se_lt_x

Aggregation - Average

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

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

Maximum Value

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

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

Minimum Value

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

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

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

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)

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])}

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)

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)

Left Outer Join

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

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.

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}

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 = ?)

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)

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

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

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)

SELECT with NOT NULL restriction

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

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

Self JOIN

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

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?

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

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)

SELECT with LIKE

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

The operator =~ means LIKE. If you are using PostgreSQL, it means ILIKE.

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

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)

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}

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'},

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},

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}

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.

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

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.

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.

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.

Hooks

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

Generated Code Hooks

Data Representation Hooks

Application Integration

Inline values

cql: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.

Schema

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

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

Event Processing and History

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

The hook predicates are:

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

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

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. */

 1288:-chr_option(line_numbers, on). 1289:-chr_option(check_guard_bindings, error). 1290:-chr_option(debug, off). 1291:-chr_option(optimize, full). 1292:-chr_option(guard_simplification, off). % Added to stop trail overflowing
 1293
 1294:-chr_type list(T) ---> [] ; [T|list(T)]. 1295
 1296:-chr_type 'AggregationOperator' ---> count ; max ; min ; avg ; sum. 1297:-chr_type 'AggregationVariable' == any. 1298:-chr_type 'ApplicationValue' == any. 1299:-chr_type 'DebugMode' ---> explicit. 1300:-chr_type 'DictinctionType' ---> no_distinction ; distinct_on_specified_attributes ; distinct_on_all_select_attributes. 1301:-chr_type 'Dsn' == any. 1302:-chr_type 'Attribute' ---> attribute('Schema', 'TableAlias', 'AttributeName'). 1303:-chr_type 'AttributeName' == any. 1304:-chr_type 'AttributeNameValuePair' ---> 'AttributeName'-'ApplicationValue'. 1305:-chr_type 'BooleanOperator' ---> and ; or. 1306:-chr_type 'ComparisonOperator' ---> < ; =< ; == ; \== ; >= ; > ; (=~) ; (=\=) ; (=:=). 1307:-chr_type 'CompilationInstruction' ---> if_var('Variable') ; if_not_var('Variable') ; if_null('Variable') ; if_not_null('Variable') ; list('Variable') ; empty('Variable') ; not_empty('Variable') ; compile ; and('CompilationInstruction', 'CompilationInstruction'). 1308:-chr_type 'CompileMode' ---> runtime ; compiletime. 1309:-chr_type 'ConjunctionGoal' == any. 1310:-chr_type 'ConjunctionVariable' == any. 1311:-chr_type 'Connection' == any. 1312:-chr_type 'Constraints' == any. 1313:-chr_type 'ClockTime' == any. 1314:-chr_type 'CpuTime' == any. 1315:-chr_type 'Cql' == any. 1316:-chr_type 'Disposition' ---> top ; where ; having ; join. 1317:-chr_type 'EqualityRestrictionVariableUsed' ---> equality_restriction_variable_used. 1318:-chr_type 'Expression' == any. 1319:-chr_type 'ExternalVariable' == any. 1320:-chr_type 'FileName' == any. 1321:-chr_type 'Format' == any. 1322:-chr_type 'FormatArg' == any. 1323:-chr_type 'Goal' == any. 1324:-chr_type 'Having' == any. 1325:-chr_type 'Identity' == int. 1326:-chr_type 'Inferences' == any. 1327:-chr_type 'InputVariable' == 'Variable'. 1328:-chr_type 'Join' == any. 1329:-chr_type 'JoinTreeNode' == any. 1330:-chr_type 'JoinType' ---> 'INNER JOIN' ; 'LEFT OUTER JOIN' ; 'RIGHT OUTER JOIN'. 1331:-chr_type 'Keep' ---> 1. 1332:-chr_type 'LineNumber' == int. 1333:-chr_type 'N' == int. 1334:-chr_type 'OdbcCachingOption' ---> do_not_cache_odbc_statement ; cache_odbc_statement. 1335:-chr_type 'OdbcDataType' ---> varchar(int) ; decimal(int, int) ; timestamp ; integer ; bit. 1336:-chr_type 'LogicalType' ---> varchar ; decimal ; timestamp ; integer ; boolean. 1337:-chr_type 'OdbcInput' == any. 1338:-chr_type 'OdbcOutput' == any. 1339:-chr_type 'OdbcParameter' ---> odbc_parameter('Schema', 'TableName', 'AttributeName', 'ApplicationValue', 'OdbcParameterUse', 'OdbcDataType')   % OdbcDataType unbound if no override required
 1340                              ; odbc_explicit_type_parameter('OdbcDataType', 'ApplicationValue', 'OdbcParameterUse'). 1341:-chr_type 'OdbcParameterUse' ---> insert_value ; update_value ; evaluated_update_attribute ; evaluated_update_parameter ; where_value ; top_value. 1342:-chr_type 'On' == any. 1343:-chr_type 'OrderBy' ---> +('Variable') ; -('Variable'). 1344:-chr_type 'Output' ---> output('Schema', 'TableName', 'AttributeName', 'Variable') ;
 1345                         ignore_output ;
 1346                         count('Variable') ;
 1347                         avg('Variable') ;  % PostgreSQL
 1348                         selection_constant('Schema', 'TableName', 'AttributeName', 'Variable'). 1349:-chr_type 'Phase' ---> initial ; distinct ; top ; select_attributes ; from ; where ; group_by ; having ; order_by ; union ; limit. 1350:-chr_type 'PreparedStatement' == any. 1351:-chr_type 'PrimaryKeyAttributeName' == 'AttributeName'. 1352:-chr_type 'PrimaryKeyValue' == any. 1353:-chr_type 'QueryId' == any. 1354:-chr_type 'QueryLevel' ---> top_level_query ; sub_query. 1355:-chr_type 'Reason' == any. 1356:-chr_type 'ResultSpec' == any. 1357:-chr_type 'Resolved' ---> resolved. 1358:-chr_type 'RestrictionExpression' == any. 1359:-chr_type 'RestrictionType' ---> where ; having ; join. 1360:-chr_type 'RestrictionTree' ---> true ;
 1361                                  comparison('ApplicationValue', 'ComparisonOperator', 'ApplicationValue') ;
 1362                                  sub_query('SubQueryType', list('SqlToken'), 'Tail', list('OdbcParameter')) ;
 1363                                  and('RestrictionTree', 'RestrictionTree') ;
 1364                                  or('RestrictionTree', 'RestrictionTree'). 1365:-chr_type 'Row' == any. 1366:-chr_type 'StateChangeType' ---> insert ; update ; delete. 1367:-chr_type 'QueryType' ---> insert ; update ; delete ; select. 1368:-chr_type 'Schema' == any. 1369:-chr_type 'SelectAttribute' ---> select_attribute('SelectBindingType', 'Schema', 'TableName', 'TableAlias', 'AttributeName'). 1370:-chr_type 'SelectAttributeWithSize' ---> 'Size'-'SelectAttributeInfo'. 1371:-chr_type 'SelectAttributeInfo' ---> select_info('CompilationInstruction', list('SqlToken'), 'Tail', 'Output'). 1372:-chr_type 'SelectAttributeVariableUsed' ---> select_attribute_variable_used. 1373:-chr_type 'SelectBindingType' ---> plain ; aggregation('AggregationOperator'). 1374:-chr_type 'SelectionType' ---> aggregation_selection ; non_aggregation_selection. 1375:-chr_type 'Side' ---> lhs ; rhs. 1376:-chr_type 'Size' == any. 1377:-chr_type 'SqlToken' == any. 1378:-chr_type 'SqlComparisonOperator' ---> < ; <= ; = ; <> ; >= ; > . 1379:-chr_type 'SubQueryType' ---> exists ; \+ . 1380:-chr_type 'Tail' == any. 1381:-chr_type 'TableAlias' == any. 1382:-chr_type 'TableName' == any. 1383:-chr_type 'Variable' == any. 1384:-chr_type 'VariablePair' ---> 'Variable'-'Variable'. 1385:-chr_type 'When' ---> pre_state_change ; post_state_change. 1386
 1387
 1388
 1389:-chr_constraint absence_of_where_restriction_is_deliberate. 1390:-chr_constraint add_on(-'Join', ?'On'). 1391:-chr_constraint aggregation_sub_query(-'QueryId', ?'TableName', ?'TableAlias', ?list('SqlToken'), ?'Tail', ?list('ApplicationValue')). 1392:-chr_constraint aggregation_variable(-'QueryId', +'AggregationOperator', ?'AggregationVariable'). 1393:-chr_constraint attribute_binding(-'QueryId', ?'Attribute', ?'ApplicationValue'). 1394:-chr_constraint attribute_for_group_by(-'QueryId', ?'TableAlias', +'AttributeName', ?'Variable'). 1395:-chr_constraint attribute_for_order_by(-'QueryId', ?'TableAlias', +'AttributeName', ?'Variable'). 1396:-chr_constraint attribute_to_check(+'Schema', +'TableName', ?'AttributeNameValuePair'). 1397:-chr_constraint attributes_to_check(-'QueryId', +'Schema', +'TableName', ?list('AttributeNameValuePair')). 1398:-chr_constraint call_history_hook(-'QueryId', +'Connection'). 1399:-chr_constraint call_row_change_hooks(-'QueryId', +'Connection'). 1400:-chr_constraint check_for_orphan_distincts. 1401:-chr_constraint check_for_orphan_group_bys. 1402:-chr_constraint check_for_orphan_order_bys. 1403:-chr_constraint check_for_orphan_select_attributes_in_aggregations. 1404:-chr_constraint check_for_orphan_select_variables_in_updates. 1405:-chr_constraint check_for_top_without_order_by. 1406:-chr_constraint check_for_unjoined_tables. 1407:-chr_constraint check_query. 1408:-chr_constraint cleanup_compile. 1409:-chr_constraint cleanup_cql_post_state_change_select_sql(-'QueryId'). 1410:-chr_constraint collect_indices(-'QueryId'). 1411:-chr_constraint collect_runtime_constraints(?'Constraints'). 1412:-chr_constraint collect_select_attributes(-'QueryId', +list('SelectAttributeWithSize')). 1413:-chr_constraint comparison(-'QueryId', ?'ApplicationValue', +'ComparisonOperator', ?'ApplicationValue'). 1414:-chr_constraint compile_mode(+'CompileMode'). 1415:-chr_constraint conjunction_constraints(?'Constraints'). 1416:-chr_constraint conjunction_goal(?'ConjunctionGoal'). 1417:-chr_constraint conjunction_variable(-'QueryId', ?'ExternalVariable', ?'ConjunctionVariable'). 1418:-chr_constraint copy_of_from(-'QueryId', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1419:-chr_constraint find_copy_of_from(-'QueryId', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1420:-chr_constraint cql2_variable(-'QueryId', -'Variable', ?'RestrictionTree'). 1421:-chr_constraint cql_execute(+'OdbcCachingOption'). 1422:-chr_constraint cql_fully_compiled. 1423:-chr_constraint cql_identity(-'QueryId', +'Schema', ?'Identity'). 1424:-chr_constraint cql_odbc_select_statement(+'Schema', +'SqlToken', ?list('OdbcParameter'), ?list('Output')). 1425:-chr_constraint cql_odbc_state_change_statement(-'QueryId', +'StateChangeType', +'Schema', +'TableName', +'SqlToken', ?list('OdbcParameter'), ?list('Output')). 1426:-chr_constraint cql_post_state_change_select_sql(-'QueryId', +list('AttributeName'), +'OdbcDataType', +'SqlToken'). 1427:-chr_constraint cql_pre_state_change_select_sql(-'QueryId', +'Schema', +'TableName', +'AttributeName', +'SqlToken', +list('AttributeName'), +list('OdbcParameter')). 1428:-chr_constraint cql_state_change_statistics_sql(-'QueryId', +'Schema', +'TableName',  +'StateChangeType', +'SqlToken', +list('AttributeName'), ?list('OdbcParameter'), ?list('OdbcParameter')). 1429:-chr_constraint cql_statement_location(+'FileName', +'LineNumber'). 1430:-chr_constraint create_cql_pre_state_change_select_sql(-'QueryId', +'StateChangeType', +list('SqlToken'), +'TableName', ?list('OdbcParameter')). 1431:-chr_constraint create_cql_state_change_statistics_sql(-'QueryId', +'StateChangeType', +list('SqlToken'), +'TableName', ?list('OdbcParameter')). 1432:-chr_constraint create_in_line_joins. 1433:-chr_constraint create_join_points. 1434:-chr_constraint create_restrictions. 1435:-chr_constraint create_select_bindings. 1436:-chr_constraint debug_after(+'Reason', ?'ResultSpec'). 1437:-chr_constraint debug_before(+'Format', +'Schema', +'FormatArg'). 1438:-chr_constraint debug_statistics(+'CpuTime', +'ClockTime', +'Inferences'). 1439:-chr_constraint delete_row(-'QueryId', +'TableName', ?'TableAlias'). 1440:-chr_constraint determine_select_distinction(-'QueryId'). 1441:-chr_constraint determine_select_distinctions. 1442:-chr_constraint determine_selection_type. 1443:-chr_constraint dictionary_addendum(-'QueryId', ?'ExternalVariable', ?'ConjunctionVariable'). 1444:-chr_constraint dictionary_lookup(-'QueryId', ?'ExternalVariable', ?'ConjunctionVariable'). 1445:-chr_constraint distinct(-'QueryId', -'Variable'). 1446:-chr_constraint distincts(-'QueryId', ?list('Variable')). 1447:-chr_constraint equality_restriction_variable(?'ApplicationValue', ?'EqualityRestrictionVariableUsed'). 1448:-chr_constraint event(-'QueryId'). 1449:-chr_constraint expression_where_restriction_variable(?'Variable'). 1450:-chr_constraint fully_compile. 1451:-chr_constraint generate_sub_query_sql. 1452:-chr_constraint get_conjunction_constraints(?'Constraints'). 1453:-chr_constraint group_by(-'QueryId', -'Variable'). 1454:-chr_constraint group_bys(-'QueryId', ?list('Variable')). 1455:-chr_constraint identify_insert_row(+'StateChangeType', -'QueryId', +'Schema', +'TableName', +'Connection', ?'Identity'). 1456:-chr_constraint identify_post_state_change_values(-'QueryId', +'Connection'). 1457:-chr_constraint identify_pre_state_change_values(-'QueryId', +'StateChangeType', +'Connection'). 1458:-chr_constraint ignore_if_null(?'Variable', ?'Variable'). 1459:-chr_constraint implicit_join(-'QueryId', +'TableAlias', -'QueryId'). % PostgreSQL only
 1460:-chr_constraint implicit_join_link(-'QueryId', -'QueryId'). % PostgreSQL only
 1461:-chr_constraint implicit_join_sql(-'QueryId', ?list('SqlToken'), ?'Tail'). % PostgreSQL only
 1462:-chr_constraint fetch_implicit_join_sql(-'QueryId', ?list('SqlToken'), ?'Tail'). % PostgreSQL only
 1463:-chr_constraint in_line_format(-'QueryId', +'Format', ?list('FormatArg'), ?'ApplicationValue'). 1464:-chr_constraint include_select_attribute(-'QueryId', ?'CompilationInstruction', +'Size', +list('SqlToken'), ?'Tail', ?'Output'). 1465:-chr_constraint insert(-'QueryId', +'Schema', +'TableName', +list('AttributeNameValuePair')). 1466:-chr_constraint instantiate_table_aliases. 1467:-chr_constraint join(-'QueryId', -'Join', -'Join', +'JoinType', -'Join'). 1468:-chr_constraint join_alias(-'Join', +'Side', ?'TableAlias'). 1469:-chr_constraint join_leaf(-'Join', ?'TableAlias'). 1470:-chr_constraint join_on(?'TableAlias', +'AttributeName', ?'TableAlias', +'AttributeName'). 1471:-chr_constraint join_pointer(-'QueryId', -'Join'). 1472:-chr_constraint join_tree_node(-'QueryId', -'Join', +'JoinTreeNode'). 1473:-chr_constraint join_tree_nodes(-'QueryId', +list('JoinTreeNode')). 1474:-chr_constraint join_variable(?'Variable'). 1475:-chr_constraint limit(-'QueryId', +'Schema', +'N'). 1476:-chr_constraint log_select(+'SqlToken', +list('OdbcInput')). 1477:-chr_constraint log_state_change(+'SqlToken', +'StateChangeType', +list('OdbcInput')). 1478:-chr_constraint next_group_by_attribute_needs_comma(-'QueryId'). 1479:-chr_constraint next_in_list_value_needs_comma(-'QueryId'). 1480:-chr_constraint next_order_by_attribute_needs_comma(-'QueryId'). 1481:-chr_constraint no_debug. 1482:-chr_constraint no_sql_statement_generated. 1483:-chr_constraint no_state_change_actions(-'QueryId'). 1484:-chr_constraint no_where_restriction(+'StateChangeType'). 1485:-chr_constraint not_a_singleton(+'Variable'). 1486:-chr_constraint nolock(-'QueryId', ?'TableAlias'). 1487:-chr_constraint number_of_rows_affected(-'QueryId', +'Connection', ?'N'). 1488
 1489:-chr_constraint odbc_select_disjunction(?'Goal'). 1490%:-chr_meta_predicate(odbc_select_disjunction(0)).
 1491
 1492:-chr_constraint odbc_select_statement(+'Schema', +'SqlToken', ?list('OdbcParameter'), ?list('Output')). 1493:-chr_constraint on(-'Join', ?'Resolved', ?'On'). 1494:-chr_constraint order_bys(-'QueryId', ?list('OrderBy')). 1495:-chr_constraint original_cql(?'Cql'). 1496:-chr_constraint original_human_query(?'Cql'). 1497:-chr_constraint outer_side_join(-'Join'). 1498:-chr_constraint phase(-'QueryId', +'Phase'). 1499:-chr_constraint post_execute_cleanup. 1500:-chr_constraint post_state_change_select_statement(-'QueryId', +list('AttributeName'), +'OdbcDataType', +'PreparedStatement'). 1501:-chr_constraint postgres_identity(-'QueryId', ?'Identity'). 1502:-chr_constraint prepare_odbc_statements. 1503:-chr_constraint prior_to_execution. 1504:-chr_constraint query(-'QueryId', +'Schema', +'QueryLevel'). 1505:-chr_constraint query_table_alias(-'QueryId', +'Schema', +'TableName', ?'TableAlias'). 1506:-chr_constraint query_type(-'QueryId', +'QueryType'). 1507:-chr_constraint remove_query(-'QueryId', -'QueryId'). 1508:-chr_constraint resolve_join_points(-'Join', ?'On', ?'On'). 1509:-chr_constraint resolve_join_points. 1510:-chr_constraint restriction_leaf(-'QueryId', +'Disposition', ?'RestrictionTree'). 1511:-chr_constraint restriction_tree(-'QueryId', +'Disposition', ?'RestrictionTree'). 1512:-chr_constraint row_count(-'QueryId', ?'N'). 1513:-chr_constraint runtime_constraints(?'Constraints'). 1514:-chr_constraint search_for_join_aliases(-'Join', +'Side', -'Join'). 1515:-chr_constraint select_attribute(-'QueryId', ?'SelectAttribute', ?'Keep', ?'SelectAttributeVariableUsed', ?'Variable'). 1516:-chr_constraint select_attribute_for_disjunction_comparison(-'QueryId', ?'SelectAttribute'). 1517:-chr_constraint select_attribute_written(-'QueryId'). 1518:-chr_constraint select_attributes_for_disjunction_comparison(-'QueryId', ?list('SelectAttribute')). 1519:-chr_constraint select_binding(-'QueryId', ?'SelectBindingType', ?'Attribute', ?'ApplicationValue'). 1520:-chr_constraint select_distinction(-'QueryId', +'DictinctionType'). 1521:-chr_constraint select_for_insert_variable(-'QueryId', ?'Variable', +'TableName'). 1522:-chr_constraint select_for_insert_variables(?list('Variable'), +'TableName'). 1523:-chr_constraint selection_type(-'QueryId', +'SelectionType'). 1524:-chr_constraint show_debug(+'DebugMode'). 1525:-chr_constraint simplify. 1526:-chr_constraint solve. 1527:-chr_constraint sql_not(-'QueryId', -'QueryId'). 1528:-chr_constraint sql_statement(-'QueryId', +list('SqlToken'), ?'Tail', +list('SqlToken'), ?'Tail', +list('SqlToken'), ?'Tail', ?list('OdbcParameter'), ?list('OdbcParameter'), ?list('Output')). 1529:-chr_constraint state_change_query(-'QueryId', +'StateChangeType', +'Schema', +'TableName'). 1530:-chr_constraint state_change_value(-'QueryId', +'StateChangeType', +'When', +'Schema', +'TableName', +'PrimaryKeyAttributeName', +'PrimaryKeyValue', +'AttributeName', +'OdbcOutput'). 1531:-chr_constraint store_equality_restriction_variables(?list('Variable')). 1532:-chr_constraint store_ignore_if_null_variables(?list('VariablePair')). 1533:-chr_constraint sub_query(-'QueryId', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1534:-chr_constraint sub_query_join_variable(?'Variable'). 1535:-chr_constraint sub_query_restriction(-'QueryId', +'SubQueryType', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1536:-chr_constraint sub_query_select(-'QueryId'). 1537:-chr_constraint referenced_table(+'TableName'). 1538:-chr_constraint referenced_tables(?list('TableName')). 1539:-chr_constraint representative_attribute(?'Expression', +'Schema', -'TableName', -'AttributeName'). 1540:-chr_constraint runtime_instantiation_check(-'QueryId', -'Variable'). 1541:-chr_constraint tables_to_remove(+'Schema', +list('Identity')). 1542:-chr_constraint temporary_table(+'Schema', +'Identity'). 1543:-chr_constraint temporary_tables(+'Schema', +list('Identity')). 1544:-chr_constraint top(-'QueryId', +'Schema', +'N'). 1545:-chr_constraint unify(?'Variable', ?'Variable'). 1546:-chr_constraint unify_ignore_if_null_variables. 1547:-chr_constraint union_outputs(-'QueryId', ?list('Output'), ?list('Variable')). 1548:-chr_constraint update(-'QueryId', +'Schema', +'TableName', ?'TableAlias', ?list('AttributeNameValuePair')). 1549:-chr_constraint update_table_alias(-'QueryId', +'Schema', -'Join', ?'TableAlias'). 1550:-chr_constraint update_table_key(-'QueryId', +'Schema', ?list('AttributeNameValuePair')). 1551:-chr_constraint updated_row(-'QueryId', +'StateChangeType', +'When', +'Schema', +'TableName', +'PrimaryKeyAttributeName', +'PrimaryKeyValue', +list('AttributeNameValuePair')). 1552:-chr_constraint updated_row_primary_key(-'QueryId', +'StateChangeType', +'Schema', +'TableName', +'PrimaryKeyAttributeName', +'PrimaryKeyValue'). 1553:-chr_constraint updated_rows(-'QueryId', +'StateChangeType', +'When', +'Schema', +'TableName', +'AttributeName', +list('AttributeName'), +list('Row')). 1554:-chr_constraint variables_to_attributes(?'Expression', ?'Expression'). 1555:-chr_constraint where_restriction_variable(?'Variable'). 1556:-chr_constraint write_expression(-'QueryId', +'Schema', +'TableName', +'AttributeName', ?'TableAlias', ?'Expression'). 1557:-chr_constraint write_group_by_attribute(-'QueryId', ?list('SqlToken'), ?'Tail'). 1558:-chr_constraint write_group_bys(-'QueryId'). 1559:-chr_constraint write_in_list(-'QueryId', +'Disposition', +'Schema', +'TableName', +'AttributeName', +list('ApplicationValue')). 1560:-chr_constraint write_insert_attribute_name(-'QueryId', +'AttributeName'). 1561:-chr_constraint write_insert_attribute_names(-'QueryId', +list('AttributeNameValuePair')). 1562:-chr_constraint write_insert_value(-'QueryId', +'Schema', +'TableName', +'AttributeName', ?'ApplicationValue'). 1563:-chr_constraint write_insert_values(-'QueryId', +'Schema', +'TableName', ?list('AttributeNameValuePair')). 1564:-chr_constraint write_join(-'QueryId', -'Join'). 1565:-chr_constraint write_join_ons(-'QueryId', ?'On'). 1566:-chr_constraint write_limit. 1567:-chr_constraint write_lock_hint(-'QueryId', +'Schema', ?'TableAlias'). 1568:-chr_constraint write_order_by(-'QueryId', ?'OrderBy'). 1569:-chr_constraint write_order_by_attribute(-'QueryId', ?list('SqlToken'), ?'Tail'). 1570:-chr_constraint write_order_bys(-'QueryId', ?list('OrderBy')). 1571:-chr_constraint write_query_sql. 1572:-chr_constraint write_restriction(-'QueryId', ?'CompilationInstruction', +'Disposition', ?'ApplicationValue', +'ComparisonOperator', ?'ApplicationValue'). 1573:-chr_constraint write_restriction_1(-'QueryId', ?'CompilationInstruction', +'Disposition', +'OdbcDataType', ?'OdbcDataType', +'Schema', +'TableName', +'AttributeName', ?'RestrictionExpression', +'ComparisonOperator', ?'RestrictionExpression'). 1574:-chr_constraint write_restriction_expression(-'QueryId', ?'CompilationInstruction', +'Disposition', ?'OdbcDataType', +'OdbcDataType', +'Schema', +'TableName', +'AttributeName', ?'RestrictionExpression'). 1575:-chr_constraint write_restriction_tree(-'QueryId', +'Disposition', ?'RestrictionTree'). 1576:-chr_constraint write_select_attribute(-'QueryId', ?'CompilationInstruction', ?list('SqlToken'), ?'Tail', ?'Output'). 1577:-chr_constraint write_select_attribute_1(-'QueryId', ?'CompilationInstruction', ?list('SqlToken'), ?'Tail', ?'Output'). 1578:-chr_constraint write_select_attributes(-'QueryId'). 1579:-chr_constraint write_sql(-'QueryId', ?'CompilationInstruction',  +'Disposition', +list('SqlToken'), ?'Tail', ?list('OdbcParameter'), ?list('Output')). 1580:-chr_constraint write_update_attribute(-'QueryId', ?'TableAlias', +'AttributeName', ?'ApplicationValue'). 1581:-chr_constraint write_update_attributes(-'QueryId', ?'TableAlias', ?list('AttributeNameValuePair')). 1582
 1583
 1584:-op(400, xfy, (::)).            % CQL
 1585:-op(900, fy,  exists).          % CQL
 1586:-op(750, yfx, *==).             % CQL
 1587:-op(750, yfx, =*=).             % CQL
 1588:-op(750, yfx, ==*).             % CQL
 1589:-op(740, yfx, on).              % CQL
 1590:-op(700, xfx, =~).              % CQL (LIKE)
 1591:-op(700, xfx, \=~).             % CQL (NOT LIKE)
 1592:-op(200, fy, #).                % CQL (nolock)
 1593:-op(920, fy, ???).              % Debugging
 1594:-op(920, fy, ??).               % Debugging
 1595:-op(920, fy, ?).                % Debugging
 cql_set_module_default_schema(+Schema)
Set the Schema for a module
 1603:-dynamic
 1604        module_default_schema/2. 1605
 1606cql_set_module_default_schema(Schema) :-                       % +
 1607        prolog_load_context(module, Module),
 1608        set_module_default_schema(Module, Schema).
 1609
 1610
 1611
 1612set_module_default_schema(Module,      % +
 1613                          Schema) :-   % +
 1614        retractall(module_default_schema(Module, _)),
 1615        assert(module_default_schema(Module, Schema)).
 cql_get_module_default_schema(+Module, ?ModuleDefaultSchema)
 1620cql_get_module_default_schema(Module,                          % +
 1621                              ModuleDefaultSchema) :-          % ?
 1622
 1623        ( module_default_schema(Module, Schema) ->
 1624            ModuleDefaultSchema = Schema
 1625        ;
 1626            default_schema(ModuleDefaultSchema)
 1627        ).
 1628
 1629% This lets me control the compiletime checks via an environment variable
 1630:-dynamic(do_cql_compiletime_checks/1). 1631do_cql_compiletime_checks:-
 1632        ( do_cql_compiletime_checks(Status)->
 1633            Status == true
 1634        ; getenv('CQL_COMPILETIME_CHECKS', Atom)->
 1635            assert(do_cql_compiletime_checks(Atom)),
 1636            Atom == true
 1637        ; otherwise->
 1638            assert(do_cql_compiletime_checks(false)),
 1639            fail
 1640        ).
 1641
 1642cql_compiletime_checks(Schema, Goals):-
 1643        forall(cql_sql_clause(Goals, SQL, Parameters),
 1644               check_decompilation(Schema, SQL, Parameters)).
 1645
 1646check_decompilation(Schema, HalfCompiledSql, HalfCompiledOdbcParameters):-
 1647        dbms(Schema, DBMS),
 1648        ( fully_compile_sql(HalfCompiledSql, HalfCompiledOdbcParameters, [], Sql, OdbcParameters, _),
 1649          atom_codes(Sql, SqlCodes),
 1650          sql_tokens(Tokens, SqlCodes, []),
 1651          findall(test,
 1652                    ( member(odbc_parameter(_, _, _, _, _, _), OdbcParameters)
 1653                    ; member(odbc_explicit_type_parameter(_, _, _), OdbcParameters)
 1654                    ),
 1655                  Bindings),
 1656          sql_parse(action(Expression, _Types), _, [dbms(DBMS)], Tokens),
 1657          with_output_to(atom(Atom), sql_write(current_output, Expression, [dbms(DBMS), parameter_bindings(Bindings), suppress_collations]))->
 1658            % Make sure that the COLLATEs are all removed
 1659            \+sub_atom(Atom, _, _, _, 'COLLATE')
 1660        ; otherwise->
 1661            prolog_load_context(source, FileName),
 1662            prolog_load_context(term_position, TermPosition),
 1663            stream_position_data(line_count, TermPosition, LineNumber),
 1664            format(user_error, 'Could not decompile generated CQL: ~w~n~q~n', [FileName:LineNumber, HalfCompiledSql])
 1665        ).
 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

 1679cql_goal_expansion(Schema, Cql, GoalExpansion) :-
 1680        % {} is also used by clp(r,q) so make sure the CQL looks like CQL
 1681        nonvar(Cql),
 1682        Cql = (Arg, _),
 1683        ( is_list(Arg)
 1684        ; nonvar(Arg),
 1685          Arg = compile_at_runtime(_)
 1686        ),
 1687
 1688        \+current_prolog_flag(xref, true), % Prevent expansion when used by pldoc to prevent spurious CQL compile errors
 1689        atom(Schema),
 1690        ( cql_goal_expansion_1(Schema, Cql, GoalExpansion_) ->
 1691            GoalExpansion = GoalExpansion_
 1692        ;
 1693            throw(format('Cannot expand CQL: Schema = ~w, Cql=(~w)', [Schema, Cql]))
 1694        ),
 1695        ( do_cql_compiletime_checks ->
 1696            setup_call_cleanup(assert(skip_cql_instantiation_check),
 1697                               cql_compiletime_checks(Schema, GoalExpansion),
 1698                               retract(skip_cql_instantiation_check))
 1699        ; otherwise->
 1700            true
 1701        ).
 1702
 1703
 1704cql_sql_clause(cql_odbc_state_change_statement(_, _, _, _, SQL, Parameters, _), SQL, Parameters).
 1705cql_sql_clause(cql_pre_state_change_select_sql(_, _, _, _, SQL, _, Parameters), SQL, Parameters).
 1706cql_sql_clause(cql_post_state_change_select_sql(_, _, Parameter, SQL), SQL, [odbc_explicit_type_parameter(Parameter, _, where_value)]).
 1707cql_sql_clause(cql_odbc_select_statement(_, SQL, Parameters, _), SQL, Parameters).
 1708cql_sql_clause((A, B), SQL, Parameters):-
 1709        ( cql_sql_clause(A, SQL, Parameters)
 1710        ; cql_sql_clause(B, SQL, Parameters)
 1711        ).
 1712
 1713:-multifile(cql_dependency_hook/2). 1714:-multifile(cql_generated_sql_hook/3). 1715cql_goal_expansion_1(Schema, (CompilationDirective, CqlA), GoalExpansion) :-
 1716        ( prolog_load_context(source, FileName),
 1717          prolog_load_context(term_position, TermPosition),
 1718          stream_position_data(line_count, TermPosition, LineNumber)->
 1719            DynamicallyCreatedCql = boolean(false)
 1720
 1721        ; otherwise ->
 1722            DynamicallyCreatedCql = boolean(true),
 1723            FileName = '<Dynamically created CQL - no source file>',
 1724            LineNumber = 0
 1725        ),
 1726
 1727        ( is_list(CompilationDirective),
 1728          EqualityRestrictionVariables = CompilationDirective,
 1729          forall(member(EqualityRestrictionVariable, EqualityRestrictionVariables), var(EqualityRestrictionVariable)) ->
 1730            CqlB = (store_equality_restriction_variables(EqualityRestrictionVariables),
 1731                    original_cql(CqlA),
 1732                    cql_statement_location(FileName, LineNumber),
 1733                    CqlA),
 1734            translate_to_constraints(Schema, CqlB, InitialConstraints),
 1735            call(InitialConstraints),
 1736            compile_mode(compiletime),
 1737            fully_compile,
 1738            runtime_constraints(cql_execute(cache_odbc_statement)),
 1739            collect_runtime_constraints(GoalExpansion),
 1740            referenced_tables(ReferencedTables),
 1741            ( ReferencedTables \== [],
 1742              DynamicallyCreatedCql == boolean(false) ->
 1743                sort(ReferencedTables, ReferencedTableSet),  % Remove duplicates
 1744                file_base_name(FileName, FileBaseName),
 1745                file_name_extension(Module, _, FileBaseName),
 1746                ignore(cql_dependency_hook(ReferencedTableSet, Module))
 1747
 1748            ; otherwise ->
 1749                true
 1750            ),
 1751            ignore(cql_generated_sql_hook(FileName, LineNumber, GoalExpansion))
 1752        ; nonvar(CompilationDirective),
 1753          CompilationDirective = compile_at_runtime(IgnoreIfNullVariables) ->
 1754            % Should this be a compile warning? runtime-compilation should now be officially deprecated
 1755            ( nonvar(IgnoreIfNullVariables) ->
 1756                variable_map(IgnoreIfNullVariables, CqlA, CqlB, VariableMap)
 1757
 1758            ; otherwise ->
 1759                true
 1760            ),
 1761            GoalExpansion = cql_runtime(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber)
 1762        ; otherwise ->
 1763            throw(error(domain_error(cql_compilation_directive, CompilationDirective), _))
 1764        ).
 cql_runtime(+Schema, +IgnoreIfNullVariables, +CqlA, +CqlB, +VariableMap, +FileName, +LineNumber)
 1770cql_runtime(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber) :-
 1771        catch(cql_runtime_1(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber),
 1772              format(Format, Arguments),  % Want a backtrace for compile errors in compile_at_runtime statements at runtime
 1773              cql_error(cql, Format, Arguments)).
 1774
 1775
 1776cql_runtime_1(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber) :-
 1777        ( var(VariableMap) ->
 1778            % Handle the case where IgnoreIfNullVariables is dynamically generated
 1779            variable_map(IgnoreIfNullVariables, CqlA, CqlB, VariableMap)
 1780
 1781        ; otherwise ->
 1782            true
 1783        ),
 1784        CqlC = (store_ignore_if_null_variables(VariableMap),
 1785                original_cql(CqlA),
 1786                cql_statement_location(FileName, LineNumber),
 1787                CqlB),
 1788        translate_to_constraints(Schema, CqlC, InitialConstraints),
 1789        call(InitialConstraints),
 1790        fully_compile,
 1791        cql_execute(do_not_cache_odbc_statement),
 1792        referenced_tables(_).    % Clean up
 1793
 1794
 1795variable_map(IgnoreIfNullVariables, CqlA, CqlB, VariableMap) :-
 1796        copy_term(CqlA, CqlB),
 1797        term_variables(CqlA, ExternalVariables),
 1798        term_variables(CqlB, InternalVariables),
 1799        variable_map_1(ExternalVariables, InternalVariables, IgnoreIfNullVariables, VariableMap).
 1800
 1801
 1802
 1803variable_map_1([], [], _, []).
 1804
 1805variable_map_1([A|As], [B|Bs], IgnoreIfNullVariables, [A-B|VariableMap]) :-
 1806        select(I, IgnoreIfNullVariables, Rest),
 1807        I == A, !,
 1808        variable_map_1(As, Bs, Rest, VariableMap).
 1809
 1810variable_map_1([V|As], [V|Bs], IgnoreIfNullVariables, VariableMap) :-
 1811        variable_map_1(As, Bs, IgnoreIfNullVariables, VariableMap).
 1812
 1813
 1814
 1815translate_to_constraints(Schema,                      % +
 1816                         Cql,                         % +
 1817                         InitialConstraints) :-       % ?
 1818        create_variable_dictionary(Cql, [], CqlGround, Dictionary),
 1819        findall(QueryId-Conjunction,
 1820                translate_to_constraints_1(Schema, top_level_query, CqlGround, QueryId, Conjunction),
 1821                Conjunctions),
 1822        store_conjunctions(Conjunctions, Dictionary),
 1823        conjunction_constraints(true),
 1824        get_conjunction_constraints(InitialConstraints).
 1825
 1826
 1827
 1828store_conjunctions([], _).
 1829
 1830store_conjunctions([QueryId-Conjunction|Conjunctions], Dictionary) :-
 1831        store_conjunction(Conjunction, QueryId, Dictionary),
 1832        store_conjunctions(Conjunctions, Dictionary).
 1833
 1834
 1835
 1836store_conjunction([], _, _).
 1837
 1838store_conjunction([Goal|Goals], QueryId, Dictionary) :-
 1839        create_conjunction_variables(Goal, QueryId, Dictionary, ConjunctionGoal),
 1840        conjunction_goal(ConjunctionGoal),
 1841        store_conjunction(Goals, QueryId, Dictionary).
 1842
 1843
 1844collect_conjunction_variables @
 1845        conjunction_constraints(Constraints),
 1846        conjunction_variable(QueryId, ExternalVariable, ConjunctionVariable)
 1847        <=>
 1848        conjunction_constraints((conjunction_variable(QueryId, ExternalVariable, ConjunctionVariable), Constraints)).
 1849
 1850
 1851collect_conjunction_goals @
 1852        conjunction_constraints(Constraints),
 1853        conjunction_goal(ConjunctionGoal)
 1854        <=>
 1855        conjunction_constraints((ConjunctionGoal, Constraints)).
 1856
 1857
 1858get_conjunction_constraints @
 1859        get_conjunction_constraints(Constraints),
 1860        conjunction_constraints(C)
 1861        <=>
 1862        C = Constraints.
 1863
 1864
 1865
 1866create_variable_dictionary(Term,                     % +
 1867                           Dictionary,               % +
 1868                           GroundTerm,               % ?
 1869                           NewDictionary) :-         % ?
 1870        ( ground(Term) ->
 1871            GroundTerm = Term,
 1872            NewDictionary = Dictionary
 1873
 1874        ; var(Term) ->
 1875            ( member(Variable-GroundTerm, Dictionary),
 1876              Variable == Term ->
 1877                NewDictionary = Dictionary
 1878            ; var_property(Term, fresh(false))->
 1879                gensym(cql_stale_var_, UniqueAtom),
 1880                GroundTerm = '$VAR'(UniqueAtom),
 1881                NewDictionary = [Term-GroundTerm|Dictionary]
 1882            ; otherwise->
 1883                gensym(cql_var_, UniqueAtom),
 1884                GroundTerm = '$VAR'(UniqueAtom),
 1885                NewDictionary = [Term-GroundTerm|Dictionary]
 1886            )
 1887
 1888        ; otherwise ->
 1889            functor(Term, Name, Arity),
 1890            functor(GroundTerm, Name, Arity),
 1891            add_args_to_dictionary(Term, Dictionary, 1, Arity, GroundTerm, NewDictionary)
 1892        ).
 1893
 1894
 1895
 1896add_args_to_dictionary(Term, Dictionary, N, Arity, GroundTerm, NewDictionary) :-
 1897        ( N > Arity ->
 1898            NewDictionary = Dictionary
 1899        ;
 1900            arg(N, Term, Arg),
 1901            create_variable_dictionary(Arg, Dictionary, GroundArg, DictionaryA),
 1902            arg(N, GroundTerm, GroundArg),
 1903            NextN is N + 1,
 1904            add_args_to_dictionary(Term, DictionaryA, NextN, Arity, GroundTerm, NewDictionary)
 1905        ).
 1906
 1907
 1908dictionary_lookup @
 1909        % This allows us to define new join points and tables as we compile (eep?)
 1910        dictionary_addendum(QueryId, A, C)
 1911        \
 1912        dictionary_lookup(QueryId, A, B)
 1913        <=>
 1914        B = C.
 1915
 1916failed_to_get_dictionary_addendum @
 1917        dictionary_lookup(_,_,_)
 1918        <=>
 1919        fail.
 1920
 1921cql_stale:attr_unify_hook(_,_).
 1922create_conjunction_variables(Term, QueryId, Dictionary, TermWithVariables) :-
 1923        ( var(Term) ->
 1924            TermWithVariables = Term
 1925        ; memberchk(ExternalVariable-Term, Dictionary) ->
 1926            conjunction_variable(QueryId, ExternalVariable, ConjunctionVariable),
 1927            ( Term = '$VAR'(Key),
 1928              atom_prefix(Key, cql_stale_var_)->
 1929                put_attr(ConjunctionVariable, cql_stale, 1)
 1930            ; otherwise->
 1931                true
 1932            ),
 1933            TermWithVariables = ConjunctionVariable
 1934        ; dictionary_lookup(QueryId, Term, Var)->
 1935            TermWithVariables = Var
 1936        ; atomic(Term) ->
 1937           TermWithVariables = Term
 1938
 1939        ; otherwise ->
 1940           functor(Term, Name, Arity),
 1941           functor(TermWithVariables, Name, Arity),
 1942           add_arg_variables(Term, QueryId, Dictionary, 1, Arity, TermWithVariables)
 1943        ).
 1944
 1945
 1946
 1947add_arg_variables(Term, QueryId, Dictionary, N, Arity, TermWithVariables) :-
 1948        ( N > Arity ->
 1949            true
 1950        ;
 1951            arg(N, Term, Arg),
 1952            create_conjunction_variables(Arg, QueryId, Dictionary, NewArg),
 1953            arg(N, TermWithVariables, NewArg),
 1954            NextN is N + 1,
 1955            add_arg_variables(Term, QueryId, Dictionary, NextN, Arity, TermWithVariables)
 1956        ).
 1957
 1958
 1959
 1960share_conjunction_variable @
 1961        conjunction_variable(QueryId, ExternalVariable, ConjunctionVariableA)
 1962        \
 1963        conjunction_variable(QueryId, ExternalVariable, ConjunctionVariableB)
 1964        <=>
 1965        ConjunctionVariableA = ConjunctionVariableB.
 1966
 1967
 1968bind_conjunction_variable_if_external_variable_gets_bound @
 1969        conjunction_variable(_, ExternalVariable, ConjunctionVariable)
 1970        <=>
 1971        nonvar(ExternalVariable)
 1972        |
 1973        ConjunctionVariable = ExternalVariable.
 1974
 1975
 1976bind_external_variables_once_fully_compiled @
 1977        cql_fully_compiled
 1978        \
 1979        conjunction_variable(_, ExternalVariable, ConjunctionVariable)
 1980        <=>
 1981        ExternalVariable = ConjunctionVariable.
 1982
 1983
 1984
 1985translate_to_constraints_1(Schema, QueryLevel, Cql, QueryId, CqlConstraints) :-
 1986        ( translate_to_constraints_2(Schema, QueryLevel, Cql, QueryId, CqlConstraints, []) *->
 1987
 1988            ( msort(CqlConstraints, SortedCqlConstraints),
 1989              nextto(state_change_query(_, _, _, _), state_change_query(_, _, _, _), SortedCqlConstraints) ->
 1990                throw(format('Cannot mix state change queries in a single CQL statement', []))
 1991            ;
 1992                true
 1993            )
 1994        ;
 1995           throw(format('Cannot translate CQL: ~w~n', [Cql]))
 1996        ).
 1997
 1998
 1999%       translate_to_constraints_2//4
 2000%
 2001%       QueryId is a variable identifying a query (or sub-query).  The
 2002%       top of the JOIN and WHERE trees is the QueryId
 2003
 2004translate_to_constraints_2(Schema, QueryLevel, Cql, QueryId) -->
 2005        translate(Schema, QueryId, QueryId, Cql),
 2006        [query(QueryId, Schema, QueryLevel),
 2007         restriction_tree(QueryId, where, true),
 2008         sql_statement(QueryId, A, A, B, B, C, C, [], [], [])].
 2009
 2010
 2011%       translate//4
 2012
 2013translate(_, _, _, compile_time_goal(Goal)) --> !,
 2014        {(Goal = Module:Goal1 ->
 2015             true
 2016         ; otherwise->
 2017             prolog_load_context(module, Module),
 2018             Goal1 = Goal
 2019         )},
 2020        [Module:Goal1].
 2021
 2022translate(_, _, _, original_cql(Cql)) --> !,
 2023        [original_cql(Cql)].
 2024
 2025translate(_, _, _, cql_statement_location(FileName, LineNumber)) --> !,
 2026        [cql_statement_location(FileName, LineNumber)].
 2027
 2028translate(_, _, _, store_equality_restriction_variables(EqualityRestrictionVariables)) --> !,
 2029        [store_equality_restriction_variables(EqualityRestrictionVariables)].
 2030
 2031translate(_, _, _, store_ignore_if_null_variables(VariableMap)) --> !,
 2032        [store_ignore_if_null_variables(VariableMap)].
 2033
 2034translate(_, _, _, Term) -->
 2035        {functor(Term, \+, Arity),
 2036         Arity \== 1,
 2037        throw(format('Negation (\\+) is arity one ... add some parentheses: ~w', [Term]))}.
 2038
 2039translate(Schema, QueryId, ParentJoin, (Lhs ; Rhs)) --> !,     % BTP (compile time!)
 2040        (translate(Schema, QueryId, ParentJoin, Lhs)
 2041        ;
 2042        translate(Schema, QueryId, ParentJoin, Rhs)).
 2043
 2044translate(Schema, QueryId, ParentJoin, (Lhs, Rhs)) --> !,
 2045        translate(Schema, QueryId, ParentJoin, Lhs),
 2046        translate(Schema, QueryId, ParentJoin, Rhs).
 2047
 2048
 2049% if we update FROM in postgres, we get an automatic join to the table we're updating
 2050% We need to push things from the @ :: [...] into the where clause, and not have them in the join
 2051% in fact, we shouldn't even list the table in the join unless we're doing a self-join
 2052% (or I guess an outer join).
 2053
 2054% I think that a right outer join in an update is the same as in inner join for all intents and purposes
 2055translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2056        {dbms(Schema, 'PostgreSQL')},
 2057        { JoinTerm =.. [JoinOperator, Lhs, on(Rhs, On)],
 2058          (join(JoinOperator, 'INNER JOIN') ; join(JoinOperator, 'RIGHT OUTER JOIN'))
 2059        },
 2060        {Lhs = (@ :: _) ; Rhs = (@ :: _)},
 2061        !,
 2062        translate(Schema, QueryId, ParentJoin, Lhs),
 2063        translate(Schema, QueryId, ParentJoin, Rhs),
 2064        translate(Schema, QueryId, ParentJoin, On),
 2065        [implicit_join(QueryId, @, SubQueryId),
 2066         implicit_join_link(QueryId, SubQueryId),
 2067         on(SubQueryId, _, On)].
 2068
 2069translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2070        {dbms(Schema, 'PostgreSQL')},
 2071        { JoinTerm =.. [JoinOperator, Lhs, Rhs],
 2072          (join(JoinOperator, 'INNER JOIN') ; join(JoinOperator, 'RIGHT OUTER JOIN'))
 2073        },
 2074        {Lhs = (@ :: _) ; Rhs = (@ :: _)},
 2075        !,
 2076        translate(Schema, QueryId, ParentJoin, Lhs),
 2077        translate(Schema, QueryId, ParentJoin, Rhs),
 2078        [implicit_join(QueryId, @, _)].
 2079
 2080
 2081% This gets very very unpleasant. To do a left outer join in the update, we have to first do an inner join the target
 2082% and then outer join from THERE to complete. Postgres does NOT support left outer join in the from clause otherwise.
 2083% note that FROM in an update is not standard SQL anyway.
 2084translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2085        {dbms(Schema, 'PostgreSQL')},
 2086        { JoinTerm =.. [JoinOperator, Lhs, Rhs],
 2087          join(JoinOperator, 'LEFT OUTER JOIN')
 2088        },
 2089        {Lhs = (@ :: Conditions)}, % don't allow for the target to be on the right. That doesn't really make a lot of sense anyway
 2090        !,
 2091        % Effectively we translate
 2092        %    @ :: [a-A, ...] ==* z :: [z-A, ...]
 2093        % into
 2094        %    (@ :: [a-A, ..., pk-Pk] =*= @@ :: [pk-Pk]) ==* z :: [z-A, ...]
 2095        % Where @@ is a symbol to mean 'the same table as the target but a different alias'
 2096        % The first part of this is dropped as an implicit join
 2097
 2098        % First off, we need to save space for the key. If we don't add this cql_var_X to the
 2099        % dictionary somehow, it will be translated as if cql_var_X were a literal for the WHERE clause
 2100         {gensym(cql_var_, KeyInfo),
 2101          dictionary_addendum(QueryId, KeyInfo, Variable),
 2102          append(Conditions, KeyInfo, NewConditions)},
 2103         [update_table_key(QueryId, Schema, Variable)],
 2104         translate(Schema, QueryId, ParentJoin, (@ :: KeyInfo =*= ((@@) :: NewConditions)) *== Rhs).
 2105
 2106
 2107translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2108        { JoinTerm =.. [JoinOperator, Lhs, on(Rhs, On)],
 2109          join(JoinOperator, JoinType)
 2110        }, !,
 2111        translate(Schema, QueryId, LhsJoin, Lhs),
 2112        translate(Schema, QueryId, RhsJoin, Rhs),
 2113
 2114        [on(ParentJoin, _, On),
 2115         join(QueryId, ParentJoin, LhsJoin, JoinType, RhsJoin)].
 2116
 2117translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2118        { JoinTerm =.. [JoinOperator, Lhs, Rhs],
 2119          join(JoinOperator, JoinType)
 2120        },
 2121        !,
 2122        translate(Schema, QueryId, LhsJoin, Lhs),
 2123        translate(Schema, QueryId, RhsJoin, Rhs),
 2124
 2125        [join(QueryId, ParentJoin, LhsJoin, JoinType, RhsJoin)].
 2126
 2127
 2128
 2129translate(Schema,
 2130          QueryId,
 2131          ParentJoin,
 2132          @ :: AttributeNameValuePairs) --> !,   % '@' means the update table
 2133        [store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs),
 2134         attributes_to_check(QueryId, Schema, @, AttributeNameValuePairs),
 2135         update_table_alias(QueryId, Schema, ParentJoin, TableAlias)].
 2136
 2137translate(Schema,
 2138          QueryId,
 2139          ParentJoin,
 2140          (@@) :: AttributeNameValuePairs) --> !,   % '@@' means a copy of update table
 2141        [store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs),
 2142         attributes_to_check(QueryId, Schema, @, AttributeNameValuePairs),
 2143         query_table_alias(QueryId, Schema, (@@), TableAlias),
 2144         join_leaf(ParentJoin, TableAlias)].
 2145
 2146translate(Schema,
 2147          QueryId,
 2148          ParentJoin,
 2149          #TableName :: AttributeNameValuePairs) --> !,   % '#' means nolock
 2150        translate_select(Schema,
 2151                         QueryId,
 2152                         ParentJoin,
 2153                         TableName,
 2154                         AttributeNameValuePairs, QueryTableAlias),
 2155        [nolock(QueryId, QueryTableAlias)].
 2156
 2157translate(Schema,
 2158          QueryId,
 2159          ParentJoin,
 2160          TableName :: AttributeNameValuePairs) --> !,
 2161        translate_select(Schema,
 2162                         QueryId,
 2163                         ParentJoin,
 2164                         TableName,
 2165                         AttributeNameValuePairs,
 2166                         _).
 2167
 2168translate(Schema, QueryId, _, insert(TableName, AttributeNameValuePairs)) --> !,
 2169        {\+ duplicate_attributes(insert, Schema, TableName, AttributeNameValuePairs)},
 2170        [insert(QueryId, Schema, TableName, AttributeNameValuePairs),
 2171         query_type(QueryId, insert),
 2172         attributes_to_check(QueryId, Schema, TableName, AttributeNameValuePairs),
 2173         state_change_query(QueryId, insert, Schema, TableName)].
 2174
 2175translate(Schema, QueryId, _, update(TableName, UpdateAttributeNameValuePairs)) --> !,
 2176        {\+ duplicate_attributes(update, Schema, TableName, UpdateAttributeNameValuePairs)},
 2177        [update(QueryId, Schema, TableName, _, UpdateAttributeNameValuePairs),
 2178         query_type(QueryId, update),
 2179         attributes_to_check(QueryId, Schema, TableName, UpdateAttributeNameValuePairs),
 2180         state_change_query(QueryId, update, Schema, TableName)].
 2181
 2182translate(Schema, QueryId, ParentJoin, delete(TableName, AttributeNameValuePairs)) --> !,
 2183        {\+ duplicate_attributes(delete, Schema, TableName, AttributeNameValuePairs)},
 2184        [delete_row(QueryId, TableName, TableAlias),
 2185         query_type(QueryId, delete),
 2186         attributes_to_check(QueryId, Schema, TableName, AttributeNameValuePairs),
 2187         store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs),
 2188         join_leaf(ParentJoin, TableAlias),
 2189         query_table_alias(QueryId, Schema, TableName, TableAlias),
 2190         state_change_query(QueryId, delete, Schema, TableName)].
 2191
 2192translate(Schema, QueryId, ParentJoin, \+((Lhs, Rhs))) --> !,     % De Morgan
 2193        translate(Schema, QueryId, ParentJoin, (\+Lhs ; \+Rhs)).
 2194
 2195translate(Schema, QueryId, ParentJoin, \+((Lhs ; Rhs))) --> !,    % De Morgan
 2196        translate(Schema, QueryId, ParentJoin, (\+Lhs, \+Rhs)).
 2197
 2198translate(Schema, QueryId, _, \+Comparison) -->
 2199        {simple_comparison(Schema, Comparison, _, InverseOperator, Lhs, Rhs)}, !,
 2200        translate_comparison(QueryId, Schema, Lhs, InverseOperator, Rhs).
 2201
 2202translate(Schema, QueryId, _, Comparison) -->
 2203        {simple_comparison(Schema, Comparison, Operator, _, Lhs, Rhs)}, !,
 2204        translate_comparison(QueryId, Schema, Lhs, Operator, Rhs).
 2205
 2206translate(Schema, QueryId, _, \+ exists(Goals)) --> !,
 2207        translate_sub_query(Schema, QueryId, \+ exists, Goals).
 2208
 2209translate(Schema, QueryId, _, exists(Goals)) --> !,
 2210        translate_sub_query(Schema, QueryId, exists, Goals).
 2211
 2212translate(_, QueryId, _, group_by(GroupBys)) --> !,
 2213        [group_bys(QueryId, GroupBys)].
 2214
 2215translate(_, QueryId, _, order_by(OrderBys)) --> !,
 2216        [order_bys(QueryId, OrderBys)].
 2217
 2218translate(Schema, QueryId, _, having(Having)) --> !,
 2219        {prolog_term_to_restriction_tree(Schema, Having, RestrictionTree)},
 2220        [restriction_tree(QueryId, having, RestrictionTree)].
 2221
 2222translate(_, QueryId, _, distinct) --> !,
 2223        [select_distinction(QueryId, distinct_on_all_select_attributes)].
 2224
 2225translate(_, QueryId, _, distinct(Distincts)) --> !,
 2226        [distincts(QueryId, Distincts)].
 2227
 2228translate(Schema, QueryId, _, top(N)) --> !,
 2229        [top(QueryId, Schema, N)].
 2230
 2231translate(Schema, QueryId, _, identity(I)) --> !,
 2232        [cql_identity(QueryId, Schema, I)].
 2233
 2234translate(_, QueryId, _, row_count(N)) --> !,
 2235        [row_count(QueryId, N)].
 2236
 2237translate(_, _, _, absence_of_where_restriction_is_deliberate) --> !,
 2238        [absence_of_where_restriction_is_deliberate].
 2239
 2240translate(_, _, _, A=B) --> !,
 2241        {(prolog_load_context(source, FileName),
 2242          prolog_load_context(term_position, TermPosition),
 2243          stream_position_data(line_count, TermPosition, LineNumber)->
 2244            true
 2245         ; otherwise->
 2246            FileName = '<Dynamically created CQL - no source file>',
 2247            LineNumber = 0
 2248         ),
 2249        print_message(warning, format('Unification in CQL is DEPRECATED (~w:~w)~n', [FileName, LineNumber]))},
 2250        [unify(A, B)].
 2251
 2252translate(_, _, _, true) --> !,
 2253        [].
 2254
 2255translate(_, QueryId, _, no_state_change_actions) --> !,
 2256        [no_state_change_actions(QueryId)].
 2257
 2258translate(_, _, _, Term) -->
 2259        {throw(format('Cannot translate CQL term: ~w~n', [Term]))}.
 2260
 2261
 2262join(*==, 'LEFT OUTER JOIN').
 2263join(=*=, 'INNER JOIN').
 2264join(==*, 'RIGHT OUTER JOIN').
 2265
 2266
 2267translate_sub_query(Schema, QueryId, SubQueryType, Goals) -->
 2268        translate_to_constraints_2(Schema, sub_query, Goals, SubQueryId), !,
 2269        [sub_query_select(SubQueryId),
 2270         sub_query_restriction(QueryId, SubQueryType, SubQuerySqlTokens, SubQueryTail, SubQueryInputs),
 2271         sub_query(SubQueryId, SubQuerySqlTokens, SubQueryTail, SubQueryInputs)].
 2272
 2273
 2274translate_select(Schema,                     % +
 2275                 QueryId,                    % +
 2276                 ParentJoin,                 % +
 2277                 TableName,                  % +
 2278                 AttributeNameValuePairs,    % +
 2279                 QueryTableAlias) -->        % ?
 2280        [store_attribute_bindings(Schema, QueryId, QueryTableAlias, AttributeNameValuePairs),
 2281         query_type(QueryId, select),
 2282         attributes_to_check(QueryId, Schema, TableName, AttributeNameValuePairs),
 2283         join_leaf(ParentJoin, QueryTableAlias),
 2284         query_table_alias(QueryId, Schema, TableName, QueryTableAlias)].
 2285
 2286
 2287simple_comparison(Schema,                    % +
 2288                  Comparison,                % +
 2289                  Operator,                  % ?
 2290                  InverseOperator,           % ?
 2291                  Lhs,                       % ?
 2292                  Rhs) :-                    % ?
 2293        functor(Comparison, Operator, 2),
 2294        prolog_to_sql_comparison_operator(Schema, Operator, _, InverseOperator),
 2295        arg(1, Comparison, Lhs),
 2296        arg(2, Comparison, Rhs).
 2297
 2298
 2299translate_comparison(QueryId, Schema, Lhs, Operator, Rhs) -->
 2300        translate_expression(Schema, Lhs, LhsResult),
 2301        translate_expression(Schema, Rhs, RhsResult),
 2302
 2303        [comparison(QueryId, LhsResult, Operator, RhsResult)].
 2304
 2305
 2306translate_expression(Schema,
 2307                     Goal,
 2308                     aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, SubQuerySqlTokens, Tail, SubQueryInputs)) -->
 2309        {functor(Goal, AggregationOperator, 2),
 2310         aggregation_operator(AggregationOperator), !,
 2311         arg(1, Goal, AggregationVariable),
 2312         arg(2, Goal, Goals)},
 2313
 2314        translate_to_constraints_2(Schema, sub_query, Goals, SubQueryId),
 2315
 2316        [aggregation_variable(SubQueryId,
 2317                              AggregationOperator,
 2318                              AggregationVariable),
 2319         aggregation_sub_query(SubQueryId, AggregationTableName, AggregationAttributeName, SubQuerySqlTokens, Tail, SubQueryInputs)].
 2320
 2321
 2322translate_expression(_, Variable, Variable) -->
 2323        [true].
 2324
 2325
 2326aggregation_operator(count).
 2327aggregation_operator(max).
 2328aggregation_operator(min).
 2329aggregation_operator(avg).
 2330aggregation_operator(sum).
 2331
 2332
 2333prolog_term_to_restriction_tree(Schema, \+(Lhs, Rhs), RestrictionTree) :- !,
 2334        prolog_term_to_restriction_tree(Schema, (\+Lhs ; \+Rhs), RestrictionTree).
 2335
 2336prolog_term_to_restriction_tree(Schema, \+(Lhs ; Rhs), RestrictionTree) :- !,
 2337        prolog_term_to_restriction_tree(Schema, (\+Lhs, \+Rhs), RestrictionTree).
 2338
 2339prolog_term_to_restriction_tree(Schema, (Lhs, Rhs), and(RestrictionLhs, RestrictionRhs)) :- !,
 2340        prolog_term_to_restriction_tree(Schema, Lhs, RestrictionLhs),
 2341        prolog_term_to_restriction_tree(Schema, Rhs, RestrictionRhs).
 2342
 2343prolog_term_to_restriction_tree(Schema, (Lhs ; Rhs), or(RestrictionLhs, RestrictionRhs)) :- !,
 2344        prolog_term_to_restriction_tree(Schema, Lhs, RestrictionLhs),
 2345        prolog_term_to_restriction_tree(Schema, Rhs, RestrictionRhs).
 2346
 2347prolog_term_to_restriction_tree(Schema, \+Comparison, comparison(RestrictionLhs, InverseOperator, RestrictionRhs)) :- !,
 2348        simple_comparison(Schema, Comparison, _, InverseOperator, RestrictionLhs, RestrictionRhs).
 2349
 2350prolog_term_to_restriction_tree(Schema, Comparison, comparison(Lhs, Operator, Rhs)) :-
 2351        ( simple_comparison(Schema, Comparison, Operator, _, Lhs, Rhs) ->
 2352            true
 2353        ;
 2354            throw(format('Cannot translate restriction term: ~w', [Comparison]))
 2355        ).
 2356
 2357
 2358equality_restriction_variables_are_unique @
 2359        equality_restriction_variable(Variable, _)
 2360        \
 2361        equality_restriction_variable(Variable, _)
 2362        <=>
 2363        true.
 2364
 2365
 2366store_equality_restriction_variables @
 2367        store_equality_restriction_variables([InputVariable|InputVariables])
 2368        <=>
 2369        equality_restriction_variable(InputVariable, _),
 2370        store_equality_restriction_variables(InputVariables).
 2371
 2372
 2373cleanup_store_equality_restriction_variables @
 2374        store_equality_restriction_variables([])
 2375        <=>
 2376        true.
 2377
 2378
 2379store_ignore_if_null_variables @
 2380        store_ignore_if_null_variables([ExternalVariable-InternalVariable|VariableMap])
 2381        <=>
 2382        ignore_if_null(ExternalVariable, InternalVariable),
 2383        store_ignore_if_null_variables(VariableMap).
 2384
 2385
 2386cleanup_store_ignore_if_null_variables @
 2387        store_ignore_if_null_variables([])
 2388        <=>
 2389        true.
 2390
 2391
 2392store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs) :-
 2393        ( store_attribute_bindings_1(Schema, QueryId, TableAlias, AttributeNameValuePairs) ->
 2394            true
 2395        ;
 2396            throw(format('Bad attribute bindings: ~w', [AttributeNameValuePairs]))
 2397        ).
 2398
 2399
 2400store_attribute_bindings_1(_, _, _, []).
 2401
 2402store_attribute_bindings_1(Schema, QueryId, TableAlias, [AttributeNameValuePair|AttributeNameValuePairs]) :-
 2403        % For INSERT from SELECTs
 2404        ( AttributeNameValuePair = as(AttributeName)-ApplicationValue ->
 2405            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), selection_constant(ApplicationValue))
 2406
 2407        % Normal Name-Value specification
 2408        ; AttributeNameValuePair = AttributeName-ApplicationValue,
 2409          atomic_application_value(ApplicationValue) ->
 2410            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), ApplicationValue)
 2411
 2412        % ignore_if_null
 2413        ; AttributeNameValuePair = (AttributeName-ignore_if_null(ApplicationValue)),
 2414          var(ApplicationValue)->
 2415            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), AttributeValue),
 2416            comparison(QueryId, AttributeValue, ==, ignore_if_null(ApplicationValue))
 2417
 2418        % runtime list
 2419        ; AttributeNameValuePair = (AttributeName-list(ApplicationValue))->
 2420            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), AttributeValue),
 2421            comparison(QueryId, AttributeValue, ==, list(ApplicationValue))
 2422
 2423        % Compile-time attribute value
 2424        ; AttributeNameValuePair = (AttributeName-CompileTimeGoal),
 2425          callable(CompileTimeGoal),
 2426          functor(CompileTimeGoal, PredicateName, ArityMinusOne),
 2427          Arity is ArityMinusOne + 1,
 2428          current_predicate(user:PredicateName/Arity),
 2429          user:call(CompileTimeGoal, ApplicationValue) ->
 2430            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), ApplicationValue)
 2431        ),
 2432        store_attribute_bindings_1(Schema, QueryId, TableAlias, AttributeNameValuePairs).
 2433
 2434
 2435atomic_application_value(ApplicationValue) :-  % +
 2436        ( var(ApplicationValue)
 2437        ; atom(ApplicationValue)
 2438        ; integer(ApplicationValue)
 2439        ; rational(ApplicationValue)
 2440	; timestamp(ApplicationValue)
 2441        ; cql_atomic_value_check_hook(ApplicationValue)
 2442        ; is_list(ApplicationValue)
 2443        ; ApplicationValue == {null}
 2444        ; ApplicationValue == {timestamp}
 2445        ; ApplicationValue == {user_id}
 2446        ; ApplicationValue == {transaction_id}
 2447        ),
 2448        !.
 2449
 2450timestamp(TS) :-
 2451	compound(TS),
 2452	functor(TS, timestamp, 7).
 2453
 2454
 2455ensure_binding_is_on_the_external_variable_so_that_ignore_if_null_works_properly_1 @
 2456        ignore_if_null(ExternalVariable, InternalVariable)
 2457        \
 2458        unify(InternalVariable, X)
 2459        <=>
 2460        ExternalVariable = X.
 2461
 2462
 2463ensure_binding_is_on_the_external_variable_so_that_ignore_if_null_works_properly_2 @
 2464        ignore_if_null(ExternalVariable, InternalVariable)
 2465        \
 2466        unify(X, InternalVariable)
 2467        <=>
 2468        ExternalVariable = X.
 2469
 2470
 2471make_unify_unify @
 2472        unify(X, Y)
 2473        <=>
 2474        X = Y.
 2475
 2476
 2477remove_comparison_involving_ignored_variable @
 2478        ignore_if_null(ExternalVariable, InternalVariable)
 2479        \
 2480        comparison(_, Lhs, _, Rhs)
 2481        <=>
 2482        ( ExternalVariable == {null},
 2483          InternalVariable == Lhs
 2484
 2485        ; ExternalVariable == {null},
 2486          InternalVariable == Rhs
 2487
 2488        ; InternalVariable == Lhs,
 2489          Rhs == {null}
 2490
 2491        ; InternalVariable == Rhs,
 2492          Lhs == {null}
 2493        )
 2494        |
 2495        true.
 2496
 2497
 2498fully_compile @
 2499        fully_compile
 2500        <=>
 2501        no_sql_statement_generated,
 2502        unify_ignore_if_null_variables,
 2503        create_in_line_joins,
 2504        create_join_points,
 2505        resolve_join_points,
 2506        determine_select_distinctions,
 2507        create_select_bindings,
 2508        determine_selection_type,
 2509        create_restrictions,
 2510        generate_sub_query_sql,
 2511        simplify,
 2512        ( debugging(cql(compile)) ->
 2513            with_output_to(codes(Codes), chr_show_store(cql)),
 2514            debug(cql(compile), '==========~n~s^^^^^^^^^^~n~n', [Codes])
 2515        ;
 2516            true
 2517        ),
 2518        check_for_top_without_order_by,
 2519        write_query_sql,
 2520        instantiate_table_aliases,
 2521        write_limit,
 2522        check_for_orphan_select_attributes_in_aggregations,
 2523        check_query,
 2524        check_for_unjoined_tables,
 2525        check_for_orphan_group_bys,
 2526        check_for_orphan_order_bys,
 2527        check_for_orphan_distincts,
 2528        check_for_orphan_select_variables_in_updates,
 2529        prepare_odbc_statements,
 2530        cleanup_compile,
 2531        cql_fully_compiled.
 2532
 2533
 2534
 2535unify_ignore_if_null_variables @
 2536        unify_ignore_if_null_variables
 2537        \
 2538        ignore_if_null(ExternalVariable, InternalVariable)
 2539        <=>
 2540        nonvar(ExternalVariable),
 2541        ExternalVariable \== {null}
 2542        |
 2543        InternalVariable = ExternalVariable.
 2544
 2545
 2546cleanup_unify_ignore_if_null_variables @
 2547        unify_ignore_if_null_variables
 2548        <=>
 2549        true.
 2550
 2551
 2552update_atat_table_name @
 2553        update(QueryId, Schema, TableName, _, _)
 2554        \
 2555        query_table_alias(QueryId, Schema, (@@), Alias)
 2556        <=>
 2557        query_table_alias(QueryId, Schema, TableName, Alias).
 2558
 2559
 2560resolve_update_table_alias @
 2561        create_join_points,
 2562        update(QueryId, Schema, TableName, UpdateTableAlias, _),
 2563        update_table_alias(QueryId, _, ParentJoin, TableAlias)
 2564        ==>
 2565        join_leaf(ParentJoin, TableAlias),
 2566        query_table_alias(QueryId, Schema, TableName, TableAlias),
 2567        UpdateTableAlias = TableAlias.
 2568
 2569
 2570where_restriction_variable_not_allowed_to_be_an_outer_join_point @
 2571        outer_side_join(Join),
 2572        join_leaf(Join, TableAlias),
 2573        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 2574        join_variable(JoinVariable),
 2575        where_restriction_variable(JoinVariable)
 2576        <=>
 2577        throw(format('A variable used in a WHERE RESTRICTION must not also be a SHARED VARIABLE defining an OUTER JOIN point', [])).
 2578
 2579
 2580deprecated_group_by @
 2581        attribute_binding(_, attribute(_, _, group_by(_)), _)
 2582        <=>
 2583        throw(format('OBSOLETE group_by format.  Use separate group_by([V1, V2, ...])', [])).
 2584
 2585
 2586bad_group_by_specification @
 2587        group_bys(_, GroupBys)
 2588        <=>
 2589        ( \+ is_list(GroupBys)
 2590        ; is_list(GroupBys),
 2591          member(GroupBy, GroupBys),
 2592          nonvar(GroupBy)
 2593        )
 2594        |
 2595        throw(format('GROUP BY must specify a LIST of variables', [group_by(GroupBys)])).
 2596
 2597
 2598individual_group_by @
 2599        group_bys(QueryId, [GroupBy|GroupBys])
 2600        <=>
 2601        group_by(QueryId, GroupBy),
 2602        group_bys(QueryId, GroupBys).
 2603
 2604
 2605no_more_group_bys @
 2606        group_bys(_, [])
 2607        <=>
 2608        true.
 2609
 2610
 2611copy_attribute_for_group_by @
 2612        attribute_binding(QueryId, attribute(_, TableAlias, AttributeName), Variable)
 2613        ==>
 2614        attribute_for_group_by(QueryId, TableAlias, AttributeName, Variable).
 2615
 2616
 2617ambiguous_group_by_attribute @
 2618        group_by(QueryId, GroupBy),
 2619        attribute_for_group_by(QueryId, TableAliasA, AttributeNameA, GroupBy),
 2620        attribute_for_group_by(QueryId, TableAliasB, AttributeNameB, GroupBy),
 2621        query_table_alias(_, _, TableNameA, TableAliasA),
 2622        query_table_alias(_, _, TableNameB, TableAliasB)
 2623        <=>
 2624        throw(format('GROUP BY variable is AMBIGUOUS.  It identifies both ~w.~w AND ~w.~w.  Use == to specify the join point?',
 2625                     [TableNameA, AttributeNameA, TableNameB, AttributeNameB])).
 2626
 2627
 2628bad_distinct_specification @
 2629        distincts(_, Distincts)
 2630        <=>
 2631        ( \+ is_list(Distincts)
 2632        ; is_list(Distincts),
 2633          member(Distinct, Distincts),
 2634          nonvar(Distinct)
 2635        )
 2636        |
 2637        throw(format('DISTINCT must specify a LIST of variables', [distinct(Distincts)])).
 2638
 2639
 2640individual_distinct @
 2641        distincts(QueryId, [Distinct|Distincts])
 2642        <=>
 2643        distinct(QueryId, Distinct),
 2644        distincts(QueryId, Distincts).
 2645
 2646
 2647no_more_distincts @
 2648        distincts(QueryId, [])
 2649        <=>
 2650        select_distinction(QueryId, distinct_on_specified_attributes).
 2651
 2652
 2653determine_select_distinctions @
 2654        query(QueryId, _, _),
 2655        determine_select_distinctions
 2656        ==>
 2657        determine_select_distinction(QueryId).
 2658
 2659
 2660select_distinction_exists @
 2661        select_distinction(QueryId, _)
 2662        \
 2663        determine_select_distinction(QueryId)
 2664        <=>
 2665        true.
 2666
 2667
 2668no_select_distinction @
 2669        determine_select_distinction(QueryId)
 2670        <=>
 2671        select_distinction(QueryId, no_distinction).
 2672
 2673
 2674select_binding @
 2675        create_select_bindings,
 2676        attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), Variable)
 2677        ==>
 2678        selection_variable(Variable)
 2679        |
 2680        Attribute = attribute(Schema, TableAlias, AttributeName),
 2681        select_binding(QueryId, plain, Attribute, Variable).
 2682
 2683cleanup_create_select_bindings @
 2684        create_select_bindings
 2685        <=>
 2686        true.
 2687
 2688
 2689selection_variable(V) :-
 2690        var(V).
 2691selection_variable(V) :-
 2692        \+ ground(V),
 2693        cql_atomic_value_check_hook(V).
 2694selection_variable(selection_constant(_)).
 2695
 2696
 2697select_binding_aggregation @
 2698        select_binding(QueryId, plain, attribute(Schema, TableAlias, AggregationTerm), Variable)
 2699        <=>
 2700        AggregationTerm =.. [AggregationOperator, AttributeName],
 2701        aggregation_operator(AggregationOperator)
 2702        |
 2703        select_binding(QueryId, aggregation(AggregationOperator), attribute(Schema, TableAlias, AttributeName), Variable).
 2704
 2705
 2706sub_select_binding_aggregation @
 2707        aggregation_variable(_, AggregationOperator, AggregationVariable),
 2708        select_binding(QueryId, plain, Attribute, Variable)
 2709        <=>
 2710        AggregationVariable == Variable
 2711        |
 2712        select_binding(QueryId, aggregation(AggregationOperator), Attribute, Variable).
 2713
 2714
 2715instantiate_table_aliases @
 2716        instantiate_table_aliases,
 2717        query_table_alias(_, _, TableName, TableAlias)
 2718        ==>
 2719        var(TableAlias)
 2720        |
 2721        table_alias_crunch(TableName, Crunched),
 2722        atom_concat(Crunched, '_', Stem),
 2723        gensym(Stem, Symbol),
 2724        map_database_atom(Symbol, TableAlias).
 2725
 2726
 2727table_alias_crunch(TableName, Crunched):-
 2728        atom_codes(TableName, Codes),
 2729        extract_abbreviation(AbbreviationCodes, [95|Codes], []),
 2730        atom_codes(Crunched, AbbreviationCodes).
 2731
 2732extract_abbreviation([])-->
 2733        [].
 2734
 2735extract_abbreviation([Code|Codes])-->
 2736        "_",
 2737        !,
 2738        [Code],
 2739        extract_abbreviation(Codes).
 2740
 2741extract_abbreviation(Codes)-->
 2742        [_],
 2743        extract_abbreviation(Codes).
 2744
 2745cleanup_instantiate_table_aliases @
 2746        instantiate_table_aliases
 2747        <=>
 2748        true.
 2749
 2750
 2751check_aggregation_attribute @
 2752        select_binding(QueryId, aggregation(_), attribute(Schema, TableAlias, AttributeName), _),
 2753        query_table_alias(QueryId, _, TableName, TableAlias)
 2754        <=>
 2755        \+ cql_data_type(Schema, TableName, AttributeName, _, _, _, _, _, _, _)
 2756        |
 2757        throw(format('Unknown SELECT attribute in CQL: ~w', [Schema:TableName:AttributeName])).
 2758
 2759
 2760aggregation_selection @
 2761        select_binding(QueryId, aggregation(_), _, _),
 2762        determine_selection_type
 2763        ==>
 2764        selection_type(QueryId, aggregation_selection).
 2765
 2766
 2767non_aggregation_selection @
 2768        select_binding(QueryId, SelectBindingType, _, _),
 2769        determine_selection_type
 2770        ==>
 2771        SelectBindingType \= aggregation(_)
 2772        |
 2773        selection_type(QueryId, non_aggregation_selection).
 2774
 2775
 2776cleanup_determine_selection_type @
 2777        determine_selection_type
 2778        <=>
 2779        true.
 2780
 2781
 2782priority @
 2783        selection_type(QueryId, aggregation_selection)
 2784        \
 2785        selection_type(QueryId, non_aggregation_selection)
 2786        <=>
 2787        true.
 2788
 2789
 2790uniqueness @
 2791        selection_type(QueryId, SelectionType)
 2792        \
 2793        selection_type(QueryId, SelectionType)
 2794        <=>
 2795        true.
 2796
 2797get_data_size(Schema, TableName, AttributeName, Size):-
 2798        cql_data_type(Schema, TableName, AttributeName, _, CharacterMaximumLength, _, _, _, _, _),
 2799        ( CharacterMaximumLength == max ->
 2800            % This should be close enough. It just has to be larger than any declared column length, and the max there is 8192 for SQL Server.
 2801            % For all other DBMS it doesnt matter
 2802            Size = 65535
 2803        ; integer(CharacterMaximumLength) ->
 2804            Size = CharacterMaximumLength
 2805        ; otherwise->
 2806            Size = 0
 2807        ).
 2808
 2809aggregation_selection @
 2810        selection_type(QueryId, aggregation_selection),
 2811        query_table_alias(QueryId, _, TableName, TableAlias)
 2812        \
 2813        select_binding(QueryId, SelectBindingType, attribute(Schema, TableAlias, AttributeName), Variable)
 2814        <=>
 2815        SelectBindingType = aggregation(_)
 2816        |
 2817        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, Variable).
 2818
 2819
 2820aggregation_group_by_selection @
 2821        selection_type(QueryId, aggregation_selection),
 2822        query_table_alias(QueryId, _, TableName, TableAlias),
 2823        group_by(QueryId, GroupBy)
 2824        \
 2825        select_binding(QueryId, SelectBindingType, attribute(Schema, TableAlias, AttributeName), GroupBy)
 2826        <=>
 2827        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, GroupBy).
 2828
 2829
 2830ignore_aggregation_select_binding @
 2831        selection_type(QueryId, aggregation_selection), where_restriction_variable(Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2832        selection_type(QueryId, aggregation_selection), join_variable(Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2833        selection_type(QueryId, aggregation_selection), sub_query_join_variable(Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2834        selection_type(QueryId, aggregation_selection), ignore_if_null(_, Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2835
 2836
 2837aggregation_select_binding_error @
 2838        check_for_orphan_select_attributes_in_aggregations,
 2839        selection_type(QueryId, aggregation_selection)
 2840        \
 2841        select_binding(QueryId, _, Attribute, _)
 2842        <=>
 2843        throw(format('Aggregation refers to an attribute which is not aggregated, not grouped by, not a restriction and not a join point: ~w', [Attribute])).
 2844
 2845
 2846non_aggregation_select_binding @
 2847        selection_type(QueryId, non_aggregation_selection),
 2848        query_table_alias(QueryId, _, TableName, TableAlias)
 2849        \
 2850        select_binding(QueryId, SelectBindingType, attribute(Schema, TableAlias, AttributeName), Variable)
 2851        <=>
 2852        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, Variable).
 2853
 2854
 2855keep_if_distinct_on_specified_attributes @
 2856        select_distinction(QueryId, distinct_on_specified_attributes),
 2857        select_attribute(QueryId, _, Keep, _, Variable)
 2858        \
 2859        distinct(QueryId, Variable)
 2860        <=>
 2861        Keep = 1.
 2862
 2863
 2864keep_if_distinct_on_all_attributes_or_if_there_is_no_distinction @
 2865        select_distinction(QueryId, Distinction),
 2866        select_attribute(QueryId, _, Keep, _, _)
 2867        ==>
 2868        ( Distinction == distinct_on_all_select_attributes
 2869        ; Distinction == no_distinction
 2870        )
 2871        |
 2872        Keep = 1.
 2873
 2874
 2875copy_select_attribute_for_disjunction_comparison @
 2876        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, _)
 2877        ==>
 2878        select_attribute_for_disjunction_comparison(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName)).
 2879
 2880
 2881bad_order_by_specification @
 2882        order_bys(_, OrderBys)
 2883        <=>
 2884        ( \+ is_list(OrderBys)
 2885        ; is_list(OrderBys),
 2886          member(OrderBy, OrderBys),
 2887          nonvar(OrderBy),
 2888          OrderBy \= +(_),
 2889          OrderBy \= -(_)
 2890        )
 2891        |
 2892        throw(format('ORDER BY must specify a LIST of +/1 and -/1 terms but found ~w', [order_by(OrderBys)])).
 2893
 2894
 2895copy_attribute_for_order_by @
 2896        attribute_binding(QueryId, attribute(_, TableAlias, AttributeName), Variable)
 2897        ==>
 2898        attribute_for_order_by(QueryId, TableAlias, AttributeName, Variable).
 2899
 2900
 2901ambiguous_order_by_attribute @
 2902        write_order_by(QueryId, OrderBy)
 2903        \
 2904        attribute_for_order_by(QueryId, TableAliasA, AttributeNameA, Variable),
 2905        attribute_for_order_by(QueryId, TableAliasB, AttributeNameB, Variable),
 2906        query_table_alias(_, _, TableNameA, TableAliasA),
 2907        query_table_alias(_, _, TableNameB, TableAliasB)
 2908        <=>
 2909        ( OrderBy == +Variable
 2910        ; OrderBy == -Variable
 2911        )
 2912        |
 2913        throw(format('ORDER BY variable is AMBIGUOUS.  It identifies both ~w.~w AND ~w.~w.  Use == to specify the join point?',
 2914                     [TableNameA, AttributeNameA, TableNameB, AttributeNameB])).
 2915
 2916select_attributes_1 @
 2917        selection_type(QueryId, _)
 2918        ==>
 2919        select_attributes_for_disjunction_comparison(QueryId, []).
 2920
 2921
 2922select_attributes_2 @
 2923        select_attribute_for_disjunction_comparison(QueryId, SelectAttribute),
 2924        select_attributes_for_disjunction_comparison(QueryId, SelectAttributes)
 2925        <=>
 2926        merge_set([SelectAttribute], SelectAttributes, SortedSelectAttributes),
 2927        select_attributes_for_disjunction_comparison(QueryId, SortedSelectAttributes).
 2928
 2929
 2930check_for_top_without_order_by @
 2931        top(QueryId, _, _), order_bys(QueryId, _) \ check_for_top_without_order_by <=> true.
 2932        top(_, _, _), check_for_top_without_order_by, original_cql(Cql) <=> throw(format('top without order_by in CQL: ~w', [Cql])).
 2933        check_for_top_without_order_by <=> true.
 2934
 2935
 2936join_tree_nodes @
 2937        simplify,
 2938        query(Join, _, top_level_query)       % Only bother doing join_tree for top_level_queries
 2939        % Solitary join leaf has the QueryId as its parent i.e. Join == QueryId
 2940        ==>
 2941        join_pointer(Join, Join),
 2942        join_tree_nodes(Join, []).
 2943
 2944
 2945identify_join_type_1 @
 2946        join(_, _, _, 'LEFT OUTER JOIN', RhsJoin)
 2947        ==>
 2948        outer_side_join(RhsJoin).
 2949
 2950
 2951identify_join_type_2 @
 2952        join(_, _, LhsJoin, 'RIGHT OUTER JOIN', _)
 2953        ==>
 2954        outer_side_join(LhsJoin).
 2955
 2956
 2957every_join_below_an_outer_side_join_is_an_outer_side_join @
 2958        outer_side_join(ParentJoin),
 2959        join(_, ParentJoin, LhsJoin, _, RhsJoin)
 2960        ==>
 2961        outer_side_join(LhsJoin),
 2962        outer_side_join(RhsJoin).
 2963
 2964
 2965walk_join_tree_branch @
 2966        join(QueryId, Join, LhsJoin, JoinType, RhsJoin)
 2967        \
 2968        join_pointer(QueryId, Join)
 2969        <=>
 2970        join_tree_node(QueryId, Join, branch(JoinType)),
 2971        join_pointer(QueryId, LhsJoin),
 2972        join_pointer(QueryId, RhsJoin).
 2973
 2974
 2975walk_join_tree_on_clause @
 2976        join_tree_node(QueryId, Join, branch(_)),
 2977        on(Join, _, On)
 2978        ==>
 2979        join_tree_node(QueryId, Join, On).
 2980
 2981
 2982cleanup_join_tree_node_branch @
 2983        join_tree_node(_, _, branch(_))
 2984        <=>
 2985        true.
 2986
 2987
 2988walk_join_tree_leaf @
 2989        join_leaf(Join, TableAlias),
 2990        query_table_alias(QueryId, _, _, TableAlias)
 2991        \
 2992        join_pointer(QueryId, Join)
 2993        <=>
 2994        join_tree_node(QueryId, Join, table_alias(TableAlias)).
 2995
 2996
 2997accumulate_join_tree_nodes @
 2998        join_tree_nodes(QueryId, JoinTreeNodes),
 2999        join_tree_node(QueryId, _, JoinTreeNode)
 3000        <=>
 3001        join_tree_nodes(QueryId, [JoinTreeNode|JoinTreeNodes]).
 3002
 3003
 3004amalgamate_restrictions_if_join_tree_is_the_same @
 3005        select_attributes_for_disjunction_comparison(QueryIdA, SelectAttributesA),
 3006        join_tree_nodes(QueryIdA, JoinTreeNodesA)
 3007        \
 3008        select_attributes_for_disjunction_comparison(QueryIdB, SelectAttributesB),
 3009        join_tree_nodes(QueryIdB, JoinTreeNodesB),
 3010        restriction_tree(QueryIdA, RestrictionType, RestrictionTreeA),
 3011        restriction_tree(QueryIdB, RestrictionType, RestrictionTreeB)
 3012        <=>
 3013        unifiable(SelectAttributesA, SelectAttributesB, _),
 3014        unifiable(JoinTreeNodesA, JoinTreeNodesB, Unifiers)
 3015        |
 3016        restriction_tree(QueryIdA, RestrictionType, or(RestrictionTreeA, RestrictionTreeB)),
 3017        remove_query(QueryIdB, QueryIdA),
 3018        unify_table_aliases(Unifiers).
 3019
 3020
 3021% We can do this because the ONLY unbound variables in the join tree are table aliases
 3022unify_table_aliases([]).
 3023unify_table_aliases([TableAliasA=TableAliasB|Unifiers]) :-
 3024        TableAliasA = TableAliasB,
 3025        unify_table_aliases(Unifiers).
 3026
 3027
 3028solve_sub_query @
 3029        generate_sub_query_sql,
 3030        query(SubQueryId, _, sub_query)
 3031        ==>
 3032        phase(SubQueryId, initial).
 3033
 3034
 3035solve_top_level_query @
 3036        write_query_sql,
 3037        query(QueryId, _, top_level_query)
 3038        ==>
 3039        phase(QueryId, initial).
 3040
 3041
 3042ignore_equality_restriction_variable_if_it_becomes_bound @
 3043        equality_restriction_variable(Variable, _)
 3044        <=>
 3045        nonvar(Variable)
 3046        |
 3047        true.
 3048
 3049
 3050add_sub_query_join_where @
 3051        query(TopLevelQueryId, _, top_level_query),
 3052        query(SubQueryId, _, sub_query),
 3053        attribute_binding(TopLevelQueryId, attribute(_, TopLevelTableAlias, TopLevelAttributeName), Variable),
 3054        attribute_binding(SubQueryId, attribute(_, SubQueryTableAlias, SubQueryAttributeName), Variable)
 3055        \
 3056        % No longer a SELECT binding in the sub-query; its now part of the sub query WHERE
 3057        select_binding(SubQueryId, _, _, Variable)
 3058        <=>
 3059        not_a_singleton(Variable),
 3060        sub_query_join_variable(Variable),
 3061        restriction_leaf(SubQueryId,
 3062                         where,
 3063                         comparison(attribute(_, TopLevelTableAlias, TopLevelAttributeName),
 3064                                    ==,
 3065                                    attribute(_, SubQueryTableAlias, SubQueryAttributeName))).
 3066
 3067
 3068restriction_from_comparison_of_top_level_query_attribute_to_sub_query_attribute @
 3069        %
 3070        % x :: [a-A, b-B], \+ y :: [b-B, c-C], C > A
 3071        %
 3072        query(QueryId, _, top_level_query),
 3073        query(SubQueryId, _, sub_query),
 3074        attribute_binding(QueryId, Attribute_1, V1),
 3075        attribute_binding(SubQueryId, Attribute_2, V2)
 3076        \
 3077        comparison(QueryId, Lhs, Operator, Rhs)
 3078        <=>
 3079        ( Lhs == V1,
 3080          Rhs == V2 ->
 3081            Comparison = comparison(Attribute_1, Operator, Attribute_2)
 3082
 3083        ; Lhs == V2,
 3084          Rhs == V1 ->
 3085            Comparison = comparison(Attribute_2, Operator, Attribute_1)
 3086        )
 3087        |
 3088        restriction_leaf(SubQueryId, where, Comparison).
 3089
 3090
 3091add_sub_query_restriction @
 3092        create_restrictions
 3093        \
 3094        sub_query_restriction(QueryId, SubQueryType, SubQuerySql, SubQueryTail, SubQueryInputs)
 3095        <=>
 3096        restriction_leaf(QueryId, where, sub_query(SubQueryType, SubQuerySql, SubQueryTail, SubQueryInputs)).
 3097
 3098
 3099restriction_from_bound_attribute @
 3100        %
 3101        % x :: a-'A1'
 3102        % x :: a-['A1', 'A2']
 3103        %
 3104        create_restrictions,
 3105        attribute_binding(QueryId, Attribute, ApplicationValue)
 3106        ==>
 3107        ( ground(ApplicationValue)
 3108        ; is_list(ApplicationValue)
 3109        ),
 3110        ApplicationValue \= selection_constant(_)
 3111        |
 3112        restriction_leaf(QueryId, where, comparison(Attribute, ==, ApplicationValue)).
 3113
 3114
 3115restriction_from_equality_restriction_variable @
 3116        %
 3117        % {[A], x :: [a-A]}
 3118        %
 3119        create_restrictions,
 3120        attribute_binding(QueryId, Attribute, Variable),
 3121        equality_restriction_variable(Variable, EqualityRestrictionVariableUsed)
 3122        ==>
 3123        EqualityRestrictionVariableUsed = equality_restriction_variable_used,
 3124        where_restriction_variable(Variable),
 3125        restriction_leaf(QueryId, where, comparison(Attribute, ==, equality_restriction(Variable))).
 3126
 3127
 3128restriction_from_comparison @
 3129        create_restrictions
 3130        \
 3131        comparison(QueryId, Lhs, Operator, Rhs)
 3132        <=>
 3133        not_a_singleton(Lhs),
 3134        not_a_singleton(Rhs),
 3135        variables_to_attributes(Lhs, MappedLhs),
 3136        variables_to_attributes(Rhs, MappedRhs),
 3137        restriction_leaf(QueryId, where, comparison(MappedLhs, Operator, MappedRhs)).
 3138
 3139cqlv2_1_restriction_from_any_non_fresh_variable @
 3140        query_type(QueryId, QueryType),
 3141        compile_mode(compiletime),
 3142        attribute_binding(QueryId, attribute(Schema, Alias, AttributeName), Variable)
 3143        ==>
 3144        atom(AttributeName), % Exclude aggregates. Is there a more elegant way?
 3145        var(Variable),
 3146        get_attr(Variable, cql_stale, 1)
 3147        |
 3148        ( ( QueryType == select ; QueryType == insert) ->
 3149            Comparison = if_not_var(Variable)
 3150        ; otherwise->
 3151            Comparison = equality_restriction(Variable)
 3152        ),
 3153        cql2_variable(QueryId, Variable, comparison(attribute(Schema, Alias, AttributeName), ==, Comparison)).
 3154
 3155/*
 3156cqlv2_variable_unique @
 3157        cql2_variable(QueryId, _, Variable)
 3158        \
 3159        cql2_variable(QueryId, _, Variable)
 3160        <=>
 3161        true.
 3162*/
 3163
 3164cqlv2_1_except_when_restriction_already_exists_1 @
 3165        equality_restriction_variable(Variable, _)
 3166        \
 3167        cql2_variable(_, Variable, _)
 3168        <=>
 3169        true.
 3170
 3171cqlv2_1_except_when_restriction_already_exists_2 @
 3172        expression_where_restriction_variable(Variable)
 3173        \
 3174        cql2_variable(_, Variable, _)
 3175        <=>
 3176        true.
 3177
 3178cqlv2_1_except_when_restriction_already_exists_3 @
 3179        sub_query_join_variable(Variable)
 3180        \
 3181        cql2_variable(_, Variable, _)
 3182        <=>
 3183        true.
 3184
 3185cqlv2_1_except_when_restriction_already_exists_4 @
 3186        expression_where_restriction_variable(Variable)
 3187        \
 3188        cql2_variable(_, Variable, _)
 3189        <=>
 3190        true.
 3191
 3192cqlv2_1_except_when_restriction_already_exists_5 @
 3193        outer_side_join(Join),
 3194        join_leaf(Join, TableAlias),
 3195        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 3196        join_variable(JoinVariable)
 3197        \
 3198        cql2_variable(_, JoinVariable, _)
 3199        <=>
 3200        true.
 3201
 3202
 3203cqlv2_1_except_comparisons @
 3204        query_type(QueryId, Type),
 3205        comparison(QueryId, Lhs, _Operator, Rhs)
 3206        \
 3207        cql2_variable(QueryId, Variable, _)
 3208        <=>
 3209        % If you write something like
 3210        % foo(X):- {[], some_table :: [column-X], X == 'VALUE'}
 3211        % Then, first of all, you probably made a coding error, since this is unlikely what you meant.
 3212        % X is not actually selected here, it MUST be part of the where clause. Further, it MUST contain 'VALUE'.
 3213        % For now, unify X with the target (Value). However, we must do it after the select. Consider:
 3214        % foo(X):- {[], some_table :: [integer_value-X], X > 500}
 3215        % This should generate
 3216        %   SELECT .... FROM some_table WHERE integer_value > 500
 3217        % What should we actually do with X? If it is bound, then we can make it
 3218        %    SELECT .... FROM some_table WHERE integer_value > 500 AND integer_value = ?
 3219        % If it is NOT bound, then I think this is actually an instantiation error?
 3220        % The trouble comes when we have
 3221        % {[], update(some_table, [...]), @ :: [integer_value-X], X == Y}.
 3222        %   If Y is unbound here, we want a runtime error, but if X is bound, we want an error as well!
 3223        ( Type == update ; Type == delete ),
 3224        ( Lhs == Variable ; Rhs == Variable )
 3225        |
 3226        runtime_instantiation_check(QueryId, Variable).
 3227
 3228
 3229cqlv2_variable_is_ok @
 3230        create_restrictions
 3231        \
 3232        cql2_variable(QueryId, Variable, Comparison)
 3233        <=>
 3234        where_restriction_variable(Variable),
 3235        restriction_leaf(QueryId, where, Comparison).
 3236
 3237
 3238
 3239
 3240
 3241
 3242variables_to_attributes @
 3243        attribute_binding(_, A, Variable) \ variables_to_attributes(Variable, Attribute) <=> var(Variable) | Attribute = A, where_restriction_variable(Variable).
 3244        variables_to_attributes(T1, T2) <=> var(T1) ; ground(T1) | T2 = T1.
 3245        variables_to_attributes([H1|T1], T3) <=> variables_to_attributes(H1, H2), variables_to_attributes(T1, T2), T3 = [H2|T2].
 3246        variables_to_attributes(T1, T4) <=> T1 =.. T2, variables_to_attributes(T2, T3), T4 =.. T3.
 3247
 3248
 3249cleanup_create_restrictions @
 3250        create_restrictions
 3251        <=>
 3252        true.
 3253
 3254
 3255ignore_comparison_to_empty_list @
 3256        restriction_leaf(_, _, comparison(Lhs, ==, Rhs))
 3257        <=>
 3258        ( Lhs == []
 3259        ; Rhs == []
 3260        )
 3261        |
 3262        true.
 3263
 3264
 3265top_level_restriction_cannot_refer_to_a_sub_query_attribute @
 3266        query(TopLevelQueryId, _, top_level_query),
 3267        query(SubQueryId, _, sub_query),
 3268        attribute_binding(SubQueryId, attribute(Schema, TableAlias, AttributeName), _)
 3269        \
 3270        restriction_leaf(TopLevelQueryId, _, comparison(Lhs, _, Rhs))
 3271        <=>
 3272        ( cql_path_arg(_, Lhs, SubTerm)
 3273        ; cql_path_arg(_, Rhs, SubTerm)),
 3274        SubTerm == attribute(Schema, TableAlias, AttributeName)
 3275        |
 3276        throw(format('Top level restriction cannot refer to a sub-query attribute : ~w', [AttributeName])).
 3277
 3278
 3279simplify_or_restriction @
 3280        % Trivial "or" conditions can arise out from the simplification of disjunctions
 3281        restriction_tree(QueryId, RestrictionType, or(Lhs, Rhs))
 3282        <=>
 3283        ( Lhs == true
 3284        ; Rhs == true
 3285        )
 3286        |
 3287        restriction_tree(QueryId, RestrictionType, true).
 3288
 3289
 3290simplify_and_restriction @
 3291        restriction_tree(QueryId, RestrictionType, and(Lhs, Rhs))
 3292        <=>
 3293        ( Lhs == true ->
 3294            RestrictionTree = Rhs
 3295
 3296        ; Rhs == true ->
 3297            RestrictionTree = Lhs
 3298        )
 3299        |
 3300        restriction_tree(QueryId, RestrictionType, RestrictionTree).
 3301
 3302
 3303add_to_restriction_tree @
 3304        restriction_tree(QueryId, RestrictionType, ExistingRestrictionTree),
 3305        restriction_leaf(QueryId, RestrictionType, Restriction)
 3306        <=>
 3307        restriction_tree(QueryId, RestrictionType, and(ExistingRestrictionTree, Restriction)).
 3308
 3309
 3310insert_inserted_ @
 3311        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3312        <=>
 3313        cql_data_type(Schema, TableName, inserted_, _, _, _, _, _, _, _),
 3314        \+ memberchk(inserted_-_, AttributeNameValuePairs)
 3315        |
 3316        insert(QueryId, Schema, TableName, [inserted_-{timestamp}|AttributeNameValuePairs]).
 3317
 3318
 3319insert_inserted_by_ @
 3320        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3321        <=>
 3322        cql_data_type(Schema, TableName, inserted_by_, _, _, _, _, _, _, _),
 3323        \+ memberchk(inserted_by_-_, AttributeNameValuePairs)
 3324        |
 3325        insert(QueryId, Schema, TableName, [inserted_by_-{user_id}|AttributeNameValuePairs]).
 3326
 3327
 3328insert_updated_ @
 3329        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3330        <=>
 3331        cql_data_type(Schema, TableName, updated_, _, _, _, _, _, _, _),
 3332        \+ memberchk(updated_-_, AttributeNameValuePairs)
 3333        |
 3334        insert(QueryId, Schema, TableName, [updated_-{timestamp}|AttributeNameValuePairs]).
 3335
 3336
 3337insert_updated_by_ @
 3338        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3339        <=>
 3340        cql_data_type(Schema, TableName, updated_by_, _, _, _, _, _, _, _),
 3341        \+ memberchk(updated_by_-_, AttributeNameValuePairs)
 3342        |
 3343        insert(QueryId, Schema, TableName, [updated_by_-{user_id}|AttributeNameValuePairs]).
 3344
 3345
 3346update_updated_ @
 3347        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3348        <=>
 3349        cql_data_type(Schema, TableName, updated_, _, _, _, _, _, _, _),
 3350        \+ memberchk(updated_-_, AttributeNameValuePairs)
 3351        |
 3352        update(QueryId, Schema, TableName, TableAlias, [updated_-{timestamp}|AttributeNameValuePairs]).
 3353
 3354
 3355update_updated_by_ @
 3356        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3357        <=>
 3358        cql_data_type(Schema, TableName, updated_by_, _, _, _, _, _, _, _),
 3359        \+ memberchk(updated_by_-_, AttributeNameValuePairs)
 3360        |
 3361        update(QueryId, Schema, TableName, TableAlias, [updated_by_-{user_id}|AttributeNameValuePairs]).
 3362
 3363
 3364insert_transaction_id_ @
 3365        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3366        <=>
 3367        cql_data_type(Schema, TableName, transaction_id_, _, _, _, _, _, _, _),
 3368        \+ memberchk(transaction_id_-_, AttributeNameValuePairs)
 3369        |
 3370        insert(QueryId, Schema, TableName, [transaction_id_-{transaction_id}|AttributeNameValuePairs]).
 3371
 3372
 3373update_transaction_id_ @
 3374        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3375        <=>
 3376        cql_data_type(Schema, TableName, transaction_id_, _, _, _, _, _, _, _),
 3377        \+ memberchk(transaction_id_-_, AttributeNameValuePairs)
 3378        |
 3379        update(QueryId, Schema, TableName, TableAlias, [transaction_id_-{transaction_id}|AttributeNameValuePairs]).
 3380
 3381
 3382insert_generation_ @
 3383        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3384        <=>
 3385        cql_data_type(Schema, TableName, generation_, _, _, _, _, _, _, _),
 3386        \+ memberchk(generation_-_, AttributeNameValuePairs)
 3387        |
 3388        insert(QueryId, Schema, TableName, [generation_-0|AttributeNameValuePairs]).
 3389
 3390
 3391update_generation_ @
 3392        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3393        <=>
 3394        cql_data_type(Schema, TableName, generation_, _, _, _, _, _, _, _),
 3395        \+ memberchk(generation_-_, AttributeNameValuePairs)
 3396        |
 3397        update(QueryId, Schema, TableName, TableAlias, [generation_-{increment}|AttributeNameValuePairs]).
 3398
 3399
 3400write_insert_based_on_select @
 3401        query_table_alias(QueryId, _, _, _),
 3402        phase(QueryId, initial)
 3403        \
 3404        insert(QueryId, _, TableName, AttributeNameValuePairs)
 3405        <=>
 3406        extract_variables(AttributeNameValuePairs, InsertVariables),
 3407        select_for_insert_variables(InsertVariables, TableName),
 3408        write_sql(QueryId, compile, top, ['INSERT INTO ', table_name(TableName), ' ('|T1], T1, [], []),
 3409        write_insert_attribute_names(QueryId, AttributeNameValuePairs),
 3410        write_sql(QueryId, compile, top, [') '|T2], T2, [], []).
 3411
 3412
 3413
 3414extract_variables([], []).
 3415
 3416extract_variables([_-V|AttributeNameValuePairs], [V|InsertVariables]) :-
 3417        var(V), !,
 3418        extract_variables(AttributeNameValuePairs, InsertVariables).
 3419
 3420extract_variables([_|AttributeNameValuePairs], InsertVariables) :-
 3421        extract_variables(AttributeNameValuePairs, InsertVariables).
 3422
 3423
 3424write_insert @
 3425        phase(QueryId, initial),
 3426        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3427        <=>
 3428        ( AttributeNameValuePairs == []->
 3429            write_sql(QueryId, compile, top, ['INSERT INTO ', table_name(TableName), ' DEFAULT VALUES'|T1], T1, [], [])
 3430        ;
 3431            write_sql(QueryId, compile, top, ['INSERT INTO ', table_name(TableName), ' ('|T2], T2, [], []),
 3432            write_insert_attribute_names(QueryId, AttributeNameValuePairs),
 3433            write_sql(QueryId, compile, top, [') VALUES ('|T3], T3, [], []),
 3434            write_insert_values(QueryId, Schema, TableName, AttributeNameValuePairs),
 3435            ( dbms(Schema, 'PostgreSQL'),
 3436              database_identity(Schema, TableName, PrimaryKey) ->
 3437                write_sql(QueryId, compile, top, [') RETURNING ', PrimaryKey|T4], T4, [], [])
 3438            ; otherwise->
 3439                write_sql(QueryId, compile, top, [')'|T4], T4, [], [])
 3440            )
 3441        ).
 3442
 3443
 3444write_insert_attribute_names_1 @
 3445        write_insert_attribute_names(QueryId, [AttributeName-_])
 3446        <=>
 3447        write_insert_attribute_name(QueryId, AttributeName).
 3448
 3449
 3450write_insert_attribute_names_2 @
 3451        write_insert_attribute_names(QueryId, [AttributeName-_|AttributeNameValuePairs])
 3452        <=>
 3453        write_insert_attribute_name(QueryId, AttributeName),
 3454        write_sql(QueryId, compile, top, [', '|T], T, [], []),
 3455        write_insert_attribute_names(QueryId, AttributeNameValuePairs).
 3456
 3457
 3458write_insert_attribute_name @
 3459        write_insert_attribute_name(QueryId, AttributeName)
 3460        <=>
 3461        write_sql(QueryId, compile, top, [attribute_name(AttributeName)|T], T, [], []).
 3462
 3463
 3464write_insert_values_1 @
 3465        write_insert_values(QueryId, Schema, TableName, [AttributeName-ApplicationValue])
 3466        <=>
 3467        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue).
 3468
 3469
 3470write_insert_values_2 @
 3471        write_insert_values(QueryId, Schema, TableName, [AttributeName-ApplicationValue|AttributeNameValuePairs])
 3472        <=>
 3473        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue),
 3474        write_sql(QueryId, compile, top, [', '|T], T, [], []),
 3475        write_insert_values(QueryId, Schema, TableName, AttributeNameValuePairs).
 3476
 3477
 3478write_in_line_formatted_insert_value @
 3479        write_insert_value(QueryId, Schema, TableName, AttributeName, format(Format, FormatArgs))
 3480        <=>
 3481        in_line_format(QueryId, Format, FormatArgs, ApplicationValue),
 3482        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue).
 3483
 3484
 3485write_insert_value @
 3486        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue)
 3487        <=>
 3488        write_sql(QueryId, compile, top, [?|T], T, [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, insert_value, _)], []).
 3489
 3490
 3491write_update @
 3492        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3493        \
 3494        phase(QueryId, initial)
 3495        <=>
 3496        ( dbms(Schema, 'Microsoft SQL Server') ->
 3497            write_sql(QueryId, compile, top, ['UPDATE ', TableAlias, ' SET '|T], T, [], [])
 3498
 3499        ; dbms(Schema, 'PostgreSQL') ->
 3500            write_sql(QueryId, compile, top, ['UPDATE ', TableName, ' ', TableAlias, ' SET '|T], T, [], [])
 3501        ; dbms(Schema, 'SQLite') ->
 3502            % SQLite does not support joins in updates. However, it has an ID for each row, meaning we can put this:
 3503            % UPDATE <tablename> SET <columns without aliases> WHERE rowid IN (SELECT <tablename>.rowid FROM <rest of the query>)
 3504            write_sql(QueryId, compile, top, ['UPDATE ', TableName, ' SET '|T], T, [], [])
 3505        ),
 3506        write_update_attributes(QueryId, TableAlias, AttributeNameValuePairs),
 3507        phase(QueryId, from).
 3508
 3509
 3510write_update_attributes_1 @
 3511        write_update_attributes(QueryId, TableAlias, [AttributeName-ApplicationValue])
 3512        <=>
 3513        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue).
 3514
 3515
 3516write_update_attributes_2 @
 3517        write_update_attributes(QueryId, TableAlias, [AttributeName-ApplicationValue|AttributeNameValuePairs])
 3518        <=>
 3519        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue),
 3520        write_sql(QueryId, compile, top, [', '|T], T, [], []),
 3521        write_update_attributes(QueryId, TableAlias, AttributeNameValuePairs).
 3522
 3523
 3524write_update_attributes_3 @
 3525        write_update_attributes(_, _, AttributeNameValuePairs)
 3526        <=>
 3527        throw(format('Bad UPDATE attributes: ~w',  [AttributeNameValuePairs])).
 3528
 3529
 3530write_generation_attribute @
 3531        update_table_alias(QueryId, Schema, _, TableAlias)
 3532        \
 3533        write_update_attribute(QueryId, TableAlias, AttributeName, {increment})
 3534        <=>
 3535        AttributeName == generation_
 3536        |
 3537        ( dbms(Schema, 'Microsoft SQL Server') ->
 3538            write_sql(QueryId,
 3539                      compile,
 3540                      top,
 3541                      [TableAlias, '.', attribute_name(AttributeName), =, TableAlias, '.', attribute_name(AttributeName), +, '1'|T],
 3542                      T,
 3543                      [],
 3544                      [])
 3545        ; dbms(Schema, 'PostgreSQL') ->
 3546            write_sql(QueryId, compile,
 3547                      top,
 3548                      [attribute_name(AttributeName), =, TableAlias, '.', attribute_name(AttributeName), +, '1'|T],
 3549                      T,
 3550                      [],
 3551                      [])
 3552        ; dbms(Schema, 'SQLite') ->
 3553            write_sql(QueryId, compile,
 3554                      top,
 3555                      [attribute_name(AttributeName), =, attribute_name(AttributeName), +, '1'|T],
 3556                      T,
 3557                      [],
 3558                      [])
 3559        ).
 3560
 3561
 3562write_update_attribute_copy_sql_server @
 3563        query_table_alias(QueryId, Schema, _, TableAlias),
 3564        select_attribute(QueryId, select_attribute(_, _, _, SelectTableAlias, SelectAttributeName), 1, SelectAttributeVariableUsed, SelectVariable)
 3565        \
 3566        write_update_attribute(QueryId, TableAlias, AttributeName, UpdateVariable)
 3567        <=>
 3568        dbms(Schema, 'Microsoft SQL Server'),
 3569        var(SelectVariable),
 3570        SelectVariable == UpdateVariable
 3571        |
 3572        SelectAttributeVariableUsed = select_attribute_variable_used,
 3573        write_sql(QueryId,
 3574                  compile,
 3575                  top,
 3576                  [TableAlias, '.', attribute_name(AttributeName), =, SelectTableAlias, '.', attribute_name(SelectAttributeName)|T],
 3577                  T,
 3578                  [],
 3579                  []).
 3580
 3581write_update_attribute_copy_postgres @
 3582        update_table_alias(QueryId, Schema, _, TargetAlias),
 3583        query_table_alias(QueryId, _, _, TableAlias),
 3584        select_attribute(QueryId, select_attribute(_, _, _, SelectTableAlias, SelectAttributeName), 1, SelectAttributeVariableUsed, SelectVariable)
 3585        \
 3586        write_update_attribute(QueryId, TableAlias, AttributeName, UpdateVariable)
 3587        <=>
 3588        dbms(Schema, 'PostgreSQL'),
 3589        var(SelectVariable),
 3590        SelectVariable == UpdateVariable
 3591        |
 3592        SelectAttributeVariableUsed = select_attribute_variable_used,
 3593
 3594        ( TargetAlias == TableAlias ->
 3595            write_sql(QueryId,
 3596                      compile,
 3597                      top,
 3598                      [attribute_name(AttributeName), =, SelectTableAlias, '.', attribute_name(SelectAttributeName)|T],
 3599                      T,
 3600                      [],
 3601                      [])
 3602        ; otherwise->
 3603            write_sql(QueryId,
 3604                      compile,
 3605                      top,
 3606                      [TableAlias, '.', attribute_name(AttributeName), =, SelectTableAlias, '.', attribute_name(SelectAttributeName)|T],
 3607                      T,
 3608                      [],
 3609                      [])
 3610
 3611        ).
 3612
 3613
 3614write_update_attribute_copy_sqlite @
 3615        update_table_alias(QueryId, Schema, _, TargetAlias),
 3616        query_table_alias(QueryId, Schema, TableName, TableAlias),
 3617        select_attribute(QueryId, select_attribute(_, _, _, SelectTableAlias, SelectAttributeName), 1, SelectAttributeVariableUsed, SelectVariable)
 3618        \
 3619        write_update_attribute(QueryId, TableAlias, AttributeName, UpdateVariable)
 3620        <=>
 3621        dbms(Schema, 'SQLite'),
 3622        var(SelectVariable),
 3623        SelectVariable == UpdateVariable
 3624        |
 3625        SelectAttributeVariableUsed = select_attribute_variable_used,
 3626        ( TargetAlias == SelectTableAlias->
 3627            % This is the simplest case. Otherwise we must write a subquery
 3628            write_sql(QueryId,
 3629                      compile,
 3630                      top,
 3631                      [attribute_name(AttributeName), =, attribute_name(SelectAttributeName)|T],
 3632                      T,
 3633                      [],
 3634                      [])
 3635        ; otherwise->
 3636            write_sql(QueryId,
 3637                      compile,
 3638                      top,
 3639                      [attribute_name(AttributeName), =, '(SELECT ', SelectTableAlias, '.', attribute_name(SelectAttributeName), ' '|T],
 3640                      T,
 3641                      [],
 3642                      []),
 3643          Tail = [' AND ', TableAlias, '.rowid = ', TableName, '.rowid)'|T3],
 3644          write_sql(QueryId,
 3645                    compile,
 3646                    top,
 3647                    X,
 3648                    T3,
 3649                    Parameters,
 3650                    []),
 3651          find_copy_of_from(QueryId, X, Tail, Parameters)
 3652        ).
 3653
 3654write_in_line_formatted_update_attribute @
 3655        query_table_alias(QueryId, _, _, TableAlias)
 3656        \
 3657        write_update_attribute(QueryId, TableAlias, AttributeName, format(Format, FormatArgs))
 3658        <=>
 3659        in_line_format(QueryId, Format, FormatArgs, ApplicationValue),
 3660        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue).
 3661
 3662:-multifile(cql_atomic_value_check_hook/1). 3663write_atomic_update_attribute @
 3664        query_table_alias(QueryId, Schema, TableName, TableAlias)
 3665        \
 3666        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue)
 3667        <=>
 3668        ( var(ApplicationValue)
 3669        ; atom(ApplicationValue)
 3670        ; integer(ApplicationValue)
 3671        ; rational(ApplicationValue)
 3672        ; cql_atomic_value_check_hook(ApplicationValue)
 3673        ; ApplicationValue == {null}
 3674        ; ApplicationValue == {timestamp}
 3675        ; ApplicationValue == {user_id}
 3676        ; ApplicationValue == {transaction_id}
 3677        )
 3678        |
 3679        ( dbms(Schema, 'Microsoft SQL Server') ->
 3680            write_sql(QueryId,
 3681                      compile,
 3682                      top,
 3683                      [TableAlias, '.', attribute_name(AttributeName), =, ?|T],
 3684                      T,
 3685                      [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, update_value, _)],
 3686                      [])
 3687        ; dbms(Schema, 'PostgreSQL') ->
 3688            write_sql(QueryId,
 3689                      compile,
 3690                      top,
 3691                      [attribute_name(AttributeName), =, ?|T],
 3692                      T,
 3693                      [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, update_value, _)],
 3694                      [])
 3695        ; dbms(Schema, 'SQLite') ->
 3696            write_sql(QueryId,
 3697                      compile,
 3698                      top,
 3699                      [attribute_name(AttributeName), =, ?|T],
 3700                      T,
 3701                      [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, update_value, _)],
 3702                      [])
 3703
 3704        ).
 3705
 3706write_evaluated_update_attribute @
 3707        query_table_alias(QueryId, Schema, TableName, TableAlias)
 3708        \
 3709        write_update_attribute(QueryId, TableAlias, AttributeName, Expression)
 3710        <=>
 3711        ( dbms(Schema, 'Microsoft SQL Server') ; dbms(Schema, 'PostgreSQL') )
 3712        |
 3713        ( dbms(Schema, 'Microsoft SQL Server') ->
 3714            write_sql(QueryId,
 3715                      compile,
 3716                      top,
 3717                      [TableAlias, '.', attribute_name(AttributeName), =|T],
 3718                      T,
 3719                      [odbc_parameter(Schema, TableName, AttributeName, {null}, evaluated_update_attribute, _)],
 3720                      [])
 3721        ; dbms(Schema, 'PostgreSQL') ->
 3722            write_sql(QueryId,
 3723                      compile,
 3724                      top,
 3725                      [attribute_name(AttributeName), =|T],
 3726                      T,
 3727                      [odbc_parameter(Schema, TableName, AttributeName, {null}, evaluated_update_attribute, _)],
 3728                      [])
 3729        ),
 3730        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Expression).
 3731
 3732write_evaluated_update_attribute_sqlite @
 3733        query_table_alias(QueryId, Schema, TableName, TableAlias)
 3734        \
 3735        write_update_attribute(QueryId, TableAlias, AttributeName, Expression)
 3736        <=>
 3737        dbms(Schema, 'SQLite')
 3738        |
 3739        % SQLite does not support the above method. Instead of Expression, we must actually put the entire FROM/WHERE clause here in a subquery like
 3740        % attribute_name(AttributeName) = SELECT(Expression FROM .......)
 3741        write_sql(QueryId,
 3742                  compile,
 3743                  top,
 3744                  [attribute_name(AttributeName), =, '(SELECT '|T],
 3745                  T,
 3746                  [odbc_parameter(Schema, TableName, AttributeName, {null}, evaluated_update_attribute, _)],
 3747                  []),
 3748        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Expression),
 3749        Tail = [' AND ', TableAlias, '.rowid = ', TableName, '.rowid)'|T3],
 3750        write_sql(QueryId,
 3751                    compile,
 3752                    top,
 3753                    X,
 3754                    T3,
 3755                    Parameters,
 3756                    []),
 3757        find_copy_of_from(QueryId, X, Tail, Parameters).
 3758
 3759
 3760write_evaluated_update_binary_expression @
 3761        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Expression)
 3762        <=>
 3763        nonvar(Expression),
 3764        functor(Expression, Operator, 2),
 3765        memberchk(Operator, [+, -, *, /])
 3766        |
 3767        arg(1, Expression, Lhs),
 3768        arg(2, Expression, Rhs),
 3769        ( dbms(Schema, 'SQLite') ->
 3770            % SQLite defaults to integer arithmetic. Fix this here
 3771            write_sql(QueryId, compile, top, ['(1.0*'|T1], T1, [], [])
 3772        ; otherwise->
 3773            write_sql(QueryId, compile, top, ['('|T1], T1, [], [])
 3774        ),
 3775        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Lhs),
 3776        write_sql(QueryId, compile, top, [' ', Operator, ' '|T2], T2, [], []),
 3777        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Rhs),
 3778        ( dbms(Schema, 'SQLite') ->
 3779            % SQLite defaults to integer arithmetic.
 3780            write_sql(QueryId, compile, top, ['*1.0)'|T3], T3, [], [])
 3781        ; otherwise->
 3782            write_sql(QueryId, compile, top, [')'|T3], T3, [], [])
 3783        ).
 3784
 3785write_evaluated_update_expression_attribute @
 3786        select_attribute(QueryId, select_attribute(_, _, _, TableAlias, AttributeName), 1, SelectAttributeVariableUsed, Variable)
 3787        \
 3788        write_expression(QueryId, _, _, _, _, Variable)
 3789        <=>
 3790        SelectAttributeVariableUsed = select_attribute_variable_used,
 3791        write_sql(QueryId, compile, top, [TableAlias, '.', attribute_name(AttributeName)|T], T, [], []).
 3792
 3793write_evaluated_update_expression_constant @
 3794        write_expression(QueryId, _, _, _, _, Constant)
 3795        <=>
 3796        integer(Constant)
 3797        |
 3798        atom_number(ConstantAtom, Constant),
 3799        write_sql(QueryId, compile, top, [ConstantAtom|T], T, [], []).
 3800
 3801write_evaluated_update_expression_parameter @
 3802        write_expression(QueryId, Schema, TableName, AttributeName, _, Variable)
 3803        <=>
 3804        var(Variable)
 3805        |
 3806        write_sql(QueryId,
 3807                  compile,
 3808                  top,
 3809                  [?|T],
 3810                  T,
 3811                  [odbc_parameter(Schema, TableName, AttributeName, Variable, evaluated_update_parameter, _)],
 3812                  []).
 3813
 3814
 3815write_evaluated_update_table_expression_attribute @
 3816        write_expression(QueryId, _, _, _, TableAlias, AttributeName)
 3817        <=>
 3818        write_sql(QueryId, compile, top, [TableAlias, '.', attribute_name(AttributeName)|T], T, [], []).
 3819
 3820
 3821write_delete_with_no_where_clause @
 3822        restriction_tree(QueryId, where, true)
 3823        \
 3824        phase(QueryId, initial),
 3825        delete_row(QueryId, TableName, _)
 3826        <=>
 3827        write_sql(QueryId, compile, top, ['DELETE'|T1], T1, [], []),
 3828        write_sql(QueryId, compile, join, [' FROM ', table_name(TableName)|T2], T2, [], []),
 3829        phase(QueryId, where).
 3830
 3831
 3832write_delete_with_where_clause @
 3833        phase(QueryId, initial),
 3834        delete_row(QueryId, TableName, TableAlias)
 3835        <=>
 3836        TableAlias = TableName,
 3837        write_sql(QueryId, compile, top, ['DELETE'|T1], T1, [], []),
 3838        write_sql(QueryId, compile, join, [' FROM ', TableAlias|T2], T2, [], []),
 3839        phase(QueryId, where).
 3840
 3841
 3842write_select_keyword @
 3843        phase(QueryId, initial)
 3844        <=>
 3845        write_sql(QueryId, compile, top, ['SELECT '|T], T, [], []),
 3846        phase(QueryId, distinct).
 3847
 3848
 3849write_select_distinct @
 3850        select_distinction(QueryId, DistinctionType)
 3851        \
 3852        phase(QueryId, distinct)
 3853        <=>
 3854        DistinctionType \== no_distinction
 3855        |
 3856        write_sql(QueryId, compile, top, ['DISTINCT '|T], T, [], []),
 3857        phase(QueryId, top).
 3858
 3859
 3860no_distinct @
 3861        phase(QueryId, distinct)
 3862        <=>
 3863        phase(QueryId, top).
 3864
 3865
 3866should_not_be_any_distinct_constraints_left_over @
 3867        check_for_orphan_distincts,
 3868        distinct(_, Distinct),
 3869        original_cql(Cql)
 3870        <=>
 3871        throw(format('Unused DISTINCT ~w in CQL: ~w', [Distinct, Cql])).
 3872
 3873select_variable_is_used_if_its_a_where_variable @
 3874        select_attribute(_, _, 1, SelectAttributeVariableUsed, Variable),
 3875        where_restriction_variable(Variable)
 3876        ==>
 3877        SelectAttributeVariableUsed = select_attribute_variable_used.
 3878
 3879select_variable_is_used_if_its_a_join_variable @
 3880        select_attribute(_, _, 1, SelectAttributeVariableUsed, Variable),
 3881        join_variable(Variable)
 3882        ==>
 3883        SelectAttributeVariableUsed = select_attribute_variable_used.
 3884
 3885
 3886select_variable_is_used_if_its_a_sub_query_join_variable @
 3887        select_attribute(_, _, 1, SelectAttributeVariableUsed, Variable),
 3888        sub_query_join_variable(Variable)
 3889        ==>
 3890        SelectAttributeVariableUsed = select_attribute_variable_used.
 3891
 3892check_for_orphan_select_variables_in_updates @
 3893        check_for_orphan_select_variables_in_updates,
 3894        query_type(QueryId, update),
 3895        select_attribute(QueryId, _, 1, SelectAttributeVariableUsed, Variable),
 3896        original_cql(Cql)
 3897        <=>
 3898        var(SelectAttributeVariableUsed)
 3899        |
 3900        throw(format('Unused SELECT variable ~w in UPDATE in CQL: ~w', [Variable, Cql])).
 3901
 3902original_cql_uniqueness @
 3903        original_cql(Cql)
 3904        \
 3905        original_cql(Cql)
 3906        <=>
 3907        true.
 3908
 3909
 3910statement_location_uniqueness @
 3911        cql_statement_location(FileName, LineNumber)
 3912        \
 3913        cql_statement_location(FileName, LineNumber)
 3914        <=>
 3915        true.
 3916
 3917
 3918top_n_error @
 3919        top(_, _, N),
 3920        original_cql(Cql)
 3921        ==>
 3922        \+ var(N),
 3923        \+ (integer(N), N >= 0)
 3924        |
 3925        throw(format('The N in top(N) must be an integer and not less than zero but found ~w in CQL: ~w', [N, Cql])).
 3926
 3927
 3928write_select_top_n @
 3929        phase(QueryId, top),
 3930        top(QueryId, Schema, N)
 3931        <=>
 3932        ( dbms(Schema, 'Microsoft SQL Server') ->
 3933            ( var(N) ->
 3934                write_sql(QueryId,
 3935                          compile,
 3936                          top,
 3937                          ['TOP (?) '|T],
 3938                          T,
 3939                          [odbc_explicit_type_parameter(integer, N, top_value)],
 3940                          [])
 3941            ; otherwise->
 3942                write_sql(QueryId, compile, top, ['TOP ', N, ' '|T], T, [], [])
 3943            )
 3944        ; dbms(Schema, 'PostgreSQL') ->
 3945            limit(QueryId, Schema, N)
 3946        ; dbms(Schema, 'SQLite') ->
 3947            limit(QueryId, Schema, N)
 3948        ),
 3949        phase(QueryId, select_attributes).
 3950
 3951
 3952no_top @
 3953        phase(QueryId, top)
 3954        <=>
 3955        phase(QueryId, select_attributes).
 3956
 3957
 3958write_limit @
 3959        write_limit,
 3960        limit(QueryId, Schema, N)
 3961        <=>
 3962        ( ( dbms(Schema, 'PostgreSQL') ; dbms(Schema, 'SQLite') )->
 3963            (var(N)->
 3964               write_sql(QueryId,
 3965                         compile,
 3966                          where,
 3967                         [' LIMIT (?) '|T],
 3968                         T,
 3969                         [odbc_explicit_type_parameter(integer, N, top_value)],
 3970                         [])
 3971            ; otherwise->
 3972                write_sql(QueryId, compile, where, [' LIMIT ', N, ' '|T], T, [], [])
 3973            )
 3974        ; otherwise->
 3975           true).
 3976
 3977no_limit @
 3978        write_limit
 3979        <=>
 3980        true.
 3981
 3982write_sub_query_select @
 3983        sub_query_select(QueryId)
 3984        \
 3985        phase(QueryId, select_attributes)
 3986        <=>
 3987        write_sql(QueryId, compile, top, [*|T], T, [], []),
 3988        phase(QueryId, from).
 3989
 3990
 3991write_select_for_insert_attributes @
 3992        phase(QueryId, select_attributes),
 3993        query_table_alias(QueryId, _, _, _)
 3994        \
 3995        select_for_insert_variables([Variable|InsertVariables], InsertTableName)
 3996        <=>
 3997        select_for_insert_variable(QueryId, Variable, InsertTableName),
 3998        select_for_insert_variables(InsertVariables, InsertTableName).
 3999
 4000
 4001write_select_for_insert_attributes_complete @
 4002        phase(QueryId, select_attributes),
 4003        select_for_insert_variables([], _)
 4004        <=>
 4005        phase(QueryId, from).
 4006
 4007
 4008write_select_for_insert_count @
 4009        select_for_insert_variable(QueryId, Variable, _),
 4010        select_attribute(QueryId, select_attribute(aggregation(count), _, _, TableAlias, AttributeName), 1, _, Variable)
 4011        <=>
 4012        write_select_attribute(QueryId,
 4013                               compile,
 4014                               ['count(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4015                               T,
 4016                               ignore_output).
 4017
 4018
 4019write_select_for_insert_aggregation @
 4020        select_for_insert_variable(QueryId, Variable, _),
 4021        select_attribute(QueryId, select_attribute(aggregation(AggregationOperator), _, _, TableAlias, AttributeName), 1, _, Variable)
 4022        <=>
 4023        % Why did someone try and do this?!
 4024        %map_database_atom(AggregationOperator, AggregationOperatorUc),
 4025        write_select_attribute(QueryId,
 4026                               compile,
 4027                               [AggregationOperator, '(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4028                               T,
 4029                               ignore_output).
 4030
 4031
 4032write_select_for_insert_select_constant @
 4033        select_for_insert_variable(QueryId, Variable, InsertTableName),
 4034        select_attribute(QueryId, select_attribute(plain, Schema, _, _, AttributeName), 1, _, selection_constant(Variable))
 4035        <=>
 4036        write_select_attribute(QueryId,
 4037                               compile,
 4038                               ['? AS ', attribute_name(AttributeName)|T],
 4039                               T,
 4040                               selection_constant(Schema, InsertTableName, AttributeName, Variable)).
 4041
 4042
 4043write_select_for_insert_plain_attribute @
 4044        select_for_insert_variable(QueryId, Variable, _),
 4045        select_attribute(QueryId, select_attribute(plain, _, _, TableAlias, AttributeName), 1, _, Variable)
 4046        <=>
 4047        write_select_attribute(QueryId,
 4048                               compile,
 4049                               [TableAlias, '.', attribute_name(AttributeName)|T],
 4050                               T,
 4051                               ignore_output).
 4052
 4053
 4054write_select_attributes @
 4055        select_attribute(QueryId, _, _, _, _)
 4056        \
 4057        phase(QueryId, select_attributes)
 4058        <=>
 4059        write_select_attributes(QueryId),
 4060        phase(QueryId, from).
 4061
 4062
 4063write_do_nothing_select @
 4064        phase(QueryId, select_attributes)
 4065        <=>
 4066        write_sql(QueryId, compile, top, [0|T], T, [], [ignore_output]),
 4067        % no human
 4068        phase(QueryId, from).
 4069
 4070
 4071write_select_count_attribute @
 4072        write_select_attributes(QueryId),
 4073        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4074        \
 4075        select_attribute(QueryId, select_attribute(aggregation(count), Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4076        <=>
 4077        get_data_size(Schema, TableName, AttributeName, Size),
 4078        include_select_attribute(QueryId,
 4079                                 compile,
 4080                                 Size,
 4081                                 ['count(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4082                                 T,
 4083                                 count(Variable)).
 4084
 4085write_select_avg_attribute_for_postgres @
 4086        write_select_attributes(QueryId),
 4087        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4088        \
 4089        select_attribute(QueryId, select_attribute(aggregation(avg), Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4090        <=>
 4091        dbms(Schema, 'PostgreSQL')
 4092        |
 4093        %map_database_atom(avg, AggregationOperatorUc),
 4094        upcase_atom(avg, AggregationOperatorUc),
 4095        get_data_size(Schema, TableName, AttributeName, Size),
 4096        include_select_attribute(QueryId,
 4097                                 compile,
 4098                                 Size,
 4099                                 [AggregationOperatorUc, '(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4100                                 T,
 4101                                 avg(Variable)).
 4102
 4103write_select_aggregation_attribute @
 4104        write_select_attributes(QueryId),
 4105        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4106        \
 4107        select_attribute(QueryId, select_attribute(aggregation(AggregationOperator), Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4108        <=>
 4109        get_data_size(Schema, TableName, AttributeName, Size),
 4110        %map_database_atom(AggregationOperator, AggregationOperatorUc),
 4111        upcase_atom(AggregationOperator, AggregationOperatorUc),
 4112        include_select_attribute(QueryId,
 4113                                 compile,
 4114                                 Size,
 4115                                 [AggregationOperatorUc, '(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4116                                 T,
 4117                                 output(Schema, TableName, AttributeName, Variable)).
 4118
 4119
 4120write_top_level_select_attribute @
 4121        write_select_attributes(QueryId),
 4122        query(QueryId, _, top_level_query),
 4123        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4124        \
 4125        select_attribute(QueryId, select_attribute(plain, Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4126        <=>
 4127        get_data_size(Schema, TableName, AttributeName, Size),
 4128        include_select_attribute(QueryId,
 4129                                 if_var(Variable),
 4130                                 Size,
 4131                                 [TableAlias, '.', attribute_name(AttributeName)|T],
 4132                                 T,
 4133                                 output(Schema, TableName, AttributeName, Variable)).
 4134
 4135
 4136
 4137flush_select_attributes @
 4138        write_select_attributes(QueryId)
 4139        ==>
 4140        collect_select_attributes(QueryId, Unsorted),
 4141        keysort(Unsorted, SortedWithKeys),
 4142        cql_strip_sort_keys(SortedWithKeys, Sorted),
 4143        actually_write_select_attributes(QueryId, compile, Sorted).
 4144
 4145collect_select_attributes @
 4146        collect_select_attributes(QueryId, Tail),
 4147        include_select_attribute(QueryId, CompileInstruction, Size, Tokens, TokenTail, Attribute)
 4148        <=>
 4149        Tail = [Size-select_info(CompileInstruction, Tokens, TokenTail, Attribute)|NewTail],
 4150        collect_select_attributes(QueryId, NewTail).
 4151
 4152finished_collecting_select_attributes @
 4153        collect_select_attributes(_, Tail)
 4154        <=>
 4155        Tail = [].
 4156
 4157actually_write_select_attributes(_, _, []).
 4158actually_write_select_attributes(QueryId, _PreviousCompileInstruction, [select_info(CompileInstruction, Tokens, Tail, Attribute)|More]):-
 4159        %instruction_conjunction(PreviousCompileInstruction, CompileInstruction, Conjunction),
 4160        write_select_attribute(QueryId, CompileInstruction, Tokens, Tail, Attribute),
 4161        actually_write_select_attributes(QueryId, CompileInstruction, More).
 4162
 4163
 4164
 4165write_select_attribute_with_leading_comma @
 4166        select_attribute_written(QueryId)
 4167        \
 4168        write_select_attribute(QueryId, CompileInstruction, SqlTokens, Tail, Output)
 4169        <=>
 4170        write_sql(QueryId, CompileInstruction, top, [', '|T], T, [], []),
 4171        write_select_attribute_1(QueryId, CompileInstruction, SqlTokens, Tail, Output).
 4172
 4173
 4174write_select_attribute_without_leading_comma @
 4175        write_select_attribute(QueryId, _CompileInstruction, SqlTokens, Tail, Output)
 4176        <=>
 4177        write_select_attribute_1(QueryId, compile, SqlTokens, Tail, Output),
 4178        select_attribute_written(QueryId).
 4179
 4180
 4181write_select_attribute_1a @
 4182        write_select_attribute_1(QueryId, CompileInstruction, SqlTokens, Tail, selection_constant(Schema, TableName, AttributeName, ApplicationValue))
 4183        <=>
 4184        write_sql(QueryId,
 4185                  CompileInstruction,
 4186                  top,
 4187                  SqlTokens,
 4188                  Tail,
 4189                  [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, insert_value, _)],
 4190                  [ignore_output]).
 4191
 4192write_select_attribute_1c @
 4193        write_select_attribute_1(QueryId, CompileInstruction, SqlTokens, Tail, Output)
 4194        <=>
 4195        write_sql(QueryId, CompileInstruction, top, SqlTokens, Tail, [], [Output]).
 4196
 4197in_line_join_on @
 4198        create_in_line_joins,
 4199        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4200        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4201        ==>
 4202        dbms(Schema, 'Microsoft SQL Server'),
 4203        var(JoinVariableA),
 4204        JoinVariableA == JoinVariableB,
 4205        TableAliasA \== TableAliasB
 4206        |
 4207        join_variable(JoinVariableA),
 4208        join_variable(JoinVariableB),
 4209        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4210
 4211
 4212in_line_join_on @
 4213        create_in_line_joins,
 4214        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4215        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4216        ==>
 4217        dbms(Schema, 'SQLite'),
 4218        var(JoinVariableA),
 4219        JoinVariableA == JoinVariableB,
 4220        TableAliasA \== TableAliasB
 4221        |
 4222        join_variable(JoinVariableA),
 4223        join_variable(JoinVariableB),
 4224        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4225
 4226
 4227/* We can end up with selects mixed in with other types because of how
 4228   translate_select ends up getting called to parse the where clauses of
 4229   other types of query. In this case, we can simply delete all the selects.
 4230*/
 4231
 4232a_query_cannot_be_select_and_something_else @
 4233        query_type(QueryId, _)
 4234        \
 4235        query_type(QueryId, select)
 4236        <=>
 4237        true.
 4238
 4239
 4240in_line_join_on_postgres_select_insert_or_delete @
 4241        create_in_line_joins,
 4242        query_type(QueryId, QueryType),
 4243        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4244        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4245        ==>
 4246        dbms(Schema, 'PostgreSQL'),
 4247        memberchk(QueryType, [select, insert, delete]),
 4248        var(JoinVariableA),
 4249        JoinVariableA == JoinVariableB,
 4250        TableAliasA \== TableAliasB
 4251        |
 4252        join_variable(JoinVariableA),
 4253        join_variable(JoinVariableB),
 4254        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4255
 4256in_line_join_on_postgres_update_but_not_target @
 4257        create_in_line_joins,
 4258        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4259        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB),
 4260        update_table_alias(QueryId, _, _, TargetAlias)
 4261        ==>
 4262        dbms(Schema, 'PostgreSQL'),
 4263        var(JoinVariableA),
 4264        JoinVariableA == JoinVariableB,
 4265        TableAliasA \== TableAliasB,
 4266        TableAliasA \== TargetAlias,
 4267        TableAliasB \== TargetAlias
 4268        |
 4269        join_variable(JoinVariableA),
 4270        join_variable(JoinVariableB),
 4271        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4272
 4273in_line_join_on_postgres_and_is_target @
 4274        create_in_line_joins,
 4275        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4276        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB),
 4277        update_table_alias(QueryId, _, _, TargetAlias)
 4278        ==>
 4279        dbms(Schema, 'PostgreSQL'),
 4280        var(JoinVariableA),
 4281        JoinVariableA == JoinVariableB,
 4282        TableAliasA \== TableAliasB,
 4283        ( TableAliasA == TargetAlias ; TableAliasB == TargetAlias)
 4284        |
 4285        join_variable(JoinVariableA),
 4286        join_variable(JoinVariableB),
 4287        comparison(QueryId, attribute(Schema, TableAliasA, AttributeNameA), ==, attribute(Schema, TableAliasB, AttributeNameB)),
 4288        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4289
 4290cleanup_create_in_line_joins @
 4291        create_in_line_joins
 4292        <=>
 4293        true.
 4294
 4295explicit_join_on_postgres_update @
 4296        create_join_points,
 4297        update_table_alias(QueryId, _, _, TargetAlias),
 4298        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4299        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4300        \
 4301        % Use up the comparison/4 as we don't want it in the WHERE clause
 4302        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4303        <=>
 4304        dbms(Schema, 'PostgreSQL'),
 4305        var(JoinVariableA),
 4306        var(JoinVariableB),
 4307        TableAliasA \== TableAliasB, TableAliasA \== TargetAlias, TableAliasB \== TargetAlias
 4308        |
 4309        join_variable(JoinVariableA),
 4310        join_variable(JoinVariableB),
 4311        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4312
 4313explicit_join_on_postgres_other @
 4314        create_join_points,
 4315        query_type(QueryId, Type),
 4316        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4317        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4318        \
 4319        % Use up the comparison/4 as we don't want it in the WHERE clause
 4320        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4321        <=>
 4322        dbms(Schema, 'PostgreSQL'),
 4323        memberchk(Type, [select, insert, delete]),
 4324        var(JoinVariableA),
 4325        var(JoinVariableB),
 4326        TableAliasA \== TableAliasB
 4327        |
 4328        join_variable(JoinVariableA),
 4329        join_variable(JoinVariableB),
 4330        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4331
 4332
 4333
 4334explicit_join_on @
 4335        create_join_points,
 4336        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4337        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4338        \
 4339        % Use up the comparison/4 as we don't want it in the WHERE clause
 4340        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4341        <=>
 4342        dbms(Schema, 'Microsoft SQL Server'),
 4343        var(JoinVariableA),
 4344        var(JoinVariableB),
 4345        TableAliasA \== TableAliasB
 4346        |
 4347        join_variable(JoinVariableA),
 4348        join_variable(JoinVariableB),
 4349        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4350
 4351explicit_join_on @
 4352        create_join_points,
 4353        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4354        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4355        \
 4356        % Use up the comparison/4 as we don't want it in the WHERE clause
 4357        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4358        <=>
 4359        dbms(Schema, 'SQLite'),
 4360        var(JoinVariableA),
 4361        var(JoinVariableB),
 4362        TableAliasA \== TableAliasB
 4363        |
 4364        join_variable(JoinVariableA),
 4365        join_variable(JoinVariableB),
 4366        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4367
 4368
 4369avoid_duplicate_join_ons @
 4370        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB)
 4371        \
 4372        join_on(TableAliasB, AttributeNameB, TableAliasA, AttributeNameA)
 4373        <=>
 4374        true.
 4375
 4376
 4377search_for_join_aliases_0 @
 4378        join(_, Join, LhsJoin, _, RhsJoin)
 4379        ==>
 4380        search_for_join_aliases(Join, lhs, LhsJoin),
 4381        search_for_join_aliases(Join, rhs, RhsJoin).
 4382
 4383
 4384search_for_join_aliases_1 @
 4385        join_leaf(Join, TableAlias),
 4386        search_for_join_aliases(JoinParent, Side, Join)
 4387        ==>
 4388        join_alias(JoinParent, Side, TableAlias).
 4389
 4390
 4391search_for_join_aliases_2 @
 4392        join(_, Join, LhsJoin, _, _),
 4393        search_for_join_aliases(JoinParent, Side, Join)
 4394        ==>
 4395        search_for_join_aliases(JoinParent, Side, LhsJoin).
 4396
 4397
 4398search_for_join_aliases_3 @
 4399        join(_, Join, _, _, RhsJoin),
 4400        search_for_join_aliases(JoinParent, Side, Join)
 4401        ==>
 4402        search_for_join_aliases(JoinParent, Side, RhsJoin).
 4403
 4404% This is for PostgreSQL
 4405% If we have an implicit join in some query QueryId, we get the join for free.
 4406% However, to do the pre-state-change stuff, we still have to build the join in memory
 4407% and capture the ON clause. This formula creates the two join points in SubQueryId
 4408% which is where the on/4 term goes and implicit_join_sql/3 looks.
 4409% Note that this is only used for explicit on clauses.
 4410search_for_join_aliases_4 @
 4411        implicit_join(QueryId, _, SubQueryId),
 4412        update_table_alias(QueryId, _Schema, _TableName, TableAlias)
 4413        ==>
 4414        join_alias(SubQueryId, lhs, TableAlias),
 4415        search_for_join_aliases(SubQueryId, rhs, QueryId).
 4416
 4417
 4418
 4419add_on_from_join_on @
 4420        resolve_join_points,
 4421        join_alias(Join, lhs, TableAliasLhs),
 4422        join_alias(Join, rhs, TableAliasRhs)
 4423        \
 4424        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB)
 4425        <=>
 4426        ( TableAliasLhs == TableAliasA,
 4427          TableAliasRhs == TableAliasB
 4428        ; TableAliasLhs == TableAliasB,
 4429          TableAliasRhs == TableAliasA
 4430        )
 4431        |
 4432        add_on(Join, TableAliasA-AttributeNameA==TableAliasB-AttributeNameB).
 4433
 4434
 4435add_ons @
 4436        on(Join, Resolved, On),
 4437        add_on(Join, ExistingOn)
 4438        <=>
 4439        on(Join, Resolved, (ExistingOn, On)).
 4440
 4441
 4442add_on @
 4443        add_on(Join, ExistingOn)
 4444        <=>
 4445        on(Join, _, ExistingOn).
 4446
 4447
 4448resolve_join_points @
 4449        resolve_join_points
 4450        \
 4451        on(Join, Resolved, On)
 4452        <=>
 4453        var(Resolved)
 4454        |
 4455        resolve_join_points(Join, On, NewOn),
 4456        on(Join, resolved, NewOn).
 4457
 4458
 4459cleanup_resolve_join_points @
 4460        resolve_join_points
 4461        <=>
 4462        true.
 4463
 4464
 4465resolve_join_points_1 @
 4466        resolve_join_points(Join, (Lhs, Rhs), NewOn)
 4467        <=>
 4468        resolve_join_points(Join, Lhs, NewLhs),
 4469        resolve_join_points(Join, Rhs, NewRhs),
 4470        NewOn = (NewLhs, NewRhs).
 4471
 4472
 4473resolve_join_points_2 @
 4474        resolve_join_points(Join, (Lhs ; Rhs), NewOn)
 4475        <=>
 4476        resolve_join_points(Join, Lhs, NewLhs),
 4477        resolve_join_points(Join, Rhs, NewRhs),
 4478        NewOn = (NewLhs ; NewRhs).
 4479
 4480
 4481resolve_join_points_3 @
 4482        % Ihese come from in-line (shared varoabel) joins and explicit where-style restrictions
 4483        resolve_join_points(_, TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs, NewOn)
 4484        <=>
 4485        NewOn = (TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs).
 4486
 4487
 4488resolve_join_points_4 @
 4489        % These come from on clauses
 4490        attribute_binding(QueryId, attribute(Schema, TableAliasLhs, AttributeNameLhs), JoinVariableA),
 4491        attribute_binding(QueryId, attribute(Schema, TableAliasRhs, AttributeNameRhs), JoinVariableB),
 4492        join_alias(Join, lhs, TableAliasLhs),
 4493        join_alias(Join, rhs, TableAliasRhs)
 4494        \
 4495        resolve_join_points(Join, A==B, NewOn)
 4496        <=>
 4497        var(A),
 4498        var(B),
 4499        ( (A==B) == (JoinVariableA==JoinVariableB)
 4500        ; (B==A) == (JoinVariableA==JoinVariableB)
 4501        )
 4502        |
 4503        not_a_singleton(JoinVariableA),
 4504        not_a_singleton(JoinVariableB),
 4505        join_variable(JoinVariableA),
 4506        join_variable(JoinVariableB),
 4507        NewOn = (TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs).
 4508
 4509
 4510resolve_join_points_5 @
 4511        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 4512        join_alias(Join, _, TableAlias)
 4513        \
 4514        resolve_join_points(Join, On, NewOn)
 4515        <=>
 4516        On =.. [Operator, V, Rhs],
 4517        JoinVariable == V
 4518        |
 4519        not_a_singleton(JoinVariable),
 4520        join_variable(JoinVariable),
 4521        NewOn =.. [Operator, TableAlias-JoinVariable, Rhs].
 4522
 4523
 4524resolve_join_points_6 @
 4525        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 4526        join_alias(Join, _, TableAlias)
 4527        \
 4528        resolve_join_points(Join, On, NewOn)
 4529        <=>
 4530        On =.. [Operator, Lhs, V],
 4531        JoinVariable == V
 4532        |
 4533        not_a_singleton(JoinVariable),
 4534        join_variable(JoinVariable),
 4535        NewOn =.. [Operator, Lhs, TableAlias-JoinVariable].
 4536
 4537
 4538resolve_join_points_7 @
 4539        resolve_join_points(_, On, _)
 4540        <=>
 4541        throw(format('Cannot translate ON specification: ~w', [On])).
 4542
 4543
 4544join_variable_must_be_a_variable @
 4545        join_variable(Variable)
 4546        <=>
 4547        nonvar(Variable)
 4548        |
 4549        true.
 4550
 4551
 4552join_variable_is_unique @
 4553        join_variable(Variable)
 4554        \
 4555        join_variable(Variable)
 4556        <=>
 4557        true.
 4558
 4559
 4560update_not_from_source @
 4561        query_table_alias(QueryId, _, _, TableAlias),
 4562        update_table_alias(QueryId, Schema, _, TargetAlias)
 4563        \
 4564        phase(QueryId, Phase)
 4565        <=>
 4566        dbms(Schema, 'PostgreSQL'),
 4567        Phase == from,
 4568        TableAlias \== TargetAlias
 4569        |
 4570        write_sql(QueryId, compile, top, [' FROM '|T], T, [], []),
 4571        write_join(QueryId, QueryId),
 4572        phase(QueryId, where).
 4573
 4574update_from_source @
 4575        query_table_alias(QueryId, _, _, _),
 4576        update_table_alias(QueryId, Schema, _, _)
 4577        \
 4578        phase(QueryId, from)
 4579        <=>
 4580        dbms(Schema, 'PostgreSQL')
 4581        |
 4582        phase(QueryId, where).
 4583
 4584
 4585select_from @
 4586        query_table_alias(QueryId, _, _, _)
 4587        \
 4588        phase(QueryId, from)
 4589        <=>
 4590        write_sql(QueryId, compile, join, [' FROM '|T], T, [], []),
 4591        write_join(QueryId, QueryId),
 4592        phase(QueryId, where).
 4593
 4594
 4595no_from_statement @
 4596        phase(QueryId, from)
 4597        <=>
 4598        phase(QueryId, where).
 4599
 4600
 4601% The inclusion of the target alias is implied in Postgres UPDATE ... FROM
 4602write_join_leaf_unless_target @
 4603        join_leaf(Join, TableAlias),
 4604        query_table_alias(QueryId, _, TableName, TableAlias),
 4605        update_table_alias(QueryId, Schema, _, TargetAlias)
 4606        \
 4607        write_join(QueryId, Join)
 4608        <=>
 4609        dbms(Schema, 'PostgreSQL'),
 4610        TargetAlias \== TableAlias
 4611        |
 4612        write_sql(QueryId, compile, join, [table_name(TableName), ' ', TableAlias|T], T, [], []).
 4613
 4614write_join_leaf @
 4615        join_leaf(Join, TableAlias),
 4616        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4617        \
 4618        write_join(QueryId, Join)
 4619        <=>
 4620        write_sql(QueryId, compile, join, [table_name(TableName), ' ', TableAlias|T], T, [], []),
 4621        write_lock_hint(QueryId, Schema, TableAlias).
 4622
 4623
 4624write_lock_hint @
 4625        write_lock_hint(QueryId, Schema, TableAlias),
 4626        nolock(QueryId, TableAlias)
 4627        <=>
 4628        dbms(Schema, 'Microsoft SQL Server')
 4629        |
 4630        write_sql(QueryId, compile, join, [' WITH (NOLOCK)'|T], T, [], []).
 4631
 4632% Ignored for PostgreSQL and SQLite
 4633cleanup_write_lock_hint @
 4634        write_lock_hint(_, _, _)
 4635        <=>
 4636        true.
 4637
 4638
 4639
 4640write_join_clause @
 4641        query(QueryId, Schema, _)
 4642        \
 4643        join(QueryId, Join, LhsJoin, JoinType, RhsJoin),
 4644        write_join(QueryId, Join),
 4645        on(Join, _, On)
 4646        <=>
 4647        write_sql(QueryId, compile, join, ['('|T1], T1, [], []),
 4648        write_join(QueryId, LhsJoin),
 4649        write_sql(QueryId, compile, join, [' ', JoinType, ' '|T2], T2, [], []),
 4650        write_join(QueryId, RhsJoin),
 4651        write_sql(QueryId, compile, join, [' ON '|T3], T3, [], []),
 4652        write_join_ons(QueryId, On),
 4653        write_sql(QueryId, compile, join, [')'|T4], T4, [], []),
 4654
 4655        ( debugging(index_suggestions) ->
 4656            on_to_where(Schema, On, RestrictionTree),
 4657            cql_suggest_indices(RestrictionTree, QueryId)
 4658
 4659        ; otherwise ->
 4660            true
 4661        ).
 4662
 4663
 4664write_join_on_conjunction @
 4665        write_join_ons(QueryId, (Lhs, Rhs))
 4666        <=>
 4667        write_join_ons(QueryId, Lhs),
 4668        write_sql(QueryId, compile, join, [' AND '|T], T, [], []),
 4669        write_join_ons(QueryId, Rhs).
 4670
 4671
 4672write_join_on_disjunction @
 4673        write_join_ons(QueryId, (Lhs ; Rhs))
 4674        <=>
 4675        write_sql(QueryId, compile, join, ['('|T1], T1, [], []),
 4676        write_join_ons(QueryId, Lhs),
 4677        write_sql(QueryId, compile, join, [' OR '|T2], T2, [], []),
 4678        write_join_ons(QueryId, Rhs),
 4679        write_sql(QueryId, compile, join, [')'|T3], T3, [], []).
 4680
 4681
 4682write_join_on_attributes @
 4683        write_join_ons(QueryId, TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs)
 4684        <=>
 4685        write_sql(QueryId,
 4686                  compile,
 4687                  join,
 4688                  [TableAliasLhs, '.', attribute_name(AttributeNameLhs), =, TableAliasRhs, '.', attribute_name(AttributeNameRhs)|T],
 4689                  T,
 4690                  [],
 4691                  []).
 4692
 4693write_join_on_lhs @
 4694        attribute_binding(_, attribute(Schema, TableAlias, AttributeName), Variable)
 4695        \
 4696        write_join_ons(QueryId, On)
 4697        <=>
 4698        var(Variable),
 4699        On =.. [ComparisonOperator, ApplicationValue, Rhs],
 4700        error_on_comparison_operator(Schema, ComparisonOperator),
 4701        Rhs == TableAlias-Variable
 4702        |
 4703        write_restriction(QueryId, compile, join, ApplicationValue, ComparisonOperator, attribute(Schema, TableAlias, AttributeName)).
 4704
 4705
 4706write_join_on_rhs @
 4707        attribute_binding(_, attribute(Schema, TableAlias, AttributeName), Variable)
 4708        \
 4709        write_join_ons(QueryId, On)
 4710        <=>
 4711        var(Variable),
 4712        On =.. [ComparisonOperator, Lhs, ApplicationValue],
 4713        error_on_comparison_operator(Schema, ComparisonOperator),
 4714        Lhs == TableAlias-Variable
 4715        |
 4716        write_restriction(QueryId, compile, join, attribute(Schema, TableAlias, AttributeName), ComparisonOperator, ApplicationValue).
 4717
 4718error_on_comparison_operator(Schema, ComparisonOperator) :-
 4719        ( ground(ComparisonOperator),
 4720          prolog_to_sql_comparison_operator(Schema, ComparisonOperator, _, _) ->
 4721            true
 4722        ;
 4723            throw(format('Invalid comparison operator in JOIN ON: ~w', [ComparisonOperator]))
 4724        ).
 4725
 4726query_table_alias_no_duplicates @
 4727        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4728        \
 4729        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4730        <=>
 4731        true.
 4732
 4733
 4734check_update_where_restriction @
 4735        phase(QueryId, where),
 4736        restriction_tree(QueryId, where, true),
 4737        state_change_query(QueryId, StateChangeType, _, _)
 4738        ==>
 4739        ( StateChangeType == delete
 4740        ; StateChangeType == update
 4741        )
 4742        |
 4743        no_where_restriction(StateChangeType).
 4744
 4745
 4746no_where_restriction_permitted @
 4747        no_where_restriction(_),
 4748        absence_of_where_restriction_is_deliberate
 4749        <=>
 4750        true.
 4751
 4752
 4753deletes_and_updates_must_have_a_where_restriction @
 4754        no_where_restriction(StateChangeType)
 4755        <=>
 4756        upcase_atom(StateChangeType, StateChangeTypeUc),
 4757        throw(format('~w has no WHERE restriction - check RESTRICTION variables declared', [StateChangeTypeUc])).
 4758
 4759
 4760remove_empty_restriction_tree @
 4761        phase(QueryId, where)
 4762        \
 4763        restriction_tree(QueryId, where, true)
 4764        <=>
 4765        true.
 4766
 4767
 4768where_restriction_clause @
 4769        phase(QueryId, where),
 4770        restriction_tree(QueryId, where, RestrictionTree)
 4771        <=>
 4772        collect_indices(QueryId),
 4773        write_sql(QueryId, compile, where, [' WHERE '|T], T, [], []),
 4774        write_restriction_tree(QueryId, where, RestrictionTree),
 4775        phase(QueryId, group_by).
 4776
 4777sqlite_must_have_where @
 4778        % This is because we add to the WHERE clause programmatically to compute subqueries for update expressions
 4779        query(QueryId, Schema, _)
 4780        \
 4781        phase(QueryId, where)
 4782        <=>
 4783        dbms(Schema, 'SQLite')
 4784        |
 4785        write_sql(QueryId, compile, where, [' WHERE 1=1 '|T], T, [], []),
 4786        phase(QueryId, group_by).
 4787
 4788
 4789no_where_restriction_clause @
 4790        phase(QueryId, where)
 4791        <=>
 4792        phase(QueryId, group_by).
 4793
 4794
 4795having_restriction_clause @
 4796        phase(QueryId, having),
 4797        restriction_tree(QueryId, having, RestrictionTree)
 4798        <=>
 4799        write_sql(QueryId, compile, having, [' HAVING '|T], T, [], []),
 4800        write_restriction_tree(QueryId, having, RestrictionTree),
 4801        phase(QueryId, order_by).
 4802
 4803
 4804no_having_restriction_clause @
 4805        phase(QueryId, having)
 4806        <=>
 4807        phase(QueryId, order_by).
 4808
 4809suggest_indices @
 4810        write_restriction_tree(QueryId, _, RestrictionTree)
 4811        \
 4812        collect_indices(QueryId)
 4813        <=>
 4814        ( debugging(index_suggestions) ->
 4815            cql_suggest_indices(RestrictionTree, QueryId)
 4816
 4817        ; otherwise ->
 4818            true
 4819        ).
 4820
 4821write_restriction_clause @
 4822        write_restriction_tree(QueryId, RestrictionType, RestrictionTree)
 4823        <=>
 4824        ( RestrictionTree = or(Lhs, Rhs) ->
 4825            Operator = 'OR'
 4826
 4827        ; RestrictionTree = and(Lhs, Rhs) ->
 4828            Operator = 'AND'
 4829        )
 4830        |
 4831        write_sql(QueryId, compile, where, ['('|T1], T1, [], []),
 4832        write_restriction_tree(QueryId, RestrictionType, Lhs),
 4833        write_sql(QueryId, compile, where, [' ', Operator, ' '|T2], T2, [], []),
 4834        write_restriction_tree(QueryId, RestrictionType, Rhs),
 4835        write_sql(QueryId, compile, where, [')'|T3], T3, [], []).
 4836
 4837write_restriction_leaf_for_ignore_if_null_on_rhs @
 4838        query_table_alias(_, Schema, _TableName, TableAlias)
 4839        \
 4840        write_restriction_tree(QueryId, RestrictionType, comparison(attribute(Schema, TableAlias, AttributeName), Operator, ignore_if_null(Variable)))
 4841        <=>
 4842        write_restriction(QueryId, if_not_null(Variable), RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, Variable),
 4843        write_sql(QueryId, if_null(Variable), RestrictionType, ['1 = 1'|T], T, [], []).
 4844
 4845
 4846write_restriction_leaf_for_ignore_if_null_on_lhs @
 4847        query_table_alias(_, Schema, _TableName, TableAlias)
 4848        \
 4849        write_restriction_tree(QueryId, RestrictionType, comparison(ignore_if_null(Variable), Operator, attribute(Schema, TableAlias, AttributeName)))
 4850        <=>
 4851        write_restriction(QueryId, if_not_null(Variable), RestrictionType, Variable, Operator, attribute(Schema, TableAlias, AttributeName)),
 4852        write_sql(QueryId, if_null(Variable), RestrictionType, ['1 = 1'|T], T, [], []).
 4853
 4854write_restriction_leaf_for_if_not_var_on_rhs @
 4855        write_restriction_tree(QueryId, RestrictionType, comparison(Attribute, ==, if_not_var(Variable)))
 4856        <=>
 4857        write_restriction(QueryId, if_not_var(Variable), RestrictionType, Attribute, ==, if_not_var(Variable)),
 4858        write_sql(QueryId, if_var(Variable), RestrictionType, ['1 = 1'|T], T, [], []).
 4859
 4860write_restriction_leaf @
 4861        write_restriction_tree(QueryId, RestrictionType, comparison(Lhs, Operator, Rhs))
 4862        <=>
 4863        write_restriction(QueryId, compile, RestrictionType, Lhs, Operator, Rhs).
 4864
 4865
 4866write_restriction_between_attribute_and_ignore_if_null @
 4867        query_table_alias(_, Schema, TableName, TableAlias)
 4868        \
 4869        write_restriction(QueryId,
 4870                          CompileInstruction,
 4871                          RestrictionType,
 4872                          attribute(Schema, TableAlias, AttributeName),
 4873                          Operator,
 4874                          ignore_if_null(ApplicationValue))
 4875        <=>
 4876        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType)
 4877        |
 4878        write_restriction_1(QueryId,
 4879                            CompileInstruction,
 4880                            RestrictionType,
 4881                            OdbcDataType,
 4882                            _,
 4883                            Schema,
 4884                            TableName,
 4885                            AttributeName,
 4886                            attribute(Schema, TableAlias, AttributeName),
 4887                            Operator,
 4888                            ignore_if_null(TableAlias, ApplicationValue)).
 4889
 4890write_restriction_between_attribute_and_if_not_var @
 4891        query_table_alias(_, Schema, TableName, TableAlias)
 4892        \
 4893        write_restriction(QueryId,
 4894                          CompileInstruction,
 4895                          RestrictionType,
 4896                          attribute(Schema, TableAlias, AttributeName),
 4897                          Operator,
 4898                          if_not_var(Variable))
 4899        <=>
 4900        atom(AttributeName),
 4901        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType)
 4902        |
 4903        write_restriction_1(QueryId,
 4904                            CompileInstruction,
 4905                            RestrictionType,
 4906                            OdbcDataType,
 4907                            _,
 4908                            Schema,
 4909                            TableName,
 4910                            AttributeName,
 4911                            attribute(Schema, TableAlias, AttributeName),
 4912                            Operator,
 4913                            if_not_var(Variable)).
 4914
 4915
 4916not_in_list_comparison_with_no_elements @
 4917        write_restriction(QueryId, CompileInstruction, RestrictionType, _, (\==), [])
 4918        <=>
 4919        true
 4920        |
 4921        write_sql(QueryId, CompileInstruction, RestrictionType, ['1 = 1'|T], T, [], []).
 4922
 4923write_list_comparison_with_collation @
 4924        query_table_alias(_, Schema, TableName, TableAlias)
 4925        \
 4926        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, List)
 4927        <=>
 4928        is_list(List),
 4929        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 4930        collatable_odbc_data_type(OdbcDataType),
 4931
 4932        ( Operator == (==) ->
 4933            ListOperator = 'IN'
 4934
 4935        ; Operator == (\==) ->
 4936            ListOperator = 'NOT IN'
 4937        )
 4938        |
 4939        % Duplicate the restriction to avoid the index scan that would result from
 4940        % using the collation comparison alone
 4941        % but only for SQL Server
 4942        ( collation(Schema, Collation)->
 4943           write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T1], T1, [], []),
 4944           write_sql(QueryId, compile, RestrictionType, [' ', Collation, ' ', ListOperator, ' ('|T2], T2, [], []),
 4945           write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, List),
 4946           write_sql(QueryId, compile, RestrictionType, [') AND '|T3], T3, [], [])
 4947        ; otherwise->
 4948           true
 4949        ),
 4950        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T4], T4, [], []),
 4951        write_sql(QueryId, compile, RestrictionType, [' ', ListOperator, ' ('|T5], T5, [], []),
 4952        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, List),
 4953        write_sql(QueryId, compile, RestrictionType, [')'|T6], T6, [], []).
 4954
 4955write_list_comparison_without_collation @
 4956        query_table_alias(_, Schema, TableName, TableAlias)
 4957        \
 4958        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, List)
 4959        <=>
 4960        is_list(List),
 4961        ( Operator == (==) ->
 4962            ListOperator = 'IN'
 4963
 4964        ; Operator == (\==) ->
 4965            ListOperator = 'NOT IN'
 4966        )
 4967        |
 4968        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T1], T1, [], []),
 4969        write_sql(QueryId, compile, RestrictionType, [' ', ListOperator, ' ('|T2], T2, [], []),
 4970        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, List),
 4971        write_sql(QueryId, compile, RestrictionType, [')'|T3], T3, [], []).
 4972
 4973write_runtime_list @
 4974        query_table_alias(_, Schema, TableName, TableAlias)
 4975        \
 4976        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, list(List))
 4977        <=>
 4978        SubInstruction = not_empty(List),
 4979        OtherInstruction = empty(List),
 4980
 4981        ( Operator == (==) ->
 4982            ListOperator = 'IN'
 4983
 4984        ; Operator == (\==) ->
 4985            ListOperator = 'NOT IN'
 4986        ),
 4987        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 4988
 4989        ( collatable_odbc_data_type(OdbcDataType), collation(Schema, Collation)->
 4990            true
 4991        ;
 4992            Collation = ''
 4993        ),
 4994        write_sql(QueryId, SubInstruction, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T1], T1, [], []),
 4995
 4996        write_sql(QueryId,
 4997                  SubInstruction,
 4998                  RestrictionType,
 4999                  [' ', Collation, ' ', ListOperator, ' ('|T2],
 5000                  T2,
 5001                  [],
 5002                  []),
 5003        write_sql(QueryId,
 5004                  list(List),
 5005                  RestrictionType,
 5006                  [?|T3],
 5007                  T3,
 5008                  [odbc_parameter(Schema, TableName, AttributeName, _, where_value, _)],
 5009                  []),
 5010        write_sql(QueryId,
 5011                  SubInstruction,
 5012                  RestrictionType,
 5013                  [')'|T4],
 5014                  T4,
 5015                  [],
 5016                  []),
 5017        write_sql(QueryId,
 5018                  OtherInstruction,
 5019                  RestrictionType,
 5020                  ['1=1'|T5],
 5021                  T5,
 5022                  [],
 5023                  []).
 5024
 5025
 5026
 5027
 5028write_rhs_null_comparison @
 5029        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(_, TableAlias, AttributeName), Operator, {null})
 5030        <=>
 5031        null_comparison_keywords(Operator, Keywords, Tail)
 5032        |
 5033        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|Keywords], Tail, [], []).
 5034
 5035
 5036write_lhs_null_comparison @
 5037        write_restriction(QueryId, _CompileInstruction, RestrictionType, {null}, Operator, attribute(_, TableAlias, AttributeName))
 5038        <=>
 5039        null_comparison_keywords(Operator, Keywords, Tail)
 5040        |
 5041        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|Keywords], Tail, [], []).
 5042
 5043
 5044null_comparison_keywords(==, [' IS NULL'|T], T).
 5045null_comparison_keywords(\==, [' IS NOT NULL'|T], T).
 5046
 5047
 5048write_restriction_between_attributes @
 5049        query_table_alias(_, _, TableNameLhs, TableAliasLhs)
 5050        \
 5051        write_restriction(QueryId,
 5052                          CompileInstruction,
 5053                          RestrictionType,
 5054                          attribute(_, TableAliasLhs, AttributeNameLhs),
 5055                          Operator,
 5056                          attribute(_, TableAliasRhs, AttributeNameRhs))
 5057        <=>
 5058        odbc_data_type(Schema, TableNameLhs, AttributeNameLhs, OdbcDataType)
 5059        |
 5060        write_restriction_1(QueryId,
 5061                            CompileInstruction,
 5062                            RestrictionType,
 5063                            OdbcDataType,
 5064                            _,
 5065                            Schema,
 5066                            TableNameLhs,
 5067                            AttributeNameLhs,
 5068                            attribute(_, TableAliasLhs, AttributeNameLhs),
 5069                            Operator,
 5070                            attribute(_, TableAliasRhs, AttributeNameRhs)).
 5071
 5072
 5073write_restriction_between_attribute_and_aggregation_sub_select @
 5074        query_table_alias(_, _, TableName, TableAlias)
 5075        \
 5076        write_restriction(QueryId,
 5077                          CompileInstruction,
 5078                          RestrictionType,
 5079                          attribute(Schema, TableAlias, AttributeName),
 5080                          Operator,
 5081                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs))
 5082        <=>
 5083        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5084        |
 5085        Tail = [')'|NewTail],
 5086        write_restriction_1(QueryId,
 5087                            CompileInstruction,
 5088                            RestrictionType,
 5089                            OdbcDataType,
 5090                            _,
 5091                            Schema,
 5092                            TableName,
 5093                            AttributeName,
 5094                            attribute(Schema, TableAlias, AttributeName),
 5095                            Operator,
 5096                            tokens_and_parameters(['('|Sql], NewTail, Inputs)).
 5097
 5098
 5099write_restriction_between_aggregation_sub_select_and_attribute @
 5100        query_table_alias(_, _, TableName, TableAlias)
 5101        \
 5102        write_restriction(QueryId,
 5103                          CompileInstruction,
 5104                          RestrictionType,
 5105                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs),
 5106                          Operator,
 5107                          attribute(Schema, TableAlias, AttributeName))
 5108        <=>
 5109        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5110        |
 5111        Tail = [')'],
 5112        write_restriction_1(QueryId,
 5113                            CompileInstruction,
 5114                            RestrictionType,
 5115                            OdbcDataType,
 5116                            _,
 5117                            Schema,
 5118                            TableName,
 5119                            AttributeName,
 5120                            ['('|Sql]-Inputs,
 5121                            Operator,
 5122                            attribute(Schema, TableAlias, AttributeName)).
 5123
 5124
 5125write_restriction_between_expression_and_aggregation_sub_select @
 5126        write_restriction(QueryId,
 5127                          CompileInstruction,
 5128                          RestrictionType,
 5129                          Expression,
 5130                          Operator,
 5131                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs))
 5132        <=>
 5133        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5134        |
 5135        Tail = [')'|NewTail],
 5136        write_restriction_1(QueryId,
 5137                            CompileInstruction,
 5138                            RestrictionType,
 5139                            OdbcDataType,
 5140                            _,
 5141                            Schema,
 5142                            AggregationTableName,
 5143                            AggregationAttributeName,
 5144                            Expression,
 5145                            Operator,
 5146                            tokens_and_parameters(['('|Sql], NewTail, Inputs)).
 5147
 5148
 5149write_restriction_between_aggregation_sub_select_and_expression @
 5150        write_restriction(QueryId,
 5151                          CompileInstruction,
 5152                          RestrictionType,
 5153                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs),
 5154                          Operator,
 5155                          Expression)
 5156        <=>
 5157        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5158        |
 5159        Tail = [')'|NewTail],
 5160        write_restriction_1(QueryId,
 5161                            CompileInstruction,
 5162                            RestrictionType,
 5163                            OdbcDataType,
 5164                            _,
 5165                            Schema,
 5166                            AggregationTableName,
 5167                            AggregationAttributeName,
 5168                            tokens_and_parameters(['('|Sql], NewTail, Inputs),
 5169                            Operator,
 5170                            Expression).
 5171
 5172
 5173write_restriction_between_expressions @
 5174        write_restriction(QueryId, CompileInstruction, RestrictionType, LhsExpression, Operator, RhsExpression)
 5175        <=>
 5176        RestrictionType \== having   % Could be an aggregation e.g. sum(x) - leave those for the HAVING phase
 5177        |
 5178        ( representative_attribute(LhsExpression+RhsExpression, Schema, TableName, AttributeName) ->
 5179            true
 5180        ; otherwise ->
 5181            throw(format('Cannot find attribute to determine expression data type in ~w or in ~w', [LhsExpression, RhsExpression]))
 5182        ),
 5183        ( odbc_data_type(Schema, TableName, AttributeName, OdbcDataType)->
 5184            true
 5185        ; otherwise->
 5186            throw(format('Could not determine the type of ~w.~w', [TableName, AttributeName]))
 5187        ),
 5188        write_restriction_1(QueryId,
 5189                            CompileInstruction,
 5190                            RestrictionType,
 5191                            OdbcDataType,
 5192                            _,
 5193                            Schema,
 5194                            TableName,
 5195                            AttributeName,
 5196                            LhsExpression,
 5197                            Operator,
 5198                            RhsExpression).
 5199
 5200
 5201representative_attribute_1 @
 5202        % Find an attribute to determine the data type of Expression
 5203        query_table_alias(_, Schema, TableName, TableAlias)
 5204        \
 5205        representative_attribute(attribute(Schema, TableAlias, AttributeName), RepresentativeSchema, RepresentativeTableName, RepresentativeAttributeName)
 5206        <=>
 5207        RepresentativeSchema = Schema,
 5208        RepresentativeTableName = TableName,
 5209        RepresentativeAttributeName = AttributeName.
 5210
 5211representative_attribute_2 @
 5212        representative_attribute(Expression, Schema, TableName, AttributeName)
 5213        <=>
 5214        nonvar(Expression),
 5215        Expression =.. [_|L],
 5216
 5217        ( L = [Lhs, Rhs] ->
 5218            ( representative_attribute(Lhs, Schema, TableName, AttributeName)
 5219            ; representative_attribute(Rhs, Schema, TableName, AttributeName)
 5220            )
 5221        ; L = [Expr] ->
 5222            representative_attribute(Expr, Schema, TableName, AttributeName)
 5223        ).
 5224
 5225
 5226write_having_comparison_between_aggregation_and_null @
 5227        query_table_alias(QueryId, Schema, _, TableAlias),
 5228        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5229        \
 5230        write_restriction(QueryId, CompileInstruction, RestrictionType, Variable, Operator, {null})
 5231        <=>
 5232        not_a_singleton(Variable),
 5233        null_comparison_keywords(Operator, Keywords, Tail),
 5234        functor(Aggregation, Functor, 1),
 5235        aggregation_operator(Functor),
 5236        arg(1, Aggregation, AttributeName)
 5237        |
 5238        write_sql(QueryId, CompileInstruction, RestrictionType, [Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Keywords], Tail, [], []).
 5239
 5240write_having_comparison_between_null_and_aggregation @
 5241        query_table_alias(QueryId, Schema, _, TableAlias),
 5242        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5243        \
 5244        write_restriction(QueryId, CompileInstruction, RestrictionType, {null}, Operator, Variable)
 5245        <=>
 5246        not_a_singleton(Variable),
 5247        null_comparison_keywords(Operator, Keywords, Tail),
 5248        functor(Aggregation, Functor, 1),
 5249        aggregation_operator(Functor),
 5250        arg(1, Aggregation, AttributeName)
 5251        |
 5252        write_sql(QueryId, CompileInstruction, RestrictionType, [Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Keywords], Tail, [], []).
 5253
 5254write_having_comparison_between_aggregation_and_expression @
 5255        query_table_alias(QueryId, Schema, TableName, TableAlias),
 5256        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5257        \
 5258        write_restriction(QueryId, CompileInstruction, RestrictionType, Variable, Operator, Expression)
 5259        <=>
 5260        not_a_singleton(Variable),
 5261        functor(Aggregation, Functor, 1),
 5262        aggregation_operator(Functor),
 5263        arg(1, Aggregation, AttributeName),
 5264        ( nonvar(Expression),
 5265          Expression = ignore_if_null(ApplicationValue) ->
 5266            IgnoreExpression =.. [Functor, TableAlias, AttributeName],
 5267            E = ignore_if_null(IgnoreExpression, ApplicationValue)
 5268
 5269        ; otherwise ->
 5270            E = Expression
 5271        ),
 5272        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 5273
 5274        ( Functor == count ->
 5275            OdbcDataTypeOverride = integer
 5276
 5277        ; otherwise ->
 5278            true
 5279        )
 5280        |
 5281        write_restriction_1(QueryId,
 5282                            CompileInstruction,
 5283                            RestrictionType,
 5284                            OdbcDataType,
 5285                            OdbcDataTypeOverride,
 5286                            Schema,
 5287                            TableName,
 5288                            AttributeName,
 5289                            tokens_and_parameters([Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Tail], Tail, []),
 5290                            Operator,
 5291                            E).
 5292
 5293write_having_comparison_between_expression_and_aggregation @
 5294        query_table_alias(QueryId, Schema, TableName, TableAlias),
 5295        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5296        \
 5297        write_restriction(QueryId, CompileInstruction, RestrictionType, Expression, Operator, Variable)
 5298        <=>
 5299        functor(Aggregation, Functor, 1),
 5300        aggregation_operator(Functor),
 5301        arg(1, Aggregation, AttributeName),
 5302        ( nonvar(Expression),
 5303          Expression = ignore_if_null(ApplicationValue) ->
 5304            IgnoreExpression =.. [Functor, TableAlias, AttributeName],
 5305            E = ignore_if_null(IgnoreExpression, ApplicationValue)
 5306
 5307        ; otherwise ->
 5308            E = Expression
 5309        ),
 5310        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 5311
 5312        ( Functor == count ->
 5313            OdbcDataTypeOverride = integer
 5314
 5315        ; otherwise ->
 5316            true
 5317        )
 5318        |
 5319        write_restriction_1(QueryId,
 5320                            CompileInstruction,
 5321                            RestrictionType,
 5322                            OdbcDataType,
 5323                            OdbcDataTypeOverride,
 5324                            Schema,
 5325                            TableName,
 5326                            AttributeName,
 5327                            E,
 5328                            Operator,
 5329                            tokens_and_parameters([Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Tail], Tail, [])).
 5330
 5331write_restriction_with_collation @  % Prolog-style atom matching i.e. case-sensitive
 5332        write_restriction_1(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs, ComparisonOperator, Rhs)
 5333        <=>
 5334        ( nonvar(OdbcDataTypeOverride) ->
 5335            collatable_odbc_data_type(OdbcDataTypeOverride)
 5336
 5337        ; collatable_odbc_data_type(OdbcDataType) ->
 5338            true
 5339        ),
 5340
 5341        collatable_operator(ComparisonOperator),
 5342        prolog_to_sql_comparison_operator(Schema, ComparisonOperator, BaseSqlOperator, _)
 5343        |
 5344        % Need to duplicate the restriction to avoid the index scan that would result from
 5345        % using the collation comparison alone
 5346        % But only in SQL Server
 5347        ( RestrictionType == where ->
 5348            instruction_conjunction(CompileInstruction, if_not_null(Variable), C1)
 5349        ; otherwise->
 5350            C1 = CompileInstruction
 5351        ),
 5352        instruction_conjunction(CompileInstruction, if_null(Variable), C2),
 5353        ( collation(Schema, Collation)->
 5354            atomic_list_concat([Collation, ' ', BaseSqlOperator], SqlOperator),
 5355            write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5356            ( ( var(Rhs)->
 5357                  Variable = Rhs
 5358              ; Rhs = if_not_var(Variable)->
 5359                  true
 5360              ; Rhs = equality_restriction(Variable) ->
 5361                  true
 5362              )->
 5363                % Either "= RHS" or " IS NULL" if RHS is {null} at runtime
 5364                write_sql(QueryId, C1, RestrictionType, [' ', SqlOperator, ' '|T1], T1, [], []),
 5365                write_restriction_expression(QueryId, C1, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5366                ( RestrictionType == where ->
 5367                    write_sql(QueryId, C2, RestrictionType, [' IS NULL'|T2], T2, [], [])
 5368                ; otherwise->
 5369                    true
 5370                )
 5371            ; otherwise->
 5372                write_sql(QueryId, CompileInstruction, RestrictionType, [' ', SqlOperator, ' '|T3], T3, [], []),
 5373                write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs)
 5374            ),
 5375            write_sql(QueryId, CompileInstruction, RestrictionType, [' AND '|T4], T4, [], [])
 5376        ; otherwise->
 5377            true
 5378        ),
 5379        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5380        ( ( var(Rhs)->
 5381              Variable = Rhs
 5382           ; Rhs = if_not_var(Variable)->
 5383                  true
 5384          ; Rhs = equality_restriction(Variable) ->
 5385              true
 5386          )->
 5387            % Either "= RHS" or " IS NULL" if RHS is {null} at runtime
 5388            write_sql(QueryId, C1, RestrictionType, [' ', BaseSqlOperator, ' '|T5], T5, [], []),
 5389            write_restriction_expression(QueryId, C1, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5390            ( RestrictionType == where ->
 5391                write_sql(QueryId, C2, RestrictionType, [' IS NULL'|T6], T6, [], [])
 5392            ; otherwise->
 5393                true
 5394            )
 5395        ; otherwise->
 5396            write_sql(QueryId, CompileInstruction, RestrictionType, [' ', BaseSqlOperator, ' '|T7], T7, [], []),
 5397            write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs)
 5398        ).
 5399
 5400
 5401write_restriction_without_collation @
 5402        write_restriction_1(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs, ComparisonOperator, Rhs)
 5403        <=>
 5404        prolog_to_sql_comparison_operator(Schema, ComparisonOperator, SqlOperator, _)
 5405        |
 5406        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5407
 5408        % This sets the RHS to be a 'LikeParameter'. We can undo this later if we discover the RHS is an expression
 5409        ( is_like_operator(ComparisonOperator, LikeOdbcDataType) ->
 5410            OdbcDataTypeOverride = LikeOdbcDataType
 5411        ; otherwise ->
 5412            true
 5413        ),
 5414
 5415        ( ( var(Rhs)->
 5416              Variable = Rhs
 5417           ; Rhs = if_not_var(Variable)->
 5418              true
 5419          ; Rhs = equality_restriction(Variable) ->
 5420              true
 5421          )->
 5422            ( RestrictionType == where ->
 5423                instruction_conjunction(CompileInstruction, if_not_null(Variable), C1)
 5424            ; otherwise->
 5425                C1 = CompileInstruction
 5426            ),
 5427            instruction_conjunction(CompileInstruction, if_null(Variable), C2),
 5428            write_sql(QueryId, C1, RestrictionType, [' ', SqlOperator, ' '|T1], T1, [], []),
 5429            write_restriction_expression(QueryId, C1, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5430            ( RestrictionType == where ->
 5431                write_sql(QueryId, C2, RestrictionType, [' IS NULL'|T2], T2, [], [])
 5432            ; otherwise->
 5433                true
 5434            )
 5435        ; otherwise->
 5436            write_sql(QueryId, CompileInstruction, RestrictionType, [' ', SqlOperator, ' '|T3], T3, [], []),
 5437            write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs)
 5438        ).
 5439
 5440
 5441% prolog_to_sql_comparison_operator(Schema,
 5442%                                   ComparisonOperator,
 5443%                                   SqlOperator,
 5444%                                   InverseComparisonOperator)
 5445
 5446prolog_to_sql_comparison_operator(_, <, <, >=).
 5447prolog_to_sql_comparison_operator(_, =<, <=, >).
 5448prolog_to_sql_comparison_operator(_, ==, =, \==).
 5449prolog_to_sql_comparison_operator(_, =:=, =, =\=).
 5450prolog_to_sql_comparison_operator(Schema, =~, Operator, \=~):-
 5451        (dbms(Schema, 'Microsoft SQL Server')->
 5452           Operator = 'LIKE'
 5453        ; dbms(Schema, 'SQLite')->
 5454           Operator = 'LIKE'
 5455        ; dbms(Schema, 'PostgreSQL')->
 5456           Operator = 'ILIKE'
 5457        ).
 5458prolog_to_sql_comparison_operator(Schema, \=~, Operator, =~):-
 5459        ( dbms(Schema, 'Microsoft SQL Server')->
 5460            Operator = 'NOT LIKE'
 5461        ; dbms(Schema, 'SQLite')->
 5462            Operator = 'NOT LIKE'
 5463        ; dbms(Schema, 'PostgreSQL')->
 5464            Operator = 'NOT ILIKE'
 5465        ).
 5466prolog_to_sql_comparison_operator(_, \==, <>, ==).
 5467prolog_to_sql_comparison_operator(_, =\=, <>, =:=).
 5468prolog_to_sql_comparison_operator(_, >=, >=, <).
 5469prolog_to_sql_comparison_operator(_, >, >, =<).
 5470
 5471
 5472collatable_odbc_data_type(char(_)).
 5473collatable_odbc_data_type(varchar(_)).
 5474collatable_odbc_data_type(longvarchar).
 5475
 5476
 5477collatable_operator(Operator):-
 5478        Operator \== (=~).
 5479
 5480
 5481is_like_operator((=~), varchar(128)).
 5482
 5483
 5484collation(Schema, 'COLLATE Latin1_General_CS_AS'):-  % Make CQL case-sensitive
 5485        dbms(Schema, 'Microsoft SQL Server').
 5486
 5487collation(Schema, _):-
 5488        ( dbms(Schema, 'PostgreSQL')->
 5489            fail
 5490        ; dbms(Schema, 'SQLite')->
 5491            fail
 5492        ).
 5493
 5494write_restriction_expression_1 @
 5495        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, _, _, _, _, tokens_and_parameters(Tokens, Tail, OdbcParameters))
 5496        <=>
 5497        write_sql(QueryId,
 5498                  CompileInstruction,
 5499                  RestrictionType,
 5500                  Tokens,
 5501                  Tail,
 5502                  OdbcParameters,
 5503                  []).
 5504
 5505write_restriction_expression_2 @
 5506        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, _, _, _, _, attribute(_, TableAlias, AttributeName))
 5507        <=>
 5508        write_sql(QueryId,
 5509                  CompileInstruction,
 5510                  RestrictionType,
 5511                  [TableAlias, '.', attribute_name(AttributeName)|T],
 5512                  T,
 5513                  [],
 5514                  []).
 5515
 5516write_restriction_expression_3 @
 5517        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, equality_restriction(Variable))
 5518        <=>
 5519        write_sql(QueryId,
 5520                  CompileInstruction,
 5521                  RestrictionType,
 5522                  [?|T],
 5523                  T,
 5524                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5525                  []).
 5526
 5527% This is a ignore_if_null against an aggregation
 5528write_restriction_expression_4a @
 5529        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, ignore_if_null(Aggregation, Variable))
 5530        <=>
 5531        functor(Aggregation, Functor, 2),
 5532        aggregation_operator(Functor)
 5533        |
 5534        arg(1, Aggregation, TableAlias),
 5535        arg(2, Aggregation, AttributeName),
 5536        write_sql(QueryId,
 5537                  CompileInstruction,
 5538                  RestrictionType,
 5539                  ['COALESCE(?, ', Functor, '(', TableAlias, '.', attribute_name(AttributeName), '))'|T],
 5540                  T,
 5541                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5542                  []).
 5543
 5544write_restriction_expression_4 @
 5545        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, ignore_if_null(TableAlias, Variable))
 5546        <=>
 5547        write_sql(QueryId,
 5548                  CompileInstruction,
 5549                  RestrictionType,
 5550                  ['COALESCE(?, ', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 5551                  T,
 5552                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5553                  []).
 5554
 5555write_restriction_expression_5 @
 5556        attribute_binding(QueryId, Attribute, Variable)
 5557        \
 5558        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Variable)
 5559        <=>
 5560        var(Variable)
 5561        |
 5562        where_restriction_variable(Variable),
 5563        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Attribute).
 5564
 5565write_restriction_expression_6 @
 5566        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, Variable)
 5567        <=>
 5568        atomic_application_value(Variable)
 5569        |
 5570        write_sql(QueryId,
 5571                  CompileInstruction,
 5572                  RestrictionType,
 5573                  [?|T],
 5574                  T,
 5575                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5576                  []).
 5577
 5578write_restriction_expression_7 @
 5579        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Expression)
 5580        <=>
 5581        Expression =.. [Operator, Rhs],
 5582        restriction_prefix_operator(Operator)
 5583        |
 5584        write_sql(QueryId, CompileInstruction,  RestrictionType, [Operator|T], T, [], []),
 5585        % We unset the OdbcDataTypeOverride here
 5586        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs).
 5587
 5588write_restriction_expression_8 @
 5589        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Expression)
 5590        <=>
 5591        Expression =.. [Operator, Lhs, Rhs],
 5592        restriction_expression_operator(Operator)
 5593        |
 5594        ( dbms(Schema, 'SQLite') ->
 5595            write_sql(QueryId, CompileInstruction, RestrictionType, ['(1.0*'|T1], T1, [], [])
 5596        ; otherwise->
 5597            write_sql(QueryId, CompileInstruction, RestrictionType, ['('|T1], T1, [], [])
 5598        ),
 5599        % We unset the OdbcDataTypeOverride here
 5600        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5601        write_sql(QueryId, CompileInstruction, RestrictionType, [')'|T2], T2, [], []),
 5602        write_sql(QueryId, CompileInstruction, RestrictionType, [Operator|T3], T3, [], []),
 5603        ( dbms(Schema, 'SQLite') ->
 5604            write_sql(QueryId, CompileInstruction, RestrictionType, ['(1.0*'|T4], T4, [], [])
 5605        ; otherwise->
 5606            write_sql(QueryId, CompileInstruction, RestrictionType, ['('|T4], T4, [], [])
 5607        ),
 5608        % We unset the OdbcDataTypeOverride here
 5609        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5610        write_sql(QueryId, CompileInstruction, RestrictionType, [')'|T5], T5, [], []).
 5611
 5612write_restriction_expression_9 @
 5613        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, if_not_var(Var))
 5614        <=>
 5615        instruction_conjunction(CompileInstruction, if_not_var(Var), NewInstruction),
 5616        write_sql(QueryId,
 5617                  NewInstruction,
 5618                  RestrictionType,
 5619                  [?|T],
 5620                  T,
 5621                  [odbc_parameter(Schema, TableName, AttributeName, Var, where_value, OdbcDataTypeOverride)],
 5622                  []).
 5623
 5624
 5625write_restriction_expression_10 @
 5626        write_restriction_expression(_, _, _, _, _, _, _, _, Expression)
 5627        <=>
 5628        throw(format('Bad restriction expression: ~w', [Expression])).
 5629
 5630
 5631restriction_prefix_operator(+).
 5632restriction_prefix_operator(-).
 5633
 5634restriction_expression_operator(+).
 5635restriction_expression_operator(-).
 5636restriction_expression_operator(/).
 5637restriction_expression_operator(*).
 5638
 5639
 5640write_sub_query @
 5641        write_restriction_tree(QueryId, RestrictionType, sub_query(SubQueryType, Sql, SqlTail, SubQueryInputs))
 5642        <=>
 5643        ( SubQueryType == exists ->
 5644            Token = 'EXISTS'
 5645
 5646        ; SubQueryType == (\+ exists) ->
 5647            Token = 'NOT EXISTS'
 5648        )
 5649        |
 5650        SqlTail = [')'|T],
 5651        write_sql(QueryId, compile, RestrictionType, [Token,  ' ('|Sql], T, SubQueryInputs, []).
 5652
 5653
 5654write_in_list_1 @
 5655        next_in_list_value_needs_comma(QueryId)
 5656        \
 5657        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, [ApplicationValue|ApplicationValues])
 5658        <=>
 5659        write_sql(QueryId, compile, RestrictionType, [', ?'|T], T, [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, where_value, _)], []),
 5660        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, ApplicationValues).
 5661
 5662
 5663write_in_list_2 @
 5664        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, [ApplicationValue|ApplicationValues])
 5665        <=>
 5666        write_sql(QueryId, compile, RestrictionType, [?|T], T, [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, where_value, _)], []),
 5667        next_in_list_value_needs_comma(QueryId),
 5668        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, ApplicationValues).
 5669
 5670
 5671write_in_list_3 @
 5672        write_in_list(QueryId, _, _, _, _, []),
 5673        next_in_list_value_needs_comma(QueryId)
 5674        <=>
 5675        true.
 5676
 5677
 5678write_group_by @
 5679        group_by(QueryId, _)
 5680        \
 5681        phase(QueryId, group_by)
 5682        <=>
 5683        write_sql(QueryId, compile, having, [' GROUP BY '|T], T, [], []),
 5684        write_group_bys(QueryId),
 5685        phase(QueryId, having).
 5686
 5687
 5688no_group_by @
 5689        phase(QueryId, group_by)
 5690        <=>
 5691        phase(QueryId, having).
 5692
 5693
 5694write_group_bys @
 5695        write_group_bys(QueryId)
 5696        \
 5697        attribute_for_group_by(QueryId, TableAlias, AttributeName, GroupBy),
 5698        group_by(QueryId, GroupBy)
 5699        <=>
 5700        write_group_by_attribute(QueryId, [TableAlias, '.', AttributeName|T], T).
 5701
 5702
 5703write_group_by_attribute_with_trailing_comma @
 5704        next_group_by_attribute_needs_comma(QueryId)
 5705        \
 5706        write_group_by_attribute(QueryId, SqlTokens, Tail)
 5707        <=>
 5708        write_sql(QueryId, compile, having, [', '|SqlTokens], Tail, [], []).
 5709
 5710
 5711write_group_by_attribute_without_trailing_comma @
 5712        write_group_by_attribute(QueryId, SqlTokens, Tail)
 5713        <=>
 5714        write_sql(QueryId, compile, having, SqlTokens, Tail, [], []),
 5715        next_group_by_attribute_needs_comma(QueryId).
 5716
 5717
 5718should_not_be_any_group_by_constraints_left_over @
 5719        check_for_orphan_group_bys,
 5720        group_by(_, GroupBy),
 5721        original_cql(Cql)
 5722        <=>
 5723        throw(format('Unused GROUP BY ~w in CQL: ~w', [GroupBy, Cql])).
 5724
 5725
 5726write_order_by @
 5727        order_bys(QueryId, OrderBys)
 5728        \
 5729        phase(QueryId, order_by)
 5730        <=>
 5731        OrderBys \== []
 5732        |
 5733        write_sql(QueryId, compile, having, [' ORDER BY '|T], T, [], []),
 5734        write_order_bys(QueryId, OrderBys),
 5735        phase(QueryId, union).
 5736
 5737
 5738no_order_by @
 5739        phase(QueryId, order_by)
 5740        <=>
 5741        phase(QueryId, union).
 5742
 5743
 5744write_order_bys @
 5745        write_order_bys(QueryId, [OrderBy|OrderBys])
 5746        <=>
 5747        write_order_by(QueryId, OrderBy),
 5748        write_order_bys(QueryId, OrderBys).
 5749
 5750
 5751clean_up_write_order_bys @
 5752        write_order_bys(_, [])
 5753        <=>
 5754        true.
 5755
 5756
 5757write_order_by_aggregate @
 5758        write_order_by(QueryId, OrderBy),
 5759        attribute_for_order_by(QueryId, TableAlias, AggregationTerm, Variable)
 5760        <=>
 5761        AggregationTerm =.. [AggregationOperator, AttributeName],
 5762        aggregation_operator(AggregationOperator),
 5763
 5764        ( OrderBy == +(Variable) ->
 5765            Direction = 'ASC'
 5766
 5767        ; OrderBy == -(Variable) ->
 5768            Direction = 'DESC'
 5769        ),
 5770        %map_database_atom(AggregationOperator, AggregationOperatorUc)
 5771        upcase_atom(AggregationOperator, AggregationOperatorUc)
 5772        |
 5773        not_a_singleton(Variable),
 5774        write_order_by_attribute(QueryId, [AggregationOperatorUc, '(', TableAlias, '.', attribute_name(AttributeName), ') ', Direction|T], T).
 5775
 5776
 5777write_order_by @
 5778        write_order_by(QueryId, OrderBy),
 5779        attribute_for_order_by(QueryId, TableAlias, AttributeName, Variable)
 5780        <=>
 5781        ( OrderBy == +(Variable) ->
 5782            Direction = 'ASC'
 5783
 5784        ; OrderBy == -(Variable) ->
 5785            Direction = 'DESC'
 5786        )
 5787        |
 5788        not_a_singleton(Variable),
 5789        write_order_by_attribute(QueryId, [TableAlias, '.', attribute_name(AttributeName), ' ', Direction|T], T).
 5790
 5791
 5792
 5793write_order_by_attribute_with_trailing_comma @
 5794        next_order_by_attribute_needs_comma(QueryId)
 5795        \
 5796        write_order_by_attribute(QueryId, SqlTokens, Tail)
 5797        <=>
 5798        write_sql(QueryId, compile, having, [', '|SqlTokens], Tail, [], []).
 5799
 5800
 5801write_order_by_attribute_without_trailing_comma @
 5802        write_order_by_attribute(QueryId, SqlTokens, Tail)
 5803        <=>
 5804        write_sql(QueryId, compile, having, SqlTokens, Tail, [], []),
 5805        next_order_by_attribute_needs_comma(QueryId).
 5806
 5807
 5808should_not_be_any_write_order_by_constraints_left_over @
 5809        check_for_orphan_order_bys,
 5810        write_order_by(_, OrderBy),
 5811        original_cql(Cql)
 5812        <=>
 5813        throw(format('Unused ORDER BY ~w in CQL: ~w', [OrderBy, Cql])).
 5814
 5815
 5816in_line_formats @
 5817        prior_to_execution \ in_line_format(_, Format, FormatArgs, ApplicationValue) <=>  format(atom(ApplicationValue), Format, FormatArgs).
 5818        prior_to_execution <=> true.
 5819
 5820
 5821postgres_identity @
 5822        number_of_rows_affected(QueryId, _, _)
 5823        \
 5824        postgres_identity(QueryId, ReturnedIdentity),
 5825        cql_identity(QueryId, _, Identity)
 5826        <=>
 5827        Identity = ReturnedIdentity.
 5828
 5829ignored_postgres_identity @
 5830        number_of_rows_affected(QueryId, _, _)
 5831        \
 5832        postgres_identity(QueryId, _)
 5833        <=>
 5834        true.
 5835
 5836identity_sql_server @
 5837        number_of_rows_affected(QueryId, Connection, _),
 5838        cql_statement_location(FileName, LineNumber)
 5839        \
 5840        cql_identity(QueryId, Schema, Identity)
 5841        <=>
 5842        dbms(Schema, 'Microsoft SQL Server'),
 5843        odbc_query(Connection, 'SELECT CAST(@@IDENTITY AS INTEGER)', row(ScopeIdentity))
 5844        |
 5845        ( integer(ScopeIdentity) ->
 5846            Identity = ScopeIdentity,
 5847            get_transaction_context(TransactionId, _, AccessToken, _, _),
 5848            cql_access_token_to_user_id(AccessToken, UserId),
 5849            cql_log([], informational, 'CQL\t~w\t~w\t   Inserted row has identity ~w\t(~w:~w)', [UserId, TransactionId, Identity, FileName, LineNumber])
 5850        ; otherwise ->
 5851            cql_error(bad_identity, 'Integer identity value expected but got ~q', [ScopeIdentity])
 5852        ).
 5853
 5854identity_sqlite @
 5855        number_of_rows_affected(QueryId, Connection, _),
 5856        cql_statement_location(FileName, LineNumber)
 5857        \
 5858        cql_identity(QueryId, Schema, Identity)
 5859        <=>
 5860        dbms(Schema, 'SQLite'),
 5861        odbc_query(Connection, 'SELECT CAST(last_insert_rowid() AS INTEGER)', row(ScopeIdentity))
 5862        |
 5863        ( integer(ScopeIdentity) ->
 5864            Identity = ScopeIdentity,
 5865            get_transaction_context(TransactionId, _, AccessToken, _, _),
 5866            cql_access_token_to_user_id(AccessToken, UserId),
 5867            cql_log([], informational, 'CQL\t~w\t~w\t   Inserted row has identity ~w\t(~w:~w)', [UserId, TransactionId, Identity, FileName, LineNumber])
 5868        ; otherwise ->
 5869            cql_error(bad_identity, 'Integer identity value expected but got ~q', [ScopeIdentity])
 5870        ).
 5871
 5872rows_affected @
 5873        number_of_rows_affected(QueryId, _, N)
 5874        \
 5875        row_count(QueryId, RowCount)
 5876        <=>
 5877        RowCount = N.
 5878
 5879post_state_change_statistics @
 5880        number_of_rows_affected(QueryId, _, N)
 5881        \
 5882        cql_state_change_statistics_sql(QueryId, Schema, TableName, update, _, StateChangeAttributeNames, OdbcParameters, _)
 5883        <=>
 5884        process_statistics_post_state_changes(Schema, TableName, StateChangeAttributeNames, OdbcParameters, N).
 5885
 5886number_of_rows_affected_cleanup @
 5887        number_of_rows_affected(QueryId, _, _),  cql_state_change_statistics_sql(QueryId, _, _, _, _, _, _, _) <=> true.
 5888        number_of_rows_affected(_, _, _) <=> true.
 5889
 5890
 5891write_sql @
 5892        sql_statement(QueryId, TokensSoFar, TokensTail, FromTokensSoFar, FromTail, RestrictionTokensSoFar, RestrictionTail, ExistingOdbcParameters, ExistingFromParameters, ExistingOutputs),
 5893        write_sql(QueryId, CompileInstruction, Position, AddTokens, AddTail, OdbcParameters, Outputs)
 5894        <=>
 5895        ( CompileInstruction == compile->
 5896            Addition = AddTokens,
 5897            AdditionTail = AddTail,
 5898            ( Position \== top->
 5899                append(ExistingFromParameters, OdbcParameters, NewFromParameters),
 5900                NewOdbcParameters = ExistingOdbcParameters
 5901            ; otherwise->
 5902                ( var(OdbcParameters)->
 5903                    append(ExistingOdbcParameters, [compile:OdbcParameters], NewOdbcParameters)
 5904                ; otherwise->
 5905                    append(ExistingOdbcParameters, OdbcParameters, NewOdbcParameters)
 5906                ),
 5907                NewFromParameters = ExistingFromParameters
 5908            )
 5909        ; otherwise->
 5910            % FIXME: This is where to keep the tail around so we can flatten quickly at runtime
 5911            Addition = [CompileInstruction:AddTokens|AdditionTail],
 5912            AddTail = [],
 5913            ( OdbcParameters == []->
 5914                NewOdbcParameters = ExistingOdbcParameters,
 5915                NewFromParameters = ExistingFromParameters
 5916            ; otherwise->
 5917                ( Position \== top->
 5918                    append(ExistingFromParameters, [CompileInstruction:OdbcParameters], NewFromParameters),
 5919                    NewOdbcParameters = ExistingOdbcParameters
 5920                ; otherwise->
 5921                    append(ExistingOdbcParameters, [CompileInstruction:OdbcParameters], NewOdbcParameters),
 5922                    NewFromParameters = ExistingFromParameters
 5923                )
 5924            )
 5925        ),
 5926        ( Position == join ->
 5927            FromTail = Addition,
 5928            NewFromTail = AdditionTail,
 5929            NewTokensTail = TokensTail,
 5930            NewRestrictionTail = RestrictionTail
 5931        ; Position == top ->
 5932            TokensTail = Addition,
 5933            NewTokensTail = AdditionTail,
 5934            NewFromTail = FromTail,
 5935            NewRestrictionTail = RestrictionTail
 5936        ; otherwise->
 5937            NewRestrictionTail = AdditionTail,
 5938            RestrictionTail = Addition,
 5939            NewTokensTail = TokensTail,
 5940            NewFromTail = FromTail
 5941        ),
 5942        ( CompileInstruction == compile ->
 5943            append(ExistingOutputs, Outputs, NewOutputs)
 5944        ; Outputs == []->
 5945            NewOutputs = ExistingOutputs
 5946        ; otherwise->
 5947            append(ExistingOutputs, [CompileInstruction:Outputs], NewOutputs)
 5948        ),
 5949        sql_statement(QueryId, TokensSoFar, NewTokensTail, FromTokensSoFar, NewFromTail, RestrictionTokensSoFar, NewRestrictionTail, NewOdbcParameters, NewFromParameters, NewOutputs).
 5950
 5951
 5952instantiate_aggregation_sub_query @
 5953        query(SubQueryId, _, sub_query)
 5954        \
 5955        phase(SubQueryId, union),
 5956        sql_statement(SubQueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, OdbcParameters, FromParameters, [output(_, TableName, AttributeName, _)]),
 5957        aggregation_sub_query(SubQueryId, AggregationTableName, AggregationAttributeName, SubQuerySqlTokens, SubQueryTail, SubQueryOdbcParameters)
 5958        <=>
 5959        AggregationTableName = TableName,
 5960        AggregationAttributeName = AttributeName,
 5961        TokensTail = SqlFromTokens,
 5962        FromTail = SqlRestrictionTokens,
 5963        SubQueryTail = RestrictionTail,
 5964        SubQuerySqlTokens = SqlTokens,
 5965        append(OdbcParameters, FromParameters, SubQueryOdbcParameters).
 5966
 5967instantiate_sub_query @
 5968        query(QueryId, _, sub_query)
 5969        \
 5970        phase(QueryId, union),
 5971        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, OdbcParameters, FromParameters, _),
 5972        sub_query(QueryId, SubQuerySqlTokens, SubQuerySqlTail, SubQueryOdbcParameters)
 5973        <=>
 5974        TokensTail = SqlFromTokens,
 5975        FromTail = SqlRestrictionTokens,
 5976        SubQuerySqlTail = RestrictionTail,
 5977        SubQuerySqlTokens = SqlTokens,
 5978        append(OdbcParameters, FromParameters, SubQueryOdbcParameters).
 5979
 5980orphan_write_restriction @
 5981        check_query,
 5982        write_restriction(_, _, _, ApplicationValueLhs, Operator, ApplicationValueRhs),
 5983        original_cql(Cql)
 5984        <=>
 5985        throw(format('Unused restriction: ~w ~w ~w in CQL: ~w', [ApplicationValueLhs, Operator, ApplicationValueRhs, Cql])).
 5986
 5987% Ignore any join_on which comes for free in PostgreSQL
 5988% (if we do an UPDATE ... FROM) we get a free join to the target
 5989% NB: We can only get implicit_join/2 if dbms is PostgreSQL
 5990ignore_implicit_joins @
 5991        implicit_join(QueryId, Ignore, SubQueryId)
 5992        \
 5993        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB)
 5994        <=>
 5995        ( Ignore == TableAliasA ; Ignore == TableAliasB)
 5996        |
 5997        add_on(SubQueryId, TableAliasA-AttributeNameA==TableAliasB-AttributeNameB),
 5998        implicit_join_link(QueryId, SubQueryId).
 5999
 6000remove_duplicate_implicit_join_links @
 6001        implicit_join_link(QueryId, SubQueryId)
 6002        \
 6003        implicit_join_link(QueryId, SubQueryId)
 6004        <=>
 6005        true.
 6006
 6007recover_implicit_join_for_update @
 6008        implicit_join_sql(QueryId, Sql, Tail),
 6009        implicit_join_link(QueryId, SubQueryId),
 6010        on(SubQueryId, _, On)
 6011        <=>
 6012        sql_statement(SubQueryId, A, A, B, B, C, C, [], [], []),
 6013        write_join_ons(SubQueryId, On),
 6014        fetch_implicit_join_sql(SubQueryId, Sql, Tail).
 6015
 6016fetch_implicit_join_sql @
 6017        fetch_implicit_join_sql(SubQueryId, Sql, Tail),
 6018        sql_statement(SubQueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, _, _, _)
 6019        <=>
 6020        TokensTail = SqlFromTokens,
 6021        FromTail = SqlRestrictionTokens,
 6022        Tail = RestrictionTail,
 6023        Sql = SqlTokens.
 6024
 6025check_for_joins @
 6026        check_query,
 6027        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB),
 6028        original_cql(Cql)
 6029        <=>
 6030        throw(format('Unused JOIN point ~w (check join operator present) in CQL: ~w',
 6031                     [join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB), Cql])).
 6032
 6033
 6034check_for_unused_select_bindings @
 6035        % Select bindings should have either been translated into select attributes (i.e. will appear in
 6036        % the SELECT clause) or been explicitly discarded.  Any left behind indicate a problem.
 6037        check_query,
 6038        select_binding(_, X, Attribute, _),
 6039        original_cql(Cql)
 6040        <=>
 6041        throw(format('Unused SELECT binding (missing GROUP BY?): ~n~w ~n~n~w~n~nin CQL: ~w', [X, Attribute, Cql])).
 6042
 6043
 6044check_for_unused_join_on_comparisons @
 6045        check_query,
 6046        write_join_ons(_, On),
 6047        original_cql(Cql)
 6048        <=>
 6049        throw(format('Unused join ON comparison <~w> in CQL: ~w', [On, Cql])).
 6050
 6051
 6052check_for_unused_comparisons @
 6053        check_query,
 6054        comparison(_, Lhs, ComparisonOperator, Rhs),
 6055        original_cql(Cql)
 6056        <=>
 6057        throw(format('Unused comparison: ~w ~w ~w in CQL: ~w', [Lhs, ComparisonOperator, Rhs, Cql])).
 6058
 6059
 6060cleanup_check_query @
 6061        check_query
 6062        <=>
 6063        true.
 6064
 6065
 6066odbc_state_change_statement_update @
 6067        prepare_odbc_statements,
 6068        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6069        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6070        <=>
 6071        dbms(Schema, 'Microsoft SQL Server'),
 6072        StateChangeType == update
 6073        |
 6074        TokensTail = SqlFromTokens,
 6075        FromTail = SqlRestrictionTokens,
 6076        RestrictionTail = [],
 6077        AllSqlTokens = SqlTokens,
 6078        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6079        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, SqlFromTokens, TableName, OdbcParameters),
 6080        create_cql_state_change_statistics_sql(QueryId, StateChangeType, SqlFromTokens, TableName, OdbcParameters),
 6081        compile_tokens(AllSqlTokens, Sql),
 6082        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6083
 6084
 6085duplicate_from_clause @
 6086        copy_of_from(QueryId, Tokens, Tail, Parameters)
 6087        \
 6088        find_copy_of_from(QueryId, NewTokens, NewTail, NewParameters)
 6089        <=>
 6090        Parameters = NewParameters,
 6091        swap_tail(Tokens, Tail, NewTail, NewTokens).
 6092
 6093remove_cycles([], []):- !.
 6094remove_cycles([compile:_|As], [Bs]):- !, remove_cycles(As, Bs).
 6095remove_cycles([A|As], [A|Bs]):- !, remove_cycles(As, Bs).
 6096remove_cycles([_|As], Bs):- remove_cycles(As, Bs).
 6097
 6098odbc_state_change_statement_update_sqlite_1 @
 6099        update_table_alias(QueryId, _, _, TargetAlias)
 6100        \
 6101        prepare_odbc_statements,
 6102        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6103        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6104        <=>
 6105        dbms(Schema, 'SQLite'),
 6106        StateChangeType == update
 6107        |
 6108        TokensTail = [' WHERE rowid IN (SELECT ', TargetAlias, '.rowid '|SqlFromTokens],
 6109        FromTail = SqlRestrictionTokens,
 6110        AllSqlTokens = SqlTokens,
 6111        % Yikes. Initially I used copy_term here but the variables need to be shared (except the tail, of course)
 6112        swap_tail(SqlFromTokens, RestrictionTail, [], StateChangeSelectTokens),
 6113        swap_tail(SqlFromTokens, RestrictionTail, CopyFromTail, CopyFrom),
 6114        copy_of_from(QueryId, CopyFrom, CopyFromTail, FromParameters),
 6115        RestrictionTail = [')'],
 6116        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6117        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, StateChangeSelectTokens, TableName, OdbcParameters),
 6118        create_cql_state_change_statistics_sql(QueryId, StateChangeType, StateChangeSelectTokens, TableName, OdbcParameters),
 6119        compile_tokens(AllSqlTokens, Sql),
 6120        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6121
 6122
 6123swap_tail(Var, RestrictionTail, Tail, Tail):-
 6124        RestrictionTail == Var, !.
 6125
 6126swap_tail([A|As], Tail, X, [A|Bs]):-
 6127        swap_tail(As, Tail, X, Bs).
 6128
 6129odbc_state_change_statement_update @
 6130        query_table_alias(QueryId, _, _, TableAlias),
 6131        update_table_alias(QueryId, _, _, TargetAlias)
 6132        \
 6133        prepare_odbc_statements,
 6134        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6135        state_change_query(QueryId, StateChangeType, Schema, UpdateTableName)
 6136        <=>
 6137        dbms(Schema, 'PostgreSQL'),
 6138        implicit_join_sql(QueryId, ImplicitJoinSQL, [')'|ImplicitJoinTail]),
 6139        % If there is any query_table_alias which is NOT the same as the update_table_alias then the
 6140        % SqlRestrictionTokens will already contain a FROM
 6141        TableAlias \== TargetAlias,
 6142        StateChangeType == update
 6143        |
 6144        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6145        % This is full of nasty traps. We must build up two very similar but not identical queries. Take care not to instantiate too much before the copy_term/2!
 6146        RestrictionTail = [],
 6147        ImplicitJoinTail = SqlRestrictionTokens,
 6148        copy_term([' FROM '|SqlFromTokens]:FromTail, SqlPreFromTokens:[' INNER JOIN ', UpdateTableName, ' ', TargetAlias, ' ON ('|ImplicitJoinSQL]),
 6149        TokensTail = SqlFromTokens,
 6150        FromTail = SqlRestrictionTokens,
 6151        AllSqlTokens = SqlTokens,
 6152
 6153        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, SqlPreFromTokens, UpdateTableName, OdbcParameters),
 6154        create_cql_state_change_statistics_sql(QueryId, StateChangeType, SqlPreFromTokens, UpdateTableName, OdbcParameters),
 6155        % Inject the implicit join here
 6156        % ImplicitJoin = [' INNER JOIN ', TableName, ' ', TableAlias, ' ON ', '(1=1)'],
 6157        compile_tokens(AllSqlTokens, Sql),
 6158        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, UpdateTableName, Sql, OdbcParameters, Outputs).
 6159
 6160
 6161odbc_state_change_statement_update @
 6162        query_table_alias(QueryId, _, TableName, TableAlias)
 6163        \
 6164        prepare_odbc_statements,
 6165        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6166        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6167        <=>
 6168        dbms(Schema, 'PostgreSQL'),
 6169        StateChangeType == update
 6170        |
 6171        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6172        % If there is no query_table_alias which is NOT the same as the update_table_alias then the
 6173        % SqlRestrictionTokens will NOT contain a FROM, so we must add one
 6174        TokensTail = SqlFromTokens,
 6175        FromTail = SqlRestrictionTokens,
 6176        RestrictionTail = [],
 6177        AllSqlTokens = SqlTokens,
 6178        % This case is much simpler than the above
 6179        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, [' FROM ', TableName, ' ', TableAlias, ' '|SqlRestrictionTokens], TableName, OdbcParameters),
 6180        create_cql_state_change_statistics_sql(QueryId, StateChangeType, [' FROM ', TableName, ' ', TableAlias, ' '|SqlRestrictionTokens], TableName, OdbcParameters),
 6181        compile_tokens(AllSqlTokens, Sql),
 6182        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6183
 6184
 6185odbc_state_change_statement_not_update @
 6186        prepare_odbc_statements,
 6187        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6188        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6189        <=>
 6190        TokensTail = SqlFromTokens,
 6191        FromTail = SqlRestrictionTokens,
 6192        RestrictionTail = [],
 6193        AllSqlTokens = SqlTokens,
 6194        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6195        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, SqlFromTokens, TableName, OdbcParameters),
 6196        ( StateChangeType == delete ->
 6197            create_cql_state_change_statistics_sql(QueryId, StateChangeType, SqlFromTokens, TableName, OdbcParameters)
 6198        ; otherwise->
 6199            true
 6200        ),
 6201        compile_tokens(AllSqlTokens, Sql),
 6202        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6203
 6204
 6205execute_state_change_query @
 6206        cql_statement_location(FileName, LineNumber)
 6207        \
 6208        cql_execute(OdbcCachingOption),
 6209        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, HalfCompiledSql, HalfCompiledOdbcParameters, _)
 6210        <=>
 6211        fully_compile_sql(HalfCompiledSql, HalfCompiledOdbcParameters, [], Sql, OdbcParameters, _),
 6212        get_transaction_context(TransactionId, _, AccessToken, _, Connection),
 6213        execute_on_connection(Schema,
 6214                              Connection,
 6215                              ( debug_before(Sql, Schema, OdbcParameters),
 6216                                identify_pre_state_change_values(QueryId, StateChangeType, Connection),
 6217                                cql_access_token_to_user_id(AccessToken, UserId),
 6218                                ( StateChangeType == insert,
 6219                                  statistic_monitored_attribute(Schema, TableName, _) ->
 6220                                    forall((statistic_monitored_attribute(Schema, TableName, MonitoredAttribute),
 6221                                            memberchk(odbc_parameter(Schema, TableName, MonitoredAttribute, ApplicationValue, insert_value, _), OdbcParameters)),
 6222                                           statistic_monitored_attribute_change(Schema, TableName, MonitoredAttribute, ApplicationValue, 1))
 6223                                ; otherwise ->
 6224                                    true
 6225                                ),
 6226                                odbc_data_types_and_inputs(OdbcParameters, OdbcDataTypes, OdbcInputs),
 6227                                log_state_change(Sql, StateChangeType, OdbcInputs),
 6228                                ( odbc_prepare_and_execute(OdbcCachingOption, Connection, FileName, LineNumber, Sql, OdbcDataTypes, OdbcInputs, Result)->
 6229                                    true
 6230                                ; otherwise->
 6231                                    % Some drivers may return SQL_NO_DATA_FOUND if an UPDATE or DELETE affects no rows. In fact,
 6232                                    % even Microsoft says they are supposed to do this:
 6233                                    % "If SQLExecute executes a searched update, insert, or delete statement that does not
 6234                                    %  affect any rows at the data source, the call to SQLExecute returns SQL_NO_DATA."
 6235                                    %   from http://msdn.microsoft.com/en-us/library/windows/desktop/ms713584(v=vs.85).aspx
 6236                                    Result = affected(0)
 6237                                ),
 6238                                ( Result = affected(N) ->
 6239                                    number_of_rows_affected(QueryId, Connection, N),
 6240                                    cql_log([], informational, 'CQL\t~w\t~w\t   ~w row(s) affected\t(~w:~w)', [UserId, TransactionId, N, FileName, LineNumber]),
 6241                                    ( N > 0 ->
 6242                                        identify_insert_row(StateChangeType, QueryId, Schema, TableName, Connection, Identity),
 6243                                        ( StateChangeType == insert->
 6244                                            DebugResult = identity(Identity)
 6245                                        ; otherwise->
 6246                                            DebugResult = Result
 6247                                        ),
 6248                                        identify_post_state_change_values(QueryId, Connection),
 6249                                        call_row_change_hooks(QueryId, Connection)
 6250                                    ; otherwise->
 6251                                        DebugResult = Result,
 6252                                        cleanup_cql_post_state_change_select_sql(QueryId)
 6253                                    )
 6254                                ; Result = row(Identity), dbms(Schema, 'PostgreSQL')->
 6255                                    postgres_identity(QueryId, Identity),
 6256                                    identify_insert_row(StateChangeType, QueryId, Schema, TableName, Connection, _),
 6257                                    number_of_rows_affected(QueryId, Connection, 1),
 6258                                    cql_log([], informational, 'CQL\t~w\t~w\t   Row inserted. Identity ~w\t(~w:~w)', [UserId, TransactionId, Identity, FileName, LineNumber]),
 6259                                    identify_post_state_change_values(QueryId, Connection),
 6260                                    call_row_change_hooks(QueryId, Connection),
 6261                                    DebugResult = identity(Identity)
 6262                                ),
 6263                                debug_after(exit, DebugResult))).
 6264
 6265
 6266
 6267collect_external_variables @
 6268        phase(QueryId, union),
 6269        sql_statement(QueryId, _, _, _, _, _, _, _, _, HalfCompiledOutputs)
 6270        ==>
 6271        strip_compile_instructions(HalfCompiledOutputs, Outputs),
 6272        union_outputs(QueryId, Outputs, []).
 6273
 6274strip_compile_instructions([], []):-!.
 6275strip_compile_instructions([_:Outputs|More], Result):-
 6276        !,
 6277        append(Outputs, O1, Result),
 6278        strip_compile_instructions(More, O1).
 6279strip_compile_instructions([Output|O1], [Output|O2]):-
 6280        strip_compile_instructions(O1, O2).
 6281
 6282
 6283collect_external_variables_1 @
 6284        conjunction_variable(_, ExternalVariable, ConjunctionVariable)
 6285        \
 6286        union_outputs(QueryId, [output(_, _, _, ConjunctionVariable)|Outputs], ExternalVariables)
 6287        <=>
 6288        union_outputs(QueryId, Outputs, [ExternalVariable|ExternalVariables]).
 6289
 6290
 6291do_not_create_a_union_if_there_is_an_order_by @
 6292        phase(QueryId, union),
 6293        order_bys(QueryId, _)
 6294        <=>
 6295        true.
 6296
 6297
 6298union_if_external_variables_the_same_and_there_is_no_order_by @
 6299        phase(QueryIdA, union)
 6300        \
 6301        sql_statement(QueryIdA, SqlTokensA, TokensTailA, SqlFromTokensA, FromTailA, SqlRestrictionTokensA, RestrictionTailA, SelectOdbcParametersA, FromOdbcParametersA, Outputs),
 6302        union_outputs(QueryIdA, [], ExternalVariables),
 6303        phase(QueryIdB, union),
 6304        sql_statement(QueryIdB, SqlTokensB, TokensTailB, SqlFromTokensB, FromTailB, SqlRestrictionTokensB, RestrictionTailB, SelectOdbcParametersB, FromOdbcParametersB, _),
 6305        union_outputs(QueryIdB, [], ExternalVariables)
 6306        <=>
 6307        append(SelectOdbcParametersA, SelectOdbcParametersB, SelectOdbcParameters),
 6308        append(FromOdbcParametersA, FromOdbcParametersB, FromOdbcParameters),
 6309        TokensTailA = SqlFromTokensA,
 6310        FromTailA = SqlRestrictionTokensA,
 6311        RestrictionTailA = [' UNION '|SqlTokensB],
 6312        TokensTailB = SqlFromTokensB,
 6313        FromTailB = SqlRestrictionTokensB,
 6314        RestrictionTailB = NewTail,
 6315        UnionSqlTokens = SqlTokensA,
 6316        sql_statement(QueryIdA, UnionSqlTokens, NewTail, A, A, B, B, SelectOdbcParameters, FromOdbcParameters, Outputs),
 6317        remove_query(QueryIdB, QueryIdA),
 6318
 6319        ( debugging(cql(union)) ->
 6320            prolog_load_context(source, FileName),
 6321            prolog_load_context(term_position, TermPosition),
 6322            stream_position_data(line_count, TermPosition, LineNumber),
 6323            debug(cql(union), 'UNION created ~w:~w~n', [FileName, LineNumber])
 6324        ;
 6325            true
 6326        ).
 6327
 6328
 6329join_has_no_on_clause @
 6330        check_for_unjoined_tables,
 6331        write_join(_, _),
 6332        original_cql(Cql)
 6333        <=>
 6334        throw(format('Generated SQL has a JOIN with no ON clause.  Ensure each table shares a variable with a preceding table: ~w',
 6335                     [Cql])).
 6336
 6337
 6338update_join_has_no_source_table @
 6339        check_for_unjoined_tables,
 6