4 ms·

Taking full advantage of SQLite's extensibility is a center piece of my Go driver. It started due to having to reimplement the OS layer in Go because of tech c

Taking full advantage of SQLite's extensibility is a center piece of my Go driver. It started due to having to reimplement the OS layer in Go because of tech constraints, but it means you _can_ implement VFSes, UDFs (scalar, aggregates and windows), and virtual tables in Go, with reasonable performance and nice APIs. I also made a point of dogfooding this as much as possible, with a bunch of extensions and a few custom VFSes that use the same APIs available to clients of the library. https://github.com/ncruces/go-sqlite3/tree/main/ext https://github.com/ncruces/go-sqlite3/tree/main/ext https://github.com/ncruces/go-sqlite3/tree/main/vfs#custom-vfses https://github.com/ncruces/go-sqlite3/tree/main/vfs#custom-v...

oefrha 2y ago
Interesting, I need to take a look at your vfs implementations. Some of my projects could benefit from a transparent zstd vfs layer, but compiling and loading one of the existing C options in a Go project is yucky even if possible.
- ncruces 2y ago
  I've never done it, but for the readonly case, I think all you need to is to combine the existing reader VFS with zstd-seekable-format-go. https://github.com/ncruces/go-sqlite3/discussions/117#discussioncomment-9975917 https://github.com/ncruces/go-sqlite3/discussions/117#discus... For read/write, I'm honestly not sure. The Zipvfs is an… erm… architectural mess that only really works because it accesses private SQLite APIs. Which is fine, but history has shown the SQLite team is willing to break those APIs, as they did for the ones they build the SQLite encryption extension on. https://sqlite.org/zipvfs/doc/trunk/www/howitworks.wiki https://sqlite.org/zipvfs/doc/trunk/www/howitworks.wiki The zstandard alternative is sqlite_zstd_vfs, which faces the same architectural issues. So, I'd rather not go there. But should be doable, as long as you're not needing private APIs. https://github.com/mlin/sqlite_zstd_vfs https://github.com/mlin/sqlite_zstd_vfs
  - oefrha 2y ago
    Thanks for the pointers!
  - hinkley 2y ago
    The problem with those API breaks is rarely the fact that they got broken, it's the fire drill that happens after they do. If you get lucky, the 2 necessary upgrades happen at a time that fits well into your schedule. If you don't get lucky, then the SQLite upgrade you need contains a CERT advisory for a zero day attack, and not only does that not fit into your schedule but it also doesn't fit into the schedule of the person who did the customization. These are rare events but over the course of a project, that low priority taken to the exponent of the number of vendors you decide to play that game with, approaches or exceeds a probability of 1.00 (>1 meaning 'happened to us twice')
    - ncruces 2y ago
      The problem with those API breaks, for me, is that it may be non trivial to work around them. Most of the open source SQLite encryption extensions have not, for the most part, been able to work around the broken APIs, and are still stuck in the last version of SQLite that supported them. And it's not for lack of effort. There's one exception that moved to the public VFS API, while retaining the ability to load previously encrypted databases. But it was a big effort, and there are compromises involved. https://utelle.github.io/SQLite3MultipleCiphers/ https://utelle.github.io/SQLite3MultipleCiphers/
newusertoday 2y ago
i wish there was something like this for wasm. There are wasm builds available but its not easy to extend them with your own functions.

oefrha 2y ago

Interesting, I need to take a look at your vfs implementations. Some of my projects could benefit from a transparent zstd vfs layer, but compiling and loading one of the existing C options in a Go project is yucky even if possible.

ncruces 2y ago
I've never done it, but for the readonly case, I think all you need to is to combine the existing reader VFS with zstd-seekable-format-go. https://github.com/ncruces/go-sqlite3/discussions/117#discussioncomment-9975917 https://github.com/ncruces/go-sqlite3/discussions/117#discus... For read/write, I'm honestly not sure. The Zipvfs is an… erm… architectural mess that only really works because it accesses private SQLite APIs. Which is fine, but history has shown the SQLite team is willing to break those APIs, as they did for the ones they build the SQLite encryption extension on. https://sqlite.org/zipvfs/doc/trunk/www/howitworks.wiki https://sqlite.org/zipvfs/doc/trunk/www/howitworks.wiki The zstandard alternative is sqlite_zstd_vfs, which faces the same architectural issues. So, I'd rather not go there. But should be doable, as long as you're not needing private APIs. https://github.com/mlin/sqlite_zstd_vfs https://github.com/mlin/sqlite_zstd_vfs
- oefrha 2y ago
  Thanks for the pointers!
- hinkley 2y ago
  The problem with those API breaks is rarely the fact that they got broken, it's the fire drill that happens after they do. If you get lucky, the 2 necessary upgrades happen at a time that fits well into your schedule. If you don't get lucky, then the SQLite upgrade you need contains a CERT advisory for a zero day attack, and not only does that not fit into your schedule but it also doesn't fit into the schedule of the person who did the customization. These are rare events but over the course of a project, that low priority taken to the exponent of the number of vendors you decide to play that game with, approaches or exceeds a probability of 1.00 (>1 meaning 'happened to us twice')
  - ncruces 2y ago
    The problem with those API breaks, for me, is that it may be non trivial to work around them. Most of the open source SQLite encryption extensions have not, for the most part, been able to work around the broken APIs, and are still stuck in the last version of SQLite that supported them. And it's not for lack of effort. There's one exception that moved to the public VFS API, while retaining the ability to load previously encrypted databases. But it was a big effort, and there are compromises involved. https://utelle.github.io/SQLite3MultipleCiphers/ https://utelle.github.io/SQLite3MultipleCiphers/

newusertoday 2y ago

i wish there was something like this for wasm. There are wasm builds available but its not easy to extend them with your own functions.