The "DB" guy at my workplace is regularly asked about certain stats from the production db, he runs some query, dumps it in a csv-formatted file and mails that to whoever asked for it.
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).
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.
If a table is constructed WITHOUT ROWID, then its spatial locality will reflect the consecutive order implied by the primary key. Range-delimited queries are extremely fast.
In effect, SQLite already is both an OLTP and OLAP database.
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.
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.
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.
Flatbuffers would be better as it doesn't need to deserialize at all (I think protobuf supports to partially deserialization nowadays?). I did something similar with https://github.com/liuliu/dflat too!
I attempted something similar with large rosbag files, but the performance gain wasn’t enough to make the initial processing worth it. That said, I’d like to try again with duckdb as that seems like it might be better suited to time series analysis.
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.)
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?
That's what I was talking about. Crawford said in a talk he had to make an amended licenses for corporations (namely, IBM), who had lawyers that needed to be able to get around the evil restriction.
Likely referring to the [JSON license](https://www.json.org/license.html) and not the jQuery license. The JSON license has this problematic (for many businesses) phrase: The Software shall be used for Good, not Evil.
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 in a newer version.
It gets even worse when you consider that there are MIT-like licenses, which can have fundamentally the same terms as MIT, but with slightly different wording. Then you can have to ship multiple variant license texts 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.
Thanks for the post, this made me research SQLite's public domain approach and I never heard of it before! Something I'll keep in mind moving forward as I always thought MIT was the "end all be all"
By itself it is wrong. Public domain is still a license. License let you do anything is a must as copyright is granted auto. You need to license. If the public domain does not license, there is no public domain (except those fall into it after certain number of years).
You may sense why the need of effort by SQLite to confirm its public domain status and left other not compiled in.
Another issue is the free vs open source debate. Does it have to be pure. Another time but SQLite seems allow both, just not in its core.
I fear this comment will come across as pedantic, but I think it's a distinction worth highlighting. I am not a lawyer, but I care about this stuff and have studied it.
My (somewhat educated) understanding is that public domain is a rejection of copyright, and is therefore the lack of a license. Rather that saying "I'm the owner, and you can do whatever you want", it instead says "I am not the owner", and indeed, even further "No one is the owner". Naturally, this can only be said by the person who would have held the copyright, so paperwork is still necessary to show this.
Stanford[0] has a page about this that's pretty interesting (among other things, it mentions public domain works can be copyrighted again if they are gathered into collections, which I find fascinating). The intro text on public domain says this:
> The term “public domain” refers to creative materials that are not protected by intellectual property laws such as copyright, trademark, or patent laws. The public owns these works, not an individual author or artist. Anyone can use a public domain work without obtaining permission, but no one can ever own it.
In the case of sqlite, I think it falls under "dedicated works"[1], which is one of the four ways they mention that leads to a work falling into the public domain.
If you read the CC0 – https://creativecommons.org/publicdomain/zero/1.0/legalcode – it is structured into two parts, a waiver (in section 2) and a fallback license (in section 3). The license is certainly a license, but is the waiver a license? Especially since the license, by its terms, only applies if the waiver does not – if the waiver is judged valid in some legal jurisdiction, the license does not apply.
Some of the language in section 2 could even be read as settling a trust, with the general public as the beneficiary, the copyright as the trust property, and the copyright owner as both settlor and trustee.
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.
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.
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.
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.
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...