> 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)?
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.
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)
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.
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 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.)
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...
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!
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.
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.
Also binary documents are a lousy fit with git, smashing square peg into round hole makes little sense.
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.
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."
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.
However this has worked well for twenty years, so PEBKAC is a reasonable conclusion.
If you make saving the default you have to manually not save. It's a trade off versus default no saves with manual saves
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?
SQL <-> In-memory representation <-> Disk format.
SQL <-> In-memory representation
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.
The whole point of my remark is that the domain model and the export document format are two entirely separate things.
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")
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
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.
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
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.
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.
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
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).
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.
95% of the time I want changes persisted immediately, but it's nice to be able to turn it off when I don't.
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.)
And by some applications I pretty much just mean browsers, but still.
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.
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.
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).
> Less code, fewer bugs.
The term you're looking for is (aptly named) crash-only software.[0]
When user saves, you just checkpointing the file, merging it back into the main database.
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