1 / 21

Automatic Generation of SQLX View Definitions from ORA-SS Views

Automatic Generation of SQLX View Definitions from ORA-SS Views. Yabing Chen, Tok Wang Ling, Mong Li Lee. Outline. Introduction Preliminaries Generating SQLX Query Definitions From ORA-SS Views Conclusion. Introduction. XML The dominant standard for exchanging data on the Internet

kordell
Download Presentation

Automatic Generation of SQLX View Definitions from ORA-SS Views

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related