Leveraging Meta-Programming in Web Services: Integrating Data and Programs for Enhanced SQL

1. Towards ExploitingMeta-Programmingfor Web ServicesGottfried VossenUniversity of Münster, Germany

2. Overview • Background: PL and DB getting back together again • Ways to integrate data and programs(to “meta-program” databases) • “Service-SQL”: SQL at your (web) service

3. Background • Stonebraker: QUEL as a data type (1984) • Gray, Turing award speech (1993) • Asilomar Report (1998) • Gray & Compton: A Call to Arms, ACM Queue (2005) Treat procedural data as data (not as text); give up the separation of data and programs! Done already in data dictionaries, OR databases, active databases, web logs, …

4. Approach I • The sky is the limit: the Reflective Algebra • Data in data relations, programs (RA expressions) in separate program relations (over a generic format) • Allows to compute the PTIME queries (since program relations can be created and evaluated dynamically) • Enables novel search facilities on the web • Catches: untyped setting, languages difficult to use • Refs: PODS ’93 & JCSS ’96also ADBIS ’00 & IDEAS ´00

5. Approach II • Getting real: the Meta Algebra • type-safe language closer to classical algebra • “meta” relations can hold queries and data • manipulation thru new operators for query columns such as extract, rewrite, eval • allows for elegant and concise querying • catch: modern DBMS allow for even more elegant things • refs: PODS ’98 & Information Systems ’99

6. Approach III • Getting practical: Meta-SQL • combining meta querying and XML “nicely” • idea: stay declarative, i.e., with SQL, but exploit modern DBMS functionality (external functions, XML as a data type) • queries stored in “query columns”, but as XML syntax trees • rewrite, extract, etc. done through XSLT functions • eval implemented on top • refs: EDBT ’04 & Information Systems ’05 Joint work with Jan Van den Bussche, Frank Neven, Stijn Vansummeren

7. Meta-Query Samples • Which queries in the log do the most joins?Syntactical; queries stored query expressions • Which queries in the log return an empty answer on the current state of the database?Semantical; answer depends on result of dynamic execution • In each query in the log, replace each view name by its definition from the catalog.Syntactical; performs transformations • Given new view definitions, which stored queries now give new answers?Syntactical and semantical

8. Encoding SQL Queries in XML select director, avg(rating) as avgrat from Movies group by director <query> <select> <sel-item> <column>director</column> </sel-item> <sel-item> <aggregate><avg/> <column-ref><column>rating</column></column-ref> </aggregate> <alias>avgrat</alias> </sel-item> </select> <from> <table-ref> <table>Movies</table> </table-ref> </from> <group-by> <column-ref> <column>director</column> </column-ref> </group-by></query> Foundation: Date‘s BNF grammar for SQL, turned into a DTD

9. Use of XSLT “Which queries (stored in ‘Views’) do the most joins?“assumptions: Views (name: string, def: xml) #joins = #table names function count_tables returns numberbegin<xsl:template match="/"> <xsl:value-of select="count(//table)“/></xsl:template>endselect name from Views where count_tables(def) = (select max(count_tables(def)) from Views)

10. Use of XSLT (cont‘d) “Which views become invalid after removal of the tables listed in `Removed´?” function mentions_tableparam tname stringreturns stringbegin <xsl:param name="tname"/> <xsl:template match="/"> <xsl:if test="//table[string(.)=$tname]"> true </xsl:if> </xsl:template>endselect name from Views, Removedwhere mentions_table(def, Removed.name) = 'true'

11. Tools for (Syntactic) Queries • XML variables • Range over the sub-elements of an XML tree • Range can be narrowed by an XPath expression • Allow to go a from a single to a set of XML documents • XML aggregation • For combining a set of XML documents into a single one

12. Example: Extract Subelements “Find all pairs (v,t) s.t. v is is a view name and t is a table name occurring in the definition of v“ function string_value returns string begin end select v.name, string_value(x) from Views v,x in v.def[//table] returns the string value of a table subelement

13. Log Example Stored queries in column Q of table Log;goal is to find “hot spots,” i.e., subqueries occurring in at least 10 different queries: select s from Log l, s in l.Q[//query] group by s having count(l.Q) >= 10

14. Sample Semantic Query “On table Customer (custid: string, query: xml), (where each query returns a table with columns item, price, …) find the max price of items requested by each customer:” EVAL returns a table select custid, max(t.price) from Customer c, EVAL(c.query) t group by custid t is a standard SQL range var UEVAL available for cases where output schema unknown

15. Web Services XML doc SQL query publish lookup XML doc MetaSQL+eval MetaSQL query utilize

16. Modeling Services • Relational Transducers (Abiteboul, Vianu et al.): • State of an application: relational database(schema + state transition program) • Interaction from outside world: input relations • Response of an application: output relations • Thus: machine maps input to output relations • Here: transducer represented as a database allowing meta programming • For simplification: SQL instead of XML Joint work with Stephan Hagemann

17. R1 O1 I1 A A A B B B Relational Transducer Input . . . input relations servicecall statetransitionprogram . . . database Output . . . output relations

18. R1 I1 A A B B Simplification Input . . . input relations servicecall statetransitionprogram . . . Output database

19. Sample Transducer: Ordering Newspapers • Relations: database: price, available input: order, pay, pending-bills state: pastOrder, pastPay output: sendbill, deliver, unavailable rejectpay, alreadypaid, rebill • State rules: pastOrder(X) +:- order(X); pastPay(X, Y) +:- pay(X, Y);

20. Representation • Database  data tables • Input  parameter tables • Input tuples  service callsthus, we can offer order, pay, and pending-bills services • State  status tables • Output  not explicitly put in tables, just shown interactively • State rules  insertions into status tables

21. ServiceSQL – Supporting Tables Data Tables Parameter Tables Status Tables order pastOrder Price pay pastPay Available

22. Sample Transducer, cont‘d • Output rules: sendbill(X, Y) :- order(X), price(X, Y), NOT pastPay(X,Y); deliver(X) :- pastOrder(X), price(X, Y), pay(X, Y), NOT pastPay(X,Y); unavailable(X) :- order(X), NOT available(X); rejectpay(X) :- pay(X, Y), NOT pastOrder(X); rejectpay(X) :- pay(X, Y), pastOrder(X), NOT price(X, Y); alreadypaid(X) :- pay(X, Y), pastPay(X, Y); rebill(X, Y) :- pending-bills, pastOrder(X), price(X, Y), NOT pastPay(X, Y).

23. Representation • Output rules  select statements, to be executed in parallel • State transitions are services in a Services table w/ • input parameters, • return values, • actions as SQL statements

24. ServiceSQL – Services Table Services service is called as soon as the corresponding input relation is filled.

25. ServiceSQL – Services • Code 1 – order • --sendbillSELECT pr.newspaper, pr.priceFROM order o, price prWHERE o.newspaper = pr.newspaper AND NOT EXISTS(SELECT * FROM pastPay pp WHERE pp.newspaper = o.newspaper AND pp.price = o.price);--unavailableSELECT o.newspaperFROM order oWHERE NOT EXISTS(SELECT * FROM available a WHERE a.newspaper = o.newspaper);--Status updateINSERT INTO pastOrder (newspaper) VALUES (SELECT * FROM order) needs to be executed atomically!  transactional aspects

26. ServiceSQL – Services • Code 2 – pay • --deliverSELECT pr.newspaperFROM pay p, pastOrder po, price prWHERE p.newspaper = po.newspaper and p.newspaper = pr.newspaper and p.price = pr.price and NOT EXISTS(SELECT * FROM pastPay pp where pp.newspaper = po.newspaper and pp.price=po.price);--rejectpay 1SELECT pr.newspaperFROM pay pWHERE NOT EXISTS(SELECT * FROM pastOrder po where po.newspaper = p.newspaper);--rejectpay 2SELECT pr.newspaperFROM pay p, pastOrder poWHERE p.newspaper = po.newspaper and NOT EXISTS(SELECT * FROM price pr where pr.newspaper = p.newspaper and pr.price=p.price);--alreadypaidSELECT pr.newspaperFROM pay p, pastPay ppWHERE p.newspaper = pp.newspaper and p.price = pp.price;--Status updateINSERT INTO pastPay (newspaper, price) VALUES (SELECT * FROM pay)

27. ServiceSQL – Services • Code 3 – pending-bills • SELECT pr.newspaper, pr.priceFROM pastOrder po, price prWHERE po.newspaper = pr.newspaper AND NOT EXISTS(SELECT * FROM pastPay pp WHERE pp.newspaper = pr.newspaper AND pp.price = pr.price); the “rebill” functionality

28. Execution Example • order (Time): [sendbill(Time, 55), unavailable()] • order(Newsweek): [sendbill(Newsweek, 45), unavailable()] • order(Die Zeit): [sendbill(), unavailable(Die Zeit)] • pending-bills(): {(Time, 55), (Newsweek, 45)} • pay(Time, 55): deliver(Time) • pay(Newsweek, 48): rejectpay(Newsweek) • pay(Newsweek, 45): deliver(Newsweek)

29. Ongoing Research • Imagine all this in XML disguise (Meta SQL as described before); how compact can manipulation be, using, e.g., XQuery? • What is needed to simulate which type of transducer? • What about Mealy automata? • Which service functionality requires what level of Service SQL? • What about transactional aspects? Object model applicable? Web services composition considered as federation of SQL services?