Live data from Hacker News

What if OpenDocument used SQLite? (2014)

sqlite.org

201–210 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#201
post #170

OpenDocument is zipped images and XML. Implying you parse the entire format and put it in RAM. And frankly I don't see how SQLite can improve this. Well XML isn't ideal, but it's zipped, so there's no huge penalty in size here. All benefits SQLite's article lists (and I love SQLite to death by the way) can be implemented by having SQLite be the runtime model of the document. On disk and in memory. But SQLite doesn't…

XML and Zip don't really do incremental updates, meaning the whole application file has to be written on save, meaning corruption can occur due to hiccups mid-write. Sqlite as a disk format and the right application implementation means you can't end up in a corrupted state. I think you can achieve the same thing with xml/zip and some rename shenanigans, but sqlite lets you get that in a single file on disk. Also if…

Incremental updates don't matter in a transport format.

The claim "it's basically free" isn't right, as for transport you need to VACUUM. And possibly COMPRESS too. And if you do that... might as well use the existing format. VACUUM completely rewrites the file from scratch. You can't do incremental updates in a VACUUMed file as it stops being VACUUMed, so you need to VACUUM it again to ensure minimal file size. Nothing is free.

ZIP also can be incrementally updated (file by file) by the way, I think MS Word uses this feature in some saves. But that's beside the point. You simply do not need incremental updates in a transport format.

I'm not sure what "hiccups mid-write" you're referring to. Any such hiccup that would damage an XML or ZIP file would also damage an SQLite file.

The distinction between a working disk file and a transport format are important. The working disk file is large, binary, messy, complex, optimized for quick look-ups and quick partial updates. If your word processor crashes, it can restore state from the working disk format in no time.

But the transport format needs to be small, readable, debuggable, simple, stable. And SQLite simply doesn't offer anything significantly superior in that department compared to the existing format. Especially nothing to justify the additional effort of changing an already working solution.

There's a reason "serialization" is called that, it's just serial data. No random access structures, no indices, single representation, often text-based. Throughout the decades, we've learned this is the best way to transport data of any kind. The messy/partial/polymorphic/cryptic/hyperoptimized/indexed formats are not for transport. They're intended to do work in, locally.

Re: What if OpenDocument used SQLite? (2014)

#203

Good article. Although one thing I do like about OpenDocument being just a bunch of XML files in a ZIP archive is that it is fairly easy to generate documents like spreadsheets without using a (potentially hefty) library which knows about the document format. I have a use case where users of a web service want to use data exported as a bunch of rows in a table in a variety of tools. Now, CSV with UTF-8 encoding is of…

As for [1], it is not really hardcoded, but depends on what is the value of localeconv()->decimal_point, if it is “,”, excel uses semicolons both in CSV files and formula expression language.

This used to be configurable when opening CSV/TXT file in excel (and still is in LibreOffice) but as a part of the overall UI dumbification was moved somewhere under the “Data” menu/ribbon tab (so you have to open new workbook and find the right option, or well, use LibreOffice if you value your time).

Re: What if OpenDocument used SQLite? (2014)

#204
post #86
post #6

I'm currently working on an application where I use SQLite as the file format. I want to keep a usual workflow for users where you can make edit to your document and it only changes the file when you save it. So to open a file I copy it into the :memory: database [1], then the user can do whatever manipulation they want and I can directly make the change in the database I don't need to have a model of the document ot…

Why do you use a secondary, volatile database ? Performance-wise you won't gain a lot more (we're talking about a user editing a file, so not even 1 write per second). A proposal: write directly, and automatically in the database. No more Save button. There are multiple advantages: - the system is crash-resistant. I like taking the approach of CouchDB where the only correct way to close the system is to crash it. Tha…

> I like taking the approach of CouchDB where the only correct way to close the system is to crash it.

The term you're looking for is (aptly named) crash-only software.[0]

0. https://en.m.wikipedia.org/wiki/Crash-only_software

Re: What if OpenDocument used SQLite? (2014)

#205

Earlier quoted context omitted.

Duplicate keys in a SQLite file sounds like an audacity bug. :(

Maybe. The SQLite list of gotchas [0] is quite something. NULLs in the PK? Sure. FKs don’t actually do anything unless you pass a PRAGMA? Why not? Etc. I could easily see someone not fully grasping just how much SQLite lets pass by default, and thus not having a test catch it. [0]: https://www.sqlite.org/quirks.html

[deleted]

Re: What if OpenDocument used SQLite? (2014)

#206

Earlier quoted context omitted.

> This isn't a concern for most software. It's not even a concern for the US Library of Congress, which defined SQLite as a recommended storage format for datasets alongside CSV, XML, and JSON.

But those are completely different uses of a storage format. Library of congress considers if someone a 100 years from now could write a new importer in whatever langauge/AI they might use by then. Office documents are something you send in email attachments to people you often barely know, and expect them to read it in whatever office system they have. And if the recipient uses e.g., Microsoft Word, OFD/Sqlite might…

It is true that it requires effort for the developers of a software program to support a given file format. Beyond that I'm not sure what your point is.

Re: What if OpenDocument used SQLite? (2014)

#207

ODT was designed to be standardised: while the predecessor format was very similar too, it relies very heavily on XHTML, SVG, and CSS, to name but three (there's a lot more). Without being able to call out to existing standards, the ODT spec itself would suddenly become massive. The effort to update the standards appears to be significant and hasn't progressed much in recent years already :/ I think realistically, an…

The specification is massive (840 pages) even though it is written in very terse way that does not really specify the effects and behavior, only the syntax.

On the other hand if one ignores few warts (explosion of local styles and text spans due to ooo:rsid attribute, non-sparse spreedsheets and weird mechanism for styling tables as a few examples) it is really well designed markup for this kind of document data that strikes right balance between it being semantic markup and representing the kinds of stuff users want to do. Compare that with Office OpenXML with stateful formatting empty tags (yes, really, in DOCX _TOGGLES_ whether following text is bold).

Re: What if OpenDocument used SQLite? (2014)

#208

Is SQLite’s disk format an open, versioned standard? Or is it just “however SQLite saves data to disk”?

SQLite file format spec: https://www.sqlite.org/fileformat2.html

Complete version history: https://sqlite.org/docsrc/finfo/pages/fileformat2.in

Note that there have been no breaking changes since the file format was designed in 2004. The changes shows in the version history above have all be one of (1) typo fixes, (2) clarifications, or (3) filling in the "reserved for future extensions" bits with descriptions of those extensions as they occurred.

Re: What if OpenDocument used SQLite? (2014)

#210
post #44
post #41

Earlier quoted context omitted.

WinFS ( https://en.wikipedia.org/wiki/WinFS ) without the mssql Engine?

Or this: https://github.com/narumatt/sqlitefs

XSLT processors work by accessing the file system. Would this sqlitefs be a way to run XSLT against an SQLite database? Or is there maybe some other way to run a file oriented XSLT processor against an SQLite database in the SQLAR format?
Post reply on HN