[HN Gopher] SQLite Plus: Missing SQLite functions
___________________________________________________________________
SQLite Plus: Missing SQLite functions
Author : tosh
Score : 138 points
Date : 2021-04-03 19:13 UTC (3 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| [deleted]
| daitangio wrote:
| Take also a look at
| https://github.com/daitangio/sqlite_ora_functions
|
| A similar project python-based
| iagovar wrote:
| Related: https://duckdb.org/
|
| It's OLAP, not OLTP like SQLite.
| jolux wrote:
| Does OLAP vs OLTP describe a real technological difference or
| just a description of the use cases a specific tool is trying
| to address?
| gigatexal wrote:
| Generally OLAP databases are columnar or otherwise optimized
| for batch and aggregations.
| pm90 wrote:
| Yes it does describe a technological difference.
| Specifically, the kind of tasks that they are good for.
|
| My very crude shorthand is that olap == optimized for time
| series (usually column oriented) and oltp == optimized for
| transactional processing (row oriented).
| CharlesW wrote:
| The "description of the uses cases" answer can also be a
| reasonable shorthand for databases which support both,
| which allows the DBA to optimize on a table-by-table (and
| even column-by-column) basis.
| Hahalolomg wrote:
| Are you guys % sure about downloading? It said for me haha you
| now have a virus and then it logged me out of my phone for 6
| minutes, I was like why did they do that. Please do not
| download this GitHub app!!!
| runningmike wrote:
| ...
| Hahalolomg wrote:
| Hi
| chunkyks wrote:
| I'm still just absurdly happy that it now has a bunch of math
| functions in the latest release:
| https://sqlite.org/lang_mathfunc.html
| dvfjsdhgfv wrote:
| Out of curiosity: what is your use case and why do you prefer
| to use them in the DB instead of the application itself?
| yawaramin wrote:
| This is a really good idea. I'm using SQLCipher right now but
| might swiitch over as I don't really use the encryption feature.
| josteink wrote:
| For me, the one thing I need to plug SQLite in at work us right
| outer join.
|
| Kinda disappointed this doesn't fix that.
| wayneftw wrote:
| After CSV files as virtual tables, I'd like to see folders of
| JSON files as virtual tables. I know there's a whole virtual
| filesystem kit based around SQLite but what I want is simpler.
| foepys wrote:
| I don't think that a folder full of JSON files is actually
| simpler. There are SQLite drivers for nearly every language out
| there and querying SQLite is not only very easy and fast but
| it's also all contained in a single file you can copy around.
|
| What do you want to gain by using JSON files specifically?
| Unless you want a document database but then SQLite is the
| wrong kind of tool anyways.
| wcarss wrote:
| I read it as "I already have or can easily get a folder of
| JSON files (each representing a table); I want to open them
| up in SQLite with nearly zero effort."
|
| That should be hackable with something like json2csv[1], but
| I imagine the intent is a desire for something supported,
| rather than awfully hacked up.
|
| 1 - https://github.com/zemirco/json2csv
| foepys wrote:
| That makes way more sense. GGP is probably meaning exactly
| that, specifically vsv from the OP that I missed initially.
|
| https://github.com/nalgeon/sqlite-
| plus/blob/main/docs/vsv.md
| willvarfar wrote:
| Not poster, but also often have large numbers of json-ld
| files. Json events are really common, and saving events to
| files is really common, and wanting to query them is also
| common.
| slt2021 wrote:
| maybe storing files in mongodb instead of a filesystem can
| be solution?
| tyingq wrote:
| SQLite doesn't compile it in by default, but there is a virtual
| table CSV implementation in the ./ext folder:
| https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/mi...
|
| Similar for regex:
| https://sqlite.org/src/file?name=ext/misc/regexp.c&ci=trunk
|
| And percentiles:
| https://sqlite.org/src/file?name=ext/misc/percentile.c&ci=tr...
| pdimitar wrote:
| How are these enabled if they are already compiled in? Or are
| they automatically enabled if they are compiled in?
| [deleted]
| foxhop wrote:
| The official binary build process flags them off, likely.
| smitty1e wrote:
| The VSV extension has additional arguments that may be useful
| in some situations:
|
| https://github.com/nalgeon/sqlite-plus/blob/main/docs/vsv.md
| mutagen wrote:
| There's also https://cgsql.dev/
|
| This allows for stored procedures in a T-SQL variant that gets
| compiled to C. It is on my list to check out but I don't have any
| experience with it yet.
| dreix wrote:
| A libicu free Unicode extension. First time I hear about that and
| just yesterday I was slightly bothered by the prospect of
| compiling sqlite with icu again. Thank you to whoever wrote the
| extension, I'll try it out soon.
| rgovostes wrote:
| Some time ago I joined a project that was recording data logs in
| Protobuf format. I demonstrated that we could log the serialized
| Protobuf structures to a SQLite database and then use a plugin to
| query across the message fields:
| https://github.com/rgov/sqlite_protobuf
|
| Learning how to use the SQLite extension API was a little mind-
| expanding but it worked like a charm.
| skissane wrote:
| One of the things I really like about SQLite is the fact that it
| is in the public domain.
|
| But most third-party extensions, like this one, aren't. (This
| extension is MIT licensed.) If you think SQLite being in the
| public domain is a positive, then that's a positive these
| extensions lack.
|
| (Or a public domain near-equivalent license like Unlicense or
| CC0, which contains an ultra-permissive copyright license for
| those jurisdictions which don't allow one to voluntarily put
| stuff in the public domain.)
| BorisTheBrave wrote:
| MIT is already an extremely permissive license. Seems kinda
| mean to complain that something is merely 90% good because you
| are comparing to something 99% good.
|
| What sort of problems do you envisage having with an MIT
| license that public domain would fix?
| skissane wrote:
| So, if you include N different MIT-licensed libraries in your
| app, you have to remember to ship N different third party
| copyright licenses in your binary (or documentation shipping
| with the binary). And you need to keep an eye out if they
| change - if you add or remove a third party library, or if a
| third party library adds the name of a new contributor to its
| copyright notice.
|
| It gets even worse when you consider the family of MIT-like
| licenses, that might all have fundamentally the same terms,
| but with slightly different wording. Then you can have to
| ship multiple variant licenses too.
|
| Public domain (and public domain-equivalent licenses) you
| don't have to worry about this, because there is no
| requirement to include copyright notices or copy of license
| text.
|
| I'm not saying you shouldn't credit people whose
| contributions you use, but you can make that a non-legal
| expectation of courtesy rather than a make-work legal
| requirement.
| otachack wrote:
| After reading SQLite's page on their public domain approach
| they seem to go through great lengths to make sure it stays
| public domain.
|
| https://sqlite.org/copyright.html
|
| Including having their closed contributors sign affidavits
| and stored in a physical location. This may be what OP is
| referring to.
|
| Compare that with an MIT license with no "hardening" like
| affidavits so that it's easy to mutate to another license
| type, I can see their point.
|
| Interesting stuff since I never knew this "public domain"
| approach before!
| zoomablemind wrote:
| Without a comprehensive test suite, this set of extension
| libraries is rather a black box.
|
| Sure, one can look over the actual code to make sense if the
| functions do what they should, but one of main SQLite's features
| is its in-depth testing.
|
| It'd be nice to have the same testing approach extended to this
| set of utilities.
___________________________________________________________________
(page generated 2021-04-03 23:00 UTC)