18 ms·

What if OpenDocument used SQLite? (2014)

• out_of_protocol 3y ago
  Other example: raster map tiles (basically up to millions of tiny square pictures) Zip vs tar vs filesystem vs sqlite. Tested all these scenarios, and sqlite was the fastest and the smallest, even beating plain archives with no overhead
  • vetinari 3y ago
    Many filesystems have an issue with tens of thousands or more files in a single directory, which is exactly what you can get with map tiles. No wonder sqlite is faster.
    • m4rtink 3y ago
      Yeah, that's why sqlite was adopted for this back then - many devices still used FAT32 on the storage volumes where tiles we often stored/cached and that had horrendous small file performance - a plain white 130 Byte PNG tile could result in 64 kB being used.
      • vetinari 3y ago
        It is not just fat32 and overhead up to cluster size; once I had 800k tiles in a single directory on ntfs. It was unusable. The only thing that I was able to do is to tar it up and move to a machine with xfs, where I was able to sort it up into more balanced subdirs and then move it back (for processing using windows-only tool). Just tarring that single directory up took several days.
        • 5e92cb50239222b 3y ago
          It's not just ntfs. I tested this a few months ago in a pretty unscientific manner using ~50 million files in one directory. btrfs was unusable (not only that particular directory, but the whole filesystem became noticeably slower). ext4 was ok. xfs didn't break a sweat. I don't recall any practical difference when compared against a nested tree like ├── aa │ ├── aa │ │ └── aaaaf3ee5e6b4b0d3255bfef95601890afd80709 │ ├── ab │ └── ac └── ab ├── aa ├── ab └── ac
          • deleted 3y ago
            [deleted]
      • Tigress8780 3y ago
        Once we had to ship millions of extremely small files to our customer, we ended up throwing them into a MongoDB and serve them with a web server. It worked great. We tried to use an image of traditional filesystems (ext4 and fat32), but with most files being under 1 KiB, it was super wasteful.
        • nxobject 3y ago
          This is an extremely low quality comment, and I accept any downvotes, but I can't resist: would you say that MongoDB was web scale?
          • Tigress8780 3y ago
            The way our "MongoFS" was organized is actually also low quality and probably would fall apart pretty quickly if used as a serious web service. However, it works if you try to quickly deploy and serve millions of files to a small number of clients. Our use case was a Maven mirror for disconnected environment that only contains metadata (i.e. lots of small XML files, without the actual jar). We already had a MongoDB service for some other JSON data, so here we are.
  • liuliu 3y ago
    If SQLite is faster, the problem is the zip library you use. SQLite has a major draw back (and yes, I love SQLite and built a lot of things around it over the years): the blob you get from the DB cannot be mmap and you have to copy it to somewhere else. For zip files, as long as the file is not compressed, you can mmap it (or it is compressed using some exotic encoding such as PVRTC) just fine.
• robertlagrant 3y ago
  > since OpenDocument predates SQLite This shocked me. Impressive how far SQLite's come in such a short space of time.
  • capableweb 3y ago
    Hmm, me too, and Wikipedia says: > OpenDocument - Initial release: 1 May 2005; 18 years ago > SQLite - Initial release: 17 August 2000; 23 years ago Wonder what gives.
    • deleted 3y ago
      [deleted]
    • robertlagrant 3y ago
      Hah - that tallies with my instinct on ODF at least. I'm confused too, then.
    • paradox460 3y ago
      OpenDocument traces it's ancestry to OpenOffice XML format, which traces it's ancestry to StarOffice, which was xmlized around the time Sun bought it in 1999
      • ReactiveJelly 3y ago
        That's fair, I wouldn't standardize on a 1-year-old database.
      • capableweb 3y ago
        Thanks for clarifying the somewhat messy history of the format!
      • filmor 3y ago
        Not to be confused with Office Open XML (OOXML), Microsoft's "standard".
    • deleted 3y ago
      [deleted]
• nyanpasu64 3y ago
  I was optimistic that Audacity adopting SQLite would be a substantial improvement in its file saving capabilities. In practice I encountered many gotchas: - On Linux, saving into a new file onto a root-owned but world-writable NTFS mount created in /etc/fstab, fails due to permission errors or something. Saving into an existing file works as usual. - Files are modified on disk when you edit the project in the program, creating spurious Git diffs if you check Audacity projects into Git as binary blobs. And when you save the file, old and deleted data is left in the SQLite file until you close the project's window (unlike saving a file in a text editor), and you can accidentally commit that into a Git repo if you don't close the window before committing. (I recall at one point that you had to manually vacuum the .aup3 file, but now closing the window is sufficient.) I'm getting Word 2003 Fast Save vibes.
  • rini17 3y ago
    Yes it should replicate the functionality user expects - save everything into temporary file and overwrite the original file only on explicit save action. As for Git, it would benefit from using text format specifically aimed for easy diffing/merging. No idea how easy the sqlite dump is in this regard.
    • bawolff 3y ago
      > Yes it should replicate the functionality user expects Do users really expect this now a days? Most users use cloud apps, and almost all of those save after every operation automatically.
      • raxxorraxor 3y ago
        Which is a compromise for using browsers really. It isn't a good solution and no user really understands this and I believe it is the most hated feature of the new cloud world. Yes, leaving the page open for multiple hours might now allow you to save because your access token expired. No, communication in the background is unreliable too. Autosave is a bad band aid for a bad solution. Doing periodic and automatic saves is good. Doing so on a document "in production" is majorly stupid. Not that I want to accidentally validate the busy work dev ops puts us through.
• vmfunction 3y ago
  At this point, why are we still using JSON/XML when there is SQLite for new projects? Stop the non sense of JSON/XML. SQLite is like json, but very queryable. Just send SQLite files around. MongoDB also saves document db type of store space just FYI.
  • constantly 3y ago
    Any text editor in the world, even the ones that ship with the most barebones shells, can open json and xml and present their data to the user. SQLite files require opening in a DB terminal or using special software to even get to the point where one can see what’s there at all. Further the entire internet basically natively supports XML and JSON.
    • vmfunction 3y ago
      That is a good argument, however many people like some big game development company start to ship with GB of json file, at that point just use SQLite. It will be faster to query load. Also if you look at how DB such as Mongo (Not promoting them in any way), but when Maildir is used aginst Mongo for file storage, Mongo saves a lot of disk space. Again, it is about how we want to store files? NixOS is a quite a way to think about having a file system or db/store.
    • eviks 3y ago
      Outside of simple cases xml is too verbose and ugly (and in these cases usually zipped), so it's not suitable for a poor human with a plain text editor, so that doesn't give you much of a leg. (Json has a higher threshold of complexity before it succumbs)
  • deleted 3y ago
    [deleted]
  • quickthrower2 3y ago
    With JSON/XML the app owner decides the schema of the saved file, as they should. One day Sqlite will do some perfectly fine change that’ll break people who outsource their file format to it. Own your file format! That said there is some nuance and it depends what the user expects. Is you app more of an MSWord where people expect a format that is decades backward compatible and only changes on explicit save, or is it more like a live app with a db back end. If the latter there should be no save concept around the DB file but perhaps a backup and restore function that exports to a controlled format.
• p4bl0 3y ago
  I'm currently working on an application where I use SQLite as the file format. I want to keep a usual workflow for users where you can make edit to your document and it only changes the file when you save it. So to open a file I copy it into the :memory: database [1], then the user can do whatever manipulation they want and I can directly make the change in the database I don't need to have a model of the document other than its database format. And to save the document I VACUUM [2] it back to the database file. It works quite well, at least for reasonably sized file (which is always the case for my app) :). [1] https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html [2] https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html
  • rewmie 3y ago
    > I can directly make the change in the database I don't need to have a model of the document other than its database format. I don't get your point. Are you saying that you don't need to have a model of the document other than the model of the document? What's the nuance I'm missing?
    • socksy 3y ago
      I suppose this is in the context where you will be syncing up the changes to a backend server which will also be storing the document in an SQL database. Normally, you might expect that data format on the client to be JSON/XML/something else, and you'd need to maintain logic that marshalls the document representation SQL <-> In-memory representation <-> Disk format. With SQL on the client, in theory you only now need to maintain SQL <-> In-memory representation Obviously I'm skirting over the format you would use to send either entire documents or partial updates of documents over the wire.
    • torstenvl 3y ago
      An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk. Programmers often have to make software design decisions around how to represent a file in memory in order to manipulate it. For example, if I'm writing an HTML editor, should I mostly treat it like a text file (maybe a gap buffer) with syntax highlighting and auto indentation as an afterthought? Or should I maybe load the whole thing into a tree? What are the robustness and performance characteristics of each? The commenter above was saying that using SQLite made that decision easy. He could keep traditional (or "atavistic" per the commenter upthread, depending on your perspective) load/save semantics while also making the data model easy to work with.
• ealexhudson 3y ago
  ODT was designed to be standardised: while the predecessor format was very similar too, it relies very heavily on XHTML, SVG, and CSS, to name but three (there's a lot more). Without being able to call out to existing standards, the ODT spec itself would suddenly become massive. The effort to update the standards appears to be significant and hasn't progressed much in recent years already :/ I think realistically, an Sqlite format could be offered as an option, but the office doc ship has really sailed. Good argument to formalise the spec of Sqlite as a standard though...
  • dfox 3y ago
    The specification is massive (840 pages) even though it is written in very terse way that does not really specify the effects and behavior, only the syntax. On the other hand if one ignores few warts (explosion of local styles and text spans due to ooo:rsid attribute, non-sparse spreedsheets and weird mechanism for styling tables as a few examples) it is really well designed markup for this kind of document data that strikes right balance between it being semantic markup and representing the kinds of stuff users want to do. Compare that with Office OpenXML with stateful formatting empty tags (yes, really, in DOCX <b/> _TOGGLES_ whether following text is bold).
• littlecranky67 3y ago
  deleted.
  • littlestymaar 3y ago
    > Nobody really believes that OpenDocument should be changed to use SQLite as its container instead of ZIP. […] Rather, the point of this article is to use OpenDocument as a concrete example of how SQLite can be used to build better application file formats for future projects.
• orf 3y ago
  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 there is a better file format we can use, but it would be better if it was decoupled from SQLite.
  • 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.
• iefbr14 3y ago
  Why only documents? How about a SQLitefs?
  • pgeorgi 3y ago
    WinFS (https://en.wikipedia.org/wiki/WinFS https://en.wikipedia.org/wiki/WinFS) without the mssql Engine?
    • iefbr14 3y ago
      Or this: https://github.com/narumatt/sqlitefs https://github.com/narumatt/sqlitefs
      • euroderf 3y ago
        XSLT processors work by accessing the file system. Would this sqlitefs be a way to run XSLT against an SQLite database? Or is there maybe some other way to run a file oriented XSLT processor against an SQLite database in the SQLAR format?
      • euroderf 3y ago
        Homebrew can't install its prerequisite osxfuse onto Ventura. There is also this, which seems to work: https://github.com/jacobsa/fuse https://github.com/jacobsa/fuse and this: https://github.com/jilio/sqlitefs https://github.com/jilio/sqlitefs
      • euroderf 3y ago
        And of course there is sqlarfs, at the bottom of https://www.sqlite.org/sqlar/doc/trunk/README.md https://www.sqlite.org/sqlar/doc/trunk/README.md
• nuc1e0n 3y ago
  It's somewhat off topic I know, but is there something like sqlite but tailored for hierachical data? Like a xml document store rather than for relational data like sqlite is.
  • layer8 3y ago
    There’s ASN.1 for hierarchical data with a schema. It doesn’t provide a query language though.
    • dfox 3y ago
      ASN.1 in itself is a schema syntax. That schema can be serialized into various related forms, but all of them are more or less a transport formats that cannot be reasonably used for random access. There are some more or less general hierarchical formats with support for random access, but most of them are tightly related with particular technology stack (ie. MS's COM Compound Document) or with particular usage area (there is HDF5 for scientific data and many multimedia containers are in fact a hierarchical databases, with both the various IFF variants and EBML being explicitly designed as reusable formats for arbitrary data). And then there are formats that implicitly contain some kind of hierarchical container mechanism (PDF, TIFF, DICOM, FPS game map files…).
• kunley 3y ago
  Love the vibe of artivles, which present let's say reason-driven development vs habit-driven. Why habit? Well, I can imagine back at the time OpenOffice was a fresh project, it went like this: "XML is going to stay forever and everybody uses XML, so ofc we use one... oh, it is so big! And there are many files, so we just zip'em"... To be fair, the author of this excellent article doesn't even say about getting rid of XML in this format- but that could also be achieved by storing stuff in a SQLite file. Usage of XML was habitual thinking there- and not very visionary, as the format is dead now...
  • tpm 3y ago
    > Well, I can imagine back at the time OpenOffice was a fresh project OpenOffice was born when Sun bought StarOffice, which was initially released in 1985 (on Z80 and certainly without any XML). So the project itself was far from fresh. OpenDocument was developed from OpenOffice.org XML format which was developed after Sun bought StarOffice in 1999. At the time XML was not used everywhere, but it was very much in vogue, certainly at Sun where the official line was that Java (created at Sun) and XML are going to conquer the world.
  • galangalalgol 3y ago
    Could you clarify the "XML is dead" comment? Don't all the major document formats still use zipped xml? I had to interface with an xml format recently, and that isn't something I ever did, and when I went looking for a crate that parses an xml schema I kept running across this whole xml is dead thing. But it still seems to be everywhere.
    • kortex 3y ago
      Not GP, but I believe the "XML is dead" sentiment stems from the observation that very few greenfield applications are deliberately choosing xml. Sure you have legacy giants like (X)HTML, SVG, office formats, etc, but you'd be hard-pressed to convince developers (especially a younger crowd) to select it as a data format. It's seen as warty, cumbersome, unwieldy, verbose.
• im3w1l 3y ago
  I don't want people to read my drafts. That could be highly embarassing, and they should not make it into the final saved document. Past version and undo history should be stored separately from the document. They should be stored out of tree where they wont be commited into some git repository or be automatically synced or anything like that.
  • regularfry 3y ago
    I want to be able to read my drafts, until I decide to bake a publication version.
    • im3w1l 3y ago
      Did you read the other part of my comment? Where I said to store the draft, but not in the document itself?
      • regularfry 3y ago
        I did.
  • eviks 3y ago
    Then don't give people access to your drafts but exported versions without history? Why put the limits on the efficiency of a format by forcing it to store changes elsewhere?
    • pornel 3y ago
      It's better if such gotchas don't exist. Otherwise you'll have every user get burned by it at least once, and blaming them for not knowing the subtle consequences of using "Save As" instead of "Export As" is not going to help anyone.
      • eviks 3y ago
        There are plenty of burns on the other side as well with users losing edits and the consequences of copying your file in a file manager with/without some out-of-tree-out-of-sight history are even more subtle. This is an app feature (it doesn't have to be "Export As", it can be a "clean history" toggle in the same "Save As" dialog and a separate command), so not a reason to excise efficient history preservation from the file format
• isoprophlex 3y ago
  Man do I love SQLite. Over the past 1.5 yrs I've build a computer vision tool from recording hardware/software, to derp learning pipelines, to front-end; we had some requirements on the recording side that were difficult to solve with existing solutions (storing exactly timestamped camera frames, gps data, car telemetry and other metadata). Using a SQLite-backed data format for the video recordings made implementing things by ourselves super straightforward.
  • sgu999 3y ago
    I'm working on a similar problem and I've been struggling to convince all my colleagues that we should sqlite most things. By any chance do you have some public code, or blog posts to share?
    • isoprophlex 3y ago
      Not in public repos, but sure. Drop me a line, hn at rombouts dot email.
  • regularfry 3y ago
    > derp learning pipelines This accurately describes the majority of my efforts, too.
    • isoprophlex 3y ago
      Honest to god this was an unintentional typo, but I decided to leave it in as it was just too juicy
• Freak_NL 3y ago
  Good article. Although one thing I do like about OpenDocument being just a bunch of XML files in a ZIP archive is that it is fairly easy to generate documents like spreadsheets without using a (potentially hefty) library which knows about the document format. I have a use case where users of a web service want to use data exported as a bunch of rows in a table in a variety of tools. Now, CSV with UTF-8 encoding is of course, totally open, conventional, and workable, but anyone who has ever offered CSV files to end users will know the pain of these users getting stuck when they want to use these files in a spreadsheet application¹. So I saved a sample spreadsheet in OpenDocument's ODS and another in that Microsoft XML abomination called OOXML as XLSX, and just figured out the basics of those XML formats. I trimmed the ZIP archives down to the essentials, marked the places where content goes, and just build a new spreadsheet file whenever data is requested in that format. Now I can output CSV, ODS, and XLSX (and JSON thrown in for good measure) of the same data. Doing this with SQLite would be possible of course, just a tad more complex and with a lower development speed. Being able to fire up the office suite, create a template document, and just dig into its XML files in the saved file is a nice feature (although admittedly of niche interest). 1: More specifically, users who use Excel in a locale like nl_NL, where CSV files are, hardcoded, assumed to have their columns separated by semicolons, because Microsoft once notoriously decided that the Dutch did not use comma's in a comma separated values file.
  • dfox 3y ago
    As for [1], it is not really hardcoded, but depends on what is the value of localeconv()->decimal_point, if it is “,”, excel uses semicolons both in CSV files and formula expression language. This used to be configurable when opening CSV/TXT file in excel (and still is in LibreOffice) but as a part of the overall UI dumbification was moved somewhere under the “Data” menu/ribbon tab (so you have to open new workbook and find the right option, or well, use LibreOffice if you value your time).
    • Freak_NL 3y ago
      > decimal_point Are you sure that affects it? The decimal point parameter sounds like it decides how to write out 5½ (i.e., 5.5 (English style) or 5,5 (Dutch style)) surely? Although on the topic of this particular bête noire I would not be surprised.
• CodeCompost 3y ago
  There really should be a "NoSQLite" or something equivalent to store hierarchical data instead of normalized data.
  • remram 3y ago
    You can probably use SQLite for that, with a single key-value table.
    • ttyprintk 3y ago
      The json* family of tree and table functions are nowadays built in.
  • OliverJones 3y ago
    It's trivial to implement hierarchical data with recursive common table expressions. https://www.sqlite.org/lang_with.html https://www.sqlite.org/lang_with.html
• cm2187 3y ago
  Sqlite format is smaller than the original format only because xml is super verbose, so any uncompressed binary format ends up being less than lightly zipped xml. But sqlite files aren't small. One thing I don't understand is why they don't do string deduplication in sqlite (as in you only store a string once and every other occurence is just a pointer to that string). It seems such an obvious and easy way to reduce file size, memory consumption and therefore increase performance (less I/O). Is there a technical reason why this would not be desirable?
  • Etheryte 3y ago
    My first guess is that if you always store the full string you don't need to scan the database to see if you already have the same string. Essentially you choose to use more space but reduce load. Regardless of whether you do the string deduping on inserts or async later on, you have to do it at some point and the unpredictable performance overhead might be undesirable.
    • cm2187 3y ago
      Well it should be a dictionary lookup, it should be pretty fast and predictable. And for freeing it up, it should be a good candidate for reference counting.
  • The_Colonel 3y ago
    If you have the same (long-ish) string repeating many times in a database, it points to a DB schema needing normalization.
    • cm2187 3y ago
      I guess it depends on the use case. If you load a csv file into a sqlite database, normalisation isn't the first thing you do.
  • kortex 3y ago
    There is nonzero overhead for doing so: optimizing for duplicate strings invariably adds cost to handling unique strings. This sounds like something you could do at the schema and application level.
• throwaway894345 3y ago
  Is SQLite’s disk format an open, versioned standard? Or is it just “however SQLite saves data to disk”?
  • SQLite 3y ago
    SQLite file format spec: https://www.sqlite.org/fileformat2.html https://www.sqlite.org/fileformat2.html Complete version history: https://sqlite.org/docsrc/finfo/pages/fileformat2.in https://sqlite.org/docsrc/finfo/pages/fileformat2.in Note that there have been no breaking changes since the file format was designed in 2004. The changes shows in the version history above have all be one of (1) typo fixes, (2) clarifications, or (3) filling in the "reserved for future extensions" bits with descriptions of those extensions as they occurred.
    • throwaway894345 3y ago
      Thanks for elaborating so thoroughly. I didn’t even realize you were on this platform!
• vxNsr 3y ago
  I’m curious to know what a gsheet/doc/slide file actually is under the hood. I as the user am only ever presented with a link, there’s no way to download a gsheet in its native format.
• tannhaeuser 3y ago
  Yeah what if? Then they haven't really understood the purpose of markup languages as plain text files for viewing/editing using generic text editors. There was no lack of proprietary formats such as MS Structured Format (used by MSO) and it was considered a big success when customers demanded open formats such as SGML/XML-based ones in late 90s/00's. The alternatives aren't even sequential (have fragments and cross pointers, etc). Yes they might be faster because they're closer to the in-memory representations as used by the original/historic app or even primitive memory dumps; marginal speed or size improvements were never a consideration though. And if anything, SQL (almost as old as SGML btw) is a joke as document query language compared to basically any alternative specifically designed for the job (ISO topic maps query language ie. Datalog, XPath and co, SPARQL, DSSSL/Scheme, ...) because of SQL's COBOLness, non-schemalessness, lock semantics/granularity a really bad fit, etc.).
• Lockal 3y ago
  Sadly they did not include bad sides: 1) Vulnerabilities: not only in SQLite, but also in wrappers like https://nvd.nist.gov/vuln/detail/CVE-2023-32697 https://nvd.nist.gov/vuln/detail/CVE-2023-32697 2) Lack of transparency: zip with xml's contains only xml's; meanwhile SQLite contains by design all kinds of traces with sensitive information or empty blocks. Attempts to fix these issues removes benefits that were mentioned. 3) Lack of implementer support. It was one of the reasons for WebSQL deprecation many years ago. 4) Lack of standardization for file format. SQLite does not even promise forward compatibility, only backward one. Which means that new documents might not open in old software, or vendor should fork SQLite and only backport security patches.
  • jmull 3y ago
    4) is enough for me, so I agree with your general point, but 1) 2) and 3) aren't really cons for SQLite. 1) Makes sense only if the average XML parsers and zip libraries in use have fewer vulnerabilities and are actively maintained as well. 2) You can store sensitive data in a SQLite database or XML file, there's no real difference. You can clean up a SQLite database pretty easily if you want and that doesn't take away all the benefits. 3) What does implementer support even mean? I believe they are open to custom work... WebSQL died because it doesn't make sense to pretend SQLite is some kind of standard -- that brings us back to 4), which is the valid reason to avoid SQLite. Actually, your 4) is worded too strongly. They say they're committed to forward compatibility as long as you don't use the new features. That makes forward compatibility the decision of the app: an app can have forward compatibility and not use newer features OR lose forward compatibility and use newer features.
  • internetter 3y ago
    > Vulnerabilities: not only in SQLite, but also in wrappers like Yes, parsing encoded files tends to introduce vulnerabilities. ZIP parsers have had plenty of vulnerabilities. This is not exclusive to SQLite. > Lack of transparency: zip with xml's contains only xml's Both zips and sqlite cannot be read with a text editor. Both are open formats with widely available tools to read them. The sqlite binary might, in fact, be more widely available than unzipping tools. > meanwhile SQLite contains by design all kinds of traces with sensitive information or empty blocks. Elaborate? > Lack of implementer support. It was one of the reasons for WebSQL deprecation many years ago. I don't understand how this is relevant? > SQLite does not even promise forward compatibility, only backward one. Which means that new documents might not open in old software Neither does OpenDocument. SQLite is actually more solid in this regard – forwards compatibility is still a thing unless new features are used.
• 3cats-in-a-coat 3y ago
  OpenDocument is zipped images and XML. Implying you parse the entire format and put it in RAM. And frankly I don't see how SQLite can improve this. Well XML isn't ideal, but it's zipped, so there's no huge penalty in size here. All benefits SQLite's article lists (and I love SQLite to death by the way) can be implemented by having SQLite be the runtime model of the document. On disk and in memory. But SQLite doesn't need to be the transport format. In fact SQLite can easily get bigger than the current format, SQLite is full of unused space when you mutate it around, it can get fragmented and sparse. And if you need to optimize it every time, then the "fast save" etc. benefit goes away. There are formats which do need delta updates and quick indexed look-ups without fully loading the file in RAM, and this is why so many apps do use SQLite as a file format. I just feel OpenDocument was a bad pick to use SQLite for in this hypothetical scenario.
  • kortex 3y ago
    XML and Zip don't really do incremental updates, meaning the whole application file has to be written on save, meaning corruption can occur due to hiccups mid-write. Sqlite as a disk format and the right application implementation means you can't end up in a corrupted state. I think you can achieve the same thing with xml/zip and some rename shenanigans, but sqlite lets you get that in a single file on disk. Also if you are using sqlite as the memory model, why not use it as the disk/transport format? It's basically free at that point. The file size issue can be dealt with VACUUM (I believe, haven't personally dealt with sqlite-as-file-format).
    • 3cats-in-a-coat 3y ago
      Incremental updates don't matter in a transport format. The claim "it's basically free" isn't right, as for transport you need to VACUUM. And possibly COMPRESS too. And if you do that... might as well use the existing format. VACUUM completely rewrites the file from scratch. You can't do incremental updates in a VACUUMed file as it stops being VACUUMed, so you need to VACUUM it again to ensure minimal file size. Nothing is free. ZIP also can be incrementally updated (file by file) by the way, I think MS Word uses this feature in some saves. But that's beside the point. You simply do not need incremental updates in a transport format. I'm not sure what "hiccups mid-write" you're referring to. Any such hiccup that would damage an XML or ZIP file would also damage an SQLite file. The distinction between a working disk file and a transport format are important. The working disk file is large, binary, messy, complex, optimized for quick look-ups and quick partial updates. If your word processor crashes, it can restore state from the working disk format in no time. But the transport format needs to be small, readable, debuggable, simple, stable. And SQLite simply doesn't offer anything significantly superior in that department compared to the existing format. Especially nothing to justify the additional effort of changing an already working solution. There's a reason "serialization" is called that, it's just serial data. No random access structures, no indices, single representation, often text-based. Throughout the decades, we've learned this is the best way to transport data of any kind. The messy/partial/polymorphic/cryptic/hyperoptimized/indexed formats are not for transport. They're intended to do work in, locally.
• stareatgoats 3y ago
  As an aside, this blew me away. I can hardly believe it. No nested query required? > SELECT manifest, versionId, max(checkinTime) FROM version; > "Aside: Yes, that second query above that uses "max(checkinTime)" really does work and really does return a well-defined answer in SQLite. Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: it returns the manifest and versionId of the entry that has the maximum checkinTime.)"
  • mwexler 3y ago
    It's not really what one would expect in SQL, but SQLite often defies expectation. In this case, handy, but non-standard.
  • mrighele 3y ago
    > Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect: It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank. Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one. > select manifest, versionId, checkinTime from version order by checkinTime desc limit 1 or something like that. This should work in SQlite and Postgresql at the minimum. I think to remember that in Oracle you have to use "where rownum=1" so indeed you have to use a nested query. I don't know about other databases.
    • globular-toast 3y ago
      The interesting thing is if you want more than one record, like you want the latest version number for each document ID. In SQLite you could do: `SELECT documentId, versionId, max(checkInTime) FROM version GROUP BY documentId`. In Postgres you can do `SELECT DISTINCT ON (documentId) documentId, versionId, checkInTime FROM version ORDER BY versionId, checkInTime DESC`. See: https://www.sqlite.org/lang_select.html#bare_columns_in_an_aggregate_query https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...
    • paulddraper 3y ago
• chadcmulligan 3y ago
  AutoCAD uses a database as its file format, it is fairly slow.
• gwbas1c 3y ago
  I shipped a product that used both SQLite and XML files. One of the improvements that I made was moving a few tables that contained small amounts of data to xml files. Because these files were small and rarely written; it simplified the data access layer, and simplified diagnostics. (I made sure the files were multi-line tabbed xml.) For "technical" people who needed to diagnose the product, asking them to crack open a SQLite database was a huge ask; but for the major part of the product that used SQLite, it was hands-down better than XML files. (An older version of the product used XML files. It had scalability problems because there's no good way to make an incremental update to an XML file.) The advantages of XML, specifically, a human-readable format; really only work for small files when the design of the schema is optimized for readable XML. Unfortunately, the need to always rewrite the entire XML file, and the "complexities" that come with lots and lots of features will quickly erode XML's biggest advantages. IMO: A "lay" person needing to muck around with the internals of an office document is fringe enough that learning to use a SQLite reader is an acceptable speed bump. The limitations of XML + Zip, when it comes to random writes in the middle of a file, just can't be overcome by Moore's law.
  • Tempest1981 3y ago
    I'm unclear on how SQLite (native format, no zip) is achieving sizes similar to XML + Zip. Are SQLite TEXT or BLOB fields compressed? Or are they assuming the caller is compressing BLOBs before writing?
    • gwbas1c 3y ago
      SQLite does not compress, as far as I know. Engineering is all about tradeoffs: SQLite is optimized for quick incremental updates where you don't need to rewrite the whole file. Zip & xml aren't. (IE, if you decide to add a letter to a word at the beginning of a document, with zip & XML you have to rewrite the whole document. SQLite can make a minor change without the whole rewrite.) In our case, file size was not a factor in choosing between SQLite and XML. But, remember that file size is deceptive: Disks are block devices; the 30 byte and 1k file take up the same space if you block size is 2k. (I've shipped a filesystem driver.) HTTP servers gzip on download. It's more important to know your needs than to get hung up on a single metric like file size. > I'm unclear on how SQLite (native format, no zip) is achieving sizes similar to XML + Zip. Are SQLite TEXT or BLOB fields compressed? Or are they assuming the caller is compressing BLOBs before writing? Remember, XML writes each tag name 1 time if there's no content and twice if there is. Each attribute has it's name written every time. I doubt SQLite writes all the metadata in each row.
• miki123211 3y ago
  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.
• MrResearcher 3y ago
  BLOBs in sqlite can be up to 2GB or less, depending on the compilation flags. If you store 2GB and the other application uses sqlite compiled with support for less than 2GB BLOB size, good luck on getting them to work... If you want to store content larger than 2GB in sqlite, you have to chunk them, manage the chunk sequences, etc. And you can't overwrite a fixed size 2KB portion at the specified offset, you'll have to rewrite the entire 2GB chunk.
• indymike 3y ago
  > there's no ISO standard defining how to interpret an SQLite file in excruciating detail. There comes a point where ISOing things doesn't help. The SQLite format belongs to SQLite, and an ISO standard would result in that standard being rendered irrelevant by the SQLite team, should they wish to make a change for any reason. Also, people would have to pay ISO for access to the specifications. SQLite should be treated as a defacto standard defined by the SQLite project.
  • didntcheck 3y ago
    Just a heads up that it looks like you meant to reply to miki123211, but you've posted a top-level comment instead :)
    • deleted 3y ago
      [deleted]
  • deleted 3y ago
    [deleted]
• samjohnation111 3y ago
  [dead]
• ongytenes 3y ago
  Would be interesting to see a fork implementing SQLite. Time would tell how well it would compete with the standard.
• roywashere 3y ago
  (2014)
• kgeist 3y ago
  Sqlite-based file formats are also very easy to debug, which saves a lot of dev time. After my app writes to a file and loading back doesn't work, I can just open it in Sqlite and inspect it in any way I wish because I have the full power of SQL at my fingertips.
• deleted 3y ago
  [deleted]
• skybrian 3y ago
  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 useful history. If you then check the file into git, you are storing one source control system into another one, and older versions appear in two different histories. To be git friendly, you don’t want to save anything other than the current version, and then let git do its thing. Possibly the answer is “don’t use git, we have it covered,” but then the app developer should realize that they are implementing something like a source control system. How do people share drafts, review them, and merge changes? How do you publish a release that only includes the version you wanted to release? And it does seem relevant that the developer of Sqlite actually did implement their own source control system [1]. Maybe they could have warned people about what they’re getting themselves into if they go down this route? I wonder how terrible it would be to either use a git repo as your file format, or to build in git compatibility into your app somehow so you could push and pull? [1] https://en.m.wikipedia.org/wiki/Fossil_(software) https://en.m.wikipedia.org/wiki/Fossil_(software)
  • mixmastamyk 3y ago
    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.
    • EricRiese 3y ago
      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.
    • skybrian 3y ago
      Yes, it's rare to use git, but it's also pretty well-known that people can share more than they intended in a Word document. Perhaps true of Open Office as well? See: https://superuser.com/questions/1562130/can-people-see-the-changes-made-in-word-document https://superuser.com/questions/1562130/can-people-see-the-c... https://foiassist.ca/2019/04/04/i-thought-we-deleted-that-metadata-and-hidden-data/ https://foiassist.ca/2019/04/04/i-thought-we-deleted-that-me...
• swiftcoder 3y ago
  > The use of a ZIP archive to encapsulate XML files plus resources is an elegant approach to an application file format. It is clearly superior to a custom binary file format. I feel like I have considerable disagreement with the author of these sentences.
  • simonw 3y ago
    Why do you disagree?
• michalc 3y ago
  Shameless plug of a couple of Python libraries I’ve been involved with that work around memory issues of ODS files (for very specific use cases): https://github.com/uktrade/stream-read-ods https://github.com/uktrade/stream-read-ods https://github.com/uktrade/stream-write-ods https://github.com/uktrade/stream-write-ods
• dang 3y ago
  Related: What If OpenDocument Used SQLite? (2014) - https://news.ycombinator.com/item?id=25462814 https://news.ycombinator.com/item?id=25462814 - Dec 2020 (194 comments) What If OpenDocument Used SQLite? - https://news.ycombinator.com/item?id=15607316 https://news.ycombinator.com/item?id=15607316 - Nov 2017 (190 comments)