Documentation Source Text

sqlite> (((.save ex1.db)))
sqlite> 
}</tcl>

<p>Be careful when using the ".save" command as it will overwrite any
preexisting database files having the same name without prompting for
confirmation.  As with the ".open" command, you might want to use a
full pathname with forward-slash directory separators to avoid ambiguity.

<h3>Special commands to sqlite3</h3>

<p>
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
But if an input line begins with a dot ("."), then

<tcl>DisplayCode {
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tcl>

<h3>Loading Extensions</h3>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the
extension in to a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:

<tcl>DisplayCode {
sqlite> .load /path/to/my_extension
}</tcl>

<p>Note that SQLite automatically adds the appropriate extension suffix
(".dll" on windows, ".dylib" on Mac, ".so" on most other unixes) to the
extension filename.  It is generally a good idea to specify the full
pathname of the extension.

<p>SQLite computes the entry point for the extension based on the extension
filename.  To override this choice, simply add the name of the extension
as a second argument to the ".load" command.

<p>Source code for several useful extensions can be found in the
<a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a>
subdirectory of the SQLite source tree.  You can use these extensions
as-is, or as a basis for creating your own custom extensions to address
your own particular needs.

<h3>Other Dot Commands</h3>

<p>There are many other dot-commands available in the command-line
shell.  See the ".help" command for a complete list for any particular
version and build of SQLite.

<h3>Compiling the sqlite3 program from sources</h3>

<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c" which you can
<a href="http://www.sqlite.org/src/finfo?name=src/shell.c">
download</a> from the SQLite website.  
[how to compile|Compile] this file (together
with the [amalgamation | sqlite3 library source code]) to generate
the executable.  For example:</p>

<blockquote><pre>
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread
</pre></blockquote>

COMPILE_OPTION {SQLITE_DEBUG} {
  The SQLite source code contains literally thousands of assert() statements
  used to verify internal assumptions and subroutine preconditions and
  postconditions.  These assert() statements are normally turned off
  (they generate no code) since turning them on makes SQLite run approximately
  three times slower.  But for testing and analysis, it is useful to turn
  the assert() statements on.  The SQLITE_DEBUG compile-time option does this.
  <p>SQLITE_DEBUG also enables some other debugging features, such as
  special [PRAGMA] statements that turn on tracing and listing features
  used for troubleshooting and analysis of the [VDBE] and code generator.
}

COMPILE_OPTION {SQLITE_MEMDEBUG} {
  The SQLITE_MEMDEBUG option causes an instrumented 
  [debugging memory allocator]
  to be used as the default memory allocator within SQLite.  The
  instrumented memory allocator checks for misuse of dynamically allocated

heading {SQLite Features and Extensions} {
  Pages describing specific features or extension modules of SQLite.
}

doc {Asynchronous IO Mode} {asyncvfs.html} {
  This page describes the asynchronous IO extension developed alongside
  SQLite. Using asynchronous IO can cause SQLite to appear more responsive
  by delegating database writes to a background thread.  <i>NB:  This
  extension is deprecated.  [WAL mode] is recommended as a replacement.</i>
}
doc {Foreign Key Support} {foreignkeys.html} {
  This document describes the support for foreign key constraints introduced
  in version 3.6.19.
}
doc {Full Text Search} {fts3.html} {
  A description of the SQLite Full Text Search (FTS3) extension.
}
doc {R-Trees} {rtree.html} {
  A description of the SQLite R-Tree extension. An R-Tree is a specialized
  data structure that supports fast multi-dimensional range queries often
  used in geospatial systems.
}
doc {Run-Time Loadable Extensions} {loadext.html} {
  A general overview on how run-time loadable extensions work, how they
  are compiled, and how developers can create their own run-time loadable
  extensions for SQLite.
}
doc {Shared Cache Mode} {sharedcache.html} {
  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Unlock Notify} {unlock_notify.html} {
  The "unlock notify" feature can be used in conjunction with
  [shared cache mode] to more efficiently manage resource conflict (database
  table locks).
}
doc {Using The Online Backup Interface} {backup.html} {
  The [sqlite3_backup_init | online-backup interface] can be used to
  copy content from a disk file into an in-memory database or vice
  versa and it can make a hot backup of a live database.  This application
  note gives examples of how.
}









heading {Upgrading SQLite, Backwards Compatibility}

doc {Moving From SQLite 3.5 to 3.6} {35to36.html} {
  A document describing the differences between SQLite version 3.5.9
  and 3.6.0.
}

<p>The sqlite_stat4 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
version of SQLite before 3.8.1.
The sqlite_stat4 table contains additional information
about the distribution of keys within an index or the distribution of
keys in the primary key of a [WITHOUT ROWID] table.
The query planner can sometimes use the additional information in
the sqlite_stat4 table to devise better and faster query algorithms.
The schema of the sqlite_stat4 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are typically between 10 to 40 entries in the sqlite_stat4 table for

  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must have a unique combination
  of values in its primary key columns. ^For the purposes of determining
  the uniqueness of primary key values, NULL values are considered distinct from
  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows
  have identical primary key values, that is a constraint violation.

<p> According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
  Unfortunately, due to a bug in some early versions, this is not the
  case in SQLite. ^Unless the column is an [INTEGER PRIMARY KEY] or
  the table is a [WITHOUT ROWID] table or the column is declared NOT NULL,
  SQLite allows NULL values in a PRIMARY KEY column.  SQLite could be fixed to
  conform to the standard, but doing so might break legacy applications.
  Hence, it has been decided to merely document the fact that SQLite
  allowing NULLs in most PRIMARY KEY columns.

  the expression associated with each CHECK constraint is evaluated and
  cast to a NUMERIC value in the same way as a [CAST expression]. If the 
  result is zero (integer value 0 or real value 0.0), then a constraint
  violation has occurred.)^ ^If the CHECK expression evaluates to NULL, or
  any other non-zero value, it is not a constraint violation.
  ^The expression of a CHECK constraint may not contain a subquery.




<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl>
<p>^A <b>NOT NULL</b> constraint may only be attached to a column definition,
  not specified as a table constraint.  Not surprisingly, ^(a NOT NULL
  constraint dictates that the associated column may not contain a NULL value.
  Attempting to set the column value to NULL when inserting a new row or
  updating an existing one causes a constraint violation.)^

the rowid.

<p> The exception mentioned above is that ^if the declaration of a column with
declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not
become an alias for the rowid and is not classified as an integer primary key.
This quirk is not by design. It is due to a bug in early versions of SQLite.
But fixing the bug could result in backwards incompatibilities.
Hence, the original behavior has been retained (and documented) because odd
behavior in a corner case is far better than a compatibility break.  This means
that ^(the following three table declarations all cause the column "x" to be an
alias for the rowid (an integer primary key):

<ul>
<li><tt>CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);</tt>
<li><tt>CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));</tt>

integer literal.)^  ^The "E" character that begins the exponentiation
clause of a floating point literal can be either upper or lower case.
^(The "." character is always used 
as the decimal point even if the locale setting specifies "," for
this role - the use of "," for the decimal point would result in
syntactic ambiguity.)^

<tcl>hd_fragment hexint {hexadecimal integer literals} {hexadecimal integers}</tcl>
<p>Hexadecimal integer literals follow the C-language notation of
"0x" or "0X" followed by hexadecimal digits.
For example, 0x1234 means the same as 4660
and 0x8000000000000000 means the same as -9223372036854775808.
 ^(Hexadecimal integer literals are interpreted as 64-bit
two's-complement integers and are thus limited
to sixteen significant digits of precision.)^
Support for hexadecimal integers was added to SQLite version 3.8.6.
For backwards compatibility, the "0x" hexadecimal integer
notation is only understood by the SQL language parser, not by the
type conversions routines.
^(String variables that
contain text formatted like hexadecimal integers are not
interpreted as hexadecimal integers when coercing the string value
into an integer due to a [CAST expression] or for a [column affinity]
transformation or prior to performing a numeric operation or for
any other run-time conversions.)^  ^(When coercing a
string value in the format of a hexadecimal integer into an integer
value, the conversion process stops when the 'x' character is seen
so the resulting integer value is always zero.)^
^(SQLite only understands the hexadecimal integer notation when it
appears in the SQL statement text, not when it appears as
part of the content of the database.)^

<p> ^A string constant is formed by enclosing the
string in single quotes (').  ^A single quote within the string can
be encoded by putting two single quotes in a row - as in Pascal.
C-style escapes using the backslash character are not supported because
they are not standard SQL.

<p> ^BLOB literals are string literals containing hexadecimal data and
preceded by a single "x" or "X" character.  ^(For example: X'53514C697465').





<p>
^A literal value can also be the token "NULL".
</p>

<tcl>hd_fragment varparam parameter parameters {bound parameter} {bound parameters}</tcl>
<h3>Parameters</h3>

subqueries.

<h3>Table Column Names</h3>

<p>^A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
^The three special identifiers describe the
unique integer key (the [rowid]) associated with every 
row of every table and so are not available on [WITHOUT ROWID] tables.
^The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.
^The rowid can be used anywhere a regular
column can be used.</p>

<p>^A [SELECT] statement used as either a scalar subquery or as the 
right-hand operand of an IN, NOT IN or EXISTS expression may contain 

  <td> ^When casting a BLOB value to INTEGER, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.  The CAST operator understands decimal integers
        only &mdash; conversion of [hexadecimal integers] stops at the "x" in the
        "0x" prefix of the hexadecimal integer string and thus result of the 
        CAST is always zero.

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible

<title>Run-Time Loadable Extensions</title>
<tcl>hd_keywords {loadext} {loadable extensions} {extension loading} \
                 {SQLite extension} {SQLite extensions} \
                 {loadable extension} \
                 {Run-Time Loadable Extensions}</tcl>

<h1 align="center">Run-Time Loadable Extensions</h1>

<p>SQLite has the ability to load extensions (including new
[application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes]) at run-time.
This feature allows the code for extensions to be developed and

was generated by scanning the 
[http://www.sqlite.org/src/finfo?name=src/vdbe.c | vdbe.c] source file 
and extracting the necessary information from comments.  So the 
source code comments are really the canonical source of information
about the virtual machine.  When in doubt, refer to the source code.</p>

<p>Each instruction in the virtual machine consists of an opcode and
up to five operands named P1, P2  P3, P4, and P5.  The P1, P2, and P3 
operands are 32-bit signed integers.  These operands often refer to 
registers but can also be use dfor other purposes.  The P1 operand is
usually the cursor number for opcodes that operate on cursors.
P2 is usually the jump destination jump instructions.
P4 may be a 32-bit signed integer, a 64-bit signed integer, a
64-bit floating point value, a string literal, a Blob literal,
a pointer to a collating sequence comparison function, or a
pointer to the implementation of an application-defined SQL
function, or various other things.  P5 is an unsigned character
normally used as a flag.
Some operators use all five operands.  Some use

<tcl>
  if {$uuid==""} {
    hd_puts <b>vdbe.c</b>.
  } else {
    hd_puts "<a href=\"http://www.sqlite.org/src/artifact/$uuid\">vdbe.c</a>."
  }
</tcl></p>

<p>Remember: The VDBE opcodes are <u>not</u> part of the interface 
definition for SQLite.  The number of opcodes and their names and meanings
are subject to change from one release of SQLite to the next.
 

<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>

<tcl>
  foreach op [lsort -dictionary $OpcodeList] {

    </p>
  }
}
proc DebugDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    This pragma is intended for use when debugging SQLite itself.  It
    is only available when the [SQLITE_DEBUG] compile-time option
    is used.</p>
  }
}
proc TestDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    The intended use of this pragma is only for testing and validation of