16 ms·

What If OpenDocument Used SQLite?

305 points by ejstronge 9y ago

Const-me 9y ago
Please don’t. When people are sending or sharing a document, they don’t expect to leak edit history of that document. And SQLite inherently leaks that edit history, in database freed pages and unallocated space. You can VACUUM the database after each save, but that’ll negate the performance benefits. Also VACUUM was designed to save space, not sure it reliably cleans up unwanted data. OTOH, these zipped XMLs are free from unwanted data, they (hopefully) only contain the data user wants to save, i.e. the content of the last version of the document.
- chj 9y ago
  People should publish PDF version of a document. But if they send the working copy around, then the history may be helpful for other collaborators. Think of git. They can even merge the changes easily.
  - aaronchall 9y ago
    Agreed. History should be considered a feature.
    - joosters 9y ago
      And it is. That's fine, as long as the user actively turns that feature on, and by default no data is leaked.
  - kbenson 9y ago
    Unfortunately, companies don't do that. They send the doc/docx. They also send the xls/xlsx instead of the csv. Protecting users from accidentally doing a very wrong thing is better than being slightly more helpful as a default. Security should be the default. IIRC there have been cases where confidential information has been found by looking through the history in government released documents.
    - Const-me 9y ago
      docx/xlsx are better, they are zipped XMLs too, just like odf. Older ones, doc & xls are closer to sqlite, for the same reason, performance. I remember finding confidential information in these files received from third parties they probably didn’t want to disclose, e.g. offer price that was before the last edit.
    - bigiain 9y ago
      You know you can pop calc with a csv file opened in Excel? from http://georgemauer.net/2017/10/07/csv-injection.html http://georgemauer.net/2017/10/07/csv-injection.html : UserId,BillToDate,ProjectName,Description,DurationMinutes 1,2017-07-25,Test Project,Flipped the jibbet,60 2,2017-07-25,Important Client,"Bop, dop, and giglip", 240 2,2017-07-25,Important Client,"=2+5+cmd|' /C calc'!A0", 240 Yeah - that "=2+5+cmd|' \c calc'!A0" gets Windows to execute code...
  - gumby 9y ago
    > People should publish PDF version of a document. Oh please no! PDF is quite user-unfriendly (e.g. can't reflow, typically can't cut/paste properly) designed to emulate an ancient medium that has benefits and drawbacks different from screens. And PDF, while executable, is essentially compiler output, with all the destruction of semantics that implies.
    - abtinf 9y ago
      It depends on what your are trying to do. Short of exporting to an image format, PDF is the best format for preserving and sharing a final rendered output. All of the things you list as drawbacks are virtues if your purpose is static presentation.
    - pjmlp 9y ago
      I only have issues with ebooks, outside them it is just perfect. Cut/paste only fails on me, when whoever created the PDF had the brilliant idea of using images instead of actual text, e.g. scans. Something that using another format won't help. Maybe you should use another PDF reader?
    - Fnoord 9y ago
      For a resume, PDF is ace. I don't want the reader to see version history or copy/paste (even though its peanuts with a half decent PDF reader). I want them to print it.
- SQLite 9y ago
  The "PRAGMA secure_delete=ON;" command will fix that for you. Or you can make secure-delete the default at compile-time using the -DSQLITE_SECURE_DELETE option. There is a (small) performance hit, since extra writes have to occur to overwrite the obsolete data with zeros, but it does work. See https://www.sqlite.org/pragma.html#pragma_secure_delete https://www.sqlite.org/pragma.html#pragma_secure_delete for further information.
  - Const-me 9y ago
    Not enough. See my next comment: https://news.ycombinator.com/item?id=15607650 https://news.ycombinator.com/item?id=15607650
- Const-me 9y ago
  P.S. Even fragmentation can leak data. E.g. search for the most fragmented portion of the document in the DB, and you'll likely find out the most heavily edited paragraph of the document.
  - JohnBooty 9y ago
    I believe that you raise excellent concerns. I also believe the solutions are perhaps just a bit of thoughtful UI design away. The options could be something like: ----Performance & Privacy----- Document Save Mode [x] Balanced. Document history is not shared. (Default) [ ] Fastest. Not recommended for sensitive data [ ] Maximum privacy. Slowest, but most secure Click for more information on what these choices mean The first option would zero out previous edits. This would be the default. This would still be faster than re-zipping the entire archive -- you'd only be zero'ing out a single file. Metadata could still be inferred via fragmentation as you say, but I do not believe this is a concern for most use cases. The second option would optimize purely for speed. The third option would optimize purely for privacy. Perhaps the entire document database would be rewritten from scratch on every save. This probably wouldn't be too much slower than the current XML dance. I understand the value of this kind of "paranoid" mode but I think it's overkill for most situations. Personally, the situations where I'd want this kind of behavior are extremely rare. Clearly some people would need/want this and that's fine; it would be only a click away. And then all future documents could inherit this choice until the app is told otherwise.
    - panic 9y ago
      This UI solves a problem for the computer, not the user. Whenever you find yourself making a UI like this, you should go back and change your program so the computer doesn't have that problem any more. In this case, there's surely a way to write files quickly enough while still maintaining user privacy -- just do that instead of bothering me with a wall of text every time I save a file!
      - JohnBooty 9y ago
        just do that instead of bothering me with a wall of text every time I save a file! I certainly wouldn't want to be hit with this wall of text every time I save a file. I was thinking that this sort of option ought to be a global setting that could perhaps be altered on a per-document basis if (and only if) the user cares to muck about with it. It could, perhaps, be a choice that is presented to the user upon new document creation. This UI solves a problem for the computer, not the user. [...] In this case, there's surely a way to write files quickly enough while still maintaining user privacy Life is full of these choices. There's a gas pedal in my car. I can mash it (to go fast but burn a lot of fuel) or be gentle with it (and save gas, but I won't go as fast). Ideally the car should be crazy fast and crazy safe and crazy fuel efficient no matter how you drive the sucker, but that's a tough engineering challenge and ultimately we (today) leave that choice to the drive. That said, you are 100% correct. Maybe the privacy/paranoia steps are something that could be done on a background thread. The file could be saved quickly, and then the database could be privacy-optimized in the background immediately thereafter.
- kbenson 9y ago
  I agree. People are vastly underestimating the information already exposed by people not understanding what they are sharing when they hand a modern document format out. New features need to examined for unintended consequences, and the fact that this feature might entail data leakage through fragmented included data should be a red flag.
geordee 9y ago
I find the idea awesome. Once the spec for such a document format is created, there would be a tool or two implementing it.
mark-r 9y ago
I thought there was an application that did something like this, but can't remember it anymore. It's nice to see a writeup that steps you incrementally through the benefits of this approach.
- fusiongyro 9y ago
  Was it Fossil SCM by chance? Because it happens to have the same author. :) http://fossil-scm.org/ http://fossil-scm.org/
- dchest 9y ago
  Acorn (awesome image editor for Mac) uses SQLite as .acorn image format. Schema: sqlite> .schema CREATE TABLE image_attributes ( name text, value blob); CREATE TABLE layers (id text, parent_id text, sequence integer, uti text, name text, data blob); CREATE TABLE layer_attributes ( id text, name text, value blob); My app, Mémoires (diary for Mac), and some other Mac apps, use SQLite via Apple's Core Data as document format.
deleted 9y ago
[deleted]
Jizzle 9y ago
The article notes "that this is only a thought experiment." And I think it should stay that way. It's very interesting to think about, yet SQLite offers nothing but problems for developers and content creators. While maybe a bit clunky, a zip file manages to be a close best choice. When considering that a document may be hundreds of megabytes with thousands of assets OR a single page of text, zip offers enough simplicity and robustness, while being highly accessible.
- majewsky 9y ago
  And SQLite doesn't... why again?
kbenson 9y ago
The first line of the file is just: <?xml version="1.0" encoding="UTF-8"?> The second line of the file contains 211792 characters of impenetrable XML. So, does no XML formatting tool exist to consume XML and pretty-print the output? I can tell you, as someone that consumes gigabytes of compressed JSON each day, having 500,000 plus characters of JSON on a single lines causes me no problems. The equivalent in XML should also cause any developer or tool wishing to consume it no problem, as this is a program interchange format, not a format meant to be read by a person (or why compress it anyway?). If someone is really espousing readability in document formats, maybe we should just replace opendocument with some variation of markdown? Also, if your complaint is that XML is too obtuse because it's not formatted well, I'mnot sure how replacing it with a schema implemented in a RDBMS format is much better, especially when there are relatively few implementations of code that can read that format. Sure, everyone has bindings to SQLITE, but those are bindings to the same C library. Almost every non-toy programming language has had the DEFLATE algorithm implemented in it, and implementing a naive XML parser isn't hard (and I'm not sure anything beyond a naive XML parser is required for the OpenDocument format).
- fusiongyro 9y ago
 The tool you're describing is `xmllint --format`.
 - chris_wot 9y ago
 Notepad++ also has a lot of plugins.
 - radicalbyte 9y ago
 This, I use the XML and JSON pretty print plugins daily.
 - oever 9y ago
 Not quite. `xmlint --format` can introduce unwanted spaces. Consider this example. hello! `xmllint --format` adds a space between the 'o' and the '!'.
ChuckMcM 9y ago
This seems like an excellent working format for documents for all of the reasons listed. One could ask why aren't all structured documents built in just this way. In particular I believe that CAD files built like this would be much more robust than they currently are in the likes of TurboCAD.
- sitkack 9y ago
  Many internal data stores for OSX are sqlite files, as are the guts of Chrome. As or a portable interchange format ... history. But yes, using an embedded relational store would be vastly preferable to what we have now.
- imron 9y ago
  This is actually a very timely article. I think I might start using sqlite as a datastore for one of my personal projects.
- randyrand 9y ago
  I don't think SQLite would be good for 3d data exchange between companies/programs, to name one example. GLTF is the newest format for this, and its much more suited for the task.
  - ChuckMcM 9y ago
    Thanks for the pointer, although it looks focuses on '3D' as in models and assets for games and renderers, not '3D' as in the dimensions and mechanical properties of concrete walls. Still I downloaded the spec for a closer read from the Github repository. The two important clients for 3D cad data are analysis software (CFD, Structural, Etc) and CAM software for manufacturing. I didn't get a sense that GLTF was looking at that space at all when it was being developed.
lifthrasiir 9y ago
https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html SQLite does not compete with client/server databases. SQLite competes with fopen(). I personally do not agree to this argument, because some operations are intrinsically better suited to text-based formats---for example with text-based formats one can at least try to diff and merge multiple files (even though the actual suitability depends more on the data structure and less on the underlying container format), but I can see where the argument is coming from.
gungoman 9y ago
Penetration testers will have a field day with this if it happens.
- jnwatson 9y ago
  Why? SQLite has been put in safety critical systems and has the best test plan I've ever seen for an open source project.
  - rkangel 9y ago
    It has the best test plan I've seen for any pure software product, and I'm including both medical and safety critical stuff.
- SQLite 9y ago
  Pen-testers have been hammering hard on SQLite for years. Attacks against SQLite are not as easy as you imagine. Pen-testers do still occasionally find minor problems. See https://www.sqlite.org/src/info/04925dee41a21ffc https://www.sqlite.org/src/info/04925dee41a21ffc for the latest example. But generally speaking, it is safe to open an SQLite database received from an untrusted source. If you are extra paranoid, activate the "PRAGMA cell_size_check=ON" feature and/or run "PRAGMA integrity_check" to verify the database before use.
btrask 9y ago
I'm a big fan of SQLite, but this kind of marketing puts me off a bit. ZIP and XML are popular standards with dozens of implementations. The only thing that can read an SQLite DB is SQLite, AFAIK. To be sure, SQLite is very well supported, runs on tons of platforms and is going to be around for a long time. But still, it is less archival quality, and more inherently complex (even though that complexity is very well encapsulated).
- abtinf 9y ago
  Why do you view a "standard" or "dozens of implementations" as a good thing - what is the context of your evaluation? Why is it not comparable, if not significantly better, to have a mostly uniform public domain code base that has been deployed to billions of devices?
  - lifthrasiir 9y ago
    The diversity of mutually compatible implementations avoids a dominant implementation's critical issue being catastrophic and keeps the standard moving to the direction no single party can totally influence. SQLite does have a list of semi-standardized requirements [1] which should technically allow for alternative implementations---not having them today makes it less viable as a true standard. [1] https://www.sqlite.org/requirements.html https://www.sqlite.org/requirements.html
    - SQLite 9y ago
      A better starting point might be the database file format description. https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
  - _jal 9y ago
    lifthrasiir also sort of touched on this (and I endorse their comment), but as a thought experiment, imagine what word processing formats would look like if MSFT hadn't owned .doc, but instead, WordPerfect and maybe two other competitors also shared the format. When a popular format can't be unilaterally controlled by one actor, both the users and the market as a whole tend to be better off.
pagnol 9y 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. Why would it be superior? The biggest advantage I can think of is code reuse, but what else?
- BinaryIdiot 9y ago
  Interoperability? If it's in a zip and in a human readable format even without the necessary application you can still dig into data. But it also helps when writing a new application as it only has to understand zip and xml, something virtually everything can handle. As much as I hate xml this seems like a decent use for it.
- blt 9y ago
  Because the author ends up duplicating features of the zip format. Think of all the stuff that goes along with a ppt slide. Fonts, images, videos, sounds. You want to embed the original files anyway, because storing the content in your own custom format is pointless extra work. think how complicated a video file format is. It's a good fit for a file archive.
peterkelly 9y ago
That would be coming full circle, and going back to the bad old days of binary .doc and .xls files, which used an efficient data store for fast incremental updates: https://www.joelonsoftware.com/2008/02/19/why-are-the-microsoft-office-file-formats-so-complicated-and-some-workarounds/ https://www.joelonsoftware.com/2008/02/19/why-are-the-micros... OpenDocument and its competitor (OOXML) both use zip/XML for portability reasons. Yes, they're less efficient. But they're also completely independent of any specific software implementation [1]. [1] Mostly - OOXML has some parts that depend on MS Office's implementation. But ODF is completely described by the spec.
- abtinf 9y ago
  I don't see how your comment is responsive to the excellent article. doc and xls were proprietary binary formats. sqlite files couldn't be more open - the software to interact with them is in the public domain and ported everywhere. And the article itself doesn't even suggest using sqlite as a binary store. Rather, it suggests using the capabilities of sqlite to augment text-based data.
  - sitkack 9y ago
    Using SQLite as a container is different from using the whole of SQLite the embedded sql database. You can use the container by reimplementing a reader for the file format. Maybe it would make the parent feel more comfortable if the sqlite db was inside a nice cozy zip (or jar file).
  - mseebach 9y ago
    > sqlite files couldn't be more open Yes, actually they could. They could be readable without any software at all. Like XML. All of the improvements suggested in the article could be implemented by extending the XML format to allow including by other XML files by reference.
    - coldtea 9y ago
      >Yes, actually they could. They could be readable without any software at all. Like XML. You can't read XML without software. Perhaps you take editors and cat and less as a given. In which case, nothing prevents you from taking sqlite the binary (or 1000 serializers that can be produced very easily) as a given too.
badsectoracula 9y ago
FWIW ZIP files also allow for partial saves as each entry is stored in a separate stream so all you need to do is update the directory, which is placed at the end of the file (in theory you could update archives in an append-only manner when saving and have a pack command to rebuild the ZIP file in order to remove the unnecessary data). It is just that (AFAIK) no program that uses ZIP as a generic container bothers with that (and besides, implementing the necessary logic for the partial saves might actually be the harder bit, regardless of the storage format).
- masklinn 9y ago
  > in theory you could update archives in an append-only manner when saving and have a pack command to rebuild the ZIP file in order to remove the unnecessary data In fact I'd expect a crash-safe "update" to do that, the in-line update only works if the revision of a file is no bigger than the old one. If you use appending methods to update the file, you write the new version of the existing file, you write a new central directory, and you remove the old directory entry. Only at that point should the update become visible.
- alkonaut 9y ago
  Handling a power outage during a save typically means writing to a new file and then handling it later. With a big zip I just copy the entire file (hoping that a file copy is reasonably fast) and then do in-place updates in the copy. If all the in-place updates in the copied zip work, then replace the original with the copy. I haven't found a reasonable way to do this that doesn't require making a copy of the whole original document. If sqlite did this elegantly, I'd switch in a heartbeat.
mjw1007 9y ago
For me, the biggest reason not to do this sort of thing is that SQLite still often refuses to work over network filesystems. I appreciate that this is because the authors are being careful about possible corruption when locking isn't working properly, but if they really mean the « SQLite competes with fopen() » line they need to find a way to get round this, because fopen() doesn't do that.
- nikbackm 9y ago
  Wouldn't fopen and friends also have issues when locking isn't working properly?
  - mjw1007 9y ago
    They might well lead to file corruption if two people try to save the same file at once. But that tends not to matter in practice for applications like word processors (which often want to implement their own system to stop two people opening the same in editable mode at the same time, not just stop them saving at the same time). Or the fopen-using application can just do things the simple way and write out a new temporary file to replace the old one with.
    - wruza 9y ago
      The same way sqlite-using app could write a new database file and replace the old. What does this argument support in this subthread?
      - mjw1007 9y ago
        Then you lose many of the advantages being touted in the « What If OpenDocument Used SQLite? » page that we're discussing. Consider the following paragraph: « Hence, "File/Save" takes longer than it ought, especially on older hardware. Newer machines are faster, but it is still bothersome that changing a single character in a 50 megabyte presentation causes one to burn through 50 megabytes of the finite write life on the SSD. »
cjsuk 9y ago
If it used SQLite it’d be like serialised COM again (xls,doc). Kill me. Zipped XML actually allows us to parse and manipulate it easily with a strongly defined schema. SQLite can barely enforce any consistency. As for XML it’s actually pretty marvellous. Most people don’t understand it which is where the problem is.
- simonh 9y ago
  I'm sorry, where is the article did you read the suggestion that the format should stop using XML? I can't find any such suggestion. Looking at the other comments here it looks like the author must have recommended ditching XML completely, but the actual article doesn't seem to bear that up. Has it been radically altered since being posted to HN? The disconnect between the pro-XML arguments here and the actual article's assumption of continuing to use XML is really quite jarring. The nearest I can find is where it suggests breaking up the XML into smaller XML documents, but that's a whole heck of a long way away from being the same thing.
  - cjsuk 9y ago
    Im not even talking about the data model. The manifest would be replaced, all resources wouldn’t have usable URIs, you couldn’t deserialise a stream using sax easily, dtd and schema references that ship with it would be invalidated. Those are all XML too. It’s not just markup. It turns a bundle of files with strict relations into an unworkable stateful blob of loose links. Welcome to the world of corruption at the application layer.
    - simonh 9y ago
      I would hardly call a system such as the one proposed corruption. Ok, so maybe this proposal might make the format a little harder to access programatically, but it's a pretty minor effect. You just need to extract the data from a SQLite DB instead of a zip file. You could even store a manifest in the DB as well. I think the thing I object to about all the criticism here is that it's all about making things easy and simple and pure from a development point of view. The article proposes concrete end user benefits from a scheme like this, which to me seem pretty worthwhile. Surely the focus should be on the user experience first and then look at how to implement that?
jbb67 9y ago
Yes please. Anything is better than XML
alkonaut 9y ago
I'm maintaining an application that uses a similar (OPC) file format. It has served us well and it beats the legacy way of having just a directory of files (e.g. when you want to send a file to someone else it's ONE file) but it doesn't scale very well when adding lots of data, and incremental update is painful compared to just using a directory. How does sqlite handle incremental update? Does it use separate file storage for large table blobs? If the blobs are in line, how does it handle changing one blob from 1Mb to 2Mb say? Presumably it just appends the new blob, updates the pointer, and marks the original 1Mb area as unused and possible to clean up? That leaves a 3Mb file, which can be vacuumed to 2Mb then?
nathell 9y ago
In my app (a search tool for Polish corpora [1]), I'm using ZIP with zero compression (stored files) as a container file format. The upside is that I can easily read the archive contents, and then mmap parts of the file as needed [2]. [1]: https://github.com/nathell/smyrna https://github.com/nathell/smyrna [2]: https://github.com/nathell/smyrna/blob/master/src/clj/smyrna/corpus.clj#L86 https://github.com/nathell/smyrna/blob/master/src/clj/smyrna...
- nerdponx 9y ago
  Why ZIP versus something like TAR?
  - umanwizard 9y ago
    Doesn't ZIP with no compression amount to the same thing as TAR? What are you claiming the difference is?
    - mschaef 9y ago
      Despite the fact that they can be used for similar purposes, the underlying formats are radically different. TAR is a 'Tape Archive'... the metadata for each file is adjacent to the file contents itself. (Which is what you'd expect for a format designed for a linear access media like tape.) For ZIP, there's a single unified directory structure that can be used as an index into the file.
      - C4K3 9y ago
        That got me thinking, are there any efforts to replace tar? A single unified directory structure does seem like a desirable feature, I'm sure it'd be possible to find other improvements to make in tar. I wonder if anybody is working on a neotar?
        ghettoimp 9y ago
        Would that be different than ZIP then?
stuaxo 9y ago
I don't see why the zip file can't be read incrementally - that sounds like an issue in their Zip Loader. - Zip stores each file it contains separately.
upofadown 9y ago
... and then sglite changes the format of the files on the disk and you are entirely boned...
- TheAceOfHearts 9y ago
  If you read through their docs [0] you'd see they're committed to maintaining backwards compatibility with all SQLite 3 releases. There haven't been breaking changes since 2004. In addition, the library is incredibly easy to embed with any project as it's largely a self-contained system [1]. If SQLite 4 were released with breaking changes, you could happily continue using SQLite 3. [0] https://sqlite.org/onefile.html https://sqlite.org/onefile.html [1] https://sqlite.org/selfcontained.html https://sqlite.org/selfcontained.html
  - upofadown 9y ago
    This is a document format we are talking about here that should be good for the ages, not just 13 years. In practice you would have to define your document format in terms of a particular version of SQLite. So you would have to unambiguously document that version in such detail that you cover everything that anyone would ever want and be able to do. Once that version of SQLite becomes unmaintained then things are broken. Someone would have to maintain that version of what would now be the ODF SQL disk format forever. Much of what would have to be maintained would be pointless complexity not required for the representation of documents.
    - SQLite 9y ago
      You seem to be operating under the assumption that the SQLite database file format is an ad-hoc, dodgy affair that the maintainers might change on a whim. I'd like to try to dispel that notion. The SQLite database file format has been unchanged (except by adding extensions through well-defined mechanisms) since 2004. And we developers have pledged to continue that 100% compatibility through at least the year 2050. The file format is cross-platform and byte-order independent. We have an extensive test suite to help us guarantee that each new version of SQLite is completely file compatible with all those that came before. The file format is documented (https://sqlite.org/fileformat.html https://sqlite.org/fileformat.html) to sufficient detail that the spec alone can and has been used to create a compatible reader/writer. (I know this because the authors of the compatible reader/writer software pointed out deficiencies in the spec which I subsequently corrected.) The file format is relatively simple. A reader for SQLite could be written from the spec with no more effort than it would take to implement a ZIP archive reader with its decompressor from spec. There is approximately 1 trillion (10^12) SQLite database files in active use today. Even without the promises from developers, the huge volume of legacy database files suggests that the SQLite database file format will be around for a long time yet. The latest version of SQLite will happily read and write any SQLite3 database written since the file format was designed in 2004. Ancient legacy versions of the SQLite library will read and write databases created yesterday, as long as those databases do not use any of the extensions that were added later.
bambax 9y ago
> Third Improvement: Versioning Regardless of SQLite, why don't modern productivity tools include at least an option to automatically version documents, ie store documents as a series of diffs that one could browse up to the start? Of course in many cases, edit history is better kept hidden, so this should not be the default. But why doesn't it exist at all?
- hoov 9y ago
  Difficult, but possible. We did this with Buzzword at Adobe. The hardest part was the UX, honestly. Most knowledge workers need some hand holding, to say nothing of teachers and students (we had success selling to high school teachers).
- kccqzy 9y ago
  I think Apple introduced this in 10.7: https://support.apple.com/en-us/HT202255 https://support.apple.com/en-us/HT202255 IMO this is better handled by the OS than by each individual app.
- da_chicken 9y ago
  The typical excuse is file space. The next excuse is privacy/security concerns. The next excuse is usually that other tools do it better (SharePoint, svn, git) for the types of files that really matter. MS Word has a Track Changes feature that kind of does this, and it's a pain because you have to remember to remove the revision history before you publish the document because it can expose details about the users that edited the document.
thoastbrot 9y ago
The typo in the very first sentence (where/were) puts me off :(
mwexler 9y ago
Look, I love SQLite as much as the next person, but as a database, it's got a lot of problems. From the lack of a formal time-date storage class to the "dynamic typing", if we are going to go binary and complex, is SQLite the best place to plan for future stability and ease of use? The first question is "Q1: Does a RDBMS help here?" and then 2nd "Q2: Given Q1=Y, Which might we use, what features are reqd and how are they implemented?". Parent jumped to SQLite as the answer (given the source) but I am unconvinced on Q2, or even Q1.
SQLite 9y ago
Related articles: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html https://www.sqlite.org/aff_short.html https://www.sqlite.org/aff_short.html
falcolas 9y ago
I'd like to take a moment and look at this "what if" from the point of view of data archiving. Zip files use a well documented, easily-reproducable and widely implemented deflate algorithm. It's reasonable to assume that someone could, 100 years from now, unzip an OpenDocument file. The contents are XML files - basically text - which can be read with any simple text editor and parsed. The XML is also at least somewhat self-documenting regarding the contained document. SQLite databases have a binary format which is known to change periodically. An attempt to read a SQLite file in 100 years would have to know the exact version of the file, and be able to reach back through history to find that version of SQLite (or a compatible version), and find a way to compile it for whatever CPU architecture exists in 100 years. This could be mitigated by including the appropriately versioned documentation of the binary format with the database itself - however to the best of my knowledge, full documentation of the binary format (outside the code) does not exist. The end product - a relational database - would also be less self-documenting than the XML version. IMO, it would be much better to keep the inefficient zipped XML files. [EDIT]: The binary file format: https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html Learn something new every day.
- foldr 9y ago
  Another solution to this problem would just be to bundle a txt description of the format with each file. So e.g. have the first n bytes be a plain text description of the sqlite format, and the rest be the DB in sqlite format.
  - ZitchDog 9y ago
    May as well just bundle SQLite while you're at it.
    - foldr 9y ago
      Hmm? I mean sure, but the point is that adding a few kb of text to the file is a more lightweight solution. Less than the overhead of using xml, probably.
hokkos 9y ago
Interesting but where should the database normalization should end ? Should every text element with a single style be a separate entry with an associated style using a foreign key ? Should all the xml disappear and be coded around databases concepts ?
deleted 9y ago
[deleted]
zubairq 9y ago
This article about storing files in Sqlite is spot on and it works. I did exactly this and i have tested it with GBs of data and 1000s of files and it works well. The table structure I use is: CREATE TABLE IF NOT EXISTS files (id TEXT, name TEXT, contents BLOB); : as you can see the source code for the Javascript NodeJs web application file here: https://github.com/zubairq/gosharedata/blob/master/src/index.js https://github.com/zubairq/gosharedata/blob/master/src/index...

305 points by ejstronge 9y ago

Please don’t. When people are sending or sharing a document, they don’t expect to leak edit history of that document. And SQLite inherently leaks that edit history, in database freed pages and unallocated space. You can VACUUM the database after each save, but that’ll negate the performance benefits. Also VACUUM was designed to save space, not sure it reliably cleans up unwanted data. OTOH, these zipped XMLs are free from unwanted data, they (hopefully) only contain the data user wants to save, i.e. the content of the last version of the document.

chj 9y ago
People should publish PDF version of a document. But if they send the working copy around, then the history may be helpful for other collaborators. Think of git. They can even merge the changes easily.
- aaronchall 9y ago
  Agreed. History should be considered a feature.
  - joosters 9y ago
    And it is. That's fine, as long as the user actively turns that feature on, and by default no data is leaked.
- kbenson 9y ago
  Unfortunately, companies don't do that. They send the doc/docx. They also send the xls/xlsx instead of the csv. Protecting users from accidentally doing a very wrong thing is better than being slightly more helpful as a default. Security should be the default. IIRC there have been cases where confidential information has been found by looking through the history in government released documents.
  - Const-me 9y ago
    docx/xlsx are better, they are zipped XMLs too, just like odf. Older ones, doc & xls are closer to sqlite, for the same reason, performance. I remember finding confidential information in these files received from third parties they probably didn’t want to disclose, e.g. offer price that was before the last edit.
  - bigiain 9y ago
    You know you can pop calc with a csv file opened in Excel? from http://georgemauer.net/2017/10/07/csv-injection.html http://georgemauer.net/2017/10/07/csv-injection.html : UserId,BillToDate,ProjectName,Description,DurationMinutes 1,2017-07-25,Test Project,Flipped the jibbet,60 2,2017-07-25,Important Client,"Bop, dop, and giglip", 240 2,2017-07-25,Important Client,"=2+5+cmd|' /C calc'!A0", 240 Yeah - that "=2+5+cmd|' \c calc'!A0" gets Windows to execute code...
- gumby 9y ago
  > People should publish PDF version of a document. Oh please no! PDF is quite user-unfriendly (e.g. can't reflow, typically can't cut/paste properly) designed to emulate an ancient medium that has benefits and drawbacks different from screens. And PDF, while executable, is essentially compiler output, with all the destruction of semantics that implies.
  - abtinf 9y ago
    It depends on what your are trying to do. Short of exporting to an image format, PDF is the best format for preserving and sharing a final rendered output. All of the things you list as drawbacks are virtues if your purpose is static presentation.
  - pjmlp 9y ago
    I only have issues with ebooks, outside them it is just perfect. Cut/paste only fails on me, when whoever created the PDF had the brilliant idea of using images instead of actual text, e.g. scans. Something that using another format won't help. Maybe you should use another PDF reader?
  - Fnoord 9y ago
    For a resume, PDF is ace. I don't want the reader to see version history or copy/paste (even though its peanuts with a half decent PDF reader). I want them to print it.
SQLite 9y ago
The "PRAGMA secure_delete=ON;" command will fix that for you. Or you can make secure-delete the default at compile-time using the -DSQLITE_SECURE_DELETE option. There is a (small) performance hit, since extra writes have to occur to overwrite the obsolete data with zeros, but it does work. See https://www.sqlite.org/pragma.html#pragma_secure_delete https://www.sqlite.org/pragma.html#pragma_secure_delete for further information.
- Const-me 9y ago
  Not enough. See my next comment: https://news.ycombinator.com/item?id=15607650 https://news.ycombinator.com/item?id=15607650
Const-me 9y ago
P.S. Even fragmentation can leak data. E.g. search for the most fragmented portion of the document in the DB, and you'll likely find out the most heavily edited paragraph of the document.
- JohnBooty 9y ago
  I believe that you raise excellent concerns. I also believe the solutions are perhaps just a bit of thoughtful UI design away. The options could be something like: ----Performance & Privacy----- Document Save Mode [x] Balanced. Document history is not shared. (Default) [ ] Fastest. Not recommended for sensitive data [ ] Maximum privacy. Slowest, but most secure Click for more information on what these choices mean The first option would zero out previous edits. This would be the default. This would still be faster than re-zipping the entire archive -- you'd only be zero'ing out a single file. Metadata could still be inferred via fragmentation as you say, but I do not believe this is a concern for most use cases. The second option would optimize purely for speed. The third option would optimize purely for privacy. Perhaps the entire document database would be rewritten from scratch on every save. This probably wouldn't be too much slower than the current XML dance. I understand the value of this kind of "paranoid" mode but I think it's overkill for most situations. Personally, the situations where I'd want this kind of behavior are extremely rare. Clearly some people would need/want this and that's fine; it would be only a click away. And then all future documents could inherit this choice until the app is told otherwise.
  - panic 9y ago
    This UI solves a problem for the computer, not the user. Whenever you find yourself making a UI like this, you should go back and change your program so the computer doesn't have that problem any more. In this case, there's surely a way to write files quickly enough while still maintaining user privacy -- just do that instead of bothering me with a wall of text every time I save a file!
    - JohnBooty 9y ago
      just do that instead of bothering me with a wall of text every time I save a file! I certainly wouldn't want to be hit with this wall of text every time I save a file. I was thinking that this sort of option ought to be a global setting that could perhaps be altered on a per-document basis if (and only if) the user cares to muck about with it. It could, perhaps, be a choice that is presented to the user upon new document creation. This UI solves a problem for the computer, not the user. [...] In this case, there's surely a way to write files quickly enough while still maintaining user privacy Life is full of these choices. There's a gas pedal in my car. I can mash it (to go fast but burn a lot of fuel) or be gentle with it (and save gas, but I won't go as fast). Ideally the car should be crazy fast and crazy safe and crazy fuel efficient no matter how you drive the sucker, but that's a tough engineering challenge and ultimately we (today) leave that choice to the drive. That said, you are 100% correct. Maybe the privacy/paranoia steps are something that could be done on a background thread. The file could be saved quickly, and then the database could be privacy-optimized in the background immediately thereafter.
kbenson 9y ago
I agree. People are vastly underestimating the information already exposed by people not understanding what they are sharing when they hand a modern document format out. New features need to examined for unintended consequences, and the fact that this feature might entail data leakage through fragmented included data should be a red flag.

geordee 9y ago

I find the idea awesome. Once the spec for such a document format is created, there would be a tool or two implementing it.

mark-r 9y ago

I thought there was an application that did something like this, but can't remember it anymore. It's nice to see a writeup that steps you incrementally through the benefits of this approach.

fusiongyro 9y ago
Was it Fossil SCM by chance? Because it happens to have the same author. :) http://fossil-scm.org/ http://fossil-scm.org/
dchest 9y ago
Acorn (awesome image editor for Mac) uses SQLite as .acorn image format. Schema: sqlite> .schema CREATE TABLE image_attributes ( name text, value blob); CREATE TABLE layers (id text, parent_id text, sequence integer, uti text, name text, data blob); CREATE TABLE layer_attributes ( id text, name text, value blob); My app, Mémoires (diary for Mac), and some other Mac apps, use SQLite via Apple's Core Data as document format.

deleted 9y ago

[deleted]

Jizzle 9y ago

The article notes "that this is only a thought experiment." And I think it should stay that way. It's very interesting to think about, yet SQLite offers nothing but problems for developers and content creators. While maybe a bit clunky, a zip file manages to be a close best choice. When considering that a document may be hundreds of megabytes with thousands of assets OR a single page of text, zip offers enough simplicity and robustness, while being highly accessible.

majewsky 9y ago
And SQLite doesn't... why again?

kbenson 9y ago

The first line of the file is just: <?xml version="1.0" encoding="UTF-8"?> The second line of the file contains 211792 characters of impenetrable XML. So, does no XML formatting tool exist to consume XML and pretty-print the output? I can tell you, as someone that consumes gigabytes of compressed JSON each day, having 500,000 plus characters of JSON on a single lines causes me no problems. The equivalent in XML should also cause any developer or tool wishing to consume it no problem, as this is a program interchange format, not a format meant to be read by a person (or why compress it anyway?). If someone is really espousing readability in document formats, maybe we should just replace opendocument with some variation of markdown? Also, if your complaint is that XML is too obtuse because it's not formatted well, I'mnot sure how replacing it with a schema implemented in a RDBMS format is much better, especially when there are relatively few implementations of code that can read that format. Sure, everyone has bindings to SQLITE, but those are bindings to the same C library. Almost every non-toy programming language has had the DEFLATE algorithm implemented in it, and implementing a naive XML parser isn't hard (and I'm not sure anything beyond a naive XML parser is required for the OpenDocument format).

fusiongyro 9y ago
The tool you're describing is `xmllint --format`.
- chris_wot 9y ago
 Notepad++ also has a lot of plugins.
 - radicalbyte 9y ago
 This, I use the XML and JSON pretty print plugins daily.
- oever 9y ago
 Not quite. `xmlint --format` can introduce unwanted spaces. Consider this example. hello! `xmllint --format` adds a space between the 'o' and the '!'.

ChuckMcM 9y ago

This seems like an excellent working format for documents for all of the reasons listed. One could ask why aren't all structured documents built in just this way. In particular I believe that CAD files built like this would be much more robust than they currently are in the likes of TurboCAD.

sitkack 9y ago
Many internal data stores for OSX are sqlite files, as are the guts of Chrome. As or a portable interchange format ... history. But yes, using an embedded relational store would be vastly preferable to what we have now.
imron 9y ago
This is actually a very timely article. I think I might start using sqlite as a datastore for one of my personal projects.
randyrand 9y ago
I don't think SQLite would be good for 3d data exchange between companies/programs, to name one example. GLTF is the newest format for this, and its much more suited for the task.
- ChuckMcM 9y ago
  Thanks for the pointer, although it looks focuses on '3D' as in models and assets for games and renderers, not '3D' as in the dimensions and mechanical properties of concrete walls. Still I downloaded the spec for a closer read from the Github repository. The two important clients for 3D cad data are analysis software (CFD, Structural, Etc) and CAM software for manufacturing. I didn't get a sense that GLTF was looking at that space at all when it was being developed.

lifthrasiir 9y ago

https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html SQLite does not compete with client/server databases. SQLite competes with fopen(). I personally do not agree to this argument, because some operations are intrinsically better suited to text-based formats---for example with text-based formats one can at least try to diff and merge multiple files (even though the actual suitability depends more on the data structure and less on the underlying container format), but I can see where the argument is coming from.

gungoman 9y ago

Penetration testers will have a field day with this if it happens.

jnwatson 9y ago
Why? SQLite has been put in safety critical systems and has the best test plan I've ever seen for an open source project.
- rkangel 9y ago
  It has the best test plan I've seen for any pure software product, and I'm including both medical and safety critical stuff.
SQLite 9y ago
Pen-testers have been hammering hard on SQLite for years. Attacks against SQLite are not as easy as you imagine. Pen-testers do still occasionally find minor problems. See https://www.sqlite.org/src/info/04925dee41a21ffc https://www.sqlite.org/src/info/04925dee41a21ffc for the latest example. But generally speaking, it is safe to open an SQLite database received from an untrusted source. If you are extra paranoid, activate the "PRAGMA cell_size_check=ON" feature and/or run "PRAGMA integrity_check" to verify the database before use.

btrask 9y ago

I'm a big fan of SQLite, but this kind of marketing puts me off a bit. ZIP and XML are popular standards with dozens of implementations. The only thing that can read an SQLite DB is SQLite, AFAIK. To be sure, SQLite is very well supported, runs on tons of platforms and is going to be around for a long time. But still, it is less archival quality, and more inherently complex (even though that complexity is very well encapsulated).

abtinf 9y ago
Why do you view a "standard" or "dozens of implementations" as a good thing - what is the context of your evaluation? Why is it not comparable, if not significantly better, to have a mostly uniform public domain code base that has been deployed to billions of devices?
- lifthrasiir 9y ago
  The diversity of mutually compatible implementations avoids a dominant implementation's critical issue being catastrophic and keeps the standard moving to the direction no single party can totally influence. SQLite does have a list of semi-standardized requirements [1] which should technically allow for alternative implementations---not having them today makes it less viable as a true standard. [1] https://www.sqlite.org/requirements.html https://www.sqlite.org/requirements.html
  - SQLite 9y ago
    A better starting point might be the database file format description. https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
- _jal 9y ago
  lifthrasiir also sort of touched on this (and I endorse their comment), but as a thought experiment, imagine what word processing formats would look like if MSFT hadn't owned .doc, but instead, WordPerfect and maybe two other competitors also shared the format. When a popular format can't be unilaterally controlled by one actor, both the users and the market as a whole tend to be better off.

pagnol 9y 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. Why would it be superior? The biggest advantage I can think of is code reuse, but what else?

BinaryIdiot 9y ago
Interoperability? If it's in a zip and in a human readable format even without the necessary application you can still dig into data. But it also helps when writing a new application as it only has to understand zip and xml, something virtually everything can handle. As much as I hate xml this seems like a decent use for it.
blt 9y ago
Because the author ends up duplicating features of the zip format. Think of all the stuff that goes along with a ppt slide. Fonts, images, videos, sounds. You want to embed the original files anyway, because storing the content in your own custom format is pointless extra work. think how complicated a video file format is. It's a good fit for a file archive.

peterkelly 9y ago

That would be coming full circle, and going back to the bad old days of binary .doc and .xls files, which used an efficient data store for fast incremental updates: https://www.joelonsoftware.com/2008/02/19/why-are-the-microsoft-office-file-formats-so-complicated-and-some-workarounds/ https://www.joelonsoftware.com/2008/02/19/why-are-the-micros... OpenDocument and its competitor (OOXML) both use zip/XML for portability reasons. Yes, they're less efficient. But they're also completely independent of any specific software implementation [1]. [1] Mostly - OOXML has some parts that depend on MS Office's implementation. But ODF is completely described by the spec.

abtinf 9y ago
I don't see how your comment is responsive to the excellent article. doc and xls were proprietary binary formats. sqlite files couldn't be more open - the software to interact with them is in the public domain and ported everywhere. And the article itself doesn't even suggest using sqlite as a binary store. Rather, it suggests using the capabilities of sqlite to augment text-based data.
- sitkack 9y ago
  Using SQLite as a container is different from using the whole of SQLite the embedded sql database. You can use the container by reimplementing a reader for the file format. Maybe it would make the parent feel more comfortable if the sqlite db was inside a nice cozy zip (or jar file).
- mseebach 9y ago
  > sqlite files couldn't be more open Yes, actually they could. They could be readable without any software at all. Like XML. All of the improvements suggested in the article could be implemented by extending the XML format to allow including by other XML files by reference.
  - coldtea 9y ago
    >Yes, actually they could. They could be readable without any software at all. Like XML. You can't read XML without software. Perhaps you take editors and cat and less as a given. In which case, nothing prevents you from taking sqlite the binary (or 1000 serializers that can be produced very easily) as a given too.

badsectoracula 9y ago

FWIW ZIP files also allow for partial saves as each entry is stored in a separate stream so all you need to do is update the directory, which is placed at the end of the file (in theory you could update archives in an append-only manner when saving and have a pack command to rebuild the ZIP file in order to remove the unnecessary data). It is just that (AFAIK) no program that uses ZIP as a generic container bothers with that (and besides, implementing the necessary logic for the partial saves might actually be the harder bit, regardless of the storage format).

masklinn 9y ago
> in theory you could update archives in an append-only manner when saving and have a pack command to rebuild the ZIP file in order to remove the unnecessary data In fact I'd expect a crash-safe "update" to do that, the in-line update only works if the revision of a file is no bigger than the old one. If you use appending methods to update the file, you write the new version of the existing file, you write a new central directory, and you remove the old directory entry. Only at that point should the update become visible.
alkonaut 9y ago
Handling a power outage during a save typically means writing to a new file and then handling it later. With a big zip I just copy the entire file (hoping that a file copy is reasonably fast) and then do in-place updates in the copy. If all the in-place updates in the copied zip work, then replace the original with the copy. I haven't found a reasonable way to do this that doesn't require making a copy of the whole original document. If sqlite did this elegantly, I'd switch in a heartbeat.

mjw1007 9y ago

For me, the biggest reason not to do this sort of thing is that SQLite still often refuses to work over network filesystems. I appreciate that this is because the authors are being careful about possible corruption when locking isn't working properly, but if they really mean the « SQLite competes with fopen() » line they need to find a way to get round this, because fopen() doesn't do that.

nikbackm 9y ago
Wouldn't fopen and friends also have issues when locking isn't working properly?
- mjw1007 9y ago
  They might well lead to file corruption if two people try to save the same file at once. But that tends not to matter in practice for applications like word processors (which often want to implement their own system to stop two people opening the same in editable mode at the same time, not just stop them saving at the same time). Or the fopen-using application can just do things the simple way and write out a new temporary file to replace the old one with.
  - wruza 9y ago
    The same way sqlite-using app could write a new database file and replace the old. What does this argument support in this subthread?
    - mjw1007 9y ago
      Then you lose many of the advantages being touted in the « What If OpenDocument Used SQLite? » page that we're discussing. Consider the following paragraph: « Hence, "File/Save" takes longer than it ought, especially on older hardware. Newer machines are faster, but it is still bothersome that changing a single character in a 50 megabyte presentation causes one to burn through 50 megabytes of the finite write life on the SSD. »

cjsuk 9y ago

If it used SQLite it’d be like serialised COM again (xls,doc). Kill me. Zipped XML actually allows us to parse and manipulate it easily with a strongly defined schema. SQLite can barely enforce any consistency. As for XML it’s actually pretty marvellous. Most people don’t understand it which is where the problem is.

simonh 9y ago
I'm sorry, where is the article did you read the suggestion that the format should stop using XML? I can't find any such suggestion. Looking at the other comments here it looks like the author must have recommended ditching XML completely, but the actual article doesn't seem to bear that up. Has it been radically altered since being posted to HN? The disconnect between the pro-XML arguments here and the actual article's assumption of continuing to use XML is really quite jarring. The nearest I can find is where it suggests breaking up the XML into smaller XML documents, but that's a whole heck of a long way away from being the same thing.
- cjsuk 9y ago
  Im not even talking about the data model. The manifest would be replaced, all resources wouldn’t have usable URIs, you couldn’t deserialise a stream using sax easily, dtd and schema references that ship with it would be invalidated. Those are all XML too. It’s not just markup. It turns a bundle of files with strict relations into an unworkable stateful blob of loose links. Welcome to the world of corruption at the application layer.
  - simonh 9y ago
    I would hardly call a system such as the one proposed corruption. Ok, so maybe this proposal might make the format a little harder to access programatically, but it's a pretty minor effect. You just need to extract the data from a SQLite DB instead of a zip file. You could even store a manifest in the DB as well. I think the thing I object to about all the criticism here is that it's all about making things easy and simple and pure from a development point of view. The article proposes concrete end user benefits from a scheme like this, which to me seem pretty worthwhile. Surely the focus should be on the user experience first and then look at how to implement that?

jbb67 9y ago

Yes please. Anything is better than XML

alkonaut 9y ago

I'm maintaining an application that uses a similar (OPC) file format. It has served us well and it beats the legacy way of having just a directory of files (e.g. when you want to send a file to someone else it's ONE file) but it doesn't scale very well when adding lots of data, and incremental update is painful compared to just using a directory. How does sqlite handle incremental update? Does it use separate file storage for large table blobs? If the blobs are in line, how does it handle changing one blob from 1Mb to 2Mb say? Presumably it just appends the new blob, updates the pointer, and marks the original 1Mb area as unused and possible to clean up? That leaves a 3Mb file, which can be vacuumed to 2Mb then?

nathell 9y ago

In my app (a search tool for Polish corpora [1]), I'm using ZIP with zero compression (stored files) as a container file format. The upside is that I can easily read the archive contents, and then mmap parts of the file as needed [2]. [1]: https://github.com/nathell/smyrna https://github.com/nathell/smyrna [2]: https://github.com/nathell/smyrna/blob/master/src/clj/smyrna/corpus.clj#L86 https://github.com/nathell/smyrna/blob/master/src/clj/smyrna...

nerdponx 9y ago
Why ZIP versus something like TAR?
- umanwizard 9y ago
  Doesn't ZIP with no compression amount to the same thing as TAR? What are you claiming the difference is?
  - mschaef 9y ago
    Despite the fact that they can be used for similar purposes, the underlying formats are radically different. TAR is a 'Tape Archive'... the metadata for each file is adjacent to the file contents itself. (Which is what you'd expect for a format designed for a linear access media like tape.) For ZIP, there's a single unified directory structure that can be used as an index into the file.
    - C4K3 9y ago
      That got me thinking, are there any efforts to replace tar? A single unified directory structure does seem like a desirable feature, I'm sure it'd be possible to find other improvements to make in tar. I wonder if anybody is working on a neotar?
      - ghettoimp 9y ago
        Would that be different than ZIP then?

stuaxo 9y ago

I don't see why the zip file can't be read incrementally - that sounds like an issue in their Zip Loader. - Zip stores each file it contains separately.

upofadown 9y ago

... and then sglite changes the format of the files on the disk and you are entirely boned...

TheAceOfHearts 9y ago
If you read through their docs [0] you'd see they're committed to maintaining backwards compatibility with all SQLite 3 releases. There haven't been breaking changes since 2004. In addition, the library is incredibly easy to embed with any project as it's largely a self-contained system [1]. If SQLite 4 were released with breaking changes, you could happily continue using SQLite 3. [0] https://sqlite.org/onefile.html https://sqlite.org/onefile.html [1] https://sqlite.org/selfcontained.html https://sqlite.org/selfcontained.html
- upofadown 9y ago
  This is a document format we are talking about here that should be good for the ages, not just 13 years. In practice you would have to define your document format in terms of a particular version of SQLite. So you would have to unambiguously document that version in such detail that you cover everything that anyone would ever want and be able to do. Once that version of SQLite becomes unmaintained then things are broken. Someone would have to maintain that version of what would now be the ODF SQL disk format forever. Much of what would have to be maintained would be pointless complexity not required for the representation of documents.
  - SQLite 9y ago
    You seem to be operating under the assumption that the SQLite database file format is an ad-hoc, dodgy affair that the maintainers might change on a whim. I'd like to try to dispel that notion. The SQLite database file format has been unchanged (except by adding extensions through well-defined mechanisms) since 2004. And we developers have pledged to continue that 100% compatibility through at least the year 2050. The file format is cross-platform and byte-order independent. We have an extensive test suite to help us guarantee that each new version of SQLite is completely file compatible with all those that came before. The file format is documented (https://sqlite.org/fileformat.html https://sqlite.org/fileformat.html) to sufficient detail that the spec alone can and has been used to create a compatible reader/writer. (I know this because the authors of the compatible reader/writer software pointed out deficiencies in the spec which I subsequently corrected.) The file format is relatively simple. A reader for SQLite could be written from the spec with no more effort than it would take to implement a ZIP archive reader with its decompressor from spec. There is approximately 1 trillion (10^12) SQLite database files in active use today. Even without the promises from developers, the huge volume of legacy database files suggests that the SQLite database file format will be around for a long time yet. The latest version of SQLite will happily read and write any SQLite3 database written since the file format was designed in 2004. Ancient legacy versions of the SQLite library will read and write databases created yesterday, as long as those databases do not use any of the extensions that were added later.

bambax 9y ago

> Third Improvement: Versioning Regardless of SQLite, why don't modern productivity tools include at least an option to automatically version documents, ie store documents as a series of diffs that one could browse up to the start? Of course in many cases, edit history is better kept hidden, so this should not be the default. But why doesn't it exist at all?

hoov 9y ago
Difficult, but possible. We did this with Buzzword at Adobe. The hardest part was the UX, honestly. Most knowledge workers need some hand holding, to say nothing of teachers and students (we had success selling to high school teachers).
kccqzy 9y ago
I think Apple introduced this in 10.7: https://support.apple.com/en-us/HT202255 https://support.apple.com/en-us/HT202255 IMO this is better handled by the OS than by each individual app.
da_chicken 9y ago
The typical excuse is file space. The next excuse is privacy/security concerns. The next excuse is usually that other tools do it better (SharePoint, svn, git) for the types of files that really matter. MS Word has a Track Changes feature that kind of does this, and it's a pain because you have to remember to remove the revision history before you publish the document because it can expose details about the users that edited the document.

thoastbrot 9y ago

The typo in the very first sentence (where/were) puts me off :(

mwexler 9y ago

Look, I love SQLite as much as the next person, but as a database, it's got a lot of problems. From the lack of a formal time-date storage class to the "dynamic typing", if we are going to go binary and complex, is SQLite the best place to plan for future stability and ease of use? The first question is "Q1: Does a RDBMS help here?" and then 2nd "Q2: Given Q1=Y, Which might we use, what features are reqd and how are they implemented?". Parent jumped to SQLite as the answer (given the source) but I am unconvinced on Q2, or even Q1.

SQLite 9y ago

Related articles: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html https://www.sqlite.org/aff_short.html https://www.sqlite.org/aff_short.html

falcolas 9y ago

I'd like to take a moment and look at this "what if" from the point of view of data archiving. Zip files use a well documented, easily-reproducable and widely implemented deflate algorithm. It's reasonable to assume that someone could, 100 years from now, unzip an OpenDocument file. The contents are XML files - basically text - which can be read with any simple text editor and parsed. The XML is also at least somewhat self-documenting regarding the contained document. SQLite databases have a binary format which is known to change periodically. An attempt to read a SQLite file in 100 years would have to know the exact version of the file, and be able to reach back through history to find that version of SQLite (or a compatible version), and find a way to compile it for whatever CPU architecture exists in 100 years. This could be mitigated by including the appropriately versioned documentation of the binary format with the database itself - however to the best of my knowledge, full documentation of the binary format (outside the code) does not exist. The end product - a relational database - would also be less self-documenting than the XML version. IMO, it would be much better to keep the inefficient zipped XML files. [EDIT]: The binary file format: https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html Learn something new every day.

foldr 9y ago
Another solution to this problem would just be to bundle a txt description of the format with each file. So e.g. have the first n bytes be a plain text description of the sqlite format, and the rest be the DB in sqlite format.
- ZitchDog 9y ago
  May as well just bundle SQLite while you're at it.
  - foldr 9y ago
    Hmm? I mean sure, but the point is that adding a few kb of text to the file is a more lightweight solution. Less than the overhead of using xml, probably.

hokkos 9y ago

Interesting but where should the database normalization should end ? Should every text element with a single style be a separate entry with an associated style using a foreign key ? Should all the xml disappear and be coded around databases concepts ?

deleted 9y ago

[deleted]

zubairq 9y ago

This article about storing files in Sqlite is spot on and it works. I did exactly this and i have tested it with GBs of data and 1000s of files and it works well. The table structure I use is: CREATE TABLE IF NOT EXISTS files (id TEXT, name TEXT, contents BLOB); : as you can see the source code for the Javascript NodeJs web application file here: https://github.com/zubairq/gosharedata/blob/master/src/index.js https://github.com/zubairq/gosharedata/blob/master/src/index...