A Bookstore Database
How can we use OQL on a database designed to manage for example a Bookstore?
The most basic query is simply a SELECT with just a class name:
This query returns any Book existing in the database. As you can notice, there is no need to specify the expected columns, as we would do in a SQL SELECT statement, because OQL queries always return complete objects.
Joining classes together
I would like to list all books written by someone whose name starts with 'Camus'
SELECT Book JOIN Artist ON Book.written_by = Artist.id WHERE Artist.name LIKE 'Camus%'
Note that there is no need to specify whether the JOIN is an INNER JOIN, or LEFT JOIN. This is well-known in the data model. The OQL engine will in turn create a SQL query based on the relevant option, but we do not want to care about it, do we?
Now, you may consider that the name of the author of a book is of importance. This is the case if it should be displayed anytime you will list a set of books, or if it is an important key to search for.
Then you have the option to change the data model, and define
the name of the author as an external field. Such an external field
would be defined by the external key
the target attribute
name. Let's define this new field
The query could then be simplified to:
SELECT Book WHERE Book.writer_name LIKE 'Camus%'
The join will actually be performed by the underlying SQL query,
but this is completely transparent to the OQL. It will happen each
and every time those objects are queried so that the attribute
writer_name becomes part of the properties of a book –
though it will be read-only.
It is also possible to retrieve the authors and their books in one query. For example let's retrieve, for each author, a list of the books written in 2001 and after. This can be achieved using the query:
SELECT Artist, Book FROM Book JOIN Artist ON Book.written_by = Artist.id WHERE Book.issued > '2001-01-01'
The data set resulting from this query will have two columns for each “row” of the set: an Artist and a Book.
Now, as this is a modern bookstore, several types of media are available: Audio, Video, Book. All of them have been declared as classes derived from Item in the data model, as shown below:
Since Audio, Video and Book objects are also Items (thanks to the class inheritance), all of them can retrieved in one single query. For example let's retrieve all Items not being produced by in France:
SELECT Item JOIN Producer ON Item.produced_by = Producer.id WHERE Producer.country != ’France’
This query will return books as well, because a Book is an Item… that’s due to classes inheritance: a Book inherits from Item, or we can say that a Book is a specialized item.
Let's imagine that a book is characterized by one Topic.
Topics are organized as a hierarchy of objects. Therefore a Topic can have a parent Topic:
Let's consider the following hierarchy of Topics:
History of science
Books can be attached at any level.
Let’s list all books about History:
SELECT Book JOIN Topic AS NodeTopic ON Book.topic_id = NodeTopic.id JOIN Topic AS RootTopic ON NodeTopic.parent_id BELOW RootTopic.id WHERE RootTopic.name = ’History’
This query will return all books related to the Topic 'History', 'Archaeology', 'Civilizations', 'Egyptology', etc. but not 'Arts'. All the topics being in the tree under 'History' will be considered, whatever the depth of the hierarchy.
The following query will return only the Books which Topics is strictly below 'Archaeology', e.g. only 'Civilizations', 'Archeologists' and 'Egyptology':
SELECT Book JOIN Topic AS NodeTopic ON Book.topic_id = NodeTopic.id JOIN Topic AS RootTopic ON NodeTopic.parent_id BELOW STRICT RootTopic.id WHERE RootTopic.name = ’Archaeology’