4 ms·

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 defin

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 a trivial task, but it's still simpler than writing an SQL parser, query optimizer, compiler, bytecode VM, full-text search engine, and whatever else Sqlite offers, without any data corruption in the process. If Open Office used SQLite, its programmers would inevitably start using its more esoteric features and writing queries that a less-capable engine wouldn't be able to optimize too well. This isn't a concern for most software. If you're writing a domain-specific, closed-source application where interoperability with other apps or ISO standardization isn't a concern, SQLite is a perfectly fine file format, but as far as I understand the situation, those concerns did exist for Open Office.

coliveira 3y ago
But you don't need a standard, because all interaction between applications and the document is made through SQL. And SQL is standardized (at least the parts that matter). If you have concerns about compatibility, make sure that the document can also be accessed through other databases (like mysql).
- orra 3y ago
  But other databases cannot access sqlite databases, because the file format is internal...
  - bane 3y ago
    https://github.com/pgspider/sqlite_fdw https://github.com/pgspider/sqlite_fdw
    - orra 3y ago
      I'll admit, that's a fantastic third party effort. But there definitely isn't the same level of first party support as there is for zip files.
  - nojvek 3y ago
    SQL file format is very well documented. In some universities it is an assignment to directly read and write sqllite files from disk and understand the paged and blocks structure. You don’t need sql for any of it. https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
    - avinassh 3y ago
      > In some universities it is an assignment to directly read and write sqllite files from disk and understand the paged and blocks structure. do you have any links?
    - dunham 3y ago
      It's interesting that this is a classroom assignment, like the sibling comment, I'd curious which university / class this was. I did the read part (+ query planning) on my own as an exercise, but I haven't gotten around to implementing writing yet. You do need to parse DDL to get the column names, they're stored as a "CREATE TABLE" string. But you don't have to if you want to dump the file without names. https://github.com/dunhamsteve/sqljs https://github.com/dunhamsteve/sqljs
  - hot_gril 3y ago
    They can if they want to, using the standard SQLite lib or their own implementation.
- marcinzm 3y ago
  >at least the parts that matter In my experience every part matters in non-trivial use cases since someone somewhere will use that part.
gwd 3y ago
> Writing an XML parser is not a trivial task, but it's still simpler than writing an SQL parser, query optimizer, compiler, bytecode VM, full-text search engine, and whatever else Sqlite offers, without any data corruption in the process. Just to clarify: You don't actually need to implement all that for it to be a standardized file format, any more than you need to implement all the spreadsheet functionality to be able to read a LibreOffice spreadsheet. All you need to do is to be able to reconstruct the tables. There's no reason, having reconstructed the tables, you couldn't write your own imperative code in the language of your choice to go over them and get whatever information you wanted.
justin66 3y ago
> This isn't a concern for most software. It's not even a concern for the US Library of Congress, which defined SQLite as a recommended storage format for datasets alongside CSV, XML, and JSON.
- nelgaard 3y ago
  But those are completely different uses of a storage format. Library of congress considers if someone a 100 years from now could write a new importer in whatever langauge/AI they might use by then. Office documents are something you send in email attachments to people you often barely know, and expect them to read it in whatever office system they have. And if the recipient uses e.g., Microsoft Word, OFD/Sqlite might not work.
  - justin66 3y ago
    It is true that it requires effort for the developers of a software program to support a given file format. Beyond that I'm not sure what your point is.
    - galangalalgol 3y ago
      Not the op, but one point would be, why did we even pick xml, when we had latex and html? Why is a relational database the right tool for a document format?
    - cxr 3y ago
      They're constrained by different requirements. The comment was clear enough: "those are completely different uses" It's not a hard concept to grasp. There is no riddle to decipher.
  - toast0 3y ago
    > Office documents are something you send in email attachments to people you often barely know, and expect them to read it in whatever office system they have. Eh, if they're not running the same office system, down to patches, you can't really expect much.
bane 3y ago
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 most ISO standards shoot for. It's designed to be used in other software and has bindings for pretty much every major language. c) Some notional OpenDocument stored in a SQLite file that's really just waiting for somebody to make and document. ISO standards are great, but if we had to wait for ISO to define a file format we'd have pitifully little to work with.
- joshspankit 3y ago
  Sounds like a solution is to use the C implementation to define the standard and have it canonized in to an ISO.
  - belenos46 3y ago
    Yeah, a solution in search of a problem.
  - cornstalks 3y ago
    That's what Opus did. The RFC[1] has a base-64 encoded libopus.tar.gz appendix (Appendix A), which is the "primary normative part of this [Opus] specification." If the prose and source code disagree, the source code takes priority and "wins" when it comes to which is normative. I have a love-hate relationship with this approach. [1]: https://datatracker.ietf.org/doc/html/rfc6716 https://datatracker.ietf.org/doc/html/rfc6716
    - dfox 3y ago
      That is common for codec standards, the normative part of many MPEG specifications is the parser/decoder in C-like pseudo-code. What is somewhat unique for Xiph is that their normative reference decoders are actually usable.
    - 3y ago
paulddraper 3y ago
> less capable engine There wouldn't be another engine. It would be SQLite. Period.
cryptonector 3y ago
This could have been used as an opportunity to standardize the SQLite3 DB file format.
sethev 3y ago
You seem to be mixing up the file format with how it's used. An application that uses SQLite's file format would use SQLite's library as part of the application. Yes, it would be quite a lot of work to replicate that library but in the same way that replicating the code that uses OpenDocument's file format would be. The file format itself is pretty straightforward.
jimbokun 3y ago
Just define the schema and the semantics of each column for each table.
patapong 3y ago
This sounds exactly like the argument that killed WebSQL in 2010: https://en.wikipedia.org/wiki/Web_SQL_Database 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.
- bb010g 3y ago
  You can still use <https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql>. <https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web>
hot_gril 3y ago
I've never seen this as a problem, since plenty of random things are distributed as sqlite files. All the remaining questions for ODF would be about the schema design.
eternityforest 3y ago
It almost seems worth giving up ISO for SQLite, but I understand there are real concerns when you get into enterprisy stuff. SQLite is kind of its own standard. It's public domain and they don't do breaking changes all day, and it's in C. As long as C is still viable, SQLite is usable on basically all non embedded platforms, and nobody really needs to reimplement it, unless they want to port it to Rust or something. Not that you'd need to, since it's already very reliable.

coliveira 3y ago

But you don't need a standard, because all interaction between applications and the document is made through SQL. And SQL is standardized (at least the parts that matter). If you have concerns about compatibility, make sure that the document can also be accessed through other databases (like mysql).

orra 3y ago
But other databases cannot access sqlite databases, because the file format is internal...
- bane 3y ago
  https://github.com/pgspider/sqlite_fdw https://github.com/pgspider/sqlite_fdw
  - orra 3y ago
    I'll admit, that's a fantastic third party effort. But there definitely isn't the same level of first party support as there is for zip files.
- nojvek 3y ago
  SQL file format is very well documented. In some universities it is an assignment to directly read and write sqllite files from disk and understand the paged and blocks structure. You don’t need sql for any of it. https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
  - avinassh 3y ago
    > In some universities it is an assignment to directly read and write sqllite files from disk and understand the paged and blocks structure. do you have any links?
  - dunham 3y ago
    It's interesting that this is a classroom assignment, like the sibling comment, I'd curious which university / class this was. I did the read part (+ query planning) on my own as an exercise, but I haven't gotten around to implementing writing yet. You do need to parse DDL to get the column names, they're stored as a "CREATE TABLE" string. But you don't have to if you want to dump the file without names. https://github.com/dunhamsteve/sqljs https://github.com/dunhamsteve/sqljs
- hot_gril 3y ago
  They can if they want to, using the standard SQLite lib or their own implementation.
marcinzm 3y ago
>at least the parts that matter In my experience every part matters in non-trivial use cases since someone somewhere will use that part.

gwd 3y ago

> Writing an XML parser is not a trivial task, but it's still simpler than writing an SQL parser, query optimizer, compiler, bytecode VM, full-text search engine, and whatever else Sqlite offers, without any data corruption in the process. Just to clarify: You don't actually need to implement all that for it to be a standardized file format, any more than you need to implement all the spreadsheet functionality to be able to read a LibreOffice spreadsheet. All you need to do is to be able to reconstruct the tables. There's no reason, having reconstructed the tables, you couldn't write your own imperative code in the language of your choice to go over them and get whatever information you wanted.

justin66 3y ago

> This isn't a concern for most software. It's not even a concern for the US Library of Congress, which defined SQLite as a recommended storage format for datasets alongside CSV, XML, and JSON.

nelgaard 3y ago
But those are completely different uses of a storage format. Library of congress considers if someone a 100 years from now could write a new importer in whatever langauge/AI they might use by then. Office documents are something you send in email attachments to people you often barely know, and expect them to read it in whatever office system they have. And if the recipient uses e.g., Microsoft Word, OFD/Sqlite might not work.
- justin66 3y ago
  It is true that it requires effort for the developers of a software program to support a given file format. Beyond that I'm not sure what your point is.
  - galangalalgol 3y ago
    Not the op, but one point would be, why did we even pick xml, when we had latex and html? Why is a relational database the right tool for a document format?
  - cxr 3y ago
    They're constrained by different requirements. The comment was clear enough: "those are completely different uses" It's not a hard concept to grasp. There is no riddle to decipher.
- toast0 3y ago
  > Office documents are something you send in email attachments to people you often barely know, and expect them to read it in whatever office system they have. Eh, if they're not running the same office system, down to patches, you can't really expect much.

bane 3y ago

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 most ISO standards shoot for. It's designed to be used in other software and has bindings for pretty much every major language. c) Some notional OpenDocument stored in a SQLite file that's really just waiting for somebody to make and document. ISO standards are great, but if we had to wait for ISO to define a file format we'd have pitifully little to work with.

joshspankit 3y ago
Sounds like a solution is to use the C implementation to define the standard and have it canonized in to an ISO.
- belenos46 3y ago
  Yeah, a solution in search of a problem.
- cornstalks 3y ago
  That's what Opus did. The RFC[1] has a base-64 encoded libopus.tar.gz appendix (Appendix A), which is the "primary normative part of this [Opus] specification." If the prose and source code disagree, the source code takes priority and "wins" when it comes to which is normative. I have a love-hate relationship with this approach. [1]: https://datatracker.ietf.org/doc/html/rfc6716 https://datatracker.ietf.org/doc/html/rfc6716
  - dfox 3y ago
    That is common for codec standards, the normative part of many MPEG specifications is the parser/decoder in C-like pseudo-code. What is somewhat unique for Xiph is that their normative reference decoders are actually usable.
  - 3y ago

paulddraper 3y ago

> less capable engine There wouldn't be another engine. It would be SQLite. Period.

cryptonector 3y ago

This could have been used as an opportunity to standardize the SQLite3 DB file format.

sethev 3y ago

You seem to be mixing up the file format with how it's used. An application that uses SQLite's file format would use SQLite's library as part of the application. Yes, it would be quite a lot of work to replicate that library but in the same way that replicating the code that uses OpenDocument's file format would be. The file format itself is pretty straightforward.

jimbokun 3y ago

Just define the schema and the semantics of each column for each table.

patapong 3y ago

This sounds exactly like the argument that killed WebSQL in 2010: https://en.wikipedia.org/wiki/Web_SQL_Database 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.

bb010g 3y ago
You can still use <https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql>. <https://jlongster.com/future-sql-web https://jlongster.com/future-sql-web>

hot_gril 3y ago

I've never seen this as a problem, since plenty of random things are distributed as sqlite files. All the remaining questions for ODF would be about the schema design.

eternityforest 3y ago

It almost seems worth giving up ISO for SQLite, but I understand there are real concerns when you get into enterprisy stuff. SQLite is kind of its own standard. It's public domain and they don't do breaking changes all day, and it's in C. As long as C is still viable, SQLite is usable on basically all non embedded platforms, and nobody really needs to reimplement it, unless they want to port it to Rust or something. Not that you'd need to, since it's already very reliable.