Skip to content

Add Between / In / NotIn predicates for range and set membership #1

Description

@jkalias

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 intint64_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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions