23 ms·

What If OpenDocument Used SQLite? (2014)

269 points by timeoperator 6y ago

wmf 6y ago
Standards like OpenDocument are expected to have multiple independent implementations. I'm not sure such a thing is feasible with SQLite (this is why WebSQL was rejected). I'm personally sympathetic to the idea that certain implementation-defined monocultures are OK but I don't think that idea has critical mass.
- usefulcat 6y ago
  I can think of reasons why re-implementing SQLite might lack appeal, buy why would it be infeasible? Even if the format is poorly- or under- specified (note: I have no idea whether that is true), you could definitely check the source.
  - otabdeveloper4 6y ago
    You'd need something like a "Sqlite standard" then, with a clear path to how to evolve it taking all stakeholder interests into account. Seems like a job at least as difficult as writing the sofware itself. (E.g., lack of a standard is why alternative Python implementations never went anywhere, even though the original cPython is, frankly, dreck from a software quality point of view.)
    - Scaevolus 6y ago
      There is a standard though-- the database file format. You can read and write database files without using any SQLite source code by conforming to the expected format: https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html It's not even that exotic: a bunch of rows in a simple binary format stored in a btree. The rollback journal / WAL are trickier, but aren't strictly necessary. Implementing arbitrary SQL queries on top of the file format is obviously _much_ harder. Here's an example project doing this-- a pure Go sqlite reader: https://github.com/alicebob/sqlittle https://github.com/alicebob/sqlittle
      - pbronez 6y ago
        I wonder if there’s a Presto connector for this file format, such that you could dump a bunch of SQLite files in object storage and run analytics on them like they were parquet. Of course, any such driver should probably just link SQLite itself...
    - ncmncm 6y ago
      SQLite has millions of lines of test code. It would actually be difficult to re-implement it in such a way that it is passes the tests, but is incompatible. Would it be as robust? Absolutely not, unless all the OS interaction code were transcribed directly. But Rust has no compiler that targets anywhere near the number of platforms SQLite is deployed on, and never will. The best that could be hoped for is that most such platforms would no longer be used, by the time the transcription is done.
- otabdeveloper4 6y ago
  Lack of multiple independent implementations means we can't ever switch to a different programing language or radically different system architecture. Nobody is feasibly going to rewrite Sqlite in Rust. So yeah, it is important.
  - kristianp 6y ago
    That's very interesting. The use case described is for quite a limited subset of sqlite. I imagine a limited query language and kv-store could suffice for the usage described in the article. (Probably could be done in a weekend! wink.)
  - GaelFG 6y ago
    Nobody is feasibly going to rewrite Sqlite in Rust. Per (Genuine) curiosity why ? I know (good) database systems are hard to make but do sqlite have particulary 'hard' part which are near impossible to replicate ? Especially since we now have a (I assume) well documented base implementation and an extensive test suite/history of error to avoid ? Is making à file based database that hard,compared to creating new programming language for exemple ?
    - fpoling 6y ago
      The problem is to write a reliable file database. Firefox got a lot of bugs related to history file corruption until it was replaced with SQLite. The same story was with Subversion where they replaced Berkeley key DB due to reliability issues. This reliability comes from a very deep knowledge of OS API and all their corner cases. Creating a programming language typically involves less corner cases and the bugs in the compiler/runtime are much simple to reproduce (again, typically).
    - merijnv 6y ago
      > Is making à file based database that hard,compared to creating new programming language for exemple ? Yes. I can hack together a trivial compiler in a weekend. A serious (non-optimising) compiler for a complex language is a task you could do in a few months. If you gave me two year I'm not sure I could implement a filesystem API that correctly did something as simple as "atomically append to a file". See also Dan Luu's writing on the topic of filesystems: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/
sargun 6y ago
The problem I have with sqlite for being a general purpose file format is that it’s difficult to take a sqlite file in any language and work with it without linking against code. I kind of wish that there were native implementations of sqlite in every language.
- Ecco 6y ago
  What's the problem of linking against SQLite? In my book it's much better because: - SQLite is coded in C, so performance and weight are going to be equal or better than any other reimplementation. - Having one code base means no compatibility issue between implementations - Also SQLite's codebase is so heavily tested and its track record is so good that I really don't see the point.
  - pvg 6y ago
    https://www.zdnet.com/article/google-chrome-impacted-by-new-magellan-2-0-vulnerabilities/ https://www.zdnet.com/article/google-chrome-impacted-by-new-...
    - sigzero 6y ago
      He didn't say it never has bugs. It does have a great track record and Dr. Hipp fixes things quickly when found.
    - cowsandmilk 6y ago
      If there were implementations of SQLite in other languages, do you believe chrome would have used a different implementation?
      - pvg 6y ago
        I don't know! But if this sort of thing can happen in Chrome, I think it's a pretty strong counterargument to "let's standardize formats in terms of a C implementation of a thing that parses a complicated language, among many other things".
Ecco 6y ago
Kind of reminds me of http://utf8everywhere.org/ http://utf8everywhere.org/, but in that case it'd be sqlite-everywhere. Which is a pretty good point. A large number of Apple-made iOS apps went this route, and they seem to be doing ok!
- takluyver 6y ago
  I think a lot of Android apps use Sqlite as well. But the achilles heel of Sqlite in my experience is network filesystems - it's well known & documented that Sqlite can suffer corruption on NFS, because of issues with locking. That doesn't really matter for iOS & Android apps, where you know storage is local, but it's a serious sticking point for desktop applications, where e.g. NFS home directories are not unusual.
  - pbronez 6y ago
    This sounds similar to the challenge of storing a git repo in a cloud-synced folder (Dropbox, one drive, etc). You have so little control over the syncing behavior in these systems that they just cause piles of locking errors. The solution I’m trying to this involves using a bare repo in the synced drive that’s used as a remote for a working repo on a separate, unsync’d folder.
nabla9 6y ago
Sustainability of Digital Formats: Planning for Library of Congress Collections https://www.loc.gov/preservation/digital/formats/fdd/fdd000461.shtml https://www.loc.gov/preservation/digital/formats/fdd/fdd0004... The Library of Congress Recommended Formats Statement (RFS) includes SQLite as a preferred format for datasets. The RFS does not specify a particular version of SQLite.
- svnpenn 6y ago
  I'd say better to export tables to CSV files, and dump the schema to an SQL file.
  - seveibar 6y ago
    Having worked with a lot of government CSV/XLSX datasets (e.g. IPEDS college reporting), I can say I'd prefer something with proper foreign keys and less ambiguity in structure (people like to put descriptive cells at the top of spreadsheets)
    - qwerty456127 6y ago
      Proper foreign keys don't really suit the dumped data case. Tables are usually imported one by one, in virtually random order so dependencies are often missing at the import time. Even if they were not, checking keys on every record import would mean a huge load for big datasets - that's really slow. That's why foreign key control is usually switched off before importing.
      - mulmen 6y ago
        Postgres at least can defer these checks, it's generally a best practice to disable rebuilding indexes while loading as well. It's not hard to simply load the tables in order so the keys are available. I would expect any decent unload utility to also create a script to load the data which should be in the right order. I think the implication here is that the archival should be done into a SQLite database and then all you need is a SQLite binary to read it. So there wouldn't even be a need to use a load script.
etimberg 6y ago
In a past job i tried hard to replace an Access database file with sqlite for a c++ desktop application. One big win from sqlite was on the testing front. You can just open an in-memory sqlite database and test against that without worrying about disk state, or state leaking between test runs.
- coddle-hark 6y ago
  We use in-memory sqlite databases for unit testing a MySQL backed web service. It’s great! You can build and test the code without setting up a MySQL server and no risk of state leaking like you said. The syntax isn’t exactly the same but we have a small wrapper layer that rewrites MySQL-specific queries into their SQLite equivalents. The only real downside is that it’s a C dependency which makes cross compiling a bit of a pain until you figure out the magic incantation the compiler wants.
systematical 6y ago
I use SQLite extensively for unit tests and demo code bases for my OSS libraries. For the latter because its super easy for someone to clone the code base and run the entire thing in seconds. Huge fan of SQLite in those two scenarios.
knighthack 6y ago
The article convinces me on the failure of the ZIP method in at least 3 respects: 1) For efficient incremental updates. Especially when you're saving a large file often, making constant single changes to ZIP files is just inefficient. 2) The insane memory gulps that ZIP takes. 3) The pile of files method. The data in OpenDocument files could be much better sorted/accessed through SQLite tables, rather than through XML files in ZIPs. I think SQLite makes far more sense for OpenDocument. Incidentally, SQLite has also made good arguments previously for its use to replace application file formats: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
dathinab 6y ago
While I generally agree with the points they make I'm not sure I would want a (stock) sqlite being used for "less" trusted files. While sqlite has a very comprehensive test suite there had been more than one attack with "manipulated" database files and as far as I know (I might be mistaken) the test suite(s) are not focused on testing that case much. Through I'm positive that it would be fine with a "hardened" sqlite where certain features are not compiled in at all and in turn the attack surface is reduced.
- teraflop 6y ago
  You could always sandbox it. This has actually already been done at least once: years ago, there used to be a "pure Java" JDBC driver for SQLite that was generated by compiling the original C code to a MIPS binary, and then translating the result into Java bytecode. http://web.archive.org/web/20080703234650/http://www.zentus.com/sqlitejdbc/ http://web.archive.org/web/20080703234650/http://www.zentus....
  - ReactiveJelly 6y ago
    https://hacks.mozilla.org/2020/02/securing-firefox-with-webassembly/ https://hacks.mozilla.org/2020/02/securing-firefox-with-weba... Firefox did the same recently to sandbox Graphite (some font dependency) C/C++ --> WebAsm --> sandboxed AOT compiled native code
- infogulch 6y ago
  The article describes the current format as a zip of files where the main content of presentation slides is stored in a `content.xml` file within. Yes, sqlite may have had file format vulnerabilities in the past, but I cannot imagine trusting it less than any xml library in existence.
  - vlovich123 6y ago
    You’d think so from reading [1]. [2] though paints a very different picture. > The attacker can submit a maliciously crafted database file to the application that the application will then open and query. I think it’s not impossible to secure SQLite better but it takes some work (like how Chrome sandboxes it in a separate process). [1] https://www.sqlite.org/security.html https://www.sqlite.org/security.html [2] https://www.sqlite.org/cves.html https://www.sqlite.org/cves.html
mattxxx 6y ago
In my experience, sqlite is rarely the wrong choice for a first pass at almost anything. It’s - Straightforward - Portable - Performant So, unless you have a good reason to not use it: - Human-readable/editable file format - Non-trivial queries across multiple servers - Specific performance optimizations needed - Etc. It’s a good first bet / gets you through mvp (and then some)
rlpb 6y ago
> The atomic update capabilities of SQLite allow small incremental changes to be safely written into the document. This reduces total disk I/O and improves File/Save performance, enhancing the user experience. How would this work without breaking the conventional "Save" paradigm? Unless the app tracks all incremental changes to apply when the user clicks "Save" instead of dumping it all back to disk, which seems difficult and error-prone to implement.
- mlyle 6y ago
  Modern word processing formats write down incremental changes, to avoid writing all of a massive document back to disk. Sometimes this causes people embarrassment, because if you send a normally saved file to someone else, they can often see the change history and intermediate versions.
  - mixcocam 6y ago
    This is indeed an issue. Most apps allow a (non default) saving manipulation which will remove the history.
- trevorishere 6y ago
  Microsoft more or less does this already with their Office Open XML file format in SharePoint Online/OneDrive. Small changes are read/written as users edit the doc. Real-time co-authoring locks on a per-paragraph basis.
- teraflop 6y ago
  > Unless the app tracks all incremental changes to apply when the user clicks "Save" instead of dumping it all back to disk, which seems difficult and error-prone to implement. That's the point of using SQLite instead of implementing it yourself. Because of how SQLite supports transactions, you can just update the database as you go; your changes will be physically written to disk, but they won't actually replace the old version from a reader's perspective until you explicitly commit, which is an atomic (and typically fast) operation. The downside of this approach is that if the application crashes while writing to disk, the database file itself might be in an inconsistent state, accompanied by a rollback journal (or write-ahead log) that contains the necessary information to recover it. But if you manually delete the journal, or otherwise separate it from its database, you get data corruption. That's probably fine as long as the user is a sysadmin who can be trusted to Just Not Do That(tm), but it's very user-hostile behavior for an office suite.
edoceo 6y ago
What would be really awesome is a PDF competitor. Put fonts, images, media in tables. Open spec so everyone can use. Perhaps an ability to lock/sign?
- fomine3 6y ago
  OpenXPS :)
- zelly 6y ago
  PDF is an open spec--even though it is so complicated that there's only one complete implementation which costs money.
  - scrollaway 6y ago
    Meet PDF/A, a safe subset of pdf which enforces certain features and is suitable for archival: https://www.loc.gov/preservation/digital/formats/fdd/fdd000318.shtml https://www.loc.gov/preservation/digital/formats/fdd/fdd0003... PDF/A is in use in several government institutions already, and there are validators out there etc.
- ReactiveJelly 6y ago
  You could do it with packed HTML, and run a little localhost web server to plug it into a browser. HTML is a mess, but at least it's popular.
  - pessimizer 6y ago
    PDF is already a popular mess, and open. If you were going to reimplement on top of sqlite, the value would be from implementing it sanely.
    - BlueTemplar 6y ago
      PDF, unlike HTML, is inappropriate for most digital documents.
  - 6y ago
WClayFerguson 6y ago
I am the developer of a platform that hosts documents in a NoSQL (MongoDB) so that every paragraph of text is a 'record' in MongoDB and there's a 'path' property which is what builds out a 'tree' structure to give the documents a hierarchy. You guys might be interested if you're interested in new kinds of document/wiki innovations. https://quanta.wiki https://quanta.wiki
dang 6y ago
If curious see also 2017 https://news.ycombinator.com/item?id=15607316 https://news.ycombinator.com/item?id=15607316
danellis 6y ago
This reminds of the WebSQL spec being dropped because the only implementations used SQLite: > The W3C Web Applications Working Group ceased working on the specification in November 2010, citing a lack of independent implementations (i.e. using database system other than SQLite as the backend) as the reason the specification could not move forward to become a W3C Recommendation. -- Wikipedia > This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. -- W3C I wonder how much of their reasoning applies here.
- tasogare 6y ago
  It strange that Microsoft didn’t try to use Sql Server Compact Edition, which was available on Windows Phone, to make the distinct required implementation.
  - pbronez 6y ago
    Fascinating, never hears of SQL Server Compact Edition before. Unfortunately Wikipedia reports it was deprecated in 2013 and will EOL in 2021. Also it doesn’t have an ODBC driver. Certainly not a contemporary alternative to SQLite.
  - detaro 6y ago
    The main problem was that the WebSQL "spec" draft did basically say: "Database that supports SQL the same way as this specific version of SQLite does", and nobody felt it was important enough to put in the work to actually specify something that could be implemented long-term.
- chungy 6y ago
  I've grown to not mind WebSQL being dropped, despite that 10 years ago, I wish it were standardized. WebAssembly's introduction a few years later means that SQLite can be built and used as a wasm module, and you aren't limited by whatever old version of SQLite was used for WebSQL -- you can always update it independently according to your needs. A lot of sites actually do this.
zelly 6y ago
Maybe this is the right thread to ask: I vaguely remember a file archive format similar to JAR/WAR (for Java) or ASAN (for Electron) which was designed for bundling software with its dependencies and static resources. It is not a container because it's just files with no specification for its runtime. Unlike JAR, it is language agnostic. These bundles could be compiled and compressed on your local machine then scp'd to a server that knows how to execute this bundle. If I recall correctly, it was some open source project from a big company. I've spent hours trying to find it again and am wondering if I might be remembering something from a dream.
- Twisol 6y ago
  XAR, perchance? https://github.com/facebookincubator/xar https://github.com/facebookincubator/xar
  - zelly 6y ago
    Yes, that is it. Thank you.
  - mxuribe 6y ago
    Wow, i never heard of this; sounds cool! I just started reading up on PEX, but now will read a little more on XAR. Thanks for sharing; TIL!
- theblazehen 6y ago
  You might be thinking of AppImage?
cyingfan 6y ago
The only application/document file format I know that uses sqlite is https://www.giuspen.com/cherrytree/ https://www.giuspen.com/cherrytree/. Is there others out there worth mentioning?
molyss 6y ago
Does that still apply if the DB is in WAL mode ? according to https://www.sqlite.org/fileformat2.html https://www.sqlite.org/fileformat2.html I'd say "no", because it sounds like the journal file might be necessary if the DB dies unexpectedly
ahoka 6y ago
Umm, because it's a proprietary format, with questionable licensing?
- chungy 6y ago
  Neither OpenDocument, SQLite, nor Zip are proprietary formats. Come again?
teleforce 6y ago
Why not go to the extreme and just use TileDB for all the documents? By using it you can utilize universal storage engine supporting Arrow format that should work with SQL, NoSQL, dataframe and even streaming systems.
ncmncm 6y ago
All I would like from OpenDocument, and LibreOffice, is an ASCII storage format that is not specifically designed to be incompatible with revision control systems. As it is, they have an almost-serviceable format that fails only by having each line start with a random number that differs from the number on the corresponding line that was originally read in. Fixing it would not even be an incompatible change! They could just remember the number that was there, and write it back out, next time. There is a report for this in their bug tracker that mentions SVN because there was no Git, yet, when it was posted.
- MayeulC 6y ago
  Not exactly a complete answer to your problem, but you can at least improve diffs for odt documents by using git textconv: *.ods diff=odf *.odt diff=odf *.odp diff=odf *.ods difftool=odf *.odt difftool=odf *.odp difftool=odf [diff "odf"] textconv=odt2txt https://git.wiki.kernel.org/index.php/Textconv https://git.wiki.kernel.org/index.php/Textconv
- sally1620 6y ago
  There is a single XML variant of the open document format. although images and videos won't be embedded in the document that way. I used this format when I was generating ODF documents using a script.
jamesfisher 6y ago
Saving the versioning in the same file would lead to security issues. Imagine redacting a file before sending it to someone. All they have to do is roll back a few versions to recover the original. (Note this is not a criticism of the "use SQLite" argument; just a criticism of the specific schema.)
- deleted 6y ago
  [deleted]
alkonaut 6y ago
The whole "Writes are atomic" still breaks down if they assume that disks are local. Having worked with one of these zip document format for 20 years now, I have found that local disk is the edge case, and SMB is the norm in corporate environments. Sure you can download the sqlite file at the start of a session and work on it locally in a temp directory with atomic writes - but in the end the user doesn't think they have "saved" until version N+1 of the file is actually on their network share.
- galgalesh 6y ago
  SQLite tries to detect when the database is on a network share and changes it's behavior accordingly.
  - alkonaut 6y ago
    This doesn't really work as well as one would like. It's not really related to sqlite it's just that expectations of file system behavior (locking, operations having completed when the system says they have, etc) are just flimsy fo many network file system implementations. All SQLite can do when it can't map the file as it would normally is basically upload the new version of the file, and not write it in place anyway. That behavior is just as easy to create by the application developer.
  - niea_11 6y ago
    The sqlite documentation advises against using it in that scenario: If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network. text from here : https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
swiley 6y ago
I'm a huge fan of sqlite but I'm not sure it's an improvement over xml for holding a DOM. Especially since there's more or less just one implementation.
sally1620 6y ago
I wonder if anyone has implemented a document file format based on SQLite. SQLite file format is documented and other implementations exist. I am sure given enough interest, it could get standardized.

269 points by timeoperator 6y ago

wmf 6y ago

Standards like OpenDocument are expected to have multiple independent implementations. I'm not sure such a thing is feasible with SQLite (this is why WebSQL was rejected). I'm personally sympathetic to the idea that certain implementation-defined monocultures are OK but I don't think that idea has critical mass.

usefulcat 6y ago
I can think of reasons why re-implementing SQLite might lack appeal, buy why would it be infeasible? Even if the format is poorly- or under- specified (note: I have no idea whether that is true), you could definitely check the source.
- otabdeveloper4 6y ago
  You'd need something like a "Sqlite standard" then, with a clear path to how to evolve it taking all stakeholder interests into account. Seems like a job at least as difficult as writing the sofware itself. (E.g., lack of a standard is why alternative Python implementations never went anywhere, even though the original cPython is, frankly, dreck from a software quality point of view.)
  - Scaevolus 6y ago
    There is a standard though-- the database file format. You can read and write database files without using any SQLite source code by conforming to the expected format: https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html It's not even that exotic: a bunch of rows in a simple binary format stored in a btree. The rollback journal / WAL are trickier, but aren't strictly necessary. Implementing arbitrary SQL queries on top of the file format is obviously _much_ harder. Here's an example project doing this-- a pure Go sqlite reader: https://github.com/alicebob/sqlittle https://github.com/alicebob/sqlittle
    - pbronez 6y ago
      I wonder if there’s a Presto connector for this file format, such that you could dump a bunch of SQLite files in object storage and run analytics on them like they were parquet. Of course, any such driver should probably just link SQLite itself...
  - ncmncm 6y ago
    SQLite has millions of lines of test code. It would actually be difficult to re-implement it in such a way that it is passes the tests, but is incompatible. Would it be as robust? Absolutely not, unless all the OS interaction code were transcribed directly. But Rust has no compiler that targets anywhere near the number of platforms SQLite is deployed on, and never will. The best that could be hoped for is that most such platforms would no longer be used, by the time the transcription is done.
otabdeveloper4 6y ago
Lack of multiple independent implementations means we can't ever switch to a different programing language or radically different system architecture. Nobody is feasibly going to rewrite Sqlite in Rust. So yeah, it is important.
- kristianp 6y ago
  That's very interesting. The use case described is for quite a limited subset of sqlite. I imagine a limited query language and kv-store could suffice for the usage described in the article. (Probably could be done in a weekend! wink.)
- GaelFG 6y ago
  Nobody is feasibly going to rewrite Sqlite in Rust. Per (Genuine) curiosity why ? I know (good) database systems are hard to make but do sqlite have particulary 'hard' part which are near impossible to replicate ? Especially since we now have a (I assume) well documented base implementation and an extensive test suite/history of error to avoid ? Is making à file based database that hard,compared to creating new programming language for exemple ?
  - fpoling 6y ago
    The problem is to write a reliable file database. Firefox got a lot of bugs related to history file corruption until it was replaced with SQLite. The same story was with Subversion where they replaced Berkeley key DB due to reliability issues. This reliability comes from a very deep knowledge of OS API and all their corner cases. Creating a programming language typically involves less corner cases and the bugs in the compiler/runtime are much simple to reproduce (again, typically).
  - merijnv 6y ago
    > Is making à file based database that hard,compared to creating new programming language for exemple ? Yes. I can hack together a trivial compiler in a weekend. A serious (non-optimising) compiler for a complex language is a task you could do in a few months. If you gave me two year I'm not sure I could implement a filesystem API that correctly did something as simple as "atomically append to a file". See also Dan Luu's writing on the topic of filesystems: https://danluu.com/deconstruct-files/ https://danluu.com/deconstruct-files/

sargun 6y ago

The problem I have with sqlite for being a general purpose file format is that it’s difficult to take a sqlite file in any language and work with it without linking against code. I kind of wish that there were native implementations of sqlite in every language.

Ecco 6y ago
What's the problem of linking against SQLite? In my book it's much better because: - SQLite is coded in C, so performance and weight are going to be equal or better than any other reimplementation. - Having one code base means no compatibility issue between implementations - Also SQLite's codebase is so heavily tested and its track record is so good that I really don't see the point.
- pvg 6y ago
  https://www.zdnet.com/article/google-chrome-impacted-by-new-magellan-2-0-vulnerabilities/ https://www.zdnet.com/article/google-chrome-impacted-by-new-...
  - sigzero 6y ago
    He didn't say it never has bugs. It does have a great track record and Dr. Hipp fixes things quickly when found.
  - cowsandmilk 6y ago
    If there were implementations of SQLite in other languages, do you believe chrome would have used a different implementation?
    - pvg 6y ago
      I don't know! But if this sort of thing can happen in Chrome, I think it's a pretty strong counterargument to "let's standardize formats in terms of a C implementation of a thing that parses a complicated language, among many other things".

Ecco 6y ago

Kind of reminds me of http://utf8everywhere.org/ http://utf8everywhere.org/, but in that case it'd be sqlite-everywhere. Which is a pretty good point. A large number of Apple-made iOS apps went this route, and they seem to be doing ok!

takluyver 6y ago
I think a lot of Android apps use Sqlite as well. But the achilles heel of Sqlite in my experience is network filesystems - it's well known & documented that Sqlite can suffer corruption on NFS, because of issues with locking. That doesn't really matter for iOS & Android apps, where you know storage is local, but it's a serious sticking point for desktop applications, where e.g. NFS home directories are not unusual.
- pbronez 6y ago
  This sounds similar to the challenge of storing a git repo in a cloud-synced folder (Dropbox, one drive, etc). You have so little control over the syncing behavior in these systems that they just cause piles of locking errors. The solution I’m trying to this involves using a bare repo in the synced drive that’s used as a remote for a working repo on a separate, unsync’d folder.

nabla9 6y ago

Sustainability of Digital Formats: Planning for Library of Congress Collections https://www.loc.gov/preservation/digital/formats/fdd/fdd000461.shtml https://www.loc.gov/preservation/digital/formats/fdd/fdd0004... The Library of Congress Recommended Formats Statement (RFS) includes SQLite as a preferred format for datasets. The RFS does not specify a particular version of SQLite.

svnpenn 6y ago
I'd say better to export tables to CSV files, and dump the schema to an SQL file.
- seveibar 6y ago
  Having worked with a lot of government CSV/XLSX datasets (e.g. IPEDS college reporting), I can say I'd prefer something with proper foreign keys and less ambiguity in structure (people like to put descriptive cells at the top of spreadsheets)
  - qwerty456127 6y ago
    Proper foreign keys don't really suit the dumped data case. Tables are usually imported one by one, in virtually random order so dependencies are often missing at the import time. Even if they were not, checking keys on every record import would mean a huge load for big datasets - that's really slow. That's why foreign key control is usually switched off before importing.
    - mulmen 6y ago
      Postgres at least can defer these checks, it's generally a best practice to disable rebuilding indexes while loading as well. It's not hard to simply load the tables in order so the keys are available. I would expect any decent unload utility to also create a script to load the data which should be in the right order. I think the implication here is that the archival should be done into a SQLite database and then all you need is a SQLite binary to read it. So there wouldn't even be a need to use a load script.

etimberg 6y ago

In a past job i tried hard to replace an Access database file with sqlite for a c++ desktop application. One big win from sqlite was on the testing front. You can just open an in-memory sqlite database and test against that without worrying about disk state, or state leaking between test runs.

coddle-hark 6y ago
We use in-memory sqlite databases for unit testing a MySQL backed web service. It’s great! You can build and test the code without setting up a MySQL server and no risk of state leaking like you said. The syntax isn’t exactly the same but we have a small wrapper layer that rewrites MySQL-specific queries into their SQLite equivalents. The only real downside is that it’s a C dependency which makes cross compiling a bit of a pain until you figure out the magic incantation the compiler wants.

systematical 6y ago

I use SQLite extensively for unit tests and demo code bases for my OSS libraries. For the latter because its super easy for someone to clone the code base and run the entire thing in seconds. Huge fan of SQLite in those two scenarios.

knighthack 6y ago

The article convinces me on the failure of the ZIP method in at least 3 respects: 1) For efficient incremental updates. Especially when you're saving a large file often, making constant single changes to ZIP files is just inefficient. 2) The insane memory gulps that ZIP takes. 3) The pile of files method. The data in OpenDocument files could be much better sorted/accessed through SQLite tables, rather than through XML files in ZIPs. I think SQLite makes far more sense for OpenDocument. Incidentally, SQLite has also made good arguments previously for its use to replace application file formats: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html

dathinab 6y ago

While I generally agree with the points they make I'm not sure I would want a (stock) sqlite being used for "less" trusted files. While sqlite has a very comprehensive test suite there had been more than one attack with "manipulated" database files and as far as I know (I might be mistaken) the test suite(s) are not focused on testing that case much. Through I'm positive that it would be fine with a "hardened" sqlite where certain features are not compiled in at all and in turn the attack surface is reduced.

teraflop 6y ago
You could always sandbox it. This has actually already been done at least once: years ago, there used to be a "pure Java" JDBC driver for SQLite that was generated by compiling the original C code to a MIPS binary, and then translating the result into Java bytecode. http://web.archive.org/web/20080703234650/http://www.zentus.com/sqlitejdbc/ http://web.archive.org/web/20080703234650/http://www.zentus....
- ReactiveJelly 6y ago
  https://hacks.mozilla.org/2020/02/securing-firefox-with-webassembly/ https://hacks.mozilla.org/2020/02/securing-firefox-with-weba... Firefox did the same recently to sandbox Graphite (some font dependency) C/C++ --> WebAsm --> sandboxed AOT compiled native code
infogulch 6y ago
The article describes the current format as a zip of files where the main content of presentation slides is stored in a `content.xml` file within. Yes, sqlite may have had file format vulnerabilities in the past, but I cannot imagine trusting it less than any xml library in existence.
- vlovich123 6y ago
  You’d think so from reading [1]. [2] though paints a very different picture. > The attacker can submit a maliciously crafted database file to the application that the application will then open and query. I think it’s not impossible to secure SQLite better but it takes some work (like how Chrome sandboxes it in a separate process). [1] https://www.sqlite.org/security.html https://www.sqlite.org/security.html [2] https://www.sqlite.org/cves.html https://www.sqlite.org/cves.html

mattxxx 6y ago

In my experience, sqlite is rarely the wrong choice for a first pass at almost anything. It’s - Straightforward - Portable - Performant So, unless you have a good reason to not use it: - Human-readable/editable file format - Non-trivial queries across multiple servers - Specific performance optimizations needed - Etc. It’s a good first bet / gets you through mvp (and then some)

rlpb 6y ago

> The atomic update capabilities of SQLite allow small incremental changes to be safely written into the document. This reduces total disk I/O and improves File/Save performance, enhancing the user experience. How would this work without breaking the conventional "Save" paradigm? Unless the app tracks all incremental changes to apply when the user clicks "Save" instead of dumping it all back to disk, which seems difficult and error-prone to implement.

mlyle 6y ago
Modern word processing formats write down incremental changes, to avoid writing all of a massive document back to disk. Sometimes this causes people embarrassment, because if you send a normally saved file to someone else, they can often see the change history and intermediate versions.
- mixcocam 6y ago
  This is indeed an issue. Most apps allow a (non default) saving manipulation which will remove the history.
trevorishere 6y ago
Microsoft more or less does this already with their Office Open XML file format in SharePoint Online/OneDrive. Small changes are read/written as users edit the doc. Real-time co-authoring locks on a per-paragraph basis.
teraflop 6y ago
> Unless the app tracks all incremental changes to apply when the user clicks "Save" instead of dumping it all back to disk, which seems difficult and error-prone to implement. That's the point of using SQLite instead of implementing it yourself. Because of how SQLite supports transactions, you can just update the database as you go; your changes will be physically written to disk, but they won't actually replace the old version from a reader's perspective until you explicitly commit, which is an atomic (and typically fast) operation. The downside of this approach is that if the application crashes while writing to disk, the database file itself might be in an inconsistent state, accompanied by a rollback journal (or write-ahead log) that contains the necessary information to recover it. But if you manually delete the journal, or otherwise separate it from its database, you get data corruption. That's probably fine as long as the user is a sysadmin who can be trusted to Just Not Do That(tm), but it's very user-hostile behavior for an office suite.

edoceo 6y ago

What would be really awesome is a PDF competitor. Put fonts, images, media in tables. Open spec so everyone can use. Perhaps an ability to lock/sign?

fomine3 6y ago
OpenXPS :)
zelly 6y ago
PDF is an open spec--even though it is so complicated that there's only one complete implementation which costs money.
- scrollaway 6y ago
  Meet PDF/A, a safe subset of pdf which enforces certain features and is suitable for archival: https://www.loc.gov/preservation/digital/formats/fdd/fdd000318.shtml https://www.loc.gov/preservation/digital/formats/fdd/fdd0003... PDF/A is in use in several government institutions already, and there are validators out there etc.
ReactiveJelly 6y ago
You could do it with packed HTML, and run a little localhost web server to plug it into a browser. HTML is a mess, but at least it's popular.
- pessimizer 6y ago
  PDF is already a popular mess, and open. If you were going to reimplement on top of sqlite, the value would be from implementing it sanely.
  - BlueTemplar 6y ago
    PDF, unlike HTML, is inappropriate for most digital documents.
- 6y ago

WClayFerguson 6y ago

I am the developer of a platform that hosts documents in a NoSQL (MongoDB) so that every paragraph of text is a 'record' in MongoDB and there's a 'path' property which is what builds out a 'tree' structure to give the documents a hierarchy. You guys might be interested if you're interested in new kinds of document/wiki innovations. https://quanta.wiki https://quanta.wiki

dang 6y ago

If curious see also 2017 https://news.ycombinator.com/item?id=15607316 https://news.ycombinator.com/item?id=15607316

danellis 6y ago

This reminds of the WebSQL spec being dropped because the only implementations used SQLite: > The W3C Web Applications Working Group ceased working on the specification in November 2010, citing a lack of independent implementations (i.e. using database system other than SQLite as the backend) as the reason the specification could not move forward to become a W3C Recommendation. -- Wikipedia > This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. -- W3C I wonder how much of their reasoning applies here.

tasogare 6y ago
It strange that Microsoft didn’t try to use Sql Server Compact Edition, which was available on Windows Phone, to make the distinct required implementation.
- pbronez 6y ago
  Fascinating, never hears of SQL Server Compact Edition before. Unfortunately Wikipedia reports it was deprecated in 2013 and will EOL in 2021. Also it doesn’t have an ODBC driver. Certainly not a contemporary alternative to SQLite.
- detaro 6y ago
  The main problem was that the WebSQL "spec" draft did basically say: "Database that supports SQL the same way as this specific version of SQLite does", and nobody felt it was important enough to put in the work to actually specify something that could be implemented long-term.
chungy 6y ago
I've grown to not mind WebSQL being dropped, despite that 10 years ago, I wish it were standardized. WebAssembly's introduction a few years later means that SQLite can be built and used as a wasm module, and you aren't limited by whatever old version of SQLite was used for WebSQL -- you can always update it independently according to your needs. A lot of sites actually do this.

zelly 6y ago

Maybe this is the right thread to ask: I vaguely remember a file archive format similar to JAR/WAR (for Java) or ASAN (for Electron) which was designed for bundling software with its dependencies and static resources. It is not a container because it's just files with no specification for its runtime. Unlike JAR, it is language agnostic. These bundles could be compiled and compressed on your local machine then scp'd to a server that knows how to execute this bundle. If I recall correctly, it was some open source project from a big company. I've spent hours trying to find it again and am wondering if I might be remembering something from a dream.

Twisol 6y ago
XAR, perchance? https://github.com/facebookincubator/xar https://github.com/facebookincubator/xar
- zelly 6y ago
  Yes, that is it. Thank you.
- mxuribe 6y ago
  Wow, i never heard of this; sounds cool! I just started reading up on PEX, but now will read a little more on XAR. Thanks for sharing; TIL!
theblazehen 6y ago
You might be thinking of AppImage?

cyingfan 6y ago

The only application/document file format I know that uses sqlite is https://www.giuspen.com/cherrytree/ https://www.giuspen.com/cherrytree/. Is there others out there worth mentioning?

molyss 6y ago

Does that still apply if the DB is in WAL mode ? according to https://www.sqlite.org/fileformat2.html https://www.sqlite.org/fileformat2.html I'd say "no", because it sounds like the journal file might be necessary if the DB dies unexpectedly

ahoka 6y ago

Umm, because it's a proprietary format, with questionable licensing?

chungy 6y ago
Neither OpenDocument, SQLite, nor Zip are proprietary formats. Come again?

teleforce 6y ago

Why not go to the extreme and just use TileDB for all the documents? By using it you can utilize universal storage engine supporting Arrow format that should work with SQL, NoSQL, dataframe and even streaming systems.

ncmncm 6y ago

All I would like from OpenDocument, and LibreOffice, is an ASCII storage format that is not specifically designed to be incompatible with revision control systems. As it is, they have an almost-serviceable format that fails only by having each line start with a random number that differs from the number on the corresponding line that was originally read in. Fixing it would not even be an incompatible change! They could just remember the number that was there, and write it back out, next time. There is a report for this in their bug tracker that mentions SVN because there was no Git, yet, when it was posted.

MayeulC 6y ago
Not exactly a complete answer to your problem, but you can at least improve diffs for odt documents by using git textconv: *.ods diff=odf *.odt diff=odf *.odp diff=odf *.ods difftool=odf *.odt difftool=odf *.odp difftool=odf [diff "odf"] textconv=odt2txt https://git.wiki.kernel.org/index.php/Textconv https://git.wiki.kernel.org/index.php/Textconv
sally1620 6y ago
There is a single XML variant of the open document format. although images and videos won't be embedded in the document that way. I used this format when I was generating ODF documents using a script.

jamesfisher 6y ago

Saving the versioning in the same file would lead to security issues. Imagine redacting a file before sending it to someone. All they have to do is roll back a few versions to recover the original. (Note this is not a criticism of the "use SQLite" argument; just a criticism of the specific schema.)

deleted 6y ago
[deleted]

alkonaut 6y ago

The whole "Writes are atomic" still breaks down if they assume that disks are local. Having worked with one of these zip document format for 20 years now, I have found that local disk is the edge case, and SMB is the norm in corporate environments. Sure you can download the sqlite file at the start of a session and work on it locally in a temp directory with atomic writes - but in the end the user doesn't think they have "saved" until version N+1 of the file is actually on their network share.

galgalesh 6y ago
SQLite tries to detect when the database is on a network share and changes it's behavior accordingly.
- alkonaut 6y ago
  This doesn't really work as well as one would like. It's not really related to sqlite it's just that expectations of file system behavior (locking, operations having completed when the system says they have, etc) are just flimsy fo many network file system implementations. All SQLite can do when it can't map the file as it would normally is basically upload the new version of the file, and not write it in place anyway. That behavior is just as easy to create by the application developer.
- niea_11 6y ago
  The sqlite documentation advises against using it in that scenario: If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network. text from here : https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

swiley 6y ago

I'm a huge fan of sqlite but I'm not sure it's an improvement over xml for holding a DOM. Especially since there's more or less just one implementation.

sally1620 6y ago

I wonder if anyone has implemented a document file format based on SQLite. SQLite file format is documented and other implementations exist. I am sure given enough interest, it could get standardized.