6 ms·

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 do

• severak_cz 3y ago
  SQLite is already used for exactly this purpose. It's used as OGC GeoPackage and Mabox/Maptiler datasets use this.
• punnerud 3y ago
  Have you checked the Apple apps? Most of them use SQLite as storage format. iMovie, iPhoto, Voice recording… Same with Docker. Can’t be that wrong?
  • tuyiown 3y ago
    App using a format specific to their own and unique implementation, that ends up kind of proprietary is perfectly ok. Using it for a open specification which target is cross implementation compatibility makes the move way more hazardous. Meaning, every implementation has to run on environment targetable and compatible wit sqlite or has to re-implement a compatibility layer on something complex enough that you only reliable definitive source of truth is the very famous sqlite test suite. It the same reason why Web SQL has being abandoned: if sqlite is the sole api implementor, it takes precedence on any others specs, and you have no control on your standard. I would be 100% for a specification on how to map open docs files to an relational structure, though, with a well know sqlite-backed implementation.
    • bawolff 3y ago
      > Meaning, every implementation has to run on environment targetable and compatible wit sqlite Well i get what you are saying, sqlite has been ported all over the place. It probably wouldn't be the limiting factor portability wise.
      • tuyiown 3y ago
        Yes, right now, no problem, and there little foreseeable future where a sqlite ported to anything would pose a problem. Still, decisions with no way back like this one requires extra cautions.
  • JimDabell 3y ago
    The Apple apps are using Core Data, which uses SQLite as its persistent store by default. So Apple could in theory migrate away from SQLite by changing Core Data’s behaviour without any application-level impact. So in a way, these applications are already decoupled from SQLite in the way the parent comment suggests.
• jve 3y ago
  - Why not? https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html - Its size is less than a megabyte: https://sqlite.org/footprint.html https://sqlite.org/footprint.html - 750KB if all features are enabled: https://www.sqlite.org/about.html 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 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 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.
  • Ensorceled 3y ago
    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.
    • ncruces 3y ago
      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/locrsf.html https://www.sqlite.org/lts.html https://www.sqlite.org/lts.html
      • Ensorceled 3y ago
        Which is why I was clarifying the original complaint and not supporting the original complaint.
      • 5e92cb50239222b 3y ago
        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.
• fweimer 3y ago
  I found the transactional aspect surprisingly difficult, especially with concurrent file access. SQLITE_BUSY handling was quite hard at the time. I know that serialization failures are expected in transaction processing, but for SQLite it was very difficult to tell persistent failures (say, due to self-deadlock) apart from transient concurrent update problems. For transient failure, you can re-execute the closure defining the transactional operation, but for persistent failure, that's of course pointless. Part of the problem is that sqlite3_stmt combines aspects of both prepared statements and result sets. There is a tendency to keep them around to cache the compiled bytecode (prepared statement), but your might code might stop mid-iteration (result set), maybe holding a lock at this point. This can lead to surprising lock-upgrade failures. In the end, I wrote extensive error reporting using sqlite3_next_stmt, sqlite3_stmt_busy, sqlite3_sql, just to weed out those issues. The entire transaction retry code I wrote is full of optional logging and many comments, even though it was just for my own personal use. Before that, I wrote transaction retry logic for PostgreSQL, and that was so much easier (but it was before fully SERIALIZABLE transactions arrived). The other surprise is that “ A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.” (https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous), but that wasn't relevant to my application.
  • HelloNurse 3y ago
    If you retry your write several times and it doesn't succeed you can tell the user it is a persistent failure without agonizing too much over the diagnosis: it is persistent enough to be a significant problem, even without proof that it is an application bug. Who would attempt to make concurrent writes to an application document format? And how wouldn't such an attempt be a user mistake? Failing to write is the solution, not the problem.
    • fweimer 3y ago
      These concurrency failures in transaction processing can be quite rare, but you have to fix them if you want 24/7 unattended operation. SQLite only has timeout-based conflict detection, so you basically have to decide whether you want to wait 60 seconds (or so, depends on how glitchy your storage is) before reporting a potential self-deadlock, which isn't great for development, or risk failing unnecessarily when actually running the job. I had no idea what the right timeout was, which is why I wrote some of the custom self-deadlock detection logic. I think I got to run it completely reliable in the end (no false aborts even under load), but as I said, it was surprisingly hard. By the way, concurrent read/writes on locally stored documents happen, even on single-user machines. If the reading process uses the SQLite structure (say a document indexer that knows about the format), it has to take some locks and may also need to flush data from the WAL log (depending on implementation details). At this point you have to deal with concurrency issues in the application, too. Unless you rewrite the entire document from scratch on every save and put it in place with an atomic rename (which I ended up doing for a different application, not the transaction-processing one). But that loses some of the advantages of SQLite.
• amiga386 3y ago
  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 patents that might affect it, and show me the multiple compatible implementations of SQLite that _all_ have these touted advantages, and _then_ we can talk about prosletizing it as a file format. If they don't, they're saying "take a hard dependency on a single-source implementation, and make all your users take it too". XML is a formal standard. ASN.1 is a formal standard. JFIF is a formal standard. Even ZIP is a formal standard (adopted as part of standardising OpenDocument: ISO/IEC 21320-1:2015) The most important thing about a document is that everyone _else_ can read it. Saving time on writing updates to disk is an irrelevant sideshow. Did we learn nothing from Microsoft perverting the standards bodies to try and keep its lock-in? https://arstechnica.com/uncategorized/2008/10/norwegian-standards-body-implodes-over-ooxml-controversy/ https://arstechnica.com/uncategorized/2008/10/norwegian-stan... > A letter of resignation written by the departing members and made public by The Inquirer accuses the standards body of folding to pressure from Microsoft, violating its own procedural rules, and ignoring the analysis of the technical committee tasked with evaluating OOXML.
  • numeromancer 3y ago
    How much work does it take to go from an engine that can read standard XML to one that can read an ODT document's XML and do something useful with it? At what point of complexity does that engine create a de facto standard?
  • tl 3y ago
    Performance matters and is sufficiently captured via working incremental updates. The single largest upside of a proposal like this is captured by using SQLAR over ZIP. That's what the Library of Congress does when SQLite claims them as a proponent. It's what Fossil does as others in this thread have pointed out. It's suggested as "first improvement" in the linked article. It's also the only part that should actually be considered for implementation. You are right to point out the folly of deeper implementations like having and needing to understand table structures for things like slides. However, the current status quo involves Microsoft implementing a fairly esoteric "update the XML file's bytes as they would be encoded in a ZIP file" in their proprietary tool (where they have enough money to invest the engineering time) and all other tools use the slower "whole file in memory" approach. User visible features like incremental fast saves (and shared editing) keep people on closed systems and give Microsoft the leverage to do the things you warn against. SQLite as a container format could have prevented that by giving everyone a shot at a lower cost but still fast implementation.
• rakoo 3y ago
  To add to your point, fossil, the versioning system designed by the people of SQLite, and using SQLite, doesn't even use SQLite as a file format. It's all a bunch of blobs, each with its own format, that happen to be stored on SQLite. SQLite offers safe storage and a bunch of helpful indexes and views, but is not necessary for fossil-the-data to work.
  • livrem 3y ago
    Looking in sqlite.fossil there are 27 tables in it and most are not used for storing blobs. I know when looking up how to do things in the past the answer has sometimes been "run this SQL query". The event table for instance looks like a list of all commits with dates and comments etc. There is a config table that looks like the kind of stuff git stores in .git/config (URL to upstream repo etc) and so on. Well, yes there are some blobs in it too.
    • rakoo 3y ago
      As described in https://fossil-scm.org/home/doc/trunk/www/tech_overview.wiki https://fossil-scm.org/home/doc/trunk/www/tech_overview.wiki, all the commits are stored as artifacts, and then fossil creates metadata tables for quick access to useful information. Configuration of a repo indeed isn't defined as an artifact but as a SQLite table. One may wonder if this should be part of a repo, and I would say it should, so it actually is surprising that it's not also stored as artifacts
• hot_gril 3y ago
  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).
  • orf 3y ago
    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.
    • hot_gril 3y ago
      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?
      • orf 3y ago
        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