Sqlpkg – SQLite Package Registry | Hacker News Reader

Sqlpkg – SQLite Package Registry

sqlpkg.org

136 pointsamadeuspagel9 comments

I am amused by the number written by Dr. Hipp. I appreciate his desire to keep core small and stable, but some of these would be great to get promoted into the base package.

News to me, there is a Python package[0], sqlean-py, which makes it trivial to bring in these extensions.

[0] https://antonz.org/sqlean-py/

Sometimes they do! The `unhex` function, for example, has finally made it's way into the core function set. So did `json1`.

Some text functions like `trim` have been added recently, and there will even be a (long-awaited) `timediff` function in the next release.

Oh no doubt, things are always improving. While I have not yet used it, happy for the recent json enhancements (personally, I would always prenormalize to standard JSON instead of naively ingesting JSON5).

Datetime is definitely one of my SQLite sore spots, so happy to see improvements there. I have to consult the docs + experimentation every time I need to do something with time manipulation. Even then, I usually left a bit disappointed with the available functionality.

The simplicity of tree-navigation in "closure" is pretty amazing: https://github.com/nalgeon/sqlean/issues/27

Given that SQLite supports huge sizes for text, you could almost use the above as a way to create an outliner, with everything stored in the database itself ...

I believe you can do this even without the `closure` extension, using standard SQL `with recursive`.

https://sqlite.org/lang_with.html#recursive_common_table_exp...

There is a valuable section about alternative tree models in the book "SQL Antipatterns" from Bill Karwin.

In the end, he compares five hierarchical data designs. Unsurprisingly, all have pros and cons and it is a (somehow advanced) matter of picking the right tool for the job. Closure tables may in fact be the lesser know variant, adjacency lists and recursive queries one of the better knows. But there are also path enumerations and nested sets.

> Bloom filter — a fast index to tell if a value is probably in a table or certainly isn't.

I will start using this way to communicate "true/false negative/positive" that always requires a further explanation. Clear statements are part of the user experience.

The main SQLite changes that are paid have no real extension: encrypted and compressed data spaces.

There are encrypt and compress functions, but nothing transparent.

This is awesome!

I adore SQLite and always find bringing in extensions to a new machine a bit annoying.

Reply on news.ycombinator.com