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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a0826e69769fa73f7a74bb7819cc09f5 |
User & Date: | drh 2014-05-29 14:08:27.165 |
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 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 | #regsub -all \n $body <br>\n body hd_puts $body hd_puts {</pre></blockquote>} } DisplayCode { $ (((sqlite3 ex1))) | | | 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.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 | 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 { | | | | 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.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.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 | 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 | | | | | > | < > | > > | | | | | 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 .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 .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 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 |
︙ | ︙ | |||
388 389 390 391 392 393 394 | <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 | | | | > > | | | | | < | 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" 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 |
︙ | ︙ | |||
590 591 592 593 594 595 596 | <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))) | | | | | | | | | > > > > > | | 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> (((.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> |
︙ | ︙ |