1/* -*- Mode: Prolog -*- */
    2   
    3:- module(sql_compiler,
    4          [
    5           plterm_to_sqlterm/3,
    6           print_sqlterm/1,
    7           sqlterm2atom/2,
    8           load_schema_defs/1,
    9           get_type/2,
   10           rewrite_query/2,
   11           op(1150,xfy,(<-)),
   12           op(1200,xfy,(::))
   13          ]).   14
   15:- multifile relation/2,relation/3,attribute/4,unique/2,schema/1.   16:- discontiguous relation/2,relation/3,attribute/4,unique/2,schema/1.   17:- discontiguous view/2,view/3,sql_expand/1.   18:- multifile view/2,view/3,sql_expand/1,schema_dbname/2.   19
   20% CJM:
   21% sqlschema_connection(?Schema,?Rdb)
   22% globals for database handles
   23:- multifile sqlschema_connection/2.   24:- dynamic sqlschema_connection/2.   25
   26% CJM: required by SWI-Prolog
   27:- op(900,fy,not).		% Draxler uses this. Should we convert wholesale to '\+' ?
   28:- op(1150,xfy,(<-)).   29:- op(1200,xfy,(::)).   30%:- op(800,xfy,in).
 view(?Head, ?Body)
SQL rewrite - Head is rewritten as Body. Can also be written as Head <- Body
   36:- dynamic view/2,view/3,schema_dbname/2.
 expand_relation_name(+R, ?RX)
prefixes table name with database name if schema_dbname/2 is set for the schema to which R belongs
   41expand_relation_name(R,RX):-
   42        relation(R,A),
   43        clause(relation(R,A),_,Clause),
   44        clause_property(Clause,file(File)),
   45        clause(schema(S),_,SClause),
   46        clause_property(SClause,file(File)),
   47        schema_dbname(S,DB),
   48        sformat(RX,'~w.~w',[DB,R]),
   49        !.
   50expand_relation_name(R,R).
   51
   52
   53/*
   54% Views: declared analagous to clauses with <- not :-
   55% rewrite to a view/2 fact
   56system:term_expansion( (Mod:Head <- Body),
   57                     [   sql_compiler:view(Head,Body),
   58                         (   Mod:Head :- getrdb(Rdb),rdb_query(Rdb,Head,Head))]):- !.
   59% TODO: only do this when asked
   60*/
   61system:term_expansion( (_:Head <- Body :: Where),
   62                     sql_compiler:view(Head,Body,Where)).
   63system:term_expansion( (_:Head <- Body),
   64                     sql_compiler:view(Head,Body)).
   65system:term_expansion( (Head <- Body),
   66                     sql_compiler:view(Head,Body)).
 load_schema_defs(+SchemaFileSpec)
loads a schema into memory, allowing sql_compiler to make use of it for plterm_to_sqlterm/3

Example: load_schema_defs(bio('sql_schema/schema_enscore44'))

   74load_schema_defs(File):-
   75        consult(File).
 plterm_to_sqlterm(+ProjectionTerm, +DatabaseGoal, ?SQLQueryTerm)
rewrite a prolog goal as a SQL Term. The SQL term can then be translated via sqlterm2atom/2
Arguments:
ProjectionTerm-
DatabaseGoal-
SQLQueryTerm-
   87:- module_transparent(plterm_to_sqlterm/3).   88plterm_to_sqlterm(ProjectionTerm0,G,SQLQueryTerm):-
   89        debug(sql_compiler,'original proj ~w',ProjectionTerm0),
   90        debug(sql_compiler,'original goal ~w',G),
   91        
   92        % first of all we rewrite the query, this is essentially a view mechanism.
   93        % it can take advantage of view/2 predicates, or it can use normal prolog program
   94        % rules in the rewriting process.
   95        % it is important that we rewrite before we copy the term, because rewriting
   96        % may introduce unification to skolem terms in the projection. These must all match up
   97        % the resulting row values. As an example see intron/1 in genome_db.
   98        rewrite_query(G,G1),
   99        !,
  100        debug(sql_compiler,'rewritten_as: ~w',G1),
  101
  102        % we copy terms because otherwise the prolog variables will be unified with sql_compiler
  103        % variable terms
  104        copy_term((ProjectionTerm0,G1),(ProjectionTerm,G2)),
  105        debug(sql_compiler,'copied to: ~w',G2),
  106
  107        % merge terms based on unique database keys
  108        optimize_query_all(G2,G3),
  109        debug(sql_compiler,'optimized as ~w',G3),
  110        
  111        % unify SqlQueryTerm with the translation results
  112        translate(ProjectionTerm,G3,SQLQueryTerm),
  113        !.
  114
  115plterm_allterms((G1,G2),GL):-
  116        !,
  117        plterm_allterms(G1,G1L),
  118        plterm_allterms(G2,G2L),
  119        append(G1L,G2L,GL).
  120plterm_allterms((G1;G2),GL):-
  121        !,
  122        plterm_allterms(G1,G1L),
  123        plterm_allterms(G2,G2L),
  124        append(G1L,G2L,GL).
  125plterm_allterms(not(G),[G|GL]):-
  126        !,
  127        plterm_allterms(G,GL).
  128plterm_allterms(G,[G]).
  129
  130optimize_query_vars(G):-
  131        plterm_allterms(G,GL),
  132        length(GL,Num),
  133        numlist(1,Num,NumList),
  134        findall(X-Y,(member(X,NumList),member(Y,NumList)),IndexPairs),
  135        unify_pairs(IndexPairs,GL).
  136
  137unify_pairs([],_).
  138unify_pairs([X-Y|NL],GL):-
  139        nth1(X,GL,G1),
  140        nth1(Y,GL,G2),
  141        (   G1\==G2,
  142            unify_plterm1(G1,G2)
  143        ->  true
  144        ;   true),
  145        unify_pairs(NL,GL).
 optimize_query_all(+G, ?G2)
[CJM] - added to account for the fact that multiple iterations over optimize_query/2 are required: eg merging two referenced terms affects terms referencing these as FKs. see for example schema_go, we may merge two dbxrefds after attempting to merge gene_product it is only after the dbxrefd terms are merged that the dbxref_id FK var is unified TODO: optimize truncated predicates: eg foo(A,B) is treated as foo(A,B,_) - but they are currently treated as different by optimization TODO: better to optimize after conversion to Disjunction? Or both? after disjunction we have translated prolog variables to $var$s
  156optimize_query_all(G,G2):-
  157        %optimize_query_vars(G),
  158        optimize_query(G,G1),
  159        (   G == G1             % identical if optimizing yielded nothing
  160        ->  G2=G                % base case
  161        ;   optimize_query_all(G1,G2)). % keep on optimizing
 optimize_query(+Goal, ?GoalOpt)
if two terms can be proved to unify to the same row, merge them unifying vars :- mode optimize_query(+,?) is det.
  167optimize_query((A=B,Gs),Go):- % e.g. (a=a,...)
  168	%nonvar(A),
  169	%nonvar(B),
  170	\+compound(A), 
  171	\+compound(B),
  172	A==B,
  173        !,
  174        optimize_query(Gs,Go).
  175optimize_query((G,Gs),Go):-
  176        unify_plterm(G,Gs),
  177        !,
  178        optimize_query(Gs,Go).
  179optimize_query((G;Gs),(G2;Gs2)):-
  180        !,
  181        optimize_query(G,G2),
  182        optimize_query(Gs,Gs2).
  183optimize_query((G,Gs),Gm):-
  184        !,
  185        optimize_query(Gs,Gs1),
  186        debug(sql_compiler,'optimized: ~w -> ~w.~nNow merging ~w ~w',[Gs,Gs1,G,Gs1]),
  187        merge_goals(G,Gs1,Gm).
  188optimize_query(not(G),not(G2)):-
  189        !,
  190        optimize_query(G,G2).
  191optimize_query(X is T,X is T2):- % e.g. count
  192        T=..[F,V,G],
  193        aggregate_functor(F,_),
  194        !,
  195        optimize_query(G,G2),
  196        T2=..[F,V,G2].
  197optimize_query(X,X).
 unify_plterm(+TermToUnify, ?Term) is semidet
example: unify_plterm(person(A,B),(person(C,D),person(A,C))) ==> B=C (if arg1 of person/2 is unique) :- mode unify_plterm(+,?) is semidet.
  203unify_plterm(G,(G2,_)):-
  204        unify_plterm1(G,G2),    % no need to recurse further
  205        !.
  206unify_plterm(G,(_,Gs)):-
  207        !,
  208        unify_plterm(G,Gs).
  209%unify_plterm(G,X is F):-
  210%        F=..[_,_,Gs],
  211%        !,
  212%        unify_plterm(G,Gs).
  213unify_plterm(_G,not(_Gs)):-
  214        !,
  215        fail.
  216        %unify_plterm(G,Gs).
  217unify_plterm(G,G2):-
  218        unify_plterm1(G,G2),
  219        !.
  220
  221% use uniqueness constraint to reduce two terms to one
  222unify_plterm1(G,G2):-
  223        functor(G,F,Arity),
  224        functor(G2,F,Arity),
  225        unique(F,Attr),
  226        \+ Attr = [_|_],
  227        attribute(AttrOrd,F,Attr,_),
  228        arg(AttrOrd,G,V1),
  229        arg(AttrOrd,G2,V2),
  230        V1 == V2,               % unique key is equivalent
  231        !,                      % only succeed once
  232        debug(sql_compiler,'unifying based on unique key ~w: ~w = ~w',[Attr,G,G2]),
  233        G=G2.                   % unify if equivalent
  234
  235% uniqueness constraint with multiple keys
  236unify_plterm1(G,G2):-
  237        functor(G,F,Arity),
  238        functor(G2,F,Arity),
  239        unique(F,Attrs),
  240        Attrs = [_|_],
  241        forall(member(Attr,Attrs),
  242               (   attribute(AttrOrd,F,Attr,_),
  243                   arg(AttrOrd,G,V1),
  244                   arg(AttrOrd,G2,V2),
  245                   V1 == V2)),  % unique key is equivalent
  246        !,                      % only succeed once
  247        debug(sql_compiler,'unifying based on unique keyset ~w: ~w = ~w',[Attrs,G,G2]),
  248        G=G2.                   % unify if equivalent
  249
  250
  251% ========================================
  252% views
  253% ========================================
  254
  255% todo: make sure that datalog predicates and sql relations are not confused
  256% e.g. see genome_bridge_from_seqfeature
  257
  258:- module_transparent rewrite_query/2.
 rewrite_query(+GoalIn, ?GoalOut) is det
  260rewrite_query(call(_),_):- !,fail.
  261rewrite_query(aggregate(count,X,G,Num),	is(Num,count_distinct(X,G))) :-
  262	!.
  263rewrite_query((G,Gs),Gm):-
  264        !,
  265        rewrite_query(G,G1),
  266        rewrite_query(Gs,Gs1),
  267        merge_goals(G1,Gs1,Gm).
  268rewrite_query((G;Gs),(G1;Gs1)):-
  269        !,
  270        rewrite_query(G,G1),
  271        rewrite_query(Gs,Gs1).
  272rewrite_query(_:G,G2):- % todo - or clauses from multiple views...
  273        % will not do full disjunction under some circumstances; see below
  274        view(G,B,Where), % /3
  275        Where,
  276        !,
  277        rewrite_query(B,G2),
  278        !.
  279%rewrite_query(_:G,G2):- % todo - or clauses from multiple views...
  280%        % will not do full disjunction under some circumstances; see below
  281%        view(G,B,Where),
  282%        trace,
  283%        setof(G-B,Where,GBs),
  284%        unify_keys(G,GBs,Bs),
  285%        !,
  286%        list_to_disj(Bs,BDisj),
  287%        rewrite_query(BDisj,G2),
  288%        !.
  289rewrite_query(_:G,G2):- % todo - or clauses from multiple views...
  290        rewrite_query(G,G2),
  291        !.
  292%rewrite_query(Head,G2):- % todo - or clauses from multiple views...
  293%	view(Head,Body),
  294%	!,
  295%	rewrite_query(Body,G2).
  296rewrite_query(Head,G2):- % proper disjoint - expmntl
  297        Head=..[_|Args],
  298	functor(Head,F,Arity),
  299	functor(HeadC,F,Arity),
  300	HeadC=..[_|ArgsC],
  301	% grab all relevant rules. we will rewrite these
  302	% foo(X, a) <- a(X) ==> foo(C,D) <- D=a,a(C).
  303	% the head is all variables, unification is explicit in the body
  304        setof(HeadC <- (ArgsC=Args,Body),
  305	      sql_compiler:view(Head, Body),
  306	      Rules1),
  307	rules_to_normal_form(Rules1,Rules),
  308	setof(Body,member(Head<-Body,Rules),DisjList),
  309				%setof(B,sql_compiler:view(G,B),Bs),
  310        list_to_disj(DisjList,BDisj),
  311        rewrite_query(BDisj,G2),
  312        !.
  313rewrite_query(_:G,G2):- % ignore module
  314        view(G,Gz),
  315        rewrite_query(Gz,G2),
  316        !.
  317rewrite_query(X is G,X is G2):-
  318        !,
  319        rewrite_query(G,G2).
  320rewrite_query(G,G2):-
  321        G=..[F,V,X],
  322        aggregate_functor(F,_), % what to do about free variables in rewritten term?
  323        !,
  324        rewrite_query(X,X2),
  325        G2=..[F,V,X2].
  326rewrite_query(not(G),not(G2)):-
  327        !,
  328        rewrite_query(G,G2).
  329rewrite_query(\+(G),not(G2)):-
  330        !,
  331        rewrite_query(G,G2).
  332
  333rewrite_query(V^G,V^G2):-
  334        !,
  335        rewrite_query(G,G2).
  336
  337% rewrite prolog predicates as views.
  338% first of all, the simple non-disjunctive case:
  339rewrite_query(H,G2):-
  340        H \= _^_,               % guard against ^/2 as predicate; we use as existential
  341        % note that the rewritten clause must either
  342        %  - be an exported module predicate
  343        %  - in the user module
  344        context_module(Mod),
  345        debug(sql_compiler,'  testing if clause(~w,_) in module: ~w ?',[H,Mod]),
  346	% first we check there is only one matching clause:
  347	% if there are more, we use the disjunction version below
  348        catch(findall(B,clause(H,B),[_]), % prolog predicate
  349              _,
  350              fail),
  351        catch(setof(B,clause(H,B),Bs), % prolog predicate
  352              _E,
  353              fail),
  354        debug(sql_compiler,'  clause: ~w ',[Bs]),
  355        !,
  356        list_to_disj(Bs,BDisj),
  357        % TODO: check for recursive predicates
  358        debug(sql_compiler,'rewrite (single clause): ~w => ~w',[H,BDisj]),
  359        rewrite_query(BDisj,G2).
  360% treat prolog predicates as views
  361% disjunctive case
  362rewrite_query(Head,G2):- 
  363        Head \= _^_,               % guard against ^/2 as predicate; we use as existential
  364	% rewrites multiple matching clauses as disjunction
  365	% DOES NOT WORK FOR SKOLEMS: assumes Head args are non-compound
  366        context_module(Mod),
  367        debug(sql_compiler,'  is_clause: ~w in module: ~w ?',[Head,Mod]),
  368        % fetch all possibilities when H contains
  369        % free variables not in B.
  370        % E.g. foo(X,a):- a(X). foo(X,b):- b(X).
  371        % What we get is [(a(X),Arg2=a),(b(X),Arg2=b)] 
  372        Head=..[_|Args],
  373	functor(Head,F,Arity),
  374	\+relation(F,Arity),
  375	functor(HeadC,F,Arity),
  376	HeadC=..[_|ArgsC],
  377	% grab all relevant rules. we will rewrite these
  378	% foo(X, a) <- a(X) ==> foo(C,D) <- D=a,a(C).
  379	% the head is all variables, unification is explicit in the body
  380        catch(setof(HeadC <- (ArgsC=Args,Body),
  381		    (	clause(Head, Body)),
  382		    Rules1),
  383	      _E,
  384	      fail),
  385	rules_to_normal_form(Rules1,Rules),
  386	setof(Body,Rules^member(Head<-Body,Rules),DisjList),
  387        list_to_disj(DisjList,BDisj),
  388
  389        %catch(setof(B,clause(H,B),Bs), % prolog predicate
  390        %      _E,
  391         %     fail),
  392        %debug(sql_compiler,'  clause: ~w ',[Bs]),
  393        %!,
  394        %list_to_disj(Bs,BDisj),
  395        % TODO: check for recursive predicates
  396        debug(sql_compiler,'rewrite (disjunction of clauses): ~w => ~w',[Head,BDisj]),
  397        rewrite_query(BDisj,G2).
  398rewrite_query(G,G).
 rules_to_normal_form(+RulesIn:list, ?RulesOut:list)
RulesIn = [Head<-(ArgList1=ArgList2,Body), ...] RulesOut = [Head<-NewBody, ...] where NewBody is conjunction using ,/2
  404rules_to_normal_form([],[]).
  405rules_to_normal_form([H<-(Args=Args2,Body)|T],[H<-ArgConj|T2]):-
  406	arglists_to_conj(Body,Args,Args2,ArgConj),
  407	rules_to_normal_form(T,T2).
 arglists_to_conj(+Body, +ArgList1, +ArgList2, ?Conj)
where Conj
  411arglists_to_conj(Body,[],[],Body).
  412arglists_to_conj(Body,[H1|T1],[H2|T2],TN):-
  413	var(H1),	
  414	var(H2),
  415	!,			% original head used variable in this position
  416	H1=H2,
  417	arglists_to_conj(Body,T1,T2,TN).
  418arglists_to_conj(Body,[H1|T1],[H2|T2],(H1=H2,TN)):-
  419	arglists_to_conj(Body,T1,T2,TN).
  420
  421merge_goals((H,T),G,(H,G2)):-
  422        !,
  423        merge_goals(T,G,G2).
  424merge_goals(G1,G2,(G1,G2)).
  425
  426goal_to_list((H,T),[H2|T2]):-
  427        !,
  428        goal_to_list(H,H2),
  429        goal_to_list(T,T2).
  430goal_to_list(X,[X]).
  431
  432%:- mode translate(+,+,?) is det.
  433%% translate(+ProjectionTerm,+DatabaseGoal,?SQLQueryTerm) is det
  434% Top level predicate translate/3 organizes the compilation and constructs a
  435% Prolog term representation of the SQL query.
  436% (in Ciao, this is called pl2sqlterm)
  437translate(ProjectionTerm,DatabaseGoal,SQLQueryTerm):-
  438
  439   % --- tokenize projection term and database goal ------------------------
  440        tokenize_term(DatabaseGoal,TokenDatabaseGoal),
  441        tokenize_term(ProjectionTerm,TokenProjectionTerm),
  442
  443   % --- lexical analysis: reordering of goals for disjunctive normalized form -
  444        convert_to_disj_norm_form(TokenDatabaseGoal,Disjunction),
  445	% TODO: we should do second optimization step; but we can't reuse the exist code based on prolog unification
  446        debug(sql_compiler,'disj ~w',[Disjunction]),
  447
  448        remove_unsatisfiable_clauses(Disjunction,DisjunctionOpt),
  449        
  450   % --- code generation -----------------------------------------------------
  451%	pp_prologterm(DisjunctionOpt),
  452%	trace,
  453        query_generation(DisjunctionOpt,TokenProjectionTerm,SQLQueryTerm),
  454        !,
  455        debug(sql_compiler,'Generated query: ~w',[SQLQueryTerm]).
  456translate(ProjectionTerm, DatabaseGoal, _SQLQueryTerm) :-
  457	error_message("SQL translation failed for ~q / ~q", [ProjectionTerm, DatabaseGoal]),
  458	fail.
 remove_unsatisfiable_clauses(+Disjunction:list, ?DisjunctionOptimized) is det
if we start with a prolog goal (bar(X),(foo(X,a);foo(X,b))),(wiggle(X),(foo(X,a);foo(X,b)))

and convert to disjunctive normal form we end up with 4 disjunctions, 2 of which are unsatisfiable.

we optimize here in case the RDB cannot.

added by: CJM 2009/07/20

  471remove_unsatisfiable_clauses([],[]).
  472remove_unsatisfiable_clauses([C|Cs],[C|Cs2]) :-
  473        is_clause_satisfiable(C),
  474        !,
  475        remove_unsatisfiable_clauses(Cs,Cs2).
  476remove_unsatisfiable_clauses([_|Cs],Cs2) :-
  477        !,
  478        remove_unsatisfiable_clauses(Cs,Cs2).
  479
  480is_clause_satisfiable(C) :-
  481        clause_bindings(C,Bindings),
  482        \+ ((member(V=C1,Bindings),
  483             member(V=C2,Bindings),
  484             C1\=C2)).
  485
  486clause_bindings((C1,C2),Bindings) :-
  487        !,
  488        clause_bindings(C1,Bindings1),
  489        clause_bindings(C2,Bindings2),
  490        append(Bindings1,Bindings2,Bindings).
  491clause_bindings('$var$'(V)='$const$'(C),[V=C]) :- !.
  492clause_bindings(_,[]).
 convert_to_disj_norm_form(+Goal, ?Disjunction:list)
turns original goal into disjunctive normalized form by computing a set of flat conjunctions and collecting them in a list, whose elements are assumed to be joined by disjuntions.
  505%:- mode convert_to_disj_norm_form(+,?) is det.
  506convert_to_disj_norm_form(Goal,Disjunction):-
  507   findall(Conjunction,linearize(Goal,Conjunction),Disjunction).
  508%   forall(member(Conjunction,Disjunction),
  509%	  is_conjunction(Conjunction)).
  510
  511is_conjunction(G) :-
  512	G=((A,B),C),
  513	format(user_error,'A: ~w~n',[A]),
  514	format(user_error,'B: ~w~n',[B]),
  515	format(user_error,'C: ~w~n',[C]),
  516	throw(not_a_conjunction(G)).
  517is_conjunction((_,B)) :-
  518	!,
  519	is_conjunction(B).
  520is_conjunction(_) :- !.
 linearize(+Goal, ?ConjunctionTerm)
Returns a conjunction of base goals for a complex disjunctive or conjunctive goal Yields several solutions upon backtracking for disjunctive goals
  528	% CJM NOTES:
  529	% Original Draxler code had ((a,b ; c),d) => (a, b),c [soln1] -- this is not
  530	%  correct, should be (a,b,d).
  531	% This fixes it such that we always have right-linear conjunctions
  532
  533%:- mode linearize(+,?) is nondet.
  534
  535%:- mode linearize(+,?) is nondet.
  536linearize(((A,B),C),(LinA,(LinB,LinC))):-
  537   % --- transform left-linear to right-linear conjunction (',' is associative) ---
  538   linearize(A,LinA),
  539   linearize(B,LinB),
  540   linearize(C,LinC).
  541
  542linearize((A,B),(LinA,LinB)):-
  543   A \= (_,_),
  544   % --- make sure A is not a conjunction -----------------------------------
  545   linearize(A,LinA),
  546   linearize(B,LinB).
  547
  548linearize((A;_B),LinA):-
  549   linearize(A,LinA).
  550
  551linearize((_A;B),LinB):-
  552   linearize(B,LinB).
  553
  554linearize(not A, not LinA):-
  555   linearize(A,LinA).
  556
  557linearize(\+ A, \+ LinA):-
  558   linearize(A,LinA).
  559
  560linearize(Var^A, Var^LinA):-
  561   linearize(A,LinA).
  562
  563linearize(A,A):-
  564   A \= (_,_),
  565   A \= (_;_),
  566   A \= _^_,
  567   A \= \+(_),			% CJM
  568   A \= not(_).
 tokenize_term(+Term, ?TokenizedTerm)
If Term is a
  587tokenize_term('$var$'(VarId),'$var$'(VarId)):-
  588        var(VarId), 
  589        % --- uninstantiated variable: instantiate it with unique identifier.                        
  590        gensym(var,VarId).
  591
  592tokenize_term('$var$'(VarId),'$var$'(VarId)):-
  593        nonvar(VarId),
  595	!
  595. 
  596
  597tokenize_term(Constant,'$const$'(Constant)):-
  598        nonvar(Constant),
  599        functor(Constant,_,0).
  600	%atm(Constant),   %[CIAO]
  601	%!,
  602	%atom_codes(Constant, RealConstant).
  603
  604% [CIAO has other stuff here]
  605
  606tokenize_term(Term,TokenizedTerm):-
  607        nonvar(Term),
  608        Term \= '$var$'(_),
  609        Term \= '$const$'(_),
  610        Term =.. [Functor|Arguments],
  611        Arguments \= [],
  612        maplist(tokenize_term,Arguments,TokenArguments),
  613        % [CJM] tokenize_arguments(Arguments,TokenArguments), 
  614        TokenizedTerm =.. [Functor|TokenArguments].
 query_generation(+ListOfConjunctions:list, +ProjectionTerm, ?ListOfQueries:list)
For each Conjunction translate the pair (ProjectionTerm,Conjunction) to an SQL query and connect each such query through a UNION-operator to result in the ListOfQueries.

A Conjunction consists of positive or negative subgoals. Each subgoal is translated as follows:

The arguments of a goal are translated as follows:

Special treatment of arithmetic functions:

  645                                % The following RESTRICTION holds:
  646%
  647%  - the binding of variables follows Prolog: variables are bound by positive base goals
  648%    and on the left side of the is/2 predicate - comparison operations, negated goals
  649%    and right sides of the is/2 predicate do not return variable bindings and may even 
  650%    require all arguments to be bound for a safe evaluation.
  651
  652%:- mode query_generation(+,+,?) is det.
  653query_generation([],_,[]).
  654query_generation([Conjunction|Conjunctions],ProjectionTerm,[Query|Queries]):-
  655        debug(sql_compiler,'conj ~w',[Conjunction]),
  656        debug(sql_compiler,'projterm ~w',[ProjectionTerm]),
  657        projection_term_variables(ProjectionTerm,InitDict),
  658        debug(sql_compiler,'InitDict ~w',[InitDict]),
  659        reorder_conjunction(Conjunction,ConjunctionOrdered), % CJM
  660        debug(sql_compiler,'Translating conjunction ~w',[ConjunctionOrdered]),
  661        translate_conjunction(ConjunctionOrdered,SQLFrom,SQLWhere,InitDict,Dict),
  662        debug(sql_compiler,'from=~w where=~w',[SQLFrom,SQLWhere]),
  663        debug(sql_compiler,'translating projection: ~w',[ProjectionTerm]),
  664        translate_projection(ProjectionTerm,Dict,SQLSelect),
  665        Query = query(SQLSelect,SQLFrom,SQLWhere),
  666        debug(sql_compiler,'query= ~w',[Query]),
  667        query_generation(Conjunctions,ProjectionTerm,Queries).
  668
  669% the above differs a little from Ciao, which uses check_xxx predicates for better error reporting
 reorder_conjunction(+Conjunction, ?NewConjunction)
make sure comparison operators do not precede relations :- mode reorder_conjunction(+,?) is det.
  675reorder_conjunction(C,CNew):-
  676        tuple_to_list(C,Gs),
  677        reorder_conjunction_list(Gs,Gs1,Gs2),
  678        append(Gs1,Gs2,GsNew),
  679        list_to_tuple(GsNew,CNew).
  680
  681reorder_conjunction_list([],[],[]).
  682reorder_conjunction_list([G|Gs],Gs1,[G|Gs2]):-
  683        G =.. [CompOp,_,_],
  684        comparison(CompOp,_),
  685        %G = (_=_),              % put compara assignments at end
  686        !,
  687        reorder_conjunction_list(Gs,Gs1,Gs2).
  688reorder_conjunction_list([G|Gs],[G|Gs1],Gs2):-
  689        reorder_conjunction_list(Gs,Gs1,Gs2).
  690
  691% DRY? TODO
  692tuple_to_list((G,Tup),[G|Gs]):-
  693        !,
  694        tuple_to_list(Tup,Gs).
  695tuple_to_list(G,[G]).
  696
  697list_to_tuple([G],G):- !.
  698list_to_tuple([G|Gs],(G,Tup)):-
  699        list_to_tuple(Gs,Tup).
  700
  701list_to_disj([G],G):- !.
  702list_to_disj([G|Gs],(G;Tup)):-
  703        list_to_disj(Gs,Tup).
  704
  705check_translate_goal(Goal, SQLFrom, SQLWhere, Dict, NewDict) :-
  706	translate_goal(Goal, SQLFrom, SQLWhere, Dict, NewDict),
  707	!.
  708check_translate_goal(Goal, _SQLFrom, _SQLWhere, _Dict, _NewDict) :-
  709	error_message('translate_goal(~w)', [Goal]),
  710	fail.
 translate_goal(+Goal, ?SQLFrom, ?SQLWhere, +Dict, ?NewDict)
translates a
  726%:- mode translate_goal(+,?,?,+,?) is det.
  727translate_goal(SimpleGoal,[SQLFrom],SQLWhere,Dict,NewDict):-
  728   % --- positive goal binds variables - these bindings are held in the dictionary -----
  729	functor(SimpleGoal,Functor,Arity),
  730	translate_functor(Functor,Arity,SQLFrom),
  731	SimpleGoal =.. [Functor|Arguments],
  732	debug(sql_compiler,'  Dict: ~w',[Dict]),
  733	debug(sql_compiler,'     translating arguments from siple goal: ~w',[SimpleGoal]),
  734	translate_arguments(Arguments,SQLFrom,1,SQLWhere,Dict,NewDict),
  735	debug(sql_compiler,'  NewDict: ~w',[NewDict]).
  736
  737translate_goal(Result is Expression,[],SQLWhere,Dict,NewDict):-
  738	debug(sql_compiler,'  translate_arithmetic_function: ~w is ~w',[Result,Expression]),
  739        translate_arithmetic_function(Result,Expression,SQLWhere,Dict,NewDict).
  740
  741% CJM : TODO
  742% e.g. for concat
  743translate_goal(eval(Result, Expression),[],SQLWhere,Dict,NewDict):-
  744        translate_builtin_function(Result,Expression,SQLWhere,Dict,NewDict).
  745% TODO: registerable functions
  746%translate_goal(Goal,[],SQLWhere,Dict,NewDict):- 
  747%        registered_func_pred(Goal,Result),   % e.g. register(atom_concat(_,_,C),C)
  748
  749
  750% negated goals do not bind variables - hence Dict is returned unchanged -------
  751translate_goal(not NegatedGoals,[],SQLNegatedSubquery,Dict,Dict):-
  752   functor(NegatedGoals,Functor,_),
  753   \+ comparison(Functor,_),
  754   translate_conjunction(NegatedGoals,SQLFrom,SQLWhere,Dict,_),
  755   SQLNegatedSubquery = [negated_existential_subquery([*],SQLFrom,SQLWhere)].
  756
  757translate_goal(not ComparisonGoal,[],SQLCompOp,Dict,Dict):-
  758   % --- comparison operations do not bind variables - Dict is returned unchanged -----
  759   ComparisonGoal =.. [ComparisonOperator,LeftArg,RightArg],
  760   comparison(ComparisonOperator,SQLOperator),
  761   negated_comparison(SQLOperator,SQLNegOperator),
  762   translate_comparison(LeftArg,RightArg,SQLNegOperator,Dict,SQLCompOp).
  763
  764translate_goal(ComparisonGoal,[],SQLCompOp,Dict,Dict):-
  765   % --- comparison operations do not bind variables - Dict is returned unchanged -----
  766   ComparisonGoal =.. [ComparisonOperator,LeftArg,RightArg],
  767   comparison(ComparisonOperator,SQLOperator),
  768   debug(sql_compiler,'  ComparisonOp: ~w in ~w',[SQLOperator,ComparisonGoal]),
  769   translate_comparison(LeftArg,RightArg,SQLOperator,Dict,SQLCompOp).
  770
  771translate_goal(not_null(Arg),[],[not_null(Term)],Dict,Dict):- % CJM
  772        evaluable_expression(Arg,Dict,Term,_ArgType).
  773
  774translate_goal(null(Arg),[],[null(Term)],Dict,Dict):- % CJM
  775        evaluable_expression(Arg,Dict,Term,_ArgType).
  776
  777% expand prolog goals; module must be loaded to have effect
  778%translate_goal(Goal,SQLFrom,SQLWhere,Dict,NewDict):-
  779%        Goal \= (_,_),
  780%        clause(Goal,Body),      % view
  781%        translate_goal(Body,SQLFrom,SQLWhere,Dict,NewDict).
 translate_conjunction(+Conjunction, ?SQLFrom, ?SQLWhere, +Dict, ?NewDict)
translates a conjunction of goals (represented as a list of goals preceeded by existentially quantified variables) to FROM- and WHERE-clause of an SQL query. A dictionary containing the associated SQL table and attribute names is built up as an accumulator pair (arguments Dict and NewDict)

Conjunction = Var^Goal | (Goal,Conjunction) | Var

  796%:- mode translate_conjunction(+,?,?,+,?) is det.
  797translate_conjunction('$var$'(VarId)^Goal,SQLFrom,SQLWhere,Dict,NewDict):-
  798   % --- add info on existentially quantified variables to dictionary here -----------
  799   add_to_dictionary(VarId,_,_,_,existential,Dict,TmpDict),
  800   translate_conjunction(Goal,SQLFrom,SQLWhere,TmpDict,NewDict).
  801
  802translate_conjunction(Goal,SQLFrom,SQLWhere,Dict,NewDict):-
  803   Goal \= (_,_),
  804   debug(sql_compiler,'simple conj, translating goal ~w',[Goal]),
  805   check_translate_goal(Goal,SQLFrom,SQLWhere,Dict,NewDict),
  806   debug(sql_compiler,'translated goal ~w',[Goal]).
  807
  808% CJM: see linearize
  809translate_conjunction((Goal,Conjunction),SQLFrom,SQLWhere,Dict,NewDict):-
  810	Goal = (G1,G2), 
  811	!,
  812	translate_conjunction((G1,(G2,Conjunction)),SQLFrom,SQLWhere,Dict,NewDict).
  813
  814translate_conjunction((Goal,Conjunction),SQLFrom,SQLWhere,Dict,NewDict):-
  815        debug(sql_compiler,'complex conj, translating goal ~w',[Goal]),
  816        check_translate_goal(Goal,FromBegin,WhereBegin,Dict,TmpDict),
  817        debug(sql_compiler,'OK ~w with ~w',[Goal,TmpDict]),
  818        debug(sql_compiler,'translating conjunction: ~w',[Conjunction]),
  819        translate_conjunction(Conjunction,FromRest,WhereRest,TmpDict,NewDict),
  820        debug(sql_compiler,'conj ~w',[Conjunction-FromRest-WhereRest]),
  821        append(FromBegin,FromRest,SQLFrom),
  822        append(WhereBegin,WhereRest,SQLWhere).
  823
  824translate_builtin_function('$var$'(VarId),Expression,[],Dict,NewDict):-
  825   % assigment of value of arithmetic expression to variable - does not
  826   % show up in WHERE-part, but expression corresponding to
  827   % variable must be stored in Dict for projection translation
  828   evaluable_expression(Expression,Dict,ArithExpression,Type),
  829   add_to_dictionary(VarId,is,ArithExpression,Type,all,Dict,NewDict).
 translate_arithmetic_function(+Result, +Expression, ?SQLWhere, +Dict:list, ?NewDict:list)
Arithmetic functions (left side of is/2 operator is bound to value of expression on right side) may be called with either
Arguments:

Result Example: $var$(Id)

-
  846translate_arithmetic_function('$var$'(VarId),Expression,[],Dict,NewDict):-
  847   % assigment of value of arithmetic expression to variable - does not
  848   % show up in WHERE-part, but expression corresponding to
  849   % variable must be stored in Dict for projection translation
  850   evaluable_expression(Expression,Dict,ArithExpression,Type),
  851   add_to_dictionary(VarId,is,ArithExpression,Type,all,Dict,NewDict).
  852
  853
  854translate_arithmetic_function('$var$'(VarId),Expression,ArithComparison,Dict,Dict):-
  855   % --- test whether left side evaluates to right side: return equality comparison ----
  856   % Left side consists of qualified attribute, i.e. range variable must not be
  857   % arithmetic operator is/2 
  858
  859   lookup(VarId,Dict,PrevRangeVar,PrevAtt,PrevType),
  860   \+ (PrevRangeVar = is),
  861
  862   % test whether type of attribute is numeric - if not, there's no sense in 
  863   % continuing the translation
  864
  865   check_type_compatible(PrevType,number),
  866   evaluable_expression(Expression,Dict,ArithExpression,ExprType),
  867   check_type_compatible(ExprType,number),
  868   ArithComparison = [comp(att(PrevRangeVar,PrevAtt),'=',ArithExpression)].
  869
  870
  871translate_arithmetic_function('$var$'(VarId),Expression,ArithComparison,Dict,Dict):-
  872   % --- test whether left side evaluates to right side: return equality comparison ----
  873   % Left side consists of arithmetic expression, i.e. VarId is stored in Dict as 
  874   % belonging to arithmetic expression which is expressed as RangeVar-argument 
  875   % of lookup returning is/2. Type information is implicit through the is/2 functor
  876
  877   lookup(VarId,Dict,is,LeftExpr,Type),
  878   check_type_compatible(Type,number),
  879   evaluable_expression(Expression,Dict,RightExpr,ExprType),
  880   check_type_compatible(ExprType,number),
  881   ArithComparison = [comp(LeftExpr,'=',RightExpr)].
  882
  883
  884translate_arithmetic_function('$const$'(Constant),Expression,ArithComparison,Dict,Dict):-
  885   % --- is/2 used to test whether left side evaluates to right side ----------------
  886   get_type('$const$'(Constant),ConstantType),
  887   check_type_compatible(ConstantType,number),
  888   evaluable_expression(Expression,Dict,ArithExpression,ExprType),
  889   check_type_compatible(ExprType,number),
  890   ArithComparison = [comp('$const$'(Constant),'=',ArithExpression)].
 translate_comparison(LeftArg, RightArg, CompOp, Dict, SQLComparison)
translates the left and right arguments of a comparison term into the appropriate comparison operation in SQL. The result type of each argument expression is checked for type compatibility
  898%:- mode translate_comparison(+,+,+,+,?) is det.
  899translate_comparison(LeftArg,RightArg,CompOp,Dict,Comparison):-
  900	evaluable_expression(LeftArg,Dict,LeftTerm,LeftArgType),
  901	evaluable_expression(RightArg,Dict,RightTerm,RightArgType),
  902	check_type_compatible(LeftArgType,RightArgType),
  903	Comparison = [comp(LeftTerm,CompOp,RightTerm)].
  904
  905% CJM member/in support
  906translate_comparison(LeftArg,RightArgList,in,Dict,Comparison):-
  907	evaluable_expression(LeftArg,Dict,LeftTerm,LeftArgType),
  908	forall(member(RightArg,RightArgList),
  909                (   evaluable_expression(RightArg,Dict,RightTerm,RightArgType),
  910                    check_type_compatible(LeftArgType,RightArgType))),
  911	findall(RightTerm,
  912                (   member(RightArg,RightArgList),
  913                    evaluable_expression(RightArg,Dict,RightTerm,_)),
  914                RightTerms),
  915	Comparison = [comp(LeftTerm,in,RightTerms)].
 translate_functor(Functor, QualifiedTableName)
translate_functor searches for the matching relation table name for a given functor and creates a unique range variable to result in a unique qualified relation table name.
  923translate_functor(Functor,Arity,rel(TableName,RangeVariable)):-
  924        relation(Functor,ActualArity,TableName),
  925        ActualArity >= Arity,   % we allow dropping of arguments [CJM]
  926	lgensym([TableName,'_'],RangeVariable). % CJM - friendlier aliases
 translate_arguments(Arguments, RelTable, ArgPos, Conditions, Dict)
translate_arguments organizes the translation of term arguments. One term argument after the other is taken from the list of term arguments until the list is exhausted.
  937translate_arguments([],_,_,[],Dict,Dict).
  938
  939translate_arguments([Arg|Args],SQLTable,Position,SQLWhere,Dict,NewDict):-
  940   translate_argument(Arg,SQLTable,Position,Where,Dict,TmpDict),
  941   debug(sql_compiler,'  Translated arguments: ~w ~w ~w',[Arg,SQLTable,Where]),
  942   NewPosition is Position + 1,
  943   translate_arguments(Args,SQLTable,NewPosition,RestWhere,TmpDict,NewDict),
  944   append(Where,RestWhere,SQLWhere).
 translate_argument(+Argument, +RelTable, +Position:int, ?Condition, +Dict:list, ?NewDict:list)
The first occurrence of a variable leads to its associated SQL attribute information to be recorded in the Dict. Any further occurrence creates an equi-join condition between the current attribute and the previously recorded attribute. Constant arguments always translate to equality comparisons between an attribute and the constant value.
Arguments:
Argument- $var$(Var) or $const$(Const)
RelTable- rel(Table,Alias)
Position- index to attribute/4, to retrueve type
Condition-
Dict-

NewDict

-
  965translate_argument('$var$'(VarId),rel(SQLTable,RangeVar),Position,[],Dict,NewDict):-
  966        attribute(Position,SQLTable,Attribute,Type),
  967        add_to_dictionary(VarId,RangeVar,Attribute,Type,all,Dict,NewDict).
  968
  969translate_argument('$var$'(VarId),rel(SQLTable,RangeVar),Position,AttComparison,Dict,Dict):-
  970        % --- Variable occurred previously - retrieve first occurrence data from dictionary -
  971        debug(sql_compiler,'  ** variable occurred previously: ~w',[VarId]),
  972        lookup(VarId,Dict,PrevRangeVar,PrevAtt,PrevType),
  973        attribute(Position,SQLTable,Attribute,Type),
  974        check_type_compatible(PrevType,Type),
  975        AttComparison = [comp(att(RangeVar,Attribute),=,att(PrevRangeVar,PrevAtt))].
  976
  977translate_argument('$const$'(Constant),rel(SQLTable,RangeVar),Position,ConstComparison,Dict,Dict):-
  978   % --- Equality comparison of constant value and attribute in table ---------------
  979        attribute(Position,SQLTable,Attribute,Type),
  980        get_type('$const$'(Constant),ConstType),
  981        debug(sql_compiler,'Checking for type compatibility ~w <=> ~w',[ConstType,Type]),
  982        check_type_compatible(ConstType,Type),
  983        debug(sql_compiler,'Compatible ~w <=> ~w',[ConstType,Type]),
  984        ConstComparison = [comp(att(RangeVar,Attribute),=,'$const$'(Constant))].
 projection_term_variables(ProjectionTerm, Dict)
extracts all variables from the ProjectionTerm and places them into the Dict as a dict/4 term with their Identifier, a non instantiated RangeVar and Attribute argument, and the keyword existential for the type of quantification
  995%:- mode projection_term_variables(+,?) is det.
  996projection_term_variables('$const(_)$',[]).
  997
  998projection_term_variables('$var$'(VarId),[dict(VarId,_,_,_,existential)]).
  999
 1000% CJM: added to allow GROUP BY vars in projection term
 1001projection_term_variables('$var$'(VarId) ^ ProjectionTerm, [dict(VarId,_,_,_,all)|ProjectionTermVariables]):-
 1002%projection_term_variables('$var$'(VarId) ^ ProjectionTerm, ProjectionTermVariables):-
 1003        !,
 1004        projection_term_variables(ProjectionTerm,ProjectionTermVariables).
 1005
 1006% compound terms
 1007projection_term_variables(ProjectionTerm,ProjectionTermVariables):-
 1008   ProjectionTerm =.. [Functor|ProjectionTermList],
 1009   not (Functor = '$var$'),
 1010   not (ProjectionTermList = []), % must be compound
 1011   projection_list_vars(ProjectionTermList,ProjectionTermVariables).
 1012
 1013
 1014projection_list_vars([],[]).
 1015projection_list_vars(['$var$'(VarId)|RestArgs],Vars):-
 1016	!,
 1017        projection_list_vars(RestArgs,RestVars),
 1018        H = dict(VarId,_,_,_,existential), % CJM - added the following to prevent dupes
 1019        (   \+ member(H, RestVars)
 1020        ->  Vars = [H|RestVars] % add new
 1021        ;   Vars = RestVars).   % already have it
 1022projection_list_vars(['$const$'(_)|RestArgs],Vars):-
 1023	!,
 1024   projection_list_vars(RestArgs,Vars).
 1025% [CJM] : added this clause to allow for complex projection terms
 1026projection_list_vars([H|RestArgs],Vars):-
 1027	projection_term_variables(H,Vars1),
 1028	projection_list_vars(RestArgs,Vars2),
 1029	append(Vars1,Vars2,Vars). % TODO - uniqify
 1030
 1031
 1032
 1033
 1034
 1035
 1036% ------------------------------------------------------------------------
 1037% RESTRICTION! ProjectionTerm underlies the following restrictions:
 1038%
 1039%  - ProjectionTerm must have a functor other than the built-in
 1040%    operators, i.e. ',',';', etc. are not allowed
 1041%
 1042%  - only variables and constants are allowed as arguments,
 1043%    i.e. no structured terms
 1044%
 1045% ------------------------------------------------------------------------
 translate_projection(+ProjectionTerm, +Dict, ?SelectList) is det
 1048translate_projection('$var$'(VarId),Dict,SelectList):-
 1049   projection_arguments(['$var$'(VarId)],SelectList,Dict).
 1050
 1051translate_projection('$const$'(Const),_,['$const$'(Const)]).
 1052
 1053translate_projection(ProjectionTerm,Dict,SelectList):-
 1054   ProjectionTerm =.. [Functor|Arguments],
 1055   not (Functor = '$var$'),
 1056   not (Functor = '$const$'),
 1057   not (Arguments = []),
 1058   projection_arguments(Arguments,SelectList,Dict).
 projection_arguments(+Args, ?SelectList, +Dict) is det
 1064projection_arguments([],[],_).
 1065
 1066projection_arguments([Arg|RestArgs],[Att|RestAtts],Dict):-
 1067   retrieve_argument(Arg,Att,Dict),
 1068   !, % [CJM]
 1069   projection_arguments(RestArgs,RestAtts,Dict).
 1070
 1071% [CJM]: extend to allow complex terms
 1072projection_arguments([Arg|RestArgs],Atts,Dict):-
 1073	translate_projection(Arg,Dict,Atts1),
 1074	projection_arguments(RestArgs,Atts2,Dict),
 1075	append(Atts1,Atts2,Atts).
 retrieve_argument(Argument, SQLAttribute, Dictionary)
retrieves the mapping of an argument to the appropriate SQL construct, i.e.
 1086retrieve_argument('$var$'(VarId),Attribute,Dict):-
 1087   lookup(VarId,Dict,TableName,AttName,_),
 1088   (
 1089    TableName = is ->
 1090      Attribute = AttName
 1091   ;
 1092      Attribute = att(TableName,AttName)
 1093   ).
 1094
 1095retrieve_argument('$const$'(Constant),'$const$'(Constant),_).
 lookup(Key, Dict, Value)
 1103lookup(VarId,Dict,RangeVar,Attribute,Type):-
 1104   member(dict(VarId,RangeVar,Attribute,Type,Quant),Dict),
 1105   (   Quant = all
 1106   ->  true
 1107   ;   nonvar(RangeVar),
 1108       nonvar(Attribute)
 1109   ).
 add_to_dictionary(+Key, +RangeVar, +Attribute, +Quantifier, +Dict:list, ?NewDict:list) is semidet
e.g. add_to_dictionary(var1,feature_1,feature_id,all,[dict(var1,..)..],_) fails if Key is a member of the dictionary as Quantifier=all
 1116add_to_dictionary(Key,RangeVar,Attribute,Type,_,Dict,Dict):-
 1117   member(dict(Key,RangeVar,Attribute,Type,existential),Dict).
 1118
 1119add_to_dictionary(Key,RangeVar,Attribute,Type,Quantifier,Dict,NewDict):-
 1120   not member(dict(Key,_,_,_,_),Dict),
 1121   NewDict = [dict(Key,RangeVar,Attribute,Type,Quantifier)|Dict].
 aggregate_function(AggregateFunctionTerm, Dict, AggregateFunctionQuery)
aggregate_function discerns five Prolog aggregate function terms: count, avg, min, max, and sum. Each such term is has two arguments: a variable indicating the attribute over which the function is to be computed, and a goal argument which must contain in at least one argument position the variable:

e.g. avg(Seats,plane(Type,Seats))

These aggregate function terms correspond to the SQL built-in aggregate functions.

RESTRICTION: AggregateGoal may only be conjunction of (positive or negative) base goals


 1142aggregate_function(AggregateFunctionTerm,Dict,AggregateFunctionExpression):-
 1143        AggregateFunctionTerm =..[AggFunctor,AggVar,AggGoal],
 1144        aggregate_functor(AggFunctor,SQLFunction),
 1145        debug(sql_compiler,'Aggregrate ~w AggVar=~w AggGoal=~w',[AggFunctor,AggVar,AggGoal]),
 1146        conjunction(AggGoal,AggConjunction),
 1147        aggregate_query_generation(SQLFunction,AggVar,AggConjunction,Dict,AggregateFunctionExpression).
 1148
 1149
 1150conjunction(Goal,Conjunction):-
 1151   convert_to_disj_norm_form(Goal,[Conjunction]).
 aggregate_query_generation(Function, FunctionVariable, AggGoal, Dict, AggregateQuery)
compiles the function variable (representing the attribute over which the aggregate function is to be computed) and the aggregate goal (representing the selection and join conditions for the computation of the aggregate function) to an SQL aggregate function subquery.
 1165%:- mode aggregate_query_generation(+,+,+,+,?) is det.
 1166aggregate_query_generation(count,'$const$'('*'),AggGoal,Dict,AggregateQuery):-
 1167   translate_conjunction(AggGoal,SQLFrom,SQLWhere,Dict,_TmpDict),
 1168   % ATTENTION! It is assumed that in count(*) aggregate query terms there cannot be
 1169   % free variables because '*' stands for "all arguments"
 1170   AggregateQuery = agg_query(_Function,(count,['$const$'(*)]),SQLFrom,SQLWhere,[]).
 1171
 1172% CJM: added for distinct
 1173aggregate_query_generation(count_distinct,'$const$'('*'),AggGoal,Dict,AggregateQuery):-
 1174   translate_conjunction(AggGoal,SQLFrom,SQLWhere,Dict,_TmpDict),
 1175   % ATTENTION! It is assumed that in count(*) aggregate query terms there cannot be
 1176   % free variables because '*' stands for "all arguments"
 1177   AggregateQuery = agg_query(_Function,(count_distinct,['$const$'(*)]),SQLFrom,SQLWhere,[]).
 1178
 1179aggregate_query_generation(Function,FunctionVariable,AggGoal,Dict,AggregateQuery):-
 1180   translate_conjunction(AggGoal,SQLFrom,SQLWhere,Dict,TmpDict),
 1181   % --- only variables occurring in the aggregate goal are relevant to the translation
 1182   % of the function variable and the free variables in the goal.
 1183   % Thus subtract from TmpDict all entries of Dict
 1184   set_difference(TmpDict,Dict,AggDict),
 1185   debug(sql_compiler,'AggDict=~w',[AggDict]), % CJM: this appears not to be the correct thing to do
 1186   % the set difference includes columns that are in the aggregate. Need to use '*'?
 1187   
 1188   translate_projection(FunctionVariable,AggDict,SQLSelect),
 1189   translate_grouping(FunctionVariable,AggDict,SQLGroup), % ???
 1190   debug(sql_compiler,'SQLGroup=~w',[SQLGroup]),
 1191   % CJM NOTES:
 1192   %  group_by is broken in original sql_compiler. The query is formed correctly, however,
 1193   %  putting the grouping variable in the projection results in the variable being removed
 1194   %  from the group clause!
 1195   %  for now eliminate grouping entirely
 1196%   AggregateQuery = agg_query(Function,SQLSelect,SQLFrom,SQLWhere,SQLGroup).
 1197   AggregateQuery = agg_query(Function,SQLSelect,SQLFrom,SQLWhere,[]). 
 translate_grouping(+FunctionVariable, +Dict, ?SQLGroup)
finds the free variables in the aggregate function term and collects their corresponding SQL qualified attributes in the SQLGroup list.
 1209translate_grouping(FunctionVariable,Dict,SQLGroup):-
 1210   free_vars(FunctionVariable,Dict,FreeVariables),
 1211   translate_free_vars(FreeVariables,SQLGroup).
 free_vars(FunctionVariable, Dict, FreeVarList)
A Variable is free if it neither occurs as the FunctionVariable, nor is stored as existentially quantified (through ^/2 in the original goal) in the dictionary

FreeVars contains for each variable the relevant attribute and relation information contained in the dictionary


 1226free_vars(FunctionVariable,Dict,FreeVarList):-
 1227        projection_term_variables(FunctionVariable,FunctionVariableList),
 1228        findall((Var,Table,Attribute),
 1229                (   member(dict(Var,Table,Attribute,_Type,all),Dict),
 1230                    \+ member(dict(Var,_,_,_,_),FunctionVariableList)),
 1231                FreeVarList).
 function_variable_list(FunctionVariable, FunctionVariableList)
extracts the list of variables which occur in the function variable term

RESTRICTION: FunctionVariable may only contain one single variable.


 1242function_variable_list('$var$'(VarId),[VarId]).
 translate_free_vars(FreeVars, SQLGroup)
translates dictionary information on free variables to SQLGroup of aggregate function query
 1254translate_free_vars([],[]).
 1255translate_free_vars([(_VarId,Table,Attribute)|FreeVars],[att(Table,Attribute)|SQLGroups]):-
 1256   translate_free_vars(FreeVars,SQLGroups).
 evaluable_expression(+ExpressionTerm, +Dictionary, ?Expression, +Type)
evaluable_expression constructs SQL arithmetic expressions with qualified attribute names from the Prolog arithmetic expression term and the information stored in the dictionary.

The type of an evaluable function is returned in the argument Type.

The dictionary is not changed because it is used for lookup only.

 1269evaluable_expression(AggregateFunctionTerm,Dictionary,AggregateFunctionExpression,Type):-
 1270        debug(sql_compiler,'~w',[evaluable_expression(AggregateFunctionTerm,Dictionary,AggregateFunctionExpression,Type)]),
 1271        fail.
 1272
 1273evaluable_expression(AggregateFunctionTerm,Dictionary,AggregateFunctionExpression,number):-
 1274   aggregate_function(AggregateFunctionTerm,Dictionary,AggregateFunctionExpression).
 1275
 1276evaluable_expression(LeftExp + RightExp,Dictionary,LeftAr + RightAr,number):-
 1277   evaluable_expression(LeftExp,Dictionary,LeftAr,number),
 1278   evaluable_expression(RightExp,Dictionary,RightAr,number).
 1279
 1280evaluable_expression(LeftExp - RightExp,Dictionary,LeftAr - RightAr,number):-
 1281   evaluable_expression(LeftExp,Dictionary,LeftAr,number),
 1282   evaluable_expression(RightExp,Dictionary,RightAr,number).
 1283
 1284evaluable_expression(LeftExp * RightExp,Dictionary,LeftAr * RightAr,number):-
 1285   evaluable_expression(LeftExp,Dictionary,LeftAr,number),
 1286   evaluable_expression(RightExp,Dictionary,RightAr,number).
 1287
 1288evaluable_expression(LeftExp / RightExp,Dictionary, LeftAr / RightAr,number):-
 1289   evaluable_expression(LeftExp,Dictionary,LeftAr,number),
 1290   evaluable_expression(RightExp,Dictionary,RightAr,number).
 1291
 1292% CJM: todo - generalize; try the code below. requires type_union
 1293/* Ciao:
 1294%% MH Unified all operators here. Also using type unions (previously was 
 1295%% hardwired to 'num'). 
 1296evaluable_expression(ArithExpr, Dictionary, SQLArithExpr, EType) :-
 1297	ArithExpr =.. [PrologOp, LeftExp, RightExp],
 1298	arithmetic_functor(PrologOp, SQLOp),
 1299	evaluable_expression(LeftExp, Dictionary, LeftAr, LType),
 1300	evaluable_expression(RightExp, Dictionary, RightAr, RType),
 1301	check_type_union(LType, RType, EType),
 1302	SQLArithExpr =.. [SQLOp, LeftAr, RightAr].
 1303*/
 1304
 1305evaluable_expression( log(LeftExp, RightExp), Dictionary, log(LeftAr / RightAr),number):-
 1306   evaluable_expression(LeftExp,Dictionary,LeftAr,number),
 1307   evaluable_expression(RightExp,Dictionary,RightAr,number).
 1308
 1309evaluable_expression('$var$'(VarId),Dictionary,att(RangeVar,Attribute),Type):-
 1310   lookup(VarId,Dictionary,RangeVar,Attribute,Type),
 1311   RangeVar \= is.
 1312
 1313evaluable_expression('$var$'(VarId),Dictionary,ArithmeticExpression,Type):-
 1314   lookup(VarId,Dictionary,is,ArithmeticExpression,Type).
 1315
 1316evaluable_expression('$const$'(Const),_,'$const$'(Const),ConstType):-
 1317   get_type('$const$'(Const),ConstType).
 1318
 1319% CJM : TODO
 1320evaluable_expression(case(TestExp,ThenExp,ElseExp),Dictionary,case(TestAr,ThenAr,ElseAr),number):-
 1321        evaluable_expression(TestExp,Dictionary,TestAr,_),
 1322        %check_translate_goal(TestExp,Dictionary,TestAr,_),
 1323        %TestAr=TestExp,
 1324        evaluable_expression(ThenExp,Dictionary,ThenAr,_),
 1325        evaluable_expression(ElseExp,Dictionary,ElseAr,_).
 1326
 1327% CJM 
 1328evaluable_expression(concat(L,J,R),Dictionary,concat(LAr,J,RAr),varchar):-
 1329        evaluable_expression(L,Dictionary,LAr,_),
 1330        %check_translate_goal(TestExp,Dictionary,TestAr,_),
 1331        %TestAr=TestExp,
 1332        evaluable_expression(R,Dictionary,RAr,_).
 1333
 1334% CJM 
 1335evaluable_expression(substr(Str,From,To),Dictionary,substr(XStr,XFrom,XTo),varchar):-
 1336        evaluable_expression(Str,Dictionary,XStr,_),
 1337        evaluable_expression(From,Dictionary,XFrom,_),
 1338        evaluable_expression(To,Dictionary,XTo,_).
 1339
 1340
 1341% CJM : allow arithmentic functions to work over subtypes of number
 1342evaluable_expression('$var$'(VarId),Dictionary,ArithmeticExpression,SuperType):-
 1343        nonvar(SuperType),
 1344        subtype(Type,SuperType),
 1345        SuperType\=Type,
 1346        evaluable_expression('$var$'(VarId),Dictionary,ArithmeticExpression,Type).
 1347
 1348
 1349
 1350% ----------------------------------------------------------------
 1351% Pretty printing of queries
 1352% ----------------------------------------------------------------
 1353
 1354queries_tokens([Query]) --> !,
 1355	query_tokens(0,Query),
 1356	[';'].
 1357queries_tokens([Query|Queries]) -->
 1358	query_tokens(0,Query),
 1359	['\n UNION '],
 1360	queries_tokens(Queries).
 1361
 1362query_tokens(Tab,query([agg_query(Function, Select, From, Where, Group)], _, _)) -->
 1363	% --- ugly rule here: aggregate function only in SELECT Part of query ----
 1364	!,
 1365	query_tokens(Tab,agg_query(Function, Select, From, Where, Group)).
 1366query_tokens(Tab,query(Select, From, Where)) -->
 1367	clause3_tokens(Tab,'SELECT DISTINCT', Select, ','),
 1368	clause3_tokens(Tab,' FROM', From, ','),
 1369	clause3_tokens(Tab,' WHERE', Where, 'AND').
 1370query_tokens(Tab,agg_query('COUNT_DISTINCT', Select, From, Where, Group)) -->
 1371	clause4_tokens_with_distinct(Tab,'SELECT', 'COUNT', Select, ','),
 1372	clause3_tokens(Tab,' FROM', From, ','),
 1373	clause3_tokens(Tab,' WHERE', Where, 'AND'),
 1374	clause3_tokens(Tab,' GROUP BY', Group, ',').
 1375query_tokens(Tab,agg_query(Function, Select, From, Where, Group)) -->
 1376	clause4_tokens(Tab,'SELECT', Function, Select, ','),
 1377	clause3_tokens(Tab,' FROM', From, ','),
 1378	clause3_tokens(Tab,' WHERE', Where, 'AND'),
 1379	clause3_tokens(Tab,' GROUP BY', Group, ',').
 1380query_tokens(Tab,negated_existential_subquery(Select, From, Where)) -->
 1381	['NOT EXISTS ('],
 1382	{tabinc(Tab,NextTab)},
 1383	query_tokens(NextTab,query(Select, From, Where)),
 1384	[')'].
 1385
 1386clause4_tokens(Tab,Keyword, Function, [Column], Separator) -->
 1387	atom_tokens(Keyword),
 1388	[' '],
 1389	atom_tokens(Function),
 1390	['('],
 1391	{tabinc(Tab,NextTab)},
 1392	clause2_tokens(NextTab,[Column], Separator),
 1393	[')'].
 1394
 1395% CJM: a bit hacky...
 1396clause4_tokens_with_distinct(Tab,Keyword, Function, [Column], Separator) -->
 1397	atom_tokens(Keyword),
 1398	[' '],
 1399	atom_tokens(Function),
 1400	{tabinc(Tab,NextTab)},
 1401	['(DISTINCT '],
 1402	clause2_tokens(NextTab,[Column], Separator),
 1403	[')'].
 1404
 1405clause3_tokens(_Tab,_Keyword, [], _) --> [].
 1406clause3_tokens(Tab,Keyword, [Column|RestColumns], Separator) -->
 1407	['\n'],
 1408	tabulate(' ',Tab),
 1409	atom_tokens(Keyword),
 1410	[' '],
 1411	clause2_tokens(Tab,[Column|RestColumns], Separator).
 1412
 1413clause2_tokens(Tab,[Item], _) -->
 1414	['\n'],
 1415	tabulate(' ',Tab),
 1416	['  '],
 1417	column_tokens(Tab,Item).
 1418clause2_tokens(Tab,[Item, NextItem|RestItems], Separator) -->
 1419	['\n'],
 1420	tabulate(' ',Tab),
 1421	['  '],
 1422	column_tokens(Tab,Item),
 1423	[' '],
 1424	atom_tokens(Separator),
 1425	[' '],
 1426	clause2_tokens(Tab,[NextItem|RestItems], Separator).
 1427
 1428column_tokens(_Tab,'*') -->
 1429	['*'].
 1430column_tokens(_Tab,att(rel1, Attribute)) --> !, % HACK FOR MySQL!!!
 1431	atom_tokens(Attribute).
 1432column_tokens(_Tab,att(RangeVar, Attribute)) -->
 1433	atom_tokens(RangeVar),
 1434	['.'],
 1435	atom_tokens(Attribute).
 1436column_tokens(_Tab,rel(Relation, rel1)) --> !, % HACK FOR MySQL!!!
 1437        {expand_relation_name(Relation,RX)},
 1438	atom_tokens(RX).
 1439column_tokens(_Tab,rel(Relation, RangeVar)) -->
 1440        {expand_relation_name(Relation,RX)},
 1441	atom_tokens(RX),
 1442	[' '],
 1443	atom_tokens(RangeVar).
 1444column_tokens(_Tab,'$const$'(String)) -->
 1445	{ get_type('$const$'(String), string) }, !,
 1446        esc_atom_tokens(String).
 1447column_tokens(_Tab,'$const$'(Number)) -->
 1448	{ get_type('$const$'(Number), NumType),
 1449          debug(sql_compiler,'check_type_compat ~w ~w',[Number,NumType]),
 1450	  %check_type_compatible(NumType, num) TODO!!
 1451          true
 1452	},
 1453	atom_tokens(Number).
 1454column_tokens(Tab,case(Test, Then, Else)) -->
 1455        !,
 1456	{tabinc(Tab,NextTab)},
 1457        [' CASE WHEN '],
 1458	column_tokens(NextTab,Test),
 1459	[' THEN '],
 1460	column_tokens(NextTab,Then),
 1461	[' ELSE '],
 1462	column_tokens(NextTab,Else),
 1463        [' END CASE '].
 1464column_tokens(Tab,substr(Str, From, Len)) -->
 1465        !,
 1466        [' SUBSTR( '],
 1467	column_tokens(Tab,Str),
 1468	[', '],
 1469	column_tokens(Tab,From),
 1470	[', '],
 1471	column_tokens(Tab,Len),
 1472        [' ) '].
 1473
 1474/*
 1475column_tokens(Tab,concat(Left, Join, Right)) -->
 1476        !,
 1477        [' CONCAT( '],
 1478	column_tokens(Tab,Left),
 1479	[', '],
 1480	esc_atom_tokens(Join),
 1481	[', '],
 1482	column_tokens(Tab,Right).
 1483        [' ) '].
 1484*/
 1485column_tokens(Tab,comp(LeftArg, in, RightArgs)) --> % CJM
 1486        !,
 1487	column_tokens(Tab,LeftArg),
 1488	[' IN '],
 1489	[' ( '],
 1490        clause2_tokens(Tab,RightArgs, ','),
 1491        [' ) '].
 1492column_tokens(Tab,comp(LeftArg, Operator, RightArg)) -->
 1493	column_tokens(Tab,LeftArg),
 1494	[' '],
 1495	atom_tokens(Operator),
 1496	[' '],
 1497	column_tokens(Tab,RightArg).
 1498column_tokens(Tab,null(Arg)) --> % CJM
 1499	column_tokens(Tab,Arg),
 1500	[' IS NULL'].
 1501column_tokens(Tab,not_null(Arg)) --> % CJM
 1502	column_tokens(Tab,Arg),
 1503	[' IS NOT NULL'].
 1504% TODO: make this more generic
 1505column_tokens(Tab,LeftExpr * RightExpr) -->
 1506	column_tokens(Tab,LeftExpr),
 1507	optnltab(Tab,RightExpr),
 1508	[' * '],
 1509	column_tokens(Tab,RightExpr).
 1510column_tokens(Tab,LeftExpr / RightExpr) -->
 1511	column_tokens(Tab,LeftExpr),
 1512	optnltab(Tab,RightExpr),
 1513	[' / '],
 1514	column_tokens(Tab,RightExpr).
 1515column_tokens(Tab,LeftExpr + RightExpr) -->
 1516	column_tokens(Tab,LeftExpr),
 1517	optnltab(Tab,RightExpr),
 1518	[' + '],
 1519	column_tokens(Tab,RightExpr).
 1520column_tokens(Tab,LeftExpr - RightExpr) -->
 1521	column_tokens(Tab,LeftExpr),
 1522	optnltab(Tab,RightExpr),
 1523	[' - '],
 1524	column_tokens(Tab,RightExpr).
 1525column_tokens(Tab,agg_query(Function, Select, From, Where, Group)) -->
 1526	{tabinc(Tab,NextTab)},
 1527	['('],
 1528	query_tokens(NextTab,agg_query(Function, Select, From, Where, Group)),
 1529	[')'].
 1530column_tokens(Tab,negated_existential_subquery(Select, From, Where)) -->
 1531	{tabinc(Tab,NextTab)},
 1532	query_tokens(NextTab,negated_existential_subquery(Select, From, Where)).
 1533
 1534string_tokens(S, Xs, Ys) :-
 1535	append(S, Ys, Xs).
 1536
 1537atom_tokens(A) --> [A].
 1538esc_atom_tokens(A) --> [''''],[AEsc],[''''],{sql_atom_escape(A,AEsc)}.
 1539
 1540sql_atom_escape(X,X):- \+ sub_atom(X,_,_,_,''''),!.
 1541sql_atom_escape(A,AE):-
 1542        atom_chars(A,Toks),
 1543        sql_escape_chars(Toks,Toks2),
 1544        atom_chars(AE,Toks2).
 1545
 1546sql_escape_chars([],[]).
 1547sql_escape_chars(['\''|L],['\'','\''|L2]):-
 1548        !,
 1549        sql_escape_chars(L,L2).
 1550sql_escape_chars([C|L],[C|L2]):-
 1551        sql_escape_chars(L,L2).
 1552
 1553% for complex expressions, start with a newline then tab
 1554optnltab(Tab,agg_query(_,_,_,_,_)) --> !,['\n'],tabulate(' ',Tab),['  '].
 1555optnltab(_,_) --> [].
 1556
 1557tabinc(Tab,NextTab):- NextTab is Tab+8.
 1558tabulate(_,0) --> !,[].
 1559tabulate(A,N) --> {Nm1 is N-1},[A],tabulate(A,Nm1).
 1560
 1561
 1562% ----------------------------------------------------------------
 1563% Conversion of SQL term to string
 1564% ----------------------------------------------------------------
 sqlterm2atom(+SQLQueryTerm, ?SQLQueryAtom) is det
translates an SQL Term to an atom that conforms to SQL syntax. see plterm_to_sqlterm/3 [Ciao: sqlterm2string]
 1570sqlterm2atom(SQLQueryTerm, SQLQueryAtom) :-
 1571                                % Original code fails on some SQLQueryTerms for which queries_dstring/1 succeeds!
 1572                                % Doing this serious kludge instead for now:
 1573	queries_tokens(SQLQueryTerm, SQLQueryTokens, []),
 1574        concat_atom(SQLQueryTokens,SQLQueryAtom),
 1575        !.
 1576sqlterm2atom(SQLQueryTerm, _) :-
 1577        throw(cannot_translate_sqlterm(SQLQueryTerm)).
 1578
 1579
 1580
 1581% Meta Database for schema definition of SQL DB in Prolog
 1582%
 1583% maps Prolog predicates to SQL table names, Prolog predicate argument positions to SQL
 1584% attributes, and Prolog operators to SQL operators. 
 1585%
 1586% ATTENTION! It is assumed that the arithmetic operators in Prolog and SQL are the same,
 1587% i.e. + is addition in Prolog and in SQL, etc. If this is not the case, then a mapping
 1588% function for arithmetic operators is necessary too.
 relation(+PrologFunctor, +Arity, SQLTableName)
 1592relation(F,A,F):- relation(F,A).
 1593relation(F,A,F2):- \+ relation(F,A),relation(F2,A),downcase_atom(F2,Fp),downcase_atom(F,Fp).
 1594
 1595
 1596% ------------------------------------------------------------------------
 1597%
 1598% Output to screen predicates - rather crude at the moment
 1599%
 1600% ------------------------------------------------------------------------
 1601
 1602%:- mode print_sqlterm(+) is det.
 1603print_sqlterm(T):-
 1604        sqlterm2atom(T,S),
 1605        writeln(S).
 lgensym(Root, Symbol)
 1608lgensym(L,Symbol):-
 1609        concat_atom(L,Root),
 1610        gensym(Root,Symbol).
 set_difference(SetA, SetB, Difference)
SetA - SetB = Difference
 1619set_difference([],_,[]).
 1620
 1621set_difference([Element|RestSet],Set,[Element|RestDifference]):-
 1622   not member(Element,Set),
 1623   set_difference(RestSet,Set,RestDifference).
 1624
 1625set_difference([Element|RestSet],Set,RestDifference):-
 1626   member(Element,Set),
 1627   set_difference(RestSet,Set,RestDifference).
 1628
 1629
 1630% Mapping of Prolog operators to SQL operators
 1631
 1632comparison(=,=).
 1633comparison(\=,'!=').
 1634comparison('iz','IS').          % CJM
 1635comparison(<,<).
 1636comparison(>,>).
 1637comparison(@<,<).
 1638comparison(@>,>).
 1639comparison(=<,'<=').            % CJM
 1640comparison(>=,>=).              % CJM
 1641comparison(in,in).              % CJM
 1642
 1643
 1644negated_comparison(is,'IS NOT'). % CJM 
 1645negated_comparison(=,'!='). % CJM changed <> to !=
 1646negated_comparison(\=,=).
 1647negated_comparison(>,=<).
 1648negated_comparison(=<,>).
 1649negated_comparison(<,>=).
 1650negated_comparison(>=,<).
 aggregate_function(PrologFunctor, SQLFunction)
 1654aggregate_functor(avg,'AVG').
 1655aggregate_functor(min,'MIN').
 1656aggregate_functor(max,'MAX').
 1657aggregate_functor(sum,'SUM').
 1658aggregate_functor(stddev,'STDDEV'). % CJM
 1659aggregate_functor(count,'COUNT').
 1660aggregate_functor(count_distinct,'COUNT_DISTINCT'). % CJM: ugly, this is translated later
 1661
 1662
 1663
 1664
 1665% type system 
 1666%
 1667% A rudimentary type system is provided for consistency checking during the
 1668% translation and for output formatting
 1669%
 1670% The basic types are string and number. number has the subtypes integer and
 1671% real.
 1672%
 1673% TODO: fix this so it's not a hack. see sqltypes
 1674
 1675type_compatible(Type,Type):-
 1676   is_type(Type).
 1677type_compatible(SubType,Type):-
 1678   subtype(SubType,Type).
 1679type_compatible(Type,SubType):-
 1680   subtype(SubType,Type).
 subtype(SubType, SuperType)
Simple type hierarchy checking
 1689rsubtype(X,X).
 1690rsubtype(SubType,SuperType):-
 1691        subtype(SubType,SuperType).
 1692
 1693
 1694
 1695subtype(SubType,SuperType):-
 1696   is_subtype(SubType,SuperType).
 1697
 1698subtype(SubType,SuperType):-
 1699   is_subtype(SubType,InterType),
 1700   subtype(InterType,SuperType).
 1701
 1702check_type_compatible(TypeA, TypeB) :- 
 1703	type_compatible(TypeA, TypeB),
 1704	!.
 1705check_type_compatible(TypeA, TypeB) :- 
 1706	error_message("incompatible types ~w, ~w", [TypeA, TypeB]),
 1707	fail.
 is_type(Type)
Type names
 1716% TODO [CJM]: add more for other SQL Types?
 1717is_type(number).
 1718is_type(integer).
 1719is_type(int).
 1720is_type(real).
 1721is_type(string).
 1722is_type(natural).
 1723is_type(float).
 1724is_type(double).
 1725% mysql:
 1726is_type(smallint).
 1727is_type(tinyint).
 1728is_type('tinyint unsigned').
 1729is_type('smallint unsigned').
 1730is_type('integer unsigned').
 is_subtype(SubType, SuperType)
Simple type hierarchy for numeric types
 1739is_subtype(integer,number).
 1740is_subtype(float,number).
 1741is_subtype(double,number).
 1742is_subtype(int,number).
 1743is_subtype(smallint,number).
 1744is_subtype(tinyint,number).
 1745is_subtype(tinyint,integer).
 1746is_subtype(integer,number).
 1747is_subtype('integer unsigned',integer).
 1748is_subtype('integer unsigned',number).
 1749is_subtype('smallint unsigned',number).
 1750is_subtype('smallint unsigned',integer).
 1751is_subtype('tinyint unsigned',integer).
 1752is_subtype('tinyint unsigned',number).
 1753is_subtype(real,number).
 1754is_subtype(natural,integer).
 1755is_subtype(bool,string).        % not quite right but seems to be required..
 get_type(+Constant, ?Type)
Prolog implementation specific definition of type retrieval sepia Prolog version given here
 1763get_type('$const$'(Constant),integer):-
 1764   number(Constant).
 1765
 1766get_type('$const$'(Constant),string):-
 1767   atom(Constant).
 1768
 1769error_message(Fmt,Args):-
 1770        sformat(Msg,Fmt,Args),
 1771        throw(sql(Msg)).
 1772
 1773% for debugging
 1774pp_prologterm(Term) :-
 1775	pp_prologterm([],Term).
 1776
 1777pp_prologterm(Tabs,L) :-
 1778	is_list(L),
 1779	!,
 1780	maplist(write,Tabs),
 1781	format('[~n'),
 1782	maplist(pp_prologterm([' '|Tabs]),L),
 1783	maplist(write,Tabs),
 1784	format(']~n').
 1785	      
 1786pp_prologterm(Tabs,Term) :-
 1787	var(Term),
 1788	!,
 1789	maplist(write,Tabs),
 1790	writeln(Term).
 1791
 1792pp_prologterm(Tabs,Term) :-
 1793	Term =.. [_|Args],
 1794	forall(member(A,Args),
 1795	       (   atomic(A)
 1796	       ;   A='$const$'(_)
 1797	       ;   A='$var$'(_))),
 1798	!,
 1799	maplist(write,Tabs),
 1800	format('~w~n',[Term]).
 1801
 1802pp_prologterm(Tabs,Term) :-
 1803	Term =.. [F|Args],
 1804	maplist(write,Tabs),
 1805	format('~w~n',[F]),
 1806	maplist(pp_prologterm([' '|Tabs]),Args).

Mapping of prolog terms to SQL

Description

This module rewrites prolog goals as SQL goals. It assumes the details of the schema are given using the following predicates:

relation/2
attribute/4
unique/2
Some example schemas are bundled with blipkit:
sql_schema/schema_go.pro
sql_schema/schema_enscore44.pro
There are also sqlmap modules that bridge between the schema and blipkit models. See for example
genomic/genome_sqlmap_enscore.pro
ontol/ontol_sqlmap_go.pro

TODO

problem with existentials being in the projection

works:

blip-sql -debug sql_compiler  -u seqfeature_sqlmap_chado_exposed_ids -u genome_bridge_from_seqfeature -r rdb/flybase prolog-to-sql -proj T "feature(G),seqfeature_db:feature_relationship(G,T,Type,Rank)"

misses join:

== blip-sql -debug sql_compiler -u seqfeature_sqlmap_chado_exposed_ids -u genome_bridge_from_seqfeature -r rdb/flybase prolog-to-sql "feature(G),seqfeature_db:feature_relationship(G,T,Type,Rank)" ==

Recursive SQL

PostgreSQL8.4 allows WITH RECURSIVE; e.g.

tree(?ID,?Parent)

struct(ID,Parent) :- struct(ID,Parent).
struct(ID,A) :- struct(ID,Parent),struct(Parent,A).

should translate to:

WITH RECURSIVE struct AS (
SELECT t.* FROM tree t WHERE id = 890
UNION ALL
SELECT t.* FROM tree t, struct s WHERE t.id = s.parent_id
)
SELECT * FROM struct;

Author

This modules is an extension of version 1.1 (Dec 21st 1992) of the Prolog to SQL compiler written by Christoph Draxler of the Univeristy of Munich.

Christoph Draxler CIS Centre for Information and Speech Processing Ludwig-Maximilians-University Munich Wagmuellerstr. 23 D 80538 Munich Tel : ++49 / +89 / 211 06 64 (-60) Fax : ++49 / +89 / 211 06 74 Mail: draxler@cis.uni-muenchen.de

It was modified by Chris Mungall to be compatible with SWI-Prolog and extended to support:

MySQL specific column types SELECT DISTINCT query rewriting for optimization additional comparison operators

Permission has been granted by the original author to distribute this software using the same license as SWI-Prolog.

RELEASE INFORMATION Current version is v. 1.1 of Dec. 21st 1992. Version 1.0 Sept. 3 1992 CJM mods 2005 */