automatic generation of sqlx view definitions from ora-ss views
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
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
XMLThe dominant standard for exchanging data
on the Internet XML Views
Exploit the potential of XMLSecure the source dataProvide 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 viewsDifficult to use query languages to define
views 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
em p lo y ee
p r o jec t
en o
jn o
p r o g r es s
p e , 2 , 1 :n , 1 :n
p e
en am e
jn am e
Object relations: project (jno, jname); employee (eno, ename); Relationship relations: pe (jno, eno, progress);
Object-relational storage schema
An ORA-SS source schema
Select xmlelement(“employee_list”, xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename) ) ) )From employee e
Query: List all employees
<employee_list> <employee eno=“e01” ename=“david”/> <employee eno=“e02” ename=“paul”/></employee_list>
Result:
9
Generate SQLX queries from ORA-SS views
Main idea An object in the view is determined by some
particular objects in the view through the relationship types involved.
m em b er
p r o jec t
m n am e
jn o
jo t_ tit le
jn am e
p u b licat io n
p n o p u b _ t it le
jm , 2 , 1 :n , 1 :n
m p , 2 , 1 :n , 1 :n
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
Drop supplier
s u p p lie r
p r o jec t
jn am e
p ar t
s p j,3,1:n ,1:ns n o
p n o
p r ic e
s p j
p s
em p lo y ee
en o en am e p r o g r es s
je
p s , 2, 1:n , 1 :n
s n am e
p n am e
f ac to r y
f n o f n am e
s f ,2 ,1 :n ,1 :n
jn o q ty je ,2,1:n ,1:n
*
em ail
p ar t
p r o jec t
jn am e
p j,2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
Source schema View schema
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);
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.
p ar t
p r o jec t
jn am e
p j, 2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
Select xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename)))From employee e, jeWhere e.eno = je.jno and je.jno = j.jno
SQLX expression for employeeemployee (eno, ename, (email)*);je (jno, eno, progress);
e.g. je
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 type in the where clause
p ar t
p r o jec t
jn am e
p j,2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
Select xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname)))From project j, pjWhere pj.jno = j.jno and pj.pno = p.pno
Select xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname)))From project j, spjWhere spj.jno = j.jno and spj.pno = p.pno
pj = spj [p, j]
spj (sno, pno, jno, qty)project (jno, jname)
e.g. pj
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’S corresponding original
relations in the where clause
p ar t
p r o jec t
jn am e
p j,2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
Select xmlagg( xmlelement(“factory”, xmlattributes(f.fno, f.fname) )From factory f, pfWhere f.fno = pf.fno and pf.pno = p.pno
Select xmlagg( xmlelement(“factory”, xmlattributes(f.fno, f.fname) )From factory f, ps, sfWhere f.fno=sf.fno and sf.sno=ps.sno and
ps.pno=p.pno
Replace pf by ps and sf in ORDB
factory (fno, fname);ps (pno, sno, price); sf (sno, fno);
e.g. pf
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 an xmlelement function for the attribute A:
xmlelement(“A”, R.A)
p ar t
p r o jec t
jn am e
p j,2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
View schema
Select xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename), xmlelement(“progress”, je.progress)))From employee e, jeWhere e.eno = je.jno and je.jno = j.jno
SQLX expression for employee
e.g. progress
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 an xmlelement function for the attribute A with agg function:
xmlelement(“A”, agg(R’.A)) Append a group by clause for the agg function (e.g. total_qty)
p ar t
p r o jec t
jn am e
p j,2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
View schema
Select xmlagg( xmlelement(“project”, xmlattributes(j.jno, j.jname), xmlelement(“total_qty”, sum(spj.qty))))From project j, spjWhere spj.jno = j.jno and spj.pno = p.pnoGroup by j.jno, j.jname
SQLX expression for project
(e.g. total_qty)
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
p ar t
p r o jec t
jn am e
p j,2 ,1 :n ,1 :np n o
p j
p n am e
f ac to r y
f n o f n am e
p f ,2 ,1 :n ,1 :n
jn o to ta l_ q ty
em p lo y ee
en o en am e p r o g r es s
je ,2 ,1 :n ,1 :n
je
*
em ail
View schema
Select xmlagg( xmlelement(“employee”, xmlattributes(e.eno, e.ename) (select xmlagg(xmlelement(“email”, email)) from table(e.email) ))From employee e, jeWhere e.eno = je.jno and je.jno = j.jno
SQLX expression for employee
(e.g. email)
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) ) ) From employee 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
1. Generate the SQLX query based on the view schema (with derived relationship types, etc)
2. 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