SQLite as an Application File Format (2014)
sqlite.org
sqlite.org
I wish a lot of metadata were defined as a database schema, and sqlite lends itself so willingly to becoming the archive/header.
Does sqlite do internal gunzip compression?
I do understand we have MHTML:
Apparently CSV is actually quite hard to parse.
If you are including CSV functionality in something you work on, please read and follow this (tiny) spec!
A bigger issue is that Excel tends to write large numbers in scientific notation, which is a common issue handling price lists. E.g. it'll turn EAN numbers into 6.2134e+11, losing most of the number. Then you have to go back to the XLS file and change the column type into text and exporting it again as CSV. As this is lossy you can't fix it when receiving such a file.
Something like the SQL Server Import/Export Wizard but being able to write SQLite files would be very handy.
It is fixed now... but I'm now wondering whether SQLite wasn't more correct in the first place...
Are you sure it wasn't an injection attempt of some kind?
But more likely is twisted creativity. It seems that some businesses are still using ancient systems, based on COBOL, AS/400, etc. There's resistance to changing legacy code. So when business changes require additional data fields, fields sometimes get subdivided. So a field that originally contained stuff like |foo| now contains stuff like |"foo","bar,baz"| or whatever. That works, because there's nothing like CSV in the data system. But when someone tries a CSV export, you get garbage.
When sharing tabular data in text format, he always preferred TSV because commas were everywhere in the material we were working with, but tab characters were really rare.
Is there a reason why nobody uses these? Did someone work out back in the 90s they were pure evil and we've just never used them since?
There is an excellent description in "C Programmer's Guide to Serial Communications" by Joe Campbell.
Those codes were originally for just such a purpose, but as others point out there was a bootstrapping problem. At this point, if Excel doesn’t support it, it’s not going to gain traction.
So yes, TSV. However, I've seen TSV with spurious tabs :(
Sometimes I ended up pushing for |-delimited data. Or even fixed-width format :)
But one can usually work around this.
I made the mistake once and learned.
CSV isn't perfect, but it provides a ton of flexibility, for example, CSVs can be streamed or support parallel segmented download across a network with useful work possible during the transfer. The format is so simple that it can approach almost free to parse (see e.g. my own https://github.com/dw/csvmonkey ).
CSV is also distinguished in that regular home users with spreadsheet programs can usually do most things a developer can do with the same file. For me user empowerment trumps all other goals in software, including warts. Things like JSON, XML or SQLite definitely don't fit in that category, although I guess SQLite is at least better due to the wide availability of decent GUIs for it.
Finally as a data transfer format, SQLite has the potential to be massively inefficient. Done incorrectly it can ship useless indexes that can inflate size >100%, and even in the absence of those, depending on how amenable the data is to being stored in a btree and the access patterns used to insert it, can leave tons of wasted space inside the file, or AFAIK even chunks of previously deleted data.
That's an extreme position to take, particularly since the SQLite code is public domain. Furthermore it's one of the formats recommended by the Library of Congress for archival/data preservation:
This part unfortunately isn't a position, it's absolute. It's hard to imagine a situation where as a developer we would not have access to a C runtime or for any reason whatsoever would not be able to use SQLite, but the hard dependency on its code is real, and represents a real hazard in the wrong environment. A super easy example would be parsing data on say, a tiny microcontroller on an IOT device. This can start to hurt quickly:
> Compiling with GCC and -Os results in a binary that is slightly less than 500KB in size
Open formats at least give you the option of implementing whatever minimal hack is necessary to finish your job without say, introducing some intermediary to do an upfront conversion, and at least for this reason SQLite cannot really be considered a perfectly universal format
>"as closed as wrapping something in a word document."
Sure CVS makes it trivial to waste your time reinventing the wheel, making your own parser. The situations were there are technical limitations that prevent the use of sqlite are becoming vanishingly rare. (Not to mention the resources necessary to use sqlite is unrelated to how many implementations there are or whether it's 'open' or 'closed'.)
2) SQLite has no standard. The same is true for CSV in practice. At least SQLite has a high quality reference implementation.
3) It’s a shame that SQLite doesn’t have a standard of some sort.
I mean, it's a point, but I don't think anybody is saying that SQLite should replace all data storage formats everywhere. If you're just storing a few dozen short text strings with keys, plain text is fine. I don't think you'd want to have a JSON parser on a tiny microcontroller either.
> This part unfortunately isn't a position, it's absolute.
It's also false. I know of SQLJet which is a pure java implementation, there may be others. But in the end, the SQLite format being well defined and documented [1], a sure-fire way to read an SQLite file is writing the code to read an SQLite file. Since SQLite a rock-solid, public domain, portable C library, it might not be the best idea to do that, but it is completely feasible. No one stops you from "implementing whatever minimal hack is necessary to finish your job" while using the SQLite format.
But more to the point, Mozilla's gripe was with SQLite's API, not with the file format used by SQLite. I can't find any source for the file format used by SQLite being the problem that tripped up WebSQL.
It's documented pretty well: https://www.sqlite.org/fileformat.html
I suppose that doesn't make it an open standard, but it hasn't changed much.
SQLite is a Recommended Storage Format for datasets according to the US Library of Congress. https://www.loc.gov/preservation/digital/formats/fdd/fdd0004...
Not saying it's useful but you saying "only way to read SQLite is SQLite" is hyperbole.
Which users? I’m curious.
Cross platform, fully documented, Fred software, multiple implementations vs closed proprietary single platform commercial software. Really?
Currently building software for the ATO (Single Touch Payroll) which uses SBR (https://en.wikipedia.org/wiki/Standard_Business_Reporting). The SBR project is listed as having on-going problems, and has cost the ATO ~$AUD1b to date (https://en.wikipedia.org/wiki/List_of_failed_and_overbudget_...). One of the reasons cited is that it uses XBRL (https://en.wikipedia.org/wiki/XBRL). Now imagine if it used sqlite...
I can easily imagine how painful it is for you to process XBRL from scratch, but it's not crazy to exploit the existing infrastructure.
Of course if you give a project to IBM I wouldn't be surprised if it costs a billion dollars, especially given they know roughly nothing about XBRL...
Here's Google's latest: https://abc.xyz/investor/static/documents/xbrl-alphabet-2018...
Please do keep in mind that this is a sort of XML key-value database with a number of keys standardized, and some level of rules defined that say "if a bank lends Volkswagen money and it uses 12% of that money to Google to run ads with a repo clause, you enter add A to value X and B to value Y". In other words, there's rules that define how complex financial data is entered into those standardized values. To find those rules, there's a SEC textbook that you wouldn't wish on your worst enemy, nothing about that in the files themselves.
There's a large directory at the SEC with the quarterly XBRLs for all US publicly traded companies. Used to be accessible over FTP until a little over a year ago.
Here it is: https://www.sec.gov/Archives/edgar/
XBRL files exist for all forms to be filed with the SEC. The ones you probably want are the 10-Q and 10-K ones (q = quarterly, k = no idea, but somehow means yearly)
(of course there's an entire industry of accountants essentially about hacking those rules, and therefore the meaning of those files. So let me give you a free quick 5 year experience in financial analysis: search for "GAAP vs non-GAAP", read 2 articles, decide the conspiracy theorists are less informative than the government, and just believe the SEC is at least trying. That doesn't mean nobody's lying, but GAAP vs non-GAAP is generally not what they're lying about)
Essentially each filing (called an instance) consists of a series of 'facts', each of which reports a single value and some metadata, and footnotes, which are XHTML content attached to facts. Fact metadata includes dimensions, which can specify arbitrary properties of a fact. So a fact might be e.g. 'profit' with metadata declaring it's in 2018, in the UK, and on beer, but all of those aspects would be defined by a specific set of rules called a taxonomy. You can create a taxonomy for any form of reporting you want.
There's also a language, XBRL Formula, which allows taxonomies to define validation. It allows something semantically similar to SQL queries over the facts in an instance, with the resulting rows being fed into arbitrary XPath expressions.
Unfortunately the tools for working with XBRL are mostly quite expensive, which probably limits its application outside finance. Arelle is a free and fairly standards compliant tool that will parse, validate and render XBRL and even push the data into an SQL database, but it's written in Python and isn't very performant. (Although it's probably good enough for most uses since it's used as the backend for US XBRL filing.) I'm not sure if there are any open source tools to help with creating instances.
Also creating a taxonomy itself is quite challenging. There are (expensive) tools to help, and using them it's still quite challenging. For real-world taxonomies it usually involves a six or seven figure payment to one of the few companies with the right expertise.
In this sense, I prefer a SQL dump file.
I particularly liked Fake capacity USB sticks. I didn't even realize that was a think. I can't even...
For sqlite there's hardly an approvable tool for analysis of the data.
Excel is driving the world.
Some might think Access would be the more obvious choice, but for the average user Access is not an accessible tool (we don't even install it as part of the Office suite where I work) and they're much more comfortable in Excel.
*Note I work with software that regularly stores terabytes of data in sqlite.
How do you process that? The restrictions I had in my mind come from that it's infeasible to concurrently process a large dataset in mapreduce/bigquery style.
The systems I work with use it for backup data. We have many readers and writes. Some of those export a iscsi daemon that represents a block device from the backup that is then booted form.
Read more at: https://www.sqlite.org/sqlar.html
The nice thing about this is that, since this “sqlar” table is just one table in the file, and the commands work whether or not other such tables exist, a file can be an “SQLite archive” while also being a regular SQLite database containing other tables at the same time. It’s sort of like how Fireworks used to add special chunks to PNG—the file was still a PNG, but now it was also a Fireworks project. (But, in this case, the “chunks” aren’t opaque binaries, but rather are SQL tables you can manipulate using regular SQL queries.)
And, of course, the SQLar “standard” isn’t all that complex—it’s designed so that you can easily construct your own SQLite archive files just by issuing regular DDL+DML queries through the SQLite binding of your language of choice. (Though, if you want support for inserting files compressed—which the `sqlite ar` command supports extracting—you’ll need a zlib binding as well.)
A lot of hairy if-else code was made unnecessary.
SQLite is IMHO the best option for data serialization/persistence/interchange in many cases, especially when some data is a blob, or, say, a huge array of floating-point numbers that you need to store with full precision (which makes text-based formats clunky to deal with).
Is this still true by default? With write-ahead (wal) and shared memory (shm) enabled, it is no longer safe to consider only the database file. Pragmas exist to disable those, but people should be familiar of these features and use with care. The document should be updated to introduce these concepts.
> There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as an application file-format.
> The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close().
If you make your file format easy to work with then it only widens this moat.
One thing though: last time I did that, I ended up writing my own ORM/serialization library (with templates and all), but I wish I didn't have to.
Is there anything freely available out there that would allow one to store object hierarchies with the ease of boost::serialization, but into an SQLite EAV scheme?
Either way, I tend to think you'd probably be better off with sqlite since it would give you more flexibility (for instance if you later realize that your application possesses relational data.)
The linked page even has a case study of the OpenDocument file format that shows exactly how SQLite is better than a Zip file: https://www.sqlite.org/affcase1.html
> Newer machines are faster, but it is still bothersome that changing a single character in a 50 megabyte presentation causes one to burn through 50 megabytes of the finite write life on the SSD.
That's funny, since Firefox would burn through gigabytes of writing to SQLite. All comes down to quality of implementation.
There's also another standard called ZIP64 to allow for larger files (maximum 4GB in the original spec).
I was going to say that RAR is proprietary, but at least there's (non-FOSS) source code and good specs: https://www.rarlab.com/technote.htm
The actual horrible thing about zip is that there is no standard whatsoever for the encoding of filenames. So mangled filenames are common and most utilities don't even let you manually select the encoding if you know what it is.
After reading this document, well, I think I will give it a try. It might not be a perfect fit, but I don't quite like yamls.
And there's this simplified fork of K8S that replaces etcd with sqlite: https://github.com/ibuildthecloud/k3s/blob/master/README.md
howso ? ... beyond querying the database server-side ...
But if you exchange them, it's too easy for app developers to misuse the library causing user's data to be leaked to third parties, in free DB pages, and even in the way how exactly the DB is fragmented.
Moreover, it's a general issue with file formats that can encode the same data in multiple ways that, in addition to the data itself, the data file also incidentally encodes information about the process by which the data was encoded. This metadata is usually ignored and abstracted out by APIs for working with the data, so application developers tend to overlook it as a place where sensitive data can leak.
For comparison, you might consider fingerprinting API clients based on the order in which they send HTTP headers or keys in JSON objects, which can often be correlated with language or library versions in environments where the most convenient map structure is an arbitrarily ordered hash.
It's not necessarily easy to think of nefarious uses for this sort of information, but SQLite database fragmentation can reveal, albeit in rather rough detail, the order in and frequency with which changes were made.
Quite the opposite of "security by obscurity", in fact.
https://sqlite.org/pragma.html#pragma_secure_delete
Needs to be manually enabled though, as that doc says it's generally not enabled by default. :/
E.g. if you have a text document with 1 row per paragraph, fragmentation will reveal where it was edited, and what kinds or edits were made. You insert a bunch of images into the document, and the physical order of pages will reveal in which order they were inserted, even if you re-arrange them afterwards.
It's possible to workaround by careful programming, but still, very easy to screw up and leak data.
OTOH, with zipped XMLs people normally use instead, you overwrite the complete file each time, it's not fragmented and hopefully doesn't contain any extra info.
Indeed - just write a new file you export data to be shared with someone else.
Heck, for many use cases (e.g. config files), the save_to_file() function can always start by deleting the old file completely, creating the DB from scratch, and filling it with data from memory (essentially, treating the DB files as immutable).
Hardly a difficulty, no?
And proprietary file formats aren't guaranteed to NOT have the problems you described; the only apriori advantage is that their structure is opaque. Hence my "security by obscurity" remark if my understanding is correct.
There's no good way to distinguish. Add separate File/Export command? Users will forget to use it. Add a checkbox on file dialog? Similarly, users will forget to check it. It's very unreliable.
The only reliable way is treat every save as "export data to be shared with someone else".
> proprietary file formats
Very few left, the industry has moved towards open formats. For example, MS Word documents are zipped XMLs, ISO/IEC 29500.
Also looks like C++ API was designed in 90-s. Maybe that's my personal preference but I find it hard to work with, too much OOP but too few types.
Datasette (https://github.com/simonw/datasette) is a new tool to publish data on the web. It uses SQLite under the hood.
e.g. tables and schema in JSON.
A table as a list of row-objects, of attributes. (or more efficiently, an object of lists. Or, following the achema, just a list of rows, each a list of attribue's values).
1) Saving application state and config/settings (window layout, ... very easy to marshall XAML stage to an SQLite file) 2) Caching of common data sets to avoid unnecessary polling to the server of data that is accessed all the time (ie customer master data)
It works beautifully.
> Any application state that can be recorded in a pile-of-files can also be recorded in an SQLite database with a simple key/value schema like this:
CREATE TABLE files(filename TEXT PRIMARY KEY, content BLOB);
Also look at the related post which suggests doing precisely that - storing images inside SQLite blobs: https://www.sqlite.org/affcase1.html
- Direcory structure
- Zip file
- Tar file
- Sqlite db
Out of these, sqlite is the most compact, by far (also the fastest)
Having a single file per very large image (for mobile) makes transferring them to the phone over USB around ten times as fast. I wish I’d done it years ago.
If you can spread your data among many files, you probably should. Not everybody can.
And reading and writing can be even faster than if they were stored directly in the filesystem, https://www.sqlite.org/fasterthanfs.html
Even if you can in theory save some time with small files with sqlite, you would be back to read/write IO and userspace/kernel byte buffer switching.
And it probably wouldn't be faster than something like a mmaped file anyway (unless the SQLite Db is also mmaped).
https://www.sqlite.org/src/doc/trunk/src/test_onefile.c
https://www.sqlite.org/vfs.html
IIRC, during one of his talks D. Richard Hipp mentions this actually being done for real.
1: https://github.com/mapbox/mbtiles-spec 2: https://wiki.openstreetmap.org/wiki/MBTiles
I already have APIs that send SQLite database files over a socket: send(), sendfile(), writev(), and so on.
What I don't have is the ability to receive SQLite database files incrementally (stream): I always need enough disk and/or memory to store an SQLite database.
I can however, receive an XLS file (or even XLSX) incrementally and implement a streaming parser for it.
I have recently been moving to toml.
It's kind of sad that you seem to think that JavaScript is the only relevant language in existence.
Generally I find that people often choose config languages because of syntax preference, where in reality they should be thinking if what they are trying to express is data or behavior?
Ansible should have been just python. Yaml makes for an ugly programming language. Whereas python setup.py files should have been anything but python. It's a terrible source of metadata.
There are cases of course where the distinction is not that obvious. Is your nginx config data or a program/behavior? How about your CI config?
Second, if you load your config from a different language like JSON, you miss out on the benefits of your first language, like static typing. Static typing gives you nice things like build-time failures for invalid config and integrated configuration autocompletion in IDEs and editors.
Third, you can do things like merging environment-specific configs into a base config or programmatically filling in an array of values explicitly and in a way that's easy to follow. If you're using Fibonacci backoff, you can write "cfg.backoffRetryTimings = fibonacci(100)" instead of listing 100 numbers.
I am not sure I understand this, I have a feeling you are mixing "configuration" with "loading the configuration" here which is a slippery slope.
> you miss out on the benefits of your first language, like static typing
Again, you are confusing configuration with writing your application. You have a point here if you are your applications only user (e.g. server apps) and you have to recompile anyway.
> Third, you can do things like merging environment-specific configs into a base config
Again, I am not sure if your config file is the place to do that. I would argue that your configuration should be different in different environments instead of having a smart configuration that recognizes the environment and does a bunch of different things.
> or programmatically filling in an array of values explicitly and in a way that's easy to follow.
This is also a good point and is definitely a place where JSON falls short.
Good stuff.
2. One place where YAML is nice is if you want to have a user-edited file. JSON can be a pain to edit by hand due to a lack of comments and being so picky about where commas can and cannot go - YAML isn't perfect, but, it does help with issues like that.
3. There are languages outside of Javascript, and it's not possible to import a javascript file directly from most of those languages.
4. There is a difference between the file that you use to configure an app - for which JSON or YAML may be a good fit - and the format for which you export data - where JSON or YAML may or may not be a good fit.
5. JSON has some very real issues with different parsers not implementing the JSON corner cases the same way - which doesn't really matter, until you hit one of those cases and then it really matters.
6. JSON doesn't support streaming access to file data - it's not impossible to process a JSON file incrementally, but, it's hard to do as JSON is easiest used when you can load the whole thing into memory.
Recently, it began to dawn on me that maybe the problem is one of visual acuity required for discerning whitespaces. I always assumed that just because I could visualize a YAML hierarchy just as well as I could JSON, and could edit YAML without messing up the indentation, that others could too. But I've witnessed highly capable developers bang their heads against the keyboard trying to fix repeated parsing errors in YAML they've written. Perhaps not everyone can pick up whitespaces quite so easily...
The result was that they were at a loss trying to figure out the free-form map and array structures. They could not grasp when to use dashes (arrays) mixed with maps (array of maps) and would fail to indent structures all the time. Trying to create meaningful error reporting was very hard since their mistakes were also valid YAML.
YAML is definitely powerful but can become too complex too fast. And being a format full of sigils and its TIMTOWTDI "there's more than a way to do it" approach can easily enrage lean purists and complexity detractors, hence the hatred.
> Simplified Application Development
It's depend "application development" is a vast area...
> Single-File Documents
Not much related to SQlite, can be or cannot be done with nearly any format you like. Also remember as a good advise how worse go Catia v6 R2009 (Catia is the best CAD/CAE/CAM suite in the world, used anywhere from automotive to aerospace&defence, it's sole real competitor are Creo (formerly Pro-e) and Nx) when it start to offer single-file PLMs for "light usage"...
> High-Level Query Language
Depending on the format you choose you may have the best/highest level query language: your own apps programming language itself, so IMO may not be an SQLite plus at all
> Accessible Content
Any binary content is FAR LESS accessible than text
> Better Applications
Sorry, it have NO mining at all.
I like SQLite for many aspects but I certainly not look at it as a file format for most applications I use or imaging, for some usage is really nice, for others usages {Tokyo,Kioto}Cabinet is another nice format, for MANY other usage text files in various format, binary files in various formats (including classic pickle) may be better...