|
1 .. -*- coding: utf-8 -*- |
|
2 |
|
3 Let's learn RQL by practice... |
|
4 |
|
5 .. contents:: |
|
6 |
|
7 Introduction |
|
8 ------------ |
|
9 |
|
10 RQL has a syntax and principle inspirated from the SQL query language, though |
|
11 it's at a higher level in order to be more intuitive and suitable to easily make |
|
12 advanced queries on a schema based database. |
|
13 |
|
14 * the main idea in RQL is that we'are following relations between entities |
|
15 * attributes are a special case of relations |
|
16 * RQL has been inspirated from SQL but is at a higher level |
|
17 * a knowledge of the application'schema is necessary to build rql queries |
|
18 |
|
19 To use RQL, you'll have to know the basis of the language as well as a good |
|
20 knowledge of the application'schema. You can always view it using the "schema" |
|
21 link in user's dropdown menu (on the top-right of the screen) or by clicking here_. |
|
22 |
|
23 .. _here: ../schema |
|
24 |
|
25 |
|
26 Some bits of theory |
|
27 ------------------- |
|
28 |
|
29 Variables et types |
|
30 ~~~~~~~~~~~~~~~~~~ |
|
31 Entities and attributes'value to follow and / or select are represented by the |
|
32 query by *variables* which must be written upper-case. |
|
33 |
|
34 Possible types for each variable are deducted from the schema according to |
|
35 constraints in the query. |
|
36 |
|
37 You can explicitly constrain a variable's type using the special relation "is". |
|
38 |
|
39 Base types |
|
40 ~~~~~~~~~~ |
|
41 * `String` (literal: between double or simple quotes) |
|
42 * `Int`, `Float` (using '.' as separator) |
|
43 * `Date`, `Datetime`, `Time` (literal: string YYYY/MM/DD[ hh:mm] or |
|
44 `TODAY` and `NOW` keywords) |
|
45 * `Boolean` (keywords `TRUE` et `FALSE`) |
|
46 * keyword `NULL` |
|
47 |
|
48 Opérateurs |
|
49 ~~~~~~~~~~ |
|
50 * Logical operators : `AND`, `OR`, `,` |
|
51 * Mathematical operators: `+`, `-`, `*`, `/` |
|
52 * Comparisons operators: `=`, `<`, `<=`, `>=`, `>`, `~=`, `LIKE`, `IN` |
|
53 |
|
54 * `=` is the default comparison operator |
|
55 |
|
56 * `LIKE` / `~=` permits use of the special character `%` in a string to tell |
|
57 the string must begin or end with a prefix or suffix (as SQL LIKE operator) :: |
|
58 |
|
59 Any X WHERE X name ~= 'Th%' |
|
60 Any X WHERE X name LIKE '%lt' |
|
61 |
|
62 * `IN` permits to give a list of possible values :: |
|
63 |
|
64 Any X WHERE X name IN ('chauvat', 'fayolle', 'di mascio', 'thenault') |
|
65 |
|
66 Grammaire des requêtes de recherche |
|
67 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
|
68 :: |
|
69 |
|
70 [DISTINCT] <entity type> V1(, V2)* |
|
71 [GROUPBY V1(, V2)*] [ORDERBY <orderterms>] |
|
72 [WHERE <restriction>] |
|
73 [LIMIT <value>] [OFFSET <value>] |
|
74 |
|
75 :entity type: |
|
76 Type of the selected variable(s). You'll usually use `Any` type to not specify |
|
77 any type. |
|
78 :restriction: |
|
79 List of relations to follow, in the form |
|
80 `V1 relation V2|<constant value>` |
|
81 :orderterms: |
|
82 Define a selection order : variable or column number, followed by the sort method |
|
83 (`ASC`, `DESC`), with ASC as default when not specified |
|
84 |
|
85 notice about grouped query (e.g using a `GROUPBY` clause): every selected |
|
86 variable should be either grouped or used in an aggregat function. |
|
87 |
|
88 |
|
89 Example schema |
|
90 -------------- |
|
91 |
|
92 In this document, we will suppose the application's schema is the one described |
|
93 here. Available entity types are : |
|
94 |
|
95 :Person: |
|
96 :: |
|
97 |
|
98 name (String, required) |
|
99 birthday (Date) |
|
100 |
|
101 |
|
102 :Company: |
|
103 :: |
|
104 |
|
105 name (String) |
|
106 |
|
107 |
|
108 :Note: |
|
109 :: |
|
110 |
|
111 diem (Date) |
|
112 type (String) |
|
113 |
|
114 |
|
115 And relations between those entities: :: |
|
116 |
|
117 Person works_for Company |
|
118 Person evaluated_by Note |
|
119 Company evaluated_by Note |
|
120 |
|
121 |
|
122 Meta-data |
|
123 ~~~~~~~~~ |
|
124 Every entities'type have the following meta-data: |
|
125 |
|
126 * `eid (Int)`, a unique identifier |
|
127 * `creation_date (Datetime)`, date on which the entity has been created |
|
128 * `modification_date (Datetime)`, lastest date on which the entity has been modified |
|
129 |
|
130 * `created_by (CWUser)`, relation to the user which has created this entity |
|
131 |
|
132 * `owned_by (CWUser)`, relation to the user()s considered as owner of this |
|
133 entity, the entity's creator by default |
|
134 |
|
135 * `is (Eetype)`, special relation to specify a variable type. |
|
136 |
|
137 A user's entity has the following schema: |
|
138 |
|
139 :CWUser: |
|
140 :: |
|
141 |
|
142 login (String) not null |
|
143 password (Password) |
|
144 firstname (String) |
|
145 surname (String) |
|
146 |
|
147 |
|
148 Basis queries |
|
149 ------------- |
|
150 0. *Every persons* :: |
|
151 |
|
152 Person X |
|
153 |
|
154 or :: |
|
155 |
|
156 Any X WHERE X is Person |
|
157 |
|
158 |
|
159 1. *The company named Logilab* :: |
|
160 |
|
161 Company S WHERE S name 'Logilab' |
|
162 |
|
163 |
|
164 2. *Every entities with a "name" attribute whose value starts with 'Log'* :: |
|
165 |
|
166 Any S WHERE S name LIKE 'Log%' |
|
167 |
|
168 or :: |
|
169 |
|
170 Any S WHERE S name ~= 'Log%' |
|
171 |
|
172 This query may return Person or Company entities. |
|
173 |
|
174 |
|
175 3. *Every persons working for the Logilab company* :: |
|
176 |
|
177 Person P WHERE P works_for S, S name "Logilab" |
|
178 |
|
179 or :: |
|
180 |
|
181 Person P WHERE P works_for S AND S name "Logilab" |
|
182 |
|
183 |
|
184 4. *Company named Caesium ou Logilab* :: |
|
185 |
|
186 Company S WHERE S name IN ('Logilab','Caesium') |
|
187 |
|
188 or :: |
|
189 |
|
190 Company S WHERE S name 'Logilab' OR S name 'Caesium' |
|
191 |
|
192 |
|
193 5. *Every company but ones named Caesium ou Logilab* :: |
|
194 |
|
195 Company S WHERE NOT S name IN ('Logilab','Caesium') |
|
196 |
|
197 or :: |
|
198 |
|
199 Company S WHERE NOT S name 'Logilab' AND NOT S name 'Caesium' |
|
200 |
|
201 |
|
202 6. *Entities evaluated by the note with eid 43* :: |
|
203 |
|
204 Any X WHERE X evaluated_by N, N eid 43 |
|
205 |
|
206 |
|
207 7. *Every persons order by birthday from the youngest to the oldest* :: |
|
208 |
|
209 Person X ORDERBY D DESC WHERE X birthday D |
|
210 |
|
211 Notice you've to define a variable using the birthday relation to use it in the |
|
212 sort term. |
|
213 |
|
214 |
|
215 8. *Number of persons working for each known company* :: |
|
216 |
|
217 Any S, COUNT(X) GROUPBY S WHERE X works_for S |
|
218 |
|
219 Notice you've that since you're writing a grouped query on S, X have to be |
|
220 either grouped as well or used in an aggregat function (as in this example). |
|
221 |
|
222 |
|
223 |
|
224 Advanced |
|
225 -------- |
|
226 0. *Person with no name specified (i.e NULL)* :: |
|
227 |
|
228 Person P WHERE P name NULL |
|
229 |
|
230 |
|
231 1. *Person which are not working for any company* :: |
|
232 |
|
233 Person P WHERE NOT p works_for S |
|
234 |
|
235 |
|
236 2. *Every company where person named toto isn't working* :: |
|
237 |
|
238 Company S WHERE NOT P works_for S , P name 'toto' |
|
239 |
|
240 |
|
241 3. *Every entity which have been modified between today and yesterday* :: |
|
242 |
|
243 Any X WHERE X modification_date <= TODAY, X modification_date >= TODAY - 1 |
|
244 |
|
245 |
|
246 4. *Every note without type, to be done in the next 7 days, ordered by date* :: |
|
247 |
|
248 Any N, D where N is Note, N type NULL, N diem D, N diem >= TODAY, |
|
249 N diem < today + 7 ORDERBY D |
|
250 |
|
251 |
|
252 5. *Person with an homonym (without duplicate)* :: |
|
253 |
|
254 DISTINCT Person X,Y where X name NX, Y name NX |
|
255 |
|
256 or even better (e.g. without both (Xeid, Yeid) and (Yeid, Xeid) in the results) :: |
|
257 |
|
258 Person X,Y where X name NX, Y name NX, X eid XE, Y eid > XE |