Documentation Source Text

Check-in [45a6eaaaa7]
Login

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

Overview
Comment:Updates to the "opcode.html" and "sqlite.html" documents.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 45a6eaaaa7cacbccedc1096f648503137e2da04b
User & Date: drh 2013-11-18 23:39:48.250
Context
2013-11-19
00:20
Update the list of keywords to include COVERING, WITH, and WITHOUT. COVERING and WITH are currently unused. (check-in: 74e28675d4 user: drh tags: trunk)
2013-11-18
23:39
Updates to the "opcode.html" and "sqlite.html" documents. (check-in: 45a6eaaaa7 user: drh tags: trunk)
20:57
Correct minor typo. (check-in: 9589b9982b user: mistachkin tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/opcode.in.
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
<p>Every SQL statement that SQLite interprets results in a program
for the virtual machine.  But if you precede the SQL statement with
the keyword [EXPLAIN] the virtual machine will not execute the
program.  Instead, the instructions of the program will be returned
like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>

<p>You can use the <b>sqlite3.exe</b> command-line interface (CLI)
tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>

<tcl>
proc Code {body} {
  hd_puts {<blockquote><tt>}
  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 {\&nbsp;} body
  regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</tt></blockquote>}
}

Code {
$ (((sqlite3 ex1.db)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode         p1    p2    p3    p4         p5  comment
----  -------------  ----  ----  ----  ---------  --  -------
0     Trace          0     0     0     explain..  00         
1     Goto           0     20    0                00         

2     OpenRead       0     2     0                00  tbl    

3     SetNumColumns  0     2     0                00         
4     Rewind         0     11    0                00         
5     Column         0     1     2                00  tbl.two
6     Integer        20    3     0                00         
7     Ge             3     10    2     cs(BINARY) 6a         

8     Rowid          0     1     0                00         

9     FifoWrite      1     0     0                00         
10    Next           0     5     0                00         

11    Close          0     0     0                00         
12    OpenWrite      0     2     0                00  tbl    
13    SetNumColumns  0     2     0                00         
14    FifoRead       1     18    0                00         
15    NotExists      0     17    1                00         
16    Delete         0     1     0     tbl        00         
17    Goto           0     14    0                00         
18    Close          0     0     0                00         
19    Halt           0     0     0                00         
20    Transaction    0     1     0                00         
21    VerifyCookie   0     1     0                00         
22    TableLock      -1    2     0     tbl        00         

23    Goto           0     2     0                00         
}
</tcl>

<p>All you have to do is add the [EXPLAIN] keyword to the front of the
SQL statement.  But if you use the ".explain" command in the CLI,
it will set up the output mode to make the program more easily
viewable.</p>

<p>Depending on compile-time options, you 
can put the SQLite virtual machine in a mode where it will trace its
execution by writing messages to standard output.  The non-standard
SQL "PRAGMA" comments can be used to turn tracing on and off.  To







|






|






<

|






|
|
|
|
>
|
>
|
|
|
<
|
>
|
>
|
|
>
|
|
<
|
|
|
|
|
|
|
|
|
>
|




|







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
<p>Every SQL statement that SQLite interprets results in a program
for the virtual machine.  But if you precede the SQL statement with
the keyword [EXPLAIN] the virtual machine will not execute the
program.  Instead, the instructions of the program will be returned
like a query result.  This feature is useful for debugging and
for learning how the virtual machine operates.</p>

<p>You can use the [CLI | sqlite3.exe command-line interface (CLI)]
tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>

<tcl>
proc Code {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 {\&nbsp;} body

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

Code {
$ (((sqlite3 ex1.db)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00               
1     Goto           0     23    0                    00               
2     Null           0     1     0                    00  r[1]=NULL    
3     OpenRead       0     2     0     2              00  root=2 iDb=0; tbl1
4     Explain        0     0     0     SCAN TABLE tbl1  00               
5     Noop           0     0     0                    00  Begin WHERE-loop0: tbl1
6     Rewind         0     14    0                    00               
7       Column         0     1     2                    00  r[2]=tbl1.two

8       Ge             3     13    2     (BINARY)       6a  if r[3]>=r[2] goto 13
9       Noop           0     0     0                    00  Begin WHERE-core
10      Rowid          0     4     0                    00  r[4]=rowid   
11      RowSetAdd      1     4     0                    00  rowset(1)=r[4]
12      Noop           0     0     0                    00  End WHERE-core
13    Next           0     7     0                    01               
14    Noop           0     0     0                    00  End WHERE-loop0: tbl1
15    Close          0     0     0                    00               
16    OpenWrite      0     2     0     3              00  root=2 iDb=0; tbl1

17      RowSetRead     1     21    4                    00  r[4]=rowset(1)
18      NotExists      0     20    4     1              00  intkey=r[4]  
19      Delete         0     1     0     tbl1           00               
20    Goto           0     17    0                    00               
21    Close          0     0     0                    00               
22    Halt           0     0     0                    00               
23    Transaction    0     1     0                    00               
24    VerifyCookie   0     1     0                    00               
25    TableLock      0     2     1     tbl1           00  iDb=0 root=2 write=1
26    Integer        20    3     0                    00  r[3]=20      
27    Goto           0     2     0                    00               
}
</tcl>

<p>All you have to do is add the [EXPLAIN] keyword to the front of the
SQL statement.  But if you use the ".explain" command in the [CLI],
it will set up the output mode to make the program more easily
viewable.</p>

<p>Depending on compile-time options, you 
can put the SQLite virtual machine in a mode where it will trace its
execution by writing messages to standard output.  The non-standard
SQL "PRAGMA" comments can be used to turn tracing on and off.  To
Changes to pages/sqlite.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
<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell}</tcl>

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

<p>The SQLite library includes 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
commands against an SQLite database.  This document provides a brief
introduction on how to use the <b>sqlite3</b> program.

<h3>Getting Started</h3>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" followed by
the name the file that holds the SQLite database.  If the file does

not exist, a new one is created automatically.

The <b>sqlite3</b> program will
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>











|




|
|
>
|
>







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
<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell}</tcl>

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

<p>The SQLite library includes 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.

<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.
The <b>sqlite3</b> program will
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>

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
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>

<h3>Aside: Querying the SQLITE_MASTER table</h3>

<p>The database schema in an SQLite database is stored in
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>

<tcl>Code {
$ (((sqlite3 ex1)))
SQLite version 3.6.11
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
sqlite>
}</tcl>

<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
indices from the database.  You can not make manual changes
to the sqlite_master table.
</p>

<p>
The schema for TEMPORARY tables is not stored in the "sqlite_master" table
since TEMPORARY tables are not visible to applications other than the
application that created the table.  The schema for TEMPORARY tables
is stored in another special table named "sqlite_temp_master".  The
"sqlite_temp_master" table is temporary itself.
</p>

<h3>Special commands to sqlite3</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.







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







71
72
73
74
75
76
77





































78
79
80
81
82
83
84
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>






































<h3>Special commands to sqlite3</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.
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

<tcl>Code {
sqlite> (((.help)))
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format


.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.genfkey ?OPTIONS?     Options are:
                         --no-drop: Do not drop old fkey triggers.
                         --ignore-errors: Ignore tables with fkey errors
                         --exec: Execute generated SQL immediately
                       See file tool/genfkey.README in the source 
                       distribution for further information.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.iotrace FILE          Enable I/O diagnostic logging to FILE

.load FILE ?ENTRY?     Load an extension library

.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values

.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen

.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements


.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings

.tables ?PATTERN?      List names of tables matching a LIKE pattern


.timeout MS            Try opening locked tables for MS milliseconds



.timer ON|OFF          Turn the CPU timer measurement on or off
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}</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>







>
>


|
<
<
|
<
<
<



|
|
>

>









|
>


>





>
>


>
|
>
>

>
>
>

<



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







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

<tcl>Code {
sqlite> (((.help)))
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.


                         With no args, it turns EXPLAIN on.



.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.open ?FILENAME?       Close existing database and reopen FILENAME
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off

sqlite> 
}</tcl>

<h3>Rules for "dot-commands</h3>

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

<ul>
<li>A dot-command must begin with the "." at the left margin
    with no preceeding whitespace.
<li>The dot-command must be entirely contained on a single input line.
<li>A dot-command cannot occur in the middle of an ordinary SQL
    statement.  In other words, a dot-command cannot occur at a
    continuation prompt.
<li>Dot-commands do not recognize comments.
</ul>

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

<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>
260
261
262
263
264
265
266



267
268
269
270
271
272
273

<p>If you specify a column a width of 0, then the column
width is automatically adjusted to be the maximum of three
numbers: 10, the width of the header, and the width of the
first row of data.  This makes the column width self-adjusting.
The default width setting for every column is this 
auto-adjusting 0 value.</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>

<tcl>Code {







>
>
>







255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271

<p>If you specify a column a width of 0, then the column
width is automatically adjusted to be the maximum of three
numbers: 10, the width of the header, and the width of the
first row of data.  This makes the column width self-adjusting.
The default width setting for every column is this 
auto-adjusting 0 value.</p>

<p>You can specify a negative column width to get
right-justified columns.</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>

<tcl>Code {
298
299
300
301
302
303
304








305
































306
307
308
309
310
311
312
<p>The last output mode is "html".  In this mode, sqlite3 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
are.  The html output mode is envisioned as being useful for
CGI.</p>











































<h3>Writing results to a file</h3>

<p>By default, sqlite3 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







>
>
>
>
>
>
>
>

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







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
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
<p>The last output mode is "html".  In this mode, sqlite3 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
are.  The html output mode is envisioned as being useful for
CGI.</p>

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

<tcl>Code {
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00               
1     Goto           0     18    0                    00               
2     Null           0     1     0                    00  r[1]=NULL    
3     OpenRead       0     2     0     2              00  root=2 iDb=0; tbl1
4     Rewind         0     10    0                    00               
5       Column         0     1     2                    00  r[2]=tbl1.two
6       Ge             3     9     2     (BINARY)       6a  if r[3]>=r[2] goto 10
7       Rowid          0     4     0                    00  r[4]=rowid   
8       RowSetAdd      1     4     0                    00  rowset(1)=r[4]
9     Next           0     7     0                    01               
10    Close          0     0     0                    00               
11    OpenWrite      0     2     0     2              00  root=2 iDb=0; tbl1
12      RowSetRead     1     16    4                    00  r[4]=rowset(1)
13      NotExists      0     12    4     1              00  intkey=r[4]  
14      Delete         0     1     0     tbl1           00               
15    Goto           0     12    0                    00               
16    Close          0     0     0                    00               
17    Halt           0     0     0                    00               
18    Transaction    0     1     0                    00               
19    VerifyCookie   0     1     0                    00               
20    TableLock      0     2     1     tbl1           00  iDb=0 root=2 write=1
21    Integer        20    3     0                    00  r[3]=20      
22    Goto           0     2     0                    00               
}</tcl>

<p>Notice how the shell changes the indentation of some opcodes to
help show the loop structure of the VDBE program.

<h3>Writing results to a file</h3>

<p>By default, sqlite3 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
320
321
322
323
324
325
326













327
328
329
330
331
332
333
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}</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>







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







358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}</tcl>

<p>If the first character of the ".output" filename is a pipe symbol
("|") then the remaining characters are treated as a command and the
output is sent to that command.  This makes it easy to pipe the results
of a query into some other process.  For example, a Mac, the 
"open -f" command opens a text editor to display the content that
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tcl>Code {
sqlite3> (((.output '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
sqlite3> (((.output stdout)))
}</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>
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
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 src/shell.c) you'll find
exactly the above query.</p>

<p>The ".indices" command works in a similar way to list all of
the indices for a particular table.  The ".indices" command takes
a single argument which is the name of the table for which the
indices are desired.  Last, but not least, is the ".schema" command.
With no arguments, the ".schema" command shows the original CREATE TABLE
and CREATE INDEX statements that were used to build the current database.







|







405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
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 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
the indices for a particular table.  The ".indices" command takes
a single argument which is the name of the table for which the
indices are desired.  Last, but not least, is the ".schema" command.
With no arguments, the ".schema" command shows the original CREATE TABLE
and CREATE INDEX statements that were used to build the current database.
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
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
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tcl>


<h3>Other Dot Commands</h3>

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

<tcl>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                  
5     Ge            0      2                  
6     Key           0      0                  
7     ListWrite     0      0                  
8     Goto          0      2                  
9     Noop          0      0                  
10    ListRewind    0      0                  
11    ListRead      0      14                 
12    Delete        0      0                  
13    Goto          0      11                 
14    ListClose     0      0                  
}</tcl>



<p>The ".timeout" command sets the amount of time that the <b>sqlite3</b>
program will wait for locks to clear on files it is trying to access
before returning an error.  The default value of the timeout is zero so
that an error is returned immediately if any needed database table or
index is locked.</p>

<p>And finally, we mention the ".exit" command which causes the
sqlite3 program to exit.</p>

<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







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







538
539
540
541
542
543
544








545





















546
547









548
549
550
551
552
553
554
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</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.










<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