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

171–180 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#171

Post by cduzz »

naniwaduni wrote: post #156
cduzz wrote: post #78

Earlier quoted context omitted.

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

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

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

Re: What if OpenDocument used SQLite? (2014)

#172

Post by justinclift »

regularfry wrote: post #54
justinclift wrote: post #51

Earlier quoted context omitted.

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

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

Re: What if OpenDocument used SQLite? (2014)

#173

Post by naniwaduni »

internetter wrote: post #126
Lockal wrote: post #113

Sadly they did not include bad sides: 1) Vulnerabilities: not only in SQLite, but also in wrappers like https://nvd.nist.gov/vuln/detail/CVE-2023-32697 2) Lack of transparency: zip with xml's contains only xml's; meanwhile SQLite contains by design all kinds of traces with sensitive information or empty blocks. Attempts to fix these issues removes benefits that were mentioned. 3) Lack of implementer support. It was o…

> Vulnerabilities: not only in SQLite, but also in wrappers like Yes, parsing encoded files tends to introduce vulnerabilities. ZIP parsers have had plenty of vulnerabilities. This is not exclusive to SQLite. > Lack of transparency: zip with xml's contains only xml's Both zips and sqlite cannot be read with a text editor. Both are open formats with widely available tools to read them. The sqlite binary might, in fact…

> Both zips and sqlite cannot be read with a text editor. Both are open formats with widely available tools to read them.

Well, that's why your archive format of choice should be cpio, which is almost a text file except that modern implementations tend to 0-terminate the filename!

Jokes aside, there are widely-distributed tools that can take in an almost-arbitrary zip file and account for every byte in it. The format is straightforward enough that, were you so inclined, you can do most of it (other than, like, decompression and crc-checking) manually in a text editor. The SQLite format is not like this. There is one implementation, and relatively easy to "hide" data in a database file that its tooling will not reveal.

Re: What if OpenDocument used SQLite? (2014)

#174

Post by didntcheck »

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

Re: What if OpenDocument used SQLite? (2014)

#175

Post by naniwaduni »

cduzz wrote: post #171
naniwaduni wrote: post #156

Earlier quoted context omitted.

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

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

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.

Re: What if OpenDocument used SQLite? (2014)

#176

Post by tl »

amiga386 wrote: post #87
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…

Exactly. Some formats are designed, first and foremost, for interchange. SQLite is pitching that you, as an "app" owner, force the SQLite format upon your users to make it a de-facto standard, without putting the work in to make it a de-jure standard. Show me a formalised ISO / IEC / ANSI / ETSI SQLite standard that the Richard Hipp and his company never deviates from, and the full legal search to ensure there are no…

Performance matters and is sufficiently captured via working incremental updates. The single largest upside of a proposal like this is captured by using SQLAR over ZIP. That's what the Library of Congress does when SQLite claims them as a proponent. It's what Fossil does as others in this thread have pointed out. It's suggested as "first improvement" in the linked article. It's also the only part that should actually be considered for implementation.

You are right to point out the folly of deeper implementations like having and needing to understand table structures for things like slides. However, the current status quo involves Microsoft implementing a fairly esoteric "update the XML file's bytes as they would be encoded in a ZIP file" in their proprietary tool (where they have enough money to invest the engineering time) and all other tools use the slower "whole file in memory" approach.

User visible features like incremental fast saves (and shared editing) keep people on closed systems and give Microsoft the leverage to do the things you warn against. SQLite as a container format could have prevented that by giving everyone a shot at a lower cost but still fast implementation.

Re: What if OpenDocument used SQLite? (2014)

#177

Post by belenos46 »

SanderNL wrote: post #84
josephg wrote: post #79

Earlier quoted context omitted.

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.

Re: What if OpenDocument used SQLite? (2014)

#178

Post by afiori »

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…

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.

Re: What if OpenDocument used SQLite? (2014)

#179

Post by didntcheck »

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…

> 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

Post reply on HN