[HN Gopher] sqlean: A set of SQLite extensions
___________________________________________________________________
sqlean: A set of SQLite extensions
Author : chmaynard
Score : 174 points
Date : 2023-02-28 14:14 UTC (8 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| chmaynard wrote:
| Previous discussion:
| https://news.ycombinator.com/item?id=26683832
| [deleted]
| tarkin2 wrote:
| https://github.com/nalgeon/sqlean/blob/main/docs/define.md
|
| I've been considering using that: define Anyone have any
| experience with it?
| srcreigh wrote:
| 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.
| tiffanyh wrote:
| My #1 wish is for this branch to become main (WAL2 + BEGIN
| CONCURRENT)
|
| https://www2.sqlite.org/src/timeline?r=begin-concurrent-pnu-...
| pstuart wrote:
| You're far from alone there. IIRC, a recent comment in the
| forum implied that this could be a _very_ long wait.
| tiffanyh wrote:
| Do you recall why?
|
| I can't find the thread.
| pstuart wrote:
| 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.
| [deleted]
| Gys wrote:
| For cross compiling a Golang application I used this alternative:
| https://github.com/multiprocessio/go-sqlite3-stdlib
| notpushkin wrote:
| Some more cool SQLite extensions:
| 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
| jmiskovic wrote:
| 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.
| simonw wrote:
| 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 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.
| cldellow wrote:
| 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/
| nalgeon wrote:
| 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.
| chmaynard wrote:
| It looks like macOS Ventura won't load a .dylib from this set
| until you approve it in Privacy & Security settings.
| mmastrac wrote:
| cd to the directory with all the dylibs and run this:
|
| xattr -d com.apple.quarantine *
| samwestdev wrote:
| Wait, there is no Unicode support in SQLite?
| HideousKojima wrote:
| There is, the extension listed there is specifically for case-
| insensitive comparison of unicode strings.
| cldellow wrote:
| Not by default. You can build it with the SQLITE_ENABLE_ICU
| flag to get Unicode support.
| simonw wrote:
| That depends on what you mean by "unicode support".
|
| 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
|
| 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.
___________________________________________________________________
(page generated 2023-02-28 23:01 UTC)