Standard Library (5 of 5). LibrettoDB and Databases

Relational databases are interpreted in Libretto as object models, and queries to databases are interpreted as Libretto expressions. The access to a database is organized in two steps:

1. The development of an object model for the database universe of discourse.
2. The construction of mapping from this object model to the database.

The technology implemented in Libretto has a theoretical background published in Databases as Ontologies. Due to this method, queries to a database can be represented as regular Libretto expressions.

• LibrettoDB
• Translation from LibrettoDB to SQL
• Technology Overview

LibrettoDB

Libretto contains many features irrelevant to database handling. Hence, a sublanguage of Libretto called LibrettoDB has been determined, the draft version of which is outlined here. In the current version, LibrettoDB supports:

• Paths and the dot operator
• the if operator
• Fields and inverse fields
• Class names as filters in paths
• Predicates as boolean combinations of ==, !=, >, <, >=, <=.

This is an example of a query in LibrettoDB:

News[municipal.mName == “Irkutsk City”].text

Translation from LibrettoDB to SQL

A two-way translator from/to LibrettoDB to/from SQL is a part of the Libretto programming environment. This translator compiles a LibrettoDB query to SQL, executes it in a DBMS, and translates the result back to Libretto. The translator is based on the interpretation of a database as a regular object model (with some restrictions).

Technology Overview

Now the development of the LibrettoDB technology is underway. Here we illustrate its basic ideas by an example of a regional news database, which accumulates news and articles from municipal districts. This database comprises 20 tables containing in total 16768 records, and 14000 news among them:

At first stage, a domain object model is being developed, which corresponds to the database. It can be constructed automatically with further manual refinements (in general, an automatedly generated object model is not hierarchical, and does not contain abstract concepts).

The following figure shows the simplified object model of the regional news database, which has been generated automatically, but with some further refinements:

The level of object model enrichment depends on the task to be solved. In particular, the object model above is enriched with some new concepts, e.g. class Info generalizing the notions of Article and News.

Let us consider some query translation examples based on the object model introduced above.

Path Translation. Assume that we need to get all municipal news from region #2:

LibrettoDB: region2.municipals.news.text

SQL:

  SELECT NEWS.TEXT
    FROM NEWS, MUNICIPAL, REGION
    WHERE MUNICIPAL.REGION_ID = REGION.ID     
     AND NEWS.MUNICIPAL_ID = MUNICIPAL.ID
       AND REGION.ID = 2

The chain of steps in the LibrettoDB path corresponds to the chain of key transitions in database tables.

Inverse Field Translation. Inverse fields (see section Inverse Fields) are also included in LibrettoDB. For instance, the inversion allows us to request for all information materials relevant to municipal district #11:

LibrettoDB: municipal11.~municipal.title

SQL:

  SELECT ARTICLE.TITLE
    FROM ARTICLE, MUNICIPAL
   WHERE
       ARTICLE.MUNICIPAL_ID = MUNICIPAL.ID
       AND MUNICIPAL.ID = 11   
UNION ALL
    SELECT NEWS.TITLE
    FROM NEWS, MUNICIPAL
    WHERE     
     NEWS.MUNICIPAL_ID = MUNICIPAL.ID
       AND MUNICIPAL.ID = 11

The domain and range of the field municipal are Info and Municipal, respectively. In ~municipal they are swapped. The peculiarity here is that since the domain of municipal is Info, from which two basic classes Article and News inherit, and the structure of the database is flat and does not support the integrating notion of Info, then we have to combine two separate queries – for Article and for News – in the SQL code. This example shows the explicit use of the class hierarchy, and demonstrates the query expressiveness, which can be achieved thanks to it.

Nested Queries. A nested query is a query, in which a table corresponding to some entity of the object model is involved more than once. In this case, a SQL sub-query should be inserted in a conditional block of the SQL query.

Assume that we need to check the correctness of the database: the following query must always return region #2:

LibrettoDB: region2.municipals.news.region.r_name

SQL:

  SELECT REGION.REGION
    FROM REGION, NEWS
    WHERE NEWS.REGION_ID = REGION.ID     
     AND NEWS.ID IN (
          SELECT NEWS.ID
          FROM NEWS, MUNICIPAL, REGION
          WHERE MUNICIPAL.REGION_ID = REGION.ID     
          AND NEWS.MUNICIPALl_ID = MUNICIPAL.ID
            AND REGION.ID = 2)

The query above transits from region #2 to all its municipal districts, then selects all news for each district, and, finally, gets back to the region. The object region2 and the field region are associated with the same database table REGION. Thus, a new SQL sub-query must be constructed from the step region.

Predicates. The translation of predicates is based on the same rules as the translation of paths:

LibrettoDB: News[municipal.m_name == “Irkutsk City”].text

SQL:

  SELECT TEXT
    FROM NEWS, MUNICIPAL
    WHERE NEWS.MUNICIPAL_ID = MINICIPAL.ID
      AND MUNICIPAL.MUNICIPAL = ‘Irkutsk City’

Here MUNICIPAL is a table attribute corresponding to the field municipal.