"Relation Query Language" tutorial

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