3 ms·
Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect w
by bob1029 6y ago
Don't forget about User-Defined Functions.
https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html
We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows.
One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.
- foepys 6y agoApplication-defined functions are very useful. One of my company's applications is already designed to work with different SQL systems and a new customer desperately wanted SQLite for a very special use case. As SQLite is quite simple and doesn't support many functions that are standard in SQL Server, MySQL, Oracle, etc., we used application-defined functions to implement all functions the application needs in C#. It's not very fast but also not slow and the customer is happy, which is what really counts.
- hnrodey 6y agoSounds very interesting. Can you elaborate on how you're leveraging C# for this?
- bob1029 6y agoDirectly through Microsoft's SQLite provider - Microsoft.Data.Sqlite. See: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
- TeMPOraL 6y agoThey're awesome! One common use case is REGEXP. SQLite has a keyword for regular expression matching, but it has no implementation for it. What your application needs to do is to take whatever regex library it's using, make (through whatever FFI method it's using) a C function that interfaces with it, and register it with SQLite. A more advanced feature of this binding mechanism is that, if you provide a bunch of specific callbacks to SQLite, you can expose anything you like as a virtual table, that can be queried and operated on as if it was just another SQLite table. See: https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html. The plugin for full-text search is implemented in terms of this mechanism.
- edwinyzh 6y agoYes, that's a very useful feature to extend the functionalities of SQLite. mORMot (https://github.com/synopse/mORMot https://github.com/synopse/mORMot) added JSON support to SQLite even before JSON1 was introduced.