ABNF for SQLite 3.28 SQL

sqlite.abnf

; FIXME: The grammar has been transformed so that `w` appears after a
; token, but there is no way in ABNF to define it as token-separator
; that can optionally contain a mix of comments and white-space. Take
; `;;` as an example, for that to match `sql-stmt-list` `w` would
; have to match the empty string. But if `w` matches the empty string
; then `ISNOT` is the same as `IS NOT`.

sql-stmt-list = [ sql-stmt ] *( ";" w [ sql-stmt ] )
sql-stmt = [ "EXPLAIN" w [ "QUERY" w "PLAN" w ] ] ( alter-table-stmt / analyze-stmt / attach-stmt / begin-stmt / commit-stmt / create-index-stmt / create-table-stmt / create-trigger-stmt / create-view-stmt / create-virtual-table-stmt / delete-stmt / delete-stmt-limited / detach-stmt / drop-index-stmt / drop-table-stmt / drop-trigger-stmt / drop-view-stmt / insert-stmt / pragma-stmt / reindex-stmt / release-stmt / rollback-stmt / savepoint-stmt / select-stmt / update-stmt / update-stmt-limited / vacuum-stmt )
alter-table-stmt = "ALTER" w "TABLE" w [ schema-name w "." w ] table-name w ( "RENAME" w "TO" w new-table-name w / "RENAME" w [ "COLUMN" w ] column-name w "TO" w new-column-name w / "ADD" w [ "COLUMN" w ] column-def )
analyze-stmt = "ANALYZE" w [ schema-name w / table-or-index-name w / schema-name w "." w table-or-index-name w ]
attach-stmt = "ATTACH" w [ "DATABASE" w ] expr "AS" w schema-name w 
begin-stmt = "BEGIN" w [ "DEFERRED" w / "IMMEDIATE" w / "EXCLUSIVE" w ] [ "TRANSACTION" w ]
commit-stmt = ( "COMMIT" w / "END" w ) [ "TRANSACTION" w ]
rollback-stmt = "ROLLBACK" w [ "TRANSACTION" w ] [ "TO" w [ "SAVEPOINT" w ] savepoint-name w ]
savepoint-stmt = "SAVEPOINT" w savepoint-name w 
release-stmt = "RELEASE" w [ "SAVEPOINT" w ] savepoint-name w 
create-index-stmt = "CREATE" w [ "UNIQUE" w ] "INDEX" w [ "IF" w "NOT" w "EXISTS" w ] [ schema-name w "." w ] index-name w "ON" w table-name w "(" w indexed-column *( "," w indexed-column ) ")" w [ "WHERE" w expr ]
indexed-column = ( column-name w / expr ) [ "COLLATE" w collation-name w ] [ "ASC" w / "DESC" w ]
create-table-stmt = "CREATE" w [ "TEMP" w / "TEMPORARY" w ] "TABLE" w [ "IF" w "NOT" w "EXISTS" w ] [ schema-name w "." w ] table-name w ( "(" w column-def *( "," w column-def ) *( "," w table-constraint ) ")" w [ "WITHOUT" w "ROWID" w ] / "AS" w select-stmt )
column-def = column-name w [ type-name ] *( column-constraint )
type-name = name w [ "(" w signed-number ")" w / "(" w signed-number "," w signed-number ")" w ]
column-constraint = [ "CONSTRAINT" w name w ] ( "PRIMARY" w "KEY" w [ "ASC" w / "DESC" w ] conflict-clause [ "AUTOINCREMENT" w ] / "NOT" w "NULL" w conflict-clause / "UNIQUE" w conflict-clause / "CHECK" w "(" w expr ")" w / "DEFAULT" w ( signed-number / literal-value / "(" w expr ")" w ) / "COLLATE" w collation-name w / foreign-key-clause )
signed-number = [ "+" w / "-" w ] numeric-literal
table-constraint = [ "CONSTRAINT" w name w ] ( ( "PRIMARY" w "KEY" w / "UNIQUE" w ) "(" w indexed-column *( "," w indexed-column ) ")" w conflict-clause / "CHECK" w "(" w expr ")" w / "FOREIGN" w "KEY" w "(" w column-name w *( "," w column-name w ) ")" w foreign-key-clause )
foreign-key-clause = "REFERENCES" w foreign-table w [ "(" w column-name w *( "," w column-name w ) ")" w ] [ ( "ON" w ( "DELETE" w / "UPDATE" w ) ( "SET" w "NULL" w / "SET" w "DEFAULT" w / "CASCADE" w / "RESTRICT" w / "NO" w "ACTION" w ) / "MATCH" w name w ) ] [ [ "NOT" w ] "DEFERRABLE" w [ "INITIALLY" w "DEFERRED" w / "INITIALLY" w "IMMEDIATE" w ] ]
conflict-clause = [ "ON" w "CONFLICT" w ( "ROLLBACK" w / "ABORT" w / "FAIL" w / "IGNORE" w / "REPLACE" w ) ]
create-trigger-stmt = "CREATE" w [ "TEMP" w / "TEMPORARY" w ] "TRIGGER" w [ "IF" w "NOT" w "EXISTS" w ] [ schema-name w "." w ] trigger-name w [ "BEFORE" w / "AFTER" w / "INSTEAD" w "OF" w ] ( "DELETE" w / "INSERT" w / "UPDATE" w [ "OF" w column-name w *( "," w column-name w ) ] ) "ON" w table-name w [ "FOR" w "EACH" w "ROW" w ] [ "WHEN" w expr ] "BEGIN" w ( update-stmt / insert-stmt / delete-stmt / select-stmt ) ";" w "END" w
create-view-stmt = "CREATE" w [ "TEMP" w / "TEMPORARY" w ] "VIEW" w [ "IF" w "NOT" w "EXISTS" w ] [ schema-name w "." w ] view-name w [ "(" w column-name w *( "," w column-name w ) ")" w ] "AS" w select-stmt
create-virtual-table-stmt = "CREATE" w "VIRTUAL" w "TABLE" w [ "IF" w "NOT" w "EXISTS" w ] [ schema-name w "." w ] table-name w "USING" w module-name w [ "(" w module-argument w *( "," w module-argument w ) ")" w ]
with-clause = "WITH" w [ "RECURSIVE" w ] cte-table-name "AS" w "(" w select-stmt ")" w *( "," w cte-table-name "AS" w "(" w select-stmt ")" w )
cte-table-name = table-name w [ "(" w column-name w *( "," w column-name w ) ")" w ]
common-table-expression = table-name w [ "(" w column-name w *( "," w column-name w ) ")" w ] "AS" w "(" w select-stmt ")" w
delete-stmt = [ with-clause ] "DELETE" w "FROM" w qualified-table-name [ "WHERE" w expr ]
delete-stmt-limited = [ with-clause ] "DELETE" w "FROM" w qualified-table-name [ "WHERE" w expr ] [ [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] "LIMIT" w expr [ ( "OFFSET" w / "," w ) expr ] ]
detach-stmt = "DETACH" w [ "DATABASE" w ] schema-name w 
drop-index-stmt = "DROP" w "INDEX" w [ "IF" w "EXISTS" w ] [ schema-name w "." w ] index-name w 
drop-table-stmt = "DROP" w "TABLE" w [ "IF" w "EXISTS" w ] [ schema-name w "." w ] table-name w 
drop-trigger-stmt = "DROP" w "TRIGGER" w [ "IF" w "EXISTS" w ] [ schema-name w "." w ] trigger-name w 
drop-view-stmt = "DROP" w "VIEW" w [ "IF" w "EXISTS" w ] [ schema-name w "." w ] view-name w 

; FIXME: bogus when binary-operator includes IS NOT and such
expr = ( literal-value 
       / bind-parameter w 
       / [ [ schema-name w "." w ] table-name w "." w ] column-name w 
       / unary-operator w expr
       / expr binary-operator w expr
       / function-name w "(" w [ [ "DISTINCT" w ] expr *( "," w expr ) / "*" w ] ")" w
       / "(" w expr *( "," w expr ) ")" w
       / "CAST" w "(" w expr "AS" w type-name ")" w
       / expr "COLLATE" w collation-name w 
       / expr [ "NOT" w ] ( "LIKE" w / "GLOB" w / "REGEXP" w / "MATCH" w ) expr [ "ESCAPE" w expr ]
       / expr ( "ISNULL" w / "NOTNULL" w / "NOT" w "NULL" w )
       / expr "IS" w [ "NOT" w ] expr
       / expr [ "NOT" w ] "BETWEEN" w expr "AND" w expr
       / expr [ "NOT" w ] "IN" w ( "(" w [ select-stmt / expr *( "," w expr ) ] ")" w / [ schema-name w "." w ] table-name w / [ schema-name w "." w ] table-function w "(" w [ expr *( "," w expr ) ] ")" w )
       / [ [ "NOT" w ] "EXISTS" w ] "(" w select-stmt ")" w
       / "CASE" w [ expr ] "WHEN" w expr "THEN" w expr [ "ELSE" w expr ] "END" w
       / raise-function
       / window-func w "(" w [ expr *( "," w expr ) / "*" w ] ")" w [ filter ] "OVER" w ( window-defn / window-name w ) )

raise-function = "RAISE" w "(" w ( "IGNORE" w / ( "ROLLBACK" w / "ABORT" w / "FAIL" w ) "," w error-message w ) ")" w
literal-value = ( numeric-literal
                / string-literal w 
                / blob-literal w 
                / "NULL" w
                / "TRUE" w
                / "FALSE" w
                / "CURRENT-TIME" w
                / "CURRENT-DATE" w
                / "CURRENT-TIMESTAMP" w )
numeric-literal = ( ( digit w [ decimal-point w *( digit w ) ] / decimal-point w digit ) [ "E" w [ "+" w / "-" w ] digit w ]
                  / "0x" w hexdigit w )
insert-stmt = [ with-clause ] ( "INSERT" w / "REPLACE" w / "INSERT" w "OR" w "REPLACE" w / "INSERT" w "OR" w "ROLLBACK" w / "INSERT" w "OR" w "ABORT" w / "INSERT" w "OR" w "FAIL" w / "INSERT" w "OR" w "IGNORE" w ) "INTO" w [ schema-name w "." w ] table-name w [ "AS" w alias w ] [ "(" w column-name w *( "," w column-name w ) ")" w ] ( "VALUES" w "(" w expr *( "," w expr ) ")" w *( "," w "(" w expr *( "," w expr ) ")" w ) / select-stmt / "DEFAULT" w "VALUES" w ) [ upsert-clause ]
upsert-clause = "ON" w "CONFLICT" w [ "(" w indexed-column *( "," w indexed-column ) ")" w [ "WHERE" w expr ] ] "DO" w ( "NOTHING" w / "UPDATE" w "SET" w ( column-name w / column-name-list ) "=" w expr *( "," w ( column-name w / column-name-list ) "=" w expr ) [ "WHERE" w expr ] )
pragma-stmt = "PRAGMA" w [ schema-name w "." w ] pragma-name w [ "=" w pragma-value / "(" w pragma-value ")" w ]
pragma-value = ( signed-number
               / name w 
               / string-literal w )
reindex-stmt = "REINDEX" w [ collation-name w / [ schema-name w "." w ] ( table-name w / index-name w ) ]
select-stmt = [ "WITH" w [ "RECURSIVE" w ] common-table-expression *( "," w common-table-expression ) ] ( "SELECT" w [ "DISTINCT" w / "ALL" w ] result-column *( "," w result-column ) [ "FROM" w ( table-or-subquery *( "," w table-or-subquery ) / join-clause ) ] [ "WHERE" w expr ] [ "GROUP" w "BY" w expr *( "," w expr ) [ "HAVING" w expr ] ] [ "WINDOW" w window-name w "AS" w window-defn *( "," w window-name w "AS" w window-defn ) ] / "VALUES" w "(" w expr *( "," w expr ) ")" w *( "," w "(" w expr *( "," w expr ) ")" w ) ) *( compound-operator ( "SELECT" w [ "DISTINCT" w / "ALL" w ] result-column *( "," w result-column ) [ "FROM" w ( table-or-subquery *( "," w table-or-subquery ) / join-clause ) ] [ "WHERE" w expr ] [ "GROUP" w "BY" w expr *( "," w expr ) [ "HAVING" w expr ] ] [ "WINDOW" w window-name w "AS" w window-defn *( "," w window-name w "AS" w window-defn ) ] / "VALUES" w "(" w expr *( "," w expr ) ")" w *( "," w "(" w expr *( "," w expr ) ")" w ) ) ) [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] [ "LIMIT" w expr [ ( "OFFSET" w / "," w ) expr ] ]
join-clause = table-or-subquery [ join-operator table-or-subquery join-constraint ]
select-core = ( "SELECT" w [ "DISTINCT" w / "ALL" w ] result-column *( "," w result-column ) [ "FROM" w ( table-or-subquery *( "," w table-or-subquery ) / join-clause ) ] [ "WHERE" w expr ] [ "GROUP" w "BY" w expr *( "," w expr ) [ "HAVING" w expr ] ] [ "WINDOW" w window-name w "AS" w window-defn *( "," w window-name w "AS" w window-defn ) ]
              / "VALUES" w "(" w expr *( "," w expr ) ")" w *( "," w "(" w expr *( "," w expr ) ")" w ) )
factored-select-stmt = [ "WITH" w [ "RECURSIVE" w ] common-table-expression *( "," w common-table-expression ) ] select-core *( compound-operator select-core ) [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] [ "LIMIT" w expr [ ( "OFFSET" w / "," w ) expr ] ]
simple-select-stmt = [ "WITH" w [ "RECURSIVE" w ] common-table-expression *( "," w common-table-expression ) ] select-core [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] [ "LIMIT" w expr [ ( "OFFSET" w / "," w ) expr ] ]
compound-select-stmt = [ "WITH" w [ "RECURSIVE" w ] common-table-expression *( "," w common-table-expression ) ] select-core ( "UNION" w / "UNION" w "ALL" w / "INTERSECT" w / "EXCEPT" w ) select-core [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] [ "LIMIT" w expr [ ( "OFFSET" w / "," w ) expr ] ]
table-or-subquery = ( [ schema-name w "." w ] table-name w [ [ "AS" w ] table-alias w ] [ "INDEXED" w "BY" w index-name w / "NOT" w "INDEXED" w ]
                    / [ schema-name w "." w ] table-function-name w "(" w [ expr *( "," w expr ) ] ")" w [ [ "AS" w ] table-alias w ]
                    / "(" w ( table-or-subquery *( "," w table-or-subquery ) / join-clause ) ")" w
                    / "(" w select-stmt ")" w [ [ "AS" w ] table-alias w ] )
result-column = ( expr [ [ "AS" w ] column-alias w ]
                / "*" w
                / table-name w "." w "*" w )
join-operator = ( "," w
                / [ "NATURAL" w ] [ "LEFT" w [ "OUTER" w ] / "INNER" w / "CROSS" w ] "JOIN" w )
join-constraint = [ "ON" w expr / "USING" w "(" w column-name w *( "," w column-name w ) ")" w ]
ordering-term = expr [ "COLLATE" w collation-name w ] [ "ASC" w / "DESC" w ]
compound-operator = ( "UNION" w
                    / "UNION" w "ALL" w
                    / "INTERSECT" w
                    / "EXCEPT" w )
update-stmt = [ with-clause ] "UPDATE" w [ "OR" w "ROLLBACK" w / "OR" w "ABORT" w / "OR" w "REPLACE" w / "OR" w "FAIL" w / "OR" w "IGNORE" w ] qualified-table-name "SET" w ( column-name w / column-name-list ) "=" w expr *( "," w ( column-name w / column-name-list ) "=" w expr ) [ "WHERE" w expr ]
column-name-list = "(" w column-name w *( "," w column-name w ) ")" w
update-stmt-limited = [ with-clause ] "UPDATE" w [ "OR" w "ROLLBACK" w / "OR" w "ABORT" w / "OR" w "REPLACE" w / "OR" w "FAIL" w / "OR" w "IGNORE" w ] qualified-table-name "SET" w ( column-name w / column-name-list ) "=" w expr *( "," w ( column-name w / column-name-list ) "=" w expr ) [ "WHERE" w expr ] [ [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] "LIMIT" w expr [ ( "OFFSET" w / "," w ) expr ] ]
qualified-table-name = [ schema-name w "." w ] table-name w [ "AS" w alias w ] [ "INDEXED" w "BY" w index-name w / "NOT" w "INDEXED" w ]
vacuum-stmt = "VACUUM" w [ schema-name w ] [ "INTO" w filename w ]
filter = "FILTER" w "(" w "WHERE" w expr ")" w
window-defn = "(" w [ base-window-name w ] [ "PARTITION" w "BY" w expr *( "," w expr ) ] [ "ORDER" w "BY" w ordering-term *( "," w ordering-term ) ] [ frame-spec ] ")" w
frame-spec = ( "RANGE" w / "ROWS" w / "GROUPS" w ) ( "BETWEEN" w ( "UNBOUNDED" w "PRECEDING" w / expr "PRECEDING" w / "CURRENT" w "ROW" w / expr "FOLLOWING" w ) "AND" w ( expr "PRECEDING" w / "CURRENT" w "ROW" w / expr "FOLLOWING" w / "UNBOUNDED" w "FOLLOWING" w ) / ( "UNBOUNDED" w "PRECEDING" w / expr "PRECEDING" w / "CURRENT" w "ROW" w ) ) [ "EXCLUDE" w "NO" w "OTHERS" w / "EXCLUDE" w "CURRENT" w "ROW" w / "EXCLUDE" w "GROUP" w / "EXCLUDE" w "TIES" w ]
function-invocation = function-name w "(" w [ [ "DISTINCT" w ] expr *( "," w expr ) / "*" w ] ")" w
window-function-invocation = window-func w "(" w [ expr *( "," w expr ) / "*" w ] ")" w [ filter ] "OVER" w ( window-defn / window-name w )

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; hand-written token defintions
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

alias = name
base-window-name = window-name
collation-name = name
column-alias = alias
column-name = name
foreign-table = table-name
error-message = string-literal
function-name = name
index-name = name
module-name = name
new-column-name = column-name
new-table-name = table-name
pragma-name = name
savepoint-name = name
schema-name = name
table-alias = alias
table-function = table-function-name
table-function-name = name
table-name = name
table-or-index-name = table-name / index-name
trigger-name = name
view-name = name
window-func = function-name
window-name = name

string-literal = "'" *("''" / %x00-26 / %x28-ff) "'"

w = 1*(%x0a / %x0d / %x20 / %x09) ; FIXME: comment or white space
newline = %x0d / %x0a
name = 1*ident ; FIXME: quoted name

ident = %x24 / %x30 / %x31 / %x32 / %x33 / %x34 / %x35 / %x36 /
        %x37 / %x38 / %x39 / %x41 / %x42 / %x43 / %x44 / %x45 /
        %x46 / %x47 / %x48 / %x49 / %x4a / %x4b / %x4c / %x4d /
        %x4e / %x4f / %x50 / %x51 / %x52 / %x53 / %x54 / %x55 /
        %x56 / %x57 / %x58 / %x59 / %x5a / %x5f / %x61 / %x62 /
        %x63 / %x64 / %x65 / %x66 / %x67 / %x68 / %x69 / %x6a /
        %x6b / %x6c / %x6d / %x6e / %x6f / %x70 / %x71 / %x72 /
        %x73 / %x74 / %x75 / %x76 / %x77 / %x78 / %x79 / %x7a /
        %x80 / %x81 / %x82 / %x83 / %x84 / %x85 / %x86 / %x87 /
        %x88 / %x89 / %x8a / %x8b / %x8c / %x8d / %x8e / %x8f /
        %x90 / %x91 / %x92 / %x93 / %x94 / %x95 / %x96 / %x97 /
        %x98 / %x99 / %x9a / %x9b / %x9c / %x9d / %x9e / %x9f /
        %xa0 / %xa1 / %xa2 / %xa3 / %xa4 / %xa5 / %xa6 / %xa7 /
        %xa8 / %xa9 / %xaa / %xab / %xac / %xad / %xae / %xaf /
        %xb0 / %xb1 / %xb2 / %xb3 / %xb4 / %xb5 / %xb6 / %xb7 /
        %xb8 / %xb9 / %xba / %xbb / %xbc / %xbd / %xbe / %xbf /
        %xc0 / %xc1 / %xc2 / %xc3 / %xc4 / %xc5 / %xc6 / %xc7 /
        %xc8 / %xc9 / %xca / %xcb / %xcc / %xcd / %xce / %xcf /
        %xd0 / %xd1 / %xd2 / %xd3 / %xd4 / %xd5 / %xd6 / %xd7 /
        %xd8 / %xd9 / %xda / %xdb / %xdc / %xdd / %xde / %xdf /
        %xe0 / %xe1 / %xe2 / %xe3 / %xe4 / %xe5 / %xe6 / %xe7 /
        %xe8 / %xe9 / %xea / %xeb / %xec / %xed / %xee / %xef /
        %xf0 / %xf1 / %xf2 / %xf3 / %xf4 / %xf5 / %xf6 / %xf7 /
        %xf8 / %xf9 / %xfa / %xfb / %xfc / %xfd / %xfe / %xff

filename = string-literal
hexdigit = HEXDIG
blob-literal = "x" "'" *HEXDIG "'"
bind-parameter = "?" 1*DIGIT / ":" name / "@" name / bind-parameter-tcl
bind-parameter-tcl = "$" name ; FIXME
decimal-point = "."
digit = DIGIT

binary-operator = 
  "||" / 
  "*" / 
  "/" / 
  "%" / 
  "+" / 
  "-" / 
  "<<" / 
  ">>" / 
  "&" / 
  "|" / 
  "<" / 
  "<=" / 
  ">" / 
  ">=" / 
  "=" / 
  "==" / 
  "!=" / 
  "<>" / 
  "IS" / 
  "IS" w "NOT" / 
  "IN" / 
  "LIKE" / 
  "GLOB" / 
  "MATCH" / 
  "REGEXP" / 
  "AND" / 
  "OR"

unary-operator = "-" / "+" / "~" / "NOT"

; FIXME: "The arguments can be just about any text
; as long as it has balanced parentheses."
module-argument = name