Chapter 8. Adding constraints to resultsets

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

Alternatively, each constraint can be passed as a separate search(), this produces identical results. Multiple calls to search() also combine constraints using ANDs:

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("="),
        "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:

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.