« Changelog History


sqlite_orm v1.6 Release Notes

Release Date: 2020-10-08 // over 4 years ago

  • 🍱 ⭐ 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 | 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 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

    • HEX
    • QUOTE
    • RANDOMBLOB
    • INSTR
    • REPLACE
    • ROUND

    • SOUNDEX 🍱 ⭐ New date & time functions - all date & time functions are supported from now!

    • TIME

    • STRFTIME 🍱 ⭐ 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")))));// 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 using get 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


Previous changes from 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 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 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 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 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::replace and storage_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 in storage_t::update_all set arguments
    • 🛠 Fixed incorrect (reverse) arguments order in GROUP BY