5
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; ""}
8
##############################################################################
9
# mlens_rules: rules related to the mlens-sourced tables
10
##############################################################################
16
mlens_movies_with_tag | mlens_movies_with_tag | mlens_movie_genre ;
18
mlens_movies_with_tag:
19
{ $mlens_movie_alias='mwt_t1'; $mlens_tag_alias='mwt_t2'; "" }
20
SELECT distinct straight_join select_option mlens_movie_select_list opt_mlens_tag_select_list
21
FROM mlens_movie AS mwt_t1 left_right_outer JOIN mlens_tag AS mwt_t2
22
ON { $mlens_movie_alias } . `id` = { $mlens_tag_alias } . `mlens_movie_id`
23
WHERE { $mlens_tag_alias} . `tag` IS not NULL
24
AND ( mlens_movie_where_list ) AND ( mlens_tag_where_list )
26
ORDER BY total_order_by LIMIT limit_value ;
29
mlens_movie_genre1 | mlens_movie_genre2 |
30
mlens_movie_genre3 | mlens_movie_genre3 | mlens_movie_genre3 ;
33
{ $mlens_movie_alias='mmg_t1'; $mlens_genre_alias='mmg_t2'; $mlens_rating_alias='mmg_t3'; "" }
34
SELECT distinct straight_join select_option
35
mlens_movie_select_list opt_mlens_genre_select_list opt_mlens_rating_select_list
36
FROM mlens_movie AS mmg_t1
37
left_right_outer JOIN mlens_genre AS mmg_t2
38
ON { $mlens_movie_alias } . `id` = { $mlens_genre_alias} . `mlens_movie_id`
39
left_right_outer JOIN mlens_rating AS mmg_t3
40
ON { $mlens_movie_alias } . `id` = { $mlens_rating_alias} . `mlens_movie_id`
41
WHERE { $mlens_genre_alias } . `genre` IN ( mlens_genre_list )
42
AND ( mlens_movie_where_list )
43
AND ( mlens_rating_where_list )
44
opt_mlens_genre_where_list
46
ORDER BY total_order_by LIMIT limit_value ;
49
{ $mlens_movie_alias='mmg_t1'; $mlens_genre_alias='mmg_t2'; $mlens_rating_alias='mmg_t3'; "" }
50
SELECT distinct straight_join select_option
51
mlens_movie_select_list opt_mlens_genre_select_list opt_mlens_rating_select_list , aggregate( `rating` )
52
FROM mlens_movie AS mmg_t1
53
left_right_outer JOIN mlens_genre AS mmg_t2
54
ON { $mlens_movie_alias } . `id` = { $mlens_genre_alias} . `mlens_movie_id`
55
left_right_outer JOIN mlens_rating AS mmg_t3
56
ON { $mlens_movie_alias } . `id` = { $mlens_rating_alias} . `mlens_movie_id`
57
WHERE { $mlens_genre_alias } . `genre` IN ( mlens_genre_list )
58
AND ( mlens_movie_where_list )
59
AND ( mlens_rating_where_list )
60
opt_mlens_genre_where_list
62
ORDER BY total_order_by LIMIT limit_value ;
65
{ $mlens_movie_alias='mmg_t1'; $mlens_genre_alias='mmg_t2'; $mlens_rating_alias='mmg_t3'; "" }
66
SELECT distinct straight_join select_option mlens_movie_select_list opt_mlens_genre_select_list , COUNT( `genre` )
67
FROM mlens_movie AS mmg_t1
68
left_right_outer JOIN mlens_genre AS mmg_t2
69
ON { $mlens_movie_alias } . `id` = { $mlens_genre_alias} . `mlens_movie_id`
70
WHERE { $mlens_genre_alias } . `genre` IN ( mlens_genre_list )
71
AND ( mlens_movie_where_list )
72
AND ( mlens_genre_where_list )
74
ORDER BY total_order_by LIMIT limit_value ;
77
##############################################################################
79
##############################################################################
81
mlens_movie_select_list:
82
mlens_movie_select_item | mlens_movie_select_item | mlens_movie_select_item, mlens_movie_select_list ;
84
mlens_movie_select_item:
85
{ $mlens_movie_alias } . mlens_movie_field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
88
`id` | `title` | `year` | `id` | `title` | `year` |
91
opt_mlens_movie_where_list:
92
| and_or mlens_movie_where_list ;
94
mlens_movie_where_list:
95
std_mlens_movie_where_list | std_mlens_movie_where_list | spec_mlens_movie_where_list ;
97
std_mlens_movie_where_list:
98
mlens_movie_where_item | std_mlens_movie_where_list and_or mlens_movie_where_item ;
100
spec_mlens_movie_where_list:
101
mlens_movie_sort_union_where_list ;
103
mlens_movie_sort_union_where_list:
104
mlens_movie_where_item | mlens_movie_sort_union_where_list OR mlens_movie_where_item ;
106
mlens_movie_where_item:
107
{ $mlens_movie_alias } . mlens_movie_text_field not LIKE first_letter |
108
{ $mlens_movie_alias } . mlens_movie_text_field comparison_operator char_value |
109
{ $mlens_movie_alias } . mlens_movie_text_field comparison_operator char_value |
110
{ $mlens_movie_alias } . mlens_movie_text_field comparison_operator char_value |
111
{ $mlens_movie_alias } . mlens_movie_text_field comparison_operator char_value |
112
{ $mlens_movie_alias } . `year` comparison_operator year_value |
113
{ $mlens_movie_alias } . `year` comparison_operator year_value |
114
{ $mlens_movie_alias } . `year` comparison_operator year_value |
115
( { $mlens_movie_alias } . `year` greater_than _year[invariant] AND { $mlens_movie_alias } . `year` less_than (_year[invariant] + small_increment) ) |
116
{ $mlens_movie_alias } . mlens_movie_text_field IS not NULL |
117
{ $mlens_movie_alias } . mlens_movie_text_field IS not NULL |
118
{ $mlens_movie_alias } . mlens_movie_text_field IS not NULL |
119
LENGTH( { $mlens_movie_alias } . mlens_movie_text_field ) comparison_operator mlens_movie_text_length_sub ;
121
mlens_movie_text_field:
122
`title` | `alternate_title` ;
124
##############################################################################
126
##############################################################################
128
opt_mlens_tag_select_list:
129
| | | | | , mlens_tag_select_list ;
131
mlens_tag_select_list:
132
mlens_tag_select_item | mlens_tag_select_item | mlens_tag_select_item, mlens_tag_select_list ;
134
mlens_tag_select_item:
135
{ $mlens_tag_alias } . mlens_tag_field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
138
`mlens_movie_id` | `user_id` | `tag` |
139
`mlens_movie_id` | `user_id` | `tag` | `timestamp` ;
141
opt_mlens_tag_where_list:
142
| and_or mlens_tag_where_list ;
144
mlens_tag_where_list:
145
std_mlens_tag_where_list | std_mlens_tag_where_list | spec_mlens_tag_where_list ;
147
std_mlens_tag_where_list:
148
mlens_tag_where_item | std_mlens_tag_where_list and_or mlens_tag_where_item ;
150
spec_mlens_tag_where_list:
151
mlens_tag_sort_union_where_list ;
153
mlens_tag_sort_union_where_list:
154
mlens_tag_where_item | mlens_tag_sort_union_where_list OR mlens_tag_where_item ;
156
mlens_tag_where_item:
157
{ $mlens_tag_alias } . `tag` IS not NULL |
158
{ $mlens_tag_alias } . `tag` IS not NULL |
159
{ $mlens_tag_alias } . `tag` IS not NULL |
160
{ $mlens_tag_alias } . `tag` comparison_operator char_value |
161
{ $mlens_tag_alias } . `tag` comparison_operator char_value |
162
{ $mlens_tag_alias } . `tag` comparison_operator char_value |
163
{ $mlens_tag_alias } . `tag` not LIKE first_letter |
164
{ $mlens_tag_alias } . `timestamp` comparison_operator _timestamp |
165
{ $mlens_tag_alias } . `user_id` comparison_operator _smallint_unsigned |
166
{ $mlens_tag_alias } . `timestamp` comparison_operator _timestamp |
167
{ $mlens_tag_alias } . `user_id` comparison_operator _smallint_unsigned |
168
( { $mlens_tag_alias } . `user_id` BETWEEN _smallint_unsigned[invariant] AND (_smallint_unsigned[invariant]+ increment) )|
169
LENGTH( { $mlens_tag_alias } . `tag` ) comparison_operator mlens_tag_text_length_sub ;
171
mlens_tag_text_field:
174
##############################################################################
176
##############################################################################
177
opt_mlens_genre_select_list:
178
| | | | | , mlens_genre_select_list ;
180
mlens_genre_select_list:
181
mlens_genre_select_item | mlens_genre_select_item | mlens_genre_select_item, mlens_genre_select_list ;
183
mlens_genre_select_item:
184
{ $mlens_genre_alias } . mlens_genre_field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
187
`mlens_movie_id` | `genre` ;
190
mlens_genre_type | mlens_genre_list , mlens_genre_type ;
193
'Action' | 'Adventure' | 'Animation' | 'Children' | 'Comedy' | 'Crime' |
194
'Documentary' | 'Drama' | 'Fantasy' | 'Film-Noir' | 'Horror' | 'Musical' |
195
'Mystery' | 'Romance' | 'Sci-Fi' | 'Thriller' | 'War' | 'Western' ;
197
opt_mlens_genre_where_list:
198
| and_or mlens_genre_where_list ;
200
mlens_genre_where_list:
201
std_mlens_genre_where_list | std_mlens_genre_where_list | spec_mlens_genre_where_list ;
203
std_mlens_genre_where_list:
204
mlens_genre_where_item | std_mlens_genre_where_list and_or mlens_genre_where_item ;
206
spec_mlens_genre_where_list:
207
mlens_genre_sort_union_where_list ;
209
mlens_genre_sort_union_where_list:
210
mlens_genre_where_item | mlens_genre_sort_union_where_list OR mlens_genre_where_item ;
212
mlens_genre_where_item:
213
{ $mlens_genre_alias } . `genre` comparison_operator mlens_genre_type |
214
{ $mlens_genre_alias } . `genre` comparison_operator char_value |
215
{ $mlens_genre_alias } . `genre` BETWEEN char_value AND char_value |
216
{ $mlens_genre_alias } . `genre` comparison_operator mlens_genre_type |
217
{ $mlens_genre_alias } . `genre` comparison_operator char_value |
218
{ $mlens_genre_alias } . `genre` BETWEEN char_value AND char_value |
219
{ $mlens_genre_alias } . `genre` comparison_operator mlens_genre_type |
220
{ $mlens_genre_alias } . `genre` comparison_operator char_value |
221
{ $mlens_genre_alias } . `genre` LIKE first_letter ;
223
##############################################################################
225
##############################################################################
226
opt_mlens_rating_select_list:
227
| | | | | , mlens_rating_select_list ;
229
mlens_rating_select_list:
230
mlens_rating_select_item | mlens_rating_select_item | mlens_rating_select_item, mlens_rating_select_list ;
232
mlens_rating_select_item:
233
{ $mlens_rating_alias } . mlens_rating_field AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
236
`mlens_movie_id` | `user_id` | `rating` | `timestamp` ;
239
opt_mlens_rating_where_list:
240
| and_or mlens_rating_where_list ;
242
mlens_rating_where_list:
243
mlens_rating_where_item | mlens_rating_where_list and_or mlens_rating_where_item ;
245
mlens_rating_where_item:
246
{ $mlens_rating_alias } . `rating` comparison_operator mlens_rating_value |
247
{ $mlens_rating_alias } . `rating` IN ( mlens_rating_list ) |
248
( { $mlens_rating_alias } . `rating` greater_than mlens_rating_low AND { $mlens_rating_alias } . `rating` less_than mlens_rating_high ) ;
249
{ $mlens_rating_alias } . `rating` comparison_operator mlens_rating_value |
250
{ $mlens_rating_alias } . `rating` IN ( mlens_rating_list ) |
251
{ $mlens_rating_alias } . `rating` comparison_operator mlens_rating_value |
252
{ $mlens_rating_alias } . `rating` IN ( mlens_rating_list ) |
253
{ $mlens_rating_alias } . `timestamp` comparison_operator _timestamp |
254
{ $mlens_rating_alias } . `user_id` comparison_operator _smallint_unsigned |
255
{ $mlens_rating_alias } . `user_id` BETWEEN _smallint_unsigned[invariant] AND (_smallint_unsigned[invariant]+ increment) ;
258
mlens_rating_value | mlens_rating_list , mlens_rating_value ;
261
0 | 0.5 | 1.0 | 1.5 | 2.0 | 2.5 |
262
3.0 | 3.5 | 4.0 | 4.5 | 5.0 ;
265
0 | 0.5 | 1.0 | 1.5 ;
269
##############################################################################
270
# fbase film rules: rules related to fbase-sourced queries
271
##############################################################################
276
SELECT fbase_film_detail.id, fbase_film_detail.name
277
FROM fbase_film_series AS fbs_t1 { $fbase_film_series_alias='fbs_t1' ; "" }
278
JOIN fbase_film_detail AS fbs_t2 { $fbase_film_detail_alias='fbs_t2' ; "" }
279
ON fbase_film_series . fbase_film_detail_id = fbase_film_detail . id
280
JOIN mlens_movie_with_rating_parm_sub AS mlens_derived_t1
281
ON fbase_film_detail . name = mlens_derived_t1 . title
282
WHERE fbase_film_series_where_list and_or fbase_film_detail_where_list
283
ORDER BY 1 LIMIT limit_value ;
285
##############################################################################
287
##############################################################################
288
opt_fbase_film_series_where_list:
289
| and_or fbase_film_series_where_list ;
291
fbase_film_series_where_list:
292
std_fbase_film_series_where_list | std_fbase_film_series_where_list | spec_fbase_film_series_where_list ;
294
std_fbase_film_series_where_list:
295
fbase_film_series_where_item | fbase_series_where_item |
296
std_fbase_film_series_where_list and_or fbase_film_series_where_item ;
298
spec_fbase_film_series_where_list:
299
fbase_film_series_sort_union_where_list ;
301
fbase_film_series_sort_union_where_list:
302
fbase_film_series_where_item | fbase_film_series_where_item | fbase_film_series_sort_union_where_list OR fbase_film_series_where_item ;
304
fbase_film_series_where_item:
305
{ $fbase_film_series_alias } . sequel_flag = zero_one ;
307
##############################################################################
308
# fbase_film_detail rules
309
##############################################################################
310
opt_fbase_film_detail_where_list:
311
| and_or fbase_film_detail_where_list ;
313
fbase_film_detail_where_list:
314
std_fbase_film_detail_where_list | std_fbase_film_detail_where_list | spec_fbase_film_detail_where_list ;
316
std_fbase_film_detail_where_list:
317
fbase_film_detail_where_item | fbase_series_where_item |
318
std_fbase_film_detail_where_list and_or fbase_film_detail_where_item ;
320
spec_fbase_film_detail_where_list:
321
fbase_film_detail_sort_union_where_list ;
323
fbase_film_detail_sort_union_where_list:
324
fbase_film_detail_where_item | fbase_film_detail_where_item |
325
fbase_film_detail_sort_union_where_list OR fbase_film_detail_where_item ;
327
fbase_film_detail_where_item:
328
{ $fbase_film_detail_alias } . init_release_date comparison_operator _datetime |
329
{ $fbase_film_detail_alias } . est_budget_id IS not NULL |
330
{ $fbase_film_detail_alias } . name comparison_operator char_value ;
334
##############################################################################
336
##############################################################################
337
# value subqueries: return a value that is used in the main query's WHERE clause
339
mlens_movie_text_length_sub:
340
# rule for selecting the MIN/MAX/AVG length of a text field from mlens_movie
341
( SELECT size_aggregate( distinct LENGTH( mlens_movie_text_field ) ) FROM mlens_movie ) ;
343
mlens_tag_text_length_sub:
344
( SELECT size_aggregate( distinct LENGTH( mlens_tag_text_field ) ) FROM mlens_tag ) ;
347
mlens_movie_with_rating_parm_sub:
348
# rule for selecting those movies that have enough ratings of a certain condition
349
( SELECT distinct straight_join select_option mlens_movie.id, mlens_movie.title
350
FROM mlens_movie { $mlens_movie_alias='mlens_movie'; "" } left_right_outer JOIN
351
( SELECT mlens_movie_id, COUNT(rating) FROM mlens_rating
352
WHERE mlens_rating_where_list
353
GROUP BY mlens_movie_id
354
HAVING COUNT(rating) > numeric_value ) AS mlens_rating_t1
355
ON id = mlens_movie_id ) ;
358
##############################################################################
360
# rules that contain lists of possible values for a comparison, etc
361
##############################################################################
364
_year | 1900 | 1910 | 1920 | 1930 |
365
1940 | 1950 | 1960 | 1970 |
366
1975 | 1980 | 1985 | 1990 | 1995 ;
369
'A%' | 'B%' | 'C%' | 'D%' | 'E%' | 'F%' | 'G%' | 'H%' | 'I%' | 'J%' | 'K%' | 'L%' | 'M%' | 'N%' |
370
'P%' | 'Q%' | 'R%' | 'S%' | 'T%' | 'U%' | 'V%' | 'X%' | 'Y%' | 'Z%' ;
373
_char | _english | _english | _quid | _quid ;
379
1 | 1 | 2 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 ;
382
20 | 25 | 50 | 75 | 100 ;
385
small_increment | large_increment ;
388
1000 | 10000 | 5000 | 500 | 100 | 10 ;
393
##############################################################################
394
# general utility rules
395
##############################################################################
397
{ join(', ', map { "field".$_ } (1..$fields) ) } desc ;
400
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
403
| | | | | | | | | | group_by_clause ;
407
MAX | MIN | AVG | SUM ;
410
MAX | MIN | SUM | COUNT ;
413
# NOTE: '=' is omitted here as we are using random
414
# comparison values that are impossible to
416
> | < | != | <> | <= | >= | greater_than | less_than ;
425
| | | | | | | LEFT outer | LEFT outer | RIGHT outer ;
428
| | | | LEFT | LEFT | LEFT | RIGHT ;
434
DISTINCT | | | | | | ;
437
| | | | | | | | | SQL_SMALL_RESULT ;
440
| | | | | | | | | | | STRAIGHT_JOIN ;