4 ms·

I guess I like the idea but this would need to be a core feature in C and not a rust extension to be useful

by greatgib 2y ago

hatsuseno 2y ago
Why is this not useful as an extension, Rust or otherwise?
- zimpenfish 2y ago
  Some builds of SQLite (e.g. the default macOS version) don't allow extension loading. Local policies may equally disallow extension loading. (I think it's definitely useful as an extension but I can appreciate there's situations where a core feature is easier.)
  - alexgarcia-xyz 2y ago
    Agreed, though the topic of timezone+DST support has come up a few times in the SQLite forums, and the SQLite team has never expressed much interest in it. Which I don't blame them, they would need to write it from scratch in C (for licensing reasons), and keep a timezone DB updates, which sounds like a nightmare. I'm curious - do you find yourself constrained by the default macOS build often? Typically I `brew install sqlite` and use that CLI to get around extension loading issues (and for more modern SQLite versions). Same with Python's default MacOS build, which I avoid at all costs. Though very curious to hear more about times where that isn't a viable option
    - zimpenfish 2y ago
      > do you find yourself constrained by the default macOS build often? Only for about 10 minutes after a new install until I ... > `brew install sqlite` But I could understand that some people may not have that option (although I've yet to work at a place that locks macOS down that much, I've worked at places which locked down their Windows laptops down far more than that.)
- greatgib 2y ago
  Often SQLite is what you will get, quite anywhere. But you might not be able to install so easily extensions. Also a key value of SQLite is to be quite self contained and easy to build without strong dependency. Finally, I'm not an expert of SQLite extensions, but I think that you will easily have a strong impact on performance using the extension for core type that you might use a lot in big tables.
alexgarcia-xyz 2y ago
Even though it's written in Rust, you can still use it in other languages like Python or Node.js. It compiles to a shared library file, which most SQLite clients support with `.loadExtension()` or another similarly named method. This extension isn't the best example, since it's a thrown-together demo, but sqlite-ulid is a similar extension written in Rust that could be run anywhere, not just Rust That being said, writing in Rust instead of C has many drawbacks (slightly slower, cross compiling, larger binary sizes, WASM is more difficult, statically compiling is complex, etc.). But for cases like this, many SQLite extensions I write in Rust are just light wrappers around extremely high quality Rust crates (like jiff), which makes my life easier and it "good enough"
- Onavo 2y ago
  > slightly slower Because of the FFI overhead?
  - alexgarcia-xyz 2y ago
    I think so - I wrote my own Rust FFI bindings for SQLite extensions, and I tried to make it as fast as possible, but a "hello world" extension was still 10-15% slower in Rust than C[0]. That being said, it depends what the extension does - a "hello world" extension mainly just calls the same SQLite C APIs over and over again, so the small Rust layer makes it a bit slower. However, my Rust extensions for regex[1] and CSV parsing[2] are usually faster than the C counterparts, mostly due to less memory allocations and batching. It's not a 1:1 comparison (both extensions have slightly different APIs and features), but I'd say a lot of "real world" features available in Rust can be faster than what's available in C. That being said, I'm sure someone could write their own faster CSV or regex extension in C that is faster than the Rust ones. But it's a ton of work to do that from scratch, and I find wrapping a pre-existing Rust implementation to be much easier [0] https://github.com/asg017/sqlite-loadable-rs?tab=readme-ov-file#benchmarks https://github.com/asg017/sqlite-loadable-rs?tab=readme-ov-f... [1] https://github.com/asg017/sqlite-regex https://github.com/asg017/sqlite-regex [2] https://github.com/asg017/sqlite-xsv https://github.com/asg017/sqlite-xsv
  - greatgib 2y ago

I guess I like the idea but this would need to be a core feature in C and not a rust extension to be useful

hatsuseno 2y ago

Why is this not useful as an extension, Rust or otherwise?

zimpenfish 2y ago
Some builds of SQLite (e.g. the default macOS version) don't allow extension loading. Local policies may equally disallow extension loading. (I think it's definitely useful as an extension but I can appreciate there's situations where a core feature is easier.)
- alexgarcia-xyz 2y ago
  Agreed, though the topic of timezone+DST support has come up a few times in the SQLite forums, and the SQLite team has never expressed much interest in it. Which I don't blame them, they would need to write it from scratch in C (for licensing reasons), and keep a timezone DB updates, which sounds like a nightmare. I'm curious - do you find yourself constrained by the default macOS build often? Typically I `brew install sqlite` and use that CLI to get around extension loading issues (and for more modern SQLite versions). Same with Python's default MacOS build, which I avoid at all costs. Though very curious to hear more about times where that isn't a viable option
  - zimpenfish 2y ago
    > do you find yourself constrained by the default macOS build often? Only for about 10 minutes after a new install until I ... > `brew install sqlite` But I could understand that some people may not have that option (although I've yet to work at a place that locks macOS down that much, I've worked at places which locked down their Windows laptops down far more than that.)
greatgib 2y ago
Often SQLite is what you will get, quite anywhere. But you might not be able to install so easily extensions. Also a key value of SQLite is to be quite self contained and easy to build without strong dependency. Finally, I'm not an expert of SQLite extensions, but I think that you will easily have a strong impact on performance using the extension for core type that you might use a lot in big tables.

alexgarcia-xyz 2y ago

Even though it's written in Rust, you can still use it in other languages like Python or Node.js. It compiles to a shared library file, which most SQLite clients support with `.loadExtension()` or another similarly named method. This extension isn't the best example, since it's a thrown-together demo, but sqlite-ulid is a similar extension written in Rust that could be run anywhere, not just Rust That being said, writing in Rust instead of C has many drawbacks (slightly slower, cross compiling, larger binary sizes, WASM is more difficult, statically compiling is complex, etc.). But for cases like this, many SQLite extensions I write in Rust are just light wrappers around extremely high quality Rust crates (like jiff), which makes my life easier and it "good enough"

Onavo 2y ago
> slightly slower Because of the FFI overhead?
- alexgarcia-xyz 2y ago
  I think so - I wrote my own Rust FFI bindings for SQLite extensions, and I tried to make it as fast as possible, but a "hello world" extension was still 10-15% slower in Rust than C[0]. That being said, it depends what the extension does - a "hello world" extension mainly just calls the same SQLite C APIs over and over again, so the small Rust layer makes it a bit slower. However, my Rust extensions for regex[1] and CSV parsing[2] are usually faster than the C counterparts, mostly due to less memory allocations and batching. It's not a 1:1 comparison (both extensions have slightly different APIs and features), but I'd say a lot of "real world" features available in Rust can be faster than what's available in C. That being said, I'm sure someone could write their own faster CSV or regex extension in C that is faster than the Rust ones. But it's a ton of work to do that from scratch, and I find wrapping a pre-existing Rust implementation to be much easier [0] https://github.com/asg017/sqlite-loadable-rs?tab=readme-ov-file#benchmarks https://github.com/asg017/sqlite-loadable-rs?tab=readme-ov-f... [1] https://github.com/asg017/sqlite-regex https://github.com/asg017/sqlite-regex [2] https://github.com/asg017/sqlite-xsv https://github.com/asg017/sqlite-xsv
- greatgib 2y ago