SQL Keyword Checking

*** DRAFT ***

SQLite C Interface

SQL Keyword Checking

int sqlite3_keyword_count(void);
int sqlite3_keyword_name(int,const char**,int*);
int sqlite3_keyword_check(const char*,int);

These routines provide access to the set of SQL language keywords recognized by SQLite. Applications can uses these routines to determine whether or not a specific identifier needs to be escaped (for example, by enclosing in double-quotes) so as not to confuse the parser.

The sqlite3_keyword_count() interface returns the number of distinct keywords understood by SQLite.

The sqlite3_keyword_name(N,Z,L) interface finds the 0-based N-th keyword and makes *Z point to that keyword expressed as UTF8 and writes the number of bytes in the keyword into *L. The string that *Z points to is not zero-terminated. The sqlite3_keyword_name(N,Z,L) routine returns SQLITE_OK if N is within bounds and SQLITE_ERROR if not. If either Z or L are NULL or invalid pointers then calls to sqlite3_keyword_name(N,Z,L) result in undefined behavior.

The sqlite3_keyword_check(Z,L) interface checks to see whether or not the L-byte UTF8 identifier that Z points to is a keyword, returning non-zero if it is and zero if not.

The parser used by SQLite is forgiving. It is often possible to use a keyword as an identifier as long as such use does not result in a parsing ambiguity. For example, the statement "CREATE TABLE BEGIN(REPLACE,PRAGMA,END);" is accepted by SQLite, and creates a new table named "BEGIN" with three columns named "REPLACE", "PRAGMA", and "END". Nevertheless, best practice is to avoid using keywords as identifiers. Common techniques used to avoid keyword name collisions include:

Put all identifier names inside double-quotes. This is the official SQL way to escape identifier names.
Put identifier names inside [...]. This is not standard SQL, but it is what SQL Server does and so lots of programmers use this technique.
Begin every identifier with the letter "Z" as no SQL keywords start with "Z".
Include a digit somewhere in every identifier name.

Note that the number of keywords understood by SQLite can depend on compile-time options. For example, "VACUUM" is not a keyword if SQLite is compiled with the -DSQLITE_OMIT_VACUUM option. Also, new keywords may be added to future releases of SQLite.

See also lists of Objects, Constants, and Functions.

*** DRAFT ***