You are previewing Using SQLite.

Using SQLite

Cover of Using SQLite by Jay A. Kreibich Published by O'Reilly Media, Inc.
  1. Using SQLite
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
      2. Views
      3. Indexes
    6. Data Manipulation Language
      1. Row Modification Commands
      2. The Query Command
    7. Transaction Control Language
      1. ACID Transactions
      2. SQL Transactions
      3. Save-Points
    8. System Catalogs
    9. Wrap-up
  9. 5. The SELECT Command
    1. SQL Tables
    2. The SELECT Pipeline
      1. FROM Clause
      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
  10. 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
    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
  11. 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
    8. Utility Functions
      1. Version Management
      2. Memory Management
    9. Summary
  12. 8. Additional Features and APIs
    1. Date and Time Features
      1. Application Requirements
      2. Representations
      3. Time and Date Functions
    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
  13. 9. SQL Functions and Extensions
    1. Scalar Functions
      1. Registering Functions
      2. Extracting Parameters
      3. Returning Results and Errors
      4. Example
    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
  14. 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
      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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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()
  20. 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
  21. 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()
  22. Index
  23. About the Author
  24. Colophon
  25. Copyright
O'Reilly logo

Self-Contained, No Server Required

Unlike most RDBMS products, SQLite does not have a client/server architecture. Most large-scale database systems have a large server package that makes up the database engine. The database server often consists of multiple processes that work in concert to manage client connections, file I/O, caches, query optimization, and query processing. A database instance typically consists of a large number of files organized into one or more directory trees on the server filesystem. In order to access the database, all of the files must be present and correct. This can make it somewhat difficult to move or reliably back up a database instance.

All of these components require resources and support from the host computer. Best practices also dictate that the host system be configured with dedicated service-user accounts, startup scripts, and dedicated storage, making the database server a very intrusive piece of software. For this reason, and for performance concerns, it is customary to dedicate a host computer system solely for the database server software.

To access the database, client software libraries are typically provided by the database vendor. These libraries must be integrated into any client application that wishes to access the database server. These client libraries provide APIs to find and connect to the database server, as well as set up and execute database queries and commands. Figure 1-1 shows how everything fits together in a typical client/server RDBMS.

In contrast, SQLite has no separate server. The entire database engine is integrated into whatever application needs to access a database. The only shared resource among applications is the single database file as it sits on disk. If you need to move or back up the database, you can simply copy the file. Figure 1-2 shows the SQLite infrastructure.

By eliminating the server, a significant amount of complexity is removed. This simplifies the software components and nearly eliminates the need for advanced operating system support. Unlike a traditional RDBMS server that requires advanced multitasking and high-performance inter-process communication, SQLite requires little more than the ability to read and write to some type of storage.

Traditional RDBMS client/server architecture that utilizes a client library.

Figure 1-1. Traditional RDBMS client/server architecture that utilizes a client library.

The SQLite server-less architecture.

Figure 1-2. The SQLite server-less architecture.

This simplicity makes it fairly straightforward to port SQLite to just about any environment, including mobile phones, handheld media players, game consoles, and other devices, where no traditional database system could ever dare to venture.

Warning

Although SQLite does not use a traditional client/server architecture, it is common to speak of applications being “SQLite clients.” This terminology is often used to describe independent applications that simultaneously access a shared SQLite database file, and is not meant to imply that there is a separate server.

SQLite is designed to be integrated directly into an executable. This eliminates the need for an external library and simplifies distribution and installation. Removing external dependencies also removes most versioning issues. If the SQLite code is built right into your application, you never have to worry about linking to the correct version of a client library, or that the client library is version-compatible with the database server.

Eliminating the server imposes some restrictions, however. SQLite is designed to address localized storage needs, such as a web server accessing a local database. This means it isn’t well suited for situations where multiple client machines need to access a centralized database. That situation is more representative of a client/server architecture, and is better serviced by a database system that uses the same architecture.

The best content for your career. Discover unlimited learning on demand for around $1/day.