1/*  Part of SWI-Prolog
    2
    3    Author:        Matt Lilley
    4    E-mail:        matt.s.lilley@gmail.com
    5    WWW:           http://www.swi-prolog.org
    6    Copyright (c)  2014, Mike Elston, Matt Lilley
    7    All rights reserved.
    8
    9    Redistribution and use in source and binary forms, with or without
   10    modification, are permitted provided that the following conditions
   11    are met:
   12
   13    1. Redistributions of source code must retain the above copyright
   14       notice, this list of conditions and the following disclaimer.
   15
   16    2. Redistributions in binary form must reproduce the above copyright
   17       notice, this list of conditions and the following disclaimer in
   18       the documentation and/or other materials provided with the
   19       distribution.
   20
   21    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   22    "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   23    LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
   24    FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
   25    COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
   26    INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
   27    BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
   28    LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
   29    CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
   30    LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
   31    ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
   32    POSSIBILITY OF SUCH DAMAGE.
   33*/
   34
   35/*  PostgreSQL is a trademark of the PostgreSQL Global Development Group.
   36    Microsoft, SQL Server, and Windows are either registered trademarks or
   37    trademarks of Microsoft Corporation in the United States and/or other
   38    countries. SQLite is a registered trademark of Hipp, Wyrick & Company,
   39    Inc in the United States. All other trademarks or registered trademarks
   40    are the property of their respective owners.
   41*/
   42
   43:-module(sql_write, [sql_write/3,
   44                     sql_quote_codes/3,
   45                     format_sql_error/3]).   46
   47:-use_module(library(cql/sql_keywords)).   48:-use_module(library(cql/sql_parser), [strip_sql_comments/2]).   49:-use_module(library(cql/cql), [cql_normalize_name/3]).   50:-use_module(library(option), [option/2]).   51
   52sql_write(Stream, Term, Options):-
   53        new_sql_stream(Output),
   54        sql_write_term(Term, '', Options, Output, Result),
   55        dump_sql_stream(Result, Stream).
   56
   57new_sql_stream(sql_stream(T, T, unknown, 0)).
   58dump_sql_stream(sql_stream(Tokens, [], _, _), Stream):-
   59        atomic_list_concat(Tokens, '', Atom),
   60        format(Stream, '~w', [Atom]).
   61
   62sql_emit_token(Format, Args, Class, Options, sql_stream(Tokens, Tail, OldClass, Indent), sql_stream(Tokens, NewTail, Class, NewIndent)):-
   63        option(errors(html), Options),
   64        !,
   65        format(atom(T2), Format, Args),
   66        ( fail, Class == OldClass ->
   67            Tail = [T2|NewTail]
   68        ; otherwise->
   69            format(atom(T1), '<span class="~w">', [Class]),
   70            format(atom(T3), '</span>', []),
   71            Tail = [T1, T2, T3|NewTail]
   72        ),
   73        atomic_list_concat(Lines, '\n', T2),
   74        ( Lines = [SingleLine]->
   75            atom_length(SingleLine, Length),
   76            NewIndent is Indent + Length
   77        ; otherwise->
   78            append(_, [LastLine], Lines),
   79            atom_length(LastLine, NewIndent)
   80        ).
   81
   82sql_emit_token(Format, Args, _Class, _Options, sql_stream(Tokens, [Token|NewTail], Class, Indent), sql_stream(Tokens, NewTail, Class, NewIndent)):-
   83        format(atom(Token), Format, Args),
   84        atomic_list_concat(Lines, '\n', Token),
   85        ( Lines = [SingleLine]->
   86            atom_length(SingleLine, Length),
   87            NewIndent is Indent + Length
   88        ; otherwise->
   89            once(append(_, [LastLine], Lines)), % Apparently this is nondet!
   90            atom_length(LastLine, NewIndent)
   91        ).
   92
   93sql_append_raw_token(Token, sql_stream(Tokens, [Token|NewTail], Class,Indent), sql_stream(Tokens, NewTail, Class, Indent)).
   94
   95tab_stop(Stop, sql_stream(Tokens, Tail, Class, Indent), sql_stream(Tokens, Tail, Class, Indent)):-
   96        findall(32, between(1, Indent, _), Spaces),
   97        atom_codes(Stop, Spaces).
   98
   99
  100sql_write_term(Var, _, _)--> {var(Var), !, throw(var)}.
  101sql_write_term(Comments:Term, Indent, Options)--> !,
  102        sql_write_comments(Comments, Indent, Options),
  103        sql_write_term(Term, Indent, Options),
  104        sql_end_comment(Comments, Indent, Options).
  105
  106sql_write_term(table_definition(Name, Columns), Indent, Options)--> !,
  107        sql_emit_token('CREATE TABLE ', [], keyword, Options),
  108        !,
  109        sql_write_term(Name, Indent, Options),
  110        ( {Columns == {all}} ->
  111            {true}
  112        ; {otherwise}->
  113            sql_emit_token('(', [], punctuation, Options),
  114            sql_write_list_with_newlines(Columns, Indent, Options),
  115            sql_emit_token(')', [], punctuation, Options)
  116        ).
  117
  118sql_write_term(domain_definition(Name, Type), Indent, Options)--> !,
  119        sql_emit_token('CREATE DOMAIN ', [], keyword, Options),
  120        !,
  121        sql_write_term(Name, Indent, Options),
  122        sql_emit_token(' AS ', [], keyword, Options),
  123        sql_write_type(Type, Indent, Options).
  124
  125
  126sql_write_term(view_definition(Name, Columns, Expression, With), Indent, Options)--> !,
  127        sql_emit_token('CREATE VIEW ', [], keyword, Options),
  128        !,
  129        sql_write_term(Name, Indent, Options),
  130        sql_write_term(With, Indent, Options),
  131        ( {Columns == {all}} ->
  132            {true}
  133        ; {otherwise}->
  134            sql_emit_token('(', [], punctuation, Options),
  135            sql_write_term(Columns, Indent, Options),
  136            sql_emit_token(')', [], punctuation, Options)
  137        ),
  138        sql_emit_token(' AS~n', [], keyword, Options),
  139        sql_write_term(Expression, Indent, Options).
  140
  141sql_write_term(parameter(I), _Indent, Options)--> !,
  142        ( {option(parameter_bindings(Bindings), Options)}->
  143            {nth0(I, Bindings, Value)},
  144            ( {Value = parameter(Name)}->
  145                sql_emit_token('~w', [Name], parameter, Options)
  146            ; {otherwise}->
  147                sql_emit_token('~C', [Value], parameter, Options)
  148            )
  149        ; {otherwise}->
  150            sql_emit_token('?', [], punctuation, Options)
  151        ).
  152sql_write_term(table(Name), Indent, Options)--> !,
  153        ( {option(errors(html), Options),
  154           strip_sql_comments(Name, identifier(_,RawName))}->
  155            {format(atom(Token), '<a href="/sql_explorer/~w">', [RawName])},
  156            sql_append_raw_token(Token),
  157            sql_write_term(Name, Indent, Options),
  158            sql_append_raw_token('</a>')
  159        ; {otherwise}->
  160            sql_write_term(Name, Indent, Options)
  161        ).
  162
  163sql_write_term(domain(Name), Indent, Options)--> !,
  164        sql_write_term(Name, Indent, Options).
  165
  166sql_write_term(derived_table(Derivation, Correlation, _Type), Indent, Options)--> !,
  167        sql_write_term(Derivation, Indent, Options),
  168        sql_emit_token(' AS ', [], keyword, Options),
  169        sql_write_term(Correlation, Indent, Options).
  170
  171
  172sql_write_term(identifier(Schema, Name), Indent, Options)--> !,
  173        ( {Schema == {no_schema}}->
  174            {true}
  175        ; {option(dbms('PostgreSQL'), Options)}->
  176            % No schema for 'PostgreSQL'
  177            {true}
  178        ; {otherwise}->
  179            sql_write_term(Schema, Indent, Options),
  180            sql_emit_token('.', [], punctuation, Options)
  181        ),
  182        ( {option(dbms('PostgreSQL'), Options)}->
  183            {strip_sql_comments(Name, NameNoComments),
  184             cql_normalize_name('PostgreSQL', NameNoComments, Normalized)},
  185            sql_write_term(Normalized, Indent, Options)
  186        ; {otherwise}->
  187            sql_write_term(Name, Indent, Options)
  188        ).
  189
  190sql_write_term(schema(Catalog, Name), Indent, Options)--> !,
  191        ( {Catalog == {no_catalog}}->
  192            {true}
  193        ; {option(dbms('PostgreSQL'), Options)}->
  194            % No catalog for 'PostgreSQL' either
  195            {true}
  196        ; {otherwise}->
  197            sql_write_term(Catalog, Indent, Options),
  198            sql_emit_token('.', [], punctuation, Options)
  199        ),
  200        sql_write_term(Name, Indent, Options).
  201
  202sql_write_term(literal(Value, decimal(_,_)), _Indent, Options)--> !,
  203        sql_emit_token('~w', [Value], literal, Options).
  204sql_write_term(literal(Value, string), _Indent, Options)--> !,
  205        sql_emit_token('\'', [], literal, Options),
  206        sql_write_literal(Value, Options),
  207        sql_emit_token('\'', [], literal, Options).
  208sql_write_term(literal(Value, identifier), _Indent, Options)--> !,
  209        ( {option(dbms('PostgreSQL'), Options)},
  210          sql_emit_token('"', [], literal, Options),
  211          sql_write_literal(Value, Options),
  212          sql_emit_token('"', [], literal, Options)
  213        ; {otherwise}->
  214            sql_emit_token('[~q]', [Value], unknown, Options)
  215        ).
  216sql_write_term(literal(Value, int(_)), _Indent, Options)--> !,
  217        sql_emit_token('~q', [Value], literal, Options).
  218
  219sql_write_term(set_function(Functor, Quantifier, Arg), Indent, Options)--> !,
  220        ( {Functor = Comments:RealFunctor}->
  221            sql_write_comments(Comments, Indent, Options),
  222            {upcase_atom(RealFunctor, FunctorUC)},
  223            sql_write_term(FunctorUC, Indent, Options),
  224            sql_end_comment(Comments, Indent, Options)
  225        ; {otherwise}->
  226            {upcase_atom(Functor, FunctorUC)},
  227            sql_write_term(FunctorUC, Indent, Options)
  228        ),
  229        sql_emit_token('(', [], punctuation, Options),
  230        sql_write_term(Quantifier, Indent, Options),
  231        sql_write_term(Arg, Indent, Options),
  232        sql_emit_token(')', [], punctuation, Options).
  233
  234sql_write_term(count(all), _Indent, Options)--> !,
  235        sql_emit_token('COUNT', [], function, Options),
  236        sql_emit_token('(*)', [], punctuation, Options).
  237
  238sql_write_term(query(Query), Indent, Options)--> !,
  239        sql_write_term(Query, Indent, Options).
  240
  241sql_write_term({no_quantifier}, _, _)--> !.
  242sql_write_term({no_limit}, _, _)--> !.
  243sql_write_term(all, _, Options)--> !, sql_emit_token(' ALL ', [], operator, Options).
  244sql_write_term(distinct, _, Options)--> !, sql_emit_token(' DISTINCT ', [], keyword, Options).
  245
  246sql_write_term(update(Table, Set, From, Where), Indent, Options)--> !,
  247        sql_emit_token('UPDATE ', [], keyword, Options),
  248        sql_write_term(Table, Indent, Options),
  249        sql_emit_token('~n~wSET ', [Indent], keyword, Options),
  250        tab_stop(NewIndent),
  251        sql_write_list_with_newlines(Set, NewIndent, Options),
  252        sql_write_term(From, Indent, Options),
  253        sql_write_term(Where, Indent, Options).
  254
  255sql_write_term(delete(Table, Where), Indent, Options)--> !,
  256        sql_emit_token('DELETE FROM ', [], keyword, Options),
  257        sql_write_term(Table, Indent, Options),
  258        sql_write_term(Where, Indent, Options).
  259
  260
  261sql_write_term(insert(Table, Values), Indent, Options)--> !,
  262        sql_emit_token('INSERT INTO ', [], keyword, Options),
  263        sql_write_term(Table, Indent, Options),
  264        sql_emit_token(' ', [], keyword, Options),
  265        sql_write_term(Values, Indent, Options).
  266
  267sql_write_term(insert_source(Source, _Override, Target), Indent, Options)--> !,
  268        sql_emit_token('(', [], keyword, Options),
  269        sql_write_list_compact(Source, Indent, Options),
  270        sql_emit_token(') ', [], keyword, Options),
  271        sql_write_term(Target, Indent, Options).
  272
  273sql_write_term(values(List), Indent, Options)--> !,
  274        sql_emit_token('~n~wVALUES ', [Indent], keyword, Options),
  275        tab_stop(NewIndent),
  276        sql_write_list_with_newlines(List, NewIndent, Options).
  277
  278
  279sql_write_term(set(Target, Source), Indent, Options)--> !,
  280        sql_write_term(Target, Indent, Options),
  281        sql_emit_token(' = ', [], operator, Options),
  282        tab_stop(NewIndent),
  283        sql_write_term(Source, NewIndent, Options).
  284
  285sql_write_term(select(Quantifier, Selections, Source, Limit, {no_for}), Indent, Options)--> !,
  286        sql_emit_token('SELECT ', [], keyword, Options),
  287        sql_write_term(Quantifier, Indent, Options),
  288        ( {Selections = _:all}->
  289            sql_emit_token('*', [], punctuation, Options)
  290        ; {otherwise}->
  291            sql_write_list_with_newlines(Selections, Indent, [explicit_literals(true)|Options])
  292        ),
  293        sql_emit_token(' ', [], punctuation, Options),
  294        ( {option(dbms('Microsoft SQL Server'), Options)}->
  295            sql_write_term(Limit, Indent, Options)
  296        ; {otherwise}->
  297            {true}
  298        ),
  299        sql_write_term(Source, Indent, Options),
  300        ( {option(dbms('PostgreSQL'), Options),
  301           Limit \== {no_limit}}->
  302            sql_write_term(Limit, Indent, Options)
  303        ; {otherwise}->
  304            {true}
  305        ).
  306
  307sql_write_term(column(Name, Type, AllowsNulls, IsIdentity, _Default), Indent, Options)--> !,
  308        ( {option(dbms(DBMS), Options)}->
  309            {cql_normalize_name(DBMS, Name, NormalizedName)}
  310        ; {otherwise}->
  311            {Name = NormalizedName}
  312        ),
  313        sql_emit_token('~w ', [NormalizedName], unknown, Options),
  314        ( {option(dbms('PostgreSQL'), Options),
  315           IsIdentity == is_identity(true)} ->
  316            sql_emit_token(' SERIAL', [], keyword, Options)
  317        ; {Type = domain(Domain)} ->
  318            {format(atom(Token), '<a href="/sql_explorer/~w">', [Domain])},
  319            sql_append_raw_token(Token),
  320            sql_write_term(Type, Indent, Options),
  321            sql_append_raw_token('</a>')
  322        ; {otherwise}->
  323            sql_write_term(Type, Indent, Options)
  324        ),
  325        ( {IsIdentity == is_identity(true)}->
  326            sql_emit_token(' PRIMARY KEY', [], keyword, Options)
  327        ; {AllowsNulls == allows_nulls(true)}->
  328            {true}
  329        ; {otherwise}->
  330            sql_emit_token(' NOT NULL', [], keyword, Options)
  331        ).
  332
  333
  334sql_write_term(select(Quantifier, Selections, Source, Limit, For), Indent, Options)-->
  335        {option(dbms('PostgreSQL'), Options),
  336        strip_sql_comments(For, for(ForClause)),
  337        strip_sql_comments(ForClause, xml_path(Separator)),
  338        strip_sql_comments(Selections, [derived_column(SingleItem, 'text()')])},
  339        !,
  340        sql_emit_token('array_to_string', [], function, Options),
  341        sql_emit_token('(', [], punctuation, Options),
  342        sql_emit_token('ARRAY', [], function, Options),
  343        sql_emit_token('(', [], punctuation, Options),
  344        sql_emit_token('SELECT ', [], keyword, Options),
  345        sql_write_term(Quantifier, Indent, Options),
  346        sql_write_term(SingleItem, Indent, Options),
  347        sql_emit_token(' ', [], punctuation, Options),
  348        sql_write_term(Source, Indent, Options),
  349        ( {Limit \== {no_limit}}->
  350            sql_write_term(Limit, Indent, Options)
  351        ; {otherwise}->
  352            {true}
  353        ),
  354        sql_emit_token(')', [], punctuation, Options),
  355        sql_emit_token(', ', [], comma, Options),
  356        sql_write_term(Separator, Indent, Options),
  357        sql_emit_token(')', [], punctuation, Options).
  358
  359sql_write_term(select(Quantifier, Selections, Source, Limit, For), Indent, Options)-->
  360        {option(dbms('Microsoft SQL Server'), Options),
  361        strip_sql_comments(For, for(xml_path(Separator)))},
  362        !,
  363        sql_write_term(select(Quantifier, Selections, Source, Limit, {no_for}), Indent, Options),
  364        sql_emit_token('FOR XML PATH', [], keyword, Options),
  365        sql_emit_token('(', [], punctuation, Options),
  366        sql_write_term(Separator, Indent, Options),
  367        sql_emit_token(')', [], punctuation, Options).
  368
  369sql_write_term(routine(Name, Args), Indent, Options)--> !,
  370        sql_write_term(Name, Indent, Options),
  371        sql_emit_token('(', [], punctuation, Options),
  372        tab_stop(NewIndent),
  373        {sql_list_length(Args, L)},
  374        ( {L =:= 0} ->
  375            % Special case - routine argument lists may be empty. No other SQL lists may be
  376            {true}
  377        ; {L < 2}->
  378            sql_write_list_compact(Args, NewIndent, Options)
  379        ; {otherwise}->
  380            sql_write_list_with_newlines(Args, NewIndent, Options)
  381        ),
  382        sql_emit_token(')', [], punctuation, Options).
  383
  384
  385sql_write_term(top(percent(N)), Indent, Options)--> !,
  386        ( {option(dbms('Microsoft SQL Server'), Options)}->
  387            sql_emit_token('TOP ', [], keyword, Options),
  388            sql_write_term(N, Indent, Options),
  389            sql_emit_token('PERCENT ', [], keyword, Options)
  390        ; {otherwise}->
  391            % Ignore TOP 100 PERCENT in 'PostgreSQL'
  392            {true}
  393        ).
  394
  395sql_write_term(top(N), Indent, Options)--> !,
  396        ( {option(dbms('Microsoft SQL Server'), Options)}->
  397            sql_emit_token('TOP ', [], keyword, Options),
  398            sql_write_term(N, Indent, Options)
  399        ; {otherwise}->
  400            sql_emit_token('~n~wLIMIT ', [Indent], keyword, Options),
  401            sql_write_term(N, Indent, Options)
  402        ).
  403
  404sql_write_term(column(Qualifier, Name), Indent, Options)--> !,
  405        ( {Qualifier == {no_qualifier}}->
  406            {true}
  407        ; {otherwise}->
  408            sql_write_term(Qualifier, Indent, Options),
  409            sql_emit_token('.', [], punctuation, Options)
  410        ),
  411        sql_write_and_strip_comments(Name, Indent, Options, StrippedName, Comments),
  412        ( {reserved_sql_keyword(StrippedName)}->
  413            ( {option(dbms('PostgreSQL'), Options)}->
  414                sql_emit_token('"', [], punctuation, Options),
  415                sql_write_term(StrippedName, Indent, Options),
  416                sql_emit_token('"', [], punctuation, Options)
  417            ; {otherwise}->
  418                sql_emit_token('[', [], punctuation, Options),
  419                sql_write_term(StrippedName, Indent, Options),
  420                sql_emit_token(']', [], punctuation, Options)
  421            )
  422        ; {otherwise}->
  423            sql_write_term(StrippedName, Indent, Options)
  424        ),
  425        sql_end_comments(Comments, Indent, Options).
  426
  427sql_write_term(group_expression(Expression, Collation), Indent, Options)--> !,
  428        sql_write_term(Expression, Indent, Options),
  429        ( {Collation == {no_collation}} ->
  430            {true}
  431        ; {otherwise}->
  432            sql_emit_token(' COLLATE ', [], keyword, Options),
  433            sql_write_term(Collation, Indent, Options)
  434        ).
  435
  436
  437sql_write_term(group_column(Name, Collation), Indent, Options)--> !,
  438        sql_write_term(Name, Indent, Options),
  439        ( {Collation == {no_collation}} ->
  440            {true}
  441        ; {otherwise}->
  442            sql_emit_token(' COLLATE ', [], keyword, Options),
  443            sql_write_term(Collation, Indent, Options)
  444        ).
  445
  446
  447sql_write_term(derived_column(Column, Alias), Indent, Options)--> !,
  448        ( {Alias \== {no_alias}}->
  449            sql_write_and_strip_comments(Column, Indent, Options, RawColumn, Comments1),
  450            ( {option(dbms('PostgreSQL'), Options),
  451              RawColumn = column(_Qualifier, PossibleLiteral),
  452              strip_sql_comments(PossibleLiteral, literal(Literal, string))}->
  453                % If the DBMS is 'PostgreSQL' then when writing out something like
  454                %   SELECT 'foo' AS bar
  455                % we have to instead output
  456                %   SELECT 'foo'::text AS bar
  457                % if we want the type of bar to be well-defined. The same is probably true of numeric literals
  458                sql_emit_token('\'', [], punctuation, Options),
  459                sql_write_literal(Literal, Options),
  460                sql_emit_token('\'::text', [], punctuation, Options)
  461            ; {otherwise}->
  462                sql_write_term(RawColumn, Indent, Options)
  463            ),
  464            sql_end_comments(Comments1, Indent, Options),
  465            sql_emit_token(' AS ', [], keyword, Options),
  466            sql_write_and_strip_comments(Alias, Indent, Options, Identifier, Comments2),
  467            ( {atom(Identifier)} ->
  468                % Must quote any identifiers!
  469                sql_write_term(literal(Identifier, identifier), Indent, Options)
  470            ; {Identifier = literal(Value, string)}->
  471                sql_write_term(literal(Value, identifier), Indent, Options)
  472            ; {otherwise}->
  473                {throw(bad_column_alias(Identifier))}
  474            ),
  475            sql_end_comments(Comments2, Indent, Options)
  476        ; {otherwise}->
  477            sql_write_term(Column, Indent, Options)
  478        ).
  479
  480sql_write_term(from(From), Indent, Options)--> !,
  481        sql_emit_token('~n~wFROM ', [Indent], keyword, Options),
  482        sql_write_list_with_newlines(From, Indent, Options).
  483
  484/* Matt-style joins
  485sql_write_term(join(LHS, RHS), Indent, Options)-->!,
  486        tab_stop(NewIndent),
  487        sql_write_term(LHS, Indent, Options),
  488        sql_emit_token('~n~w', [NewIndent], punctuation, Options),
  489        sql_write_term(RHS, Indent, Options).
  490
  491sql_write_term(qualified_join(Type, RHS, On), Indent, Options)--> !,
  492        tab_stop(NewIndent),
  493        sql_emit_token('   ', [], punctuation, Options),
  494        sql_write_term(Type, Indent, Options),
  495        sql_emit_token('~n~w', [NewIndent], punctuation, Options),
  496        sql_write_term(RHS, Indent, Options),
  497        sql_write_term(On, Indent, Options).
  498
  499sql_write_term(cross_join(RHS), Indent, Options)--> !,
  500        tab_stop(NewIndent),
  501        sql_emit_token('  CROSS JOIN~n~w', [NewIndent], operator, Options),
  502        sql_write_term(RHS, Indent, Options).
  503*/
  504
  505/* Chris-style joins */
  506sql_write_term(join(LHS, RHS), Indent, Options)-->!,
  507        tab_stop(NewIndent),
  508        sql_write_term(LHS, Indent, Options),
  509        sql_emit_token('~n~w', [NewIndent], punctuation, Options),
  510        sql_write_term(RHS, Indent, Options).
  511
  512sql_write_term(qualified_join(Type, RHS, On), Indent, Options)--> !,
  513        tab_stop(NewIndent),
  514        sql_write_term(Type, Indent, Options),
  515        sql_emit_token(' ', [], punctuation, Options),
  516        sql_write_term(RHS, Indent, Options),
  517        sql_emit_token('~n  ~w', [NewIndent], punctuation, Options),
  518        sql_write_term(On, Indent, Options).
  519
  520sql_write_term(cross_join(RHS), Indent, Options)--> !,
  521        tab_stop(NewIndent),
  522        sql_emit_token('  CROSS JOIN~n~w', [NewIndent], operator, Options),
  523        sql_write_term(RHS, Indent, Options).
  524
  525
  526sql_write_term(correlation(Name, Columns), Indent, Options)-->!,
  527        sql_write_term(Name, Indent, Options),
  528        ( {Columns == {no_columns}}->
  529            {true}
  530        ; {otherwise}->
  531            sql_emit_token('(', [], punctuation, Options),
  532            sql_write_list_compact(Columns, Indent, Options),
  533            sql_emit_token(')', [], punctuation, Options)
  534        ).
  535
  536sql_write_term(correlated_table(Name, Correlation), Indent, Options)--> !,
  537        sql_write_term(Name, Indent, Options),
  538        ( {Correlation == {no_correlation}}->
  539            {true}
  540        ; {otherwise}->
  541            sql_emit_token(' AS ', [], keyword, Options),
  542            sql_write_term(Correlation, Indent, Options)
  543        ).
  544
  545sql_write_term(on(Condition), Indent, Options)--> !,
  546        sql_emit_token(' ON ', [], keyword, Options),
  547        sql_emit_token('(', [], punctuation, Options),
  548        sql_write_term(Condition, Indent, Options),
  549        sql_emit_token(')', [], punctuation, Options).
  550
  551sql_write_term(predicate(P), Indent, Options)--> !,
  552        sql_write_term(P, Indent, Options).
  553
  554sql_write_term(comparison(Op, LHS, RHS), Indent, Options)--> !,
  555        sql_write_term(LHS, Indent, Options),
  556        sql_emit_token(' ', [], punctuation, Options),
  557        sql_write_term(Op, Indent, Options),
  558        sql_emit_token(' ', [], punctuation, Options),
  559        sql_write_term(RHS, Indent, Options).
  560
  561sql_write_term(element(A), Indent, Options)--> !,
  562        sql_write_term(A, Indent, Options).
  563
  564sql_write_term(and(A, B), Indent, Options)-->
  565        {option(suppress_collations, Options)},
  566        {should_suppress_collation(A)},
  567        !,
  568        sql_write_term(B, Indent, Options).
  569
  570sql_write_term(and(A, B), Indent, Options)-->
  571        {option(suppress_trivial_conditions, Options)},
  572        {should_suppress_condition(B)},
  573        !,
  574        sql_write_term(A, Indent, Options).
  575
  576
  577sql_write_term(and(A, B), Indent, Options)--> !,
  578        tab_stop(S),
  579        sql_write_term(A, Indent, Options),
  580        sql_emit_token(' AND~n~w', [S], operator, Options),
  581        sql_write_term(B, Indent, Options).
  582
  583sql_write_term(or(A, B), Indent, Options)--> !,
  584        sql_emit_token('(', [], punctuation, Options),
  585        sql_write_term(A, Indent, Options),
  586        sql_emit_token(') ', [], punctuation, Options),
  587        sql_emit_token('OR', [], operator, Options),
  588        sql_emit_token(' (', [], punctuation, Options),
  589        sql_write_term(B, Indent, Options),
  590        sql_emit_token(')', [], punctuation, Options).
  591
  592sql_write_term(multiply(A,B), Indent, Options)--> !,  % WARNING
  593        sql_write_term(A, Indent, Options),
  594        sql_emit_token(' * ', [], operator, Options),
  595        sql_write_term(B, Indent, Options).
  596
  597sql_write_term(add(A,B), Indent, Options)--> !,  % WARNING
  598        sql_write_term(A, Indent, Options),
  599        sql_emit_token(' + ', [], operator, Options),
  600        sql_write_term(B, Indent, Options).
  601
  602sql_write_term(subtract(A,B), Indent, Options)--> !,  % WARNING
  603        sql_write_term(A, Indent, Options),
  604        sql_emit_token(' - ', [], operator, Options),
  605        sql_write_term(B, Indent, Options).
  606
  607sql_write_term(divide(A,B), Indent, Options)--> !,  % WARNING
  608        sql_write_term(A, Indent, Options),
  609        sql_emit_token(' / ', [], operator, Options),
  610        sql_write_term(B, Indent, Options).
  611
  612sql_write_term(not(X), Indent, Options)--> !,
  613        sql_emit_token('NOT', [], operator, Options),
  614        sql_emit_token(' (', [], punctuation, Options),
  615        sql_write_term(X, Indent, Options),
  616        sql_emit_token(')', [], punctuation, Options).
  617
  618sql_write_term(round(X, P), Indent, Options)--> !,
  619        sql_emit_token('ROUND', [], function, Options),
  620        sql_emit_token('(', [], punctuation, Options),
  621        sql_write_term(X, Indent, Options),
  622        sql_emit_token(',', [], comma, Options),
  623        sql_write_term(P, Indent, Options),
  624        sql_emit_token(')', [], punctuation, Options).
  625
  626sql_write_term(floor(X), Indent, Options)--> !,
  627        sql_emit_token('FLOOR', [], function, Options),
  628        sql_emit_token('(', [], punctuation, Options),
  629        sql_write_term(X, Indent, Options),
  630        sql_emit_token(')', [], punctuation, Options).
  631
  632sql_write_term(ceiling(X), Indent, Options)--> !,
  633        sql_emit_token('CEILING', [], function, Options),
  634        sql_emit_token('(', [], punctuation, Options),
  635        sql_write_term(X, Indent, Options),
  636        sql_emit_token(')', [], punctuation, Options).
  637
  638sql_write_term(float(X), Indent, Options)--> !,
  639        sql_emit_token('FLOAT', [], function, Options),
  640        sql_emit_token('(', [], punctuation, Options),
  641        sql_write_term(X, Indent, Options),
  642        sql_emit_token(')', [], punctuation, Options).
  643
  644sql_write_term(username(X), Indent, Options)--> !, % TBD: Force normalization
  645        sql_emit_token('USERNAME', [], function, Options),
  646        sql_emit_token('(', [], punctuation, Options),
  647        sql_write_term(X, Indent, Options),
  648        sql_emit_token(')', [], punctuation, Options).
  649
  650sql_write_term(permissions(X), Indent, Options)--> !, % TBD: Force normalization
  651        sql_emit_token('PERMISSIONS', [], function, Options),
  652        sql_emit_token('(', [], punctuation, Options),
  653        sql_write_term(X, Indent, Options),
  654        sql_emit_token(')', [], punctuation, Options).
  655
  656sql_write_term(getdate({}), _Indent, Options)-->
  657        ( {option(dbms('PostgreSQL'), Options) ; option(normalize, Options)}),
  658        !,
  659        sql_emit_token('CURRENT_TIMESTAMP', [], function, Options).
  660
  661sql_write_term(getdate({}), _Indent, Options)--> % TBD: Force normalization
  662        {option(dbms('Microsoft SQL Server'), Options)},
  663        !,
  664        sql_emit_token('GETDATE', [], function, Options),
  665        sql_emit_token('()', [], punctuation, Options).
  666
  667sql_write_term(dbname({}), _Indent, Options)--> !, % TBD: Force normalization
  668        sql_emit_token('DBNAME', [], function, Options),
  669        sql_emit_token('()', [], punctuation, Options).
  670
  671sql_write_term(fn_now({}), _Indent, Options)-->
  672        ( {option(dbms('PostgreSQL'), Options) ; option(normalize, Options)}), !,
  673        sql_emit_token('CURRENT_TIMESTAMP', [], function, Options).
  674
  675sql_write_term(fn_now({}), _Indent, Options)--> !, % TBD: Force normalization
  676        sql_emit_token('{ fn now() }', [], legacy, Options).
  677
  678sql_write_term(len(X), Indent, Options)-->
  679        {option(dbms('PostgreSQL'), Options)}, !,
  680        % The ANSI string-length function is called CHAR_LENGTH. This is, incredibly, unsupported by SQL Server
  681        sql_emit_token('CHAR_LENGTH', [], function, Options),
  682        sql_emit_token('(', [], punctuation, Options),
  683        sql_write_term(X, Indent, Options),
  684        sql_emit_token(')', [], punctuation, Options).
  685
  686sql_write_term(len(X), Indent, Options)--> !,
  687        sql_emit_token('LEN', [], function, Options),
  688        sql_emit_token('(', [], punctuation, Options),
  689        sql_write_term(X, Indent, Options),
  690        sql_emit_token(')', [], punctuation, Options).
  691
  692sql_write_term(str(X), Indent, Options)-->
  693        {option(dbms('PostgreSQL'), Options)},
  694        !,
  695        % STR in SQL Server is used to convert floats to strings.
  696        % The default length is 9, and the default precision is 0
  697        % The 'PostgreSQL' equivalent is therefore like to_char(X, '9999999999')
  698        sql_emit_token('TO_CHAR', [], function, Options),
  699        sql_emit_token('(', [], punctuation, Options),
  700        sql_write_term(X, Indent, Options),
  701        sql_emit_token(', ', [], comma, Options),
  702        sql_emit_token('\'9999999999\'', [], literal, Options),
  703        sql_emit_token(')', [], punctuation, Options).
  704
  705sql_write_term(str(X), Indent, Options)--> !,
  706        sql_emit_token('STR', [], function, Options),
  707        sql_emit_token('(', [], punctuation, Options),
  708        sql_write_term(X, Indent, Options),
  709        sql_emit_token(')', [], punctuation, Options).
  710
  711sql_write_term(concatenate(A,B), Indent, Options)--> !,
  712        sql_write_term(A, Indent, Options),
  713        ( {option(dbms('Microsoft SQL Server'), Options)}->
  714            sql_emit_token(' + ', [], punctuation, Options)
  715        ; {otherwise}->
  716            sql_emit_token(' || ', [], punctuation, Options)
  717        ),
  718        sql_write_term(B, Indent, Options).
  719
  720sql_write_term(add_interval(A,B), Indent, Options)--> !,
  721        sql_write_term(A, Indent, Options),
  722        sql_emit_token(' + ', [], punctuation, Options),
  723        ( {option(dbms('Microsoft SQL Server'), Options)}->
  724            sql_write_term(B, Indent, Options)
  725        ; {otherwise}->
  726            sql_emit_token('CAST', [], function, Options),
  727            sql_emit_token('(', [], punctuation, Options),
  728            sql_write_term(B, Indent, Options),
  729            sql_emit_token(' || ', [], punctuation, Options),
  730            sql_emit_token(' \' days\'', [], literal, Options),
  731            sql_emit_token(' AS', [], keyword, Options),
  732            sql_emit_token(' interval', [], function, Options),
  733            sql_emit_token(')', [], punctuation, Options)
  734        ).
  735
  736sql_write_term(left(V, N), Indent, Options)--> !,
  737        sql_emit_token('LEFT', [], function, Options),
  738        sql_emit_token('(', [], punctuation, Options),
  739        sql_write_term(V, Indent, Options),
  740        sql_emit_token(', ', [], comma, Options),
  741        sql_write_term(N, Indent, Options),
  742        sql_emit_token(')', [], punctuation, Options).
  743
  744sql_write_term(right(V, N), Indent, Options)--> !,
  745        sql_emit_token('RIGHT', [], function, Options),
  746        sql_emit_token('(', [], punctuation, Options),
  747        sql_write_term(V, Indent, Options),
  748        sql_emit_token(', ', [], comma, Options),
  749        sql_write_term(N, Indent, Options),
  750        sql_emit_token(')', [], punctuation, Options).
  751
  752sql_write_term(rtrim(V), Indent, Options)--> !,
  753        sql_emit_token('RTRIM', [], function, Options),
  754        sql_emit_token('(', [], punctuation, Options),
  755        sql_write_term(V, Indent, Options),
  756        sql_emit_token(')', [], punctuation, Options).
  757
  758sql_write_term(ltrim(V), Indent, Options)--> !,
  759        sql_emit_token('LTRIM', [], function, Options),
  760        sql_emit_token('(', [], punctuation, Options),
  761        sql_write_term(V, Indent, Options),
  762        sql_emit_token(')', [], punctuation, Options).
  763
  764sql_write_term(upper(V), Indent, Options)--> !,
  765        sql_emit_token('UPPER', [], function, Options),
  766        sql_emit_token('(', [], punctuation, Options),
  767        sql_write_term(V, Indent, Options),
  768        sql_emit_token(')', [], punctuation, Options).
  769
  770sql_write_term(lower(V), Indent, Options)--> !,
  771        sql_emit_token('LOWER', [], function, Options),
  772        sql_emit_token('(', [], punctuation, Options),
  773        sql_write_term(V, Indent, Options),
  774        sql_emit_token(')', [], punctuation, Options).
  775
  776sql_write_term(day(A), Indent, Options)-->
  777        {option(dbms('PostgreSQL'), Options)}, !,
  778        sql_emit_token('DATE_PART', [], function, Options),
  779        sql_emit_token('(', [], punctuation, Options),
  780        sql_emit_token('\'day\'', [], literal, Options),
  781        sql_emit_token(', ', [], comma, Options),
  782        sql_write_term(A, Indent, Options),
  783        sql_emit_token(')', [], punctuation, Options).
  784
  785sql_write_term(month(A), Indent, Options)-->
  786        {option(dbms('PostgreSQL'), Options)}, !,
  787        sql_emit_token('DATE_PART', [], function, Options),
  788        sql_emit_token('(', [], punctuation, Options),
  789        sql_emit_token('\'month\'', [], literal, Options),
  790        sql_emit_token(', ', [], comma, Options),
  791        sql_write_term(A, Indent, Options),
  792        sql_emit_token(')', [], punctuation, Options).
  793
  794sql_write_term(year(A), Indent, Options)-->
  795        {option(dbms('PostgreSQL'), Options)}, !,
  796        sql_emit_token('DATE_PART', [], function, Options),
  797        sql_emit_token('(', [], punctuation, Options),
  798        sql_emit_token('\'year\'', [], literal, Options),
  799        sql_emit_token(', ', [], comma, Options),
  800        sql_write_term(A, Indent, Options),
  801        sql_emit_token(')', [], punctuation, Options).
  802
  803sql_write_term(day(A), Indent, Options)--> !, % TBD: Force normalization
  804        sql_emit_token('DAY', [], function, Options),
  805        sql_emit_token('(', [], punctuation, Options),
  806        sql_write_term(A, Indent, Options),
  807        sql_emit_token(')', [], punctuation, Options).
  808
  809sql_write_term(month(A), Indent, Options)--> !, % TBD: Force normalization
  810        sql_emit_token('MONTH', [], function, Options),
  811        sql_emit_token('(', [], punctuation, Options),
  812        sql_write_term(A, Indent, Options),
  813        sql_emit_token(')', [], punctuation, Options).
  814
  815sql_write_term(year(A), Indent, Options)--> !, % TBD: Force normalization
  816        sql_emit_token('YEAR', [], function, Options),
  817        sql_emit_token('(', [], punctuation, Options),
  818        sql_write_term(A, Indent, Options),
  819        sql_emit_token(')', [], punctuation, Options).
  820
  821sql_write_term(dateadd(A,B,C), Indent, Options)-->
  822        {option(dbms('PostgreSQL'), Options)}, !,
  823        sql_write_and_strip_comments(A, Indent, Options, Class, Comments),
  824        % Quirk. SQL Server allows implicit cast of 0 to a datetime to get 1/1/1901.
  825        sql_write_date(C, Indent, Options),
  826        sql_emit_token(' + ', [], punctuation, Options),
  827        sql_emit_token('CAST', [], function, Options),
  828        sql_emit_token('(', [], punctuation, Options),
  829        sql_emit_token('CAST', [], function, Options),
  830        sql_emit_token('(', [], punctuation, Options),
  831        sql_write_term(B, Indent, Options),
  832        sql_emit_token(' AS ', [], keyword, Options),
  833        sql_emit_token('text', [], function, Options),
  834        sql_emit_token(')', [], punctuation, Options),
  835        sql_emit_token(' || ', [], punctuation, Options),
  836        sql_emit_token('\' ~w\'', [Class], literal, Options),
  837        sql_emit_token(' AS ', [], keyword, Options),
  838        sql_emit_token('interval', [], function, Options),
  839        sql_emit_token(')', [], punctuation, Options),
  840        sql_end_comments(Comments, Indent, Options).
  841
  842sql_write_term(dateadd(A,B,C), Indent, Options)--> !, % TBD: Force normalization
  843        sql_emit_token('DATEADD', [], function, Options),
  844        sql_emit_token('(', [], punctuation, Options),
  845        sql_write_term(A, Indent, Options),
  846        sql_emit_token(', ', [], comma, Options),
  847        sql_write_term(B, Indent, Options),
  848        sql_emit_token(', ', [], comma, Options),
  849        sql_write_term(C, Indent, Options),
  850        sql_emit_token(')', [], punctuation, Options).
  851
  852sql_write_term(datepart(A,B), Indent, Options)-->
  853        ( {option(dbms('PostgreSQL'), Options) ; option(normalize, Options)}),
  854        !,
  855        sql_emit_token('EXTRACT', [], function, Options),
  856        sql_emit_token('(', [], punctuation, Options),
  857        sql_emit_token('\'', [], literal, Options),
  858        sql_write_term(A, Indent, Options),
  859        sql_emit_token('\'', [], literal, Options),
  860        sql_emit_token(' FROM ', [], keyword, Options),
  861        sql_write_term(B, Indent, Options),
  862        sql_emit_token(')', [], punctuation, Options).
  863
  864
  865sql_write_term(datepart(A,B), Indent, Options)--> !, % TBD: Force normalization
  866        sql_emit_token('EXTRACT', [], function, Options),
  867        sql_emit_token('(', [], punctuation, Options),
  868        sql_emit_token('\'', [], literal, Options),
  869        sql_write_term(A, Indent, Options),
  870        sql_emit_token('\'', [], literal, Options),
  871        sql_emit_token(', ', [], comma, Options),
  872        sql_write_term(B, Indent, Options),
  873        sql_emit_token(')', [], punctuation, Options).
  874
  875
  876sql_write_term(datename(A,B), Indent, Options)-->
  877        {option(dbms('PostgreSQL'), Options)}, !, % Also Oracle
  878        sql_emit_token('TO_CHAR', [], function, Options),
  879        sql_emit_token('(', [], punctuation, Options),
  880        sql_write_term(B, Indent, Options),
  881        sql_emit_token(', ', [], comma, Options),
  882        sql_write_and_strip_comments(A, Indent, Options, AA, Comments),
  883        ( {normalize_date_type(AA, Type)}->
  884            {true}
  885        ; {otherwise}->
  886            {throw(cql_error(cannot_canonicalize_date_part, AA))}
  887        ),
  888        ( {Type == day_of_week} ->
  889            sql_emit_token('\'Day\'', [], literal, Options)
  890        ; {otherwise}->
  891            {throw(cql_error(cannot_map_date_type, Type))}
  892        ),
  893        sql_end_comments(Comments, Indent, Options),
  894        sql_emit_token(')', [], punctuation, Options).
  895
  896sql_write_term(datename(A,B), Indent, Options)--> !, % TBD: Force normalization
  897        sql_emit_token('DATENAME', [], function, Options),
  898        sql_emit_token('(', [], punctuation, Options),
  899        sql_write_term(A, Indent, Options),
  900        sql_emit_token(', ', [], comma, Options),
  901        sql_write_term(B, Indent, Options),
  902        sql_emit_token(')', [], punctuation, Options).
  903
  904sql_write_term(datediff(A,B,C), Indent, Options)-->
  905        {option(dbms('PostgreSQL'), Options)}, !,
  906        sql_write_and_strip_comments(A, Indent, Options, AA, Comments),
  907        ( {normalize_date_type(AA, Type)}->
  908            {true}
  909        ; {otherwise}->
  910            {throw(cql_error(cannot_canonicalize_date_part, AA))}
  911        ),
  912        ( {Type == day}->
  913            sql_emit_token('DATE_PART', [], function, Options),
  914            sql_emit_token('(', [], punctuation, Options),
  915            sql_emit_token('\'day\'', [], literal, Options),
  916            sql_emit_token(', ', [], comma, Options),
  917            sql_write_date(C, Indent, Options),
  918            sql_emit_token(' - ', [], punctuation, Options),
  919            sql_write_date(B, Indent, Options),
  920            sql_emit_token(') ', [], punctuation, Options)
  921        ; {Type == week} ->
  922            sql_emit_token('TRUNC', [], function, Options),
  923            sql_emit_token('(', [], punctuation, Options),
  924            sql_emit_token('DATE_PART', [], function, Options),
  925            sql_emit_token('(', [], punctuation, Options),
  926            sql_emit_token('\'day\'', [], literal, Options),
  927            sql_emit_token(', ', [], comma, Options),
  928            sql_write_date(C, Indent, Options),
  929            sql_emit_token(' - ', [], punctuation, Options),
  930            sql_write_date(B, Indent, Options),
  931            sql_emit_token(')', [], punctuation, Options),
  932            sql_emit_token(' / ', [], punctuation, Options),
  933            sql_emit_token('7', [], literal, Options),
  934            sql_emit_token(')', [], punctuation, Options)
  935        ; {Type == second} ->
  936            % This is unfortunately quite complicated. Basically:
  937            % days_diff = DATE_PART('day', end - start)
  938            % hours_diff = days_diff * 24 + DATE_PART('hour', end - start )
  939            % minutes_diff = hours_diff * 60 + DATE_PART('minute', end - start )
  940            % seconds_diff = minutes_diff * 60 + DATE_PART('second', end - start )
  941            % So overall
  942            % ((DATE_PART('day', end - start) * 24 + DATE_PART('hour', end - start )) * 60 + DATE_PART('minute', end - start )) * 60 + DATE_PART('second', end - start )
  943
  944            sql_emit_token('(', [], punctuation, Options),
  945            sql_emit_token('(', [], punctuation, Options),
  946            sql_emit_token('DATE_PART', [], function, Options),
  947            sql_emit_token('(', [], punctuation, Options),
  948            sql_emit_token('\'day\'', [], literal, Options),
  949            sql_emit_token(', ', [], comma, Options),
  950            sql_write_term(C, Indent, Options),
  951            sql_emit_token(' - ', [], punctuation, Options),
  952            sql_write_term(B, Indent, Options),
  953            sql_emit_token(') ', [], punctuation, Options),
  954            sql_emit_token(' * ', [], punctuation, Options),
  955            sql_emit_token('24', [], literal, Options),
  956            sql_emit_token(' + ', [], punctuation, Options),
  957            sql_emit_token('DATE_PART', [], function, Options),
  958            sql_emit_token('(', [], punctuation, Options),
  959            sql_emit_token('\'hour\'', [], literal, Options),
  960            sql_emit_token(', ', [], comma, Options),
  961            sql_write_term(C, Indent, Options),
  962            sql_emit_token(' - ', [], punctuation, Options),
  963            sql_write_term(B, Indent, Options),
  964            sql_emit_token(')', [], punctuation, Options),
  965            sql_emit_token(')', [], punctuation, Options),
  966            sql_emit_token(' * ', [], punctuation, Options),
  967            sql_emit_token('60', [], literal, Options),
  968            sql_emit_token(' + ', [], punctuation, Options),
  969            sql_emit_token('DATE_PART', [], function, Options),
  970            sql_emit_token('(', [], punctuation, Options),
  971            sql_emit_token('\'minute\'', [], literal, Options),
  972            sql_emit_token(', ', [], comma, Options),
  973            sql_write_term(C, Indent, Options),
  974            sql_emit_token(' - ', [], punctuation, Options),
  975            sql_write_term(B, Indent, Options),
  976            sql_emit_token(')', [], punctuation, Options),
  977            sql_emit_token(')', [], punctuation, Options),
  978            sql_emit_token(' * ', [], punctuation, Options),
  979            sql_emit_token('60', [], literal, Options),
  980            sql_emit_token(' + ', [], punctuation, Options),
  981            sql_emit_token('DATE_PART', [], function, Options),
  982            sql_emit_token('(', [], punctuation, Options),
  983            sql_emit_token('\'second\'', [], literal, Options),
  984            sql_emit_token(', ', [], comma, Options),
  985            sql_write_term(C, Indent, Options),
  986            sql_emit_token(' - ', [], punctuation, Options),
  987            sql_write_term(B, Indent, Options),
  988            sql_emit_token(') ', [], punctuation, Options)
  989        ; {Type == year} ->
  990            sql_emit_token('DATE_PART', [], function, Options),
  991            sql_emit_token('(', [], punctuation, Options),
  992            sql_emit_token('\'year\'', [], literal, Options),
  993            sql_emit_token(', ', [], comma, Options),
  994            sql_write_date(C, Indent, Options),
  995            sql_emit_token(' - ', [], punctuation, Options),
  996            sql_write_date(B, Indent, Options),
  997            sql_emit_token(') ', [], punctuation, Options)
  998        ; {Type == month}->
  999            sql_emit_token('DATE_PART', [], function, Options),
 1000            sql_emit_token('(', [], punctuation, Options),
 1001            sql_emit_token('\'year\'', [], literal, Options),
 1002            sql_emit_token(', ', [], comma, Options),
 1003            sql_write_date(C, Indent, Options),
 1004            sql_emit_token(' - ', [], punctuation, Options),
 1005            sql_write_date(B, Indent, Options),
 1006            sql_emit_token(') ', [], punctuation, Options),
 1007            sql_emit_token(' * ', [], punctuation, Options),
 1008            sql_emit_token('12', [], literal, Options),
 1009            sql_emit_token(' + ', [], punctuation, Options),
 1010            sql_emit_token('DATE_PART', [], function, Options),
 1011            sql_emit_token('(', [], punctuation, Options),
 1012            sql_emit_token('\'month\'', [], literal, Options),
 1013            sql_emit_token(', ', [], comma, Options),
 1014            sql_write_date(C, Indent, Options),
 1015            sql_emit_token(' - ', [], punctuation, Options),
 1016            sql_write_date(B, Indent, Options),
 1017            sql_emit_token(')', [], punctuation, Options)
 1018        ; {otherwise}->
 1019            {throw(cql_error(cannot_datediff, AA))}
 1020        ),
 1021        sql_end_comments(Comments, Indent, Options).
 1022
 1023sql_write_term(datediff(A,B,C), Indent, Options)--> !, % TBD: Force normalization
 1024        sql_emit_token('DATEDIFF', [], function, Options),
 1025        sql_emit_token('(', [], punctuation, Options),
 1026        sql_write_term(A, Indent, Options),
 1027        sql_emit_token(', ', [], comma, Options),
 1028        sql_write_term(B, Indent, Options),
 1029        sql_emit_token(', ', [], comma, Options),
 1030        sql_write_term(C, Indent, Options),
 1031        sql_emit_token(')', [], punctuation, Options).
 1032
 1033sql_write_term(replace(A,B,C), Indent, Options)--> !,
 1034        sql_emit_token('REPLACE', [], function, Options),
 1035        sql_emit_token('(', [], punctuation, Options),
 1036        sql_write_term(A, Indent, Options),
 1037        sql_emit_token(', ', [], comma, Options),
 1038        sql_write_term(B, Indent, Options),
 1039        sql_emit_token(', ', [], comma, Options),
 1040        sql_write_term(C, Indent, Options),
 1041        sql_emit_token(')', [], punctuation, Options).
 1042
 1043sql_write_term(substring(A,B,C), Indent, Options)--> !,
 1044        sql_emit_token('SUBSTRING', [], function, Options),
 1045        sql_emit_token('(', [], punctuation, Options),
 1046        sql_write_term(A, Indent, Options),
 1047        sql_emit_token(', ', [], comma, Options),
 1048        sql_write_term(B, Indent, Options),
 1049        sql_emit_token(', ', [], comma, Options),
 1050        sql_write_term(C, Indent, Options),
 1051        sql_emit_token(')', [], punctuation, Options).
 1052
 1053sql_write_term(charindex(ExpressionToFind, ExpressionToSearch, StartLocation), Indent, Options)-->
 1054        ( {option(dbms('PostgreSQL'), Options) ; option(normalize, Options)}),
 1055        !,
 1056        ( {strip_sql_comments(StartLocation, {no_start})}->
 1057            sql_emit_token('POSITION', [], function, Options),
 1058            sql_emit_token('(', [], punctuation, Options),
 1059            sql_write_term(ExpressionToFind, Indent, Options),
 1060            sql_emit_token(' IN ', [], keyword, Options),
 1061            sql_write_term(ExpressionToSearch, Indent, Options),
 1062            sql_emit_token(')', [], punctuation, Options)
 1063        ; {otherwise}->
 1064            sql_emit_token('POSITION', [], function, Options),
 1065            sql_emit_token('(', [], punctuation, Options),
 1066            sql_write_term(ExpressionToFind, Indent, Options),
 1067            sql_emit_token(' IN SUBSTRING', [], keyword, Options),
 1068            sql_emit_token('(', [], punctuation, Options),
 1069            sql_write_term(ExpressionToSearch, Indent, Options),
 1070            sql_emit_token(' FROM ', [], keyword, Options),
 1071            sql_write_term(StartLocation, Indent, Options),
 1072            sql_emit_token(')', [], punctuation, Options),
 1073            sql_emit_token(')', [], punctuation, Options)
 1074        ).
 1075
 1076sql_write_term(charindex(A,B,C), Indent, Options)--> !,
 1077        sql_emit_token('CHARINDEX', [], function, Options),
 1078        sql_emit_token('(', [], punctuation, Options),
 1079        sql_write_term(A, Indent, Options),
 1080        sql_emit_token(', ', [], comma, Options),
 1081        sql_write_term(B, Indent, Options),
 1082        ( {C == {no_start}} ->
 1083            {true}
 1084        ; {otherwise}->
 1085            sql_emit_token(', ', [], comma, Options),
 1086            sql_write_term(C, Indent, Options)
 1087        ),
 1088        sql_emit_token(')', [], punctuation, Options).
 1089
 1090sql_write_term(precision_cast(A,B,C), Indent, Options)--> !,
 1091        ( {option(dbms('Microsoft SQL Server'), Options),
 1092           \+option(normalize, Options)}->
 1093            sql_emit_token('CONVERT', [], function, Options),
 1094            sql_emit_token('(', [], punctuation, Options),
 1095            sql_write_term(A, Indent, Options),
 1096            sql_emit_token(', ', [], comma, Options),
 1097            sql_write_term(B, Indent, Options),
 1098            ( {C == {no_precision}} ->
 1099                {true}
 1100            ; {otherwise}->
 1101                sql_emit_token(', ', [], comma, Options),
 1102                sql_write_term(C, Indent, Options)
 1103            ),
 1104            sql_emit_token(')', [], punctuation, Options)
 1105        ; {otherwise}->
 1106            ( {C == {no_precision}} ->
 1107                sql_emit_token('CAST', [], function, Options),
 1108                sql_emit_token('(', [], punctuation, Options),
 1109                sql_write_term(B, Indent, Options),
 1110                sql_emit_token(' AS ', [], keyword, Options),
 1111                sql_write_term(A, Indent, Options)
 1112            ; {A = _:native_type(NativeType),
 1113              strip_sql_comments(NativeType, varchar(_))}->
 1114                sql_emit_token('CAST', [], function, Options),
 1115                sql_emit_token('(', [], punctuation, Options),
 1116                sql_write_term(B, Indent, Options),
 1117                sql_emit_token(' AS ', [], keyword, Options),
 1118                sql_emit_token('VARCHAR', [], keyword, Options),
 1119                sql_emit_token('(', [], punctuation, Options),
 1120                sql_write_term(C, Indent, Options),
 1121                sql_emit_token(')', [], punctuation, Options)
 1122            ; {otherwise}->
 1123                {throw(unnormalizable(precision_cast(A,C)))}
 1124            ),
 1125            sql_emit_token(')', [], punctuation, Options)
 1126        ).
 1127
 1128sql_write_term(cast(A, B), Indent, Options)--> !,
 1129        sql_emit_token('CAST', [], function, Options),
 1130        sql_emit_token('(', [], punctuation, Options),
 1131        sql_write_term(A, Indent, Options),
 1132        sql_emit_token(' AS ', [], keyword, Options),
 1133        sql_write_term(B, Indent, Options),
 1134        sql_emit_token(')', [], punctuation, Options).
 1135
 1136sql_write_term(native_type(A), Indent, Options)--> !,
 1137        sql_write_type(A, Indent, Options).
 1138
 1139sql_write_term(like(LHS,Pattern,Escape), Indent, Options)--> !,
 1140        sql_write_term(LHS, Indent, Options),
 1141        sql_emit_token(' LIKE ', [], operator, Options),
 1142        sql_write_term(Pattern, Indent, Options),
 1143        ( {Escape == {no_escape}}->
 1144            {true}
 1145          ; {otherwise}->
 1146            sql_emit_token(' ESCAPE ', [], keyword, Options),
 1147            sql_write_term(Escape, Indent, Options)
 1148        ).
 1149
 1150sql_write_term(not_like(LHS,Pattern,Escape), Indent, Options)-->!,
 1151        sql_write_term(LHS, Indent, Options),
 1152        sql_emit_token(' NOT LIKE ', [], operator, Options),
 1153        sql_write_term(Pattern, Indent, Options),
 1154        ( {Escape == {no_escape}}->
 1155            {true}
 1156        ; {otherwise}->
 1157            sql_emit_token(' ESCAPE ', [], keyword, Options),
 1158            sql_write_term(Escape, Indent, Options)
 1159        ).
 1160
 1161sql_write_term({no_from}, _, _)--> !.
 1162sql_write_term({no_where}, _, _)--> !.
 1163sql_write_term({no_groupby}, _, _)--> !.
 1164sql_write_term({no_orderby}, _, _)--> !.
 1165sql_write_term({no_having}, _, _)--> !.
 1166sql_write_term({default_values}, _Indent, Options)--> !,
 1167        sql_emit_token(' DEFAULT VALUES ', [], keyword, Options).
 1168
 1169sql_write_term(source(From, Where, GroupBy, OrderBy, Having), Indent, Options)--> !,
 1170        sql_write_term(From, Indent, Options),
 1171        sql_write_term(Where, Indent, Options),
 1172        sql_write_term(GroupBy, Indent, Options),
 1173        ( {option(dbms('PostgreSQL'), Options)}->
 1174            sql_write_term(Having, Indent, Options),
 1175            sql_write_term(OrderBy, Indent, Options)
 1176        ; {otherwise}->
 1177            sql_write_term(OrderBy, Indent, Options),
 1178            sql_write_term(Having, Indent, Options)
 1179        ).
 1180
 1181sql_write_term(exists(A), _Indent, Options)--> !,
 1182        sql_emit_token('EXISTS ', [], operator, Options),
 1183        tab_stop(S),
 1184        sql_write_term(A, S, Options).
 1185
 1186sql_write_term(cast(A,B), Indent, Options)--> !,
 1187        sql_emit_token('CAST', [], function, Options),
 1188        sql_emit_token('(', [], punctuation, Options),
 1189        sql_write_term(A, Indent, Options),
 1190        sql_emit_token(', ', [], comma, Options),
 1191        sql_write_term(B, Indent, Options),
 1192        sql_emit_token(')', [], punctuation, Options).
 1193
 1194sql_write_term(coalesce(List), Indent, Options)--> !,
 1195        sql_emit_token('COALESCE', [], function, Options),
 1196        sql_emit_token('(', [], punctuation, Options),
 1197        sql_write_list_compact(List, Indent, Options),
 1198        sql_emit_token(')', [], punctuation, Options).
 1199
 1200sql_write_term(isnull(A, B), Indent, Options)--> !,
 1201        ( {option(dbms('Microsoft SQL Server'), Options),
 1202           \+option(normalize, Options)}->
 1203            sql_emit_token('ISNULL', [], function, Options),
 1204            sql_emit_token('(', [], punctuation, Options),
 1205            sql_write_term(A, Indent, Options),
 1206            sql_emit_token(', ', [], comma, Options),
 1207            sql_write_term(B, Indent, Options),
 1208            sql_emit_token(')', [], punctuation, Options)
 1209        ; {otherwise}->
 1210            sql_emit_token('COALESCE', [], function, Options),
 1211            sql_emit_token('(', [], punctuation, Options),
 1212            sql_write_term(A, Indent, Options),
 1213            sql_emit_token(', ', [], comma, Options),
 1214            sql_write_term(B, Indent, Options),
 1215            sql_emit_token(')', [], punctuation, Options)
 1216        ).
 1217
 1218sql_write_term(negative(A), Indent, Options)--> !,
 1219        sql_emit_token('-', [], punctuation, Options), % WARNING: Order of operations
 1220        sql_write_term(A, Indent, Options).
 1221
 1222sql_write_term(abs(A), Indent, Options)--> !,
 1223        sql_emit_token('ABS', [], function, Options),
 1224        sql_emit_token('(', [], punctuation, Options),
 1225        sql_write_term(A, Indent, Options),
 1226        sql_emit_token(')', [], punctuation, Options).
 1227
 1228sql_write_term(else(Else), Indent, Options)--> !,
 1229        sql_write_term(Else, Indent, Options).
 1230
 1231sql_write_term(simple_case(Operand, Cases, Else), _Indent, Options)-->!,
 1232        tab_stop(S),
 1233        sql_emit_token('CASE ', [], keyword, Options),
 1234        tab_stop(SS),
 1235        sql_write_term(Operand, SS, Options),
 1236        sql_emit_token('~n~w', [SS], punctuation, Options),
 1237        sql_write_list_with_newlines_and_no_commas(Cases, SS, Options),
 1238        ( {Else == {no_else}}->
 1239            {true}
 1240        ; {otherwise}->
 1241            sql_emit_token('~n~w     ELSE ', [S], keyword, Options),
 1242            tab_stop(SSS),
 1243            sql_write_term(Else, SSS, Options)
 1244        ),
 1245        sql_emit_token('~n~wEND', [S], keyword, Options).
 1246
 1247sql_write_term(case(Cases, Else), _Indent, Options)-->!,
 1248        tab_stop(S),
 1249        sql_emit_token('CASE ', [], keyword, Options),
 1250        tab_stop(SS),
 1251        sql_write_list_with_newlines_and_no_commas(Cases, SS, Options),
 1252        ( {Else == {no_else}}->
 1253            {true}
 1254        ; {otherwise}->
 1255            sql_emit_token('~n~w     ELSE ', [S], keyword, Options),
 1256            tab_stop(SSS),
 1257            sql_write_term(Else, SSS, Options)
 1258        ),
 1259        sql_emit_token('~n~wEND', [S], keyword, Options).
 1260
 1261sql_write_term(when(searched(S), R), Indent, Options)--> !,
 1262        sql_emit_token('WHEN ', [], keyword, Options),
 1263        sql_write_term(S, Indent, Options),
 1264        sql_emit_token('~n~w  THEN ', [Indent], keyword, Options),
 1265        sql_write_term(R, Indent, Options).
 1266
 1267sql_write_term(when(Match, R), Indent, Options)--> !,
 1268        sql_emit_token('WHEN ', [], keyword, Options),
 1269        sql_write_term(Match, Indent, Options),
 1270        sql_emit_token('~n~w  THEN ', [Indent], keyword, Options),
 1271        sql_write_term(R, Indent, Options).
 1272
 1273sql_write_term(having(Having), Indent, Options)--> !,
 1274        sql_emit_token('~n~w', [Indent], punctuation, Options),
 1275        sql_emit_token('HAVING ', [], keyword, Options),
 1276        sql_write_term(Having, Indent, Options).
 1277
 1278sql_write_term(where(Where), Indent, Options)--> !,
 1279        sql_emit_token('~n~w', [Indent], punctuation, Options),
 1280        sql_emit_token('WHERE ', [], keyword, Options),
 1281        sql_write_term(Where, Indent, Options).
 1282
 1283sql_write_term(group_by(Groupings), Indent, Options)--> !,
 1284        sql_emit_token('~n~w', [Indent], punctuation, Options),
 1285        sql_emit_token('GROUP BY ', [], keyword, Options),
 1286        sql_write_list_with_newlines(Groupings, Indent, Options).
 1287
 1288sql_write_term(order_by(Orderings), Indent, Options)--> !,
 1289        sql_emit_token('~n~w', [Indent], punctuation, Options),
 1290        sql_emit_token('ORDER BY ', [], keyword, Options),
 1291        sql_write_list_with_newlines(Orderings, Indent, Options).
 1292
 1293sql_write_term(subquery(Q), _Indent, Options)--> !,
 1294        sql_emit_token('( ', [], punctuation, Options),
 1295        tab_stop(S),
 1296        sql_write_term(Q, S, Options),
 1297        sql_emit_token(')', [], punctuation, Options).
 1298
 1299sql_write_term(collate(C), Indent, Options)--> !,
 1300        sql_write_term(C, Indent, Options).
 1301
 1302sql_write_term(collation(C), Indent, Options)--> !,
 1303        sql_write_term(C, Indent, Options).
 1304
 1305sql_write_term(collated_factor(F, C), Indent, Options)-->
 1306        {option(dbms('PostgreSQL'), Options) ; option(suppress_collations, Options)},
 1307        !,
 1308        sql_write_term(F, Indent, Options),
 1309        sql_write_and_strip_comments(C, Indent, Options, _Collation, Comments),
 1310        sql_end_comments(Comments, Indent, Options).
 1311        % TBD: All collations for 'PostgreSQL' are just ignored.
 1312        %sql_write_term(Collation, Indent, Options).
 1313
 1314
 1315sql_write_term(collated_factor(F, C), Indent, Options)-->!,
 1316        sql_write_term(F, Indent, Options),
 1317        sql_emit_token(' COLLATE ', [], keyword, Options),
 1318        sql_write_term(C, Indent, Options).
 1319
 1320sql_write_term(sort_column(C), Indent, Options)--> !,
 1321        sql_write_term(C, Indent, Options).
 1322sql_write_term(index(I), Indent, Options)--> !, % Should we normalize this?
 1323        sql_write_term(I, Indent, Options).
 1324sql_write_term(sort_expression(Expression), Indent, Options)--> !,
 1325        sql_write_term(Expression, Indent, Options).
 1326
 1327
 1328sql_write_term(sort_key(Key, Collate, Order), Indent, Options)--> !,
 1329        sql_write_term(Key, Indent, Options),
 1330        ( {Collate == {no_collation}} ->
 1331            {true}
 1332        ; {otherwise}->
 1333            sql_emit_token(' COLLATE ', [], keyword, Options),
 1334            sql_write_term(Collate, Indent, Options)
 1335        ),
 1336        ( {Order == {no_order}} ->
 1337            ( {option(normalize, Options)}->
 1338                sql_emit_token(' ASC ', [], keyword, Options)
 1339            ; {otherwise}->
 1340                {true}
 1341            )
 1342        ; {otherwise}->
 1343            sql_write_term(Order, Indent, Options)
 1344        ).
 1345
 1346sql_write_term(desc, _, Options)-->!, sql_emit_token(' DESC ', [], keyword, Options).
 1347sql_write_term(asc, _, Options)-->!, sql_emit_token(' ASC ', [], keyword, Options).
 1348
 1349sql_write_term(search(S), Indent, Options)--> !,
 1350        sql_write_term(S, Indent, Options).
 1351
 1352sql_write_term(in(Value, List), Indent, Options)--> !,
 1353        sql_write_term(Value, Indent, Options),
 1354        sql_emit_token(' IN ', [], operator, Options),
 1355        sql_write_term(List, Indent, Options).
 1356
 1357sql_write_term(not_in(Value, List), Indent, Options)--> !,
 1358        sql_write_term(Value, Indent, Options),
 1359        sql_emit_token(' NOT IN ', [], operator, Options),
 1360        sql_write_term(List, Indent, Options).
 1361
 1362sql_write_term(between(Value, Min, Max), Indent, Options)--> !,
 1363        sql_write_term(Value, Indent, Options),
 1364        sql_emit_token(' BETWEEN ', [], operator, Options),
 1365        sql_write_term(Min, Indent, Options),
 1366        sql_emit_token(' AND ', [], operator, Options),
 1367        sql_write_term(Max, Indent, Options).
 1368
 1369sql_write_term(list(Values), Indent, Options)--> !,
 1370        sql_emit_token('(', [], punctuation, Options),
 1371        sql_write_list_compact(Values, Indent, Options),
 1372        sql_emit_token(')', [], punctuation, Options).
 1373
 1374sql_write_term(join_type(Type), Indent, Options)--> !,
 1375        sql_write_term(Type, Indent, Options).
 1376
 1377sql_write_term(inner, _, Options)--> !,
 1378        sql_emit_token('INNER JOIN ', [], operator, Options).
 1379sql_write_term(outer(T1), Indent, Options)--> !,
 1380        sql_write_term(T1, Indent, Options),
 1381        sql_emit_token(' OUTER JOIN ', [], operator, Options).
 1382
 1383sql_write_term(left, _, Options)--> !, sql_emit_token('LEFT', [], operator, Options).
 1384sql_write_term(right, _, Options)--> !, sql_emit_token('RIGHT', [], operator, Options).
 1385sql_write_term(full, _, Options)--> !, sql_emit_token('FULL', [], operator, Options).
 1386
 1387sql_write_term(is_not_null(X), Indent, Options)--> !,
 1388        sql_write_term(X, Indent, Options),
 1389        sql_emit_token(' IS NOT NULL', [], operator, Options).
 1390
 1391sql_write_term(is_null(X), Indent, Options)--> !,
 1392        sql_write_term(X, Indent, Options),
 1393        sql_emit_token(' IS NULL', [], operator, Options).
 1394
 1395sql_write_term(union(LHS, RHS, Corresponding), Indent, Options)--> !,
 1396        ( {option(unions(left), Options)}->
 1397            sql_emit_token('    ', [], punctuation, Options),
 1398            tab_stop(S),
 1399            sql_write_term(LHS, S, Options),
 1400            sql_emit_token('~n~wUNION~n~w', [Indent, Indent], keyword, Options),
 1401            ( {option(unroll_unions(true), Options),
 1402              RHS = union(_, _)}->
 1403                sql_write_term(RHS, Indent, Options)
 1404            ; {otherwise}->
 1405                sql_emit_token('    ', [], punctuation, Options),
 1406                sql_write_term(RHS, S, Options)
 1407            )
 1408        ; {otherwise}->
 1409            tab_stop(S),
 1410            sql_write_term(LHS, Indent, Options),
 1411            sql_emit_token('~n~w  UNION~n~w', [S, S], keyword, Options),
 1412            sql_write_term(RHS, Indent, Options)
 1413        ),
 1414        ( {Corresponding == {no_corresponding}}->
 1415            {true}
 1416        ; {otherwise}->
 1417            sql_write_term(Corresponding, Indent, Options)
 1418        ).
 1419
 1420sql_write_term(union_all(LHS, RHS, Corresponding), Indent, Options)--> !,
 1421        tab_stop(S),
 1422        sql_write_term(LHS, Indent, Options),
 1423        sql_emit_token('~n~w  UNION', [S], keyword, Options),
 1424        sql_emit_token(' ALL~n~w', [S], operator, Options),
 1425        sql_write_term(RHS, Indent, Options),
 1426        ( {Corresponding == {no_corresponding}}->
 1427            {true}
 1428        ; {otherwise}->
 1429            sql_write_term(Corresponding, Indent, Options)
 1430        ).
 1431
 1432sql_write_term(except(LHS, RHS, Corresponding), Indent, Options)--> !,
 1433        tab_stop(S),
 1434        sql_write_term(LHS, Indent, Options),
 1435        sql_emit_token('~n~w  EXCEPT~n~w', [S, S], keyword, Options),
 1436        sql_write_term(RHS, Indent, Options),
 1437        ( {Corresponding == {no_corresponding}}->
 1438            {true}
 1439        ; {otherwise}->
 1440            sql_write_term(Corresponding, Indent, Options)
 1441        ).
 1442
 1443sql_write_term(except_all(LHS, RHS, Corresponding), Indent, Options)--> !,
 1444        tab_stop(S),
 1445        sql_write_term(LHS, Indent, Options),
 1446        sql_emit_token('~n~w  EXCEPT', [S], keyword, Options),
 1447        sql_emit_token(' ALL~n~w', [S], operator, Options),
 1448        sql_write_term(RHS, Indent, Options),
 1449        ( {Corresponding == {no_corresponding}}->
 1450            {true}
 1451        ; {otherwise}->
 1452            sql_write_term(Corresponding, Indent, Options)
 1453        ).
 1454
 1455sql_write_term({no_with}, _, _)--> !.
 1456sql_write_term(with(schemabinding), _, Options)--> !,
 1457        ( {option(dbms('Microsoft SQL Server'), Options)}->
 1458            sql_emit_token(' WITH SCHEMABINDING', [], keyword, Options)
 1459        ; {otherwise}->
 1460            {true}
 1461        ).
 1462
 1463sql_write_term({null}, _Indent, Options)--> !,
 1464        sql_emit_token('NULL', [], null, Options).
 1465
 1466sql_write_term(join, _Indent, Options)--> !,
 1467        sql_emit_token('JOIN', [], keyword, Options).
 1468
 1469sql_write_term(Atom, _Indent, Options)-->
 1470        {atomic(Atom)}, !,
 1471        sql_emit_token('~w', [Atom], unknown, Options).
 1472
 1473sql_write_term({Foo}, _, _)-->
 1474        {throw(sql_write_curly(Foo))}.
 1475sql_write_term(Other, _, _)-->
 1476        {functor(Other, Functor, Arity),
 1477         throw(sql_write_term(Functor/Arity))}.
 1478
 1479sql_write_list_compact(Comments:List, Indent, Options)--> !,
 1480        sql_write_comments(Comments, Indent, Options),
 1481        sql_write_list_compact(List, Indent, Options),
 1482        sql_end_comment(Comments, Indent, Options).
 1483
 1484sql_write_list_compact([Tail], Indent, Options)--> !,
 1485        sql_write_term(Tail, Indent, Options).
 1486
 1487sql_write_list_compact([Head|Tail], Indent, Options)--> !,
 1488        sql_write_term(Head, Indent, Options),
 1489        sql_emit_token(', ', [], comma, Options),
 1490        sql_write_list_compact(Tail, Indent, Options).
 1491
 1492sql_write_list_with_newlines(Comments:List, Indent, Options)--> !,
 1493        sql_write_comments(Comments, Indent, Options),
 1494        sql_write_list_with_newlines(List, Indent, Options),
 1495        sql_end_comment(Comments, Indent, Options).
 1496
 1497sql_write_list_with_newlines(List, _ExistingIndent, Options)-->
 1498        tab_stop(S),
 1499        sql_write_list_with_newlines_1(List, S, Options).
 1500
 1501sql_write_list_with_newlines_1(Comments:List, Indent, Options)--> !,
 1502        sql_write_comments(Comments, Indent, Options),
 1503        sql_write_list_with_newlines(List, Indent, Options),
 1504        sql_end_comment(Comments, Indent, Options).
 1505
 1506sql_write_list_with_newlines_1([Tail], Indent, Options)--> !,
 1507        sql_write_term(Tail, Indent, Options).
 1508
 1509sql_write_list_with_newlines_1([Head|Tail], Indent, Options)--> !,
 1510        sql_write_term(Head, Indent, Options),
 1511        sql_emit_token(',~n~w', [Indent], comma, Options),
 1512        sql_write_list_with_newlines_1(Tail, Indent, Options).
 1513
 1514sql_write_list_with_newlines_and_no_commas(Comments:List, Indent, Options)--> !,
 1515        sql_write_comments(Comments, Indent, Options),
 1516        sql_write_list_with_newlines_and_no_commas(List, Indent, Options),
 1517        sql_end_comment(Comments, Indent, Options).
 1518
 1519sql_write_list_with_newlines_and_no_commas(List, _ExistingIndent, Options)-->
 1520        tab_stop(S),
 1521        sql_write_list_with_newlines_and_no_commas_1(List, S, Options).
 1522
 1523sql_write_list_with_newlines_and_no_commas_1(Comments:List, Indent, Options)--> !,
 1524        sql_write_comments(Comments, Indent, Options),
 1525        sql_write_list_with_newlines_and_no_commas(List, Indent, Options),
 1526        sql_end_comment(Comments, Indent, Options).
 1527
 1528sql_write_list_with_newlines_and_no_commas_1([Tail], Indent, Options)--> !,
 1529        sql_write_term(Tail, Indent, Options).
 1530
 1531sql_write_list_with_newlines_and_no_commas_1([Head|Tail], Indent, Options)--> !,
 1532        sql_write_term(Head, Indent, Options),
 1533        sql_emit_token('~n~w', [Indent], punctuation, Options),
 1534        sql_write_list_with_newlines_and_no_commas_1(Tail, Indent, Options).
 1535
 1536sql_write_and_strip_comments(Comments:Term, Indent, Options, X, [Comments|Y])-->
 1537        !,
 1538        sql_write_comments(Comments, Indent, Options),
 1539        sql_write_and_strip_comments(Term, Indent, Options, X, Y).
 1540
 1541sql_write_and_strip_comments(Term, _Indent, _Options, Term, [])--> [].
 1542
 1543
 1544sql_write_comments(meta(Comments, Errors), Indent, Options)--> !,
 1545        ( {Errors == {null}} ->
 1546            {true}
 1547        ; {option(errors(ErrorMode), Options)}->
 1548            ( {ErrorMode == ansi} ->
 1549                sql_emit_token('~A', [[foreground-red]], machinery, Options)
 1550            ; {ErrorMode == html} ->
 1551                {format_sql_error(Errors, Index, Atom)},
 1552                ( {Index == {null}} ->
 1553                    {format(atom(Token), '<span class="error" title="~w">', [Atom])},
 1554                    sql_append_raw_token(Token)
 1555                ; {otherwise}->
 1556                    {format(atom(Token), '<span class="error error_~w" data-index="error_~w" title="~w" onMouseOver="mouseOver(event)" onMouseOut="mouseOut(event)" onClick="mouseClick(event)">', [Index, Index, Atom])},
 1557                    sql_append_raw_token(Token)
 1558                )
 1559            ; {otherwise}->
 1560                {true}
 1561            )
 1562        ; {otherwise}->
 1563            {true}
 1564        ),
 1565        sql_write_comments_1(Comments, Indent, Options).
 1566
 1567sql_write_comments_1([], _Indent, _Options)--> [].
 1568sql_write_comments_1([Comment|Comments], Indent, Options)-->
 1569        sql_write_comment(Comment, Indent, Options),
 1570        sql_write_comments_1(Comments, Indent, Options).
 1571
 1572format_sql_error(type_mismatch(I, A, B), I, Atom):-
 1573        !,
 1574        format(atom(Atom), 'Type mismatch between ~w and ~w', [A, B]).
 1575
 1576format_sql_error(order_by(top_level), {null}, 'ORDER BY is meaningless in the top level expression'):- !.
 1577format_sql_error(coalesce(null_argument), {null}, 'NULL as an argument to COALESCE() is meaningless'):- !.
 1578format_sql_error(order(having, order_by), {null}, 'HAVING clause should follow ORDER BY clause'):- !.
 1579format_sql_error(sql_escape, {null}, 'Escape from SQL with { fn ... }'):- !.
 1580format_sql_error(superfluous_quote(X), {null}, Message):- !, format(atom(Message), '~w does not require quoting here. It is quoted in the original source', [X]).
 1581format_sql_error(percent, {null}, 'PERCENT clause used, but has no effect in SQL2005 and greater').
 1582format_sql_error(for_clause, {null}, 'FOR clause?'):- !.
 1583format_sql_error(deprecated(D, R), {null}, Message):- !, format(atom(Message), 'Deprecated function ~w: Use ~w instead', [D, R]).
 1584format_sql_error(null_value, {null}, 'NULL is not actually allowed here. Use CAST(NULL AS <some type>)'):- !.
 1585
 1586format_sql_error(A, {null}, Atom):-
 1587        format(atom(Atom), 'Unknown error: ~q', [A]).
 1588
 1589sql_write_comment(comment(long, Codes), _Indent, Options)--> !,
 1590        sql_emit_token('/* ~s */ ', [Codes], comment, Options).
 1591
 1592sql_write_comment(comment(short, Codes), Indent, Options)--> !,
 1593        sql_emit_token('-- ~s~n~w', [Codes, Indent], comment, Options).
 1594
 1595sql_end_comments([], _Indent, _Options)--> !.
 1596sql_end_comments([Comment|Comments], Indent, Options)-->
 1597        sql_end_comment(Comment, Indent, Options),
 1598        sql_end_comments(Comments, Indent, Options).
 1599
 1600sql_end_comment(meta(_, Errors), _Indent, Options)--> !,
 1601        ( {Errors == {null}} ->
 1602            {true}
 1603        ; {option(errors(ErrorMode), Options)}->
 1604            ( {ErrorMode == ansi} ->
 1605                {format(atom(Code), '~A', [{reset}])},
 1606                sql_append_raw_token(Code)
 1607            ; {ErrorMode == html} ->
 1608                sql_append_raw_token('</span>')
 1609            ; {otherwise}->
 1610                {true}
 1611            )
 1612        ; {otherwise}->
 1613            {true}
 1614        ).
 1615
 1616
 1617sql_write_type(Comments:Type, Indent, Options)--> !,
 1618        sql_write_comments(Comments, Indent, Options),
 1619        sql_write_type(Type, Indent, Options),
 1620        sql_end_comment(Comments, Indent, Options).
 1621
 1622sql_write_type(varchar(L), Indent, Options)--> !,
 1623        ( {L == {unknown}} ->
 1624            sql_emit_token('VARCHAR', [], keyword, Options)
 1625        ; {otherwise}->
 1626            sql_emit_token('VARCHAR', [], keyword, Options),
 1627            sql_emit_token('(', [], punctuation, Options),
 1628            sql_write_term(L, Indent, Options),
 1629            sql_emit_token(')', [], punctuation, Options)
 1630        ).
 1631
 1632sql_write_type(int, _Indent, Options)--> !,
 1633        sql_emit_token('INTEGER', [], keyword, Options).
 1634sql_write_type(smallint, _Indent, Options)--> !,
 1635        sql_emit_token('SMALLINT', [], keyword, Options).
 1636sql_write_type(tinyint, _Indent, Options)--> !,
 1637        ( {option(dbms('PostgreSQL'), Options)}->
 1638            % 'PostgreSQL' does not have a TINYINT (which is 1 byte). Use SMALLINT (2 bytes) instead
 1639            sql_emit_token('SMALLINT', [], keyword, Options)
 1640        ; {otherwise}->
 1641            sql_emit_token('TINYINT', [], keyword, Options)
 1642        ).
 1643
 1644sql_write_type(decimal(Precision, Scale), Indent, Options)--> !,
 1645        sql_emit_token('DECIMAL', [], keyword, Options),
 1646        ( {Precision == {no_precision}} ->
 1647            {true}
 1648        ; {otherwise}->
 1649          sql_emit_token('(', [], punctuation, Options),
 1650          sql_write_term(Precision, Indent, Options),
 1651          ( {Scale == {no_scale}} ->
 1652              {true}
 1653          ; {otherwise}->
 1654              sql_emit_token(',', [], comma, Options),
 1655              sql_write_term(Scale, Indent, Options)
 1656          ),
 1657          sql_emit_token(')', [], punctuation, Options)
 1658        ).
 1659
 1660sql_write_type(float(Precision), Indent, Options)--> !,
 1661        ( {Precision == {no_precision}}->
 1662            sql_emit_token('FLOAT', [], keyword, Options)
 1663        ; {otherwise}->
 1664            sql_emit_token('FLOAT', [], keyword, Options),
 1665            sql_emit_token('(', [], punctuation, Options),
 1666            sql_write_term(Precision, Indent, Options),
 1667            sql_emit_token(')', [], punctuation, Options)
 1668        ).
 1669
 1670sql_write_type(real, _Indent, Options)--> !,
 1671        sql_emit_token('REAL', [], keyword, Options).
 1672
 1673sql_write_type(double(Precision), Indent, Options)--> !,
 1674        sql_emit_token('DOUBLE', [], keyword, Options),
 1675        sql_emit_token('(', [], punctuation, Options),
 1676        sql_write_term(Precision, Indent, Options),
 1677        sql_emit_token(')', [], punctuation, Options).
 1678
 1679sql_write_type(datetime, _Indent, Options)--> !, % Should normalize
 1680        ( {option(dbms('PostgreSQL'), Options)}->
 1681            sql_emit_token('TIMESTAMP', [], keyword, Options)
 1682        ; {otherwise}->
 1683            sql_emit_token('DATETIME', [], keyword, Options)
 1684        ).
 1685
 1686sql_write_type(date, _Indent, Options)--> !, % Should normalize
 1687        sql_emit_token('DATE', [], keyword, Options).
 1688
 1689
 1690sql_list_length(_:X, Y):- !, sql_list_length(X, Y).
 1691sql_list_length([], 0):- !.
 1692sql_list_length([_A|B], N):-
 1693        sql_list_length(B, NN),
 1694        N is NN+1.
 1695
 1696normalize_date_type(day, day).
 1697normalize_date_type(dd, day).
 1698normalize_date_type(wk, week).
 1699normalize_date_type(week, week).
 1700normalize_date_type(second, second).
 1701normalize_date_type(weekday, day_of_week).
 1702normalize_date_type(year, year).
 1703normalize_date_type(month, month).
 1704
 1705sql_write_literal(Value, Options)-->
 1706        {atom_codes(Value, Codes),
 1707         sql_quote_codes(QuotedCodes, Codes, [])},
 1708        sql_emit_token('~s', [QuotedCodes], literal, Options).
 1709
 1710
 1711sql_quote_codes([], [], []):- !.
 1712sql_quote_codes([39, 39|Codes])-->
 1713        [39], !,
 1714        sql_quote_codes(Codes).
 1715sql_quote_codes([Code|Codes])-->
 1716        [Code],
 1717        sql_quote_codes(Codes).
 1718
 1719% Quirk. SQL Server allows implicit cast of 0 to a datetime to get 1/1/1901.
 1720sql_write_date(X, Indent, Options)-->
 1721        sql_write_and_strip_comments(X, Indent, Options, Date, Comments),
 1722        ( {Date == 0}->
 1723            sql_emit_token('CAST', [], function, Options),
 1724            sql_emit_token('(', [], punctuation, Options),
 1725            sql_emit_token('\'Jan 1 1901\' ', [], literal, Options),
 1726            sql_emit_token('AS timestamp without time zone', [], keyword, Options),
 1727            sql_emit_token(')', [], punctuation, Options)
 1728        ; {otherwise}->
 1729            sql_write_term(Date, Indent, Options)
 1730        ),
 1731        sql_end_comments(Comments, Indent, Options).
 1732
 1733should_suppress_collation(X):-
 1734        strip_sql_comments(X, predicate(comparison(_, Lhs, Rhs))),
 1735        ( Lhs = element(collated_factor(_, _))->
 1736            true
 1737        ; Rhs = element(collated_factor(_, _))->
 1738            true
 1739        ).
 1740
 1741should_suppress_condition(X):-
 1742        strip_sql_comments(X, predicate(comparison(_, element(1), element(1)))), !