3 ms·
SQLite's RDBMS is fucking incredible because it doesnt make a bunch of assumptions. I don't think developers are paying attention to the most important feature.
by bob1029 6y ago
SQLite's RDBMS is fucking incredible because it doesnt make a bunch of assumptions. I don't think developers are paying attention to the most important feature.
SQLite is embedded in your application. You can write user-defined functions in your application logic that are then bound to SQL functions. These functions are then treated just like first-class functions built-in to the dialect:
https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html
The way Microsoft exposes this in their provider is really wonderful. Using aggregate UDFs feels like cheating. We have also done some next-level stuff where we can call a UDF that executes arbitrary SQL command (i.e. thus invoking subsequent UDFs recursively). We found that UDFs don't necessarily need to be deterministic and can mutate the outside world...
https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
If you think along this axis, you would find SQLite as more a blank canvas that you can construct a very domain-specific view of the world upon and then leverage it for implementing complex business logic. You can get from "lots-of-code" to "no-code" applications pretty damn fast if you play this game right.
- yread 6y agoI usually try not to use the stuff that's strictly RDBMS-specific so that if I need to move it's not a huge hassle, but DAMN: connection.CreateFunction( "regexp", (string pattern, string input) => Regex.IsMatch(input, pattern)); var command = connection.CreateCommand(); command.CommandText = @" SELECT count() FROM user WHERE bio REGEXP '\w\. {2,}\w' "; var count = command.ExecuteScalar();
- bob1029 6y agoYes. Also consider the implications of: var myUdfInstance = new MyUdfInstance(connection); connection.CreateFunction( "Execute", (string command) => myUdfInstance.ExecuteSql(command)); I can confirm this actually works. It stacks up nicely in the debugger too.