书店数据库

对于设计去管理书店的示例数据库,我们如何使用OQL呢?

最基本的查询是简单的一个SELECT,并且只有一个类名称:

SELECT
    Book

此查询返回数据库中存在的任何书籍。您可以注意到,不需要像在SQL SELECT语句中那样指定期望的列,因为OQL查询总是返回完整的对象。

连接类

我想列出所有以“Camus”的名字开头的人写的书

SELECT
    Book
    JOIN Artist ON Book.written_by = Artist.id
    WHERE Artist.name LIKE 'Camus%'

注意,不需要指定连接是内部连接还是左连接。这在数据模型中是众所周知的。OQL引擎将根据相关选项创建一个SQL查询,我们不想关心这些细节,不是吗?

现在,你可能认为一本书作者的名字很重要。想要列出一套书籍或者查询关键词的列表。

然后您可以选择更改数据模型,并将作者的名字定义为外部字段。这样的外部字段将由外部键written_by和目标属性名定义。让我们将这个新字段定义为writer_name。

查询可简化为:

SELECT
    Book
    WHERE Book.writer_name LIKE 'Camus%'

连接实际上是由底层SQL查询执行的,但是对于OQL来说这是完全透明的。每次查询这些对象时都会发生这种情况,这样writer_name属性就会成为书籍属性的一部分——尽管它是只读的。

也可以在一个查询中检索作者及其著作。例如,让我们检索2001年及以后每个作者写的书的列表。这可以使用查询实现:

SELECT
    Artist, Book FROM
    Book
    JOIN Artist ON Book.written_by = Artist.id
    WHERE Book.issued > '2001-01-01'

这个查询产生的数据集的每“行”都有两列:艺术家和书。

类继承

现在,由于这是一家现代书店,有几种媒体可供选择:音频、视频、书籍。它们都被声明为从数据模型中类别派生的类,如下图所示:

  • Item

    • Audio
    • Video
    • Book

由于Audio、Video和Book对象也是项(多亏了类继承),所以它们都可以在一个查询中检索到。例如,让我们检索所有不是法国生产的类别:

SELECT
    Item
    JOIN Producer ON Item.produced_by = Producer.id
    WHERE Producer.country != ’France’

这个查询也将返回books,因为Book是一个项目……这是由于类继承:Book继承自Item,或者我们可以说Book是一个专门化的类别。

数据层次结构

让我们假设一本书有一个主题。

主题被组织为对象的层次结构。因此,一个主题可以有一个父主题:

让我们考虑以下主题层次结构:

  • 艺术

  • 历史

    • 考古学
      • 文明
      • 考古学家
      • 埃及学
    • 科学历史
    • 战争

书被附加到任何级别。

让我们列出所有关于历史的书:

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’

这个查询将返回所有与“历史”、“考古”、“文明”、“埃及学”等主题相关的书籍,但不返回“艺术”。不管层次结构的深度如何,“历史”树中的所有主题都将被返回。

以下查询将只返回主题严格是“考古学”的书籍,例如“文明”、“考古学家”和“埃及学”:

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’

原文:https://www.itophub.io/wiki/page?id=2_6_0%3Aoql%3Aexample


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.

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 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.

Classes Inheritance

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.

Data Hierarchies

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’
标签:
由 superadmin 在 2020/08/25, 16:24 创建
    

需要帮助?

如果您需要有关XWiki的帮助,可以联系:

深圳市艾拓先锋企业管理咨询有限公司