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 (fnc12@me.com) using e-mail for details. If you'd like to develop sqlite_orm and earn money as a developer please contact owner (fnc12@me.com) 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

• https://github.com/fnc12/sqlite_orm/issues/625
• https://github.com/fnc12/sqlite_orm/issues/638
• https://github.com/fnc12/sqlite_orm/issues/654 (thanks to @denzor200)
• https://github.com/fnc12/sqlite_orm/issues/663 (thanks to @denzor200)
• https://github.com/fnc12/sqlite_orm/issues/687
• https://github.com/fnc12/sqlite_orm/issues/699
• https://github.com/fnc12/sqlite_orm/issues/716 (thanks to @Ashoat)
• https://github.com/fnc12/sqlite_orm/issues/723
• https://github.com/fnc12/sqlite_orm/issues/730
• https://github.com/fnc12/sqlite_orm/issues/632
• https://github.com/fnc12/sqlite_orm/issues/765
• https://github.com/fnc12/sqlite_orm/issues/784