Sqlite_orm: provide "user define function"

[paineZhang]

Hi!
Could you provide "user define function" function?
Refer to the link: http://sqlite.org/c3ref/create_function.html and http://sqlite.org/c3ref/create_collation.html
This is usually very helpful for complex comparisons, sorting, etc. /:)

[fnc12]

Hm, this is very interesting. I didn't know about custom collation before. Do you have concrete example of custom collating function?
I've been thinking about custom functions a lot of time already. I see two ways of implementing it:
1) To define a custom function one have to define a class (say 'MyFunc) derived from kindasqlite_orm::function. This class must have a cast tostd::stringoperator overloaded which must return function name. To specify this function in query one have to writestorage.select(MyFunc(&User::id)); To bind this custom function to a storage you must add custom_functions<MyFunc> in make_storage as an argument.
2) Add custom_function("my_func", [](int arg0, std::string arg1){ .... }) into make_storage and call it with storage.select(custom("my_func", &User::id, &User::name));. Easier to write but you'll need to write function name again and again every time.

What do you think about that? You can offer your own option if you have one. Thanks

[data-man]

@fnc12
Maybe can useful https://github.com/utelle/wxsqlite3/blob/master/src/wxsqlite3.cpp#L3864

[paineZhang]

About custom collation:

//define a collation function
int pinyin_cmp(void* usr_arg, int n1, const void* p1, int n2, const void* p2){
 ....
}

//create in sqlite3
sqlite3_create_collation(sqlite_handle,"_pinyin_cmp", SQLITE_UTF8, nullptr, &pinyin_cmp);

//table
create table person(name text)

//use
select * from person order by name collate pinyin_cmp;

About custom function.
I think the consistency of style is very important.So, I prefer to the second solution.And it is better to use function object instead of name.

[fnc12]

@paineZhang what do you mean function object instead of name? Can you provide example code?

[paineZhang]

storage.select(custom("my_func", &User::id, &User::name));
"my_func" replace by some function object.

sqlite3 bind function address with name,and use function with name.
sqlite_orm's style is bind type with name, and use type only.
So,I think storage.select(custom(&XXX_fun, &User::id, &User::name))it; is better.Although it may be somewhat awkward in usage. /:(
And , there may be some problems, for example, why record a function address in sqlite_orm.
It may be helpful only to check the type of parameters at the compile stage.

[fnc12]

@paineZhang I guess I know the best way. First of all we need to know whether custom functions/collations binding must be static or dynamic. Static means functions and collations are provided in make_storage only and dynamic means that one can bind any function/collation at any point of time like this storage.add_custom_function("my_func", [](std::string arg0, int arg1){...});. So it will be very nice to implement both method to allow developers act as they want. What do you think?

[paineZhang]

Well, that's perfect.
The "dynamic" is exactly what I need in my current coding work of high-level interfaces.And I was originally planning to provide a method when calling select. Use like :

template<typename T, typename K, typename Fun=void>
vector<T> get(const K& _key, Fun& _fun){
    _fun.name() ;
    _fun.call();
}

[fnc12]

I don't understand the code you provided. What raw query do you mean under this code? I assume custom function will be called like this:

storage.bind_custom_function("my_func", [](int arg0){ ... });
auto rows = storage.select(custom_function("my_func", &User::id));

or in case of static function:

struct MyFunc : public sqlite_orm::function<1> {   // 1 argument
    operator std::string() const {
        return "my_func";
    }

    int operator()(int arg0) const {
        return arg0 * 2;    // dummy body
    }
};
auto storage = make_storage("db.sqlite", make_table(...), custom_functions<MyFunc>());

auto rows = storage.select(custom_function<MyFunc>(&User::id));

[paineZhang]

The "static function" case.

[fnc12]

@paineZhang please check out custom-collation branch and new test in tests.cpp testCustomCollate() example. Tell me whether it is ok or not. I'll be developing other features within the issue. Thanks

[fnc12]

@paineZhang I'm trying to implement custom functions and I got one question: how do you expect to separate aggregate and scalar functions?

[paineZhang]

About custom collate, that ok with usage.Is it gonna work on order_by?

About separate aggregate and scalar functions.How about this:
The storage provide a function like storage.function(string bind_name, args...).It is usage like count.

Honestly, it's really hard to separate them when use bind,unless developers can provide a clear purpose for them.And need developer's self to make sure is using the right way.It is difficult to provide such safeguards.I can't think of a good idea at the moment. /:<

[fnc12]

I'll add collate(std::string) to order_by soon.

About custom function: count can be used in two ways: auto rowsCount = storage.count<User>(); or auto rows = storage.select(columns(&User::name, count()), group_by(&User::gender));. Which one do you mean?

[paineZhang]

The first.
I don't understand why there is a need to differentiate between aggregate and scalar functions.The second way makes them seem indistinguishable. /@.@

[fnc12]

Cause there are two callbacks in sqlite api here.

int sqlite3_create_function(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);

xFunc is used for scalar functions and xStep for aggregate ones

[paineZhang]

Aha!
All right.Then there has to be a explicit way for bind./:)

[fnc12]

@paineZhang Ok, I've thought about it too

[fnc12]

@paineZhang please check out the latest commit in dev branch - now one can you custom collations with ORDER BY

[paineZhang]

OK.They works properly.

[fnc12]

Good news: I understand the difference between custom simple functions and custom aggregate functions. Here you can find an excellent article. In short: max(id) is aggregate cause it works with table data but max(1, 4.5, id) is simple cause it works with simple data and has > 1 arguments. In other words aggregate function is a transformator for row entry but simple function is a function that we often use in common programming languages. E.g. std::max, std::sin.
So simple function has a single callback that is called once. Aggregate functions have two callbacks: step and finalize. step is called on every row and finalize is called once after all steps are fired.

[paineZhang]

Roger that.
Here are 100 praises. /:>

[fnc12]

Holy crap. I don't know how exactly I need to implement custom functions. There are two major options:
1) static - functions are define in make_storage and cannot be redefined after storage creation.
2) dynamic - functions can and must be added after make_storage call and can be removed
What do you think?

[fnc12]

hey @paineZhang . I started to work on custom functions again. The issue is really non trivial. So the only option I know how to implement right now is static and scalar with determinate amount of arguments. So this solution skips dynamic function creating, aggregate functions and scalar functions with a variadic amount of arguments. I shall provide an example code here soon

[fnc12]

auto storage = make_storage("db.sqlite",
                               make_table(...),
                               functions(make_function("SQRT", [](double arg){
                                      return std::sqrt(arg);
                               }),
                                                make_function("LOG10", [](double arg){
                                      return std::log10(arg);
                               }));

[fnc12]

About difference between scalar and aggregate functions.
Scalar function examples: substr, random, sin etc. Scalar functions are common functions which take some arguments and evaluate value at once.
Aggregate functions examples: count, sum, avg. Aggregate functions take a bunch of data: column or a result of a query.
More clare example:

SELECT substr(name)
FROM users

will give you result with N rows where N is equal to amount of rows in the users table. It is how scalar functions work.
But

SELECT avg(id)
FROM users

will not give you N rows but only 1 row cause avg is an aggregate function. It takes the whole column, iterates over it and evaluates average value. This is the difference. Also functions max and min are both aggregate and scalar. max with a single argument with a column of a query result is an aggregate function and evaluates max value of a given bunch of values. But max with > 1 arguments is a scalar function - it returns a max value of a given arguments at once without iterating over rows (cause there are no rows).
Next: aggregate function must have a body of a single iteration and a finalize routine. It means two lambdas. Also aggregate function must transfer some data between iteration routines and final routine. Scalar function have no data to transfer. I guess that some people require aggregate functions support in sqlite_orm and I have no idea how to implement API for it. If you have one please feel free to share it with me and the community. Thanks

[fnc12]

Damn. During this issue existence the third type of functions appeared: WINDOW functions. I shall add only scalar and aggregate custom function support in this issue. There is another issue for WINDOW functions.