Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update the cli.html document and the change log for the .system and the .once enhancements to the command-line shell. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a0826e69769fa73f7a74bb7819cc09f5 |
| User & Date: | drh 2014-05-29 14:08:27 |
Context
|
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
Changes to pages/changes.in.
31 31 <li>Added new [URI query parameters] "nolock" and "immutable". 32 32 <li>Use less memory by not remembering CHECK constraints on read-only 33 33 database connections. 34 34 <li>Enable the [or-connected-terms | OR optimization] for [WITHOUT ROWID] tables. 35 35 <li>Render expressions of the form "x IN (?)" (with a single value in 36 36 the list on the right-hand side of the IN operator) as if they where "x==?", 37 37 Similarly optimize "x NOT IN (?)" 38 +<li>Add the ".system" and ".once" commands to the [command-line shell]. 38 39 <p><b>Bug Fixes:</b> 39 40 <li>OFFSET clause ignored on queries without a FROM clause. 40 41 Ticket [http://www.sqlite.org/src/info/07d6a0453d | 07d6a0453d] 41 42 <li>Assertion fault on queries involving expressions of the form 42 43 "x IN (?)". Ticket [http://www.sqlite.org/src/info/e39d032577|e39d032577]. 43 44 <li>Incorrect column datatype reported. 44 45 Ticket [http://www.sqlite.org/src/info/a8a0d2996a | a8a0d2996a]
Changes to pages/cli.in.
41 41 #regsub -all \n $body <br>\n body 42 42 hd_puts $body 43 43 hd_puts {</pre></blockquote>} 44 44 } 45 45 46 46 DisplayCode { 47 47 $ (((sqlite3 ex1))) 48 -SQLite version 3.8.4 2014-02-11 16:24:34 48 +SQLite version 3.8.5 2014-05-29 12:36:14 49 49 Enter ".help" for usage hints. 50 50 sqlite> (((create table tbl1(one varchar(10), two smallint);))) 51 51 sqlite> (((insert into tbl1 values('hello!',10);))) 52 52 sqlite> (((insert into tbl1 values('goodbye', 20);))) 53 53 sqlite> (((select * from tbl1;))) 54 54 hello!|10 55 55 goodbye|20 ................................................................................ 84 84 the command-line shell to pop-up a terminal window running SQLite. Note, 85 85 however, that by default this SQLite session is using an in-memory database, 86 86 not a file on disk, and so all changes will be lost when the session exits. 87 87 To use a persistent disk file as the database, enter the ".open" command 88 88 immediately after the terminal window starts up: 89 89 90 90 <tcl>DisplayCode { 91 -SQLite version 3.8.4 2014-02-11 16:24:34 91 +SQLite version 3.8.5 2014-05-29 12:36:14 92 92 Enter ".help" for usage hints. 93 93 Connected to a transient in-memory database. 94 94 Use ".open FILENAME" to reopen on a persistent database. 95 95 sqlite> (((.open ex1.db))) 96 96 sqlite> 97 97 }</tcl> 98 98 ................................................................................ 102 102 think it is in. Use forward-slashes as the directory separator character. 103 103 In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p> 104 104 105 105 <p>Alternatively, you can create a new database using the default in-memory 106 106 storage, then save that database into a disk file using the ".save" command: 107 107 108 108 <tcl>DisplayCode { 109 -SQLite version 3.8.4 2014-02-11 16:24:34 109 +SQLite version 3.8.5 2014-05-29 12:36:14 110 110 Enter ".help" for usage hints. 111 111 Connected to a transient in-memory database. 112 112 Use ".open FILENAME" to reopen on a persistent database. 113 113 sqlite> [[[... many SQL commands omitted ...]]] 114 114 sqlite> (((.save ex1.db))) 115 115 sqlite> 116 116 }</tcl> ................................................................................ 135 135 For a listing of the available dot commands, you can enter ".help" 136 136 at any time. For example: 137 137 </p> 138 138 139 139 <tcl>DisplayCode { 140 140 sqlite> (((.help))) 141 141 .backup ?DB? FILE Backup DB (default "main") to FILE 142 -.bail ON|OFF Stop after hitting an error. Default OFF 142 +.bail on|off Stop after hitting an error. Default OFF 143 143 .clone NEWDB Clone data into NEWDB from the existing database 144 144 .databases List names and files of attached databases 145 145 .dump ?TABLE? ... Dump the database in an SQL text format 146 146 If TABLE specified, only dump tables matching 147 147 LIKE pattern TABLE. 148 -.echo ON|OFF Turn command echo on or off 148 +.echo on|off Turn command echo on or off 149 149 .exit Exit this program 150 -.explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off. 150 +.explain ?on|off? Turn output mode suitable for EXPLAIN on or off. 151 151 With no args, it turns EXPLAIN on. 152 -.header(s) ON|OFF Turn display of headers on or off 152 +.headers on|off Turn display of headers on or off 153 153 .help Show this message 154 154 .import FILE TABLE Import data from FILE into TABLE 155 155 .indices ?TABLE? Show names of all indices 156 156 If TABLE specified, only show indices for tables 157 157 matching LIKE pattern TABLE. 158 158 .load FILE ?ENTRY? Load an extension library 159 159 .log FILE|off Turn logging on or off. FILE can be stderr/stdout ................................................................................ 163 163 html HTML <table> code 164 164 insert SQL insert statements for TABLE 165 165 line One value per line 166 166 list Values delimited by .separator string 167 167 tabs Tab-separated values 168 168 tcl TCL list elements 169 169 .nullvalue STRING Use STRING in place of NULL values 170 +.once FILENAME Output for the next SQL command only to FILENAME 170 171 .open ?FILENAME? Close existing database and reopen FILENAME 171 -.output FILENAME Send output to FILENAME 172 -.output stdout Send output to the screen 172 +.output ?FILENAME? Send output to FILENAME or stdout 173 173 .print STRING... Print literal STRING 174 174 .prompt MAIN CONTINUE Replace the standard prompts 175 175 .quit Exit this program 176 176 .read FILENAME Execute SQL in FILENAME 177 177 .restore ?DB? FILE Restore content of DB (default "main") from FILE 178 178 .save FILE Write in-memory database into FILE 179 179 .schema ?TABLE? Show the CREATE statements 180 180 If TABLE specified, only show tables matching 181 181 LIKE pattern TABLE. 182 182 .separator STRING Change separator used by output mode and .import 183 +.shell CMD ARGS... Run CMD ARGS... in a system shell 183 184 .show Show the current values for various settings 184 -.stats ON|OFF Turn stats on or off 185 +.stats on|off Turn stats on or off 186 +.system CMD ARGS... Run CMD ARGS... in a system shell 185 187 .tables ?TABLE? List names of tables 186 188 If TABLE specified, only list tables matching 187 189 LIKE pattern TABLE. 188 190 .timeout MS Try opening locked tables for MS milliseconds 191 +.timer on|off Turn SQL timer on or off 189 192 .trace FILE|off Output each SQL statement as it is run 190 193 .vfsname ?AUX? Print the name of the VFS stack 191 194 .width NUM1 NUM2 ... Set column widths for "column" mode 192 -.timer ON|OFF Turn the CPU timer measurement on or off 195 + Negative values right-justify 193 196 sqlite> 194 197 }</tcl> 195 198 196 199 <h3>Rules for "dot-commands"</h3> 197 200 198 -<p>Ordinary SQL statements are very much free-form, can be 201 +<p>Ordinary SQL statements are free-form, and can be 199 202 spread across multiple lines, and can have whitespace and 200 -comments anywhere. But dot-commands are not like that. 201 -The dot-commands are more restrictive: 203 +comments anywhere. But dot-commands are 204 +more restrictive: 202 205 203 206 <ul> 204 207 <li>A dot-command must begin with the "." at the left margin 205 208 with no preceding whitespace. 206 209 <li>The dot-command must be entirely contained on a single input line. 207 210 <li>A dot-command cannot occur in the middle of an ordinary SQL 208 211 statement. In other words, a dot-command cannot occur at a ................................................................................ 388 391 389 392 <p>Notice how the shell changes the indentation of some opcodes to 390 393 help show the loop structure of the VDBE program. 391 394 392 395 <h3>Writing results to a file</h3> 393 396 394 397 <p>By default, sqlite3 sends query results to standard output. You 395 -can change this using the ".output" command. Just put the name of 396 -an output file as an argument to the .output command and all subsequent 397 -query results will be written to that file. Use ".output stdout" to 398 +can change this using the ".output" and ".once" commands. Just put 399 +the name of an output file as an argument to .output and all subsequent 400 +query results will be written to that file. Or use the .once command 401 +instead of .output and output will only be redirected for the single next 402 +command before returning the console. Use .output with no arguments to 398 403 begin writing to standard output again. For example:</p> 399 404 400 405 <tcl>DisplayCode { 401 406 sqlite> (((.mode list))) 402 407 sqlite> (((.separator |))) 403 408 sqlite> (((.output test_file_1.txt))) 404 409 sqlite> (((select * from tbl1;))) ................................................................................ 405 410 sqlite> (((.exit))) 406 411 $ (((cat test_file_1.txt))) 407 412 hello|10 408 413 goodbye|20 409 414 $ 410 415 }</tcl> 411 416 412 -<p>If the first character of the ".output" filename is a pipe symbol 413 -("|") then the remaining characters are treated as a command and the 417 +<p>If the first character of the ".output" or ".once" filename is a pipe 418 +symbol ("|") then the remaining characters are treated as a command and the 414 419 output is sent to that command. This makes it easy to pipe the results 415 -of a query into some other process. For example, a Mac, the 416 -"open -f" command opens a text editor to display the content that 420 +of a query into some other process. For example, the 421 +"open -f" command on a Mac opens a text editor to display the content that 417 422 it reads from standard input. So to see the results of a query 418 423 in a text editor, one could type:</p> 419 424 420 425 <tcl>DisplayCode { 421 -sqlite3> (((.output '|open -f'))) 426 +sqlite3> (((.once '|open -f'))) 422 427 sqlite3> (((SELECT * FROM bigTable;))) 423 -sqlite3> (((.output stdout))) 424 428 }</tcl> 425 429 426 430 <h3>Querying the database schema</h3> 427 431 428 432 <p>The sqlite3 program provides several convenience commands that 429 433 are useful for looking at the schema of the database. There is 430 434 nothing that these commands do that cannot be done by some other ................................................................................ 590 594 591 595 <p>To export an SQLite table (or part of a table) as CSV, simply set 592 596 the "mode" to "csv" and then run a query to extract the desired rows 593 597 of the table. 594 598 595 599 <tcl>DisplayCode { 596 600 sqlite> (((.header on))) 597 -sqlite> (((.output c:/work/dataout.csv))) 598 601 sqlite> (((.mode csv))) 602 +sqlite> (((.once c:/work/dataout.csv))) 599 603 sqlite> (((SELECT * FROM tab1;))) 600 -sqlite> (((.output stdout))) 604 +sqlite> (((.system c:/work/dataout.csv))) 601 605 }</tcl> 602 606 603 607 <p>In the example above, the ".header on" line causes column labels to 604 608 be printed as the first row of output. This means that the first row of 605 609 the resulting CSV file will contain column labels. If column labels are 606 610 not desired, set ".header off" instead. (The ".header off" setting is 607 611 the default and can be omitted if the headers have not been previously 608 612 turned on.) 609 613 610 -<p>The line ".output <i>FILENAME</i>" causes all query output to go into 614 +<p>The line ".once <i>FILENAME</i>" causes all query output to go into 611 615 the named file instead of being printed on the console. In the example 612 616 above, that line causes the CSV content to be written into a file named 613 -"C:/work/dataout.csv". The final line of the example 614 -(the ".output stdout" line) causes subsequent output to be written to 615 -the console again. The use of ".output" is not required for CSV export, 616 -but it is often more convenient than trying to copy/paste the resulting 617 -CSV text from the console. 617 +"C:/work/dataout.csv". 618 + 619 +<p>The final line of the example (the ".system c:/work/dataout.csv") 620 +has the same effect as double-clicking on the c:/work/dataout.csv file 621 +in windows. This will typically bring up a spreadsheet program to display 622 +the CSV file. That command only works as shown on Windows. The 623 +equivalent line on a Mac would be ".system open /work/dataout.csv". 624 +On Linux and other unix systems you will need to enter something like 625 +".system libreoffice /work/dataout.csv", substituting your perferred 626 +CSV viewing program for "libreoffice". 618 627 619 628 <h3>Converting An Entire Database To An ASCII Text File</h3> 620 629 621 630 <p>Use the ".dump" command to convert the entire contents of a 622 631 database into a single ASCII text file. This file can be converted 623 632 back into a database by piping it back into <b>sqlite3</b>.</p> 624 633