Register custom functions
Closed this issue · 6 comments
Hi,
I wondered if it's possible to create custom sqlite3 functions? In particular I'm interested in a REGEXP Operator, which needs to be implemented by the user afaik. For example here is an implementation with the regular sqlite3 lib:
https://stackoverflow.com/questions/6288287/sqlite-in-c-and-supporting-regexp
I didn't see "sqlite3_create_function()" exposed anywhere.
Furthermore, it might be nice, if the lib already provided a REGEXP Operator Implementation with std::regex perhaps? What do you think?
Hmm. How would the create function be used?
It is possible to write additional functions for the library, of course, and those could make use of previously "created" functions. But when you use those library functions, I think you would still be required to call sqlite3_create_function yourself.
Regarding offering a regex operator: std::regex is one of the slowest implementations available, see for instance https://compile-time.re/cppcon2019/slides/#/2/0/0 It would be weird to offer an operator based on it in this library. And any other regex library would add a dependency I would like to avoid.
That being said: Offering new operators for sqlpp11 or it's connectors is not limited to the respective repositories. You can easily add your own functions in you own projects.
That function is basically just an sql operator then:
WHERE x REGEXP <regex>
So I think what the connector is missing is just a wrapper around 'sqlite3_create_function'. (Edit: I just noticed that I could call the function by myself as sqlite3 is a public dependency. But I think using a function provided by sqlpp11, even if it just a wrapper in the end, is better to be consistent)
That being said: Offering new operators for sqlpp11 or it's connectors is not limited to the respective repositories. You can easily add your own functions in you own projects.
Could you explain what you meant by this?
Regarding offering a regex operator: std::regex is one of the slowest implementations available, see for instance https://compile-time.re/cppcon2019/slides/#/2/0/0 It would be weird to offer an operator based on it in this library. And any other regex library would add a dependency I would like to avoid.
Oh, good to know! I just used it so far as it's built in :-) Perhaps one option would be to add such an operator, based on an external dependency, as optional. So add some feature flag, which is off by default. Then there would be no disadvantage for people not needing it. What do you think?
IIUC, using regex in sqlite3 takes at least the following steps:
- Write the regex function, maybe similar to the one in stackoverflow.
- call
sqlite3_create_function()to register the regex function - actually use it in a query
a. using verbatim
b. using a type-safe function similar to sqlpp::concat or sqlpp::sum.
I don't know whose responsibility it should be to perform step 2.
Step 3 a) can be done today.
Steps 1 and 3 b) require some implementation effort. I currently don't consider them a good fit for the library.
I'd be happy to link to your repository though, if you want to code it.
Regarding 2:
As I said, I think it would be a good idea to expose a wrapper function, which basically does the same thing. I think people expect to use sqlpp11 rather than using the sqlite3 lib directly. At least I expected it and didn't even come up with the idea to just use the sqllite3 function by myself.
Maybe even implement a function in sqlpp11. I don't know other databases very well, but I assume mysql and others have similar options to create custom functions?
Regarding 3b:
Going away from sqlite3, REGEXP is a thing in general. I don't think it's standard sql, but beside sqlite3 (which reserved the keyword) also mysql and mariadb have this. Not sure about others. So it might be a candidate for sqlpp11 in general to have this?
Regarding 1:
It does in deed require some implementation effort, although it seems to be rather low from what I can see. But I understand if you don't see it a good fit for the library.
Hi, not sure what happened to my previous answer. I edited it to have item 1 present...
Regarding REGEXP becoming a thing in general: Sure, when it does so, it should go into sqlpp11 and the connectors for the databases that support it. Currently, that would still exclude sqlite3, which does not support it yet.
The problem I see is that we don't know how sqlite3 would implement REGEXP? Probably different from whatever we would offer as a placeholder. And then one day, we would make the switch and the behavior changes from one version to the next. That's not the intention of the library.
However, I see the value in experimenting with this. You could try to the steps above in a separate repository. I am happy to help with that, too.
The sqlite3 connector got merged into sqlpp11.
Closing all issues here, before archiving. If this issue is still relevant, please re-file for sqlpp11.