4 ms·

sqlean: A set of SQLite extensions

236 points by chmaynard 4y ago

deleted 4y ago
[deleted]
srcreigh 4y ago
Very cool. with the define module the last annoyance with SQLite can go away, I can easily define functions to convert between epoch and iso times.
tarkin2 4y ago
https://github.com/nalgeon/sqlean/blob/main/docs/define.md https://github.com/nalgeon/sqlean/blob/main/docs/define.md I've been considering using that: define Anyone have any experience with it?
- 0x09 4y ago
  I'm the author of the extension that the vtab and define function in that module were adapted from. It allows you to create something like a parameterized view, but the way it works is fairly simple: a prepared statement is created from the provided SQL on the same db connection as the vtab, and is executed each time the virtual table is queried. Parameters can be supplied as constraints (or using table valued function syntax) and the results of executing the statement are the row values. Did you have any questions in particular?
chmaynard 4y ago
It looks like macOS Ventura won't load a .dylib from this set until you approve it in Privacy & Security settings.
- mmastrac 4y ago
  cd to the directory with all the dylibs and run this: xattr -d com.apple.quarantine *
chmaynard 4y ago
Previous discussion: https://news.ycombinator.com/item?id=26683832 https://news.ycombinator.com/item?id=26683832
- deleted 4y ago
  [deleted]
- hummus_bae 4y ago
  Is this duplicative of SQLite's own FTS functions? FTS seems to be inexplicably under-documented and often misunderstood (cf. https://www.sqlite.org/fts3.html https://www.sqlite.org/fts3.html , where it says: "Note that SQLite will process an SQL SELECT statement tree against an FTS table in three phases: ... The final phase rewrite the query from the inner join into an outer join.", and https://www.sqlite.org/indexing_ft_only.html https://www.sqlite.org/indexing_ft_only.html , where it says: "the bookwith table can be faster if you omit the where clause and just ask for the full-text results for all of rows of bookwith." ? And which is more useful for general-purpose website full-text search -- FTS, or a table with a _column called sqlite_fts_index=... ?
samwestdev 4y ago
Wait, there is no Unicode support in SQLite?
- cldellow 4y ago
  Not by default. You can build it with the SQLITE_ENABLE_ICU flag to get Unicode support.
- HideousKojima 4y ago
  There is, the extension listed there is specifically for case-insensitive comparison of unicode strings.
- simonw 4y ago
  That depends on what you mean by "unicode support". https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html notes that: > TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). But if you want unicode-aware collations, LIKE operations etc you need this extension: https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt You can compile this in too, but from a quick review none of my various SQLite installations seem to have been compiled with the SQLITE_ENABLE_ICU flag.
Gys 4y ago
For cross compiling a Golang application I used this alternative: https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib
jmiskovic 4y ago
From my limited exploring this is mostly noise. For example, math lib is taken from recent sqlite and backported, useful only if your sqlite lib is more than 2 years old. Quite a few extensions are taken from 'ext' dir in sqlite sources. Others are redundant and trivial; why use a specialized cube-root extension when you can directly calculate pow(_, 1/3)? Adding this collection as a project dependency would be opposite of "lean". Better to manually collect the functions you need, favoring the well maintained and tested sqlite code base over other sources.
- cldellow 4y ago
  I use sqlean for the same reason that I use any pre-built library: it makes my life easier. sqlean's existence provides the kernel around which other things in the ecosystem can coalesce. For example, karlb's sqlite-sqlean pip module [1], which makes it easy to get these extensions in any Python project in a cross-platform manner. I've used the math library myself recently: the SQLite in my distro is 3.31. I could install the tooling necessary to build a new SQLite, or I could use this project. I also use the crypto library in my datasette-ui-extras library, which can run on a variety of end-user platforms. It's nice not to have think about the packaging myself. To each their own, of course! But for me, calling this "mostly noise" is a disservice to its maintainers. [1]: https://pypi.org/project/sqlite-sqlean/ https://pypi.org/project/sqlite-sqlean/
- simonw 4y ago
  It can be surprising difficult to upgrade to a more recent SQLite release, depending on your operating system and which language bindings you are using. I still haven't found a good, reliable method of upgrading the SQLite version that is made available to Python's "sqlite3" standard library module for example, that works reliably across Linux and macOS. https://til.simonwillison.net/sqlite/ld-preload https://til.simonwillison.net/sqlite/ld-preload is one mechanism I've explored, but it's not ideal. As such, extensions which package stuff that you could get in SQLite core if you had a good way of recompiling that with extra options are really useful.
- nalgeon 4y ago
  I don't expect anyone to use all the incubator extensions (like the cube root you mentioned). They are in the incubator and not in the main set for a reason. Even the main set extensions are split into modules, rather than released as a single binary - so people can use them independently. As for 'noise'. Well, good luck implementing `regexp_substr` and `regexp_replace` using the well-maintained and tested sqlite codebase. Or streaming file I/O.
tiffanyh 4y ago
My #1 wish is for this branch to become main (WAL2 + BEGIN CONCURRENT) https://www2.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2 https://www2.sqlite.org/src/timeline?r=begin-concurrent-pnu-...
- pstuart 4y ago
  You're far from alone there. IIRC, a recent comment in the forum implied that this could be a very long wait.
  - tiffanyh 4y ago
    Do you recall why? I can't find the thread.
    - pstuart 4y ago
      They take a very conservative approach towards reliability and won't enable features unless they feel it's rock solid. It's a small team and a big change. That said, the rate of refinements and new features is quite impressive.
notpushkin 4y ago
Some more cool SQLite extensions: https://github.com/asg017?tab=repositories&q=sqlite https://github.com/asg017?tab=repositories&q=sqlite (I love that the author makes those available as Python packages as well:) import sqlite3 import sqlite_regex conn = sqlite3.connect(':memory:') sqlite_regex.load(conn) conn.execute('select regex_version(), regex()').fetchone() # ('v0.1.0', '01gr7gwc5aq22ycea6j8kxq4s9
- karl42 4y ago
  Python packages are available for sqlean as well: https://github.com/karlb/sqlite-sqlean/ https://github.com/karlb/sqlite-sqlean/.
Something1234 4y ago
One of the issues I've had is using custom extensions on aws lambda with python. Even if you build them properly it really doesn't like to load extra native code.

236 points by chmaynard 4y ago

deleted 4y ago

[deleted]

Very cool. with the define module the last annoyance with SQLite can go away, I can easily define functions to convert between epoch and iso times.

tarkin2 4y ago

https://github.com/nalgeon/sqlean/blob/main/docs/define.md https://github.com/nalgeon/sqlean/blob/main/docs/define.md I've been considering using that: define Anyone have any experience with it?

0x09 4y ago
I'm the author of the extension that the vtab and define function in that module were adapted from. It allows you to create something like a parameterized view, but the way it works is fairly simple: a prepared statement is created from the provided SQL on the same db connection as the vtab, and is executed each time the virtual table is queried. Parameters can be supplied as constraints (or using table valued function syntax) and the results of executing the statement are the row values. Did you have any questions in particular?

chmaynard 4y ago

It looks like macOS Ventura won't load a .dylib from this set until you approve it in Privacy & Security settings.

mmastrac 4y ago
cd to the directory with all the dylibs and run this: xattr -d com.apple.quarantine *

chmaynard 4y ago

Previous discussion: https://news.ycombinator.com/item?id=26683832 https://news.ycombinator.com/item?id=26683832

deleted 4y ago
[deleted]
hummus_bae 4y ago
Is this duplicative of SQLite's own FTS functions? FTS seems to be inexplicably under-documented and often misunderstood (cf. https://www.sqlite.org/fts3.html https://www.sqlite.org/fts3.html , where it says: "Note that SQLite will process an SQL SELECT statement tree against an FTS table in three phases: ... The final phase rewrite the query from the inner join into an outer join.", and https://www.sqlite.org/indexing_ft_only.html https://www.sqlite.org/indexing_ft_only.html , where it says: "the bookwith table can be faster if you omit the where clause and just ask for the full-text results for all of rows of bookwith." ? And which is more useful for general-purpose website full-text search -- FTS, or a table with a _column called sqlite_fts_index=... ?

samwestdev 4y ago

Wait, there is no Unicode support in SQLite?

cldellow 4y ago
Not by default. You can build it with the SQLITE_ENABLE_ICU flag to get Unicode support.
HideousKojima 4y ago
There is, the extension listed there is specifically for case-insensitive comparison of unicode strings.
simonw 4y ago
That depends on what you mean by "unicode support". https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html notes that: > TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). But if you want unicode-aware collations, LIKE operations etc you need this extension: https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt https://www.sqlite.org/src/doc/trunk/ext/icu/README.txt You can compile this in too, but from a quick review none of my various SQLite installations seem to have been compiled with the SQLITE_ENABLE_ICU flag.

Gys 4y ago

For cross compiling a Golang application I used this alternative: https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib

jmiskovic 4y ago

From my limited exploring this is mostly noise. For example, math lib is taken from recent sqlite and backported, useful only if your sqlite lib is more than 2 years old. Quite a few extensions are taken from 'ext' dir in sqlite sources. Others are redundant and trivial; why use a specialized cube-root extension when you can directly calculate pow(_, 1/3)? Adding this collection as a project dependency would be opposite of "lean". Better to manually collect the functions you need, favoring the well maintained and tested sqlite code base over other sources.

cldellow 4y ago
I use sqlean for the same reason that I use any pre-built library: it makes my life easier. sqlean's existence provides the kernel around which other things in the ecosystem can coalesce. For example, karlb's sqlite-sqlean pip module [1], which makes it easy to get these extensions in any Python project in a cross-platform manner. I've used the math library myself recently: the SQLite in my distro is 3.31. I could install the tooling necessary to build a new SQLite, or I could use this project. I also use the crypto library in my datasette-ui-extras library, which can run on a variety of end-user platforms. It's nice not to have think about the packaging myself. To each their own, of course! But for me, calling this "mostly noise" is a disservice to its maintainers. [1]: https://pypi.org/project/sqlite-sqlean/ https://pypi.org/project/sqlite-sqlean/
simonw 4y ago
It can be surprising difficult to upgrade to a more recent SQLite release, depending on your operating system and which language bindings you are using. I still haven't found a good, reliable method of upgrading the SQLite version that is made available to Python's "sqlite3" standard library module for example, that works reliably across Linux and macOS. https://til.simonwillison.net/sqlite/ld-preload https://til.simonwillison.net/sqlite/ld-preload is one mechanism I've explored, but it's not ideal. As such, extensions which package stuff that you could get in SQLite core if you had a good way of recompiling that with extra options are really useful.
nalgeon 4y ago
I don't expect anyone to use all the incubator extensions (like the cube root you mentioned). They are in the incubator and not in the main set for a reason. Even the main set extensions are split into modules, rather than released as a single binary - so people can use them independently. As for 'noise'. Well, good luck implementing `regexp_substr` and `regexp_replace` using the well-maintained and tested sqlite codebase. Or streaming file I/O.

tiffanyh 4y ago

My #1 wish is for this branch to become main (WAL2 + BEGIN CONCURRENT) https://www2.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2 https://www2.sqlite.org/src/timeline?r=begin-concurrent-pnu-...

pstuart 4y ago
You're far from alone there. IIRC, a recent comment in the forum implied that this could be a very long wait.
- tiffanyh 4y ago
  Do you recall why? I can't find the thread.
  - pstuart 4y ago
    They take a very conservative approach towards reliability and won't enable features unless they feel it's rock solid. It's a small team and a big change. That said, the rate of refinements and new features is quite impressive.

notpushkin 4y ago

Some more cool SQLite extensions: https://github.com/asg017?tab=repositories&q=sqlite https://github.com/asg017?tab=repositories&q=sqlite (I love that the author makes those available as Python packages as well:) import sqlite3 import sqlite_regex conn = sqlite3.connect(':memory:') sqlite_regex.load(conn) conn.execute('select regex_version(), regex()').fetchone() # ('v0.1.0', '01gr7gwc5aq22ycea6j8kxq4s9

karl42 4y ago
Python packages are available for sqlean as well: https://github.com/karlb/sqlite-sqlean/ https://github.com/karlb/sqlite-sqlean/.

Something1234 4y ago

One of the issues I've had is using custom extensions on aws lambda with python. Even if you build them properly it really doesn't like to load extra native code.