sqlpp23

sqlpp23 is a C++ library that allows you to write SQL queries in C++ using a natural, type-safe syntax. It leverages C++ templates to provide compile-time validation of your SQL statements, catching errors like typos, type mismatches (comparing incompatible types), and missing tables before your code even runs.

Key features include:

• Strong Typing: Tables, columns, and functions are strongly typed, allowing the compiler to catch oversight errors.
• Type-Safe Results: Query results can be iterated as ranges, and rows contain strongly typed, aptly named data members for safe data access.
• IDE Support: The design enables effective code completion in modern IDEs.
• Natural Syntax: SQL constructs are mapped to C++ code to feel intuitive to SQL users.

When deciding how to handle concurrent data access, consider the following comparison between Optimistic Concurrency Control (OCC) and Pessimistic Concurrency Control (PCC):

Key Takeaways:

• Use OCC when contention is low. It simplifies architecture by removing the need for manual locks and avoiding deadlocks.
• Use PCC when contention is high, as OCC performance degrades significantly under high conflict rates.

When multiple mapping mechanisms are used, sqlpp23-ddl2cpp applies them in the following order of precedence (from lowest to highest):

1. sqlpp23 default mapping
2. CSV: SQL type $\rightarrow$ C++ type
3. CSV: column name $\rightarrow$ C++ type
4. CSV: table.column name $\rightarrow$ C++ type
5. CSV: schema.table.column $\rightarrow$ C++ type
6. Comment annotations (of any kind)

Note: Comment annotations must not contradict each other.

The MySQL connector has the following specific limitations:

1. No full_outer_join: Attempting to use .full_outer_join() will result in a compile-time static_assert with the message "MySQL: No support for full outer join".
2. No Boolean CAST: MySQL does not support casting to or from sqlpp::boolean.

CTEs are created using sqlpp::cte with a name (via sqlpp::alias) and defined using the .as() operator, which takes a statement (like a select) as its argument.

Non-recursive CTEs

const auto x = sqlpp::cte(sqlpp::alias::x)
               .as(select(foo.id).from(foo));

Recursive CTEs

Recursive CTEs are built in two steps: first defining a base case, then unioning it with a recursive statement.

// 1. Construct the base
const auto x_base = sqlpp::cte(sqlpp::alias::x).as(select(sqlpp::value(0).as(sqlpp::alias::a)));

// 2. Union with the recursion statement
const auto x = x_base.union_all(select((x_base.a + 1).as(sqlpp::alias::a))
                                .from(x_base)
                                .where(x_base.a < 10));

To use a CTE in a query, use the with clause.

In sqlpp23, SQL NULL values are represented using std::optional. Specifically, NULL is mapped to std::nullopt.

When reading fields from a result row (via select) or assigning values (via insert or update), you should treat these fields as std::optional<T>.

Important Note on Comparisons: sqlpp23 preserves standard SQL behavior for NULL comparisons. Comparing any value to NULL (including NULL to itself) using standard equality/inequality operators does not return a boolean true or false; instead, it returns NULL.

NULL != NULL    -> NULL
NULL = NULL     -> NULL

While sqlpp23-ddl2cpp has native mappings for standard SQL types, you can specify custom mappings to map SQL types or specific columns to custom C++ types.

Available built-in sqlpp types for mapping include:

• ::sqlpp::blob (Binary Large Object)
• ::sqlpp::boolean (Boolean)
• ::sqlpp::date (Date)
• ::sqlpp::floating_point (Floating-point)
• ::sqlpp::integral (Integer)
• ::sqlpp::serial (Auto-incrementing integer, non-nullable)
• ::sqlpp::text (Text)
• ::sqlpp::time (Time of day)
• ::sqlpp::timestamp (Timestamp)

You can also map to your own user-defined C++ types.

// Example of target types
::sqlpp::integral
::sqlpp::text
::some::custom_type

sqlpp23 does not provide specific thread-safety guarantees and aims to have no influence on thread safety itself. The actual thread safety of your application depends on the underlying database client library being used.

To avoid crashes, lock-ups, or SQL errors, avoid these two patterns:

1. Cross-thread connection usage: Creating a connection in one thread and then attempting to use it in a different thread.
2. Simultaneous multi-thread usage: Attempting to use the same connection instance in multiple threads at the same time.

Note on specific drivers: For drivers like MySQL, PostgreSQL, and SQLite3, it is often safe to create a connection in one thread and move it to another, but using a single connection simultaneously across multiple threads is generally unsafe.