C H A P T E R  2

Getting Started

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.

Where to Get SQLite

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.

SQLite on Windows

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.

Getting the Command-Line Program

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:

1. Open your favorite browser, and navigate to the SQLite home page at www.sqlite.org.
2. Click the download link at the top of the page. This will take you to the download page.
3. Scroll down to the section Precompiled Binaries For Windows, where you will find filenames along the lines of 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.
4. Unzip the file, either using Windows built-in support for zip files or using your favorite third-party compression tool such as WinZip or 7-Zip. The zip file should contain a single file named 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.

5. Open a command shell. You can do this in different ways. Using the Windows Start menu, select Start » Run. Type 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

6. Within the shell, type 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.

Getting the SQLite DLL

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:

1. Go to the SQLite home page, www.sqlite.com, and choose the download link at the top of the page. This will take you to the download page.
2. On the download page, find the section Precompiled Binaries For Windows.
3. Locate the DLL zip file. This file will have the description “This is a DLL of the SQLite library without the TCL bindings.” The filename will have the form 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.
4. Download and unzip the file. The extracted contents should include two files: the actual DLL file (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:

You should see normal library generation output similar to the following example:

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.

Compiling the SQLite Source Code on Windows

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

The Stable Source Distribution

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:

1. Go to the SQLite website, www.sqlite.org. Follow the download link, which will take you to the download page.
2. On the download page, find the Source Code section.
3. The first file listed is the recommended zip file containing the source code and suitable for compilation for Windows. The file will be 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.

4. Extract/unzip the file to a directory of your choosing. The extracted contents will be the complete SQLite version 3 source code, suitable for Windows.

Anonymous Fossil Source Control

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:

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.

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

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.

Now you can tell Fossil to check out the current incarnation of the source, using the open command:

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.

Building the SQLite DLL with Microsoft Visual C++

To build the SQLite DLL from source using Visual C++, follow these steps:

1. Start Visual Studio. Create a new DLL project within the unpacked SQLite source directory. Do this by selecting File New Project. Under Project Types (Figure 2-4), select Visual C++ Projects, and then select Win32. Choose the Win32 Project template. In the Location text box, enter the folder name that contains your SQLite source folder. In this example, it would be 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

2. Next, the Win 32 Application Wizard will automatically open (Figure 2-5). Choose Application Settings, and set the application type to DLL. Be sure to select the Empty Project box. Click Finish, and this will create a blank DLL project.
   

   Figure 2-5. The Win32 Application Wizard

3. Add SQLite source files and headers to the project. Select Project Add Existing Item. Add all .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.)
4. Specify an export or a module definition (.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

5. From the main menu, select Build Build sqlite to build the DLL.
6. Once you have built the DLL, be sure to create the import library, as described in the section “Getting the SQLite DLL.”

Building a Dynamically Linked SQLite Client with Visual C++

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:

1. From the main menu, select File New Project. Under Project Types, select Visual C++ Projects, and then select Win32. Choose the Win32 Project template. Name the project (shell, for example), and click OK.
2. After this, the Win 32 Application Wizard will automatically open. Choose Application Settings, set the application type to Console Application, and be sure to check the Empty Project box. Click Finish to create a blank executable project.
3. Next you want to add the SQLite CLP source file. Select Project Add Existing Item. In the dialog box that appears, add the source file shell.c.
4. Tell Visual C++ to link against the SQLite DLL. Select Project Properties. In the dialog box that appears, select All Configurations in the Configuration drop-down box. Next select the Linker folder. Select the Input submenu, and within the Additional Dependencies property page, add 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.

Building SQLite with MinGW

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:

1. Open your favorite browser, and navigate to the SourceForge website: sourceforge.net.
2. Search for the TakeOffGW project, or navigate straight to the project page at sourceforge.net/projects/takeoffgw/.
3. Download the latest release of the TakeOffGW network installer, usually just called setup.exe.
4. After downloading the TakeOffGW network installer, run the 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

5. Click Next to continue, and TakeOffGW will download and install all the components required.
6. Download the Linux/Unix SQLite source code distribution. Navigate to 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).
7. TakeOffGW will have placed an icon on your desktop. Double-click that icon to open the environment.
8. Navigate to your temporary directory in which you downloaded the SQLite source distribution. Since this is a Unix-like environment, you will need to use Unix file system conventions. For example, to get to c:Temp, you would type cd /c/Temp.
9. Unpack the SQLite tarball. Issue this command: tar -xzvf sqlite-amalgamation-3.6.23.1.tar.gz.
10. Move into the unpacked directory:
    cd sqlite-3.6.32.1
11. Create the makefile. For the SQLite binary, run this:
    ./configure
12. Build the source:
    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 on Linux, Mac OS X, and Other POSIX Systems

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.

Binaries and Packages

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, and apt-get, in addition to its own utility—fink. You can download Fink from http://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, where x, y, and z (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

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:

1. Download the Linux/Unix SQLite tarball (source code) from the SQLite website. At the time of this writing, the current version is sqlite-amalgamation-3.6.23.1.tar.gz. Place it in a directory (e.g., /tmp).
2. Navigate to your build directory:
   cd /tmp
3. Unpack the SQLite tarball:
   tar -xzvf sqlite-amalgamation-3.6.23.1.tar.gz
4. Move into the unpacked directory:
   cd sqlite-3.6.23.1
5. Create the makefile:
   ./configure
6. Other options, such as the installation directory, are also available. For a complete list of configure options, run this:
   ./configure --help
7. Build the source:
   make
8. As root, install:
   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:

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 Command-Line Program

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.

The CLP in Shell Mode

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.

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:

---

---

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.

The CLP in Command-Line Mode

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:

---

---

The CLP in command-line mode takes the following arguments:

• A list of options (optional)
• A database filename (optional)
• A SQL command to execute (optional)

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.

Database Administration

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.

Creating a Database

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:

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:

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:

• A primary key column called 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.
• A simple text field called value.

Let's add a few rows to the table:

Now fetch them back:

---

---

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():

---

---

Before we quit, let's add an index and a view to the database. These will come in handy in the examples that follow:

To exit the shell, issue the .exit command:

On Windows, you can also terminate the shell by using the key sequence Ctrl+C. On Unix, you can use Ctrl+D.

Getting Database Schema Information

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:

---

---

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

---

---

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:

---

---

---

---

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.

Table 2-1. SQLite Master Table Schema

Name	Description
type	The object's type (table, index, view, trigger)
name	The object's name
tbl_name	The table the object is associated with
rootpage	The object's root page index in the database (where it begins)
sql	The object's SQL definition (DDL)

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

---

---

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.

Exporting Data

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:

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.

Importing Data

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:

---

---

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:

Formatting

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

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:

The contents of file.csv now appear as shown next.

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:

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.

Exporting Delimited Data

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:

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:

The CLP, therefore, makes it easy to both import and export text-delimited data to and from the database.

Performing Unattended Maintenance

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.

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:

To make it useful, we should redirect the output to a file:

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:

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:

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:

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:

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

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:

Within the shell, you can redirect output to an external file, issue the command, and restore output to the screen as follows:

Likewise, importing a database is most easily done by providing the SQL dump as an input stream to the CLP:

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:

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.

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.

Getting Database File Information

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:

---

---

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:

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.

Other SQLite Tools

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

Summary

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.