doc/book/en/annexes/rql/language.rst
author Sylvain Thénault <sylvain.thenault@logilab.fr>
Wed, 18 Aug 2010 16:53:05 +0200
branchstable
changeset 6120 c000e41316ec
parent 5924 b218df942dd4
child 6324 bdb85e3602c8
permissions -rw-r--r--
[book] some more documentation and cleanups * merged existing facets documentation with the one I've just written and put almost everything within the code * added a note about __depends__ and __recommends__ and about the recommends semantic (extracted from a post on the ml) * added a note about write security checking (extracted from a post on the ml) * fixed some dumb sphinx errors
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     1
.. -*- coding: utf-8 -*-
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     2
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     3
.. _RQL:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     4
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     5
RQL syntax
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     6
----------
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     7
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     8
Reserved keywords
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
     9
~~~~~~~~~~~~~~~~~
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    10
The keywords are not case sensitive.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    11
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    12
::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    13
3686
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    14
  AND, ASC, BEING, DELETE, DESC, DISTINCT, EXISTS, FALSE, GROUPBY,
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    15
  HAVING, ILIKE, IN, INSERT, LIKE, LIMIT, NOT, NOW, NULL, OFFSET,
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    16
  OR, ORDERBY, SET, TODAY, TRUE, UNION, WHERE, WITH
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    17
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    18
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    19
Variables and Typing
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    20
~~~~~~~~~~~~~~~~~~~~
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    21
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    22
With RQL, we do not distinguish between entities and attributes. The
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    23
value of an attribute is considered an entity of a particular type (see
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    24
below), linked to one (real) entity by a relation called the name of
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    25
the attribute.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    26
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    27
Entities and values to browse and/or select are represented in
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    28
the query by *variables* that must be written in capital letters.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    29
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    30
There is a special type **Any**, referring to a non specific type.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    31
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    32
We can restrict the possible types for a variable using the
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    33
special relation **is** in the constraints.
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    34
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    35
The possible type(s) for each variable is derived from the schema
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    36
according to the constraints expressed above and thanks to the relations
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    37
between each variable.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    38
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    39
Built-in types
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    40
``````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    41
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    42
The base types supported are string (between double or single quotes),
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    43
integers or floats (the separator is '.'), dates and
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    44
boolean. We expect to receive a schema in which types String,
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    45
Int, Float, Date and Boolean are defined.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    46
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    47
* `String` (literal: between double or single quotes).
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    48
* `Int`, `Float` (separator being'.').
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    49
* `Date`, `Datetime`, `Time` (literal: string YYYY/MM/DD [hh:mm] or keywords
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    50
  `TODAY` and `NOW`).
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    51
* `Boolean` (keywords `TRUE` and `FALSE`).
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    52
* `Keyword` NULL.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    53
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    54
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    55
Operators
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    56
~~~~~~~~~
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    57
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    58
Logical Operators
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    59
`````````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    60
::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    61
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    62
     AND, OR, NOT, ','
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    63
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    64
  ',' is equivalent to 'AND' but with the smallest among the priority
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    65
  of logical operators (see :ref:`PriorityOperators`).
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    66
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    67
Mathematical Operators
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    68
``````````````````````
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    69
::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    70
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    71
     +, -, *, /
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    72
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    73
Comparison operators
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    74
````````````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    75
::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    76
3686
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    77
     =, <, <=, >=, >, ~=, IN, LIKE, ILIKE
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    78
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    79
* Syntax to use comparison operator:
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    80
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    81
    `VARIABLE relation operator VALUE`
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    82
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    83
* The operator `=` is the default operator and can be omitted.
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    84
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    85
* `relation` name is always attended
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    86
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    87
* The operator `LIKE` equivalent to `~=` can be used with the
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
    88
  special character `%` in a string to indicate that the chain
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    89
  must start or finish by a prefix/suffix:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    90
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    91
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    92
     Any X WHERE X name ~= 'Th%'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    93
     Any X WHERE X name LIKE '%lt'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    94
3686
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    95
* The operator `ILIKE` is the case insensitive version of `LIKE`.
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    96
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    97
* The operator `IN` provides a list of possible values:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    98
  ::
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
    99
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   100
    Any X WHERE X name IN ('chauvat', 'fayolle', 'di mascio', 'thenault')
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   101
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   102
5393
875bdc0fe8ce [doc/book] hide most XXXs
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 5312
diff changeset
   103
.. XXX nico: "A trick <> 'bar'" wouldn't it be more convenient than "NOT A trick 'bar'" ?
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   104
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   105
.. _PriorityOperators:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   106
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   107
Operators priority
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   108
``````````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   109
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   110
1. '*', '/'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   111
2. '+', '-'
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   112
3. 'NOT'
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   113
4. 'AND'
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   114
5. 'OR'
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   115
6. ','
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   116
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   117
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   118
Search Query
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   119
~~~~~~~~~~~~
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   120
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   121
   [ `DISTINCT`] <entity type> V1 (, V2) \ *
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   122
   [ `GROUPBY` V1 (V2) \*] [ `ORDERBY` <orderterms>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   123
   [ `LIMIT` <value>] [ `OFFSET` <value>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   124
   [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   125
   [ `WITH` V1 (, V2) \ * BEING (<query>)]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   126
   [ `HAVING` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   127
   [ `UNION` <query>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   128
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   129
:entity type:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   130
   Type of selected variables.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   131
   The special type `Any` is equivalent to not specify a type.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   132
:restriction:
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   133
   list of conditions to test successively
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   134
     `V1 relation V2 | <static value>`
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   135
:orderterms:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   136
   Definition of the selection order: variable or column number followed by
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   137
   sorting method ( `ASC`, `DESC`), ASC is the default.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   138
:note for grouped queries:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   139
   For grouped queries (e.g., a clause `GROUPBY`), all
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   140
   selected variables must be aggregated or grouped.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   141
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   142
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   143
Sorting and groups
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   144
``````````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   145
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   146
- For grouped queries (e.g. with a GROUPBY clause), all
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   147
  selected variables should be grouped at the right of the keyword.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   148
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   149
- If the sorting method (SORT_METHOD) is not specified, then the sorting is
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   150
  ascendant (`ASC`).
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   151
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   152
- Aggregate Functions: COUNT, MIN, MAX, AVG, SUM, GROUP_CONCAT
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   153
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   154
Having
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   155
``````
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   156
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   157
The HAVING clause, as in SQL, has been originally introduced to restrict a query
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   158
according to value returned by an aggregate function, e.g.::
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   159
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   160
    Any X GROUPBY X WHERE X relation Y HAVING COUNT(Y) > 10
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   161
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   162
It may however be used for something else...
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   163
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   164
In the WHERE clause, we are limited to 3-expression, such thing can't be
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   165
expressed directly as in the SQL's way. But this can be expressed using HAVING
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   166
comparison expression.
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   167
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   168
For instance, let's say you want to get people whose uppercased first name equals
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   169
to another person uppercased first name::
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   170
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   171
    Person X WHERE X firstname XFN, Y firstname YFN HAVING X > Y, UPPER(XFN) = UPPER(YFN)
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   172
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   173
This open some new possibilities. Another example::
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   174
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   175
    Person X WHERE X birthday XB HAVING YEAR(XB) = 2000
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   176
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   177
That lets you use transformation functions not only in selection but for
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   178
restriction as well and to by-pass limitation of the WHERE clause, which was the
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   179
major flaw in the RQL language.
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   180
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   181
Notice that while we would like this to work without the HAVING clause, this
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   182
can't be currently be done because it introduces an ambiguity in RQL's grammar
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   183
that can't be handled by Yapps_, the parser's generator we're using.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   184
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   185
Negation
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   186
````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   187
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   188
* A query such as `Document X WHERE NOT X owned_by U` means "the documents have
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   189
  no relation `owned_by`".
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   190
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   191
* But the query `Document X WHERE NOT X owned_by U, U login "syt"` means "the
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   192
  documents have no relation `owned_by` with the user syt". They may have a
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   193
  relation "owned_by" with another user.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   194
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   195
Identity
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   196
````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   197
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   198
You can use the special relation `identity` in a query to
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   199
add an identity constraint between two variables. This is equivalent
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   200
to ``is`` in python::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   201
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   202
   Any A WHERE A comments B, A identity B
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   203
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   204
return all objects that comment themselves. The relation `identity` is
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   205
especially useful when defining the rules for securities with `RQLExpressions`.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   206
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   207
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   208
Limit / offset
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   209
``````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   210
::
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   211
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   212
    Any P ORDERBY N LIMIT 5 OFFSET 10 WHERE P is Person, P firstname N
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   213
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   214
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   215
Exists
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   216
``````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   217
::
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   218
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   219
    Any X ORDERBY PN,N
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   220
    WHERE X num N, X version_of P, P name PN,
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   221
          EXISTS(X in_state S, S name IN ("dev", "ready"))
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   222
          OR EXISTS(T tags X, T name "priority")
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   223
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   224
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   225
Optional relations
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   226
``````````````````
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   227
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   228
It is a similar concept that the `Left outer join`_:
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   229
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   230
    the result of a left outer join (or simply left join) for table A and B
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   231
    always contains all records of the "left" table (A), even if the
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   232
    join-condition does not find any matching record in the "right" table (B).
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   233
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   234
* They allow you to select entities related or not to another.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   235
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   236
* You must use the `?` behind the variable to specify that the relation
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   237
  toward it is optional:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   238
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   239
   - Anomalies of a project attached or not to a version ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   240
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   241
       Any X, V WHERE X concerns P, P eid 42, X corrected_in V?
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   242
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   243
   - All cards and the project they document if necessary ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   244
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   245
       Any C, P WHERE C is Card, P? documented_by C
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   246
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   247
    Any T,P,V WHERE T is Ticket, T concerns P, T done_in V?
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   248
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   249
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   250
Subqueries
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   251
``````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   252
::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   253
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   254
    (Any X WHERE X is Person) UNION (Any X WHERE X is Company)
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   255
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   256
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   257
     DISTINCT Any W, REF
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   258
        WITH W, REF BEING
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   259
            (
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   260
              (Any W, REF WHERE W is Workcase, W ref REF,
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   261
                                 W concerned_by D, D name "Logilab")
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   262
               UNION
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   263
              (Any W, REF WHERE W is Workcase, W ref REF, '
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   264
                                W split_into WP, WP name "WP1")
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   265
            )
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   266
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   267
Function calls
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   268
``````````````
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   269
::
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   270
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   271
    Any UPPER(N) WHERE P firstname N
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   272
    Any LOWER(N) WHERE P firstname N
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   273
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   274
Functions available on string: `UPPER`, `LOWER`
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   275
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   276
.. XXX retrieve available function automatically
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   277
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   278
For a performance issue, you can enrich the RQL dialect by RDMS (Relational database management system) functions.
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   279
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   280
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   281
Examples
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   282
~~~~~~~~
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   283
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   284
- *Search for the object of identifier 53*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   285
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   286
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   287
        Any WHERE X
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   288
        X eid 53
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   289
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   290
- *Search material such as comics, owned by syt and available*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   291
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   292
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   293
        Any X WHERE X is Document
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   294
        X occurence_of F, F class C, C name 'Comics'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   295
        X owned_by U, U login 'syt'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   296
        X available TRUE
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   297
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   298
- *Looking for people working for eurocopter interested in training*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   299
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   300
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   301
        Any P WHERE
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   302
        P is Person, P work_for S, S name 'Eurocopter'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   303
        P interested_by T, T name 'training'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   304
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   305
- *Search note less than 10 days old written by jphc or ocy*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   306
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   307
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   308
        Any N WHERE
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   309
        N is Note, N written_on D, D day> (today -10),
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   310
        N written_by P, P name 'jphc' or P name 'ocy'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   311
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   312
- *Looking for people interested in training or living in Paris*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   313
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   314
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   315
        Any P WHERE
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   316
        P is Person, (P interested_by T, T name 'training') OR
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   317
        (P city 'Paris')
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   318
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   319
- *The surname and firstname of all people*
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   320
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   321
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   322
        Any N, P WHERE
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   323
        X is Person, X name N, X firstname P
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   324
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   325
  Note that the selection of several entities generally force
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   326
  the use of "Any" because the type specification applies otherwise
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   327
  to all the selected variables. We could write here
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   328
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   329
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   330
        String N, P WHERE
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   331
        X is Person, X name N, X first_name P
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   332
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   333
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   334
  Note: You can not specify several types with * ... where X is FirstType or X is SecondType*.
2043
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1714
diff changeset
   335
  To specify several types explicitly, you have to do
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   336
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   337
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   338
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   339
        Any X where X is in (FirstType, SecondType)
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   340
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   341
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   342
Insertion query
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   343
~~~~~~~~~~~~~~~
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   344
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   345
    `INSERT` <entity type> V1 (, <entity type> V2) \ * `:` <assignments>
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   346
    [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   347
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   348
:assignments:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   349
   list of relations to assign in the form `V1 relationship V2 | <static value>`
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   350
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   351
The restriction can define variables used in assignments.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   352
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   353
Caution, if a restriction is specified, the insertion is done for
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   354
*each line result returned by the restriction*.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   355
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   356
- *Insert a new person named 'foo'*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   357
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   358
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   359
        INSERT Person X: X name 'foo'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   360
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   361
- *Insert a new person named 'foo', another called 'nice' and a 'friend' relation
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   362
  between them*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   363
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   364
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   365
        INSERT Person X, Person Y: X name 'foo', Y name 'nice', X friend Y
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   366
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   367
- *Insert a new person named 'foo' and a 'friend' relation with an existing
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   368
  person called 'nice'*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   369
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   370
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   371
        INSERT Person X: X name 'foo', X friend  Y WHERE name 'nice'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   372
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   373
Update and relation creation queries
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   374
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   375
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   376
    `SET` <assignements>
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   377
    [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   378
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   379
Caution, if a restriction is specified, the update is done *for
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   380
each result line returned by the restriction*.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   381
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   382
- *Renaming of the person named 'foo' to 'bar' with the first name changed*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   383
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   384
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   385
        SET X name 'bar', X firstname 'original' WHERE X is Person, X name 'foo'
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   386
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   387
- *Insert a relation of type 'know' between objects linked by
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   388
  the relation of type 'friend'*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   389
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   390
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   391
        SET X know Y  WHERE X friend Y
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   392
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   393
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   394
Deletion query
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   395
~~~~~~~~~~~~~~
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   396
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   397
    `DELETE` (<entity type> V) | (V1 relation v2 ),...
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   398
    [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   399
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   400
Caution, if a restriction is specified, the deletion is made *for
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   401
each line result returned by the restriction*.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   402
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   403
- *Deletion of the person named 'foo'*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   404
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   405
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   406
        DELETE Person X WHERE X name 'foo'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   407
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   408
- *Removal of all relations of type 'friend' from the person named 'foo'*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   409
  ::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   410
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   411
        DELETE X friend Y WHERE X is Person, X name 'foo'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   412
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   413
5312
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   414
Virtual RQL relations
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   415
~~~~~~~~~~~~~~~~~~~~~
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   416
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   417
Those relations may only be used in RQL query and are not actual
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   418
attributes of your entities.
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   419
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   420
* `has_text`: relation to use to query the full text index (only for
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   421
  entities having fulltextindexed attributes).
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   422
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   423
* `identity`: `Identity`_ relation to use to tell that a RQL variable should be
5312
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   424
  the same as another (but you've to use two different rql variables
d2dbba898a96 [doc/book] misc on views, docstrings
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 3686
diff changeset
   425
  for querying purpose)
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   426
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   427
* `is`: relation to enforce possible types for a variable
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   428
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   429
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   430
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   431
.. _Yapps: http://theory.stanford.edu/~amitp/yapps/
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   432
.. _Left outer join: http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join