.. -*- coding: utf-8 -*-

.. _RQL:

RQL syntax

----------

.. _RQLKeywords:

Reserved keywords

~~~~~~~~~~~~~~~~~

::

  AND, ASC, BEING, DELETE, DESC, DISTINCT, EXISTS, FALSE, GROUPBY,

  HAVING, ILIKE, INSERT, LIKE, LIMIT, NOT, NOW, NULL, OFFSET,

  OR, ORDERBY, SET, TODAY, TRUE, UNION, WHERE, WITH

The keywords are not case sensitive. You should not use them when defining your

schema, or as RQL variable names.

.. _RQLCase:

Case

~~~~

* Variables should be all upper-cased.

* Relation should be all lower-cased and match exactly names of relations defined

  in the schema.

* Entity types should start with an upper cased letter and be followed by at least

  a lower cased latter.

.. _RQLVariables:

Variables and typing

~~~~~~~~~~~~~~~~~~~~

Entities and values to browse and/or select are represented in the query by

*variables* that must be written in capital letters.

With RQL, we do not distinguish between entities and attributes. The value of an

attribute is considered as an entity of a particular type (see below), linked to

one (real) entity by a relation called the name of the attribute, where the

entity is the subject and the attribute the object.

The possible type(s) for each variable is derived from the schema according to

the constraints expressed above and thanks to the relations between each

variable.

We can restrict the possible types for a variable using the special relation

**is** in the restrictions.

.. _VirtualRelations:

Virtual relations

~~~~~~~~~~~~~~~~~

Those relations may only be used in RQL query but are not actual attributes of

your entities.

* `has_text`: relation to use to query the full text index (only for entities

  having fulltextindexed attributes).

* `identity`: relation to use to tell that a RQL variable is the same as another

  when you've to use two different variables for querying purpose. On the

  opposite it's also useful together with the ``NOT`` operator to tell that two

  variables should not identify the same entity

.. _RQLLiterals:

Literal expressions

~~~~~~~~~~~~~~~~~~~

Bases types supported by RQL are those supported by yams schema. Literal values

are expressed as explained below:

* string should be between double or single quotes. If the value contains a

  quote, it should be preceded by a backslash '\\'

* floats separator is dot '.'

* boolean values are ``TRUE`` and ``FALSE`` keywords

* date and time should be expressed as a string with ISO notation : YYYY/MM/DD

  [hh:mm], or using keywords ``TODAY`` and ``NOW``

You may also use the ``NULL`` keyword, meaning 'unspecified'.

.. _RQLOperators:

Operators

~~~~~~~~~

.. _RQLLogicalOperators:

Logical operators

`````````````````

::

     AND, OR, NOT, ','

',' is equivalent to 'AND' but with the smallest among the priority of logical

operators (see :ref:`RQLOperatorsPriority`).

.. _RQLMathematicalOperators:

Mathematical operators

``````````````````````

+----------+---------------------+-----------+--------+

| Operator |    Description      | Example   | Result |

+==========+=====================+===========+========+

|  `+`     | addition            | 2 + 3     | 5      |

+----------+---------------------+-----------+--------+

|  `-`     | subtraction         | 2 - 3     | -1     |

+----------+---------------------+-----------+--------+

|  `*`     | multiplication      | 2 * 3     | 6      |

+----------+---------------------+-----------+--------+

|  /       | division            | 4 / 2     | 2      |

+----------+---------------------+-----------+--------+

|  %       | modulo (remainder)  | 5 % 4     | 1      |

+----------+---------------------+-----------+--------+

|  ^       | exponentiation      | 2.0 ^ 3.0 | 8      |

+----------+---------------------+-----------+--------+

|  &       | bitwise AND         | 91 & 15   | 11     |

+----------+---------------------+-----------+--------+

|  `|`     | bitwise OR          | 32 | 3    | 35     |

+----------+---------------------+-----------+--------+

|  #       | bitwise XOR         | 17 # 5    | 20     |

+----------+---------------------+-----------+--------+

|  ~       | bitwise NOT         | ~1        | -2     |

+----------+---------------------+-----------+--------+

|  <<      | bitwise shift left  | 1 << 4    | 16     |

+----------+---------------------+-----------+--------+

|  >>      | bitwise shift right | 8 >> 2    | 2      |

+----------+---------------------+-----------+--------+

Notice integer division truncates results depending on the backend behaviour. For

instance, postgresql does.

.. _RQLComparisonOperators:

Comparison operators

````````````````````

 ::

     =, !=, <, <=, >=, >, IN

The syntax to use comparison operators is:

    `VARIABLE attribute <operator> VALUE`

The `=` operator is the default operator and can be omitted, i.e. :

    `VARIABLE attribute = VALUE`

is equivalent to

    `VARIABLE attribute VALUE`

The operator `IN` provides a list of possible values:

.. sourcecode:: sql

    Any X WHERE X name IN ('chauvat', 'fayolle', 'di mascio', 'thenault')

.. _RQLStringOperators:

String operators

````````````````

::

  LIKE, ILIKE, ~=, REGEXP

The ``LIKE`` string operator can be used with the special character `%` in

a string as wild-card:

.. sourcecode:: sql

     -- match every entity whose name starts with 'Th'

     Any X WHERE X name ~= 'Th%'

     -- match every entity whose name endswith 'lt'

     Any X WHERE X name LIKE '%lt'

     -- match every entity whose name contains a 'l' and a 't'

     Any X WHERE X name LIKE '%l%t%'

``ILIKE`` is the case insensitive version of ``LIKE``. It's not

available on all backend (e.g. sqlite doesn't support it). If not available for

your backend, ``ILIKE`` will behave like ``LIKE``.

`~=` is a shortcut version of ``ILIKE``, or of ``LIKE`` when the

former is not available on the back-end.

The ``REGEXP`` is an alternative to ``LIKE`` that supports POSIX

regular expressions:

.. sourcecode:: sql

   -- match entities whose title starts with a digit

   Any X WHERE X title REGEXP "^[0-9].*"

The underlying SQL operator used is back-end-dependent :

- the ``~`` operator is used for postgresql,

- the ``REGEXP`` operator for mysql and sqlite.

Other back-ends are not supported yet.

.. _RQLOperatorsPriority:

Operators priority

``````````````````

#. `(`, `)`

#. `^`, `<<`, `>>`

#. `*`, `/`, `%`, `&`

#. `+`, `-`, `|`, `#`

#. `NOT`

#. `AND`

#. `OR`

#. `,`

.. _RQLSearchQuery:

Search Query

~~~~~~~~~~~~

Simplified grammar of search query: ::

   [ `DISTINCT`] `Any` V1 (, V2) \*

   [ `GROUPBY` V1 (, V2) \*] [ `ORDERBY` <orderterms>]

   [ `LIMIT` <value>] [ `OFFSET` <value>]

   [ `WHERE` <triplet restrictions>]

   [ `WITH` V1 (, V2)\* BEING (<query>)]

   [ `HAVING` <other restrictions>]

   [ `UNION` <query>]

Selection

`````````

The fist occuring clause is the selection of terms that should be in the result

set.  Terms may be variable, literals, function calls, arithmetic, etc. and each

term is separated by a comma.

There will be as much column in the result set as term in this clause, respecting

order.

Syntax for function call is somewhat intuitive, for instance:

.. sourcecode:: sql

    Any UPPER(N) WHERE P firstname N

Grouping and aggregating

````````````````````````

The ``GROUPBY`` keyword is followed by a list of terms on which results

should be grouped. They are usually used with aggregate functions, responsible to

aggregate values for each group (see :ref:`RQLAggregateFunctions`).

For grouped queries, all selected variables must be either aggregated (i.e. used

by an aggregate function) or grouped (i.e. listed in the ``GROUPBY``

clause).

Sorting

```````

The ``ORDERBY`` keyword if followed by the definition of the selection

order: variable or column number followed by sorting method (``ASC``,

``DESC``), ``ASC`` being the default. If the sorting method is not

specified, then the sorting is ascendant (`ASC`).

Pagination

``````````

The ``LIMIT`` and ``OFFSET`` keywords may be respectively used to

limit the number of results and to tell from which result line to start (for

instance, use `LIMIT 20` to get the first 20 results, then `LIMIT 20 OFFSET 20`

to get the next 20.

Restrictions

````````````

The ``WHERE`` keyword introduce one of the "main" part of the query, where

you "define" variables and add some restrictions telling what you're interested

in.

It's a list of triplets "subject relation object", e.g. `V1 relation

(V2 | <static value>)`. Triplets are separated using :ref:`RQLLogicalOperators`.

.. note::

  About the negation operator (``NOT``):

  * ``NOT X relation Y`` is equivalent to ``NOT EXISTS(X relation Y)``

  * ``Any X WHERE NOT X owned_by U`` means "entities that have no relation

    ``owned_by``".

  * ``Any X WHERE NOT X owned_by U, U login "syt"`` means "the entity have no

    relation ``owned_by`` with the user syt". They may have a relation "owned_by"

    with another user.

In this clause, you can also use ``EXISTS`` when you want to know if some

expression is true and do not need the complete set of elements that make it

true. Testing for existence is much faster than fetching the complete set of

results, especially when you think about using ``OR`` against several expressions. For instance

if you want to retrieve versions which are in state "ready" or tagged by

"priority", you should write :

.. sourcecode:: sql

    Any X ORDERBY PN,N

    WHERE X num N, X version_of P, P name PN,

          EXISTS(X in_state S, S name "ready")

          OR EXISTS(T tags X, T name "priority")

not

.. sourcecode:: sql

    Any X ORDERBY PN,N

    WHERE X num N, X version_of P, P name PN,

          (X in_state S, S name "ready")

          OR (T tags X, T name "priority")

Both queries aren't at all equivalent :

* the former will retrieve all versions, then check for each one which are in the

  matching state of or tagged by the expected tag,

* the later will retrieve all versions, state and tags (cartesian product!),

  compute join and then exclude each row which are in the matching state or

  tagged by the expected tag. This implies that you won't get any result if the

  in_state or tag tables are empty (ie there is no such relation in the

  application). This is usually NOT what you want.

Another common case where you may want to use ``EXISTS`` is when you

find yourself using ``DISTINCT`` at the beginning of your query to

remove duplicate results. The typical case is when you have a

multivalued relation such as Version version_of Project and you want

to retrieve projects which have a version:

.. sourcecode:: sql

  Any P WHERE V version_of P

will return each project number of versions times. So you may be

tempted to use:

.. sourcecode:: sql

  DISTINCT Any P WHERE V version_of P

This will work, but is not efficient, as it will use the ``SELECT

DISTINCT`` SQL predicate, which needs to retrieve all projects, then

sort them and discard duplicates, which can have a very high cost for

large result sets. So the best way to write this is:

.. sourcecode:: sql

  Any P WHERE EXISTS(V version_of P)

You can also use the question mark (`?`) to mark optional relations. This allows

you to select entities related **or not** to another. It is a similar concept

to `Left outer join`_:

    the result of a left outer join (or simply left join) for table A and B

    always contains all records of the "left" table (A), even if the

    join-condition does not find any matching record in the "right" table (B).

You must use the `?` behind a variable to specify that the relation to

that variable is optional. For instance:

- Bugs of a project attached or not to a version

   .. sourcecode:: sql

       Any X, V WHERE X concerns P, P eid 42, X corrected_in V?

  You will get a result set containing all the project's tickets, with either the

  version in which it's fixed or None for tickets not related to a version.

- All cards and the project they document if any

  .. sourcecode:: sql

       Any C, P WHERE C is Card, P? documented_by C

Notice you may also use outer join:

- on the RHS of attribute relation, e.g.

  .. sourcecode:: sql

       Any X WHERE X ref XR, Y name XR?

  so that Y is outer joined on X by ref/name attributes comparison

- on any side of an ``HAVING`` expression, e.g.

  .. sourcecode:: sql

       Any X WHERE X creation_date XC, Y creation_date YC

       HAVING YEAR(XC)=YEAR(YC)?

  so that Y is outer joined on X by comparison of the year extracted from their

  creation date.

  .. sourcecode:: sql

       Any X WHERE X creation_date XC, Y creation_date YC

       HAVING YEAR(XC)?=YEAR(YC)

  would outer join X on Y instead.

Having restrictions

```````````````````

The ``HAVING`` clause, as in SQL, may be used to restrict a query

according to value returned by an aggregate function, e.g.