Hacker News Reader

Comment by nyanpasu64 | Hacker News Reader

I was optimistic that Audacity adopting SQLite would be a substantial improvement in its file saving capabilities. In practice I encountered many gotchas:

- On Linux, saving into a new file onto a root-owned but world-writable NTFS mount created in /etc/fstab, fails due to permission errors or something. Saving into an existing file works as usual.

- Files are modified on disk when you edit the project in the program, creating spurious Git diffs if you check Audacity projects into Git as binary blobs. And when you save the file, old and deleted data is left in the SQLite file until you close the project's window (unlike saving a file in a text editor), and you can accidentally commit that into a Git repo if you don't close the window before committing. (I recall at one point that you had to manually vacuum the .aup3 file, but now closing the window is sufficient.) I'm getting Word 2003 Fast Save vibes.

View on HN

It’s also a bit of a bother if Audacity crashes (or is otherwise terminated abnormally), as the cleanup just doesn’t happen at all then, whereas in the past the recovery process would mention the presence of orphaned blocks and allow you to choose to keep or delete them. But when I had a several-gigabyte project that should have only been a couple of hundred megabytes, and needed to save disk space, I finally found a solution suitable for my simple single-track stuff: Mix and Render. Doesn’t change the audio, but allowed it to clean up the detritus on save and exit. But all up, this is clearly an application-level problem, not something inherent to SQLite.

Hmm… I think I vaguely recall that Audacity 2 had the concept of a temporary working space, whereas it seems that Audacity 3 just uses the .aup3 file as its working space? Some advantages, some disadvantages.

Mildly less on-topic: I looked into Audacity 3’s format, and was utterly baffled by what they’ve done with the project data (what used to be the .aup file). They still encode it as XML, storing it in a single-row table, but instead of just writing it as text, they use a simplistic dictionary coder on it. Just… why? Why did someone go to all the trouble of writing that code? It makes interoperability and inspection much harder, surely harms performance (even if by a trivial amount), and the space saving will be rounding error in every plausible case (like, maybe as much as a few kilobytes out of hundreds of megabytes of audio files).

Yes it should replicate the functionality user expects - save everything into temporary file and overwrite the original file only on explicit save action.

As for Git, it would benefit from using text format specifically aimed for easy diffing/merging. No idea how easy the sqlite dump is in this regard.

> Yes it should replicate the functionality user expects

Do users really expect this now a days? Most users use cloud apps, and almost all of those save after every operation automatically.

Which is a compromise for using browsers really. It isn't a good solution and no user really understands this and I believe it is the most hated feature of the new cloud world. Yes, leaving the page open for multiple hours might now allow you to save because your access token expired. No, communication in the background is unreliable too. Autosave is a bad band aid for a bad solution.

Doing periodic and automatic saves is good. Doing so on a document "in production" is majorly stupid. Not that I want to accidentally validate the busy work dev ops puts us through.

Its pretty easy to make a cloud app that emulates the traditional working draft/save workflow. Browsers all have pretty reliable local storage technology now a days if your network is unstable. I don't think this design choice is a compromise of the medium. If anything it seems like if you were going to have to compromise for web you would do it in the other direction so apps are more usable during poor network conditions.

I would say the traditional model is a compromise from back when disks were unacceptably slow to be saving constantly.

> As for Git, it would benefit from using text format specifically aimed for easy diffing/merging. No idea how easy the sqlite dump is in this regard.

The problem I'd predict here is that then people would expect to be able to do three-way merges. It might even work correctly a lot of the time, depending on the exact pattern of changes. But my gut feel is that unless the schema were designed just right, there would be possible merges that would result in a database that was valid from SQLite's point of view but insane from the application's point of view (broke expected variants, etc).

If you want to use flat files you should just use flat files. There are plenty of unix tools to treat them like DB.

You're not going to have a sensible text version of a btree that is reasonably editable by a text editor.

I set up my Git to use the SQLite dump on SQLite files when using “git diff”. This at least shows me the changes row-by-row, or shows nothing if no changes.

I don’t expect to be able to merge though.

I have been told that the new generation of users does not expect, want or appreciate applications that use explicit saves.

I've also been told that they don't understand or even want to understand folders...

The context here is someone using git. Who presumably understands folders.

My wife uses Audacity all day and every few days there is a corrupt sqlite file (duplicate key) which cannot be (as far as we know) repaired/reimported etc from Audacity. I can fix it manually if it's important, but usually just throw the file away and things work again.

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

Yeah, it definitely is. And it's fixable manually. Kind of the advantage to an open file format with nice tooling.

It also sounds like something that could be manually prevented ahead of time. If you can crack open the file on first save and add the right uniqueness constraint, that should make Audacity crash when it tries to corrupt the data.

> ... that should make Audacity crash when it tries to corrupt the data.

That'd be fairly non-optimal behaviour. ;)

When the application tries to add wrong data (eg duplicate key violating uniqueness constraint), SQLite will return an error.

The application should handle things better than by crashing. In theory anyway. :)

If your choices are a crash or corruption, choose the crash.

Sure, data integrity is important.

But hopefully the choices are a bit better than just those two. :)

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

Reply on news.ycombinator.com