## Run this Tcl script to generate the sqlite.html file.#set rcsid {$Id: sqlite.tcl,v 1.23 2005/12/02 01:57:43 drh Exp $}source common.tclheader {sqlite: A command-line access program for SQLite databases}puts {<h2>sqlite: A command-line access program for SQLite databases</h2><p>The SQLite library includes a simple command-line utility named<b>sqlite</b> that allows the user to manually enter and execute SQLcommands against an SQLite database.  This document provides a briefintroduction on how to use <b>sqlite</b>.<h3>Getting Started</h3><p>To start the <b>sqlite</b> program, just type "sqlite" followed bythe name the file that holds the SQLite database.  If the file doesnot exist, a new one is created automatically.The <b>sqlite</b> program willthen prompt you to enter SQL.  Type in SQL statements (terminated by asemicolon), press "Enter" and the SQL will be executed.</p><p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p>}proc Code {body} {  puts {<blockquote><tt>}  regsub -all {&} [string trim $body] {\&amp;} body  regsub -all {>} $body {\&gt;} body  regsub -all {<} $body {\&lt;} body  regsub -all {\(\(\(} $body {<b>} body  regsub -all {\)\)\)} $body {</b>} body  regsub -all { } $body {\&nbsp;} body  regsub -all \n $body <br>\n body  puts $body  puts {</tt></blockquote>}}Code {$ (((sqlite ex1)))SQLite version 2.0.0Enter ".help" for instructionssqlite> (((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!|10goodbye|20sqlite>}puts {<p>You can terminate the sqlite program by typing your systemsEnd-Of-File character (usually a Control-D) or the interruptcharacter (usually a Control-C).</p><p>Make sure you type a semicolon at the end of each SQL command!The sqlite looks for a semicolon to know when your SQL command iscomplete.  If you omit the semicolon, sqlite will give you acontinuation prompt and wait for you to enter more text to beadded to the current SQL command.  This feature allows you toenter SQL commands that span multiple lines.  For example:</p>}Code {sqlite> (((CREATE TABLE tbl2 ()))   ...> (((  f1 varchar(30) primary key,)))   ...> (((  f2 text,)))   ...> (((  f3 real)))   ...> ((();)))sqlite> }puts {<h3>Aside: Querying the SQLITE_MASTER table</h3><p>The database schema in an SQLite database is stored ina special table named "sqlite_master".You can execute "SELECT" statements against thespecial sqlite_master table just like any other tablein an SQLite database.  For example:</p>}Code {$ (((sqlite ex1)))SQlite vresion 2.0.0Enter ".help" for instructionssqlite> (((select * from sqlite_master;)))    type = table    name = tbl1tbl_name = tbl1rootpage = 3     sql = create table tbl1(one varchar(10), two smallint)sqlite>}puts {<p>But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE againstthe sqlite_master table.  The sqlite_mastertable is updated automatically as you create or drop tables andindices from the database.  You can not make manual changesto the sqlite_master table.</p><p>The schema for TEMPORARY tables is not stored in the "sqlite_master" tablesince TEMPORARY tables are not visible to applications other than theapplication that created the table.  The schema for TEMPORARY tablesis stored in another special table named "sqlite_temp_master".  The"sqlite_temp_master" table is temporary itself.</p><h3>Special commands to sqlite</h3><p>Most of the time, sqlite just reads lines of input and passes themon to the SQLite library for execution.But if an input line begins with a dot ("."), thenthat line is intercepted and interpreted by the sqlite program itself.These "dot commands" are typically used to change the output formatof queries, or to execute certain prepackaged query statements.</p><p>For a listing of the available dot commands, you can enter ".help"at any time.  For example:</p>}Code {sqlite> (((.help))).databases             List names and files of attached databases.dump ?TABLE? ...      Dump the database in a text format.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..header(s) ON|OFF      Turn display of headers on or off.help                  Show this message.indices TABLE         Show names of all indices on TABLE.mode MODE             Set mode to one of "line(s)", "column(s)",                        "insert", "list", or "html".mode insert TABLE     Generate SQL insert statements for TABLE.nullvalue STRING      Print STRING instead of nothing for NULL data.output FILENAME       Send output to FILENAME.output stdout         Send output to the screen.prompt MAIN CONTINUE  Replace the standard prompts.quit                  Exit this program.read FILENAME         Execute SQL in FILENAME.schema ?TABLE?        Show the CREATE statements.separator STRING      Change separator string for "list" mode.show                  Show the current values for various settings.tables ?PATTERN?      List names of tables matching a pattern.timeout MS            Try opening locked tables for MS milliseconds.width NUM NUM ...     Set column widths for "column" modesqlite> }puts {<h3>Changing Output Formats</h3><p>The sqlite program is able to show the results of a queryin five different formats: "line", "column", "list", "html", and "insert".You can use the ".mode" dot command to switch between these outputformats.</p><p>The default output mode is "list".  Inlist mode, each record of a query result is written on one line ofoutput and each column within that record is separated by a specificseparator string.  The default separator is a pipe symbol ("|").List mode is especially useful when you are going to send the outputof a query to another program (such as AWK) for additional processing.</p>}Code {sqlite> (((.mode list)))sqlite> (((select * from tbl1;)))hello|10goodbye|20sqlite>}puts {<p>You can use the ".separator" dot command to change the separatorfor list mode.  For example, to change the separator to a comma anda space, you could do this:</p>}Code {sqlite> (((.separator ", ")))sqlite> (((select * from tbl1;)))hello, 10goodbye, 20sqlite>}puts {<p>In "line" mode, each column in a row of the databaseis shown on a line by itself.  Each line consists of the columnname, an equal sign and the column data.  Successive records areseparated by a blank line.  Here is an example of line modeoutput:</p>}Code {sqlite> (((.mode line)))sqlite> (((select * from tbl1;)))one = hellotwo = 10one = goodbyetwo = 20sqlite>}puts {<p>In column mode, each record is shown on a separate line with thedata aligned in columns.  For example:</p>}Code {sqlite> (((.mode column)))sqlite> (((select * from tbl1;)))one         two       ----------  ----------hello       10        goodbye     20        sqlite>}puts {<p>By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated.  You canadjust the column widths using the ".width" command.  Like this:</p>}Code {sqlite> (((.width 12 6)))sqlite> (((select * from tbl1;)))one           two   ------------  ------hello         10    goodbye       20    sqlite>}puts {<p>The ".width" command in the example above sets the width of the firstcolumn to 12 and the width of the second column to 6.  All other columnwidths were unaltered.  You can gives as many arguments to ".width" asnecessary to specify the widths of as many columns as are in yourquery results.</p><p>If you specify a column a width of 0, then the columnwidth is automatically adjusted to be the maximum of threenumbers: 10, the width of the header, and the width of thefirst row of data.  This makes the column width self-adjusting.The default width setting for every column is this auto-adjusting 0 value.</p><p>The column labels that appear on the first two lines of outputcan be turned on and off using the ".header" dot command.  In theexamples above, the column labels are on.  To turn them off youcould do this:</p>}Code {sqlite> (((.header off)))sqlite> (((select * from tbl1;)))hello         10    goodbye       20    sqlite>}puts {<p>Another useful output mode is "insert".  In insert mode, the outputis formatted to look like SQL INSERT statements.  You can use insertmode to generate text that can later be used to input data into a different database.</p><p>When specifying insert mode, you have to give an extra argumentwhich is the name of the table to be inserted into.  For example:</p>}Code {sqlite> (((.mode insert new_table)))sqlite> (((select * from tbl1;)))INSERT INTO 'new_table' VALUES('hello',10);INSERT INTO 'new_table' VALUES('goodbye',20);sqlite>}puts {