The Definitive Guide to SQLite

369 Pages·2010·5.768 MB·English

by  Owens, Michael;Allen, Grant

Books for professionals By professionals® The eXperT’s Voice® in open source
companion
eBook Available
The Definitive Guide to SQLite
T
Dear Reader,
h
Outside of the world of enterprise computing there is one database that enables e
a huge range of software and hardware to flex relational database capabilities, D
without the baggage and cost of traditional database management systems. Ge The Definitive Guide to
That database is SQLite—an embeddable database with an amazingly small
uf
footprint, yet it can handle databases of enormous size. SQLite comes equipped i
in
Grant Allen with an array of powerful features available through a host of programming and di SQLite
development environments. It is supported by languages such as C, Java, Perl, et
PHP, Python, Ruby, TCL, and more. tiv
The Definitive Guide to SQLite, 2nd Edition is devoted to complete cover- oe
age of the latest version of this powerful database. It offers a thorough over-
view of SQLite’s capabilities and APIs. The book also uses SQLite as the basis for S
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
Q
plug-in or the next great iPhone or Android application!
• You’ll learn about SQLite extensions for C, Java, Perl, PHP, Python, Ruby, and Tcl. L
• You’ll get solid coverage of SQLite internals.
• You’ll explore developing iOS (iPhone) and Android applications with SQLite.
i
Mike Owens
t
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 e
certainly use SQLite every day without even realizing it!
Take control of this compact and powerful tool to
Sincerely,
embed sophisticated SQL databases within your
Grant Allen and Mike Owens
applications
THE APRESS ROADMAP
Companion eBook
Beginning
Database Design
Applied Mathematics
The Definitive Guide for Database SeconD SeCOnD eDiTiOn
to SQLite
Professionals eDiTion
See last page for details Beginning
on $10 eBook version SQL Queries
O Grant Allen Mike Owens
SOURCE CODE ONLINE wA and
ISBN 978-1-4302-3225-4
www.apress.com 54999 enlle
sn

US $49.99
Shelve in:
Databases
User level: 9 781430 232254
Intermediate–Advanced
>
m
o
ok.c
o
b
e
w
o
w
w.
w
w
<
ok
o
B
e
w!
o
W
m
o
d fr
a
o
nl
w
o
D
The Definitive Guide
to SQLite
Second Edition

■ ■ ■
Grant Allen
Mike Owens
The Definitive Guide to SQLite, Second Edition
Copyright © 2010 by Grant Allen and Mike Owens
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-3225-4
ISBN-13 (electronic): 978-1-4302-3226-1
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only
in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of
the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject
to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewer: Richard Hipp
Editorial Board: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick,
Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper,
Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade,
Tom Welsh
Coordinating Editor: Jennifer L. Blackwell
Copy Editor: Kim Wimpsett
Production Support: Patrick Cunningham
Indexer: Julie Grady
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC.,
233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone],
e-mail [e-mail], or visit www.springeronline.com.
For information on translations, please e-mail [e-mail], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our
Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be caused
directly or indirectly by the information contained in this work.
The source code for this book is available to readers at www.apress.com.
To my mother for her endless support of all my crazy ideas
Contents at a Glance
About the Authors .................................................................................................... xvi
About the Technical Reviewer ................................................................................ xvii
Acknowledgments ................................................................................................. xviii
Introduction ............................................................................................................. xix

■ Chapter 1: Introducing SQLite.................................................................................. 1
■ Chapter 2: Getting Started ..................................................................................... 17
■ Chapter 3: SQL for SQLite ...................................................................................... 47
■ Chapter 4: Advanced SQL for SQLite ...................................................................... 87
■ Chapter 5: SQLite Design and Concepts ............................................................... 125
■ Chapter 6: The Core C API .................................................................................... 153
■ Chapter 7: The Extension C API ........................................................................... 195
■ Chapter 8: Language Extensions ......................................................................... 219
■ Chapter 9: iOS Development with SQLite ............................................................. 253
■ Chapter 10: Android Development with SQLite .................................................... 279
■ Chapter 11: SQLite Internals and New Features .................................................. 303

Index ....................................................................................................................... 323
iv
Contents
About the Authors .................................................................................................... xvi
About the Technical Reviewer ................................................................................ xvii
Acknowledgments ................................................................................................. xviii
Introduction ............................................................................................................. xix

■ Chapter 1: Introducing SQLite.................................................................................. 1
An Embedded Database ..................................................................................................... 1
A Developer’s Database ..................................................................................................... 2
An Administrator’s Database ............................................................................................. 3
SQLite History .................................................................................................................... 3
Who Uses SQLite ................................................................................................................ 4
Architecture ....................................................................................................................... 5
The Interface ............................................................................................................................................ 6
The Compiler ............................................................................................................................................ 6
The Virtual Machine .................................................................................................................................. 6
The Back End ............................................................................................................................................ 7
Utilities and Test Code .............................................................................................................................. 8
SQLite’s Features and Philosophy ..................................................................................... 8
Zero Configuration .................................................................................................................................... 8
Portability ................................................................................................................................................. 8
Compactness ............................................................................................................................................ 9
v
■ CONTENTS
Simplicity .................................................................................................................................................. 9
Flexibility .................................................................................................................................................. 9
Liberal Licensing ...................................................................................................................................... 9
Reliability ................................................................................................................................................ 10
Convenience ........................................................................................................................................... 10
Performance and Limitations ........................................................................................... 11
Who Should Read This Book ............................................................................................ 13
How This Book Is Organized ............................................................................................ 14
Additional Information ..................................................................................................... 15
Summary ......................................................................................................................... 15
■ Chapter 2: Getting Started ..................................................................................... 17
Where to Get SQLite ......................................................................................................... 17
SQLite on Windows .......................................................................................................... 18
Getting the Command-Line Program ...................................................................................................... 18
Getting the SQLite DLL ............................................................................................................................ 21
Compiling the SQLite Source Code on Windows .................................................................................... 22
Building the SQLite DLL with Microsoft Visual C++ ............................................................................... 25
Building a Dynamically Linked SQLite Client with Visual C++ ............................................................... 27
Building SQLite with MinGW ................................................................................................................... 28
SQLite on Linux, Mac OS X, and Other POSIX Systems .................................................... 30
Binaries and Packages ........................................................................................................................... 30
Compiling SQLite from Source ................................................................................................................ 31
The Command-Line Program ........................................................................................... 32
The CLP in Shell Mode ............................................................................................................................ 33
The CLP in Command-Line Mode ........................................................................................................... 34
vi
■ CONTENTS
Database Administration ................................................................................................. 35
Creating a Database ............................................................................................................................... 35
Getting Database Schema Information ................................................................................................... 37
Exporting Data ........................................................................................................................................ 39
Importing Data ........................................................................................................................................ 40
Formatting .............................................................................................................................................. 40
Exporting Delimited Data ........................................................................................................................ 41
Performing Unattended Maintenance ..................................................................................................... 41
Backing Up a Database .......................................................................................................................... 42
Getting Database File Information .......................................................................................................... 44
Other SQLite Tools ........................................................................................................... 45
Summary ......................................................................................................................... 46
■ Chapter 3: SQL for SQLite ...................................................................................... 47
The Example Database .................................................................................................... 47
Installation .............................................................................................................................................. 48
Running the Examples ............................................................................................................................ 49
Syntax .............................................................................................................................. 50
Commands .............................................................................................................................................. 51
Literals .................................................................................................................................................... 52
Keywords and Identifiers ........................................................................................................................ 53
Comments .............................................................................................................................................. 53
Creating a Database ........................................................................................................ 53
Creating Tables ....................................................................................................................................... 53
Altering Tables ........................................................................................................................................ 54
Querying the Database .................................................................................................... 55
Relational Operations ............................................................................................................................. 55
select and the Operational Pipeline ........................................................................................................ 57
Filtering .................................................................................................................................................. 59
vii
■ CONTENTS
Limiting and Ordering ............................................................................................................................. 64
Functions and Aggregates ...................................................................................................................... 66
Grouping ................................................................................................................................................. 67
Removing Duplicates .............................................................................................................................. 72
Joining Tables ........................................................................................................................................ 72
Names and Aliases ................................................................................................................................. 77
Subqueries ............................................................................................................................................. 79
Compound Queries ................................................................................................................................. 81
Conditional Results ................................................................................................................................. 83
Handling Null in SQLite ........................................................................................................................... 84
Summary ................................................................................................................................................ 86
■ Chapter 4: Advanced SQL for SQLite ...................................................................... 87
Modifying Data ................................................................................................................. 87
Inserting Records ................................................................................................................................... 87
Updating Records ................................................................................................................................... 91
Deleting Records .................................................................................................................................... 92
Data Integrity ................................................................................................................... 92
Entity Integrity ........................................................................................................................................ 93
Domain Integrity ..................................................................................................................................... 97
Storage Classes .................................................................................................................................... 101
Views .................................................................................................................................................... 104
Indexes ................................................................................................................................................. 106
Triggers ................................................................................................................................................ 108
Transactions .................................................................................................................. 111
Transaction Scopes .............................................................................................................................. 111
Conflict Resolution ................................................................................................................................ 112
Database Locks .................................................................................................................................... 115
Deadlocks ............................................................................................................................................. 116
Transaction Types ................................................................................................................................ 117
viii