Hacker News Reader

Comment by jve | Hacker News Reader

- 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 databases. SQLite competes with fopen()": https://www.sqlite.org/whentouse.html

In the end, you don't need a database, but a library that gives you database API and behavior.

View on HN

> 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. Because you don't _need_ any of SQL, or really sqlite to improve the OpenDocument format. You need the storage format.

OpenDocument is very different from the pretty scientific/niche/highly-vendor-locked examples given in replies by others here. Locking this into a format developed by essentially a single person with a single implementation is absolutely mad.

But... it's less mad if the file format wasn't coupled to sqlite.

A great thing about just using sqlite as the format is that you get lots of potential features. Sure most applications don't need full SQL power just to save and load data. But then at some point you might want more advanced functionality, or migrate to a new structure. And both you and your users get tools for free, e.g. to extract data or fix problems, or just look around. Other applications can quite easily read your files, without you needing to write various language libraries. Very few projects get around to building that kind of tools for their made up format.

I could agree about the single implementation, but if the alternative is making something new up I am not sure in what way that would be better.

The Sqlite format is open and the spec is here: https://www.sqlite.org/fileformat2.html

I haven't studied the spec in detail but it seems comprehensive.

The fact that there also exists a high-quality, stable, public domain reference implementation can't really be counted against the format, can it?

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

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.

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.

SQLite (not just the language SQL) would make it much easier to reimplement in a way that's fast and safe, yes.

> If you decouple the file format from the SQL engine

That alone would be a difficult project. If you really want to break things down, easier to say your document standard relies on SQLite's rather simple query language (https://www.sqlite.org/lang.html), and there, it's independent of SQLite's query planner and file format. Wouldn't be hard to make it work with Postgres or MySQL, for instance.

I’d love to understand your thinking behind the idea that a document standard should rely on a query language and not a file format…

Document standards are file formats…

Or are you saying a document format should just be some DDL statements? What? How is that interoperable? It’s coupled to the database that is storing the data as an implementation detail, which is exactly the problem with using SQLite.

> That alone would be a difficult project

I’m not suggesting using the SQLite file format, I’m suggesting the pretty basic idea that the storage for a general purpose widely used and interoperable document format should be logically decoupled from anything else, and definitely not be tied to the implantation details of a single library or even a single version of that library.

The file format is the most important part. It’s the only part. Nothing else matters because there is nothing else.

It’s not rocket science.

> Or are you saying a document format should just be some DDL statements? What? How is that interoperable?

Yes. How is it interoperable, because it's quite easy to make DDL for SQLite that also works for many other DBMSes, given that SQLite is kinda the lowest common denominator of those.

Maybe not as interoperable as ODF since it's easier to implement an ODF parser/writer than a SQLite clone, but probably more interoperable than some kind of advanced ODF designed for efficient updates. Just because you define a standard doesn't mean there are good portable implementations out there.

> Maybe not as interoperable as ODF since it's easier to implement an ODF parser/writer than a SQLite clone

Ladies and gentlemen: he’s so close, he’s nearly there, but he just can’t make the final connection!

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.

Which is also "not a big issue", since it's a recommended Library of Congress storage format, and supported long term:

https://www.sqlite.org/locrsf.html

https://www.sqlite.org/lts.html

Which is why I was clarifying the original complaint and not supporting the original complaint.

It is somewhat of a problem: the development team is very small, they don't take outside contributions (so nobody outside the core team really builds up expertise over time), and the vast majority of tests are proprietary. I hope they have a contingency plan just in case (some sort of a dead man's switch that publishes the test suite under a permissible license) as it would probably be quite difficult for others to maintain the same quality without those tests, or re-implement them in a reasonable time frame.

I'm pretty sure this is why libsql was created https://github.com/libsql/libsql

But that equally applies to getting critical bug fixes for your particular usage scenario of SQLite. It's not just about the viability as a storage format.

For the latter, because the stored data has such a simple format and the implementation has so few dependencies, I expect it will be very easy to get your data out for a long time to come. It's going to be tougher if you have business logic in views or other SQL expressions, of course, and if you rely on SQLite's particular approach to data types (as in “values have types“, but not much more).

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

Reply on news.ycombinator.com