Background
The typed query API in include/query_predicates.h currently offers these WHERE predicates, each built from a compile-time-checked pointer-to-member: Equal, Unequal, Like, GreaterThan, GreaterThanOrEqual, SmallerThan, SmallerThanOrEqual, and the compound And / Or. All of them are binary — a single member compared against a single value — and every value is bound through a prepared-statement placeholder, so they are safe against SQL injection.
What is missing is first-class support for two extremely common SQL idioms: range tests (BETWEEN) and set membership tests (IN / NOT IN).
Problem
Both idioms are expressible today, but only awkwardly:
- A bounded range such as
age BETWEEN 18 AND 65 must be written as two predicates joined by And:
GreaterThanOrEqual(&Person::age, 18).And(SmallerThanOrEqual(&Person::age, 65));
- Set membership such as
id IN (1, 4, 9) has no direct form at all. It has to be emulated by OR-chaining Equal:
Equal(&Person::id, 1).Or(Equal(&Person::id, 4)).Or(Equal(&Person::id, 9));
This does not scale: the expression grows linearly with the number of values, is easy to get wrong, and produces a deeply nested predicate tree rather than the single IN (...) clause SQLite can optimize. There is no way to express NOT IN at all without manually negating each term.
Proposal
Add three new predicate types alongside the existing ones, following the same member-pointer, prepared-statement design:
Between(&Person::age, 18, 65) → age BETWEEN ? AND ?
In(&Person::id, {1, 4, 9}) → id IN (?, ?, ?)
NotIn(&Person::id, {1, 4, 9}) → id NOT IN (?, ?, ?)
Design notes:
- Each value (the two bounds for
Between, every element for In / NotIn) is bound as a separate SqlValue placeholder, consistent with Bindings() today — no values are ever concatenated into the SQL text.
- The new predicates should compose with
And / Or like any other QueryPredicateBase.
- Type safety carries over from the member pointer, including the existing
int → int64_t and const wchar_t* → std::wstring convenience overloads, so the element type is checked against the member type at compile time.
- An empty
In / NotIn list should have well-defined behavior (e.g. reject it, or emit the SQLite-correct always-false / always-true form) rather than producing invalid SQL.
Acceptance criteria
Between, In, and NotIn are available from query_predicates.h and usable in Fetch, Update, and Delete predicates.
- Generated SQL uses bound placeholders for all values.
- Tests cover each predicate, composition with
And / Or, the integer/string overloads, and the empty-list edge case.
- README "Fetching records" predicate table is updated with the new helpers.
Background
The typed query API in
include/query_predicates.hcurrently offers theseWHEREpredicates, each built from a compile-time-checked pointer-to-member:Equal,Unequal,Like,GreaterThan,GreaterThanOrEqual,SmallerThan,SmallerThanOrEqual, and the compoundAnd/Or. All of them are binary — a single member compared against a single value — and every value is bound through a prepared-statement placeholder, so they are safe against SQL injection.What is missing is first-class support for two extremely common SQL idioms: range tests (
BETWEEN) and set membership tests (IN/NOT IN).Problem
Both idioms are expressible today, but only awkwardly:
age BETWEEN 18 AND 65must be written as two predicates joined byAnd:id IN (1, 4, 9)has no direct form at all. It has to be emulated by OR-chainingEqual:IN (...)clause SQLite can optimize. There is no way to expressNOT INat all without manually negating each term.Proposal
Add three new predicate types alongside the existing ones, following the same member-pointer, prepared-statement design:
Between(&Person::age, 18, 65)→age BETWEEN ? AND ?In(&Person::id, {1, 4, 9})→id IN (?, ?, ?)NotIn(&Person::id, {1, 4, 9})→id NOT IN (?, ?, ?)Design notes:
Between, every element forIn/NotIn) is bound as a separateSqlValueplaceholder, consistent withBindings()today — no values are ever concatenated into the SQL text.And/Orlike any otherQueryPredicateBase.int→int64_tandconst wchar_t*→std::wstringconvenience overloads, so the element type is checked against the member type at compile time.In/NotInlist should have well-defined behavior (e.g. reject it, or emit the SQLite-correct always-false / always-true form) rather than producing invalid SQL.Acceptance criteria
Between,In, andNotInare available fromquery_predicates.hand usable inFetch,Update, andDeletepredicates.And/Or, the integer/string overloads, and the empty-list edge case.