Documentation Source Text

<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     {command-line interface} </tcl>

<h1 align=center>
Command Line Shell For SQLite
</h1>

<p>The SQLite project provides a simple command-line utility named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on windows)
that allows the user to manually enter and execute SQL
statements against an SQLite database.  This document provides a brief
introduction on how to use the <b>sqlite3</b> program.

<tcl>hd_fragment intro</tcl>
<h3>Getting Started</h3>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally
followed by the name the file that holds the SQLite database.  If the 
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified, a temporary 
database is created, then deleted when the "sqlite3" program exits.

................................................................................
by a semicolon), press "Enter" and the SQL will be executed.</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>

<tcl>
proc DisplayCode {body} {
  hd_puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all {\[\[\[} $body {<i>} body
  regsub -all {\]\]\]} $body {</i>} body
  #regsub -all { } $body {\&nbsp;} body
  #regsub -all \n $body <br>\n body


  hd_puts $body
  hd_puts {</pre></blockquote>}

}

DisplayCode {
$ (((sqlite3 ex1)))
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
................................................................................
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>

<tcl>hd_fragment dblclick</tcl>
<h3>Double-click Startup On Windows</h3>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:
................................................................................

<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.

<tcl>hd_fragment dotcmd {dot-commands}</tcl>
<h3>Special commands to sqlite3 (dot-commands)</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
that line is intercepted and interpreted by the sqlite3 program itself.
These "dot commands" are typically used to change the output format
................................................................................
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tcl>

<tcl>hd_fragment dotrules</tcl>
<h3>Rules for "dot-commands"</h3>

<p>Ordinary SQL statements are free-form, and can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are
more restrictive:

<ul>
................................................................................

<p>And, of course, it is important to remember that the dot-commands
are interpreted by the sqlite3.exe command-line program, not by
SQLite itself.  So none of the dot-commands will work as an argument
to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()].

<tcl>hd_fragment dotmode</tcl>
<h3>Changing Output Formats</h3>

<p>The sqlite3 program is able to show the results of a query
in eight different formats: "csv", "column", "html", "insert",
"line", "list", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>

................................................................................

<p>Beginning with [Version 3.11.0], the command-line shell defaults to
"auto-explain" mode, in which the EXPLAIN commands are automatically
detected and the output is automatically formatted.  So the ".explain"
command has become superfluous.

<tcl>hd_fragment dotoutput</tcl>
<h3>Writing results to a file</h3>

<p>By default, sqlite3 sends query results to standard output.  You
can change this using the ".output" and ".once" commands.  Just put 
the name of an output file as an argument to .output and all subsequent
query results will be written to that file.  Or use the .once command
instead of .output and output will only be redirected for the single next
command before returning the console.  Use .output with no arguments to
................................................................................

<tcl>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tcl>

<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h4>File I/O Functions</h4>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate read content from a file into an table column, and writing the
content of a column into a file, respectively.

<p>The readfile(X) SQL function reads the entire content of the file named
X and returns that content as a BLOB.  This can be used to load content into
................................................................................
<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].

<tcl>hd_fragment schema</tcl>
<h3>Querying the database schema</h3>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<p>For example, to see a list of the tables in the database, you
................................................................................
sqlite>
}</tcl>


<p>The ".tables" command is similar to setting list mode then
executing the following query:</p>

<blockquote><pre>
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL 
SELECT name FROM sqlite_temp_master 
WHERE type IN ('table','view') 
ORDER BY 1
</pre></blockquote>

<p>In fact, if you look at the source code to the sqlite3 program
(found in the source tree in the file 
[https://www.sqlite.org/src/artifact?ci=trunk&filename=src/shell.c|src/shell.c])
you'll find a query very much like the above.</p>

<p>The ".indices" command works in a similar way to list all of
................................................................................
sqlite>
}</tcl>


<p>The ".schema" command accomplishes the same thing as setting
list mode, then entering the following query:</p>

<blockquote><pre>
SELECT sql FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name
</pre></blockquote>

<p>Or, if you give an argument to ".schema" because you only
want the schema for a single table, the query looks like this:</p>

<blockquote><pre>
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
</pre></blockquote>

<p>
You can supply an argument to the .schema command.  If you do, the
query looks like this:
</p>

<blockquote><pre>
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
</pre></blockquote>

<p>The "%s" in the query is replace by your argument.  This allows you
to view the schema for some subset of the database.</p>


<tcl>DisplayCode {
sqlite> (((.schema %abc%)))
................................................................................
are requested to provide the complete ".fullschema" output as part
of the trouble report.  Note that the sqlite_stat3 and sqlite_stat4
tables contain samples of index entries and so might contain sensitive
data, so do not send the ".fullschema" output of a proprietary database
over a public channel.</p>

<tcl>hd_fragment csv {CSV import}</tcl>
<h3>CSV Import</h3>

<p>Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.  The ".import" command takes two arguments which are the
name of the disk file from which CSV data is to be read and the name of the
SQLite table into which the CSV data is to be inserted.

<p>Note that it is important to set the "mode" to "csv" before running the
................................................................................
<p>For the second case, when the table already exists, every row of the
CSV file, including the first row, is assumed to be actual content.  If
the CSV file contains an initial row of column labels, that row will be
read as data and inserted into the table.  To avoid this, make sure that
table does not previously exist.

<tcl>hd_fragment csvout {CSV export}</tcl>
<h3>CSV Export</h3>

<p>To export an SQLite table (or part of a table) as CSV, simply set
the "mode" to "csv" and then run a query to extract the desired rows
of the table.

<tcl>DisplayCode {
sqlite> (((.header on)))
................................................................................
the CSV file.  That command only works as shown on Windows.  The
equivalent line on a Mac would be ".system open /work/dataout.csv".
On Linux and other unix systems you will need to enter something like
".system libreoffice /work/dataout.csv", substituting your preferred
CSV viewing program for "libreoffice".

<tcl>hd_fragment dump</tcl>
<h3>Converting An Entire Database To An ASCII Text File</h3>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>

................................................................................

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

<tcl>hd_fragment dotload</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:

................................................................................
<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.

<tcl>hd_fragment dotother</tcl>
<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.

<tcl>hd_fragment insh</tcl>
<h3>Using sqlite3 in a shell script</h3>

<p>
One way to use sqlite3 in a shell script is to use "echo" or
"cat" to generate a sequence of commands in a file, then invoke sqlite3
while redirecting input from the generated command file.  This
works fine and is appropriate in many circumstances.  But as
an added convenience, sqlite3 allows a single SQL command to be
................................................................................
> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}</tcl>

<tcl>hd_fragment endsh</tcl>
<h3>Ending shell commands</h3>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a slash character 
"/" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively.  These won't work in <b>sqlite3_exec()</b>, 
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<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>

<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     {command-line interface} </tcl>

<table_of_contents>



<p>The SQLite project provides a simple command-line utility named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on windows)
that allows the user to manually enter and execute SQL
statements against an SQLite database.  This document provides a brief
introduction on how to use the <b>sqlite3</b> program.

<tcl>hd_fragment intro</tcl>
<h1>Getting Started</h1>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally
followed by the name the file that holds the SQLite database.  If the 
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified, a temporary 
database is created, then deleted when the "sqlite3" program exits.

................................................................................
by a semicolon), press "Enter" and the SQL will be executed.</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>

<tcl>
proc DisplayCode {body} {

  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all {\[\[\[} $body {<i>} body
  regsub -all {\]\]\]} $body {</i>} body
  #regsub -all { } $body {\&nbsp;} body
  #regsub -all \n $body <br>\n body

  #hd_puts {<blockquote><pre>}
  #hd_puts $body
  #hd_puts {</pre></blockquote>}
  hd_puts [CodeBlock $body]
}

DisplayCode {
$ (((sqlite3 ex1)))
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
................................................................................
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>

<tcl>hd_fragment dblclick</tcl>
<h1>Double-click Startup On Windows</h1>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:
................................................................................

<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.

<tcl>hd_fragment dotcmd {dot-commands}</tcl>
<h1>Special commands to sqlite3 (dot-commands)</h1>

<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
that line is intercepted and interpreted by the sqlite3 program itself.
These "dot commands" are typically used to change the output format
................................................................................
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tcl>

<tcl>hd_fragment dotrules</tcl>
<h1>Rules for "dot-commands"</h1>

<p>Ordinary SQL statements are free-form, and can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are
more restrictive:

<ul>
................................................................................

<p>And, of course, it is important to remember that the dot-commands
are interpreted by the sqlite3.exe command-line program, not by
SQLite itself.  So none of the dot-commands will work as an argument
to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()].

<tcl>hd_fragment dotmode</tcl>
<h1>Changing Output Formats</h1>

<p>The sqlite3 program is able to show the results of a query
in eight different formats: "csv", "column", "html", "insert",
"line", "list", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>

................................................................................

<p>Beginning with [Version 3.11.0], the command-line shell defaults to
"auto-explain" mode, in which the EXPLAIN commands are automatically
detected and the output is automatically formatted.  So the ".explain"
command has become superfluous.

<tcl>hd_fragment dotoutput</tcl>
<h1>Writing results to a file</h1>

<p>By default, sqlite3 sends query results to standard output.  You
can change this using the ".output" and ".once" commands.  Just put 
the name of an output file as an argument to .output and all subsequent
query results will be written to that file.  Or use the .once command
instead of .output and output will only be redirected for the single next
command before returning the console.  Use .output with no arguments to
................................................................................

<tcl>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tcl>

<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate read content from a file into an table column, and writing the
content of a column into a file, respectively.

<p>The readfile(X) SQL function reads the entire content of the file named
X and returns that content as a BLOB.  This can be used to load content into
................................................................................
<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].

<tcl>hd_fragment schema</tcl>
<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<p>For example, to see a list of the tables in the database, you
................................................................................
sqlite>
}</tcl>


<p>The ".tables" command is similar to setting list mode then
executing the following query:</p>

<tcl>DisplayCode {
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL 
SELECT name FROM sqlite_temp_master 
WHERE type IN ('table','view') 
ORDER BY 1
} </tcl>

<p>In fact, if you look at the source code to the sqlite3 program
(found in the source tree in the file 
[https://www.sqlite.org/src/artifact?ci=trunk&filename=src/shell.c|src/shell.c])
you'll find a query very much like the above.</p>

<p>The ".indices" command works in a similar way to list all of
................................................................................
sqlite>
}</tcl>


<p>The ".schema" command accomplishes the same thing as setting
list mode, then entering the following query:</p>

<tcl>DisplayCode {
SELECT sql FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name
} </tcl>

<p>Or, if you give an argument to ".schema" because you only
want the schema for a single table, the query looks like this:</p>

<tcl>DisplayCode {
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
} </tcl>

<p>
You can supply an argument to the .schema command.  If you do, the
query looks like this:
</p>

<tcl>DisplayCode {
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
} </tcl>

<p>The "%s" in the query is replace by your argument.  This allows you
to view the schema for some subset of the database.</p>


<tcl>DisplayCode {
sqlite> (((.schema %abc%)))
................................................................................
are requested to provide the complete ".fullschema" output as part
of the trouble report.  Note that the sqlite_stat3 and sqlite_stat4
tables contain samples of index entries and so might contain sensitive
data, so do not send the ".fullschema" output of a proprietary database
over a public channel.</p>

<tcl>hd_fragment csv {CSV import}</tcl>
<h1>CSV Import</h1>

<p>Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.  The ".import" command takes two arguments which are the
name of the disk file from which CSV data is to be read and the name of the
SQLite table into which the CSV data is to be inserted.

<p>Note that it is important to set the "mode" to "csv" before running the
................................................................................
<p>For the second case, when the table already exists, every row of the
CSV file, including the first row, is assumed to be actual content.  If
the CSV file contains an initial row of column labels, that row will be
read as data and inserted into the table.  To avoid this, make sure that
table does not previously exist.

<tcl>hd_fragment csvout {CSV export}</tcl>
<h1>CSV Export</h1>

<p>To export an SQLite table (or part of a table) as CSV, simply set
the "mode" to "csv" and then run a query to extract the desired rows
of the table.

<tcl>DisplayCode {
sqlite> (((.header on)))
................................................................................
the CSV file.  That command only works as shown on Windows.  The
equivalent line on a Mac would be ".system open /work/dataout.csv".
On Linux and other unix systems you will need to enter something like
".system libreoffice /work/dataout.csv", substituting your preferred
CSV viewing program for "libreoffice".

<tcl>hd_fragment dump</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>

................................................................................

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

<tcl>hd_fragment dotload</tcl>
<h1>Loading Extensions</h1>

<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:

................................................................................
<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.

<tcl>hd_fragment dotother</tcl>
<h1>Other Dot Commands</h1>

<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.

<tcl>hd_fragment insh</tcl>
<h1>Using sqlite3 in a shell script</h1>

<p>
One way to use sqlite3 in a shell script is to use "echo" or
"cat" to generate a sequence of commands in a file, then invoke sqlite3
while redirecting input from the generated command file.  This
works fine and is appropriate in many circumstances.  But as
an added convenience, sqlite3 allows a single SQL command to be
................................................................................
> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}</tcl>

<tcl>hd_fragment endsh</tcl>
<h1>Ending shell commands</h1>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a slash character 
"/" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively.  These won't work in <b>sqlite3_exec()</b>, 
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<h1>Compiling the sqlite3 program from sources</h1>

<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>

<tcl>DisplayCode {
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread
} </tcl>

<title>Datatypes In SQLite Version 3</title>
<tcl>hd_keywords {datatype} {dynamic typing}</tcl>

<h1 align=center>Datatypes In SQLite Version 3</h1>


<p>Most SQL database engines (every SQL database engine other than SQLite,
as far as we know) uses static, rigid typing.  With static typing, the datatype
of a value is determined by its container - the particular column in
which the value is stored.</p>

<p>SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with its container.
................................................................................
compatible with the more common static type systems of other database engines
in the sense that SQL statements that work on statically typed databases should
work the same way in SQLite.  However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases.</p>

<tcl>hd_fragment storageclasses {storage class}</tcl>
<h2>1.0 Storage Classes and Datatypes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

................................................................................
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>
<h3>1.1 Boolean Datatype</h3>

<p>SQLite does not have a separate Boolean storage class.
Instead, ^Boolean values are stored as integers 0 (false) and 1 (true).</p>

<tcl>hd_fragment datetime {date and time datatype}</tcl>
<h3>1.2 Date and Time Datatype</h3>

<p>SQLite does not have a storage class set aside for storing
dates and/or times.
^(Instead, the built-in [Date And Time Functions] of SQLite are capable of 
storing dates and times as TEXT, REAL, or INTEGER values:</p>

<ul>
................................................................................

<p>Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date
and time functions.</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h2>2.0 Type Affinity</h2>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The important idea here is that the type is recommended, not
required.  Any column can still store any type of data.
................................................................................
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity BLOB does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

<tcl>hd_fragment affname {rules for determining column affinity}</tcl>
<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the declared type contains the string "INT" then it
  is assigned INTEGER affinity.</p>
................................................................................
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared type is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>

<h3>2.2 Affinity Name Examples</h3>

<p>The following table shows how many common datatype names from
more traditional SQL implementations are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
datatype names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
................................................................................
</table>)^
</blockquote>

<p>^Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

<h3>2.3 Column Affinity Behavior Example</h3>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>

^(<blockquote>
<pre>
CREATE TABLE t1(
................................................................................
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null
</pre>
</blockquote>)^

<tcl>hd_fragment comparisons {comparison expressions}</tcl>
<h2>3.0 Comparison Expressions</h2>

<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "==", "&lt;", "&lt;=", "&gt;", "&gt;=", "!=", "<>",
"IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .

<h3>3.1 Sort Order</h3>

<p>The results of a comparison depend on the storage classes of the
operands, according to the following rules:</p>
<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

................................................................................
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  determined using memcmp().</p>
</ul>

<tcl>hd_fragment expraff {expression affinity}</tcl>
<h3>3.2 Affinity Of Comparison Operands</h3>

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the type affinity of the operands.

<p>Note that every table column as a type affinity (one of BLOB, TEXT, INTEGER,
................................................................................
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has no affinity.
</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
................................................................................
^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
a = +y OR a = +z OR ...".)^  
^In other words, the values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to have no affinity, 
even if they happen to be column values or CAST expressions.  
</p>

<h3>3.4 Comparison Example</h3>

^(<blockquote>
<pre>
CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
................................................................................
</pre>
</blockquote>)^

<p>^All of the result in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h2>4.0 Operators</h2>

<p>^(All mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
&amp;, and |)
cast both operands to the NUMERIC storage class prior to being carried out.)^
^The cast is carried through even if it is lossy and irreversible.
^A NULL operand on a mathematical operator yields a NULL result.
^(An operand on a mathematical operator that does not look in any way
numeric and is not NULL is converted to 0 or 0.0.)^
</p>

<h2>5.0 Sorting, Grouping and Compound SELECTs</h2>

<p>^When query results are sorted by an ORDER BY clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values in collating
sequence order, and finally BLOB values in memcmp() order.  ^No storage
class conversions occur before the sort.</p>

................................................................................
are compared as is.</p>

<tcl>hd_fragment collation {*collating sequence} {*collating sequences}\
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h2>6.0 Collating Sequences</h2>

<p>^When SQLite compares two strings, it uses a collating sequence or
collating function (two words for the same thing) to determine which
string is greater or if the two strings are equal.
^SQLite has three built-in collating functions:  BINARY, NOCASE, and 
RTRIM.</p>

................................................................................
<li>^(<b>RTRIM</b> - The same as binary, except that trailing space
     characters are ignored.)^</li>
</ul>

<p>An application can register additional collating functions using
the [sqlite3_create_collation()] interface.</p>

<h3>6.1 Assigning Collating Sequences from SQL</h3>

<p>
^Every column of every
table has an associated collating function.  ^If no collating function
is explicitly defined, then the collating function defaults to BINARY.
^The COLLATE clause of the [column definition] is used
to define alternative collating functions for a column.
................................................................................
used for sorting.
^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column and has no
COLLATE clause, then the BINARY collating sequence is used.
</p>  

<h3>6.2 Collation Sequence Examples</h3>
<p>
The examples below identify the collating sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>

<title>Datatypes In SQLite Version 3</title>
<tcl>hd_keywords {datatype} {dynamic typing}</tcl>

<table_of_contents>

<h1>Datatypes In SQLite</h1>
<p>Most SQL database engines (every SQL database engine other than SQLite,
as far as we know) uses static, rigid typing.  With static typing, the datatype
of a value is determined by its container - the particular column in
which the value is stored.</p>

<p>SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with its container.
................................................................................
compatible with the more common static type systems of other database engines
in the sense that SQL statements that work on statically typed databases should
work the same way in SQLite.  However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases.</p>

<tcl>hd_fragment storageclasses {storage class}</tcl>
<h1>Storage Classes and Datatypes</h1>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

................................................................................
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>
<h2>Boolean Datatype</h2>

<p>SQLite does not have a separate Boolean storage class.
Instead, ^Boolean values are stored as integers 0 (false) and 1 (true).</p>

<tcl>hd_fragment datetime {date and time datatype}</tcl>
<h2>Date and Time Datatype</h2>

<p>SQLite does not have a storage class set aside for storing
dates and/or times.
^(Instead, the built-in [Date And Time Functions] of SQLite are capable of 
storing dates and times as TEXT, REAL, or INTEGER values:</p>

<ul>
................................................................................

<p>Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date
and time functions.</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h1>Type Affinity</h1>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The important idea here is that the type is recommended, not
required.  Any column can still store any type of data.
................................................................................
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity BLOB does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

<tcl>hd_fragment affname {rules for determining column affinity}</tcl>
<h2>Determination Of Column Affinity</h2>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the declared type contains the string "INT" then it
  is assigned INTEGER affinity.</p>
................................................................................
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared type is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>

<h2>Affinity Name Examples</h2>

<p>The following table shows how many common datatype names from
more traditional SQL implementations are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
datatype names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
................................................................................
</table>)^
</blockquote>

<p>^Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

<h2>Column Affinity Behavior Example</h2>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>

^(<blockquote>
<pre>
CREATE TABLE t1(
................................................................................
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null
</pre>
</blockquote>)^

<tcl>hd_fragment comparisons {comparison expressions}</tcl>
<h1>Comparison Expressions</h1>

<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "==", "&lt;", "&lt;=", "&gt;", "&gt;=", "!=", "<>",
"IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .

<h2>Sort Order</h2>

<p>The results of a comparison depend on the storage classes of the
operands, according to the following rules:</p>
<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

................................................................................
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  determined using memcmp().</p>
</ul>

<tcl>hd_fragment expraff {expression affinity}</tcl>
<h2>Affinity Of Comparison Operands</h2>

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the type affinity of the operands.

<p>Note that every table column as a type affinity (one of BLOB, TEXT, INTEGER,
................................................................................
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has no affinity.
</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h2>Type Conversions Prior To Comparison</h2>

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
................................................................................
^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
a = +y OR a = +z OR ...".)^  
^In other words, the values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to have no affinity, 
even if they happen to be column values or CAST expressions.  
</p>

<h2>Comparison Example</h2>

^(<blockquote>
<pre>
CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
................................................................................
</pre>
</blockquote>)^

<p>^All of the result in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h1>Operators</h1>

<p>^(All mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
&amp;, and |)
cast both operands to the NUMERIC storage class prior to being carried out.)^
^The cast is carried through even if it is lossy and irreversible.
^A NULL operand on a mathematical operator yields a NULL result.
^(An operand on a mathematical operator that does not look in any way
numeric and is not NULL is converted to 0 or 0.0.)^
</p>

<h1>Sorting, Grouping and Compound SELECTs</h1>

<p>^When query results are sorted by an ORDER BY clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values in collating
sequence order, and finally BLOB values in memcmp() order.  ^No storage
class conversions occur before the sort.</p>

................................................................................
are compared as is.</p>

<tcl>hd_fragment collation {*collating sequence} {*collating sequences}\
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h1>Collating Sequences</h1>

<p>^When SQLite compares two strings, it uses a collating sequence or
collating function (two words for the same thing) to determine which
string is greater or if the two strings are equal.
^SQLite has three built-in collating functions:  BINARY, NOCASE, and 
RTRIM.</p>

................................................................................
<li>^(<b>RTRIM</b> - The same as binary, except that trailing space
     characters are ignored.)^</li>
</ul>

<p>An application can register additional collating functions using
the [sqlite3_create_collation()] interface.</p>

<h2>Assigning Collating Sequences from SQL</h2>

<p>
^Every column of every
table has an associated collating function.  ^If no collating function
is explicitly defined, then the collating function defaults to BINARY.
^The COLLATE clause of the [column definition] is used
to define alternative collating functions for a column.
................................................................................
used for sorting.
^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column and has no
COLLATE clause, then the BINARY collating sequence is used.
</p>  

<h2>Collation Sequence Examples</h2>
<p>
The examples below identify the collating sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>

<title>The RBU Extension</title>
<tcl>
hd_keywords {RBU} {RBU extension}
proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
  #hd_puts [CodeBlock $text]
}
</tcl>
<!table_of_contents>
<h1 align='center'>The RBU Extension</h1>

<p>The RBU extension is an add-on for SQLite designed for use with large 
SQLite database files on low-power devices at the edge of a network. RBU
may be used for two separate tasks:

<ul>

<title>The RBU Extension</title>
<tcl>
hd_keywords {RBU} {RBU extension}
proc CODE {text} {
  #hd_puts "<blockquote><pre>"
  #hd_puts $text
  #hd_puts "</pre></blockquote>"
  hd_puts [CodeBlock $text]
}
</tcl>
<table_of_contents>
<h1 align='center'>The RBU Extension</h1>

<p>The RBU extension is an add-on for SQLite designed for use with large 
SQLite database files on low-power devices at the edge of a network. RBU
may be used for two separate tasks:

<ul>

    rc = TCL_OK;
  }

  tokenizerClose(&sToken);
  return rc;
}



































/*
** Tcl command: parsehtml HTML SCRIPT
*/
static int parsehtmlcmd(
  ClientData clientData,
  Tcl_Interp *interp,
................................................................................

  z = zHtml;
  while( *z ){
    char *zText = z;
    while( *z && *z!='<' ) z++;

    /* Invoke the callback script for the chunk of text just parsed. */
    Tcl_IncrRefCount( aCall[nElem]   = Tcl_NewObj() );
    Tcl_IncrRefCount( aCall[nElem+1] = Tcl_NewStringObj(zText, z-zText) );
    rc = Tcl_EvalObjv(interp, nElem+2, aCall, 0);
    Tcl_DecrRefCount( aCall[nElem] );
    Tcl_DecrRefCount( aCall[nElem+1] );
    if( rc!=TCL_OK ) return rc;

    /* Unless is at the end of the document, z now points to the start of a
    ** markup tag. Either an opening or a closing tag. Parse it up and 
    ** invoke the callback script. */
    if( *z ){
      int nTag;
................................................................................
            }
            Tcl_ListObjAppendElement(interp,pParam,Tcl_NewStringObj(zVal,nVal));
          }else if( zAttr ){
            Tcl_ListObjAppendElement(interp, pParam, Tcl_NewIntObj(1));
          }
        }
        
        Tcl_IncrRefCount( aCall[nElem]   = Tcl_NewStringObj(zTag, nTag) );
        Tcl_IncrRefCount( aCall[nElem+1] = pParam );
        rc = Tcl_EvalObjv(interp, nElem+2, aCall, 0);

        Tcl_DecrRefCount( aCall[nElem] );
        Tcl_DecrRefCount( aCall[nElem+1] );


        if( rc!=TCL_OK ) return rc;




      }

      while( *z && !ISSPACE(*z) && *z!='>' ) z++;
      if( *z ) z++;
    }

  }

    rc = TCL_OK;
  }

  tokenizerClose(&sToken);
  return rc;
}

static int doTextCallback(
  Tcl_Interp *interp,
  Tcl_Obj **aCall,
  int nElem,
  const char *zText,
  int nText
){
  int rc;
  Tcl_IncrRefCount( aCall[nElem]   = Tcl_NewObj() );
  Tcl_IncrRefCount( aCall[nElem+1] = Tcl_NewStringObj(zText, nText) );
  rc = Tcl_EvalObjv(interp, nElem+2, aCall, 0);
  Tcl_DecrRefCount( aCall[nElem] );
  Tcl_DecrRefCount( aCall[nElem+1] );
  return rc;
}

static int doTagCallback(
  Tcl_Interp *interp,
  Tcl_Obj **aCall,
  int nElem,
  const char *zTag, int nTag,
  Tcl_Obj *pParam
){
  int rc;
  Tcl_Obj *pArg = pParam;
  if( pArg==0 ) pArg = Tcl_NewObj();
  Tcl_IncrRefCount( aCall[nElem]   = Tcl_NewStringObj(zTag, nTag) );
  Tcl_IncrRefCount( aCall[nElem+1] = pArg );
  rc = Tcl_EvalObjv(interp, nElem+2, aCall, 0);
  Tcl_DecrRefCount( aCall[nElem] );
  Tcl_DecrRefCount( aCall[nElem+1] );
  return rc;
}

/*
** Tcl command: parsehtml HTML SCRIPT
*/
static int parsehtmlcmd(
  ClientData clientData,
  Tcl_Interp *interp,
................................................................................

  z = zHtml;
  while( *z ){
    char *zText = z;
    while( *z && *z!='<' ) z++;

    /* Invoke the callback script for the chunk of text just parsed. */
    rc = doTextCallback(interp, aCall, nElem, zText, z-zText);




    if( rc!=TCL_OK ) return rc;

    /* Unless is at the end of the document, z now points to the start of a
    ** markup tag. Either an opening or a closing tag. Parse it up and 
    ** invoke the callback script. */
    if( *z ){
      int nTag;
................................................................................
            }
            Tcl_ListObjAppendElement(interp,pParam,Tcl_NewStringObj(zVal,nVal));
          }else if( zAttr ){
            Tcl_ListObjAppendElement(interp, pParam, Tcl_NewIntObj(1));
          }
        }
        
        rc = doTagCallback(interp, aCall, nElem, zTag, nTag, pParam);
        if( rc!=TCL_OK ) return rc;


        if( nTag==3 && memcmp(zTag, "tcl", 3)==0 ){
          const char *zText = &z[1];
          while( *z && strncasecmp("</tcl>", z, 6) ) z++;
          rc = doTextCallback(interp, aCall, nElem, zText, z-zText);
          if( rc!=TCL_OK ) return rc;
          rc = doTagCallback(interp, aCall, nElem, "/tcl", 4, 0);
          if( rc!=TCL_OK ) return rc;
          if( *z ) z++;
        }
      }

      while( *z && !ISSPACE(*z) && *z!='>' ) z++;
      if( *z ) z++;
    }

  }