Automatic Generation of SQLX View Definitions from ORA-SS Views

1. Automatic Generation of SQLX View Definitions from ORA-SS Views Yabing Chen, Tok Wang Ling, Mong Li Lee

2. Outline • Introduction • Preliminaries • Generating SQLX Query Definitions From ORA-SS Views • Conclusion

3. Introduction • XML • The dominant standard for exchanging data on the Internet • XML Views • Exploit the potential of XML • Secure the source data • Provide an application-specific view

4. Introduction (cont.) • Main related work • In all these works, the original data are in RDB • SilkRoute [1] [2] • Two declarative language RXL and XML-QL to define and query the views over relational data • XPERANTO [3] [4] • uses a canonical mapping to create a default XML view from relational data • Oracle [7], IBM DB2 [8] & SQL Server [6] • provide the ability to export relational data to materialized XML views

5. Introduction (cont.) • Disadvantage of the main related work • Ignore semantic information in source data • For example, ignore the difference between object class, attribute and relationship in schema • Cannot check the validity of designed views • Difficult to use query languages to defineviews • Proprietary language or XQuery

6. Introduction (cont.) • Our approach for XML views • The original data are in XML and then stored in ORDB • Design valid XML Views [9] • Based on a semantically rich model - ORA-SS • Use query operators, such as selection, drop, swap, join, etc. • Support more flexible views than related work, such as swapping views • Transform the designed views into SQLX queries on ORDB (this paper)

7. Preliminaries • ORA-SS data model • Three concepts: Object class, attribute and relationship type • An object-relational storage for XML • Store each object class with all its attributes into a nested relation • Store each relationship type with all its attributes into a nested relation • SQLX syntax • An emerging part of the ANSI and ISO SQL standard • XMLELEMENT • Generate an XML element • XMLAGG • Produce a forest of XML elements from a collection of individual elements

8. Preliminaries – Example Query: List all employees Select xmlelement(“employee_list”, xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename) ) ) ) From employee e An ORA-SS source schema Result: <employee_list> <employee eno=“e01” ename=“david”/> <employee eno=“e02” ename=“paul”/> </employee_list> Object relations: project (jno, jname); employee (eno, ename); Relationship relations: pe (jno, eno, progress); Object-relational storage schema

9. Generate SQLX queries from ORA-SS views • Main idea • An object in the viewis determined by some particular objects in the view through the relationship types involved. • publication is determined by member through relationship type mp • publication has nothing to do with project • member is called the Determined Object Class(DOC) of publication • mp is called the Determined Relationship Type(DRT) of publication

10. Generate SQLX queries from ORA-SS views • Main idea (cont.) • Identify relationship types in the views • Original relationship in the source schema • Derived relationship by projecting existing relation in the ORDB • Derived relationship by joining existing relations in the ORDB Object relations: supplier (sno, sname); part (pno, pname); factory (fno, fname); project (jno, jname); employee (eno, ename, (email)*); Relationship relations: ps (pno, sno, price); sf (sno, fno); spj (sno, pno, jno, qty); je (jno, eno, progress); Drop supplier Source schema View schema

11. General rules for generating SQLX queries from ORA-SS views • Rule Gen 1 for object class along with its attributes • Case 1: DRT is original relationship • Case 2: DRT is derived relationship by projecting existing relation • Case 3: DRT is derived relationship by joining existing relations • Rule Gen 2 for attributes • Case 1: original relationship attributes • Case 2: derived relationship attributes • Case 3: multi-valued attributes

12. Rule Gen 1 (for object class in the view) • Case 1: If the DRT is an original relationship type from the source schema, then • Directly use the original relationship type to generate the where conditions in the where clause. e.g. je Select xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename))) From employee e, je Where e.eno = je.jno and je.jno = j.jno employee (eno, ename, (email)*); je (jno, eno, progress); SQLX expression for employee

13. Rule Gen 1(cont.) • Case 2: if the DRT is a derived relationship type by projecting existing relation in the ORDB, then • Replace the derived relationship type with its corresponding original relationship typein the where clause Select xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname))) From project j, pj Where pj.jno = j.jno and pj.pno = p.pno e.g. pj pj =spj [p, j] Select xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname))) From project j, spj Where spj.jno = j.jno and spj.pno = p.pno spj (sno, pno, jno, qty) project (jno, jname)

14. Rule Gen 1(cont.) • Case 3: If the DRT is a derived relationship type by joining existing relations in the ORDB, then • Rewrite the where condition with the DRT’Scorresponding original relationsin the where clause Select xmlagg( xmlelement(“factory”, xmlattributes(f.fno, f.fname) ) From factory f, pf Where f.fno = pf.fno and pf.pno = p.pno e.g. pf Replace pf byps and sf in ORDB Select xmlagg( xmlelement(“factory”, xmlattributes(f.fno, f.fname) ) From factory f, ps, sf Where f.fno=sf.fno and sf.sno=ps.sno and ps.pno=p.pno factory (fno, fname); ps (pno, sno, price); sf (sno, fno);

15. Rule Gen 2 (For relationship attributes) • Case 1: If a single valued attribute A belongs to R and R is an original relationship type from source schema, then • generate anxmlelementfunction for the attribute A: xmlelement(“A”, R.A) e.g. progress Select xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename), xmlelement(“progress”, je.progress))) From employee e, je Where e.eno = je.jno and je.jno = j.jno SQLX expression for employee View schema

16. Rule Gen 2 (cont.) • Case 2: If an attribute A belongs to R and R is a derived relationship type by projecting an original relation R’ in the ORDB, then • Generate anxmlelement function for the attribute A with aggfunction: xmlelement(“A”, agg(R’.A)) • Append agroup byclausefor the agg function (e.g. total_qty) (e.g. total_qty) Select xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname), xmlelement(“total_qty”, sum(spj.qty)))) From project j, spj Where spj.jno = j.jno and spj.pno = p.pno Group by j.jno, j.jname SQLX expression for project View schema

17. Rule Gen 2 (cont.) • Case 3: If an attribute A is a multi-valued attribute in the ORDB, then • Generate an sub query to extract the value of A (e.g. email) Select xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename) (select xmlagg(xmlelement(“email”, email)) from table(e.email) )) From employee e, je Where e.eno = je.jno and je.jno = j.jno View schema SQLX expression for employee

18. The SQLX query for the whole view example Select //generate root & part xmlelement(“root”, xmlagg( xmlelement(“part”, xmlattributes(p.pno, p.pname), (Select // generate factory xmlagg( xmlelement(“factory”, xmlattributes(f.fno, f.fname) ) ) From factory f, ps, sf Where f.fno = sf.fno and sf.sno = ps.sno and ps.pno = p.pno ), (Select // generate project xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname), xmlelement(“total_qty”, sum(spj.qty)), (Select xmlagg( // generate employee xmlelement(“employee”, xmlattributes(e.eno, e.ename), (select xmlagg(xmlelement(“email”, email)) from table(e.email) ) xmlelement(“progress”, je.progress) ) ) Fromemployee e, je Where e.eno = je.jno and je.jno = j.jno ) ) ) From project j, spj Where j.jno = spj.jno and spj.pno = p.pno Group by j.jno, j.jname ) ) ) From part p

19. Algorithm • Use a deep-first search algorithm to generate the SQLX query for the ORA-SS view • Generate the SQLX query based on the view schema (with derived relationship types, etc) • Replace each derived relationship type by using the corresponding relations in the ORDB • For swapping and selection relationship types, • They refers to the same relations in the ORDB • For selection operator, append the selection condition in the SQLX query

20. Conclusion • Main contribution • An approach to automatically generate SQLX query definitions of ORA-SS views • Remove the need for users to manually write complex SQLX view definitions • Main difference between this paper and ER’03 paper [10] • The storage of XML data • In ER’03: Text file • In this paper: ORDB • Semantics of ORA-SS views (relationship in the views) • In ER’03: Do not utilize the semantics • In this paper: Utilize the semantics (e.g. DRT & DOC)

21. References [1]M. Fernandez, W. Tan, D. Suciu, “Efficient Evaluation of XML Middleware Queries”, ACM SIGMOD, pp. 103-114, 2001. [2] M. Fernandez, W. Tan, D. Suciu, “SilkRoute: Trading Between Relations and XML”, World Wide Web Conference, 1999. [3] M. Carey, J. Kiernan, J. hanmugasundaram, et. al., “XPERANTO: A Middleware for Publishing Object-Relational Data as XML Documents”, VLDB, pp. 646-648, 2000. [4] M. Carey, D. Florescu, Z. Ives, et. al., “XPERANTO: Publishing Object-Relational Data as XML”, WebDB Workshop, 2000. [6] Microsoft Corp. http://www.microsoft.com/XML. [7] Oracle Corp. http://www.oracle.com/XML. [8] IBM Corp. http://www.ibm.com/XML. [9] Y.B. Chen, T.W. Ling, M.L. Lee, “Designing Valid XML Views”, ER Conference, 2002 [10] Y.B.Chen, T.W.Ling, M.L.Lee, “Automatic Generation of XQuery Definitions from ORA-SS Views”, ER Conference 2003. [11] Y.Y.Mo, T.W.Ling, “Storing and Maintaining Semistructured Data Efficiently in an Object-Relational Database”, WISE Conference, 2002. [12] SQLX. http://www.sqlx.org