Using SQLite (Jay A. Kreibich) (Z-Library)

Using SQLite

Using SQLite Jay A. Kreibich Beijing • Cambridge • Farnham • Köln • Sebastopol • Taipei • Tokyo

Using SQLite by Jay A. Kreibich Copyright © 2010 Jay A. Kreibich. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://my.safaribooksonline.com). For more information, contact our corporate/institutional sales department: (800) 998-9938 or corporate@oreilly.com. Editor: Mike Loukides Production Editor: Kristen Borg Proofreader: Kiel Van Horn Indexer: Lucie Haskins Cover Designer: Karen Montgomery Interior Designer: David Futato Illustrator: Robert Romano Printing History: August 2010: First Edition. Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly Media, Inc. Using SQLite, the image of a great white heron, and related trade dress are trade- marks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information con- tained herein. ISBN: 978-0-596-52118-9 [M] 1281104401

To my Great-Uncle Albert “Unken Al” Kreibich. 1918–1994 He took a young boy whose favorite question was “why?” and taught him to ask the question “how?” (Who also—much to the dismay of his parents and the kitchen telephone—taught him the joy of answering that question, especially if it involved pliers or screwdrivers.) —jk

Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv 1. What Is SQLite? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Self-Contained, No Server Required 2 Single File Database 4 Zero Configuration 4 Embedded Device Support 5 Unique Features 5 Compatible License 6 Highly Reliable 6 2. Uses of SQLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Database Junior 9 Application Files 10 Application Cache 11 Archives and Data Stores 11 Client/Server Stand-in 11 Teaching Tool 12 Generic SQL Engine 13 Not the Best Choice 13 Big Name Users 15 3. Building and Installing SQLite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 SQLite Products 17 Precompiled Distributions 18 Documentation Distribution 18 Source Distributions 19 The Amalgamation 19 Source Files 19 Source Downloads 20 Building 21 vii

Configure 21 Manually 22 Build Customization 23 Build and Installation Options 23 An sqlite3 Primer 24 Summary 26 4. The SQL Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Learning SQL 27 Brief Background 28 Declarative 28 Portability 29 General Syntax 30 Basic Syntax 30 Three-Valued Logic 31 Simple Operators 33 SQL Data Languages 34 Data Definition Language 34 Tables 35 Views 43 Indexes 44 Data Manipulation Language 45 Row Modification Commands 46 The Query Command 49 Transaction Control Language 51 ACID Transactions 51 SQL Transactions 53 Save-Points 55 System Catalogs 57 Wrap-up 58 5. The SELECT Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 SQL Tables 61 The SELECT Pipeline 62 FROM Clause 63 WHERE Clause 68 GROUP BY Clause 69 SELECT Header 70 HAVING Clause 73 DISTINCT Keyword 74 ORDER BY Clause 74 LIMIT and OFFSET Clauses 75 Advanced Techniques 76 viii | Table of Contents

Subqueries 76 Compound SELECT Statements 77 Alternate JOIN Notation 78 SELECT Examples 79 Simple SELECTs 80 Simple JOINs 80 JOIN...ON 81 JOIN...USING, NATURAL JOIN 82 OUTER JOIN 82 Compound JOIN 82 Self JOIN 83 WHERE Examples 83 GROUP BY Examples 84 ORDER BY Examples 85 What’s Next 85 6. Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Tables and Keys 87 Keys Define the Table 87 Foreign Keys 89 Foreign Key Constraints 90 Generic ID Keys 91 Keep It Specific 92 Common Structures and Relationships 93 One-to-One Relationships 93 One-to-Many Relationships 95 Many-to-Many Relationships 97 Hierarchies and Trees 99 Normal Form 102 Normalization 103 Denormalization 103 The First Normal Form 104 The Second Normal Form 104 The Third Normal Form 105 Higher Normal Forms 106 Indexes 107 How They Work 107 Must Be Diverse 108 INTEGER PRIMARY KEYs 109 Order Matters 109 One at a Time 110 Index Summary 111 Transferring Design Experience 112 Table of Contents | ix

Tables Are Types 112 Keys Are Backwards Pointers 113 Do One Thing 113 Closing 114 7. C Programming Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 API Overview 115 Structure 116 Strings and Unicode 117 Error Codes 118 Structures and Allocations 118 More Info 119 Library Initialization 119 Database Connections 120 Opening 120 Special Cases 121 Closing 122 Example 122 Prepared Statements 123 Statement Life Cycle 123 Prepare 124 Step 126 Result Columns 127 Reset and Finalize 130 Statement Transitions 131 Examples 132 Bound Parameters 133 Parameter Tokens 133 Binding Values 135 Security and Performance 138 Example 140 Potential Pitfalls 141 Convenience Functions 142 Result Codes and Error Codes 146 Standard Codes 146 Extended Codes 148 Error Functions 148 Prepare v2 149 Transactions and Errors 150 Database Locking 151 Utility Functions 156 Version Management 156 Memory Management 157 x | Table of Contents

Summary 158 8. Additional Features and APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Date and Time Features 159 Application Requirements 160 Representations 160 Time and Date Functions 162 ICU Internationalization Extension 167 Full-Text Search Module 169 Creating and Populating FTS Tables 169 Searching FTS Tables 170 More Details 171 R*Trees and Spatial Indexing Module 171 Scripting Languages and Other Interfaces 172 Perl 172 PHP 173 Python 173 Java 174 Tcl 174 ODBC 175 .NET 175 C++ 175 Other Languages 176 Mobile and Embedded Development 176 Memory 176 Storage 177 Other Resources 178 iPhone Support 178 Other Environments 179 Additional Extensions 180 9. SQL Functions and Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Scalar Functions 182 Registering Functions 182 Extracting Parameters 184 Returning Results and Errors 186 Example 189 Aggregate Functions 194 Defining Aggregates 194 Aggregate Context 195 Example 197 Collation Functions 200 Registering a Collation 201 Table of Contents | xi

Collation Example 202 SQLite Extensions 204 Extension Architecture 205 Extension Design 206 Example Extension: sql_trig 207 Building and Integrating Static Extensions 209 Using Loadable Extensions 211 Building Loadable Extensions 212 Loadable Extension Security 213 Loading Loadable Extensions 213 Multiple Entry Points 215 Chapter Summary 215 10. Virtual Tables and Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Introduction to Modules 218 Internal Modules 218 External Modules 218 Example Modules 219 SQL for Anything 219 Module API 220 Simple Example: dblist Module 224 Create and Connect 224 Disconnect and Destroy 229 Query Optimization 230 Custom Functions 231 Table Rename 232 Opening and Closing Table Cursors 233 Filtering Rows 235 Extracting and Returning Data 237 Virtual Table Modifications 239 Cursor Sequence 240 Transaction Control 241 Register the Module 243 Example Usage 245 Advanced Example: weblog Module 246 Create and Connect 248 Disconnect and Destroy 249 Other Table Functions 250 Open and Close 250 Filter 252 Rows and Columns 254 Register the Module 259 Example Usage 259 xii | Table of Contents

Best Index and Filter 262 Purpose and Need 262 xBestIndex() 263 xFilter() 266 Typical Usage 267 Wrap-Up 268 A. SQLite Build Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 B. sqlite3 Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 C. SQLite SQL Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 D. SQLite SQL Expression Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341 E. SQLite SQL Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 F. SQLite SQL PRAGMA Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 G. SQLite C API Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 Table of Contents | xiii

Preface This book provides an introduction to the SQLite database product. SQLite is a zero- configuration, standalone, relational database engine that is designed to be embedded directly into an application. Database instances are self-contained within a single file, allowing easy transport and simple setup. Using SQLite is primarily written for experienced software developers that have never had a particular need to learn about relational databases. For one reason or another, you now find yourself with a large data management task, and are hoping a product like SQLite may provide the answer. To help you out, the various chapters cover the SQL language, the SQLite C programming API, and the basics of relational database design, giving you everything you need to successfully integrate SQLite into your ap- plications and development work. The book is divided into two major sections. The first part is a traditional set of chapters that are primarily designed to be read in order. The first two chapters provide an in- depth look at exactly what SQLite provides and how it can be used. The third chapter covers downloading and building the library. Chapters Four and Five provide an in- troduction to the SQL language, while Chapter Six covers database design concepts. Chapter Seven covers the basics of the C API. Chapter Eight builds on that to cover more advanced topics, such as storing times and dates, using SQLite from scripting languages, and utilizing some of the more advanced extensions. Chapters Nine and Ten cover writing your own custom SQL functions, extensions, and modules. To complete the picture, the ten chapters are followed by several reference appendixes. These references cover all of the SQL commands, expressions, and built-in functions supported by SQLite, as well as documentation for the complete SQLite API. SQLite Versions The first edition of this book coves SQLite version 3.6.23.1. As this goes to press, work on SQLite version 3.7 is being finalized. SQLite 3.7 introduces a new transaction journal mode known as Write Ahead Logging, or WAL. In some environments, WAL can pro- vide better concurrent transaction performance than the current rollback journal. This xv

performance comes at a cost, however. WAL has more restrictive operational require- ments and requires more advanced support from the operating system. Once WAL has been fully tested and released, look for an article on the O’Reilly website that covers this new feature and how to get the most out of it. Email Lists The SQLite project maintains three mailing lists. If you’re trying to learn more about SQLite, or have any questions that are not addressed in this book or in the project documentation, these are often a good place to start. sqlite-announce@sqlite.org This list is limited to announcements of new releases, critical bug alerts, and other significant events in the SQLite community. Traffic is extremely low, and most messages are posted by the SQLite development team. sqlite-users@sqlite.org This is the main support list for SQLite. It covers a broad range of topics, including SQL questions, programming questions, and questions about how the library works. This list is moderately busy. sqlite-dev@sqlite.org This list is for people working on the internal code of the SQLite library itself. If you have questions about how to use the published SQLite API, those questions belong on the sqlite-users list. Traffic on this list is fairly low. You can find instructions on how to join these mailing lists on the SQLite website. Visit http://www.sqlite.org/support.html for more details. The sqlite-users@sqlite.org email list can be quite helpful, but it is a moderately busy list. If you’re only a casual user and don’t wish to receive that much email, you can also access and search list messages through a web archive. Links to several different archives are available on the SQLite support page. Example Code Download The code examples found in this book are available for download from the O’Reilly website. You can find a link to the examples on the book’s catalog page at http://oreilly .com/catalog/9780596521196/. The files include both the SQL examples and the C examples found in later chapters. xvi | Preface

How We Got Here Taking a book from an idea to a finished product involves a great many people. Al- though my name is on the cover, this could not have been possible without all of their help. First, I would like to acknowledge the friendship and support of my primary editor, Mike Loukides. Thanks to some mutual friends, I first started doing technical reviews for Mike over eight years ago. Through the years, Mike gently encouraged me to take on my own project. The first step on that path came nearly three years ago. I had downloaded a set of database exports from the Wikipedia project and was trying to devise a minimal data- base configuration that would (hopefully) cram nearly all the current data onto a small flash storage card. The end goal was to provide a local copy of the Wikipedia articles on an ebook reader I had. SQLite was a natural choice. At some point, frustrated with trying to understand the correct call sequence, I threw my hands up and exclaimed, “Someone should write a book about this!”—Ding!—The proverbial light bulb went off, and many, many (many…) late nights later, here we are. Behind Mike stands the whole staff of O’Reilly Media. Everyone I interacted with did their best to help me out, calm me down, and fix my problems—sometimes all at once. The production staff understands how to make life easy for the author, so that we can focus on writing and leave the details to someone else. I would like to thank D. Richard Hipp, the creator and lead maintainer of SQLite. In addition to coordinating the continued development of SQLite and providing us all with a high-quality software product, he was also gracious enough to answer numerous questions, as well as review a final draft of the manuscript. Some tricky spots went through several revisions, and he was always quick to review things and get back to me with additional comments. A technical review was also done by Jon W. Marks. Jon is an old personal and profes- sional friend with enterprise-class database experience. He has had the opportunity to mentor several experienced developers as they made their first journey into the rela- tional database world. Jon provided very insightful feedback, and was able to pinpoint areas that are often difficult for beginners to grasp. My final two technical reviewers were Jordan Hawker and Erin Moy. Although they are knowledgeable developers, they were relatively new to relational databases. As they went through the learning process, they kept me honest when I started to make too many assumptions, and kept me on track when I started to skip ahead too quickly. Preface | xvii

I also owe a thank-you to Mike Kulas and all my coworkers at Volition, Inc. In addition to helping me find the right balance between my professional work and the book work, Mike helped me navigate our company’s intellectual property policies, making sure everything was on the straight and narrow. Numerous coworkers also deserve a thank- you for reviewing small sections, looking at code, asking lots of good questions, and otherwise putting up with me venting about not having enough time in the day. A tip of the hat goes out to the crew at the Aroma Café in downtown Champaign, Illinois. They’re just a few blocks down from my workplace, and a significant portion of this book was written at their coffee shop. Many thanks to Michael and his staff, including Kim, Sara, Nichole, and Jerry, for always having a hot and creamy mocha ready. Finally, I owe a tremendous debt to my wife, Debbie Fligor, and our two sons. They were always willing to make time for me to write and showed enormous amounts of patience and understanding. They all gave more than I had any right to ask, and this accomplishment is as much theirs as it is mine. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values deter- mined by context. This icon signifies a tip, suggestion, or general note. This icon indicates a warning or caution. xviii | Preface