︙ | | |
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
-
+
|
#regsub -all \n $body <br>\n body
hd_puts $body
hd_puts {</pre></blockquote>}
}
DisplayCode {
$ (((sqlite3 ex1)))
SQLite version 3.8.4 2014-02-11 16:24:34
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);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
|
︙ | | |
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
|
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
|
-
+
-
+
|
the command-line shell to pop-up a terminal window running SQLite. Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:
<tcl>DisplayCode {
SQLite version 3.8.4 2014-02-11 16:24:34
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite>
}</tcl>
<p>The example above causes the database file named "ex1.db" to be opened
and used, and created if it does not previously exist. You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in. Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>
<p>Alternatively, you can create a new database using the default in-memory
storage, then save that database into a disk file using the ".save" command:
<tcl>DisplayCode {
SQLite version 3.8.4 2014-02-11 16:24:34
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1.db)))
sqlite>
}</tcl>
|
︙ | | |
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
|
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
|
-
+
-
+
-
+
-
+
+
-
+
-
+
-
+
+
+
-
+
-
+
-
-
+
+
|
For a listing of the available dot commands, you can enter ".help"
at any time. For example:
</p>
<tcl>DisplayCode {
sqlite> (((.help)))
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.bail on|off Stop after hitting an error. Default OFF
.clone NEWDB Clone data into NEWDB from the existing database
.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
.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.
.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
.headers 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
.once FILENAME Output for the next SQL command only to FILENAME
.open ?FILENAME? Close existing database and reopen FILENAME
.output FILENAME Send output to FILENAME
.output ?FILENAME? Send output to FILENAME or stdout
.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
.save FILE Write in-memory database into 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
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ON|OFF Turn stats on or off
.stats on|off Turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.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
.timer on|off Turn SQL timer on or off
.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
Negative values right-justify
sqlite>
}</tcl>
<h3>Rules for "dot-commands"</h3>
<p>Ordinary SQL statements are very much free-form, can be
<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 not like that.
The dot-commands are more restrictive:
comments anywhere. But dot-commands are
more restrictive:
<ul>
<li>A dot-command must begin with the "." at the left margin
with no preceding 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
|
︙ | | |
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
|
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
|
-
-
-
+
+
+
+
+
-
-
+
+
-
-
+
+
-
+
-
|
<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
can change this using the ".output" and ".once" commands. Just put
the name of an output file as an argument to .output and all subsequent
query results will be written to that file. Or use the .once command
instead of .output and output will only be redirected for the single next
command before returning the console. Use .output with no arguments to
begin writing to standard output again. For example:</p>
<tcl>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
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
<p>If the first character of the ".output" or ".once" 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
of a query into some other process. For example, the
"open -f" command on a Mac 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>DisplayCode {
sqlite3> (((.output '|open -f')))
sqlite3> (((.once '|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
|
︙ | | |
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
|
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
|
-
-
+
+
-
+
-
+
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
|
<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)))
sqlite> (((.output c:/work/dataout.csv)))
sqlite> (((.mode csv)))
sqlite> (((.mode csv)))
sqlite> (((.once c:/work/dataout.csv)))
sqlite> (((SELECT * FROM tab1;)))
sqlite> (((.output stdout)))
sqlite> (((.system c:/work/dataout.csv)))
}</tcl>
<p>In the example above, the ".header on" line causes column labels to
be printed as the first row of output. This means that the first row of
the resulting CSV file will contain column labels. If column labels are
not desired, set ".header off" instead. (The ".header off" setting is
the default and can be omitted if the headers have not been previously
turned on.)
<p>The line ".output <i>FILENAME</i>" causes all query output to go into
<p>The line ".once <i>FILENAME</i>" causes all query output to go into
the named file instead of being printed on the console. In the example
above, that line causes the CSV content to be written into a file named
"C:/work/dataout.csv". The final line of the example
(the ".output stdout" line) causes subsequent output to be written to
the console again. The use of ".output" is not required for CSV export,
but it is often more convenient than trying to copy/paste the resulting
CSV text from the console.
"C:/work/dataout.csv".
<p>The final line of the example (the ".system c:/work/dataout.csv")
has the same effect as double-clicking on the c:/work/dataout.csv file
in windows. This will typically bring up a spreadsheet program to display
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 perferred
CSV viewing program for "libreoffice".
<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>
|
︙ | | |