Documentation Source Text

Check-in [bd775e82aa]
Login

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

Overview
Comment:Add automatically generated tables of contents to rbu.html, cli.html and datatype3.html.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bd775e82aaa9677c3e3970abcbea48a081b94cd6
User & Date: dan 2016-04-26 17:59:52.884
Context
2016-04-26
19:37
Also add a table of contents to fileformat2.html. (check-in: e9007b6030 user: dan tags: trunk)
17:59
Add automatically generated tables of contents to rbu.html, cli.html and datatype3.html. (check-in: bd775e82aa user: dan tags: trunk)
15:37
Add documentation for RBU vacuum to rbu.in. (check-in: 2906c27967 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
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
30
31
32
33
34
35
36
37
38
39
40
41
42


43
44

45
46
47
48
49
50
51
<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.

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




|
<
<








|
















<









>
>
|
|
>







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>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>
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);)))
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
   ...> (((  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:







|







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
   ...> (((  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:
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

<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







|







119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

<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
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
.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>







|







208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
.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>
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245

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








|







231
232
233
234
235
236
237
238
239
240
241
242
243
244
245

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

375
376
377
378
379
380
381
382
383
384
385
386
387
388
389

<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







|







375
376
377
378
379
380
381
382
383
384
385
386
387
388
389

<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
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425

<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







|







411
412
413
414
415
416
417
418
419
420
421
422
423
424
425

<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
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
<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







|







442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
<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
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
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







|






|







464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
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
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
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%)))







|





|




|





|






|






|







509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
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%)))
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
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







|







586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
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
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
<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)))







|







619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
<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)))
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
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>








|







655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
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>

690
691
692
693
694
695
696
697
698
699
700
701
702
703
704

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








|







690
691
692
693
694
695
696
697
698
699
700
701
702
703
704

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

718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
<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







|






|







718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
<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
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
> ((( 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>







|











|










|

|
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
> ((( 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>
Changes to pages/datatype3.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
<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.
The dynamic type system of SQLite is backwards
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>




|

>















|







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>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.
The dynamic type system of SQLite is backwards
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>

57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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>







|





|







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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>
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

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







|







86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

<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.
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
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>







|







154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
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>
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
</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







|







184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
</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
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
</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(







|







256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
</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(
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
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>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
  ^When an INTEGER or REAL is compared to another INTEGER or REAL, a
  numerical comparison is performed.</p>

  <li><p>^A TEXT value is less than a BLOB value.  ^When two TEXT values
  are compared an appropriate collating sequence is used to determine 
  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,







|





|




















|







303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
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>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
  ^When an INTEGER or REAL is compared to another INTEGER or REAL, a
  numerical comparison is performed.</p>

  <li><p>^A TEXT value is less than a BLOB value.  ^When two TEXT values
  are compared an appropriate collating sequence is used to determine 
  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,
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
  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>







|







362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
  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>
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
^(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







|







394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
^(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
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
</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>








|










|







460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
</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>

495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
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>








|







496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
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>

519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
<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.







|







520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
<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.
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
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>







|







586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
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>
Changes to pages/rbu.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<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>




|
|
|
|


|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<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>
Changes to search/parsehtml.c.
114
115
116
117
118
119
120

































121
122
123
124
125
126
127
    rc = TCL_OK;
  }

  tokenizerClose(&sToken);
  return rc;
}



































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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
    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,
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

  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;







<
|
<
<
<







183
184
185
186
187
188
189

190



191
192
193
194
195
196
197

  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;
220
221
222
223
224
225
226
227
228
229
230
231


232




233
234
235
236
237
238
239
            }
            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++;
    }

  }







|
|
|
|
|
>
>
|
>
>
>
>







249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
            }
            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++;
    }

  }