Documentation Source Text

      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}







chng {2008 Aug 6 (3.6.1)} {
<li>Added the [lookaside memory allocator] for a speed improvement in excess
    of 15% on some workloads.  (Your mileage may vary.)</li>
<li>Added the [SQLITE_CONFIG_LOOKASIDE] verb to [sqlite3_config()] to control
    the default lookaside configuration.</li>
<li>Added verbs [SQLITE_STATUS_PAGECACHE_SIZE] and

      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2008 Aug 30 (3.6.2)} {
<li>Split the pager subsystem into separate pager and pcache subsystems.</li>
<li>Factor out indentifier resolution procedures into separate files.</li>
<li>Bug fixes</li>
}

chng {2008 Aug 6 (3.6.1)} {
<li>Added the [lookaside memory allocator] for a speed improvement in excess
    of 15% on some workloads.  (Your mileage may vary.)</li>
<li>Added the [SQLITE_CONFIG_LOOKASIDE] verb to [sqlite3_config()] to control
    the default lookaside configuration.</li>
<li>Added verbs [SQLITE_STATUS_PAGECACHE_SIZE] and

</td>
<td width="20"></td><td bgcolor="#80a796" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_6_1.html">Version 3.6.1</a>
of SQLite is recommended for all new development.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>

</td>
<td width="20"></td><td bgcolor="#80a796" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_6_2.html">Version 3.6.2</a>
of SQLite is recommended for all new development.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>

  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}











newsitem {2008-Aug-06} {Version 3.6.1} {
  SQLite version 3.6.1 is a stabilization and performance enhancement
  release.  
}

newsitem {2008-July-16} {Version 3.6.0 beta} {

  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2008-Aug-30} {Version 3.6.2} {
  SQLite version 3.6.2 contains rewrites of the page-cache subsystem and
  the procedures for matching identifiers to table columns in SQL statements.
  These changes are designed to better modularize the code and make it more
  maintainable and reliable moving forward.  Nearly 5000 non-comment lines 
  of core code (about 11.3%) have changed
  from the previous release.  Nevertheless, there should be no
  application-visible changes, other than bug fixes.
}

newsitem {2008-Aug-06} {Version 3.6.1} {
  SQLite version 3.6.1 is a stabilization and performance enhancement
  release.  
}

newsitem {2008-July-16} {Version 3.6.0 beta} {

SQLite:</p>

<ul>
<li><a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce">
sqlite-announce</a> - announcements of new
releases or significant developments.</li>
<li><a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users">
sqlite-users</a> - general user discussion.</li>
<li><a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev">
sqlite-dev</a> - developer conversations.</li>

</ul>

<p>
Most users of SQLite will want to join the
<a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce">
sqlite-announce</a> list and many will want to join the
<a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users">

SQLite:</p>

<ul>
<li><a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce">
sqlite-announce</a> - announcements of new
releases or significant developments.</li>
<li><a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users">
sqlite-users</a> - general user discussion; most postings belong here.</li>
<li><a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev">
sqlite-dev</a> - developer conversations; for people who have or aspire to
have write permission on the SQLite CVS repository.</li>
</ul>

<p>
Most users of SQLite will want to join the
<a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce">
sqlite-announce</a> list and many will want to join the
<a href="http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users">

automatically, by [sqlite3_step()].  So even though it is reasonable
to think about preparation as being a function of [sqlite3_prepare_v2()]
and its companions and evaluation as being a function of
[sqlite3_step()], the reader should keep in mind that
[sqlite3_step()] might also sometimes do preparations following
schema changes.</p>





<tcl>
syntaxreq {H42000} {} {
  SQLite expects all statements to be terminated by a semicolon.
  If a statement is submitted to SQLite that does not end in a
  semicolon, then a semicolon is added automatically by the tokenizer.
  (See [H41040].)
} {
................................................................................
  robustness in the case where a seldom-used keyword is accidentally
  used as the name of a table or column.
} {
  A token received by the parser shall be converted into an ID token
  if the original token value would have resulted in a syntax error,
  a token value of ID will allow the parse to continue,
  and if the original token value was one of:
  ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN CASCADE CAST CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW
  TEMP TRIGGER VACUUM VIEW VIRTUAL






















  EXCEPT INTERSECT UNION









  REINDEX RENAME CTIME_KW IF










}
syntaxreq {H420012} {} {
  A second level of fallback allows any token to be converted into
  the token ANY.  The ANY token is used to gather arbitrary delimited
  token sequences, such as used as the arguments to the CREATE VIRTUAL
  TABLE command.
} {
................................................................................
  A token received by the parser shall be converted into an ANY token
  if the original token value would have resulted in a syntax error
  and if a token value of ANY will allow the parse to continue.
}
 
</tcl>







































<h2>1.0 Transaction Control</h2>

<h3>1.1 BEGIN</h3>

<tcl>
syntaxreq {H42010} {} {} {
  The SQLite parser shall accept BEGIN statements
  that conform to the following syntax:
} {
  cmd ::= BEGIN transaction_type transaction_name.
................................................................................
  lock to be obtained for all underlying database files.
}
</tcl>
<p>The <i>transaction_name</i> clause of a BEGIN statement is
provided for syntactic compatibility to other SQL database engines.
The <i>transaction_name</i> clause is silently ignored.</p>

<h3>1.2 COMMIT</h3>

<tcl>
syntaxreq {H42110} {} {} {
  SQLite shall accept the following COMMIT statement syntax:
} {
  cmd ::= COMMIT transaction_name.
  cmd ::= END transaction_name.
................................................................................
The <i>transaction_name</i> clause is silently ignored.</p>

<p>The COMMIT and END statements are aliases for one another and
accomplish exactly the same thing.  One is merely a different way
of expression the other.</p>


<h3>1.3 ROLLBACK</h3>

<tcl>
syntaxreq {H42210} {} {} {
  The SQLite parser shall accept ROLLBACK statements
  that conform to the following syntax:
} {
  cmd ::= ROLLBACK transaction_name.
................................................................................
}
</tcl>
<p>The <i>transaction_name</i> clause of a BEGIN statement is
provided for syntactic compatibility to other SQL database engines.
The <i>transaction_name</i> clause is silently ignored.</p>


<h2>2.0 Data Definition Language (DDL)</h2>

<h3>2.1 CREATE TABLE</h3>

<tcl>
syntaxreq {H42310} {} {} {
  The SQLite parser shall accept CREATE TABLE statements
  that conform to the following syntax:
} {
  cmd ::= CREATE temp TABLE ifnotexists fullname table_definition.
................................................................................
syntaxreq {H42334} {} {} {
  The preparation of a CREATE TABLE statement shall fail with an error if the
  the <i>databasename</i> references a database that is not
  attached to the same [database connection].
}
</tcl>

<h4>2.1.1 CREATE TABLE column definitions</h4>

<tcl>
syntaxdef [list {ordinary CREATE TABLE statement} {CREATE TABLE AS statement}] {
  There are two varieties of CREATE TABLE statements.  The most common form
  specifies the names of all columns in the table together with datatype
  and constraint information.  This first form is called a
  "ordinary CREATE TABLE statement".  The second form of CREATE TABLE
................................................................................
  CHECK constraint that uses a subquery shall fail with an error.
}
syntaxreq {H42517} {} {} {
  The preparation of a CREATE TABLE statement that contains a
  CHECK constraint that uses a parameter shall fail with an error.
}
syntaxreq {H42518} {} {} {

  The preparation of a CREATE TABLE statement that contains a
  CHECK constraint that references any column of a table other than


  the table being created shall fail with an error.
}
syntaxreq {H42521} {} {} {
  The preparation of a CREATE TABLE statement that contains a
  DEFAULT constraint with an non-constant expression
  shall fail with an error.
}
syntaxreq {H42536} {} {
................................................................................
  fkaction ::= RESTRICT.
  initially_deferred_clause ::= .
  initially_deferred_clause ::= INITIALLY DEFERRED.
  initially_deferred_clause ::= INITIALLY IMMEDIATE.
}
</tcl>

<h4>2.1.2 CREATE TABLE AS</h4>

<tcl>
syntaxreq {H42610} {} {
  The CREATE TABLE AS statement generates a new table to hold the
  result set of a SELECT statement.
} {
  The SQLite parser shall accept the following syntax for creating new
................................................................................
  All columns in a table generated by a CREATE TABLE AS statement
  shall have a default collating sequence of BINARY.
}

</tcl>


<h3>2.2 DROP TABLE</h3>

<tcl>
syntaxreq {H42700} {} {} {
  The SQLite parser shall accept DROP TABLE statements
  that conform to the following syntax.
} {
  cmd ::= DROP TABLE ifexists fullname.
................................................................................
}
syntaxreq {H42724} {} {} {
  The preparation of a DROP TABLE statement shall fail with an
  error if <i>fullname</i> is a system table.
}
</tcl>

<h3>2.3 CREATE INDEX</h3>

<tcl>
syntaxreq {H42800} {} {} {
  The SQLite parser shall accept CREATE INDEX statements that 
  conform to the following syntax:
} {
  cmd ::= CREATE unique INDEX ifnotexists fullname ON tablename LP indexlist RP.
................................................................................
syntaxreq {H42842} {} {} {
  The preparation of a CREATE INDEX statement shall fail with an error
  if the <i>tablename</i> refers to a system table.
}

</tcl>

<h3>2.4 DROP INDEX</h3>

<tcl>
syntaxreq {H42900} {} {} {
  The SQLite parser shall accept DROP INDEX statements 
  that conform to the following syntax:
} {
  cmd ::= DROP INDEX ifexists fullname.
................................................................................
syntaxreq {H42916} {} {} {
  The successful evaluation of a DROP INDEX statement shall cause
  the index identified by <i>fullname</i> to be removed from its
  database and discarded.
}
</tcl>

<h3>2.5 CREATE VIEW</h3>

<tcl>
syntaxreq {H43100} {} {} {
  The SQLite parser shall accept CREATE VIEW statements 
  that conform to the following syntax:
} {
  cmd ::= CREATE temp VIEW ifnotexists fullname AS select.
................................................................................
  Each column name in a table generated by a CREATE VIEW
  statement shall have an arbitrary suffix appended to its basename
  if and only if such a suffix is necessary to make the name
  distinct from all preceding column names in the view.
}
</tcl>

<h3>2.6 DROP VIEW</h3>

<tcl>
syntaxreq {H43200} {} {} {
  The SQLite parser shall accept DROP VIEW statements
  that conform to the following syntax:
} {
  cmd ::= DROP VIEW ifexists fullname.
................................................................................
syntaxreq {H43214} {} {} {
  The successful evaluation of a DROP VIEW statement shall cause
  all triggers attached to the view identified by <i>fullname</i>
  to be removed from their database and discarded.
}
</tcl>

<h3>2.7 CREATE TRIGGER</h3>

<tcl>
syntaxreq {H43300} {} {} {
  The SQLite parser shall accept CREATE TRIGGER statements
  that conform to the following syntax:
} {
  cmd ::= CREATE temp TRIGGER ifnotexists fullname trigger trigger_body.
................................................................................
  The preparation of a CREATE TRIGGER statement shall fail with an error
  if the <i>trigger_target</i> is a system table.
}

</tcl>


<h3>2.8 DROP TRIGGER</h3>

<tcl>
syntaxreq {H43500} {} {} {
  The SQLite parser shall accept DROP TRIGGER statements
  that conform to the following syntax:
} {
  cmd ::= DROP TRIGGER ifexists fullname.
................................................................................
syntaxreq {H43514} {} {} {
  The successful evaluation of a DROP TRIGGER statement shall cause
  all triggers attached to the trigger identified by <i>fullname</i>
  to be removed from their database and discarded.
}
</tcl>

<h3>2.9 CREATE VIRTUAL TABLE</h3>

<tcl>
syntaxreq {H43600} {} {} {
  The SQLite parser shall accept CREATE VIRTUAL TABLE statements
  that conform to the following syntax.
} {
  cmd ::= CREATE VIRTUAL TABLE fullname USING name vtab_arg_list.
................................................................................
  vtab_arg_token ::= ANY.
  vtab_arg_token ::= LP anylist RP.
  anylist ::= .
  anylist ::= anylist ANY.
}
</tcl>

<h3>2.10 ALTER TABLE</h3>
<h4>2.11 ALTER TABLE RENAME</h4>

<tcl>
syntaxreq {H43700} {} {} {
  The SQLite parser shall accept ALTER TABLE RENAME statements
  that conform to the following syntax:
} {
  cmd ::= ALTER TABLE fullname RENAME TO name.
}
</tcl>

<h4>2.12 ALTER TABLE ADD COLUMN</h4>

<tcl>
syntaxreq {H43750} {} {} {
  The SQLite parser shall accept ALTER TABLE ADD COLUMN statements
  that conform to the following syntax:
} {
  cmd ::= ALTER TABLE fullname ADD column_keyword column.
  column_keyword ::= .
  column_keyword ::= COLUMNKW.
}
</tcl>

<h2>3.0 Data Manipulation Language (DML)</h2>

<h3>3.1 INSERT</h3>
<tcl>
syntaxreq {H43810} {} {} {
  The SQLite parser shall accept INSERT statements that
  conform to the following syntax:
} {
  cmd ::= insert_cmd INTO fullname columnlist_opt insert_content.
  insert_cmd ::= INSERT.
................................................................................
  change to REPLACE.
}
syntaxreq {H43827} {} {} {
  When the form of an INSERT statement is "INSERT OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
}






</tcl>

<h4>3.1.1 INSERT VALUE</h4>
<tcl>
syntaxreq {H43840} {} {} {
  The SQLite parser shall accept INSERT VALUE statements that
  conform to the following syntax:
} {
  insert_content ::= VALUES LP exprlist RP.
}
................................................................................
  error if the <i>columnlist</i> element does not exists and the number of 
  entries in the <i>exprlist</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}
</tcl>

<h4>3.1.2 INSERT SELECT</h4>
syntaxreq {H43870} {} {} {
  The SQLite parser shall accept INSERT SELECT statements that
  conform to the following syntax:
} {
  insert_contents ::= select.
}
syntaxreq {H43873} {} {} {
  The preparation of an INSERT SELECT statement shall fail with an
  error if the <i>columnlist</i> element exists and the number of 
  entries in the <i>instcollist</i> is different
  from the number of columns in the result set of <i>select</i>.
}
syntaxreq {H43876} {} {} {
  The preparation of an INSERT VALUE statement shall fail with an
  error if the <i>columnlist</i> element does not exists and the number of 
  columns in the result set of the <i>select</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}
</tcl>
<h4>3.1.3 INSERT DEFAULT</h4>
<tcl>
syntaxreq {H43890} {} {} {
  The SQLite parser shall accept INSERT DEFAULT statements that
  conform to the following syntax:
} {
  insert_contents ::= DEFAULT VALUES.
}
</tcl>

<h3>3.2 DELETE</h3>

<tcl>
syntaxreq {H43900} {} {} {
  The SQLite parser shall accept DELETE statements that
  conform to the following syntax:
} {
  cmd ::= DELETE FROM fullname where.
................................................................................
}
syntaxreq {H43907} {} {} {
  The preparation of a DELETE statement shall fail with an error if
  the <i>objectname</i> of the <i>fullname</i> element is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable_schema mode.
}






</tcl>

<h3>3.3 UPDATE</h3>

<tcl>
syntaxreq {H44100} {} {} {
  The SQLite parser shall accept UPDATE statements that
  conform to the following syntax:
} {
  cmd ::= update_cmd fullname SET setlist where.
................................................................................
  be used.
}
syntaxreq {H44127} {} {} {
  When the form of an UPDATE statement is "UPDATE OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
}






</tcl>

<h3>3.4 SELECT</h3>

<tcl>
syntaxreq {H45000} {} {} {
  The SQLite parser shall accept SELECT statements that
  conform to the following syntax:
} {
  cmd ::= select.
................................................................................
syntaxreq {H45027} {} {} {
  The preparation of a statement containing a <i>limit</i>
  shall fail with an error if any <i>expr</i> within the <i>limit</i>
  does not evaluate to a compile-time integer.
}
</tcl>





























<h2>4.0 Other Language Elements</h2>

<h3>4.1 VACUUM</h3>

<tcl>
syntaxreq {H44200} {} {} {
  The SQLite parser shall accept VACUUM statements that
  conform to the following syntax:
} {
  cmd ::= VACUUM.
  cmd ::= VACUUM name.
}
</tcl>

<h3>4.2 ANALYZE</h3>

<tcl>
syntaxreq {H44300} {} {} {
  The SQLite parser shall accept ANALYZE statements
  that conform to the following syntax:
} {
  cmd ::= ANALYZE.
................................................................................
  The preparation of an ANALYZE statement
  shall fail with an error if
  the <i>fullname</i> is included and does not evaluate to
  either an individual table name or the name of a database.
}
</tcl>

<h3>4.3 REINDEX</h3>

<tcl>
syntaxreq {H44400} {} {} {
  The SQLite parser shall accept REINDEX statements
  that conform to the following syntax:
} {
  cmd ::= REINDEX.
................................................................................
  shall fail with an error if
  the <i>fullname</i> is included and does not evaluate to
  either an individual table name or the name of a database
  or the name of a collating sequence.	
}
</tcl>

<h3>4.4 PRAGMA</h3>

<tcl>
syntaxreq {H46000} {} {} {
  The SQLite parser shall accept PRAGMA statements
  that conform to the following syntax:
} {
  cmd ::= PRAGMA fullname EQ DELETE.
................................................................................
  the PRAGMA verb.
} {
  The evaluation of a PRAGMA statement with an unknown verb shall
  be a silent no-op.
}
</tcl>

<h3>4.5 ATTACH</h3>

<tcl>
syntaxreq {H44500} {} {
  The SQLite parser shall accept ATTACH statements
  that conform to the following syntax:
} {
  cmd ::= ATTACH database_kw expr AS expr.
................................................................................
syntaxreq {H44509} {} {} {
  The preparation of an ATTACH statement shall fail with an error
  if the second <i>expr</i> evaluates to the name of a database
  that is already attached to the database connection.
}
</tcl>

<h3>4.6 DETACH</h3>

<tcl>
syntaxreq {H44600} {} {} {
  The SQLite parser shall accept DETACH statements
  that conform to the following syntax:
} {
  cmd ::= DETACH database_kw expr.
................................................................................
syntaxreq {H44609} {} {} {
  The preparation of an DETACH statement shall fail with an error
  if the <i>expr</i> does not evaluate to the 
  name of an attached database other than "temp" or "main".
}
</tcl>

<h3>4.7 EXPLAIN</h3>

<tcl>
syntaxreq {H44700} {} {} {
  The SQLite parser shall accept the EXPLAIN keyword as a prefix
  to other valid SQL statements, as shown by the following syntax:
} {
  sql_statement ::= EXPLAIN cmd SEMI.
}
</tcl>

<h3>4.8 EXPLAIN QUERY PLAN</h3>

<tcl>
syntaxreq {H44800} {} {} {
  The SQLite parser shall accept EXPLAIN QUERY PLAY as a prefix
  to other valid SQL statements, as shown by the following syntax:
} {
  sql_statement ::= EXPLAIN QUERY PLAN cmd SEMI.
}
</tcl>

<h2>5.0 Common Language Subelements</h2>

<h3>5.1 Expressions</h3>

<tcl>
syntaxreq {H47000} {} {} {
  The SQLite parser shall accept expressions that
  conform to the following syntax:
} {
  expr ::= BITNOT expr.
  expr ::= CASE case_operand case_exprlist case_else END.
  expr ::= CAST LP expr AS typetoken RP.
  expr ::= EXISTS LP select RP.
  expr ::= function_name LP STAR RP.
  expr ::= function_name LP distinct exprlist RP.
  expr ::= ID.
  expr ::= JOIN_KW.
  expr ::= LP expr RP.
  expr ::= LP select RP.
  expr ::= MINUS expr.
  expr ::= NOT expr.
  expr ::= PLUS expr.
  expr ::= RAISE LP IGNORE RP.
  expr ::= RAISE LP ABORT COMMA name RP.
................................................................................
  expr ::= expr NOT IN LP exprlist RP.
  expr ::= expr NOT IN LP select RP.
  expr ::= expr NOT IN fullname.
  expr ::= expr LIKE_KW expr escape.
  expr ::= expr MATCH expr escape.
  expr ::= expr NOT LIKE_KW expr escape.
  expr ::= expr NOT MATCH expr escape.
  expr ::= databasename DOT tablename DOT columnname.
  expr ::= tablename DOT columnname.
  expr ::= term.
  term ::= CTIME_KW.
  term ::= INTEGER.
  term ::= FLOAT
  term ::= BLOB.
  term ::= NULL.
  term ::= STRING.
................................................................................
  The AND operator shall have precedence over the OR operator.
}
syntaxreq {H47051} {} {} {
  Operators of equal precedence shall group from right to left.
}
</tcl>

<h3>5.2 Functions</h3>
<h4>5.2.1 Core Scalar Functions</h4>
<h4>5.2.2 Date and Time Functions</h4>
<h4>5.2.3 Aggregate Functions</h4>

<h3>5.3 Symbolic Names</h3>

<tcl>
syntaxreq {H49100} {} {} {
  The SQLite parser shall accept names, fullnames, and identifiers
  that conform to the following syntax:
} {
  name ::= ID.
................................................................................
  fullname ::= objectname.
  fullname ::= databasename DOT objectname.
  objectname ::= name.
  databasename ::= name.
  columnname ::= name.
  identifier ::= ID.
  identifier ::= STRING.
}









</tcl>

automatically, by [sqlite3_step()].  So even though it is reasonable
to think about preparation as being a function of [sqlite3_prepare_v2()]
and its companions and evaluation as being a function of
[sqlite3_step()], the reader should keep in mind that
[sqlite3_step()] might also sometimes do preparations following
schema changes.</p>

<h2>1.0 General Requirements</h2>

<h3>1.1 Parser operation</h3>

<tcl>
syntaxreq {H42000} {} {
  SQLite expects all statements to be terminated by a semicolon.
  If a statement is submitted to SQLite that does not end in a
  semicolon, then a semicolon is added automatically by the tokenizer.
  (See [H41040].)
} {
................................................................................
  robustness in the case where a seldom-used keyword is accidentally
  used as the name of a table or column.
} {
  A token received by the parser shall be converted into an ID token
  if the original token value would have resulted in a syntax error,
  a token value of ID will allow the parse to continue,
  and if the original token value was one of:
     ABORT
     AFTER
     ANALYZE
     ASC
     ATTACH
     BEFORE
     BEGIN
     CASCADE
     CAST
     CONFLICT
     CTIME_KW
     DATABASE
     DEFERRED
     DESC
     DETACH
     EACH
     END
     EXCEPT
     EXCLUSIVE
     EXPLAIN
     FAIL
     FOR
     IF
     IGNORE
     IMMEDIATE
     INITIALLY
     INSTEAD
     INTERSECT
     KEY
     LIKE_KW
     MATCH
     OF
     OFFSET
     PLAN
     PRAGMA
     QUERY
     RAISE
     REINDEX
     RENAME
     REPLACE
     RESTRICT
     ROW
     TEMP
     TRIGGER
     UNION
     VACUUM
     VIEW
     VIRTUAL
}
syntaxreq {H420012} {} {
  A second level of fallback allows any token to be converted into
  the token ANY.  The ANY token is used to gather arbitrary delimited
  token sequences, such as used as the arguments to the CREATE VIRTUAL
  TABLE command.
} {
................................................................................
  A token received by the parser shall be converted into an ANY token
  if the original token value would have resulted in a syntax error
  and if a token value of ANY will allow the parse to continue.
}
 
</tcl>

<h3>1.2 Name Contexts</h3>

<p>Expressions in SQL statements often contain identifiers that refer
to columns in tables or views or to columns in the result set of a 
SELECT statement.
The process of determining which column an identifier refers to
is called "name resolution".</p>

<p>Names are resolved with the aid of a "name context".
A name context is a set of tables and views and result set expressions
to which an expression identifier can refer.  A single name context
is a hierarchy of "name context layers".  Subqueries have their own
name context layer (called the "inner layer") that is distinct from the
name context layer (called the "outer layer") associated with the overall 
statement.  In an SQL statement with multiple levels of tested subqueries,
there can be multiple name context layers in the name context.  In
other words, a complete name context is an ordered list of name
context layers.</p>

<p>Within a name context a "source" is a table or view containing columns
that expression names can match against.  Every source has a "canonical
name" which is the name of the table or view as it appears in the schema.
A source might also have an "aliased name" resulting from an AS clause.
The "source name" is the aliased name if it exists, otherwise the source
name is the canonical name.</p>

<p>Each name context layer has a "source set" and a "result set",
either or both of which can be empty.  The source set is an ordered set
of sources.  The result set is an ordered set of expressions with
an optional alias on each expression.  Roughly speaking, a source set
corresponds to the FROM clause of a SELECT statement and the result set
corresponds to the list of expressions that form the result set of the
SELECT statement.</p>

<p>A "empty name context" is a name context comprised of a single
name context layer in which both the source set and the result set are
empty.</p>

<h2>2.0 Transaction Control</h2>

<h3>2.1 BEGIN</h3>

<tcl>
syntaxreq {H42010} {} {} {
  The SQLite parser shall accept BEGIN statements
  that conform to the following syntax:
} {
  cmd ::= BEGIN transaction_type transaction_name.
................................................................................
  lock to be obtained for all underlying database files.
}
</tcl>
<p>The <i>transaction_name</i> clause of a BEGIN statement is
provided for syntactic compatibility to other SQL database engines.
The <i>transaction_name</i> clause is silently ignored.</p>

<h3>2.2 COMMIT</h3>

<tcl>
syntaxreq {H42110} {} {} {
  SQLite shall accept the following COMMIT statement syntax:
} {
  cmd ::= COMMIT transaction_name.
  cmd ::= END transaction_name.
................................................................................
The <i>transaction_name</i> clause is silently ignored.</p>

<p>The COMMIT and END statements are aliases for one another and
accomplish exactly the same thing.  One is merely a different way
of expression the other.</p>


<h3>2.3 ROLLBACK</h3>

<tcl>
syntaxreq {H42210} {} {} {
  The SQLite parser shall accept ROLLBACK statements
  that conform to the following syntax:
} {
  cmd ::= ROLLBACK transaction_name.
................................................................................
}
</tcl>
<p>The <i>transaction_name</i> clause of a BEGIN statement is
provided for syntactic compatibility to other SQL database engines.
The <i>transaction_name</i> clause is silently ignored.</p>


<h2>3.0 Data Definition Language (DDL)</h2>

<h3>3.1 CREATE TABLE</h3>

<tcl>
syntaxreq {H42310} {} {} {
  The SQLite parser shall accept CREATE TABLE statements
  that conform to the following syntax:
} {
  cmd ::= CREATE temp TABLE ifnotexists fullname table_definition.
................................................................................
syntaxreq {H42334} {} {} {
  The preparation of a CREATE TABLE statement shall fail with an error if the
  the <i>databasename</i> references a database that is not
  attached to the same [database connection].
}
</tcl>

<h4>3.1.1 CREATE TABLE column definitions</h4>

<tcl>
syntaxdef [list {ordinary CREATE TABLE statement} {CREATE TABLE AS statement}] {
  There are two varieties of CREATE TABLE statements.  The most common form
  specifies the names of all columns in the table together with datatype
  and constraint information.  This first form is called a
  "ordinary CREATE TABLE statement".  The second form of CREATE TABLE
................................................................................
  CHECK constraint that uses a subquery shall fail with an error.
}
syntaxreq {H42517} {} {} {
  The preparation of a CREATE TABLE statement that contains a
  CHECK constraint that uses a parameter shall fail with an error.
}
syntaxreq {H42518} {} {} {
  Name resolution of the <i>expr</i> with each CHECK constraint of
  a CREATE TABLE statement shall be carried out using a name context

  with an empty result set and a source set holding
  single source element that is the
  table being created.
}
syntaxreq {H42521} {} {} {
  The preparation of a CREATE TABLE statement that contains a
  DEFAULT constraint with an non-constant expression
  shall fail with an error.
}
syntaxreq {H42536} {} {
................................................................................
  fkaction ::= RESTRICT.
  initially_deferred_clause ::= .
  initially_deferred_clause ::= INITIALLY DEFERRED.
  initially_deferred_clause ::= INITIALLY IMMEDIATE.
}
</tcl>

<h4>3.1.2 CREATE TABLE AS</h4>

<tcl>
syntaxreq {H42610} {} {
  The CREATE TABLE AS statement generates a new table to hold the
  result set of a SELECT statement.
} {
  The SQLite parser shall accept the following syntax for creating new
................................................................................
  All columns in a table generated by a CREATE TABLE AS statement
  shall have a default collating sequence of BINARY.
}

</tcl>


<h3>3.2 DROP TABLE</h3>

<tcl>
syntaxreq {H42700} {} {} {
  The SQLite parser shall accept DROP TABLE statements
  that conform to the following syntax.
} {
  cmd ::= DROP TABLE ifexists fullname.
................................................................................
}
syntaxreq {H42724} {} {} {
  The preparation of a DROP TABLE statement shall fail with an
  error if <i>fullname</i> is a system table.
}
</tcl>

<h3>3.3 CREATE INDEX</h3>

<tcl>
syntaxreq {H42800} {} {} {
  The SQLite parser shall accept CREATE INDEX statements that 
  conform to the following syntax:
} {
  cmd ::= CREATE unique INDEX ifnotexists fullname ON tablename LP indexlist RP.
................................................................................
syntaxreq {H42842} {} {} {
  The preparation of a CREATE INDEX statement shall fail with an error
  if the <i>tablename</i> refers to a system table.
}

</tcl>

<h3>3.4 DROP INDEX</h3>

<tcl>
syntaxreq {H42900} {} {} {
  The SQLite parser shall accept DROP INDEX statements 
  that conform to the following syntax:
} {
  cmd ::= DROP INDEX ifexists fullname.
................................................................................
syntaxreq {H42916} {} {} {
  The successful evaluation of a DROP INDEX statement shall cause
  the index identified by <i>fullname</i> to be removed from its
  database and discarded.
}
</tcl>

<h3>3.5 CREATE VIEW</h3>

<tcl>
syntaxreq {H43100} {} {} {
  The SQLite parser shall accept CREATE VIEW statements 
  that conform to the following syntax:
} {
  cmd ::= CREATE temp VIEW ifnotexists fullname AS select.
................................................................................
  Each column name in a table generated by a CREATE VIEW
  statement shall have an arbitrary suffix appended to its basename
  if and only if such a suffix is necessary to make the name
  distinct from all preceding column names in the view.
}
</tcl>

<h3>3.6 DROP VIEW</h3>

<tcl>
syntaxreq {H43200} {} {} {
  The SQLite parser shall accept DROP VIEW statements
  that conform to the following syntax:
} {
  cmd ::= DROP VIEW ifexists fullname.
................................................................................
syntaxreq {H43214} {} {} {
  The successful evaluation of a DROP VIEW statement shall cause
  all triggers attached to the view identified by <i>fullname</i>
  to be removed from their database and discarded.
}
</tcl>

<h3>3.7 CREATE TRIGGER</h3>

<tcl>
syntaxreq {H43300} {} {} {
  The SQLite parser shall accept CREATE TRIGGER statements
  that conform to the following syntax:
} {
  cmd ::= CREATE temp TRIGGER ifnotexists fullname trigger trigger_body.
................................................................................
  The preparation of a CREATE TRIGGER statement shall fail with an error
  if the <i>trigger_target</i> is a system table.
}

</tcl>


<h3>3.8 DROP TRIGGER</h3>

<tcl>
syntaxreq {H43500} {} {} {
  The SQLite parser shall accept DROP TRIGGER statements
  that conform to the following syntax:
} {
  cmd ::= DROP TRIGGER ifexists fullname.
................................................................................
syntaxreq {H43514} {} {} {
  The successful evaluation of a DROP TRIGGER statement shall cause
  all triggers attached to the trigger identified by <i>fullname</i>
  to be removed from their database and discarded.
}
</tcl>

<h3>3.9 CREATE VIRTUAL TABLE</h3>

<tcl>
syntaxreq {H43600} {} {} {
  The SQLite parser shall accept CREATE VIRTUAL TABLE statements
  that conform to the following syntax.
} {
  cmd ::= CREATE VIRTUAL TABLE fullname USING name vtab_arg_list.
................................................................................
  vtab_arg_token ::= ANY.
  vtab_arg_token ::= LP anylist RP.
  anylist ::= .
  anylist ::= anylist ANY.
}
</tcl>

<h3>3.10 ALTER TABLE</h3>
<h4>3.11 ALTER TABLE RENAME</h4>

<tcl>
syntaxreq {H43700} {} {} {
  The SQLite parser shall accept ALTER TABLE RENAME statements
  that conform to the following syntax:
} {
  cmd ::= ALTER TABLE fullname RENAME TO name.
}
</tcl>

<h4>3.12 ALTER TABLE ADD COLUMN</h4>

<tcl>
syntaxreq {H43750} {} {} {
  The SQLite parser shall accept ALTER TABLE ADD COLUMN statements
  that conform to the following syntax:
} {
  cmd ::= ALTER TABLE fullname ADD column_keyword column.
  column_keyword ::= .
  column_keyword ::= COLUMNKW.
}
</tcl>

<h2>4.0 Data Manipulation Language (DML)</h2>

<h3>4.1 INSERT</h3>
<tcl>
syntaxreq {H43810} {} {} {
  The SQLite parser shall accept INSERT statements that
  conform to the following syntax:
} {
  cmd ::= insert_cmd INTO fullname columnlist_opt insert_content.
  insert_cmd ::= INSERT.
................................................................................
  change to REPLACE.
}
syntaxreq {H43827} {} {} {
  When the form of an INSERT statement is "INSERT OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
}
syntaxreq {H43831} {} {} {
  Name resolution in the <i>insert_content</i> term of an INSERT statement
  shall be carried out using a name context
  with an empty result set and a source set holding
  single source element that is the <i>fullname</i> table.
}
</tcl>

<h4>4.1.1 INSERT VALUE</h4>
<tcl>
syntaxreq {H43840} {} {} {
  The SQLite parser shall accept INSERT VALUE statements that
  conform to the following syntax:
} {
  insert_content ::= VALUES LP exprlist RP.
}
................................................................................
  error if the <i>columnlist</i> element does not exists and the number of 
  entries in the <i>exprlist</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}
</tcl>

<h4>4.1.2 INSERT SELECT</h4>
syntaxreq {H43870} {} {} {
  The SQLite parser shall accept INSERT SELECT statements that
  conform to the following syntax:
} {
  insert_contents ::= select.
}
syntaxreq {H43873} {} {} {
  The preparation of an INSERT SELECT statement shall fail with an
  error if the <i>columnlist</i> element exists and the number of 
  entries in the <i>instcollist</i> is different
  from the number of columns in the result set of <i>select</i>.
}
syntaxreq {H43876} {} {} {
  The preparation of an INSERT SELECT statement shall fail with an
  error if the <i>columnlist</i> element does not exists and the number of 
  columns in the result set of the <i>select</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}
</tcl>
<h4>4.1.3 INSERT DEFAULT</h4>
<tcl>
syntaxreq {H43890} {} {} {
  The SQLite parser shall accept INSERT DEFAULT statements that
  conform to the following syntax:
} {
  insert_contents ::= DEFAULT VALUES.
}
</tcl>

<h3>4.2 DELETE</h3>

<tcl>
syntaxreq {H43900} {} {} {
  The SQLite parser shall accept DELETE statements that
  conform to the following syntax:
} {
  cmd ::= DELETE FROM fullname where.
................................................................................
}
syntaxreq {H43907} {} {} {
  The preparation of a DELETE statement shall fail with an error if
  the <i>objectname</i> of the <i>fullname</i> element is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable_schema mode.
}
syntaxreq {H43911} {} {} {
  Name resolution in the <i>where</i> term of a DELETE statement
  shall be carried out using a name context
  with an empty result set and a source set holding
  single source element that is the <i>fullname</i> table.
}
</tcl>

<h3>4.3 UPDATE</h3>

<tcl>
syntaxreq {H44100} {} {} {
  The SQLite parser shall accept UPDATE statements that
  conform to the following syntax:
} {
  cmd ::= update_cmd fullname SET setlist where.
................................................................................
  be used.
}
syntaxreq {H44127} {} {} {
  When the form of an UPDATE statement is "UPDATE OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
}
syntaxreq {H44131} {} {} {
  Name resolution in the <i>where</i> term of a DELETE statement
  shall be carried out using a name context
  with an empty result set and a source set holding
  single source element that is the <i>fullname</i> table.
}
</tcl>

<h3>4.4 SELECT</h3>

<tcl>
syntaxreq {H45000} {} {} {
  The SQLite parser shall accept SELECT statements that
  conform to the following syntax:
} {
  cmd ::= select.
................................................................................
syntaxreq {H45027} {} {} {
  The preparation of a statement containing a <i>limit</i>
  shall fail with an error if any <i>expr</i> within the <i>limit</i>
  does not evaluate to a compile-time integer.
}
</tcl>

<h4>4.4.1 Name resolution with SELECT statements</h4>

<tcl>
syntaxreq {H45103} {} {} {
  Name resolution of a top-level SELECT statement shall use an
  empty name context.
}
syntaxreq {H45106} {} {} {
  Name context of a <i>query</i> term shall be
  constructed by adding a new inner name context layer
  to the name context of the construct containing the <i>query</i>
  term.
}
syntaxreq {H45109} {} {} {
  Name resolution of the <i>resultset</i> of a <i>query</i> shall
  use the name context of the <i>query</i> with an empty result
  set and the source set
  configured to the <i>from</i> clause of the query.
}
syntaxreq {H45112} {} {} {
  Name resolution of all child terms of a <i>query</i> other than
  <i>resultset</i> child shall use the name context of the <i>query</i>
  with the result set configured to be the <i>resultset</i> clause of the
  <i>query</i> and with the source set configured to be the
  <i>from</i> clause of the query.
}
</tcl>

<h2>5.0 Other Language Elements</h2>

<h3>5.1 VACUUM</h3>

<tcl>
syntaxreq {H44200} {} {} {
  The SQLite parser shall accept VACUUM statements that
  conform to the following syntax:
} {
  cmd ::= VACUUM.
  cmd ::= VACUUM name.
}
</tcl>

<h3>5.2 ANALYZE</h3>

<tcl>
syntaxreq {H44300} {} {} {
  The SQLite parser shall accept ANALYZE statements
  that conform to the following syntax:
} {
  cmd ::= ANALYZE.
................................................................................
  The preparation of an ANALYZE statement
  shall fail with an error if
  the <i>fullname</i> is included and does not evaluate to
  either an individual table name or the name of a database.
}
</tcl>

<h3>5.3 REINDEX</h3>

<tcl>
syntaxreq {H44400} {} {} {
  The SQLite parser shall accept REINDEX statements
  that conform to the following syntax:
} {
  cmd ::= REINDEX.
................................................................................
  shall fail with an error if
  the <i>fullname</i> is included and does not evaluate to
  either an individual table name or the name of a database
  or the name of a collating sequence.	
}
</tcl>

<h3>5.4 PRAGMA</h3>

<tcl>
syntaxreq {H46000} {} {} {
  The SQLite parser shall accept PRAGMA statements
  that conform to the following syntax:
} {
  cmd ::= PRAGMA fullname EQ DELETE.
................................................................................
  the PRAGMA verb.
} {
  The evaluation of a PRAGMA statement with an unknown verb shall
  be a silent no-op.
}
</tcl>

<h3>5.5 ATTACH</h3>

<tcl>
syntaxreq {H44500} {} {
  The SQLite parser shall accept ATTACH statements
  that conform to the following syntax:
} {
  cmd ::= ATTACH database_kw expr AS expr.
................................................................................
syntaxreq {H44509} {} {} {
  The preparation of an ATTACH statement shall fail with an error
  if the second <i>expr</i> evaluates to the name of a database
  that is already attached to the database connection.
}
</tcl>

<h3>5.6 DETACH</h3>

<tcl>
syntaxreq {H44600} {} {} {
  The SQLite parser shall accept DETACH statements
  that conform to the following syntax:
} {
  cmd ::= DETACH database_kw expr.
................................................................................
syntaxreq {H44609} {} {} {
  The preparation of an DETACH statement shall fail with an error
  if the <i>expr</i> does not evaluate to the 
  name of an attached database other than "temp" or "main".
}
</tcl>

<h3>5.7 EXPLAIN</h3>

<tcl>
syntaxreq {H44700} {} {} {
  The SQLite parser shall accept the EXPLAIN keyword as a prefix
  to other valid SQL statements, as shown by the following syntax:
} {
  sql_statement ::= EXPLAIN cmd SEMI.
}
</tcl>

<h3>5.8 EXPLAIN QUERY PLAN</h3>

<tcl>
syntaxreq {H44800} {} {} {
  The SQLite parser shall accept EXPLAIN QUERY PLAY as a prefix
  to other valid SQL statements, as shown by the following syntax:
} {
  sql_statement ::= EXPLAIN QUERY PLAN cmd SEMI.
}
</tcl>

<h2>6.0 Common Language Subelements</h2>

<h3>6.1 Expressions</h3>

<tcl>
syntaxreq {H47000} {} {} {
  The SQLite parser shall accept expressions that
  conform to the following syntax:
} {
  expr ::= BITNOT expr.
  expr ::= CASE case_operand case_exprlist case_else END.
  expr ::= CAST LP expr AS typetoken RP.
  expr ::= EXISTS LP select RP.
  expr ::= function_name LP STAR RP.
  expr ::= function_name LP distinct exprlist RP.


  expr ::= LP expr RP.
  expr ::= LP select RP.
  expr ::= MINUS expr.
  expr ::= NOT expr.
  expr ::= PLUS expr.
  expr ::= RAISE LP IGNORE RP.
  expr ::= RAISE LP ABORT COMMA name RP.
................................................................................
  expr ::= expr NOT IN LP exprlist RP.
  expr ::= expr NOT IN LP select RP.
  expr ::= expr NOT IN fullname.
  expr ::= expr LIKE_KW expr escape.
  expr ::= expr MATCH expr escape.
  expr ::= expr NOT LIKE_KW expr escape.
  expr ::= expr NOT MATCH expr escape.
  expr ::= rtvalue.

  expr ::= term.
  term ::= CTIME_KW.
  term ::= INTEGER.
  term ::= FLOAT
  term ::= BLOB.
  term ::= NULL.
  term ::= STRING.
................................................................................
  The AND operator shall have precedence over the OR operator.
}
syntaxreq {H47051} {} {} {
  Operators of equal precedence shall group from right to left.
}
</tcl>






<h3>6.2 Symbolic Names</h3>

<tcl>
syntaxreq {H49100} {} {} {
  The SQLite parser shall accept names, fullnames, and identifiers
  that conform to the following syntax:
} {
  name ::= ID.
................................................................................
  fullname ::= objectname.
  fullname ::= databasename DOT objectname.
  objectname ::= name.
  databasename ::= name.
  columnname ::= name.
  identifier ::= ID.
  identifier ::= STRING.
}
syntaxreq {H49103} {} {} {
  The SQLite parser shall accept <i>rtvalue</i> elements of an
  <i>expr</i> that conform to the following syntax:
} {
  rtvalue ::= databasename DOT tablename DOT columnname.
  rtvalue ::= tablename DOT columnname.
  rtvalue ::= ID.
  rtvalue ::= JOIN_KW.
}
</tcl>