Hacker News new | ask | show | jobs
by ergocoder 1389 days ago
Depends on what easily means.

Sqlite can't do custom format date parsing and regex extract. How do we extend something like this?

If we go beyond a simple function to window function, I imagine it would be even harder.

At this point, we nlmight as well use postgres.
2 comments
polyrand 1389 days ago
Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0].

If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]

[0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust...

[1]: https://github.com/multiprocessio/go-sqlite3-stdlib

[2]: https://github.com/nalgeon/sqlean

link
ergocoder 1389 days ago
Wow this is helpful. I'm using sqlite for some of my projects and always bothered that some functions are missing. WITH RECURSIVE is too mind bending.

This seems like I can add a lot more functions to it, not just regex extract.

Came here to complain and learned something useful.

link
Groxx 1389 days ago
Probably also worth noting: you don't need to build (many kinds of) extensions as C-compatible code and separate .so files that you load.

SQLite is an in-process database. You can give it a callback func to execute. So your regex-extract can literally just call a function in your code: https://sqlite.org/appfunc.html

edit: Python's stdlib documentation concisely shows how easy this can be: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... Basically every SQLite library should have something similar. This extreme ease of extending is a big part of why SQLite has so little built-in.

link
simonw 1389 days ago
Funny you should mention those specific examples - I have Datasette plugins adding custom SQL functions to SQLite for both of them!

- https://datasette.io/plugins/datasette-dateutil

- https://datasette.io/plugins/datasette-rure

link