Associations¶

An association represents a relationship between two views in a similar way a join view links one view with another one. The section Associations of the Administration Guide explains this concept in more detail.

This section explains the VQL statements to create, modify and delete associations.

CREATE [ OR REPLACE ] ASSOCIATION <identifier with database> [ REFERENTIAL CONSTRAINT ]
    [ FOLDER = <folder:literal> ]
    [ DESCRIPTION = <association_description:literal> ]
    ENDPOINT <role_name:identifier> <view_name:identifier>
             [ <mult:multiplicity> ]
    [ PRECONDITION <condition> ]
    [ DESCRIPTION = <endpoint_description:literal> ]

    ENDPOINT <role_name:identifier> <view_name:identifier>
             [<mult:multiplicity>]
    [ PRECONDITION <condition> ]
    [ DESCRIPTION = <endpoint_description:literal> ]
    [ ADD MAPPING <val1:mapping_value> = <val1:mapping_value> ]+

<mapping_value> ::=
      <field name>
    | <mapping_value> <funcsymbol> <value>
    | <value> <funcsymbol> <mapping_value>
    | CASE <mapping_value>
          WHEN <compare_value:value> THEN <result:value>
          [ WHEN <compare_value:value> THEN <result:value> ]*
          [ ELSE <result:value> ] END
    | CASE WHEN <condition> THEN <result:value>
           [ WHEN <condition> THEN <result:value> ]*
           [ ELSE <result:value> ] END
<multiplicity> ::=
      ( 0 , 1 )
    | ( 1 )
    | ( * )
    | ( + )

<identifier with database> ::= (see Basic elements of VQL statements)

<value> ::= (see Rules for forming functions)

<condition> ::= (see Rules for forming functions)

The clause REFERENTIAL CONSTRAINT marks the association as a referential constraint (see the section Referential Integrity in Associations of the Administration Guide)

The first DESCRIPTION clause is the description of the association and the second and the third ones are the descriptions of the two endpoints of the association.

The clause PRECONDITIONS represent the Role preconditions of each end point (see the section Role Preconditions of the Administration Guide).

ALTER ASSOCIATION <name:identifier>
    [ RENAME <new_name:identifier> ]
    [ DESCRIPTION = <desc:literal> ]

Use the ALTER ASSOCIATION statement to rename the association and/or change its description.

To delete an association, execute the statement DROP ASSOCATION (see Syntax of the DROP statement)

Navigational Queries¶

With SELECT_NAVIGATIONAL you can execute queries that allow you to “navigate” through the associations defined between views.

If you want to identify a particular row of a view with its primary key and then, obtain the rows of another view that are linked with the rows of the other view, you can do it in two ways:

• With a join of two views.

• Or defining an association between these views and at runtime, use SELECT_NAVIGATIONAL to traverse the association.

Defining an association provides several benefits over doing a join:

• The queries are much simpler.

• The client does not need to know the name of the view at the other side of the association.

• The client does not need to know how to build a valid join condition that links the two views.

• Any change on the view at the other side of the association or the condition of the association does not affect clients.

For example, if there is an association between the views “customer” and “order”, an application can execute the statement SELECT_NAVIGATIONAL to obtain all the orders of a particular customer without having to know the names of the fields of the join condition or the name of the view that holds the customers’ orders.

The drawback of doing this with an association instead of a join is that joins allow you to link two views in a more complex way.

Example

Consider a view customer whose primary key is the field cid. This view participates in an association whose role on the endpoint of the customer view is called orders.

SELECT_NAVIGATIONAL *
FROM customer / 2 / orders
WHERE o_description like '%Bandages%'

This query returns the orders of the customer with cid = 2 and whose description starts with Bandage.

The equivalent SELECT query is more complex and you need to know the names of the fields to build the JOIN condition. E.g.:

SELECT orders.*
FROM customer INNER JOIN orders
ON customer.cid = orders.cid
WHERE customer.cid = 2 AND orders.o_description like '%Bandages%'

SELECT_NAVIGATIONAL <navigation fields>
    FROM <view:identifier> [ <select navigations> ]
    [ EXPAND <expand elements> ]
    [ WHERE <extended condition> ]
    [ GROUP BY <group by expression> [ , <group by expression> ]* ]
    [ HAVING <extended condition> ]
    [ ORDER BY <order by expression> [ ASC | DESC ] [, <order by expression> [ ASC | DESC ] ]* ]
    [ WITH CONDITION MAPPINGS EVALUATION ]
    [ FLATTEN FIRST LEVEL ROLES ]
    [ OFFSET <number> [ ROW | ROWS ] ]
    [
        {
          FETCH { FIRST | NEXT } [ <number> ] { ROW | ROWS } ONLY
        | LIMIT [ <number> ]
        }
    ]
    [ CONTEXT ( <context information> [ , <context information> ]* ) ]
    [ TRACE ]

<navigation fields> ::=
  <navigation field> [, <navigation field> ]*

<navigation field> ::=
    *
  | <field:identifier> [ AS <alias:identifier> ]
  | <role:identifier> / *
  | <role:identifier> / <field:identifier>
  | <function> ( [ <function parameter> ]* ) [ AS <alias:identifier> ]
  | <expression>

<function parameter> ::=
    <field:identifier>
  | <simple navigation field>

<simple navigation field> ::=
  <role:identifier> / <field:identifier>

<select navigations> ::=
    / <primary key values>
  | / [ <condition> ]
  | { / <primary key values> / <association:identifier> }*
           [ / <primary key values> | / LBRACE <condition> RBRACE ]

<primary key values> ::=
  <primary key value:value> [, <primary key value:value> ]*

<expand elements> ::=
  <expand element> [ , <expand element> ]*

<expand element> ::=
  <role:identifier> [ ( WHERE <regular condition:condition> ) ] [ / <role:identifier> [ ( WHERE <regular condition:condition> ) ] ]*

<group by expression> ::=
    <field:identifier>
  | <role:identifier> / <field:identifier>
  | <expression>
  | <function> ( [ <function parameter> ]* )

<order by expression> ::=
    <expression>
  | <field>
  | <field position:integer>
  | <role:identifier> / <field:identifier>
  | <function> ( [ <function parameter> ]* )

The most important part of this statement is the navigational expression of the FROM clause. These expressions identify a collection of one or more elements on which to apply the query. A collection may be a view, a row of a view or the result of traversing an association from one view to another one.

The clauses WHERE, HAVING and ORDER BY support the use of navigation fields (e.g. role_customer/customer_name). You can also use scalar functions and their parameters can be navigation fields as well. For example:

SELECT_NAVIGATIONAL order_id, order_date, concat( customer / company_name, ' - ', customer / contact_name) as company_contact, shipper / company_name
FROM order
EXPAND customer, shipper
WHERE UPPER(customer / "City") = 'LONDON'
FLATTEN FIRST LEVEL ROLES;

Primary Key Requirement

A navigational query can only traverse an association if the view has a primary key defined. The section Primary Keys of Views of the Administration Guide explains how to define the primary key of a view using the Administration Tool. The Syntax of the statement CREATE TABLE and the Syntax of the CREATE VIEW statement contain the syntax of the VQL statements to define the primary key when creating base and derived views.

Operator DIV (/)

To use the division operator in a “navigation field” or expression, use DIV. / is not allowed in navigational queries other than to traverse a role.

Cardinality Multiple

When the WHERE clause has navigation fields that navigate to an endpoint with cardinality N, consider this:

1. The condition is “true” if one or more rows meet the WHERE condition. For example:

   SELECT_NAVIGATIONAL *, order_detail / *
   FROM customer
   EXPAND order_detail
   WHERE order_detail / discount > 0.2
   

   This query returns the rows of the table “customer” that are associated with one or more rows of “order_detail” whose “discount” is greater than 0.2.

2. If you use several navigation fields that meet this condition in an OR condition, these navigation fields have to traverse the same roles. E.g. This is supported:

   -- All the navigation fields traverse the role "emails".
   
   WHERE POSITION('acme.com' IN emails / email) > 0 OR POSITION('denodo.com' IN emails / email)
   

   But this condition is unsupported because it uses a non-navigation field (city):

   WHERE city = 'Los Angeles' OR POSITION('acme.com' IN emails / email) > 0
   

   This limitation does NOT apply to AND conditions. E.g. This is supported:

   -- One field is not
   WHERE city = 'Santiago de Compostela' AND POSITION('acme.com' IN emails / email) > 0
   

Filtering Conditions on Roles

When you expand roles that navigate to an endpoint with cardinality N, it is possible to filter the collection of results returned for those roles. E.g.:

SELECT_NAVIGATIONAL *, order_detail / *
FROM customer
EXPAND order_detail ( WHERE discount > 0.1 )

This query returns all the rows of the table “customer”. The values returned for order_detail / * will be those whose “discount” is greater than 0.1.

Consider this:

1. When you define a filtering condition for a role “myrole”, only use fields from the endpoint where “myrole” navigates to. You cannot use navigation fields in these conditions either. That is, a query like this one is invalid because of the condition:

   SELECT_NAVIGATIONAL *, order_detail / *, order_detail / product_detail / *
   FROM customer
   EXPAND order_detail ( WHERE product_detail / warehouse_id = 132 ) / product_detail
   

2. When you define a filtering condition for a role “myrole” and that role appears more than once in the EXPAND clause, define the same filtering condition for all the occurrences of “myrole”. For example:

   SELECT_NAVIGATIONAL *, order_detail / product_detail / *, order_detail / incidence_detail / *
   FROM customer
   EXPAND order_detail ( WHERE discount > 0.1 ) / product_detail, order_detail ( WHERE discount > 0.1 ) / incidence_detail
   

Extra Fields

SELECT_NAVIGATIONAL queries return some extra fields in addition to the ones specified in the SELECT clause:

• One additional field for each association of the view. The value of this field is a register containing:

  • Name and description of the involved role.

  • A path expression relative to the one used in the FROM clause that allows to traverse the association for that row.

• If the view has the primary key defined, the result will have the field _primary_key.

  It provides a path expression that uniquely identifies each row of the result. The values of this field are registers with the same structure as the association fields. In this case the value of the rel attribute is always self and the path expression is absolute.

Use of “ORDER BY”

You can only add a field to the ORDER BY clause if query also project that field.

Aggregations (GROUP BY)

SELECT_NAVIGATIONAL does not support doing a GROUP BY that involves expanded fields (e.g. “customer_role / id”).

Examples

Let us say we have this:

1. The view customer, whose primary key is the field cid.

2. The view order, whose primary key is the field oid.

3. An association between them called customer_orders, whose role name on the view customer is called final_orders.

Consider the following examples of valid navigational expressions:

The remaining clauses (SELECT, WHERE, ORDER BY, FETCH and OFFSET) are used in the same way as in the SELECT statement.