Using the schema from the previous chapter:
accounts all_accounts=accounts::create(conn); all_accounts->search("balance", ">", 0); for (const auto &row: *all_accounts) { // ... }
search() adds a constraint to the resultset.
basically a WHERE. The above example iterates over
an equivalent of “SELECT fields FROM
accounts WHERE balance > 0”.
Each basic constraint consists of three parameters, the first two of each are strings. The first parameter is a string that gives the name of some column from the resultset's schema, but it's also possible to get away with a short SQL expression. The second parameter is a string with an appropriate comparison operator, like “=” or “!=”.
The third parameter is the value that's being compared. The resulting raw
SQL gets formed by concatenating all three parameters together,
and replacing the value with a “?”
placeholder. The placeholder's value gets provided as a
discrete parameter in the
internal
execute()
call
that executes the query. The value's type can be any type that
execute() normally accepts.
all_accounts->search("a", "=", key, "b", "!=", value);
This uses columns that are actually a part of the sample schema,
from the previous chapter; this is just to illustrate this example.
The resulting query's
“WHERE” constraints specifies
“A = ? AND b != ?”.
search() takes a variadic list of
constraint specifications. Multiple constraint specifications
get joined together by “AND”s.
Alternatively, each constraint can be passed as a separate
search(), this produces
identical results. Multiple calls to search() also
combine constraints using
“AND”s:
all_accounts->search("a", "=", key); all_accounts->search("b", "!=", value);
This also produces the “A = ? AND b != ?”
constraint.
all_accounts->search(x::sql::dbi::AND("a", "=", key, "b", "!=", value)); all_accounts->search(x::sql::dbi::OR("a", "=", key, "b", "!=", value));
AND()
takes individual constraints as parameters, and explicitly combines
them with ANDs.
OR()
uses OR clauses instead of
AND clauses. The second call to
search() constructs
“a = ? OR b != ?”.
They can be combined accordingly:
all_accounts->search(x::sql::dbi::AND("key", "=", 4, x::sql::dbi::OR("category", "=", "memo", "category", "=", "note"), "discarded", "=", 0));
This constructs a “(key = ? AND (category = ? OR
category = ?) AND discarded = ?)” constraint.
all_accounts->search(x::sql::dbi::NOT("category", ">", 0, "category", "<", 10);
NOT()
is equivalent to
AND(),
inside an SQL “NOT”.
This example constructs
“NOT (category > ? AND category < ?)”
SQL.
Giving nullptr for a value creates SQL
that tests for a NULL:
all_accounts->search("category", ">", 0, "discarded", "=", nullptr);
This example constructs
“CATEGORY > ? AND discarded IS NULL” SQL.
Using “!=” instead of
“=” produces an
“IS NOT NULL” instead, and all other
comparison strings for a nullptr
result in a dummy “1=0”
constraint, which always evaluates to a logical false.
all_accounts->search("total", x::sql::text("="), "sum(entries)");
Passing a
“x::sql::text(comparison)”
for the comparison parameter has the effect of processing the comparison
value, the third parameter, as unquoted SQL literal text. The above
example produces the “total=sum(entries)”
constraint, instead of “total=?”, with
the “sum(entries)” given as a string value for the
placeholder.
And just to make things a bit more complicated, with
x::sql::text the comparison value can
be a non-empty std::list<std::string>,
with the first value in the list becoming the unquoted SQL literal
text, with the remaining strings in the list giving values for the
placeholders in the literal text:
all_accounts->search("account_id", x::sql::text("="), std::list<std::string>({ "currval(pg_get_serial_sequence(?, ?))", table_name, column }));
The library uses this construct internally to process serial columns; it's unlikely that this would be useful in general.
std::vector<int> codes={1,5}; all_accounts->search("category", ">", 0, "code", "=", codes);
Comparing something using “=” against a
std::vector results in an
“IN” SQL. The above example constructs
“category > ? AND code IN (?, ?)”,
with the number of placeholders depending on the size of the vector.
The constraint makes a copy of the vector (which may or may not be a
performance issue), and passes its values to
execute(), for the placeholders.
The “!=” comparator creates a
“NOT IN” SQL. Any other comparator throws
an exception.
An empty vector constructs substitute SQL that returns the appropriate
value. Using “=” to compare anything to an
empty list constructs a “1=0” SQL,
that evaluates to a logical false. Comparing
“x” against an
empty list using “!=” constructs a
“x IS NOT NULL” SQL:
This is because the logical assertion
“value is
in an empty list” is always a logical false.
“value is
not in an empty list” is always true; however, by definition,
any comparison that involves a NULL value is
always false.
“x IS NOT NULL” SQL in this
situation calculates the correct result.
auto codes=x;:vector<int>::create(); auto constraint=x::sql::dbi::AND("category", ">", 0, "code", "=", codes);
An x::vector
may be used in place of a std::vector.
This avoids the need to copy an entire std::vector
into the resultset's constraint.
However, no locking takes place here,
and the vector should not be modified by other threads, at least until
after begin() and
end() return the sequence iterators.