The Definitive Guide to SQLite PDF
Preview The Definitive Guide to SQLite
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 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 (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. For information on translations, please e-mail [email protected], 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
Description:The list of books you might like
