Processing of Compound Types: Example¶

Imagine that you want to define a relation that models books with title and various authors. We could have the attributes:

• TITLE: simple type (text)

• AUTHOR: compound type. More specifically, we can have various authors and, for each author, we want to represent his/her name, surname and a list of contact addresses. As explained earlier, an array type models a subrelation, whereby it is necessary to indicate using a register type the schema of this relation. The subrelation AUTHOR thus has an associated register type with subattributes of the simple type NAME, SURNAME and other compound attributes of the array type to contain the list of contact addresses (CONTACT). CONTACT represents another subrelation, with a schema comprised of the subattributes MAIL and ADDRESS; MAIL has a simple type and ADDRESS is a register comprised of the subattributes: STREET, PLACE and COUNTRY.

The tree of the type AUTHOR is shown in Trees of compound elements. The data type to represent elements of the type AUTHOR can be created with the following statements:

CREATE TYPE address AS REGISTER OF (
    STREET:text,
    CITY:text,
    COUNTRY:text
);

CREATE TYPE contactAddress AS REGISTER OF (
    MAIL:text,
    ADDRESS:address
);

CREATE TYPE contactAddressArray AS ARRAY OF contactAddress;

CREATE TYPE author AS REGISTER OF (
    NAME:text,
    SURNAME:text,
    CONTACTADDRESS:contactAddressArray
);

CREATE TYPE authorArray AS ARRAY OF author;

Trees of compound elements¶

Tuple with compound elements shows an example of a tuple of this view and its internal representation:

The structure of the value tree is shown in Tree of Compound-type values.

Tree of Compound-type values¶

Now a base relation that models this relation can be created:

CREATE TABLE BOOK I18N es_euro (
    TITLE:text (SEARCH),
    AUTHOR:authorArray
);

It will also be necessary to create a wrapper for the relation. Note that, as always, the schema of the data returned by the wrapper should be compatible with the schema of the relation, which in this case means that the wrapper requires that the data be returned in the form of compound values.

For example, the following figure shows part of a VQL sentence to create an ITPilot wrapper to obtain the required data. Note how the output schema defined is compatible with that of the relation:

CREATE WRAPPER ITP BOOK_sm1
    OUTPUTSCHEMA (
        TITLE,
        AUTHOR : ARRAY OF
            AUTHOR : REGISTER OF (
                NAME,
                SURNAME,
                CONTACTADDRESS : ARRAY OF
                    CONTACTADDRESS : REGISTER OF (
                        MAIL,
                        ADDRESS : ARRAY OF
                            ADDRESS : REGISTER OF (
                                STREET,
                                CITY,
                                COUNTRY
                            )
                       )
                   )
               )
  ... Wrapper definition ...;

Once the wrapper has been created, a search method can be defined for the BOOK relation (see section Modifying a Base View). In most cases, query restrictions will only be defined for URIs that indicate simple data types (this is consistent with the fact that compound-type attributes are considered as though they were subrelations). However, it is also possible to add restrictions for URIs indicating compound types (in this case, remember that the operands on the right of the conditions will be built using the constructors ROW and { } and that only operators = and <> may be used). The following sentence adds a possible search method (note that a restriction has been included for the compound URI AUTHOR.CONTACTADDRESS):

ALTER TABLE BOOK
   ADD SEARCHMETHOD BOOK_SM1 (
     CONSTRAINTS (
        ADD TITLE                                    NOS ZERO ()
        ADD AUTHOR.NAME                              NOS ZERO ()
        ADD AUTHOR.SURNAME                           NOS ZERO ()
        ADD AUTHOR.CONTACTADDRESS                    NOS ZERO ()
        ADD AUTHOR.CONTACTADDRESS.MAIL               NOS ZERO ()
        ADD AUTHOR.CONTACTADDRESS.ADDRESS.STREET     NOS ZERO ()
        ADD AUTHOR.CONTACTADDRESS.ADDRESS.CITY       NOS ZERO ()
        ADD AUTHOR.CONTACTADDRESS.ADDRESS.COUNTRY    NOS ZERO ()
    )
    OUTPUTLIST (TITLE, AUTHOR)
    WRAPPER (itp book)
);

Finally, some examples of queries that could be made on the relation are shown:

1. Obtain the title and the authors’ names of all the books that contain in their title the word “java”.

   SELECT TITLE, LIST((AUTHOR).NAME) AS AUTHORLIST
   FROM BOOK
   WHERE TITLE like '%java%'
   GROUP BY TITLE;
   

2. Find the tile and the list of contact addresses for each of the authors of the books that contain in their title the word “java”.

   SELECT TITLE, LIST((AUTHOR).CONTACTADDRESS) AS AUTHORLIST
   FROM BOOK
   WHERE TITLE like '%java%'
   GROUP BY TITLE
   

3. Find the title and the first e-mail address of each of the authors of all the books that contain in their title the word “java”.

   SELECT TITLE,LIST((AUTHOR).CONTACTADDRESS[0].MAIL) AS AUTHORLIST
   FROM BOOK
   WHERE TITLE like '%java%'
   GROUP BY TITLE
   

4. Find the title and the name of each of the authors of all the books that contain the word “java” in their title and that have at least one author with an e-mail address that contains the word .es.

   SELECT TITLE, LIST((AUTHOR).NAME) AS AUTHORLIST
   FROM BOOK
   WHERE (TITLE like '%java%')
         AND ((AUTHOR).CONTACTADDRESS.MAIL like '%.es%' )
   GROUP BY TITLE
   

5. Find the title and the name of each of the authors of all the books that contain the word java in their title and that have at least one author with an address in the street Real.

   SELECT TITLE, LIST((AUTHOR).NAME) AS AUTHORLIST
   FROM BOOK
   WHERE (TITLE like '%java%')
         AND ((AUTHOR).CONTACTADDRESS.ADDRESS.STREET like '%Real%')
   GROUP BY TITLE
   

6. Find the books written by an author with a single contact address, the e-mail john@mail.com and who lives in Real street in the city of Madrid (Spain).

   SELECT TITLE, AUTHOR
   FROM BOOK
   WHERE (AUTHOR).CONTACTADDRESS =
    { ROW('john@mail.com',{ROW('Real', 'Madrid', 'Spain')}) }