ODMG Query Language: OQL

1
ODMG Query Language OQL
2
Introduction

• OQL A standard query language for OODBMSs
• Specified by ODMG Object Data Management Group
• Objectives
• Declarative language
• Smooth Integration of query language with
  programming language
• SQL-like (not SQL compatible) retrieval queries
  (not encapsulated)
• Updates through methods or in programs
• Optimization provided by the DBMS
• Manipulation of named elements and collections
• Use of main OO concepts
• complex structure
• generalization / specialization hierarchies
• methods

3
The FormaPerm Example
Persons
Taddress
name
street number city ZIP
forenames
address
Students
Teachers
reg
tel
age()
bank
Teacher
birthdate
status
diploma
account
bankInfo
courses
list
agency
0n
year
0n list
0n
attends
givesCourse
obtainedCourse
students
prof
11
student
0n
11
isPrequisite
0n
course
11
ObtainedCourse
approved
hasPrequisite
0n
0n
course cname cycle
grade
year
Courses
4
OQL SQL-like syntax

• SELECT DISTINCT ltdefinition of the resultgt
• FROM variablei IN collectioni, ..
• WHERE ltconditiongt
• GROUP BY HAVING predicate
• ORDER BY . DESC / ASC
• The result of a query can be input to another
  query (closure/compositionality of the language)
• Queries can be named
• ex define Q1 as ltquerygt

5
Queries onto denotable elements

• Extents associated to classes (EXTENT clause )
• Return the name of the student having reg 111
• SELECT s?name
• FROM s IN Students
• WHERE s?reg 111
• Named variables
• name Phil Student
• queries
• Phil?name Return the name of the student denoted
  as Phil
• SELECT c?cname Return the name of the courses
• FROM c IN Phil?attends attended by the
  student denoted as Phil

6
Definition of the result

• The result can be
• An object (oid, non printable)
• A collection of objects
• A value (printable), atomic or complex, from
  within an object
• A value returned via a method call
• A collection of values
• Assuming
• NAME Phil Student
• Phil Student (name'Rochat', forenames LIST
  ('Philippe', 'André'), address , reg ,
  birthdate , courses )
• Possible result definitions
• Phil an oid/object
• Phil?name a value
• Phil?age() a value computed by the method
• Students a collection of objects

7
Query results

• An element
• A collection
• The semantics of the query defines the result as
  an element or as a collection of a given type
  (SET, LIST, BAG, ARRAY)
• SELECT tFROM t IN TeachersWHERE t?status
  'assistant'
• gt SET of objects of type Teacher
• SELECT s?address?cityFROM s IN Students
• gt BAG of values of simple type STRING (with
  duplicates)
• SELECT DISTINCT s?address?city FROM s IN
  Students
• gt SET of values of simple type STRING (no
  duplicates)

8
Structured Result (flat)

• Return for each student her/his name and the city
  (s)he lives in
• SELECT STRUCT(name s?name, city
  s?address?city)FROM s IN Students
• gt BAG of complex values of type
  STRUCT(name STRING, city STRING)
• IMPORTANT the explicit creation of a complex
  type (STRUCT SET LIST BAG ...) is mandatory
• Just writing (name e?name, city
  e?address?city) as a specification of the result
  produces an "unknown OQL type" error

9
Structured Result (nested)

• Return for each teacher his name and the names of
  the courses he/she presents
• SELECT STRUCT( name t?name, courses (SELECT
  c?cname FROM c IN t?givesCourse))FROM t IN
  Teachers
• SET of complex types struct(name STRING,
  courses SET(STRING))
• NB it is not possible to define the result as
• t?givesCourse?cname
• because givesCourse is multivalued and a
  multivalued link cannot be used within a dot path
  (it can only be used as last element in the path)

10
Traversing structures

• Navigation through the data structure is
  specified using the dot notation
• x?y x monovalued composite element
• y component of x
• Examples (t denotes a teacher, c denotes a
  course)
• t?address?city
• c?prof?status
• t?givesCourse
• but
• t?givesCourse?cname is incorrect!
• This also applies to navigation through
  composition links

11
Traversing multivalued elements

• Associate a variable to the collection
• x IN Phil?courses (Phil is the name of an
  object of type Student,   courses is a
  multivalued attribute of Student)
• Use the dot notation to denote the targeted
  component of the multivalued element
• x?year
• Execution of the query iteratively binds the
  variable to each component of the collection
  (loop over collection elements)
• Return Phils diplomas received during year 2000
• SELECT x?diploma
• FROM x IN Phil?courses
• WHERE x?year 2000
• (do NOT write Phil?courses?diploma !)

12
Two uses for IN

• Within the FROM clause to declare a variable
  over a collection
• FROM s IN Students
• FROM x IN Phil?courses
• FROM x IN (SELECT FROM WHERE )
• Within the WHERE clause to specify a "belongs
  to/exists in" predicate
• Example Return the names of courses attended by
  Phil
• 1) SELECT c?cname FROM c IN Phil.attends
• 2) SELECT c?cname FROM c IN Courses
  WHERE Phil IN c.students

13
Via Composition Links

• Links of cardinality 01 or 11
• Return the name of the teacher of the DB course
• SELECT c?prof?nameFROM c IN CoursesWHERE
  c?cname 'DB'
• Links of cardinality 0n or 1n
• Return the names of students registered for the
  DB course
• SELECT s?nameFROM c IN Courses, s IN
  c?studentsWHERE c?cname 'DB'

14
Nested OQL Queries

• Return the names of the courses attended by both
  Phil and Annie Muller
• SELECT c?cnameFROM c IN Phil?attendsWHERE c
  IN ( SELECT x FROM s IN Students, x IN
  s?attends WHERE s?name'Muller' AND
  'Annie' IN s?forenames )

15
Via Is-a links

• Given an access to a superclass, e.g
• SELECT p FROM p IN PersonsWHERE
  p?address?city'Lausanne'
• What is the result?
• Only objects in the root class( persons nor
  students neither teachers)
• All the objects in the hierarchy rooted at the
  superclass
• Root class format (Person format), or
• Heterogeneous format (Person, Student, Teacher)?
• The result depends on the OODBMS at hand
• OQL all objects in homogeneous format (format of
  the superclass)

16
Testing Equality
Persons

• Return persons who live in an apartment they own
• SELECT pFROM p IN Persons, x IN p?ownsWHERE
  p?lives x Oid test
• Return persons who own an apartment identical to
  the apartment they live in that is not the one
  they live in
• SELECT p FROM p IN Persons, x IN
  p?ownsWHERE p?lives ? x AND
  p?lives?nbRooms x?nbRooms AND
  p?lives?type x?type AND
  p?lives?surface x?surface

17
Using methods

• Wherever an object/value of type X is expected, a
  method whose result is an object/value of type X
  can be used
• Return name and age of students older than 30
• SELECT DISTINCT STRUCT( s?name, s?age() )
• FROM s in Students
• WHERE s?age()gt30
• Return students older than 30
• SELECT s
• FROM s in Students
• WHERE s?age()gt30

18
SELECT FROM WHERE Queries and syntax
19
Type of queries

• 1. Atomic queries
• OQLgt 52310 (returns 235)
• OQLgt Students
• OQLgt Phil
• OQLgt Phil?name
• OQLgt Q1 (named queries)
• 2. SELECT FROM WHERE .
• 3. ltquery1gt op ltquery2gt
• 4. Object / Value Creation

20
SELECTFROMWHERE

• SELECT DISTINCT ltresultgt
• FROM variablei IN collectioni, ..
• WHERE ltconditiongt
• GROUP BY HAVING predicate
• ORDER BY . DESC / ASC
• Result A bag or set of
• Value
• Object
• STRUCT (x?prop1, x?prop2, , x?propn )
• An element in STRUCT may be a query SELECT
• FROM x in collec1, y in collec2 Cartesian
  product
• WHERE predicate filtering retrieved elements

21
ltresultgt

• Any expression that
• Denotes an object or a collection of objects
• SELECT t FROM t IN Teachers
• SELECT t?givesCourse FROM t IN Teachers
• Denotes a value or a collection of values
• SELECT t?name FROM t IN Teachers
• SELECT t?forenames FROM t IN Teachers
• Builds a complex value
• SELECT STRUCT (names?name, courses s?attends,
  course2 (SELECT c FROM s?attends WHERE
  c?cycle2) FROM s IN Students
• STRUCT(name STRING, courses SET(Course),
  course2 SET(Course))

22
variablei IN collectioni

• collectioni may be any collection in the database
• An extent SELECT t FROM t IN Teachers WHERE
  
• Any other collection FROM t IN Teachers, c IN
  t?givesCourse
• Return the 2nd cycle courses attended by Phil
  together with the name of their teacher
• SELECT STRUCT(coursename c?cname,
  profname c?prof?name)FROM c IN
  (SELECT x FROM Phil?attends
  WHERE x?cycle2)

23
WHERE ltconditiongt

• Condition
• elementary condition
• (condition)
• condition AND condition
• condition OR condition
• Elementary condition
• expression1 comparison-operator expression2
• Examples (s is a Student)
• s?name 'Muller'
• s?age() lt 20
• 'Annie' IN s?forenames
• COUNT(s?forenames) gt 2
• With a quantifier
• EXISTS x IN s?courses x.year 2000
• FOR ALL x IN s?courses x.year gt 2000

24
Existential Quantifier

• Conditions in the WHERE clause may be expressed
  using existential quantification
• EXISTS x IN collection condition0
• Declaration of a local variable x
• condition0 is a predicate on x
• (EXISTS) is true iff there is at least one
  element in the collection that satisfies
  condition0
• Return names of students who got at least a grade
  6
• SELECT s FROM s IN Students
• WHERE EXISTS c in s?obtainedCourse c?grade6

25
Universal Quantifier

• Conditions in the WHERE clause may be expressed
  using universal quantification
• FORALL x IN collection condition0
• Declaration of a local variable x
• condition0 is a predicate on x
• (FORALL) is true iff all elements of the
  collection satisfy condition0
• Return names of students who got all their
  obtained courses with grade 6
• SELECT s FROM s IN Students
• WHERE FORALL c in s?obtainedCourse c?grade6 AND
  COUNT(s?obtainedCourse)gt0

26
Aggregation functions

• COUNT(collection)MIN(collection)MAX(collection)
  AVG(collection)SUM(collection)
• COUNT(Phil?courses) gt number of Phil diplomas
• COUNT(Students) gt number of students
• COUNT(SELECT p FROM p IN Persons WHERE
  p?name'Rochat') gt number of 'Rochat' in the
  DB

27
Using Aggregate Functions

• For each student, return his/her name, the total
  number of diplomas he/she has obtained, those
  obtained in 2003 and the first year he/she
  obtained a diploma.
• SELECT STRUCT( name s?name, nbdiplomas
  COUNT(s?courses) , nbdiplomas03 COUNT(
  SELECT c FROM c IN s?courses
  WHERE c?year2003) , firstyear MIN( SELECT
  c?year FROM c IN s?courses) )
• FROM s IN Students

28
ORDER BY

• SELECT DISTINCT STRUCT( s?name, s?forenames,
  s?age() )
• FROM s IN Persons
• WHERE s?age()lt30
• ORDER BY s?name ASC

29
Manipulating collections

• Conversion operators
• element(singleton)
• flatten(collection de collection)
• list_to_set, list_to_bag, bag_to_set
• distinct (ltbaggt)

30
Partitions
31
Element Grouping GROUP BY

• Objective to partition a collection into groups
  of elements having the same value for a given
  list of attributes, usually to compute aggregate
  values for each group
• Examples
• Return the number of courses in each cycle
• Return for each bank the number of professors and
  assistants whose account is with the bank
• Return for each city the number of students, and
  the number of professors living in it
• A group is called a partition

32
Queries on Partitions

• SELECT . FROM WHERE GROUP BY label1
  attribute1, label2 attribute2,
• HAVING predicate
• Partitions the collection defined in the FROM
  clause into subsets with elements having the same
  values for attribute1, attribute2,
• SELECT
• FROM v1 IN E1, v2 IN E2, WHERE
• GROUP BY att1 v1.A1, att2 v1.A2, att3
  v2.A3,
• resulting type is
• STRUCT (att1 TypeA1, att2 TypeA2, att3
  TypeA3,
• partition BAG(STRUCT(v1 TypeE1, v2
  TypeE2, ) ) )
• HAVING Filter on the subsets

33
Partition's Structure

• SELECT
• FROM v1 IN E1, v2 IN E2, WHERE
• GROUP BY att1 v1.A1, att2 v1.A2, att3
  v2.A3,
• resulting type is
• STRUCT (att1 TypeA1, att2 TypeA2, att3
  TypeA3,
• partition BAG(STRUCT(v1 TypeE1, v2
  TypeE2, ) ) )
• --------------------------------------------------
  ----------------------------
• SELECT att1, att2, att3, numberOfx COUNT
  (partition), maxOfB1 MAX(SELECT x.v1.B1 FROM
  x IN partition)
• FROM v1 IN E1, v2 IN E2 WHERE
• GROUP BY att1 v1.A1, att2 v1.A2, att3
  v2.A3
• resulting type is
• STRUCT (att1 TypeA1, att2 TypeA2, att3
  TypeA3,
• partition BAG(STRUCT(numberOfx
  INTEGER, maxOfB1 TypeB1) ) )

34
Partition Query Example

• return number of courses per cycle
• SELECT cyc, numberOfCourses COUNT (partition)
• FROM c IN Course
• GROUP BY cyc c?cycle
• the type built by the GROUP BY is
• STRUCT (cyc INTEGER, partition BAG(STRUCT(c
  Course)) )
• the result of the query is

35
Partition Query Example2

• return students grouped by age
• SELECT ageGroup, st (SELECT STRUCT (sname
  x?s?name,   sforenames x?s?forenames FROM x
  IN partition) )
• FROM s IN Students
• GROUP BY ageGroup s?age()
• the type built by the GROUP BY is
• STRUCT (ageGroup INTEGER, partition
  BAG(STRUCT(s Student) )
• the result is

36
Ad-Hoc Partitioning

• The grouping predicate can be explicitly defined
• SELECT
• FROM s IN Students
• GROUP BY (young s?age() lt 20,
• adult s?age() gt20 AND s?age() lt 40,
• old s?age() gt 40)
• resulting type is
• STRUCT lt young Boolean, adult Boolean, old
  Boolean, partition BAG ( STRUCT (s
  Student) )

T, F, F set of young students F, T, F set of
adult students F, F, T set of old students
37
GROUP BY HAVING

• select the partitions to be kept
• SELECT
• FROM s IN Students
• GROUP BY city s?city
• HAVING COUNT (partition) gt 10
• Only partitions having more than 10 elements are
  kept

38
Ordering Partitions

• SELECT
• FROM s IN Students
• GROUP BY city s?city
• ORDER BY AVG(SELECT s?age() FROM partition)
• the partitions are ordered by average age of the
  students living in the same city

39
Set Operations
40
Set operators

• collection1 UNION collection2 collection1
  EXCEPT collection2 (difference)
• collection1 INTERSECT collection2
• (collections may be SET or BAG)
• Operands must have compatible types
• Same type, or
• Common super-type gt comparisons on common
  properties only
• For object collections, comparisons may compare
  either values or oids OQL compares oids

41
Set operators Example

• Return courses attended by Phil or (inclusive) by
  Annie Muller
• Phil.attends UNION(SELECT c FROM s IN
  Students, c IN s?attends WHERE s?name
  'Muller' AND 'Annie' IN
  s?forenames)

42
Object / Value Creation
43
Object Creation

• Object creation is via the class
  constructor(i.e., the creation method specific
  to the class)
• OQLgt sStudent(name "Rochat", forenames (Marie,
  Anne), reg 957, birthdate 19851205)

1) creates an object (with its oid) of type
Student 2) returns the oid (into variable s)3)
if there exists a population (EXTENT...) the
object is inserted into it (into Students)
44
Value Creation

• Its type exists
• similar to object creation
• OQLgt aTaddress(street 'Rue Centrale', number
  23, city 'Lausanne', ZIP1002)
• Of a new type needs explicit definition of the
  new type (using STRUCT, SET, LIST,
  constructors)
• OQLgt b STRUCT (cname 'ADB', profName
  'Stefano', assistantName 'Fabio', section 'IN',
  students SET (Phil, Annie, Nicole, ) )

45
Conclusion

• OQL a query language for OODBMS
• declarative gt query optimization done by the
  DBMS
• compatible with OO programming languages
• orthogonal wherever a concept of type X is
  expected, all expressions evaluating to X can be
  employed
• Example FROM v IN collection
• collection may be any collection structure
• Population (extension)
• v2?multi-valued attribute
• (SELECT )