Sqlite_orm: Prepare sql statements separate from execution?

[ink-splatters]

Hello, as I see now statement is prepared on each API call. It would be nice to have ability to prepare statements before execution calls, something like

storage.prepare_update<model::element>(where(c(/*.....)*/)); and then:
storage.update(prepared_stmt, element_instance);

Otherwise there is a speed limitation for usage under relatively high load with large amount of typical select/insert/update operations.

[fnc12]

Prepared statements is a good feature but it is not supported by sqlite_orm right now. It exists in to do list. Please keep the issue open until this feature is done. Thanks for using the lib.
P.S. If you have any clear ideas about API for prepared please feel free to share it here

[fnc12]

This is how I see it:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5)));
auto rows = storage.execute(statement);
auto rowsAgain = storage.execute(statement);

Is it ok for you?

[ink-splatters]

Hello,

yes, pretty good! Thanks a lot!

Best regards,
Peter

On Thu, Jun 27, 2019 at 10:41 AM Yevgeniy Zakharov [e-mail]
wrote:

This is how I see it:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5)));auto rows = storage.execute(statement);auto rowsAgain = storage.execute(statement);

Is it ok for you?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/fnc12/sqlite_orm/issues/212?email_source=notifications&email_token=AAUU3RFPOUYL7BD2UVBBSXDP4R4LXA5CNFSM4GDHKXWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYWMZCQ#issuecomment-506252426,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAUU3RD43PUPDAOR4BIQRE3P4R4LXANCNFSM4GDHKXWA
.

[fnc12]

Do we need API for changing bound values? Between execute calls?

[ink-splatters]

It could be convenient, so yes especially if it doesn’t add more complexity

On 27 Jun 2019, at 13:33, Yevgeniy Zakharov [e-mail] wrote:

Do we need API for changing bound values? Between execute calls?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/fnc12/sqlite_orm/issues/212?email_source=notifications&email_token=AAUU3RBG2WZTBSS6AFEDRH3P4SQRJA5CNFSM4GDHKXWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYW2VFY#issuecomment-506309271, or mute the thread https://github.com/notifications/unsubscribe-auth/AAUU3RBPRIM3CHXVZPZ2MZDP4SQRJANCNFSM4GDHKXWA.

[fnc12]

It does add complexity to implementation but it is required cause it exists in SQLite3 docs https://www.sqlite.org/c3ref/stmt.html

[fnc12]

I see it like this:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > p<int>())));
get<1>(statement) = 5;
auto rows = storage.execute(statement);
auto rowsAgain = storage.execute(statement);

What do you think?

[ink-splatters]

Looks nice for me, thanks a lot for paying attention to your users requests!

On 27 Jun 2019, at 16:26, Yevgeniy Zakharov [e-mail] wrote:

I see it like this:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > p())));
get<1>(statement) = 5;
auto rows = storage.execute(statement);
auto rowsAgain = storage.execute(statement);
What do you think?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/fnc12/sqlite_orm/issues/212?email_source=notifications&email_token=AAUU3RETWNV6WNW3K5GREBDP4TEZXA5CNFSM4GDHKXWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYXJJDA#issuecomment-506369164, or mute the thread https://github.com/notifications/unsubscribe-auth/AAUU3RAGQCXBTI5DA7EAAF3P4TEZXANCNFSM4GDHKXWA.

[fnc12]

Or we can omit specifying bind index explicitly and make it like this:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5)));
auto rows = storage.execute(statement);
std::get<0>(statement.query.conditions).condition.rhs = 10;    // here we replaced 5 with 10. It is not so clear. `.query` returns `select_t` object, `.conditions` returns `std::tuple<where_t<...>`, next we get `where` by zero index, next `.conditions` returns `greater_than_t<decltype(&User::name), int>`, `.rhs` returns int field with `5`
auto rowsAgain = storage.execute(statement);

[ink-splatters]

Personally I prefer first variant, because it requires less knowledge about underlying entities, though i can presume it needs statement to be “castable” to tuple?

On 27 Jun 2019, at 17:37, Yevgeniy Zakharov [e-mail] wrote:

Or we can omit specifying bind index explicitly and make it lie this:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5)));
auto rows = storage.execute(statement);
std::get<0>(statement.query.conditions).condition.rhs = 10; // here we replaced 5 with 10. It is not so clear. .query returns select_t object, .conditions returns std::tuple<where_t<...>, next we get where by zero index, next .conditions returns greater_than_t<decltype(&User::name), int>, .rhs returns int field with 5
auto rowsAgain = storage.execute(statement);
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/fnc12/sqlite_orm/issues/212?email_source=notifications&email_token=AAUU3RHHMJ4OHD6MJUELR53P4TNB5A5CNFSM4GDHKXWKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYXQJZI#issuecomment-506397925, or mute the thread https://github.com/notifications/unsubscribe-auth/AAUU3RCAUW3XRAY5QQQH3N3P4TNB5ANCNFSM4GDHKXWA.

[fnc12]

No, get<N> is used from sqlite_orm:: namespace

[fnc12]

I think I finalized the concept

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5 && like(&User::surname, "o%"))));
auto rows = storage.execute(statement);
get<0>(statement) = 10;   /// change 5 to 10, it is sqlite_orm::get, not std::get
get<1>(statement) = "a%";    // change 'o%' to 'a%'
auto rowsAgain = storage.execute(statement);

Actual binding happens inside execute function call.

Also

User user{...};
auto statement = storage.prepare(insert(user));
auto insertedId = storage.execute(statement);

and

auto statement = storage.prepare(remove<User>(5));
storage.execute(statement);   // delete user with id 5
get<0>(statement) = 6;   // change 5 to 6
storage.execute(statement);   // delete user with id 6

[fnc12]

Merged https://github.com/fnc12/sqlite_orm/pull/366 . Please check out https://github.com/fnc12/sqlite_orm/blob/dev/tests/prepared_statement_tests.cpp . Not you can prepare select, get_all, get, update, update_all, remove, remove_all, insert and replace.
get_no_throw, get_pointer, insert_range, replace_range, insert(cols) to go.
Also rebindings gotta be done right after. Statements are now in experimental mode until it is merged in 1.5. I'd like you to use it now to test whether it is comfortable to use. Thanks

[ink-splatters]

Hello, thanks a lot for your efforts. It looks like what I was expecting. So after implementing binding system that's gonna be it. Syntactically I would do similar placeholders syntax as it's done in std::bind / boost::bind (if possible). Also I have off-topic comment how to improve transaction interface. The proposal is to add API to auto-commit transaction:

1. using lambdas:
   auto transaction = storage.exec_transaction([](){...}); or
2. using guard which commits on leaving scope:
   auto guard = storage.transaction_guard().auto_commit();
   maybe second variant is even more beautiful due to much more simplicity and readability

[fnc12]

I don't understand you about bind. Could you please describe it more detailed?
Transaction guard does exist and it can commit in dtor if you assign guard.commit_on_destroy = true;

[ink-splatters]

1. I meant something like this:

using sqlite_orm::placeholders::_1;
using sqlite_orm::placeholders::_2;

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > _1 and 
like(&User::name, _2))));

statement.bind(_1, 5);
statement.bind(_2, "test");

1. Thanks, I overlooked it. But still:

transaction_guard_t& commit_on_destroy() {
    this->_commit_on_destroy = true;
    return *this;
}

this would allow auto guard = storage.transaction_guard().commit_on_destroy();
this is minor syntax sugar, of course, just thoughts

[fnc12]

1. I think that it would be better to omit placholders:

auto statement = storage.prepare(select(&User::id, where(length(&User::name) > 5 and 
like(&User::name, "%ototo"))));
// and then if you need to rebind something you do
get<0>(statement) = 10;  // length(&User::name) > 5 is replaced with length(&User::name) > 10
get<1>(statement) = "%o%";  // like(&User::name, "%ototo") is replaced with like(&User::name, "%o%")

This is quicker - in the least case it requires only one line of code. What do you think?

1. Great idea! Let me think about it

[ink-splatters]

1. Pretty good!

[jahnf]

Hi, first of all, thanks for the library.

How would you go about inserting multiple rows via a prepared statement?

I tried the following, which crashes on me: (latest header from dev branch)

  User user{0, "SomeName"};
  auto stmt = storage.prepare(insert(user));
  storage.execute(stmt);
  user.name = "Someothername";
  storage.execute(stmt);

[fnc12]

@jahnf Try to make it within a transaction. This issue is in progress right now

[fnc12]

Hey, good news! I merged https://github.com/fnc12/sqlite_orm/pull/374 . Now transaction is not required for performing a prepared statement. Now storage's connection is kept opened until al least one statement object exists.

[fnc12]

Hey people good news! I merged https://github.com/fnc12/sqlite_orm/pull/388 . Now one can reuse statements more than one time. Also I've added by value arguments storing for update, replace and insert support. Next will be argument rebinding with get<N>(statement) = ... function. After that I shall write comments, examples and wiki articles and prepared statements will be ready

[fnc12]

so I merged get template function. Now one can rebind statement values using a syntax like this:

auto statement = storage.prepare(select(10));
auto rows = storage.execute(statement);    // {10}
get<0>(statement) = 20;
auto rows2 = storage.execute(statement);    // {20}

Also I am going to refactor crud statements to work by value by default and add support for std::ref arguments in case you want to bind a value by ref. Next I shall split prepared statements tests file cause it has a lot of lines and the feature will be done.

[fnc12]

the feature is done. Please check out the example

[jahnf]

Awesome, Hopefully I have time on the weekend to try it in my codebase.

[fnc12]

closing the issue. If something is wrong please reopen and give a feedback. Thanks