Using SQLite

Using SQLite

by Jay A. Kreibich
Released August 2010
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596521189

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

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

Publisher resources

View/Submit Errata

Table of contents

  1. Using SQLite
  2. Dedication
  3. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  4. A Note Regarding Supplemental Files
  5. Preface
    1. SQLite Versions
    2. Email Lists
    3. Example Code Download
    4. How We Got Here
    5. Conventions Used in This Book
    6. Using Code Examples
    7. Safari® Books Online
    8. How to Contact Us
  6. 1. What Is SQLite?
    1. Self-Contained, No Server Required
    2. Single File Database
    3. Zero Configuration
    4. Embedded Device Support
    5. Unique Features
    6. Compatible License
    7. Highly Reliable
  7. 2. Uses of SQLite
    1. Database Junior
    2. Application Files
    3. Application Cache
    4. Archives and Data Stores
    5. Client/Server Stand-in
    6. Teaching Tool
    7. Generic SQL Engine
    8. Not the Best Choice
    9. Big Name Users
  8. 3. Building and Installing SQLite
    1. SQLite Products
    2. Precompiled Distributions
    3. Documentation Distribution
    4. Source Distributions
      1. The Amalgamation
      2. Source Files
      3. Source Downloads
    5. Building
      1. Configure
      2. Manually
      3. Build Customization
    6. Build and Installation Options
    7. An sqlite3 Primer
    8. Summary
  9. 4. The SQL Language
    1. Learning SQL
    2. Brief Background
      1. Declarative
      2. Portability
    3. General Syntax
      1. Basic Syntax
      2. Three-Valued Logic
      3. Simple Operators
    4. SQL Data Languages
    5. Data Definition Language
      1. Tables
        1. The basics
        2. Column types
        3. Column constraints
        4. Primary keys
        5. Table constraints
        6. Tables from queries
        7. Altering tables
        8. Dropping tables
        9. Virtual tables
      2. Views
      3. Indexes
    6. Data Manipulation Language
      1. Row Modification Commands
        1. INSERT
        2. UPDATE
        3. DELETE
      2. The Query Command
    7. Transaction Control Language
      1. ACID Transactions
      2. SQL Transactions
      3. Save-Points
    8. System Catalogs
    9. Wrap-up
  10. 5. The SELECT Command
    1. SQL Tables
    2. The SELECT Pipeline
      1. FROM Clause
        1. CROSS JOIN
        2. INNER JOIN
        3. OUTER JOIN
        4. Table aliases
      2. WHERE Clause
      3. GROUP BY Clause
      4. SELECT Header
      5. HAVING Clause
      6. DISTINCT Keyword
      7. ORDER BY Clause
      8. LIMIT and OFFSET Clauses
    3. Advanced Techniques
      1. Subqueries
      2. Compound SELECT Statements
      3. Alternate JOIN Notation
    4. SELECT Examples
      1. Simple SELECTs
      2. Simple JOINs
      3. JOIN...ON
      4. JOIN...USING, NATURAL JOIN
      5. OUTER JOIN
      6. Compound JOIN
      7. Self JOIN
      8. WHERE Examples
      9. GROUP BY Examples
      10. ORDER BY Examples
    5. What’s Next
  11. 6. Database Design
    1. Tables and Keys
      1. Keys Define the Table
      2. Foreign Keys
      3. Foreign Key Constraints
      4. Generic ID Keys
      5. Keep It Specific
    2. Common Structures and Relationships
      1. One-to-One Relationships
      2. One-to-Many Relationships
      3. Many-to-Many Relationships
      4. Hierarchies and Trees
        1. Adjacency Model
        2. Nested set
        3. More information
    3. Normal Form
      1. Normalization
      2. Denormalization
      3. The First Normal Form
      4. The Second Normal Form
      5. The Third Normal Form
      6. Higher Normal Forms
    4. Indexes
      1. How They Work
      2. Must Be Diverse
      3. INTEGER PRIMARY KEYs
      4. Order Matters
      5. One at a Time
      6. Index Summary
    5. Transferring Design Experience
      1. Tables Are Types
      2. Keys Are Backwards Pointers
      3. Do One Thing
    6. Closing
  12. 7. C Programming Interface
    1. API Overview
      1. Structure
      2. Strings and Unicode
      3. Error Codes
      4. Structures and Allocations
      5. More Info
    2. Library Initialization
    3. Database Connections
      1. Opening
      2. Special Cases
      3. Closing
      4. Example
    4. Prepared Statements
      1. Statement Life Cycle
      2. Prepare
      3. Step
      4. Result Columns
      5. Reset and Finalize
      6. Statement Transitions
      7. Examples
    5. Bound Parameters
      1. Parameter Tokens
      2. Binding Values
      3. Security and Performance
      4. Example
      5. Potential Pitfalls
    6. Convenience Functions
    7. Result Codes and Error Codes
      1. Standard Codes
      2. Extended Codes
      3. Error Functions
      4. Prepare v2
      5. Transactions and Errors
      6. Database Locking
        1. Busy handlers
        2. Deadlocks
        3. Avoiding SQLITE_BUSY
        4. Avoiding deadlocks
        5. When BUSY becomes BLOCKED
    8. Utility Functions
      1. Version Management
      2. Memory Management
    9. Summary
  13. 8. Additional Features and APIs
    1. Date and Time Features
      1. Application Requirements
      2. Representations
        1. Julian Day
        2. Text values
        3. Time zones
      3. Time and Date Functions
        1. Conversion Function
        2. Convenience functions
        3. Time literals
        4. Examples
    2. ICU Internationalization Extension
    3. Full-Text Search Module
      1. Creating and Populating FTS Tables
      2. Searching FTS Tables
      3. More Details
    4. R*Trees and Spatial Indexing Module
    5. Scripting Languages and Other Interfaces
      1. Perl
      2. PHP
      3. Python
      4. Java
      5. Tcl
      6. ODBC
      7. .NET
      8. C++
      9. Other Languages
    6. Mobile and Embedded Development
      1. Memory
      2. Storage
      3. Other Resources
      4. iPhone Support
      5. Other Environments
    7. Additional Extensions
  14. 9. SQL Functions and Extensions
    1. Scalar Functions
      1. Registering Functions
      2. Extracting Parameters
      3. Returning Results and Errors
      4. Example
        1. sql_set_limit
        2. sql_get_limit
        3. sql_getset_limit
        4. sql_getset_var_limit
    2. Aggregate Functions
      1. Defining Aggregates
      2. Aggregate Context
      3. Example
    3. Collation Functions
      1. Registering a Collation
      2. Collation Example
    4. SQLite Extensions
      1. Extension Architecture
      2. Extension Design
      3. Example Extension: sql_trig
      4. Building and Integrating Static Extensions
      5. Using Loadable Extensions
      6. Building Loadable Extensions
      7. Loadable Extension Security
      8. Loading Loadable Extensions
      9. Multiple Entry Points
      10. Chapter Summary
  15. 10. Virtual Tables and Modules
    1. Introduction to Modules
      1. Internal Modules
      2. External Modules
      3. Example Modules
      4. SQL for Anything
    2. Module API
    3. Simple Example: dblist Module
      1. Create and Connect
        1. Allocate the vtab structure
        2. Define the table structure
        3. Storage initialization
        4. Create/connect dblist example
      2. Disconnect and Destroy
      3. Query Optimization
      4. Custom Functions
      5. Table Rename
      6. Opening and Closing Table Cursors
      7. Filtering Rows
      8. Extracting and Returning Data
      9. Virtual Table Modifications
      10. Cursor Sequence
      11. Transaction Control
      12. Register the Module
      13. Example Usage
    4. Advanced Example: weblog Module
      1. Create and Connect
      2. Disconnect and Destroy
      3. Other Table Functions
      4. Open and Close
      5. Filter
      6. Rows and Columns
      7. Register the Module
      8. Example Usage
    5. Best Index and Filter
      1. Purpose and Need
      2. xBestIndex()
      3. xFilter()
      4. Typical Usage
    6. Wrap-Up
  16. A. SQLite Build Options
    1. Shell Directives
      1. ENABLE_READLINE
    2. Default Values
      1. SQLITE_DEFAULT_AUTOVACUUM
      2. SQLITE_DEFAULT_CACHE_SIZE
      3. SQLITE_DEFAULT_FILE_FORMAT
      4. SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT
      5. SQLITE_DEFAULT_MEMSTATUS
      6. SQLITE_DEFAULT_PAGE_SIZE
      7. SQLITE_DEFAULT_TEMP_CACHE_SIZE
      8. YYSTACKDEPTH
    3. Sizes and Limits
      1. SQLITE_MAX_ATTACHED
      2. SQLITE_MAX_COLUMN
      3. SQLITE_MAX_COMPOUND_SELECT
      4. SQLITE_MAX_DEFAULT_PAGE_SIZE
      5. SQLITE_MAX_EXPR_DEPTH
      6. SQLITE_MAX_FUNCTION_ARG
      7. SQLITE_MAX_LENGTH
      8. SQLITE_MAX_LIKE_PATTERN_LENGTH
      9. SQLITE_MAX_PAGE_COUNT
      10. SQLITE_MAX_PAGE_SIZE
      11. SQLITE_MAX_SQL_LENGTH
      12. SQLITE_MAX_TRIGGER_DEPTH
      13. SQLITE_MAX_VARIABLE_NUMBER
    4. Operation and Behavior
      1. SQLITE_CASE_SENSITIVE_LIKE
      2. SQLITE_HAVE_ISNAN
      3. SQLITE_OS_OTHER
      4. SQLITE_SECURE_DELETE
      5. SQLITE_THREADSAFE
      6. SQLITE_TEMP_STORE
    5. Debug Settings
      1. SQLITE_DEBUG
      2. SQLITE_MEMDEBUG
    6. Enable Extensions
      1. SQLITE_ENABLE_ATOMIC_WRITE
      2. SQLITE_ENABLE_COLUMN_METADATA
      3. SQLITE_ENABLE_FTS3
      4. SQLITE_ENABLE_FTS3_PARENTHESIS
      5. SQLITE_ENABLE_ICU
      6. SQLITE_ENABLE_IOTRACE
      7. SQLITE_ENABLE_LOCKING_STYLE
      8. SQLITE_ENABLE_MEMORY_MANAGEMENT
      9. SQLITE_ENABLE_MEMSYS3
      10. SQLITE_ENABLE_MEMSYS5
      11. SQLITE_ENABLE_RTREE
      12. SQLITE_ENABLE_STAT2
      13. SQLITE_ENABLE_UPDATE_DELETE_LIMIT
      14. SQLITE_ENABLE_UNLOCK_NOTIFY
      15. YYTRACKMAXSTACKDEPTH
    7. Limit Features
      1. SQLITE_DISABLE_LFS
      2. SQLITE_DISABLE_DIRSYNC
      3. SQLITE_ZERO_MALLOC
    8. Omit Core Features
  17. B. sqlite3 Command Reference
    1. Command-Line Options
    2. Interactive Dot-Commands
      1. .backup
      2. .bail
      3. .databases
      4. .dump
      5. .echo
      6. .exit
      7. .explain
      8. .headers
      9. .help
      10. .import
      11. .indices
      12. .iotrace
      13. .load
      14. .log
      15. .mode
      16. .nullvalue
      17. .output
      18. .prompt
      19. .quit
      20. .read
      21. .restore
      22. .schema
      23. .separator
      24. .show
      25. .tables
      26. .timeout
      27. .timer
      28. .width
  18. C. SQLite SQL Command Reference
    1. SQLite SQL Commands
      1. ALTER TABLE
      2. ANALYZE
      3. ATTACH DATABASE
      4. BEGIN TRANSACTION
      5. COMMIT TRANSACTION
      6. CREATE INDEX
      7. CREATE TABLE
      8. CREATE TRIGGER
      9. CREATE VIEW
      10. CREATE VIRTUAL TABLE
      11. DELETE
      12. DETACH DATABASE
      13. DROP INDEX
      14. DROP TABLE
      15. DROP TRIGGER
      16. DROP VIEW
      17. END TRANSACTION
      18. EXPLAIN
      19. INSERT
      20. PRAGMA
      21. REINDEX
      22. RELEASE SAVEPOINT
      23. REPLACE
      24. ROLLBACK TRANSACTION
      25. SAVEPOINT
      26. SELECT
      27. UPDATE
      28. VACUUM
  19. D. SQLite SQL Expression Reference
    1. Literal Expressions
    2. Logic Representations
    3. Unary Expressions
    4. Binary Expressions
    5. Function Calls
    6. Column Names
    7. General Expressions
      1. AND
      2. BETWEEN
      3. CASE
      4. CAST
      5. COLLATE
      6. EXISTS
      7. GLOB
      8. IN
      9. IS
      10. ISNULL
      11. LIKE
      12. MATCH
      13. NOTNULL
      14. OR
      15. RAISE
      16. REGEXP
      17. SELECT
  20. E. SQLite SQL Function Reference
    1. Scalar Functions
      1. abs()
      2. changes()
      3. coalesce()
      4. date()
      5. datetime()
      6. glob()
      7. ifnull()
      8. hex()
      9. julianday()
      10. last_insert_rowid()
      11. length()
      12. like()
      13. load_extension()
      14. lower()
      15. ltrim()
      16. match()
      17. max()
      18. min()
      19. nullif()
      20. quote()
      21. random()
      22. randomblob()
      23. regex()
      24. replace()
      25. round()
      26. rtrim()
      27. sqlite_compileoption_get()
      28. sqlite_compileoption_used()
      29. sqlite_source_id()
      30. sqlite_version()
      31. strftime()
      32. substr()
      33. time()
      34. total_changes()
      35. trim()
      36. typeof()
      37. upper()
      38. zeroblob()
    2. Aggregate Functions
      1. avg()
      2. count()
      3. group_concat()
      4. max()
      5. min()
      6. sum()
      7. total()
  21. F. SQLite SQL PRAGMA Reference
    1. SQLite PRAGMAs
      1. auto_vacuum
      2. cache_size
      3. case_sensitive_like
      4. collation_list
      5. count_changes
      6. database_list
      7. default_cache_size
      8. encoding
      9. foreign_keys
      10. foreign_key_list
      11. freelist_count
      12. full_column_names
      13. fullfsync
      14. ignore_check_constraints
      15. incremental_vacuum
      16. index_info
      17. index_list
      18. integrity_check
      19. journal_mode
      20. journal_size_limit
      21. legacy_file_format
      22. locking_mode
      23. lock_proxy_file
      24. lock_status
      25. max_page_count
      26. omit_readlock
      27. page_count
      28. page_size
      29. parser_trace
      30. quick_check
      31. read_uncommitted
      32. recursive_triggers
      33. reverse_unordered_selects
      34. schema_version
      35. secure_delete
      36. short_column_names
      37. sql_trace
      38. synchronous
      39. table_info
      40. temp_store
      41. temp_store_directory
      42. user_version
      43. vdbe_trace
      44. vdbe_listing
      45. writable_schema
  22. G. SQLite C API Reference
    1. API Datatypes
      1. sqlite3
      2. sqlite3_backup
      3. sqlite3_blob
      4. sqlite3_context
      5. sqlite3_int64, sqlite3_uint64, sqlite_int64, sqlite_uint64
      6. sqlite3_module
      7. sqlite3_mutex
      8. sqlite3_stmt
      9. sqlite3_value
      10. sqlite3_vfs
    2. API Functions
      1. sqlite3_aggregate_context()
      2. sqlite3_auto_extension()
      3. sqlite3_backup_finish()
      4. sqlite3_backup_init()
      5. sqlite3_backup_pagecount()
      6. sqlite3_backup_remaining()
      7. sqlite3_backup_step()
      8. sqlite3_bind_xxx()
      9. sqlite3_bind_parameter_count()
      10. sqlite3_bind_parameter_index()
      11. sqlite3_bind_parameter_name()
      12. sqlite3_blob_bytes()
      13. sqlite3_blob_close()
      14. sqlite3_blob_open()
      15. sqlite3_blob_read()
      16. sqlite3_blob_write()
      17. sqlite3_busy_handler()
      18. sqlite3_busy_timeout()
      19. sqlite3_changes()
      20. sqlite3_clear_bindings()
      21. sqlite3_close()
      22. sqlite3_collation_needed()
      23. sqlite3_column_xxx()
      24. sqlite3_column_bytes()
      25. sqlite3_column_count()
      26. sqlite3_column_database_name()
      27. sqlite3_column_decltype()
      28. sqlite3_column_name()
      29. sqlite3_column_origin_name()
      30. sqlite3_column_table_name()
      31. sqlite3_column_type()
      32. sqlite3_commit_hook()
      33. sqlite3_compileoption_get()
      34. sqlite3_compileoption_used()
      35. sqlite3_complete()
      36. sqlite3_config()
      37. sqlite3_context_db_handle()
      38. sqlite3_create_collation()
      39. sqlite3_create_function()
      40. sqlite3_create_module()
      41. sqlite3_data_count()
      42. sqlite3_db_config()
      43. sqlite3_db_handle()
      44. sqlite3_db_mutex()
      45. sqlite3_db_status()
      46. sqlite3_declare_vtab()
      47. sqlite3_enable_load_extension()
      48. sqlite3_enable_shared_cache()
      49. sqlite3_errcode()
      50. sqlite3_errmsg()
      51. sqlite3_exec()
      52. sqlite3_extended_errcode()
      53. sqlite3_extended_result_codes()
      54. sqlite3_file_control()
      55. sqlite3_finalize()
      56. sqlite3_free()
      57. sqlite3_free_table()
      58. sqlite3_get_autocommit()
      59. sqlite3_get_auxdata()
      60. sqlite3_get_table()
      61. sqlite3_initialize()
      62. sqlite3_interrupt()
      63. sqlite3_last_insert_rowid()
      64. sqlite3_libversion()
      65. sqlite3_libversion_number()
      66. sqlite3_limit()
      67. sqlite3_load_extension()
      68. sqlite3_log()
      69. sqlite3_malloc()
      70. sqlite3_memory_highwater()
      71. sqlite3_memory_used()
      72. sqlite3_mprintf()
      73. sqlite3_mutex_alloc()
      74. sqlite3_mutex_enter()
      75. sqlite3_mutex_free()
      76. sqlite3_mutex_held()
      77. sqlite3_mutex_leave()
      78. sqlite3_mutex_notheld()
      79. sqlite3_mutex_try()
      80. sqlite3_next_stmt()
      81. sqlite3_open()
      82. sqlite3_open_v2()
      83. sqlite3_overload_function()
      84. sqlite3_prepare_xxx()
      85. sqlite3_profile()
      86. sqlite3_progress_handler()
      87. sqlite3_randomness()
      88. sqlite3_realloc()
      89. sqlite3_release_memory()
      90. sqlite3_reset()
      91. sqlite3_reset_auto_extension()
      92. sqlite3_result_xxx()
      93. sqlite3_result_error_xxx()
      94. sqlite3_rollback_hook()
      95. sqlite3_set_authorizer()
      96. sqlite3_set_auxdata()
      97. sqlite3_shutdown()
      98. sqlite3_sleep()
      99. sqlite3_snprintf()
      100. sqlite3_soft_heap_limit()
      101. sqlite3_sourceid()
      102. sqlite3_sql()
      103. sqlite3_status()
      104. sqlite3_step()
      105. sqlite3_stmt_status()
      106. sqlite3_strnicmp()
      107. sqlite3_table_column_metadata()
      108. sqlite3_threadsafe()
      109. sqlite3_total_changes()
      110. sqlite3_trace()
      111. sqlite3_unlock_notify()
      112. sqlite3_update_hook()
      113. sqlite3_user_data()
      114. sqlite3_value_xxx()
      115. sqlite3_value_bytes()
      116. sqlite3_value_numeric_type()
      117. sqlite3_value_type()
      118. sqlite3_version[]
      119. sqlite3_vfs_find()
      120. sqlite3_vfs_register()
      121. sqlite3_vfs_unregister()
      122. sqlite3_vmprintf()
  23. Index
  24. About the Author
  25. Colophon
  26. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  27. Copyright

Product information

  • Title: Using SQLite
  • Author(s): Jay A. Kreibich
  • Release date: August 2010
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596521189