Skip to content

sqlite3.md

Latest commit

 

History

History
126 lines (88 loc) · 3.34 KB

sqlite3.md

File metadata and controls

126 lines (88 loc) · 3.34 KB

< Connectors

Sqlite3 and SQLCipher connector

Creating a connection

// Create a connection configuration.
auto config = std::make_shared<sqlpp::sqlite3::connection_config>();
config->path_to_database = ":memory:";
config->flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;

// Create a connection
sqlpp::sqlite3::connection db;
db.connect_using(config); // This can throw an exception.

See also the logging documentation.

insert_or_*

The sqlite3 connector offers

  • insert_or_replace
  • insert_or_ignore

These can be used like this:

auto i = db(sqlpp::sqlite3::insert_or_replace().into(tab).set(
    tab.textNnD = "test", dynamic(true, tab.boolN = true)));

returning

insert_into, update, and delete_from support the returning clause to return one or more columns from affected rows, for instance:

for (const auto& row :
     db(sqlpp::sqlite3::update(foo)
            .set(foo.intN = 0, foo.doubleN = std::nullopt)
            .returning(foo.textNnD, dynamic(maybe, foo.intN)))) {
  // use row.textNnD
  // use row.intN
}

on_conflict ... do_nothing

The connector supports ON CONFLICT ... DO NOTHING with zero or more conflict targets, e.g.

// No conflict targets
sql::insert_into(foo).default_values().on_conflict().do_nothing());

// One or maybe two conflict targets
db(sql::insert_into(foo)
       .default_values()
       .on_conflict(foo.id)
       .do_nothing());

Note

sqlpp23 does not understand constraints. It has no way of verifying whether the conflict targets are valid.

Note

I have not figured out a valid scenario with more than one conflict target for sqlite3. Let me know if you have one.

on_conflict ... do_update ... [where ...]

The connector supports ON CONFLICT ... DO UPDATE ... WHERE with zero or more conflict targets, one or more update assignments and optional where clause, e.g.

for (const auto& row : db(sql::insert_into(foo)
                              .default_values()
                              .on_conflict(foo.id)
                              .do_update(foo.intN = 5,
          dynamic(maybe, foo.textNnD = "test bla"), foo.boolN = true)
                              .where(foo.intN == 2)
                              .returning(foo.textNnD))) {
  // use row.textNnD;
}

any

This is not supported and will fail to compile.

delete_from ... using

using is not supported and will fail to compile.

These are not supported and will fail to compile before version 3.39.0.

full_outer_join and right_outer_join

These are not supported and will fail to compile before version 3.39.0.

with

This is not supported and will fail to compile before version 3.8.0.

CAST

sqlite3 does not support

  • cast to sqlpp::date.
  • cast to sqlpp::timestamp.
  • cast to sqlpp::time.

Exceptions

In exceptional situations that yield an Sqlite3 error code, an sqlpp::sqlite3::exception will be thrown. The native error code can be obtained through the error_code function, e.g.

try {
  auto db = sqlpp::sqlite3::connection(config);
  db(select(sqlpp::verbatim("nonsense").as(sqlpp::alias::a)));
} catch (const sqlpp::sqlite3::exception& e) {
  println("Exception: {}, Sqlite3 error code: {}", e.what(), e.error_code());
}

< Connectors