Transcript Chapter 8

Chapter 8
Object-oriented Query Languages
1
Object-oriented query language
• OQL (Object Query Language)
– ODMG (Object Database Management Group)
– ODMG-93 release 1.2 (1993)
– The Object Database Standard: ODMG 2.0, Ed. R.G.G. Cattell and D.K.
Barry, Morgan Kaufmann, 1997
• SQL3
– ISO/IEC JTC1 SC21 WG3
– http://speckle.ncsl.nist.edu/~ftp/
• While OQL is trying to bring the best of SQL into the object-oriented
world, SQL3 is trying to bring the best of objet-orientation into the
relational world
2
Using an OODBMS
Declarations in
ODL or PL ODL
Application Source
in [extended] PL
Declaration
Preprocessor
PL Compiler
metadata
ODBMS
Runtime
Application
Binary
Linker
data access
Database
Running
Application
3
ODL
• The coupling of ODL definitions and the host language is tighter
– The host language should be object-oriented (C++, Smalltalk)
– the ODL declarations can be translated directly into declarations of the
host language
Application
Data Structures
Copy and
Translation
Relational
Representation
Transparent
ODBMS
Data Transfer
RDBMS
4
Method signature in ODL
• ODL allows method as property
– A function associated with a class
– Applied to an object and may also take one or more other arguments
• declare the method names and function parameters
– The actual code for a method would be written in the host language
– Function name can be overloaded
• Signature syntax
– Functions parameters are specified to be in, out or inout
– The function may have a return value
– Functions may raise exceptions
5
Adding method signature to the Movie class
– interface Movie
(extent Movies
key (title, year))
{
attribute string title;
attribute integer year;
attribute integer length;
attribute enumeration {color, blackAndWhite} filmType;
relationship set <Star> stars inverse Star::starredIn;
relationship Studio ownedBy inverse Studio::owns;
float lengthInHours() raises (noLengthFound);
starNames(out set <string>);
otherMovies(in Star, out set <Movie>) raises (noSuchStar);
};
6
The extent of a class
• The extent of a type is the set of all instances of the type within a
particular database
– If an object is an instance of the type A, then it will of necessity to be
member of the extent of A
– If a type A is a subtype of type B, then the extent of A is a subset of the
extent of B
• Declaration format
– EXTENT <extent name>
– Must appear immediately after the declaration of the interface name
• OQL queries refer to the extent of a class, not to the class name itself
7
An object-oriented movie schema
interface Movie (extent Movies key (title, year))
{
attribute string title;
attribute integer year;
attribute integer length;
attribute enumeration {color, blackAndWhite} filmType;
relationship Set <Star> stars inverse Star::starredIn
relationship Studio ownedBy inverse Studio::owns;
float lengthInHours() raises (noLengthFound);
starNames (out Set<String>);
otherMovies (in Star, out Set<Movie>) raises (noSuchStar);
};
interface Star (extent Stars key name)
interface Studio (extent Studios key name)
{
{
attribute string name;
attribute string name;
attribute struct Addr
attribute string address;
{string street, string city} address;
relationship Set<Movie> owns
relationship Set<Movie> starredIn
inverse Movie::ownedBy;
inverse Movie::stars;
};
};
8
The OQL type system
• There are no limits in OQL on the depth of nesting of type constructors
• Constants (immutable objects) are constructed as below:
– Basic types
• atomic types: integers, floats, characters, strings, and Booleans
• enumerations: the values in an enumeration are actually declared in ODL. Any
one of these values may be used as a constant
– Complex types
• Set (…), Bag (…), List (…), Array (…), Struct (…)
• The first four of these are called collection types
• Example
– struct(foo: bag(1,2,1), bar: “baz”)
9
Path expression
• If a denotes an object belonging to class C, and p is some property of
the class, then a.p denotes the result of “applying p to a”.
– if p is an attribute, then a.p is the value of that attribute in object a
– if p is a relationship. Then a.p is the object or collection of objects related
to a by relationship p
– if p is a method, then a.p is the result of applying p to a
• OQL uses the arrow () as a synonym for the dot
• For example
–
–
–
–
–
Suppose myMovie is a host-language variable for the Movie object
myMovie.length
myMovie.lengthInHours()
myMovie.stars
myMovie.starNames(myStars)
10
Select-from-where expressions in OQL
• OQL’s select-from-where is similar to SQL’s
– Possible to use any collect-producing expression such as another selectfrom-where expression (for example, in the FROM clause, in the SELECT
clause)
– Use != rather than <>
• The query produces a bag of objects
• Example
– SELECT m.year
FROM Movies m
WHERE m.title = “Gone with the Wind”
11
Example of select-from-where expressions
• Find the names of the stars of Casablanca
– select s.name
from Movies m, m.stars s
where m.title = “Casablanca”;
– Evaluation can be carried out this way:
for each m in Movies do
for each s in m.stars do
if m.title = “Casablanca” then
add s.name to the output bag
12
Eliminating duplicates
• Technically a query produces a bag, rather than a set as an answer
• To eliminate duplicates, use DISTINCT following SELECT
• Example
– SELECT DISTINCT s.name
FROM Movies m, m.stars s
WHERE m.ownedBy.name = “Disney”;
13
Complex output types
• The expression(s) in the SELECT clause can be any expression,
including expressions built using type constructors.
• Example
– Find the set of pairs of stars living at the same address
– select star1: s1, star2: s2
from Stars s1, Stars s2
where s1.addr = s2.addr and s1.name < s2.name;
14
Subqueries
• A select-from-where expression can be present in FROM clause
• The similar capability will be a part of the proposed SQL3
• Example
– Retrieve the stars of the movies made by Disney
– select distinct s.name
from Movies m, m.stars s
where m.ownedBy.name = “Disney”
– select distinct s.name
from (select m
from Movies m
where m.ownedBy.name = “Disney”) d, d.stars s
15
Ordering the result
• We can make the output be a list with an ORDR BY clause, while the
result of OQL is normally either a bag or (if distinct is used) a set
• Example
– find the set of Disney movies, but let the result be a list of movies, ordered
by length. If there are ties, let the movies of equal length be ordered
alphabetically.
– Select m
from Movies m
where m.ownedBy.name = “Disney”
order by m.length, m.title;
• ASC vs DESC
16
Quantifier expressions
• We can test whether all, or at least one, member of a set satisfies some
condition
– FOR ALL x IN S : C(x)
– EXISTS x IN S : C(x)
• Example
– Find all the stars of Disney movies
select s
from Stars s
where exists m in s.starredIn : m.ownedBy.name = “Disney”
– Find stars that have appeared only in Disney movies
select s
from Stars s
where for all m in s.starredIn : m.ownedBy.name = “Disney”;
17
Aggregations
• OQL uses the same five aggregation operators that SQL does
– AVG, COUNT, SUM, MIN and MAX
– the operators in OQL apply to collections whose members are of a suitable
type
• Example
– To compute the average length of all movies
– AVG (select m.length from Movies m)
18
Group-by expressions (1)
• The form of GROUP BY clause
– GROUP BY f1:e1, f2:e2, …, fn:en
• When there is only one variable x in the FROM clause
–
–
–
–
The value of x ranges over some collection C
The actual value returned by the GROUP BY is a set of structures
Struct (f1:v1, f2:v2, …, fn:vn, partition: P)
v1, v2, …, vn are a list of values that comes from evaluating e1(i), e2(i), …,
en(i) for at least one value of i in the collection C
– The last field has the special name Partition. P is a bag consisting of
structures of the form
Struct (x:i)
– The SELECT clause may refer only to the fields in the result of the
GROUP BY, namely f1, f2, …, fn, partition
19
Group-by expressions (2)
• The variables appearing in the FROM clause are x1, x2, …, xk
– All variables x1, x2, …, xk may be used in the expressions e1, e2, …, en of
the GROUP BY clause
– Structures in the bag that is the value of the partition field have fields
named x1, x2, …, xk
– Suppose i1, i2, …, ik are values for x1, x2, …, xk, respectively, that make
the WHERE clause true. Then there is a structure in the set that is the
result of the GROUP BY of the form
Struct(f1:e1 (i1, i2, …, ik ), …, fn:en(i1, i2, …, ik ), partition P)
and in bag P is the structure:
Struct(x1:i1, x2:i2, …, xk:ik)
20
Example of group-by expressions
• Build a table of the total length of movies for each studio and for each
year
– select std, yr, sumLength: SUM(select p.m.length from partition p)
from Movies m
group by std: m.ownedBy.name, yr: m.year
– Result of group by: a set of structures
21
Having clauses
• To eliminate some of the groups created by the GROUP BY, use a
HAVING clause
– HAVING <condition>
• Example
– Compute the sum of the lengths of movies for only those studios and years
such that the studio produced at least one movie of 120 minutes
– select std, yr, sumLength: SUM (select p.m.length from partition p)
from Movies m
group by std : m.ownedBy.name, yr : m.year
having max (select p.m.length from partition p) > 120
22
Set operators
• The operators UNION, INTERSECT, EXCEPT operate on either sets
or bags
– Set-oriented process takes place only if both operands are sets
• Rules for operating on bags
–
–
–
–
Suppose an object x appears n1 and n2 times in B1 and B2 respectively
In B1  B2, x appears n1+n2 times
In B1  B2, x appears min (n1, n2) times
In B1  B2, x appears
• if n1  n2, then 0 times
• if n1 > n2, then n1 - n2 times
23
Example of set operators
• Find the set of movies starring Harrison Ford that were not made by
Disney
– (select distinct m
from Movies m, m.stars s
where s.name = “Harrison Ford”)
except
(select distinct m
from Movies m
where m.ownedBy.name = “Disney”)
24
Assigning values to host-language variables
• It is possible to assign result of OQL expressions to host-language
variable of the proper type
• Example
– Retrieve all movies made before 1920
– select distinct m
from Movies m
where m.year < 1920
– if oldMovies is a host-language variable of set<Movie>
– oldMovies = select distinct m
from Movies m
where m.year < 1920;
25
Extracting elements of collections
• OQL provides the operator ELEMENT to turn a singleton set or bag
into its lone member
• Example
– gwtw = element (select m
from Movies m
where m.title = “Gone With The Wind”
);
– The SELECT statement must return a bag with one element only
26
Obtaining each member of a collection
• Need to make a set (bag) into a list, using ORDER BY
• Can access ith element by L[i-1]
• Example
– A C++ function that prints the title, year, and length of each movie
– movieList = select m
from Movies m
order by m.title, m.year;
numberOfMovies = count(Movies);
for (i = 0; i < numberOfMovies; i++) {
movie = movieList[i];
cout << movie.title << “ “ << movie.year << “ “
<< movie.length << “\n”;
}
27
Creating new objects
• OQL expressions allow us to create new object
– In place of the triangular brackets that are used describe types, the round
parentheses are used when we are constructing values
• Example
– x = Struct (a:1, b:2);
y = Bag (x, x, Struct (a:3, b:4));
– Suppose that the type StarPair is struct{star1: Star, star2: Star}
– Select distinct StarPair(star1: s1, star2: s2)
from Stars s1, Stars s2
where s1.addr = s2.addr and s1.name < s2.name
– The query returns Set<StarPair>
28
Tuple objects in SQL3
• OQL has no specific notion of relation, objects in SQL3 keep relations
as a central concept
• Objects in SQL3
– Row objects
– Abstract Data Types (ADT, value ADT)
29
Row types
• In SQL3, one can define a row type T
– CREATE ROW TYPE T ( <component declarations> )
• Example
– Create row type AddressType (
street char(50),
city char(20)
);
Create row type StarType (
name char(30),
address AddressType
);
30
Declaring relations with a row type
• May declare one or more relations whose tuples are of a row type
– CREATE TABLE <table_name> OF TYPE < row type name>
• Example
– Create table MovieStar of type StarType;
• To access a component, use a double-dot notation that corresponds
closely to the single-dot notation of OQL or C
• Example
– Find the name and street address of each star living in Beverly Hills
– select MovieStar.name, MovieStar.address..street
from MovieStar
where MovieStar.address..city = ‘Beverly Hills’;
// “MovieStar.” is not necessary
31
References
• If T is a row type, then REF(T) is the type of a reference to a tuple of
type T
• Example
– Create row type MovieType (
title char(30),
year integer,
inColor bit(1) );
Create table Movie of type MovieType;
Create row type StarType (
name char(30),
address AddressType,
bestMovie ref(MovieType));
// reference to the star’s best movie
32
Example of references
• Represent the stars-in relationship (M:N) between movies and stars
– Is likely that use of collection types as attribute types will be deferred to
SQL4
– Hence, need to create a separate relation for the M:N relationship
– Create row type MovieType (
title char(30), year integer, inColor bit(1));
Create row type AddressType (street char(50), city char(20));
Create row type StarType (name char(30), address AddressType);
Create row type StarsInType (
star ref(StarType), movie ref(MovieType));
Create table Movie of type MovieType;
Create table MovieStar of type StarType;
Create table StarsIn of type StarsInType;
33
Following references
• In SQL3, -> is used for dereferencing (same meaning as in C)
• if x is a reference to a tuple t and a is an attribute of t, then x -> a is the
value of the attribute a in tuple t
• Example
– Find the titles of all the movies in which Mel Gibson starred
– Select movie->title
from StarsIn
where star->name = ‘Mel Gibson’;
34
Dereferencing and component-extraction
• The dot and -> operators are synonyms in OQL
– each applies to an OQL object that is a tuple and returns a component of
that object
• In SQL3
– “->” is applicable to a reference to tuple
– dot operator is applicable to tuple variable
– if r is a reference to a tuple t, then r->a yields the same value as t..a
35
Domains and row types
• Major differences between domains and row types
– Domains define types for components, while row types are types for entire
tuples
– Two domains can represent the same type and values from those domains
will not be distinguished
– However, suppose two row types T1 and T2 have identical definitions.
Then tuples from relations with those types cannot be interchanged
• An attribute whose type is a reference to a T1 cannot be made to refer to a
tuple whose type is T2
36
Scopes of references
• SQL3 provides a mechanism for specifying which relation a reference
attribute refers to
– SCOPE FOR <attribute> IS <relation>
– The named attribute whose type must be a reference, always refers to a
tuple of the named relation
• Example
– Create row type StarsInType (
star ref(StarType),
movie ref(MovieType) );
Create table StarsIn of type StarsInType
scope for star is MovieStar,
scope for movie is Movie;
– Useful when there are other relations declared to be type StarType or
MovieType
37
Object identifiers as values
• In SQL3, we can refer to object IDs explicilty
– VALUES FOR <attribute> ARE SYSTEM GENERATED
• Example
– create row type MovieType (
movie_id ref(MovieType),
title char(30),
year integer,
inColor bit(1));
Create table Movie of type MovieType
values for movie_id are system generated;
38
Abstract data types in SQL3
• Row types are not encapsulated !
• SQL3 has another “class” definition, abstract data type (ADT), that
does support encapsulation
– Objects of an ADT are used as components of tuples, not as tuples
themselves
39
Defining ADT’s
• ADT definition :
– CREATE TYPE <type name> (
list of attributes and their types
optional declaration of = and < functions for the type
declaration of functions (methods) for the type
);
• SQL3 provides certain “built-in” functions with every ADT, and these
do not need to be declared or defined
– Constructor function that returns a new object of the type
– Observer functions for each attribute that return the value of this attribute
– Mutator functions for each attribute that set the value of that attribute to a
new value
40
Example of defining ADT’s
– Create type AddressADT (
street char(50),
city char(20),
equals addrEq,
less than addrLT,
other function could be declared here );
– Create type Mpeg (
video blob,
length integer,
copyright varchar(255),
equals default,
less than none,
definitions of functions go here );
41
Defining methods for ADT’s
• After the attribute list of an ADT, we can append any list of function
declarations
– FUNCTION <name> ( <arguments> ) RETURNS <type> ;
• Functions are of two types: internal and external
– external: written in host language and only their signature appears in the
definition of the ADT
– internal: written in an extended SQL
– some options
• := assignment operator
• A variable local can be declared by giving its name, preceded by a colon and
followed by its type
• The dot operator is used to access components of a structure
• Boolean values can be expressed as in WHERE clauses
• BEGIN and END are used to collect several arguments into the body of a
function
42
ADT definition example (1/2)
– Function AddressADT (:s char(50), :c char(20)) returns AddressADT;
:a AddressADT;
begin
:a := AddressADT(); // built-in constructor
:a.street := :s;
:a.city := :c;
return :a;
end;
– Function addrEq (:a1 AddressADT, :a2 AddressADT) returns boolean;
return(:a1.street = :a2.street and :a1.city = :a2.city);
43
ADT definition example (2/2)
– Function addrLT (:a1 AddressADT, :a2 AddressADT) returns boolean;
return ((:a1.city < :a2.city) or
(:a1.city = :a2.city) and (:a1.street < :a2.street) );
– Function fullAddr (:a AddressADT) returns char(82);
:z char(10);
begin
:z = findZip (:a.street, :a.city);
return(:a.street || ‘ ‘ || :a.city || ‘ ‘ || :z);
end;
44
External functions
• ADTs may also have methods that are written in some host language
rather than in SQL3
– DECLARE EXTERNAL <function name> <signature>
LANGUAGE <language name>
• Example
– DECLARE EXTERNAL findZip
char(50) char(20) RETURNS char(10)
LANGUAGE C;
45
Comparison of ODL/OQL and SQL3
ODL/OQL
SQL3 row types
SQL3 ADTs
Programming
environment
Embedded in an OO
programming language
Limited interface
between SQL and hostlanguage variables
Additionally
external function
Role of
relations
Weak
Strong
Strong
Encapsulation
Encapsulated
Not encapsulated
Encapsulated
Extents for
classes
Maintain single extent
for each class
No notion of extents
Same as the left
Mutability of
objects
Mutable
Mutable
Mutable
Object
identity
Cannot stored or
manipulated by user
Can stored or
manipulated by user
(REF type)
Cannot stored or
manipulated by user
46