You are previewing The Definitive Guide to SQLite, Second Edition.
O'Reilly logo
The Definitive Guide to SQLite, Second Edition

Book Description

The Definitive Guide to SQLite, Second Edition is your one-stop resource for integrating one of the most advanced and innovative open source database management systems into your development projects. SQLite is freely-available and comes with an amazingly small foot print of less than 300 kilobytes for a full-blown implementation. It's perfect for mobile and other embedded implementations, including the iPhone and Android platforms. It's also the default database distributed with PHP.

In The Definitive Guide to SQLite, Second Edition, Authors Grant Allen and Mike Owen provide complete coverage of SQLite's powerful array of features. You'll learn to use SQLite with PHP, C++, Java, and more. You'll see how SQLite's file-based approach can easily handle databases as large as two terabytes. You'll also enjoy complete coverage of the API, and learn to embed SQLite within mobile applications on the iPhone, and on Android platforms.

  • Demonstrates SQLite's use in iPhone and Android development.

  • Helps you integrate with popular languages such as PHP, C++, Java, and more.

  • Serves as an excellent tutorial, and ongoing reference.

Table of Contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Introduction
    1. Prerequisites
    2. How This Book Is Organized
    3. Obtaining the Source Code of the Examples
  6. 1. Introducing SQLite
    1. 1.1. An Embedded Database
    2. 1.2. A Developer's Database
    3. 1.3. An Administrator's Database
    4. 1.4. SQLite History
    5. 1.5. Who Uses SQLite
    6. 1.6. Architecture
      1. 1.6.1. The Interface
      2. 1.6.2. The Compiler
      3. 1.6.3. The Virtual Machine
      4. 1.6.4. The Back End
      5. 1.6.5. Utilities and Test Code
    7. 1.7. SQLite's Features and Philosophy
      1. 1.7.1. Zero Configuration
      2. 1.7.2. Portability
      3. 1.7.3. Compactness
      4. 1.7.4. Simplicity
      5. 1.7.5. Flexibility
      6. 1.7.6. Liberal Licensing
      7. 1.7.7. Reliability
      8. 1.7.8. Convenience
    8. 1.8. Performance and Limitations
    9. 1.9. Who Should Read This Book
    10. 1.10. How This Book Is Organized
    11. 1.11. Additional Information
    12. 1.12. Summary
  7. 2. Getting Started
    1. 2.1. Where to Get SQLite
    2. 2.2. SQLite on Windows
      1. 2.2.1. Getting the Command-Line Program
      2. 2.2.2. Getting the SQLite DLL
      3. 2.2.3. Compiling the SQLite Source Code on Windows
        1. 2.2.3.1. The Stable Source Distribution
        2. 2.2.3.2. Anonymous Fossil Source Control
      4. 2.2.4. Building the SQLite DLL with Microsoft Visual C++
      5. 2.2.5. Building a Dynamically Linked SQLite Client with Visual C++
      6. 2.2.6. Building SQLite with MinGW
    3. 2.3. SQLite on Linux, Mac OS X, and Other POSIX Systems
      1. 2.3.1. Binaries and Packages
      2. 2.3.2. Compiling SQLite from Source
    4. 2.4. The Command-Line Program
      1. 2.4.1. The CLP in Shell Mode
      2. 2.4.2. The CLP in Command-Line Mode
    5. 2.5. Database Administration
      1. 2.5.1. Creating a Database
      2. 2.5.2. Getting Database Schema Information
      3. 2.5.3. Exporting Data
      4. 2.5.4. Importing Data
      5. 2.5.5. Formatting
      6. 2.5.6. Exporting Delimited Data
      7. 2.5.7. Performing Unattended Maintenance
      8. 2.5.8. Backing Up a Database
      9. 2.5.9. Getting Database File Information
    6. 2.6. Other SQLite Tools
    7. 2.7. Summary
  8. 3. SQL for SQLite
    1. 3.1. The Example Database
      1. 3.1.1. Installation
      2. 3.1.2. Running the Examples
    2. 3.2. Syntax
      1. 3.2.1. Commands
      2. 3.2.2. Literals
      3. 3.2.3. Keywords and Identifiers
      4. 3.2.4. Comments
    3. 3.3. Creating a Database
      1. 3.3.1. Creating Tables
      2. 3.3.2. Altering Tables
    4. 3.4. Querying the Database
      1. 3.4.1. Relational Operations
      2. 3.4.2. select and the Operational Pipeline
      3. 3.4.3. Filtering
        1. 3.4.3.1. Values
        2. 3.4.3.2. Operators
        3. 3.4.3.3. Binary Operators
        4. 3.4.3.4. Logical Operators
        5. 3.4.3.5. The LIKE and GLOB Operators
      4. 3.4.4. Limiting and Ordering
      5. 3.4.5. Functions and Aggregates
      6. 3.4.6. Grouping
      7. 3.4.7. Removing Duplicates
      8. 3.4.8. Joining Tables
        1. 3.4.8.1. Inner Joins
        2. 3.4.8.2. Cross Joins
        3. 3.4.8.3. Outer Joins
        4. 3.4.8.4. Natural Joins
        5. 3.4.8.5. Preferred Syntax
      9. 3.4.9. Names and Aliases
      10. 3.4.10. Subqueries
      11. 3.4.11. Compound Queries
      12. 3.4.12. Conditional Results
      13. 3.4.13. Handling Null in SQLite
    5. 3.5. Summary
  9. 4. Advanced SQL for SQLite
    1. 4.1. Modifying Data
      1. 4.1.1. Inserting Records
        1. 4.1.1.1. Inserting One Row
        2. 4.1.1.2. Inserting a Set of Rows
        3. 4.1.1.3. Inserting Multiple Rows
      2. 4.1.2. Updating Records
      3. 4.1.3. Deleting Records
    2. 4.2. Data Integrity
      1. 4.2.1. Entity Integrity
        1. 4.2.1.1. Unique Constraints
        2. 4.2.1.2. Primary Key Constraints
      2. 4.2.2. Domain Integrity
        1. 4.2.2.1. Default Values
        2. 4.2.2.2. NOT NULL Constraints
        3. 4.2.2.3. Check Constraints
        4. 4.2.2.4. Foreign Key Constraints
        5. 4.2.2.5. Collations
      3. 4.2.3. Storage Classes
      4. 4.2.4. Views
      5. 4.2.5. Indexes
        1. 4.2.5.1. Collations
        2. 4.2.5.2. Index Utilization
      6. 4.2.6. Triggers
        1. 4.2.6.1. Update Triggers
        2. 4.2.6.2. Error Handling
        3. 4.2.6.3. Updatable Views
    3. 4.3. Transactions
      1. 4.3.1. Transaction Scopes
      2. 4.3.2. Conflict Resolution
      3. 4.3.3. Database Locks
      4. 4.3.4. Deadlocks
      5. 4.3.5. Transaction Types
    4. 4.4. Database Administration
      1. 4.4.1. Attaching Databases
      2. 4.4.2. Cleaning Databases
      3. 4.4.3. Database Configuration
        1. 4.4.3.1. The Connection Cache Size
        2. 4.4.3.2. Getting Database Information
        3. 4.4.3.3. Synchronous Writes
        4. 4.4.3.4. Temporary Storage
        5. 4.4.3.5. Page Size, Encoding, and Autovacuum
        6. 4.4.3.6. Debugging
      4. 4.4.4. The System Catalog
      5. 4.4.5. Viewing Query Plans
    5. 4.5. Summary
  10. 5. SQLite Design and Concepts
    1. 5.1. The API
      1. 5.1.1. The Principal Data Structures
        1. 5.1.1.1. Connections and Statements
        2. 5.1.1.2. The B-tree and Pager
      2. 5.1.2. The Core API
        1. 5.1.2.1. Connecting to a Database
        2. 5.1.2.2. Executing Prepared Queries
        3. 5.1.2.3. Using Parameterized SQL
        4. 5.1.2.4. Executing Wrapped Queries
        5. 5.1.2.5. Handling Errors
        6. 5.1.2.6. Formatting SQL Statements
      3. 5.1.3. Operational Control
      4. 5.1.4. Using Threads
    2. 5.2. The Extension API
      1. 5.2.1. Creating User-Defined Functions
      2. 5.2.2. Creating User-Defined Aggregates
      3. 5.2.3. Creating User-Defined Collations
    3. 5.3. Transactions
      1. 5.3.1. Transaction Life Cycles
      2. 5.3.2. Lock States
      3. 5.3.3. Read Transactions
      4. 5.3.4. Write Transactions
        1. 5.3.4.1. The Reserved State
        2. 5.3.4.2. The Pending State
        3. 5.3.4.3. The Exclusive State
        4. 5.3.4.4. Autocommit and Efficiency
    4. 5.4. Tuning the Page Cache
      1. 5.4.1. Transitioning to Exclusive
      2. 5.4.2. Sizing the Page Cache
    5. 5.5. Waiting for Locks
      1. 5.5.1. Using a Busy Handler
      2. 5.5.2. Using the Right Transaction
    6. 5.6. Code
      1. 5.6.1. Using Multiple Connections
      2. 5.6.2. The Importance of Finalizing
      3. 5.6.3. Shared Cache Mode
    7. 5.7. Summary
  11. 6. The Core C API
    1. 6.1. Wrapped Queries
      1. 6.1.1. Connecting and Disconnecting
      2. 6.1.2. The exec Query
      3. 6.1.3. The Get Table Query
    2. 6.2. Prepared Queries
      1. 6.2.1. Compilation
      2. 6.2.2. Execution
      3. 6.2.3. Finalization and Reset
    3. 6.3. Fetching Records
      1. 6.3.1. Getting Column Information
      2. 6.3.2. Getting Column Values
      3. 6.3.3. A Practical Example
    4. 6.4. Parameterized Queries
      1. 6.4.1. Numbered Parameters
      2. 6.4.2. Named Parameters
      3. 6.4.3. Tcl Parameters
    5. 6.5. Errors and the Unexpected
      1. 6.5.1. Handling Errors
      2. 6.5.2. Handling Busy Conditions
        1. 6.5.2.1. User-Defined Busy Handlers
        2. 6.5.2.2. Advice
      3. 6.5.3. Handling Schema Changes
    6. 6.6. Operational Control
      1. 6.6.1. Commit Hooks
      2. 6.6.2. Rollback Hooks
      3. 6.6.3. Update Hooks
      4. 6.6.4. Authorizer Functions
    7. 6.7. Threads
      1. 6.7.1. Shared Cache Mode
        1. 6.7.1.1. Read Uncommitted Isolation Level
        2. 6.7.1.2. Unlock Notification
      2. 6.7.2. Threads and Memory Management
    8. 6.8. Summary
  12. 7. The Extension C API
    1. 7.1. The API
      1. 7.1.1. Registering Functions
      2. 7.1.2. The Step Function
      3. 7.1.3. Return Values
    2. 7.2. Functions
      1. 7.2.1. Return Values
      2. 7.2.2. Arrays and Cleanup Handlers
      3. 7.2.3. Error Conditions
      4. 7.2.4. Returning Input Values
    3. 7.3. Aggregates
      1. 7.3.1. Registration Function
      2. 7.3.2. A Practical Example
        1. 7.3.2.1. The Step Function
        2. 7.3.2.2. The Aggregate Context
        3. 7.3.2.3. The Finalize Function
        4. 7.3.2.4. Results
    4. 7.4. Collations
      1. 7.4.1. Collation Defined
        1. 7.4.1.1. How Collation Works
        2. 7.4.1.2. Standard Collation Types
      2. 7.4.2. A Simple Example
        1. 7.4.2.1. The Compare Function
        2. 7.4.2.2. The Test Program
        3. 7.4.2.3. Results
      3. 7.4.3. Collation on Demand
    5. 7.5. Summary
  13. 8. Language Extensions
    1. 8.1. Selecting an Extension
    2. 8.2. Perl
      1. 8.2.1. Installation
      2. 8.2.2. Connecting
      3. 8.2.3. Query Processing
      4. 8.2.4. Parameter Binding
      5. 8.2.5. User-Defined Functions
      6. 8.2.6. Aggregates
    3. 8.3. Python
      1. 8.3.1. Installation
      2. 8.3.2. Connecting
      3. 8.3.3. Query Processing
      4. 8.3.4. Parameter Binding
      5. 8.3.5. User-Defined Functions
      6. 8.3.6. Aggregates
      7. 8.3.7. APSW as an Alternative Python Interface
    4. 8.4. Ruby
      1. 8.4.1. Installation
      2. 8.4.2. Connecting
      3. 8.4.3. Query Processing
      4. 8.4.4. Parameter Binding
      5. 8.4.5. User-Defined Functions
    5. 8.5. Java
      1. 8.5.1. Installation
      2. 8.5.2. Connecting
      3. 8.5.3. Query Processing
      4. 8.5.4. User-Defined Functions and Aggregates
      5. 8.5.5. JDBC
    6. 8.6. Tcl
      1. 8.6.1. Installation
      2. 8.6.2. Connecting
      3. 8.6.3. Query Processing
      4. 8.6.4. User-Defined Functions
    7. 8.7. PHP
      1. 8.7.1. Installation
      2. 8.7.2. Connections
      3. 8.7.3. Queries
      4. 8.7.4. User-Defined Functions and Aggregates
    8. 8.8. Summary
  14. 9. iOS Development with SQLite
    1. 9.1. Prerequisites for SQLite iOS Development
      1. 9.1.1. Signing Up for Apple Developer
      2. 9.1.2. Downloading and Installing Xcode and the iOS SDK
      3. 9.1.3. Alternatives to Xcode
    2. 9.2. Building the iSeinfeld iOS SQLite Application
      1. 9.2.1. Step 1: Creating a New Xcode Project
      2. 9.2.2. Step 2: Adding the SQLite Framework to Your Project
      3. 9.2.3. Step 3: Preparing the Foods Database
      4. 9.2.4. Step 4: Creating Classes for the Food Data
        1. 9.2.4.1. The Food Class
        2. 9.2.4.2. The FoodViewController Class
      5. 9.2.5. Step 5: Accessing and Querying the SQLite DB
      6. 9.2.6. Step 6: Final Polish and Wiring for iSeinfeld
    3. 9.3. iSeinfeld in Action!
    4. 9.4. Working with Large SQLite Databases Under iOS
    5. 9.5. Summary
  15. 10. Android Development with SQLite
    1. 10.1. Prerequisites for SQLite Android Development
      1. 10.1.1. Check Prerequisites and the JDK
      2. 10.1.2. Downloading and Installing the Android SDK Starter Package
      3. 10.1.3. Downloading and Installing the Android Developer Tools
      4. 10.1.4. Adding Android Platforms and Components
    2. 10.2. The Android SQLite Classes and Interfaces
      1. 10.2.1. Using the Basic Helper Class, SQLiteOpenHelper
      2. 10.2.2. Working with the SQLiteDatabase Class
        1. 10.2.2.1. Opening and Closing the SQLiteDatabase
        2. 10.2.2.2. Executing General Queries with SQLiteDatabase
        3. 10.2.2.3. Using Convenience Methods with SQLiteDatabase
        4. 10.2.2.4. Managing Transactions with SQLiteDatabase
        5. 10.2.2.5. Using Other SQLiteDatabase Methods
      3. 10.2.3. Applying SQLiteOpenHelper and SQLiteDatabase in Practice
      4. 10.2.4. Querying SQLite with SQLiteQueryBuilder
    3. 10.3. Building the Seinfeld Android SQLite Application
      1. 10.2.5. Creating a New Android Project
      2. 10.2.6. Adding the Seinfeld SQLite Database to Your Project
      3. 10.2.7. Querying the Foods Table
      4. 10.2.8. Defining the User Interface
      5. 10.2.9. Linking the Data and User Interface
      6. 10.2.10. Viewing the Finished Seinfeld Application
    4. 10.4. Care and Feeding for SQLite Android Applications
      1. 10.3.1. Database Backup for Android
      2. 10.3.2. Working with Large SQLite Databases Under Android
    5. 10.5. Summary
  16. 11. SQLite Internals and New Features
    1. 11.1. The B-Tree and Pager Modules
      1. 11.1.1. Database File Format
        1. 11.1.1.1. B-Tree Records
        2. 11.1.1.2. B+Trees
        3. 11.1.1.3. Records and Fields
        4. 11.1.1.4. Hierarchical Data Organization
        5. 11.1.1.5. Overflow Pages
      2. 11.1.2. The B-Tree API
        1. 11.1.2.1. Access and Transaction Functions
        2. 11.1.2.2. Table Functions
        3. 11.1.2.3. Cursor Functions
        4. 11.1.2.4. Record Functions
        5. 11.1.2.5. Configuration Functions
    2. 11.2. Manifest Typing, Storage Classes, and Affinity
      1. 11.2.1. Manifest Typing
      2. 11.2.2. Type Affinity
        1. 11.2.2.1. Column Types and Affinities
      3. 11.2.3. Affinities and Storage
        1. 11.2.3.1. Affinities in Action
        2. 11.2.3.2. Storage Classes and Type Conversions
    3. 11.3. Write Ahead Logging
      1. 11.3.1. How WAL Works
        1. 11.3.1.1. Checkpoints
        2. 11.3.1.2. Concurrency
      2. 11.3.2. Activation and Configuration WAL
      3. 11.3.3. WAL Advantages and Disadvantages
      4. 11.3.4. Operational Issues with WAL-Enabled SQLite Databases
        1. 11.3.4.1. Performance
        2. 11.3.4.2. Recovery
    4. 11.4. Summary