/ Check-in [3a422bb9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to the sqlite3 command-line shell documentation. Ticket #2144. (CVS 3573)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:3a422bb9eedf5357ce4d3bed68981c000eb96ee4
User & Date: drh 2007-01-08 14:31:36
Context
2007-01-08
16:19
Fix another round-off problem in strftime(). Ticket #2153. (CVS 3574) check-in: d49ddc5f user: drh tags: trunk
14:31
Updates to the sqlite3 command-line shell documentation. Ticket #2144. (CVS 3573) check-in: 3a422bb9 user: drh tags: trunk
13:40
Add the $(BEXE) suffix to every mention of "lemon" in the Makefile.in, so that the build will work on OS/2. Ticket #2149. (CVS 3571) check-in: f5989a0b user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code to implement the "sqlite" command line
    13     13   ** utility for accessing SQLite databases.
    14     14   **
    15         -** $Id: shell.c,v 1.157 2006/12/19 18:47:41 drh Exp $
           15  +** $Id: shell.c,v 1.158 2007/01/08 14:31:36 drh Exp $
    16     16   */
    17     17   #include <stdlib.h>
    18     18   #include <string.h>
    19     19   #include <stdio.h>
    20     20   #include <assert.h>
    21     21   #include "sqlite3.h"
    22     22   #include <ctype.h>
................................................................................
   817    817     return rc;
   818    818   }
   819    819   
   820    820   /*
   821    821   ** Text of a help message
   822    822   */
   823    823   static char zHelp[] =
          824  +  ".bail ON|OFF           Stop after hitting an error.  Default OFF\n"
   824    825     ".databases             List names and files of attached databases\n"
   825    826     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
   826    827     ".echo ON|OFF           Turn command echo on or off\n"
   827    828     ".exit                  Exit this program\n"
   828    829     ".explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.\n"
   829    830     ".header(s) ON|OFF      Turn display of headers on or off\n"
   830    831     ".help                  Show this message\n"

Changes to www/sqlite.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: sqlite.tcl,v 1.24 2006/08/19 13:32:05 drh Exp $}
            4  +set rcsid {$Id: sqlite.tcl,v 1.25 2007/01/08 14:31:36 drh Exp $}
     5      5   source common.tcl
     6         -header {sqlite: A command-line access program for SQLite databases}
            6  +header {sqlite3: A command-line access program for SQLite databases}
     7      7   puts {
     8         -<h2>sqlite: A command-line access program for SQLite databases</h2>
            8  +<h2>sqlite3: A command-line access program for SQLite databases</h2>
     9      9   
    10     10   <p>The SQLite library includes a simple command-line utility named
    11         -<b>sqlite</b> that allows the user to manually enter and execute SQL
           11  +<b>sqlite3</b> that allows the user to manually enter and execute SQL
    12     12   commands against an SQLite database.  This document provides a brief
    13         -introduction on how to use <b>sqlite</b>.
           13  +introduction on how to use <b>sqlite3</b>.
    14     14   
    15     15   <h3>Getting Started</h3>
    16     16   
    17         -<p>To start the <b>sqlite</b> program, just type "sqlite" followed by
           17  +<p>To start the <b>sqlite3</b> program, just type "sqlite3" followed by
    18     18   the name the file that holds the SQLite database.  If the file does
    19     19   not exist, a new one is created automatically.
    20         -The <b>sqlite</b> program will
           20  +The <b>sqlite3</b> program will
    21     21   then prompt you to enter SQL.  Type in SQL statements (terminated by a
    22     22   semicolon), press "Enter" and the SQL will be executed.</p>
    23     23   
    24     24   <p>For example, to create a new SQLite database named "ex1" 
    25     25   with a single table named "tbl1", you might do this:</p>
    26     26   }
    27     27   
................................................................................
    35     35     regsub -all { } $body {\&nbsp;} body
    36     36     regsub -all \n $body <br>\n body
    37     37     puts $body
    38     38     puts {</tt></blockquote>}
    39     39   }
    40     40   
    41     41   Code {
    42         -$ (((sqlite ex1)))
    43         -SQLite version 2.0.0
           42  +$ (((sqlite3 ex1)))
           43  +SQLite version 3.3.10
    44     44   Enter ".help" for instructions
    45     45   sqlite> (((create table tbl1(one varchar(10), two smallint);)))
    46     46   sqlite> (((insert into tbl1 values('hello!',10);)))
    47     47   sqlite> (((insert into tbl1 values('goodbye', 20);)))
    48     48   sqlite> (((select * from tbl1;)))
    49     49   hello!|10
    50     50   goodbye|20
    51     51   sqlite>
    52     52   }
    53     53   
    54     54   puts {
    55         -<p>You can terminate the sqlite program by typing your systems
           55  +<p>You can terminate the sqlite3 program by typing your systems
    56     56   End-Of-File character (usually a Control-D) or the interrupt
    57     57   character (usually a Control-C).</p>
    58     58   
    59     59   <p>Make sure you type a semicolon at the end of each SQL command!
    60         -The sqlite looks for a semicolon to know when your SQL command is
    61         -complete.  If you omit the semicolon, sqlite will give you a
           60  +The sqlite3 program looks for a semicolon to know when your SQL command is
           61  +complete.  If you omit the semicolon, sqlite3 will give you a
    62     62   continuation prompt and wait for you to enter more text to be
    63     63   added to the current SQL command.  This feature allows you to
    64     64   enter SQL commands that span multiple lines.  For example:</p>
    65     65   }
    66     66   
    67     67   Code {
    68     68   sqlite> (((CREATE TABLE tbl2 ()))
................................................................................
    81     81   a special table named "sqlite_master".
    82     82   You can execute "SELECT" statements against the
    83     83   special sqlite_master table just like any other table
    84     84   in an SQLite database.  For example:</p>
    85     85   }
    86     86   
    87     87   Code {
    88         -$ (((sqlite ex1)))
    89         -SQlite vresion 2.0.0
           88  +$ (((sqlite3 ex1)))
           89  +SQlite vresion 3.3.10
    90     90   Enter ".help" for instructions
    91     91   sqlite> (((select * from sqlite_master;)))
    92     92       type = table
    93     93       name = tbl1
    94     94   tbl_name = tbl1
    95     95   rootpage = 3
    96     96        sql = create table tbl1(one varchar(10), two smallint)
................................................................................
   110    110   The schema for TEMPORARY tables is not stored in the "sqlite_master" table
   111    111   since TEMPORARY tables are not visible to applications other than the
   112    112   application that created the table.  The schema for TEMPORARY tables
   113    113   is stored in another special table named "sqlite_temp_master".  The
   114    114   "sqlite_temp_master" table is temporary itself.
   115    115   </p>
   116    116   
   117         -<h3>Special commands to sqlite</h3>
          117  +<h3>Special commands to sqlite3</h3>
   118    118   
   119    119   <p>
   120         -Most of the time, sqlite just reads lines of input and passes them
          120  +Most of the time, sqlite3 just reads lines of input and passes them
   121    121   on to the SQLite library for execution.
   122    122   But if an input line begins with a dot ("."), then
   123         -that line is intercepted and interpreted by the sqlite program itself.
          123  +that line is intercepted and interpreted by the sqlite3 program itself.
   124    124   These "dot commands" are typically used to change the output format
   125    125   of queries, or to execute certain prepackaged query statements.
   126    126   </p>
   127    127   
   128    128   <p>
   129    129   For a listing of the available dot commands, you can enter ".help"
   130    130   at any time.  For example:
   131    131   </p>}
   132    132   
   133    133   Code {
   134    134   sqlite> (((.help)))
          135  +.bail ON|OFF           Stop after hitting an error.  Default OFF
   135    136   .databases             List names and files of attached databases
   136         -.dump ?TABLE? ...      Dump the database in a text format
          137  +.dump ?TABLE? ...      Dump the database in an SQL text format
   137    138   .echo ON|OFF           Turn command echo on or off
   138    139   .exit                  Exit this program
   139    140   .explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
   140    141   .header(s) ON|OFF      Turn display of headers on or off
   141    142   .help                  Show this message
          143  +.import FILE TABLE     Import data from FILE into TABLE
   142    144   .indices TABLE         Show names of all indices on TABLE
   143         -.mode MODE             Set mode to one of "line(s)", "column(s)", 
   144         -                       "insert", "list", or "html"
   145         -.mode insert TABLE     Generate SQL insert statements for TABLE
   146         -.nullvalue STRING      Print STRING instead of nothing for NULL data
          145  +.load FILE ?ENTRY?     Load an extension library
          146  +.mode MODE ?TABLE?     Set output mode where MODE is one of:
          147  +                         csv      Comma-separated values
          148  +                         column   Left-aligned columns.  (See .width)
          149  +                         html     HTML <table> code
          150  +                         insert   SQL insert statements for TABLE
          151  +                         line     One value per line
          152  +                         list     Values delimited by .separator string
          153  +                         tabs     Tab-separated values
          154  +                         tcl      TCL list elements
          155  +.nullvalue STRING      Print STRING in place of NULL values
   147    156   .output FILENAME       Send output to FILENAME
   148    157   .output stdout         Send output to the screen
   149    158   .prompt MAIN CONTINUE  Replace the standard prompts
   150    159   .quit                  Exit this program
   151    160   .read FILENAME         Execute SQL in FILENAME
   152    161   .schema ?TABLE?        Show the CREATE statements
   153         -.separator STRING      Change separator string for "list" mode
          162  +.separator STRING      Change separator used by output mode and .import
   154    163   .show                  Show the current values for various settings
   155         -.tables ?PATTERN?      List names of tables matching a pattern
          164  +.tables ?PATTERN?      List names of tables matching a LIKE pattern
   156    165   .timeout MS            Try opening locked tables for MS milliseconds
   157    166   .width NUM NUM ...     Set column widths for "column" mode
   158    167   sqlite> 
   159    168   }
   160    169   
   161    170   puts {
   162    171   <h3>Changing Output Formats</h3>
   163    172   
   164         -<p>The sqlite program is able to show the results of a query
   165         -in five different formats: "line", "column", "list", "html", and "insert".
          173  +<p>The sqlite3 program is able to show the results of a query
          174  +in eight different formats: "csv", "column", "html", "insert",
          175  +"line", "tabs", and "tcl".
   166    176   You can use the ".mode" dot command to switch between these output
   167    177   formats.</p>
   168    178   
   169    179   <p>The default output mode is "list".  In
   170    180   list mode, each record of a query result is written on one line of
   171    181   output and each column within that record is separated by a specific
   172    182   separator string.  The default separator is a pipe symbol ("|").
................................................................................
   283    293   sqlite> (((select * from tbl1;)))
   284    294   INSERT INTO 'new_table' VALUES('hello',10);
   285    295   INSERT INTO 'new_table' VALUES('goodbye',20);
   286    296   sqlite>
   287    297   }
   288    298   
   289    299   puts {
   290         -<p>The last output mode is "html".  In this mode, sqlite writes
          300  +<p>The last output mode is "html".  In this mode, sqlite3 writes
   291    301   the results of the query as an XHTML table.  The beginning
   292    302   &lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
   293    303   all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
   294    304   are.  The html output mode is envisioned as being useful for
   295    305   CGI.</p>
   296    306   }
   297    307   
   298    308   puts {
   299    309   <h3>Writing results to a file</h3>
   300    310   
   301         -<p>By default, sqlite sends query results to standard output.  You
          311  +<p>By default, sqlite3 sends query results to standard output.  You
   302    312   can change this using the ".output" command.  Just put the name of
   303    313   an output file as an argument to the .output command and all subsequent
   304    314   query results will be written to that file.  Use ".output stdout" to
   305    315   begin writing to standard output again.  For example:</p>}
   306    316   
   307    317   Code {
   308    318   sqlite> (((.mode list)))
................................................................................
   315    325   goodbye|20
   316    326   $
   317    327   }
   318    328   
   319    329   puts {
   320    330   <h3>Querying the database schema</h3>
   321    331   
   322         -<p>The sqlite program provides several convenience commands that
          332  +<p>The sqlite3 program provides several convenience commands that
   323    333   are useful for looking at the schema of the database.  There is
   324    334   nothing that these commands do that cannot be done by some other
   325    335   means.  These commands are provided purely as a shortcut.</p>
   326    336   
   327    337   <p>For example, to see a list of the tables in the database, you
   328    338   can enter ".tables".</p>
   329    339   }
................................................................................
   332    342   sqlite> (((.tables)))
   333    343   tbl1
   334    344   tbl2
   335    345   sqlite>
   336    346   }
   337    347   
   338    348   puts {
   339         -<p>The ".tables" command is the same as setting list mode then
          349  +<p>The ".tables" command is similar to setting list mode then
   340    350   executing the following query:</p>
   341    351   
   342    352   <blockquote><pre>
   343         -SELECT name FROM sqlite_master WHERE type='table' 
   344         -UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table'
   345         -ORDER BY name;
          353  +SELECT name FROM sqlite_master 
          354  +WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
          355  +UNION ALL 
          356  +SELECT name FROM sqlite_temp_master 
          357  +WHERE type IN ('table','view') 
          358  +ORDER BY 1
   346    359   </pre></blockquote>
   347    360   
   348         -<p>In fact, if you look at the source code to the sqlite program
          361  +<p>In fact, if you look at the source code to the sqlite3 program
   349    362   (found in the source tree in the file src/shell.c) you'll find
   350    363   exactly the above query.</p>
   351    364   
   352    365   <p>The ".indices" command works in a similar way to list all of
   353    366   the indices for a particular table.  The ".indices" command takes
   354    367   a single argument which is the name of the table for which the
   355    368   indices are desired.  Last, but not least, is the ".schema" command.
................................................................................
   391    404   <p>Or, if you give an argument to ".schema" because you only
   392    405   want the schema for a single table, the query looks like this:</p>
   393    406   
   394    407   <blockquote><pre>
   395    408   SELECT sql FROM
   396    409      (SELECT * FROM sqlite_master UNION ALL
   397    410       SELECT * FROM sqlite_temp_master)
   398         -WHERE tbl_name LIKE '%s' AND type!='meta'
   399         -ORDER BY type DESC, name
          411  +WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
          412  +ORDER BY substr(type,2,1), name
          413  +</pre></blockquote>
          414  +
          415  +<p>
          416  +You can supply an argument to the .schema command.  If you do, the
          417  +query looks like this:
          418  +</p>
          419  +
          420  +<blockquote><pre>
          421  +SELECT sql FROM
          422  +   (SELECT * FROM sqlite_master UNION ALL
          423  +    SELECT * FROM sqlite_temp_master)
          424  +WHERE tbl_name LIKE '%s'
          425  +  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
          426  +ORDER BY substr(type,2,1), name
   400    427   </pre></blockquote>
   401    428   
   402         -<p>The <b>%s</b> in the query above is replaced by the argument
   403         -to ".schema", of course.  Notice that the argument to the ".schema"
   404         -command appears to the right of an SQL LIKE operator.  So you can
   405         -use wildcards in the name of the table.  For example, to get the
   406         -schema for all tables whose names contain the character string
   407         -"abc" you could enter:</p>}
          429  +<p>The "%s" in the query is replace by your argument.  This allows you
          430  +to view the schema for some subset of the database.</p>
          431  +}
   408    432   
   409    433   Code {
   410    434   sqlite> (((.schema %abc%)))
   411    435   }
   412    436   
   413    437   puts {
   414    438   <p>
................................................................................
   432    456   }
   433    457   
   434    458   puts {
   435    459   <h3>Converting An Entire Database To An ASCII Text File</h3>
   436    460   
   437    461   <p>Use the ".dump" command to convert the entire contents of a
   438    462   database into a single ASCII text file.  This file can be converted
   439         -back into a database by piping it back into <b>sqlite</b>.</p>
          463  +back into a database by piping it back into <b>sqlite3</b>.</p>
   440    464   
   441    465   <p>A good way to make an archival copy of a database is this:</p>
   442    466   }
   443    467   
   444    468   Code {
   445         -$ (((echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz)))
          469  +$ (((echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz)))
   446    470   }
   447    471   
   448    472   puts {
   449    473   <p>This generates a file named <b>ex1.dump.gz</b> that contains everything
   450    474   you need to reconstruct the database at a later time, or on another
   451    475   machine.  To reconstruct the database, just type:</p>
   452    476   }
   453    477   
   454    478   Code {
   455         -$ (((zcat ex1.dump.gz | sqlite ex2)))
          479  +$ (((zcat ex1.dump.gz | sqlite3 ex2)))
   456    480   }
   457    481   
   458    482   puts {
   459         -<p>The text format used is the same as used by
   460         -<a href="http://www.postgresql.org/">PostgreSQL</a>, so you
          483  +<p>The text format is pure SQL so you
   461    484   can also use the .dump command to export an SQLite database
   462         -into a PostgreSQL database.  Like this:</p>
          485  +into other popular SQL database engines.  Like this:</p>
   463    486   }
   464    487   
   465    488   Code {
   466    489   $ (((createdb ex2)))
   467         -$ (((echo '.dump' | sqlite ex1 | psql ex2)))
   468         -}
   469         -
   470         -puts {
   471         -<p>You can almost (but not quite) go the other way and export
   472         -a PostgreSQL database into SQLite using the <b>pg_dump</b> utility.
   473         -Unfortunately, when <b>pg_dump</b> writes the database schema information,
   474         -it uses some SQL syntax that SQLite does not understand.
   475         -So you cannot pipe the output of <b>pg_dump</b> directly 
   476         -into <b>sqlite</b>.
   477         -But if you can recreate the
   478         -schema separately, you can use <b>pg_dump</b> with the <b>-a</b>
   479         -option to list just the data
   480         -of a PostgreSQL database and import that directly into SQLite.</p>
   481         -}
   482         -
   483         -Code {
   484         -$ (((sqlite ex3 <schema.sql)))
   485         -$ (((pg_dump -a ex2 | sqlite ex3)))
          490  +$ (((sqlite3 ex1 .dump | psql ex2)))
   486    491   }
   487    492   
   488    493   puts {
   489    494   <h3>Other Dot Commands</h3>
   490    495   
   491    496   <p>The ".explain" dot command can be used to set the output mode
   492    497   to "column" and to set the column widths to values that are reasonable
................................................................................
   517    522   12    Delete        0      0                  
   518    523   13    Goto          0      11                 
   519    524   14    ListClose     0      0                  
   520    525   }
   521    526   
   522    527   puts {
   523    528   
   524         -<p>The ".timeout" command sets the amount of time that the <b>sqlite</b>
          529  +<p>The ".timeout" command sets the amount of time that the <b>sqlite3</b>
   525    530   program will wait for locks to clear on files it is trying to access
   526    531   before returning an error.  The default value of the timeout is zero so
   527    532   that an error is returned immediately if any needed database table or
   528    533   index is locked.</p>
   529    534   
   530    535   <p>And finally, we mention the ".exit" command which causes the
   531         -sqlite program to exit.</p>
          536  +sqlite3 program to exit.</p>
   532    537   
   533         -<h3>Using sqlite in a shell script</h3>
          538  +<h3>Using sqlite3 in a shell script</h3>
   534    539   
   535    540   <p>
   536         -One way to use sqlite in a shell script is to use "echo" or
   537         -"cat" to generate a sequence of commands in a file, then invoke sqlite 
          541  +One way to use sqlite3 in a shell script is to use "echo" or
          542  +"cat" to generate a sequence of commands in a file, then invoke sqlite3
   538    543   while redirecting input from the generated command file.  This
   539    544   works fine and is appropriate in many circumstances.  But as
   540         -an added convenience, sqlite allows a single SQL command to be
          545  +an added convenience, sqlite3 allows a single SQL command to be
   541    546   entered on the command line as a second argument after the
   542         -database name.  When the sqlite program is launched with two
          547  +database name.  When the sqlite3 program is launched with two
   543    548   arguments, the second argument is passed to the SQLite library
   544    549   for processing, the query results are printed on standard output
   545    550   in list mode, and the program exits.  This mechanism is designed
   546         -to make sqlite easy to use in conjunction with programs like
          551  +to make sqlite3 easy to use in conjunction with programs like
   547    552   "awk".  For example:</p>}
   548    553   
   549    554   Code {
   550         -$ (((sqlite ex1 'select * from tbl1' |)))
          555  +$ (((sqlite3 ex1 'select * from tbl1' |)))
   551    556   > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
   552    557   <tr><td>hello<td>10
   553    558   <tr><td>goodbye<td>20
   554    559   $
   555    560   }
   556    561   
   557    562   puts {
   558    563   <h3>Ending shell commands</h3>
   559    564   
   560    565   <p>
   561    566   SQLite commands are normally terminated by a semicolon.  In the shell 
   562    567   you can also use the word "GO" (case-insensitive) or a slash character 
   563    568   "/" on a line by itself to end a command.  These are used by SQL Server 
   564         -and Oracle, respectively.  These won't work in <b>sqlite_exec()</b>, 
          569  +and Oracle, respectively.  These won't work in <b>sqlite3_exec()</b>, 
   565    570   because the shell translates these into a semicolon before passing them 
   566    571   to that function.</p>
   567    572   }
   568    573   
   569    574   puts {
   570         -<h3>Compiling the sqlite program from sources</h3>
          575  +<h3>Compiling the sqlite3 program from sources</h3>
   571    576   
   572    577   <p>
   573         -The sqlite program is built automatically when you compile the
   574         -sqlite library.  Just get a copy of the source tree, run
          578  +The sqlite3 program is built automatically when you compile the
          579  +SQLite library.  Just get a copy of the source tree, run
   575    580   "configure" and then "make".</p>
   576    581   }
   577    582   footer $rcsid