Writing Your Own Function Wrappers

Quince's wrappers let you call some SQL functions, but you may want to call others. Then the best approach is to define new wrappers, using the same technique that quince uses. E.g. here is quince's implementation of length():

length(const abstract_mapper<std::string> &arg) {
    return function<int32_t>("length", arg);

length() is delegating most of its work to function(), and that is what your wrappers should do too. Here's what you need to know:

A word of caution. When you use function() to wrap some SQL function foo, quince cannot know how many arguments foo expects, nor what types they should be, nor what type foo returns. As the author of a wrapper, it is your duty to find that information and code it into your wrapper, so that call sites will have the benefit of static type checking. In particular:

Allowing for NULL

Be especially careful to check whether the SQL function can return NULL. If it can, then the template parameter to function() should be a boost::optional. E.g. here is a possible definition of the 16-bit version of max():

max(const abstract_mapper<int16_t> &arg) {
    return function<boost::optional<int16_t>>("max", arg);

function<boost::optional<int16_t>>() returns an exprn_mapper<boost::optional<int16_t>>, which in this case we hand straight back to our caller. If we wanted to give the caller an exprn_mapper<int16_t> then we would have to provide a default, e.g.:

max_or_zero(const abstract_mapper<int16_t> &arg) {
    return function<boost::optional<int16_t>>("max", arg).get_value_or(0);
Allowing for DBMS-dependent types

Often the arguments or return values of an SQL function depend on the DBMS. E.g. on PostgreSQL you can apply the SQL abs function to a smallint (which represents an int16_t), and the result will be a smallint; whereas on sqlite there is nothing like smallint: if you want to take the absolute value of an integer, no matter how small, you pass abs an INTEGER, which can hold up to 64 bits, and it returns an INTEGER.

That sort of DBMS-dependency takes no effort. E.g. here is quince's implementation of the 16-bit overload of abs():

abs(const abstract_mapper<int16_t> &arg) {
    return function<int16_t>("abs", arg);

We use the polymorphically mapped type int16_t, which quince_postgresql maps to smallint; and quince_sqlite backend maps to INTEGER. So it always turns out the same as SQL abs's argument, and SQL abs's return value.

Other cases are harder. Take avg(). On PostgreSQL, if you apply the SQL avg function to inputs of integral type, the result is a variable-precision type called numeric, which quince does not currently map to any C++ type at all. On sqlite, however, if you apply avg to inputs of integral type, the result is REAL.

The approach we took with abs() is not immediately available, because there is no C++ type that maps to numeric on one DBMS and to REAL on the other.

The solution is to ask for more help from the DBMSes. After evaluating avg, the DBMSes must convert the result to something that is mapped by a single C++ type -- some C++ type that will be acceptable to all parties. In this case, I nominate double.

So here's how quince implements avg() with an int16_t argument:


avg(const abstract_mapper<int16_t> &arg) {
    return cast<boost::optional<double>>(function<any_avg>("avg", arg));

The first line defines a type any_avg, which will be the C++ counterpart of any type that SQL's avg returns, even allowing NULLs [9] . We will be using any_avg as if it were a mapped type, but only for intermediate values within server-side expressions. Its values will never get converted either to or from any C++ type, and its SQL typename will never be be mentioned: not in quince, not in backends, not in application code. So quince doesn't need to know anything about the type any_avg, except that we're using it consistently. The macro QUINCE_DEFINE_SERVER_ONLY_TYPE defines types that fit this very limited role.

When avg() calls function(), to build the call to SQL's avg with its obscure, possibly NULL return type, the template parameter is any_avg. That causes function() to return an exprn_mapper<any_avg>. We pass that to cast<boost::optional<double>>(), which represents a server-side cast, from the possibly-NULL obscure thing to a possibly NULL value of whatever type is represented by double on the particular DBMS. The result is an exprn_mapper<boost::optional<double>>, which users can put to work in all the normal ways, safely and without regard to the chicanery that went into making it.

[9] because SQL's avg is an aggregate function that returns NULL on empty input.