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

Convenience Functions

SQLite includes a number of convenience functions that can be used to prepare, step, and finalize an SQL statement in one call. Most of these functions exist for historical reasons and, as the name says, convenience.

While they’re not fully deprecated, there are a number of reasons why their use is not exactly encouraged. First off, understand that there is nothing special under the hood. Both of these functions eventually call the same sqlite3_prepare_xxx(), sqlite3_step(), and sqlite3_finalize() calls that are available in the public API. These functions are not faster, nor are they more efficient.

Second, since the API doesn’t support the use of bound parameters, you’re forced to use string manipulations to build your SQL commands. That means these functions are slower to process and much more vulnerable to SQL injection attacks. This is particularly dangerous because all the convenience functions are designed to automatically process multiple SQL statements from a single command string. If input strings are not properly sanitized, this situation effectively gives anyone providing input data full access to the database engine, including the ability to delete data or drop whole tables.

These functions also tend to be a bit slower. All results are returned in a string representation, without any kind of type information. This can make it difficult to determine the type of a return value, and can lead to a lot of extra type conversions.

For all their disadvantages, ...

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