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

31–40 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#31

Post by chrismorgan »

nyanpasu64 wrote: post #4

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…

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

Re: What if OpenDocument used SQLite? (2014)

#32

Post by bawolff »

rini17 wrote: post #11
nyanpasu64 wrote: post #4

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…

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.

Re: What if OpenDocument used SQLite? (2014)

#33

Post by gwd »

rini17 wrote: post #11
nyanpasu64 wrote: post #4

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…

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.

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

Re: What if OpenDocument used SQLite? (2014)

#34

Post by bawolff »

tuyiown wrote: post #26
punnerud wrote: post #20

Earlier quoted context omitted.

Have you checked the Apple apps? Most of them use SQLite as storage format. iMovie, iPhoto, Voice recording… Same with Docker. Can’t be that wrong?

App using a format specific to their own and unique implementation, that ends up kind of proprietary is perfectly ok. Using it for a open specification which target is cross implementation compatibility makes the move way more hazardous. Meaning, every implementation has to run on environment targetable and compatible wit sqlite or has to re-implement a compatibility layer on something complex enough that you only re…

> Meaning, every implementation has to run on environment targetable and compatible wit sqlite

Well i get what you are saying, sqlite has been ported all over the place. It probably wouldn't be the limiting factor portability wise.

Re: What if OpenDocument used SQLite? (2014)

#35

Post by justsomehnguy »

nyanpasu64 wrote: post #24
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…

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

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

Re: What if OpenDocument used SQLite? (2014)

#36

Post by vmfunction »

constantly wrote: post #9
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.

Any text editor in the world, even the ones that ship with the most barebones shells, can open json and xml and present their data to the user. SQLite files require opening in a DB terminal or using special software to even get to the point where one can see what’s there at all. Further the entire internet basically natively supports XML and JSON.

That is a good argument, however many people like some big game development company start to ship with GB of json file, at that point just use SQLite. It will be faster to query load. Also if you look at how DB such as Mongo (Not promoting them in any way), but when Maildir is used aginst Mongo for file storage, Mongo saves a lot of disk space. Again, it is about how we want to store files? NixOS is a quite a way to think about having a file system or db/store.

Re: What if OpenDocument used SQLite? (2014)

#37

Post by filmor »

paradox460 wrote: post #16
capableweb wrote: post #7

Earlier quoted context omitted.

Hmm, me too, and Wikipedia says: > OpenDocument - Initial release: 1 May 2005; 18 years ago > SQLite - Initial release: 17 August 2000; 23 years ago Wonder what gives.

OpenDocument traces it's ancestry to OpenOffice XML format, which traces it's ancestry to StarOffice, which was xmlized around the time Sun bought it in 1999

Not to be confused with Office Open XML (OOXML), Microsoft's "standard".

Re: What if OpenDocument used SQLite? (2014)

#38

Post by eastern »

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…

In sqlite the on-disk file format does not matter.

All that matters is that you should be able to issue sql to the sqlite embedded library and get back the results.

Freeing you from the overhead of owning (thus inventing and then maintaining) your own file format is almost the entire point of using sqlite in this manner.

Re: What if OpenDocument used SQLite? (2014)

#39

Post by vetinari »

m4rtink wrote: post #30
vetinari wrote: post #10

Earlier quoted context omitted.

Many filesystems have an issue with tens of thousands or more files in a single directory, which is exactly what you can get with map tiles. No wonder sqlite is faster.

Yeah, that's why sqlite was adopted for this back then - many devices still used FAT32 on the storage volumes where tiles we often stored/cached and that had horrendous small file performance - a plain white 130 Byte PNG tile could result in 64 kB being used.

It is not just fat32 and overhead up to cluster size; once I had 800k tiles in a single directory on ntfs.

It was unusable. The only thing that I was able to do is to tar it up and move to a machine with xfs, where I was able to sort it up into more balanced subdirs and then move it back (for processing using windows-only tool). Just tarring that single directory up took several days.

Re: What if OpenDocument used SQLite? (2014)

#40

Post by Ensorceled »

jve wrote: post #29
orf wrote: post #14

Coupling a file format to SQLite smells wrong. SQLite is good, but it is also fairly unique in this space. Why? Because it’s hard to replicate everything it does, because it does a lot. But… for this case, do we need it do a lot? No, not really. We don’t need the full SQL standard, a query optimiser, etc etc for basic (+ safe) transaction semantics and the ability to store data in a basic table structure. Perhaps the…

- Why not? https://www.sqlite.org/appfileformat.html - Its size is less than a megabyte: https://sqlite.org/footprint.html - 750KB if all features are enabled: https://www.sqlite.org/about.html - Looks like fair amount of functionality can be left out when compiling sqlite and with options to influence/strip down query planner: https://www.sqlite.org/compile.html - And "SQLite does not compete with client/server data…

The complaint is not “it isn’t good” but rather “it is not replaceable”. Since SQLite is so powerful, once you specify it as a format, you are stuck with SQLite forever.
Post reply on HN