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

261–270 of 310 posts

Re: What if OpenDocument used SQLite? (2014)

#261

Post by hot_gril »

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…

You do need all these things for these applications. Efficiently and safely querying and writing data is central to any document format; you'll leverage both the file structure and in-memory structs to do this. SQL would probably work for this, in fact it's especially natural for spreadsheets (rows x cols).

Re: What if OpenDocument used SQLite? (2014)

#262

Post by hot_gril »

orf wrote: post #45
jve wrote: post #29

Earlier quoted context omitted.

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

> 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. Many have tried to build layers on top that'll work with multiple DBMSes, and it's never worth. Anyone writing an app backend is just gonna marry a particular DBMS for the performance benefits (puns intended).

If for some reason an alternative implementation really needs to exist, SQLite is simple and open enough that someone can do it.

Re: What if OpenDocument used SQLite? (2014)

#263

Post by zie »

bane wrote: post #157
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…

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…

100% agree and the Library of Congress loves it: https://www.loc.gov/preservation/digital/formats/fdd/fdd0004... and https://sqlite.org/locrsf.html

Re: What if OpenDocument used SQLite? (2014)

#264

Post by EricRiese »

mixmastamyk wrote: post #222
skybrian wrote: post #219

Implementing versioning in the file format conflicts with git, because each document is essentially its own little source control system. This can be surprising to users who copy the file and don’t realize that they’ve effectively copied the entire repo. Copying a file will sometimes include drafts they didn’t want to share. It can mean you lose control over when things are committed, and so you don’t end up with a u…

It's pretty rare to put office docs into version control, as they are typically binary instead of text. So, doesn't work well. Perhaps there is a version of open-doc that doesn't use the zip file but a folder of XML instead? Also the XML might need to be optimized to prefer line-oriented operations.

Yes, in LibreOffice you can save as FODT: flat ODT, which is a single unzipped XML. That's what I use to store my resume in git.

Re: What if OpenDocument used SQLite? (2014)

#265

Post by hot_gril »

Ensorceled wrote: post #40
jve wrote: post #29

Earlier quoted context omitted.

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

Once you pick ODF as a format, you're stuck with it forever... except I wouldn't categorize it as powerful.

Re: What if OpenDocument used SQLite? (2014)

#266

Post by orf »

hot_gril wrote: post #261
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…

You do need all these things for these applications. Efficiently and safely querying and writing data is central to any document format; you'll leverage both the file structure and in-memory structs to do this. SQL would probably work for this, in fact it's especially natural for spreadsheets (rows x cols).

I mean, clearly you don’t: OpenDocument works just fine without it.

You need key/value lookup, a way to list/paginate, and transaction semantics for updates.

AKA: a zip file with entries as keys, and XML documents or attachment blobs for values. What’s lacking and causes issues is the update semantics.

You can wack a SQL query language over those 3 operations if you’d like. Or don’t. Up to you, because the format is defined and can be reimplemented rather than the large, complex library api.

Re: What if OpenDocument used SQLite? (2014)

#267

Post by hot_gril »

orf wrote: post #266
hot_gril wrote: post #261

Earlier quoted context omitted.

You do need all these things for these applications. Efficiently and safely querying and writing data is central to any document format; you'll leverage both the file structure and in-memory structs to do this. SQL would probably work for this, in fact it's especially natural for spreadsheets (rows x cols).

I mean, clearly you don’t: OpenDocument works just fine without it. You need key/value lookup, a way to list/paginate, and transaction semantics for updates. AKA: a zip file with entries as keys, and XML documents or attachment blobs for values. What’s lacking and causes issues is the update semantics. You can wack a SQL query language over those 3 operations if you’d like. Or don’t. Up to you, because the format is…

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?

Re: What if OpenDocument used SQLite? (2014)

#268

Post by quickthrower2 »

eastern wrote: post #117
quickthrower2 wrote: post #103

Earlier quoted context omitted.

It matters for 2 reasons. One, the expectation that the file changes only when you click Save is broken (as mentioned in another comment), and Two, unless you pin the version of sqlite forever then the file format may have braking changes or your need to deal with migrations.

> expectation that the file changes only when you click Save is broken This has nothing to do with sqlite. You can have (or not have) gradual saves in any file format. It's a choice that the developers of that app made. > file format may have braking changes The sqlite file format is unchanged for 19 years now. A world of features and capabilities have been added since. Don't hold your breath waiting for the sqlite f…

This is not gradual saves. File changes when nothing has saved even, according to that comment.

Fair enough about the history of it not changing and you can always embed a frozen copy if it does. But this is a pragmatic assumption not a guarantee.

Re: What if OpenDocument used SQLite? (2014)

#269

Post by orf »

hot_gril wrote: post #267
orf wrote: post #266

Earlier quoted context omitted.

I mean, clearly you don’t: OpenDocument works just fine without it. You need key/value lookup, a way to list/paginate, and transaction semantics for updates. AKA: a zip file with entries as keys, and XML documents or attachment blobs for values. What’s lacking and causes issues is the update semantics. You can wack a SQL query language over those 3 operations if you’d like. Or don’t. Up to you, because the format is…

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 expect zip compression is applied to multiple files at once, rather than per file? So now you might need to update multiple seemingly unrelated files.

You need to step down from the concept of a whole file as a unit and move towards pages of data that can be incrementally updated/reused/freed, where each page might contain one, many or even only a part of a “unit” (file/row/whatever)

This makes things more complex for sure

Re: What if OpenDocument used SQLite? (2014)

#270

Post by hot_gril »

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…

I think SQL, a formal standard, has shown that formal standards fail to define a good way to interact with a database. The only real implementations all broke the standard. And an editable document isn't far from a database.
Post reply on HN