10 ms·
The great thing about applications that use sqlite as their application file format is that you can write secondary apps and utilities to supplement the applica
by TheRealPomax 6y ago
The great thing about applications that use sqlite as their application file format is that you can write secondary apps and utilities to supplement the applications themselves.
For example, Adobe's Lightroom uses sqlite as their application file format, which means that it's almost trivial to write an application to help it do things it's either really bad/exceedingly slow at (like removing images from a catalogue that no longer exist on disk) or literally can't do (like generating a playlist of files-on-disk for a specific collection or tag), without being locked into whatever janky scripting solution exists in-app. If there even is one to begin with.
All you need is a programming language with a sqlite connector, and you're in the driving seat. And sure, you'll need to figure out the table schemas you need to care about, but sqlite comes with sqldiff so it's really easy to figure out which operations hit which tables with only a few minutes of work.
Good luck reverse engineering proprietary file formats in the same way!
- TheRealPomax 6y agoHeck, I wrote https://github.com/Pomax/lightroom-catalog-helper https://github.com/Pomax/lightroom-catalog-helper exactly because lrcat is an open format that takes barely any time to figure out, which means the only limit to what you can do with "lightroom" is what your programming skills allow for.
- mongol 6y agoI tried to do that with a Firefox SQLite database files but it had an exclusive lock.
- TheRealPomax 6y agoThis is the case for any application that constantly writes to their dbs, so you usually run your own application/utility on them when the "parent" application isn't running.
- simonw 6y agoYou can usually work around that by creating a copy - I run "cp /path/to/locked.db /tmp" all the time for this.
- pjscott 6y agoMake sure to also copy locked.db-wal if it's present. (Is there a better way to do all this? I feel like there really ought to be a better way.)
- petters 6y agoIt's very annoying when the applications use sqlite with encryption. That is not open source, so regular language bindings can not be used.
- TheRealPomax 6y agoI'm not sure how slqite as your application file format has anything to do with open source, honestly. It's certainly super easy for open source projects to use, but a closed source proprietary application using an encrypted sqlite file sounds perfectly sensible to me? We all win when folks decide to leave it accessible, but I'm not going to hold "encrypting a file format so that people can't easily reverse engineer it" against folks who are trying to sell software.
- cfn 6y agoI guess he/she meant that the extension that encrypts the database is proprietary. I believe that was the only option for a while but now there is an open source alternative.
- yellowapple 6y agoIs the open-source alternative bidirectionally interoperable with the closed-source version (i.e. it can decrypt the proprietary encryption and the proprietary version can decrypt the FOSS version)? If so, then that's handy.
- justinclift 6y agoThe "officially blessed" (my wording) encryption for SQLite is SEE (proprietary): https://www.sqlite.org/see/doc/release/www/index.wiki https://www.sqlite.org/see/doc/release/www/index.wiki There are third party encryption approaches for SQLite. One of the most popular is SQLCipher: https://github.com/sqlcipher/sqlcipher/ https://github.com/sqlcipher/sqlcipher/ Those two don't have compatible file formats. eg no interoperability Not sure if any others do, but yeah it would be handy if there are.
- lbutler 6y agoI have an application that uses sqlite as their file format but they've cleared the header so you can't open it directly. I'd love to be able to make secondary applications like you've described but being enterprise software they don't want to make it too easy. They obviously want to keep people locked in with their $40k per seat application! I guess the first step is figuring out the page size and other bits the other meta data you set in the header [1]. I know I just have to sit down and understand the format better and I will eventually figure it out... [1] https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html
- TheRealPomax 6y agoThat's mean, but you'll just have to live with that. If someone takes active steps towards preventing people from making secondary apps/utils, then they're (a) jerks, and (b) allowed to do that. Might be worth actually contacting them to ask why, if you can make the case that secondary applications will increase the value of their app, not decrease it.
- lbutler 6y agoI can understand why they do it, it's enterprise software so the more open you make it, then the less licences they can sell. They do have a module you can purchase to run API calls and access their files/software but as you probably guessed that's another $40k license! Most of my apps I build use this API, but for me to provide to other companies they need them to also buy the API extension. I'd love to cut out the middle man and I'll do it eventually when I reverse engineer the header!
- mrmonkeyman 6y agoI don't get this mentality. You haven't created this system. You have no idea what it takes or else you would have created it yourself. You don't like the price, but you want it anyway. How is this, I mean come on, different from stealing? You even say it: cutting out the middle man. You want to remove their profits.
- 6y ago
- simonw 6y agoCompletely agree - I wrote about some fun I had with Apple Photos last month: https://simonwillison.net/2020/May/21/dogsheep-photos/ https://simonwillison.net/2020/May/21/dogsheep-photos/
- tomduncalf 6y agoEnjoyed this post a lot and had no idea apple use SQLite u see the hood for a lot of stuff - thanks for sharing!
- saagarjha 6y agoMost Mac apps are backed by a SQLite database.
- TheRealPomax 6y agoIt opens up so many possibilities. Nice write-up!
- me551ah 6y agoThe only thing stopping SQLite from becoming the primary application file format is browsers. Every single other client supports SQLite. Embedded, Mobile, Lightroom, Desktop Apps and Cross platform mobile frameworks have really good support for SQLite.
- TheRealPomax 6y agoPrimary application file format for... what kind of files? Because it's certainly not going to (nor should it ever) replace hypertext documents. Being able to load a read-only .sqlite database might seem cool, but I can't think of a single instance in which that's smaller and/or more efficient than calling data endpoints that use gzip/brotli for transport. Or are you thinking "as replacement for IndexedDB"? In which case, hard agree but then it's an actual database, not used as file data container.
- HelloNurse 6y agoOn the one hand, browsers dislike access to files (regardless of format) for security reasons. On the other hand, browsers use SQLite a lot. For example Firefox uses SQLite at least for IndexedDB (various files) and for history and bookmarks (places.sqlite): https://developer.mozilla.org/en-US/docs/Mozilla/Firefox_Operational_Information_Database:_SQLite https://developer.mozilla.org/en-US/docs/Mozilla/Firefox_Ope...
- mbreese 6y agoWasn't there an effort a few years back to try to create a W3C standard for accessing data that stalled because all of the browsers used SQLite and no one wanted to create a different implementation? IIRC, the standard required two different implementations and SQLite was used by everyone?
- EvanAnderson 6y agoYou're think of WebSQL. My take on it dying was a combination of "key-value stores can replace relational semantics and key-value stores are cool", "only oldsters use SQL", and "wheel-reinventing is fun". The whole "we need multiple independent implementations" was a convenient excuse to kill it in my view.
- vbezhenar 6y agoIs it any different from JSON or XML?
- TheRealPomax 6y agothat's like asking whether .zip is any different from .txt - yes, it's rather quite a lot different. A databases--that-is-a-file-on-disk, used as application file format for containing arbitrary -and arbitrarily complex- data is almost nothing like a plain text markup document.
- vbezhenar 6y agoDatabases, XML and JSON are structured formats. They contain embedded metadata (tag names/keys/table&column names) so reverse-engineering is much easier. I don't really see any difference or advantage of using SQLite over XML, unless you need full RDBMS engine power for your configuration (highly unlikely).
- TheRealPomax 6y agoThen it kind of feels like you're willfully ignoring the differences just so you can claim "they're structured formats". 1. SQLite database files are self descriptive, which JSON and XML are not. 2. SQLite is a compressed binary format. JSON and XML are plain text. 3. building on that, SQLite files were designed to contain arbitrary data, and you can store whatever you want using the BLOB datatype. JSON and XML can't, they have no notion of types, everything has to be syntax-compliant strings. 4. SQLite files can be encrypted. JSON and XML can't. 5. SQLite files were designed to be queried. JSON and XML are not. And I already hear you try to object, so let's expand: 1. JSON and XML cannot describe their own structure, their syntax is prescribed, but any schema has to come from either external files, like XML's DTD, or from literally nothing because JSON has no official schema language. Yes, https://json-schema.org/ https://json-schema.org/ exists, but it's certainly not an official spec - maybe "yet", maybe just "full stop". 2. Yes, you can compress JSON/XML using zip/etc but now it's no longer JSON/XML. Now it's an archive file like any other archive file, and it's "nothing" you can work with until you unpack it, incurring delays, and then repack it once you're done, incurring even more delays (because packing is far more work than unpacking), and worse: the bigger the file gets, the longer the delay becomes. 3. Sure, you can convert binary to a text format and then put that in JSON/XML too, but there no types: you have no way of universally indicating which field is plain text, and which field is binary-as-text nor a way to universally indicate which encoding you've used. 4. Same as (2): sure, you can encrypt the data yourself, but there is no universal spec for indicating what encryption you used for which fields, or parts of the file, or the entire file, in JSON or XML. 5. JSON and XML are data serialization formats: they are perfect for transport, but they are not data repositories and even at the spec level make zero affordances towards efficient data retrieval, storage, and representation. Which for an application file format is critical. Can you use JSON/XML as application file formats? No, not really. They're terrible for that purpose. They might work for configs (as you point out), and they are fantastic for moving small quantities of data from one system to another (for large quantities, they make no sense: even something as dumb as CSV becomes more efficient when dealing with lots of data) but they are absolutely unsuitable for storing arbitrary application data, because they are horrendously inefficient for almost everything an application needs out of a good file format, without rolling your own standards, for which there is no enforcement tooling unless you write that yourself, too. And then get others to adopt it as well. And now you're started down the path of replicating what SQLite already does, and doing so poorly.
- unspecified 6y ago> sqldiff You just blew my mind. Thank you! I can't believe the things I find out from random HN comments.