An Introduction To ThinSQLite++

Table of Contents

• Summary
• Introduction
• Core Objects And Interfaces
• Typical Usage Of Core Objects and Methods
• Convenience Wrappers Around Core Methods
• Binding Parameters and Reusing Prepared Statements
• Configuring SQLite
• Extending SQLite
• Other Interfaces

Note

This page closely follows An Introduction To The SQLite C/C++ Interface. Since ThinSQLite++ is a thin and exact wrapper of SQLite's C API all the conceptual parts are exactly the same. The difference is in safety and convenience.

Summary

The following two objects and their methods comprise the essential elements of the ThinSQLite++ interface:

• database → The database connection object.
• statement → The prepared statement object.
• database::open → Open a connection to a new or existing SQLite database. Creates database instances.
• statement::create → Compile SQL text into byte-code that will do the work of querying or updating the database. Creates statement instances.
• statement::bind → Store application data into parameters of the original SQL.
• statement::step → Advance a statement to the next result row or to completion.
• statement::column_value → Column values in the current result row for a statement
• row_range → An STL forward range that wraps statement::step loop to access statement results in a standard C++ range fashion.
• database::exec → A set of wrapper functions that does statement::create, statement::step, etc. for a string of one or more SQL statements.
• row → An STL random-access container of cells. Yielded by row_range and database::exec
• cell → A cell contained in a row that wraps statement::column_ family of functions for a given column.
• exception → An exception thrown on any SQLite errors

Introduction

ThinSQLite++ has many APIs. However, most of the APIs are optional and very specialized and can be ignored by beginners. The core API is small, simple, and easy to learn. This article summarizes the core API.

Core Objects And Interfaces

The principal task of an SQL database engine is to evaluate SQL statements of SQL. To accomplish this, the developer needs two objects:

• The database connection object: database
• The prepared statement object: statement

Strictly speaking, the prepared statement object is not required since the convenience wrapper interface, database::exec, can be used and this convenience wrapper encapsulates and hides the prepared statement object. Nevertheless, an understanding of prepared statements is needed to make full use of ThinSQLite++.

The database connection and prepared statement objects are controlled by a small set of interfaces listed below.

• database::open
• statement::create
• statement::step
• statement::column_value

Here is a summary of what the core interfaces do:

• database::open

  This static method opens a connection to an SQLite database file and returns an std::unique_ptr to the database connection object. This is often the first ThinSQLite++ API call that an application makes and is a prerequisite for most other ThinSQLite++ APIs. This routine is the only way to construct the database connection object.

• statement::create

  This static method converts SQL text into a prepared statement object and returns an std::unique_ptr to that object. This interface requires a database connection reference created by a prior call to database::open and a text string containing the SQL statement to be prepared. This API does not actually evaluate the SQL statement. It merely prepares the SQL statement for evaluation.

  Think of each SQL statement as a small computer program. The purpose of statement::create is to compile that program into object code. The prepared statement is the object code. The statement::step interface then runs the object code to get a result.

• statement::step

  This method is used to evaluate a prepared statement that has been previously created by the statement::create interface. The statement is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke statement::step again. Continue invoking statement::step until it returns false indicating that the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to statement::step.

• statement::column_value

  This template method returns a single column from the current row of a result set for a prepared statement that is being evaluated by statement::step. Each time statement::step stops with a new result set row, this method can be called multiple times to find the values of all columns in that row.

  You indicate the desired return value type via a template parameter to statement::column_value. Possible types are:

  • int
  • int64_t
  • double
  • std::string_view
  • std::u8string_view (if char8_t is supported by your compiler/library)
  • blob_view (a span of bytes)

Typical Usage Of Core Objects and Methods

An application will typically use database::open to create a single database connection during initialization. Note that database::open can be used to either open existing database files or to create and open new database files. While many applications use only a single database connection, there is no reason why an application cannot call database::open multiple times in order to open multiple database connections - either to the same database or to different databases. Sometimes a multi-threaded application will create separate database connections for each thread. Note that a single database connection can access two or more databases using the ATTACH SQL command, so it is not necessary to have a separate database connection for each database file.

To run an SQL statement, the application follows these steps:

1. Create a prepared statement using statement::create.
2. Evaluate the prepared statement by statement::step one or more times.
3. For queries, extract results by calling statement::column_value in between two calls to statement::step.
4. Handle (or decide not to) any exceptions thrown while doing the above

The foregoing is all one really needs to know in order to use SQLite effectively. All the rest is optimization and detail.

Convenience Wrappers Around Core Methods

A row_range is a convenience wrapper that exposes statement::step loop as STL forward range. It yields row objects which, in turn, model a random access container of cells. Using these wrappers you can handle data extraction as STL iteration, using range-for loops and STL algorithms.

The database::exec methods are convenience wrappers that parse multiple SQL statements and execute statement::create and statement::step loop with a single function call. An optional callback function passed into database::exec is used to process each row of the result set.

It is important to realize that neither row_range nor database::exec do anything that cannot be accomplished using the core methods. In fact, these wrappers are implemented purely in terms of the core routines.

Binding Parameters and Reusing Prepared Statements

In prior discussion, it was assumed that each SQL statement is prepared once, evaluated, then destroyed. However, SQLite allows the same prepared statement to be evaluated multiple times. This is accomplished using the following:

• statement::reset or auto_reset RAII wrapper
• statement::bind

After a prepared statement has been evaluated by one or more calls to statement::step, it can be reset in order to be evaluated again by a call to statement::reset. Think of statement::reset as rewinding the prepared statement program back to the beginning. Using statement::reset on an existing prepared statement rather than creating a new prepared statement avoids unnecessary calls to statement::create. For many SQL statements, the time needed to run statement::create equals or exceeds the time needed by statement::step. So avoiding calls to statement::create can give a significant performance improvement.

As with any "destruction" action ensuring that statement::reset is called on all code paths before reuse can be tricky and best dealt with RAII. The auto_reset performs statement::reset on destruction and can greatly simply managing statement reuse.

It is not commonly useful to evaluate the exact same SQL statement more than once. More often, one wants to evaluate similar statements. For example, you might want to evaluate an INSERT statement multiple times with different values. Or you might want to evaluate the same query multiple times using a different key in the WHERE clause. To accommodate this, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same prepared statement can be evaluated a second time using the new values.

SQLite allows a parameter wherever a string literal, blob literal, numeric constant, or NULL is allowed in queries or data modification statements. (DQL or DML) (Parameters may not be used for column or table names, or as values for constraints or default values. (DDL)) A parameter takes one of the following forms:

• ?
• ?NNN
• :AAA
• $AAA
• @AAA

In the examples above, NNN is an integer value and AAA is an identifier. A parameter initially has a value of NULL. Prior to calling statement::step for the first time or immediately after statement::reset, the application can invoke one of the statement::bind methods to attach values to the parameters. Each call to statement::bind overrides prior bindings on the same parameter.

An application is allowed to prepare multiple SQL statements in advance and evaluate them as needed. There is no arbitrary limit to the number of outstanding prepared statements. Some applications call statement::create multiple times at start-up to create all of the prepared statements they will ever need. Other applications keep a cache of the most recently used prepared statements and then reuse prepared statements out of the cache when available. Another approach is to only reuse prepared statements when they are inside of a loop.

Configuring SQLite

The default configuration for SQLite works great for most applications. But sometimes developers want to tweak the setup to try to squeeze out a little more performance, or take advantage of some obscure feature.

The config interface is used to make global, process-wide configuration changes for SQLite. The config interface must be called before any database connections are created. The config interface allows the programmer to do things like:

• Adjust how SQLite does memory allocation, including setting up alternative memory allocators appropriate for safety-critical real-time embedded systems and application-defined memory allocators.
• Set up a process-wide error log.
• Specify an application-defined page cache.
• Adjust the use of mutexes so that they are appropriate for various threading models, or substitute an application-defined mutex system.

After process-wide configuration is complete and database connections have been created, individual database connections can be configured using calls to database::limit and database::config.

Extending SQLite

ThinSQLite++ includes interfaces that can be used to extend SQLite functionality. Such routines include:

• database::create_collation
• database::create_function
• database::create_module

The database::create_collation methods is used to create new collating sequences for sorting text.

The database::create_function methods create new SQL functions - either scalar or aggregate. The new function implementation typically makes use of the context and value objects and the following additional interfaces:

• context::aggregate_context
• context::result
• context::user_data

The database::create_module can be used to create Virtual Table interfaces. More information can be found in Implementing Virtual Tables.

Shared libraries or DLLs can be used as loadable extensions to SQLite.

Other Interfaces

This article only mentions the most important and most commonly used ThinSQLite++ interfaces. The ThinSQLite++ library includes many other APIs implementing useful features that are not described here. Refer to the list of topics, content of the thinsqlitepp namespace or the list of classes for complete and authoritative information about all ThinSQLite++ interfaces.