-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlite_select.gbnf
184 lines (139 loc) · 6.67 KB
/
sqlite_select.gbnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
# A GBNF grammar to handle select statements in SQLite
# Most of the grammar here was created with the help of the SQLite documentation: https://www.sqlite.org/lang_select.html
# I did my best to make it complete, but given my unfamiliarity with some extended features of SQLite, it may not be perfect.
root ::= select-stmt (";" ws select-stmt)*
select-statement ::= with-clause? select-core (compound-operator ws select-core)* ordering-clause? limit-clause?
with-clause ::= "WITH " "RECURSIVE "? common-table-expression (", " common-table-expression)* ws
select-core ::= select-core-select | select-core-values
select-core-select ::= "SELECT " ("ALL " | "DISTINCT ")? result-column (", " result-column)* from-clause? where-clause? group-by-clause? window-clause?
select-core-values ::= "VALUES " "(" expr (", " expr)* ")"
from-clause ::= ws "FROM " (table-or-subquery (", " table-or-subquery)* | join-clause)
where-clause ::= ws "WHERE " expr
group-by-clause ::= ws "GROUP BY " expr (", " expr)* ("HAVING " expr)?
window-clause ::= ws "WINDOW " window-name "AS " window-defn (", " window-name "AS " window-defn)*
ordering-clause ::= ws "ORDER BY " ordering-term (", " ordering-term)*
limit-clause ::= ws "LIMIT " expr ("OFFSET " expr)?
result-column ::= "*" | table-name ".*" | expr ( "AS " column-alias )?
column-name-list ::= " (" column-name ("," column-name)* ")"
materialized-option ::= "NOT "? "MATERIALIZED "
table-or-subquery ::= (
(schema-name ".")? table-name as-alias? indexed-option?
| (schema-name ".")? table-function-name "(" expr ")" as-alias?
| "(" select-stmt ")" as-alias?
| "(" ((table-or-subquery (", " table-or-subquery)*) | join-clause) ")"
)
indexed-option ::= (
" INDEXED BY " index-name
| " NOT INDEXED"
)
as-alias ::= " AS "? table-alias
table-function-name ::= identifier
table-alias ::= identifier
join-clause ::= table-or-subquery (join-operator table-or-subquery join-constraint?)*
join-operator ::= (
","
| "CROSS "? "JOIN "
| "NATURAL "? ("INNER " | ("LEFT " | "RIGHT " | "FULL ") "OUTER "?) "JOIN "
)
join-constraint ::= (
" ON " expr
| " USING (" column-name ("," column-name)* ")"
)
window-name ::= identifier
window-defn ::= "(" base-window-name? window-partition-clause? order-by-clause? frame-spec? ")"
base-window-name ::= identifier
compound-operator ::= "UNION" | "UNION ALL" | "INTERSECT" | "EXCEPT"
# TODO: schema-name.table-name.column-name should be limited to the columns in the table
expr ::= (
literal-value
| bind-parameter
| (schema-name ".")? (table-name ".")? column-name
| unary-operator ws expr
| expr ws binary-operator ws expr
| function-name "(" function-arguments? ")" filter-clause? over-clause?
| "(" expr (", " expr)* ")"
| "CAST(" expr " AS " type-name ")"
| expr "COLLATE " collation-name
| expr "NOT "? "LIKE " expr (" ESCAPE " expr)?
| expr "NOT "? ("GLOB " | "REGEXP " | "MATCH ") expr
| expr "ISNULL"
| expr "NOTNULL"
| expr "NOT NULL"
| expr "IS " "NOT "? ("DISTINCT FROM ")? expr
| expr "NOT "? "BETWEEN " expr " AND " expr
| expr "NOT "? "IN (" (select-stmt | expr (", " expr)*) ")"
| expr "NOT "? "IN " (schema-name ".")? table-name
| expr "NOT "? "IN " (schema-name ".")? table-function "(" expr (", " expr)* ")"
| "EXISTS "? "(" select-stmt ")"
| "NOT EXISTS (" select-stmt ")"
| "CASE " expr? (" WHEN " expr " THEN " expr)+ (" ELSE " expr)? " END"
| raise-function
)
unary-operator ::= "-" | "+" | "~" | "NOT"
binary-operator ::= "+" | "-" | "*" | "/" | "%" | "<<" | ">>" | "&" | "|" | "<" | "<=" | ">" | ">=" | "=" | "==" | "!=" | "<>" | "IS" | "IS NOT" | "IN" | "LIKE" | "GLOB" | "MATCH" | "REGEXP"
function-name ::= identifier
function-arguments ::= (
"DISTINCT"? expr ("," expr)* ("ORDER" "BY" ordering-term ("," ordering-term)*)?
| "*"
)
ordering-term ::= expr ("COLLATE " collation-name)? (" ASC" | " DESC")? (" NULLS " ("FIRST" | "LAST"))?
filter-clause ::= " FILTER(WHERE " expr ")"
over-clause ::= " OVER " (window-name | "(" (partition-by-clause? order-by-clause? frame-spec?) ")")
partition-by-clause ::= "PARTITION BY " expr (", " expr)*
order-by-clause ::= " ORDER BY " ordering-term (", " ordering-term)*
frame-spec ::= ws frame-spec-type frame-content frame-exclusion?
frame-spec-type ::= "RANGE " | "ROWS " | "GROUPS "
frame-content ::= (
"BETWEEN " frame-bound "AND " frame-bound
| frame-bound
)
frame-bound ::= (
"CURRENT ROW"
| "UNBOUNDED" frame-bound-relative
| expr frame-bound-relative
)
frame-bound-relative ::= " PRECEDING" | " FOLLOWING"
frame-exclusion ::= (
" EXCLUDE NO OTHERS"
| " EXCLUDE CURRENT ROW"
| " EXCLUDE GROUP"
| " EXCLUDE TIES"
)
# NOTE: Because of type-affinity in SQLite, we only need to support a small set of types, and don't need to support numeric qualifiers (such as "VARCHAR(255)") on datatypes.
type-name ::= "TEXT" | "NUMERIC" | "INTEGER" | "REAL" | "BLOB"
collation-name ::= identifier
# TODO: Do we need to add blob-literals?
literal-value ::= numeric-literal | string-literal | "NULL" | "TRUE" | "FALSE" | "CURRENT_TIME" | "CURRENT_DATE" | "CURRENT_TIMESTAMP"
bind-parameter ::= "?" | "?" digit+ | ":" [a-zA-Z]+ | "@" [a-zA-Z]+ | "$" [a-zA-Z]+
base-identifier ::= [a-zA-Z_][a-zA-Z0-9_]*
quoted-identifier ::= "\"" [^"]* "\""
identifier ::= base-identifier | quoted-identifier
raise-function ::= "RAISE(" ("IGNORE" | (("ROLLBACK" | "ABORT" | "FAIL") ", " string-literal ) ) ")"
numeric-literal ::= (
digit+
| digit+ "." digit+
| "." digit+
| digit+ ("E" | "e") ("+" | "-")? digit+
| "0x" hexdigit+
)
# TODO: How to handle escaped quotes in string literals?
string-literal ::= "'" [^']* "'"
digit ::= [0-9]
hexdigit ::= [0-9a-fA-F]
letter ::= [A-Za-z]
ws ::= [ \t\n]+
# NOTE: The following will be replaced for custom-generated gbnf files that are tailored to a specific database.
#### BEGIN: DB DATA ####
# NOTE: common-table-expression should be re-written to be more specific to a user's particular database.
common-table-expression ::= table-name-and-columns "AS " materialized-option? "(" select-stmt ")"
# NOTE: table-name-and-columns should be re-written to be more specific to a user's particular database.
# NOTE: Should be a list of specific tables and columns
table-name-and-columns ::= table-name column-name-list?
# NOTE: table-name should be a list of all valid tables in the database
table-name ::= identifier
# NOTE: column-name should be a list of all valid columns in the database
column-name ::= identifier
# NOTE: schema-name should be a list of all valid schemas in the database
schema-name ::= identifier
# NOTE: index-name should be a list of all valid indexes in the database
index-name ::= identifier