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).
Head <- Body
36:- dynamic view/2,view/3,schema_dbname/2.
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*/ 61systemterm_expansion( (_:Head <- Body :: Where), 62 sql_compiler:view(Head,Body,Where)). 63systemterm_expansion( (_:Head <- Body), 64 sql_compiler:view(Head,Body)). 65systemterm_expansion( (Head <- Body), 66 sql_compiler:view(Head,Body)).
Example: load_schema_defs(bio('sql_schema/schema_enscore44'))
74load_schema_defs(File):-
75 consult(File).
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).
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$s156optimize_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(+,?)
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(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.
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 , 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).
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).
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.
(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(_,[]).
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(_) :- !.
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(_).
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].
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(+,?)
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.
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).
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).
Only the equality test shows up in the WHERE clause of an SQLquery.
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)].
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)].
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
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).
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))].
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% ------------------------------------------------------------------------
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).
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).
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),_).
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 ).
dict(var1,..)
..],_)
fails if Key is a member of the dictionary as Quantifier=all1116add_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].
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]).
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,[]).
1209translate_grouping(FunctionVariable,Dict,SQLGroup):-
1210 free_vars(FunctionVariable,Dict,FreeVariables),
1211 translate_free_vars(FreeVariables,SQLGroup).
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).
RESTRICTION: FunctionVariable may only contain one single variable.
1242function_variable_list('$var$'(VarId),[VarId]).
1254translate_free_vars([],[]). 1255translate_free_vars([(_VarId,Table,Attribute)|FreeVars],[att(Table,Attribute)|SQLGroups]):- 1256 translate_free_vars(FreeVars,SQLGroups).
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% ----------------------------------------------------------------
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.
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).
1608lgensym(L,Symbol):-
1609 concat_atom(L,Root),
1610 gensym(Root,Symbol).
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(>=,<).
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).
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.
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').
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..
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:
TODO
problem with existentials being in the projection
works:
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.
should translate to:
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.
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 */