The Definitive Guide to SQLite, Second Edition

The Definitive Guide to SQLite, Second Edition

by Grant Allen, Mike Owens
Released November 2010
Publisher(s): Apress
ISBN: 9781430232254

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Outside of the world of enterprise computing, there is one database that enables a huge range of software and hardware to flex relational database capabilities, without the baggage and cost of traditional database management systems. That database is SQLite—an embeddable database with an amazingly small footprint, yet able to handle databases of enormous size. SQLite comes equipped with an array of powerful features available through a host of programming and development environments. It is supported by languages such as C, Java, Perl, PHP, Python, Ruby, TCL, and more.

The Definitive Guide to SQLite, Second Edition is devoted to complete coverage of the latest version of this powerful database. It offers a thorough overview of SQLite's capabilities and APIs. The book also uses SQLite as the basis for helping newcomers make their first foray into database development. In only a short time you can be writing programs as diverse as a server-side browser plug-in or the next great iPhone or Android application!

  • Learn about SQLite extensions for C, Java, Perl, PHP, Python, Ruby, and Tcl.

  • Get solid coverage of SQLite internals.

  • Explore developing iOS (iPhone) and Android applications with SQLite.

SQLite is the solution chosen for thousands of products around the world, from mobile phones and GPS devices to set-top boxes and web browsers. You almost certainly use SQLite every day without even realizing it!

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. About the Authors
  8. About the Technical Reviewer
  9. Acknowledgments
  10. Introduction
    1. Prerequisites
    2. How This Book Is Organized
    3. Obtaining the Source Code of the Examples
  11. CHAPTER 1: Introducing SQLite
    1. An Embedded Database
    2. A Developer's Database
    3. An Administrator's Database
    4. SQLite History
    5. Who Uses SQLite
    6. Architecture
      1. The Interface
      2. The Compiler
      3. The Virtual Machine
      4. The Back End
      5. Utilities and Test Code
    7. SQLite's Features and Philosophy
      1. Zero Configuration
      2. Portability
      3. Compactness
      4. Simplicity
      5. Flexibility
      6. Liberal Licensing
      7. Reliability
      8. Convenience
    8. Performance and Limitations
    9. Who Should Read This Book
    10. How This Book Is Organized
    11. Additional Information
    12. Summary
  12. CHAPTER 2: Getting Started
    1. Where to Get SQLite
    2. SQLite on Windows
      1. Getting the Command-Line Program
      2. Getting the SQLite DLL
      3. Compiling the SQLite Source Code on Windows
      4. Building the SQLite DLL with Microsoft Visual C++
      5. Building a Dynamically Linked SQLite Client with Visual C++
      6. Building SQLite with MinGW
    3. SQLite on Linux, Mac OS X, and Other POSIX Systems
      1. Binaries and Packages
      2. Compiling SQLite from Source
    4. The Command-Line Program
      1. The CLP in Shell Mode
      2. The CLP in Command-Line Mode
    5. Database Administration
      1. Creating a Database
      2. Getting Database Schema Information
      3. Exporting Data
      4. Importing Data
      5. Formatting
      6. Exporting Delimited Data
      7. Performing Unattended Maintenance
      8. Backing Up a Database
      9. Getting Database File Information
    6. Other SQLite Tools
    7. Summary
  13. CHAPTER 3: SQL for SQLite
    1. The Example Database
      1. Installation
      2. Running the Examples
    2. Syntax
      1. Commands
      2. Literals
      3. Keywords and Identifiers
      4. Comments
    3. Creating a Database
      1. Creating Tables
      2. Altering Tables
    4. Querying the Database
      1. Relational Operations
      2. select and the Operational Pipeline
      3. Filtering
      4. Limiting and Ordering
      5. Functions and Aggregates
      6. Grouping
      7. Removing Duplicates
      8. Joining Tables
      9. Names and Aliases
      10. Subqueries
      11. Compound Queries
      12. Conditional Results
      13. Handling Null in SQLite
      14. Summary
  14. CHAPTER 4: Advanced SQL for SQLite
    1. Modifying Data
      1. Inserting Records
      2. Updating Records
      3. Deleting Records
    2. Data Integrity
      1. Entity Integrity
      2. Domain Integrity
      3. Storage Classes
      4. Views
      5. Indexes
      6. Triggers
    3. Transactions
      1. Transaction Scopes
      2. Conflict Resolution
      3. Database Locks
      4. Deadlocks
      5. Transaction Types
    4. Database Administration
      1. Attaching Databases
      2. Cleaning Databases
      3. Database Configuration
      4. The System Catalog
      5. Viewing Query Plans
    5. Summary
  15. CHAPTER 5: SQLite Design and Concepts
    1. The API
      1. The Principal Data Structures
      2. The Core API
      3. Operational Control
      4. Using Threads
    2. The Extension API
      1. Creating User-Defined Functions
      2. Creating User-Defined Aggregates
      3. Creating User-Defined Collations
    3. Transactions
      1. Transaction Life Cycles
      2. Lock States
      3. Read Transactions
      4. Write Transactions
    4. Tuning the Page Cache
      1. Transitioning to Exclusive
      2. Sizing the Page Cache
    5. Waiting for Locks
      1. Using a Busy Handler
      2. Using the Right Transaction
    6. Code
      1. Using Multiple Connections
      2. The Importance of Finalizing
      3. Shared Cache Mode
    7. Summary
  16. CHAPTER 6: The Core C API
    1. Wrapped Queries
      1. Connecting and Disconnecting
      2. The exec Query
      3. The Get Table Query
    2. Prepared Queries
      1. Compilation
      2. Execution
      3. Finalization and Reset
    3. Fetching Records
      1. Getting Column Information
      2. Getting Column Values
      3. A Practical Example
    4. Parameterized Queries
      1. Numbered Parameters
      2. Named Parameters
      3. Tcl Parameters
    5. Errors and the Unexpected
      1. Handling Errors
      2. Handling Busy Conditions
      3. Handling Schema Changes
    6. Operational Control
      1. Commit Hooks
      2. Rollback Hooks
      3. Update Hooks
      4. Authorizer Functions
    7. Threads
      1. Shared Cache Mode
      2. Threads and Memory Management
    8. Summary
  17. CHAPTER 7: The Extension C API
    1. The API
      1. Registering Functions
      2. The Step Function
      3. Return Values
    2. Functions
      1. Return Values
      2. Arrays and Cleanup Handlers
      3. Error Conditions
      4. Returning Input Values
    3. Aggregates
      1. Registration Function
      2. A Practical Example
    4. Collations
      1. Collation Defined
      2. A Simple Example
      3. Collation on Demand
    5. Summary
  18. CHAPTER 8: Language Extensions
    1. Selecting an Extension
    2. Perl
      1. Installation
      2. Connecting
      3. Query Processing
      4. Parameter Binding
      5. User-Defined Functions
      6. Aggregates
    3. Python
      1. Installation
      2. Connecting
      3. Query Processing
      4. Parameter Binding
      5. User-Defined Functions
      6. Aggregates
      7. APSW as an Alternative Python Interface
    4. Ruby
      1. Installation
      2. Connecting
      3. Query Processing
      4. Parameter Binding
      5. User-Defined Functions
    5. Java
      1. Installation
      2. Connecting
      3. Query Processing
      4. User-Defined Functions and Aggregates
      5. JDBC
    6. Tcl
      1. Installation
      2. Connecting
      3. Query Processing
      4. User-Defined Functions
    7. PHP
      1. Installation
      2. Connections
      3. Queries
      4. User-Defined Functions and Aggregates
    8. Summary
  19. CHAPTER 9: iOS Development with SQLite
    1. Prerequisites for SQLite iOS Development
      1. Signing Up for Apple Developer
      2. Downloading and Installing Xcode and the iOS SDK
      3. Alternatives to Xcode
    2. Building the iSeinfeld iOS SQLite Application
      1. Step 1: Creating a New Xcode Project
      2. Step 2: Adding the SQLite Framework to Your Project
      3. Step 3: Preparing the Foods Database
      4. Step 4: Creating Classes for the Food Data
      5. Step 5: Accessing and Querying the SQLite DB
      6. Step 6: Final Polish and Wiring for iSeinfeld
    3. iSeinfeld in Action!
    4. Working with Large SQLite Databases Under iOS
    5. Summary
  20. CHAPTER 10: Android Development with SQLite
    1. Prerequisites for SQLite Android Development
      1. Check Prerequisites and the JDK
      2. Downloading and Installing the Android SDK Starter Package
      3. Downloading and Installing the Android Developer Tools
      4. Adding Android Platforms and Components
    2. The Android SQLite Classes and Interfaces
      1. Using the Basic Helper Class, SQLiteOpenHelper
      2. Working with the SQLiteDatabase Class
      3. Applying SQLiteOpenHelper and SQLiteDatabase in Practice
      4. Querying SQLite with SQLiteQueryBuilder
    3. Building the Seinfeld Android SQLite Application
      1. Creating a New Android Project
      2. Adding the Seinfeld SQLite Database to Your Project
      3. Querying the Foods Table
      4. Defining the User Interface
      5. Linking the Data and User Interface
      6. Viewing the Finished Seinfeld Application
    4. Care and Feeding for SQLite Android Applications
      1. Database Backup for Android
      2. Working with Large SQLite Databases Under Android
    5. Summary
  21. CHAPTER 11: SQLite Internals and New Features
    1. The B-Tree and Pager Modules
      1. Database File Format
      2. The B-Tree API
    2. Manifest Typing, Storage Classes, and Affinity
      1. Manifest Typing
      2. Type Affinity
      3. Affinities and Storage
    3. Write Ahead Logging
      1. How WAL Works
      2. Activation and Configuration WAL
      3. WAL Advantages and Disadvantages
      4. Operational Issues with WAL-Enabled SQLite Databases
    4. Summary
  22. Index

Product information

  • Title: The Definitive Guide to SQLite, Second Edition
  • Author(s): Grant Allen, Mike Owens
  • Release date: November 2010
  • Publisher(s): Apress
  • ISBN: 9781430232254