User defined functions

Version available: v1.7

SQLite3 has a very powerful feature which allows defining new functions bound to user C/C++ code. sqlite_orm also has this feature support. There are three types of functions that can be bound:

scalar
aggregate
window

Current page describes scalar and aggregate functions. Window functions are not implemented in sqlite_orm right now.

What is the difference between scalar and aggregate functions?

Scalar and aggregate functions have the same syntax but aggregate has a state which is stored between calls. E.g.: ABS is a scalar function but SUM is an aggregate one. What is the difference? When you call a function with a column as function argument with N rows in specified column scalar function is called N times and returns N values. Aggregate function is also called N times but the result is only one: the sum of all N values. And during evaluating result of query call function SUM will have aggregate state which will contain sum and will increment it on every call. Scalar functions do not have opportunity of having such a state. So scalar functions are pure functions.

Also user defined functions can have constant amount of arguments or variadic.

Scalar functions

To create a scalar function use create_scalar_function storage's public API:

storage.create_scalar_function<SignFunction>();

User defined function must be defined as a dedicated class. Let's assume we need to add SIGN function:

struct SignFunction {

    double operator()(double arg) const {
        if(arg > 0) {
            return 1;
        } else if(arg < 0) {
            return -1;
        } else {
            return 0;
        }
    }

    static const char *name() {
        return "SIGN";
    }
};

Class must have a default constructor and have at least two functions: operator() and static name(). The rules are:

operator() can be both right const and non-right const;
operator() must have non-void result type which must have statement_binder specialization with void result(sqlite3_context* context, const V& value) const public member function;
static name() can have any result type but it must have operator<< overload with std::ostream &. So you can use std::string, std::string_view, const char * or any other type you want;
static name() must not have arguments.

Once you defined a class for it you need to tell your storage to create a function on SQLite side:

storage.create_scalar_function<SignFunction>();

Note that create_scalar_function can be called at any time does not matter whether inner connection state is open or no.

Then you need to write func<SignFunction>(...) right where you want to call SIGN. E.g. to call

SELECT SIGN(5)

you need to write

auto rows = storage.select(func<SignFunction>(5));

or

auto statement = storage.prepare(select(func<SignFunction>(5)));
auto rows = storage.execute(statement);

If you want to delete scalar function from your database call

storage.delete_scalar_function<SignFunction>();

Aggregate functions

Aggregate are similar with scalar functions but with difference inside dedicated class. Let's assume we need to make an arithmetic mean aggregate function:

struct ArithmeticMeanAggregateFunction {
    double sum = 0;
    int count = 0;

    void step(double arg) {
        ++this->count;
        this->sum += arg;
    }

    double fin() const {
        if(this->count > 0){
            return this->sum / double(this->count);
        }else{
            return 0;
        }
    }

    static std::string_view name() {
        return "ARITHMETIC_MEAN";
    }
};

Then let's tell our storage to create an aggregate function on SQLite side:

storage.create_aggregate_function<ArithmeticMeanAggregateFunction>();

Nice. Now we can call our aggregate function:

auto rows = storage.select(func<ArithmeticMeanAggregateFunction>(&User::id));

It means

SELECT ARITHMETIC_MEAN(id)
FROM users

If users table contains data like this

id
1
2
3

then we'll get 2.0 as a result.

How it works? step member function will be called three times cause there are three rows accepted to our query. fin member function will be called once in the end.

Variadic arguments

All examples listed above are about constant (or static) amount of arguments. But functions can have variadic amount of arguments and you may have a question 'How can my cute function class accept dynamic amount of arguments if all code is linked statically'. Fortunately you can do it easily. To accept variadic arguments you need to change arguments list to a list of only one argument of type const arg_values &. So for scalar functions you need to implement operator()(const arg_values &args) in your scalar function class and for aggregate functions you need to implement void step(const arg_values &args) in your aggregate function class.

arg_values is a class with STL container API which has size, operator[], at, empty, begin and end. E.g. we want to make a scalar function which takes variadic arguments and returns arithmetic mean:

struct ArithmeticMeanFunction {

    double operator()(const arg_values &args) const {
        double result = 0;
        for(auto arg_value: args) {
            if(arg_value.is_float()) {
                result += arg_value.get<double>();
            } else if(arg_value.is_integer()) {
                result += arg_value.get<int>();
            }
        }
        if(!args.empty()) {
            result /= double(args.size());
        }
        return result;
    }

    static const std::string &name() {
        static const std::string result = "ARITHMETIC_MEAN";
        return result;
    }
};

Please consider operator() code. One can iterate arg_values with for loop and check every element of arg_values whether it is integer or float. More info about arg_values here.

Deleting functions

One can delete any user defined function using code:

storage.delete_scalar_function<SignFunction>();

for scalar and

storage.delete_aggregate_function<ArithmeticMeanAggregateFunction>();

for aggregate functions.

Links:

Provide feedback

Saved searches