--- a/doc/book/en/annexes/rql/language.rst Thu Oct 27 18:19:00 2011 +0200
+++ b/doc/book/en/annexes/rql/language.rst Fri Oct 28 10:32:48 2011 +0200
@@ -55,6 +55,8 @@
**is** in the restrictions.
+.. _VirtualRelations:
+
Virtual relations
~~~~~~~~~~~~~~~~~
@@ -66,7 +68,7 @@
* `identity`: relation to use to tell that a RQL variable is the same as another
when you've to use two different variables for querying purpose. On the
- opposite it's also useful together with the :ref:`NOT` operator to tell that two
+ opposite it's also useful together with the ``NOT`` operator to tell that two
variables should not identify the same entity
@@ -83,12 +85,12 @@
* floats separator is dot '.'
-* boolean values are :keyword:`TRUE` and :keyword:`FALSE` keywords
+* boolean values are ``TRUE`` and ``FALSE`` keywords
* date and time should be expressed as a string with ISO notation : YYYY/MM/DD
- [hh:mm], or using keywords :keyword:`TODAY` and :keyword:`NOW`
+ [hh:mm], or using keywords ``TODAY`` and ``NOW``
-You may also use the :keyword:`NULL` keyword, meaning 'unspecified'.
+You may also use the ``NULL`` keyword, meaning 'unspecified'.
.. _RQLOperators:
@@ -167,7 +169,9 @@
`VARIABLE attribute VALUE`
-The operator `IN` provides a list of possible values: ::
+The operator `IN` provides a list of possible values:
+
+.. sourcecode:: sql
Any X WHERE X name IN ('chauvat', 'fayolle', 'di mascio', 'thenault')
@@ -180,28 +184,32 @@
LIKE, ILIKE, ~=, REGEXP
-The :keyword:`LIKE` string operator can be used with the special character `%` in
-a string as wild-card: ::
+The ``LIKE`` string operator can be used with the special character `%` in
+a string as wild-card:
+
+.. sourcecode:: sql
- # match every entity whose name starts with 'Th'
+ -- match every entity whose name starts with 'Th'
Any X WHERE X name ~= 'Th%'
- # match every entity whose name endswith 'lt'
+ -- match every entity whose name endswith 'lt'
Any X WHERE X name LIKE '%lt'
- # match every entity whose name contains a 'l' and a 't'
+ -- match every entity whose name contains a 'l' and a 't'
Any X WHERE X name LIKE '%l%t%'
-:keyword:`ILIKE` is the case insensitive version of :keyword:`LIKE`. It's not
+``ILIKE`` is the case insensitive version of ``LIKE``. It's not
available on all backend (e.g. sqlite doesn't support it). If not available for
-your backend, :keyword:`ILIKE` will behave like :keyword:`LIKE`.
+your backend, ``ILIKE`` will behave like ``LIKE``.
-`~=` is a shortcut version of :keyword:`ILIKE`, or of :keyword:`LIKE` when the
+`~=` is a shortcut version of ``ILIKE``, or of ``LIKE`` when the
former is not available on the back-end.
-The :keyword:`REGEXP` is an alternative to :keyword:`LIKE` that supports POSIX
-regular expressions::
+The ``REGEXP`` is an alternative to ``LIKE`` that supports POSIX
+regular expressions:
- # match entities whose title starts with a digit
+.. sourcecode:: sql
+
+ -- match entities whose title starts with a digit
Any X WHERE X title REGEXP "^[0-9].*"
@@ -253,7 +261,9 @@
There will be as much column in the result set as term in this clause, respecting
order.
-Syntax for function call is somewhat intuitive, for instance: ::
+Syntax for function call is somewhat intuitive, for instance:
+
+.. sourcecode:: sql
Any UPPER(N) WHERE P firstname N
@@ -261,28 +271,28 @@
Grouping and aggregating
````````````````````````
-The :keyword:`GROUPBY` keyword is followed by a list of terms on which results
+The ``GROUPBY`` keyword is followed by a list of terms on which results
should be grouped. They are usually used with aggregate functions, responsible to
aggregate values for each group (see :ref:`RQLAggregateFunctions`).
For grouped queries, all selected variables must be either aggregated (i.e. used
-by an aggregate function) or grouped (i.e. listed in the :keyword:`GROUPBY`
+by an aggregate function) or grouped (i.e. listed in the ``GROUPBY``
clause).
Sorting
```````
-The :keyword:`ORDERBY` keyword if followed by the definition of the selection
-order: variable or column number followed by sorting method (:keyword:`ASC`,
-:keyword:`DESC`), :keyword:`ASC` being the default. If the sorting method is not
+The ``ORDERBY`` keyword if followed by the definition of the selection
+order: variable or column number followed by sorting method (``ASC``,
+``DESC``), ``ASC`` being the default. If the sorting method is not
specified, then the sorting is ascendant (`ASC`).
Pagination
``````````
-The :keyword:`LIMIT` and :keyword:`OFFSET` keywords may be respectively used to
+The ``LIMIT`` and ``OFFSET`` keywords may be respectively used to
limit the number of results and to tell from which result line to start (for
instance, use `LIMIT 20` to get the first 20 results, then `LIMIT 20 OFFSET 20`
to get the next 20.
@@ -291,34 +301,34 @@
Restrictions
````````````
-The :keyword:`WHERE` keyword introduce one of the "main" part of the query, where
+The ``WHERE`` keyword introduce one of the "main" part of the query, where
you "define" variables and add some restrictions telling what you're interested
in.
It's a list of triplets "subject relation object", e.g. `V1 relation
(V2 | <static value>)`. Triplets are separated using :ref:`RQLLogicalOperators`.
-.. Note:
+.. note::
- About the negation operator (:keyword:`NOT`):
+ About the negation operator (``NOT``):
- * "NOT X relation Y" is equivalent to "NOT EXISTS(X relation Y)"
+ * ``NOT X relation Y`` is equivalent to ``NOT EXISTS(X relation Y)``
- * `Any X WHERE NOT X owned_by U` means "entities that have no relation
- `owned_by`".
+ * ``Any X WHERE NOT X owned_by U`` means "entities that have no relation
+ ``owned_by``".
- * `Any X WHERE NOT X owned_by U, U login "syt"` means "the entity have no
- relation `owned_by` with the user syt". They may have a relation "owned_by"
+ * ``Any X WHERE NOT X owned_by U, U login "syt"`` means "the entity have no
+ relation ``owned_by`` with the user syt". They may have a relation "owned_by"
with another user.
-In this clause, you can also use :keyword:`EXISTS` when you want to know if some
+In this clause, you can also use ``EXISTS`` when you want to know if some
expression is true and do not need the complete set of elements that make it
true. Testing for existence is much faster than fetching the complete set of
-results, especially when you think about using `OR` against several expressions. For instance
+results, especially when you think about using ``OR`` against several expressions. For instance
if you want to retrieve versions which are in state "ready" or tagged by
"priority", you should write :
-::
+.. sourcecode:: sql
Any X ORDERBY PN,N
WHERE X num N, X version_of P, P name PN,
@@ -327,7 +337,7 @@
not
-::
+.. sourcecode:: sql
Any X ORDERBY PN,N
WHERE X num N, X version_of P, P name PN,
@@ -356,7 +366,9 @@
You must use the `?` behind a variable to specify that the relation toward it
is optional. For instance:
-- Bugs of a project attached or not to a version ::
+- Bugs of a project attached or not to a version
+
+ .. sourcecode:: sql
Any X, V WHERE X concerns P, P eid 42, X corrected_in V?
@@ -364,26 +376,34 @@
version in which it's corrected or None for tickets not related to a version.
-- All cards and the project they document if any ::
+- All cards and the project they document if any
+
+ .. sourcecode:: sql
Any C, P WHERE C is Card, P? documented_by C
Notice you may also use outer join:
-- on the RHS of attribute relation, e.g. ::
+- on the RHS of attribute relation, e.g.
+
+ .. sourcecode:: sql
Any X WHERE X ref XR, Y name XR?
so that Y is outer joined on X by ref/name attributes comparison
-- on any side of an `HAVING` expression, e.g. ::
+- on any side of an ``HAVING`` expression, e.g.
+
+ .. sourcecode:: sql
Any X WHERE X creation_date XC, Y creation_date YC
HAVING YEAR(XC)=YEAR(YC)?
so that Y is outer joined on X by comparison of the year extracted from their
- creation date. ::
+ creation date.
+
+ .. sourcecode:: sql
Any X WHERE X creation_date XC, Y creation_date YC
HAVING YEAR(XC)?=YEAR(YC)
@@ -394,20 +414,26 @@
Having restrictions
```````````````````
-The :keyword:`HAVING` clause, as in SQL, may be used to restrict a query
-according to value returned by an aggregate function, e.g.::
+The ``HAVING`` clause, as in SQL, may be used to restrict a query
+according to value returned by an aggregate function, e.g.
+
+.. sourcecode:: sql
Any X GROUPBY X WHERE X relation Y HAVING COUNT(Y) > 10
-It may however be used for something else: In the :keyword:`WHERE` clause, we are
+It may however be used for something else: In the ``WHERE`` clause, we are
limited to triplet expressions, so some things may not be expressed there. Let's
take an example : if you want to get people whose upper-cased first name equals to
another person upper-cased first name. There is no proper way to express this
-using triplet, so you should use something like: ::
+using triplet, so you should use something like:
+
+.. sourcecode:: sql
Any X WHERE X firstname XFN, Y firstname YFN, NOT X identity Y HAVING UPPER(XFN) = UPPER(YFN)
-Another example: imagine you want person born in 2000: ::
+Another example: imagine you want person born in 2000:
+
+.. sourcecode:: sql
Any X WHERE X birthday XB HAVING YEAR(XB) = 2000
@@ -419,19 +445,21 @@
Sub-queries
```````````
-The :keyword:`WITH` keyword introduce sub-queries clause. Each sub-query has the
+The ``WITH`` keyword introduce sub-queries clause. Each sub-query has the
form:
V1(,V2) BEING (rql query)
-Variables at the left of the :keyword:`BEING` keyword defines into which
+Variables at the left of the ``BEING`` keyword defines into which
variables results from the sub-query will be mapped to into the outer query.
Sub-queries are separated from each other using a comma.
Let's say we want to retrieve for each project its number of versions and its
number of tickets. Due to the nature of relational algebra behind the scene, this
can't be achieved using a single query. You have to write something along the
-line of: ::
+line of:
+
+.. sourcecode:: sql
Any X, VC, TC WHERE X identity XX
WITH X, VC BEING (Any X, COUNT(V) GROUPBY X WHERE V version_of X),
@@ -439,19 +467,24 @@
Notice that we can't reuse a same variable name as alias for two different
sub-queries, hence the usage of 'X' and 'XX' in this example, which are then
-unified using the special `identity` relation (see :ref:`XXX`).
+unified using the special `identity` relation (see :ref:`VirtualRelations`).
-.. Warning:
+.. warning::
Sub-queries define a new variable scope, so even if a variable has the same name
- in the outer query and in the sub-query, they technically **aren't* the same
- variable. So ::
+ in the outer query and in the sub-query, they technically **aren't** the same
+ variable. So:
+
+ .. sourcecode:: sql
Any W, REF WITH W, REF BEING
(Any W, REF WHERE W is Workcase, W ref REF,
W concerned_by D, D name "Logilab")
+
could be written:
+ .. sourcecode:: sql
+
Any W, REF WITH W, REF BEING
(Any W1, REF1 WHERE W1 is Workcase, W1 ref REF1,
W1 concerned_by D, D name "Logilab")
@@ -459,14 +492,18 @@
Also, when a variable is coming from a sub-query, you currently can't reference
its attribute or inlined relations in the outer query, you've to fetch them in
the sub-query. For instance, let's say we want to sort by project name in our
- first example, we would have to write ::
+ first example, we would have to write:
+
+ .. sourcecode:: sql
Any X, VC, TC ORDERBY XN WHERE X identity XX
WITH X, XN, VC BEING (Any X, COUNT(V) GROUPBY X,XN WHERE V version_of X, X name XN),
XX, TC BEING (Any X, COUNT(T) GROUPBY X WHERE T ticket_of X)
- instead of ::
+ instead of:
+
+ .. sourcecode:: sql
Any X, VC, TC ORDERBY XN WHERE X identity XX, X name XN,
WITH X, XN, VC BEING (Any X, COUNT(V) GROUPBY X WHERE V version_of X),
@@ -479,10 +516,10 @@
`````
You may get a result set containing the concatenation of several queries using
-the :keyword:`UNION`. The selection of each query should have the same number of
+the ``UNION``. The selection of each query should have the same number of
columns.
-::
+.. sourcecode:: sql
(Any X, XN WHERE X is Person, X surname XN) UNION (Any X,XN WHERE X is Company, X name XN)
@@ -514,7 +551,7 @@
+--------------------+----------------------------------------------------------+
All aggregate functions above take a single argument. Take care some aggregate
-functions (e.g. :keyword:`MAX`, :keyword:`MIN`) may return `None` if there is no
+functions (e.g. ``MAX``, ``MIN``) may return `None` if there is no
result row.
.. _RQLStringFunctions:
@@ -522,26 +559,26 @@
String transformation functions
```````````````````````````````
-+-------------------------+-----------------------------------------------------------------+
-| :func:`UPPER(String)` | upper case the string |
-+-------------------------+-----------------------------------------------------------------+
-| :func:`LOWER(String)` | lower case the string |
-+-------------------------+-----------------------------------------------------------------+
-| :func:`LENGTH(String)` | return the length of the string |
-+-------------------------+-----------------------------------------------------------------+
-| :func:`SUBSTRING( | extract from the string a string starting at given index and of |
-| String, start, length)`| given length |
-+-------------------------+-----------------------------------------------------------------+
-| :func:`LIMIT_SIZE( | if the length of the string is greater than given max size, |
-| String, max size)` | strip it and add ellipsis ("..."). The resulting string will |
-| | hence have max size + 3 characters |
-+-------------------------+-----------------------------------------------------------------+
-| :func:`TEXT_LIMIT_SIZE( | similar to the above, but allow to specify the MIME type of the |
-| String, format, | text contained by the string. Supported formats are text/html, |
-| max size)` | text/xhtml and text/xml. All others will be considered as plain |
-| | text. For non plain text format, sgml tags will be first removed|
-| | before limiting the string. |
-+-------------------------+-----------------------------------------------------------------+
++---------------------------------------------------+-----------------------------------------------------------------+
+| :func:`UPPER(String)` | upper case the string |
++---------------------------------------------------+-----------------------------------------------------------------+
+| :func:`LOWER(String)` | lower case the string |
++---------------------------------------------------+-----------------------------------------------------------------+
+| :func:`LENGTH(String)` | return the length of the string |
++---------------------------------------------------+-----------------------------------------------------------------+
+| :func:`SUBSTRING(String, start, length)` | extract from the string a string starting at given index and of |
+| | given length |
++---------------------------------------------------+-----------------------------------------------------------------+
+| :func:`LIMIT_SIZE(String, max size)` | if the length of the string is greater than given max size, |
+| | strip it and add ellipsis ("..."). The resulting string will |
+| | hence have max size + 3 characters |
++---------------------------------------------------+-----------------------------------------------------------------+
+| :func:`TEXT_LIMIT_SIZE(String, format, max size)` | similar to the above, but allow to specify the MIME type of the |
+| | text contained by the string. Supported formats are text/html, |
+| | text/xhtml and text/xml. All others will be considered as plain |
+| | text. For non plain text format, sgml tags will be first removed|
+| | before limiting the string. |
++---------------------------------------------------+-----------------------------------------------------------------+
.. _RQLDateFunctions:
@@ -588,61 +625,63 @@
~~~~~~~~
- *Search for the object of identifier 53*
- ::
- Any WHERE X
- X eid 53
+ .. sourcecode:: sql
+
+ Any WHERE X eid 53
- *Search material such as comics, owned by syt and available*
- ::
+
+ .. sourcecode:: sql
- Any X WHERE X is Document
- X occurence_of F, F class C, C name 'Comics'
- X owned_by U, U login 'syt'
- X available TRUE
+ Any X WHERE X is Document,
+ X occurence_of F, F class C, C name 'Comics',
+ X owned_by U, U login 'syt',
+ X available TRUE
- *Looking for people working for eurocopter interested in training*
- ::
+
+ .. sourcecode:: sql
- Any P WHERE
- P is Person, P work_for S, S name 'Eurocopter'
- P interested_by T, T name 'training'
+ Any P WHERE P is Person, P work_for S, S name 'Eurocopter',
+ P interested_by T, T name 'training'
- *Search note less than 10 days old written by jphc or ocy*
- ::
+
+ .. sourcecode:: sql
- Any N WHERE
- N is Note, N written_on D, D day> (today -10),
- N written_by P, P name 'jphc' or P name 'ocy'
+ Any N WHERE N is Note, N written_on D, D day> (today -10),
+ N written_by P, P name 'jphc' or P name 'ocy'
- *Looking for people interested in training or living in Paris*
- ::
+
+ .. sourcecode:: sql
- Any P WHERE
- P is Person, (P interested_by T, T name 'training') OR
- (P city 'Paris')
+ Any P WHERE P is Person, (P interested_by T, T name 'training') OR
+ (P city 'Paris')
- *The surname and firstname of all people*
- ::
- Any N, P WHERE
- X is Person, X name N, X firstname P
+ .. sourcecode:: sql
+
+ Any N, P WHERE X is Person, X name N, X firstname P
Note that the selection of several entities generally force
the use of "Any" because the type specification applies otherwise
to all the selected variables. We could write here
- ::
- String N, P WHERE
- X is Person, X name N, X first_name P
+ .. sourcecode:: sql
+
+ String N, P WHERE X is Person, X name N, X first_name P
Note: You can not specify several types with * ... where X is FirstType or X is SecondType*.
To specify several types explicitly, you have to do
- ::
- Any X where X is in (FirstType, SecondType)
+ .. sourcecode:: sql
+
+ Any X WHERE X is IN (FirstType, SecondType)
.. _RQLInsertQuery:
@@ -662,19 +701,22 @@
*each line result returned by the restriction*.
- *Insert a new person named 'foo'*
- ::
+
+ .. sourcecode:: sql
INSERT Person X: X name 'foo'
- *Insert a new person named 'foo', another called 'nice' and a 'friend' relation
between them*
- ::
+
+ .. sourcecode:: sql
INSERT Person X, Person Y: X name 'foo', Y name 'nice', X friend Y
- *Insert a new person named 'foo' and a 'friend' relation with an existing
person called 'nice'*
- ::
+
+ .. sourcecode:: sql
INSERT Person X: X name 'foo', X friend Y WHERE name 'nice'
@@ -690,13 +732,15 @@
each result line returned by the restriction*.
- *Renaming of the person named 'foo' to 'bar' with the first name changed*
- ::
+
+ .. sourcecode:: sql
SET X name 'bar', X firstname 'original' WHERE X is Person, X name 'foo'
- *Insert a relation of type 'know' between objects linked by
the relation of type 'friend'*
- ::
+
+ .. sourcecode:: sql
SET X know Y WHERE X friend Y
@@ -713,12 +757,14 @@
each line result returned by the restriction*.
- *Deletion of the person named 'foo'*
- ::
+
+ .. sourcecode:: sql
DELETE Person X WHERE X name 'foo'
- *Removal of all relations of type 'friend' from the person named 'foo'*
- ::
+
+ .. sourcecode:: sql
DELETE X friend Y WHERE X is Person, X name 'foo'