Documentation Source Text

Check-in [a0826e6976]
Login

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: a0826e69769fa73f7a74bb7819cc09f56fa065e1
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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