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

Let's learn RQL by practice...

.. contents::

Introduction
------------

RQL has a syntax and principle inspirated from the SQL query language, though
it's at a higher level in order to be more intuitive and suitable to easily make
advanced queries on a schema based database.

* the main idea in RQL is that we'are following relations between entities
* attributes are a special case of relations
* RQL has been inspirated from SQL but is at a higher level
* a knowledge of the application'schema is necessary to build rql queries

To use RQL, you'll have to know the basis of the language as well as a good
knowledge of the application'schema. You can always view it using the "schema"
link in user's dropdown menu (on the top-right of the screen) or by clicking here_.

.. _here: ../schema


Some bits of theory
-------------------

Variables et types
~~~~~~~~~~~~~~~~~~
Entities and attributes'value to follow and / or select are represented by the
query by *variables* which must be written upper-case.

Possible types for each variable are deducted from the schema according to
constraints in the query.

You can explicitly constrain a variable's type using the special relation "is".

Base types
~~~~~~~~~~
* `String` (literal: between double or simple quotes)
* `Int`, `Float` (using '.' as separator)
* `Date`, `Datetime`, `Time` (literal: string YYYY/MM/DD[ hh:mm] or
  `TODAY` and `NOW` keywords)
* `Boolean` (keywords `TRUE` et `FALSE`)
* keyword `NULL`

Opérateurs
~~~~~~~~~~
* Logical operators : `AND`, `OR`, `,`
* Mathematical operators: `+`, `-`, `*`, `/`
* Comparisons operators: `=`, `<`, `<=`, `>=`, `>`, `~=`, `LIKE`, `IN`

  * `=` is the default comparison operator

  * `LIKE` / `~=` permits use of the special character `%` in a string to tell
    the string must begin or end with a prefix or suffix (as SQL LIKE operator) ::

      Any X WHERE X name ~= 'Th%'
      Any X WHERE X name LIKE '%lt'

  * `IN` permits to give a list of possible values ::

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

Grammaire des requêtes de recherche
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
::

  [DISTINCT] <entity type> V1(, V2)*
  [GROUPBY V1(, V2)*]  [ORDERBY <orderterms>]
  [WHERE <restriction>]
  [LIMIT <value>] [OFFSET <value>]

:entity type:
  Type of the selected variable(s). You'll usually use `Any` type to not specify
  any type.
:restriction:
  List of relations to follow, in the form
    `V1 relation V2|<constant value>`
:orderterms:
  Define a selection order : variable or column number, followed by the sort method
  (`ASC`, `DESC`), with ASC as default when not specified

notice about grouped query (e.g using a `GROUPBY` clause): every selected
variable should be either grouped or used in an aggregat function.


Example schema
--------------

In this document, we will suppose the application's schema is the one described
here. Available entity types are :

:Person:
  ::

    name      (String, required)
    birthday (Date)


:Company:
  ::

    name   (String)


:Note:
  ::

    diem (Date)
    type (String)


And relations between those entities: ::

    Person  works_for    Company
    Person  evaluated_by Note
    Company evaluated_by Note


Meta-data
~~~~~~~~~
Every entities'type have the following meta-data:

* `eid (Int)`, a unique identifier
* `creation_date (Datetime)`, date on which the entity has been created
* `modification_date (Datetime)`, lastest date on which the entity has been modified

* `created_by (CWUser)`, relation to the user which has created this entity

* `owned_by (CWUser)`, relation to the user()s considered as owner of this
  entity, the entity's creator by default

* `is (Eetype)`, special relation to specify a variable type.

A user's entity has the following schema:

:CWUser:
  ::

    login        (String) not null
    password  (Password)
    firstname (String)
    surname   (String)


Basis queries
-------------
0. *Every persons* ::

      Person X

   or ::

      Any X WHERE X is Person


1. *The company named Logilab* ::

      Company S WHERE S name 'Logilab'


2. *Every entities with a "name" attribute whose value starts with 'Log'* ::

      Any S WHERE S name LIKE 'Log%'

   or ::

      Any S WHERE S name ~= 'Log%'

   This query may return Person or Company entities.


3. *Every persons working for the Logilab company* ::

      Person P WHERE P works_for S, S name "Logilab"

   or ::

      Person P WHERE P works_for S AND S name "Logilab"


4. *Company named Caesium ou Logilab* ::

      Company S WHERE S name IN ('Logilab','Caesium')

   or ::

      Company S WHERE S name 'Logilab' OR S name 'Caesium'


5. *Every company but ones named Caesium ou Logilab* ::

      Company S WHERE NOT S name IN ('Logilab','Caesium')

   or ::

      Company S WHERE NOT S name 'Logilab' AND NOT S name 'Caesium'


6. *Entities evaluated by the note with eid 43* ::

      Any X WHERE X evaluated_by N, N eid 43


7. *Every persons order by birthday from the youngest to the oldest* ::

      Person X ORDERBY D DESC WHERE X birthday D

   Notice you've to define a variable using the birthday relation to use it in the
   sort term.


8. *Number of persons working for each known company* ::

      Any S, COUNT(X) GROUPBY S WHERE X works_for S

   Notice you've that since you're writing a grouped query on S, X have to be
   either grouped as well or used in an aggregat function (as in this example).



Advanced
--------
0. *Person with no name specified (i.e NULL)* ::

      Person P WHERE P name NULL


1. *Person which are not working for any company* ::

      Person P WHERE NOT p works_for S


2. *Every company where person named toto isn't working* ::

      Company S WHERE NOT P works_for S , P name 'toto'


3. *Every entity which have been modified between today and yesterday* ::

      Any X WHERE X modification_date <= TODAY, X modification_date >= TODAY - 1


4. *Every note without type, to be done in the next 7 days, ordered by date* ::

      Any N, D where N is Note, N type NULL, N diem D, N diem >= TODAY,
      N diem < today + 7 ORDERBY D


5. *Person with an homonym (without duplicate)* ::

      DISTINCT Person X,Y where X name NX, Y name NX

   or even better (e.g. without both (Xeid, Yeid) and (Yeid, Xeid) in the results) ::

      Person X,Y where X name NX, Y name NX, X eid XE, Y eid > XE