⭐ 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 ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
m(iter, cx, cy, x, y) AS (
SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
UNION ALL
SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
WHERE (x*x + y*y) < 4.0 AND iter<28
),
m2(iter, cx, cy) AS (
SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
),
a(t) AS (
SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
FROM m2 GROUP BY cy
)
SELECT group_concat(rtrim(t),x'0a') FROM a;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:
constexpr orm_cte_moniker auto xaxis = "xaxis"_cte;
constexpr orm_cte_moniker auto yaxis = "yaxis"_cte;
constexpr orm_cte_moniker auto m = "m"_cte;
constexpr orm_cte_moniker auto m2 = "m2"_cte;
constexpr orm_cte_moniker auto a = "string"_cte;
constexpr orm_column_alias auto x = "x"_col;
constexpr orm_column_alias auto y = "y"_col;
constexpr orm_column_alias auto iter = "iter"_col;
constexpr orm_column_alias auto cx = "cx"_col;
constexpr orm_column_alias auto cy = "cy"_col;
constexpr orm_column_alias auto t = "t"_col;
auto ast = 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
using cte_xaxis = decltype(1_ctealias);
using cte_yaxis = decltype(2_ctealias);
using cte_m = decltype(3_ctealias);
using cte_m2 = decltype(4_ctealias);
using cte_a = decltype(5_ctealias);
constexpr auto x = colalias_a{};
constexpr auto y = colalias_b{};
constexpr auto iter = colalias_c{};
constexpr auto cx = colalias_d{};
constexpr auto cy = colalias_e{};
constexpr auto t = colalias_f{};
auto ast = 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
auto stmt = storage.prepare(ast);
auto results = storage.execute(stmt);
cout << "Apfelmaennchen (Mandelbrot set):\n";
for(const string& 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 ALL SELECT x + 1 FROM cnt WHERE x < 1000000)
SELECT x FROM cnt;which can be represented in C++ as
constexpr orm_cte_moniker auto cnt = "cnt"_cte;
auto ast = 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
using cnt = decltype(1_ctealias);
auto ast = 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:
struct Post {
std::string title;
std::string body;
};
auto storage = 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 representsMATCHSQLite operator. Can be used like any other function within FTS5 queriesrank()function which representsRANKkeyword. One can writeorder_by(rank())to getORDER BY rankor plainrank()withoutorder_byto getRANKkeyword in your queryis_equaloverload for table comparison:is_equal<User>("Tom Gregory")serializes to"users" = 'Tom Gregory'highlight<T>(a, b, c)function which representsHIGHLIGHT(table, a, b, c)SQL functionunindexed()forUNINDEXEDcolumn constraintprefix(auto)forprefix=xcolumn constrainttokenize(auto)fortokenize = x. E.g.tokenize("porter ascii")producestokenize = 'porter ascii'content(auto)forcontent=x. E.g.content("")producescontent=''content<T>()forcontent="table_name_for_T". E.g.content<T>()producescontent="users"
⭐ pragma.module_list
module_list is a get-only pragma which returns std::vector<std::string>:
auto storage = make_storage(...);
const auto moduleList = storage.pragma.module_list(); // decltype(moduleList) is std::vector<std::string>⭐ 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 then sync_schema will create a column with NOT NULL and you will not be able to insert NULL there even though you want it bad. In some cases it was not very comfortable for development and code maintaining. And now from v1.9 one can specify null() and not_null() column constraints no matter the field type. If you don't specify then old algorithm is used. But if you specify either you'll get respective column constrained specified during sync_schema call.
How it works if I extract NULL inside non-nullable type like int or std::string? Default value will be applied. int will become 0, std::string will become a value built with default constructor and so on. Also you can insert NULL into column with explicit not_null() constraint but mapped to non-nullable field using raw insert (the one which is insert(into<...>) by passing nullptr as value to insert. But you'll get a runtime error just like in raw SQLite. So please be wise.
⭐ New fancy explicit column syntax
Sometimes when we use derived classes to map to sqlite_orm storage we have to use column<Derived>(&Base::id) syntax instead of regular &Derived::id to make C++ understand we want Derived type to be identified not Base. Now you can replace column<Derived>(&Base::field) huge syntax with more lightweight and clearer
constexpr auto derived = c<Derived>();
select(derived->*&Derived::id);Available with C++20 and later.
⭐ current_time, current_date and current_timestamp
New functions current_time(), current_date() and current_timestamp() are available which are serialized to CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP respectively. You can use these functions inside queries and also as storage's member functions:
auto storage = make_storage(...);
const auto time = storage.current_time();
const auto date = storage.current_date();
const auto timestamp = storage.current_timestamp(); // <= this guy existed before tbhAnd you can put these functions right into default column constraint function like this:
struct User {
int id = 0;
std::string current;
};
auto storage = make_storage({},
make_table("users",
make_column("id", &User::id, primary_key()),
make_column("current", &User::current, default_value(current_time())))); // <= here we go
storage.sync_schema();The default_value(current_time()) part will be serialized to DEFAULT (CURRENT_TIME).
Also you can query those values inside raw select:
auto rows = storage.select(current_time());which equals to
SELECT CURRENT_TIME⭐ Whole new fancy API for creating user-defined functions
No need to create a dedicated C++ class. This is how it looks:
// example for a freestanding function from a library
constexpr auto clamp_int_f = "clamp_int"_scalar.quote(std::clamp<int>);
// example for a stateless lambda
constexpr auto is_fatal_error_f = "IS_FATAL_ERROR"_scalar.quote([](unsigned long errcode) {
return errcode != 0;
});
// example for a function object instance
constexpr auto equal_to_int_f = "equal_to"_scalar.quote(std::equal_to<int>{});
// example for a function object
constexpr auto equal_to_int_2_f = "equal_to"_scalar.quote<std::equal_to<int>>();
storage.create_scalar_function<clamp_int_f>();
storage.create_scalar_function<is_fatal_error_f>();
storage.create_scalar_function<equal_to_int_f>();
storage.create_scalar_function<equal_to_int_2_f>();
auto rows = storage.select(clamp_int_f(0, 1, 1));
auto rows = storage.select(is_fatal_error_f(1));
auto rows = storage.select(equal_to_int_f(1, 1));
auto rows = storage.select(equal_to_int_2_f(1, 1));However the old API is also supported and is not planned to be deprecated.
⭐ transaction_guard API improvement
There are various types of transactions and also there is a nice API in sqlite_orm made for transactions storage.transaction_guard(). Now this brilliant API became even more brilliant cause it supports various transaction types:
auto guard = storage.deferred_transaction_guard(); // for `BEGIN DEFERRED TRANSACTION` call
auto guard = storage.immediate_transaction_guard(); // for `BEGIN IMMEDIATE TRANSACTION` call
auto guard = storage.exclusive_transaction_guard(); // for `BEGIN EXCLUSIVE TRANSACTION` call⭐ quick_check pragma support
const auto value = storage.pragma.quick_check();
// decltype(value) is std::vector<std::string>Thanks to @jakemumu
⭐ recursive_triggers pragma support
// to get call
const auto value = storage.pragma.recursive_triggers();
// decltype(value) is bool
// to set call
storage.pragma.recursive_triggers(true);⚙️ column operators overloads improvements
- before:
column<User>(&User::id) > c(0)now:column<User>(&User::id) > 0 - before:
"alias"_col > c(0)now:"alias"_col > 0
⚙️ iteration over select
auto db = make_storage("");
std::ranges::view auto int_view = db.iterate(select(1));
for (int i : int_view) {
// ...
}⚙️ user-defined functions API improvement
Usually user-defined functions can be called like this
struct IdFunc {
int operator()(int arg) { return arg; }
};
select(func<IdFunc>(42));Now you also co do it like this:
constexpr auto id_func = func<IdFunc>;
select(id_func(42));⚙️ compound operators improvement
Compound operators (union_, union_all, intersect, except) now support variadic amount of arguments.
struct Object {
int64 id;
int64 difference; // diff value in days
};
union_all(
select(cast<int64>(min(&Object::difference))),
select(count(), where(c(&Object::difference) < 0)),
select(count(), where(c(&Object::difference) >= 0 and c(&Object::difference) < 7)),
select(count(), where(c(&Object::difference) >= 7 and c(&Object::difference) < 15)),
...
);⚙️ modernized the pointer passing interface with C++20 features
Pointer type tags (as part of the pointer passing interface) can also be created in the same way as table references, table aliases, CTE monikers and column aliases.
In addition, these inline variable "tags" should be used instead of type aliases.
For example, the pointer type "carray" is now defined:
inline constexpr orm_pointer_type auto carray_pointer_tag = "carray"_pointer_type;instead of:
inline constexpr const char carray_pointer_name[] = "carray";
using carray_pointer_type = std::integral_constant<const char*, carray_pointer_name>;⚙️ dynamic in supports `std::list` as well
std::list<int> list{1, 2, 3};
in(&User::id, list);- ⚙️ Code quality improvement (thanks to @trueqbit)
- ⚙️ Ability to use "sqlite_master" table in your queries (e.g.
storage.get_all<sqlite_master_table>(where(sqlite_master_table->*&sqlite_master::type == "table"))) - ⚙️ Moved all library's operator overloads into
internalnamespace in order to facilitate ADL - ⚙️
lesser_thanrenamed toless_thancause ideologically "less" refers to quantity, "lesser" refers to quality.lesser_thanis still available and will be available till the next release but marked as deprecated - ⚙️
autoincrementdedicated function is removed. It was deprecated in the previous release. Useprimary_key().autoincrement()instead - ⚙️
havingdedicated function is removed as well. It was deprecated in the previous release just likeautoincrement. Please usegroup_by(...).having(...)instead - ⚙️ User-defined functions memory allocations improvement #1248
- ⚙️ Build time improvements! All for you with love ❤️
- ⚙️ Prepared statements containing bound pointers couldn't be dumped with
storage_t<>::dump, but can now
We have to notice that @trueqbit added huge improvements in this release. He rocks!
