Changelog History
-
v1.6 Changes
October 08, 2020๐ฑ โญ Added
CHECK
constraintauto 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 | 13storage.select(bitwise\_and(60, 13)); // SELECT 60 & 13storage.select(bitwise\_shift\_left(60, 2)); // SELECT 60 \<\< 2storage.select(bitwise\_shift\_right(60, 2)); // SELECT 60 \>\> 2storage.select(bitwise\_not(60)); // SELECT ~60
๐ฑ โญ Added
indexed_column
function to specify order (ASC
,DESC
) and collation for indexed columnsauto 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
HEX
QUOTE
RANDOMBLOB
INSTR
REPLACE
ROUND
SOUNDEX
๐ฑ โญ New date & time functions - all date & time functions are supported from now!TIME
STRFTIME
๐ฑ โญ Addedstorage.dump
function for prepared statementsauto 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 thatdump
function prints real values instead of question marks. Also it does not call anysqlite3
functions - it callssqlite_orm
serializer instead.๐ฑ โญ Added custom container support for
get_all
prepared statementExample:
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
APIExample:
// 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")))));// orstorage.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 usingget
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
: #521 - ๐ฑ ๐ Fixed backup bug: #540
Special thanks to:
@undisputed-seraphim
@Leon0402
@air-h-128k-il -
v1.5
December 22, 2019 -
v1.4 Changes
August 13, 2019๐ โญ 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 usersauto 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
andmulti_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 amulti_order_by
that acceptsorder_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 insqlite_orm
:// SELECT name LIKE 'J%' ESCAPE '\_'// FROM usersauto rows = storage.select(like(&User::name, "J%").escape("\_"));
or
// SELECT LIKE(name, 'J%', '\_')// FROM usersauto 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
๐ฑ ๐ Better error reporting
๐ป Before once you get an exception thrown asking
e.what()
gave you a poor text likeNOT NULL constraint failed
. Now it is more detailed (thanks tosqlite3_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::replace
andstorage_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
instorage_t::update_all
set arguments - ๐ Fixed incorrect (reverse) arguments order in GROUP BY
-
v1.3 Changes
August 11, 2019โญ Complex subqueries
SELECT cust\_code, cust\_name, cust\_city, gradeFROM customerWHERE grade=2 AND EXISTS (SELECT COUNT(\*) FROM customer WHERE grade=2GROUP 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\_idFROM dept\_master EXCEPTSELECT dept\_idFROM emp\_master
is just
auto rows = storage.select(except(select(&DeptMaster::deptId), select(&EmpMaster::deptId)));
and
SELECT dept\_idFROM dept\_master INTERSECTSELECT dept\_idFROM emp\_master
is just
auto rows = storage.select(intersect(select(&DeptMaster::deptId), select(&EmpMaster::deptId)));
โญ Column aliases
โญ
SELECT * FROM table
with syntaxstorage.select(asterisk<T>())
returnsstd::tuple
of mapped members' typesโญ
CAST(expression AS type)
expression withcast<T>(expression)
syntaxโญ added
julianday
function๐ฑ ๐
FOREIGN KEY
now 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_ptr
withstd::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
- ๐ improved performance - replaced
-
v1.2 Changes
July 04, 2018- UNION
Useunion_
/union_all
functions with two subselects to query data withUNION
/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 ofstatic_assert
functions. - Speed
โฌ Decreaseddump()
and several other functions work time
- UNION
-
v1.1 Changes
March 17, 2018๐ Features
- VACUUM
Usestorage.vacuum()
to callVACUUM
query explicitly or usestorage.pragma.auto_vacuum(...);
to setPRAGMA auto_vacuum
. - Arithmetic operators
+
,-
,*
,/
and%
are now available for using within expressions. Example:auto doubledAge = storage.select(c(&User::age) * 2);
orauto 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
isadd(&User:age, 5)
. Alsosub
,mul
,div
andmod
functions are now available insqlite_orm
namespace. - ๐ Bug fixes
๐ Fixed compilation error in casesqlite_orm.h
file is included in more than one place.
๐ Fixed incorrect query generation inremove
call in casePRIMARY KEY
is defined as a separate column. - โ Warning fixes
๐ Fixed three Visual Studio 2017 warnings (thanks to @ntkernelcom)
- VACUUM
-
v1.0 Changes
March 09, 2018๐ 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 aboutdb << "SELECT " + idColumnName + " FROM " + myTableName + " WHERE " + idColumnName + " < 10 ORDER BY " + nameColumnName;
. Just writestorage.select(&Object::id, where(c(&Object::id) < 10), order_by(&Object::name));
instead - Intuitive syntax
most of SQLite3 keywords are provided as functions insqlite_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 addusing 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. Butsqlite_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 astd::vector
(or any other STL-compatible container) of any column with or without any desiredwhere
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 toTEXT
,int
,long
map toINTEGER
,float
,double
map toREAL
. But you can also use your custom types if you have it. You can even bind yourenum
to be mapped as string or int or whatever. Or even bindboost::optional<T>
as nullable generic type.๐ BLOB support
BLOB
SQLite type maps tostd::vector<char>
or you can add binding to any other type.๐ FOREIGN KEY support
you can useFOREIGN KEY
with intuitive syntax:foreign_key(&Visit::location).references(&Location::id)
. And you don't need to callPRAGMA 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 writeprimary_key(&User::id, &User::firstName)
and your composite key is ready to go.๐ JOIN support
๐ all kinds ofJOIN
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 callsync_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 duringmake_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 inmake_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. Useconan
,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 catchstd::system_error
. By thisstd::system_error
you can know whether error happened in SQLIte or in the lib by inspecting theerror_category
.
sqlite_orm.h
SHA16e0b40c2b7122c02cb6d9efbade487689d933827
- No raw string queries :