doc/book/annexes/rql/language.rst
author Nicolas Chauvat <nicolas.chauvat@logilab.fr>
Wed, 14 Dec 2016 13:34:08 +0100
branch3.24
changeset 11889 207e6d088791
parent 10522 1660a0fa4f43
child 12621 092d201a7c17
permissions -rw-r--r--
[doc/book] fix error in markup
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
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
     8
.. _RQLKeywords:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
     9
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    10
Reserved keywords
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
::
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    14
3686
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    15
  AND, ASC, BEING, DELETE, DESC, DISTINCT, EXISTS, FALSE, GROUPBY,
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    16
  HAVING, ILIKE, INSERT, LIKE, LIMIT, NOT, NOW, NULL, OFFSET,
3686
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
    17
  OR, ORDERBY, SET, TODAY, TRUE, UNION, WHERE, WITH
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    18
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    19
The keywords are not case sensitive. You should not use them when defining your
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    20
schema, or as RQL variable names.
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    21
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    22
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    23
.. _RQLCase:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
    24
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    25
Case
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    26
~~~~
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    27
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    28
* Variables should be all upper-cased.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    29
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    30
* Relation should be all lower-cased and match exactly names of relations defined
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    31
  in the schema.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    32
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    33
* Entity types should start with an upper cased letter and be followed by at least
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    34
  a lower cased latter.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    35
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    36
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    37
.. _RQLVariables:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
    38
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    39
Variables and typing
1714
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
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    42
Entities and values to browse and/or select are represented in the query by
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    43
*variables* that must be written in capital letters.
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
    44
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    45
With RQL, we do not distinguish between entities and attributes. The value of an
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    46
attribute is considered as an entity of a particular type (see below), linked to
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    47
one (real) entity by a relation called the name of the attribute, where the
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    48
entity is the subject and the attribute the object.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    49
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    50
The possible type(s) for each variable is derived from the schema according to
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    51
the constraints expressed above and thanks to the relations between each
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    52
variable.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    53
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    54
We can restrict the possible types for a variable using the special relation
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    55
**is** in the restrictions.
1714
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
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
    58
.. _VirtualRelations:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
    59
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    60
Virtual relations
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    61
~~~~~~~~~~~~~~~~~
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    62
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    63
Those relations may only be used in RQL query but are not actual attributes of
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    64
your entities.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    65
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    66
* `has_text`: relation to use to query the full text index (only for entities
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    67
  having fulltextindexed attributes).
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    68
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    69
* `identity`: relation to use to tell that a RQL variable is the same as another
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    70
  when you've to use two different variables for querying purpose. On the
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
    71
  opposite it's also useful together with the ``NOT`` operator to tell that two
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    72
  variables should not identify the same entity
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    73
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    74
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    75
.. _RQLLiterals:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
    76
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    77
Literal expressions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    78
~~~~~~~~~~~~~~~~~~~
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    79
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    80
Bases types supported by RQL are those supported by yams schema. Literal values
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    81
are expressed as explained below:
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    82
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    83
* string should be between double or single quotes. If the value contains a
9313
95119045ffb5 [book] properly escape backslash
Dimitri Papadopoulos <dimitri.papadopoulos@cea.fr>
parents: 8630
diff changeset
    84
  quote, it should be preceded by a backslash '\\'
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    85
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    86
* floats separator is dot '.'
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    87
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
    88
* boolean values are ``TRUE`` and ``FALSE`` keywords
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    89
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    90
* date and time should be expressed as a string with ISO notation : YYYY/MM/DD
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
    91
  [hh:mm], or using keywords ``TODAY`` and ``NOW``
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    92
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
    93
You may also use the ``NULL`` keyword, meaning 'unspecified'.
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    94
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    95
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
    96
.. _RQLOperators:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
    97
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    98
Operators
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
    99
~~~~~~~~~
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   100
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   101
.. _RQLLogicalOperators:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   102
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   103
Logical operators
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
::
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
     AND, OR, NOT, ','
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   108
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   109
',' is equivalent to 'AND' but with the smallest among the priority of logical
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   110
operators (see :ref:`RQLOperatorsPriority`).
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   111
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   112
.. _RQLMathematicalOperators:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   113
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   114
Mathematical operators
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   115
``````````````````````
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   116
8017
1df3b5e9d010 [book] Some minor fixes to the CubicWeb doc (typos, invalid ReST, ...)
Anthony Truchet <anthony.truchet@logilab.fr>
parents: 7738
diff changeset
   117
+----------+---------------------+-----------+--------+
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   118
| Operator |    Description      | Example   | Result |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   119
+==========+=====================+===========+========+
8017
1df3b5e9d010 [book] Some minor fixes to the CubicWeb doc (typos, invalid ReST, ...)
Anthony Truchet <anthony.truchet@logilab.fr>
parents: 7738
diff changeset
   120
|  `+`     | addition            | 2 + 3     | 5      |
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   121
+----------+---------------------+-----------+--------+
8017
1df3b5e9d010 [book] Some minor fixes to the CubicWeb doc (typos, invalid ReST, ...)
Anthony Truchet <anthony.truchet@logilab.fr>
parents: 7738
diff changeset
   122
|  `-`     | subtraction         | 2 - 3     | -1     |
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   123
+----------+---------------------+-----------+--------+
8017
1df3b5e9d010 [book] Some minor fixes to the CubicWeb doc (typos, invalid ReST, ...)
Anthony Truchet <anthony.truchet@logilab.fr>
parents: 7738
diff changeset
   124
|  `*`     | multiplication      | 2 * 3     | 6      |
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   125
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   126
|  /       | division            | 4 / 2     | 2      |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   127
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   128
|  %       | modulo (remainder)  | 5 % 4     | 1      |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   129
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   130
|  ^       | exponentiation      | 2.0 ^ 3.0 | 8      |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   131
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   132
|  &       | bitwise AND         | 91 & 15   | 11     |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   133
+----------+---------------------+-----------+--------+
10378
ad0cea3ab218 [doc] escape | operator so it will be visible in the docs
Rabah Meradi <rabah.meradi@logilab.fr>
parents: 9801
diff changeset
   134
|  `|`     | bitwise OR          | 32 | 3    | 35     |
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   135
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   136
|  #       | bitwise XOR         | 17 # 5    | 20     |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   137
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   138
|  ~       | bitwise NOT         | ~1        | -2     |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   139
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   140
|  <<      | bitwise shift left  | 1 << 4    | 16     |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   141
+----------+---------------------+-----------+--------+
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   142
|  >>      | bitwise shift right | 8 >> 2    | 2      |
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   143
+----------+---------------------+-----------+--------+
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   144
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   145
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   146
Notice integer division truncates results depending on the backend behaviour. For
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   147
instance, postgresql does.
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   148
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   149
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   150
.. _RQLComparisonOperators:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   151
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   152
Comparison operators
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   153
````````````````````
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   154
 ::
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   155
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   156
     =, !=, <, <=, >=, >, IN
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   157
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   158
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   159
The syntax to use comparison operators is:
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   160
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   161
    `VARIABLE attribute <operator> VALUE`
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   162
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   163
The `=` operator is the default operator and can be omitted, i.e. :
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   164
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   165
    `VARIABLE attribute = VALUE`
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   166
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   167
is equivalent to
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   168
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   169
    `VARIABLE attribute VALUE`
3686
d44b17016944 [doc] document rql operator ILIKE
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2175
diff changeset
   170
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   171
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   172
The operator `IN` provides a list of possible values:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   173
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   174
.. sourcecode:: sql
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   175
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   176
    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
   177
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   178
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   179
.. _RQLStringOperators:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   180
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   181
String operators
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   182
````````````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   183
::
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   184
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   185
  LIKE, ILIKE, ~=, REGEXP
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   186
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   187
The ``LIKE`` string operator can be used with the special character `%` in
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   188
a string as wild-card:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   189
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   190
.. sourcecode:: sql
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   191
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   192
     -- match every entity whose name starts with 'Th'
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   193
     Any X WHERE X name ~= 'Th%'
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   194
     -- match every entity whose name endswith 'lt'
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   195
     Any X WHERE X name LIKE '%lt'
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   196
     -- match every entity whose name contains a 'l' and a 't'
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   197
     Any X WHERE X name LIKE '%l%t%'
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   198
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   199
``ILIKE`` is the case insensitive version of ``LIKE``. It's not
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   200
available on all backend (e.g. sqlite doesn't support it). If not available for
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   201
your backend, ``ILIKE`` will behave like ``LIKE``.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   202
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   203
`~=` is a shortcut version of ``ILIKE``, or of ``LIKE`` when the
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   204
former is not available on the back-end.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   205
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   206
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   207
The ``REGEXP`` is an alternative to ``LIKE`` that supports POSIX
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   208
regular expressions:
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   209
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   210
.. sourcecode:: sql
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   211
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   212
   -- match entities whose title starts with a digit
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   213
   Any X WHERE X title REGEXP "^[0-9].*"
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   214
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   215
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   216
The underlying SQL operator used is back-end-dependent :
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   217
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   218
- the ``~`` operator is used for postgresql,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   219
- the ``REGEXP`` operator for mysql and sqlite.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   220
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   221
Other back-ends are not supported yet.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   222
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   223
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   224
.. _RQLOperatorsPriority:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   225
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   226
Operators priority
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   227
``````````````````
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   228
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   229
#. `(`, `)`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   230
#. `^`, `<<`, `>>`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   231
#. `*`, `/`, `%`, `&`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   232
#. `+`, `-`, `|`, `#`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   233
#. `NOT`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   234
#. `AND`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   235
#. `OR`
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   236
#. `,`
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   237
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   238
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   239
.. _RQLSearchQuery:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   240
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   241
Search Query
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
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   244
Simplified grammar of search query: ::
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   245
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   246
   [ `DISTINCT`] `Any` V1 (, V2) \*
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   247
   [ `GROUPBY` V1 (, V2) \*] [ `ORDERBY` <orderterms>]
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   248
   [ `LIMIT` <value>] [ `OFFSET` <value>]
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   249
   [ `WHERE` <triplet restrictions>]
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   250
   [ `WITH` V1 (, V2)\* BEING (<query>)]
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   251
   [ `HAVING` <other restrictions>]
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   252
   [ `UNION` <query>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   253
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   254
Selection
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   255
`````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   256
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   257
The fist occuring clause is the selection of terms that should be in the result
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   258
set.  Terms may be variable, literals, function calls, arithmetic, etc. and each
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   259
term is separated by a comma.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   260
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   261
There will be as much column in the result set as term in this clause, respecting
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   262
order.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   263
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   264
Syntax for function call is somewhat intuitive, for instance:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   265
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   266
.. sourcecode:: sql
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   267
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   268
    Any UPPER(N) WHERE P firstname N
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   269
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   270
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   271
Grouping and aggregating
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   272
````````````````````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   273
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   274
The ``GROUPBY`` keyword is followed by a list of terms on which results
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   275
should be grouped. They are usually used with aggregate functions, responsible to
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   276
aggregate values for each group (see :ref:`RQLAggregateFunctions`).
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   277
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   278
For grouped queries, all selected variables must be either aggregated (i.e. used
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   279
by an aggregate function) or grouped (i.e. listed in the ``GROUPBY``
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   280
clause).
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   281
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   282
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   283
Sorting
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   284
```````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   285
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   286
The ``ORDERBY`` keyword if followed by the definition of the selection
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   287
order: variable or column number followed by sorting method (``ASC``,
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   288
``DESC``), ``ASC`` being the default. If the sorting method is not
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   289
specified, then the sorting is ascendant (`ASC`).
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   290
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   291
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   292
Pagination
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   293
``````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   294
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   295
The ``LIMIT`` and ``OFFSET`` keywords may be respectively used to
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   296
limit the number of results and to tell from which result line to start (for
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   297
instance, use `LIMIT 20` to get the first 20 results, then `LIMIT 20 OFFSET 20`
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   298
to get the next 20.
1714
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
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   301
Restrictions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   302
````````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   303
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   304
The ``WHERE`` keyword introduce one of the "main" part of the query, where
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   305
you "define" variables and add some restrictions telling what you're interested
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   306
in.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   307
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   308
It's a list of triplets "subject relation object", e.g. `V1 relation
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   309
(V2 | <static value>)`. Triplets are separated using :ref:`RQLLogicalOperators`.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   310
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   311
.. note::
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   312
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   313
  About the negation operator (``NOT``):
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   314
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   315
  * ``NOT X relation Y`` is equivalent to ``NOT EXISTS(X relation Y)``
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   316
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   317
  * ``Any X WHERE NOT X owned_by U`` means "entities that have no relation
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   318
    ``owned_by``".
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   319
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   320
  * ``Any X WHERE NOT X owned_by U, U login "syt"`` means "the entity have no
11889
207e6d088791 [doc/book] fix error in markup
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 10522
diff changeset
   321
    relation ``owned_by`` with the user syt". They may have a relation "owned_by"
207e6d088791 [doc/book] fix error in markup
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 10522
diff changeset
   322
    with another user.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   323
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   324
In this clause, you can also use ``EXISTS`` when you want to know if some
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   325
expression is true and do not need the complete set of elements that make it
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   326
true. Testing for existence is much faster than fetching the complete set of
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   327
results, especially when you think about using ``OR`` against several expressions. For instance
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   328
if you want to retrieve versions which are in state "ready" or tagged by
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   329
"priority", you should write :
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   330
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   331
.. sourcecode:: sql
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   332
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   333
    Any X ORDERBY PN,N
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   334
    WHERE X num N, X version_of P, P name PN,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   335
          EXISTS(X in_state S, S name "ready")
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   336
          OR EXISTS(T tags X, T name "priority")
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   337
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   338
not
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   339
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   340
.. sourcecode:: sql
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   341
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   342
    Any X ORDERBY PN,N
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   343
    WHERE X num N, X version_of P, P name PN,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   344
          (X in_state S, S name "ready")
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   345
          OR (T tags X, T name "priority")
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   346
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   347
Both queries aren't at all equivalent :
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   348
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   349
* the former will retrieve all versions, then check for each one which are in the
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   350
  matching state of or tagged by the expected tag,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   351
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   352
* the later will retrieve all versions, state and tags (cartesian product!),
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   353
  compute join and then exclude each row which are in the matching state or
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   354
  tagged by the expected tag. This implies that you won't get any result if the
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   355
  in_state or tag tables are empty (ie there is no such relation in the
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   356
  application). This is usually NOT what you want.
7529
2fdc310be7cd [book] add autoload section from code and fix sphinx warnings
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 7081
diff changeset
   357
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   358
Another common case where you may want to use ``EXISTS`` is when you
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   359
find yourself using ``DISTINCT`` at the beginning of your query to
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   360
remove duplicate results. The typical case is when you have a
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   361
multivalued relation such as Version version_of Project and you want
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   362
to retrieve projects which have a version:
7529
2fdc310be7cd [book] add autoload section from code and fix sphinx warnings
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 7081
diff changeset
   363
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   364
.. sourcecode:: sql
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   365
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   366
  Any P WHERE V version_of P
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   367
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   368
will return each project number of versions times. So you may be
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   369
tempted to use:
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   370
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   371
.. sourcecode:: sql
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   372
10449
279b3662e8f1 [doc/book] fix rql syntax examples
Julien Cristau <julien.cristau@logilab.fr>
parents: 10378
diff changeset
   373
  DISTINCT Any P WHERE V version_of P
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   374
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   375
This will work, but is not efficient, as it will use the ``SELECT
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   376
DISTINCT`` SQL predicate, which needs to retrieve all projects, then
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   377
sort them and discard duplicates, which can have a very high cost for
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   378
large result sets. So the best way to write this is:
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   379
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   380
.. sourcecode:: sql
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   381
10449
279b3662e8f1 [doc/book] fix rql syntax examples
Julien Cristau <julien.cristau@logilab.fr>
parents: 10378
diff changeset
   382
  Any P WHERE EXISTS(V version_of P)
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   383
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   384
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   385
You can also use the question mark (`?`) to mark optional relations. This allows
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   386
you to select entities related **or not** to another. It is a similar concept
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   387
to `Left outer join`_:
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   388
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   389
    the result of a left outer join (or simply left join) for table A and B
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   390
    always contains all records of the "left" table (A), even if the
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   391
    join-condition does not find any matching record in the "right" table (B).
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   392
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   393
You must use the `?` behind a variable to specify that the relation to
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   394
that variable is optional. For instance:
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   395
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   396
- Bugs of a project attached or not to a version
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   397
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   398
   .. sourcecode:: sql
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   399
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   400
       Any X, V WHERE X concerns P, P eid 42, X corrected_in V?
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   401
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   402
  You will get a result set containing all the project's tickets, with either the
8145
e88a24f88143 [book] Add some details about usage of EXISTS
Alexandre Fayolle
parents: 8032
diff changeset
   403
  version in which it's fixed or None for tickets not related to a version.
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   404
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   405
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   406
- All cards and the project they document if any
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   407
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   408
  .. sourcecode:: sql
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   409
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   410
       Any C, P WHERE C is Card, P? documented_by C
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   411
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   412
Notice you may also use outer join:
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   413
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   414
- on the RHS of attribute relation, e.g.
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   415
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   416
  .. sourcecode:: sql
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   417
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   418
       Any X WHERE X ref XR, Y name XR?
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   419
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   420
  so that Y is outer joined on X by ref/name attributes comparison
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   421
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   422
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   423
- on any side of an ``HAVING`` expression, e.g.
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   424
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   425
  .. sourcecode:: sql
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   426
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   427
       Any X WHERE X creation_date XC, Y creation_date YC
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   428
       HAVING YEAR(XC)=YEAR(YC)?
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   429
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   430
  so that Y is outer joined on X by comparison of the year extracted from their
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   431
  creation date.
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   432
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   433
  .. sourcecode:: sql
7738
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   434
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   435
       Any X WHERE X creation_date XC, Y creation_date YC
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   436
       HAVING YEAR(XC)?=YEAR(YC)
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   437
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   438
  would outer join X on Y instead.
e0c86caf5c48 [book, rql] update rql langage chapter with new operators and outer join capabilities
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7632
diff changeset
   439
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   440
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   441
Having restrictions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   442
```````````````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   443
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   444
The ``HAVING`` clause, as in SQL, may be used to restrict a query
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   445
according to value returned by an aggregate function, e.g.
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   446
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   447
.. sourcecode:: sql
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   448
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   449
    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
   450
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   451
It may however be used for something else: In the ``WHERE`` clause, we are
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   452
limited to triplet expressions, so some things may not be expressed there. Let's
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   453
take an example : if you want to get people whose upper-cased first name equals to
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   454
another person upper-cased first name. There is no proper way to express this
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   455
using triplet, so you should use something like:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   456
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   457
.. sourcecode:: sql
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   458
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   459
    Any X WHERE X firstname XFN, Y firstname YFN, NOT X identity Y HAVING UPPER(XFN) = UPPER(YFN)
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   460
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   461
Another example: imagine you want person born in 2000:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   462
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   463
.. sourcecode:: sql
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   464
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   465
    Any X WHERE X birthday XB HAVING YEAR(XB) = 2000
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   466
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   467
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
   468
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
   469
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
   470
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   471
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   472
Sub-queries
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   473
```````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   474
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   475
The ``WITH`` keyword introduce sub-queries clause. Each sub-query has the
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   476
form:
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   477
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   478
  V1(,V2) BEING (rql query)
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   479
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   480
Variables at the left of the ``BEING`` keyword defines into which
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   481
variables results from the sub-query will be mapped to into the outer query.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   482
Sub-queries are separated from each other using a comma.
6120
c000e41316ec [book] some more documentation and cleanups
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5924
diff changeset
   483
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   484
Let's say we want to retrieve for each project its number of versions and its
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   485
number of tickets. Due to the nature of relational algebra behind the scene, this
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   486
can't be achieved using a single query. You have to write something along the
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   487
line of:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   488
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   489
.. sourcecode:: sql
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   490
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   491
  Any X, VC, TC WHERE X identity XX
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   492
  WITH X, VC BEING (Any X, COUNT(V) GROUPBY X WHERE V version_of X),
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   493
       XX, TC BEING (Any X, COUNT(T) GROUPBY X WHERE T ticket_of X)
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   494
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   495
Notice that we can't reuse a same variable name as alias for two different
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   496
sub-queries, hence the usage of 'X' and 'XX' in this example, which are then
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   497
unified using the special `identity` relation (see :ref:`VirtualRelations`).
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   498
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   499
.. warning::
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   500
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   501
  Sub-queries define a new variable scope, so even if a variable has the same name
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   502
  in the outer query and in the sub-query, they technically **aren't** the same
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   503
  variable. So:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   504
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   505
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   506
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   507
     Any W, REF WITH W, REF BEING
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   508
         (Any W, REF WHERE W is Workcase, W ref REF,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   509
                           W concerned_by D, D name "Logilab")
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   510
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   511
  could be written:
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   512
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   513
  .. sourcecode:: sql
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   514
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   515
     Any W, REF WITH W, REF BEING
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   516
        (Any W1, REF1 WHERE W1 is Workcase, W1 ref REF1,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   517
                            W1 concerned_by D, D name "Logilab")
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   518
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   519
  Also, when a variable is coming from a sub-query, you currently can't reference
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   520
  its attribute or inlined relations in the outer query, you've to fetch them in
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   521
  the sub-query. For instance, let's say we want to sort by project name in our
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   522
  first example, we would have to write:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   523
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   524
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   525
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   526
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   527
    Any X, VC, TC ORDERBY XN WHERE X identity XX
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   528
    WITH X, XN, VC BEING (Any X, COUNT(V) GROUPBY X,XN WHERE V version_of X, X name XN),
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   529
         XX, TC BEING (Any X, COUNT(T) GROUPBY X WHERE T ticket_of X)
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   530
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   531
  instead of:
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   532
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   533
  .. sourcecode:: sql
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   534
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   535
    Any X, VC, TC ORDERBY XN WHERE X identity XX, X name XN,
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   536
    WITH X, XN, VC BEING (Any X, COUNT(V) GROUPBY X WHERE V version_of X),
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   537
         XX, TC BEING (Any X, COUNT(T) GROUPBY X WHERE T ticket_of X)
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   538
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   539
  which would result in a SQL execution error.
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   540
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   541
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   542
Union
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   543
`````
7081
6beb7f1e05fa [doc] complete description of EXISTS in rql/language.rst
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 6324
diff changeset
   544
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   545
You may get a result set containing the concatenation of several queries using
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   546
the ``UNION``. The selection of each query should have the same number of
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   547
columns.
7081
6beb7f1e05fa [doc] complete description of EXISTS in rql/language.rst
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 6324
diff changeset
   548
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   549
.. sourcecode:: sql
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   550
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   551
    (Any X, XN WHERE X is Person, X surname XN) UNION (Any X,XN WHERE X is Company, X name XN)
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   552
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   553
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   554
.. _RQLFunctions:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   555
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   556
Available functions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   557
~~~~~~~~~~~~~~~~~~~
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   558
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   559
Below is the list of aggregate and transformation functions that are supported
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   560
nativly by the framework. Notice that cubes may define additional functions.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   561
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   562
.. _RQLAggregateFunctions:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   563
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   564
Aggregate functions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   565
```````````````````
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   566
+------------------------+----------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   567
| ``COUNT(Any)``         | return the number of rows                                |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   568
+------------------------+----------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   569
| ``MIN(Any)``           | return the minimum value                                 |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   570
+------------------------+----------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   571
| ``MAX(Any)``           | return the maximum value                                 |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   572
+------------------------+----------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   573
| ``AVG(Any)``           | return the average value                                 |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   574
+------------------------+----------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   575
| ``SUM(Any)``           | return the sum of values                                 |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   576
+------------------------+----------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   577
| ``COMMA_JOIN(String)`` | return each value separated by a comma (for string only) |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   578
+------------------------+----------------------------------------------------------+
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   579
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   580
All aggregate functions above take a single argument. Take care some aggregate
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   581
functions (e.g. ``MAX``, ``MIN``) may return `None` if there is no
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   582
result row.
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   583
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   584
.. _RQLStringFunctions:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   585
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   586
String transformation functions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   587
```````````````````````````````
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   588
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   589
+-----------------------------------------------+-----------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   590
| ``UPPER(String)``                             | upper case the string                                           |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   591
+-----------------------------------------------+-----------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   592
| ``LOWER(String)``                             | lower case the string                                           |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   593
+-----------------------------------------------+-----------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   594
| ``LENGTH(String)``                            | return the length of the string                                 |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   595
+-----------------------------------------------+-----------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   596
| ``SUBSTRING(String, start, length)``          | extract from the string a string starting at given index and of |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   597
|                                               | given length                                                    |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   598
+-----------------------------------------------+-----------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   599
| ``LIMIT_SIZE(String, max size)``              | if the length of the string is greater than given max size,     |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   600
|                                               | strip it and add ellipsis ("..."). The resulting string will    |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   601
|                                               | hence have max size + 3 characters                              |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   602
+-----------------------------------------------+-----------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   603
| ``TEXT_LIMIT_SIZE(String, format, max size)`` | similar to the above, but allow to specify the MIME type of the |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   604
|                                               | text contained by the string. Supported formats are text/html,  |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   605
|                                               | text/xhtml and text/xml. All others will be considered as plain |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   606
|                                               | text. For non plain text format, sgml tags will be first removed|
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   607
|                                               | before limiting the string.                                     |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   608
+-----------------------------------------------+-----------------------------------------------------------------+
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   609
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   610
.. _RQLDateFunctions:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   611
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   612
Date extraction functions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   613
`````````````````````````
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   614
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   615
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   616
| ``YEAR(Date)``       | return the year of a date or datetime  |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   617
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   618
| ``MONTH(Date)``      | return the month of a date or datetime |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   619
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   620
| ``DAY(Date)``        | return the day of a date or datetime   |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   621
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   622
| ``HOUR(Datetime)``   | return the hours of a datetime         |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   623
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   624
| ``MINUTE(Datetime)`` | return the minutes of a datetime       |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   625
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   626
| ``SECOND(Datetime)`` | return the seconds of a datetime       |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   627
+----------------------+----------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   628
| ``WEEKDAY(Date)``    | return the day of week of a date or    |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   629
|                      | datetime.  Sunday == 1, Saturday == 7. |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   630
+----------------------+----------------------------------------+
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   631
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   632
.. _RQLOtherFunctions:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   633
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   634
Other functions
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   635
```````````````
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   636
+-------------------+--------------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   637
| ``ABS(num)``      |  return the absolute value of a number                             |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   638
+-------------------+--------------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   639
| ``RANDOM()``      | return a pseudo-random value from 0.0 to 1.0                       |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   640
+-------------------+--------------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   641
| ``FSPATH(X)``     | expect X to be an attribute whose value is stored in a             |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   642
|                   | :class:`BFSStorage` and return its path on the file system         |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   643
+-------------------+--------------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   644
| ``FTIRANK(X)``    | expect X to be an entity used in a has_text relation, and return a |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   645
|                   | number corresponding to the rank order of each resulting entity    |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   646
+-------------------+--------------------------------------------------------------------+
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   647
| ``CAST(Type, X)`` | expect X to be an attribute and return it casted into the given    |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   648
|                   | final type                                                         |
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   649
+-------------------+--------------------------------------------------------------------+
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   650
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   651
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   652
.. _RQLExamples:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   653
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   654
Examples
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   655
~~~~~~~~
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   656
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   657
- *Search for the object of identifier 53*
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   658
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   659
  .. sourcecode:: sql
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   660
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   661
        Any X WHERE X eid 53
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   662
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   663
- *Search material such as comics, owned by syt and available*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   664
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   665
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   666
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   667
        Any X WHERE X is Document,
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   668
                    X occurence_of F, F class C, C name 'Comics',
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   669
                    X owned_by U, U login 'syt',
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   670
                    X available TRUE
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   671
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   672
- *Looking for people working for eurocopter interested in training*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   673
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   674
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   675
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   676
        Any P WHERE P is Person, P work_for S, S name 'Eurocopter',
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   677
                    P interested_by T, T name 'training'
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   678
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   679
- *Search note less than 10 days old written by jphc or ocy*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   680
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   681
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   682
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   683
        Any N WHERE N is Note, N written_on D, D day> (today -10),
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   684
                    N written_by P, P name 'jphc' or P name 'ocy'
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   685
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   686
- *Looking for people interested in training or living in Paris*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   687
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   688
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   689
9801
e0532c37c9c2 [book] fix various parts of the rql section
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 9330
diff changeset
   690
        Any P WHERE P is Person, EXISTS(P interested_by T, T name 'training') OR
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   691
                    (P city 'Paris')
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   692
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   693
- *The surname and firstname of all people*
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   694
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   695
  .. sourcecode:: sql
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   696
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   697
        Any N, P WHERE 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
   698
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   699
  Note that the selection of several entities generally force
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   700
  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
   701
  to all the selected variables. We could write here
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   702
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   703
  .. sourcecode:: sql
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   704
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   705
        String N, P WHERE X is Person, X name N, X first_name P
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   706
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   707
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   708
  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
   709
  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
   710
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   711
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   712
  .. sourcecode:: sql
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   713
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   714
        Any X WHERE X is IN (FirstType, SecondType)
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   715
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   716
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   717
.. _RQLInsertQuery:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   718
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   719
Insertion query
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   720
~~~~~~~~~~~~~~~
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   721
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   722
    `INSERT` <entity type> V1 (, <entity type> V2) \ * `:` <assignments>
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   723
    [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   724
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   725
:assignments:
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   726
   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
   727
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   728
The restriction can define variables used in assignments.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   729
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   730
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
   731
*each line result returned by the restriction*.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   732
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   733
- *Insert a new person named 'foo'*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   734
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   735
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   736
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   737
        INSERT Person X: X name 'foo'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   738
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   739
- *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
   740
  between them*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   741
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   742
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   743
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   744
        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
   745
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   746
- *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
   747
  person called 'nice'*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   748
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   749
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   750
9330
45e0034e2a73 [doc] Fix a sample request
Christophe de Vienne <cdevienne@gmail.com>
parents: 9313
diff changeset
   751
        INSERT Person X: X name 'foo', X friend  Y WHERE Y name 'nice'
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   752
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   753
.. _RQLSetQuery:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   754
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   755
Update and relation creation queries
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   756
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   757
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   758
    `SET` <assignements>
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   759
    [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   760
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   761
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
   762
each result line returned by the restriction*.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   763
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   764
- *Renaming of the person named 'foo' to 'bar' with the first name changed*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   765
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   766
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   767
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   768
        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
   769
2172
cf8f9180e63e delete-trailing-whitespace
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 2043
diff changeset
   770
- *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
   771
  the relation of type 'friend'*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   772
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   773
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   774
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   775
        SET X know Y  WHERE X friend Y
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   776
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   777
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   778
.. _RQLDeleteQuery:
7632
3c9dfc6e820b [book] fix some rest/sphinx errors
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7578
diff changeset
   779
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   780
Deletion query
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   781
~~~~~~~~~~~~~~
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   782
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   783
    `DELETE` (<entity type> V) | (V1 relation v2 ),...
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   784
    [ `WHERE` <restriction>]
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   785
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   786
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
   787
each line result returned by the restriction*.
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   788
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   789
- *Deletion of the person named 'foo'*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   790
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   791
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   792
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   793
        DELETE Person X WHERE X name 'foo'
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   794
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   795
- *Removal of all relations of type 'friend' from the person named 'foo'*
8032
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   796
bcb87336c7d2 [doc] fix most of ReST compilation errors and warnings
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 8017
diff changeset
   797
  .. sourcecode:: sql
1714
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   798
a721966779be new book layout, do not compile yet
sylvain.thenault@logilab.fr
parents:
diff changeset
   799
        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
   800
5924
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   801
b218df942dd4 [book] update rql documentation and some erroneous/deprecated examples
Julien Jehannet <julien.jehannet@logilab.fr>
parents: 5393
diff changeset
   802
.. _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
   803
.. _Left outer join: http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join
7578
49be8619b745 [book] rewrite / complete the RQL language chapter
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 7529
diff changeset
   804