Documentation Source Text

<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     {command-line interface} {sqlite3.exe command-line shell} {sqlite3.exe} </tcl>
<table_of_contents>

<tcl>hd_fragment intro</tcl>
<h1>Getting Started</h1>

<p>The SQLite project provides a simple command-line program named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows)
that allows the user to manually enter and execute SQL
statements against an SQLite database or against a
<a href="#zipdb">ZIP archive</a>.  This document provides a brief
introduction on how to use the <b>sqlite3</b> program.

<p>Start the <b>sqlite3</b> program by typing "sqlite3" at the
command prompt, optionally followed 
by the name the file that holds the SQLite database
(or <a href="#zipdb">ZIP archive</a>).  If the named
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified on the
command-line, a temporary database is created, then deleted when 
the "sqlite3" program exits.

<p>On startup, the <b>sqlite3</b> program will show a brief banner
message then prompt you to enter SQL.  Type in SQL statements (terminated

for CSV files.

<p>
There is also a ".once -e" command which works similarly, except that
it names the temporary file with a ".txt" suffix so that the default
text editor for the system will be invoked, instead of the default
spreadsheet.

<tcl>hd_fragment zipdb {ZIP file as database}</tcl>
<h1>Accessing ZIP Archives As Database Files</h1>

<p>In addition to reading and writing SQLite database files,
the <b>sqlite3</b> program will also read and write ZIP archives.
Simply specify a ZIP archive filename in place of an SQLite database
filename on the initial command line, or in the ".open" command,
and <b>sqlite3</b> will automatically detect that the file is a
ZIP archive instead of an SQLite database and will open it as such.
This works regardless of file suffix.  So you can open JAR, DOCX,
and ODP files and any other file format that is really a ZIP
archive and SQLite will read it for you.

<p>A ZIP archive appears to be a database containing a single table
with the following schema:

<tclscript>DisplayCode {
CREATE TABLE zip(
  name,     // Name of the file
  mode,     // Unix-style file permissions
  mtime,    // Timestamp, seconds since 1970
  sz,       // File size after decompression
  rawdata,  // Raw compressed file data
  data,     // Uncompressed file content
  method    // ZIP compression method code
);
}</tclscript>

<p>So, for example, if you wanted to see the compression efficiency
(expressed as the size of the compressed content relative to the
original uncompressed file size) for all files in the ZIP archive,
sorted from most compressed to least compressed, you could run a
query like this:

<tclscript>DisplayCode {
sqlite> SELECT name, (100.0*length(rawdata))/sz FROM zip ORDER BY 2;
}</tclscript>

<p>Or using [file I/O functions], you can extract elements of the
ZIP archive:

<tclscript>DisplayCode {
sqlite> SELECT writefile(name,content) FROM zip
   ...> WHERE name LIKE 'docProps/%';
}</tclscript>

<h2>How ZIP archive access is implemented</h2>

<p>The command-line shell uses the [Zipfile virtual table] to
access ZIP archives.  You can see this by running the ".schema"
command when a ZIP archive is open:

<tclscript>DisplayCode {
sqlite> .schema
CREATE VIRTUAL TABLE zip USING zipfile('document.docx')
/* zip(name,mode,mtime,sz,rawdata,data,method) */;
}</tclscript>

<p>When opening a file, if the command-line client discovers that the
file is ZIP archive instead of an SQLite database, it actually opens
an [in-memory database] and then in that in-memory database it creates
an instance of the [Zipfile virtual table] that is attached to the
ZIP archive.

<p>The special processing for opening ZIP archives is a trick of the
command-line shell, not the core SQLite library.  So if you want to
open a ZIP archive as a database in your application, you will need to
activate the [Zipfile virtual table] module then run an appropriate
[CREATE VIRTUAL TABLE] statement.


<tcl>hd_fragment dump {.dump}</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<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>

    hidden columns are shown rather than being omitted.
}

DangerousPragma {schema_version} {
    <p><b>PRAGMA DB.schema_version; 
      <br>PRAGMA DB.schema_version = </b><i>integer </i>;

<p>   ^The schema_version pragma will get or set
       the value of the schema-version integer at offset 40 in the
       [database header]. 

<p>    ^SQLite automatically increments the schema-version whenever the
       schema changes. ^As each SQL statement runs, the schema version is
       checked to ensure that the schema has not changed since the SQL
       statement was [sqlite3_prepare|prepared].