« Changelog History


sqlite_orm v1.9.1 Release Notes

Release Date: 2025-02-03 // over 1 year ago

  • ⭐ drop_table_if_exists, drop_index_if_exists, drop_trigger_if_exists 

    storage.drop_table_if_exists("users");
storage.drop_index_if_exists("id\_index");
storage.drop_trigger_if_exists("update\_everything");

    The main difference from existing drop_X APIs is that existing APIs don't send IF EXISTS string part to SQLite statement. So if you have to call DROP ... IF EXISTS now you have your functions.

    ⭐ PRAGMA locking_mode 

    locking_mode value = storage.pragma.locking_mode();
storage.pragma.locking_mode(locking_mode::EXCLUSIVE);// orstorage.pragma.locking_mode(locking_mode::NORMAL);

    ⭐ PRAGMA max_page_count API (thanks to @akrisfx) 

    autovalue = storage.pragma.max_page_count();// PRAGMA max\_page\_count;value +=2;
storage.pragma.max_page_count(value);// PRAGMA max\_page\_count = value

    ⭐ Additional FOREIGN KEY API for inheritance

    If you have a superclass for mapped classes and you have foreign key which references inherited class field you can use new API foreign_key(&ReferencingClass::fieldA).references<Subclass>(&Subclass::fieldB)

    structPerson{intid;
        std::string name;intage;
    };// Define derived class StudentstructStudent:publicPerson{
        std::string school_name;Student(intid, std::string name,intage, std::string school_name) :
            Person{id,std::move(name), age}, school_name(std::move(school_name)) {}
    };// Define derived class TeacherstructTeacher:publicPerson{
        std::string subject;doublesalary;Teacher(intid, std::string name,intage, std::string subject,doublesalary) :
            Person{id,std::move(name), age}, subject(subject), salary(salary) {}
    };// Define Classroom class referencing Teacher and StudentstructClassroom{intid;intteacher_id;// Foreign key referencing Teacherintstudent_id;// Foreign key referencing Studentstd::string room_name;
    };autostorage =make\_storage("",// Define the Person table as a base, though it is not used directlymake_table<Person>("persons",make\_column("id", &Person::id, primary_key().autoincrement()),
                                        make_column("name", &Person::name),
                                        make_column("age", &Person::age)),// Define the Student table with foreign key inheritancemake_table<Student>("students",make\_column("id", &Student::id, primary_key()),
                                         make_column("name", &Student::name),
                                         make_column("age", &Student::age),
                                         make_column("school\_name", &Student::school_name)),// Define the Teacher table with foreign key inheritancemake_table<Teacher>("teachers",make\_column("id", &Teacher::id, primary_key()),
                                         make_column("name", &Teacher::name),
                                         make_column("age", &Teacher::age),
                                         make_column("subject", &Teacher::subject),
                                         make_column("salary", &Teacher::salary)),// Define the Classroom table with foreign keys to Teacher and Studentmake_table<Classroom>("classrooms",make\_column("id", &Classroom::id, primary_key().autoincrement()),
                                           make_column("teacher\_id", &Classroom::teacher_id),
                                           make_column("student\_id", &Classroom::student_id),
                                           make_column("room\_name", &Classroom::room_name),
                                           foreign_key(&Classroom::teacher_id).references<Teacher>(&Teacher::id),// \<=== here it isforeign_key(&Classroom::student_id).references<Student>(&Student::id)));// \<=== here it is
    • ⭐ Common Table Expressions for CRUD operations.
    • ⭐ Added C++ operator equivalents for bitwise operator expressions.
    • ⭐ Added the possibility of using unary minus expressions.

    ⚙️ Brand new std::any binding example

    🚀 This new example shows you how powerful SQLite with sqlite_orm can be.

    ⚙️ VSCode integration

    Now you can use VSCode tasks to make your developer experience easier when you contribute to sqlite_orm.
    When you have sqlite_orm folder opened in VSCode hit Ctrl/CMD+Shift+P to show actions palette, select 'Run task' and select Run amalgamate script to call amalgamate script without interacting with terminal, Run clang-format to format all the repo and Run unit_tests to run unit_tests binary if you have at already compiled (expected that you've already configured and built the project using cmake)

    • ⚙️ Improved serializing logical and bitwise "not" expressions - less parentheses.
    • ⚙️ Placed constexpr strings used for serialization into the read-only segment, which makes resulting executable binaries smaller.

    🛠 🐞List of bug fixes

    📝Regressions from v1.9:


Previous changes from v1.9

  • ⭐ Common table expressions

    There is a keyword WITH in SQLite which allows making unreal queries like Mandelbrot Set (Apfelmaennchen in German):

    WITH RECURSIVE
  xaxis(x)AS(VALUES(-2.0)UNION ALLSELECTx+0.05FROMxaxisWHEREx\<1.2),
  yaxis(y)AS(VALUES(-1.0)UNION ALLSELECTy+0.1FROMyaxisWHEREy\<1.0),
  m(iter, cx, cy, x, y)AS(SELECT0, x, y,0.0,0.0FROMxaxis, yaxisUNION ALLSELECTiter+1, cx, cy, x\*x-y\*y+cx,2.0\*x\*y+cyFROMmWHERE(x\*x+y\*y)\<4.0ANDiter\<28),
  m2(iter, cx, cy)AS(SELECTmax(iter), cx, cyFROMmGROUP BYcx, cy
  ),
  a(t)AS(SELECTgroup_concat( substr(' .+\*#',1+min(iter/7,4),1),'')FROMm2GROUP BYcy
  )SELECTgroup_concat(rtrim(t),x'0a')FROMa;

    which produces

    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####.... +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####.... +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.

    or Sudoku solver. Actually those exampled can be inspected here. Long story short from this release you can create all those common table expressions right with sqlite_orm. E.g. Mandelbrot Set's code in C++ looks like this:

    constexprorm_cte_monikerautoxaxis ="xaxis"_cte;constexprorm_cte_monikerautoyaxis ="yaxis"_cte;constexprorm_cte_monikerautom ="m"_cte;constexprorm_cte_monikerautom2 ="m2"_cte;constexprorm_cte_monikerautoa ="string"_cte;constexprorm_column_aliasautox ="x"_col;constexprorm_column_aliasautoy ="y"_col;constexprorm_column_aliasautoiter ="iter"_col;constexprorm_column_aliasautocx ="cx"_col;constexprorm_column_aliasautocy ="cy"_col;constexprorm_column_aliasautot ="t"_col;autoast = with_recursive(make\_tuple(xaxis(x).as(union_all(select(-2.0), select(xaxis->*x +0.05, where(xaxis->*x <1.2)))),
        yaxis(y).as(union_all(select(-1.0), select(yaxis->*y +0.10, where(yaxis->*y <1.0)))),
        m(iter, cx, cy, x, y)
            .as(union_all(select(columns(0, xaxis->*x, yaxis->*y,0.0,0.0)),
                          select(columns(m->*iter +1,
                                         m->*cx,
                                         m->*cy,
                                         m->*x * m->*x - m->*y * m->*y + m->*cx,2.0* m->*x * m->*y + m->*cy),
                                 where((m->*x * m->*x + m->*y * m->*y) < 4.0 && m->*iter < 28)))),
        m2(iter, cx, cy).as(select(columns(max<>(m->*iter), m->*cx, m->*cy), group_by(m->*cx, m->*cy))),
        a(t).as(select(group_concat(substr(" .+\*#",1+ min<>(m2->*iter /7.0,4.0), 1), ""), group_by(m2->*cy)))),
    select(group_concat(rtrim(a->*t), "\n")));

    for C++20 or

    usingcte_xaxis =decltype(1_ctealias);usingcte_yaxis =decltype(2_ctealias);usingcte_m =decltype(3_ctealias);usingcte_m2 =decltype(4_ctealias);usingcte_a =decltype(5_ctealias);constexprautox = colalias_a{};constexprautoy = colalias_b{};constexprautoiter = colalias_c{};constexprautocx = colalias_d{};constexprautocy = colalias_e{};constexprautot = colalias_f{};autoast = with_recursive(make\_tuple(
        cte<cte_xaxis>("x").as(union\_all(select(-2.0>>= x), select(column<cte_xaxis>(x) + 0.05, where(column<cte_xaxis>(x) < 1.2)))),
        cte<cte_yaxis>("y").as(union\_all(select(-1.0>>= y), select(column<cte_yaxis>(y) + 0.10, where(column<cte_yaxis>(y) < 1.0)))),
        cte<cte_m>("iter","cx","cy","x","y")
            .as(union_all(select(columns(0>>= iter,
                               column<cte_xaxis>(x) >>= cx,
                               column<cte_yaxis>(y) >>= cy,
                               0.0 >>= x,
                               0.0 >>= y)),
                select(columns(column<cte_m>(iter) + 1,
                               column<cte_m>(cx),
                               column<cte_m>(cy),
                               column<cte_m>(x) * column<cte_m>(x) - column<cte_m>(y) * column<cte_m>(y) +
                                   column<cte_m>(cx),
                               2.0 * column<cte_m>(x) * column<cte_m>(y) + column<cte_m>(cy)),
                       where((column<cte_m>(x) * column<cte_m>(x) + column<cte_m>(y) * column<cte_m>(y)) < 4.0 &&
                             column<cte_m>(iter) < 28)))),
        cte<cte_m2>("iter","cx","cy")
            .as(select(columns(max<>(column<cte_m>(iter)) >>= iter, column<cte_m>(cx), column<cte_m>(cy)),
                       group_by(column<cte_m>(cx), column<cte_m>(cy)))),
        cte<cte_a>("t").as(select(group_concat(substr(" .+\*#",1+ min<>(column<cte_m2>(iter) / 7.0, 4.0), 1), "") >>= t,
                   group_by(column<cte_m2>(cy))))),
    select(group_concat(rtrim(column<cte_a>(t)), "\n")));

    for the older C++ versions. And then just run

    autostmt = storage.prepare(ast);autoresults = storage.execute(stmt);
cout <<"Apfelmaennchen (Mandelbrot set):\n";for(conststring& rowString: results) {
    cout << rowString <<'\n';
}
cout << endl;

    In case this example is too complex let's use the very simple example:

    WITH RECURSIVE
    cnt(x)AS(VALUES(1)UNION ALLSELECTx+1FROMcntWHEREx\<1000000)SELECTxFROMcnt;

    which can be represented in C++ as

    constexprorm_cte_monikerautocnt ="cnt"_cte;autoast = with_recursive(cnt().as(union_all(select(from), select(cnt->*1_colalias +1, where(cnt->*1_colalias < end)))),
    select(cnt->*1_colalias));

    in C++20 and as

    usingcnt =decltype(1_ctealias);autoast = with_recursive(
    cte<cnt>().as(union\_all(select(from), select(column<cnt>(1_colalias) + 1, where(column<cnt>(1_colalias) < end)))),
    select(column<cnt>(1_colalias)));

    in the older C++ versions.

    There are a lot of exampleы of CTE (common table expressions) available in our examples folder here.

    Thanks to @trueqbit for such a brilliant feature!

    👍 ⭐ FTS5 extension support

    Example:

    structPost{
    std::string title;
    std::string body;
};autostorage = make_storage("",make\_virtual\_table("posts",using\_fts5(make_column("title", &Post::title), make_column("body", &Post::body))));

    which is equivalent of

    CREATE VIRTUAL TABLE posts
USING FTS5(title, body);

    Post class is mapped to virtual table posts . You can operate with it like a regular table just like you do with FTS5 virtual table.
    🆕 New AST nodes:

    • match<T>(expression) function which represents MATCH SQLite operator. Can be used like any other function within FTS5 queries
    • rank() function which represents RANK keyword. One can write order_by(rank()) to get ORDER BY rank or plain rank() without order_by to get RANK keyword in your query
    • is_equal overload for table comparison: is_equal<User>("Tom Gregory") serializes to "users" = 'Tom Gregory'
    • highlight<T>(a, b, c) function which represents HIGHLIGHT(table, a, b, c) SQL function
    • unindexed() for UNINDEXED column constraint
    • prefix(auto) for prefix=x column constraint
    • tokenize(auto) for tokenize = x. E.g. tokenize("porter ascii") produces tokenize = 'porter ascii'
    • content(auto) for content=x. E.g. content("") produces content=''
    • content<T>() for content="table_name_for_T". E.g. content<T>() produces content="users"

    More info about FTS5

    ⭐ pragma.module_list

    module_list is a get-only pragma which returns std::vector<std::string>:

    autostorage = make_storage(...);constautomoduleList = storage.pragma.module_list();// decltype(moduleList) is std::vector\<std::string\>

    More info about module_list

    ⭐ Explicit NULL and NOT NULL column constraints

    For a long time sqlite_orm did not have null() and not_null() explicit column constraints functions cause nullability has being computed from mapped member pointer field type. E.g. std::optional, std::unique_ptr and std::shared_ptr are treated as nullables by default. And if you create a class with a field of one of these types and call sync_schema for empty database the columns will be created as nullables and you may store SQLite's NULL there using nullptr value in C++. Also you can create your own nullable types using sqlite_orm::type_is_nullable trait specialization. All other types are not nullable by default. E.g. if your column is mapped to a field of std::string th...