5
{ @nonaggregates = () ; $tables = 0 ; $fields = 0 ; ""}
12
# will have series, film_crew, and genre
13
SELECT distinct straight_join select_option fbase_query1_select_list
14
FROM `fbase_film_series` AS table1 left_right_outer JOIN
15
`fbase_film_crew` AS table2
16
ON table1 . fbase_film_detail_id = table2 . fbase_film_detail_id
17
left_right_outer JOIN `fbase_film_genre` AS table3
18
ON table_one_two . fbase_film_detail_id = table3 . fbase_film_detail_id
19
WHERE ( fbase_film_series_where_list )
20
and_or ( fbase_film_crew_where_list )
21
and_or ( fbase_film_genre_where_list )
22
opt_fbase_query1_having_clause
23
ORDER BY total_order_by LIMIT limit_value ;
25
fbase_query1_select_list:
26
table1 . fbase_film_detail_id AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ,
27
fbase_role_code AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ,
28
genre_tag AS { my $f = "field".++$fields ; push @nonaggregates , $f ; $f } ;
30
fbase_select_disabled:
31
fbase_film_series_select_list |
32
fbase_film_genre_select_list |
33
fbase_film_crew_select_list ;
35
fbase_film_series_select_list:
38
opt_fbase_query1_having_clause:
39
| | | | | | fbase_query1_having_clause ;
41
fbase_query1_having_clause:
42
HAVING fbase_query1_having_list ;
44
fbase_query1_having_list:
45
fbase_film_series_where_list |
46
fbase_film_genre_where_list |
47
fbase_film_crew_where_list ;
49
fbase_film_series_where_list:
50
fbase_film_series_where_item |
51
fbase_film_series_where_item and_or fbase_film_series_where_list ;
53
fbase_film_series_where_item:
54
related_film_id comparison_operator char_value |
55
( related_film_id BETWEEN char_value AND char_value ) |
56
related_film_id comparison_operator table_one_two_three . fbase_film_detail_id |
57
sequel_flag comparison_operator true_false |
58
sequel_flag = zero_one ;
60
fbase_film_genre_where_list:
61
fbase_film_genre_where_item |
62
fbase_film_genre_where_item and_or fbase_film_series_where_list |
63
( fbase_film_genre_where_item and_or fbase_film_series_where_list ) |
64
( fbase_film_genere_where_item ) and_or fbase_film_series_where_list ;
66
fbase_film_genre_where_item:
67
genre_tag comparison_operator char_value |
68
( genre_tag BETWEEN char_value AND char_value ) |
69
genre_tag LIKE first_letter ;
70
genre_tag IN ( fbase_genre_list ) ;
73
fbase_genre_item | fbase_genre_item , fbase_genre_list ;
76
'Camp' | 'Comedy' | 'Crime' | 'Cyberpunk' |
77
'Horror' | 'History' | 'Martial arts' | 'Sports' ;
80
fbase_film_crew_where_list:
81
fbase_film_crew_where_item |
82
fbase_film_crew_where_item and_or fbase_film_series_where_list |
83
( fbase_film_crew_where_item and_or fbase_film_series_where_list ) |
84
( fbase_film_genere_where_item ) and_or fbase_film_series_where_list ;
86
fbase_film_crew_where_item:
87
fbase_role_code LIKE first_letter |
88
fbase_role_code comparison_operator char_value |
89
LENGTH( fbase_role_code ) comparison_operator numeric_value |
90
( fbase_role_code BETWEEN char_value AND char_value ) |
91
fbase_role_code IN ( fbase_role_list ) ;
94
fbase_role_item | fbase_role_item , fbase_role_list ;
97
'actor' | 'director' | 'producer' | 'writer' | 'editor' ;
105
##############################################################################
107
# rules that contain lists of possible values for a comparison, etc
108
##############################################################################
120
table1 | table2 | table3 ;
123
_year | 1900 | 1910 | 1920 | 1930 |
124
1940 | 1950 | 1960 | 1970 |
125
1975 | 1980 | 1985 | 1990 | 1995 ;
128
'A%' | 'B%' | 'C%' | 'D%' | 'E%' | 'F%' | 'G%' | 'H%' | 'I%' | 'J%' | 'K%' | 'L%' | 'M%' | 'N%' |
129
'P%' | 'Q%' | 'R%' | 'S%' | 'T%' | 'U%' | 'V%' | 'X%' | 'Y%' | 'Z%' ;
132
_char | _english | _english | _quid | _quid ;
138
1 | 1 | 2 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 ;
141
20 | 25 | 50 | 75 | 100 ;
144
small_increment | large_increment ;
147
1000 | 10000 | 5000 | 500 | 100 | 10 ;
152
##############################################################################
153
# general utility rules
154
##############################################################################
156
{ join(', ', map { "field".$_ } (1..$fields) ) } desc ;
159
{ scalar(@nonaggregates) > 0 ? " GROUP BY ".join (', ' , @nonaggregates ) : "" } ;
162
| | | | | | | | | | group_by_clause ;
166
MAX | MIN | AVG | SUM ;
169
MAX | MIN | SUM | COUNT ;
172
# NOTE: '=' is omitted here as we are using random
173
# comparison values that are impossible to
175
> | < | != | <> | <= | >= | greater_than | less_than ;
184
| | | | | | | LEFT outer | LEFT outer | RIGHT outer ;
187
| | | | LEFT | LEFT | LEFT | RIGHT ;
193
DISTINCT | | | | | | ;
196
| | | | | | | | | SQL_SMALL_RESULT ;
199
| | | | | | | | | | | STRAIGHT_JOIN ;