MyGit

v1.8

fnc12/sqlite_orm

版本发布时间: 2022-12-09 11:40:18

fnc12/sqlite_orm最新发布版本:v1.9(2024-08-24 10:26:18)

⭐ 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` functions which identifies column order

Example:

auto rows = storage.select(object<User>());
// decltype(rows) is std::vector<User>, where the User objects are constructed from columns in implicitly stored order

auto rows = storage.select(object<User>(true));
// decltype(rows) is std::vector<User>, where the User objects are constructed from columns in declared make_table order
⭐ built-in aggregate functions now support `FILTER` clause

avg(&User::id) -> AVG(users.id) avg(&User::id).filter(where(length(&User::name) > 5)) -> AVG(users.id) FILTER (WHERE LENGTH(users.name) > 5)

⭐ indexes support `WHERE` clause 
make_index("idx_contacts_name", &Contract::firstName, &Contract::lastName, where(length(&Contract::firstName) > 2))

will produce

CREATE INDEX idx_contacts_name ON contacts (first_name, last_name) WHERE LENGTH(first_name) > 2
⭐ added `column_name` member function to `prepared_statement`

Example:

auto statement = storage.prepare(select(columns(&User::id, &User::name)));
auto idColumnName = statement.column_name(0);
auto nameColumnName = statement.column_name(1);
//  decltype(idColumnName) and decltype(nameColumnName) is `std::string_view`

This API is available only with C++17 and higher

⭐ added `dbstat` virtual table support

SELECT * FROM dbstat; can be called with

auto storage =
     make_storage("dbstat.sqlite",
                  make_table("users", make_column("id", &User::id, primary_key()), make_column("name", &User::name)),
                  make_dbstat_table());
storage.sync_schema();

auto dbstatRows = storage.get_all<dbstat>();

dbstat struct looks as follows:

struct dbstat {
    std::string name;
    std::string path;
    int pageno = 0;
    std::string pagetype;
    int ncell = 0;
    int payload = 0;
    int unused = 0;
    int mx_payload = 0;
    int pgoffset = 0;
    int pgsize = 0;
};

It is available if SQLITE_ENABLE_DBSTAT_VTAB macro is defined.

More info on sqlite.org

⭐ `order_by` supports column aliases (thanks to @trueqbit)

Example:

//  SELECT name, instr(abilities, 'o') i
//  FROM marvel
//  WHERE i > 0
//  ORDER BY i
auto rows = storage.select(columns(&MarvelHero::name, as<colalias_i>(instr(&MarvelHero::abilities, "o"))),
                           where(greater_than(get<colalias_i>(), 0)),
                           order_by(get<colalias_i>()));

For more info please check new example in examples/column_aliases.cpp.

⭐ order by kth column (#950, thanks to @trueqbit)

Example:

//  SELECT name, instr(abilities, 'o')
//  FROM marvel
//  ORDER BY 2
auto rows = storage.select(columns(&MarvelHero::name, as<colalias_i>(instr(&MarvelHero::abilities, "o"))),
                           order_by(2));

For more info please check new example in examples/column_aliases.cpp.

⭐ improved `primary_key` API
  • primary_key().asc().autoincrement() makes PRIMARY KEY ASC AUTOINCREMENT
  • primary_key().on_conflict_rollback() makes PRIMARY KEY ON CONFLICT ROLLBACK
  • primary_key().desc().on_conflict_abort() makes PRIMARY KEY DESC ON CONFLICT ABORT

It means that all available SQLite PRIMARY KEY options now are available in sqlite_orm!

⭐ enhanced API for `BEGIN TRANSACTION`

Now once can call BEGIN DEFERRED TRANSACTION, BEGIN IMMEDIATE TRANSACTION and BEGIN EXCLUSIVE TRANSACTION queries with this:

storage.begin_deferred_transaction();
storage.begin_immediate_transaction();
storage.begin_exclusive_transaction();
⭐ `asterisk` now supports table aliases as well (thanks to @trueqbit) 
auto expression1 = storage.select(asterisk<User>()); // SELECT * FROM users
// will return the same as
using als_u = alias_u<User>;
auto expression2 = storage.select(asterisk<als_u>()); // SELECT * FROM users u
⭐ added explicit columns support in `using_` function

using_(&User::id) will produce the same as using_(column<User>(&User::id)). Why people may need this? To use mapped structs with inheritance.

⭐ added `PRAGMA.application_id` 
storage.pragma.application_id(3);  // PRAGMA application_id = 3, function accepts int
auto value = storage.pragma.application_id();  // PRAGMA application_id, decltype(value) is int
🐞 Bug fixes

相关地址：原始地址 下载(tar) 下载(zip)

查看：2022-12-09发行的版本

微信小程序
MyGit:GitHub仓库更新&通知小工具