4 ms·

> 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

by orf 3y ago

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

nurbl 3y ago
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.
colonwqbang 3y ago
The Sqlite format is open and the spec is here: https://www.sqlite.org/fileformat2.html 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?
hot_gril 3y ago
> 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.
- orf 3y ago
  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.
  - hot_gril 3y ago
    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.
    - orf 3y ago
      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.
      - hot_gril 3y ago
        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 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.

by orf 3y ago

nurbl 3y ago

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.

colonwqbang 3y ago

The Sqlite format is open and the spec is here: https://www.sqlite.org/fileformat2.html 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?

hot_gril 3y ago

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

orf 3y ago
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.
- hot_gril 3y ago
  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.
  - orf 3y ago
    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.
    - hot_gril 3y ago
      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 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.