What if OpenDocument used SQLite? (2014) • Hacker News Board

Re: What if OpenDocument used SQLite? (2014)

#181

Post by HelloNurse » Mon, Sep 18, 2023, 3:01 PM UTC

fweimer wrote: post #153

HelloNurse wrote: post #110

Earlier quoted context omitted.

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 actu…

You are clearly discussing a shared database for concurrent transaction processing ("you want 24/7 unattended operation"), not people editing application document files.

Setting aside technological details, multiple clients operating on independent rows of the same table can only, at worst, waste time by retrying a transaction, while multiple concurrent users attempting to modify the same document are asking for trouble, and if they succeed they probably succeed at corrupting the document.

Even without lock contention the aggregate document state can be incoherent (for example, Alice and Bruno edit a text, but they accidentally modify the same section and the latest save prevails and nobody notices).

Re: What if OpenDocument used SQLite? (2014)

#182

Post by fluidcruft » Mon, Sep 18, 2023, 3:05 PM UTC

afiori wrote: post #178

fluidcruft wrote: post #116

Earlier quoted context omitted.

There is nothing I hate more than an app that modifies files secretly when I open them. Then I have to get all defensive to copy files before I open them to keep them intact. You may not see the problem with changing the checksum or hash of a file, but silently tampering with files is a nightmare in many domains. If you open a file and accidentally change something trivial (some apps like to store things like present…

For an application working with reasonably sized files sqlite files it would be reasonable to 1. on opening a file clone it to a temporary folder 2. edit the temporary file there on disk 3. on save mv/cp the temporary file over the destination I am probably missing a lot of use cases, but it migth be a good idea for a game like Factorio where you are expected to have multiple on disk saves of the same run at differen…

In the sqlite case, I think it actually can save uncommited edits to a separate journal file until committed. At least, one of the systems I am familiar with that uses sqlite as a container format (MRI scanner) seems to do this, so I suspect sqlite supports that mode natively.

I'm just pushing back against the idea that its a good or helpful idea to "help the users" by taking the deliberate "save" action away from them.

As an aside, one of the things that has been learned from this class of MRI scanners is that users need to feel "in control" of the machines they're using. The "look how smart this machine is by doing all these magical things you used to do yourself!" attitude works well in sales but really does not go over well in the field because users encounter the fuckups and are held responsible for them. So they quickly start to distrust the machines.

Re: What if OpenDocument used SQLite? (2014)

#183

Post by paulddraper » Mon, Sep 18, 2023, 3:05 PM UTC

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…

> less capable engine

There wouldn't be another engine.

It would be SQLite. Period.

Re: What if OpenDocument used SQLite? (2014)

#184

Post by paulddraper » Mon, Sep 18, 2023, 3:07 PM UTC

mrighele wrote: post #132

stareatgoats wrote: post #119

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what yo…

> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from vers…

MySQL allows the query, but the non aggregate fields are selected randomly

Re: What if OpenDocument used SQLite? (2014)

#185

Post by fluidcruft » Mon, Sep 18, 2023, 3:19 PM UTC

didntcheck wrote: post #179

fluidcruft wrote: post #116

Earlier quoted context omitted.

There is nothing I hate more than an app that modifies files secretly when I open them. Then I have to get all defensive to copy files before I open them to keep them intact. You may not see the problem with changing the checksum or hash of a file, but silently tampering with files is a nightmare in many domains. If you open a file and accidentally change something trivial (some apps like to store things like present…

> Not to mention that writing a single byte of content to a filesystems marks the entire blob as needing backup. If the size, mtime, and inode number stay the same (i.e. it writes into the file directly instead of replacing it), then most backup software will skip it. AFAIK to do otherwise you either need to read the whole file every time, or be live monitoring audit events to see what files have been opened for writ…

I am under the impression that modifying a file's content updates the modification time. Is this incorrect? Modifying a file without updating the mtime or allowing mtimes to be edited in userspace sounds like a security nightmare.

Re: What if OpenDocument used SQLite? (2014)

#186

Post by wongarsu » Mon, Sep 18, 2023, 3:20 PM UTC

fluidcruft wrote: post #116

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…

There is nothing I hate more than an app that modifies files secretly when I open them. Then I have to get all defensive to copy files before I open them to keep them intact. You may not see the problem with changing the checksum or hash of a file, but silently tampering with files is a nightmare in many domains. If you open a file and accidentally change something trivial (some apps like to store things like present…

Word has a fairly simple solution to this: there's a big slider labeled "Autosave" in the title bar, right next to the save button, allowing you to turn this behavior on and off at any time.

95% of the time I want changes persisted immediately, but it's nice to be able to turn it off when I don't.

Re: What if OpenDocument used SQLite? (2014)

#187

Post by [deleted] » Mon, Sep 18, 2023, 3:25 PM UTC

didntcheck wrote: post #174

indymike wrote: post #168

> there's no ISO standard defining how to interpret an SQLite file in excruciating detail. There comes a point where ISOing things doesn't help. The SQLite format belongs to SQLite, and an ISO standard would result in that standard being rendered irrelevant by the SQLite team, should they wish to make a change for any reason. Also, people would have to pay ISO for access to the specifications. SQLite should be treate…

Just a heads up that it looks like you meant to reply to miki123211, but you've posted a top-level comment instead :)

[deleted]

Re: What if OpenDocument used SQLite? (2014)

#188

Post by fluidcruft » Mon, Sep 18, 2023, 3:26 PM UTC

rakoo wrote: post #134

fluidcruft wrote: post #116

Earlier quoted context omitted.

There is nothing I hate more than an app that modifies files secretly when I open them. Then I have to get all defensive to copy files before I open them to keep them intact. You may not see the problem with changing the checksum or hash of a file, but silently tampering with files is a nightmare in many domains. If you open a file and accidentally change something trivial (some apps like to store things like present…

You are right, this is a use case I absolutely did not take into account, but I want to separate user-defined actions and app-defined actions. A level of zoom is something a user does to read a document, but I wouldn't consider that as data to be persisted automatically, unlike characters typed or a font chosen. I value the idea of persisting it, but that would be a user-specific action ("Save view" or something like…

I see what your are saying, but in other use cases the presentation state needs to be considered as part of the document. This is one of the reasons zip/jar containers work somewhat well. You can audit different chunks of data separately and cryptographically sign them. sqlite actually has an archive format[1] that is interesting to think about and I have pondered using it for some applications (store the files and also store tables of metadata/analysis)

[1] https://sqlite.org/sqlar.html

Re: What if OpenDocument used SQLite? (2014)

#189

Post by wongarsu » Mon, Sep 18, 2023, 3:27 PM UTC

paulddraper wrote: post #184

mrighele wrote: post #132

Earlier quoted context omitted.

> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from vers…

MySQL allows the query, but the non aggregate fields are selected randomly

Following MySQL's longstanding tradition of just doing whatever instead of showing an error message, no matter how unreasonable the result.

Re: What if OpenDocument used SQLite? (2014)

#190

Post by Tigress8780 » Mon, Sep 18, 2023, 3:37 PM UTC

nxobject wrote: post #72

Tigress8780 wrote: post #65

Earlier quoted context omitted.

Once we had to ship millions of extremely small files to our customer, we ended up throwing them into a MongoDB and serve them with a web server. It worked great. We tried to use an image of traditional filesystems (ext4 and fat32), but with most files being under 1 KiB, it was super wasteful.

This is an extremely low quality comment, and I accept any downvotes, but I can't resist: would you say that MongoDB was web scale?

The way our "MongoFS" was organized is actually also low quality and probably would fall apart pretty quickly if used as a serious web service. However, it works if you try to quickly deploy and serve millions of files to a small number of clients.

Our use case was a Maven mirror for disconnected environment that only contains metadata (i.e. lots of small XML files, without the actual jar). We already had a MongoDB service for some other JSON data, so here we are.