Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What if OpenDocument used SQLite? (2014)

sqlite.org

Post reply on HN
Print view View original

151–160 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#151

Post by theamk »

knome wrote: post #105
rakoo wrote: post #86

Earlier quoted context omitted.

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…

a save button is still good, as it allows you to keep specific checkpoints. but the save button could simply tag specific save points in a larger table. if the format can roll up changes to compress them, they also indicate where which variants need to be kept indefinitely.

Auto-save is nice, but I think it works much better when it's a separate file.

This way "main" document file (which might be checked in to git, or shared via dropbox or read by some document) only contains nice, clean, saved version.

And yet if your computer crashes for whatever reason, the data is still not lost and can be trivially recovered.

Re: What if OpenDocument used SQLite? (2014)

#152

Post by OliverJones »

quickthrower2 wrote: post #23
vmfunction wrote: post #5

At this point, why are we still using JSON/XML when there is SQLite for new projects? Stop the non sense of JSON/XML. SQLite is like json, but very queryable. Just send SQLite files around. MongoDB also saves document db type of store space just FYI.

With JSON/XML the app owner decides the schema of the saved file, as they should. One day Sqlite will do some perfectly fine change that’ll break people who outsource their file format to it. Own your file format! That said there is some nuance and it depends what the user expects. Is you app more of an MSWord where people expect a format that is decades backward compatible and only changes on explicit save, or is it…

XML and JSON are for serialization. zip and sqlite, and file-system files, are for lossless persistence. They're separate issues.

An app can go bananas with serialization and use, I dunno, binary JSON or Matroska / ebml or .mp4 containers or whatever, and still serialize any way it wants.

Re: What if OpenDocument used SQLite? (2014)

#153

Post by fweimer »

HelloNurse wrote: post #110
fweimer wrote: post #68

Earlier quoted context omitted.

I found the transactional aspect surprisingly difficult, especially with concurrent file access. SQLITE_BUSY handling was quite hard at the time. I know that serialization failures are expected in transaction processing, but for SQLite it was very difficult to tell persistent failures (say, due to self-deadlock) apart from transient concurrent update problems. For transient failure, you can re-execute the closure def…

If you retry your write several times and it doesn't succeed you can tell the user it is a persistent failure without agonizing too much over the diagnosis: it is persistent enough to be a significant problem, even without proof that it is an application bug. Who would attempt to make concurrent writes to an application document format? And how wouldn't such an attempt be a user mistake? Failing to write is the solut…

These concurrency failures in transaction processing can be quite rare, but you have to fix them if you want 24/7 unattended operation. SQLite only has timeout-based conflict detection, so you basically have to decide whether you want to wait 60 seconds (or so, depends on how glitchy your storage is) before reporting a potential self-deadlock, which isn't great for development, or risk failing unnecessarily when actually running the job. I had no idea what the right timeout was, which is why I wrote some of the custom self-deadlock detection logic. I think I got to run it completely reliable in the end (no false aborts even under load), but as I said, it was surprisingly hard.

By the way, concurrent read/writes on locally stored documents happen, even on single-user machines. If the reading process uses the SQLite structure (say a document indexer that knows about the format), it has to take some locks and may also need to flush data from the WAL log (depending on implementation details). At this point you have to deal with concurrency issues in the application, too. Unless you rewrite the entire document from scratch on every save and put it in place with an atomic rename (which I ended up doing for a different application, not the transaction-processing one). But that loses some of the advantages of SQLite.

Re: What if OpenDocument used SQLite? (2014)

#154

Post by nojvek »

orra wrote: post #141
coliveira wrote: post #139

Earlier quoted context omitted.

But you don't need a standard, because all interaction between applications and the document is made through SQL. And SQL is standardized (at least the parts that matter). If you have concerns about compatibility, make sure that the document can also be accessed through other databases (like mysql).

But other databases cannot access sqlite databases, because the file format is internal...

SQL file format is very well documented. In some universities it is an assignment to directly read and write sqllite files from disk and understand the paged and blocks structure.

You don’t need sql for any of it.

https://www.sqlite.org/fileformat.html

Re: What if OpenDocument used SQLite? (2014)

#155

Post by p4bl0 »

nextaccountic wrote: post #27
p4bl0 wrote: 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…

This means that like a regular app, you lose data if the app crashes or there is a power loss. It's much better to save after each operation in a temporary place (probably in ~/.local/share/application/yourapp, using XDG directories), and when the user clicks save, just copy the file into the desired location. That way, if there is a power loss and you reopen the app, it opens right back where it was doing (losing ma…

Yes, I am aware of that, and you are right about this in general. In my particular case however, it is preferable to loose some work in the rare cases were a crash occurs than to have a copy of the file in some place that the users are not aware of. Of course if crashes were frequent the trade-off would be different.

Re: What if OpenDocument used SQLite? (2014)

#156

Post by naniwaduni »

cduzz wrote: post #78
Someone wrote: post #60

Earlier quoted context omitted.

> and when the user clicks save, just copy the file into the desired location. To be perfectly safe, you want to rename it, not copy it. If there’s a power loss during copying, you may endcup with corrupted data. Renaming is, to coin a phrase, “more atomic” than copying (on Linux, the OS says it is atomic. ISO C says it, too, but POSIX doesn’t ( https://pubs.opengroup.org/onlinepubs/000095399/functions/re... : “This…

Well, copying is simply not atomic in linux; directory entry operations (rename, link, unlink) are atomic. There is a definition somewhere that says how many bytes may be written atomically; that's it -- past that writes are not atomic. The prior comment of "model user interactions in the database" seems spot on -- just keep track of what the user's doing as unsaved data in the database and commit it (in the appropri…

> The prior comment of "model user interactions in the database" seems spot on -- just keep track of what the user's doing as unsaved data in the database and commit it (in the appropriate way) to the DB as it happens; save is just another user action.

The trouble is that often in the wild, the content of the file on the filesystem is a user-facing interface. Users will copy it around and attach the whole document onto emails. When they do, they do not expect the file to contain data that they didn't want to save.

(Yes, they sometimes also expect the file to contain data that they wanted to but didn't explicitly save. This is not a contradiction.)

Re: What if OpenDocument used SQLite? (2014)

#157

Post by bane »

miki123211 wrote: post #136

The problem with SQLite is that it's not a standardized file format. It's well-documented and pretty well understood for sure, but there's no ISO standard defining how to interpret an SQLite file in excruciating detail. Same goes for competing implementations, Zip and XML have a much smaller API surface than SQLite, whose API, apart from a bunch of C functions, is the SQL language itself. Writing an XML parser is not…

I'm not sure if the problem you are pointing out has to do with:

a) SQLite the file format - which is Public Domain and so well documented that parsers for it exist in numerous other languages even though it's almost pointless because...

b) SQLite, the Public Domain (and thus entirely source available) C implementation of the library that can operate on the file format -- and is documented to a level well above what most ISO standards shoot for. It's designed to be used in other software and has bindings for pretty much every major language.

c) Some notional OpenDocument stored in a SQLite file that's really just waiting for somebody to make and document.

ISO standards are great, but if we had to wait for ISO to define a file format we'd have pitifully little to work with.

Re: What if OpenDocument used SQLite? (2014)

#158

Post by pornel »

eviks wrote: post #100
im3w1l wrote: post #49

I don't want people to read my drafts. That could be highly embarassing, and they should not make it into the final saved document. Past version and undo history should be stored separately from the document. They should be stored out of tree where they wont be commited into some git repository or be automatically synced or anything like that.

Then don't give people access to your drafts but exported versions without history? Why put the limits on the efficiency of a format by forcing it to store changes elsewhere?

It's better if such gotchas don't exist. Otherwise you'll have every user get burned by it at least once, and blaming them for not knowing the subtle consequences of using "Save As" instead of "Export As" is not going to help anyone.

Re: What if OpenDocument used SQLite? (2014)

#159

Post by olddustytrail »

dsego wrote: post #92
chrisshroba wrote: post #83

Earlier quoted context omitted.

I used to have that instinct but lost it in the age of auto save. The applications (web or native) I use most often all do it for me: Google docs, Dropbox paper, notion, vscode. I don’t think I’m alone in this!

I actually tried using open/libre docs a few years ago just because of it being open source. I was trying to make a point of using locally installed software and avoid google products. Then the thing crashed and I lost an hour of work because it didn't save a temporary version. That's when I gave up on it for good.

Libre office does keep a temporary version that allows you to recover, so you're talking crap.

Re: What if OpenDocument used SQLite? (2014)

#160

Post by kortex »

galangalalgol wrote: post #64
kunley wrote: post #46

Love the vibe of artivles, which present let's say reason-driven development vs habit-driven. Why habit? Well, I can imagine back at the time OpenOffice was a fresh project, it went like this: "XML is going to stay forever and everybody uses XML, so ofc we use one... oh, it is so big! And there are many files, so we just zip'em"... To be fair, the author of this excellent article doesn't even say about getting rid of…

Could you clarify the "XML is dead" comment? Don't all the major document formats still use zipped xml? I had to interface with an xml format recently, and that isn't something I ever did, and when I went looking for a crate that parses an xml schema I kept running across this whole xml is dead thing. But it still seems to be everywhere.

Not GP, but I believe the "XML is dead" sentiment stems from the observation that very few greenfield applications are deliberately choosing xml. Sure you have legacy giants like (X)HTML, SVG, office formats, etc, but you'd be hard-pressed to convince developers (especially a younger crowd) to select it as a data format. It's seen as warty, cumbersome, unwieldy, verbose.
Post reply on HN