Using SQLite: Small. Fast. Reliable. Choose Any Three.

by Jay Kreibich

Overview

Application developers, take note: databases aren't just for the IS group any more. You can build database-backed applications for the desktop, Web, embedded systems, or operating systems without linking to heavy-duty client-server databases such as Oracle and MySQL. This book shows you how to use SQLite, a small and lightweight relational database engine that you can build directly into your application.

With SQLite, you'll discover how to develop a database-backed application that remains manageable in size and complexity. This book guides you every step of the way. You'll get a crash course in data modeling, become familiar with SQLite's dialect of the SQL database language, and much more.

• Learn how to maintain localized storage in a single file that requires no configuration
• Build your own SQLite library or use a precompiled distribution in your application
• Get a primer on SQL, and learn how to use several language functions and extensions
• Work with SQLite using a scripting language or a C-based language such as C# or Objective-C
• Understand the basics of database design, and learn how to transfer what you already know to SQLite
• Take advantage of virtual tables and modules

"Complex SQL concepts explained clearly."

—D. Richard Hipp, creator of SQLite

Product Details

ISBN-13:	9780596521189
Publisher:	O'Reilly Media, Incorporated
Publication date:	08/26/2010
Pages:	503
Product dimensions:	7.00(w) x 9.00(h) x 1.10(d)

About the Author

Jay Kreibich is a professional software engineer who has always been interested in how people process and understand information. He is current working for Volition, Inc., a software studio that specializes in open-world video games. He lives on a small farm in central Illinois with his wife and two sons, where he enjoys reading, photography, and tinkering.

Table of Contents

Preface; SQLite Versions; Email Lists; Example Code Download; How We Got Here; Conventions Used in This Book; Using Code Examples; Safari® Books Online; How to Contact Us; Chapter 1: What Is SQLite?; 1.1 Self-Contained, No Server Required; 1.2 Single File Database; 1.3 Zero Configuration; 1.4 Embedded Device Support; 1.5 Unique Features; 1.6 Compatible License; 1.7 Highly Reliable; Chapter 2: Uses of SQLite; 2.1 Database Junior; 2.2 Application Files; 2.3 Application Cache; 2.4 Archives and Data Stores; 2.5 Client/Server Stand-in; 2.6 Teaching Tool; 2.7 Generic SQL Engine; 2.8 Not the Best Choice; 2.9 Big Name Users; Chapter 3: Building and Installing SQLite; 3.1 SQLite Products; 3.2 Precompiled Distributions; 3.3 Documentation Distribution; 3.4 Source Distributions; 3.5 Building; 3.6 Build and Installation Options; 3.7 An sqlite3 Primer; 3.8 Summary; Chapter 4: The SQL Language; 4.1 Learning SQL; 4.2 Brief Background; 4.3 General Syntax; 4.4 SQL Data Languages; 4.5 Data Definition Language; 4.6 Data Manipulation Language; 4.7 Transaction Control Language; 4.8 System Catalogs; 4.9 Wrap-up; Chapter 5: The SELECT Command; 5.1 SQL Tables; 5.2 The SELECT Pipeline; 5.3 Advanced Techniques; 5.4 SELECT Examples; 5.5 What’s Next; Chapter 6: Database Design; 6.1 Tables and Keys; 6.2 Common Structures and Relationships; 6.3 Normal Form; 6.4 Indexes; 6.5 Transferring Design Experience; 6.6 Closing; Chapter 7: C Programming Interface; 7.1 API Overview; 7.2 Library Initialization; 7.3 Database Connections; 7.4 Prepared Statements; 7.5 Bound Parameters; 7.6 Convenience Functions; 7.7 Result Codes and Error Codes; 7.8 Utility Functions; 7.9 Summary; Chapter 8: Additional Features and APIs; 8.1 Date and Time Features; 8.2 ICU Internationalization Extension; 8.3 Full-Text Search Module; 8.4 R*Trees and Spatial Indexing Module; 8.5 Scripting Languages and Other Interfaces; 8.6 Mobile and Embedded Development; 8.7 Additional Extensions; Chapter 9: SQL Functions and Extensions; 9.1 Scalar Functions; 9.2 Aggregate Functions; 9.3 Collation Functions; 9.4 SQLite Extensions; Chapter 10: Virtual Tables and Modules; 10.1 Introduction to Modules; 10.2 Module API; 10.3 Simple Example: dblist Module; 10.4 Advanced Example: weblog Module; 10.5 Best Index and Filter; 10.6 Wrap-Up; SQLite Build Options; Shell Directives; Default Values; Sizes and Limits; Operation and Behavior; Debug Settings; Enable Extensions; Limit Features; Omit Core Features; sqlite3 Command Reference; Command-Line Options; Interactive Dot-Commands; SQLite SQL Command Reference; SQLite SQL Commands; SQLite SQL Expression Reference; Literal Expressions; Logic Representations; Unary Expressions; Binary Expressions; Function Calls; Column Names; General Expressions; SQLite SQL Function Reference; Scalar Functions; Aggregate Functions; SQLite SQL PRAGMA Reference; SQLite PRAGMAs; SQLite C API Reference; API Datatypes; API Functions; Colophon;