home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #31 / NN_1992_31.iso / spool / comp / lang / prolog / 2292 < prev    next >
Encoding:
Internet Message Format  |  1992-12-22  |  16.4 KB
  1. Path: sparky!uunet!dtix!darwin.sura.net!jvnc.net!yale.edu!ira.uka.de!fauern!lrz-muenchen.de!mac_server.cis.uni-muenchen.de!user
  2. From: draxler@cis.uni-muenchen.de (Christoph Draxler)
  3. Newsgroups: comp.lang.prolog
  4. Subject: Prolog to SQL compiler v. 1.1 (part 2/2)
  5. Message-ID: <draxler-221292135550@mac_server.cis.uni-muenchen.de>
  6. Date: 22 Dec 92 12:50:31 GMT
  7. Sender: news@news.lrz-muenchen.de (Mr. News)
  8. Followup-To: comp.lang.prolog
  9. Organization: CIS - Centrum fuer Informations- und Sprachverarbeitung
  10. Lines: 687
  11.  
  12. % --- CODE for SQL compiler continued from part 1/2 ---------
  13.  
  14.  
  15. %
  16. --------------------------------------------------------------------------------------
  17. %
  18. % Output to screen predicates - rather crude at the moment
  19. %
  20. %
  21. --------------------------------------------------------------------------------------
  22.  
  23.  
  24. % --- printqueries(Code)
  25. ---------------------------------------------------------------
  26.  
  27. printqueries([Query]):-
  28.    nl,
  29.    print_query(Query),
  30.    write(';'),
  31.    nl,
  32.    nl.
  33.  
  34. printqueries([Query|Queries]):-
  35.    not (Queries = []),
  36.    nl,
  37.    print_query(Query),
  38.    nl,
  39.    write('UNION'),
  40.    nl,
  41.    printqueries(Queries).
  42.  
  43.  
  44.  
  45. % --- print_query(QueryCode)
  46. -----------------------------------------------------------
  47.  
  48. print_query(query([agg_query(Function,Select,From,Where,Group)],_,_)):-
  49.    % --- ugly rule here: aggregate function only in SELECT Part of query
  50. ----
  51.    !,
  52.    print_query(agg_query(Function,Select,From,Where,Group)).
  53.  
  54. print_query(query(Select,From,Where)):-
  55.    print_clause('SELECT',Select,','),
  56.    nl,
  57.    print_clause('FROM',From,','),
  58.    nl,
  59.    print_clause('WHERE',Where,'AND'),
  60.    nl.
  61.  
  62. print_query(agg_query(Function,Select,From,Where,Group)):-
  63.    print_clause('SELECT',Function,Select,','),
  64.    nl,
  65.    print_clause('FROM',From,','),
  66.    nl,
  67.    print_clause('WHERE',Where,'AND'),
  68.    nl,
  69.    print_clause('GROUP BY',Group,',').
  70.  
  71. print_query(negated_existential_subquery(Select,From,Where)):-
  72.    write('NOT EXISTS'),
  73.    nl,
  74.    write('('),
  75.    print_clause('SELECT',Select,','),
  76.    nl,
  77.    print_clause('FROM',From,','),
  78.    nl,
  79.    print_clause('WHERE',Where,'AND'),
  80.    nl,
  81.    write(')').
  82.  
  83.  
  84.  
  85.  
  86. % --- print_clause(Keyword,ClauseCode,Separator)
  87. ---------------------------------------
  88. %
  89. % with 
  90. % Keyword    one of SELECT, FROM, WHERE, or GROUP BY, 
  91. % ClauseCode the code corresponding to the appropriate clause of an SQL
  92. query, and 
  93. % Separator  indicating the character(s) through which the items of a
  94. clause
  95. %            are separated from each other (',' or 'AND').
  96. % 
  97. %
  98. --------------------------------------------------------------------------------------
  99.  
  100. print_clause(Keyword,[],_).
  101.  
  102. print_clause(Keyword,[Column|RestColumns],Separator):-
  103.    write(Keyword),
  104.    write(' '),
  105.    print_clause([Column|RestColumns],Separator).
  106.  
  107. print_clause(Keyword,Function,[Column],Separator):-
  108.    write(Keyword),
  109.    write(' '),
  110.    write(Function),
  111.    write('('),
  112.    print_clause([Column],Separator),
  113.    write(')').
  114.  
  115.  
  116.  
  117.  
  118.  
  119. % --- print_clause(ClauseCode,Separator)
  120. -----------------------------------------------
  121.  
  122. print_clause([Item],_):-
  123.    print_column(Item).
  124.  
  125. print_clause([Item,NextItem|RestItems],Separator):-
  126.    print_column(Item),
  127.    write(' '),
  128.    write(Separator),
  129.    write(' '),
  130.    print_clause([NextItem|RestItems],Separator).
  131.  
  132.  
  133.  
  134.  
  135. % --- print_column(ColumnCode) --------------------------------
  136.  
  137. print_column('*'):-
  138.    write('*').
  139.  
  140. print_column(att(RangeVar,Attribute)):-
  141.    write(RangeVar),
  142.    write('.'),
  143.    write(Attribute).
  144.  
  145. print_column(rel(Relation,RangeVar)):-
  146.    write(Relation),
  147.    write(' '),
  148.    write(RangeVar).
  149.  
  150. print_column(const(String)):-
  151.    get_type(const(String),string),
  152.    write('"'),
  153.    write(String),
  154.    write('"').
  155.  
  156. print_column(const(Number)):-
  157.    get_type(const(Number),NumType),
  158.    type_compatible(NumType,number),
  159.    write(Number).
  160.  
  161. print_column(comp(LeftArg,Operator,RightArg)):-
  162.    print_column(LeftArg),
  163.    write(' '),
  164.    write(Operator),
  165.    write(' '),
  166.    print_column(RightArg).
  167.  
  168. print_column(LeftExpr * RightExpr):-
  169.    print_column(LeftExpr),
  170.    write('*'),
  171.    print_column(RightExpr).
  172.  
  173. print_column(LeftExpr / RightExpr):-
  174.    print_column(LeftExpr),
  175.    write('/'),
  176.    print_column(RightExpr).
  177.  
  178. print_column(LeftExpr + RightExpr):-
  179.    print_column(LeftExpr),
  180.    write('+'),
  181.    print_column(RightExpr).
  182.  
  183. print_column(LeftExpr - RightExpr):-
  184.    print_column(LeftExpr),
  185.    write('-'),
  186.    print_column(RightExpr).
  187.  
  188. print_column(agg_query(Function,Select,From,Where,Group)):-
  189.    nl,
  190.    write('('),
  191.    print_query(agg_query(Function,Select,From,Where,Group)),
  192.    write(')').
  193.  
  194. print_column(negated_existential_subquery(Select,From,Where)):-
  195.    print_query(negated_existential_subquery(Select,From,Where)).
  196.  
  197.  
  198.  
  199.  
  200.  
  201. % --- queries_atom(Queries,QueryAtom) ---------------------------- 
  202. %
  203. % queries_atom(Queries,QueryAtom) returns in its second argument
  204. % the SQL query as a Prolog atom. For efficiency reasons, a list
  205. % of ASCII codes is ceated as a difference list, and it is then 
  206. % transformed to an atom by name/2
  207. % ---------------------------------------------------------------- 
  208.  
  209.  
  210. queries_atom(Queries,QueryAtom):-
  211.    queries_atom(Queries,QueryList,[]),
  212.    name(QueryAtom,QueryList).
  213.  
  214.  
  215.  
  216. queries_atom([Query],QueryList,Diff):-
  217.    query_atom(Query,QueryList,Diff).
  218.  
  219. queries_atom([Query|Queries],QueryList,Diff):-
  220.    Queries \= [],
  221.    query_atom(Query,QueryList,X1),
  222.    column_atom('UNION',X1,X2),
  223.    queries_atom(Queries,X2,Diff).
  224.  
  225.  
  226.  
  227. % --- query_atom(QueryCode) --------------------------------
  228.  
  229. query_atom(query([agg_query(Function,Select,From,Where,Group)],_,_),QueryList,Diff):-
  230.    % --- ugly rule here: aggregate function only in SELECT Part of query
  231. ----
  232.    !,
  233.    query_atom(agg_query(Function,Select,From,Where,Group),QueryList,Diff).
  234.  
  235. query_atom(query(Select,From,Where),QueryList,Diff):-
  236.    clause_atom('SELECT',Select,',',QueryList,X1),
  237.    clause_atom('FROM',From,',',X1,X2),
  238.    clause_atom('WHERE',Where,'AND',X2,Diff).
  239.  
  240. query_atom(agg_query(Function,Select,From,Where,Group),QueryList,Diff):-
  241.    clause_atom('SELECT',Select,',',QueryList,X1),
  242.    clause_atom('FROM',From,',',X1,X2),
  243.    clause_atom('WHERE',Where,'AND',X2,X3),
  244.    clause_atom('GROUP BY',Group,',',X3,Diff).
  245.  
  246. query_atom(negated_existential_subquery(Select,From,Where),QueryList,Diff):-
  247.    column_atom('NOT EXISTS(',QueryList,X1),   
  248.    clause_atom('SELECT',Select,',',X1,X2),
  249.    clause_atom('FROM',From,',',X2,X3),
  250.    clause_atom('WHERE',Where,'AND',X3,X4),
  251.    column_atom(')',X4,Diff).
  252.  
  253.  
  254.  
  255.  
  256. % --- clause_atom(Keyword,ClauseCode,Junctor,CurrAtom,QueryAtom)
  257. -------------
  258. %
  259. % with 
  260. % Keyword    one of SELECT, FROM, WHERE, or GROUP BY, 
  261. % ClauseCode the code corresponding to the appropriate clause of an SQL
  262. query, and 
  263. % Junctor    indicating the character(s) through which the items of a
  264. clause
  265. %            are separated from each other (',' or 'AND').
  266.  
  267. clause_atom(Keyword,[],_,QueryList,QueryList).
  268.  
  269. clause_atom(Keyword,[Column|RestColumns],Junctor,QueryList,Diff):-
  270.    column_atom(Keyword,QueryList,X1),
  271.    column_atom(' ',X1,X2),
  272.    clause_atom([Column|RestColumns],Junctor,X2,X3),
  273.    column_atom(' ',X3,Diff).
  274.  
  275. clause_atom(Keyword,Function,[Column],Junctor,QueryList,Diff):-
  276.    column_atom(Keyword,QueryList,X1),
  277.    column_atom('(',X1,X2),
  278.    clause_atom([Column],Junctor,X2,X3),
  279.    column_atom(')',X3,Diff).
  280.  
  281.  
  282.  
  283.  
  284.  
  285.  
  286. % --- clause_atom(ClauseCode,Junctor) --------------------------------
  287.  
  288. clause_atom([Item],_,QueryList,Diff):-
  289.    column_atom(Item,QueryList,Diff).
  290.  
  291. clause_atom([Item,NextItem|RestItems],Junctor,QueryList,Diff):-
  292.    column_atom(Item,QueryList,X1),
  293.    column_atom(' ',X1,X2),
  294.    column_atom(Junctor,X2,X3),
  295.    column_atom(' ',X3,X4),
  296.    clause_atom([NextItem|RestItems],Junctor,X4,Diff).
  297.  
  298.  
  299.  
  300.  
  301.  
  302. column_atom(att(RangeVar,Attribute),QueryList,Diff):-
  303.    column_atom(RangeVar,QueryList,X1),
  304.    column_atom('.',X1,X2),
  305.    column_atom(Attribute,X2,Diff).
  306.  
  307. column_atom(rel(Relation,RangeVar),QueryList,Diff):-
  308.    column_atom(Relation,QueryList,X1),
  309.    column_atom(' ',X1,X2),
  310.    column_atom(RangeVar,X2,Diff).
  311.  
  312. column_atom(const(String),QueryList,Diff):-
  313.    get_type(const(String),string),
  314.    column_atom('"',QueryList,X1),
  315.    column_atom(String,X1,X2),
  316.    column_atom('"',X2,Diff).
  317.  
  318. column_atom(const(Number),QueryList,Diff):-
  319.    get_type(const(Number),NumType),
  320.    type_compatible(NumType,number),
  321.    column_atom(Number,QueryList,Diff).
  322.  
  323. column_atom(comp(LeftArg,Operator,RightArg),QueryList,Diff):-
  324.    column_atom(LeftArg,QueryList,X1),
  325.    column_atom(' ',X1,X2),
  326.    column_atom(Operator,X2,X3),
  327.    column_atom(' ',X3,X4),
  328.    column_atom(RightArg,X4,Diff).
  329.  
  330. column_atom(LeftExpr * RightExpr,QueryList,Diff):-
  331.    column_atom(LeftExpr,QueryList,X1),
  332.    column_atom('*',X1,X2),
  333.    column_atom(RightExpr,X2,Diff).
  334.  
  335. column_atom(LeftExpr + RightExpr,QueryList,Diff):-
  336.    column_atom(LeftExpr,QueryList,X1),
  337.    column_atom('+',X1,X2),
  338.    column_atom(RightExpr,X2,Diff).
  339.  
  340. column_atom(LeftExpr - RightExpr,QueryList,Diff):-
  341.    column_atom(LeftExpr,QueryList,X1),
  342.    column_atom('-',X1,X2),
  343.    column_atom(RightExpr,X2,Diff).
  344.  
  345. column_atom(LeftExpr / RightExpr,QueryList,Diff):-
  346.    column_atom(LeftExpr,QueryList,X1),
  347.    column_atom('/',X1,X2),
  348.    column_atom(RightExpr,X2,Diff).
  349.  
  350. column_atom(agg_query(Function,Select,From,Where,Group),QueryList,Diff):-
  351.    column_atom('(',QueryList,X1),
  352.    query_atom(agg_query(Function,Select,From,Where,Group),X1,X2),
  353.    column_atom(')',X2,Diff).
  354.  
  355. column_atom(negated_existential_subquery(Select,From,Where),QueryList,Diff):-
  356.   
  357. query_atom(negated_existential_subquery(Select,From,Where),QueryList,Diff).
  358.  
  359.  
  360. column_atom(Atom,List,Diff):-
  361.    atom(Atom),
  362.    name(Atom,X1),
  363.    append(X1,Diff,List).
  364.  
  365.  
  366.  
  367.  
  368.  
  369.  
  370. % --- benchmarks of sample queries
  371. --------------------------------------------
  372. %
  373. % benchmark(N,No,Duration) runs the query No N times and returns the
  374. runtime
  375. %
  376. % To run each benchmark once, enter ?- benchmark(1,No,D). and type ; after
  377. % a solution has been returned.
  378. %
  379. %
  380. -----------------------------------------------------------------------------
  381.  
  382. benchmark(N,1,D):-
  383.    cpu_time(N,
  384.      (translate(flight(No,Dep,Dest,Type),flight(No,Dep,Dest,Type),Code),
  385.       printqueries(Code)),
  386.    D).
  387.  
  388. benchmark(N,2,D):-
  389.    cpu_time(N,
  390.      (translate(capacity(No,Dep,Dest,Type,Seats),
  391.         (flight(No,Dep,Dest,Type),
  392.          plane(Type,Seats),
  393.          Type='b-737'),Code),
  394.        printqueries(Code)),
  395.    D).
  396.  
  397. benchmark(N,3,D):-
  398.    cpu_time(N,
  399.       (translate(no_planes(No,Dep,Dest,Type),
  400.           (flight(No,Dep,Dest,Type),
  401.            not plane(Type,Seats)),Code),
  402.        printqueries(Code)),
  403.     D).
  404.  
  405. benchmark(N,4,D):-
  406.    cpu_time(N,(translate(X,X is
  407. count(S,plane(P,S)),Code),printqueries(Code)),D).
  408.  
  409. benchmark(N,5,D):-
  410.    cpu_time(N,
  411.       (translate(big_planes(munich,Dest,Type,Seats),
  412.           FNo^(flight(FNo,munich,Dest,Type),
  413.                plane(Type,Seats),
  414.                Seats > avg(S, T^plane(T,S))),Code),
  415.       printqueries(Code)),
  416.    D).
  417.  
  418. benchmark(N,6,D):-
  419.    cpu_time(N,(
  420.      translate(big_planes(munich,Dest,Type,Seats),
  421.          FNo^(flight(FNo,munich,Dest,Type),
  422.               plane(Type,Seats),
  423.               Seats > avg(S, T^plane(T,S))),Code),
  424.          printqueries(Code)),
  425.    D).
  426.  
  427. benchmark(N,7,D):-
  428.    cpu_time(N,(
  429.      translate(big_planes(munich,Dest,Type,Seats),
  430.          FNo^(flight(FNo,munich,Dest,Type),
  431.               plane(Type,Seats),
  432.               Seats > avg(S, T^plane(T,S))),Code),
  433.          queries_atom(Code,SQLQueryAtom),
  434.          writeq(query_atom(SQLQueryAtom)),
  435.          nl),
  436.    D).
  437.  
  438.  
  439.  
  440.  
  441. % --- gensym(Root,Symbol)
  442. ----------------------------------------------------
  443. %
  444. % SEPIA 3.0.7. version - other Prolog implementations provide gensym/2
  445. % and init_gensym/1 as built-ins. */
  446. %
  447. % (C) Christoph Draxler, Aug. 1992
  448. %
  449. % 
  450.  
  451. gensym(Root,Symbol):-
  452.    not var(Root),
  453.    var(Symbol),
  454.  
  455.    incval(Root),
  456.    getval(Root,Counter),
  457.  
  458.    % --- create char list of root and counter ---
  459.    name(Root,RootList),
  460.    name(Counter,CounterList),
  461.  
  462.    % --- append RootList and CounterList ---
  463.    append(RootList,CounterList,SymbolList),
  464.  
  465.    % --- create atom from SymbolList ---
  466.    name(Symbol,SymbolList).
  467.  
  468.  
  469. init_gensym(Root):-
  470.    not var(Root),
  471.    setval(Root,0).
  472.  
  473.  
  474.  
  475. % --- auxiliary predicates
  476. -----------------------------------------------------
  477. %
  478. % Some Prologs have Bem, some donBt. 
  479. %
  480. %
  481. ------------------------------------------------------------------------------
  482.  
  483. append([],L,L).
  484. append([H1|L1],L2,[H1|L3]):-
  485.    append(L1,L2,L3).
  486.  
  487.  
  488.  
  489. member(X,[X|_]).
  490. member(X,[_|T]):-
  491.    member(X,T).
  492.  
  493.  
  494.  
  495. repeat_n(N):-
  496.    integer(N),
  497.    N > 0,
  498.    repeat_1(N).
  499.  
  500. repeat_1(1):-!.
  501. repeat_1(_).
  502. repeat_1(N):-
  503.    N1 is N-1,
  504.    repeat_1(N1).
  505.  
  506.  
  507.  
  508. % --- benchmark programs for translation of database goals ---------------
  509. %
  510. % taken from R. O'Keefe: The Craft of Prolog, MIT Press 1990
  511. %
  512. % Sepia Prolog version
  513.  
  514. cpu_time(Time):-
  515.    cputime(Time).
  516.  
  517.  
  518. cpu_time(Goal,Duration):-
  519.    !,
  520.    cputime(T1),
  521.    (call(Goal) -> true; true),
  522.    cputime(T2),
  523.    Duration is T2 - T1.
  524.  
  525. cpu_time(N,Goal,Duration):-
  526.    !,
  527.    cpu_time((repeat_n(N),(Goal -> fail);true),D1),
  528.    cpu_time((repeat_n(N),(true -> fail);true),D2),
  529.    Duration is D1 - D2.
  530.  
  531.  
  532.  
  533. % --- set_difference(SetA,SetB,Difference)
  534. --------------------------------------------
  535. %
  536. % SetA - SetB = Difference
  537.  
  538. set_difference([],_,[]).
  539.  
  540. set_difference([Element|RestSet],Set,[Element|RestDifference]):-
  541.    not member(Element,Set),
  542.    set_difference(RestSet,Set,RestDifference).
  543.  
  544. set_difference([Element|RestSet],Set,RestDifference):-
  545.    member(Element,Set),
  546.    set_difference(RestSet,Set,RestDifference).
  547.  
  548.  
  549.  
  550.  
  551. % --- Meta Database for schema definition of SQL DB in Prolog
  552. --------------------------
  553. %
  554. % maps Prolog predicates to SQL table names, Prolog predicate argument
  555. positions to SQL
  556. % attributes, and Prolog operators to SQL operators. 
  557. %
  558. % ATTENTION! It is assumed that the arithmetic operators in Prolog and SQL
  559. are the same,
  560. % i.e. + is addition in Prolog and in SQL, etc. If this is not the case,
  561. then a mapping
  562. % function for arithmetic operators is necessary too.
  563. %
  564. % CHANGE THIS TO MEET YOUR DATABASE SCHEMA!
  565. %
  566. %
  567. --------------------------------------------------------------------------------------
  568.  
  569.  
  570. % --- relation(PrologFunctor,Arity,SQLTableName)
  571. ---------------------------------------
  572.  
  573. relation(flight,4,'FLIGHT').
  574. relation(plane,2,'PLANE').
  575.  
  576.  
  577. % --- attribute(PrologArgumentPosition,SQLTableName,SQLAttributeName)
  578. ------------------
  579.  
  580. attribute(1,'FLIGHT','FLIGHT_NO',string).
  581. attribute(2,'FLIGHT','DEPARTURE',string).
  582. attribute(3,'FLIGHT','DESTINATION',string).
  583. attribute(4,'FLIGHT','PLANE_TYPE',string).
  584.  
  585.  
  586. attribute(1,'PLANE','TYPE',string).
  587. attribute(2,'PLANE','SEATS',integer).
  588.  
  589.  
  590. % --- Mapping of Prolog operators to SQL operators
  591. -------------------------------------
  592.  
  593. comparison(=,=).
  594. comparison(<,<).
  595. comparison(>,>).
  596. comparison(@<,<).
  597. comparison(@>,>).
  598.  
  599.  
  600. negated_comparison(=,'<>').
  601. negated_comparison(\=,=).
  602. negated_comparison(>,=<).
  603. negated_comparison(=<,>).
  604. negated_comparison(<,>=).
  605. negated_comparison(>=,<).
  606.  
  607.  
  608. % --- aggregate_function(PrologFunctor,SQLFunction) -----------------
  609.  
  610. aggregate_functor(avg,'AVG').
  611. aggregate_functor(min,'MIN').
  612. aggregate_functor(max,'MAX').
  613. aggregate_functor(sum,'SUM').
  614. aggregate_functor(count,'COUNT').
  615.  
  616.  
  617.  
  618. % --- type system
  619. --------------------------------------------------------------
  620. %
  621. % A rudimentary type system is provided for consistency checking during the
  622. % translation and for output formatting
  623. %
  624. % The basic types are string and number. number has the subtypes integer
  625. and
  626. % real.
  627. %
  628. %
  629. ------------------------------------------------------------------------------
  630.  
  631.  
  632. type_compatible(Type,Type):-
  633.    is_type(Type).
  634. type_compatible(SubType,Type):-
  635.    subtype(SubType,Type).
  636. type_compatible(Type,SubType):-
  637.    subtype(SubType,Type).
  638.  
  639.  
  640. % --- subtype(SubType,SuperType)
  641. -----------------------------------------------
  642. %
  643. % Simple type hierarchy checking
  644. %
  645. %
  646. ------------------------------------------------------------------------------
  647.  
  648. subtype(SubType,SuperType):-
  649.    is_subtype(SubType,SuperType).
  650.  
  651. subtype(SubType,SuperType):-
  652.    is_subtype(SubType,InterType),
  653.    subtype(InterType,SuperType).
  654.  
  655.  
  656.  
  657. % --- is_type(Type)
  658. ------------------------------------------------------------
  659. %
  660. % Type names
  661. %
  662. %
  663. ------------------------------------------------------------------------------
  664.  
  665. is_type(number).
  666. is_type(integer).
  667. is_type(real).
  668. is_type(string).
  669. is_type(natural).
  670.  
  671.  
  672. % --- is_subtype(SubType,SuperType)
  673. --------------------------------------------
  674. %
  675. % Simple type hierarchy for numeric types
  676. %
  677. %
  678. ------------------------------------------------------------------------------
  679.  
  680. is_subtype(integer,number).
  681. is_subtype(real,number).
  682. is_subtype(natural,integer).
  683.  
  684.  
  685. % --- get_type(Constant,Type)
  686. --------------------------------------------------
  687. %
  688. % Prolog implementation specific definition of type retrieval
  689. % sepia Prolog version given here
  690. %
  691. %
  692. ------------------------------------------------------------------------------
  693.  
  694. get_type(const(Constant),integer):-
  695.    number(Constant).
  696.  
  697. get_type(const(Constant),string):-
  698.    atom(Constant).
  699.