SQLite

Check-in [57dce04add]
Login

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

Overview
Comment::-) (CVS 84)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 57dce04addf6389a0e2b723aea47da6a54bff14e
User & Date: drh 2000-06-08 19:38:36.000
Context
2000-06-08
19:43
:-) (CVS 85) (check-in: 8b1c151b7b user: drh tags: trunk)
19:38
:-) (CVS 84) (check-in: 57dce04add user: drh tags: trunk)
16:54
:-) (CVS 83) (check-in: 2e5786d101 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to Makefile.in.
︙︙
178
179
180
181
182
183
184



185
186
187
188
189
190
191

192
193
194
195
196
197
198


c_interface.html:	$(TOP)/www/c_interface.tcl
	tclsh $(TOP)/www/c_interface.tcl >c_interface.html

changes.html:	$(TOP)/www/changes.tcl
	tclsh $(TOP)/www/changes.tcl >changes.html




# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \

  c_interface.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite









>
>
>







>








178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202


c_interface.html:	$(TOP)/www/c_interface.tcl
	tclsh $(TOP)/www/c_interface.tcl >c_interface.html

changes.html:	$(TOP)/www/changes.tcl
	tclsh $(TOP)/www/changes.tcl >changes.html

fileformat.html:	$(TOP)/www/fileformat.tcl
	tclsh $(TOP)/www/fileformat.tcl >fileformat.html

# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \
  fileformat.html \
  c_interface.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite
︙︙
Added www/fileformat.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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218

#
# Run this Tcl script to generate the fileformat.html file.
#
set rcsid {$Id: fileformat.tcl,v 1.1 2000/06/08 19:38:36 drh Exp $}

puts {<html>
<head>
  <title>The SQLite file format</title>
</head>
<body bgcolor=white>
<h1 align=center>
The SQLite File Format
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] GMT)
</p>"

puts {
<p>SQLite stores each SQL table and index in a separate GDBM file.
The name of the GDBM file used to store a particular table is usually
just the table name with "<b>.tbl</b>" appended.
Consider an example:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><i>} body
  regsub -all {\)\)\)} $body {</i></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((rm -rf ex1)))
$ (((sqlite ex1)))
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((create index idx1 on tbl1(one);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((.exit)))
$ ls ex1
idx1.tbl  sqlite_master.tbl  tbl1.tbl
$
}

puts {
<p>The example above creates a new SQL database with a single
table named <b>tbl1</b> and a single index named <b>idx1</b>.
Three files were created for this database.  <b>tbl1.tbl</b> stores
all the data for the <b>tbl1</b> table and <b>idx1.tbl</b> stores
all the information needed by the index <b>idx1</b>.  The remaining file
<b>sqlite_master.tbl</b> holds the data for the special
built-in table called <b>sqlite_master</b>.  Every SQLite database
has an <b>sqlite_master</b> table.  This table contains the schema
for the database.  You can query the <b>sqlite_master</b> table
using ordinary SQL commands, but you cannot write to the
<b>sqlite_master</b> table.</p>

<p>The GDBM file used to store an SQL table is <em>usually</em>
just the name of the table with <b>.tbl</b> appended.  But there
are exceptions.  First, the name of the table is converted to
all lower case letters before being used to construct the filename.
This is because SQL table names are not case sensitive but Unix filenames are.
Second, if the table name contains any characters other than 
alphanumerics and underscores, the exceptional characters are encoded
as a single '+' sign.  For example:</p>
}

Code {
$ (((sqlite ex1)))
sqlite> (((create table 'Strange Table Name!'(a int, b char(30));)))
sqlite> .exit
$ (((ls ex1)))
idx1.tbl sqlite_master.tbl strange+table+name+.tbl tbl1.tbl
$
}

puts {
<h2>SQL Table File Format</h2>

<p>Each record of a GDBM file contains a key and a data.
Both key and data are arbitary bytes of any length.  The information
from an SQL table is mapped into a GDBM file as follows:</p>

<p>The GDBM key for each record of an SQL table file is a
randomly chosen integer.  The key size thus depends on the size
of an integer on the host computer.  (Typically this means "4 bytes".)
</p>

<p>If the SQL table contains N columns, then the data entry
for each record begins with N integers.  Each integer is the
offset in bytes from the beginning of the GDBM data to the 
start of the data for the corresponding column.  If the column
contains a NULL value, then its corresponding integer will
be zero.  All column data is stored as null-terminated ASCII
text strings.</p>

<p>Consider a simple example:</p>
}

Code {
$ (((rm -rf ex1)))
$ (((sqlite ex1)))
sqlite> (((create table t1(a int, b text, c text);)))
sqlite> (((insert into t1 values(10,NULL,'hello!');)))
sqlite> (((insert into t1 values(-11,'this is','a test');)))
sqlite> (((.exit)))
$ (((gdbmdump ex1/t1.tbl)))
key  : 223100ae                                      "1..
data : 0c000000 10000000 18000000 2d313100 74686973  ............-11.this
       20697300 61207465 737400                       is.a test.

key  : a840e996                                      .@..
data : 0c000000 00000000 0f000000 31300068 656c6c6f  ............10.hello
       2100                                          !.

$
}

puts {
<p>In the example above, we have created a new table named <b>t1</b>
that contains two records. The <b>gdbmdump</b> program is used to
dump the contents of the <b>t1</b> GDBM file
in a human readable format.  The source code to <b>gdbmdump</b>
is included with the SQLite distribution.  Just type "make gdbmdump"
to build it.</p>

<p>We can see in the dump of <b>t1</b> that each record
is a separate GDBM entry with a 4-byte random key.  The keys
shown are for a single sample run. If you try
this experiment yourself, you will probably get completely different
keys.<p>

<p>Because the <b>t1</b> table contains 3 columns, the data part of
each record begins with 3 integers.  In both records of the example,
the first integer
has the value 12 since the beginning of the data for the first column
begins on the 13th byte of the record.  You can see how each column's
data is stored as a null-terminated string.  For the second record,
observe that the offset integer is zero for the second column.  This
indicates that the second column contains NULL data.</p>

<h2>SQL Index File Format</h2>

<p>Each SQL index is also represented using a single GDBM file.
There is one entry in the GDBM file for each unique SQL key in the
table that is being indexed.  The GDBM key is an
arbitrary length null-terminated string which is SQL key that
is used by the index.  The data is a list of integers that correspond
to GDBM keys of entries in data table that have the corresponding
SQL key.</p>

<p>To illustrate, we will create an index on the example table
shown above, and add a new entry to this table that has a duplicate
SQL key.</p>
}

Code {
$ (((sqlite ex1)))
sqlite> (((create index i1 on t1(a);)))
sqlite> (((insert into t1 values(10,'another','record');)))
sqlite> (((.exit)))
$ (((gdbmdump ex1/t1.tbl)))
key  : 223100ae                                      "1..
data : 0c000000 10000000 18000000 2d313100 74686973  ............-11.this
       20697300 61207465 737400                       is.a test.

key  : a840e996                                      .@..
data : 0c000000 00000000 0f000000 31300068 656c6c6f  ............10.hello
       2100                                          !.

key  : c19e3119                                      ..1.
data : 0c000000 0f000000 17000000 31300061 6e6f7468  ............10.anoth
       65720072 65636f72 6400                        er.record.
$
}

puts {
<p>We added the new record to the <b>t1</b> table because we wanted to
have two records with the same value on column <b>a</b> since that
column is used by the <b>i1</b> index.  You can see from the dump
above that the new <b>t1</b> record is assigned another random
GDBM key.</p>

<p>Now let's look at a dump of the index file.</p>
}

Code {
$ (((gdbmdump ex1/i1.tbl)))
key  : 313000                                        10.
data : a840e996 c19e3119                             .@....1.

key  : 2d313100                                      -11.
data : 223100ae                                      "1..

$
}

puts {
<p>The GDBM file for the index contains only two records because
the <b>t1</b> table contains only two distinct values for
column <b>a</b>.  You can see that the GDBM keys for each record
are just the text values for <b>a</b> columns of table <b>t1</b>.
The data for each record of the index is a list of integers
where each integer is the GDBM key for an entry in the <b>t1</b>
table that has the corresponding value for the <b>a</b> column.</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}
Changes to www/index.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
31
32
33
34
35
36

37
38
39
40
41
42

43





44

45
46
47








48
49
50
51
52
53
54
55

56
57
58






59

60
61
62
63
64

65
66
67
68
69
70
71
72
73
74






75
76
77
78
79
80
81
82
83
84
85
86

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.14 2000/06/06 22:19:02 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Built Upon 
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>"
puts "The SQLite source code was last modifed on [exec cat last_change] GMT"
puts {</p>}


puts {
<h2>News</h2>
<p>
The SQLite code base is being called "beta" only because it is
relatively new.  It appears to be stable and usable.
Most of the SQL language is now implemented and working.  
The regression test suite
provides good coverage, according to
<a href="http://gcc.gnu.org/onlinedocs/gcov_1.html">gcov</a>.
There are currently no known errors in the code.</p>

<p>If you find bugs or missing features, please submit a comment
to the <a href="#mailinglist">SQLite mailing list</a>.</p>
}


puts {<h2>Introduction</h2>

<p>SQLite is an SQL database built atop the 
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM library</a>.
The SQLite distribution includes both a interactive command-line
access program (<b>sqlite</b>) and a C library (<b>libsqlite.a</b>)

that can be linked
with a C/C++ program to provide SQL database access without having
to rely on an external RDBMS.</p>

<p>The C interface to SQLite is very simple, consisting of only
four functions, a single opaque data structure, and a handful of

constants that define error return codes.





See <a href="c_interface.html">c_interface.html</a> for details.

A Tcl interface
to SQLite is also available and is included in the source tree.
Documentation on the Tcl interface is pending.








Interfaces for perl and python may be supplied in future releases.</p>

<p>The standalone program <b>sqlite</b> can be used
to interactively create, update and/or query an SQLite database.
The sources to the sqlite program are part of the source tree and
can be used as an example of how to interact with the SQLite C
library.  For more information on the sqlite program,
see <a href="sqlite.html">sqlite.html</a>.</p>


<p>A history of changes to SQLite is found
<a href="changes.html">here</a>.</p>








<p>SQLite now implements most of the SQL language.
The following are the known limitations:</p>

<p>
<ul>

<li>Constraints are parsed but are not enforced</li>
<li>There is no support for transactions or rollback</li>
</ul>
</p>

<H2>Status</h2>

<p>New features are still being added to the SQLite code base.
Nevertheless, the code appears to be stable and relatively
bug-free. At least one large database has






be loaded into SQLite and appears to work.</p>

<p>SQLite has so far been tested only on RedHat 6.0 Linux.  But we
know of no reason why it will not work on any other Unix platform,
or on Windows95/98/NT.</p>
}

puts {
<a name="mailinglist" />
<h2>Mailing List</h2>
<p>A mailing list has been set up on eGroups for discussion of
SQLite design issues or for asking questions about SQLite.</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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

57
58
59
60
61
62
63
64
65
66






67
68

69
70
71
72
73
74
75
76
77
78

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

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.15 2000/06/08 19:38:36 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>"
puts "The SQLite source code was last modifed on [exec cat last_change] GMT"
puts {</p>}

if 0 {
puts {
<h2>News</h2>
<p>
The SQLite code base is being called "beta" only because it is
relatively new.  It appears to be stable and usable.
Most of the SQL language is now implemented and working.  
The regression test suite
provides good coverage, according to
<a href="http://gcc.gnu.org/onlinedocs/gcov_1.html">gcov</a>.
There are currently no known errors in the code.</p>

<p>If you find bugs or missing features, please submit a comment
to the <a href="#mailinglist">SQLite mailing list</a>.</p>
}
}

puts {<h2>Introduction</h2>

<p>SQLite is an SQL database engine built on top of the
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM library</a>.
SQLite includes a standalone command-line
access program (<a href="sqlite.html">sqlite</a>)
and a C library (<a href="c_interface.html">libsqlite.a</a>)
that can be linked
with a C/C++ program to provide SQL database access without
an separate RDBMS.</p>

<h2>Features</h2>

<p><ul>
<li>Implements most of SQL92.</li>
<li>A database is just a directory of GDBM files.</li>
<li>Unlimited length records.</li>
<li>Import and export data from 
<a href="http://www.postgresql.org/">PostgreSQL</a>.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> uses only
four functions and one opaque structure.</li>
<li>A <a href="http://dev.scriptics.com/">Tcl</a> interface is
included.</li>

<li>Command-line access program <a href="sqlite.html">sqlite</a> uses
the <a href="http://www.google.com/search?q=gnu+readline+library">GNU
Readline library</a></li>
<li>A Tcl-based test suite provides near 100% code coverage</li>
<li>7500+ lines of C code.  No external dependencies other than GDBM.</li>
<li>Built and tested under Linux (RedHat 6.0).  Should work under any Unix and
probably also under Windows95/98/NT/2000.</li>
</ul>
</p>







<h2>Current Status</h2>


<p>A <a href="changes.html">change history</a> is available online.
There are currently no <em>known</em> bugs or memory leaks
in the library.  <a href="http://gcc.gnu.org/onlinedocs/gcov_1.html">Gcov</a>
is used to verify test coverage.  The test suite currently exercises
all code except for a few areas which are unreachable or which are
only reached when <tt>malloc()</tt> fails.  The code has been tested
for memory leaks and is found to be clean.</p>

<p>
Among the SQL features that SQLite does not currently implement are:</p>


<p>
<ul>
<li>outer joins</li>
<li>constraints are parsed but are not enforced</li>
<li>no support for transactions or rollback</li>
</ul>
</p>

<h2>Documentation</h2>


<p>The following documentation is currently available:</p>

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="fileformat.html">file format</a> used by SQLite databases.</li>
</ul>
</p>

<p>The SQLite source code is 35% comment.  These comments are
another important source of information. </p>

}

puts {
<a name="mailinglist" />
<h2>Mailing List</h2>
<p>A mailing list has been set up on eGroups for discussion of
SQLite design issues or for asking questions about SQLite.</p>
︙︙
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115


























116
117
118
119
120
121
122

puts {</p>

<p>To build sqlite, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>

<blockquote><pre>
$ tar xzf sqlite.tar.gz   ;# Unpacks into directory named "sqlite"
$ mkdir bld               ;# Create a separate build directory
$ cd bld
$ ../sqlite/configure
$ make                    ;# Builds "sqlite" and "libsqlite.a"
$ make test               ;# Optional: run regression tests
</pre></blockquote>
}



























puts {<h2>Related Sites</h2>

<ul>
<li><p>The canonical site for GDBM is
       <a href="http://www.gnu.org/software/gdbm/gdbm.html">
       http://www.gnu.org/software/gdbm/gdbm.html</a></p></li>









|
|


|
|



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








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
161
162
163
164
165
166
167
168
169
170

puts {</p>

<p>To build sqlite, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>

<blockquote><pre>
$ tar xzf sqlite.tar.gz      <i> Unpacks into directory named "sqlite" </i>
$ mkdir bld                  <i> Create a separate build directory </i>
$ cd bld
$ ../sqlite/configure
$ make                       <i> Builds "sqlite" and "libsqlite.a" </i>
$ make test                  <i> Optional: run regression tests </i>
</pre></blockquote>
}

puts {<h2>Command-line Usage Example</h2>

<p>Download the source archive and compile the <b>sqlite</b>
program as described above.  The type:</p>

<blockquote><pre>
bash$ sqlite ~/newdb              <i>Directory ~/newdb created automatically</i>
sqlite> create table t1(
   ...>    a int,
   ...>    b varchar(20)
   ...>    c text
   ...> );                        <i>End each SQL statement with a ';'</i>
sqlite> insert into t1
   ...> values(1,'hi','y''all');
sqlite> select * from t1;
1|hello|world
sqlite> .mode columns             <i>Special commands begin with '.'</i>
sqlite> .header on                <i>Type ".help" for a list of commands</i>
sqlite> select * from t1;
a      b       c
------ ------- -------
1      hi      y'all
sqlite> .exit
base$
</pre></blockquote>
}
puts {<h2>Related Sites</h2>

<ul>
<li><p>The canonical site for GDBM is
       <a href="http://www.gnu.org/software/gdbm/gdbm.html">
       http://www.gnu.org/software/gdbm/gdbm.html</a></p></li>
︙︙
Changes to www/sqlite.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.6 2000/06/02 13:28:00 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.7 2000/06/08 19:38:36 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

  puts {</pre></blockquote>}
}

Code {
$ (((mkdir ex1)))
$ (((sqlite ex1)))
Enter ".help" for instructions
sql> (((create table tbl1(one varchar(10), two smallint);)))
sql> (((insert into tbl1 values('hello!',10);)))
sql> (((insert into tbl1 values('goodbye', 20);)))
sql> (((select * from tbl1;)))
hello!|10
goodbye|20
sql>
}

puts {
<p>(In the example above, and in all subsequent examples, the commands
you type are shown with a green tint in an italic font and the responses
from the computer are shown in black with a constant-width font.)</p>

<p>You can terminate the sqlite program by typing your systems
End-Of-File character (usually a Control-D) or the interrupt
character (usually a Control-C).</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite will give you a
continuation prompt and wait for you to enter more text to be
added to the current SQL command.  This feature allows you to
enter SQL commands that span multiple lines.  For example:</p>
}

Code {
sql> (((CREATE TABLE tbl2 ()))
.... (((  f1 varchar(30) primary key,)))
.... (((  f2 text,)))
.... (((  f3 real)))
.... ((();)))
sql> 
}

puts {
<p>If you exit sqlite and look at the contents of the directory "ex1"
you'll see that it now contains two files: <b>sqlite_master.tcl</b>
and <b>tbl1.tbl</b>.  The <b>tbl1.tbl</b> file contains all the
data for table "tbl1" in your database.  The file








|
|
|
|


|




















|
|
|
|
|
|








48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

  puts {</pre></blockquote>}
}

Code {
$ (((mkdir ex1)))
$ (((sqlite ex1)))
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}

puts {
<p>(In the example above, and in all subsequent examples, the commands
you type are shown with a green tint in an italic font and the responses
from the computer are shown in black with a constant-width font.)</p>

<p>You can terminate the sqlite program by typing your systems
End-Of-File character (usually a Control-D) or the interrupt
character (usually a Control-C).</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite will give you a
continuation prompt and wait for you to enter more text to be
added to the current SQL command.  This feature allows you to
enter SQL commands that span multiple lines.  For example:</p>
}

Code {
sqlite> (((CREATE TABLE tbl2 ()))
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}

puts {
<p>If you exit sqlite and look at the contents of the directory "ex1"
you'll see that it now contains two files: <b>sqlite_master.tcl</b>
and <b>tbl1.tbl</b>.  The <b>tbl1.tbl</b> file contains all the
data for table "tbl1" in your database.  The file
︙︙
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

special sqlite_master table just like any other table
in an SQLite database.  For example:</p>
}

Code {
$ (((sqlite ex1)))
Enter ".help" for instructions
sql> (((select * from sqlite_master;)))
type = table
name = tbl1
tbl_name = tbl1
sql = create table tbl1(one varchar(10), two smallint)
sql>
}

puts {
<p>
But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against
the sqlite_master table.  The sqlite_master
table is updated automatically as you create or drop tables and








|




|








104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

special sqlite_master table just like any other table
in an SQLite database.  For example:</p>
}

Code {
$ (((sqlite ex1)))
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
type = table
name = tbl1
tbl_name = tbl1
sql = create table tbl1(one varchar(10), two smallint)
sqlite>
}

puts {
<p>
But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against
the sqlite_master table.  The sqlite_master
table is updated automatically as you create or drop tables and
︙︙
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167





























168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
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


<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
</p>}

Code {
sql> (((.help)))

.exit                  Exit this program
.explain               Set output mode suitable for EXPLAIN
.header ON|OFF         Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line", "column", "list", or "html"
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.tables                List names all tables in the database
.width NUM NUM ...     Set column widths for "column" mode
sql> 
}

puts {
<h2>Changing Output Formats</h2>

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






























<p>In "line" mode, each field in a record of the database
is shown on a line by itself.  Each line consists of the field
name, an equal sign and the field data.  Successive records are
separated by a blank line.  Here is an example of line mode
output:</p>}

Code {
sql> (((.mode line)))
sql> (((select * from tbl1;)))
one = hello
two = 10

one = goodbye
two = 20
sql>
}

puts {
<p>Line mode used to be the default mode setting.  But after some
experience using the utility, it was decided that "list" mode made
a better default and so now the default mode is "list".</p>
}

puts {
<p>In column mode, each record is shown on a separate line with the
data aligned in columns.  For example:</p>}

Code {
sql> (((.mode column)))
sql> (((select * from tbl1;)))
one         two       
----------  ----------
hello       10        
goodbye     20        
sql>
}

puts {
<p>By default, each column is 10 characters wide. 
Data that is too wide to fit in a column is truncated.  You can
adjust the column widths using the ".width" command.  Like this:</p>}

Code {
sql> (((.width 12 6)))
sql> (((select * from tbl1;)))
one           two   
------------  ------
hello         10    
goodbye       20    
sql>
}

puts {
<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can gives as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>

<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".header" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>}

Code {
sql> (((.header off)))
sql> (((select * from tbl1;)))
hello         10    
goodbye       20    
sql>
}

puts {
<p>The third output mode supported by sqlite is called "list".  In
list mode, each record of a query result is written on one line of
output and each field within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>}

Code {
sql> (((.mode list)))
sql> (((select * from tbl1;)))
hello|10
goodbye|20
sql>
}

puts {
<p>You can use the ".separator" dot command to change the separator
for list mode.  For example, to change the separator to a comma and
a space, you could do this:</p>}

Code {
sql> (((.separator ", ")))
sql> (((select * from tbl1;)))
hello, 10
goodbye, 20
sql>
}

puts {
<p>The last output mode is "html".  In this mode, sqlite writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&;gt and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s








|
>












|









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








|
|





|
<
<
<
<
<
<







|
|




|








|
|




|















|
|


|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<








138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213






214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262





























263
264
265
266
267
268
269


<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
</p>}

Code {
sqlite> (((.help)))
.dump                  Dump database in a text format
.exit                  Exit this program
.explain               Set output mode suitable for EXPLAIN
.header ON|OFF         Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line", "column", "list", or "html"
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.tables                List names all tables in the database
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}

puts {
<h2>Changing Output Formats</h2>

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

puts {
<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each field within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>}

Code {
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))
hello|10
goodbye|20
sqlite>
}

puts {
<p>You can use the ".separator" dot command to change the separator
for list mode.  For example, to change the separator to a comma and
a space, you could do this:</p>}

Code {
sqlite> (((.separator ", ")))
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20
sqlite>
}

<p>In "line" mode, each field in a record of the database
is shown on a line by itself.  Each line consists of the field
name, an equal sign and the field data.  Successive records are
separated by a blank line.  Here is an example of line mode
output:</p>}

Code {
sqlite> (((.mode line)))
sqlite> (((select * from tbl1;)))
one = hello
two = 10

one = goodbye
two = 20
sqlite>






}

puts {
<p>In column mode, each record is shown on a separate line with the
data aligned in columns.  For example:</p>}

Code {
sqlite> (((.mode column)))
sqlite> (((select * from tbl1;)))
one         two       
----------  ----------
hello       10        
goodbye     20        
sqlite>
}

puts {
<p>By default, each column is 10 characters wide. 
Data that is too wide to fit in a column is truncated.  You can
adjust the column widths using the ".width" command.  Like this:</p>}

Code {
sqlite> (((.width 12 6)))
sqlite> (((select * from tbl1;)))
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>
}

puts {
<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can gives as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>

<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".header" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>}

Code {
sqlite> (((.header off)))
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>





























}

puts {
<p>The last output mode is "html".  In this mode, sqlite writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&;gt and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
︙︙
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323

<p>By default, sqlite sends query results to standard output.  You
can change this using the ".output" command.  Just put the name of
an output file as an argument to the .output command and all subsequent
query results will be written to that file.  Use ".output stdout" to
begin writing to standard output again.  For example:</p>}

Code {
sql> (((.mode list)))
sql> (((.separator |)))
sql> (((.output test_file_1.txt)))
sql> (((select * from tbl1;)))
sql> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}

puts {
<h2>Querying the database schema</h2>

<p>The sqlite 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
can enter ".tables".</p>
}

Code {
sql> (((.tables)))
tbl1
tbl2
sql>
}

puts {
<p>The ".tables" command is the same as setting list mode then
executing the following query:</p>

<blockquote><pre>








|
|
|
|
|



















|


|








277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318

<p>By default, sqlite sends query results to standard output.  You
can change this using the ".output" command.  Just put the name of
an output file as an argument to the .output command and all subsequent
query results will be written to that file.  Use ".output stdout" to
begin writing to standard output again.  For example:</p>}

Code {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}

puts {
<h2>Querying the database schema</h2>

<p>The sqlite 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
can enter ".tables".</p>
}

Code {
sqlite> (((.tables)))
tbl1
tbl2
sqlite>
}

puts {
<p>The ".tables" command is the same as setting list mode then
executing the following query:</p>

<blockquote><pre>
︙︙
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364

With no arguments, the ".schema" command shows the original CREATE TABLE
and CREATE INDEX statements that were used to build the current database.
If you give the name of a table to ".schema", it shows the original
CREATE statement used to make that table and all if its indices.
We have:</p>}

Code {
sql> (((.schema)))
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sql> (((.schema tbl2)))
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sql>
}

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

<blockquote><pre>








|






|





|








332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359

With no arguments, the ".schema" command shows the original CREATE TABLE
and CREATE INDEX statements that were used to build the current database.
If you give the name of a table to ".schema", it shows the original
CREATE statement used to make that table and all if its indices.
We have:</p>}

Code {
sqlite> (((.schema)))
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite> (((.schema tbl2)))
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>
}

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

<blockquote><pre>
︙︙
374
375
376
377
378
379
380






















































381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401

WHERE tbl_name LIKE '%s'
ORDER BY type DESC, name
</pre></blockquote>

<p>The <b>%s</b> in the query above is replaced by the argument
to ".schema", of course.</p>























































<h2>Other Dot Commands</h2>

<p>The ".explain" dot command can be used to set the output mode
to "column" and to set the column widths to values that are reasonable
for looking at the output of an EXPLAIN command.  The EXPLAIN command
is an SQLite-specific SQL extension that is useful for debugging.  If any
regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and
analyzed but is not executed.  Instead, the sequence of virtual machine
instructions that would have been used to execute the SQL command are
returned like a query result.  For example:</p>}

Code {
sql> (((.explain)))
sql> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3          
----  ------------  -----  -----  -------------------------------------   
0     ListOpen      0      0                  
1     Open          0      1      tbl1        
2     Next          0      9                  
3     Field         0      1                  
4     Integer       20     0                  








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












|
|








369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
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
448
449
450

WHERE tbl_name LIKE '%s'
ORDER BY type DESC, name
</pre></blockquote>

<p>The <b>%s</b> in the query above is replaced by the argument
to ".schema", of course.</p>

<h2>Converting An Entire Database To An ASCII Text File</h2>

<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>sqlite</b>.</p>

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

Code {
$ (((echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz)))
}

puts {
<p>This generates a file named <b>ex1.dump.gz</b> that contains everything
you need to reconstruct the database at a later time, or on another
machine.  To reconstruct the database, just type:</p>
}

Code {
$ (((zcat ex1.dump.gz | sqlite ex2)))
}

puts {
<p>The text format used is the same as used by
<a href="http://www.postgresql.org/">PostgreSQL</a>, so you
can also use the .dump command to export an SQLite database
into a PostgreSQL database.  Like this:</p>
}

Code {
$ (((createdb ex2)))
$ (((echo '.dump' | sqlite ex1 | psql ex2)))
}

puts {
<p>You can almost (but not quite) go the other way and export
a PostgreSQL database into SQLite using the <b>pg_dump</b> utility.
Unfortunately, when <b>pg_dump</b> writes the database schema information,
it uses some SQL syntax that SQLite does not understand.
So you cannot pipe the output of <b>pg_dump</b> directly 
into <b>sqlite</b>.
But if you can recreate the
schema separately, you can use <b>pg_dump</b> with the <b>-a</b>
option to list just the data
of a PostgreSQL database and import that directly into SQLite.</p>
}

Code {
$ (((sqlite ex3 <schema.sql)))
$ (((pg_dump -a ex2 | sqlite ex3)))
}

puts {
<h2>Other Dot Commands</h2>

<p>The ".explain" dot command can be used to set the output mode
to "column" and to set the column widths to values that are reasonable
for looking at the output of an EXPLAIN command.  The EXPLAIN command
is an SQLite-specific SQL extension that is useful for debugging.  If any
regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and
analyzed but is not executed.  Instead, the sequence of virtual machine
instructions that would have been used to execute the SQL command are
returned like a query result.  For example:</p>}

Code {
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3          
----  ------------  -----  -----  -------------------------------------   
0     ListOpen      0      0                  
1     Open          0      1      tbl1        
2     Next          0      9                  
3     Field         0      1                  
4     Integer       20     0
︙︙