3 ms·

Sqlpkg – SQLite Package Registry

136 points by amadeuspagel 3y ago

fbdab103 3y ago
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/ https://antonz.org/sqlean-py/
- nalgeon 3y ago
  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.
  - fbdab103 3y ago
    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.
shrubble 3y ago
The simplicity of tree-navigation in "closure" is pretty amazing: https://github.com/nalgeon/sqlean/issues/27 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 ...
- nalgeon 3y ago
  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_expressions https://sqlite.org/lang_with.html#recursive_common_table_exp...
  - smartmic 3y ago
    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.
jkrubin 3y ago
This is awesome! I adore SQLite and always find bringing in extensions to a new machine a bit annoying.
pachico 3y ago
> 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.
sroussey 3y ago
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.

136 points by amadeuspagel 3y ago

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/ https://antonz.org/sqlean-py/

nalgeon 3y ago
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.
- fbdab103 3y ago
  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.

shrubble 3y ago

The simplicity of tree-navigation in "closure" is pretty amazing: https://github.com/nalgeon/sqlean/issues/27 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 ...

nalgeon 3y ago
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_expressions https://sqlite.org/lang_with.html#recursive_common_table_exp...
- smartmic 3y ago
  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.

jkrubin 3y ago

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

pachico 3y ago

> 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.

sroussey 3y ago

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.