.. -*- 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