Documentation Source Text

<title>The Carray() Table-Valued Function</title>
<tcl>hd_keywords carray {carray() table-valued function}</tcl>
<fancy_format>

<h1>Overview</h1>

<p>Carray($PTR,$N) is a [table-valued function] with a single column (named
"value") and zero or more rows.
The "value" of each row in the carray() is taken from a C-language array
that is $N elements long and begins at address $PTR.
In this way, the carray() function provides a convenient mechanism to
bind C-language arrays to SQL queries.

<h1>Availability</h1>

<p>The carray() function is not compiled into SQLite by default.
It is available as a [loadable extension] in the
[https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/carray.c|ext/misc/carray.c]
source file.

<p>The carray() function is a little dangerous.  The first parameter is
a 64-bit integer which gets cast into a pointer to an array.  In an
application that runs user-generated or untrusted SQL, the carray()
function could be used to crash the appliation or to leak sensitive 
information that the user is not suppose to have access to.  For that
reason, the carray() function will never be a standard part of SQLite.
In that way, carray() will only be available in applications that 
deliberately request it, and which therefore presumably have protections
in place to prevent misuse.

<h1>Details</h1>

<p>The carray() function takes two or three arguments.
The first argument is a 64-bit integer that will be cast into a pointer
to the C-language array that is to be returned by the function.  The
second argument is the number of elements in the array.  The optional
third argument is a string that determines the datatype of the elements
in the C-language array.  Allowed values for the third argument are:

<ol>
<li> 'int32'
<li> 'int64'
<li> 'double'
<li> 'char*'
</ol>

<p>The default datatype is 'int32'.

<h1>Example Usage</h1>

<i>TBD...</i>

<li>Added support for [WITHOUT ROWID virtual tables].
<li>Improved the query planner so that the [OR optimization] can
    be used on [virtual tables] even if one or more of the disjuncts
    use the [LIKE], [GLOB], [REGEXP], [MATCH] operators.
<li>Added the [CSV virtual table] for reading
    [https://www.ietf.org/rfc/rfc4180.txt|RFC 4180] formatted comma-separated
    value files.

<li>Enabled [persistent loadable extensions] using the new
    [SQLITE_OK_LOAD_PERMANENTLY] return code from the extension
    entry point.
<li>Added the [SQLITE_DBSTATUS_CACHE_USED_SHARED] option to [sqlite3_db_status()].
<li>Add the vfsstat.c loadable extension - a VFS shim that measures I/O
    together with an [eponymous virtual table] that provides access to the measurements.
<li>Improved algorithm for running queries with both an ORDER BY and a LIMIT where

<li>Added support for [WITHOUT ROWID virtual tables].
<li>Improved the query planner so that the [OR optimization] can
    be used on [virtual tables] even if one or more of the disjuncts
    use the [LIKE], [GLOB], [REGEXP], [MATCH] operators.
<li>Added the [CSV virtual table] for reading
    [https://www.ietf.org/rfc/rfc4180.txt|RFC 4180] formatted comma-separated
    value files.
<li>Added the [carray() table-valued function] extension.
<li>Enabled [persistent loadable extensions] using the new
    [SQLITE_OK_LOAD_PERMANENTLY] return code from the extension
    entry point.
<li>Added the [SQLITE_DBSTATUS_CACHE_USED_SHARED] option to [sqlite3_db_status()].
<li>Add the vfsstat.c loadable extension - a VFS shim that measures I/O
    together with an [eponymous virtual table] that provides access to the measurements.
<li>Improved algorithm for running queries with both an ORDER BY and a LIMIT where

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

<p>When started, the <b>sqlite3</b> program will show a brief banner
message then prompt you to enter SQL.  Type in SQL statements (terminated
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>
................................................................................
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:

<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
................................................................................

<p>The example above causes the database file named "ex1.db" to be opened
and used, and created if it does not previously exist.  You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in.  Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>

<p>Alternatively, you can create a new database using the default in-memory
storage, then save that database into a disk file using the ".save" command:

<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
................................................................................

<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
of queries, or to execute certain prepackaged query statements.
</p>

<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
................................................................................
<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
begin writing to standard output again.  For example:</p>

<tcl>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
................................................................................
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
a table.  For example:

<tcl>DisplayCode {

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

<table_of_contents>

<tcl>hd_fragment intro</tcl>
<h1>Getting Started</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.




<p>To start the <b>sqlite3</b> program, simply type "sqlite3" at the
command prompt.  The "sqlite3" command may be 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 on the
command-line, a temporary database is created, then deleted when 
the "sqlite3" program exits.

<p>On startup, the <b>sqlite3</b> program will show a brief banner
message then prompt you to enter SQL.  Type in SQL statements (terminated
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>
................................................................................
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.  However,
because double-clicking starts the sqlite3.exe without command-line arguments,
no database file will have been specified, so SQLite will use a temporary
database that is deleted when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:

<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
................................................................................

<p>The example above causes the database file named "ex1.db" to be opened
and used, and created if it does not previously exist.  You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in.  Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>

<p>Alternatively, you can create a new database using the default temporary
storage, then save that database into a disk file using the ".save" command:

<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
................................................................................

<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 input lines that begin with a dot (".")
are intercepted and interpreted by the sqlite3 program itself.
These "dot commands" are typically used to change the output format
of queries, or to execute certain prepackaged query statements.
</p>

<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
................................................................................
<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 reverting to the console.  Use .output with no arguments to
begin writing to standard output again.  For example:</p>

<tcl>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
................................................................................
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 reading 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
a table.  For example:

<tcl>DisplayCode {

<title>The DBSTAT Virtual Table</title>
<tcl>hd_keywords dbstat {dbstat virtual table}</tcl>
<table_of_contents>
<h1>The DBSTAT Virtual Table</h1>

<p>
The DBSTAT virtual tables is a read-only [eponymous virtual table] that returns
information about which pages of the database files are used by which
tables and indexes in the schema.
The the DBSTAT virtual table is used to implement [sqlite3_analyzer.exe]
utility program, and to help compute the 
[https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in
the [https://www.fossil-scm.org/|Fossil-implemented] version control system
for SQLite.
</p>


<h2>Overview</h2>

<p>
^The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
^The dbstat virtual table provides low-level information 
about btree and overflow pages in a database file.
................................................................................

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
dbstat virtual table.

<h2>The "path" column of the dbstat virtual table</h2>

<p>
The "path" column describes the path taken from the 
root node of the btree structure to each page.  The
"path" of the root node itself is '/'.

The "path" for the left-most child page of the root of
................................................................................
the overflow pages associated with a cell will appear earlier in the
sort-order than its child page:

<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h2>Example uses of the dbstat virtual table</h2>

<p>
To find the total number of pages used to store table "xyz" in schema "aux1",
use:

<blockquote><pre>
SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';

<title>The DBSTAT Virtual Table</title>
<tcl>hd_keywords dbstat {dbstat virtual table}</tcl>
<fancy_format>


<p>
The DBSTAT virtual tables is a read-only [eponymous virtual table] that returns
information about which pages of the database files are used by which
tables and indexes in the schema.
The the DBSTAT virtual table is used to implement [sqlite3_analyzer.exe]
utility program, and to help compute the 
[https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in
the [https://www.fossil-scm.org/|Fossil-implemented] version control system
for SQLite.
</p>


<h1>Overview</h1>

<p>
^The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
^The dbstat virtual table provides low-level information 
about btree and overflow pages in a database file.
................................................................................

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
dbstat virtual table.

<h1>The "path" column of the dbstat virtual table</h1>

<p>
The "path" column describes the path taken from the 
root node of the btree structure to each page.  The
"path" of the root node itself is '/'.

The "path" for the left-most child page of the root of
................................................................................
the overflow pages associated with a cell will appear earlier in the
sort-order than its child page:

<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h1>Example uses of the dbstat virtual table</h1>

<p>
To find the total number of pages used to store table "xyz" in schema "aux1",
use:

<blockquote><pre>
SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';

  # The following three are set by the [parsehtml] callback. The title,
  # table-of-contents and text of the pre-processed document.
  #
  set ::Addtoc(title) ""
  set ::Addtoc(toc) ""
  set ::Addtoc(doc) ""


  parsehtml $zDoc addtoc_cb

  # Variable $toc is set to the HTML text for the table of contents. The
  # text "<table_of_contents>" in the input file will be replaced by
  # this text. The "<div class=startsearch>" tag tells the script that 
  # builds the site-search database not to index any text that occurs
................................................................................
      $::Addtoc(title)
    </div>
    <div style="font-size:1.5em;margin:1em;color:#044a64">
      Table Of Contents</div>
    <div id=toc> $::Addtoc(toc) </div>
    <div class=startsearch></div>
  }]








  string map [list <table_of_contents> $toc] $::Addtoc(doc)
}

  # The following three are set by the [parsehtml] callback. The title,
  # table-of-contents and text of the pre-processed document.
  #
  set ::Addtoc(title) ""
  set ::Addtoc(toc) ""
  set ::Addtoc(doc) ""
  set ::Addtoc(fancy) ""

  parsehtml $zDoc addtoc_cb

  # Variable $toc is set to the HTML text for the table of contents. The
  # text "<table_of_contents>" in the input file will be replaced by
  # this text. The "<div class=startsearch>" tag tells the script that 
  # builds the site-search database not to index any text that occurs
................................................................................
      $::Addtoc(title)
    </div>
    <div style="font-size:1.5em;margin:1em;color:#044a64">
      Table Of Contents</div>
    <div id=toc> $::Addtoc(toc) </div>
    <div class=startsearch></div>
  }]
  set fancy [subst {
    <div class=fancy>
    <div style="font-size:2em;text-align:center;color:#044a64">
      $::Addtoc(title)
    </div>
    <div class=startsearch></div>
  }]

  string map [list <table_of_contents> $toc <fancy_format> $fancy] $::Addtoc(doc)
}

#
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  if {[string first <table_of_contents> $in]>=0} { set in [addtoc $in] }
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  hd_open_main $outfile
  db eval {
    INSERT INTO page(filename,pagetitle)
................................................................................
proc hd_requirement {args} {}
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  if {[string first <table_of_contents> $in]>=0} { set in [addtoc $in] }
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  hd_open_main $outfile
  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in

#
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  if {[regexp {<(fancy_format|table_of_contents)>} $in]} { set in [addtoc $in] }
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  hd_open_main $outfile
  db eval {
    INSERT INTO page(filename,pagetitle)
................................................................................
proc hd_requirement {args} {}
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  if {[regexp {<(fancy_format|table_of_contents)>} $in]} { set in [addtoc $in] }
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  hd_open_main $outfile
  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in