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:

SELECT
    Book

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.

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 written_by and the target attribute name. Let's define this new field as writer_name.

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:

• Item
  • Audio
  • Video
  • Book

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:

• Art
• History
  • Archaeology
    • Civilizations
    • Archeologists
    • Egyptology
  • History of science
  • War

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’