The Definitive Guide to SQLite (eBook)

Grant Allen, Mike Owens (Autoren)

eBook Download: PDF

2011 | 2nd ed.
XIX, 368 Seiten
Apress (Verlag)
978-1-4302-3226-1 (ISBN)

Infos

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.

Inhalt

Title Page 1
Copyright Page 2
Contents at a Glance 4
Table of Contents 5
About the Authors 16
About the Technical Reviewer 17
Acknowledgments 18
Introduction 19
Prerequisites 19
How This Book Is Organized 19
Obtaining the Source Code of the Examples 19
Chapter 1 Introducing SQLite 20
An Embedded Database 20
A Developer’s Database 21
An Administrator’s Database 22
SQLite History 22
Who Uses SQLite 23
Architecture 24
The Interface 25
The Compiler 25
The Virtual Machine 25
The Back End 26
Utilities and Test Code 27
SQLite’s Features and Philosophy 27
Zero Configuration 27
Portability 27
Compactness 28
Simplicity 28
Flexibility 28
Liberal Licensing 28
Reliability 29
Convenience 29
Performance and Limitations 30
Who Should Read This Book 32
How This Book Is Organized 33
Additional Information 34
Summary 34
Chapter 2 Getting Started 36
Where to Get SQLite 36
SQLite on Windows 37
Getting the Command-Line Program 37
Getting the SQLite DLL 40
Compiling the SQLite Source Code on Windows 41
The Stable Source Distribution 41
Anonymous Fossil Source Control 42
Building the SQLite DLL with Microsoft Visual C++ 44
Building a Dynamically Linked SQLite Client with Visual C++ 46
Building SQLite with MinGW 47
SQLite on Linux, Mac OS X, and Other POSIX Systems 49
Binaries and Packages 49
Compiling SQLite from Source 50
The Command-Line Program 51
The CLP in Shell Mode 52
The CLP in Command-Line Mode 53
Database Administration 54
Creating a Database 54
Getting Database Schema Information 56
Exporting Data 58
Importing Data 59
Formatting 59
Exporting Delimited Data 60
Performing Unattended Maintenance 60
Backing Up a Database 61
Getting Database File Information 63
Other SQLite Tools 64
Summary 65
Chapter 3 SQL for SQLite 66
The Example Database 66
Installation 67
Running the Examples 68
Syntax 69
Commands 70
Literals 71
Keywords and Identifiers 72
Comments 72
Creating a Database 72
Creating Tables 72
Altering Tables 73
Querying the Database 74
Relational Operations 74
Select and the Operational Pipeline 76
Filtering 78
Values 79
Operators 79
Binary Operators 79
Logical Operators 82
The LIKE and GLOB Operators 82
Limiting and Ordering 83
Functions and Aggregates 85
Grouping 86
Removing Duplicates 91
Joining Tables 91
Inner Joins 93
Cross Joins 94
Outer Joins 95
Natural Joins 96
Preferred Syntax 96
Names and Aliases 96
Subqueries 98
Compound Queries 100
Conditional Results 102
Handling Null in SQLite 103
Summary 105
Chapter 4 Advanced SQL for SQLite 106
Modifying Data 106
Inserting Records 106
Inserting One Row 106
Inserting a Set of Rows 108
Inserting Multiple Rows 109
Updating Records 110
Deleting Records 111
Data Integrity 111
Entity Integrity 112
Unique Constraints 112
Primary Key Constraints 113
Domain Integrity 116
Default Values 117
NOT NULL Constraints 117
Check Constraints 118
Foreign Key Constraints 119
Collations 120
Storage Classes 120
Views 123
Indexes 125
Collations 126
Index Utilization 126
Triggers 127
Update Triggers 128
Error Handling 129
Updatable Views 129
Transactions 130
Transaction Scopes 130
Conflict Resolution 131
Database Locks 134
Deadlocks 135
Transaction Types 136
Database Administration 137
Attaching Databases 137
Cleaning Databases 138
Database Configuration 139
The Connection Cache Size 139
Getting Database Information 139
Synchronous Writes 140
Temporary Storage 141
Page Size, Encoding, and Autovacuum 141
Debugging 141
The System Catalog 142
Viewing Query Plans 142
Summary 143
Chapter 5 SQLite Design and Concepts 144
The API 144
The Principal Data Structures 145
Connections and Statements 145
The B-tree and Pager 146
The Core API 146
Connecting to a Database 147
Executing Prepared Queries 148
Using Parameterized SQL 150
Executing Wrapped Queries 151
Handling Errors 152
Formatting SQL Statements 153
Operational Control 154
Using Threads 155
The Extension API 155
Creating User-Defined Functions 155
Creating User-Defined Aggregates 156
Creating User-Defined Collations 157
Transactions 157
Transaction Life Cycles 157
Lock States 158
Read Transactions 160
Write Transactions 160
The Reserved State 160
The Pending State 161
The Exclusive State 161
Autocommit and Efficiency 162
Tuning the Page Cache 164
Transitioning to Exclusive 164
Sizing the Page Cache 164
Waiting for Locks 165
Using a Busy Handler 165
Using the Right Transaction 166
Code 168
Using Multiple Connections 168
The Importance of Finalizing 169
Shared Cache Mode 170
Summary 170
Chapter 6 The Core C API 172
Wrapped Queries 172
Connecting and Disconnecting 172
The exec Query 174
The Get Table Query 178
Prepared Queries 180
Compilation 180
Execution 181
Finalization and Reset 182
Fetching Records 183
Getting Column Information 184
Getting Column Values 185
A Practical Example 187
Parameterized Queries 188
Numbered Parameters 191
Named Parameters 192
Tcl Parameters 192
Errors and the Unexpected 193
Handling Errors 193
Handling Busy Conditions 195
User-Defined Busy Handlers 196
Advice 196
Handling Schema Changes 196
Operational Control 197
Commit Hooks 197
Rollback Hooks 198
Update Hooks 198
Authorizer Functions 199
Threads 209
Shared Cache Mode 209
Read Uncommitted Isolation Level 210
Unlock Notification 211
Threads and Memory Management 212
Summary 212
Chapter 7 The Extension C API 213
The API 214
Registering Functions 214
The Step Function 216
Return Values 216
Functions 218
Return Values 220
Arrays and Cleanup Handlers 220
Error Conditions 221
Returning Input Values 221
Aggregates 222
Registration Function 223
A Practical Example 224
The Step Function 225
The Aggregate Context 225
The Finalize Function 226
Results 226
Collations 227
Collation Defined 228
How Collation Works 228
Standard Collation Types 230
A Simple Example 230
The Compare Function 231
The Test Program 232
Results 233
Collation on Demand 234
Summary 235
Chapter 8 Language Extensions 236
Selecting an Extension 237
Perl 238
Installation 238
Connecting 239
Query Processing 239
Parameter Binding 241
User-Defined Functions 241
Aggregates 242
Python 243
Installation 243
Connecting 244
Query Processing 244
Parameter Binding 246
User-Defined Functions 247
Aggregates 248
APSW as an Alternative Python Interface 249
Ruby 249
Installation 249
Connecting 250
Query Processing 250
Parameter Binding 251
User-Defined Functions 253
Java 253
Installation 254
Connecting 255
Query Processing 255
User-Defined Functions and Aggregates 257
JDBC 258
Tcl 260
Installation 260
Connecting 261
Query Processing 261
User-Defined Functions 264
PHP 264
Installation 265
Connections 265
Queries 265
User-Defined Functions and Aggregates 268
Summary 269
Chapter 9 iOS Development with SQLite 270
Prerequisites for SQLite iOS Development 270
Signing Up for Apple Developer 271
Downloading and Installing Xcode and the iOS SDK 271
Alternatives to Xcode 275
Building the iSeinfeld iOS SQLite Application 276
Step 1: Creating a New Xcode Project 276
Step 2: Adding the SQLite Framework to Your Project 278
Step 3: Preparing the Foods Database 280
Step 4: Creating Classes for the Food Data 281
The Food Class 282
The FoodViewController Class 283
Step 5: Accessing and Querying the SQLite DB 286
Step 6: Final Polish and Wiring for iSeinfeld 289
iSeinfeld in Action! 289
Working with Large SQLite Databases Under iOS 293
Summary 294
Chapter 10 Android Development with SQLite 295
Prerequisites for SQLite Android Development 295
Check Prerequisites and the JDK 296
Downloading and Installing the Android SDK Starter Package 296
Downloading and Installing the Android Developer Tools 296
Adding Android Platforms and Components 297
The Android SQLite Classes and Interfaces 301
Using the Basic Helper Class, SQLiteOpenHelper 301
Working with the SQLiteDatabase Class 302
Opening and Closing the SQLiteDatabase 302
Executing General Queries with SQLiteDatabase 303
Using Convenience Methods with SQLiteDatabase 304
Managing Transactions with SQLiteDatabase 305
Using Other SQLiteDatabase Methods 306
Applying SQLiteOpenHelper and SQLiteDatabase in Practice 306
Querying SQLite with SQLiteQueryBuilder 309
Building the Seinfeld Android SQLite Application 310
Creating a New Android Project 311
Adding the Seinfeld SQLite Database to Your Project 312
Querying the Foods Table 312
Defining the User Interface 313
Linking the Data and User Interface 314
Viewing the Finished Seinfeld Application 315
Care and Feeding for SQLite Android Applications 316
Database Backup for Android 316
Working with Large SQLite Databases Under Android 316
Summary 317
Chapter 11 SQLite Internals and New Features 318
The B-Tree and Pager Modules 318
Database File Format 318
Page Reuse and Vacuum 319
B-Tree Records 319
B+Trees 319
Records and Fields 320
Hierarchical Data Organization 322
Overflow Pages 322
The B-Tree API 323
Access and Transaction Functions 323
Table Functions 324
Cursor Functions 324
Record Functions 325
Configuration Functions 325
Manifest Typing, Storage Classes, and Affinity 326
Manifest Typing 326
Type Affinity 328
Column Types and Affinities 328
Affinities and Storage 329
Affinities in Action 329
Storage Classes and Type Conversions 330
Write Ahead Logging 333
How WAL Works 333
Checkpoints 333
Concurrency 334
Activation and Configuration WAL 334
WAL Advantages and Disadvantages 335
Operational Issues with WAL-Enabled SQLite Databases 336
Performance 336
Recovery 336
Summary 337
Index 338

Produkt-Details

• Erscheint lt. Verlag: 28.1.2011
• Zusatzinfo: XIX, 368 p.
• Verlagsort: Berkeley
• Sprache: englisch
• Themenwelt: Mathematik / Informatik ► Informatik ► Datenbanken
• Themenwelt: Mathematik / Informatik ► Informatik ► Software Entwicklung
• Schlagworte: Database • Database Management • Relational Database • SQL • SQLite
• ISBN-10: 1-4302-3226-9 / 1430232269
• ISBN-13: 978-1-4302-3226-1 / 9781430232261