Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Update the cli.html document and the change log for the .system and the .once enhancements to the command-line shell. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a0826e69769fa73f7a74bb7819cc09f5 |
| User & Date: | drh 2014-05-29 14:08:27 |
|
2014-05-29
| ||
| 15:46 | Change the default maximum POST size in althttpd to 20MB. (check-in: 35fcd6cd5b user: drh tags: trunk) | |
| 14:08 | Update the cli.html document and the change log for the .system and the .once enhancements to the command-line shell. (check-in: a0826e6976 user: drh tags: trunk) | |
|
2014-05-28
| ||
| 19:37 | Add instructions on building DLLs to the howtocompile.html page. (check-in: cec1821ade user: drh tags: trunk) | |
Changes to pages/changes.in.
31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
<li>Added new [URI query parameters] "nolock" and "immutable".
<li>Use less memory by not remembering CHECK constraints on read-only
database connections.
<li>Enable the [or-connected-terms | OR optimization] for [WITHOUT ROWID] tables.
<li>Render expressions of the form "x IN (?)" (with a single value in
the list on the right-hand side of the IN operator) as if they where "x==?",
Similarly optimize "x NOT IN (?)"
<p><b>Bug Fixes:</b>
<li>OFFSET clause ignored on queries without a FROM clause.
Ticket [http://www.sqlite.org/src/info/07d6a0453d | 07d6a0453d]
<li>Assertion fault on queries involving expressions of the form
"x IN (?)". Ticket [http://www.sqlite.org/src/info/e39d032577|e39d032577].
<li>Incorrect column datatype reported.
Ticket [http://www.sqlite.org/src/info/a8a0d2996a | a8a0d2996a]
|
> |
31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<li>Added new [URI query parameters] "nolock" and "immutable".
<li>Use less memory by not remembering CHECK constraints on read-only
database connections.
<li>Enable the [or-connected-terms | OR optimization] for [WITHOUT ROWID] tables.
<li>Render expressions of the form "x IN (?)" (with a single value in
the list on the right-hand side of the IN operator) as if they where "x==?",
Similarly optimize "x NOT IN (?)"
<li>Add the ".system" and ".once" commands to the [command-line shell].
<p><b>Bug Fixes:</b>
<li>OFFSET clause ignored on queries without a FROM clause.
Ticket [http://www.sqlite.org/src/info/07d6a0453d | 07d6a0453d]
<li>Assertion fault on queries involving expressions of the form
"x IN (?)". Ticket [http://www.sqlite.org/src/info/e39d032577|e39d032577].
<li>Incorrect column datatype reported.
Ticket [http://www.sqlite.org/src/info/a8a0d2996a | a8a0d2996a]
|
Changes to pages/cli.in.
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 .. 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 ... 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 ... 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 ... 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 ... 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 ... 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 |
#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
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
................................................................................
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
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>
................................................................................
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
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>
................................................................................
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
.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
.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
................................................................................
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
.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
.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 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
................................................................................
<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
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
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>DisplayCode {
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
................................................................................
<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> (((SELECT * FROM tab1;)))
sqlite> (((.output stdout)))
}</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
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.
<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>
|
| | | | | | | | | | > | > > | | | | | | | > > | | | | | < < > | | | < > | | | > > > > > |
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 .. 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 ... 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 ... 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 ... 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 ... 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 ... 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 |
#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);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
................................................................................
the command-line shell to pop-up a terminal window running SQLite. Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:
<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite>
}</tcl>
................................................................................
think it is in. Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>
<p>Alternatively, you can create a new database using the default in-memory
storage, then save that database into a disk file using the ".save" command:
<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1.db)))
sqlite>
}</tcl>
................................................................................
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
.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
.exit Exit this program
.explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
With no args, it turns EXPLAIN on.
.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
................................................................................
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 or stdout
.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
.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
Negative values right-justify
sqlite>
}</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>
<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
................................................................................
<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" 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" 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, 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> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</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
................................................................................
<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> (((.mode csv)))
sqlite> (((.once c:/work/dataout.csv)))
sqlite> (((SELECT * FROM tab1;)))
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 ".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".
<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>
|