Documentation Source Text

Check-in [e89a552dc3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the <fancy_format> markup which works like <table_of_contents> but omits the actual table of contents. Begin writing documentation for the carray() function. Fix typos and adjust wording in the CLI document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e89a552dc3e7cf5f045da7f90682c34265d011a4
User & Date: drh 2016-07-12 23:52:19.308
Context
2016-07-13
00:55
Improvements to the carray() function documentation. (check-in: 3f0e846a97 user: drh tags: trunk)
2016-07-12
23:52
Add the <fancy_format> markup which works like <table_of_contents> but omits the actual table of contents. Begin writing documentation for the carray() function. Fix typos and adjust wording in the CLI document. (check-in: e89a552dc3 user: drh tags: trunk)
20:54
Updates to reflect changes in 3.14.0. The "expr" syntax diagram is changed to show the ability to put table-valued functions on the RHS of an IN operator. Related text changes. Update the change log. (check-in: 24cd5438cc user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Added pages/carray.in.






































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<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>
Changes to pages/changes.in.
25
26
27
28
29
30
31

32
33
34
35
36
37
38
<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







>







25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<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
Changes to pages/cli.in.
1
2
3

4
5

6
7
8
9
10
11
12
13
14
15
16

17
18
19

20
21
22
23
24
25
26
27
28
29
<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>



>

|
>


|




<
<
<
|
>


|
>
|

|







1
2
3
4
5
6
7
8
9
10
11
12
13
14



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<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>
79
80
81
82
83
84
85
86

87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
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.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite> 
}</tcl>

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







|
>
|
|


















|







80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite> 
}</tcl>

<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.
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

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







|
|







126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

<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:
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
<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;)))







|







384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
<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;)))
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
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 {







|







416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
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 {
Changes to pages/dbstat.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

17
18
19
20
21
22
23
24
<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.


|
<












>
|







1
2
3

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<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.
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

<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







|







78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

<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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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';







|







114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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';
Changes to pages/fancyformat.tcl.
410
411
412
413
414
415
416

417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436







437
438
439
440

  # 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
  # before it. This stops the table of contents from being used for 
  # snippets on search results pages.
  #
  set toc [subst {
    <div class=fancy>
    <div style="font-size:2em;text-align:center;color:#044a64">
      $::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)
}








>




















>
>
>
>
>
>
>

|

<
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447


  # 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
  # before it. This stops the table of contents from being used for 
  # snippets on search results pages.
  #
  set toc [subst {
    <div class=fancy>
    <div style="font-size:2em;text-align:center;color:#044a64">
      $::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)
}

Changes to wrap.tcl.
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
#
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)







|







751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
#
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)
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
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







|







783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
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