It's very easy to begin using SQLite no matter what operating system you are using. For the vast majority of users, you can be up and running with SQLite in less than five minutes, regardless of experience. This chapter covers everything you need to know in order to install SQLite and work with databases. By the time we are done, you will have a working knowledge of where to obtain SQLite software or source code and how to install or compile it on multiple platforms. You'll be working with new SQLite databases and creating tables, views, and indexes that you can query, back up, and restore.
You will learn everything you need to know about managing SQLite databases, including how to create, view, and examine their contents. Finally, you will be introduced to several tools with which to work with SQLite in various environments. This chapter does include some examples that use SQL to introduce the SQLite command-line program. If you are not yet familiar with SQL, the examples will be easy enough that you should still be able to follow them without much trouble. We take an in-depth look at SQL in detail in Chapters 3 and 4.
The SQLite website (www.sqlite.org
) provides both precompiled binaries of SQLite as well as source code. Binaries are available for popular platforms including Mac OS X, Windows, and Linux.
There are several binary packages to choose from, each of which is specific to a particular way of using SQLite. The binary packages are as follows:
sqlite3 command-line program (CLP): This version of the SQLite command-line program has the database engine compiled in with statically linked dependencies, acting as a self-contained, stand-alone program. This provides a convenient way to work with SQLite databases from the command line without having to worry about whether the SQLite shared library is installed on your system or located in the right place. If you run on of the contemporary Linux distributions or OS X, you almost certainly already have this installed.
SQLite shared library (DLL or “so”): This is the SQLite database engine packaged into a shared object (so) or Windows dynamic link library (DDL). Use this with programs that dynamically link to SQLite. This form makes it easier to upgrade SQLite without having to recompile the software that depends on it. Note that there is currently no precompiled
.dylib
shared library for Mac OS X.SQLite Analyzer: The Analyzer tool is useful for inspecting many qualities and statistics about a given SQLite database file, including things such as physical data distribution and layout within the file, relative fragmentation, internal data size and free space, and other measures. This information is very useful for performance optimization work.
Tcl extension: This binary incorporates the Tcl language extensions with the SQLite core. This allows you to connect to SQLite from within the Tcl language. Tcl and C are the two language bindings provided by the SQLite team themselves. Other language bindings are covered in Chapter 8.
SQLite's source code is provided in a variety of forms for convenience and to target different platforms. The main differences in the source distributions concern whether the code is amalgamated into a single source file and header or left in constituent files. There is also a source package incorporating the Tcl Extension Architecture (TEA) for those who want to compile SQLite with Tcl bindings.
The SQLite amalgamated source appears twice on the website—once as a zip file and again as a gzipped tarball. The source code itself does not differ between these source distributions. The zip file is generally recommended and in particular is the only source readily suitable for compilation on Windows (unless you prefer going to laborious effort on that platform). The zip file source distribution benefits from already having certain preprocessing and code generation performed on the code by build tools like GNU autoconf
. This means Windows users don't need to hunt down Windows-specific equivalents for these tools. The gzipped tarball is targeted to users of Linux, Unix flavors, Mac OS X, BSD variants, and so on. These platforms include in their normal distributions the expected tool chain to do much of the preprocessing work, as well as normal compilation, so users of those platforms can adopt this source package in the knowledge they need not hunt down additional tools to compile SQLite.
Whether you are using SQLite as an end user, writing programs that use SQLite, or using it as a learning platform for relational theory and SQL, SQLite can be installed on Windows with a minimum of fuss. In this section, we will cover all the options, from installing the available binary packages to building everything from source using the most popular compilers. We start with the easy things first and progress to things more technically challenging.
The SQLite command-line program (hereafter referred to as the CLP) is by far the easiest way to get started using SQLite. Follow these steps to obtain the CLP:
www.sqlite.org
.sqlite-3_x_y_z.zip
, where x
, y,
and z
are the minor-version numbers. There should be a comment beside it that reads “A command-line program for accessing and modifying SQLite databases.” Download this file to your Windows environment.sqlite3.exe
. You can start using the file from the location in which you unzipped it, or if you would like to run the CLP from any directory in the Windows shell, you need to copy it to a folder that is in your Windows system path. A suitable default that should work on all versions of Windows is the windowssystem32
folder on your root partition (C: for most systems
).
Note If you don't know your Windows system path value, you can find it in several ways. From a command prompt, you can execute this command:
echo %PATH%
This will return a string of directories separated by semicolons—this is your path value. For the graphically inclined, you can obtain the same information from the Control Panel. Click Start » Control Panel. Choose the System icon. In the resulting dialog box, select the Advanced tab, and click the Environmental Variables button. In the System Variables list in the lower half of the dialog box, double-click the Path entry. This will open the Edit System Variables dialog box, in which the Values text box contains your path list. You can add additional directories to this path if you like by simply appending a semicolon to the end of the line and typing the new path.
cmd
in the Open drop-down box, and click OK (Figure 2-1). This will open a Windows command shell. If this doesn't work, try selecting Start » All Programs » Accessories » Command Prompt.
Figure 2-1. Opening a Windows command shell
sqlite3
on the command line, and press Enter. This should bring up a SQLite command prompt. (If you get an error, then the sqlite3.exe
executable has not been copied to a folder in your system path. Recheck your path, and place a copy of the program somewhere within it.) When the SQLite shell appears, type .help
on the command line. This will display a list of commands with their associated descriptions similar to the one in Figure 2-2. Type .exit
to exit the program. You now have a working copy of the SQLite CLP installed on your system.
Figure 2-2. The SQLite shell on Windows
If you are especially eager to work with SQLite at this point, you may want to skip ahead to the section “The CLP in Shell Mode.” The next few sections are geared to developers who want to write programs that use SQLite.
The SQLite DLL is used for software compiled to link dynamically to SQLite. This means that the application will load the DLL at runtime when SQLite features are required, instead of embedding the SQLite code in the application itself. Software that uses SQLite in this fashion typically includes its own copy of the SQLite DLL and installs it automatically with the software.
If you plan to develop with SQLite, using the DLL is probably the easiest way to start. You can obtain the SQLite DLL as follows:
www.sqlite.com
, and choose the download link at the top of the page. This will take you to the download page.sqlitedll-3_x_y_z.zip
, where x
, y,
and z
are the minor versions. If you want Tcl support included, select the file with the name of the form tclsqlitedll-3_x_y_z.zip
.sqlite3.dll
) together with another file called sqlite3.def
. The SQLite DLL provided here is thread safe, because it was compiled with the THREADSAFE
preprocessor flag defined. This allows you to use SQLite features in multithreaded applications; you can therefore use this DLL in multithreaded programs, performing simultaneous actions in multiple threads safely.To use the DLL, it needs to either be in the same folder with programs that use it, be placed somewhere in the system's path (see the note on the Windows System path in the previous section), or follow Window's dynamic library loading rules for locations searched for DLLs.
If you want to write programs that use the SQLite DLL, you will need to create an import library with which to link your programs. This is quite simple to do using the sqlite3.def
file mentioned earlier. If you are using C++ in Microsoft Visual Studio, open the command prompt, change the directory to the SQLite distribution, and simply run the following command:
LIB /DEF:sqlite3.def
You should see normal library generation output similar to the following example:
C:sqlite>lib /DEF:sqlite3.def
Microsoft (R) Library Manager Version 10.00.30319.01
Copyright (C) Microsoft Corporation. All rights reserved.
LINK : warning LNK4068: /MACHINE not specified; defaulting to X86
Creating library sqlite3.lib and object sqlite3.exp
This will generate an import library named sqlite3.lib
and an exports file named .exp
. If you are using MinGW (see the section “Building SQLite with MinGW” later in this chapter), run this command:
The dlltool
for MinGW will also create an import library called sqlite3.lib
with which you can link your programs. By linking your programs to this import library, they will load and use the SQLite DLL upon execution.
Building SQLite from source within Windows is straightforward. Depending on the compiler you are using and what you are trying to achieve, there are several approaches to compiling SQLite. The most common scenarios on Windows include using Microsoft Visual C++, MinGW, or Eclipse (which often uses MinGW for C++ work). We'll cover each of these here. You can find information about how to compile SQLite with other compilers on the SQLite wiki (www.sqlite.org/cvstrac/wiki?p=HowToCompile
).
You can obtain stable versions of SQLite's source code in zip files from the SQLite website. Bleeding-edge versions can be obtained from the Fossil distributed source control system maintained by the SQLite team. Unless you are familiar with Fossil, using the source distribution is the easiest way to go. To download a stable source distribution, follow these steps:
www.sqlite.org
. Follow the download link, which will take you to the download page.named sqlite-amalgamation-3-x_y_z.zip
, where x
, y
, and z
(and possibly further minor digits) are the minor-version numbers.
Note The zip archive and the other tarballs on the download page differ slightly in their contents. Although they contain identical source code, the SQLite distribution uses some POSIX build tools (sed
, awk
, and so on) to dynamically generate some C source code in the build process. These build tools are not available by default on Windows systems. Therefore, the zip source archive includes all the preprocessing and generated code as a matter of convenience to Windows users who lack the build support infrastructure of Linux, Unix, Mac OS X, and so on. This is why Windows users should use the zip archives rather than the tarballs on the download page. It is still possible to build the tarballs on Windows, but you need the requisite build tools from a source such as Cygwin, MinGW, GnuWin32, or similar.
If you want to play with the latest features or participate in SQLite development, then retrieving SQLite from the project's Fossil source control system makes the most sense. Fossil provides the same kind of anonymous access you'll likely have seen in other source control systems, like CVS, Subversion, Perforce, Git, and so on. Fossil allows you to maintain the absolutely latest version of the SQLite source code by accessing the source straight from your browser! If you want, you can keep your copy of the code synced up to the day, hour, or minute to stay current with changes as they are committed. Thus, if you see an important bug fix or feature posted that you want to take advantage of, you can have Fossil sync and recompile your copy of the code. Fossil even includes an autosync feature to guarantee you stay at the forefront of any source changes.
Obtaining SQLite from Fossil on Windows couldn't be easier. Like SQLite binaries, Fossil is distributed as a single statically linked file. You can download Fossil from the project's home page, www.fossil-scm.org
. The download page includes up-to-the-minute releases for Windows, as well as Mac OS X and Linux. Unzip the Fossil archive to a location on your path (see our previous discussion on determining your PATH
environment variable). Next, make a new directory to house your Fossil source extracts, or choose an existing directory if you prefer.
You can do most of your source control work with Fossil in a browser—Fossil acts as its own HTTP server to handle all the server-side tasks. The fastest way to get your initial check-out of the SQLite source code is to use your newly downloaded fossil.exe
binary to clone one of the SQLite online repositories. Open a command prompt, and change directories to the new or existing directory you've chosen for your SQLite Fossil source control repository. Then at the command prompt, issue the Fossil clone
command, as shown here:
C:sqlitefossil.exe clone http://www.sqlite.org/src sqlite.fossil
Bytes Cards Artifacts Deltas
Send: 49 1 0 0
Received: 1499917 32606 0 0
Send: 10025 225 0 0
Received: 132364 239 7 193
...
Send: 4655 99 0 0
Received: 1698453 125 29 69
Total network traffic: 1379539 bytes sent, 17512520 bytes received
Rebuilding repository meta-data...
32605 (100%)...
project-id: 2ab58778c2967968b94284e989e43dc11791f548
server-id: 38f0c0987054889a5d2c0b4f27b370e9e8632a16
admin-user: fuzz (password is "******")
The format of the command is fossil <action> <Fossil Repository URL> <your repository name>
. In this case, the action was clone
, meaning to take a complete copy of the SQLite repository. We provide one of the SQLite Fossil repositories described on the SQLite website, in this case www.sqlite.org/src
. Lastly, we provide a name of our choosing, which will be the name of the file Fossil creates to contain and manage our copy of the source. This can be any name you like that satisfies Windows file-naming conventions. We choose sqlite.fossil
to be a useful name reminding us what the file contains and what uses it.
Note We are targeting the current “head” of the source control tree for SQLite with the example given. This means you would be compiling from the latest and greatest source. That's a two-edged sword of course, giving you the absolute latest developments but not the benefit of a “stable” release. Feel free to choose a stable branch if you prefer.
You are now ready to launch and use the Fossil web interface. From your command line, just run the next command (substitute your own directory and local repository filename as appropriate).
C:sqlitefossil.exe ui sqlite.fossil
You should see output from Fossil indicating it has successfully launched its HTTP server and is listening for connections. This should very quickly disappear behind your favorite browser, which Fossil will have automatically opened at the home of your local SQLite repository, as shown in Figure 2-3.
Figure 2-3. Your local SQLite Fossil source control system, complete with up-to-date source
You are now up and running with Fossil. From here, you'll obviously want to start with checking out the code so that you can start work. That's just as easy as most other Fossil actions. First, from your shell, create a directory for the source, and then change into that directory.
mkdir c:sqlitesrc
cd c:sqlitesrc
Now you can tell Fossil to check out the current incarnation of the source, using the open
command:
c:sqlitefossil.exe open c:sqlitesqlite.fossil
You'll see all the source files for the SQLite project fly by on the screen. When completed, around 15MB of files are downloaded, and you have the source ready with which to work.
To build the SQLite DLL from source using Visual C++, follow these steps:
C:sqlite
. In the Name text box, enter the name of the folder containing the SQLite source code—src
in this example. This will create the Visual C++ project inside the existing SQLite source folder (C:sqlitesrc
). Click OK.
Figure 2-4. Creating a new Visual C++ project
Figure 2-5. The Win32 Application Wizard
.c
and .h
files in the directory except for two files: tclsqlite.c
and shell.c
. (The first is for Tcl support; the second is for creating the SQLite CLP, neither of which we want in this case.).def
) file. This file defines what symbols (or functions) to export (make visible) to programs that link to the library. SQLite's source distribution is kind enough to include such a file (sqlite3.def
) for this very purpose. Also within the Property Pages dialog box, select All Configurations in the Configuration drop-down box (Figure 2-6). Then click the Linker folder, and click the Input submenu. In the Module Definition File property page, type sqlite3.def
. You are now ready to build the DLL.
Figure 2-6. Project properties
The binary for a static CLP is available on the SQLite website, but what if you want a version that uses the SQLite DLL? To build such a version in Visual C++, do the following:
Note Many of the steps are very similar to the process of building a DLL, mentioned earlier—you may want to use some of the figures listed there for reference.
shell
, for example), and click OK.shell.c
.sqlite3.lib
. You are now ready to build the program. Note that the SQLite DLL needs to be either in the same directory as the command-line program or in the Windows system path.Note If you build the SQLite DLL with threading enabled or you obtain the DLL from the SQLite website, you need to use the multithreaded Microsoft C runtime library DLL when building the CLP. To do this, refer to the second half of step 4 in “Building the SQLite DLL with Microsoft Visual C++.” It contains two informative figures that make it easy to set this option.
MinGW (www.mingw.org
) is a fork of the Cygwin project, and it provides a nice distribution of the GNU Compiler Collection (GCC) for Windows. It also includes freely available Windows-specific header files and libraries that you can use to create native Windows programs that do not rely on any third-party C runtime DLLs. Put simply, it is a free C/C++ compiler for Windows, and it's a very good one at that. It is usually used in conjunction with MSYS, which offers a bash-like shell and POSIX environment that makes Unix users feel at home on Windows. Together, the two provide a powerful environment with which to compile and build software on Windows. The two are popular but also can be difficult to stick together by hand. Thankfully, several very good bundles exist to take all the hard work out of deploying them, allowing you to get on with the fun of compiling your own SQLite from source. We recommend the TakeOffGW package, freely available from SourceForge.
To build the SQLite DLL from source with the TakeOffGW distribution of MinGW and MSYS, do the following:
sourceforge.net
.sourceforge.net/projects/takeoffgw/
.setup.exe
.setup.exe
file, and choose the defaults for installation location, package directory, and links to the Internet (remembering any proxy server you may use). When you get to the package selection page, shown in Figure 2-7, be sure to select all the packages available under the MSYS heading. Do this by toggling the MSYS heading itself until the phrase “install” appears.
Figure 2-7. TakeOffGW installation components, with MSYS selected for install
www.sqlite.org
, and click the download link. On the download page, find the Source Code section. The file you are looking for is the source distribution in tarballs form, which should have a name of the form sqlite-amalgamation-3.x.y.z.tar.gz
, where x
, y
, and z
(and possibly further numbers) are the minor-version numbers (at the time of this writing, the current filename is sqlite-amalgamation-3.6.23.1.tar.gz
). Download the tarball, and place it in a temporary directory (e.g., C:Temp
).c:Temp
, you would type cd /c/Temp
.sqlite-amalgamation-3.6.23.1.tar.gz
.cd sqlite-3.6.32.1
./configure
make
You will see configure
and make
scroll many lines of status output to the screen. When make
completes, you now have a functional SQLite binary. To use it easily, add the TakeOffGW bin
directory to your path. If you followed the defaults prompted by the TakeOffGW network installer, this will be the c:cygwinin
directory. You can now use Windows Explorer or the command prompt, navigate to the temporary directory, and run sqlite3.exe
. You now have a working SQLite CLP.
SQLite compiles and builds identically on systems such as Linux, Mac OS X, FreeBSD, NetBSD, OpenBSD, Solaris, and others—known historically as POSIX systems (though this is now a less useful term, because systems as diverse as mainframes and Microsoft Windows have been POSIX compliant for more than a decade). SQLite binaries can be obtained in a variety of ways depending on the particular operating system.
If you are using Mac OS 10.4 (Tiger) or greater, you already have SQLite installed on your system. If not, there are several routes you can take to install it. The easiest way is to use one of the following Mac-specific package management systems, all of which include packages or ports for SQLite:
MacPorts: MacPorts is probably the most popular source of open source software, tools, and encompassing package management features with Mac users today. It includes the absolute latest SQLite distributions, including additional packages such as the Tcl bindings, and so on.
Fink: Fink is a Debian-based package management system that uses Debian utilities such as
dpkg
,dselect
, andapt-get
, in addition to its own utility—fink
. You can download Fink fromhttp://fink.sourceforge.net
. With Fink, it is possible to install straight from precompiled binaries. No compilation step is needed.
BSD users will have no trouble installing SQLite either. FreeBSD, OpenBSD, and NetBSD all have packages and/or ports for SQLite, all of which are very easy to install. Each distribution has ports for very recent versions of SQLite 3.6.x.
Solaris 10 uses SQLite as part of the OS, originally shipping with version 2, but if your system is patched regularly, contemporary Solaris 10 platforms are patched to version 3.6.20.
As mentioned earlier, binaries for Linux are available directly from the SQLite website. The download page on SQLite's website provides the following binaries:
Statically linked command-line program: The filename is of the form
sqlite3-3.
x.y.z.bin.gz
, wherex
,y
, andz
(and possibly more digits) are the minor-version numbers.Shared library: Two forms of the shared library exist. One form includes the Tcl bindings; the other does not. The description next to each highlights which shared library source is which. Note that the shared libraries provided are not thread safe. If you need a thread-safe version, you will have to compile the library from source. See the section “Compiling SQLite from Source” for more details.
SQLite Analyzer: This is a command-line program that provides detailed information about the contents of a SQLite database. You'll find information on this program in the section “Getting Database File Information.”
Probably the best way to get new versions of SQLite and/or its source for your Linux platform is from the relevant package repositories for your distribution. Red Hat, CentOS, Fedora, and other Red Hat derivatives can use yum
to search and find various SQLite packages. Debian-based distributions (including Ubuntu) will have no trouble getting up-to-date versions of SQLite. SQLite 3 packages are available online in both Ubuntu and Debian repositories, among others. Use apt
or Synaptic to search and retrieve the packages of your choice.
Compiling SQLite from source on Linux, Mac OS X, the BSD flavors, or other Unix systems follows very closely the MinGW/TakeOffGW instructions given earlier for the Windows platform (actually, it is more the other way around; MinGW installation apes Linux source installation!). To build SQLite on these systems, you need to ensure that you have the GNU Compiler Collection (GCC) installed, including Autoconf, Automake, and Libtool. Most of the systems already discussed include all of these by default. With this software in place, you can build SQLite by doing the following:
sqlite-amalgamation-3.6.23.1.tar.gz
. Place it in a directory (e.g., /tmp
).cd /tmp
tar -xzvf sqlite-amalgamation-3.6.23.1.tar.gz
cd sqlite-3.6.23.1
./configure
./configure --help
make
make install
You now have a functional SQLite installation on your system. If you have GNU Readline installed on you system, the CLP should be compiled with Readline support. Test it by running it from the command line:
root@linux # sqlite3
This will invoke the CLP using an in-memory database. Type .help
for a list of shell commands. Type .exit
to close the application, or press Ctrl+D.
The SQLite CLP is the most common means you can use to work with and manage SQLite databases. It operates the same way on all platforms, so learning how to use it ensures you will always have a common and familiar way to manage your databases. The CLP is really two programs in one. It can be run in shell mode acting as an interactive query processor, or it can run from the command line to perform various administration tasks.
Open a shell, and change directories to some temporary folder—say C:Temp
if you are on Windows or /tmp
if you're in Unix. If you're happy working from a more permanent location, you can create a sqlite
directory—e.g., c:sqlite
under Windows or /sqlite
for Linux or Unix. This will be your current working directory. All files you create in the course of working with the shell will be created in this directory.
Note If you need a refresher on how to get to the Windows command prompt, refer to step 5 in the “Getting the Command-Line Program” section earlier in this chapter.
To invoke the CLP as in shell mode, type sqlite3
from a command line, followed by an optional database name. If you do not specify a database name, SQLite will use an in-memory database (the contents of which will be lost when the CLP exits).
Using the CLP as an interactive shell, you can issue queries, obtain schema information, import and export data, and perform many other database tasks. The CLP will consider any statement issued as a query, except for commands that begin with a period (.
). These commands are reserved for specific CLP operations, a complete list of which can be obtained by typing .help
, as shown here:
fuzzy@linux:/tmp$ sqlite3
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.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.
With no args, it turns EXPLAIN on.
.genfkey ?OPTIONS? Options are:
--no-drop: Do not drop old fkey triggers.
--ignore-errors: Ignore tables with fkey errors
--exec: Execute generated SQL immediately
See file tool/genfkey.README in the source
distribution for further information.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices ?TABLE? Show names of all indices
If TABLE specified, only show indices for tables
matching LIKE pattern TABLE.
.load FILE ?ENTRY? Load an extension library
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.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
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.width NUM1 NUM2 ... Set column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off
.width NUM NUM ... Set column widths for "column" mode
sqlite>.exit
You can just as easily type .h
for short. Many of the commands can be similarly abbreviated, such as .e
—short for .exit
—to exit the shell.
You can use the CLP from the command line for tasks such as importing and exporting data, returning result sets, and performing general batch processing. It is ideal for use in shell scripts for automated database administration. To see what the CLP offers in command-line mode, invoke it from the shell (Windows or Unix) with the –help
switch, as shown here:
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
-help show this message
-init filename read/process named file
-echo print commands before execution
-[no]header turn headers on or off
-bail stop after hitting an error
-interactive force interactive I/O
-batch force batch I/O
-column set output mode to 'column'
-csv set output mode to 'csv'
-html set output mode to HTML
-line set output mode to 'line'
-list set output mode to 'list'
-separator 'x' set output field separator (|)
-nullvalue 'text' set text string for NULL values
-version show SQLite version -init filename read/process named file
The CLP in command-line mode takes the following arguments:
Most of the options control output formatting except for the init
switch, which specifies a batch file of SQL commands to process. The database filename is required. The SQL command is optional with a few caveats.
Now you that you've seen how to invoke the CLP both interactively and in command-line mode, it's time to look at some examples of using the CLP for some common, administrative tasks. We'll begin at the beginning, with database creation.
Let's start by creating a database that we will call test.db
. From the command line, open the CLP in shell mode by typing the following:
sqlite3 test.db
Even though we have provided a database name, SQLite does not actually create the database (yet) if it doesn't already exist. SQLite will defer creating the database until you actually create something inside it, such as a table or view. The reason for this is so that you have the opportunity to set various permanent database settings (such as page size) before the database structure is committed to disk. Some settings such as page size and character encoding (UTF-8, UTF-16, etc.) cannot be changed easily once the database is created, so this is where you have a chance to specify them. We will go with the default settings here, so to actually create the database on disk, we need only to create a table. Issue the following statement from the shell:
sqlite> create table test (id integer primary key, value text);
Now you have a database file on disk called test.db
, which contains one table called test
. This table, as you can see, has two columns:
id
, which has the ability to automatically generate values by default. Wherever you define a column of type integer primary key, SQLite will apply an function for the column to create and apply monotonically increasing values. That is, if no value is provided for the column in an INSERT
statement, SQLite will automatically generate one by finding the next integer value specific to that column.value
.Let's add a few rows to the table:
sqlite> insert into test (id, value) values(1, 'eenie'),
sqlite> insert into test (id, value) values(2, 'meenie'),
sqlite> insert into test (value) values('miny'),
sqlite> insert into test (value) values('mo'),
Now fetch them back:
sqlite> .mode column
sqlite> .headers on
sqlite> select * from test;
id value
---------- ----------
1 eenie
2 meenie
3 miny
4 mo
The two commands preceding the select
statement (.headers
and .mode
) are used to improve the formatting a little (these commands and others like them are covered later). We can see that our explicit ID values for the first two rows were used. We can also see that SQLite provided sequential integer values for the id
column for rows 3 and 4, which we did not provide in the insert
statements. While on the topic of autoincrement columns, you might be interested to know that the value of the last inserted autoincrement value can be obtained using the SQL function last_insert_rowid()
:
sqlite> select last_insert_rowid();
last_insert_rowid()
-------------------
4
Before we quit, let's add an index and a view to the database. These will come in handy in the examples that follow:
sqlite> create index test_idx on test (value);
sqlite> create view schema as select * from sqlite_master;
To exit the shell, issue the .exit
command:
sqlite> .exit
On Windows, you can also terminate the shell by using the key sequence Ctrl+C. On Unix, you can use Ctrl+D.
There are several shell commands for obtaining information about the contents of a database. You can retrieve a list of tables (and views) using .tables [pattern]
, where the optional [pattern]
can be any pattern that the SQL like
operator understands (we cover like
in Chapter 3 if you are unfamiliar with it). All tables and views matching the given pattern will be returned. If no pattern is supplied, all tables and views are returned:
sqlite> .tables
schema test
Here we see our table named test
and our view named schema
. Similarly, indexes for a given table can be printed using .indices [table name]
:
test_idx
Here we see the index we created earlier on test
, called test_idx
. The SQL definition or data definition language (DDL) for a table or view can be obtained using .schema [table name]
. If no table name is provided, the SQL definitions of all database objects (tables, indexes, views, and triggers) are returned:
sqlite> .schema test
CREATE TABLE test (id integer primary key, value text);
CREATE INDEX test_idx on test (value);
sqlite> .schema
CREATE TABLE test (id integer primary key, value text);
CREATE VIEW schema as select * from sqlite_master;
CREATE INDEX test_idx on test (value);
More detailed schema information can be had from SQLite's principal system view, sqlite_master. This view is a simple system catalog of sorts. Its schema is described in Table 2-1.
Querying sqlite_master
for our current database returns the following (don't forget to use the .mode column
and .headers on
commands first to manually set the column format and headers):
sqlite> .mode column
sqlite> .headers on
sqlite> select type, name, tbl_name, sql from sqlite_master order by type;
type name tbl_name sql
---------- ---------- ---------- -------------------------------------
index test_idx test CREATE INDEX test_idx on test (value)
table test test CREATE TABLE test (id integer primary
view schema schema CREATE VIEW schema as select * from s
We see a complete inventory of test.db
objects: one table, one index, and one view, each with their respective original DDL creation statements.
There are few additional commands for obtaining schema information through SQLite's PRAGMA
commands, table_info
, index_info
, and index_list
, which are covered in Chapter 4.
Tip Don't forget that most command-line tools like the SQLite CLP keep a history of the commands that you execute. To rerun a previous command, you can hit the Up Arrow key to scroll through your previous commands. On Windows, you can also hit F7
in any command prompt window to see a scrollable list of the commands you have entered.
You can export database objects to SQL format using the .dump
command. Without any arguments, .dump
will export the entire database as a series of DDL and data manipulation language (DML) commands, suitable for re-creating the database objects and the data contained therein. If you provide arguments, the shell interprets them as table names or views. Any tables or views matching the given arguments will be exported. Those that don't are simply ignored. In shell mode, the output from the .dump
command is directed to the screen by default. If you want to redirect output to a file, use the .output [filename]
command. This command redirects all output to the file filename
. To restore output back to the screen, simply issue .output stdout
. So, to export the current database to a file file.sql
, you would do the following:
sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout
This will create the file file.sql
in your current working directory if it does not already exist. If a file by that name does exist, it will be overwritten.
By combining redirection with SQL and the various shell formatting options (covered later), you have a great deal of control over exporting data. You can export specific subsets of tables and views in various formats using the delimiter of your choice, which can later be imported using the .import
command described next.
There are two ways to import data, depending on the format of the data in the file to import. If the file is composed of SQL, you can use the .read
command to execute the commands contained in the file. If the file contains comma-separated values (CSV) or other delimited data, you can use the .import [file][table]
command. This command will parse the specified file and attempt to insert it into the specified table. It does this by parsing each line in the file using the pipe character (|
) as the delimiter and inserting the parsed columns into the table. Naturally, the number of parsed fields in the file should match up with the number of columns in the table. You can specify a different delimiter using the .separator
command. To see the current value set for the separator, use the .show
command. This will show all user-defined settings for the shell, among them the current default separator:
sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
The .read
command is the way to import files created by the .dump
command. Using file.sql
created earlier as a backup, we can drop the existing database objects (the test
table and schema
view) and re-import it as follows:
sqlite> drop table test;
sqlite> drop view schema;
sqlite> .read file.sql
The shell offers a number of formatting options to help you and make your results and output neat and tidy. The simplest are .echo
, which echoes the last run command after issuing a command, and .headers, which includes column names for queries when set to on
. The text representation of NULL can be set with .nullvalue
. For instance, if you want NULLs
to appear as the text string NULL, simply issue the command .nullvalue NULL
. By default, this presentation value is an empty string.
The shell prompt can be changed using .prompt [value]
:
sqlite> .prompt 'sqlite3> '
sqlite3>
Result data can be formatted several ways using the .mode
command. The current options are csv
, column
, html
, insert
, line
, list
, tabs
, and tcl
, each of which is helpful in different ways. The default is .list
. For instance, list mode displays results with the columns separated by the default separator. Thus, if you wanted to dump a table in a CSV format, you could do the following:
sqlite3> .output file.csv
sqlite3> .separator ,
sqlite3> select * from test;
sqlite3> .output stdout
The contents of file.csv
now appear as shown next.
1,eenie
2,meenie
3,miny
4,mo
Actually, since there is a CSV mode already defined in the shell, it is just as easy to use it in this particular example instead:
sqlite3> .output file.csv
sqlite3> .mode csv
sqlite3> select * from test;
sqlite3> .output stdout
The results obtained are the same. The difference is that CSV mode will wrap field values with double quotes, whereas list mode (the default) does not.
Combining the previous three sections on exporting, importing, and formatting data, we now have an easy way to export and import data in delimited form. For example, to export only the rows of the test
table whose value
fields start with the letter m to a file called test.csv
in comma-separated values, do the following:
sqlite> .output text.csv
sqlite> .separator ,
sqlite> select * from test where value like 'm%';
sqlite> .output stdout
If you want to then import this CSV data into a similar table with the same structure as the test
table (call it test2
), do the following:
sqlite> create table test2(id integer primary key, value text);
sqlite> .import text.csv test2
The CLP, therefore, makes it easy to both import and export text-delimited data to and from the database.
So far, you've seen the CLP used interactively to perform tasks such as creating a database and exporting data. However, you don't always want to be tied to your seat, executing CLP commands one at a time. Instead, you can use the command mode to run CLP commands in batches. You can then use your operating system's built-in scheduler to schedule those batches to run whenever you need them.
Note You are free to invoke the CLP from the command line interactively. Any time you have a sequence of commands that you want to invoke routinely, it's useful to use the command-line approach.
There are actually two ways to invoke the CLP in command-line mode. The first is to provide a SQL command, or a SQLite shell command as well, such as .dump
and .schema
. Any valid SQL or SQLite shell command will do. SQLite will execute the specified command, print the result to standard output, and exit. For example, to dump the test.db
database from the command line, issue the following command:
sqlite3 test.db .dump
To make it useful, we should redirect the output to a file:
sqlite3 test.db .dump > test.sql
The file test.sql
now contains the complete human-readable set of DDL and DML statements for the database test.db
. Similarly, to select all records for the test
table, issue this:
sqlite3 test.db "select * from test"
The second way to invoke the CLP in command-line mode is to redirect a file as an input stream. For instance, to create a new database test2.db
from our database dump test.sql
, do the following:
sqlite3 test2.db < test.sql
The CLP will read the file as standard input and then process and apply all SQL commands within it to the test2.db
database file.
Another way to create a database from the test.sql
file is to use the init
option and provide the test.sql
as an argument:
sqlite3 –init test.sql test3.db
The CLP will process test.sql
, create the test3.db
database, and then go into shell mode. Why? The invocation included no SQL command or input stream. To get around this, you need to provide a SQL command or SQLite shell command. For example:
sqlite3 –init test.sql test3.db .exit
The .exit
command prompts the CLP to run in command-line mode and does as little as possible. All things considered, redirection is perhaps the easiest method for processing files from the command line.
Backing up a database can be done in two ways, depending on the type of backup you desire. A SQL dump is perhaps the most portable form for keeping backups. The standard way to generate one is using the CLP .dump
command, as shown in the previous section. From the command line, this is done as follows:
sqlite3 test.db .dump > test.sql
Within the shell, you can redirect output to an external file, issue the command, and restore output to the screen as follows:
sqlite> .output file.sql
sqlite> .dump
sqlite> .output stdout
sqlite> .exit
Likewise, importing a database is most easily done by providing the SQL dump as an input stream to the CLP:
sqlite3 test.db < test.sql
This assumes that test.db
does not already exist. If it does, then things may still work if the contents of test.sql
are different from those of test.db
. You will of course get errors if test.sql
contains objects that already reside within test.db
or contains data that violates primary key or foreign key constraints (though see the PRAGMA
discussion in subsequent chapters on how to finesse this behavior).
Making a binary backup of a database is little more than a file copy. One small operation you may want to perform beforehand is a database vacuum, which will free up any unused space created from deleted objects. This will provide you with a smaller resulting file from the binary copy:
sqlite3 test.db vacuum
cp test.db test.backup
As a general rule, binary backups are not as portable as SQL backups. On the whole, SQLite does have good backward compatibility and is binary compatible across all platforms for a given database format. However, for long-term backups, it is always a good idea to use SQL form. If size is an issue, SQL format (raw text) usually yields a good compression ratio.
Caution No matter how good you think your chosen backup approach is, remember you are only as good as your last successful restore. Test your restore procedure if you need to rely on it—otherwise, you'll be remembered for one failed restore, regardless of how many successful backups you took.
Finally, if you've worked with other databases, “dropping” a database in SQLite, like binary backups, is a simple file operation: you simply delete the database file you want to drop.
The primary means by which to obtain logical database information, such as table names, DDL statements, and so on, is using the sqlite_master
view, which provides detailed information about all objects contained in a given database.
If you want information on the physical database structure, you can use a tool called SQLite Analyzer, which can be downloaded in binary form from the SQLite website. SQLite Analyzer provides detailed technical information about the on-disk structure of a SQLite database. This information includes a detailed breakdown of database, table, and index statistics for individual objects and in aggregate. It provides everything from database properties such as page size, number of tables, indexes, file size, and average page density (utilization) to detailed descriptions of individual database objects. Following the report is a detailed list of definitions explaining all terms used within the report. A partial output of sqlite_analyzer
is as follows:
fuzzy@linux:/tmp$ sqlite3_analyzer test.db
/** Disk-Space Utilization Report For test.db
*** As of 2010-May-07 20:26:23
Page size in bytes.................... 1024
Pages in the whole file (measured).... 3
Pages in the whole file (calculated).. 3
Pages that store data................. 3 100.0%
Pages on the freelist (per header).... 0 0.0%
Pages on the freelist (calculated).... 0 0.0%
Pages of auto-vacuum overhead......... 0 0.0%
Number of tables in the database...... 2
Number of indices..................... 1
Number of named indices............... 1
Automatically generated indices....... 0
Size of the file in bytes............. 3072
Bytes of user payload stored.......... 26 0.85%
*** Page counts for all tables with their indices ********************
TEST.................................. 2 66.7%
SQLITE_MASTER......................... 1 33.3%
*** All tables and indices *******************************************
Percentage of total database.......... 100.0%
Number of entries..................... 11
Bytes of storage consumed............. 3072
Bytes of payload...................... 235 7.6%
Average payload per entry............. 21.36
Average unused bytes per entry........ 243.00
Maximum payload per entry............. 72
Entries that use overflow............. 0 0.0%
Primary pages used.................... 3
Overflow pages used................... 0
Total pages used...................... 3
Unused bytes on primary pages......... 2673 87.0%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 2673 87.0%
*** Table TEST and all its indices ***********************************
Percentage of total database.......... 66.7%
Number of entries..................... 8
Bytes of storage consumed............. 2048
Bytes of payload...................... 60 2.9%
Average payload per entry............. 7.50
Average unused bytes per entry........ 243.00
Maximum payload per entry............. 10
Entries that use overflow............. 0 0.0%
Primary pages used.................... 2
Overflow pages used................... 0
Total pages used...................... 2
Unused bytes on primary pages......... 1944 94.9%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 1944 94.9%
SQLite Analyzer is provided in binary form on the SQLite website for Linux, Mac OS X, and Windows. SQLite Analyzer can be built from the source using the Unix makefile provided. From the build directory, issue the following command:
make sqlite3_analyzer
You must, however, have Tcl support configured in the build settings because SQLite Analyzer uses the Tcl extension to perform most of its work. Refer to “Compiling SQLite from Source” for more information.
There are many other open source and commercial programs available with which to work with SQLite. Good graphical, cross-platform tools include the following:
SQLite Database Browser (
http://sqlitebrowser.sourceforge.net
): Allows users to manage databases, tables, and indexes, as well as import and export them. Users can interactively run SQL queries and inspect the results, as well as examine a log of all SQL commands issued. It recently received a major upgrade to version 2.SQLiteman (
http://www.sqliteman.com
): A cross-platform program that's targeted at people managing and administering SQLite databases. It allows for general management of databases, tables, indexes, and triggers, as well as other common management tasks.SQLiteManager (
www.sqlabs.net/sqlitemanager.php
): A commercial software package designed for working with and administering SQLite. Users can manage database objects, execute queries, and save SQL, as well as create reports with flexible report templates. It includes its own high-level scripting language to complement its SQL capabilities.
These are just the cross-platform tools. Many more tools are available that can be used with just about any programming, end-user, or management environment. You can find more information on such packages on the SQLite wiki (www.sqlite.org/cvstrac/wiki?p=ManagementTools
).
No matter what platform you work on, SQLite is easy to install and build. Windows, Mac OS X, and Linux users can obtain binaries directly from the SQLite website. Users of many other operating systems can also obtain binaries using their native—or even third-party—package systems.
The common way to work with SQLite across all platforms is using the SQLite command-line program (CLP). This program operates as both a command-line tool and an interactive shell. You can issue queries and do essential database administration tasks such as creating tables, indexes, and views as well as exporting and importing data. SQLite databases are contained in single operating system files, so doing things like backups are very simple—just copy the file. For long-term backups, however, it is always best to dump the database in SQL format, because this is portable across SQLite versions.
In the next few chapters, you will be using the CLP to explore SQL and the database aspects of SQLite. We will start with the basics of using SQL with SQLite in Chapter 3 and move to more advanced SQL in Chapter 4.