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

271–280 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#271

Post by bb010g »

patapong wrote: post #238
miki123211 wrote: post #136

The problem with SQLite is that it's not a standardized file format. It's well-documented and pretty well understood for sure, but there's no ISO standard defining how to interpret an SQLite file in excruciating detail. Same goes for competing implementations, Zip and XML have a much smaller API surface than SQLite, whose API, apart from a bunch of C functions, is the SQL language itself. Writing an XML parser is not…

This sounds exactly like the argument that killed WebSQL in 2010: https://en.wikipedia.org/wiki/Web_SQL_Database I am still salty about this, as WebSQL would have made it much easier to build a certain class of web apps.

You can still use https://github.com/jlongster/absurd-sql>. https://jlongster.com/future-sql-web>

Re: What if OpenDocument used SQLite? (2014)

#272

Post by hot_gril »

orf wrote: post #269
hot_gril wrote: post #267

Earlier quoted context omitted.

OpenDocument also leverages the file/dir structure for efficient querying like we're both saying. If you mean that you don't need SQLite over ODF, well yeah, ODF works too. I just wouldn't prefer it. What's the issue with the update semantics, though?

The issue is incremental updates, and this is where things get complex. If you have a file embedded in the middle of a zip file that is 100 bytes, and you want to resize it to 150 bytes, how do you do that? You can’t squeeze it in without moving everything else about, which disrupts other readers. You could append it to the end maybe, but you need to handle concurrent writers. Compression also is an issue here - I ex…

So basically ODF loads everything into memory, relies heavily on in-memory structs for quick unsaved updates, and is crash-safe by writing the whole zip to a temp location during saves. Kinda similar to MS Office. The file structure also helps a little. This is good enough for small docs.

Many times have I encountered large docs, often spreadsheets, that push the limits here and become noticeably slow. If you want to get more sophisticated with the indexing and paging, SQLite is a very natural path. Anything else would be reinventing the same wheels SQLite has spent decades refining.

Re: What if OpenDocument used SQLite? (2014)

#273

Post by orf »

hot_gril wrote: post #262
orf wrote: post #45

Earlier quoted context omitted.

> In the end, you don't need a database, but a library that gives you database API and behavior. Why do you need a single library that gives you a database API and behaviour? Wouldn't it be better to decouple those: provide an open, standard format that enables compact, fast, structured storage that is built to allow transaction/atomic updates. If that exists then you can plug sqlite on top of that, or something else…

> Wouldn't it be better to decouple those: provide an open, standard format that enables compact, fast, structured storage that is built to allow transaction/atomic updates. The high-level software abstraction approach doesn't hold up when it comes to databases. This is such a wide and performance-critical interface that any abstractions are gonna leak badly. Even the SQL standard has all these impl-specific flavors.…

You’re totally right, but there are a few things missing: this isn’t a DBMS, really, and the files are not going to be huge.

You need fast listing/pagination, key value get/set, and transactional updates. Basically DynamoDB, but for a single file. Build a query layer on top of that, sure. Use those primitive to build persistent indexes if you want.

Or just iterate through the keys in a for loop. It fits in memory anyway.

You don’t need a fully fledged DBMS for a word document. And if you’re shuffling around lots of data in a structured format with no updates needed, you probably want arrow/parquet rather than sqlite because the read performance is going to crush SQLite.

Re: What if OpenDocument used SQLite? (2014)

#274

Post by prewett »

wongarsu wrote: post #186
fluidcruft wrote: post #116

Earlier quoted context omitted.

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…

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

Re: What if OpenDocument used SQLite? (2014)

#275

Post by deathanatos »

stareatgoats wrote: post #119

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what yo…

That's um… quite the aside. How can it possibly claim that to be well-defined, given that `manifest` and `versionId` are not functionally dependent¹ on `max(checkinTime)`?

¹e.g., there could be two rows with the same checkinTime, whose value happens to then be the max such.

Re: What if OpenDocument used SQLite? (2014)

#276

Post by orf »

hot_gril wrote: post #272
orf wrote: post #269

Earlier quoted context omitted.

The issue is incremental updates, and this is where things get complex. If you have a file embedded in the middle of a zip file that is 100 bytes, and you want to resize it to 150 bytes, how do you do that? You can’t squeeze it in without moving everything else about, which disrupts other readers. You could append it to the end maybe, but you need to handle concurrent writers. Compression also is an issue here - I ex…

So basically ODF loads everything into memory, relies heavily on in-memory structs for quick unsaved updates, and is crash-safe by writing the whole zip to a temp location during saves. Kinda similar to MS Office. The file structure also helps a little. This is good enough for small docs. Many times have I encountered large docs, often spreadsheets, that push the limits here and become noticeably slow. If you want to…

> Anything else would be reinventing the same wheels SQLite has spent decades refining.

Which is exactly the problem. They (I.e one dude?), and they alone have spent decades refining a single implementation.

Before we go and lock the entirety of the worlds documents into what’s essentially a proprietary format specific to a single implementation of a single library written by a single dude… we should double check if that’s a good idea or not, and if we can, collectively, solve some of these issues without reimplementing the whole of SQLite.

Because that’s complex. Perhaps more complex than it needs to be for most applications, which would benefit from the storage part more than the query part. And then we are back at the start of our discussion?

Re: What if OpenDocument used SQLite? (2014)

#277

Post by deathanatos »

mrighele wrote: post #132
stareatgoats wrote: post #119

As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what yo…

> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from vers…

> or something like that

That query isn't guaranteed to produce a well-defined result in most SQL engines. (For pretty much the same reason the original doesn't/can't/shouldn't…) In the simple case of two rows with the same `checkinTime`, many engines permit the results to be ordered arbitrarily.

Re: What if OpenDocument used SQLite? (2014)

#278

Post by lucideer »

btilly wrote: post #231
bane wrote: post #157

Earlier quoted context omitted.

I'm not sure if the problem you are pointing out has to do with: a) SQLite the file format - which is Public Domain and so well documented that parsers for it exist in numerous other languages even though it's almost pointless because... b) SQLite, the Public Domain (and thus entirely source available) C implementation of the library that can operate on the file format -- and is documented to a level well above what…

It is possible that the C implementation of SQLite is the single most commonly deployed software library ever. If not, then it is probably the second, after zlib. https://www.sqlite.org/mostdeployed.html Therefore I consider it a better supported format than most standardized formats.

That page makes the argument for zlib & sqlite, but Daniel Stenberg makes some good points here[0].

My guess would be zlib is still number 1 though, even accounting for Daniel's considerations.

[0] https://daniel.haxx.se/blog/2021/10/21/the-most-used-softwar...

Re: What if OpenDocument used SQLite? (2014)

#279

Post by hot_gril »

orf wrote: post #273
hot_gril wrote: post #262

Earlier quoted context omitted.

> Wouldn't it be better to decouple those: provide an open, standard format that enables compact, fast, structured storage that is built to allow transaction/atomic updates. The high-level software abstraction approach doesn't hold up when it comes to databases. This is such a wide and performance-critical interface that any abstractions are gonna leak badly. Even the SQL standard has all these impl-specific flavors.…

You’re totally right, but there are a few things missing: this isn’t a DBMS, really, and the files are not going to be huge. You need fast listing/pagination, key value get/set, and transactional updates. Basically DynamoDB, but for a single file. Build a query layer on top of that, sure. Use those primitive to build persistent indexes if you want. Or just iterate through the keys in a for loop. It fits in memory any…

I don't know, probably a lot of us have dealt with large docs that become noticeably slow to edit and scary to save, mostly spreadsheets.

Re: What if OpenDocument used SQLite? (2014)

#280

Post by orf »

hot_gril wrote: post #279
orf wrote: post #273

Earlier quoted context omitted.

You’re totally right, but there are a few things missing: this isn’t a DBMS, really, and the files are not going to be huge. You need fast listing/pagination, key value get/set, and transactional updates. Basically DynamoDB, but for a single file. Build a query layer on top of that, sure. Use those primitive to build persistent indexes if you want. Or just iterate through the keys in a for loop. It fits in memory any…

I don't know, probably a lot of us have dealt with large docs that become noticeably slow to edit and scary to save, mostly spreadsheets.

Ok cool: so adding SQL to that is going to magically speed it up?

No. It’s the on disk format that matters. Because it would be just as slow and scary if it used a sqlite file that was embedded in a zip file or something equally as mad.

It’s not the SQL, it’s the file format.

If you decouple the file format from the SQL engine, it becomes simpler to reimplement, more agnostic and less vendor locked.

Post reply on HN