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(debug)).  107:-use_module(library(cql/sql_parser)).  108:-use_module(library(cql/sql_tokenizer)).  109:-use_module(library(cql/sql_write)).  110:-use_module(library(cql/sql_keywords)).  111:-use_module(library(cql/cql_database)).  112:-reexport(cql_database, [register_database_connection_details/2,
  113                          cql_transaction/3]).  114
  115
  116/** <module> CQL - Constraint Query Language
  117
  118Note that CQL is currently in a state   of flux. Features may be dropped
  119in future releases, and the generated   SQL may change between releases.
  120In particular, _|runtime|_ mode is deprecated.
  121
  122CQL is a Prolog interface to SQL databases. There are two modes: _|fully
  123compiled|_ and _|runtime|_. The _|fully compiled|_   mode should be used
  124if possible due to the far greater compile time checking it provides.
  125
  126## Warnings from CQL {#cql-warnings}
  127
  128### CQL Comparisons with NULL {#cql-compare-null}
  129
  130CQLv2  correctly  compiles  equality  comparisons  with  NULL  into  the
  131appropriate expression at runtime. In CQLv1, executing
  132
  133   ==
  134   A={null}, {[A], foo :: [a-A]}
  135   ==
  136
  137would never succeed, regardless of the value of foo.a. This is no longer
  138the case: If A is `{null}` then this will execute as =|SELECT .... WHERE
  139a IS NULL|= and if A is  not   {null},  it will execute as =|SELECT ....
  140WHERE a = ?|=
  141
  142See the section _|Removing null comparisions|_  for the dealing with the
  143common requirement to ignore comparisons with null.
  144
  145### Avoid setof/3 and bagof/3 in CQL queries {#cql-and-setof}
  146
  147It is generally not a good  idea  to   wrap  CQL  inside  a setof/3 or a
  148bagof/3 ... unless you are prepared  to   declare  all the CQL variables
  149that are neither bound nor mentioned in the setof/bagof template. If you
  150want to sort, use findall/3 followed by   sort/2. Note that sort/2 (like
  151setof/3) removes duplicates. If you don't want to remove duplicates, use
  152msort/2.
  153
  154## CQL: Retrieved nulls have special logic to handle outer joins {#cql-null-outer-jpin}
  155
  156In the course of executing a select query, the following rules are applied:
  157
  158   1. Any selected attribute that is null does not bind its associated variable.
  159   2. Just before returning from the query any select variables that
  160      are still free are bound to {null}.
  161
  162This is so we can handle outer joins.  Consider this:
  163
  164  ==
  165  x :: [a-A] *== y :: [a-A]
  166  ==
  167
  168Assume x.a binds A to a non-null value.   If there is no matching row in
  169`y`, then `y.a = null`. If variable `A` was truly shared the query could
  170never succeed. By not binding  the   variable  associated with `y.a` the
  171query can succeed ( rule 1) and `A` will be bound to the value in `x.a`.
  172
  173## CQL: Getting Started Quickly {#cql-quickstart}
  174
  175Here is a simple example of a SQL SELECT from the table `se_lt_x`
  176
  177  ==
  178  test(A) :-
  179    format('About to call CQL with A=~w', [A]),
  180    {[],
  181    se_lt_x :: [a-A,
  182		b-B,
  183		c-C]},
  184    format('B=~w, C=~w', [B, C]).
  185  ==
  186
  187   * The CQL is distinguished from the ordinary Prolog by appearing
  188     in curly brackets
  189   * Prolog variables which are ground when the CQL is executed will
  190     appear in the resulting SQL as part of the WHERE clause
  191
  192Comparisons can be done in-line e.g.
  193
  194  ==
  195  [a-'ELSTON_M']
  196  ==
  197
  198or with the == operator e.g.
  199
  200  ==
  201  [a-A], A == 'ELSTON_M'.
  202  ==
  203
  204*|The single = operator means unify, not compare. Use = for unification,
  205not comparison|*
  206
  207FIXME: Unification is deprecated.
  208
  209The operators `=:=` and `\==`  are   also  available for numerical value
  210comparisons (they just translate to SQL  `=`   and  `<>`, so in fact you
  211could use them for string comparisons)
  212
  213
  214## Debugging CQL queries {#cql-debugging}
  215
  216You can debug CQL using the meta-predicates ?/1, ??/2 and ???/3:
  217
  218  ==
  219  ???{[], se_lt_x :: [a-A, b-_], A == 'ELSTON_M'}.
  220  ==
  221
  222  $ ?/1 : Display a summary form of the generated SQL before and
  223  after the goal is called.
  224
  225    ==
  226    [main]  CALL   SELECT slx_2.b, slx_2.a  FROM se_lt_x AS slx_2 WHERE slx_2.a = 'ELSTON_M'
  227    [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)
  228    ==
  229
  230  $ ??/1 : Display the exact query (and results) in a format which
  231  can be executed directly by the DBMS (In this case, SQL Server) The
  232  generated SQL may be significantly more complicated than expected, and
  233  this can be used to debug the CQL compiler itself
  234
  235    ==
  236    [main]  CALL
  237    DECLARE @P0 VARCHAR(50);
  238    SET @P0 = 'ELSTON_M';
  239    SELECT slx_450.b,
  240	   slx_450.a
  241    FROM se_lt_x AS slx_450
  242    WHERE slx_450.a = @P0 AND slx_450.a COLLATE Latin1_General_CS_AS = @P0
  243    Result: se_lt_x.b = {null}
  244	    se_lt_x.a = 'ELSTON_M'
  245     (0.003304s, 0.00cpu, 359 inferences)
  246    ==
  247
  248
  249  $ ???/1 : Display simplified SQL before the goal is called and display
  250  the results afterwards
  251
  252    ==
  253    [main]  CALL
  254    SELECT slx_450.b,
  255	   slx_450.a
  256    FROM se_lt_x AS slx_450
  257    WHERE slx_450.a = 'ELSTON_M'
  258    Result: se_lt_x.b = {null}
  259	    se_lt_x.a = 'ELSTON_M'
  260     (0.003304s, 0.00cpu, 359 inferences)
  261    ==
  262
  263
  264## Prolog Variables in CQL queries {#cql-prolog-vars}
  265
  266A Prolog variable  can  be  simultaneously   a  _|SELECT|_  variable,  a
  267_|JOIN|_ variable and a _|WHERE|_ variable  as   A  is  in the following
  268example:
  269
  270  ==
  271  {[],
  272   se_lt_x :: [a-A, c-C]
  273   =*=
  274   se_lt_y :: [d-A, f-F],
  275   A == 'A4'}
  276  ==
  277
  278which generates the following SQL
  279
  280  ==
  281  SELECT
  282    x_192.a, x_192.c, y_73.d, y_73.f
  283   FROM
  284    se_lt_x x_192 INNER JOIN se_lt_y y_73 ON y_73.d=x_192.a
  285   WHERE   x_192.a = ? and y_73.d = ?
  286  ==
  287
  288Note how *|all the variables referenced in  the query|* are retrieved in
  289the SELECT. This is done to make   the query _|Prolog-like|_. This means
  290the retrieved row should behave like a Prolog  fact so that when a query
  291succeeds all the variables become instantiated.
  292
  293There is one notable  exception  however:   *|WHERE  variables  and JOIN
  294variables are not bound in aggregation selections|*
  295
  296FIXME: Is this still the case?
  297
  298  ==
  299  sum_test :-
  300    {[],
  301     #se_lt_x :: [a-ValueA,
  302		  sum(b)-Summation]
  303     =*=
  304     #se_lt_y :: [e-ValueB],
  305
  306     ValueA == ValueB,   % Explicit join point
  307
  308     group_by([ValueA])},
  309
  310    writeln(ValueA-ValueB-Summation).
  311  ==
  312
  313  ==
  314  'ELSTON_M'-_G375971-99450
  315  true ;
  316  ==
  317
  318## CQL Special Attributes {#cql-special-attrs}
  319
  320The following attributes are automatically provided i.e if the attribute
  321is present in the table, CQL will automatically fill in the value:
  322
  323   1. *|generation_|* Set to 0 on INSERT and incremented by 1 on each update
  324
  325   2. *|inserted_|* Set to the current time at the time of the INSERT
  326      transaction
  327
  328   3. *|inserted_by_|*  Set to the user ID corresponding to the access
  329      token supplied to the transaction
  330
  331   4. *|updated_|* Set to the current time at the time of the UPDATE
  332      transaction. Note that updated_ is also set by an INSERT
  333
  334   5. *|updated_by_|* Set to the user ID corresponding to the access
  335      token supplied to the transaction. Note that updated_by_ is also
  336      set by an INSERT
  337
  338   6. *|transaction_id_|* Set to the transaction ID
  339
  340All  the  special  attributes  can  be    overridden  by  supplying  the
  341attribute-value pair explicitly.
  342
  343## CQL Examples {#cql-examples}
  344
  345Rather than provide an abstract description of CQL syntax here is a set of
  346examples that show how to use it.
  347
  348### CQL Simple INSERT {#cql-insert}
  349
  350  ==
  351  {[],
  352   insert(se_lt_x, [a-'A', b-'B', c-100])}
  353  ==
  354
  355### CQL Simple INSERT with retrieval of identity of the inserted {#cql-insert2}
  356
  357==
  358{[],
  359 insert(se_lt_x, [a-'A', b-'B', c-100]),
  360 identity(I)}
  361==
  362
  363### CQL Simple DELETE {#cql-delete}
  364
  365  ==
  366  {[],
  367   delete(se_lt_x, [x_pk-I])}
  368  ==
  369
  370Note that the WHERE clause is part  of the delete/2 term unlike _update_
  371where the WHERE clause is defined  outside   the  update/2 term. I could
  372have made delete consisent with update, but this would have required the
  373@ alias in the delete WHERE clause to  identify the table where the rows
  374are to be deleted). This seems  like   overkill  because a delete can in
  375fact refer to only one table  anyway   i.e.  you  can't identify rows to
  376delete via a JOIN.
  377
  378### CQL Simple SELECT {#cql-select}
  379
  380  ==
  381  {[],
  382   se_lt_x :: [a-A, b-B]}
  383  ==
  384
  385This query will either:
  386
  387   * If A is bound, and B are bound, fail if there are no such
  388     rows, or succeed (without binding anything) the same number of
  389     times as there are matching rows in se_lt_x.
  390
  391   * If A is bound and B is unbound, bind B to each of the values in
  392     `se_lt_x.b` where `se_lt_x.a = A`
  393
  394   * If B is bound and A is unbound, bind A to each of the values in
  395     `se_lt_x.a` where `se_lt_x.b = B`
  396
  397   * If `A` and `B` are both unbound, bind `A` and `B` to each of
  398     the tuples in `se_lt_x`
  399
  400### CQL Simple UPDATE {#cql-update}
  401
  402  ==
  403  {[],
  404   update(se_lt_x, [c-100]),
  405   @ :: [a-'A1'],
  406   row_count(N)}
  407  ==
  408
  409This corresponds to =|UPDATE se_lt_x   SET c=100 WHERE se_lt_x.a='A1'|=.
  410The '@' is a special alias referring to the table that is being updated.
  411The row_count/1 term gives the number or rows updated.
  412
  413### CQL WHERE with arithmetic comparison {#cql-where-arith}
  414
  415  ==
  416  {[],
  417   se_lt_x :: [a-A, c-C],
  418   C > 10}
  419  ==
  420
  421### CQL Simple INNER JOIN {#cql-inner-join}
  422
  423  ==
  424  {[],
  425   se_lt_x :: [a-J1, c-C]
  426    =*=
  427   se_lt_y :: [d-J1, f-F]}
  428  ==
  429
  430The join is `se_lt_x.a = se_lt_y.d` because of the shared variable `J1`.
  431`se_lt_x.c` will be returned in `C` and  `se_lt_y.f` will be returned in
  432`F`
  433
  434### CQL Arithmetic UPDATE with an INNER JOIN and a WHERE restriction {#cql-update-join-where}
  435
  436==
  437{[],
  438 update(se_lt_x, [c-(C + 2 * F + 20)]),
  439 @ :: [a-A, c-C] =*= se_lt_y :: [d-A, f-F],
  440 C < 100}
  441==
  442
  443This joins the table being updated  (table `se_lt_x`) on table `se_lt_y`
  444where `se_lt_x.a = se_lt_y.a` and where   `se_lt_x.c < 200` then updates
  445each identified row `se_lt_x.c` with the specified expression.
  446
  447### CQL: Confirm row does not exist {#cql-not-exists}
  448
  449  ==
  450  \+ exists {[], se_lt_x :: [a-'Z']}
  451  ==
  452
  453### CQL: Aggregation - Count {#cql-count}
  454
  455  ==
  456  {[],
  457   se_lt_x :: [count(c)-C]}
  458  ==
  459
  460This will count the rows in table se_lt_x
  461
  462### CQL: Aggregation - Sum {#cql-sum}
  463
  464  ==
  465  {[],
  466   se_lt_x :: [sum(c)-C]}
  467  ==
  468
  469Sum the values of attribute c in table se_lt_x
  470
  471### CQL: Aggregation - Average {#cql-avg}
  472
  473  ==
  474  {[],
  475   se_lt_x :: [avg(c)-C]}
  476  ==
  477
  478Calculate the mean of the values of attribute c in table se_lt_x
  479
  480### CQL: Maximum Value {#cql-max}
  481
  482  ==
  483  {[],
  484   se_lt_x :: [max(c)-C]}
  485  ==
  486
  487Calculate the maximum of the values of attribute c in table se_lt_x
  488
  489### CQL: Minimum Value {#cql-min}
  490
  491  ==
  492  {[],
  493   se_lt_x :: [min(c)-C]}
  494  ==
  495
  496Calculate the minimum of the values of attribute c in table se_lt_x
  497
  498### CQL: Aggregation requiring GROUP BY {#cql-group-by}
  499
  500  ==
  501  {[],
  502   se_lt_z :: [g-G, sum(i)-I],
  503   group_by([G])}
  504  ==
  505
  506This will generate the =|GROUP BY  SQL|=   and  sum `se_lt_z.i` for each
  507value of `se_lt_z.g`
  508
  509### CQL: INNER JOIN with an aggregation sub-query where the sub-query is constrained by a shared variable from the main query {#cql-agg-subquery}
  510
  511  ==
  512  {[],
  513   se_lt_x :: [b-J1, a-A]
  514     =*=
  515   se_lt_z :: [h-J1, i-I, g-Z],
  516   I > min(Y, se_lt_y :: [f-Y, d-Z])}
  517  ==
  518
  519The main query and the sub-query share variable Z. The generated SQL is:
  520
  521  ==
  522  SELECT
  523    x37.a, z4.i, z4.g
  524   FROM
  525    se_lt_x x37 INNER JOIN se_lt_z z4 ON x37.b=z4.h and z4.h=x37.b
  526   WHERE
  527    z4.i > (SELECT min(y11.f) FROM se_lt_y y11 WHERE z4.g=y11.d)
  528  ==
  529
  530### CQL: INNER JOIN in an aggregation sub-query {#cql-join-subquery}
  531
  532  ==
  533  {[],
  534   se_lt_y :: [d-D,f-F],
  535   F < sum(I,
  536	   se_lt_x :: [b-J1]
  537	     =*=
  538	   se_lt_z :: [h-J1, i-I])}
  539  ==
  540
  541### CQL: Negation {#cql-negation}
  542
  543  ==
  544  {[],
  545   se_lt_x :: [a-A, b-B],
  546   \+ exists se_lt_y :: [d-A]}
  547  ==
  548
  549The generated SQL is:
  550
  551  ==
  552  SELECT
  553    x39.a, x39.b
  554   FROM
  555    se_lt_x x39
  556   WHERE NOT EXISTS (SELECT * FROM se_lt_y y13 WHERE x39.a = y13.d)
  557  ==
  558
  559### CQL: EXISTS {#cql-exists}
  560
  561An exists restriction translates to a =|WHERE|= sub-query and is used to
  562say that "each row  returned  in  the   main  query  must  satisfy  some
  563condition expressed by another query".
  564
  565*Example*
  566
  567  ==
  568  {[],
  569   se_lt_x :: [a-A, b-B],
  570   exists se_lt_y :: [d-A]}
  571  ==
  572
  573compiles to:
  574
  575  ==
  576  SELECT
  577    x.b, x.a
  578  FROM
  579    se_lt_x x
  580  WHERE
  581    EXISTS (SELECT * FROM se_lt_y WHERE x.a = y.d)
  582  ==
  583
  584### CQL: Left Outer Join {#cql-left-outer-join}
  585
  586  ==
  587  se_lt_x :: [a-J1, b-B]
  588    *==
  589  se_lt_y :: [d-J1, e-E]}
  590  ==
  591
  592### CQL: List-based Restrictions {#cql-list-restrictions}
  593
  594CQL supports query restrictions based on lists.  Note that in both cases
  595*|\== [] and  ==  []  are   equivalent|*  despite  the  obvious  logical
  596inconsistency.
  597
  598FIXME: Can we make this behaviour be controlled by a flag? It IS quite useful, even if it is completely illogical
  599
  600  ==
  601  {[], se_lt_x :: [a-Bar], Bar == []}
  602  ==
  603
  604and
  605
  606  ==
  607  {[], se_lt_x :: [a-Bar], Bar \== []}
  608  ==
  609
  610both do *exactly* the same thing  -   they  will  not restrict the query
  611based on Bar. The second case  seems   to  be logically consistent - all
  612things are not in the empty list.
  613
  614### CQL: Compile time in-list constraint {#cql-list-compilation}
  615
  616If your list is bound at compile-time,  you   can  simply  use it as the
  617attribute value in CQL, for example:
  618
  619  ==
  620  {[], se_lt_x :: [a-['ELSTON_M', 'LILLEY_N']]}
  621  ==
  622
  623This does not require the  list  to   be  *ground*,  merely *bound*. For
  624example, this is not precluded:
  625
  626  ==
  627  foo(V1, V2):-
  628      {[], se_lt_x :: [a-[V1, V2]]}.
  629  ==
  630
  631If, however, your list is not bound   at compile-time, you must wrap the
  632variable in list/1:
  633
  634  ==
  635  Bar = [a,b,c],
  636  {[], se_lt_x :: [bar-list(Bar)]}
  637  ==
  638
  639If you write
  640
  641  ==
  642  foo(V1):-
  643      {[], se_lt_x :: [a-V1]}.
  644  ==
  645
  646and at runtime call foo([value1]), you will get a type error.
  647
  648_|Remember:|_ If the list of IN values is _empty_ then no restriction is
  649generated i.e.
  650
  651  ==
  652  {[], se_lt_x :: [a-[], b-B}
  653
  654  is the exactly the same as
  655
  656  {[], se_lt_x :: [b-B}
  657  ==
  658
  659### CQL: Disjunction resulting in OR in WHERE clause {#cql-disjunction}
  660
  661  ==
  662  {[],
  663   se_lt_x :: [a-A, b-B, c-C],
  664   (C == 10 ; B == 'B2', C < 4)}
  665  ==
  666
  667The generated SQL is:
  668
  669  ==
  670  SELECT
  671    x.a, x.b, x.c
  672   FROM
  673    se_lt_x x
  674   WHERE
  675    ((x.b = ? AND x.c < ?) OR x.c = ?)
  676  ==
  677
  678### CQL: Disjunction resulting in different joins (implemented as a SQL UNION) {#cql-disj-diff-joins}
  679
  680  ==
  681  {[],
  682   se_lt_x :: [a-A, c-C]
  683   =*=
  684   (se_lt_y :: [d-A] ; se_lt_z :: [g-A])}
  685  ==
  686
  687The generated SQL is:
  688
  689  ==
  690  SELECT
  691    x43.c
  692   FROM
  693    (se_lt_x x43 INNER JOIN se_lt_z z6 ON x43.a=z6.g AND z6.g=x43.a)
  694
  695  UNION
  696
  697  SELECT
  698    x44.c
  699   FROM
  700    (se_lt_x x44 INNER JOIN se_lt_y y16 ON x44.a=y16.d AND y16.d=x44.a)
  701  ==
  702
  703### CQL: Disjunction resulting in different SELECT attributes (implemented as separate ODBC queries) {#cql-disj-attrs}
  704
  705  ==
  706  {[],
  707   (se_lt_x :: [a-A, c-10]
  708   ;
  709   se_lt_y :: [d-A, f-25])}
  710  ==
  711
  712The output variable  A  is  bound  to   the  value  from  two  different
  713attributes and so the query is implemented as two separate ODBC queries
  714
  715### CQL: ORDER BY {#cql-order-by}
  716
  717==
  718{[],
  719 se_lt_z :: [g-G, h-H],
  720 order_by([-G])}
  721==
  722
  723The order_by specification is a list  of "signed" variables. The example
  724above will order by se_lt_z.g descending
  725
  726### CQL: DISTINCT {#cql-distinct}
  727
  728Use distinct(ListOfVars) to specify which  attributes   you  want  to be
  729distinct:
  730
  731  ==
  732  test_distinct :-
  733    findall(UserName,
  734	    {[],
  735	     se_lt_x :: [a-UserName,
  736			 c-Key],
  737	     Key >= 7,
  738	     distinct([UserName])},
  739	    L),
  740    length(L, N),
  741    format('~w solutions~n', [N]).
  742
  743  CALL  : user:test_distinct/0
  744  26 solutions
  745  EXIT  : user:test_distinct/0 (0.098133s, 0.00cpu, 1,488 inferences)
  746  ==
  747
  748
  749### CQL: SELECT with NOT NULL restriction {#cql-select-not-null}
  750
  751  ==
  752  {[],
  753   se_lt_z :: [i-I, j-J],
  754   J \== {null}}
  755  ==
  756
  757### CQL: First N {#cql-top}
  758
  759  ==
  760  {[],
  761   N = 3,
  762   se_lt_z :: [i-I],
  763   top(N),
  764   order_by([+I])}
  765  ==
  766
  767This generates a TOP  clause  in  SQL   Server,  and  LIMIT  clauses for
  768PostgreSQL and SQLite
  769
  770
  771### CQL: Self JOIN {#cql-self-join}
  772
  773  ==
  774  {[],
  775   se_lt_z :: [h-H, i-I1]
  776    =*=
  777   se_lt_z :: [h-H, i-I2],
  778   I1 \== I2}
  779  ==
  780
  781
  782### CQL: Removing null comparisions {#cql-no-null-compare}
  783
  784Use the ignore_if_null wrapper in your CQL   to  'filter out' null input
  785values. This is a useful extension for creating user-designed searches.
  786
  787  ==
  788  {[],
  789   se_lt_x :: [a-UserName,
  790	       b-ignore_if_null(SearchKey),
  791	       ...]}
  792  ==
  793
  794At runtime, if SearchKey is bound to a  value other than {null} then the
  795query will contain =|WHERE ... b =  ?|=. If, however, SearchKey is bound
  796to `{null}`, then this comparison will be omitted.
  797
  798*Disjunctions*
  799
  800In general, don't use  ignore_if_null   in  disjunctions.  Consider this
  801query:
  802
  803  ==
  804  SearchKey = '%ELSTON%',
  805  {[],
  806   se_lt_x :: [a-UserName,
  807	       b-RealName],
  808   ( RealName =~ SearchKey
  809   ; UserName =~ SearchKey)}
  810  ==
  811
  812The query means "find a user where   the UserName contains ELSTON OR the
  813RealName contain ELSTON". If !SearchKey is {null} then RealName=~ {null}
  814will fail, which is correct. If ignore_if_null  was used, the test would
  815_succeed_, which means the disjunction  would   always  succeed i.e. the
  816query would contain no restriction, which   is  clearly not the intended
  817result. FIXME: Mike, what is this all about?
  818
  819### CQL: Three table JOIN {#cql-three-table-join}
  820
  821  ==
  822  {[],
  823   se_lt_x :: [a-A, c-C]
  824    =*=
  825   se_lt_y :: [d-A, f-F]
  826    =*=
  827   se_lt_z :: [i-F, g-G]}
  828  ==
  829
  830The shared variable A joins `se_lt_x` and `se_lt_y`; the shared variable
  831`F` joins `se_lt_y` and `se_lt_z`
  832
  833### CQL: Three table JOIN with NOLOCK locking hint {#cql-three-nolock}
  834
  835  ==
  836  {[],
  837   se_lt_x :: [a-A, c-C]
  838    =*=
  839   #se_lt_y :: [d-A, f-F]
  840    =*=
  841   #se_lt_z :: [i-F, g-G]}
  842  ==
  843
  844The hash operator indicates the table that should be accessed WITH (NOLOCK)
  845
  846### CQL: SELECT with LIKE {#cql-like}
  847
  848  ==
  849  {[],
  850   se_lt_z :: [g-G, i-I],
  851   G =~ 'A_'}
  852  ==
  853
  854The operator =~ means LIKE. If you are using PostgreSQL, it means ILIKE.
  855
  856### CQL: Writing exceptions directly to the database {#cql-write}
  857
  858You can write an exception term directly to a varchar-type column in the
  859database. Note that it will be rendered  as text using ~p, and truncated
  860if necessary - so you certainly can't read   it  out again and expect to
  861get an exception! Example code:
  862
  863  ==
  864  catch(process_message(Message),
  865	Exception,
  866	{[],
  867	update(some_table, [status-'ERROR',
  868			    status_comment-Exception]),
  869	@ :: [some_table_primary_key-PrimaryKey]}).
  870  ==
  871
  872FIXME: This code is specific to my usage of CQL
  873
  874### CQL: TOP N is Parametric {#cql-top-n-parametric}
  875
  876You can pass the  "N"  is  TOP  N   as  a  parameter  (Subject  to  DBMS
  877compatibility. This works in SQL Server 2005 and later, and PostgreSQL 9
  878(possibly earlier versions) and SQLite3.
  879
  880  ==
  881  N = 3,
  882  findall(I,
  883	  {[],
  884	   se_lt_z :: [i-I], top(N), order_by([+I])},
  885	  L)
  886  ==
  887
  888
  889### CQL: Using compile_time_goal/1 {#cql-compile-time-goal}
  890
  891You can include compile_time_goal(Goal) in your   CQL.  If you specify a
  892module, it will be used,  otherwise  the   goal  will  be  called in the
  893current module. Note that the goal is executed in-order - if you want to
  894use the bindings in your CQL, you  must put the compile_time_goal before
  895them.
  896
  897*|Example 1|*
  898
  899  ==
  900  {[],
  901   se_lt_x :: [a-UserName,
  902	       b-RealName,
  903	       d-FavouriteColour],
  904     compile_time_goal(standard_batch_size_for_search(StandardBatchSize)),
  905     top(StandardBatchSize),
  906     order_by([+UserName]}
  907  ==
  908
  909*|Example 2|*
  910
  911  ==
  912  excellent_colours(['RED', 'BLUE']).
  913
  914  {[],
  915   se_lt_x :: [a-UserName,
  916	       b-RealName,
  917	       d-FavouriteColour],
  918   compile_time_goal(excellent_colours(Colours)),
  919   FavouriteColour == Colours}
  920  ==
  921
  922### CQL: ON {#cql-on}
  923
  924CQL supports both constant and shared variable join specifications. This
  925is particularly useful when specifying outer joins.
  926
  927*Example*
  928
  929  ==
  930  {[],
  931   se_lt_x :: [a-UserNameA,
  932	       b-RealName,
  933	       d-FavouriteColour]
  934   *==
  935   se_lt_x :: [a-UserNameB,
  936	       e-FavouriteFood] on( UserNameA == UserNameB,
  937				    FavouriteColour == FavouriteFood,
  938				    FavouriteFood == 'ORANGE')}
  939  ==
  940
  941All the CQL comparison operators, =|<, =<, ==, =~, \=~, \==, >=, >|= can
  942be used in ON specifications.
  943
  944For example:
  945
  946  ==
  947  {[],
  948   se_lt_z :: [i-J1, k-K]
  949   *==
  950   se_lt_x :: [c-J1, a-A, b-B] on A \== 'A1'},
  951  ==
  952
  953### CQL: Expressions In Where Restrictions {#cql-expr-in-where}
  954
  955Expressions in WHERE restrictions are supported, for example:
  956
  957  ==
  958  {[],
  959   se_lt_n :: [i-I, j-J, k-K],
  960   J > 10 * (K / I) + 15},
  961  ==
  962
  963
  964### CQL: Explicitly avoid the "No WHERE restriction" message {#cql-no-where}
  965
  966To avoid accidentally deleting or  updating  all   rows  in  a table CQL
  967raises an exception if there is no WHERE restriction.
  968
  969Sometimes however you really do need to delete   or update all rows in a
  970table.
  971
  972To support this requirement in  a  disciplined   way  (and  to avoid the
  973creation    of    "dummy"    WHERE      restrictions)     the    keyword
  974*absence_of_where_restriction_is_deliberate*   has   been   added.   For
  975example:
  976
  977  ==
  978  {[],
  979   update(se_lt_x, [c-10]),
  980	  @ :: [],
  981	  absence_of_where_restriction_is_deliberate}
  982  ==
  983
  984### CQL: HAVING {#cql-having}
  985
  986HAVING restrictions can be specified.  For example:
  987
  988  ==
  989  {[],
  990   se_lt_z :: [sum(i)-I,
  991	       g-G],
  992   group_by([G]),
  993   having(I > 30)}
  994  ==
  995
  996For         a         description         of          HAVING         see
  997http://en.wikipedia.org/wiki/Having_(SQL)
  998
  999There is one important  difference  between   SQL  HAVING  and SQL WHERE
 1000clauses. The SQL WHERE clause  condition   is  tested against *|each and
 1001every|* row of data, while the  SQL   HAVING  clause condition is tested
 1002against the _|groups and/or aggregates  specified   in  the SQL GROUP BY
 1003clause and/or the SQL SELECT column list|_.
 1004
 1005
 1006### CQL: INSERT and UPDATE value in-line formatting {#cql-inst-update-value}
 1007
 1008INSERT and UPDATE values can be formatted in-line at runtime.  For example:
 1009
 1010  ==
 1011  Suffix = 'NOGG',
 1012  cql_transaction(Schema, UserId,
 1013		  {[],
 1014		  insert(se_lt_x, [a-'A', b-'B', c-100, d-format('EGG_~w', [Suffix])])}),
 1015  ==
 1016
 1017will insert 'EGG_NOGG' into attribute 'd'.
 1018
 1019### CQL: Negations in WHERE Clauses {#cql-neg-where}
 1020
 1021You can specify negations in CQL WHERE clauses e.g.
 1022
 1023  ==
 1024  {[],
 1025   se_lt_z :: [g-G, h-H, i-I],
 1026   \+((G == 'A1', H == 'B1' ; G == 'D1', H == 'B3'))},
 1027  ==
 1028
 1029Note that, just like  in  Prolog,  \+   is  a  unary  operator hence the
 1030"double" brackets in the example above.
 1031
 1032### CQL: Predicate-generated Attribute Values {#cql-gen-att-values}
 1033
 1034It  is  possible  to  generate  *|compile  time|*  attribute  values  by
 1035specifying a _predicate_ which is  executed   when  the CQL statement is
 1036compiled.
 1037
 1038The predicate must return the value you   want as its last argument. You
 1039specify the predicate where you would  normally put the attribute value.
 1040The predicate is specified _with its output argument missing_.
 1041
 1042*Example* - Using domain allowed values in a query.
 1043
 1044In the following CQL statement  the predicate cql_domain_allowed_value/3
 1045is called within findall/3 *|at compile  time|*   to  generate a list of
 1046domain values that restrict favourite_colour to be 'ORANGE' or 'PINK' or
 1047'BLUE', or 'GREEN'.
 1048
 1049  ==
 1050  colour('ORANGE').
 1051  colour('PINK').
 1052  colour('BLUE').
 1053  colour('GREEN').
 1054
 1055  {[],
 1056   se_lt_x :: [d-findall(Value,
 1057			 permissible_colour(Value)),
 1058	       a-UserName]}
 1059  ==
 1060
 1061Note how findall/3 is actually called by specifying findall/2.
 1062
 1063There is not much point using   predicate-generated  attribute values in
 1064compile-at-runtime CQL as you can always  call the predicate to generate
 1065the required values _outside_ the CQL statement.
 1066
 1067### CQL: INSERT from SELECT {#cql-insert-from-select}
 1068
 1069INSERT from SELECT is supported:
 1070
 1071  ==
 1072  Constant = 'MIKE',
 1073  {[],
 1074   insert(se_lt_x1, [x_pk-Pk, a-A, b-B, c-C, d-Constant]),
 1075   se_lt_x :: [x_pk-Pk, a-A, b-B, c-C, as(d)-Constant]}
 1076  ==
 1077
 1078which generates the following SQL:
 1079
 1080  ==
 1081  INSERT INTO se_lt_x1 (x_pk, a, b, c, d)
 1082  SELECT se_lt_x_955.x_pk, se_lt_x_955.a, se_lt_x_955.b, se_lt_x_955.c, ? AS d
 1083    FROM se_lt_x lt_x_955
 1084  ==
 1085
 1086Note the use of the `as(d)` construct in   the SELECT part of the CQL to
 1087make the constant *|'MIKE'|* appear to come from the SELECT thus setting
 1088`lt_x1.d` to *|'MIKE'|* in every row inserted.
 1089
 1090## CQL: Hooks {#cql-hooks}
 1091
 1092CQL provides a large number of hooks   to  fine-tune behaviour and allow
 1093for customization. These are:
 1094
 1095### CQL: Generated Code Hooks {#cql-code-hooks}
 1096
 1097   * cql:cql_dependency_hook(+EntitySet, +Module) can be defined to be
 1098     notified when a given Module references a list of database
 1099     entities. This can be used to manage metadata/code dependency
 1100
 1101   * cql:cql_generated_sql_hook(+Filename, +LineNumber, +Goals) can be
 1102     defined to examine generated SQL. Use cql_sql_clause(+Goals, -SQL,
 1103     -Parameters) to examine the goals
 1104
 1105   * cql:cql_index_suggestion_hook(+Index) can be defined if you are
 1106     interested in proposed indices for your schema. Note that this is
 1107     not very mature (yet)
 1108
 1109### CQL: Data Representation Hooks {#cql-represent-hooks}
 1110
 1111   * cql:cql_atomic_value_check_hook(+Value) can be defined to declare
 1112     new 'atomic' types (That is, types which can be written directly to
 1113     the database), such as a representation like boolean(true) for 1.
 1114
 1115   * cql:cql_check_value_hook(+Value) can be used to check that a value
 1116     is legal
 1117
 1118   * cql:application_value_to_odbc_value_hook(+OdbcDataType, +Schema,
 1119     +TableName, +ColumnName, +Qualifiers, +ApplicationValue,
 1120     -OdbcValue).
 1121
 1122   * cql:odbc_value_to_application_value_hook(+OdbcDataType, +Schema,
 1123     +TableName, +ColumnName, +Domain, +OdbcValue, -ApplicationValue).
 1124
 1125### CQL: Application Integration {#cql-appl-hooks}
 1126
 1127   * cql:cql_access_token_hook(+AccessToken, -UserId) can be defined to
 1128     map the generic 'AccessToken' passed to cql_transaction/3 to a user
 1129     ID. If not defined, the AccessToken is assumed to be the user ID.
 1130     This UserID is used in logging.
 1131
 1132   * cql:log_selects can be defined if you want to receive logging
 1133     information about selects. By default only update, delete and
 1134     insert are logged
 1135
 1136   * cql:cql_execution_hook(+Statement, +OdbcParameters,
 1137     +OdbcParameterDataTypes, -Row) can be defined if you want to
 1138     implement the exeuction yourself (for example, to add extra
 1139     debugging)
 1140
 1141   * cql:cql_log_hook(+Topics, +Level, +Format, +Args) can be defined
 1142     to redirect CQL logging.
 1143      * Levels is one of informational, warning, or error
 1144      * Topics is a list of topics. Currently the only lists possible
 1145        are [] and [debug(deadlocks)]
 1146
 1147   * cql:sql_gripe_hook(+Level, +Format, +Args) is called when
 1148     suspect SQL is found by the SQL parser
 1149
 1150   * cql:cql_normalize_atom_hook(+DBMS, +ApplciationAtom, -DBMSAtom)
 1151     can be used to create a map for atoms in a specific DBMS. For
 1152     example, your schema may have arbitrarily long table names, but
 1153     your DBMS may only allow names up to 64 bytes long. In this case,
 1154     you can create a scheme for mapping the application-level atom to
 1155     the DBMS. Other uses include deleting or normalizing illegal
 1156     characters in names
 1157
 1158   * cql:cql_error_hook(+ErrorId, +Format, +Args) can be defined to
 1159     generate a specific exception term from the given arguments. If not
 1160     defined (or if it does not throw an exception, or fails), you will
 1161     get cql_error(ErrorId, FormattedMessage).
 1162
 1163   * cql:cql_max_db_connections_hook(-Max)
 1164     can be defined to limit the number of simultaneous connections each
 1165     thread will attempt to have
 1166
 1167   * cql:odbc_connection_complete_hook(+Schema, +Details, +Connection)
 1168     can be hooked if you want to know every time a connection is made
 1169
 1170   * cql:cql_transaction_info_hook(+AccessToken, +Connection, +DBMS, +Goal, -Info)
 1171     can be defined if you want to define any application-defined
 1172     information on a per-transaction level. This can be recovered via
 1173     database_transaction_query_info(?ThreadId, ?Goal, ?Info).
 1174
 1175### CQL: Inline values {#cql-inline-hooks}
 1176
 1177   * cql:cql_inline_domain_value_hook(+DomainName, +Value)
 1178     can be defined if you want the given value to be 'inlined' into the
 1179     CQL (ie not supplied as a parameter). Great care must be taken to
 1180     avoid SQL injection attacks if this is used.
 1181
 1182### CQL: Schema {#cql-shema-hook}
 1183
 1184These define the  schema.  You  MUST   either  define  them,  or include
 1185library(cql/cql_autoschema) and add two directives   to build the schema
 1186automatically:
 1187
 1188   * :-register_database_connection_details(+Schema, +ConnectionInfo).
 1189   * :-build_schema(+Schema).
 1190
 1191Otherwise,  you  need  to  define   at  least  cql:default_schema/1  and
 1192cql:dbms/2, and then as many  of  the   other  facts  as needed for your
 1193schema.
 1194
 1195   * cql:default_schema(-Schema)
 1196     MUST be defined. CQL autoschema will define this for you if you use
 1197     it.
 1198
 1199   * cql:dbms(+Schema, -DBMS)
 1200   MUST be defined for every schema you use. CQL autoschema will define
 1201   this for you if you use it. DBMS must be one of the following:
 1202
 1203      * 'Microsoft SQL Server'
 1204      * 'PostgreSQL'
 1205      * 'SQLite'
 1206
 1207   * cql:odbc_data_type(+Schema, +TableName, +ColumnName, +OdbcDataType).
 1208   * cql:primary_column_name(+Schema, +Tablename, +ColumnName).
 1209   * cql:database_attribute(+EntityType:table/view, +Schema:atom, +EntityName:atom, +ColumnName:atom, +DomainOrNativeType:atom, +AllowsNulls:allows_nulls(true/false), +IsIdentity:is_identity(true/false), +ColumnDefault).
 1210   * cql:database_domain(+DomainName, +OdbcDataType).
 1211   * cql:routine_return_type(+Schema, +RoutineName, +OdbcDataType).
 1212   * cql:database_constraint(+Schema, +EntityName, +ConstraintName, +Constraint).
 1213
 1214### CQL: Event Processing and History {#cql-events-and-history}
 1215
 1216CQL provides hooks for  maintaining  detailed   history  of  data in the
 1217database.
 1218
 1219The hook predicates are:
 1220
 1221   * cql:cql_event_notification_table(+Schema, +TableName)
 1222   * cql:cql_history_attribute(+Schema, +TableName, +ColumnName)
 1223   * cql:cql_update_history_hook(+Schema, +TableName, +ColumnName, +PrimaryKeyColumnName, +PrimaryKeyValue, +ApplicationValueBefore, +ApplicationValueAfter, +AccessToken, +TransactionId, +TransactionTimestamp, +ThreadId, +Connection, +Goal).
 1224   * cql:process_database_events(+Events)
 1225
 1226Event  Processing  and  History  recording  can   be  suppressed  for  a
 1227particular   update/insert/delete   statement    by     including    the
 1228_no_state_change_actions_9 directive.
 1229
 1230For example
 1231
 1232  ==
 1233  {[],
 1234   update(se_lt_x, [f-'LILAC']
 1235   @ :: [a-'ELSTON_M'],
 1236   no_state_change_actions,   % Don't want history to record this change
 1237   row_count(RowCount)}
 1238  ==
 1239
 1240### CQL: Statistical Hooks {#cql-statistics-hooks}
 1241
 1242CQL has hooks to enable in-memory statistics  to be tracked for database
 1243tables. Using this hook, it's possible to  monitor the number of rows in
 1244a table with a particular value in a particular column.
 1245
 1246Often the kind of statistics of  interest   are  'how  many rows in this
 1247table are in ERROR' or 'how many in this table are at NEW'? While it may
 1248be possible to maintain these directly in any code which updates tables,
 1249it can be difficult to ensure all  cases are accounted for, and requires
 1250developers to remember which attributes are tracked.
 1251
 1252To ensure that all (CQL-originated) updates   to  statuses are captured,
 1253it's possible to use the CQL hook   system to update them automatically.
 1254Define add a fact like:
 1255
 1256  ==
 1257  cql_statistic_monitored_attribute_hook(my_schema, my_table,
 1258					 my_table_status_column).
 1259  ==
 1260
 1261This will examine the domain   for  the column 'my_table_status_column',
 1262and       generate       a       statistic       for       each       of
 1263my_table::my_table_status_column(xxx),  where  xxx  is    each  possible
 1264allowed value for the domain. Code   will  be automatically generated to
 1265trap updates to this specific column, and  maintain the state. This way,
 1266if you are interested in the number  of   rows  in my_table which have a
 1267status       of       'NEW',        you         can        look       at
 1268my_table::my_table_status_column('NEW'), without having to   manage  the
 1269state directly. CQL update  statements  which   affect  the  status will
 1270automatically maintain the statistics.
 1271
 1272The calculations are vastly simpler than the history mechanism, so as to
 1273keep performance as high as possible. For   inserts, there is no cost to
 1274monitoring the table (the insert simply  increments the statistic if the
 1275transaction completes). For deletes, the delete query  is first run as a
 1276select, aggregating on the  monitored  columns   to  find  the number of
 1277deletes for each domain allowed  value.  This   means  that  a delete of
 1278millions of rows might requires a select returning only a single row for
 1279statistics purposes. For updates,  the  delete   code  is  run, then the
 1280insert calculation is done, multiplied by the number of rows affected by
 1281the update.
 1282
 1283In       all       cases,       CQL         ends        up       calling
 1284cql_statistic_monitored_attribute_change_hook/5, where the last argument
 1285is a signed value indicating the number   of  changes to that particular
 1286statistic.
 1287*/
 1288
 1289:-chr_option(line_numbers, on). 1290:-chr_option(check_guard_bindings, error). 1291:-chr_option(debug, off). 1292:-chr_option(optimize, full). 1293:-chr_option(guard_simplification, off). % Added to stop trail overflowing
 1294
 1295:-chr_type list(T) ---> [] ; [T|list(T)]. 1296
 1297:-chr_type 'AggregationOperator' ---> count ; max ; min ; avg ; sum. 1298:-chr_type 'AggregationVariable' == any. 1299:-chr_type 'ApplicationValue' == any. 1300:-chr_type 'DebugMode' ---> explicit. 1301:-chr_type 'DictinctionType' ---> no_distinction ; distinct_on_specified_attributes ; distinct_on_all_select_attributes. 1302:-chr_type 'Dsn' == any. 1303:-chr_type 'Attribute' ---> attribute('Schema', 'TableAlias', 'AttributeName'). 1304:-chr_type 'AttributeName' == any. 1305:-chr_type 'AttributeNameValuePair' ---> 'AttributeName'-'ApplicationValue'. 1306:-chr_type 'BooleanOperator' ---> and ; or. 1307:-chr_type 'ComparisonOperator' ---> < ; =< ; == ; \== ; >= ; > ; (=~) ; (=\=) ; (=:=). 1308:-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'). 1309:-chr_type 'CompileMode' ---> runtime ; compiletime. 1310:-chr_type 'ConjunctionGoal' == any. 1311:-chr_type 'ConjunctionVariable' == any. 1312:-chr_type 'Connection' == any. 1313:-chr_type 'Constraints' == any. 1314:-chr_type 'ClockTime' == any. 1315:-chr_type 'CpuTime' == any. 1316:-chr_type 'Cql' == any. 1317:-chr_type 'Disposition' ---> top ; where ; having ; join. 1318:-chr_type 'EqualityRestrictionVariableUsed' ---> equality_restriction_variable_used. 1319:-chr_type 'Expression' == any. 1320:-chr_type 'ExternalVariable' == any. 1321:-chr_type 'FileName' == any. 1322:-chr_type 'Format' == any. 1323:-chr_type 'FormatArg' == any. 1324:-chr_type 'Goal' == any. 1325:-chr_type 'Having' == any. 1326:-chr_type 'Identity' == int. 1327:-chr_type 'Inferences' == any. 1328:-chr_type 'InputVariable' == 'Variable'. 1329:-chr_type 'Join' == any. 1330:-chr_type 'JoinTreeNode' == any. 1331:-chr_type 'JoinType' ---> 'INNER JOIN' ; 'LEFT OUTER JOIN' ; 'RIGHT OUTER JOIN'. 1332:-chr_type 'Keep' ---> 1. 1333:-chr_type 'LineNumber' == int. 1334:-chr_type 'N' == int. 1335:-chr_type 'OdbcCachingOption' ---> do_not_cache_odbc_statement ; cache_odbc_statement. 1336:-chr_type 'OdbcDataType' ---> varchar(int) ; decimal(int, int) ; timestamp ; integer ; bit. 1337:-chr_type 'LogicalType' ---> varchar ; decimal ; timestamp ; integer ; boolean. 1338:-chr_type 'OdbcInput' == any. 1339:-chr_type 'OdbcOutput' == any. 1340:-chr_type 'OdbcParameter' ---> odbc_parameter('Schema', 'TableName', 'AttributeName', 'ApplicationValue', 'OdbcParameterUse', 'OdbcDataType')   % OdbcDataType unbound if no override required
 1341                              ; odbc_explicit_type_parameter('OdbcDataType', 'ApplicationValue', 'OdbcParameterUse'). 1342:-chr_type 'OdbcParameterUse' ---> insert_value ; update_value ; evaluated_update_attribute ; evaluated_update_parameter ; where_value ; top_value. 1343:-chr_type 'On' == any. 1344:-chr_type 'OrderBy' ---> +('Variable') ; -('Variable'). 1345:-chr_type 'Output' ---> output('Schema', 'TableName', 'AttributeName', 'Variable') ;
 1346                         ignore_output ;
 1347                         count('Variable') ;
 1348                         avg('Variable') ;  % PostgreSQL
 1349                         selection_constant('Schema', 'TableName', 'AttributeName', 'Variable'). 1350:-chr_type 'Phase' ---> initial ; distinct ; top ; select_attributes ; from ; where ; group_by ; having ; order_by ; union ; limit. 1351:-chr_type 'PreparedStatement' == any. 1352:-chr_type 'PrimaryKeyAttributeName' == 'AttributeName'. 1353:-chr_type 'PrimaryKeyValue' == any. 1354:-chr_type 'QueryId' == any. 1355:-chr_type 'QueryLevel' ---> top_level_query ; sub_query. 1356:-chr_type 'Reason' == any. 1357:-chr_type 'ResultSpec' == any. 1358:-chr_type 'Resolved' ---> resolved. 1359:-chr_type 'RestrictionExpression' == any. 1360:-chr_type 'RestrictionType' ---> where ; having ; join. 1361:-chr_type 'RestrictionTree' ---> true ;
 1362                                  comparison('ApplicationValue', 'ComparisonOperator', 'ApplicationValue') ;
 1363                                  sub_query('SubQueryType', list('SqlToken'), 'Tail', list('OdbcParameter')) ;
 1364                                  and('RestrictionTree', 'RestrictionTree') ;
 1365                                  or('RestrictionTree', 'RestrictionTree'). 1366:-chr_type 'Row' == any. 1367:-chr_type 'StateChangeType' ---> insert ; update ; delete. 1368:-chr_type 'QueryType' ---> insert ; update ; delete ; select. 1369:-chr_type 'Schema' == any. 1370:-chr_type 'SelectAttribute' ---> select_attribute('SelectBindingType', 'Schema', 'TableName', 'TableAlias', 'AttributeName'). 1371:-chr_type 'SelectAttributeWithSize' ---> 'Size'-'SelectAttributeInfo'. 1372:-chr_type 'SelectAttributeInfo' ---> select_info('CompilationInstruction', list('SqlToken'), 'Tail', 'Output'). 1373:-chr_type 'SelectAttributeVariableUsed' ---> select_attribute_variable_used. 1374:-chr_type 'SelectBindingType' ---> plain ; aggregation('AggregationOperator'). 1375:-chr_type 'SelectionType' ---> aggregation_selection ; non_aggregation_selection. 1376:-chr_type 'Side' ---> lhs ; rhs. 1377:-chr_type 'Size' == any. 1378:-chr_type 'SqlToken' == any. 1379:-chr_type 'SqlComparisonOperator' ---> < ; <= ; = ; <> ; >= ; > . 1380:-chr_type 'SubQueryType' ---> exists ; \+ . 1381:-chr_type 'Tail' == any. 1382:-chr_type 'TableAlias' == any. 1383:-chr_type 'TableName' == any. 1384:-chr_type 'Variable' == any. 1385:-chr_type 'VariablePair' ---> 'Variable'-'Variable'. 1386:-chr_type 'When' ---> pre_state_change ; post_state_change. 1387
 1388
 1389
 1390:-chr_constraint absence_of_where_restriction_is_deliberate. 1391:-chr_constraint add_on(-'Join', ?'On'). 1392:-chr_constraint aggregation_sub_query(-'QueryId', ?'TableName', ?'TableAlias', ?list('SqlToken'), ?'Tail', ?list('ApplicationValue')). 1393:-chr_constraint aggregation_variable(-'QueryId', +'AggregationOperator', ?'AggregationVariable'). 1394:-chr_constraint attribute_binding(-'QueryId', ?'Attribute', ?'ApplicationValue'). 1395:-chr_constraint attribute_for_group_by(-'QueryId', ?'TableAlias', +'AttributeName', ?'Variable'). 1396:-chr_constraint attribute_for_order_by(-'QueryId', ?'TableAlias', +'AttributeName', ?'Variable'). 1397:-chr_constraint attribute_to_check(+'Schema', +'TableName', ?'AttributeNameValuePair'). 1398:-chr_constraint attributes_to_check(-'QueryId', +'Schema', +'TableName', ?list('AttributeNameValuePair')). 1399:-chr_constraint call_history_hook(-'QueryId', +'Connection'). 1400:-chr_constraint call_row_change_hooks(-'QueryId', +'Connection'). 1401:-chr_constraint check_for_orphan_distincts. 1402:-chr_constraint check_for_orphan_group_bys. 1403:-chr_constraint check_for_orphan_order_bys. 1404:-chr_constraint check_for_orphan_select_attributes_in_aggregations. 1405:-chr_constraint check_for_orphan_select_variables_in_updates. 1406:-chr_constraint check_for_top_without_order_by. 1407:-chr_constraint check_for_unjoined_tables. 1408:-chr_constraint check_query. 1409:-chr_constraint cleanup_compile. 1410:-chr_constraint cleanup_cql_post_state_change_select_sql(-'QueryId'). 1411:-chr_constraint collect_indices(-'QueryId'). 1412:-chr_constraint collect_runtime_constraints(?'Constraints'). 1413:-chr_constraint collect_select_attributes(-'QueryId', +list('SelectAttributeWithSize')). 1414:-chr_constraint comparison(-'QueryId', ?'ApplicationValue', +'ComparisonOperator', ?'ApplicationValue'). 1415:-chr_constraint compile_mode(+'CompileMode'). 1416:-chr_constraint conjunction_constraints(?'Constraints'). 1417:-chr_constraint conjunction_goal(?'ConjunctionGoal'). 1418:-chr_constraint conjunction_variable(-'QueryId', ?'ExternalVariable', ?'ConjunctionVariable'). 1419:-chr_constraint copy_of_from(-'QueryId', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1420:-chr_constraint find_copy_of_from(-'QueryId', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1421:-chr_constraint cql2_variable(-'QueryId', -'Variable', ?'RestrictionTree'). 1422:-chr_constraint cql_execute(+'OdbcCachingOption'). 1423:-chr_constraint cql_fully_compiled. 1424:-chr_constraint cql_identity(-'QueryId', +'Schema', ?'Identity'). 1425:-chr_constraint cql_odbc_select_statement(+'Schema', +'SqlToken', ?list('OdbcParameter'), ?list('Output')). 1426:-chr_constraint cql_odbc_state_change_statement(-'QueryId', +'StateChangeType', +'Schema', +'TableName', +'SqlToken', ?list('OdbcParameter'), ?list('Output')). 1427:-chr_constraint cql_post_state_change_select_sql(-'QueryId', +list('AttributeName'), +'OdbcDataType', +'SqlToken'). 1428:-chr_constraint cql_pre_state_change_select_sql(-'QueryId', +'Schema', +'TableName', +'AttributeName', +'SqlToken', +list('AttributeName'), +list('OdbcParameter')). 1429:-chr_constraint cql_state_change_statistics_sql(-'QueryId', +'Schema', +'TableName',  +'StateChangeType', +'SqlToken', +list('AttributeName'), ?list('OdbcParameter'), ?list('OdbcParameter')). 1430:-chr_constraint cql_statement_location(+'FileName', +'LineNumber'). 1431:-chr_constraint create_cql_pre_state_change_select_sql(-'QueryId', +'StateChangeType', +list('SqlToken'), +'TableName', ?list('OdbcParameter')). 1432:-chr_constraint create_cql_state_change_statistics_sql(-'QueryId', +'StateChangeType', +list('SqlToken'), +'TableName', ?list('OdbcParameter')). 1433:-chr_constraint create_in_line_joins. 1434:-chr_constraint create_join_points. 1435:-chr_constraint create_restrictions. 1436:-chr_constraint create_select_bindings. 1437:-chr_constraint debug_after(+'Reason', ?'ResultSpec'). 1438:-chr_constraint debug_before(+'Format', +'Schema', +'FormatArg'). 1439:-chr_constraint debug_statistics(+'CpuTime', +'ClockTime', +'Inferences'). 1440:-chr_constraint delete_row(-'QueryId', +'TableName', ?'TableAlias'). 1441:-chr_constraint determine_select_distinction(-'QueryId'). 1442:-chr_constraint determine_select_distinctions. 1443:-chr_constraint determine_selection_type. 1444:-chr_constraint dictionary_addendum(-'QueryId', ?'ExternalVariable', ?'ConjunctionVariable'). 1445:-chr_constraint dictionary_lookup(-'QueryId', ?'ExternalVariable', ?'ConjunctionVariable'). 1446:-chr_constraint distinct(-'QueryId', -'Variable'). 1447:-chr_constraint distincts(-'QueryId', ?list('Variable')). 1448:-chr_constraint equality_restriction_variable(?'ApplicationValue', ?'EqualityRestrictionVariableUsed'). 1449:-chr_constraint event(-'QueryId'). 1450:-chr_constraint expression_where_restriction_variable(?'Variable'). 1451:-chr_constraint fully_compile. 1452:-chr_constraint generate_sub_query_sql. 1453:-chr_constraint get_conjunction_constraints(?'Constraints'). 1454:-chr_constraint group_by(-'QueryId', -'Variable'). 1455:-chr_constraint group_bys(-'QueryId', ?list('Variable')). 1456:-chr_constraint identify_insert_row(+'StateChangeType', -'QueryId', +'Schema', +'TableName', +'Connection', ?'Identity'). 1457:-chr_constraint identify_post_state_change_values(-'QueryId', +'Connection'). 1458:-chr_constraint identify_pre_state_change_values(-'QueryId', +'StateChangeType', +'Connection'). 1459:-chr_constraint ignore_if_null(?'Variable', ?'Variable'). 1460:-chr_constraint implicit_join(-'QueryId', +'TableAlias', -'QueryId'). % PostgreSQL only
 1461:-chr_constraint implicit_join_link(-'QueryId', -'QueryId'). % PostgreSQL only
 1462:-chr_constraint implicit_join_sql(-'QueryId', ?list('SqlToken'), ?'Tail'). % PostgreSQL only
 1463:-chr_constraint fetch_implicit_join_sql(-'QueryId', ?list('SqlToken'), ?'Tail'). % PostgreSQL only
 1464:-chr_constraint in_line_format(-'QueryId', +'Format', ?list('FormatArg'), ?'ApplicationValue'). 1465:-chr_constraint include_select_attribute(-'QueryId', ?'CompilationInstruction', +'Size', +list('SqlToken'), ?'Tail', ?'Output'). 1466:-chr_constraint insert(-'QueryId', +'Schema', +'TableName', +list('AttributeNameValuePair')). 1467:-chr_constraint instantiate_table_aliases. 1468:-chr_constraint join(-'QueryId', -'Join', -'Join', +'JoinType', -'Join'). 1469:-chr_constraint join_alias(-'Join', +'Side', ?'TableAlias'). 1470:-chr_constraint join_leaf(-'Join', ?'TableAlias'). 1471:-chr_constraint join_on(?'TableAlias', +'AttributeName', ?'TableAlias', +'AttributeName'). 1472:-chr_constraint join_pointer(-'QueryId', -'Join'). 1473:-chr_constraint join_tree_node(-'QueryId', -'Join', +'JoinTreeNode'). 1474:-chr_constraint join_tree_nodes(-'QueryId', +list('JoinTreeNode')). 1475:-chr_constraint join_variable(?'Variable'). 1476:-chr_constraint limit(-'QueryId', +'Schema', +'N'). 1477:-chr_constraint log_select(+'SqlToken', +list('OdbcInput')). 1478:-chr_constraint log_state_change(+'SqlToken', +'StateChangeType', +list('OdbcInput')). 1479:-chr_constraint next_group_by_attribute_needs_comma(-'QueryId'). 1480:-chr_constraint next_in_list_value_needs_comma(-'QueryId'). 1481:-chr_constraint next_order_by_attribute_needs_comma(-'QueryId'). 1482:-chr_constraint no_debug. 1483:-chr_constraint no_sql_statement_generated. 1484:-chr_constraint no_state_change_actions(-'QueryId'). 1485:-chr_constraint no_where_restriction(+'StateChangeType'). 1486:-chr_constraint not_a_singleton(+'Variable'). 1487:-chr_constraint nolock(-'QueryId', ?'TableAlias'). 1488:-chr_constraint number_of_rows_affected(-'QueryId', +'Connection', ?'N'). 1489
 1490:-chr_constraint odbc_select_disjunction(?'Goal'). 1491%:-chr_meta_predicate(odbc_select_disjunction(0)).
 1492
 1493:-chr_constraint odbc_select_statement(+'Schema', +'SqlToken', ?list('OdbcParameter'), ?list('Output')). 1494:-chr_constraint on(-'Join', ?'Resolved', ?'On'). 1495:-chr_constraint order_bys(-'QueryId', ?list('OrderBy')). 1496:-chr_constraint original_cql(?'Cql'). 1497:-chr_constraint original_human_query(?'Cql'). 1498:-chr_constraint outer_side_join(-'Join'). 1499:-chr_constraint phase(-'QueryId', +'Phase'). 1500:-chr_constraint post_execute_cleanup. 1501:-chr_constraint post_state_change_select_statement(-'QueryId', +list('AttributeName'), +'OdbcDataType', +'PreparedStatement'). 1502:-chr_constraint postgres_identity(-'QueryId', ?'Identity'). 1503:-chr_constraint prepare_odbc_statements. 1504:-chr_constraint prior_to_execution. 1505:-chr_constraint query(-'QueryId', +'Schema', +'QueryLevel'). 1506:-chr_constraint query_table_alias(-'QueryId', +'Schema', +'TableName', ?'TableAlias'). 1507:-chr_constraint query_type(-'QueryId', +'QueryType'). 1508:-chr_constraint remove_query(-'QueryId', -'QueryId'). 1509:-chr_constraint resolve_join_points(-'Join', ?'On', ?'On'). 1510:-chr_constraint resolve_join_points. 1511:-chr_constraint restriction_leaf(-'QueryId', +'Disposition', ?'RestrictionTree'). 1512:-chr_constraint restriction_tree(-'QueryId', +'Disposition', ?'RestrictionTree'). 1513:-chr_constraint row_count(-'QueryId', ?'N'). 1514:-chr_constraint runtime_constraints(?'Constraints'). 1515:-chr_constraint search_for_join_aliases(-'Join', +'Side', -'Join'). 1516:-chr_constraint select_attribute(-'QueryId', ?'SelectAttribute', ?'Keep', ?'SelectAttributeVariableUsed', ?'Variable'). 1517:-chr_constraint select_attribute_for_disjunction_comparison(-'QueryId', ?'SelectAttribute'). 1518:-chr_constraint select_attribute_written(-'QueryId'). 1519:-chr_constraint select_attributes_for_disjunction_comparison(-'QueryId', ?list('SelectAttribute')). 1520:-chr_constraint select_binding(-'QueryId', ?'SelectBindingType', ?'Attribute', ?'ApplicationValue'). 1521:-chr_constraint select_distinction(-'QueryId', +'DictinctionType'). 1522:-chr_constraint select_for_insert_variable(-'QueryId', ?'Variable', +'TableName'). 1523:-chr_constraint select_for_insert_variables(?list('Variable'), +'TableName'). 1524:-chr_constraint selection_type(-'QueryId', +'SelectionType'). 1525:-chr_constraint show_debug(+'DebugMode'). 1526:-chr_constraint simplify. 1527:-chr_constraint solve. 1528:-chr_constraint sql_not(-'QueryId', -'QueryId'). 1529:-chr_constraint sql_statement(-'QueryId', +list('SqlToken'), ?'Tail', +list('SqlToken'), ?'Tail', +list('SqlToken'), ?'Tail', ?list('OdbcParameter'), ?list('OdbcParameter'), ?list('Output')). 1530:-chr_constraint state_change_query(-'QueryId', +'StateChangeType', +'Schema', +'TableName'). 1531:-chr_constraint state_change_value(-'QueryId', +'StateChangeType', +'When', +'Schema', +'TableName', +'PrimaryKeyAttributeName', +'PrimaryKeyValue', +'AttributeName', +'OdbcOutput'). 1532:-chr_constraint store_equality_restriction_variables(?list('Variable')). 1533:-chr_constraint store_ignore_if_null_variables(?list('VariablePair')). 1534:-chr_constraint sub_query(-'QueryId', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1535:-chr_constraint sub_query_join_variable(?'Variable'). 1536:-chr_constraint sub_query_restriction(-'QueryId', +'SubQueryType', ?list('SqlToken'), ?'Tail', ?list('OdbcParameter')). 1537:-chr_constraint sub_query_select(-'QueryId'). 1538:-chr_constraint referenced_table(+'TableName'). 1539:-chr_constraint referenced_tables(?list('TableName')). 1540:-chr_constraint representative_attribute(?'Expression', +'Schema', -'TableName', -'AttributeName'). 1541:-chr_constraint runtime_instantiation_check(-'QueryId', -'Variable'). 1542:-chr_constraint tables_to_remove(+'Schema', +list('Identity')). 1543:-chr_constraint temporary_table(+'Schema', +'Identity'). 1544:-chr_constraint temporary_tables(+'Schema', +list('Identity')). 1545:-chr_constraint top(-'QueryId', +'Schema', +'N'). 1546:-chr_constraint unify(?'Variable', ?'Variable'). 1547:-chr_constraint unify_ignore_if_null_variables. 1548:-chr_constraint union_outputs(-'QueryId', ?list('Output'), ?list('Variable')). 1549:-chr_constraint update(-'QueryId', +'Schema', +'TableName', ?'TableAlias', ?list('AttributeNameValuePair')). 1550:-chr_constraint update_table_alias(-'QueryId', +'Schema', -'Join', ?'TableAlias'). 1551:-chr_constraint update_table_key(-'QueryId', +'Schema', ?list('AttributeNameValuePair')). 1552:-chr_constraint updated_row(-'QueryId', +'StateChangeType', +'When', +'Schema', +'TableName', +'PrimaryKeyAttributeName', +'PrimaryKeyValue', +list('AttributeNameValuePair')). 1553:-chr_constraint updated_row_primary_key(-'QueryId', +'StateChangeType', +'Schema', +'TableName', +'PrimaryKeyAttributeName', +'PrimaryKeyValue'). 1554:-chr_constraint updated_rows(-'QueryId', +'StateChangeType', +'When', +'Schema', +'TableName', +'AttributeName', +list('AttributeName'), +list('Row')). 1555:-chr_constraint variables_to_attributes(?'Expression', ?'Expression'). 1556:-chr_constraint where_restriction_variable(?'Variable'). 1557:-chr_constraint write_expression(-'QueryId', +'Schema', +'TableName', +'AttributeName', ?'TableAlias', ?'Expression'). 1558:-chr_constraint write_group_by_attribute(-'QueryId', ?list('SqlToken'), ?'Tail'). 1559:-chr_constraint write_group_bys(-'QueryId'). 1560:-chr_constraint write_in_list(-'QueryId', +'Disposition', +'Schema', +'TableName', +'AttributeName', +list('ApplicationValue')). 1561:-chr_constraint write_insert_attribute_name(-'QueryId', +'AttributeName'). 1562:-chr_constraint write_insert_attribute_names(-'QueryId', +list('AttributeNameValuePair')). 1563:-chr_constraint write_insert_value(-'QueryId', +'Schema', +'TableName', +'AttributeName', ?'ApplicationValue'). 1564:-chr_constraint write_insert_values(-'QueryId', +'Schema', +'TableName', ?list('AttributeNameValuePair')). 1565:-chr_constraint write_join(-'QueryId', -'Join'). 1566:-chr_constraint write_join_ons(-'QueryId', ?'On'). 1567:-chr_constraint write_limit. 1568:-chr_constraint write_lock_hint(-'QueryId', +'Schema', ?'TableAlias'). 1569:-chr_constraint write_order_by(-'QueryId', ?'OrderBy'). 1570:-chr_constraint write_order_by_attribute(-'QueryId', ?list('SqlToken'), ?'Tail'). 1571:-chr_constraint write_order_bys(-'QueryId', ?list('OrderBy')). 1572:-chr_constraint write_query_sql. 1573:-chr_constraint write_restriction(-'QueryId', ?'CompilationInstruction', +'Disposition', ?'ApplicationValue', +'ComparisonOperator', ?'ApplicationValue'). 1574:-chr_constraint write_restriction_1(-'QueryId', ?'CompilationInstruction', +'Disposition', +'OdbcDataType', ?'OdbcDataType', +'Schema', +'TableName', +'AttributeName', ?'RestrictionExpression', +'ComparisonOperator', ?'RestrictionExpression'). 1575:-chr_constraint write_restriction_expression(-'QueryId', ?'CompilationInstruction', +'Disposition', ?'OdbcDataType', +'OdbcDataType', +'Schema', +'TableName', +'AttributeName', ?'RestrictionExpression'). 1576:-chr_constraint write_restriction_tree(-'QueryId', +'Disposition', ?'RestrictionTree'). 1577:-chr_constraint write_select_attribute(-'QueryId', ?'CompilationInstruction', ?list('SqlToken'), ?'Tail', ?'Output'). 1578:-chr_constraint write_select_attribute_1(-'QueryId', ?'CompilationInstruction', ?list('SqlToken'), ?'Tail', ?'Output'). 1579:-chr_constraint write_select_attributes(-'QueryId'). 1580:-chr_constraint write_sql(-'QueryId', ?'CompilationInstruction',  +'Disposition', +list('SqlToken'), ?'Tail', ?list('OdbcParameter'), ?list('Output')). 1581:-chr_constraint write_update_attribute(-'QueryId', ?'TableAlias', +'AttributeName', ?'ApplicationValue'). 1582:-chr_constraint write_update_attributes(-'QueryId', ?'TableAlias', ?list('AttributeNameValuePair')). 1583
 1584
 1585:-op(400, xfy, (::)).            % CQL
 1586:-op(900, fy,  exists).          % CQL
 1587:-op(750, yfx, *==).             % CQL
 1588:-op(750, yfx, =*=).             % CQL
 1589:-op(750, yfx, ==*).             % CQL
 1590:-op(740, yfx, on).              % CQL
 1591:-op(700, xfx, =~).              % CQL (LIKE)
 1592:-op(700, xfx, \=~).             % CQL (NOT LIKE)
 1593:-op(200, fy, #).                % CQL (nolock)
 1594:-op(920, fy, ???).              % Debugging
 1595:-op(920, fy, ??).               % Debugging
 1596:-op(920, fy, ?).                % Debugging
 1597
 1598
 1599
 1600%%      cql_set_module_default_schema(+Schema).
 1601%
 1602%       Set the Schema for a module
 1603
 1604:-dynamic
 1605        module_default_schema/2. 1606
 1607cql_set_module_default_schema(Schema) :-                       % +
 1608        prolog_load_context(module, Module),
 1609        set_module_default_schema(Module, Schema).
 1610
 1611
 1612
 1613set_module_default_schema(Module,      % +
 1614                          Schema) :-   % +
 1615        retractall(module_default_schema(Module, _)),
 1616        assert(module_default_schema(Module, Schema)).
 1617
 1618
 1619%%      cql_get_module_default_schema(+Module, ?ModuleDefaultSchema).
 1620
 1621cql_get_module_default_schema(Module,                          % +
 1622                              ModuleDefaultSchema) :-          % ?
 1623
 1624        ( module_default_schema(Module, Schema) ->
 1625            ModuleDefaultSchema = Schema
 1626        ;
 1627            default_schema(ModuleDefaultSchema)
 1628        ).
 1629
 1630% This lets me control the compiletime checks via an environment variable
 1631:-dynamic(do_cql_compiletime_checks/1). 1632do_cql_compiletime_checks:-
 1633        ( do_cql_compiletime_checks(Status)->
 1634            Status == true
 1635        ; getenv('CQL_COMPILETIME_CHECKS', Atom)->
 1636            assert(do_cql_compiletime_checks(Atom)),
 1637            Atom == true
 1638        ; otherwise->
 1639            assert(do_cql_compiletime_checks(false)),
 1640            fail
 1641        ).
 1642
 1643cql_compiletime_checks(Schema, Goals):-
 1644        forall(cql_sql_clause(Goals, SQL, Parameters),
 1645               check_decompilation(Schema, SQL, Parameters)).
 1646
 1647check_decompilation(Schema, HalfCompiledSql, HalfCompiledOdbcParameters):-
 1648        dbms(Schema, DBMS),
 1649        ( fully_compile_sql(HalfCompiledSql, HalfCompiledOdbcParameters, [], Sql, OdbcParameters, _),
 1650          atom_codes(Sql, SqlCodes),
 1651          sql_tokens(Tokens, SqlCodes, []),
 1652          findall(test,
 1653                    ( member(odbc_parameter(_, _, _, _, _, _), OdbcParameters)
 1654                    ; member(odbc_explicit_type_parameter(_, _, _), OdbcParameters)
 1655                    ),
 1656                  Bindings),
 1657          sql_parse(action(Expression, _Types), _, [dbms(DBMS)], Tokens),
 1658          with_output_to(atom(Atom), sql_write(current_output, Expression, [dbms(DBMS), parameter_bindings(Bindings), suppress_collations]))->
 1659            % Make sure that the COLLATEs are all removed
 1660            \+sub_atom(Atom, _, _, _, 'COLLATE')
 1661        ; otherwise->
 1662            prolog_load_context(source, FileName),
 1663            prolog_load_context(term_position, TermPosition),
 1664            stream_position_data(line_count, TermPosition, LineNumber),
 1665            format(user_error, 'Could not decompile generated CQL: ~w~n~q~n', [FileName:LineNumber, HalfCompiledSql])
 1666        ).
 1667
 1668
 1669
 1670
 1671
 1672%%      cql_goal_expansion(?Schema,
 1673%%                         ?Cql,
 1674%%                         ?GoalExpansion).
 1675%
 1676%       Expand at compile time if the first term is a list of unbound input variables
 1677%
 1678%       Expand at runtime if the first term is compile_at_runtime
 1679
 1680cql_goal_expansion(Schema, Cql, GoalExpansion) :-
 1681        % {} is also used by clp(r,q) so make sure the CQL looks like CQL
 1682        nonvar(Cql),
 1683        Cql = (Arg, _),
 1684        ( is_list(Arg)
 1685        ; nonvar(Arg),
 1686          Arg = compile_at_runtime(_)
 1687        ),
 1688
 1689        \+current_prolog_flag(xref, true), % Prevent expansion when used by pldoc to prevent spurious CQL compile errors
 1690        atom(Schema),
 1691        ( cql_goal_expansion_1(Schema, Cql, GoalExpansion_) ->
 1692            GoalExpansion = GoalExpansion_
 1693        ;
 1694            throw(format('Cannot expand CQL: Schema = ~w, Cql=(~w)', [Schema, Cql]))
 1695        ),
 1696        ( do_cql_compiletime_checks ->
 1697            setup_call_cleanup(assert(skip_cql_instantiation_check),
 1698                               cql_compiletime_checks(Schema, GoalExpansion),
 1699                               retract(skip_cql_instantiation_check))
 1700        ; otherwise->
 1701            true
 1702        ).
 1703
 1704
 1705cql_sql_clause(cql_odbc_state_change_statement(_, _, _, _, SQL, Parameters, _), SQL, Parameters).
 1706cql_sql_clause(cql_pre_state_change_select_sql(_, _, _, _, SQL, _, Parameters), SQL, Parameters).
 1707cql_sql_clause(cql_post_state_change_select_sql(_, _, Parameter, SQL), SQL, [odbc_explicit_type_parameter(Parameter, _, where_value)]).
 1708cql_sql_clause(cql_odbc_select_statement(_, SQL, Parameters, _), SQL, Parameters).
 1709cql_sql_clause((A, B), SQL, Parameters):-
 1710        ( cql_sql_clause(A, SQL, Parameters)
 1711        ; cql_sql_clause(B, SQL, Parameters)
 1712        ).
 1713
 1714:-multifile(cql_dependency_hook/2). 1715:-multifile(cql_generated_sql_hook/3). 1716cql_goal_expansion_1(Schema, (CompilationDirective, CqlA), GoalExpansion) :-
 1717        ( prolog_load_context(source, FileName),
 1718          prolog_load_context(term_position, TermPosition),
 1719          stream_position_data(line_count, TermPosition, LineNumber)->
 1720            DynamicallyCreatedCql = boolean(false)
 1721
 1722        ; otherwise ->
 1723            DynamicallyCreatedCql = boolean(true),
 1724            FileName = '<Dynamically created CQL - no source file>',
 1725            LineNumber = 0
 1726        ),
 1727
 1728        ( is_list(CompilationDirective),
 1729          EqualityRestrictionVariables = CompilationDirective,
 1730          forall(member(EqualityRestrictionVariable, EqualityRestrictionVariables), var(EqualityRestrictionVariable)) ->
 1731            CqlB = (store_equality_restriction_variables(EqualityRestrictionVariables),
 1732                    original_cql(CqlA),
 1733                    cql_statement_location(FileName, LineNumber),
 1734                    CqlA),
 1735            translate_to_constraints(Schema, CqlB, InitialConstraints),
 1736            call(InitialConstraints),
 1737            compile_mode(compiletime),
 1738            fully_compile,
 1739            runtime_constraints(cql_execute(cache_odbc_statement)),
 1740            collect_runtime_constraints(GoalExpansion),
 1741            referenced_tables(ReferencedTables),
 1742            ( ReferencedTables \== [],
 1743              DynamicallyCreatedCql == boolean(false) ->
 1744                sort(ReferencedTables, ReferencedTableSet),  % Remove duplicates
 1745                file_base_name(FileName, FileBaseName),
 1746                file_name_extension(Module, _, FileBaseName),
 1747                ignore(cql_dependency_hook(ReferencedTableSet, Module))
 1748
 1749            ; otherwise ->
 1750                true
 1751            ),
 1752            ignore(cql_generated_sql_hook(FileName, LineNumber, GoalExpansion))
 1753        ; nonvar(CompilationDirective),
 1754          CompilationDirective = compile_at_runtime(IgnoreIfNullVariables) ->
 1755            % Should this be a compile warning? runtime-compilation should now be officially deprecated
 1756            ( nonvar(IgnoreIfNullVariables) ->
 1757                variable_map(IgnoreIfNullVariables, CqlA, CqlB, VariableMap)
 1758
 1759            ; otherwise ->
 1760                true
 1761            ),
 1762            GoalExpansion = cql_runtime(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber)
 1763        ; otherwise ->
 1764            throw(error(domain_error(cql_compilation_directive, CompilationDirective), _))
 1765        ).
 1766
 1767
 1768
 1769%%      cql_runtime(+Schema, +IgnoreIfNullVariables, +CqlA, +CqlB, +VariableMap, +FileName, +LineNumber)
 1770
 1771cql_runtime(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber) :-
 1772        catch(cql_runtime_1(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber),
 1773              format(Format, Arguments),  % Want a backtrace for compile errors in compile_at_runtime statements at runtime
 1774              cql_error(cql, Format, Arguments)).
 1775
 1776
 1777cql_runtime_1(Schema, IgnoreIfNullVariables, CqlA, CqlB, VariableMap, FileName, LineNumber) :-
 1778        ( var(VariableMap) ->
 1779            % Handle the case where IgnoreIfNullVariables is dynamically generated
 1780            variable_map(IgnoreIfNullVariables, CqlA, CqlB, VariableMap)
 1781
 1782        ; otherwise ->
 1783            true
 1784        ),
 1785        CqlC = (store_ignore_if_null_variables(VariableMap),
 1786                original_cql(CqlA),
 1787                cql_statement_location(FileName, LineNumber),
 1788                CqlB),
 1789        translate_to_constraints(Schema, CqlC, InitialConstraints),
 1790        call(InitialConstraints),
 1791        fully_compile,
 1792        cql_execute(do_not_cache_odbc_statement),
 1793        referenced_tables(_).    % Clean up
 1794
 1795
 1796variable_map(IgnoreIfNullVariables, CqlA, CqlB, VariableMap) :-
 1797        copy_term(CqlA, CqlB),
 1798        term_variables(CqlA, ExternalVariables),
 1799        term_variables(CqlB, InternalVariables),
 1800        variable_map_1(ExternalVariables, InternalVariables, IgnoreIfNullVariables, VariableMap).
 1801
 1802
 1803
 1804variable_map_1([], [], _, []).
 1805
 1806variable_map_1([A|As], [B|Bs], IgnoreIfNullVariables, [A-B|VariableMap]) :-
 1807        select(I, IgnoreIfNullVariables, Rest),
 1808        I == A, !,
 1809        variable_map_1(As, Bs, Rest, VariableMap).
 1810
 1811variable_map_1([V|As], [V|Bs], IgnoreIfNullVariables, VariableMap) :-
 1812        variable_map_1(As, Bs, IgnoreIfNullVariables, VariableMap).
 1813
 1814
 1815
 1816translate_to_constraints(Schema,                      % +
 1817                         Cql,                         % +
 1818                         InitialConstraints) :-       % ?
 1819        create_variable_dictionary(Cql, [], CqlGround, Dictionary),
 1820        findall(QueryId-Conjunction,
 1821                translate_to_constraints_1(Schema, top_level_query, CqlGround, QueryId, Conjunction),
 1822                Conjunctions),
 1823        store_conjunctions(Conjunctions, Dictionary),
 1824        conjunction_constraints(true),
 1825        get_conjunction_constraints(InitialConstraints).
 1826
 1827
 1828
 1829store_conjunctions([], _).
 1830
 1831store_conjunctions([QueryId-Conjunction|Conjunctions], Dictionary) :-
 1832        store_conjunction(Conjunction, QueryId, Dictionary),
 1833        store_conjunctions(Conjunctions, Dictionary).
 1834
 1835
 1836
 1837store_conjunction([], _, _).
 1838
 1839store_conjunction([Goal|Goals], QueryId, Dictionary) :-
 1840        create_conjunction_variables(Goal, QueryId, Dictionary, ConjunctionGoal),
 1841        conjunction_goal(ConjunctionGoal),
 1842        store_conjunction(Goals, QueryId, Dictionary).
 1843
 1844
 1845collect_conjunction_variables @
 1846        conjunction_constraints(Constraints),
 1847        conjunction_variable(QueryId, ExternalVariable, ConjunctionVariable)
 1848        <=>
 1849        conjunction_constraints((conjunction_variable(QueryId, ExternalVariable, ConjunctionVariable), Constraints)).
 1850
 1851
 1852collect_conjunction_goals @
 1853        conjunction_constraints(Constraints),
 1854        conjunction_goal(ConjunctionGoal)
 1855        <=>
 1856        conjunction_constraints((ConjunctionGoal, Constraints)).
 1857
 1858
 1859get_conjunction_constraints @
 1860        get_conjunction_constraints(Constraints),
 1861        conjunction_constraints(C)
 1862        <=>
 1863        C = Constraints.
 1864
 1865
 1866
 1867create_variable_dictionary(Term,                     % +
 1868                           Dictionary,               % +
 1869                           GroundTerm,               % ?
 1870                           NewDictionary) :-         % ?
 1871        ( ground(Term) ->
 1872            GroundTerm = Term,
 1873            NewDictionary = Dictionary
 1874
 1875        ; var(Term) ->
 1876            ( member(Variable-GroundTerm, Dictionary),
 1877              Variable == Term ->
 1878                NewDictionary = Dictionary
 1879            ; var_property(Term, fresh(false))->
 1880                gensym(cql_stale_var_, UniqueAtom),
 1881                GroundTerm = '$VAR'(UniqueAtom),
 1882                NewDictionary = [Term-GroundTerm|Dictionary]
 1883            ; otherwise->
 1884                gensym(cql_var_, UniqueAtom),
 1885                GroundTerm = '$VAR'(UniqueAtom),
 1886                NewDictionary = [Term-GroundTerm|Dictionary]
 1887            )
 1888
 1889        ; otherwise ->
 1890            functor(Term, Name, Arity),
 1891            functor(GroundTerm, Name, Arity),
 1892            add_args_to_dictionary(Term, Dictionary, 1, Arity, GroundTerm, NewDictionary)
 1893        ).
 1894
 1895
 1896
 1897add_args_to_dictionary(Term, Dictionary, N, Arity, GroundTerm, NewDictionary) :-
 1898        ( N > Arity ->
 1899            NewDictionary = Dictionary
 1900        ;
 1901            arg(N, Term, Arg),
 1902            create_variable_dictionary(Arg, Dictionary, GroundArg, DictionaryA),
 1903            arg(N, GroundTerm, GroundArg),
 1904            NextN is N + 1,
 1905            add_args_to_dictionary(Term, DictionaryA, NextN, Arity, GroundTerm, NewDictionary)
 1906        ).
 1907
 1908
 1909dictionary_lookup @
 1910        % This allows us to define new join points and tables as we compile (eep?)
 1911        dictionary_addendum(QueryId, A, C)
 1912        \
 1913        dictionary_lookup(QueryId, A, B)
 1914        <=>
 1915        B = C.
 1916
 1917failed_to_get_dictionary_addendum @
 1918        dictionary_lookup(_,_,_)
 1919        <=>
 1920        fail.
 1921
 1922cql_stale:attr_unify_hook(_,_).
 1923create_conjunction_variables(Term, QueryId, Dictionary, TermWithVariables) :-
 1924        ( var(Term) ->
 1925            TermWithVariables = Term
 1926        ; memberchk(ExternalVariable-Term, Dictionary) ->
 1927            conjunction_variable(QueryId, ExternalVariable, ConjunctionVariable),
 1928            ( Term = '$VAR'(Key),
 1929              atom_prefix(Key, cql_stale_var_)->
 1930                put_attr(ConjunctionVariable, cql_stale, 1)
 1931            ; otherwise->
 1932                true
 1933            ),
 1934            TermWithVariables = ConjunctionVariable
 1935        ; dictionary_lookup(QueryId, Term, Var)->
 1936            TermWithVariables = Var
 1937        ; atomic(Term) ->
 1938           TermWithVariables = Term
 1939
 1940        ; otherwise ->
 1941           functor(Term, Name, Arity),
 1942           functor(TermWithVariables, Name, Arity),
 1943           add_arg_variables(Term, QueryId, Dictionary, 1, Arity, TermWithVariables)
 1944        ).
 1945
 1946
 1947
 1948add_arg_variables(Term, QueryId, Dictionary, N, Arity, TermWithVariables) :-
 1949        ( N > Arity ->
 1950            true
 1951        ;
 1952            arg(N, Term, Arg),
 1953            create_conjunction_variables(Arg, QueryId, Dictionary, NewArg),
 1954            arg(N, TermWithVariables, NewArg),
 1955            NextN is N + 1,
 1956            add_arg_variables(Term, QueryId, Dictionary, NextN, Arity, TermWithVariables)
 1957        ).
 1958
 1959
 1960
 1961share_conjunction_variable @
 1962        conjunction_variable(QueryId, ExternalVariable, ConjunctionVariableA)
 1963        \
 1964        conjunction_variable(QueryId, ExternalVariable, ConjunctionVariableB)
 1965        <=>
 1966        ConjunctionVariableA = ConjunctionVariableB.
 1967
 1968
 1969bind_conjunction_variable_if_external_variable_gets_bound @
 1970        conjunction_variable(_, ExternalVariable, ConjunctionVariable)
 1971        <=>
 1972        nonvar(ExternalVariable)
 1973        |
 1974        ConjunctionVariable = ExternalVariable.
 1975
 1976
 1977bind_external_variables_once_fully_compiled @
 1978        cql_fully_compiled
 1979        \
 1980        conjunction_variable(_, ExternalVariable, ConjunctionVariable)
 1981        <=>
 1982        ExternalVariable = ConjunctionVariable.
 1983
 1984
 1985
 1986translate_to_constraints_1(Schema, QueryLevel, Cql, QueryId, CqlConstraints) :-
 1987        ( translate_to_constraints_2(Schema, QueryLevel, Cql, QueryId, CqlConstraints, []) *->
 1988
 1989            ( msort(CqlConstraints, SortedCqlConstraints),
 1990              nextto(state_change_query(_, _, _, _), state_change_query(_, _, _, _), SortedCqlConstraints) ->
 1991                throw(format('Cannot mix state change queries in a single CQL statement', []))
 1992            ;
 1993                true
 1994            )
 1995        ;
 1996           throw(format('Cannot translate CQL: ~w~n', [Cql]))
 1997        ).
 1998
 1999
 2000%       translate_to_constraints_2//4
 2001%
 2002%       QueryId is a variable identifying a query (or sub-query).  The
 2003%       top of the JOIN and WHERE trees is the QueryId
 2004
 2005translate_to_constraints_2(Schema, QueryLevel, Cql, QueryId) -->
 2006        translate(Schema, QueryId, QueryId, Cql),
 2007        [query(QueryId, Schema, QueryLevel),
 2008         restriction_tree(QueryId, where, true),
 2009         sql_statement(QueryId, A, A, B, B, C, C, [], [], [])].
 2010
 2011
 2012%       translate//4
 2013
 2014translate(_, _, _, compile_time_goal(Goal)) --> !,
 2015        {(Goal = Module:Goal1 ->
 2016             true
 2017         ; otherwise->
 2018             prolog_load_context(module, Module),
 2019             Goal1 = Goal
 2020         )},
 2021        [Module:Goal1].
 2022
 2023translate(_, _, _, original_cql(Cql)) --> !,
 2024        [original_cql(Cql)].
 2025
 2026translate(_, _, _, cql_statement_location(FileName, LineNumber)) --> !,
 2027        [cql_statement_location(FileName, LineNumber)].
 2028
 2029translate(_, _, _, store_equality_restriction_variables(EqualityRestrictionVariables)) --> !,
 2030        [store_equality_restriction_variables(EqualityRestrictionVariables)].
 2031
 2032translate(_, _, _, store_ignore_if_null_variables(VariableMap)) --> !,
 2033        [store_ignore_if_null_variables(VariableMap)].
 2034
 2035translate(_, _, _, Term) -->
 2036        {functor(Term, \+, Arity),
 2037         Arity \== 1,
 2038        throw(format('Negation (\\+) is arity one ... add some parentheses: ~w', [Term]))}.
 2039
 2040translate(Schema, QueryId, ParentJoin, (Lhs ; Rhs)) --> !,     % BTP (compile time!)
 2041        (translate(Schema, QueryId, ParentJoin, Lhs)
 2042        ;
 2043        translate(Schema, QueryId, ParentJoin, Rhs)).
 2044
 2045translate(Schema, QueryId, ParentJoin, (Lhs, Rhs)) --> !,
 2046        translate(Schema, QueryId, ParentJoin, Lhs),
 2047        translate(Schema, QueryId, ParentJoin, Rhs).
 2048
 2049
 2050% if we update FROM in postgres, we get an automatic join to the table we're updating
 2051% We need to push things from the @ :: [...] into the where clause, and not have them in the join
 2052% in fact, we shouldn't even list the table in the join unless we're doing a self-join
 2053% (or I guess an outer join).
 2054
 2055% I think that a right outer join in an update is the same as in inner join for all intents and purposes
 2056translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2057        {dbms(Schema, 'PostgreSQL')},
 2058        { JoinTerm =.. [JoinOperator, Lhs, on(Rhs, On)],
 2059          (join(JoinOperator, 'INNER JOIN') ; join(JoinOperator, 'RIGHT OUTER JOIN'))
 2060        },
 2061        {Lhs = (@ :: _) ; Rhs = (@ :: _)},
 2062        !,
 2063        translate(Schema, QueryId, ParentJoin, Lhs),
 2064        translate(Schema, QueryId, ParentJoin, Rhs),
 2065        translate(Schema, QueryId, ParentJoin, On),
 2066        [implicit_join(QueryId, @, SubQueryId),
 2067         implicit_join_link(QueryId, SubQueryId),
 2068         on(SubQueryId, _, On)].
 2069
 2070translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2071        {dbms(Schema, 'PostgreSQL')},
 2072        { JoinTerm =.. [JoinOperator, Lhs, Rhs],
 2073          (join(JoinOperator, 'INNER JOIN') ; join(JoinOperator, 'RIGHT OUTER JOIN'))
 2074        },
 2075        {Lhs = (@ :: _) ; Rhs = (@ :: _)},
 2076        !,
 2077        translate(Schema, QueryId, ParentJoin, Lhs),
 2078        translate(Schema, QueryId, ParentJoin, Rhs),
 2079        [implicit_join(QueryId, @, _)].
 2080
 2081
 2082% This gets very very unpleasant. To do a left outer join in the update, we have to first do an inner join the target
 2083% and then outer join from THERE to complete. Postgres does NOT support left outer join in the from clause otherwise.
 2084% note that FROM in an update is not standard SQL anyway.
 2085translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2086        {dbms(Schema, 'PostgreSQL')},
 2087        { JoinTerm =.. [JoinOperator, Lhs, Rhs],
 2088          join(JoinOperator, 'LEFT OUTER JOIN')
 2089        },
 2090        {Lhs = (@ :: Conditions)}, % don't allow for the target to be on the right. That doesn't really make a lot of sense anyway
 2091        !,
 2092        % Effectively we translate
 2093        %    @ :: [a-A, ...] ==* z :: [z-A, ...]
 2094        % into
 2095        %    (@ :: [a-A, ..., pk-Pk] =*= @@ :: [pk-Pk]) ==* z :: [z-A, ...]
 2096        % Where @@ is a symbol to mean 'the same table as the target but a different alias'
 2097        % The first part of this is dropped as an implicit join
 2098
 2099        % First off, we need to save space for the key. If we don't add this cql_var_X to the
 2100        % dictionary somehow, it will be translated as if cql_var_X were a literal for the WHERE clause
 2101         {gensym(cql_var_, KeyInfo),
 2102          dictionary_addendum(QueryId, KeyInfo, Variable),
 2103          append(Conditions, KeyInfo, NewConditions)},
 2104         [update_table_key(QueryId, Schema, Variable)],
 2105         translate(Schema, QueryId, ParentJoin, (@ :: KeyInfo =*= ((@@) :: NewConditions)) *== Rhs).
 2106
 2107
 2108translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2109        { JoinTerm =.. [JoinOperator, Lhs, on(Rhs, On)],
 2110          join(JoinOperator, JoinType)
 2111        }, !,
 2112        translate(Schema, QueryId, LhsJoin, Lhs),
 2113        translate(Schema, QueryId, RhsJoin, Rhs),
 2114
 2115        [on(ParentJoin, _, On),
 2116         join(QueryId, ParentJoin, LhsJoin, JoinType, RhsJoin)].
 2117
 2118translate(Schema, QueryId, ParentJoin, JoinTerm) -->
 2119        { JoinTerm =.. [JoinOperator, Lhs, Rhs],
 2120          join(JoinOperator, JoinType)
 2121        },
 2122        !,
 2123        translate(Schema, QueryId, LhsJoin, Lhs),
 2124        translate(Schema, QueryId, RhsJoin, Rhs),
 2125
 2126        [join(QueryId, ParentJoin, LhsJoin, JoinType, RhsJoin)].
 2127
 2128
 2129
 2130translate(Schema,
 2131          QueryId,
 2132          ParentJoin,
 2133          @ :: AttributeNameValuePairs) --> !,   % '@' means the update table
 2134        [store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs),
 2135         attributes_to_check(QueryId, Schema, @, AttributeNameValuePairs),
 2136         update_table_alias(QueryId, Schema, ParentJoin, TableAlias)].
 2137
 2138translate(Schema,
 2139          QueryId,
 2140          ParentJoin,
 2141          (@@) :: AttributeNameValuePairs) --> !,   % '@@' means a copy of update table
 2142        [store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs),
 2143         attributes_to_check(QueryId, Schema, @, AttributeNameValuePairs),
 2144         query_table_alias(QueryId, Schema, (@@), TableAlias),
 2145         join_leaf(ParentJoin, TableAlias)].
 2146
 2147translate(Schema,
 2148          QueryId,
 2149          ParentJoin,
 2150          #TableName :: AttributeNameValuePairs) --> !,   % '#' means nolock
 2151        translate_select(Schema,
 2152                         QueryId,
 2153                         ParentJoin,
 2154                         TableName,
 2155                         AttributeNameValuePairs, QueryTableAlias),
 2156        [nolock(QueryId, QueryTableAlias)].
 2157
 2158translate(Schema,
 2159          QueryId,
 2160          ParentJoin,
 2161          TableName :: AttributeNameValuePairs) --> !,
 2162        translate_select(Schema,
 2163                         QueryId,
 2164                         ParentJoin,
 2165                         TableName,
 2166                         AttributeNameValuePairs,
 2167                         _).
 2168
 2169translate(Schema, QueryId, _, insert(TableName, AttributeNameValuePairs)) --> !,
 2170        {\+ duplicate_attributes(insert, Schema, TableName, AttributeNameValuePairs)},
 2171        [insert(QueryId, Schema, TableName, AttributeNameValuePairs),
 2172         query_type(QueryId, insert),
 2173         attributes_to_check(QueryId, Schema, TableName, AttributeNameValuePairs),
 2174         state_change_query(QueryId, insert, Schema, TableName)].
 2175
 2176translate(Schema, QueryId, _, update(TableName, UpdateAttributeNameValuePairs)) --> !,
 2177        {\+ duplicate_attributes(update, Schema, TableName, UpdateAttributeNameValuePairs)},
 2178        [update(QueryId, Schema, TableName, _, UpdateAttributeNameValuePairs),
 2179         query_type(QueryId, update),
 2180         attributes_to_check(QueryId, Schema, TableName, UpdateAttributeNameValuePairs),
 2181         state_change_query(QueryId, update, Schema, TableName)].
 2182
 2183translate(Schema, QueryId, ParentJoin, delete(TableName, AttributeNameValuePairs)) --> !,
 2184        {\+ duplicate_attributes(delete, Schema, TableName, AttributeNameValuePairs)},
 2185        [delete_row(QueryId, TableName, TableAlias),
 2186         query_type(QueryId, delete),
 2187         attributes_to_check(QueryId, Schema, TableName, AttributeNameValuePairs),
 2188         store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs),
 2189         join_leaf(ParentJoin, TableAlias),
 2190         query_table_alias(QueryId, Schema, TableName, TableAlias),
 2191         state_change_query(QueryId, delete, Schema, TableName)].
 2192
 2193translate(Schema, QueryId, ParentJoin, \+((Lhs, Rhs))) --> !,     % De Morgan
 2194        translate(Schema, QueryId, ParentJoin, (\+Lhs ; \+Rhs)).
 2195
 2196translate(Schema, QueryId, ParentJoin, \+((Lhs ; Rhs))) --> !,    % De Morgan
 2197        translate(Schema, QueryId, ParentJoin, (\+Lhs, \+Rhs)).
 2198
 2199translate(Schema, QueryId, _, \+Comparison) -->
 2200        {simple_comparison(Schema, Comparison, _, InverseOperator, Lhs, Rhs)}, !,
 2201        translate_comparison(QueryId, Schema, Lhs, InverseOperator, Rhs).
 2202
 2203translate(Schema, QueryId, _, Comparison) -->
 2204        {simple_comparison(Schema, Comparison, Operator, _, Lhs, Rhs)}, !,
 2205        translate_comparison(QueryId, Schema, Lhs, Operator, Rhs).
 2206
 2207translate(Schema, QueryId, _, \+ exists(Goals)) --> !,
 2208        translate_sub_query(Schema, QueryId, \+ exists, Goals).
 2209
 2210translate(Schema, QueryId, _, exists(Goals)) --> !,
 2211        translate_sub_query(Schema, QueryId, exists, Goals).
 2212
 2213translate(_, QueryId, _, group_by(GroupBys)) --> !,
 2214        [group_bys(QueryId, GroupBys)].
 2215
 2216translate(_, QueryId, _, order_by(OrderBys)) --> !,
 2217        [order_bys(QueryId, OrderBys)].
 2218
 2219translate(Schema, QueryId, _, having(Having)) --> !,
 2220        {prolog_term_to_restriction_tree(Schema, Having, RestrictionTree)},
 2221        [restriction_tree(QueryId, having, RestrictionTree)].
 2222
 2223translate(_, QueryId, _, distinct) --> !,
 2224        [select_distinction(QueryId, distinct_on_all_select_attributes)].
 2225
 2226translate(_, QueryId, _, distinct(Distincts)) --> !,
 2227        [distincts(QueryId, Distincts)].
 2228
 2229translate(Schema, QueryId, _, top(N)) --> !,
 2230        [top(QueryId, Schema, N)].
 2231
 2232translate(Schema, QueryId, _, identity(I)) --> !,
 2233        [cql_identity(QueryId, Schema, I)].
 2234
 2235translate(_, QueryId, _, row_count(N)) --> !,
 2236        [row_count(QueryId, N)].
 2237
 2238translate(_, _, _, absence_of_where_restriction_is_deliberate) --> !,
 2239        [absence_of_where_restriction_is_deliberate].
 2240
 2241translate(_, _, _, A=B) --> !,
 2242        {(prolog_load_context(source, FileName),
 2243          prolog_load_context(term_position, TermPosition),
 2244          stream_position_data(line_count, TermPosition, LineNumber)->
 2245            true
 2246         ; otherwise->
 2247            FileName = '<Dynamically created CQL - no source file>',
 2248            LineNumber = 0
 2249         ),
 2250        print_message(warning, format('Unification in CQL is DEPRECATED (~w:~w)~n', [FileName, LineNumber]))},
 2251        [unify(A, B)].
 2252
 2253translate(_, _, _, true) --> !,
 2254        [].
 2255
 2256translate(_, QueryId, _, no_state_change_actions) --> !,
 2257        [no_state_change_actions(QueryId)].
 2258
 2259translate(_, _, _, Term) -->
 2260        {throw(format('Cannot translate CQL term: ~w~n', [Term]))}.
 2261
 2262
 2263join(*==, 'LEFT OUTER JOIN').
 2264join(=*=, 'INNER JOIN').
 2265join(==*, 'RIGHT OUTER JOIN').
 2266
 2267
 2268translate_sub_query(Schema, QueryId, SubQueryType, Goals) -->
 2269        translate_to_constraints_2(Schema, sub_query, Goals, SubQueryId), !,
 2270        [sub_query_select(SubQueryId),
 2271         sub_query_restriction(QueryId, SubQueryType, SubQuerySqlTokens, SubQueryTail, SubQueryInputs),
 2272         sub_query(SubQueryId, SubQuerySqlTokens, SubQueryTail, SubQueryInputs)].
 2273
 2274
 2275translate_select(Schema,                     % +
 2276                 QueryId,                    % +
 2277                 ParentJoin,                 % +
 2278                 TableName,                  % +
 2279                 AttributeNameValuePairs,    % +
 2280                 QueryTableAlias) -->        % ?
 2281        [store_attribute_bindings(Schema, QueryId, QueryTableAlias, AttributeNameValuePairs),
 2282         query_type(QueryId, select),
 2283         attributes_to_check(QueryId, Schema, TableName, AttributeNameValuePairs),
 2284         join_leaf(ParentJoin, QueryTableAlias),
 2285         query_table_alias(QueryId, Schema, TableName, QueryTableAlias)].
 2286
 2287
 2288simple_comparison(Schema,                    % +
 2289                  Comparison,                % +
 2290                  Operator,                  % ?
 2291                  InverseOperator,           % ?
 2292                  Lhs,                       % ?
 2293                  Rhs) :-                    % ?
 2294        functor(Comparison, Operator, 2),
 2295        prolog_to_sql_comparison_operator(Schema, Operator, _, InverseOperator),
 2296        arg(1, Comparison, Lhs),
 2297        arg(2, Comparison, Rhs).
 2298
 2299
 2300translate_comparison(QueryId, Schema, Lhs, Operator, Rhs) -->
 2301        translate_expression(Schema, Lhs, LhsResult),
 2302        translate_expression(Schema, Rhs, RhsResult),
 2303
 2304        [comparison(QueryId, LhsResult, Operator, RhsResult)].
 2305
 2306
 2307translate_expression(Schema,
 2308                     Goal,
 2309                     aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, SubQuerySqlTokens, Tail, SubQueryInputs)) -->
 2310        {functor(Goal, AggregationOperator, 2),
 2311         aggregation_operator(AggregationOperator), !,
 2312         arg(1, Goal, AggregationVariable),
 2313         arg(2, Goal, Goals)},
 2314
 2315        translate_to_constraints_2(Schema, sub_query, Goals, SubQueryId),
 2316
 2317        [aggregation_variable(SubQueryId,
 2318                              AggregationOperator,
 2319                              AggregationVariable),
 2320         aggregation_sub_query(SubQueryId, AggregationTableName, AggregationAttributeName, SubQuerySqlTokens, Tail, SubQueryInputs)].
 2321
 2322
 2323translate_expression(_, Variable, Variable) -->
 2324        [true].
 2325
 2326
 2327aggregation_operator(count).
 2328aggregation_operator(max).
 2329aggregation_operator(min).
 2330aggregation_operator(avg).
 2331aggregation_operator(sum).
 2332
 2333
 2334prolog_term_to_restriction_tree(Schema, \+(Lhs, Rhs), RestrictionTree) :- !,
 2335        prolog_term_to_restriction_tree(Schema, (\+Lhs ; \+Rhs), RestrictionTree).
 2336
 2337prolog_term_to_restriction_tree(Schema, \+(Lhs ; Rhs), RestrictionTree) :- !,
 2338        prolog_term_to_restriction_tree(Schema, (\+Lhs, \+Rhs), RestrictionTree).
 2339
 2340prolog_term_to_restriction_tree(Schema, (Lhs, Rhs), and(RestrictionLhs, RestrictionRhs)) :- !,
 2341        prolog_term_to_restriction_tree(Schema, Lhs, RestrictionLhs),
 2342        prolog_term_to_restriction_tree(Schema, Rhs, RestrictionRhs).
 2343
 2344prolog_term_to_restriction_tree(Schema, (Lhs ; Rhs), or(RestrictionLhs, RestrictionRhs)) :- !,
 2345        prolog_term_to_restriction_tree(Schema, Lhs, RestrictionLhs),
 2346        prolog_term_to_restriction_tree(Schema, Rhs, RestrictionRhs).
 2347
 2348prolog_term_to_restriction_tree(Schema, \+Comparison, comparison(RestrictionLhs, InverseOperator, RestrictionRhs)) :- !,
 2349        simple_comparison(Schema, Comparison, _, InverseOperator, RestrictionLhs, RestrictionRhs).
 2350
 2351prolog_term_to_restriction_tree(Schema, Comparison, comparison(Lhs, Operator, Rhs)) :-
 2352        ( simple_comparison(Schema, Comparison, Operator, _, Lhs, Rhs) ->
 2353            true
 2354        ;
 2355            throw(format('Cannot translate restriction term: ~w', [Comparison]))
 2356        ).
 2357
 2358
 2359equality_restriction_variables_are_unique @
 2360        equality_restriction_variable(Variable, _)
 2361        \
 2362        equality_restriction_variable(Variable, _)
 2363        <=>
 2364        true.
 2365
 2366
 2367store_equality_restriction_variables @
 2368        store_equality_restriction_variables([InputVariable|InputVariables])
 2369        <=>
 2370        equality_restriction_variable(InputVariable, _),
 2371        store_equality_restriction_variables(InputVariables).
 2372
 2373
 2374cleanup_store_equality_restriction_variables @
 2375        store_equality_restriction_variables([])
 2376        <=>
 2377        true.
 2378
 2379
 2380store_ignore_if_null_variables @
 2381        store_ignore_if_null_variables([ExternalVariable-InternalVariable|VariableMap])
 2382        <=>
 2383        ignore_if_null(ExternalVariable, InternalVariable),
 2384        store_ignore_if_null_variables(VariableMap).
 2385
 2386
 2387cleanup_store_ignore_if_null_variables @
 2388        store_ignore_if_null_variables([])
 2389        <=>
 2390        true.
 2391
 2392
 2393store_attribute_bindings(Schema, QueryId, TableAlias, AttributeNameValuePairs) :-
 2394        ( store_attribute_bindings_1(Schema, QueryId, TableAlias, AttributeNameValuePairs) ->
 2395            true
 2396        ;
 2397            throw(format('Bad attribute bindings: ~w', [AttributeNameValuePairs]))
 2398        ).
 2399
 2400
 2401store_attribute_bindings_1(_, _, _, []).
 2402
 2403store_attribute_bindings_1(Schema, QueryId, TableAlias, [AttributeNameValuePair|AttributeNameValuePairs]) :-
 2404        % For INSERT from SELECTs
 2405        ( AttributeNameValuePair = as(AttributeName)-ApplicationValue ->
 2406            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), selection_constant(ApplicationValue))
 2407
 2408        % Normal Name-Value specification
 2409        ; AttributeNameValuePair = AttributeName-ApplicationValue,
 2410          atomic_application_value(ApplicationValue) ->
 2411            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), ApplicationValue)
 2412
 2413        % ignore_if_null
 2414        ; AttributeNameValuePair = (AttributeName-ignore_if_null(ApplicationValue)),
 2415          var(ApplicationValue)->
 2416            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), AttributeValue),
 2417            comparison(QueryId, AttributeValue, ==, ignore_if_null(ApplicationValue))
 2418
 2419        % runtime list
 2420        ; AttributeNameValuePair = (AttributeName-list(ApplicationValue))->
 2421            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), AttributeValue),
 2422            comparison(QueryId, AttributeValue, ==, list(ApplicationValue))
 2423
 2424        % Compile-time attribute value
 2425        ; AttributeNameValuePair = (AttributeName-CompileTimeGoal),
 2426          callable(CompileTimeGoal),
 2427          functor(CompileTimeGoal, PredicateName, ArityMinusOne),
 2428          Arity is ArityMinusOne + 1,
 2429          current_predicate(user:PredicateName/Arity),
 2430          user:call(CompileTimeGoal, ApplicationValue) ->
 2431            attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), ApplicationValue)
 2432        ),
 2433        store_attribute_bindings_1(Schema, QueryId, TableAlias, AttributeNameValuePairs).
 2434
 2435
 2436atomic_application_value(ApplicationValue) :-  % +
 2437        ( var(ApplicationValue)
 2438        ; atom(ApplicationValue)
 2439        ; integer(ApplicationValue)
 2440        ; rational(ApplicationValue)
 2441	; timestamp(ApplicationValue)
 2442        ; cql_atomic_value_check_hook(ApplicationValue)
 2443        ; is_list(ApplicationValue)
 2444        ; ApplicationValue == {null}
 2445        ; ApplicationValue == {timestamp}
 2446        ; ApplicationValue == {user_id}
 2447        ; ApplicationValue == {transaction_id}
 2448        ),
 2449        !.
 2450
 2451timestamp(TS) :-
 2452	compound(TS),
 2453	functor(TS, timestamp, 7).
 2454
 2455
 2456ensure_binding_is_on_the_external_variable_so_that_ignore_if_null_works_properly_1 @
 2457        ignore_if_null(ExternalVariable, InternalVariable)
 2458        \
 2459        unify(InternalVariable, X)
 2460        <=>
 2461        ExternalVariable = X.
 2462
 2463
 2464ensure_binding_is_on_the_external_variable_so_that_ignore_if_null_works_properly_2 @
 2465        ignore_if_null(ExternalVariable, InternalVariable)
 2466        \
 2467        unify(X, InternalVariable)
 2468        <=>
 2469        ExternalVariable = X.
 2470
 2471
 2472make_unify_unify @
 2473        unify(X, Y)
 2474        <=>
 2475        X = Y.
 2476
 2477
 2478remove_comparison_involving_ignored_variable @
 2479        ignore_if_null(ExternalVariable, InternalVariable)
 2480        \
 2481        comparison(_, Lhs, _, Rhs)
 2482        <=>
 2483        ( ExternalVariable == {null},
 2484          InternalVariable == Lhs
 2485
 2486        ; ExternalVariable == {null},
 2487          InternalVariable == Rhs
 2488
 2489        ; InternalVariable == Lhs,
 2490          Rhs == {null}
 2491
 2492        ; InternalVariable == Rhs,
 2493          Lhs == {null}
 2494        )
 2495        |
 2496        true.
 2497
 2498
 2499fully_compile @
 2500        fully_compile
 2501        <=>
 2502        no_sql_statement_generated,
 2503        unify_ignore_if_null_variables,
 2504        create_in_line_joins,
 2505        create_join_points,
 2506        resolve_join_points,
 2507        determine_select_distinctions,
 2508        create_select_bindings,
 2509        determine_selection_type,
 2510        create_restrictions,
 2511        generate_sub_query_sql,
 2512        simplify,
 2513        ( debugging(cql(compile)) ->
 2514            with_output_to(codes(Codes), chr_show_store(cql)),
 2515            debug(cql(compile), '==========~n~s^^^^^^^^^^~n~n', [Codes])
 2516        ;
 2517            true
 2518        ),
 2519        check_for_top_without_order_by,
 2520        write_query_sql,
 2521        instantiate_table_aliases,
 2522        write_limit,
 2523        check_for_orphan_select_attributes_in_aggregations,
 2524        check_query,
 2525        check_for_unjoined_tables,
 2526        check_for_orphan_group_bys,
 2527        check_for_orphan_order_bys,
 2528        check_for_orphan_distincts,
 2529        check_for_orphan_select_variables_in_updates,
 2530        prepare_odbc_statements,
 2531        cleanup_compile,
 2532        cql_fully_compiled.
 2533
 2534
 2535
 2536unify_ignore_if_null_variables @
 2537        unify_ignore_if_null_variables
 2538        \
 2539        ignore_if_null(ExternalVariable, InternalVariable)
 2540        <=>
 2541        nonvar(ExternalVariable),
 2542        ExternalVariable \== {null}
 2543        |
 2544        InternalVariable = ExternalVariable.
 2545
 2546
 2547cleanup_unify_ignore_if_null_variables @
 2548        unify_ignore_if_null_variables
 2549        <=>
 2550        true.
 2551
 2552
 2553update_atat_table_name @
 2554        update(QueryId, Schema, TableName, _, _)
 2555        \
 2556        query_table_alias(QueryId, Schema, (@@), Alias)
 2557        <=>
 2558        query_table_alias(QueryId, Schema, TableName, Alias).
 2559
 2560
 2561resolve_update_table_alias @
 2562        create_join_points,
 2563        update(QueryId, Schema, TableName, UpdateTableAlias, _),
 2564        update_table_alias(QueryId, _, ParentJoin, TableAlias)
 2565        ==>
 2566        join_leaf(ParentJoin, TableAlias),
 2567        query_table_alias(QueryId, Schema, TableName, TableAlias),
 2568        UpdateTableAlias = TableAlias.
 2569
 2570
 2571where_restriction_variable_not_allowed_to_be_an_outer_join_point @
 2572        outer_side_join(Join),
 2573        join_leaf(Join, TableAlias),
 2574        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 2575        join_variable(JoinVariable),
 2576        where_restriction_variable(JoinVariable)
 2577        <=>
 2578        throw(format('A variable used in a WHERE RESTRICTION must not also be a SHARED VARIABLE defining an OUTER JOIN point', [])).
 2579
 2580
 2581deprecated_group_by @
 2582        attribute_binding(_, attribute(_, _, group_by(_)), _)
 2583        <=>
 2584        throw(format('OBSOLETE group_by format.  Use separate group_by([V1, V2, ...])', [])).
 2585
 2586
 2587bad_group_by_specification @
 2588        group_bys(_, GroupBys)
 2589        <=>
 2590        ( \+ is_list(GroupBys)
 2591        ; is_list(GroupBys),
 2592          member(GroupBy, GroupBys),
 2593          nonvar(GroupBy)
 2594        )
 2595        |
 2596        throw(format('GROUP BY must specify a LIST of variables', [group_by(GroupBys)])).
 2597
 2598
 2599individual_group_by @
 2600        group_bys(QueryId, [GroupBy|GroupBys])
 2601        <=>
 2602        group_by(QueryId, GroupBy),
 2603        group_bys(QueryId, GroupBys).
 2604
 2605
 2606no_more_group_bys @
 2607        group_bys(_, [])
 2608        <=>
 2609        true.
 2610
 2611
 2612copy_attribute_for_group_by @
 2613        attribute_binding(QueryId, attribute(_, TableAlias, AttributeName), Variable)
 2614        ==>
 2615        attribute_for_group_by(QueryId, TableAlias, AttributeName, Variable).
 2616
 2617
 2618ambiguous_group_by_attribute @
 2619        group_by(QueryId, GroupBy),
 2620        attribute_for_group_by(QueryId, TableAliasA, AttributeNameA, GroupBy),
 2621        attribute_for_group_by(QueryId, TableAliasB, AttributeNameB, GroupBy),
 2622        query_table_alias(_, _, TableNameA, TableAliasA),
 2623        query_table_alias(_, _, TableNameB, TableAliasB)
 2624        <=>
 2625        throw(format('GROUP BY variable is AMBIGUOUS.  It identifies both ~w.~w AND ~w.~w.  Use == to specify the join point?',
 2626                     [TableNameA, AttributeNameA, TableNameB, AttributeNameB])).
 2627
 2628
 2629bad_distinct_specification @
 2630        distincts(_, Distincts)
 2631        <=>
 2632        ( \+ is_list(Distincts)
 2633        ; is_list(Distincts),
 2634          member(Distinct, Distincts),
 2635          nonvar(Distinct)
 2636        )
 2637        |
 2638        throw(format('DISTINCT must specify a LIST of variables', [distinct(Distincts)])).
 2639
 2640
 2641individual_distinct @
 2642        distincts(QueryId, [Distinct|Distincts])
 2643        <=>
 2644        distinct(QueryId, Distinct),
 2645        distincts(QueryId, Distincts).
 2646
 2647
 2648no_more_distincts @
 2649        distincts(QueryId, [])
 2650        <=>
 2651        select_distinction(QueryId, distinct_on_specified_attributes).
 2652
 2653
 2654determine_select_distinctions @
 2655        query(QueryId, _, _),
 2656        determine_select_distinctions
 2657        ==>
 2658        determine_select_distinction(QueryId).
 2659
 2660
 2661select_distinction_exists @
 2662        select_distinction(QueryId, _)
 2663        \
 2664        determine_select_distinction(QueryId)
 2665        <=>
 2666        true.
 2667
 2668
 2669no_select_distinction @
 2670        determine_select_distinction(QueryId)
 2671        <=>
 2672        select_distinction(QueryId, no_distinction).
 2673
 2674
 2675select_binding @
 2676        create_select_bindings,
 2677        attribute_binding(QueryId, attribute(Schema, TableAlias, AttributeName), Variable)
 2678        ==>
 2679        selection_variable(Variable)
 2680        |
 2681        Attribute = attribute(Schema, TableAlias, AttributeName),
 2682        select_binding(QueryId, plain, Attribute, Variable).
 2683
 2684cleanup_create_select_bindings @
 2685        create_select_bindings
 2686        <=>
 2687        true.
 2688
 2689
 2690selection_variable(V) :-
 2691        var(V).
 2692selection_variable(V) :-
 2693        \+ ground(V),
 2694        cql_atomic_value_check_hook(V).
 2695selection_variable(selection_constant(_)).
 2696
 2697
 2698select_binding_aggregation @
 2699        select_binding(QueryId, plain, attribute(Schema, TableAlias, AggregationTerm), Variable)
 2700        <=>
 2701        AggregationTerm =.. [AggregationOperator, AttributeName],
 2702        aggregation_operator(AggregationOperator)
 2703        |
 2704        select_binding(QueryId, aggregation(AggregationOperator), attribute(Schema, TableAlias, AttributeName), Variable).
 2705
 2706
 2707sub_select_binding_aggregation @
 2708        aggregation_variable(_, AggregationOperator, AggregationVariable),
 2709        select_binding(QueryId, plain, Attribute, Variable)
 2710        <=>
 2711        AggregationVariable == Variable
 2712        |
 2713        select_binding(QueryId, aggregation(AggregationOperator), Attribute, Variable).
 2714
 2715
 2716instantiate_table_aliases @
 2717        instantiate_table_aliases,
 2718        query_table_alias(_, _, TableName, TableAlias)
 2719        ==>
 2720        var(TableAlias)
 2721        |
 2722        table_alias_crunch(TableName, Crunched),
 2723        atom_concat(Crunched, '_', Stem),
 2724        gensym(Stem, Symbol),
 2725        map_database_atom(Symbol, TableAlias).
 2726
 2727
 2728table_alias_crunch(TableName, Crunched):-
 2729        atom_codes(TableName, Codes),
 2730        extract_abbreviation(AbbreviationCodes, [95|Codes], []),
 2731        atom_codes(Crunched, AbbreviationCodes).
 2732
 2733extract_abbreviation([])-->
 2734        [].
 2735
 2736extract_abbreviation([Code|Codes])-->
 2737        "_",
 2738        !,
 2739        [Code],
 2740        extract_abbreviation(Codes).
 2741
 2742extract_abbreviation(Codes)-->
 2743        [_],
 2744        extract_abbreviation(Codes).
 2745
 2746cleanup_instantiate_table_aliases @
 2747        instantiate_table_aliases
 2748        <=>
 2749        true.
 2750
 2751
 2752check_aggregation_attribute @
 2753        select_binding(QueryId, aggregation(_), attribute(Schema, TableAlias, AttributeName), _),
 2754        query_table_alias(QueryId, _, TableName, TableAlias)
 2755        <=>
 2756        \+ cql_data_type(Schema, TableName, AttributeName, _, _, _, _, _, _, _)
 2757        |
 2758        throw(format('Unknown SELECT attribute in CQL: ~w', [Schema:TableName:AttributeName])).
 2759
 2760
 2761aggregation_selection @
 2762        select_binding(QueryId, aggregation(_), _, _),
 2763        determine_selection_type
 2764        ==>
 2765        selection_type(QueryId, aggregation_selection).
 2766
 2767
 2768non_aggregation_selection @
 2769        select_binding(QueryId, SelectBindingType, _, _),
 2770        determine_selection_type
 2771        ==>
 2772        SelectBindingType \= aggregation(_)
 2773        |
 2774        selection_type(QueryId, non_aggregation_selection).
 2775
 2776
 2777cleanup_determine_selection_type @
 2778        determine_selection_type
 2779        <=>
 2780        true.
 2781
 2782
 2783priority @
 2784        selection_type(QueryId, aggregation_selection)
 2785        \
 2786        selection_type(QueryId, non_aggregation_selection)
 2787        <=>
 2788        true.
 2789
 2790
 2791uniqueness @
 2792        selection_type(QueryId, SelectionType)
 2793        \
 2794        selection_type(QueryId, SelectionType)
 2795        <=>
 2796        true.
 2797
 2798get_data_size(Schema, TableName, AttributeName, Size):-
 2799        cql_data_type(Schema, TableName, AttributeName, _, CharacterMaximumLength, _, _, _, _, _),
 2800        ( CharacterMaximumLength == max ->
 2801            % 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.
 2802            % For all other DBMS it doesnt matter
 2803            Size = 65535
 2804        ; integer(CharacterMaximumLength) ->
 2805            Size = CharacterMaximumLength
 2806        ; otherwise->
 2807            Size = 0
 2808        ).
 2809
 2810aggregation_selection @
 2811        selection_type(QueryId, aggregation_selection),
 2812        query_table_alias(QueryId, _, TableName, TableAlias)
 2813        \
 2814        select_binding(QueryId, SelectBindingType, attribute(Schema, TableAlias, AttributeName), Variable)
 2815        <=>
 2816        SelectBindingType = aggregation(_)
 2817        |
 2818        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, Variable).
 2819
 2820
 2821aggregation_group_by_selection @
 2822        selection_type(QueryId, aggregation_selection),
 2823        query_table_alias(QueryId, _, TableName, TableAlias),
 2824        group_by(QueryId, GroupBy)
 2825        \
 2826        select_binding(QueryId, SelectBindingType, attribute(Schema, TableAlias, AttributeName), GroupBy)
 2827        <=>
 2828        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, GroupBy).
 2829
 2830
 2831ignore_aggregation_select_binding @
 2832        selection_type(QueryId, aggregation_selection), where_restriction_variable(Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2833        selection_type(QueryId, aggregation_selection), join_variable(Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2834        selection_type(QueryId, aggregation_selection), sub_query_join_variable(Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2835        selection_type(QueryId, aggregation_selection), ignore_if_null(_, Variable) \ select_binding(QueryId, _, _, Variable) <=> true.
 2836
 2837
 2838aggregation_select_binding_error @
 2839        check_for_orphan_select_attributes_in_aggregations,
 2840        selection_type(QueryId, aggregation_selection)
 2841        \
 2842        select_binding(QueryId, _, Attribute, _)
 2843        <=>
 2844        throw(format('Aggregation refers to an attribute which is not aggregated, not grouped by, not a restriction and not a join point: ~w', [Attribute])).
 2845
 2846
 2847non_aggregation_select_binding @
 2848        selection_type(QueryId, non_aggregation_selection),
 2849        query_table_alias(QueryId, _, TableName, TableAlias)
 2850        \
 2851        select_binding(QueryId, SelectBindingType, attribute(Schema, TableAlias, AttributeName), Variable)
 2852        <=>
 2853        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, Variable).
 2854
 2855
 2856keep_if_distinct_on_specified_attributes @
 2857        select_distinction(QueryId, distinct_on_specified_attributes),
 2858        select_attribute(QueryId, _, Keep, _, Variable)
 2859        \
 2860        distinct(QueryId, Variable)
 2861        <=>
 2862        Keep = 1.
 2863
 2864
 2865keep_if_distinct_on_all_attributes_or_if_there_is_no_distinction @
 2866        select_distinction(QueryId, Distinction),
 2867        select_attribute(QueryId, _, Keep, _, _)
 2868        ==>
 2869        ( Distinction == distinct_on_all_select_attributes
 2870        ; Distinction == no_distinction
 2871        )
 2872        |
 2873        Keep = 1.
 2874
 2875
 2876copy_select_attribute_for_disjunction_comparison @
 2877        select_attribute(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName), _, _, _)
 2878        ==>
 2879        select_attribute_for_disjunction_comparison(QueryId, select_attribute(SelectBindingType, Schema, TableName, TableAlias, AttributeName)).
 2880
 2881
 2882bad_order_by_specification @
 2883        order_bys(_, OrderBys)
 2884        <=>
 2885        ( \+ is_list(OrderBys)
 2886        ; is_list(OrderBys),
 2887          member(OrderBy, OrderBys),
 2888          nonvar(OrderBy),
 2889          OrderBy \= +(_),
 2890          OrderBy \= -(_)
 2891        )
 2892        |
 2893        throw(format('ORDER BY must specify a LIST of +/1 and -/1 terms but found ~w', [order_by(OrderBys)])).
 2894
 2895
 2896copy_attribute_for_order_by @
 2897        attribute_binding(QueryId, attribute(_, TableAlias, AttributeName), Variable)
 2898        ==>
 2899        attribute_for_order_by(QueryId, TableAlias, AttributeName, Variable).
 2900
 2901
 2902ambiguous_order_by_attribute @
 2903        write_order_by(QueryId, OrderBy)
 2904        \
 2905        attribute_for_order_by(QueryId, TableAliasA, AttributeNameA, Variable),
 2906        attribute_for_order_by(QueryId, TableAliasB, AttributeNameB, Variable),
 2907        query_table_alias(_, _, TableNameA, TableAliasA),
 2908        query_table_alias(_, _, TableNameB, TableAliasB)
 2909        <=>
 2910        ( OrderBy == +Variable
 2911        ; OrderBy == -Variable
 2912        )
 2913        |
 2914        throw(format('ORDER BY variable is AMBIGUOUS.  It identifies both ~w.~w AND ~w.~w.  Use == to specify the join point?',
 2915                     [TableNameA, AttributeNameA, TableNameB, AttributeNameB])).
 2916
 2917select_attributes_1 @
 2918        selection_type(QueryId, _)
 2919        ==>
 2920        select_attributes_for_disjunction_comparison(QueryId, []).
 2921
 2922
 2923select_attributes_2 @
 2924        select_attribute_for_disjunction_comparison(QueryId, SelectAttribute),
 2925        select_attributes_for_disjunction_comparison(QueryId, SelectAttributes)
 2926        <=>
 2927        merge_set([SelectAttribute], SelectAttributes, SortedSelectAttributes),
 2928        select_attributes_for_disjunction_comparison(QueryId, SortedSelectAttributes).
 2929
 2930
 2931check_for_top_without_order_by @
 2932        top(QueryId, _, _), order_bys(QueryId, _) \ check_for_top_without_order_by <=> true.
 2933        top(_, _, _), check_for_top_without_order_by, original_cql(Cql) <=> throw(format('top without order_by in CQL: ~w', [Cql])).
 2934        check_for_top_without_order_by <=> true.
 2935
 2936
 2937join_tree_nodes @
 2938        simplify,
 2939        query(Join, _, top_level_query)       % Only bother doing join_tree for top_level_queries
 2940        % Solitary join leaf has the QueryId as its parent i.e. Join == QueryId
 2941        ==>
 2942        join_pointer(Join, Join),
 2943        join_tree_nodes(Join, []).
 2944
 2945
 2946identify_join_type_1 @
 2947        join(_, _, _, 'LEFT OUTER JOIN', RhsJoin)
 2948        ==>
 2949        outer_side_join(RhsJoin).
 2950
 2951
 2952identify_join_type_2 @
 2953        join(_, _, LhsJoin, 'RIGHT OUTER JOIN', _)
 2954        ==>
 2955        outer_side_join(LhsJoin).
 2956
 2957
 2958every_join_below_an_outer_side_join_is_an_outer_side_join @
 2959        outer_side_join(ParentJoin),
 2960        join(_, ParentJoin, LhsJoin, _, RhsJoin)
 2961        ==>
 2962        outer_side_join(LhsJoin),
 2963        outer_side_join(RhsJoin).
 2964
 2965
 2966walk_join_tree_branch @
 2967        join(QueryId, Join, LhsJoin, JoinType, RhsJoin)
 2968        \
 2969        join_pointer(QueryId, Join)
 2970        <=>
 2971        join_tree_node(QueryId, Join, branch(JoinType)),
 2972        join_pointer(QueryId, LhsJoin),
 2973        join_pointer(QueryId, RhsJoin).
 2974
 2975
 2976walk_join_tree_on_clause @
 2977        join_tree_node(QueryId, Join, branch(_)),
 2978        on(Join, _, On)
 2979        ==>
 2980        join_tree_node(QueryId, Join, On).
 2981
 2982
 2983cleanup_join_tree_node_branch @
 2984        join_tree_node(_, _, branch(_))
 2985        <=>
 2986        true.
 2987
 2988
 2989walk_join_tree_leaf @
 2990        join_leaf(Join, TableAlias),
 2991        query_table_alias(QueryId, _, _, TableAlias)
 2992        \
 2993        join_pointer(QueryId, Join)
 2994        <=>
 2995        join_tree_node(QueryId, Join, table_alias(TableAlias)).
 2996
 2997
 2998accumulate_join_tree_nodes @
 2999        join_tree_nodes(QueryId, JoinTreeNodes),
 3000        join_tree_node(QueryId, _, JoinTreeNode)
 3001        <=>
 3002        join_tree_nodes(QueryId, [JoinTreeNode|JoinTreeNodes]).
 3003
 3004
 3005amalgamate_restrictions_if_join_tree_is_the_same @
 3006        select_attributes_for_disjunction_comparison(QueryIdA, SelectAttributesA),
 3007        join_tree_nodes(QueryIdA, JoinTreeNodesA)
 3008        \
 3009        select_attributes_for_disjunction_comparison(QueryIdB, SelectAttributesB),
 3010        join_tree_nodes(QueryIdB, JoinTreeNodesB),
 3011        restriction_tree(QueryIdA, RestrictionType, RestrictionTreeA),
 3012        restriction_tree(QueryIdB, RestrictionType, RestrictionTreeB)
 3013        <=>
 3014        unifiable(SelectAttributesA, SelectAttributesB, _),
 3015        unifiable(JoinTreeNodesA, JoinTreeNodesB, Unifiers)
 3016        |
 3017        restriction_tree(QueryIdA, RestrictionType, or(RestrictionTreeA, RestrictionTreeB)),
 3018        remove_query(QueryIdB, QueryIdA),
 3019        unify_table_aliases(Unifiers).
 3020
 3021
 3022% We can do this because the ONLY unbound variables in the join tree are table aliases
 3023unify_table_aliases([]).
 3024unify_table_aliases([TableAliasA=TableAliasB|Unifiers]) :-
 3025        TableAliasA = TableAliasB,
 3026        unify_table_aliases(Unifiers).
 3027
 3028
 3029solve_sub_query @
 3030        generate_sub_query_sql,
 3031        query(SubQueryId, _, sub_query)
 3032        ==>
 3033        phase(SubQueryId, initial).
 3034
 3035
 3036solve_top_level_query @
 3037        write_query_sql,
 3038        query(QueryId, _, top_level_query)
 3039        ==>
 3040        phase(QueryId, initial).
 3041
 3042
 3043ignore_equality_restriction_variable_if_it_becomes_bound @
 3044        equality_restriction_variable(Variable, _)
 3045        <=>
 3046        nonvar(Variable)
 3047        |
 3048        true.
 3049
 3050
 3051add_sub_query_join_where @
 3052        query(TopLevelQueryId, _, top_level_query),
 3053        query(SubQueryId, _, sub_query),
 3054        attribute_binding(TopLevelQueryId, attribute(_, TopLevelTableAlias, TopLevelAttributeName), Variable),
 3055        attribute_binding(SubQueryId, attribute(_, SubQueryTableAlias, SubQueryAttributeName), Variable)
 3056        \
 3057        % No longer a SELECT binding in the sub-query; its now part of the sub query WHERE
 3058        select_binding(SubQueryId, _, _, Variable)
 3059        <=>
 3060        not_a_singleton(Variable),
 3061        sub_query_join_variable(Variable),
 3062        restriction_leaf(SubQueryId,
 3063                         where,
 3064                         comparison(attribute(_, TopLevelTableAlias, TopLevelAttributeName),
 3065                                    ==,
 3066                                    attribute(_, SubQueryTableAlias, SubQueryAttributeName))).
 3067
 3068
 3069restriction_from_comparison_of_top_level_query_attribute_to_sub_query_attribute @
 3070        %
 3071        % x :: [a-A, b-B], \+ y :: [b-B, c-C], C > A
 3072        %
 3073        query(QueryId, _, top_level_query),
 3074        query(SubQueryId, _, sub_query),
 3075        attribute_binding(QueryId, Attribute_1, V1),
 3076        attribute_binding(SubQueryId, Attribute_2, V2)
 3077        \
 3078        comparison(QueryId, Lhs, Operator, Rhs)
 3079        <=>
 3080        ( Lhs == V1,
 3081          Rhs == V2 ->
 3082            Comparison = comparison(Attribute_1, Operator, Attribute_2)
 3083
 3084        ; Lhs == V2,
 3085          Rhs == V1 ->
 3086            Comparison = comparison(Attribute_2, Operator, Attribute_1)
 3087        )
 3088        |
 3089        restriction_leaf(SubQueryId, where, Comparison).
 3090
 3091
 3092add_sub_query_restriction @
 3093        create_restrictions
 3094        \
 3095        sub_query_restriction(QueryId, SubQueryType, SubQuerySql, SubQueryTail, SubQueryInputs)
 3096        <=>
 3097        restriction_leaf(QueryId, where, sub_query(SubQueryType, SubQuerySql, SubQueryTail, SubQueryInputs)).
 3098
 3099
 3100restriction_from_bound_attribute @
 3101        %
 3102        % x :: a-'A1'
 3103        % x :: a-['A1', 'A2']
 3104        %
 3105        create_restrictions,
 3106        attribute_binding(QueryId, Attribute, ApplicationValue)
 3107        ==>
 3108        ( ground(ApplicationValue)
 3109        ; is_list(ApplicationValue)
 3110        ),
 3111        ApplicationValue \= selection_constant(_)
 3112        |
 3113        restriction_leaf(QueryId, where, comparison(Attribute, ==, ApplicationValue)).
 3114
 3115
 3116restriction_from_equality_restriction_variable @
 3117        %
 3118        % {[A], x :: [a-A]}
 3119        %
 3120        create_restrictions,
 3121        attribute_binding(QueryId, Attribute, Variable),
 3122        equality_restriction_variable(Variable, EqualityRestrictionVariableUsed)
 3123        ==>
 3124        EqualityRestrictionVariableUsed = equality_restriction_variable_used,
 3125        where_restriction_variable(Variable),
 3126        restriction_leaf(QueryId, where, comparison(Attribute, ==, equality_restriction(Variable))).
 3127
 3128
 3129restriction_from_comparison @
 3130        create_restrictions
 3131        \
 3132        comparison(QueryId, Lhs, Operator, Rhs)
 3133        <=>
 3134        not_a_singleton(Lhs),
 3135        not_a_singleton(Rhs),
 3136        variables_to_attributes(Lhs, MappedLhs),
 3137        variables_to_attributes(Rhs, MappedRhs),
 3138        restriction_leaf(QueryId, where, comparison(MappedLhs, Operator, MappedRhs)).
 3139
 3140cqlv2_1_restriction_from_any_non_fresh_variable @
 3141        query_type(QueryId, QueryType),
 3142        compile_mode(compiletime),
 3143        attribute_binding(QueryId, attribute(Schema, Alias, AttributeName), Variable)
 3144        ==>
 3145        atom(AttributeName), % Exclude aggregates. Is there a more elegant way?
 3146        var(Variable),
 3147        get_attr(Variable, cql_stale, 1)
 3148        |
 3149        ( ( QueryType == select ; QueryType == insert) ->
 3150            Comparison = if_not_var(Variable)
 3151        ; otherwise->
 3152            Comparison = equality_restriction(Variable)
 3153        ),
 3154        cql2_variable(QueryId, Variable, comparison(attribute(Schema, Alias, AttributeName), ==, Comparison)).
 3155
 3156/*
 3157cqlv2_variable_unique @
 3158        cql2_variable(QueryId, _, Variable)
 3159        \
 3160        cql2_variable(QueryId, _, Variable)
 3161        <=>
 3162        true.
 3163*/
 3164
 3165cqlv2_1_except_when_restriction_already_exists_1 @
 3166        equality_restriction_variable(Variable, _)
 3167        \
 3168        cql2_variable(_, Variable, _)
 3169        <=>
 3170        true.
 3171
 3172cqlv2_1_except_when_restriction_already_exists_2 @
 3173        expression_where_restriction_variable(Variable)
 3174        \
 3175        cql2_variable(_, Variable, _)
 3176        <=>
 3177        true.
 3178
 3179cqlv2_1_except_when_restriction_already_exists_3 @
 3180        sub_query_join_variable(Variable)
 3181        \
 3182        cql2_variable(_, Variable, _)
 3183        <=>
 3184        true.
 3185
 3186cqlv2_1_except_when_restriction_already_exists_4 @
 3187        expression_where_restriction_variable(Variable)
 3188        \
 3189        cql2_variable(_, Variable, _)
 3190        <=>
 3191        true.
 3192
 3193cqlv2_1_except_when_restriction_already_exists_5 @
 3194        outer_side_join(Join),
 3195        join_leaf(Join, TableAlias),
 3196        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 3197        join_variable(JoinVariable)
 3198        \
 3199        cql2_variable(_, JoinVariable, _)
 3200        <=>
 3201        true.
 3202
 3203
 3204cqlv2_1_except_comparisons @
 3205        query_type(QueryId, Type),
 3206        comparison(QueryId, Lhs, _Operator, Rhs)
 3207        \
 3208        cql2_variable(QueryId, Variable, _)
 3209        <=>
 3210        % If you write something like
 3211        % foo(X):- {[], some_table :: [column-X], X == 'VALUE'}
 3212        % Then, first of all, you probably made a coding error, since this is unlikely what you meant.
 3213        % X is not actually selected here, it MUST be part of the where clause. Further, it MUST contain 'VALUE'.
 3214        % For now, unify X with the target (Value). However, we must do it after the select. Consider:
 3215        % foo(X):- {[], some_table :: [integer_value-X], X > 500}
 3216        % This should generate
 3217        %   SELECT .... FROM some_table WHERE integer_value > 500
 3218        % What should we actually do with X? If it is bound, then we can make it
 3219        %    SELECT .... FROM some_table WHERE integer_value > 500 AND integer_value = ?
 3220        % If it is NOT bound, then I think this is actually an instantiation error?
 3221        % The trouble comes when we have
 3222        % {[], update(some_table, [...]), @ :: [integer_value-X], X == Y}.
 3223        %   If Y is unbound here, we want a runtime error, but if X is bound, we want an error as well!
 3224        ( Type == update ; Type == delete ),
 3225        ( Lhs == Variable ; Rhs == Variable )
 3226        |
 3227        runtime_instantiation_check(QueryId, Variable).
 3228
 3229
 3230cqlv2_variable_is_ok @
 3231        create_restrictions
 3232        \
 3233        cql2_variable(QueryId, Variable, Comparison)
 3234        <=>
 3235        where_restriction_variable(Variable),
 3236        restriction_leaf(QueryId, where, Comparison).
 3237
 3238
 3239
 3240
 3241
 3242
 3243variables_to_attributes @
 3244        attribute_binding(_, A, Variable) \ variables_to_attributes(Variable, Attribute) <=> var(Variable) | Attribute = A, where_restriction_variable(Variable).
 3245        variables_to_attributes(T1, T2) <=> var(T1) ; ground(T1) | T2 = T1.
 3246        variables_to_attributes([H1|T1], T3) <=> variables_to_attributes(H1, H2), variables_to_attributes(T1, T2), T3 = [H2|T2].
 3247        variables_to_attributes(T1, T4) <=> T1 =.. T2, variables_to_attributes(T2, T3), T4 =.. T3.
 3248
 3249
 3250cleanup_create_restrictions @
 3251        create_restrictions
 3252        <=>
 3253        true.
 3254
 3255
 3256ignore_comparison_to_empty_list @
 3257        restriction_leaf(_, _, comparison(Lhs, ==, Rhs))
 3258        <=>
 3259        ( Lhs == []
 3260        ; Rhs == []
 3261        )
 3262        |
 3263        true.
 3264
 3265
 3266top_level_restriction_cannot_refer_to_a_sub_query_attribute @
 3267        query(TopLevelQueryId, _, top_level_query),
 3268        query(SubQueryId, _, sub_query),
 3269        attribute_binding(SubQueryId, attribute(Schema, TableAlias, AttributeName), _)
 3270        \
 3271        restriction_leaf(TopLevelQueryId, _, comparison(Lhs, _, Rhs))
 3272        <=>
 3273        ( cql_path_arg(_, Lhs, SubTerm)
 3274        ; cql_path_arg(_, Rhs, SubTerm)),
 3275        SubTerm == attribute(Schema, TableAlias, AttributeName)
 3276        |
 3277        throw(format('Top level restriction cannot refer to a sub-query attribute : ~w', [AttributeName])).
 3278
 3279
 3280simplify_or_restriction @
 3281        % Trivial "or" conditions can arise out from the simplification of disjunctions
 3282        restriction_tree(QueryId, RestrictionType, or(Lhs, Rhs))
 3283        <=>
 3284        ( Lhs == true
 3285        ; Rhs == true
 3286        )
 3287        |
 3288        restriction_tree(QueryId, RestrictionType, true).
 3289
 3290
 3291simplify_and_restriction @
 3292        restriction_tree(QueryId, RestrictionType, and(Lhs, Rhs))
 3293        <=>
 3294        ( Lhs == true ->
 3295            RestrictionTree = Rhs
 3296
 3297        ; Rhs == true ->
 3298            RestrictionTree = Lhs
 3299        )
 3300        |
 3301        restriction_tree(QueryId, RestrictionType, RestrictionTree).
 3302
 3303
 3304add_to_restriction_tree @
 3305        restriction_tree(QueryId, RestrictionType, ExistingRestrictionTree),
 3306        restriction_leaf(QueryId, RestrictionType, Restriction)
 3307        <=>
 3308        restriction_tree(QueryId, RestrictionType, and(ExistingRestrictionTree, Restriction)).
 3309
 3310
 3311insert_inserted_ @
 3312        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3313        <=>
 3314        cql_data_type(Schema, TableName, inserted_, _, _, _, _, _, _, _),
 3315        \+ memberchk(inserted_-_, AttributeNameValuePairs)
 3316        |
 3317        insert(QueryId, Schema, TableName, [inserted_-{timestamp}|AttributeNameValuePairs]).
 3318
 3319
 3320insert_inserted_by_ @
 3321        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3322        <=>
 3323        cql_data_type(Schema, TableName, inserted_by_, _, _, _, _, _, _, _),
 3324        \+ memberchk(inserted_by_-_, AttributeNameValuePairs)
 3325        |
 3326        insert(QueryId, Schema, TableName, [inserted_by_-{user_id}|AttributeNameValuePairs]).
 3327
 3328
 3329insert_updated_ @
 3330        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3331        <=>
 3332        cql_data_type(Schema, TableName, updated_, _, _, _, _, _, _, _),
 3333        \+ memberchk(updated_-_, AttributeNameValuePairs)
 3334        |
 3335        insert(QueryId, Schema, TableName, [updated_-{timestamp}|AttributeNameValuePairs]).
 3336
 3337
 3338insert_updated_by_ @
 3339        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3340        <=>
 3341        cql_data_type(Schema, TableName, updated_by_, _, _, _, _, _, _, _),
 3342        \+ memberchk(updated_by_-_, AttributeNameValuePairs)
 3343        |
 3344        insert(QueryId, Schema, TableName, [updated_by_-{user_id}|AttributeNameValuePairs]).
 3345
 3346
 3347update_updated_ @
 3348        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3349        <=>
 3350        cql_data_type(Schema, TableName, updated_, _, _, _, _, _, _, _),
 3351        \+ memberchk(updated_-_, AttributeNameValuePairs)
 3352        |
 3353        update(QueryId, Schema, TableName, TableAlias, [updated_-{timestamp}|AttributeNameValuePairs]).
 3354
 3355
 3356update_updated_by_ @
 3357        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3358        <=>
 3359        cql_data_type(Schema, TableName, updated_by_, _, _, _, _, _, _, _),
 3360        \+ memberchk(updated_by_-_, AttributeNameValuePairs)
 3361        |
 3362        update(QueryId, Schema, TableName, TableAlias, [updated_by_-{user_id}|AttributeNameValuePairs]).
 3363
 3364
 3365insert_transaction_id_ @
 3366        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3367        <=>
 3368        cql_data_type(Schema, TableName, transaction_id_, _, _, _, _, _, _, _),
 3369        \+ memberchk(transaction_id_-_, AttributeNameValuePairs)
 3370        |
 3371        insert(QueryId, Schema, TableName, [transaction_id_-{transaction_id}|AttributeNameValuePairs]).
 3372
 3373
 3374update_transaction_id_ @
 3375        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3376        <=>
 3377        cql_data_type(Schema, TableName, transaction_id_, _, _, _, _, _, _, _),
 3378        \+ memberchk(transaction_id_-_, AttributeNameValuePairs)
 3379        |
 3380        update(QueryId, Schema, TableName, TableAlias, [transaction_id_-{transaction_id}|AttributeNameValuePairs]).
 3381
 3382
 3383insert_generation_ @
 3384        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3385        <=>
 3386        cql_data_type(Schema, TableName, generation_, _, _, _, _, _, _, _),
 3387        \+ memberchk(generation_-_, AttributeNameValuePairs)
 3388        |
 3389        insert(QueryId, Schema, TableName, [generation_-0|AttributeNameValuePairs]).
 3390
 3391
 3392update_generation_ @
 3393        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3394        <=>
 3395        cql_data_type(Schema, TableName, generation_, _, _, _, _, _, _, _),
 3396        \+ memberchk(generation_-_, AttributeNameValuePairs)
 3397        |
 3398        update(QueryId, Schema, TableName, TableAlias, [generation_-{increment}|AttributeNameValuePairs]).
 3399
 3400
 3401write_insert_based_on_select @
 3402        query_table_alias(QueryId, _, _, _),
 3403        phase(QueryId, initial)
 3404        \
 3405        insert(QueryId, _, TableName, AttributeNameValuePairs)
 3406        <=>
 3407        extract_variables(AttributeNameValuePairs, InsertVariables),
 3408        select_for_insert_variables(InsertVariables, TableName),
 3409        write_sql(QueryId, compile, top, ['INSERT INTO ', table_name(TableName), ' ('|T1], T1, [], []),
 3410        write_insert_attribute_names(QueryId, AttributeNameValuePairs),
 3411        write_sql(QueryId, compile, top, [') '|T2], T2, [], []).
 3412
 3413
 3414
 3415extract_variables([], []).
 3416
 3417extract_variables([_-V|AttributeNameValuePairs], [V|InsertVariables]) :-
 3418        var(V), !,
 3419        extract_variables(AttributeNameValuePairs, InsertVariables).
 3420
 3421extract_variables([_|AttributeNameValuePairs], InsertVariables) :-
 3422        extract_variables(AttributeNameValuePairs, InsertVariables).
 3423
 3424
 3425write_insert @
 3426        phase(QueryId, initial),
 3427        insert(QueryId, Schema, TableName, AttributeNameValuePairs)
 3428        <=>
 3429        ( AttributeNameValuePairs == []->
 3430            write_sql(QueryId, compile, top, ['INSERT INTO ', table_name(TableName), ' DEFAULT VALUES'|T1], T1, [], [])
 3431        ;
 3432            write_sql(QueryId, compile, top, ['INSERT INTO ', table_name(TableName), ' ('|T2], T2, [], []),
 3433            write_insert_attribute_names(QueryId, AttributeNameValuePairs),
 3434            write_sql(QueryId, compile, top, [') VALUES ('|T3], T3, [], []),
 3435            write_insert_values(QueryId, Schema, TableName, AttributeNameValuePairs),
 3436            ( dbms(Schema, 'PostgreSQL'),
 3437              database_identity(Schema, TableName, PrimaryKey) ->
 3438                write_sql(QueryId, compile, top, [') RETURNING ', PrimaryKey|T4], T4, [], [])
 3439            ; otherwise->
 3440                write_sql(QueryId, compile, top, [')'|T4], T4, [], [])
 3441            )
 3442        ).
 3443
 3444
 3445write_insert_attribute_names_1 @
 3446        write_insert_attribute_names(QueryId, [AttributeName-_])
 3447        <=>
 3448        write_insert_attribute_name(QueryId, AttributeName).
 3449
 3450
 3451write_insert_attribute_names_2 @
 3452        write_insert_attribute_names(QueryId, [AttributeName-_|AttributeNameValuePairs])
 3453        <=>
 3454        write_insert_attribute_name(QueryId, AttributeName),
 3455        write_sql(QueryId, compile, top, [', '|T], T, [], []),
 3456        write_insert_attribute_names(QueryId, AttributeNameValuePairs).
 3457
 3458
 3459write_insert_attribute_name @
 3460        write_insert_attribute_name(QueryId, AttributeName)
 3461        <=>
 3462        write_sql(QueryId, compile, top, [attribute_name(AttributeName)|T], T, [], []).
 3463
 3464
 3465write_insert_values_1 @
 3466        write_insert_values(QueryId, Schema, TableName, [AttributeName-ApplicationValue])
 3467        <=>
 3468        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue).
 3469
 3470
 3471write_insert_values_2 @
 3472        write_insert_values(QueryId, Schema, TableName, [AttributeName-ApplicationValue|AttributeNameValuePairs])
 3473        <=>
 3474        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue),
 3475        write_sql(QueryId, compile, top, [', '|T], T, [], []),
 3476        write_insert_values(QueryId, Schema, TableName, AttributeNameValuePairs).
 3477
 3478
 3479write_in_line_formatted_insert_value @
 3480        write_insert_value(QueryId, Schema, TableName, AttributeName, format(Format, FormatArgs))
 3481        <=>
 3482        in_line_format(QueryId, Format, FormatArgs, ApplicationValue),
 3483        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue).
 3484
 3485
 3486write_insert_value @
 3487        write_insert_value(QueryId, Schema, TableName, AttributeName, ApplicationValue)
 3488        <=>
 3489        write_sql(QueryId, compile, top, [?|T], T, [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, insert_value, _)], []).
 3490
 3491
 3492write_update @
 3493        update(QueryId, Schema, TableName, TableAlias, AttributeNameValuePairs)
 3494        \
 3495        phase(QueryId, initial)
 3496        <=>
 3497        ( dbms(Schema, 'Microsoft SQL Server') ->
 3498            write_sql(QueryId, compile, top, ['UPDATE ', TableAlias, ' SET '|T], T, [], [])
 3499
 3500        ; dbms(Schema, 'PostgreSQL') ->
 3501            write_sql(QueryId, compile, top, ['UPDATE ', TableName, ' ', TableAlias, ' SET '|T], T, [], [])
 3502        ; dbms(Schema, 'SQLite') ->
 3503            % SQLite does not support joins in updates. However, it has an ID for each row, meaning we can put this:
 3504            % UPDATE <tablename> SET <columns without aliases> WHERE rowid IN (SELECT <tablename>.rowid FROM <rest of the query>)
 3505            write_sql(QueryId, compile, top, ['UPDATE ', TableName, ' SET '|T], T, [], [])
 3506        ),
 3507        write_update_attributes(QueryId, TableAlias, AttributeNameValuePairs),
 3508        phase(QueryId, from).
 3509
 3510
 3511write_update_attributes_1 @
 3512        write_update_attributes(QueryId, TableAlias, [AttributeName-ApplicationValue])
 3513        <=>
 3514        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue).
 3515
 3516
 3517write_update_attributes_2 @
 3518        write_update_attributes(QueryId, TableAlias, [AttributeName-ApplicationValue|AttributeNameValuePairs])
 3519        <=>
 3520        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue),
 3521        write_sql(QueryId, compile, top, [', '|T], T, [], []),
 3522        write_update_attributes(QueryId, TableAlias, AttributeNameValuePairs).
 3523
 3524
 3525write_update_attributes_3 @
 3526        write_update_attributes(_, _, AttributeNameValuePairs)
 3527        <=>
 3528        throw(format('Bad UPDATE attributes: ~w',  [AttributeNameValuePairs])).
 3529
 3530
 3531write_generation_attribute @
 3532        update_table_alias(QueryId, Schema, _, TableAlias)
 3533        \
 3534        write_update_attribute(QueryId, TableAlias, AttributeName, {increment})
 3535        <=>
 3536        AttributeName == generation_
 3537        |
 3538        ( dbms(Schema, 'Microsoft SQL Server') ->
 3539            write_sql(QueryId,
 3540                      compile,
 3541                      top,
 3542                      [TableAlias, '.', attribute_name(AttributeName), =, TableAlias, '.', attribute_name(AttributeName), +, '1'|T],
 3543                      T,
 3544                      [],
 3545                      [])
 3546        ; dbms(Schema, 'PostgreSQL') ->
 3547            write_sql(QueryId, compile,
 3548                      top,
 3549                      [attribute_name(AttributeName), =, TableAlias, '.', attribute_name(AttributeName), +, '1'|T],
 3550                      T,
 3551                      [],
 3552                      [])
 3553        ; dbms(Schema, 'SQLite') ->
 3554            write_sql(QueryId, compile,
 3555                      top,
 3556                      [attribute_name(AttributeName), =, attribute_name(AttributeName), +, '1'|T],
 3557                      T,
 3558                      [],
 3559                      [])
 3560        ).
 3561
 3562
 3563write_update_attribute_copy_sql_server @
 3564        query_table_alias(QueryId, Schema, _, TableAlias),
 3565        select_attribute(QueryId, select_attribute(_, _, _, SelectTableAlias, SelectAttributeName), 1, SelectAttributeVariableUsed, SelectVariable)
 3566        \
 3567        write_update_attribute(QueryId, TableAlias, AttributeName, UpdateVariable)
 3568        <=>
 3569        dbms(Schema, 'Microsoft SQL Server'),
 3570        var(SelectVariable),
 3571        SelectVariable == UpdateVariable
 3572        |
 3573        SelectAttributeVariableUsed = select_attribute_variable_used,
 3574        write_sql(QueryId,
 3575                  compile,
 3576                  top,
 3577                  [TableAlias, '.', attribute_name(AttributeName), =, SelectTableAlias, '.', attribute_name(SelectAttributeName)|T],
 3578                  T,
 3579                  [],
 3580                  []).
 3581
 3582write_update_attribute_copy_postgres @
 3583        update_table_alias(QueryId, Schema, _, TargetAlias),
 3584        query_table_alias(QueryId, _, _, TableAlias),
 3585        select_attribute(QueryId, select_attribute(_, _, _, SelectTableAlias, SelectAttributeName), 1, SelectAttributeVariableUsed, SelectVariable)
 3586        \
 3587        write_update_attribute(QueryId, TableAlias, AttributeName, UpdateVariable)
 3588        <=>
 3589        dbms(Schema, 'PostgreSQL'),
 3590        var(SelectVariable),
 3591        SelectVariable == UpdateVariable
 3592        |
 3593        SelectAttributeVariableUsed = select_attribute_variable_used,
 3594
 3595        ( TargetAlias == TableAlias ->
 3596            write_sql(QueryId,
 3597                      compile,
 3598                      top,
 3599                      [attribute_name(AttributeName), =, SelectTableAlias, '.', attribute_name(SelectAttributeName)|T],
 3600                      T,
 3601                      [],
 3602                      [])
 3603        ; otherwise->
 3604            write_sql(QueryId,
 3605                      compile,
 3606                      top,
 3607                      [TableAlias, '.', attribute_name(AttributeName), =, SelectTableAlias, '.', attribute_name(SelectAttributeName)|T],
 3608                      T,
 3609                      [],
 3610                      [])
 3611
 3612        ).
 3613
 3614
 3615write_update_attribute_copy_sqlite @
 3616        update_table_alias(QueryId, Schema, _, TargetAlias),
 3617        query_table_alias(QueryId, Schema, TableName, TableAlias),
 3618        select_attribute(QueryId, select_attribute(_, _, _, SelectTableAlias, SelectAttributeName), 1, SelectAttributeVariableUsed, SelectVariable)
 3619        \
 3620        write_update_attribute(QueryId, TableAlias, AttributeName, UpdateVariable)
 3621        <=>
 3622        dbms(Schema, 'SQLite'),
 3623        var(SelectVariable),
 3624        SelectVariable == UpdateVariable
 3625        |
 3626        SelectAttributeVariableUsed = select_attribute_variable_used,
 3627        ( TargetAlias == SelectTableAlias->
 3628            % This is the simplest case. Otherwise we must write a subquery
 3629            write_sql(QueryId,
 3630                      compile,
 3631                      top,
 3632                      [attribute_name(AttributeName), =, attribute_name(SelectAttributeName)|T],
 3633                      T,
 3634                      [],
 3635                      [])
 3636        ; otherwise->
 3637            write_sql(QueryId,
 3638                      compile,
 3639                      top,
 3640                      [attribute_name(AttributeName), =, '(SELECT ', SelectTableAlias, '.', attribute_name(SelectAttributeName), ' '|T],
 3641                      T,
 3642                      [],
 3643                      []),
 3644          Tail = [' AND ', TableAlias, '.rowid = ', TableName, '.rowid)'|T3],
 3645          write_sql(QueryId,
 3646                    compile,
 3647                    top,
 3648                    X,
 3649                    T3,
 3650                    Parameters,
 3651                    []),
 3652          find_copy_of_from(QueryId, X, Tail, Parameters)
 3653        ).
 3654
 3655write_in_line_formatted_update_attribute @
 3656        query_table_alias(QueryId, _, _, TableAlias)
 3657        \
 3658        write_update_attribute(QueryId, TableAlias, AttributeName, format(Format, FormatArgs))
 3659        <=>
 3660        in_line_format(QueryId, Format, FormatArgs, ApplicationValue),
 3661        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue).
 3662
 3663:-multifile(cql_atomic_value_check_hook/1). 3664write_atomic_update_attribute @
 3665        query_table_alias(QueryId, Schema, TableName, TableAlias)
 3666        \
 3667        write_update_attribute(QueryId, TableAlias, AttributeName, ApplicationValue)
 3668        <=>
 3669        ( var(ApplicationValue)
 3670        ; atom(ApplicationValue)
 3671        ; integer(ApplicationValue)
 3672        ; rational(ApplicationValue)
 3673        ; cql_atomic_value_check_hook(ApplicationValue)
 3674        ; ApplicationValue == {null}
 3675        ; ApplicationValue == {timestamp}
 3676        ; ApplicationValue == {user_id}
 3677        ; ApplicationValue == {transaction_id}
 3678        )
 3679        |
 3680        ( dbms(Schema, 'Microsoft SQL Server') ->
 3681            write_sql(QueryId,
 3682                      compile,
 3683                      top,
 3684                      [TableAlias, '.', attribute_name(AttributeName), =, ?|T],
 3685                      T,
 3686                      [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, update_value, _)],
 3687                      [])
 3688        ; dbms(Schema, 'PostgreSQL') ->
 3689            write_sql(QueryId,
 3690                      compile,
 3691                      top,
 3692                      [attribute_name(AttributeName), =, ?|T],
 3693                      T,
 3694                      [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, update_value, _)],
 3695                      [])
 3696        ; dbms(Schema, 'SQLite') ->
 3697            write_sql(QueryId,
 3698                      compile,
 3699                      top,
 3700                      [attribute_name(AttributeName), =, ?|T],
 3701                      T,
 3702                      [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, update_value, _)],
 3703                      [])
 3704
 3705        ).
 3706
 3707write_evaluated_update_attribute @
 3708        query_table_alias(QueryId, Schema, TableName, TableAlias)
 3709        \
 3710        write_update_attribute(QueryId, TableAlias, AttributeName, Expression)
 3711        <=>
 3712        ( dbms(Schema, 'Microsoft SQL Server') ; dbms(Schema, 'PostgreSQL') )
 3713        |
 3714        ( dbms(Schema, 'Microsoft SQL Server') ->
 3715            write_sql(QueryId,
 3716                      compile,
 3717                      top,
 3718                      [TableAlias, '.', attribute_name(AttributeName), =|T],
 3719                      T,
 3720                      [odbc_parameter(Schema, TableName, AttributeName, {null}, evaluated_update_attribute, _)],
 3721                      [])
 3722        ; dbms(Schema, 'PostgreSQL') ->
 3723            write_sql(QueryId,
 3724                      compile,
 3725                      top,
 3726                      [attribute_name(AttributeName), =|T],
 3727                      T,
 3728                      [odbc_parameter(Schema, TableName, AttributeName, {null}, evaluated_update_attribute, _)],
 3729                      [])
 3730        ),
 3731        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Expression).
 3732
 3733write_evaluated_update_attribute_sqlite @
 3734        query_table_alias(QueryId, Schema, TableName, TableAlias)
 3735        \
 3736        write_update_attribute(QueryId, TableAlias, AttributeName, Expression)
 3737        <=>
 3738        dbms(Schema, 'SQLite')
 3739        |
 3740        % SQLite does not support the above method. Instead of Expression, we must actually put the entire FROM/WHERE clause here in a subquery like
 3741        % attribute_name(AttributeName) = SELECT(Expression FROM .......)
 3742        write_sql(QueryId,
 3743                  compile,
 3744                  top,
 3745                  [attribute_name(AttributeName), =, '(SELECT '|T],
 3746                  T,
 3747                  [odbc_parameter(Schema, TableName, AttributeName, {null}, evaluated_update_attribute, _)],
 3748                  []),
 3749        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Expression),
 3750        Tail = [' AND ', TableAlias, '.rowid = ', TableName, '.rowid)'|T3],
 3751        write_sql(QueryId,
 3752                    compile,
 3753                    top,
 3754                    X,
 3755                    T3,
 3756                    Parameters,
 3757                    []),
 3758        find_copy_of_from(QueryId, X, Tail, Parameters).
 3759
 3760
 3761write_evaluated_update_binary_expression @
 3762        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Expression)
 3763        <=>
 3764        nonvar(Expression),
 3765        functor(Expression, Operator, 2),
 3766        memberchk(Operator, [+, -, *, /])
 3767        |
 3768        arg(1, Expression, Lhs),
 3769        arg(2, Expression, Rhs),
 3770        ( dbms(Schema, 'SQLite') ->
 3771            % SQLite defaults to integer arithmetic. Fix this here
 3772            write_sql(QueryId, compile, top, ['(1.0*'|T1], T1, [], [])
 3773        ; otherwise->
 3774            write_sql(QueryId, compile, top, ['('|T1], T1, [], [])
 3775        ),
 3776        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Lhs),
 3777        write_sql(QueryId, compile, top, [' ', Operator, ' '|T2], T2, [], []),
 3778        write_expression(QueryId, Schema, TableName, AttributeName, TableAlias, Rhs),
 3779        ( dbms(Schema, 'SQLite') ->
 3780            % SQLite defaults to integer arithmetic.
 3781            write_sql(QueryId, compile, top, ['*1.0)'|T3], T3, [], [])
 3782        ; otherwise->
 3783            write_sql(QueryId, compile, top, [')'|T3], T3, [], [])
 3784        ).
 3785
 3786write_evaluated_update_expression_attribute @
 3787        select_attribute(QueryId, select_attribute(_, _, _, TableAlias, AttributeName), 1, SelectAttributeVariableUsed, Variable)
 3788        \
 3789        write_expression(QueryId, _, _, _, _, Variable)
 3790        <=>
 3791        SelectAttributeVariableUsed = select_attribute_variable_used,
 3792        write_sql(QueryId, compile, top, [TableAlias, '.', attribute_name(AttributeName)|T], T, [], []).
 3793
 3794write_evaluated_update_expression_constant @
 3795        write_expression(QueryId, _, _, _, _, Constant)
 3796        <=>
 3797        integer(Constant)
 3798        |
 3799        atom_number(ConstantAtom, Constant),
 3800        write_sql(QueryId, compile, top, [ConstantAtom|T], T, [], []).
 3801
 3802write_evaluated_update_expression_parameter @
 3803        write_expression(QueryId, Schema, TableName, AttributeName, _, Variable)
 3804        <=>
 3805        var(Variable)
 3806        |
 3807        write_sql(QueryId,
 3808                  compile,
 3809                  top,
 3810                  [?|T],
 3811                  T,
 3812                  [odbc_parameter(Schema, TableName, AttributeName, Variable, evaluated_update_parameter, _)],
 3813                  []).
 3814
 3815
 3816write_evaluated_update_table_expression_attribute @
 3817        write_expression(QueryId, _, _, _, TableAlias, AttributeName)
 3818        <=>
 3819        write_sql(QueryId, compile, top, [TableAlias, '.', attribute_name(AttributeName)|T], T, [], []).
 3820
 3821
 3822write_delete_with_no_where_clause @
 3823        restriction_tree(QueryId, where, true)
 3824        \
 3825        phase(QueryId, initial),
 3826        delete_row(QueryId, TableName, _)
 3827        <=>
 3828        write_sql(QueryId, compile, top, ['DELETE'|T1], T1, [], []),
 3829        write_sql(QueryId, compile, join, [' FROM ', table_name(TableName)|T2], T2, [], []),
 3830        phase(QueryId, where).
 3831
 3832
 3833write_delete_with_where_clause @
 3834        phase(QueryId, initial),
 3835        delete_row(QueryId, TableName, TableAlias)
 3836        <=>
 3837        TableAlias = TableName,
 3838        write_sql(QueryId, compile, top, ['DELETE'|T1], T1, [], []),
 3839        write_sql(QueryId, compile, join, [' FROM ', TableAlias|T2], T2, [], []),
 3840        phase(QueryId, where).
 3841
 3842
 3843write_select_keyword @
 3844        phase(QueryId, initial)
 3845        <=>
 3846        write_sql(QueryId, compile, top, ['SELECT '|T], T, [], []),
 3847        phase(QueryId, distinct).
 3848
 3849
 3850write_select_distinct @
 3851        select_distinction(QueryId, DistinctionType)
 3852        \
 3853        phase(QueryId, distinct)
 3854        <=>
 3855        DistinctionType \== no_distinction
 3856        |
 3857        write_sql(QueryId, compile, top, ['DISTINCT '|T], T, [], []),
 3858        phase(QueryId, top).
 3859
 3860
 3861no_distinct @
 3862        phase(QueryId, distinct)
 3863        <=>
 3864        phase(QueryId, top).
 3865
 3866
 3867should_not_be_any_distinct_constraints_left_over @
 3868        check_for_orphan_distincts,
 3869        distinct(_, Distinct),
 3870        original_cql(Cql)
 3871        <=>
 3872        throw(format('Unused DISTINCT ~w in CQL: ~w', [Distinct, Cql])).
 3873
 3874select_variable_is_used_if_its_a_where_variable @
 3875        select_attribute(_, _, 1, SelectAttributeVariableUsed, Variable),
 3876        where_restriction_variable(Variable)
 3877        ==>
 3878        SelectAttributeVariableUsed = select_attribute_variable_used.
 3879
 3880select_variable_is_used_if_its_a_join_variable @
 3881        select_attribute(_, _, 1, SelectAttributeVariableUsed, Variable),
 3882        join_variable(Variable)
 3883        ==>
 3884        SelectAttributeVariableUsed = select_attribute_variable_used.
 3885
 3886
 3887select_variable_is_used_if_its_a_sub_query_join_variable @
 3888        select_attribute(_, _, 1, SelectAttributeVariableUsed, Variable),
 3889        sub_query_join_variable(Variable)
 3890        ==>
 3891        SelectAttributeVariableUsed = select_attribute_variable_used.
 3892
 3893check_for_orphan_select_variables_in_updates @
 3894        check_for_orphan_select_variables_in_updates,
 3895        query_type(QueryId, update),
 3896        select_attribute(QueryId, _, 1, SelectAttributeVariableUsed, Variable),
 3897        original_cql(Cql)
 3898        <=>
 3899        var(SelectAttributeVariableUsed)
 3900        |
 3901        throw(format('Unused SELECT variable ~w in UPDATE in CQL: ~w', [Variable, Cql])).
 3902
 3903original_cql_uniqueness @
 3904        original_cql(Cql)
 3905        \
 3906        original_cql(Cql)
 3907        <=>
 3908        true.
 3909
 3910
 3911statement_location_uniqueness @
 3912        cql_statement_location(FileName, LineNumber)
 3913        \
 3914        cql_statement_location(FileName, LineNumber)
 3915        <=>
 3916        true.
 3917
 3918
 3919top_n_error @
 3920        top(_, _, N),
 3921        original_cql(Cql)
 3922        ==>
 3923        \+ var(N),
 3924        \+ (integer(N), N >= 0)
 3925        |
 3926        throw(format('The N in top(N) must be an integer and not less than zero but found ~w in CQL: ~w', [N, Cql])).
 3927
 3928
 3929write_select_top_n @
 3930        phase(QueryId, top),
 3931        top(QueryId, Schema, N)
 3932        <=>
 3933        ( dbms(Schema, 'Microsoft SQL Server') ->
 3934            ( var(N) ->
 3935                write_sql(QueryId,
 3936                          compile,
 3937                          top,
 3938                          ['TOP (?) '|T],
 3939                          T,
 3940                          [odbc_explicit_type_parameter(integer, N, top_value)],
 3941                          [])
 3942            ; otherwise->
 3943                write_sql(QueryId, compile, top, ['TOP ', N, ' '|T], T, [], [])
 3944            )
 3945        ; dbms(Schema, 'PostgreSQL') ->
 3946            limit(QueryId, Schema, N)
 3947        ; dbms(Schema, 'SQLite') ->
 3948            limit(QueryId, Schema, N)
 3949        ),
 3950        phase(QueryId, select_attributes).
 3951
 3952
 3953no_top @
 3954        phase(QueryId, top)
 3955        <=>
 3956        phase(QueryId, select_attributes).
 3957
 3958
 3959write_limit @
 3960        write_limit,
 3961        limit(QueryId, Schema, N)
 3962        <=>
 3963        ( ( dbms(Schema, 'PostgreSQL') ; dbms(Schema, 'SQLite') )->
 3964            (var(N)->
 3965               write_sql(QueryId,
 3966                         compile,
 3967                          where,
 3968                         [' LIMIT (?) '|T],
 3969                         T,
 3970                         [odbc_explicit_type_parameter(integer, N, top_value)],
 3971                         [])
 3972            ; otherwise->
 3973                write_sql(QueryId, compile, where, [' LIMIT ', N, ' '|T], T, [], [])
 3974            )
 3975        ; otherwise->
 3976           true).
 3977
 3978no_limit @
 3979        write_limit
 3980        <=>
 3981        true.
 3982
 3983write_sub_query_select @
 3984        sub_query_select(QueryId)
 3985        \
 3986        phase(QueryId, select_attributes)
 3987        <=>
 3988        write_sql(QueryId, compile, top, [*|T], T, [], []),
 3989        phase(QueryId, from).
 3990
 3991
 3992write_select_for_insert_attributes @
 3993        phase(QueryId, select_attributes),
 3994        query_table_alias(QueryId, _, _, _)
 3995        \
 3996        select_for_insert_variables([Variable|InsertVariables], InsertTableName)
 3997        <=>
 3998        select_for_insert_variable(QueryId, Variable, InsertTableName),
 3999        select_for_insert_variables(InsertVariables, InsertTableName).
 4000
 4001
 4002write_select_for_insert_attributes_complete @
 4003        phase(QueryId, select_attributes),
 4004        select_for_insert_variables([], _)
 4005        <=>
 4006        phase(QueryId, from).
 4007
 4008
 4009write_select_for_insert_count @
 4010        select_for_insert_variable(QueryId, Variable, _),
 4011        select_attribute(QueryId, select_attribute(aggregation(count), _, _, TableAlias, AttributeName), 1, _, Variable)
 4012        <=>
 4013        write_select_attribute(QueryId,
 4014                               compile,
 4015                               ['count(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4016                               T,
 4017                               ignore_output).
 4018
 4019
 4020write_select_for_insert_aggregation @
 4021        select_for_insert_variable(QueryId, Variable, _),
 4022        select_attribute(QueryId, select_attribute(aggregation(AggregationOperator), _, _, TableAlias, AttributeName), 1, _, Variable)
 4023        <=>
 4024        % Why did someone try and do this?!
 4025        %map_database_atom(AggregationOperator, AggregationOperatorUc),
 4026        write_select_attribute(QueryId,
 4027                               compile,
 4028                               [AggregationOperator, '(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4029                               T,
 4030                               ignore_output).
 4031
 4032
 4033write_select_for_insert_select_constant @
 4034        select_for_insert_variable(QueryId, Variable, InsertTableName),
 4035        select_attribute(QueryId, select_attribute(plain, Schema, _, _, AttributeName), 1, _, selection_constant(Variable))
 4036        <=>
 4037        write_select_attribute(QueryId,
 4038                               compile,
 4039                               ['? AS ', attribute_name(AttributeName)|T],
 4040                               T,
 4041                               selection_constant(Schema, InsertTableName, AttributeName, Variable)).
 4042
 4043
 4044write_select_for_insert_plain_attribute @
 4045        select_for_insert_variable(QueryId, Variable, _),
 4046        select_attribute(QueryId, select_attribute(plain, _, _, TableAlias, AttributeName), 1, _, Variable)
 4047        <=>
 4048        write_select_attribute(QueryId,
 4049                               compile,
 4050                               [TableAlias, '.', attribute_name(AttributeName)|T],
 4051                               T,
 4052                               ignore_output).
 4053
 4054
 4055write_select_attributes @
 4056        select_attribute(QueryId, _, _, _, _)
 4057        \
 4058        phase(QueryId, select_attributes)
 4059        <=>
 4060        write_select_attributes(QueryId),
 4061        phase(QueryId, from).
 4062
 4063
 4064write_do_nothing_select @
 4065        phase(QueryId, select_attributes)
 4066        <=>
 4067        write_sql(QueryId, compile, top, [0|T], T, [], [ignore_output]),
 4068        % no human
 4069        phase(QueryId, from).
 4070
 4071
 4072write_select_count_attribute @
 4073        write_select_attributes(QueryId),
 4074        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4075        \
 4076        select_attribute(QueryId, select_attribute(aggregation(count), Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4077        <=>
 4078        get_data_size(Schema, TableName, AttributeName, Size),
 4079        include_select_attribute(QueryId,
 4080                                 compile,
 4081                                 Size,
 4082                                 ['count(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4083                                 T,
 4084                                 count(Variable)).
 4085
 4086write_select_avg_attribute_for_postgres @
 4087        write_select_attributes(QueryId),
 4088        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4089        \
 4090        select_attribute(QueryId, select_attribute(aggregation(avg), Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4091        <=>
 4092        dbms(Schema, 'PostgreSQL')
 4093        |
 4094        %map_database_atom(avg, AggregationOperatorUc),
 4095        upcase_atom(avg, AggregationOperatorUc),
 4096        get_data_size(Schema, TableName, AttributeName, Size),
 4097        include_select_attribute(QueryId,
 4098                                 compile,
 4099                                 Size,
 4100                                 [AggregationOperatorUc, '(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4101                                 T,
 4102                                 avg(Variable)).
 4103
 4104write_select_aggregation_attribute @
 4105        write_select_attributes(QueryId),
 4106        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4107        \
 4108        select_attribute(QueryId, select_attribute(aggregation(AggregationOperator), Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4109        <=>
 4110        get_data_size(Schema, TableName, AttributeName, Size),
 4111        %map_database_atom(AggregationOperator, AggregationOperatorUc),
 4112        upcase_atom(AggregationOperator, AggregationOperatorUc),
 4113        include_select_attribute(QueryId,
 4114                                 compile,
 4115                                 Size,
 4116                                 [AggregationOperatorUc, '(', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 4117                                 T,
 4118                                 output(Schema, TableName, AttributeName, Variable)).
 4119
 4120
 4121write_top_level_select_attribute @
 4122        write_select_attributes(QueryId),
 4123        query(QueryId, _, top_level_query),
 4124        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4125        \
 4126        select_attribute(QueryId, select_attribute(plain, Schema, _, TableAlias, AttributeName), 1, _, Variable)
 4127        <=>
 4128        get_data_size(Schema, TableName, AttributeName, Size),
 4129        include_select_attribute(QueryId,
 4130                                 if_var(Variable),
 4131                                 Size,
 4132                                 [TableAlias, '.', attribute_name(AttributeName)|T],
 4133                                 T,
 4134                                 output(Schema, TableName, AttributeName, Variable)).
 4135
 4136
 4137
 4138flush_select_attributes @
 4139        write_select_attributes(QueryId)
 4140        ==>
 4141        collect_select_attributes(QueryId, Unsorted),
 4142        keysort(Unsorted, SortedWithKeys),
 4143        cql_strip_sort_keys(SortedWithKeys, Sorted),
 4144        actually_write_select_attributes(QueryId, compile, Sorted).
 4145
 4146collect_select_attributes @
 4147        collect_select_attributes(QueryId, Tail),
 4148        include_select_attribute(QueryId, CompileInstruction, Size, Tokens, TokenTail, Attribute)
 4149        <=>
 4150        Tail = [Size-select_info(CompileInstruction, Tokens, TokenTail, Attribute)|NewTail],
 4151        collect_select_attributes(QueryId, NewTail).
 4152
 4153finished_collecting_select_attributes @
 4154        collect_select_attributes(_, Tail)
 4155        <=>
 4156        Tail = [].
 4157
 4158actually_write_select_attributes(_, _, []).
 4159actually_write_select_attributes(QueryId, _PreviousCompileInstruction, [select_info(CompileInstruction, Tokens, Tail, Attribute)|More]):-
 4160        %instruction_conjunction(PreviousCompileInstruction, CompileInstruction, Conjunction),
 4161        write_select_attribute(QueryId, CompileInstruction, Tokens, Tail, Attribute),
 4162        actually_write_select_attributes(QueryId, CompileInstruction, More).
 4163
 4164
 4165
 4166write_select_attribute_with_leading_comma @
 4167        select_attribute_written(QueryId)
 4168        \
 4169        write_select_attribute(QueryId, CompileInstruction, SqlTokens, Tail, Output)
 4170        <=>
 4171        write_sql(QueryId, CompileInstruction, top, [', '|T], T, [], []),
 4172        write_select_attribute_1(QueryId, CompileInstruction, SqlTokens, Tail, Output).
 4173
 4174
 4175write_select_attribute_without_leading_comma @
 4176        write_select_attribute(QueryId, _CompileInstruction, SqlTokens, Tail, Output)
 4177        <=>
 4178        write_select_attribute_1(QueryId, compile, SqlTokens, Tail, Output),
 4179        select_attribute_written(QueryId).
 4180
 4181
 4182write_select_attribute_1a @
 4183        write_select_attribute_1(QueryId, CompileInstruction, SqlTokens, Tail, selection_constant(Schema, TableName, AttributeName, ApplicationValue))
 4184        <=>
 4185        write_sql(QueryId,
 4186                  CompileInstruction,
 4187                  top,
 4188                  SqlTokens,
 4189                  Tail,
 4190                  [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, insert_value, _)],
 4191                  [ignore_output]).
 4192
 4193write_select_attribute_1c @
 4194        write_select_attribute_1(QueryId, CompileInstruction, SqlTokens, Tail, Output)
 4195        <=>
 4196        write_sql(QueryId, CompileInstruction, top, SqlTokens, Tail, [], [Output]).
 4197
 4198in_line_join_on @
 4199        create_in_line_joins,
 4200        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4201        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4202        ==>
 4203        dbms(Schema, 'Microsoft SQL Server'),
 4204        var(JoinVariableA),
 4205        JoinVariableA == JoinVariableB,
 4206        TableAliasA \== TableAliasB
 4207        |
 4208        join_variable(JoinVariableA),
 4209        join_variable(JoinVariableB),
 4210        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4211
 4212
 4213in_line_join_on @
 4214        create_in_line_joins,
 4215        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4216        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4217        ==>
 4218        dbms(Schema, 'SQLite'),
 4219        var(JoinVariableA),
 4220        JoinVariableA == JoinVariableB,
 4221        TableAliasA \== TableAliasB
 4222        |
 4223        join_variable(JoinVariableA),
 4224        join_variable(JoinVariableB),
 4225        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4226
 4227
 4228/* We can end up with selects mixed in with other types because of how
 4229   translate_select ends up getting called to parse the where clauses of
 4230   other types of query. In this case, we can simply delete all the selects.
 4231*/
 4232
 4233a_query_cannot_be_select_and_something_else @
 4234        query_type(QueryId, _)
 4235        \
 4236        query_type(QueryId, select)
 4237        <=>
 4238        true.
 4239
 4240
 4241in_line_join_on_postgres_select_insert_or_delete @
 4242        create_in_line_joins,
 4243        query_type(QueryId, QueryType),
 4244        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4245        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4246        ==>
 4247        dbms(Schema, 'PostgreSQL'),
 4248        memberchk(QueryType, [select, insert, delete]),
 4249        var(JoinVariableA),
 4250        JoinVariableA == JoinVariableB,
 4251        TableAliasA \== TableAliasB
 4252        |
 4253        join_variable(JoinVariableA),
 4254        join_variable(JoinVariableB),
 4255        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4256
 4257in_line_join_on_postgres_update_but_not_target @
 4258        create_in_line_joins,
 4259        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4260        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB),
 4261        update_table_alias(QueryId, _, _, TargetAlias)
 4262        ==>
 4263        dbms(Schema, 'PostgreSQL'),
 4264        var(JoinVariableA),
 4265        JoinVariableA == JoinVariableB,
 4266        TableAliasA \== TableAliasB,
 4267        TableAliasA \== TargetAlias,
 4268        TableAliasB \== TargetAlias
 4269        |
 4270        join_variable(JoinVariableA),
 4271        join_variable(JoinVariableB),
 4272        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4273
 4274in_line_join_on_postgres_and_is_target @
 4275        create_in_line_joins,
 4276        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4277        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB),
 4278        update_table_alias(QueryId, _, _, TargetAlias)
 4279        ==>
 4280        dbms(Schema, 'PostgreSQL'),
 4281        var(JoinVariableA),
 4282        JoinVariableA == JoinVariableB,
 4283        TableAliasA \== TableAliasB,
 4284        ( TableAliasA == TargetAlias ; TableAliasB == TargetAlias)
 4285        |
 4286        join_variable(JoinVariableA),
 4287        join_variable(JoinVariableB),
 4288        comparison(QueryId, attribute(Schema, TableAliasA, AttributeNameA), ==, attribute(Schema, TableAliasB, AttributeNameB)),
 4289        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4290
 4291cleanup_create_in_line_joins @
 4292        create_in_line_joins
 4293        <=>
 4294        true.
 4295
 4296explicit_join_on_postgres_update @
 4297        create_join_points,
 4298        update_table_alias(QueryId, _, _, TargetAlias),
 4299        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4300        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4301        \
 4302        % Use up the comparison/4 as we don't want it in the WHERE clause
 4303        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4304        <=>
 4305        dbms(Schema, 'PostgreSQL'),
 4306        var(JoinVariableA),
 4307        var(JoinVariableB),
 4308        TableAliasA \== TableAliasB, TableAliasA \== TargetAlias, TableAliasB \== TargetAlias
 4309        |
 4310        join_variable(JoinVariableA),
 4311        join_variable(JoinVariableB),
 4312        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4313
 4314explicit_join_on_postgres_other @
 4315        create_join_points,
 4316        query_type(QueryId, Type),
 4317        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4318        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4319        \
 4320        % Use up the comparison/4 as we don't want it in the WHERE clause
 4321        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4322        <=>
 4323        dbms(Schema, 'PostgreSQL'),
 4324        memberchk(Type, [select, insert, delete]),
 4325        var(JoinVariableA),
 4326        var(JoinVariableB),
 4327        TableAliasA \== TableAliasB
 4328        |
 4329        join_variable(JoinVariableA),
 4330        join_variable(JoinVariableB),
 4331        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4332
 4333
 4334
 4335explicit_join_on @
 4336        create_join_points,
 4337        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4338        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4339        \
 4340        % Use up the comparison/4 as we don't want it in the WHERE clause
 4341        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4342        <=>
 4343        dbms(Schema, 'Microsoft SQL Server'),
 4344        var(JoinVariableA),
 4345        var(JoinVariableB),
 4346        TableAliasA \== TableAliasB
 4347        |
 4348        join_variable(JoinVariableA),
 4349        join_variable(JoinVariableB),
 4350        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4351
 4352explicit_join_on @
 4353        create_join_points,
 4354        attribute_binding(QueryId, attribute(Schema, TableAliasA, AttributeNameA), JoinVariableA),
 4355        attribute_binding(QueryId, attribute(Schema, TableAliasB, AttributeNameB), JoinVariableB)
 4356        \
 4357        % Use up the comparison/4 as we don't want it in the WHERE clause
 4358        comparison(QueryId, JoinVariableA, ==, JoinVariableB)
 4359        <=>
 4360        dbms(Schema, 'SQLite'),
 4361        var(JoinVariableA),
 4362        var(JoinVariableB),
 4363        TableAliasA \== TableAliasB
 4364        |
 4365        join_variable(JoinVariableA),
 4366        join_variable(JoinVariableB),
 4367        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB).
 4368
 4369
 4370avoid_duplicate_join_ons @
 4371        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB)
 4372        \
 4373        join_on(TableAliasB, AttributeNameB, TableAliasA, AttributeNameA)
 4374        <=>
 4375        true.
 4376
 4377
 4378search_for_join_aliases_0 @
 4379        join(_, Join, LhsJoin, _, RhsJoin)
 4380        ==>
 4381        search_for_join_aliases(Join, lhs, LhsJoin),
 4382        search_for_join_aliases(Join, rhs, RhsJoin).
 4383
 4384
 4385search_for_join_aliases_1 @
 4386        join_leaf(Join, TableAlias),
 4387        search_for_join_aliases(JoinParent, Side, Join)
 4388        ==>
 4389        join_alias(JoinParent, Side, TableAlias).
 4390
 4391
 4392search_for_join_aliases_2 @
 4393        join(_, Join, LhsJoin, _, _),
 4394        search_for_join_aliases(JoinParent, Side, Join)
 4395        ==>
 4396        search_for_join_aliases(JoinParent, Side, LhsJoin).
 4397
 4398
 4399search_for_join_aliases_3 @
 4400        join(_, Join, _, _, RhsJoin),
 4401        search_for_join_aliases(JoinParent, Side, Join)
 4402        ==>
 4403        search_for_join_aliases(JoinParent, Side, RhsJoin).
 4404
 4405% This is for PostgreSQL
 4406% If we have an implicit join in some query QueryId, we get the join for free.
 4407% However, to do the pre-state-change stuff, we still have to build the join in memory
 4408% and capture the ON clause. This formula creates the two join points in SubQueryId
 4409% which is where the on/4 term goes and implicit_join_sql/3 looks.
 4410% Note that this is only used for explicit on clauses.
 4411search_for_join_aliases_4 @
 4412        implicit_join(QueryId, _, SubQueryId),
 4413        update_table_alias(QueryId, _Schema, _TableName, TableAlias)
 4414        ==>
 4415        join_alias(SubQueryId, lhs, TableAlias),
 4416        search_for_join_aliases(SubQueryId, rhs, QueryId).
 4417
 4418
 4419
 4420add_on_from_join_on @
 4421        resolve_join_points,
 4422        join_alias(Join, lhs, TableAliasLhs),
 4423        join_alias(Join, rhs, TableAliasRhs)
 4424        \
 4425        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB)
 4426        <=>
 4427        ( TableAliasLhs == TableAliasA,
 4428          TableAliasRhs == TableAliasB
 4429        ; TableAliasLhs == TableAliasB,
 4430          TableAliasRhs == TableAliasA
 4431        )
 4432        |
 4433        add_on(Join, TableAliasA-AttributeNameA==TableAliasB-AttributeNameB).
 4434
 4435
 4436add_ons @
 4437        on(Join, Resolved, On),
 4438        add_on(Join, ExistingOn)
 4439        <=>
 4440        on(Join, Resolved, (ExistingOn, On)).
 4441
 4442
 4443add_on @
 4444        add_on(Join, ExistingOn)
 4445        <=>
 4446        on(Join, _, ExistingOn).
 4447
 4448
 4449resolve_join_points @
 4450        resolve_join_points
 4451        \
 4452        on(Join, Resolved, On)
 4453        <=>
 4454        var(Resolved)
 4455        |
 4456        resolve_join_points(Join, On, NewOn),
 4457        on(Join, resolved, NewOn).
 4458
 4459
 4460cleanup_resolve_join_points @
 4461        resolve_join_points
 4462        <=>
 4463        true.
 4464
 4465
 4466resolve_join_points_1 @
 4467        resolve_join_points(Join, (Lhs, Rhs), NewOn)
 4468        <=>
 4469        resolve_join_points(Join, Lhs, NewLhs),
 4470        resolve_join_points(Join, Rhs, NewRhs),
 4471        NewOn = (NewLhs, NewRhs).
 4472
 4473
 4474resolve_join_points_2 @
 4475        resolve_join_points(Join, (Lhs ; Rhs), NewOn)
 4476        <=>
 4477        resolve_join_points(Join, Lhs, NewLhs),
 4478        resolve_join_points(Join, Rhs, NewRhs),
 4479        NewOn = (NewLhs ; NewRhs).
 4480
 4481
 4482resolve_join_points_3 @
 4483        % Ihese come from in-line (shared varoabel) joins and explicit where-style restrictions
 4484        resolve_join_points(_, TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs, NewOn)
 4485        <=>
 4486        NewOn = (TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs).
 4487
 4488
 4489resolve_join_points_4 @
 4490        % These come from on clauses
 4491        attribute_binding(QueryId, attribute(Schema, TableAliasLhs, AttributeNameLhs), JoinVariableA),
 4492        attribute_binding(QueryId, attribute(Schema, TableAliasRhs, AttributeNameRhs), JoinVariableB),
 4493        join_alias(Join, lhs, TableAliasLhs),
 4494        join_alias(Join, rhs, TableAliasRhs)
 4495        \
 4496        resolve_join_points(Join, A==B, NewOn)
 4497        <=>
 4498        var(A),
 4499        var(B),
 4500        ( (A==B) == (JoinVariableA==JoinVariableB)
 4501        ; (B==A) == (JoinVariableA==JoinVariableB)
 4502        )
 4503        |
 4504        not_a_singleton(JoinVariableA),
 4505        not_a_singleton(JoinVariableB),
 4506        join_variable(JoinVariableA),
 4507        join_variable(JoinVariableB),
 4508        NewOn = (TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs).
 4509
 4510
 4511resolve_join_points_5 @
 4512        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 4513        join_alias(Join, _, TableAlias)
 4514        \
 4515        resolve_join_points(Join, On, NewOn)
 4516        <=>
 4517        On =.. [Operator, V, Rhs],
 4518        JoinVariable == V
 4519        |
 4520        not_a_singleton(JoinVariable),
 4521        join_variable(JoinVariable),
 4522        NewOn =.. [Operator, TableAlias-JoinVariable, Rhs].
 4523
 4524
 4525resolve_join_points_6 @
 4526        attribute_binding(_, attribute(_, TableAlias, _), JoinVariable),
 4527        join_alias(Join, _, TableAlias)
 4528        \
 4529        resolve_join_points(Join, On, NewOn)
 4530        <=>
 4531        On =.. [Operator, Lhs, V],
 4532        JoinVariable == V
 4533        |
 4534        not_a_singleton(JoinVariable),
 4535        join_variable(JoinVariable),
 4536        NewOn =.. [Operator, Lhs, TableAlias-JoinVariable].
 4537
 4538
 4539resolve_join_points_7 @
 4540        resolve_join_points(_, On, _)
 4541        <=>
 4542        throw(format('Cannot translate ON specification: ~w', [On])).
 4543
 4544
 4545join_variable_must_be_a_variable @
 4546        join_variable(Variable)
 4547        <=>
 4548        nonvar(Variable)
 4549        |
 4550        true.
 4551
 4552
 4553join_variable_is_unique @
 4554        join_variable(Variable)
 4555        \
 4556        join_variable(Variable)
 4557        <=>
 4558        true.
 4559
 4560
 4561update_not_from_source @
 4562        query_table_alias(QueryId, _, _, TableAlias),
 4563        update_table_alias(QueryId, Schema, _, TargetAlias)
 4564        \
 4565        phase(QueryId, Phase)
 4566        <=>
 4567        dbms(Schema, 'PostgreSQL'),
 4568        Phase == from,
 4569        TableAlias \== TargetAlias
 4570        |
 4571        write_sql(QueryId, compile, top, [' FROM '|T], T, [], []),
 4572        write_join(QueryId, QueryId),
 4573        phase(QueryId, where).
 4574
 4575update_from_source @
 4576        query_table_alias(QueryId, _, _, _),
 4577        update_table_alias(QueryId, Schema, _, _)
 4578        \
 4579        phase(QueryId, from)
 4580        <=>
 4581        dbms(Schema, 'PostgreSQL')
 4582        |
 4583        phase(QueryId, where).
 4584
 4585
 4586select_from @
 4587        query_table_alias(QueryId, _, _, _)
 4588        \
 4589        phase(QueryId, from)
 4590        <=>
 4591        write_sql(QueryId, compile, join, [' FROM '|T], T, [], []),
 4592        write_join(QueryId, QueryId),
 4593        phase(QueryId, where).
 4594
 4595
 4596no_from_statement @
 4597        phase(QueryId, from)
 4598        <=>
 4599        phase(QueryId, where).
 4600
 4601
 4602% The inclusion of the target alias is implied in Postgres UPDATE ... FROM
 4603write_join_leaf_unless_target @
 4604        join_leaf(Join, TableAlias),
 4605        query_table_alias(QueryId, _, TableName, TableAlias),
 4606        update_table_alias(QueryId, Schema, _, TargetAlias)
 4607        \
 4608        write_join(QueryId, Join)
 4609        <=>
 4610        dbms(Schema, 'PostgreSQL'),
 4611        TargetAlias \== TableAlias
 4612        |
 4613        write_sql(QueryId, compile, join, [table_name(TableName), ' ', TableAlias|T], T, [], []).
 4614
 4615write_join_leaf @
 4616        join_leaf(Join, TableAlias),
 4617        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4618        \
 4619        write_join(QueryId, Join)
 4620        <=>
 4621        write_sql(QueryId, compile, join, [table_name(TableName), ' ', TableAlias|T], T, [], []),
 4622        write_lock_hint(QueryId, Schema, TableAlias).
 4623
 4624
 4625write_lock_hint @
 4626        write_lock_hint(QueryId, Schema, TableAlias),
 4627        nolock(QueryId, TableAlias)
 4628        <=>
 4629        dbms(Schema, 'Microsoft SQL Server')
 4630        |
 4631        write_sql(QueryId, compile, join, [' WITH (NOLOCK)'|T], T, [], []).
 4632
 4633% Ignored for PostgreSQL and SQLite
 4634cleanup_write_lock_hint @
 4635        write_lock_hint(_, _, _)
 4636        <=>
 4637        true.
 4638
 4639
 4640
 4641write_join_clause @
 4642        query(QueryId, Schema, _)
 4643        \
 4644        join(QueryId, Join, LhsJoin, JoinType, RhsJoin),
 4645        write_join(QueryId, Join),
 4646        on(Join, _, On)
 4647        <=>
 4648        write_sql(QueryId, compile, join, ['('|T1], T1, [], []),
 4649        write_join(QueryId, LhsJoin),
 4650        write_sql(QueryId, compile, join, [' ', JoinType, ' '|T2], T2, [], []),
 4651        write_join(QueryId, RhsJoin),
 4652        write_sql(QueryId, compile, join, [' ON '|T3], T3, [], []),
 4653        write_join_ons(QueryId, On),
 4654        write_sql(QueryId, compile, join, [')'|T4], T4, [], []),
 4655
 4656        ( debugging(index_suggestions) ->
 4657            on_to_where(Schema, On, RestrictionTree),
 4658            cql_suggest_indices(RestrictionTree, QueryId)
 4659
 4660        ; otherwise ->
 4661            true
 4662        ).
 4663
 4664
 4665write_join_on_conjunction @
 4666        write_join_ons(QueryId, (Lhs, Rhs))
 4667        <=>
 4668        write_join_ons(QueryId, Lhs),
 4669        write_sql(QueryId, compile, join, [' AND '|T], T, [], []),
 4670        write_join_ons(QueryId, Rhs).
 4671
 4672
 4673write_join_on_disjunction @
 4674        write_join_ons(QueryId, (Lhs ; Rhs))
 4675        <=>
 4676        write_sql(QueryId, compile, join, ['('|T1], T1, [], []),
 4677        write_join_ons(QueryId, Lhs),
 4678        write_sql(QueryId, compile, join, [' OR '|T2], T2, [], []),
 4679        write_join_ons(QueryId, Rhs),
 4680        write_sql(QueryId, compile, join, [')'|T3], T3, [], []).
 4681
 4682
 4683write_join_on_attributes @
 4684        write_join_ons(QueryId, TableAliasLhs-AttributeNameLhs==TableAliasRhs-AttributeNameRhs)
 4685        <=>
 4686        write_sql(QueryId,
 4687                  compile,
 4688                  join,
 4689                  [TableAliasLhs, '.', attribute_name(AttributeNameLhs), =, TableAliasRhs, '.', attribute_name(AttributeNameRhs)|T],
 4690                  T,
 4691                  [],
 4692                  []).
 4693
 4694write_join_on_lhs @
 4695        attribute_binding(_, attribute(Schema, TableAlias, AttributeName), Variable)
 4696        \
 4697        write_join_ons(QueryId, On)
 4698        <=>
 4699        var(Variable),
 4700        On =.. [ComparisonOperator, ApplicationValue, Rhs],
 4701        error_on_comparison_operator(Schema, ComparisonOperator),
 4702        Rhs == TableAlias-Variable
 4703        |
 4704        write_restriction(QueryId, compile, join, ApplicationValue, ComparisonOperator, attribute(Schema, TableAlias, AttributeName)).
 4705
 4706
 4707write_join_on_rhs @
 4708        attribute_binding(_, attribute(Schema, TableAlias, AttributeName), Variable)
 4709        \
 4710        write_join_ons(QueryId, On)
 4711        <=>
 4712        var(Variable),
 4713        On =.. [ComparisonOperator, Lhs, ApplicationValue],
 4714        error_on_comparison_operator(Schema, ComparisonOperator),
 4715        Lhs == TableAlias-Variable
 4716        |
 4717        write_restriction(QueryId, compile, join, attribute(Schema, TableAlias, AttributeName), ComparisonOperator, ApplicationValue).
 4718
 4719error_on_comparison_operator(Schema, ComparisonOperator) :-
 4720        ( ground(ComparisonOperator),
 4721          prolog_to_sql_comparison_operator(Schema, ComparisonOperator, _, _) ->
 4722            true
 4723        ;
 4724            throw(format('Invalid comparison operator in JOIN ON: ~w', [ComparisonOperator]))
 4725        ).
 4726
 4727query_table_alias_no_duplicates @
 4728        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4729        \
 4730        query_table_alias(QueryId, Schema, TableName, TableAlias)
 4731        <=>
 4732        true.
 4733
 4734
 4735check_update_where_restriction @
 4736        phase(QueryId, where),
 4737        restriction_tree(QueryId, where, true),
 4738        state_change_query(QueryId, StateChangeType, _, _)
 4739        ==>
 4740        ( StateChangeType == delete
 4741        ; StateChangeType == update
 4742        )
 4743        |
 4744        no_where_restriction(StateChangeType).
 4745
 4746
 4747no_where_restriction_permitted @
 4748        no_where_restriction(_),
 4749        absence_of_where_restriction_is_deliberate
 4750        <=>
 4751        true.
 4752
 4753
 4754deletes_and_updates_must_have_a_where_restriction @
 4755        no_where_restriction(StateChangeType)
 4756        <=>
 4757        upcase_atom(StateChangeType, StateChangeTypeUc),
 4758        throw(format('~w has no WHERE restriction - check RESTRICTION variables declared', [StateChangeTypeUc])).
 4759
 4760
 4761remove_empty_restriction_tree @
 4762        phase(QueryId, where)
 4763        \
 4764        restriction_tree(QueryId, where, true)
 4765        <=>
 4766        true.
 4767
 4768
 4769where_restriction_clause @
 4770        phase(QueryId, where),
 4771        restriction_tree(QueryId, where, RestrictionTree)
 4772        <=>
 4773        collect_indices(QueryId),
 4774        write_sql(QueryId, compile, where, [' WHERE '|T], T, [], []),
 4775        write_restriction_tree(QueryId, where, RestrictionTree),
 4776        phase(QueryId, group_by).
 4777
 4778sqlite_must_have_where @
 4779        % This is because we add to the WHERE clause programmatically to compute subqueries for update expressions
 4780        query(QueryId, Schema, _)
 4781        \
 4782        phase(QueryId, where)
 4783        <=>
 4784        dbms(Schema, 'SQLite')
 4785        |
 4786        write_sql(QueryId, compile, where, [' WHERE 1=1 '|T], T, [], []),
 4787        phase(QueryId, group_by).
 4788
 4789
 4790no_where_restriction_clause @
 4791        phase(QueryId, where)
 4792        <=>
 4793        phase(QueryId, group_by).
 4794
 4795
 4796having_restriction_clause @
 4797        phase(QueryId, having),
 4798        restriction_tree(QueryId, having, RestrictionTree)
 4799        <=>
 4800        write_sql(QueryId, compile, having, [' HAVING '|T], T, [], []),
 4801        write_restriction_tree(QueryId, having, RestrictionTree),
 4802        phase(QueryId, order_by).
 4803
 4804
 4805no_having_restriction_clause @
 4806        phase(QueryId, having)
 4807        <=>
 4808        phase(QueryId, order_by).
 4809
 4810suggest_indices @
 4811        write_restriction_tree(QueryId, _, RestrictionTree)
 4812        \
 4813        collect_indices(QueryId)
 4814        <=>
 4815        ( debugging(index_suggestions) ->
 4816            cql_suggest_indices(RestrictionTree, QueryId)
 4817
 4818        ; otherwise ->
 4819            true
 4820        ).
 4821
 4822write_restriction_clause @
 4823        write_restriction_tree(QueryId, RestrictionType, RestrictionTree)
 4824        <=>
 4825        ( RestrictionTree = or(Lhs, Rhs) ->
 4826            Operator = 'OR'
 4827
 4828        ; RestrictionTree = and(Lhs, Rhs) ->
 4829            Operator = 'AND'
 4830        )
 4831        |
 4832        write_sql(QueryId, compile, where, ['('|T1], T1, [], []),
 4833        write_restriction_tree(QueryId, RestrictionType, Lhs),
 4834        write_sql(QueryId, compile, where, [' ', Operator, ' '|T2], T2, [], []),
 4835        write_restriction_tree(QueryId, RestrictionType, Rhs),
 4836        write_sql(QueryId, compile, where, [')'|T3], T3, [], []).
 4837
 4838write_restriction_leaf_for_ignore_if_null_on_rhs @
 4839        query_table_alias(_, Schema, _TableName, TableAlias)
 4840        \
 4841        write_restriction_tree(QueryId, RestrictionType, comparison(attribute(Schema, TableAlias, AttributeName), Operator, ignore_if_null(Variable)))
 4842        <=>
 4843        write_restriction(QueryId, if_not_null(Variable), RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, Variable),
 4844        write_sql(QueryId, if_null(Variable), RestrictionType, ['1 = 1'|T], T, [], []).
 4845
 4846
 4847write_restriction_leaf_for_ignore_if_null_on_lhs @
 4848        query_table_alias(_, Schema, _TableName, TableAlias)
 4849        \
 4850        write_restriction_tree(QueryId, RestrictionType, comparison(ignore_if_null(Variable), Operator, attribute(Schema, TableAlias, AttributeName)))
 4851        <=>
 4852        write_restriction(QueryId, if_not_null(Variable), RestrictionType, Variable, Operator, attribute(Schema, TableAlias, AttributeName)),
 4853        write_sql(QueryId, if_null(Variable), RestrictionType, ['1 = 1'|T], T, [], []).
 4854
 4855write_restriction_leaf_for_if_not_var_on_rhs @
 4856        write_restriction_tree(QueryId, RestrictionType, comparison(Attribute, ==, if_not_var(Variable)))
 4857        <=>
 4858        write_restriction(QueryId, if_not_var(Variable), RestrictionType, Attribute, ==, if_not_var(Variable)),
 4859        write_sql(QueryId, if_var(Variable), RestrictionType, ['1 = 1'|T], T, [], []).
 4860
 4861write_restriction_leaf @
 4862        write_restriction_tree(QueryId, RestrictionType, comparison(Lhs, Operator, Rhs))
 4863        <=>
 4864        write_restriction(QueryId, compile, RestrictionType, Lhs, Operator, Rhs).
 4865
 4866
 4867write_restriction_between_attribute_and_ignore_if_null @
 4868        query_table_alias(_, Schema, TableName, TableAlias)
 4869        \
 4870        write_restriction(QueryId,
 4871                          CompileInstruction,
 4872                          RestrictionType,
 4873                          attribute(Schema, TableAlias, AttributeName),
 4874                          Operator,
 4875                          ignore_if_null(ApplicationValue))
 4876        <=>
 4877        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType)
 4878        |
 4879        write_restriction_1(QueryId,
 4880                            CompileInstruction,
 4881                            RestrictionType,
 4882                            OdbcDataType,
 4883                            _,
 4884                            Schema,
 4885                            TableName,
 4886                            AttributeName,
 4887                            attribute(Schema, TableAlias, AttributeName),
 4888                            Operator,
 4889                            ignore_if_null(TableAlias, ApplicationValue)).
 4890
 4891write_restriction_between_attribute_and_if_not_var @
 4892        query_table_alias(_, Schema, TableName, TableAlias)
 4893        \
 4894        write_restriction(QueryId,
 4895                          CompileInstruction,
 4896                          RestrictionType,
 4897                          attribute(Schema, TableAlias, AttributeName),
 4898                          Operator,
 4899                          if_not_var(Variable))
 4900        <=>
 4901        atom(AttributeName),
 4902        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType)
 4903        |
 4904        write_restriction_1(QueryId,
 4905                            CompileInstruction,
 4906                            RestrictionType,
 4907                            OdbcDataType,
 4908                            _,
 4909                            Schema,
 4910                            TableName,
 4911                            AttributeName,
 4912                            attribute(Schema, TableAlias, AttributeName),
 4913                            Operator,
 4914                            if_not_var(Variable)).
 4915
 4916
 4917not_in_list_comparison_with_no_elements @
 4918        write_restriction(QueryId, CompileInstruction, RestrictionType, _, (\==), [])
 4919        <=>
 4920        true
 4921        |
 4922        write_sql(QueryId, CompileInstruction, RestrictionType, ['1 = 1'|T], T, [], []).
 4923
 4924write_list_comparison_with_collation @
 4925        query_table_alias(_, Schema, TableName, TableAlias)
 4926        \
 4927        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, List)
 4928        <=>
 4929        is_list(List),
 4930        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 4931        collatable_odbc_data_type(OdbcDataType),
 4932
 4933        ( Operator == (==) ->
 4934            ListOperator = 'IN'
 4935
 4936        ; Operator == (\==) ->
 4937            ListOperator = 'NOT IN'
 4938        )
 4939        |
 4940        % Duplicate the restriction to avoid the index scan that would result from
 4941        % using the collation comparison alone
 4942        % but only for SQL Server
 4943        ( collation(Schema, Collation)->
 4944           write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T1], T1, [], []),
 4945           write_sql(QueryId, compile, RestrictionType, [' ', Collation, ' ', ListOperator, ' ('|T2], T2, [], []),
 4946           write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, List),
 4947           write_sql(QueryId, compile, RestrictionType, [') AND '|T3], T3, [], [])
 4948        ; otherwise->
 4949           true
 4950        ),
 4951        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T4], T4, [], []),
 4952        write_sql(QueryId, compile, RestrictionType, [' ', ListOperator, ' ('|T5], T5, [], []),
 4953        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, List),
 4954        write_sql(QueryId, compile, RestrictionType, [')'|T6], T6, [], []).
 4955
 4956write_list_comparison_without_collation @
 4957        query_table_alias(_, Schema, TableName, TableAlias)
 4958        \
 4959        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, List)
 4960        <=>
 4961        is_list(List),
 4962        ( Operator == (==) ->
 4963            ListOperator = 'IN'
 4964
 4965        ; Operator == (\==) ->
 4966            ListOperator = 'NOT IN'
 4967        )
 4968        |
 4969        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T1], T1, [], []),
 4970        write_sql(QueryId, compile, RestrictionType, [' ', ListOperator, ' ('|T2], T2, [], []),
 4971        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, List),
 4972        write_sql(QueryId, compile, RestrictionType, [')'|T3], T3, [], []).
 4973
 4974write_runtime_list @
 4975        query_table_alias(_, Schema, TableName, TableAlias)
 4976        \
 4977        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(Schema, TableAlias, AttributeName), Operator, list(List))
 4978        <=>
 4979        SubInstruction = not_empty(List),
 4980        OtherInstruction = empty(List),
 4981
 4982        ( Operator == (==) ->
 4983            ListOperator = 'IN'
 4984
 4985        ; Operator == (\==) ->
 4986            ListOperator = 'NOT IN'
 4987        ),
 4988        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 4989
 4990        ( collatable_odbc_data_type(OdbcDataType), collation(Schema, Collation)->
 4991            true
 4992        ;
 4993            Collation = ''
 4994        ),
 4995        write_sql(QueryId, SubInstruction, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|T1], T1, [], []),
 4996
 4997        write_sql(QueryId,
 4998                  SubInstruction,
 4999                  RestrictionType,
 5000                  [' ', Collation, ' ', ListOperator, ' ('|T2],
 5001                  T2,
 5002                  [],
 5003                  []),
 5004        write_sql(QueryId,
 5005                  list(List),
 5006                  RestrictionType,
 5007                  [?|T3],
 5008                  T3,
 5009                  [odbc_parameter(Schema, TableName, AttributeName, _, where_value, _)],
 5010                  []),
 5011        write_sql(QueryId,
 5012                  SubInstruction,
 5013                  RestrictionType,
 5014                  [')'|T4],
 5015                  T4,
 5016                  [],
 5017                  []),
 5018        write_sql(QueryId,
 5019                  OtherInstruction,
 5020                  RestrictionType,
 5021                  ['1=1'|T5],
 5022                  T5,
 5023                  [],
 5024                  []).
 5025
 5026
 5027
 5028
 5029write_rhs_null_comparison @
 5030        write_restriction(QueryId, _CompileInstruction, RestrictionType, attribute(_, TableAlias, AttributeName), Operator, {null})
 5031        <=>
 5032        null_comparison_keywords(Operator, Keywords, Tail)
 5033        |
 5034        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|Keywords], Tail, [], []).
 5035
 5036
 5037write_lhs_null_comparison @
 5038        write_restriction(QueryId, _CompileInstruction, RestrictionType, {null}, Operator, attribute(_, TableAlias, AttributeName))
 5039        <=>
 5040        null_comparison_keywords(Operator, Keywords, Tail)
 5041        |
 5042        write_sql(QueryId, compile, RestrictionType, [TableAlias, '.', attribute_name(AttributeName)|Keywords], Tail, [], []).
 5043
 5044
 5045null_comparison_keywords(==, [' IS NULL'|T], T).
 5046null_comparison_keywords(\==, [' IS NOT NULL'|T], T).
 5047
 5048
 5049write_restriction_between_attributes @
 5050        query_table_alias(_, _, TableNameLhs, TableAliasLhs)
 5051        \
 5052        write_restriction(QueryId,
 5053                          CompileInstruction,
 5054                          RestrictionType,
 5055                          attribute(_, TableAliasLhs, AttributeNameLhs),
 5056                          Operator,
 5057                          attribute(_, TableAliasRhs, AttributeNameRhs))
 5058        <=>
 5059        odbc_data_type(Schema, TableNameLhs, AttributeNameLhs, OdbcDataType)
 5060        |
 5061        write_restriction_1(QueryId,
 5062                            CompileInstruction,
 5063                            RestrictionType,
 5064                            OdbcDataType,
 5065                            _,
 5066                            Schema,
 5067                            TableNameLhs,
 5068                            AttributeNameLhs,
 5069                            attribute(_, TableAliasLhs, AttributeNameLhs),
 5070                            Operator,
 5071                            attribute(_, TableAliasRhs, AttributeNameRhs)).
 5072
 5073
 5074write_restriction_between_attribute_and_aggregation_sub_select @
 5075        query_table_alias(_, _, TableName, TableAlias)
 5076        \
 5077        write_restriction(QueryId,
 5078                          CompileInstruction,
 5079                          RestrictionType,
 5080                          attribute(Schema, TableAlias, AttributeName),
 5081                          Operator,
 5082                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs))
 5083        <=>
 5084        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5085        |
 5086        Tail = [')'|NewTail],
 5087        write_restriction_1(QueryId,
 5088                            CompileInstruction,
 5089                            RestrictionType,
 5090                            OdbcDataType,
 5091                            _,
 5092                            Schema,
 5093                            TableName,
 5094                            AttributeName,
 5095                            attribute(Schema, TableAlias, AttributeName),
 5096                            Operator,
 5097                            tokens_and_parameters(['('|Sql], NewTail, Inputs)).
 5098
 5099
 5100write_restriction_between_aggregation_sub_select_and_attribute @
 5101        query_table_alias(_, _, TableName, TableAlias)
 5102        \
 5103        write_restriction(QueryId,
 5104                          CompileInstruction,
 5105                          RestrictionType,
 5106                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs),
 5107                          Operator,
 5108                          attribute(Schema, TableAlias, AttributeName))
 5109        <=>
 5110        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5111        |
 5112        Tail = [')'],
 5113        write_restriction_1(QueryId,
 5114                            CompileInstruction,
 5115                            RestrictionType,
 5116                            OdbcDataType,
 5117                            _,
 5118                            Schema,
 5119                            TableName,
 5120                            AttributeName,
 5121                            ['('|Sql]-Inputs,
 5122                            Operator,
 5123                            attribute(Schema, TableAlias, AttributeName)).
 5124
 5125
 5126write_restriction_between_expression_and_aggregation_sub_select @
 5127        write_restriction(QueryId,
 5128                          CompileInstruction,
 5129                          RestrictionType,
 5130                          Expression,
 5131                          Operator,
 5132                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs))
 5133        <=>
 5134        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5135        |
 5136        Tail = [')'|NewTail],
 5137        write_restriction_1(QueryId,
 5138                            CompileInstruction,
 5139                            RestrictionType,
 5140                            OdbcDataType,
 5141                            _,
 5142                            Schema,
 5143                            AggregationTableName,
 5144                            AggregationAttributeName,
 5145                            Expression,
 5146                            Operator,
 5147                            tokens_and_parameters(['('|Sql], NewTail, Inputs)).
 5148
 5149
 5150write_restriction_between_aggregation_sub_select_and_expression @
 5151        write_restriction(QueryId,
 5152                          CompileInstruction,
 5153                          RestrictionType,
 5154                          aggregation_sub_query_sql(AggregationTableName, AggregationAttributeName, Sql, Tail, Inputs),
 5155                          Operator,
 5156                          Expression)
 5157        <=>
 5158        odbc_data_type(Schema, AggregationTableName, AggregationAttributeName, OdbcDataType)
 5159        |
 5160        Tail = [')'|NewTail],
 5161        write_restriction_1(QueryId,
 5162                            CompileInstruction,
 5163                            RestrictionType,
 5164                            OdbcDataType,
 5165                            _,
 5166                            Schema,
 5167                            AggregationTableName,
 5168                            AggregationAttributeName,
 5169                            tokens_and_parameters(['('|Sql], NewTail, Inputs),
 5170                            Operator,
 5171                            Expression).
 5172
 5173
 5174write_restriction_between_expressions @
 5175        write_restriction(QueryId, CompileInstruction, RestrictionType, LhsExpression, Operator, RhsExpression)
 5176        <=>
 5177        RestrictionType \== having   % Could be an aggregation e.g. sum(x) - leave those for the HAVING phase
 5178        |
 5179        ( representative_attribute(LhsExpression+RhsExpression, Schema, TableName, AttributeName) ->
 5180            true
 5181        ; otherwise ->
 5182            throw(format('Cannot find attribute to determine expression data type in ~w or in ~w', [LhsExpression, RhsExpression]))
 5183        ),
 5184        ( odbc_data_type(Schema, TableName, AttributeName, OdbcDataType)->
 5185            true
 5186        ; otherwise->
 5187            throw(format('Could not determine the type of ~w.~w', [TableName, AttributeName]))
 5188        ),
 5189        write_restriction_1(QueryId,
 5190                            CompileInstruction,
 5191                            RestrictionType,
 5192                            OdbcDataType,
 5193                            _,
 5194                            Schema,
 5195                            TableName,
 5196                            AttributeName,
 5197                            LhsExpression,
 5198                            Operator,
 5199                            RhsExpression).
 5200
 5201
 5202representative_attribute_1 @
 5203        % Find an attribute to determine the data type of Expression
 5204        query_table_alias(_, Schema, TableName, TableAlias)
 5205        \
 5206        representative_attribute(attribute(Schema, TableAlias, AttributeName), RepresentativeSchema, RepresentativeTableName, RepresentativeAttributeName)
 5207        <=>
 5208        RepresentativeSchema = Schema,
 5209        RepresentativeTableName = TableName,
 5210        RepresentativeAttributeName = AttributeName.
 5211
 5212representative_attribute_2 @
 5213        representative_attribute(Expression, Schema, TableName, AttributeName)
 5214        <=>
 5215        nonvar(Expression),
 5216        Expression =.. [_|L],
 5217
 5218        ( L = [Lhs, Rhs] ->
 5219            ( representative_attribute(Lhs, Schema, TableName, AttributeName)
 5220            ; representative_attribute(Rhs, Schema, TableName, AttributeName)
 5221            )
 5222        ; L = [Expr] ->
 5223            representative_attribute(Expr, Schema, TableName, AttributeName)
 5224        ).
 5225
 5226
 5227write_having_comparison_between_aggregation_and_null @
 5228        query_table_alias(QueryId, Schema, _, TableAlias),
 5229        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5230        \
 5231        write_restriction(QueryId, CompileInstruction, RestrictionType, Variable, Operator, {null})
 5232        <=>
 5233        not_a_singleton(Variable),
 5234        null_comparison_keywords(Operator, Keywords, Tail),
 5235        functor(Aggregation, Functor, 1),
 5236        aggregation_operator(Functor),
 5237        arg(1, Aggregation, AttributeName)
 5238        |
 5239        write_sql(QueryId, CompileInstruction, RestrictionType, [Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Keywords], Tail, [], []).
 5240
 5241write_having_comparison_between_null_and_aggregation @
 5242        query_table_alias(QueryId, Schema, _, TableAlias),
 5243        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5244        \
 5245        write_restriction(QueryId, CompileInstruction, RestrictionType, {null}, Operator, Variable)
 5246        <=>
 5247        not_a_singleton(Variable),
 5248        null_comparison_keywords(Operator, Keywords, Tail),
 5249        functor(Aggregation, Functor, 1),
 5250        aggregation_operator(Functor),
 5251        arg(1, Aggregation, AttributeName)
 5252        |
 5253        write_sql(QueryId, CompileInstruction, RestrictionType, [Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Keywords], Tail, [], []).
 5254
 5255write_having_comparison_between_aggregation_and_expression @
 5256        query_table_alias(QueryId, Schema, TableName, TableAlias),
 5257        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5258        \
 5259        write_restriction(QueryId, CompileInstruction, RestrictionType, Variable, Operator, Expression)
 5260        <=>
 5261        not_a_singleton(Variable),
 5262        functor(Aggregation, Functor, 1),
 5263        aggregation_operator(Functor),
 5264        arg(1, Aggregation, AttributeName),
 5265        ( nonvar(Expression),
 5266          Expression = ignore_if_null(ApplicationValue) ->
 5267            IgnoreExpression =.. [Functor, TableAlias, AttributeName],
 5268            E = ignore_if_null(IgnoreExpression, ApplicationValue)
 5269
 5270        ; otherwise ->
 5271            E = Expression
 5272        ),
 5273        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 5274
 5275        ( Functor == count ->
 5276            OdbcDataTypeOverride = integer
 5277
 5278        ; otherwise ->
 5279            true
 5280        )
 5281        |
 5282        write_restriction_1(QueryId,
 5283                            CompileInstruction,
 5284                            RestrictionType,
 5285                            OdbcDataType,
 5286                            OdbcDataTypeOverride,
 5287                            Schema,
 5288                            TableName,
 5289                            AttributeName,
 5290                            tokens_and_parameters([Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Tail], Tail, []),
 5291                            Operator,
 5292                            E).
 5293
 5294write_having_comparison_between_expression_and_aggregation @
 5295        query_table_alias(QueryId, Schema, TableName, TableAlias),
 5296        attribute_binding(QueryId, attribute(Schema, TableAlias, Aggregation), Variable)
 5297        \
 5298        write_restriction(QueryId, CompileInstruction, RestrictionType, Expression, Operator, Variable)
 5299        <=>
 5300        functor(Aggregation, Functor, 1),
 5301        aggregation_operator(Functor),
 5302        arg(1, Aggregation, AttributeName),
 5303        ( nonvar(Expression),
 5304          Expression = ignore_if_null(ApplicationValue) ->
 5305            IgnoreExpression =.. [Functor, TableAlias, AttributeName],
 5306            E = ignore_if_null(IgnoreExpression, ApplicationValue)
 5307
 5308        ; otherwise ->
 5309            E = Expression
 5310        ),
 5311        odbc_data_type(Schema, TableName, AttributeName, OdbcDataType),
 5312
 5313        ( Functor == count ->
 5314            OdbcDataTypeOverride = integer
 5315
 5316        ; otherwise ->
 5317            true
 5318        )
 5319        |
 5320        write_restriction_1(QueryId,
 5321                            CompileInstruction,
 5322                            RestrictionType,
 5323                            OdbcDataType,
 5324                            OdbcDataTypeOverride,
 5325                            Schema,
 5326                            TableName,
 5327                            AttributeName,
 5328                            E,
 5329                            Operator,
 5330                            tokens_and_parameters([Functor, '(', TableAlias, '.', attribute_name(AttributeName), ')'|Tail], Tail, [])).
 5331
 5332write_restriction_with_collation @  % Prolog-style atom matching i.e. case-sensitive
 5333        write_restriction_1(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs, ComparisonOperator, Rhs)
 5334        <=>
 5335        ( nonvar(OdbcDataTypeOverride) ->
 5336            collatable_odbc_data_type(OdbcDataTypeOverride)
 5337
 5338        ; collatable_odbc_data_type(OdbcDataType) ->
 5339            true
 5340        ),
 5341
 5342        collatable_operator(ComparisonOperator),
 5343        prolog_to_sql_comparison_operator(Schema, ComparisonOperator, BaseSqlOperator, _)
 5344        |
 5345        % Need to duplicate the restriction to avoid the index scan that would result from
 5346        % using the collation comparison alone
 5347        % But only in SQL Server
 5348        ( RestrictionType == where ->
 5349            instruction_conjunction(CompileInstruction, if_not_null(Variable), C1)
 5350        ; otherwise->
 5351            C1 = CompileInstruction
 5352        ),
 5353        instruction_conjunction(CompileInstruction, if_null(Variable), C2),
 5354        ( collation(Schema, Collation)->
 5355            atomic_list_concat([Collation, ' ', BaseSqlOperator], SqlOperator),
 5356            write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5357            ( ( var(Rhs)->
 5358                  Variable = Rhs
 5359              ; Rhs = if_not_var(Variable)->
 5360                  true
 5361              ; Rhs = equality_restriction(Variable) ->
 5362                  true
 5363              )->
 5364                % Either "= RHS" or " IS NULL" if RHS is {null} at runtime
 5365                write_sql(QueryId, C1, RestrictionType, [' ', SqlOperator, ' '|T1], T1, [], []),
 5366                write_restriction_expression(QueryId, C1, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5367                ( RestrictionType == where ->
 5368                    write_sql(QueryId, C2, RestrictionType, [' IS NULL'|T2], T2, [], [])
 5369                ; otherwise->
 5370                    true
 5371                )
 5372            ; otherwise->
 5373                write_sql(QueryId, CompileInstruction, RestrictionType, [' ', SqlOperator, ' '|T3], T3, [], []),
 5374                write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs)
 5375            ),
 5376            write_sql(QueryId, CompileInstruction, RestrictionType, [' AND '|T4], T4, [], [])
 5377        ; otherwise->
 5378            true
 5379        ),
 5380        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5381        ( ( var(Rhs)->
 5382              Variable = Rhs
 5383           ; Rhs = if_not_var(Variable)->
 5384                  true
 5385          ; Rhs = equality_restriction(Variable) ->
 5386              true
 5387          )->
 5388            % Either "= RHS" or " IS NULL" if RHS is {null} at runtime
 5389            write_sql(QueryId, C1, RestrictionType, [' ', BaseSqlOperator, ' '|T5], T5, [], []),
 5390            write_restriction_expression(QueryId, C1, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5391            ( RestrictionType == where ->
 5392                write_sql(QueryId, C2, RestrictionType, [' IS NULL'|T6], T6, [], [])
 5393            ; otherwise->
 5394                true
 5395            )
 5396        ; otherwise->
 5397            write_sql(QueryId, CompileInstruction, RestrictionType, [' ', BaseSqlOperator, ' '|T7], T7, [], []),
 5398            write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs)
 5399        ).
 5400
 5401
 5402write_restriction_without_collation @
 5403        write_restriction_1(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs, ComparisonOperator, Rhs)
 5404        <=>
 5405        prolog_to_sql_comparison_operator(Schema, ComparisonOperator, SqlOperator, _)
 5406        |
 5407        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5408
 5409        % This sets the RHS to be a 'LikeParameter'. We can undo this later if we discover the RHS is an expression
 5410        ( is_like_operator(ComparisonOperator, LikeOdbcDataType) ->
 5411            OdbcDataTypeOverride = LikeOdbcDataType
 5412        ; otherwise ->
 5413            true
 5414        ),
 5415
 5416        ( ( var(Rhs)->
 5417              Variable = Rhs
 5418           ; Rhs = if_not_var(Variable)->
 5419              true
 5420          ; Rhs = equality_restriction(Variable) ->
 5421              true
 5422          )->
 5423            ( RestrictionType == where ->
 5424                instruction_conjunction(CompileInstruction, if_not_null(Variable), C1)
 5425            ; otherwise->
 5426                C1 = CompileInstruction
 5427            ),
 5428            instruction_conjunction(CompileInstruction, if_null(Variable), C2),
 5429            write_sql(QueryId, C1, RestrictionType, [' ', SqlOperator, ' '|T1], T1, [], []),
 5430            write_restriction_expression(QueryId, C1, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5431            ( RestrictionType == where ->
 5432                write_sql(QueryId, C2, RestrictionType, [' IS NULL'|T2], T2, [], [])
 5433            ; otherwise->
 5434                true
 5435            )
 5436        ; otherwise->
 5437            write_sql(QueryId, CompileInstruction, RestrictionType, [' ', SqlOperator, ' '|T3], T3, [], []),
 5438            write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs)
 5439        ).
 5440
 5441
 5442% prolog_to_sql_comparison_operator(Schema,
 5443%                                   ComparisonOperator,
 5444%                                   SqlOperator,
 5445%                                   InverseComparisonOperator)
 5446
 5447prolog_to_sql_comparison_operator(_, <, <, >=).
 5448prolog_to_sql_comparison_operator(_, =<, <=, >).
 5449prolog_to_sql_comparison_operator(_, ==, =, \==).
 5450prolog_to_sql_comparison_operator(_, =:=, =, =\=).
 5451prolog_to_sql_comparison_operator(Schema, =~, Operator, \=~):-
 5452        (dbms(Schema, 'Microsoft SQL Server')->
 5453           Operator = 'LIKE'
 5454        ; dbms(Schema, 'SQLite')->
 5455           Operator = 'LIKE'
 5456        ; dbms(Schema, 'PostgreSQL')->
 5457           Operator = 'ILIKE'
 5458        ).
 5459prolog_to_sql_comparison_operator(Schema, \=~, Operator, =~):-
 5460        ( dbms(Schema, 'Microsoft SQL Server')->
 5461            Operator = 'NOT LIKE'
 5462        ; dbms(Schema, 'SQLite')->
 5463            Operator = 'NOT LIKE'
 5464        ; dbms(Schema, 'PostgreSQL')->
 5465            Operator = 'NOT ILIKE'
 5466        ).
 5467prolog_to_sql_comparison_operator(_, \==, <>, ==).
 5468prolog_to_sql_comparison_operator(_, =\=, <>, =:=).
 5469prolog_to_sql_comparison_operator(_, >=, >=, <).
 5470prolog_to_sql_comparison_operator(_, >, >, =<).
 5471
 5472
 5473collatable_odbc_data_type(char(_)).
 5474collatable_odbc_data_type(varchar(_)).
 5475collatable_odbc_data_type(longvarchar).
 5476
 5477
 5478collatable_operator(Operator):-
 5479        Operator \== (=~).
 5480
 5481
 5482is_like_operator((=~), varchar(128)).
 5483
 5484
 5485collation(Schema, 'COLLATE Latin1_General_CS_AS'):-  % Make CQL case-sensitive
 5486        dbms(Schema, 'Microsoft SQL Server').
 5487
 5488collation(Schema, _):-
 5489        ( dbms(Schema, 'PostgreSQL')->
 5490            fail
 5491        ; dbms(Schema, 'SQLite')->
 5492            fail
 5493        ).
 5494
 5495write_restriction_expression_1 @
 5496        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, _, _, _, _, tokens_and_parameters(Tokens, Tail, OdbcParameters))
 5497        <=>
 5498        write_sql(QueryId,
 5499                  CompileInstruction,
 5500                  RestrictionType,
 5501                  Tokens,
 5502                  Tail,
 5503                  OdbcParameters,
 5504                  []).
 5505
 5506write_restriction_expression_2 @
 5507        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, _, _, _, _, attribute(_, TableAlias, AttributeName))
 5508        <=>
 5509        write_sql(QueryId,
 5510                  CompileInstruction,
 5511                  RestrictionType,
 5512                  [TableAlias, '.', attribute_name(AttributeName)|T],
 5513                  T,
 5514                  [],
 5515                  []).
 5516
 5517write_restriction_expression_3 @
 5518        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, equality_restriction(Variable))
 5519        <=>
 5520        write_sql(QueryId,
 5521                  CompileInstruction,
 5522                  RestrictionType,
 5523                  [?|T],
 5524                  T,
 5525                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5526                  []).
 5527
 5528% This is a ignore_if_null against an aggregation
 5529write_restriction_expression_4a @
 5530        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, ignore_if_null(Aggregation, Variable))
 5531        <=>
 5532        functor(Aggregation, Functor, 2),
 5533        aggregation_operator(Functor)
 5534        |
 5535        arg(1, Aggregation, TableAlias),
 5536        arg(2, Aggregation, AttributeName),
 5537        write_sql(QueryId,
 5538                  CompileInstruction,
 5539                  RestrictionType,
 5540                  ['COALESCE(?, ', Functor, '(', TableAlias, '.', attribute_name(AttributeName), '))'|T],
 5541                  T,
 5542                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5543                  []).
 5544
 5545write_restriction_expression_4 @
 5546        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, ignore_if_null(TableAlias, Variable))
 5547        <=>
 5548        write_sql(QueryId,
 5549                  CompileInstruction,
 5550                  RestrictionType,
 5551                  ['COALESCE(?, ', TableAlias, '.', attribute_name(AttributeName), ')'|T],
 5552                  T,
 5553                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5554                  []).
 5555
 5556write_restriction_expression_5 @
 5557        attribute_binding(QueryId, Attribute, Variable)
 5558        \
 5559        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Variable)
 5560        <=>
 5561        var(Variable)
 5562        |
 5563        where_restriction_variable(Variable),
 5564        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Attribute).
 5565
 5566write_restriction_expression_6 @
 5567        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _, OdbcDataTypeOverride, Schema, TableName, AttributeName, Variable)
 5568        <=>
 5569        atomic_application_value(Variable)
 5570        |
 5571        write_sql(QueryId,
 5572                  CompileInstruction,
 5573                  RestrictionType,
 5574                  [?|T],
 5575                  T,
 5576                  [odbc_parameter(Schema, TableName, AttributeName, Variable, where_value, OdbcDataTypeOverride)],
 5577                  []).
 5578
 5579write_restriction_expression_7 @
 5580        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Expression)
 5581        <=>
 5582        Expression =.. [Operator, Rhs],
 5583        restriction_prefix_operator(Operator)
 5584        |
 5585        write_sql(QueryId, CompileInstruction,  RestrictionType, [Operator|T], T, [], []),
 5586        % We unset the OdbcDataTypeOverride here
 5587        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs).
 5588
 5589write_restriction_expression_8 @
 5590        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Expression)
 5591        <=>
 5592        Expression =.. [Operator, Lhs, Rhs],
 5593        restriction_expression_operator(Operator)
 5594        |
 5595        ( dbms(Schema, 'SQLite') ->
 5596            write_sql(QueryId, CompileInstruction, RestrictionType, ['(1.0*'|T1], T1, [], [])
 5597        ; otherwise->
 5598            write_sql(QueryId, CompileInstruction, RestrictionType, ['('|T1], T1, [], [])
 5599        ),
 5600        % We unset the OdbcDataTypeOverride here
 5601        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Lhs),
 5602        write_sql(QueryId, CompileInstruction, RestrictionType, [')'|T2], T2, [], []),
 5603        write_sql(QueryId, CompileInstruction, RestrictionType, [Operator|T3], T3, [], []),
 5604        ( dbms(Schema, 'SQLite') ->
 5605            write_sql(QueryId, CompileInstruction, RestrictionType, ['(1.0*'|T4], T4, [], [])
 5606        ; otherwise->
 5607            write_sql(QueryId, CompileInstruction, RestrictionType, ['('|T4], T4, [], [])
 5608        ),
 5609        % We unset the OdbcDataTypeOverride here
 5610        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, Rhs),
 5611        write_sql(QueryId, CompileInstruction, RestrictionType, [')'|T5], T5, [], []).
 5612
 5613write_restriction_expression_9 @
 5614        write_restriction_expression(QueryId, CompileInstruction, RestrictionType, _OdbcDataType, OdbcDataTypeOverride, Schema, TableName, AttributeName, if_not_var(Var))
 5615        <=>
 5616        instruction_conjunction(CompileInstruction, if_not_var(Var), NewInstruction),
 5617        write_sql(QueryId,
 5618                  NewInstruction,
 5619                  RestrictionType,
 5620                  [?|T],
 5621                  T,
 5622                  [odbc_parameter(Schema, TableName, AttributeName, Var, where_value, OdbcDataTypeOverride)],
 5623                  []).
 5624
 5625
 5626write_restriction_expression_10 @
 5627        write_restriction_expression(_, _, _, _, _, _, _, _, Expression)
 5628        <=>
 5629        throw(format('Bad restriction expression: ~w', [Expression])).
 5630
 5631
 5632restriction_prefix_operator(+).
 5633restriction_prefix_operator(-).
 5634
 5635restriction_expression_operator(+).
 5636restriction_expression_operator(-).
 5637restriction_expression_operator(/).
 5638restriction_expression_operator(*).
 5639
 5640
 5641write_sub_query @
 5642        write_restriction_tree(QueryId, RestrictionType, sub_query(SubQueryType, Sql, SqlTail, SubQueryInputs))
 5643        <=>
 5644        ( SubQueryType == exists ->
 5645            Token = 'EXISTS'
 5646
 5647        ; SubQueryType == (\+ exists) ->
 5648            Token = 'NOT EXISTS'
 5649        )
 5650        |
 5651        SqlTail = [')'|T],
 5652        write_sql(QueryId, compile, RestrictionType, [Token,  ' ('|Sql], T, SubQueryInputs, []).
 5653
 5654
 5655write_in_list_1 @
 5656        next_in_list_value_needs_comma(QueryId)
 5657        \
 5658        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, [ApplicationValue|ApplicationValues])
 5659        <=>
 5660        write_sql(QueryId, compile, RestrictionType, [', ?'|T], T, [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, where_value, _)], []),
 5661        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, ApplicationValues).
 5662
 5663
 5664write_in_list_2 @
 5665        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, [ApplicationValue|ApplicationValues])
 5666        <=>
 5667        write_sql(QueryId, compile, RestrictionType, [?|T], T, [odbc_parameter(Schema, TableName, AttributeName, ApplicationValue, where_value, _)], []),
 5668        next_in_list_value_needs_comma(QueryId),
 5669        write_in_list(QueryId, RestrictionType, Schema, TableName, AttributeName, ApplicationValues).
 5670
 5671
 5672write_in_list_3 @
 5673        write_in_list(QueryId, _, _, _, _, []),
 5674        next_in_list_value_needs_comma(QueryId)
 5675        <=>
 5676        true.
 5677
 5678
 5679write_group_by @
 5680        group_by(QueryId, _)
 5681        \
 5682        phase(QueryId, group_by)
 5683        <=>
 5684        write_sql(QueryId, compile, having, [' GROUP BY '|T], T, [], []),
 5685        write_group_bys(QueryId),
 5686        phase(QueryId, having).
 5687
 5688
 5689no_group_by @
 5690        phase(QueryId, group_by)
 5691        <=>
 5692        phase(QueryId, having).
 5693
 5694
 5695write_group_bys @
 5696        write_group_bys(QueryId)
 5697        \
 5698        attribute_for_group_by(QueryId, TableAlias, AttributeName, GroupBy),
 5699        group_by(QueryId, GroupBy)
 5700        <=>
 5701        write_group_by_attribute(QueryId, [TableAlias, '.', AttributeName|T], T).
 5702
 5703
 5704write_group_by_attribute_with_trailing_comma @
 5705        next_group_by_attribute_needs_comma(QueryId)
 5706        \
 5707        write_group_by_attribute(QueryId, SqlTokens, Tail)
 5708        <=>
 5709        write_sql(QueryId, compile, having, [', '|SqlTokens], Tail, [], []).
 5710
 5711
 5712write_group_by_attribute_without_trailing_comma @
 5713        write_group_by_attribute(QueryId, SqlTokens, Tail)
 5714        <=>
 5715        write_sql(QueryId, compile, having, SqlTokens, Tail, [], []),
 5716        next_group_by_attribute_needs_comma(QueryId).
 5717
 5718
 5719should_not_be_any_group_by_constraints_left_over @
 5720        check_for_orphan_group_bys,
 5721        group_by(_, GroupBy),
 5722        original_cql(Cql)
 5723        <=>
 5724        throw(format('Unused GROUP BY ~w in CQL: ~w', [GroupBy, Cql])).
 5725
 5726
 5727write_order_by @
 5728        order_bys(QueryId, OrderBys)
 5729        \
 5730        phase(QueryId, order_by)
 5731        <=>
 5732        OrderBys \== []
 5733        |
 5734        write_sql(QueryId, compile, having, [' ORDER BY '|T], T, [], []),
 5735        write_order_bys(QueryId, OrderBys),
 5736        phase(QueryId, union).
 5737
 5738
 5739no_order_by @
 5740        phase(QueryId, order_by)
 5741        <=>
 5742        phase(QueryId, union).
 5743
 5744
 5745write_order_bys @
 5746        write_order_bys(QueryId, [OrderBy|OrderBys])
 5747        <=>
 5748        write_order_by(QueryId, OrderBy),
 5749        write_order_bys(QueryId, OrderBys).
 5750
 5751
 5752clean_up_write_order_bys @
 5753        write_order_bys(_, [])
 5754        <=>
 5755        true.
 5756
 5757
 5758write_order_by_aggregate @
 5759        write_order_by(QueryId, OrderBy),
 5760        attribute_for_order_by(QueryId, TableAlias, AggregationTerm, Variable)
 5761        <=>
 5762        AggregationTerm =.. [AggregationOperator, AttributeName],
 5763        aggregation_operator(AggregationOperator),
 5764
 5765        ( OrderBy == +(Variable) ->
 5766            Direction = 'ASC'
 5767
 5768        ; OrderBy == -(Variable) ->
 5769            Direction = 'DESC'
 5770        ),
 5771        %map_database_atom(AggregationOperator, AggregationOperatorUc)
 5772        upcase_atom(AggregationOperator, AggregationOperatorUc)
 5773        |
 5774        not_a_singleton(Variable),
 5775        write_order_by_attribute(QueryId, [AggregationOperatorUc, '(', TableAlias, '.', attribute_name(AttributeName), ') ', Direction|T], T).
 5776
 5777
 5778write_order_by @
 5779        write_order_by(QueryId, OrderBy),
 5780        attribute_for_order_by(QueryId, TableAlias, AttributeName, Variable)
 5781        <=>
 5782        ( OrderBy == +(Variable) ->
 5783            Direction = 'ASC'
 5784
 5785        ; OrderBy == -(Variable) ->
 5786            Direction = 'DESC'
 5787        )
 5788        |
 5789        not_a_singleton(Variable),
 5790        write_order_by_attribute(QueryId, [TableAlias, '.', attribute_name(AttributeName), ' ', Direction|T], T).
 5791
 5792
 5793
 5794write_order_by_attribute_with_trailing_comma @
 5795        next_order_by_attribute_needs_comma(QueryId)
 5796        \
 5797        write_order_by_attribute(QueryId, SqlTokens, Tail)
 5798        <=>
 5799        write_sql(QueryId, compile, having, [', '|SqlTokens], Tail, [], []).
 5800
 5801
 5802write_order_by_attribute_without_trailing_comma @
 5803        write_order_by_attribute(QueryId, SqlTokens, Tail)
 5804        <=>
 5805        write_sql(QueryId, compile, having, SqlTokens, Tail, [], []),
 5806        next_order_by_attribute_needs_comma(QueryId).
 5807
 5808
 5809should_not_be_any_write_order_by_constraints_left_over @
 5810        check_for_orphan_order_bys,
 5811        write_order_by(_, OrderBy),
 5812        original_cql(Cql)
 5813        <=>
 5814        throw(format('Unused ORDER BY ~w in CQL: ~w', [OrderBy, Cql])).
 5815
 5816
 5817in_line_formats @
 5818        prior_to_execution \ in_line_format(_, Format, FormatArgs, ApplicationValue) <=>  format(atom(ApplicationValue), Format, FormatArgs).
 5819        prior_to_execution <=> true.
 5820
 5821
 5822postgres_identity @
 5823        number_of_rows_affected(QueryId, _, _)
 5824        \
 5825        postgres_identity(QueryId, ReturnedIdentity),
 5826        cql_identity(QueryId, _, Identity)
 5827        <=>
 5828        Identity = ReturnedIdentity.
 5829
 5830ignored_postgres_identity @
 5831        number_of_rows_affected(QueryId, _, _)
 5832        \
 5833        postgres_identity(QueryId, _)
 5834        <=>
 5835        true.
 5836
 5837identity_sql_server @
 5838        number_of_rows_affected(QueryId, Connection, _),
 5839        cql_statement_location(FileName, LineNumber)
 5840        \
 5841        cql_identity(QueryId, Schema, Identity)
 5842        <=>
 5843        dbms(Schema, 'Microsoft SQL Server'),
 5844        odbc_query(Connection, 'SELECT CAST(@@IDENTITY AS INTEGER)', row(ScopeIdentity))
 5845        |
 5846        ( integer(ScopeIdentity) ->
 5847            Identity = ScopeIdentity,
 5848            get_transaction_context(TransactionId, _, AccessToken, _, _),
 5849            cql_access_token_to_user_id(AccessToken, UserId),
 5850            cql_log([], informational, 'CQL\t~w\t~w\t   Inserted row has identity ~w\t(~w:~w)', [UserId, TransactionId, Identity, FileName, LineNumber])
 5851        ; otherwise ->
 5852            cql_error(bad_identity, 'Integer identity value expected but got ~q', [ScopeIdentity])
 5853        ).
 5854
 5855identity_sqlite @
 5856        number_of_rows_affected(QueryId, Connection, _),
 5857        cql_statement_location(FileName, LineNumber)
 5858        \
 5859        cql_identity(QueryId, Schema, Identity)
 5860        <=>
 5861        dbms(Schema, 'SQLite'),
 5862        odbc_query(Connection, 'SELECT CAST(last_insert_rowid() AS INTEGER)', row(ScopeIdentity))
 5863        |
 5864        ( integer(ScopeIdentity) ->
 5865            Identity = ScopeIdentity,
 5866            get_transaction_context(TransactionId, _, AccessToken, _, _),
 5867            cql_access_token_to_user_id(AccessToken, UserId),
 5868            cql_log([], informational, 'CQL\t~w\t~w\t   Inserted row has identity ~w\t(~w:~w)', [UserId, TransactionId, Identity, FileName, LineNumber])
 5869        ; otherwise ->
 5870            cql_error(bad_identity, 'Integer identity value expected but got ~q', [ScopeIdentity])
 5871        ).
 5872
 5873rows_affected @
 5874        number_of_rows_affected(QueryId, _, N)
 5875        \
 5876        row_count(QueryId, RowCount)
 5877        <=>
 5878        RowCount = N.
 5879
 5880post_state_change_statistics @
 5881        number_of_rows_affected(QueryId, _, N)
 5882        \
 5883        cql_state_change_statistics_sql(QueryId, Schema, TableName, update, _, StateChangeAttributeNames, OdbcParameters, _)
 5884        <=>
 5885        process_statistics_post_state_changes(Schema, TableName, StateChangeAttributeNames, OdbcParameters, N).
 5886
 5887number_of_rows_affected_cleanup @
 5888        number_of_rows_affected(QueryId, _, _),  cql_state_change_statistics_sql(QueryId, _, _, _, _, _, _, _) <=> true.
 5889        number_of_rows_affected(_, _, _) <=> true.
 5890
 5891
 5892write_sql @
 5893        sql_statement(QueryId, TokensSoFar, TokensTail, FromTokensSoFar, FromTail, RestrictionTokensSoFar, RestrictionTail, ExistingOdbcParameters, ExistingFromParameters, ExistingOutputs),
 5894        write_sql(QueryId, CompileInstruction, Position, AddTokens, AddTail, OdbcParameters, Outputs)
 5895        <=>
 5896        ( CompileInstruction == compile->
 5897            Addition = AddTokens,
 5898            AdditionTail = AddTail,
 5899            ( Position \== top->
 5900                append(ExistingFromParameters, OdbcParameters, NewFromParameters),
 5901                NewOdbcParameters = ExistingOdbcParameters
 5902            ; otherwise->
 5903                ( var(OdbcParameters)->
 5904                    append(ExistingOdbcParameters, [compile:OdbcParameters], NewOdbcParameters)
 5905                ; otherwise->
 5906                    append(ExistingOdbcParameters, OdbcParameters, NewOdbcParameters)
 5907                ),
 5908                NewFromParameters = ExistingFromParameters
 5909            )
 5910        ; otherwise->
 5911            % FIXME: This is where to keep the tail around so we can flatten quickly at runtime
 5912            Addition = [CompileInstruction:AddTokens|AdditionTail],
 5913            AddTail = [],
 5914            ( OdbcParameters == []->
 5915                NewOdbcParameters = ExistingOdbcParameters,
 5916                NewFromParameters = ExistingFromParameters
 5917            ; otherwise->
 5918                ( Position \== top->
 5919                    append(ExistingFromParameters, [CompileInstruction:OdbcParameters], NewFromParameters),
 5920                    NewOdbcParameters = ExistingOdbcParameters
 5921                ; otherwise->
 5922                    append(ExistingOdbcParameters, [CompileInstruction:OdbcParameters], NewOdbcParameters),
 5923                    NewFromParameters = ExistingFromParameters
 5924                )
 5925            )
 5926        ),
 5927        ( Position == join ->
 5928            FromTail = Addition,
 5929            NewFromTail = AdditionTail,
 5930            NewTokensTail = TokensTail,
 5931            NewRestrictionTail = RestrictionTail
 5932        ; Position == top ->
 5933            TokensTail = Addition,
 5934            NewTokensTail = AdditionTail,
 5935            NewFromTail = FromTail,
 5936            NewRestrictionTail = RestrictionTail
 5937        ; otherwise->
 5938            NewRestrictionTail = AdditionTail,
 5939            RestrictionTail = Addition,
 5940            NewTokensTail = TokensTail,
 5941            NewFromTail = FromTail
 5942        ),
 5943        ( CompileInstruction == compile ->
 5944            append(ExistingOutputs, Outputs, NewOutputs)
 5945        ; Outputs == []->
 5946            NewOutputs = ExistingOutputs
 5947        ; otherwise->
 5948            append(ExistingOutputs, [CompileInstruction:Outputs], NewOutputs)
 5949        ),
 5950        sql_statement(QueryId, TokensSoFar, NewTokensTail, FromTokensSoFar, NewFromTail, RestrictionTokensSoFar, NewRestrictionTail, NewOdbcParameters, NewFromParameters, NewOutputs).
 5951
 5952
 5953instantiate_aggregation_sub_query @
 5954        query(SubQueryId, _, sub_query)
 5955        \
 5956        phase(SubQueryId, union),
 5957        sql_statement(SubQueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, OdbcParameters, FromParameters, [output(_, TableName, AttributeName, _)]),
 5958        aggregation_sub_query(SubQueryId, AggregationTableName, AggregationAttributeName, SubQuerySqlTokens, SubQueryTail, SubQueryOdbcParameters)
 5959        <=>
 5960        AggregationTableName = TableName,
 5961        AggregationAttributeName = AttributeName,
 5962        TokensTail = SqlFromTokens,
 5963        FromTail = SqlRestrictionTokens,
 5964        SubQueryTail = RestrictionTail,
 5965        SubQuerySqlTokens = SqlTokens,
 5966        append(OdbcParameters, FromParameters, SubQueryOdbcParameters).
 5967
 5968instantiate_sub_query @
 5969        query(QueryId, _, sub_query)
 5970        \
 5971        phase(QueryId, union),
 5972        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, OdbcParameters, FromParameters, _),
 5973        sub_query(QueryId, SubQuerySqlTokens, SubQuerySqlTail, SubQueryOdbcParameters)
 5974        <=>
 5975        TokensTail = SqlFromTokens,
 5976        FromTail = SqlRestrictionTokens,
 5977        SubQuerySqlTail = RestrictionTail,
 5978        SubQuerySqlTokens = SqlTokens,
 5979        append(OdbcParameters, FromParameters, SubQueryOdbcParameters).
 5980
 5981orphan_write_restriction @
 5982        check_query,
 5983        write_restriction(_, _, _, ApplicationValueLhs, Operator, ApplicationValueRhs),
 5984        original_cql(Cql)
 5985        <=>
 5986        throw(format('Unused restriction: ~w ~w ~w in CQL: ~w', [ApplicationValueLhs, Operator, ApplicationValueRhs, Cql])).
 5987
 5988% Ignore any join_on which comes for free in PostgreSQL
 5989% (if we do an UPDATE ... FROM) we get a free join to the target
 5990% NB: We can only get implicit_join/2 if dbms is PostgreSQL
 5991ignore_implicit_joins @
 5992        implicit_join(QueryId, Ignore, SubQueryId)
 5993        \
 5994        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB)
 5995        <=>
 5996        ( Ignore == TableAliasA ; Ignore == TableAliasB)
 5997        |
 5998        add_on(SubQueryId, TableAliasA-AttributeNameA==TableAliasB-AttributeNameB),
 5999        implicit_join_link(QueryId, SubQueryId).
 6000
 6001remove_duplicate_implicit_join_links @
 6002        implicit_join_link(QueryId, SubQueryId)
 6003        \
 6004        implicit_join_link(QueryId, SubQueryId)
 6005        <=>
 6006        true.
 6007
 6008recover_implicit_join_for_update @
 6009        implicit_join_sql(QueryId, Sql, Tail),
 6010        implicit_join_link(QueryId, SubQueryId),
 6011        on(SubQueryId, _, On)
 6012        <=>
 6013        sql_statement(SubQueryId, A, A, B, B, C, C, [], [], []),
 6014        write_join_ons(SubQueryId, On),
 6015        fetch_implicit_join_sql(SubQueryId, Sql, Tail).
 6016
 6017fetch_implicit_join_sql @
 6018        fetch_implicit_join_sql(SubQueryId, Sql, Tail),
 6019        sql_statement(SubQueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, _, _, _)
 6020        <=>
 6021        TokensTail = SqlFromTokens,
 6022        FromTail = SqlRestrictionTokens,
 6023        Tail = RestrictionTail,
 6024        Sql = SqlTokens.
 6025
 6026check_for_joins @
 6027        check_query,
 6028        join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB),
 6029        original_cql(Cql)
 6030        <=>
 6031        throw(format('Unused JOIN point ~w (check join operator present) in CQL: ~w',
 6032                     [join_on(TableAliasA, AttributeNameA, TableAliasB, AttributeNameB), Cql])).
 6033
 6034
 6035check_for_unused_select_bindings @
 6036        % Select bindings should have either been translated into select attributes (i.e. will appear in
 6037        % the SELECT clause) or been explicitly discarded.  Any left behind indicate a problem.
 6038        check_query,
 6039        select_binding(_, X, Attribute, _),
 6040        original_cql(Cql)
 6041        <=>
 6042        throw(format('Unused SELECT binding (missing GROUP BY?): ~n~w ~n~n~w~n~nin CQL: ~w', [X, Attribute, Cql])).
 6043
 6044
 6045check_for_unused_join_on_comparisons @
 6046        check_query,
 6047        write_join_ons(_, On),
 6048        original_cql(Cql)
 6049        <=>
 6050        throw(format('Unused join ON comparison <~w> in CQL: ~w', [On, Cql])).
 6051
 6052
 6053check_for_unused_comparisons @
 6054        check_query,
 6055        comparison(_, Lhs, ComparisonOperator, Rhs),
 6056        original_cql(Cql)
 6057        <=>
 6058        throw(format('Unused comparison: ~w ~w ~w in CQL: ~w', [Lhs, ComparisonOperator, Rhs, Cql])).
 6059
 6060
 6061cleanup_check_query @
 6062        check_query
 6063        <=>
 6064        true.
 6065
 6066
 6067odbc_state_change_statement_update @
 6068        prepare_odbc_statements,
 6069        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6070        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6071        <=>
 6072        dbms(Schema, 'Microsoft SQL Server'),
 6073        StateChangeType == update
 6074        |
 6075        TokensTail = SqlFromTokens,
 6076        FromTail = SqlRestrictionTokens,
 6077        RestrictionTail = [],
 6078        AllSqlTokens = SqlTokens,
 6079        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6080        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, SqlFromTokens, TableName, OdbcParameters),
 6081        create_cql_state_change_statistics_sql(QueryId, StateChangeType, SqlFromTokens, TableName, OdbcParameters),
 6082        compile_tokens(AllSqlTokens, Sql),
 6083        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6084
 6085
 6086duplicate_from_clause @
 6087        copy_of_from(QueryId, Tokens, Tail, Parameters)
 6088        \
 6089        find_copy_of_from(QueryId, NewTokens, NewTail, NewParameters)
 6090        <=>
 6091        Parameters = NewParameters,
 6092        swap_tail(Tokens, Tail, NewTail, NewTokens).
 6093
 6094remove_cycles([], []):- !.
 6095remove_cycles([compile:_|As], [Bs]):- !, remove_cycles(As, Bs).
 6096remove_cycles([A|As], [A|Bs]):- !, remove_cycles(As, Bs).
 6097remove_cycles([_|As], Bs):- remove_cycles(As, Bs).
 6098
 6099odbc_state_change_statement_update_sqlite_1 @
 6100        update_table_alias(QueryId, _, _, TargetAlias)
 6101        \
 6102        prepare_odbc_statements,
 6103        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6104        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6105        <=>
 6106        dbms(Schema, 'SQLite'),
 6107        StateChangeType == update
 6108        |
 6109        TokensTail = [' WHERE rowid IN (SELECT ', TargetAlias, '.rowid '|SqlFromTokens],
 6110        FromTail = SqlRestrictionTokens,
 6111        AllSqlTokens = SqlTokens,
 6112        % Yikes. Initially I used copy_term here but the variables need to be shared (except the tail, of course)
 6113        swap_tail(SqlFromTokens, RestrictionTail, [], StateChangeSelectTokens),
 6114        swap_tail(SqlFromTokens, RestrictionTail, CopyFromTail, CopyFrom),
 6115        copy_of_from(QueryId, CopyFrom, CopyFromTail, FromParameters),
 6116        RestrictionTail = [')'],
 6117        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6118        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, StateChangeSelectTokens, TableName, OdbcParameters),
 6119        create_cql_state_change_statistics_sql(QueryId, StateChangeType, StateChangeSelectTokens, TableName, OdbcParameters),
 6120        compile_tokens(AllSqlTokens, Sql),
 6121        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6122
 6123
 6124swap_tail(Var, RestrictionTail, Tail, Tail):-
 6125        RestrictionTail == Var, !.
 6126
 6127swap_tail([A|As], Tail, X, [A|Bs]):-
 6128        swap_tail(As, Tail, X, Bs).
 6129
 6130odbc_state_change_statement_update @
 6131        query_table_alias(QueryId, _, _, TableAlias),
 6132        update_table_alias(QueryId, _, _, TargetAlias)
 6133        \
 6134        prepare_odbc_statements,
 6135        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6136        state_change_query(QueryId, StateChangeType, Schema, UpdateTableName)
 6137        <=>
 6138        dbms(Schema, 'PostgreSQL'),
 6139        implicit_join_sql(QueryId, ImplicitJoinSQL, [')'|ImplicitJoinTail]),
 6140        % If there is any query_table_alias which is NOT the same as the update_table_alias then the
 6141        % SqlRestrictionTokens will already contain a FROM
 6142        TableAlias \== TargetAlias,
 6143        StateChangeType == update
 6144        |
 6145        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6146        % 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!
 6147        RestrictionTail = [],
 6148        ImplicitJoinTail = SqlRestrictionTokens,
 6149        copy_term([' FROM '|SqlFromTokens]:FromTail, SqlPreFromTokens:[' INNER JOIN ', UpdateTableName, ' ', TargetAlias, ' ON ('|ImplicitJoinSQL]),
 6150        TokensTail = SqlFromTokens,
 6151        FromTail = SqlRestrictionTokens,
 6152        AllSqlTokens = SqlTokens,
 6153
 6154        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, SqlPreFromTokens, UpdateTableName, OdbcParameters),
 6155        create_cql_state_change_statistics_sql(QueryId, StateChangeType, SqlPreFromTokens, UpdateTableName, OdbcParameters),
 6156        % Inject the implicit join here
 6157        % ImplicitJoin = [' INNER JOIN ', TableName, ' ', TableAlias, ' ON ', '(1=1)'],
 6158        compile_tokens(AllSqlTokens, Sql),
 6159        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, UpdateTableName, Sql, OdbcParameters, Outputs).
 6160
 6161
 6162odbc_state_change_statement_update @
 6163        query_table_alias(QueryId, _, TableName, TableAlias)
 6164        \
 6165        prepare_odbc_statements,
 6166        sql_statement(QueryId, SqlTokens, TokensTail, SqlFromTokens, FromTail, SqlRestrictionTokens, RestrictionTail, SelectOdbcParameters, FromParameters, Outputs),
 6167        state_change_query(QueryId, StateChangeType, Schema, TableName)
 6168        <=>
 6169        dbms(Schema, 'PostgreSQL'),
 6170        StateChangeType == update
 6171        |
 6172        append(SelectOdbcParameters, FromParameters, OdbcParameters),
 6173        % If there is no query_table_alias which is NOT the same as the update_table_alias then the
 6174        % SqlRestrictionTokens will NOT contain a FROM, so we must add one
 6175        TokensTail = SqlFromTokens,
 6176        FromTail = SqlRestrictionTokens,
 6177        RestrictionTail = [],
 6178        AllSqlTokens = SqlTokens,
 6179        % This case is much simpler than the above
 6180        create_cql_pre_state_change_select_sql(QueryId, StateChangeType, [' FROM ', TableName, ' ', TableAlias, ' '|SqlRestrictionTokens], TableName, OdbcParameters),
 6181        create_cql_state_change_statistics_sql(QueryId, StateChangeType, [' FROM ', TableName, ' ', TableAlias, ' '|SqlRestrictionTokens], TableName, OdbcParameters),
 6182        compile_tokens(AllSqlTokens, Sql),
 6183        cql_odbc_state_change_statement(QueryId, StateChangeType, Schema, TableName, Sql, OdbcParameters, Outputs).
 6184
 6185
 6186odbc_state_change_statement_not_update @
 6187        prepare_odbc_statements,
 6188        sql_statement(QueryId,