Proposal: Designated Placeholder Parameters for Prepared Statements

[zhuangyan-stone]

I have written a line of code as follows:

auto s = storage.prepare(select(columns(&User::id, &User::create_time) , where(substr(&O::create_time, -8) >= "12:00:00")));

the second parameter of substr, -8, is also a parameter. However, in practical scenarios, it rarely needs to be modified. As a result, I subconsciously perceive it as not being a parameter, which makes it easy to later write code like:

get<0>(s) = "18:00:00"; // The index of get<> should be 1.

Especially when there are multiple functions and parameters in the statement, it becomes even easier to use the wrong parameter index.

Is it possible to add another method, such as another_prepare (this is just an example; the actual name may vary), that allows users to specify which parameters should actually be replaced with question marks? The usage might look something like this:

auto s = storage.another_prepare(select(columns(&User::id, &User::create_time) , where(substr(&User::create_time, -8) >= Q("12:00:00"))));

The main change is the addition of a sqlite_orm::Q() function. Then, when generating the SQL statement, sqlite_orm would only convert the necessary parameters into question marks:

select id, create_time from user where substr(create_time, 8) >= ?

Thanks.

[fnc12]

Looks like you need API like bind_by_name but sqlite3 doesn't have it ( link1, link2 ). So sqlite_orm probably could have something like explicit name to index map inside every statement but it looks like an over-engineering. Another approach which can help is simulating std::bind API similar to a situation when we make

using namespace std::placeholders;

int myFunc(int arg1, int arg2) {
   return arg1 + arg2;
}

auto callable = std::bind(myFunc, 5, _2);
callable(10);  // calls myFunc(5, 10)

and it can be a really good idea for rare cases just like yours. But I have no idea how this API has to look for sqlite_orm. Probably you @zhuangyan-stone or @trueqbit have any ideas.

Another approach is to make no_bind API like this:

using namespace sqlite_orm;

auto statement1 = storage.prepare(select(columns(&User::id, &User::create_time),
    where(substr(&O::create_time, -8) >= "12:00:00")));
cout << get<0>(statement1) << endl;  // returns -8

auto statement2 = storage.prepare(select(columns(&User::id, &User::create_time),
    where(substr(&O::create_time, no_bind(-8)) >= "12:00:00")));
cout << get<0>(statement2) << endl;  // returns "12:00:00"

This approach looks quite what you are asking, also it looks the easiest one to implement. I'd like to get your opinions on this gentlemen @zhuangyan-stone @trueqbit

[trueqbit]

@fnc12 Just for the records: SQLite has named parameters. But this is actually independent of how we would do this in sqlite_orm.

I would make it very simple, using named parameters.
I do like compile-time monikers, which sqlite_orm has in quite some places, created with C++ user-defined (string) literals.

Something like:

constexpr orm_named_parameter auto myparam = "myparam"_param;

auto stmt = storage.prepare(select(columns(&User::id, &User::create_time),
                            where(substr(&User::create_time, -8) >= myparam("12:00:00"))));
// or:
auto stmt = storage.prepare(select(columns(&User::id, &User::create_time),
                            where(substr(&User::create_time, -8) >= bind<myparam>("12:00:00"))));
// ... then
get<myparam>(stmt) = "18:00:00";

[fnc12]

@trueqbit you're right - there are named parameters (I am answering like LLM lol). Link. And I agree that your approach is the best idea for this. So looks like we got a new feature request. Let me change the label of this issue. One of us will implement it soon @zhuangyan-stone

[zhuangyan-stone]

I think this should be an improvement in API usability. In other words, the purpose of the improvement is to reduce the likelihood of users making mistakes. The "no_bind(arg)" solution, I believe, is not appropriate. Because if users clearly know where to add no_bind(), it means they have a clear understanding of the index that each get should use.

[zhuangyan-stone]

@trueqbit 's method is exactly what I need. Suppose there are 10 parameters that can be bound in a SQL operation, but only 2 of them need to be bound. By clearly specifying these 2 parameters with myparam(), users no longer have to carefully count the parameter indices in the code. However, there is a minor concern: after adding the myparam() feature, will it be compatible with the existing code that calls prepare()? The ideal effect seems to be: if myparam() does not appear in the entire statement, the original prepare() operation will be executed; but if there is even one myparam(), it will exhibit new behavior that all parameters without myparam() will be treated as unbindable. I remember that the number of bindable parameters in the existing prepare() statement is determined at compile time , but the function I envision seems to be full of dynamic characteristics...

[fnc12]

@zhuangyan-stone I guess it will get argument index by calling sqlite3_bind_parameter_index Link, next it will call regular bind which currently is called during index based binding

[trueqbit]

I played around with what I suggested, and I discovered a few things along the way. See draft PR #1454.

There are now 3 ways of accessing 'bindables' by moniker in a statement:

1. This was always possible: traditionally with std::ref'ing an existing variable.
2. By associating a moniker with a bindable - this is a purely logical construct within sqlite_orm.
3. By creating a real named SQL parameter associated with a shared variable.

Each has its use cases, probably.

1. variable reference

For your @zhuangyan-stone purpose, this traditional approach would suffice. It is simple and does not carry the risk of over-engineering.

std::string thresholdValue = "12:00:00";

auto stmt = storage.prepare(select(columns(&User::id, &User::create_time),
                            where(substr(&User::create_time, -8) >= std::ref(thresholdValue))));
storage.execute(stmt);

// ... then
thresholdValue = "18:00:00";
storage.execute(stmt);

2. associated (logical) label

constexpr orm_bindable_label auto threshold = "threshold"_bindable;

auto stmt = storage.prepare(select(columns(&User::id, &User::create_time),
                            where(substr(&User::create_time, -8) >= ("12:00:00" >>= threshold))));
storage.execute(stmt);

// ... then
access<threshold>(stmt) = "18:00:00";
storage.execute(stmt);

The label is a purely logical construct because it is never serialized.
Note that I have chosen the >>= operator like already in use for column aliases.
Also note that I detest the name get, so I decided to use access for now.

3. real named SQLite host parameter

constexpr orm_parameter_name auto threshold = "@threshold"_param;
auto thresholdArg = threshold.create<std::string>("12:00:00");

auto stmt = storage.prepare(select(columns(&User::id, &User::create_time),
                            where(substr(&User::create_time, -8) >= thresholdArg)));
storage.execute(stmt);

// ... then a.)
thresholdArg = "18:00:00";
// ... or b.)
access<threshold>(stmt) = "18:00:00";

storage.execute(stmt);

This approach creates a named SQLite host parameter, which can be present multiple times in the same statement. This involves a shared pointer.
Also note that I really detest the name get, so I decided to use access for now.

Thoughts:

• Approach 1. is simple and lightweight, and I assume that it is usually enough for the job.
• Approach 1. and 3.b. are clearly superior in terms of overhead because we have a direct reference already. Approach 2., 3.a and the index-based access needs to find the bindable instance in the abstract syntax tree (at runtime).
• Approach 2. and 3. make it simpler to access bindables in other places, e.g. outside of a factory function for an SQL statement.
• Approach 3. covers more SQLite functionality as it allows to create real named SQLite host parameters - they are serialized with their name. Because they can be present multiple times in an SQL statement they involve a shared pointer.

Notes:

• All bindables can still be accessed via 0-based index with sqlite_orm::get<>().
• New code needs a C++20 compiler.

This is a draft and things are subject to change or not be implemented based on your feedback!
There are no example yet, but how this is used can be seen in the tests/prepared_statement_tests/select.cpp unit test file.