Allen / Owens The Definitive Guide to SQLite

2. ed
ISBN: 978-1-4302-3226-1
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 368 Seiten

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!

Grant Allen has worked in the IT field for over 20 years, as a CTO, enterprise architect, and database administrator. Grant's roles have covered private enterprise, academia and the government sector around the world, specialising in global-scale systems design, development, and performance. He is a frequent speaker at industry and academic conferences, on topics ranging from data mining to compliance, and technologies such as databases (DB2, Oracle, SQL Server, MySQL), content management, collaboration, disruptive innovation, and mobile ecosystems like Android. His first Android application was a task list to remind him to finish all his other unfinished Android projects. Grant works for Google, and in his spare time is completing a Ph.D on building innovative high-technology environments. Grant is the author of Beginning DB2, and lead author of Oracle SQL Recipes and The Definitive Guide to SQLite.

Autoren/Hrsg.

Allen, Grant

Owens, Mike

Inhaltsverzeichnis

1;Title Page;1
2;Copyright Page;2
3;Contents at a Glance;4
4;Table of Contents;5
5;About the Authors;16
6;About the Technical Reviewer;17
7;Acknowledgments;18
8;Introduction;19
8.1;Prerequisites;19
8.2;How This Book Is Organized;19
8.3;Obtaining the Source Code of the Examples;19
9;Chapter 1 Introducing SQLite;20
9.1;An Embedded Database;20
9.2;A Developer’s Database;21
9.3;An Administrator’s Database;22
9.4;SQLite History;22
9.5;Who Uses SQLite;23
9.6;Architecture;24
9.6.1;The Interface;25
9.6.2;The Compiler;25
9.6.3;The Virtual Machine;25
9.6.4;The Back End;26
9.6.5;Utilities and Test Code;27
9.7;SQLite’s Features and Philosophy;27
9.7.1;Zero Configuration;27
9.7.2;Portability;27
9.7.3;Compactness;28
9.7.4;Simplicity;28
9.7.5;Flexibility;28
9.7.6;Liberal Licensing;28
9.7.7;Reliability;29
9.7.8;Convenience;29
9.8;Performance and Limitations;30
9.9;Who Should Read This Book;32
9.10;How This Book Is Organized;33
9.11;Additional Information;34
9.12;Summary;34
10;Chapter 2 Getting Started;36
10.1;Where to Get SQLite;36
10.2;SQLite on Windows;37
10.2.1;Getting the Command-Line Program;37
10.2.2;Getting the SQLite DLL;40
10.2.3;Compiling the SQLite Source Code on Windows;41
10.2.3.1;The Stable Source Distribution;41
10.2.3.2;Anonymous Fossil Source Control;42
10.2.4;Building the SQLite DLL with Microsoft Visual C++;44
10.2.5;Building a Dynamically Linked SQLite Client with Visual C++;46
10.2.6;Building SQLite with MinGW;47
10.3;SQLite on Linux, Mac OS X, and Other POSIX Systems;49
10.3.1;Binaries and Packages;49
10.3.2;Compiling SQLite from Source;50
10.4;The Command-Line Program;51
10.4.1;The CLP in Shell Mode;52
10.4.2;The CLP in Command-Line Mode;53
10.5;Database Administration;54
10.5.1;Creating a Database;54
10.5.2;Getting Database Schema Information;56
10.5.3;Exporting Data;58
10.5.4;Importing Data;59
10.5.5;Formatting;59
10.5.6;Exporting Delimited Data;60
10.5.7;Performing Unattended Maintenance;60
10.5.8;Backing Up a Database;61
10.5.9;Getting Database File Information;63
10.6;Other SQLite Tools;64
10.7;Summary;65
11;Chapter 3 SQL for SQLite;66
11.1;The Example Database;66
11.1.1;Installation;67
11.1.2;Running the Examples;68
11.2;Syntax;69
11.2.1;Commands;70
11.2.2;Literals;71
11.2.3;Keywords and Identifiers;72
11.2.4;Comments;72
11.3;Creating a Database;72
11.3.1;Creating Tables;72
11.3.2;Altering Tables;73
11.4;Querying the Database;74
11.4.1;Relational Operations;74
11.4.2;Select and the Operational Pipeline;76
11.4.3;Filtering;78
11.4.3.1;Values;79
11.4.3.2;Operators;79
11.4.3.3;Binary Operators;79
11.4.3.4;Logical Operators;82
11.4.3.5;The LIKE and GLOB Operators;82
11.4.4;Limiting and Ordering;83
11.4.5;Functions and Aggregates;85
11.4.6;Grouping;86
11.4.7;Removing Duplicates;91
11.4.8;Joining Tables;91
11.4.8.1;Inner Joins;93
11.4.8.2;Cross Joins;94
11.4.8.3;Outer Joins;95
11.4.8.4;Natural Joins;96
11.4.8.5;Preferred Syntax;96
11.4.9;Names and Aliases;96
11.4.10;Subqueries;98
11.4.11;Compound Queries;100
11.4.12;Conditional Results;102
11.4.13;Handling Null in SQLite;103
11.4.14;Summary;105
12;Chapter 4 Advanced SQL for SQLite;106
12.1;Modifying Data;106
12.1.1;Inserting Records;106
12.1.1.1;Inserting One Row;106
12.1.1.2;Inserting a Set of Rows;108
12.1.1.3;Inserting Multiple Rows;109
12.1.2;Updating Records;110
12.1.3;Deleting Records;111
12.2;Data Integrity;111
12.2.1;Entity Integrity;112
12.2.1.1;Unique Constraints;112
12.2.1.2;Primary Key Constraints;113
12.2.2;Domain Integrity;116
12.2.2.1;Default Values;117
12.2.2.2;NOT NULL Constraints;117
12.2.2.3;Check Constraints;118
12.2.2.4;Foreign Key Constraints;119
12.2.2.5;Collations;120
12.2.3;Storage Classes;120
12.2.4;Views;123
12.2.5;Indexes;125
12.2.5.1;Collations;126
12.2.5.2;Index Utilization;126
12.2.6;Triggers;127
12.2.6.1;Update Triggers;128
12.2.6.2;Error Handling;129
12.2.6.3;Updatable Views;129
12.3;Transactions;130
12.3.1;Transaction Scopes;130
12.3.2;Conflict Resolution;131
12.3.3;Database Locks;134
12.3.4;Deadlocks;135
12.3.5;Transaction Types;136
12.4;Database Administration;137
12.4.1;Attaching Databases;137
12.4.2;Cleaning Databases;138
12.4.3;Database Configuration;139
12.4.3.1;The Connection Cache Size;139
12.4.3.2;Getting Database Information;139
12.4.3.3;Synchronous Writes;140
12.4.3.4;Temporary Storage;141
12.4.3.5;Page Size, Encoding, and Autovacuum;141
12.4.3.6;Debugging;141
12.4.4;The System Catalog;142
12.4.5;Viewing Query Plans;142
12.5;Summary;143
13;Chapter 5 SQLite Design and Concepts;144
13.1;The API;144
13.1.1;The Principal Data Structures;145
13.1.1.1;Connections and Statements;145
13.1.1.2;The B-tree and Pager;146
13.1.2;The Core API;146
13.1.2.1;Connecting to a Database;147
13.1.2.2;Executing Prepared Queries;148
13.1.2.3;Using Parameterized SQL;150
13.1.2.4;Executing Wrapped Queries;151
13.1.2.5;Handling Errors;152
13.1.2.6;Formatting SQL Statements;153
13.1.3;Operational Control;154
13.1.4;Using Threads;155
13.2;The Extension API;155
13.2.1;Creating User-Defined Functions;155
13.2.2;Creating User-Defined Aggregates;156
13.2.3;Creating User-Defined Collations;157
13.3;Transactions;157
13.3.1;Transaction Life Cycles;157
13.3.2;Lock States;158
13.3.3;Read Transactions;160
13.3.4;Write Transactions;160
13.3.4.1;The Reserved State;160
13.3.4.2;The Pending State;161
13.3.4.3;The Exclusive State;161
13.3.4.4;Autocommit and Efficiency;162
13.4;Tuning the Page Cache;164
13.4.1;Transitioning to Exclusive;164
13.4.2;Sizing the Page Cache;164
13.5;Waiting for Locks;165
13.5.1;Using a Busy Handler;165
13.5.2;Using the Right Transaction;166
13.6;Code;168
13.6.1;Using Multiple Connections;168
13.6.2;The Importance of Finalizing;169
13.6.3;Shared Cache Mode;170
13.7;Summary;170
14;Chapter 6 The Core C API;172
14.1;Wrapped Queries;172
14.1.1;Connecting and Disconnecting;172
14.1.2;The exec Query;174
14.1.3;The Get Table Query;178
14.2;Prepared Queries;180
14.2.1;Compilation;180
14.2.2;Execution;181
14.2.3;Finalization and Reset;182
14.3;Fetching Records;183
14.3.1;Getting Column Information;184
14.3.2;Getting Column Values;185
14.3.3;A Practical Example;187
14.4;Parameterized Queries;188
14.4.1;Numbered Parameters;191
14.4.2;Named Parameters;192
14.4.3;Tcl Parameters;192
14.5;Errors and the Unexpected;193
14.5.1;Handling Errors;193
14.5.2;Handling Busy Conditions;195
14.5.2.1;User-Defined Busy Handlers;196
14.5.2.2;Advice;196
14.5.3;Handling Schema Changes;196
14.6;Operational Control;197
14.6.1;Commit Hooks;197
14.6.2;Rollback Hooks;198
14.6.3;Update Hooks;198
14.6.4;Authorizer Functions;199
14.7;Threads;209
14.7.1;Shared Cache Mode;209
14.7.1.1;Read Uncommitted Isolation Level;210
14.7.1.2;Unlock Notification;211
14.7.2;Threads and Memory Management;212
14.8;Summary;212
15;Chapter 7 The Extension C API;213
15.1;The API;214
15.1.1;Registering Functions;214
15.1.2;The Step Function;216
15.1.3;Return Values;216
15.2;Functions;218
15.2.1;Return Values;220
15.2.2;Arrays and Cleanup Handlers;220
15.2.3;Error Conditions;221
15.2.4;Returning Input Values;221
15.3;Aggregates;222
15.3.1;Registration Function;223
15.3.2;A Practical Example;224
15.3.2.1;The Step Function;225
15.3.2.2;The Aggregate Context;225
15.3.2.3;The Finalize Function;226
15.3.2.4;Results;226
15.4;Collations;227
15.4.1;Collation Defined;228
15.4.1.1;How Collation Works;228
15.4.1.2;Standard Collation Types;230
15.4.2;A Simple Example;230
15.4.2.1;The Compare Function;231
15.4.2.2;The Test Program;232
15.4.2.3;Results;233
15.4.3;Collation on Demand;234
15.5;Summary;235
16;Chapter 8 Language Extensions;236
16.1;Selecting an Extension;237
16.2;Perl;238
16.2.1;Installation;238
16.2.2;Connecting;239
16.2.3;Query Processing;239
16.2.4;Parameter Binding;241
16.2.5;User-Defined Functions;241
16.2.6;Aggregates;242
16.3;Python;243
16.3.1;Installation;243
16.3.2;Connecting;244
16.3.3;Query Processing;244
16.3.4;Parameter Binding;246
16.3.5;User-Defined Functions;247
16.3.6;Aggregates;248
16.3.7;APSW as an Alternative Python Interface;249
16.4;Ruby;249
16.4.1;Installation;249
16.4.2;Connecting;250
16.4.3;Query Processing;250
16.4.4;Parameter Binding;251
16.4.5;User-Defined Functions;253
16.5;Java;253
16.5.1;Installation;254
16.5.2;Connecting;255
16.5.3;Query Processing;255
16.5.4;User-Defined Functions and Aggregates;257
16.5.5;JDBC;258
16.6;Tcl;260
16.6.1;Installation;260
16.6.2;Connecting;261
16.6.3;Query Processing;261
16.6.4;User-Defined Functions;264
16.7;PHP;264
16.7.1;Installation;265
16.7.2;Connections;265
16.7.3;Queries;265
16.7.4;User-Defined Functions and Aggregates;268
16.8;Summary;269
17;Chapter 9 iOS Development with SQLite;270
17.1;Prerequisites for SQLite iOS Development;270
17.1.1;Signing Up for Apple Developer;271
17.1.2;Downloading and Installing Xcode and the iOS SDK;271
17.1.3;Alternatives to Xcode;275
17.2;Building the iSeinfeld iOS SQLite Application;276
17.2.1;Step 1: Creating a New Xcode Project;276
17.2.2;Step 2: Adding the SQLite Framework to Your Project;278
17.2.3;Step 3: Preparing the Foods Database;280
17.2.4;Step 4: Creating Classes for the Food Data;281
17.2.4.1;The Food Class;282
17.2.4.2;The FoodViewController Class;283
17.2.5;Step 5: Accessing and Querying the SQLite DB;286
17.2.6;Step 6: Final Polish and Wiring for iSeinfeld;289
17.3;iSeinfeld in Action!;289
17.4;Working with Large SQLite Databases Under iOS;293
17.5;Summary;294
18;Chapter 10 Android Development with SQLite;295
18.1;Prerequisites for SQLite Android Development;295
18.1.1;Check Prerequisites and the JDK;296
18.1.2;Downloading and Installing the Android SDK Starter Package;296
18.1.3;Downloading and Installing the Android Developer Tools;296
18.1.4;Adding Android Platforms and Components;297
18.2;The Android SQLite Classes and Interfaces;301
18.2.1;Using the Basic Helper Class, SQLiteOpenHelper;301
18.2.2;Working with the SQLiteDatabase Class;302
18.2.2.1;Opening and Closing the SQLiteDatabase;302
18.2.2.2;Executing General Queries with SQLiteDatabase;303
18.2.2.3;Using Convenience Methods with SQLiteDatabase;304
18.2.2.4;Managing Transactions with SQLiteDatabase;305
18.2.2.5;Using Other SQLiteDatabase Methods;306
18.2.3;Applying SQLiteOpenHelper and SQLiteDatabase in Practice;306
18.2.4;Querying SQLite with SQLiteQueryBuilder;309
18.3;Building the Seinfeld Android SQLite Application;310
18.3.1;Creating a New Android Project;311
18.3.2;Adding the Seinfeld SQLite Database to Your Project;312
18.3.3;Querying the Foods Table;312
18.3.4;Defining the User Interface;313
18.3.5;Linking the Data and User Interface;314
18.3.6;Viewing the Finished Seinfeld Application;315
18.4;Care and Feeding for SQLite Android Applications;316
18.4.1;Database Backup for Android;316
18.4.2;Working with Large SQLite Databases Under Android;316
18.5;Summary;317
19;Chapter 11 SQLite Internals and New Features;318
19.1;The B-Tree and Pager Modules;318
19.1.1;Database File Format;318
19.1.1.1;Page Reuse and Vacuum;319
19.1.1.2;B-Tree Records;319
19.1.1.3;B+Trees;319
19.1.1.4;Records and Fields;320
19.1.1.5;Hierarchical Data Organization;322
19.1.1.6;Overflow Pages;322
19.1.2;The B-Tree API;323
19.1.2.1;Access and Transaction Functions;323
19.1.2.2;Table Functions;324
19.1.2.3;Cursor Functions;324
19.1.2.4;Record Functions;325
19.1.2.5;Configuration Functions;325
19.2;Manifest Typing, Storage Classes, and Affinity;326
19.2.1;Manifest Typing;326
19.2.2;Type Affinity;328
19.2.2.1;Column Types and Affinities;328
19.2.3;Affinities and Storage;329
19.2.3.1;Affinities in Action;329
19.2.3.2;Storage Classes and Type Conversions;330
19.3;Write Ahead Logging;333
19.3.1;How WAL Works;333
19.3.1.1;Checkpoints;333
19.3.1.2;Concurrency;334
19.3.2;Activation and Configuration WAL;334
19.3.3;WAL Advantages and Disadvantages;335
19.3.4;Operational Issues with WAL-Enabled SQLite Databases;336
19.3.4.1;Performance;336
19.3.4.2;Recovery;336
19.4;Summary;337
20;Index;338