Documentation Source Text

Check-in [a6ea023899]
Login

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

Overview
Comment:Rename the "sqlite.html" page to "cli.html". Keep an "sqlite.html" page that redirects to "cli.html".
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a6ea023899ca8c95feb03a858b029c33f863fac8
User & Date: drh 2014-04-22 00:32:41
Context
2014-04-25
20:47
Fix more typos in the foreignkey.html document. check-in: 25bc66990e user: drh tags: version-3.8.4.3
2014-04-22
01:06
Update the version number to 3.8.5. Add a change log. check-in: aabef1a296 user: drh tags: trunk
00:32
Rename the "sqlite.html" page to "cli.html". Keep an "sqlite.html" page that redirects to "cli.html". check-in: a6ea023899 user: drh tags: trunk
2014-04-21
18:20
Update the command-line shell documentation with a discussion of CSV import and export. check-in: 070bfddab9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/c_interface.in.

  1100   1100   
  1101   1101   <h3>6.0 Usage Examples</h3>
  1102   1102   
  1103   1103   <p>For examples of how the SQLite C/C++ interface can be used,
  1104   1104   refer to the source code for the <b>sqlite</b> program in the
  1105   1105   file <b>src/shell.c</b> of the source tree.
  1106   1106   Additional information about sqlite is available at
  1107         -<a href="sqlite.html">sqlite.html</a>.
         1107  +<a href="cli.html">cli.html</a>.
  1108   1108   See also the sources to the Tcl interface for SQLite in
  1109   1109   the source file <b>src/tclsqlite.c</b>.</p>

Name change from pages/sqlite.in to pages/cli.in.

     1      1   <title>Command Line Shell For SQLite</title>
     2         -<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell}</tcl>
            2  +<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
            3  +     {command-line interface} </tcl>
     3      4   
     4      5   <h1 align=center>
     5      6   Command Line Shell For SQLite
     6      7   </h1>
     7      8   
     8      9   <p>The SQLite project provides a simple command-line utility named
     9     10   <b>sqlite3</b> (or <b>sqlite3.exe</b> on windows)
................................................................................
    23     24   message then prompt you to enter SQL.  Type in SQL statements (terminated
    24     25   by a semicolon), press "Enter" and the SQL will be executed.</p>
    25     26   
    26     27   <p>For example, to create a new SQLite database named "ex1" 
    27     28   with a single table named "tbl1", you might do this:</p>
    28     29   
    29     30   <tcl>
    30         -proc Code {body} {
           31  +proc DisplayCode {body} {
    31     32     hd_puts {<blockquote><pre>}
    32     33     regsub -all {&} [string trim $body] {\&amp;} body
    33     34     regsub -all {>} $body {\&gt;} body
    34     35     regsub -all {<} $body {\&lt;} body
    35     36     regsub -all {\(\(\(} $body {<b>} body
    36     37     regsub -all {\)\)\)} $body {</b>} body
    37     38     regsub -all {\[\[\[} $body {<i>} body
................................................................................
    38     39     regsub -all {\]\]\]} $body {</i>} body
    39     40     #regsub -all { } $body {\&nbsp;} body
    40     41     #regsub -all \n $body <br>\n body
    41     42     hd_puts $body
    42     43     hd_puts {</pre></blockquote>}
    43     44   }
    44     45   
    45         -Code {
           46  +DisplayCode {
    46     47   $ (((sqlite3 ex1)))
    47     48   SQLite version 3.8.4 2014-02-11 16:24:34
    48     49   Enter ".help" for usage hints.
    49     50   sqlite> (((create table tbl1(one varchar(10), two smallint);)))
    50     51   sqlite> (((insert into tbl1 values('hello!',10);)))
    51     52   sqlite> (((insert into tbl1 values('goodbye', 20);)))
    52     53   sqlite> (((select * from tbl1;)))
................................................................................
    64     65   The sqlite3 program looks for a semicolon to know when your SQL command is
    65     66   complete.  If you omit the semicolon, sqlite3 will give you a
    66     67   continuation prompt and wait for you to enter more text to be
    67     68   added to the current SQL command.  This feature allows you to
    68     69   enter SQL commands that span multiple lines.  For example:</p>
    69     70   
    70     71   
    71         -<tcl>Code {
           72  +<tcl>DisplayCode {
    72     73   sqlite> (((CREATE TABLE tbl2 ()))
    73     74      ...> (((  f1 varchar(30) primary key,)))
    74     75      ...> (((  f2 text,)))
    75     76      ...> (((  f3 real)))
    76     77      ...> ((();)))
    77     78   sqlite> 
    78     79   }</tcl>
................................................................................
    82     83   <p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
    83     84   the command-line shell to pop-up a terminal window running SQLite.  Note,
    84     85   however, that by default this SQLite session is using an in-memory database,
    85     86   not a file on disk, and so all changes will be lost when the session exits.
    86     87   To use a persistent disk file as the database, enter the ".open" command
    87     88   immediately after the terminal window starts up:
    88     89   
    89         -<tcl>Code {
           90  +<tcl>DisplayCode {
    90     91   SQLite version 3.8.4 2014-02-11 16:24:34
    91     92   Enter ".help" for usage hints.
    92     93   Connected to a transient in-memory database.
    93     94   Use ".open FILENAME" to reopen on a persistent database.
    94     95   sqlite> (((.open ex1.db)))
    95     96   sqlite> 
    96     97   }</tcl>
................................................................................
   100    101   use a full pathname to ensure that the file is in the directory that you
   101    102   think it is in.  Use forward-slashes as the directory separator character.
   102    103   In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>
   103    104   
   104    105   <p>Alternatively, you can create a new database using the default in-memory
   105    106   storage, then save that database into a disk file using the ".save" command:
   106    107   
   107         -<tcl>Code {
          108  +<tcl>DisplayCode {
   108    109   SQLite version 3.8.4 2014-02-11 16:24:34
   109    110   Enter ".help" for usage hints.
   110    111   Connected to a transient in-memory database.
   111    112   Use ".open FILENAME" to reopen on a persistent database.
   112    113   sqlite> [[[... many SQL commands omitted ...]]]
   113    114   sqlite> (((.save ex1.db)))
   114    115   sqlite> 
................................................................................
   131    132   </p>
   132    133   
   133    134   <p>
   134    135   For a listing of the available dot commands, you can enter ".help"
   135    136   at any time.  For example:
   136    137   </p>
   137    138   
   138         -<tcl>Code {
          139  +<tcl>DisplayCode {
   139    140   sqlite> (((.help)))
   140    141   .backup ?DB? FILE      Backup DB (default "main") to FILE
   141    142   .bail ON|OFF           Stop after hitting an error.  Default OFF
   142    143   .clone NEWDB           Clone data into NEWDB from the existing database
   143    144   .databases             List names and files of attached databases
   144    145   .dump ?TABLE? ...      Dump the database in an SQL text format
   145    146                            If TABLE specified, only dump tables matching
................................................................................
   225    226   <p>The default output mode is "list".  In
   226    227   list mode, each record of a query result is written on one line of
   227    228   output and each column within that record is separated by a specific
   228    229   separator string.  The default separator is a pipe symbol ("|").
   229    230   List mode is especially useful when you are going to send the output
   230    231   of a query to another program (such as AWK) for additional processing.</p>
   231    232   
   232         -<tcl>Code {
          233  +<tcl>DisplayCode {
   233    234   sqlite> (((.mode list)))
   234    235   sqlite> (((select * from tbl1;)))
   235    236   hello|10
   236    237   goodbye|20
   237    238   sqlite>
   238    239   }</tcl>
   239    240   
   240    241   <p>You can use the ".separator" dot command to change the separator
   241    242   for list mode.  For example, to change the separator to a comma and
   242    243   a space, you could do this:</p>
   243    244   
   244         -<tcl>Code {
          245  +<tcl>DisplayCode {
   245    246   sqlite> (((.separator ", ")))
   246    247   sqlite> (((select * from tbl1;)))
   247    248   hello, 10
   248    249   goodbye, 20
   249    250   sqlite>
   250    251   }</tcl>
   251    252   
   252    253   <p>In "line" mode, each column in a row of the database
   253    254   is shown on a line by itself.  Each line consists of the column
   254    255   name, an equal sign and the column data.  Successive records are
   255    256   separated by a blank line.  Here is an example of line mode
   256    257   output:</p>
   257    258   
   258         -<tcl>Code {
          259  +<tcl>DisplayCode {
   259    260   sqlite> (((.mode line)))
   260    261   sqlite> (((select * from tbl1;)))
   261    262   one = hello
   262    263   two = 10
   263    264   
   264    265   one = goodbye
   265    266   two = 20
   266    267   sqlite>
   267    268   }</tcl>
   268    269   
   269    270   <p>In column mode, each record is shown on a separate line with the
   270    271   data aligned in columns.  For example:</p>
   271    272   
   272         -<tcl>Code {
          273  +<tcl>DisplayCode {
   273    274   sqlite> (((.mode column)))
   274    275   sqlite> (((select * from tbl1;)))
   275    276   one         two       
   276    277   ----------  ----------
   277    278   hello       10        
   278    279   goodbye     20        
   279    280   sqlite>
   280    281   }</tcl>
   281    282   
   282    283   <p>By default, each column is at least 10 characters wide. 
   283    284   Data that is too wide to fit in a column is truncated.  You can
   284    285   adjust the column widths using the ".width" command.  Like this:</p>
   285    286   
   286         -<tcl>Code {
          287  +<tcl>DisplayCode {
   287    288   sqlite> (((.width 12 6)))
   288    289   sqlite> (((select * from tbl1;)))
   289    290   one           two   
   290    291   ------------  ------
   291    292   hello         10    
   292    293   goodbye       20    
   293    294   sqlite>
................................................................................
   310    311   right-justified columns.</p>
   311    312   
   312    313   <p>The column labels that appear on the first two lines of output
   313    314   can be turned on and off using the ".header" dot command.  In the
   314    315   examples above, the column labels are on.  To turn them off you
   315    316   could do this:</p>
   316    317   
   317         -<tcl>Code {
          318  +<tcl>DisplayCode {
   318    319   sqlite> (((.header off)))
   319    320   sqlite> (((select * from tbl1;)))
   320    321   hello         10    
   321    322   goodbye       20    
   322    323   sqlite>
   323    324   }</tcl>
   324    325   
................................................................................
   326    327   is formatted to look like SQL INSERT statements.  You can use insert
   327    328   mode to generate text that can later be used to input data into a 
   328    329   different database.</p>
   329    330   
   330    331   <p>When specifying insert mode, you have to give an extra argument
   331    332   which is the name of the table to be inserted into.  For example:</p>
   332    333   
   333         -<tcl>Code {
          334  +<tcl>DisplayCode {
   334    335   sqlite> (((.mode insert new_table)))
   335    336   sqlite> (((select * from tbl1;)))
   336    337   INSERT INTO 'new_table' VALUES('hello',10);
   337    338   INSERT INTO 'new_table' VALUES('goodbye',20);
   338    339   sqlite>
   339    340   }</tcl>
   340    341   
................................................................................
   351    352   for looking at the output of an EXPLAIN command.  The EXPLAIN command
   352    353   is an SQLite-specific SQL extension that is useful for debugging.  If any
   353    354   regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and
   354    355   analyzed but is not executed.  Instead, the sequence of virtual machine
   355    356   instructions that would have been used to execute the SQL command are
   356    357   returned like a query result.  For example:</p>
   357    358   
   358         -<tcl>Code {
          359  +<tcl>DisplayCode {
   359    360   sqlite> (((.explain)))
   360    361   sqlite> (((explain delete from tbl1 where two<20;)))
   361    362   addr  opcode         p1    p2    p3    p4             p5  comment      
   362    363   ----  -------------  ----  ----  ----  -------------  --  -------------
   363    364   0     Trace          0     0     0                    00               
   364    365   1     Goto           0     18    0                    00               
   365    366   2     Null           0     1     0                    00  r[1]=NULL    
................................................................................
   392    393   
   393    394   <p>By default, sqlite3 sends query results to standard output.  You
   394    395   can change this using the ".output" command.  Just put the name of
   395    396   an output file as an argument to the .output command and all subsequent
   396    397   query results will be written to that file.  Use ".output stdout" to
   397    398   begin writing to standard output again.  For example:</p>
   398    399   
   399         -<tcl>Code {
          400  +<tcl>DisplayCode {
   400    401   sqlite> (((.mode list)))
   401    402   sqlite> (((.separator |)))
   402    403   sqlite> (((.output test_file_1.txt)))
   403    404   sqlite> (((select * from tbl1;)))
   404    405   sqlite> (((.exit)))
   405    406   $ (((cat test_file_1.txt)))
   406    407   hello|10
................................................................................
   412    413   ("|") then the remaining characters are treated as a command and the
   413    414   output is sent to that command.  This makes it easy to pipe the results
   414    415   of a query into some other process.  For example, a Mac, the 
   415    416   "open -f" command opens a text editor to display the content that
   416    417   it reads from standard input.  So to see the results of a query
   417    418   in a text editor, one could type:</p>
   418    419   
   419         -<tcl>Code {
          420  +<tcl>DisplayCode {
   420    421   sqlite3> (((.output '|open -f')))
   421    422   sqlite3> (((SELECT * FROM bigTable;)))
   422    423   sqlite3> (((.output stdout)))
   423    424   }</tcl>
   424    425   
   425    426   <h3>Querying the database schema</h3>
   426    427   
................................................................................
   429    430   nothing that these commands do that cannot be done by some other
   430    431   means.  These commands are provided purely as a shortcut.</p>
   431    432   
   432    433   <p>For example, to see a list of the tables in the database, you
   433    434   can enter ".tables".</p>
   434    435   
   435    436   
   436         -<tcl>Code {
          437  +<tcl>DisplayCode {
   437    438   sqlite> (((.tables)))
   438    439   tbl1
   439    440   tbl2
   440    441   sqlite>
   441    442   }</tcl>
   442    443   
   443    444   
................................................................................
   463    464   indices are desired.  Last, but not least, is the ".schema" command.
   464    465   With no arguments, the ".schema" command shows the original CREATE TABLE
   465    466   and CREATE INDEX statements that were used to build the current database.
   466    467   If you give the name of a table to ".schema", it shows the original
   467    468   CREATE statement used to make that table and all if its indices.
   468    469   We have:</p>
   469    470   
   470         -<tcl>Code {
          471  +<tcl>DisplayCode {
   471    472   sqlite> (((.schema)))
   472    473   create table tbl1(one varchar(10), two smallint)
   473    474   CREATE TABLE tbl2 (
   474    475     f1 varchar(30) primary key,
   475    476     f2 text,
   476    477     f3 real
   477    478   )
................................................................................
   521    522   ORDER BY substr(type,2,1), name
   522    523   </pre></blockquote>
   523    524   
   524    525   <p>The "%s" in the query is replace by your argument.  This allows you
   525    526   to view the schema for some subset of the database.</p>
   526    527   
   527    528   
   528         -<tcl>Code {
          529  +<tcl>DisplayCode {
   529    530   sqlite> (((.schema %abc%)))
   530    531   }</tcl>
   531    532   
   532    533   
   533    534   <p>
   534    535   Along these same lines,
   535    536   the ".table" command also accepts a pattern as its first argument.
................................................................................
   542    543   the current connection.  There will always be at least 2.  The first
   543    544   one is "main", the original database opened.  The second is "temp",
   544    545   the database used for temporary tables. There may be additional 
   545    546   databases listed for databases attached using the ATTACH statement.
   546    547   The first output column is the name the database is attached with, 
   547    548   and the second column is the filename of the external file.</p>
   548    549   
   549         -<tcl>Code {
          550  +<tcl>DisplayCode {
   550    551   sqlite> (((.databases)))
   551    552   }</tcl>
   552    553   
   553    554   
   554    555   <tcl>hd_fragment csv {CSV import}</tcl>
   555    556   <h3>CSV Import</h3>
   556    557   
................................................................................
   559    560   name of the disk file from which CSV data is to be read and the name of the
   560    561   SQLite table into which the CSV data is to be inserted.
   561    562   
   562    563   <p>Note that it is important to set the "mode" to "csv" before running the
   563    564    ".import" command.  This is necessary to prevent the command-line shell
   564    565   from trying to interpret the input file text as some other format.
   565    566   
   566         -<tcl>Code {
          567  +<tcl>DisplayCode {
   567    568   sqlite> (((.mode csv)))
   568    569   sqlite> (((.import C:/work/somedata.csv tab1)))
   569    570   }</tcl>
   570    571   
   571    572   <p>There are two cases to consider:  (1) Table "tab1" does not previously
   572    573   exist and (2) table "tab1" does already exist.
   573    574   
................................................................................
   587    588   <tcl>hd_fragment csvout {CSV export}</tcl>
   588    589   <h3>CSV Export</h3>
   589    590   
   590    591   <p>To export an SQLite table (or part of a table) as CSV, simply set
   591    592   the "mode" to "csv" and then run a query to extract the desired rows
   592    593   of the table.
   593    594   
   594         -<tcl>Code {
          595  +<tcl>DisplayCode {
   595    596   sqlite> (((.header on)))
   596    597   sqlite> (((.output c:/work/dataout.csv)))
   597    598   sqlite> (((.mode csv)))
   598    599   sqlite> (((SELECT * FROM tab1;)))
   599    600   sqlite> (((.output stdout)))
   600    601   }</tcl>
   601    602   
................................................................................
   620    621   <p>Use the ".dump" command to convert the entire contents of a
   621    622   database into a single ASCII text file.  This file can be converted
   622    623   back into a database by piping it back into <b>sqlite3</b>.</p>
   623    624   
   624    625   <p>A good way to make an archival copy of a database is this:</p>
   625    626   
   626    627   
   627         -<tcl>Code {
          628  +<tcl>DisplayCode {
   628    629   $ (((echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz)))
   629    630   }</tcl>
   630    631   
   631    632   
   632    633   <p>This generates a file named <b>ex1.dump.gz</b> that contains everything
   633    634   you need to reconstruct the database at a later time, or on another
   634    635   machine.  To reconstruct the database, just type:</p>
   635    636   
   636    637   
   637         -<tcl>Code {
          638  +<tcl>DisplayCode {
   638    639   $ (((zcat ex1.dump.gz | sqlite3 ex2)))
   639    640   }</tcl>
   640    641   
   641    642   
   642    643   <p>The text format is pure SQL so you
   643    644   can also use the .dump command to export an SQLite database
   644    645   into other popular SQL database engines.  Like this:</p>
   645    646   
   646    647   
   647         -<tcl>Code {
          648  +<tcl>DisplayCode {
   648    649   $ (((createdb ex2)))
   649    650   $ (((sqlite3 ex1 .dump | psql ex2)))
   650    651   }</tcl>
   651    652   
   652    653   
   653    654   <h3>Other Dot Commands</h3>
   654    655   
................................................................................
   668    669   database name.  When the sqlite3 program is launched with two
   669    670   arguments, the second argument is passed to the SQLite library
   670    671   for processing, the query results are printed on standard output
   671    672   in list mode, and the program exits.  This mechanism is designed
   672    673   to make sqlite3 easy to use in conjunction with programs like
   673    674   "awk".  For example:</p>
   674    675   
   675         -<tcl>Code {
          676  +<tcl>DisplayCode {
   676    677   $ (((sqlite3 ex1 'select * from tbl1' |)))
   677    678   > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
   678    679   <tr><td>hello<td>10
   679    680   <tr><td>goodbye<td>20
   680    681   $
   681    682   }</tcl>
   682    683   

Changes to pages/download.in.

   119    119     This is a snapshot (as of VERSION) of the current SQLite code under 
   120    120     development, packaged and ready to build using the
   121    121     <a href="http://www.tcl.tk/doc/tea/">Tcl Extension Architecture (TEA)</a>.
   122    122     Use this snapshot for testing only.  This is not a release.
   123    123   }
   124    124   Product {snapshot/sqlite-shell-win32-x86-DATE.zip} {
   125    125     This is a snapshot (as of VERSION) build of the 
   126         -  <a href="sqlite.html">sqlite3.exe command-line shell</a>
          126  +  [CLI | sqlite3.exe command-line shell]
   127    127     shell program for 32-bit windows.
   128    128   }
   129    129   Product {snapshot/sqlite-shell-win64-x64-DATE.zip} {
   130    130     This is a snapshot (as of VERSION) build of the 
   131         -  <a href="sqlite.html">sqlite3.exe command-line shell</a>
          131  +  [CLI  | sqlite3.exe command-line shell]
   132    132     shell program for 64-bit windows.
   133    133   }
   134    134   
   135    135   Product {snapshot/sqlite-winrt81-DATE.vsix} {
   136    136     This is a snapshot (as of VERSION) for
   137    137     a complete VSIX package with an extension SDK and all other components
   138    138     needed to use SQLite for WinRT 8.1 application development with Visual Studio

Changes to pages/features.in.

    29     29   <li><a href="selfcontained.html">Self-contained</a>:
    30     30       no external dependencies.</li>
    31     31   <li>Cross-platform: Unix (Linux, Mac OS-X, Android, iOS) and Windows
    32     32       (Win32, WinCE, WinRT)
    33     33       are supported out of the box.  Easy to port to other systems.
    34     34   <li>Sources are in the <a href="copyright.html">public domain</a>.
    35     35       Use for any purpose.</li>
    36         -<li>Comes with a standalone <a href="sqlite.html">command-line interface</a>
           36  +<li>Comes with a standalone [command-line interface]
    37     37       (CLI) client that can be used to administer SQLite databases.</li>
    38     38   </ul>
    39     39   </p>
    40     40   
    41     41   <h2>Suggested Uses For SQLite:</h2>
    42     42   
    43     43   <p><ul>

Changes to pages/quickstart.in.

    16     16   
    17     17   <ul>
    18     18   <li><p>At a shell or DOS prompt, enter: "<b>sqlite3 test.db</b>".  This will
    19     19   create a new database named "test.db".  (You can use a different name if
    20     20   you like.)</p></li>
    21     21   <li><p>Enter SQL commands at the prompt to create and populate the
    22     22   new database.</p></li>
    23         -<li><p>Additional documentation is available <a href="sqlite.html">here</a></li>
           23  +<li><p>Additional documentation is available [CLI | here]</li>
    24     24   </ul>
    25     25   
    26     26   <h2>Write Programs That Use SQLite</h2>
    27     27   
    28     28   <ul>
    29     29   <li><p>Below is a simple 
    30     30   [http://www.tcl.tk | TCL program] that demonstrates how to use

Added rawpages/sqlite.html.

            1  +<html>
            2  +<head>
            3  +<meta http-equiv="refresh" content="0; ./cli.html">
            4  +</head>
            5  +<!-- The "cli.html" page used to be called "sqlite.html".  This is a replace
            6  +     "sqlite.html" page that automatically redirects to the new "cli.html" -->
            7  +<body>
            8  +Redirect to <a href="./cli.html">./cli.html</a>
            9  +</body>
           10  +</html>