sqlite3.h

Overview

sqlite3.h is the primary C language header file that defines the public API for the SQLite library. It specifies the interfaces, constants, data types, and function prototypes that client applications use to interact with SQLite databases. This file embodies the contract between SQLite and its clients, detailing how to open, configure, execute queries against, and manage SQLite database connections and statements.

The file includes definitions for key SQLite abstractions such as database connections (sqlite3), prepared statements (sqlite3_stmt), virtual file system interfaces (sqlite3_vfs), and file I/O methods (sqlite3_io_methods). It also provides mechanisms for configuration, error handling, memory management, SQL compilation, execution, and extension registration.

This header is used by applications linking against the SQLite library or its variants (e.g., SQLCipher) to embed SQL database functionality.

Key Data Types and Structures

sqlite3

An opaque structure representing a database connection handle. It acts as the core object for all database operations.

• Obtained via sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2().

• Closed via sqlite3_close() or sqlite3_close_v2().

• Used in most SQLite API calls as the first parameter.

sqlite3_stmt

An opaque structure representing a compiled SQL statement (prepared statement).

• Created by sqlite3_prepare_v2() and related functions.

• Evaluated by invoking sqlite3_step().

• Destroyed by sqlite3_finalize().

sqlite3_file

Represents an open file in the VFS (Virtual File System) layer.

• Contains a pointer to sqlite3_io_methods which define the file operations.

• Intended to be subclassed by VFS implementations.

sqlite3_io_methods

Defines virtual methods for file I/O operations used by the SQLite core.

• Includes methods for reading, writing, locking, syncing, and file control.

• Supports multiple versions with extended functionality.

• Key methods: xRead(), xWrite(), xLock(), xUnlock(), xSync(), etc.

sqlite3_vfs

Represents an OS interface layer or Virtual File System.

• Provides abstraction over the underlying OS file system.

• Contains methods like xOpen(), xDelete(), xAccess(), and timing/randomness utilities.

• Supports extensibility with versioning (iVersion).

sqlite3_value and sqlite3_context

• sqlite3_value represents a SQL value with dynamic typing (integer, float, text, blob, null).

• sqlite3_context is the context passed to application-defined SQL functions to manage result and function state.

sqlite3_mem_methods

Defines an interface for custom memory allocators to replace SQLite’s default allocator.

Important Constants and Macros

• Result Codes: Constants like SQLITE_OK, SQLITE_ERROR, SQLITE_BUSY, and extended result codes such as SQLITE_IOERR_READ.

• File Open Flags: Flags for controlling file opening behavior, e.g., SQLITE_OPEN_READONLY, SQLITE_OPEN_CREATE, SQLITE_OPEN_URI.

• Lock Levels: Defines locking granularity for files (SQLITE_LOCK_NONE to SQLITE_LOCK_EXCLUSIVE).

• Text Encodings: Defines text encoding constants (SQLITE_UTF8, SQLITE_UTF16LE, SQLITE_UTF16BE, etc.).

• Function Flags: Flags for SQL function properties like SQLITE_DETERMINISTIC and SQLITE_DIRECTONLY.

• Authorizer Codes: Codes for SQL statement action authorization callbacks (SQLITE_CREATE_TABLE, SQLITE_INSERT, etc.).

• Prepare Flags: Flags modifying SQL statement preparation behavior (SQLITE_PREPARE_PERSISTENT, SQLITE_PREPARE_NO_VTAB).

Primary Functions and Their Usage

Initialization and Shutdown

• int sqlite3_initialize(void)

  Initializes the SQLite library. Only effective the first time it is called.

• int sqlite3_shutdown(void)

  Deallocates resources allocated by sqlite3_initialize().

• int sqlite3_os_init(void) and int sqlite3_os_end(void)

  Platform-specific initialization and shutdown routines, called internally by sqlite3_initialize() and sqlite3_shutdown().

Opening and Closing Database Connections

• int sqlite3_open(const char *filename, sqlite3 **ppDb)

  Opens a UTF-8 encoded database file and returns a database handle.

• int sqlite3_open16(const void *filename, sqlite3 **ppDb)

  Opens a UTF-16 encoded database file.

• int sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs)

  Opens a database file with additional flags and optional VFS name.

• int sqlite3_close(sqlite3*)

  Closes a database connection, returning SQLITE_OK on success or SQLITE_BUSY if unfinalized prepared statements or unclosed BLOB handles exist.

• int sqlite3_close_v2(sqlite3*)

  Like sqlite3_close(), but marks the connection as a zombie if resources remain, deferring cleanup.

SQL Execution

• int sqlite3_exec(sqlite3*, const char sql, int (callback)(void,int,char,char*), void *, char **errmsg)

  Convenience function to execute multiple semicolon-separated SQL statements with optional callback for each result row.

  Example:

  sqlite3_exec(db, "CREATE TABLE test(id INT); INSERT INTO test VALUES(1);",
               NULL, NULL, NULL);
  

• int sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail)

  Compiles the first SQL statement in zSql into a prepared statement.

• int sqlite3_step(sqlite3_stmt*)

  Executes the prepared statement, returning SQLITE_ROW for each row of results or SQLITE_DONE when complete.

• int sqlite3_finalize(sqlite3_stmt *pStmt)

  Deletes a prepared statement object.

• int sqlite3_reset(sqlite3_stmt *pStmt)

  Resets a prepared statement to its initial state for re-execution.

Binding Parameters to Prepared Statements

• Functions such as sqlite3_bind_int(), sqlite3_bind_text(), sqlite3_bind_blob(), and sqlite3_bind_null() bind values to SQL parameters.

• int sqlite3_bind_parameter_count(sqlite3_stmt*): Returns the number of parameters.

• const char sqlite3_bind_parameter_name(sqlite3_stmt, int): Returns the name of a parameter by index.

• int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName): Returns the index of a parameter by name.

Retrieving Result Column Data

• Functions to access columns of the current result row in a prepared statement:

  • sqlite3_column_int(), sqlite3_column_int64(), sqlite3_column_double()

  • sqlite3_column_text(), sqlite3_column_blob()

  • int sqlite3_column_bytes(sqlite3_stmt*, int): Size of a BLOB or text result.

  • int sqlite3_column_count(sqlite3_stmt*): Number of columns in result set.

• Metadata retrieval functions:

  • const char sqlite3_column_name(sqlite3_stmt, int): Column name.

  • const char sqlite3_column_database_name(sqlite3_stmt, int): Database origin.

  • const char sqlite3_column_table_name(sqlite3_stmt, int): Table origin.

  • const char sqlite3_column_decltype(sqlite3_stmt, int): Declared datatype.

Error Handling

• int sqlite3_errcode(sqlite3 *db): Returns numeric error or extended error code of the most recent API call.

• const char sqlite3_errmsg(sqlite3): Returns English-language error message string for most recent error.

• int sqlite3_error_offset(sqlite3 *db): Returns byte offset of token causing error in SQL.

• const char *sqlite3_errstr(int): Returns English-language description of an error code.

Configuration and Diagnostics

• int sqlite3_config(int, ...): Set global SQLite configuration options.

• int sqlite3_db_config(sqlite3*, int op, ...): Configure a single database connection.

• int sqlite3_compileoption_used(const char *zOptName): Query if compile-time option was used.

• const char *sqlite3_compileoption_get(int N): Iterate over compile-time options.

Memory Management

• void sqlite3_malloc(int), sqlite3_free(void), void sqlite3_realloc(void, int): Memory allocator for SQLite internals.

• sqlite3_int64 sqlite3_memory_used(void): Returns current outstanding memory usage.

• sqlite3_int64 sqlite3_memory_highwater(int resetFlag): Returns peak memory usage.

Virtual File System (VFS) Interaction

• sqlite3_vfs structure abstracts the underlying OS file system.

• xOpen(), xDelete(), xAccess(), xFullPathname(): File management methods.

• xRandomness(), xSleep(), xCurrentTime(), xCurrentTimeInt64(): Utility methods for randomness, sleeping, and time queries.

• VFS implementations subclass sqlite3_file and provide sqlite3_io_methods to handle file operations.

• File control opcodes (e.g., SQLITE_FCNTL_LOCKSTATE, SQLITE_FCNTL_SIZE_HINT) enable fine control over file behavior.

SQL Function and Aggregate Registration

• int sqlite3_create_function(sqlite3*, const char*, int nArg, int eTextRep, void *pApp, void (*xFunc)(...), void (*xStep)(...), void (*xFinal)(...))

  Registers or redefines SQL scalar or aggregate functions.

• int sqlite3_create_window_function(...)

  Registers window aggregate functions with additional callbacks.

Important Implementation Details and Algorithms

• Dynamic Typing & Type Conversion: SQLite values are dynamically typed (sqlite3_value), and automatic type conversions occur when accessing values through column functions.

• Extended Result Codes: SQLite supports extended error codes for detailed error diagnostics, enabled per connection with sqlite3_extended_result_codes().

• Prepared Statements Lifecycle: Statements are compiled once and can be executed multiple times with bound parameters, and reset for reuse.

• Thread Safety: The library can be compiled with different threading modes (SQLITE_THREADSAFE), and the API provides sqlite3_threadsafe() to test at runtime.

• Virtual File System Abstraction: SQLite abstracts OS file operations to support portability and custom VFS implementations.

• URI Filename Support: The open functions support URI style filenames with query parameters to customize connection behavior.

Interaction with Other System Components

• Applications: Use the API to open databases, prepare and execute SQL statements, bind parameters, and fetch results.

• VFS Implementations: Provide custom file I/O methods by subclassing sqlite3_file and implementing sqlite3_io_methods. The VFS layer is used by SQLite core for all filesystem interactions.

• Loadable Extensions: Extensions interact via opaque sqlite3_api_routines pointers and use this API to register functions, collations, and virtual tables.

• Memory Allocator and Mutex Subsystems: Can be replaced or customized via sqlite3_config() using the sqlite3_mem_methods and sqlite3_mutex interfaces.

• Error Logging: Applications can configure logging callbacks using sqlite3_config() (with SQLITE_CONFIG_LOG).

• Busy and Progress Handlers: Application-defined callbacks for handling database busy conditions and progress reporting during long-running queries.

Selected Function Documentation Examples

int sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs)

• Description: Opens a new database connection with specified flags and optional VFS.

• Parameters:

  • filename: UTF-8 database filename or URI.

  • ppDb: Out parameter; pointer to store the database handle.

  • flags: Bitwise OR of open flags such as SQLITE_OPEN_READWRITE, SQLITE_OPEN_CREATE.

  • zVfs: Name of the VFS module to use, or NULL for default.

• Returns: SQLITE_OK on success or an error code.

• Usage Example:

  sqlite3 *db;
  int rc = sqlite3_open_v2("example.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
  if (rc != SQLITE_OK) {
    // Handle error
  }
  

int sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail)

• Description: Compiles the first SQL statement in UTF-8 string zSql into a prepared statement.

• Parameters:

  • db: Open database connection.

  • zSql: SQL statement(s) to compile.

  • nByte: Length of zSql in bytes, or -1 if nul-terminated.

  • ppStmt: Out parameter for prepared statement object.

  • pzTail: Out parameter to the unused portion of zSql.

• Returns: SQLITE_OK on success, or an error code.

• Usage Example:

  sqlite3_stmt *stmt;
  const char *tail;
  int rc = sqlite3_prepare_v2(db, "SELECT * FROM users WHERE id = ?;", -1, &stmt, &tail);
  if (rc == SQLITE_OK) {
    // Bind parameters and execute
  }
  

int sqlite3_step(sqlite3_stmt *pStmt)

• Description: Executes a prepared statement until completion or a row is available.

• Parameters:

  • pStmt: Prepared statement.

• Returns: One of:

  • SQLITE_ROW: A row of data is available.

  • SQLITE_DONE: Statement has finished executing.

  • SQLITE_BUSY, SQLITE_ERROR, SQLITE_INTERRUPT, or other error codes.

• Usage Example:

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    // Process row
  }
  

int sqlite3_bind_int(sqlite3_stmt*, int, int)

• Description: Binds an integer value to a host parameter in a prepared statement.

• Parameters:

  • The prepared statement (sqlite3_stmt*).

  • Parameter index (1-based).

  • Integer value to bind.

• Returns: SQLITE_OK on success or error code.

• Usage Example:

  sqlite3_bind_int(stmt, 1, 42);  // Bind 42 to the first parameter
  

const void *sqlite3_column_text(sqlite3_stmt*, int iCol)

• Description: Returns a pointer to the UTF-8 encoded text of the specified column in the current result row.

• Parameters:

  • sqlite3_stmt*: Prepared statement.

  • iCol: Column index (0-based).

• Returns: Pointer to UTF-8 text or NULL if the value is NULL.

Visual Diagram

flowchart TD
subgraph Database Connection (sqlite3)
A[Open: sqlite3_open*] --> B[Prepare Statement: sqlite3_prepare*]
B --> C[Bind Parameters: sqlite3_bind_*]
C --> D[Execute: sqlite3_step]
D --> E{Result?}
E -->|Row Data| F[Access Columns: sqlite3_column_*]
E -->|Done| G[Reset: sqlite3_reset]
G --> C
G -->|Or| H[Finalize: sqlite3_finalize]
H --> I[Close Connection: sqlite3_close*]
end
subgraph VFS Layer (sqlite3_vfs)
J[VFS xOpen] --> K[sqlite3_file with sqlite3_io_methods]
K --> L[File I/O: xRead, xWrite, xLock, xSync, ...]
end
A ---|Uses| J

Notes on Usage and Interaction

• Applications interact primarily with sqlite3 and sqlite3_stmt objects to manage SQL execution lifecycle.

• VFS and file I/O abstractions are intended for advanced users implementing custom OS interfaces.

• Memory and threading behaviors can be configured globally or per connection.

• The API supports extensive error reporting and diagnostics for robust applications.

• SQL functions and aggregates can be extended by registering callbacks using the function creation routines.

• Bindings allow safe parameterization of SQL statements, preventing SQL injection.

• The header file defines many macros and constants for fine control over SQLite behavior.

Reference to Related Topics

• Virtual File System (VFS) — for detailed explanation of sqlite3_vfs and file I/O methods.

• Prepared Statements — for usage patterns of sqlite3_stmt.

• SQL Function Creation — for registering custom SQL functions and aggregates.

• Error Handling and Result Codes — for understanding SQLite error codes and extended error codes.

• Memory Management in SQLite — for the memory allocator interface and configuration.

• Threading and Concurrency — for thread safety and mutex configuration.

• URI Filenames — for database file URI syntax and parameters.

• Binding Parameters — for detailed binding API and usage examples.