sqlite_orm

❤️ SQLite ORM light header only library for modern C++

AGPL-3.0 License

Stars
2.2K
Committers
42
View Code on GitHub View on X
Ecosystems: C++, SQLite

Bot releases are hidden (Show)

sqlite_orm - v1.8.2 Latest Release

Published by fnc12 over 1 year ago 

make_index<User>("idx", json_extract<bool>(&User::name, "$.field"))

means

CREATE INDEX IF NOT EXISTS \"idx\" ON \"users\" (JSON_EXTRACT(\"name\", '$.field'))

Dynamic set is the same things as static set function (it is not a container, it is function which is used in update_all expressions) but it has variable amount of arguments which can be changed at runtime. dynamic_set is a similar thing like dynamic_order_by.

auto set = dynamic_set(storage);
expression.push_back(assign(&User::id, 5));
if (...) {
    expression.push_back(assign(&User::name, nameVariable));
}
storage.update_all(set, where(...));

dynamic_set can be used just like set (the static one) but please beware that get functions will work incorrectly with it cause get needs expressions fully known at compile time. Also dynamic_set has one minor limitation: dynamic_set prints literal arguments as ? instead of real values. It may be fixed in future releases.

select(c("first name") || " " || "last name");

means

SELECT 'first name' || ' ' || 'last name'

One can run

select(columns(asterisk<X>(), asterisk<Y>()), ...);

to get columns of several tables.

  • ⚙️ Removed dependency on RTTI
  • ⚙️ Compilation time improvement https://github.com/fnc12/sqlite_orm/pull/1161
  • ⚙️ Corrected library dependencies on Catch2 and sqlite3 in CMake, which improves the process of finding or building them
  • ⚙️ Fixed a bunch of warnings (unqualified call to std::move() and std::forward(), unused parameters)
  • ⚙️ Fixed sync'ing the eponymous virtual table dbstat
  • ⚙️ Fixed a bunch of errors in C++20 mode in environments that lack C++20 library features
  • ⚙️ Fixed serialization of EXISTS clause, which must be always enclosed in parentheses
sqlite_orm - v1.8.1

Published by fnc12 over 1 year ago

In order to https://github.com/microsoft/vcpkg/pull/28988 vcpkg's sqlite-orm port, a few things needed to be addressed:

  • pinned appveyor test environment to vcpkg 2023.01.09
  • made type_is_nullable<> a specializable struct again
  • examples are now fully linkable again: chrono_binding.cpp, nullable_enum_binding.cpp, pointer_passing_interface.cpp (those examples - wrongly commented out the main() function)
  • updated unit test framework to Catch2 v3 (https://github.com/fnc12/sqlite_orm/issues/1114)
  • updated clang-format lint action on github
  • removed dangling submodule reference (https://github.com/fnc12/sqlite_orm/issues/1107)
  • made all examples compilable with Visual C++ 2015 Update 3

Update (2023-01-15):
Currently unit tests fail with Visual C++ 2015 Update 3 as the minimal C++14 base compiler, as Catch2 v3 now requires a fully compliant C++14 compiler. Let's see what the decision is on the [feature request] (https://github.com/catchorg/Catch2/issues/2624) I made.

Update (2023-01-24):
Now that Catch2 v3 requires a conforming C++14 compiler, legacy compilers like Visual C++ 2015 Update 3 can't be used anymore for compiling the unit tests. The library itself and its examples are still usable with legacy compilers.

sqlite_orm - v1.8

Published by fnc12 almost 2 years ago

Triggers have to be specified inside make_storage call just like tables and indexes:

struct Lead {
    int id = 0;
    std::string firstName;
    std::string lastName;
    std::string email;
    std::string phone;
};

struct LeadLog {
    int id = 0;
    int oldId = 0;
    int newId = 0;
    std::string oldPhone;
    std::string newPhone;
    std::string oldEmail;
    std::string newEmail;
    std::string userAction;
    std::string createdAt;
};

    auto storage = make_storage("",

                                //  CREATE TRIGGER validate_email_before_insert_leads
                                //     BEFORE INSERT ON leads
                                //  BEGIN
                                //     SELECT
                                //        CASE
                                //      WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
                                //           RAISE (ABORT,'Invalid email address')
                                //         END;
                                //  END;
                                make_trigger("validate_email_before_insert_leads",
                                             before()
                                                 .insert()
                                                 .on<Lead>()
                                                 .begin(select(case_<int>()
                                                                   .when(not like(new_(&Lead::email), "%_@__%.__%"),
                                                                         then(raise_abort("Invalid email address")))
                                                                   .end()))
                                                 .end()),

                                //  CREATE TRIGGER log_contact_after_update
                                //     AFTER UPDATE ON leads
                                //     WHEN old.phone <> new.phone
                                //          OR old.email <> new.email
                                //  BEGIN
                                //      INSERT INTO lead_logs (
                                //          old_id,
                                //          new_id,
                                //          old_phone,
                                //          new_phone,
                                //          old_email,
                                //          new_email,
                                //          user_action,
                                //          created_at
                                //      )
                                //  VALUES
                                //      (
                                //          old.id,
                                //          new.id,
                                //          old.phone,
                                //          new.phone,
                                //          old.email,
                                //          new.email,
                                //          'UPDATE',
                                //          DATETIME('NOW')
                                //      ) ;
                                //  END;
                                make_trigger("log_contact_after_update",
                                             after()
                                                 .update()
                                                 .on<Lead>()
                                                 .when(is_not_equal(old(&Lead::phone), new_(&Lead::phone)) and
                                                       is_not_equal(old(&Lead::email), new_(&Lead::email)))
                                                 .begin(insert(into<LeadLog>(),
                                                               columns(&LeadLog::oldId,
                                                                       &LeadLog::newId,
                                                                       &LeadLog::oldPhone,
                                                                       &LeadLog::newPhone,
                                                                       &LeadLog::oldEmail,
                                                                       &LeadLog::newEmail,
                                                                       &LeadLog::userAction,
                                                                       &LeadLog::createdAt),
                                                               values(std::make_tuple(old(&Lead::id),
                                                                                      new_(&Lead::id),
                                                                                      old(&Lead::phone),
                                                                                      new_(&Lead::phone),
                                                                                      old(&Lead::email),
                                                                                      new_(&Lead::email),
                                                                                      "UPDATE",
                                                                                      datetime("NOW")))))
                                                 .end()),

                                //  CREATE TABLE leads (
                                //      id integer PRIMARY KEY,
                                //      first_name text NOT NULL,
                                //      last_name text NOT NULL,
                                //      email text NOT NULL,
                                //      phone text NOT NULL
                                //  );
                                make_table("leads",
                                           make_column("id", &Lead::id, primary_key()),
                                           make_column("first_name", &Lead::firstName),
                                           make_column("last_name", &Lead::lastName),
                                           make_column("email", &Lead::email),
                                           make_column("phone", &Lead::phone)),

                                //  CREATE TABLE lead_logs (
                                //      id INTEGER PRIMARY KEY,
                                //      old_id int,
                                //      new_id int,
                                //      old_phone text,
                                //      new_phone text,
                                //      old_email text,
                                //      new_email text,
                                //      user_action text,
                                //      created_at text
                                //  );
                                make_table("lead_logs",
                                           make_column("id", &LeadLog::id, primary_key()),
                                           make_column("old_id", &LeadLog::oldId),
                                           make_column("new_id", &LeadLog::newId),
                                           make_column("old_phone", &LeadLog::oldPhone),
                                           make_column("new_phone", &LeadLog::newPhone),
                                           make_column("old_email", &LeadLog::oldEmail),
                                           make_column("new_email", &LeadLog::newEmail),
                                           make_column("user_action", &LeadLog::userAction),
                                           make_column("created_at", &LeadLog::createdAt)));

Triggers are being created during sync_schema call if they do not exist.

For more information please check out an example file at examples/triggers.cpp.

More info about triggers at sqlite.org.

struct Product {
     int id = 0;
     std::string name;
     int quantity = 0;
     float price = 0;
     float totalValue = 0;
 };
 auto storage = make_storage({},
                             make_table("products",
                                        make_column("id", &Product::id, primary_key()),
                                        make_column("name", &Product::name),
                                        make_column("quantity", &Product::quantity),
                                        make_column("price", &Product::price),
                                        make_column("total_value",
                                                    &Product::totalValue,
                                                    //  GENERATED ALWAYS AS (price * quantity)
                                                    generated_always_as(&Product::price * c(&Product::quantity)))));

More info about generated columns on sqlite.org.

Leverages the convenient way of communicating non-SQL values between subcomponents or between an extension and the application.

Pointer Passing is superior to transforming a C-language pointer into a BLOB or a 64-bit integer, and allows for writing ad-hoc domain-specific extensions from within an application.

For more information please check the SQLite documentation, as well as the example at examples/pointer_passing_interface.cpp.

Example:

auto rows = storage.select(object<User>());
// decltype(rows) is std::vector<User>, where the User objects are constructed from columns in implicitly stored order

auto rows = storage.select(object<User>(true));
// decltype(rows) is std::vector<User>, where the User objects are constructed from columns in declared make_table order

avg(&User::id) -> AVG(users.id)
avg(&User::id).filter(where(length(&User::name) > 5)) -> AVG(users.id) FILTER (WHERE LENGTH(users.name) > 5)

make_index("idx_contacts_name", &Contract::firstName, &Contract::lastName, where(length(&Contract::firstName) > 2))

will produce

CREATE INDEX idx_contacts_name ON contacts (first_name, last_name) WHERE LENGTH(first_name) > 2

Example:

auto statement = storage.prepare(select(columns(&User::id, &User::name)));
auto idColumnName = statement.column_name(0);
auto nameColumnName = statement.column_name(1);
//  decltype(idColumnName) and decltype(nameColumnName) is `std::string_view`

This API is available only with C++17 and higher

SELECT * FROM dbstat; can be called with

auto storage =
     make_storage("dbstat.sqlite",
                  make_table("users", make_column("id", &User::id, primary_key()), make_column("name", &User::name)),
                  make_dbstat_table());
storage.sync_schema();

auto dbstatRows = storage.get_all<dbstat>();

dbstat struct looks as follows:

struct dbstat {
    std::string name;
    std::string path;
    int pageno = 0;
    std::string pagetype;
    int ncell = 0;
    int payload = 0;
    int unused = 0;
    int mx_payload = 0;
    int pgoffset = 0;
    int pgsize = 0;
};

It is available if SQLITE_ENABLE_DBSTAT_VTAB macro is defined.

More info on sqlite.org

Example:

//  SELECT name, instr(abilities, 'o') i
//  FROM marvel
//  WHERE i > 0
//  ORDER BY i
auto rows = storage.select(columns(&MarvelHero::name, as<colalias_i>(instr(&MarvelHero::abilities, "o"))),
                           where(greater_than(get<colalias_i>(), 0)),
                           order_by(get<colalias_i>()));

For more info please check new example in examples/column_aliases.cpp.

Example:

//  SELECT name, instr(abilities, 'o')
//  FROM marvel
//  ORDER BY 2
auto rows = storage.select(columns(&MarvelHero::name, as<colalias_i>(instr(&MarvelHero::abilities, "o"))),
                           order_by(2));

For more info please check new example in examples/column_aliases.cpp.

  • primary_key().asc().autoincrement() makes PRIMARY KEY ASC AUTOINCREMENT
  • primary_key().on_conflict_rollback() makes PRIMARY KEY ON CONFLICT ROLLBACK
  • primary_key().desc().on_conflict_abort() makes PRIMARY KEY DESC ON CONFLICT ABORT

It means that all available SQLite PRIMARY KEY options now are available in sqlite_orm!

Now once can call BEGIN DEFERRED TRANSACTION, BEGIN IMMEDIATE TRANSACTION and BEGIN EXCLUSIVE TRANSACTION queries with this:

storage.begin_deferred_transaction();
storage.begin_immediate_transaction();
storage.begin_exclusive_transaction();

auto expression1 = storage.select(asterisk<User>()); // SELECT * FROM users
// will return the same as
using als_u = alias_u<User>;
auto expression2 = storage.select(asterisk<als_u>()); // SELECT * FROM users u

using_(&User::id) will produce the same as using_(column<User>(&User::id)).
Why people may need this? To use mapped structs with inheritance.

storage.pragma.application_id(3);  // PRAGMA application_id = 3, function accepts int
auto value = storage.pragma.application_id();  // PRAGMA application_id, decltype(value) is int
  • ⭐ asterisk now supports table aliases as well
  • ⭐ storage.dump now can accept prepared expressions to serialize them (thanks to @trueqbit)
  • ⭐ added new example file chrono_binding.cpp
  • ⭐ added scalar versions of MAX and MIN functions (yes we missed them; thanks to @trueqbit)
  • ⭐ added NULLIF and IFNULL core functions support (thanks to @trueqbit)
  • ⭐ now you can select std::optional<T>() to return NULL as T in raw select
  • ⭐ also you can select nullptr to return NULL as std::nullptr_t in raw select same way
  • ⭐ update_all now allows updating more than one table at once. If no tables specified orm_error_code::no_tables_specified is thrown
  • ⭐ transaction_guard_t finally has move constructor. Move it carefully
  • ⭐ prepared statement can be moved as well. Let's make a moving party!
  • ⭐ std::string_view can be used in queries as read only data not result type (available in C++17 and higher)
  • ⚙️ sync_schema function call supports DROP COLUMN instead of dropping and recreating the whole table in SQLite 3.35 and higher
  • ⚙️ Huge inner template code refactoring. Certainly reduces the amount of resources needed by the compiler (thanks to @trueqbit)
  • ⚙️ having function marked as deprecated. Please use group_by(...).having(...) instead. Simple having function will be removed in v1.9 release
  • ⚙️ Improved unit tests run speed (thanks to @trueqbit)
  • ⚙️ Refactored unit tests to make them more brilliant (thanks to @trueqbit)
  • ⚙️ Properly quote identifiers (thanks to @trueqbit)
  • ⚙️ Refactored complex inner serializing functions and identifiers quoting (thanks to @trueqbit)
  • ⚙️ Optimized passing arguments to user defined functions: removed excess arguments copying
  • ⚙️ Reduced amount of warnings (thanks to @trueqbit)
  • ⚙️ Added new example with blob binding in case you need it examples/blob_binding.cpp
  • ⚙️ Improved multi-threaded use of persistent connections (https://github.com/fnc12/sqlite_orm/pull/1054)
  • ⚙️ Identified and encapsulated workarounds for legacy compilers.
    Fully works with Visual C++ 2015 Update 3.
sqlite_orm - v1.7.1

Published by fnc12 almost 3 years ago

  • Fixed compilation error is_upsert_clause - is not a member of sqlite_orm::internal
sqlite_orm - v1.7

Published by fnc12 almost 3 years ago

sqlite_orm is being developed more than 5 years and I am (@fnc12) very happy that people love it. But the project now becomes huger and more difficult to maintain. Dropping support is not an option so the best way of going on with active updates is switching to part-time/full-time job mode for me with this library. What does it mean for you? If you use this lib within another open source project then nothing changed cause GNU AGPL license allows using it anywhere if the source of 'anywhere' is open and public available. If you use this lib within a closed source project and you want to update sqlite_orm version used in your project to v1.7 or higher then you need to pay 50$ to obtain a MIT license of sqlite_orm for your project. Payments can be accepted using PayPal. Add your email and project name to payment comment. If you have PRs merged into this lib before then you can have a discount. Please contact lib owner ([email protected]) using e-mail for details. If you'd like to develop sqlite_orm and earn money as a developer please contact owner ([email protected]) using e-mail for details.

Note: 50$ is not a huge amount. Actually it is two visits for a dinner at cafe. Consider it as a meeting with me at cafe where you pay for a dinner.

struct SignFunction {

    double operator()(double arg) const {
        if(arg > 0) {
            return 1;
        } else if(arg < 0) {
            return -1;
        } else {
            return 0;
        }
    }

    static const char *name() {
        return "SIGN";
    }
};
storage.create_scalar_function<SignFunction>();
//  SELECT SIGN(5)
auto rows = storage.select(func<SignFunction>(5));

More info can be found at wiki page.

Sometimes existing storage.insert<T> and storage.replace<T> functions are not enough so now you also have a function to achieve every case during INSERT/REPLACE call. E.g. how to call INSERT INTO ... SELECT?

//  INSERT INTO artists_backup 
//  SELECT ArtistId, Name
//  FROM artists;
storage.insert(into<ArtistBackup>(),
               select(columns(&Artist::id, &Artist::name)));

or call INSERT OR ABORT:

//  INSERT OR ABORT
//  INTO users(id, name)
//  VALUES(10, 'Mabel')
storage.insert(or_abort(), 
               into<User>(),
               columns(&User::id, &User::name),
               values(std::tuple(10, "Mabel")))

More info can be found at wiki page

SQLite 3.35 added a lot of built in math functions. Now all these functions are also available within sqlite_orm. E.g. sin, cos, log. To use it make sure that your SQLite version is 3.35 or higher and have SQLITE_ENABLE_MATH_FUNCTIONS compilation flag. More info about all built in functions can be found at wiki

Why you may need this? In cases when you may get null as a result and want to obtain it as std::nullopt instead.

auto rows = storage.select(as_optional(&User::id));  // decltype(rows) is std::vector<std::optional<decltype(User::id)>>

More extensions - more power! JSON1 is a very useful extension which adds JSON API right into SQLite. Example:

auto rows = storage.select(json_object("a", 2, "c", 4));  // decltype(rows) is std::vector<std::string> and equal '{"a":2,"c":4}'

All JSON1 extensions functions are available except json_each and json_tree functions. Information about all JSON1 extension functions are available here.

This is an alternative way of using collations. Once user defined functions feature appeared the idea of the same API for collations was born. And here we go:

struct OtotoCollation {
    int operator()(int leftLength, const void* lhs, int rightLength, const void* rhs) const {
        if(leftLength == rightLength) {
            return ::strncmp((const char*)lhs, (const char*)rhs, leftLength);
        } else {
            return 1;
        }
    }

    static const char* name() {
        return "ototo";
    }
};

storage.create_collation<OtotoCollation>();

//  SELECT name
//  FROM items
//  WHERE name == 'Mercury' COLLATE 'ototo'
auto rows = storage.select(&Item::name, where(is_equal(&Item::name, "Mercury").collate<OtotoCollation>()));

Strong typed collations is a way of writing more clear code cause you need to write a name of your collations only once.

sqlite_orm defines tables set for FROM query section for you automatically. But sometimes you may need to specify FROM tables set explicitly. It can happen when you make a subselect:

int n = storage->count(&ItemData::id,
                       where(exists(select(asterisk<ScanResultData>(),
                                    where(is_equal(&ScanResultData::itemId, &ItemData::id))))));

will call

SELECT COUNT(item.id) 
FROM scan_result, item
WHERE EXISTS (SELECT * 
              FROM scan_result, item  
              WHERE scan_result.item = item.id)

and it may be not what you expect to be called (pay attention to the second line FROM scan_result, item). Why are there two tables in FROM table set instead of one? Because storage tries to define what tables are mentioned inside query arguments and it does well except some corner cases like this one. So if you want to call a query like this but with only one table inside high level FROM sections then you need to write it like this:

int n = storage->count(&ItemData::id,
                       from<ItemData>(),
                       where(exists(select(asterisk<ScanResultData>(),
                                    where(is_equal(&ScanResultData::itemId, &ItemData::id))))));

Function call from<ItemData>() will be serialized to FROM items. If you don't specify any from<T>() call then FROM section table list is deduced automatically as before.

Sometimes you may want to use insert_range and replace_range API with containers with not strict objects but something else: pointers, optionals, whatever. In that cases you need to use the third argument of insert_range and replace_range - a transformer caller object:

// strict objects
std::vector<User> users;
// fulfill users vector
storage.insert_range(users.begin(), users.end());

// not strict objects
std::vector<std::unique_ptr<User>> userPointers;
// fulfill userPointers vector
storage.insert_range(users.begin(), users.end(), [](const std::unique_ptr<User> &pointer) {
                                                     return *pointer;
                                                 });

auto rows = storage.pragma.integrity_check();
// or
auto rows = storage.pragma.integrity_check(5);
// or
auto rows = storage.pragma.integrity_check("users");

decltype(rows) is std::vector<std::string>. More info here.

  • UNICODE
  • TYPEOF
  • TOTAL_CHANGES
  • LAST_INSERT_ROWID
  • IFNULL
  • ⭐ added static IN feature (fixed bugs https://github.com/fnc12/sqlite_orm/issues/675 and https://github.com/fnc12/sqlite_orm/issues/512)
  • ⭐ added storage.column_name API
  • ⚙️ added noexcept getters and setter modifiers. Available with C++17 and higher
  • ⚙️ added std::nullopt support. It works just like nullptr works and available with C++17 or higher
  • ⚙️ binary operators can be used as row results
  • ⚙️ added some thread safety improvements https://github.com/fnc12/sqlite_orm/pull/736
  • ⚙️ added static assert in case if you try to call storage.insert with a non-insertable table (https://github.com/fnc12/sqlite_orm/pull/644 thanks to @denzor200)
  • ⚙️ improved serialization for some AST nodes: std::string was replaced with std::string_view for C++17. It reduces amount of heap allocations during query serialization
  • ⚙️ file tests/CMakeLists.txt now has a pretty look (thanks to @undisputed-seraphim)
  • ⚙️ fixed GCC warnings (thanks to @denzor200)
  • ⚙️ improved code formatting
  • ⚙️ iterator_t now is compatible with std::input_iterator concept (https://github.com/fnc12/sqlite_orm/pull/685 thanks to @andrei-datcu)
  • ⚙️ field_printer now has an additional template argument for SFINAE tricks (thanks to @Overlordff)
  • ⚙️ improved bool transaction(const std::function<bool()>& f) call - now it uses guard inside to make calls safer (thanks to @denzor200)

Special thanks to:
@denzor200
@mishal23
@undisputed-seraphim
@Ashoat
@andrei-datcu

sqlite_orm - v1.6

Published by fnc12 about 4 years ago 

auto storage = make_storage("database.sqlite",
                            make_table("contacts",
                                       make_column("contact_id", &Contact::id, primary_key()),
                                       make_column("phone", &Contact::phone),
                                       check(length(&Contact::phone) >= 10)));

means

CREATE TABLE contacts (
    contact_id INTEGER NOT NULL PRIMARY KEY,
    phone TEXT NOT NULL,
    CHECK(LENGTH(phone >= 10))
)

or

auto storage = make_storage("database.sqlite",
                            make_table("BOOK",
                                       make_column("Book_id", &Book::id, primary_key()),
                                       make_column("Book_name", &Book::name),
                                       make_column("Pub_name", &Book::pubName),
                                       make_column("PRICE", &Book::price, check(c(&Book::price) > 0))));

means

CREATE TABLE BOOK(
    Book_id INTEGER NOT NULL PRIMARY KEY,
    Book_name TEXT NOT NULL,
    Pub_name TEXT NOT NULL,
    PRICE NUMERIC NOT NULL CHECK(PRICE > 0)
)

storage.select(bitwise_or(60, 13));  // SELECT 60 | 13
storage.select(bitwise_and(60, 13));  // SELECT 60 & 13
storage.select(bitwise_shift_left(60, 2));  // SELECT 60 << 2
storage.select(bitwise_shift_right(60, 2));  // SELECT 60 >> 2
storage.select(bitwise_not(60));  // SELECT ~60

auto storage = make_storage({}, 
                            make_index("name_index", indexed_column(&User::name).collate("binary").asc()), 
                            make_table("users", 
                                       make_column("id", &User::id), 
                                       make_column("name", &User::name));

will translate to

CREATE TABLE users (
    id INTEGER NOT NULL,
    name TEXT NOT NULL);
CREATE INDEX name_index ON users (name COLLATE binary ASC);
  • HEX
  • QUOTE
  • RANDOMBLOB
  • INSTR
  • REPLACE
  • ROUND
  • SOUNDEX
  • TIME
  • STRFTIME
auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5 and like(&User::name, "T%"))));
auto str = storage.dump(statement);  // str is something like 'SELECT \"users\".\"name\", \"users\".\"id\" FROM 'users'  WHERE ( ((\"id\" % 2) = 0)) ORDER BY \"users\".\"name\" '

The difference between statement.sql is that dump function prints real values instead of question marks. Also it does not call any sqlite3 functions - it calls sqlite_orm serializer instead.

Example:

auto statement = storage.prepare(get_all<User, std::list<User>>());

make_table("shapes",
           make_column("shape_id", &Shape::id, primary_key()),
           make_column("background_color", &Shape::backgroundColor),
           make_column("foreground_color", &Shape::foregroundColor),
           sqlite_orm::unique(&Shape::backgroundColor, &Shape::foregroundColor))

Example:

* storage.rename_table<User>("new_table_name") -> change name in table information not database
* storage.rename_table("old_name", "new_name");  -> rename table using SQL query
* storage.tablename<User>(); -> get table name as `std::string` from table info not database

Example:

//    DELETE FROM devices
//    WHERE (serial_number, device_id) IN (VALUES ('abc', '123'), ('def', '456'))
storage.remove_all<Device>(where(in(std::make_tuple(&Device::serialNumber, &Device::deviceId),
                                         values(std::make_tuple("abc", "123"), std::make_tuple("def", "456")))));
//  or
storage.remove_all<Device>(
         where(in(std::make_tuple(&Device::serialNumber, &Device::deviceId),
                  values(std::vector<std::tuple<std::string, std::string>>{std::make_tuple("abc", "123"),
                                                                           std::make_tuple("def", "456")}))));

These queries are the same. The difference between them is that the first is static and the second is dynamic (std::vector based). It may be useful if you change bound values using get API.

  • ⚙️ sync_schema behavior changes: now types are ignored cause SQLite ignores them too. It allows using custom types.
  • ⚙️ Fixed all clang and GCC warnings.
  • 🐞 Fixed bug: unable to use reserved keywords as foreign key columns
  • 🐞 Fixed bug: compilation error during using any core function within CASE operator
  • 🐞 Fixed bug in sync_schema: https://github.com/fnc12/sqlite_orm/issues/521
  • 🐞 Fixed backup bug: https://github.com/fnc12/sqlite_orm/issues/540

Special thanks to:
@undisputed-seraphim
@Leon0402
@air-h-128k-il

sqlite_orm - v1.5

Published by fnc12 about 4 years ago 

//  SELECT doctor_id
//  FROM visits
//  WHERE LENGTH(patient_name) > 8
auto selectStatement = storage.prepare(select(&Visit::doctor_id, 
                                       where(length(&Visit::patient_name) > 8)));
cout << "selectStatement = " << selectStatement.sql() << endl;
auto rows = storage.execute(selectStatement);

get<0>(selectStatement) = 10;    // change LENGTH(patient_name) > 8 to LENGTH(patient_name) > 10
auto rows2 = storage.execute(selectStatement);

More info can be found in the example

//  SELECT id, first_name, last_name
//  FROM users
//  WHERE first_name GLOB 'C*'
auto users = storage.get_all<User>(where(glob(&User::firstName, "C*")));

or

//  SELECT id
//  FROM users
//  WHERE last_name GLOB '*a*' OR first_name LIKE 'J%'
auto rows = storage.select(&User::id, where(glob(lower(&User::lastName), "*a*") 
                           or like(&User::firstName, "J%"));

More info about GLOB

auto userMaybe = storage.get_optional<User>(14);  // decltype(userMaybe) is std::optional<User>
if(userMaybe.has_value()){
    cout << "user = " << storage.dump(userMaybe.value()) << endl;
}else{
    cout << "user with id 14 doesn't exist" << endl;
}

std::optional better suites for returning nullable data than std::unique_ptr so it is highly recommended to use storage_t::get_optional instead of storage_t::get_pointer to avoid extra heap allocations. Hint: available only with C++17 or higher. One can set C++ standard version with -std=c++17 compiler option with clang and gcc or in target properties in Visual Studio and Xcode. For different build systems please check out related documentation.

More info about std::optional on cppreference

storage_t:: get_all_pointer can be useful if you want to obtain your objects allocated as unique pointers.

auto users = storage.get_all_pointer<User>();  // decltype(users) is std::vector<std::unique_ptr<User>>

or

auto statement = storage.prepare(get_all_pointer<User>(where(c(&User::id) < 100));
auto users = storage.execute(statement);  // decltype(users) is std::vector<std::unique_ptr<User>>

DEFAULT constraint can accept not only literals but functions like DATETIME. sqlite_orm now also has support for it.

auto storage = make_storage("myDatabase.sqlite",
        make_table("induction",
                   make_column("timestamp", &Induction::time, default_value(datetime("now", "localtime")))));

means

CREATE TABLE induction (
    timestamp INTEGER NOT NULL DEFAULT DATETIME('now', 'localtime')
)

Once you try to create a query with more than one WHERE options you get a static assert telling you "a single query cannot contain > 1 WHERE blocks". Same check works for:

  • WHERE
  • GROUP BY
  • ORDER BY
  • LIMIT

Before you'd know that you constructed incorrect query only in runtime. Now this check happens in compile time!

Use storage_t::filename() function to retrieve filename passed in storage during construction.
Example:

const auto &filename = storage.filename();  // decltype(filename) is std::string

All library code is formatted with clang-format using config located in project root. From now when you create a pull request please don't forget to format it using clang-format tool. If code is not formatted then your pull request will be declined cause one of CI check will be failed.
More information about clang-format can be found here.

Now one can write these two constraints in either order: the correct one and the legacy one.

  • 🐞 fixed compilation errors with older versions of sqlite3

  • 🐞 #384

  • 🐞 #369

  • 🐞 #400

sqlite_orm - v1.4

Published by fnc12 about 5 years ago 

//  SELECT ID, NAME, MARKS,
//      CASE
//      WHEN MARKS >=80 THEN 'A+'
//      WHEN MARKS >=70 THEN 'A'
//      WHEN MARKS >=60 THEN 'B'
//      WHEN MARKS >=50 THEN 'C'
//      ELSE 'Sorry!! Failed'
//      END
//      FROM STUDENT;
auto rows = storage.select(columns(&Student::id,
                                   &Student::name,
                                   &Student::marks,
                                   case_<std::string>()
                                   .when(greater_or_equal(&Student::marks, 80), then("A+"))
                                   .when(greater_or_equal(&Student::marks, 70), then("A"))
                                   .when(greater_or_equal(&Student::marks, 60), then("B"))
                                   .when(greater_or_equal(&Student::marks, 50), then("C"))
                                   .else_("Sorry!! Failed")
                                   .end()));
//    decltype(rows) is std::vector<std::tuple<decltype(Student::id), decltype(Student::name), decltype(Student::marks), std::string>>

or

//    SELECT CASE country WHEN 'USA' THEN 'Dosmetic' ELSE 'Foreign' END
//    FROM users
auto rows = storage.select(columns(case_<std::string>(&User::country)
                                           .when("USA", then("Dosmetic"))
                                           .else_("Foreign")
                                           .end()),
                                   multi_order_by(order_by(&User::lastName), order_by(&User::firstName)));
//    decltype(rows) is std::vector<std::string>

//  SELECT coalesce(10,20);
cout << "coalesce(10,20) = " << storage.select(coalesce<int>(10, 20)).front() << endl;

//  SELECT substr('SQLite substr', 8);
cout << "substr('SQLite substr', 8) = " << storage.select(substr("SQLite substr", 8)).front() << endl;

//  SELECT substr('SQLite substr', 1, 6);
cout << "substr('SQLite substr', 1, 6) = " << storage.select(substr("SQLite substr", 1, 6)).front() << endl;

//  SELECT zeroblob(5);
cout << "zeroblob(5) = " << storage.select(zeroblob(5)).front().size() << endl;

order_by and multi_order_by are strong typed so you cannot specify ORDER BY column type at runtime. dynamic_order_by solves this problem. dynamic_order_by is a multi_order_by that accepts order_by conditions at runtime. Example:

auto orderBy = dynamic_order_by(storage);
if(shouldOrderByNameAndId){
    orderBy.push_back(order_by(&User::name));
    orderBy.push_back(order_by(&User::id));
}else{
    orderBy.push_back(order_by(&User::id));
}
auto rows = storage.get_all<User>(where(...), orderBy);

Now LIKE can also be used as a core function to retrieve a result:

auto rows = storage.select(like(&User::name, "J%"));
//    decltype(rows) is std::vector<bool>

LIKE has a third argument and now it is available in sqlite_orm:

//    SELECT name LIKE 'J%' ESCAPE '_'
//    FROM users
auto rows = storage.select(like(&User::name, "J%").escape("_"));

or

//    SELECT LIKE(name, 'J%', '_')
//    FROM users
auto rows = storage.select(like(&User::name, "J%", "_"));
  • ⚙️ Added Catch2 unit tests framework into unit tests project
  • ⚙️ Added unit tests configurations for even more platforms and compilers (thanks to @Farwaykorse)
  • ⚙️ Added contributing doc
  • 🚀 Added nullptr binding to WHERE conditions
  • 🚀 Reduced binary size
  • 🚀 Added composite key support for storage_t::remove function
  • 🚀 Reduces memory consumption

Before once you get an exception thrown asking e.what() gave you a poor text like NOT NULL constraint failed. Now it is more detailed (thanks to sqlite3_errmsg function) like: NOT NULL constraint failed: users.age: constraint failed

  • Fixed GCC6 compilation bug
  • Fixed runtime error on ARM architecture
  • Fixed getter by value support for storage_t::replace and storage_t::update functions
  • Fixed bug with iterating over blob values
  • Fixed on_copy coping on storage_t copy
  • Fixed silencing binding failure - now exception is thrown
  • Fixed using std::unique_ptr in storage_t::update_all set arguments
  • Fixed incorrect (reverse) arguments order in GROUP BY
sqlite_orm - v1.3

Published by fnc12 about 5 years ago 

SELECT cust_code, cust_name, cust_city, grade
FROM customer
WHERE grade=2 AND EXISTS
    (SELECT COUNT(*)
    FROM customer
    WHERE grade=2
    GROUP BY grade
    HAVING COUNT(*)>2);

now can be called with this way:

auto rows = storage.select(columns(&Customer::code, &Customer::name, &Customer::city, &Customer::grade),
                                   where(is_equal(&Customer::grade, 2)
                                         and exists(select(count<Customer>(),
                                                           where(is_equal(&Customer::grade, 2)),
                                                           group_by(&Customer::grade),
                                                           having(greater_than(count(), 2))))));

All compound operators now are available:

SELECT dept_id
FROM dept_master
EXCEPT
SELECT dept_id
FROM emp_master

is just

auto rows = storage.select(except(select(&DeptMaster::deptId),
                                          select(&EmpMaster::deptId)));

and

SELECT dept_id
FROM dept_master
INTERSECT
SELECT dept_id
FROM emp_master

is just

auto rows = storage.select(intersect(select(&DeptMaster::deptId),
                                             select(&EmpMaster::deptId)));

  • ⭐ Column aliases

  • ⭐ SELECT * FROM table with syntax storage.select(asterisk<T>()) returns std::tuple of mapped members' types

  • ⭐ CAST(expression AS type) expression with cast<T>(expression) syntax

  • ⭐ added julianday function

  • 🚀 FOREIGN KEY now works with composite PRIMARY KEY

bool myFilterIsOn = getMyFilterValue();
auto values = storage.get_all<User>(where(!myFilterIsOn and like(&User::name, "Adele%")));
  • 🚀 improved performance - replaced std::shared_ptr with std::unique_ptr inside storage, view iterator and aggregate functions
  • ⚙️ added Windows CI with Appveyor (thanks to @soroshsabz)
  • 🐞 Bug fixes - fixed runtime error which can be faced during storage::iterate() call
  • ⚠️ Minor warning fixes
sqlite_orm - v1.2 Union

Published by fnc12 over 6 years ago

  • UNION
    Use union_/union_all functions with two subselects to query data with UNION/UNION ALL operators
  • Custom collations
    Now you can bind your function as a collating function used to compare data during selection
  • Setters/getters
    Added different getters/setters support. E.g. now one can easily map Protobuf structure as is
  • Easier development
    Library code is split into different header files for easily development. Also final code is still available as a single source for include
  • Explicit types
    Explicit columns/tables types. Now one can map subclasses with inherited fields and select them from database properly
  • Limits
    Added all SQLite limits API
  • Explicit INSERT
    Now you can specify columns to insert from provided object
  • Static unit tests
    Static unit tests are checked at compile time and have a bulk of static_assert functions.
  • Speed
    Decreased dump() and several other functions work time
sqlite_orm - v1.1

Published by fnc12 over 6 years ago

Features

  • VACUUM
    Use storage.vacuum() to call VACUUM query explicitly or use storage.pragma.auto_vacuum(...); to set PRAGMA auto_vacuum.
  • Arithmetic operators
    +, -, *, / and % are now available for using within expressions. Example: auto doubledAge = storage.select(c(&User::age) * 2); or auto doubledAge = storage.select(mul(c(&User::age), 2));. As you can see every operator has a function in case you like functions more than operators: c(&User::age) + 5 is add(&User:age, 5). Also sub, mul, div and mod functions are now available in sqlite_orm namespace.
  • Bug fixes
    Fixed compilation error in case sqlite_orm.h file is included in more than one place.
    Fixed incorrect query generation in remove call in case PRIMARY KEY is defined as a separate column.
  • Warning fixes
    Fixed three Visual Studio 2017 warnings (thanks to @ntkernelcom)
sqlite_orm - The very first

Published by fnc12 over 6 years ago

Finally sqlite_orm v1.0 is released. This is a first stable version. All future versions with the same major version number will have back-compatibility with this version. If you meet broken compatibility within the same major version please report a bug in the issues section.

Features

  • No raw string queries:
    forget about db << "SELECT " + idColumnName + " FROM " + myTableName + " WHERE " + idColumnName + " < 10 ORDER BY " + nameColumnName;. Just write storage.select(&Object::id, where(c(&Object::id) < 10), order_by(&Object::name)); instead
  • Intuitive syntax
    most of SQLite3 keywords are provided as functions in sqlite_orm: foreign_key, unique, autoincrement, default_value, collate, using_, on, cross_join, natural_join, left_join, join, left_outer_join, inner_join, offset, limit, is_null, is_not_null, in, where, order_by, group_by, between, like, date, datetime, char_, trim, ltrim, rtrim, changes, length, abs, lower, upper, avg, count, sum, max, min, total, group_concat, distinct, all, rowid, oid, _rowid_. Just imagine SQL syntax is provided in your IDE. (Hint: don't forget to add using namespace sqlite_orm)
  • Comfortable interface - one code line per single query
    yes, there are no service objects required to be declared to make a single query. You can write the most complicated queries within a single semicolon:
storage.select(columns(&Visit::mark, &Visit::visited_at, &Location::place),
               inner_join<Location>(on(is_equal(&Visit::location, &Location::id))),
               where(is_equal(&Visit::user, id) and
                     greater_than(&Visit::visited_at, fromDate) and
                     lesser_than(&Visit::visited_at, toDate) and
                     lesser_than(&Location::distance, toDistance)),
               order_by(&Visit::visited_at));
  • Built with modern C++14 features (no macros and external scripts)
    yes, some ORM libs require scripts/macros to make columns to members mapping work. But sqlite_orm just works as is
  • CRUD support
    declare a variable and insert it without any other unnecessary stuff. Next get it by a primary key, update it, remove it or replace it.
  • Pure select query support
    also you can just get a std::vector (or any other STL-compatible container) of any column with or without any desired where conditions. Or you can even select several column in a vector of tuples.
  • STL compatible
    it means two things: 1) select your objects or columns in any STL compatible container (std::list, QList or even nlohmann::json); 2) iterate your objects in C++11 for loop:
for(auto &user : storage.iterate<User>()) {
    cout << storage.dump(user) << endl;
}
  • Custom types binding support
    sqlite_orm understands implicitly what column type must be by member pointer type you provide. E.g. std::string member pointer maps to TEXT, int, long map to INTEGER, float, double map to REAL. But you can also use your custom types if you have it. You can even bind your enum to be mapped as string or int or whatever. Or even bind boost::optional<T> as nullable generic type.
  • BLOB support
    BLOB SQLite type maps to std::vector<char> or you can add binding to any other type.
  • FOREIGN KEY support
    you can use FOREIGN KEY with intuitive syntax: foreign_key(&Visit::location).references(&Location::id). And you don't need to call PRAGMA foreign_keys = 1 every time - storage class calls it for you on every database open if there is at least one foreign key exists.
  • Composite key support
    PRIMARY KEY with several columns also supported. Just write primary_key(&User::id, &User::firstName) and your composite key is ready to go.
  • JOIN support
    all kinds of JOIN supported by SQLite are also supported by the lib.
  • Transactions support
    transaction is one the most important performance improvement tool. There are three different ways to use transactions in the lib to make your code more flexible and stable.
  • Migrations functionality
    sometimes when you use some ORM libs you need to create your database with tables first. Someone performs it at runtime, someone creates all tables with a SQLite client and adds this file in the project assets. Forget about it. Just call sync_schema and storage will check all tables and columns and if there is something missing it will recreate/alter it. sync_schema guarantees that schema will be the same as you specified during make_storage call.
  • Powerful conditions
    don't be shy - use any combinations of conditions during selection/deleting.
  • INDEX support
    use indexes as is - just specify member pointer in make_index function.
  • Follows single responsibility principle
    this is a very important thing which many developers omit - your data model classes must know nothing about storage and other services. It is very useful if your software has a lot of modules and sometimes you change some of them.
  • Easy integration
    single header, no .cpp files. Use conan, cmake or just include it as is.
  • The only dependency
    5 seconds required to connect the lib to your project.
  • C++ standard code style
    no 'initcapped' C# like function names, no camel case in public function/classes names. Include it and use it just like it is one of the standard headers.
  • No undefined behaviour
    as you know some code in standard library can produce undefined behaviour (e.g. std::vector<int>()[5]). sqlite_orm creators do not like undefined behavior at all. So if something goes wrong be ready to catch std::system_error. By this std::system_error you can know whether error happened in SQLIte or in the lib by inspecting the error_category.

sqlite_orm.h SHA1 6e0b40c2b7122c02cb6d9efbade487689d933827

Package Rankings
Top 9.59% on Proxy.golang.org
Top 13.73% on Cocoapods.org
Badges
Extracted from project README
C++ SQLite GitHub Actions CMake Stack Overflow PayPal Twitter Patreon Build Status Build status Build Status Build status Video from conference
Related Projects
cxx

Configuration-free utility for building, testing and packaging executables written in C++. Can au...

19 Dec 2017 121
ormpp

modern C++ ORM, C++17, support mysql, postgresql,sqlite

04 Nov 2017 1,270
sqlite_modern_cpp

The C++14 wrapper around sqlite library

11 May 2014 893
ccORM

Compile time static reflection C++ ORM(mysql,pgsql,sqlite), the character recognition type system...

15 Jul 2021 5
cmdlime

Possibly the least verbose command line parsing library for C++

02 May 2021 85
sqlite

21 Dec 2022 4
sqlpp11

A type safe SQL template library for C++

13 Aug 2013 2,346
figcone

Read JSON, YAML, TOML, XML or INI configuration by declaring a struct

18 Apr 2022 100