Hacker News Reader

Comment by p4bl0 | Hacker News Reader

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 other than its database format. And to save the document I VACUUM [2] it back to the database file. It works quite well, at least for reasonably sized file (which is always the case for my app) :).

[1] https://www.sqlite.org/inmemorydb.html

[2] https://www.sqlite.org/lang_vacuum.html

View on HN

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. That way a crash is an expected situation that you actually account for, not a special case that you might forget

- there is only one database. Less code, fewer bugs.

- it is safe. A write to SQLite works or doesn't work, there is no in-between. As said in the VACUUM doc you point to: "However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt"

- it is how SQLite was intended to work. And because of that, you won't have to think about it for the lifetime of SQLite

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 presentation state i. e. window positions, last page viewed, zoom level, ...)

For example in many regulated domains such as human subjects research files must be approved and only approved files may be used. "Is this version of the consent document the version that the IRB approved?" Well let's see... (1) file modification date is after the approval date and (2) checksums do not match.

Not to mention that writing a single byte of content to a filesystems marks the entire blob as needing backup.

The fact is the filesystem is the user's database, save is commit, and it should be under the users control because application developers do not have the faintest idea about user context.

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 that)

In the case of checksums in a database, that is why read-only modes should be used and I don't see what automatically saving would change. If anything, when the user zooms on a document in read-only mode, either it shouldn't be stored or storing it should trigger the same flow as modifying the document

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

Why not do it like Blender: just autosave into some software directory, have the possibility to restore on crash, have the possibility to restore the last n autosaves from disk and add a setting for how many to save etc in the options.

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.

Depends a little on the type of file. A prose document, sure, probably want autosave by default. A vector graphics file? I want autosave when I'm creating it, but I do NOT want autosave when I'm copying out a piece buried several groups in and behind some things I need to delete/move out of the way. I also don't want to have to think about whether I need it or not.

But generally the way autosave works is to save a copy that can be recovered on a crash, and only overwrite the original if directed by the user. That works for both use cases. (Haven't used Word in years, so I'm not sure if they have a different behavior now.)

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 different times.

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.

> 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 write, or be the filesystem (e.g. ZFS snapshots, which can be maximally efficient since it knows exactly which blocks it's modified)

Of course this has its own downsides. While those writes may have been "unimportant", the fact is that your backups are now flawed. And if the application has had the foresight to distinguish unimportant writes, and preserve the mtime, I'd rather they just not make those writes in the first place

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.

On Linux, the mtime ("modification time") is indeed automatically updated when the content is changed, but it can also be set arbitrarily from userspace (e.g. using `touch`) without special permissions. This is very useful, e.g. when you mirror a directory you'll typically want to preserve the mtimes, to help identify changes later.

Linux also has a ctime ("status change time"), which is automatically updated when the content or metadata (inode) are changed. It is not possible to change from userspace (you have to use tricks such as changing the computer clock or directly modifying the disk). This gives you the security benefits, but it is not commonly used e.g. in backup tools, precisely because they want to be able to set "fake" timestamps.

Yes it does. mtimes can definitely be edited, if you have permission, but it is rare. I have a photo script that pulls the taken time from Exif and writes it to the file mtime.

> mtimes can definitely be edited, if you have permission

It just requires you to be the owner of the file (https://serverfault.com/a/337810), no special permissions. On the other hand, editing the ctime is not easy -- it requires tricks (e.g. change the computer clock, which typically requires root privileges).

There's also SSD wear issues. There shouldn't be, because SSDs are durable, but some applications find dumb reasons to write multiple GB in a minute.

And by some applications I pretty much just mean browsers, but still.

> Why do you use a secondary, volatile database ?

For the exact reason I gave in the comment you are replying to: I want to keep a usual workflow for users. Principle of least surprise.

Users are okay with change being autosaved when there is a single "thing" that can be edited to the point that you don't even have to open it, it's just there, it can be seen as a property (as in ownership) of the application more than of the user. For example, your music library in your jukebox application.

On the contrary, when the user have to open the "thing" with your application and can choose between many of their files that can be edited with your application, users do not expect their files to be automatically modified at all. For example users may start doing some heavy editing and then at the moment of saving their work, they might make a backup of the previous state file before saving, or choose to "save as…" in order to keep the old version just in case.

Crashes are not something that happen that often. It can become an actual problem when you have tens of thousands of users and rare-events do happen, but in the particular case of the application I working on, I do not actually have to worry about that (on the contrary, any solution would have downsides that are worst in the particular case of this application than having to do some work again because of a crash if it ever happens).

Another option is to explicitly start in read-only mode (modification buttons hidden / grayed out, some distinct mode indicator "Viewing Document" next to a button to "Start Editing", etc), and when the user chooses then switch into autosave mode. At this point many users are used to autosave and don't pay due attention to the document saved state. With Microsoft Windows' habit of rebooting your system overnight without your explicit permission, I'm concerned that this might lead to a lot of lost work.

You can keep a distinction between old and new version inside the same database, by having a pointer to the "current" version, and updating the pointer when clicking on "save". You could store all changes in the database in a "staging area", such that when you reopen the app you can load the changes and you don't need a recovery phase, but with the "save" button active meaning that something changed since last save.

I could, but as you said in your previous comment (emphase is mine):

> Less code, fewer bugs.

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.

In this view the save action is more like a commit, where the user manually checkpoints and also offers a simple description of why this is an important point. But in my view all intermediary points also need to be saved, because the user might have forgotten to explicitly checkpoint, and might still want some undo/redo capability that is more granular than just checkpoints.

> 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

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 maybe he last few seconds of changes, but not all unsaved data)

If you have a db, why not just model it as unsaved data? I.e. all changes get stored to the db, but have a flag of unsaved. If you open up a file and there are unsaved changes, you can prompt the user to either make them saved or discard them.

That feels like it requires the data model to be very different? The file format would essentially need to be a list of changes, with a "committed" flag.

Like, if someone changes some text in a paragraph, you can't just model that as "this paragraph now contains this new text". You have to model it as "this paragraph used to contain this text, but an uncommitted change changed it to this other text". User deletes an image? You have to still store the image and all the references to it, but with an uncommitted change to delete the image and remove the references to it.

And maybe that's a good thing, maybe a git-like system where the history of every change is tracked is what you want. But it certainly doesn't feel like it'd be appropriate for every application and file format.

You wouldn't necessarily need to track every change, you could just have 2 tables, one which contains the last "saved" version of the document, and one which contains the last modified version of the document. Upon opening after a crash, if there is a more recent modified version, the program will ask if you want to load that version.

Databases handle all this natively with transactions and WALs. i.e. Don't need to build a Flintstones version yourself.

Also binary documents are a lousy fit with git, smashing square peg into round hole makes little sense.

> 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 rename() function is equivalent for regular files to that defined by the ISO C standard. Its inclusion here expands that definition to include actions on directories and specifies behavior when the new parameter names a file that already exists. That specification requires that the action of the function be atomic”)

Also, filesystems may have bugs, hardware may lie about syncing to disk, and network shares can be finicky.

Doing this properly isn’t as easy as one would think. You’ve to make sure to sync the file to be written and you’ll have to handle the case where the save location is on a different file system than your temporary file. If so, you’ll have to create a copy on that file system first.

I think many tools do not check whether they need to work cross filesystem and just write their scratch files to the save directory with a different name and then rename them.

Of course, that means you always need twice the disk space on the target disk to do a save. That used to be a problem almost everywhere, but nowadays mostly is restricted to embedded systems and USB sticks.

In this case, however, SQLite will do a lot for you, and probably better than you would do it. It claims (https://www.sqlite.org/atomiccommit.html#_multi_file_commit):

“SQLite allows a single database connection to talk to two or more database files simultaneously through the use of the ATTACH DATABASE command. When multiple database files are modified within a single transaction, all files are updated atomically. In other words, either all of the database files are updated or else none of them are. Achieving an atomic commit across multiple database files is more complex that doing so for a single file. This section describes how SQLite works that bit of magic.”

However, about VACUUM INTO, it says (https://www.sqlite.org/lang_vacuum.html):

“The VACUUM INTO command is transactional in the sense that the generated output database is a consistent snapshot of the original database. However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt. Also, SQLite does not invoke fsync() or FlushFileBuffers() on the generated database to ensure that it has reached non-volatile storage before completing.”

So, I don’t think doing “VACUUM INTO” is sufficient to guarantee that you get a good copy of your data on disk.

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 appropriate way) to the DB as it happens; save is just another user action.

Presumably sqlite has figured out how to write to the filesystem without corrupting itself even in a variety of adverse scenarios?

If not, commit to a temporary copy of the DB that gets renamed to the "main" name periodically or when the app closes. There's an xzzzbit meme there somewhere, yo.

> 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.)

That's certainly true, and also the case in many modern file formats.

For example there's the story of the academic studies with falsified data [1] where forensics on the included excel documents showed they'd gone through and replaced data with "randomized" data (if I remember correctly); there are tons of examples of "redacted" data in pdf docs being visible under the blacked-out rectangles.

I'm not disagreeing with you, btw, such actions are certainly problematic, but hopefully kids will grow up knowing they need to run an export to sanitize their data if they don't want to show the whole world their transaction logs...

[1] https://www.npr.org/transcripts/1190568472

While "knowing they need to run an export to sanitize their data" is (unfortunately!) a thing user just Have to Know, a wider issue imo is that we also shouldn't really be encouraging developers to casually assume that users will only interact with their software through their own ordained interfaces.

> they do not expect the file to contain data that they didn't want to save.

I think that's fine. Just remove "save" from the UI, and save after every keystroke. This may sound crazy in 1970, but it's how nearly everything works today. It's really only us weirdos that started using computers before "the cloud" that think "save" is an operation that does something, and we're dwindling in numbers!

> Linux, the OS says it is atomic

nitpick: At least on some filesystems if you rename a.txt to overwrite b.txt and the machine crashes, you might end up both a.txt and b.txt hardlinked so they contain the same data.

Of course this is no big deal since b.txt is still updated atomically so it contains the new data (assuming a.txt was fsynced) or the old data. I assume nobody depends on a.txt being deleted simultaneously.

Good ole .filename.swp

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.

You are right and like you explained this is trivially easily fixed by autosaving regularly.

What I have trouble imagining is people working with documents on computers for more than a few years yet somehow failing to develop the Always Save Instinct. I regularly catch myself saving unreasonably often.

Users shouldn’t ever need to adapt to computer crashes like this. Software should always auto save or have recovery files or something. As a principle, software should hold anything a user inputs with reverence.

I agree. Maybe as a dev I've become cynical and don't trust anything. Least of all some app holding my document.

Makes me think of the "Voting software" xkcd: https://xkcd.com/2030

"I don't quite know how to put this, but our entire field is bad at what we do, and if you rely on us, everyone will die."

You think that's cynical? I must be some avatar curmudgeonliness then.

I'm pretty sure that what's actually going on is that everything we think of as a 'profession' is that way, and that people are in fact dying because of it.

The difference is that devs are honest about it.

Yes, however you have to be sure every single program across decades is written with those rules in mind. Hard on any OS, but a lost cause on */Linux.

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.

It might very well keep it, but either that behavior wasn't turned on by default or it crashed in way where it wasn't recoverable. I know I lost work.

Doesn't need to be turned on. However it's always possible you mistakenly pressed Esc/Close, didn't read the dialog, or hit a very obscure bug?

However this has worked well for twenty years, so PEBKAC is a reasonable conclusion.

That may have been true years ago in win 95 or xp days. The modern paradigm starting with google docs is that things are automatically saved and even always sharable through the cloud, making manual saving actually an atavistic leftover of a bygone era.

What if I actually don't want any changes saved because I've only opened a document for reference purposes?

Then you proactively prevent changes. Either "open a copy" or "open in readonly mode".

If you make saving the default you have to manually not save. It's a trade off versus default no saves with manual saves

Have a "Read-only" checkbox. For the love of God, have a "read-only" checkbox.

Modern apps like gdocs have a toggle for switching between read-only, suggest changes, and editing. If you forgot to toggle, you can just open version history and revert. MS Office also had version history for nearly a decade.

Maybe simpler? When open the DB, change it to WAL mode, turn off the automatic checkpoint https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint

When user saves, you just checkpointing the file, merging it back into the main database.

> The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

> The VACUUM INTO command works the same way except that it uses the file named on the INTO clause in place of the temporary database and omits the step of copying the vacuumed database back over top of the original database.

Do you use VACUUM (uses a write-ahead log to survive power-off) or VACUUM INTO (as far as I can tell, it doesn't survive power-off during writing, and might corrupt the existing file contents if the filename already exists)?

>> The file named by the INTO clause must not previously exist, or else it must be an empty file, or the VACUUM INTO command will fail with an error.

EDIT: there is no difference between VACUUM/VACUUM INTO - they both write to a new file (COW) it's just VACUUM [NOT INTO] does mv temp.sqlite originalfile.sqlite after that, while VACUUM INTO does not.

We used something similar (DB doing caching run in memory but saved periodically on disk) but with backup API

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

Why not use a transaction?

A single transaction for the whole user session? That seems a bad idea. Also I'm not sure you can do transactions during another transaction, and I need them for other purpose, i.e., for what they were designed to do (doing changes in multiple tables that need to stay consistent).

It’s exactly what transactions are for. A nested transaction is called a savepoint, which sqlite does support.

> I can directly make the change in the database I don't need to have a model of the document other than its database format.

I don't get your point. Are you saying that you don't need to have a model of the document other than the model of the document? What's the nuance I'm missing?

An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk.

Programmers often have to make software design decisions around how to represent a file in memory in order to manipulate it. For example, if I'm writing an HTML editor, should I mostly treat it like a text file (maybe a gap buffer) with syntax highlighting and auto indentation as an afterthought? Or should I maybe load the whole thing into a tree? What are the robustness and performance characteristics of each?

The commenter above was saying that using SQLite made that decision easy. He could keep traditional (or "atavistic" per the commenter upthread, depending on your perspective) load/save semantics while also making the data model easy to work with.

> An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk.

The whole point of my remark is that the domain model and the export document format are two entirely separate things.

I suppose this is in the context where you will be syncing up the changes to a backend server which will also be storing the document in an SQL database. Normally, you might expect that data format on the client to be JSON/XML/something else, and you'd need to maintain logic that marshalls the document representation

    SQL <-> In-memory representation <-> Disk format.

With SQL on the client, in theory you only now need to maintain

    SQL <-> In-memory representation

Obviously I'm skirting over the format you would use to send either entire documents or partial updates of documents over the wire.

When an application loads a document, for example if the document is formally a list of things (imagine a very simple TODO app), the usual approach is to have this data represented (modeled) as an actual list in your program, like a Python list of objects, because it's what is easy to manipulate programmatically.

Then, saving your document means serializing the data in some format (which could be JSON, XML, CSV, an SQLite database, …) and writing that to disk, and opening a document means reading the file from disk and unserializing it to your internal model.

What I'm saying is that my approach is to use an in-memory SQLite database as the internal model of the data in the applications. I presented an upside (opening and saving are easy), but is also has downsides: I have to do SQL queries to manipulate the data rather than manipulating objects directly (which could be mitigated using an ORM but that's outside my point). In Python-like pseudo-code you can imagining something like:

    self.todos[42].status = 'DONE'

    self._db.query("UPDATE todos SET status='DONE' WHERE id=42")

(Of course there is the possibility of using ORMs or other approach in between the two.)

Btw, Apple's CoreData, commonly used by iPhone and Mac apps, uses SQLite by default. That part works fine, so you can study it if you'd like and ignore all the bad parts built on top (ORM, MVC framework, etc).

Reply on news.ycombinator.com