Releases: fnc12/sqlite_orm
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 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 th...
Contributors
Assets 2
v1.8.2
⭐ Indexes support expressions
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
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.
⭐ Daisy chaining concatenation operator
select(c("first name") || " " || "last name");means
SELECT 'first name' || ' ' || 'last name'⭐ Multi-table select
One can run
select(columns(asterisk<X>(), asterisk<Y>()), ...);to get columns of several tables.
Curated list of corrections and improvements
- ⚙️ Removed dependency on RTTI
- ⚙️ Compilation time improvement #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()andstd::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
EXISTSclause, which must be always enclosed in parentheses
v1.8.1
In order to microsoft/vcpkg#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 (#1114)
- updated clang-format lint action on github
- removed dangling submodule reference (#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] (catchorg/Catch2#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.
v1.8
⭐ Triggers! No more words (thanks to @f-michaut)
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.
⭐ generated columns support
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.
⭐ added pointer passing interface API
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.
⭐ added `bool` optional argument to `asterisk` and `object` ...
Contributors
Assets 2
v1.7.1
v1.7
⚖️ license changed from BSD3 to GNU AGPL + paid MIT
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.
⭐ added custom scalar and aggregate functions support
Long story short:
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.
⭐ added raw `INSERT`/`REPLACE` feature
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
⭐ added all built in math functions
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
⭐ added `as_optional` function which allows you obtaining result type as `std::optional`. Available with C++17 or higher
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)>>⭐ added JSON1 extension support
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.
⭐ added strong type collations
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.
⭐ added explicit FROM feature
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.
⭐ added transformer support for `insert_range` and `replace_range` statements
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;
});⭐ added `PRAGMA integrity_check` (thanks to @mishal23)
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.
⭐ new core functions support
UNICODETYPEOFTOTAL_CHANGESLAST_INSERT_ROWIDIFNULL
- ⭐ added static
INfeature (fixed bugs #675 and #512) - ⭐ added
storage.column_nameAPI - ⚙️ added
noexceptgetters and setter modifiers. Available with C++17 and higher - ⚙️ added
std::nulloptsupport. It works just likenullptrworks and available with C++17 or higher - ⚙️ binary operators can be used as row results
- ⚙️ added some thread safety improvements #736
- ⚙️ added static assert in case if you try to call
storage.insertwith a non-insertable table (#644 thanks to @denzor200) - ⚙️ improved serialization for some AST nodes:
std::stringwas replaced withstd::string_viewfor C++17. It reduces amount of heap allocations during query serialization - ⚙️ file
tests/CMakeLists.txtnow has a pretty look (thanks to @undisputed-seraphim) - ⚙️ fixed GCC warnings (thanks to @denzor200)
- ⚙️ improved code formatting
- ⚙️
iterator_tnow is compatible withstd::input_iteratorconcept (#685 thanks to @andrei-datcu) - ⚙️
field_printernow 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)
Contributors
Assets 2
v1.6
⭐ Added `CHECK` constraint
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)
)⭐ Added bitwise operators support
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⭐ Added `indexed_column` function to specify order (`ASC`, `DESC`) and collation for indexed columns
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);⭐ New core functions
HEXQUOTERANDOMBLOBINSTRREPLACEROUNDSOUNDEX
⭐ New date & time functions - all date & time functions are supported from now!
TIMESTRFTIME
⭐ Added `storage.dump` function for prepared statements
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.
⭐ Added custom container support for `get_all` prepared statement
Example:
auto statement = storage.prepare(get_all<User, std::list<User>>());⭐ `UNIQUE` constraint supports more than one column
Example:
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))⭐ New table operating API
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
⭐ Added `VALUES` API
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_schemabehavior 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
CASEoperator - 🐞 Fixed bug in
sync_schema: #521 - 🐞 Fixed backup bug: #540
Special thanks to:
@undisputed-seraphim
@Leon0402
@air-h-128k-il
v1.5
⭐ Prepared statements
// 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
⭐ GLOB operator
// 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%"));⭐ std::optional support (C++17 and higher)
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
⭐ get_all_pointer query
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>>⭐ Improved DEFAULT constraint
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')
)⚙️ Query static validations
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!
⚙️ storage_t::filename()
Use storage_t::filename() function to retrieve filename passed in storage during construction.
Example:
const auto &filename = storage.filename(); // decltype(filename) is std::string⚙️ Added code format with clang-format
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.
🐞 Fixed bug with incorrect PRIMARY KEY and AUTOINCREMENT order
Now one can write these two constraints in either order: the correct one and the legacy one.
v1.4
⭐ CASE support
// 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>⭐ Added core functions: COALESCE, ZEROBLOB, SUBSTR
// 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;⭐ Dynamic ORDER BY
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);⭐ Added LIKE as a query result
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>⭐ Added LIKE ESCAPE option support
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
nullptrbinding to WHERE conditions - 🚀 Reduced binary size
- 🚀 Added composite key support for
storage_t::removefunction - 🚀 Reduces memory consumption
🚀 Better error reporting
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
🐞 Bug fixes
- Fixed GCC6 compilation bug
- Fixed runtime error on ARM architecture
- Fixed getter by value support for
storage_t::replaceandstorage_t::updatefunctions - Fixed bug with iterating over blob values
- Fixed on_copy coping on
storage_tcopy - Fixed silencing binding failure - now exception is thrown
- Fixed using
std::unique_ptrinstorage_t::update_allset arguments - Fixed incorrect (reverse) arguments order in GROUP BY
v1.3
⭐ Complex subqueries
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))))));⭐ EXCEPT and INTERSECT
All compound operators now are available:
SELECT dept_id
FROM dept_master
EXCEPT
SELECT dept_id
FROM emp_masteris 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_masteris just
auto rows = storage.select(intersect(select(&DeptMaster::deptId),
select(&EmpMaster::deptId)));-
⭐ Column aliases
-
⭐
SELECT * FROM tablewith syntaxstorage.select(asterisk<T>())returnsstd::tupleof mapped members' types -
⭐
CAST(expression AS type)expression withcast<T>(expression)syntax -
⭐ added
juliandayfunction -
🚀
FOREIGN KEYnow works with compositePRIMARY KEY
🚀 added simple arithmetic types biding to WHERE conditions
bool myFilterIsOn = getMyFilterValue();
auto values = storage.get_all<User>(where(!myFilterIsOn and like(&User::name, "Adele%")));- 🚀 improved performance - replaced
std::shared_ptrwithstd::unique_ptrinside 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