.. -*- 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 théory

-------------------

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