SQLite

Introduction

Welcome to SQLite

SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. In fact one of SQLite's greatest strengths is that it is extremely lightweight indeed yet still manages to retain a large number of features.

Why Use SQLite?

There are many reasons for choosing SQLite, including

• Performance—SQLite performs database operations efficiently and is faster than other free databases such as MySQL and PostgreSQL.
• Size—SQLite has a small memory footprint and only a single library is required to access databases, making it ideal for embedded database applications.
• Portability—SQLite runs on many platforms and its databases can be ported easily with no client/server setup or ongoing administration required.
• Stability—SQLite is ACID-compliant, meeting all four criteria—Atomicity, Consistency, Isolation, and Durability.
• SQL support—SQLite implements a large subset of the ANSI-92 SQL standard, including views, subqueries, and triggers.
• Interfaces—SQLite has language APIs for C/C++, PHP, Perl, Python, Tcl, and many more beyond those covered in this book.
• Cost—SQLite is in the public domain and therefore is free to use for any purpose without cost and can be freely redistributed.

Who This Book Is For

This book is aimed at intermediate- to advanced-level programmers looking to include database functionality within their applications. You should have at least a basic working knowledge of one of the languages covered by this book—C/C++, PHP, Perl, Python, or Tcl. The underlying library is written in C/C++; however, it is not necessary to understand that language in order to use the full capabilities of SQLite in your applications.

If you are a new programmer you should still be able to pick up SQLite fairly quickly, but this book does not cover programming basics in any of the languages for which there is a SQLite interface. The benefits of SQLite are only realized through using a programming API as it does not include the tools required to operate as a standalone database system.

It is not a prerequisite to have used a relational database in the past. If SQLite will be the first SQL-based database you have encountered, the book gives a thorough SQL tutorial covering the syntax as understood by SQLite. SQL veterans will still benefit from reviewing the sections that cover features included and omitted by SQLite.

How the Book Is Organized

This book is organized into three parts.

Part I—General SQLite Use

• Chapter 1: Getting Started—Gives some background on SQLite and discusses its strengths and weaknesses. Covers a few basic SQL commands to get things going and looks at the interactive interfaces to SQLite.
• Chapter 2: Working with Data—A concise SQLite tutorial introduces working with the SQLite back end. Discusses programming and database design issues related to SQLite.
• Chapter 3: SQLite Syntax and Use—Examines SQL syntax as supported by SQLite and suggests workarounds for the unsupported features.
• Chapter 4: Query Optimization—Discusses performance considerations related to SQL queries and gives some techniques that can be used to speed up your database application.

Part II—Using SQLite Programming Interfaces

• Chapter 5: The PHP Interface—How to use the SQLite library within PHP scripts to create dynamic, database-driven web pages.
• Chapter 6: The C/C++ Interface—How to write C/C++ programs using the SQLite library.
• Chapter 7: The Perl Interface—How to write Perl scripts using the Database Interface module and SQLite Database Driver.
• Chapter 8: The Tcl Interface—How to write Tcl scripts using the supplied SQLite extension.
• Chapter 9: The Python Interface—How to write Python programs using the PySQLite extension.

Part III—SQLite Administration

• Chapter 10: General Database Administration—Discusses basic administration of SQLite and examines SQLite's internal architecture and the Virtual Database Engine (VDBE).

Versions of Software Covered

At the time of writing, the most recent stable version of SQLite is 2.8.15; however, SQLite 3 is already available as a beta and includes some changes and enhancements over the version 2 series. This book was written with the established, stable, and well-supported version 2 series in mind.

For the other software discussed in this book, the current versions are as follows:

• PHP 5.0.1
• Perl 5.8.5
• Perl::DBI 1.4.3
• DBD::SQLite2 0.32
• Tcl 8.4.7
• Python 2.3.4

Additional Resources

The following are the primary web pages for each of the packages used in this book:

• SQLite—http://www.sqlite.org/
• PHP—http://www.php.net/
• Perl DBI—http://dbi.perl.org/
• Tcl—http://www.tcl.tk/
• Python—http://www.python.org/

For support with any of these technologies the relevant mailing list is a good place to start. Instructions on joining them can be found in the following locations:

• SQLite—http://www.sqlite.org/support.html
• PHP—http://www.php.net/mailing-lists.php
• Perl DBI—http://dbi.perl.org/support/
• Tcl—http://wiki.tcl.tk/1301
• Python—http://www.python.org/community/lists.html

© Copyright Pearson Education. All rights reserved.