4 ms·
Opening this and then searching for "\.db$" reveals all of the processes that are (probably) using SQLite, which is fun for finding things you can poke around i
by simonw 4y ago
Opening this and then searching for "\.db$" reveals all of the processes that are (probably) using SQLite, which is fun for finding things you can poke around in.
Try "\.sqlite" too.
This is fun:
brew install datasette
datasette ~/Library/Calendars/Calendar.sqlitedb
- graderjs 4y agoI think because Core data is SQL lite?
- simonw 4y agoYup - as a result, there's usually a TON of interesting SQLite files on any Mac (and iPhone too if you can get at them).
- dunham 4y agoSometimes you can pull the phone ones out of backups. You used to be able to mount an application's full directory tree, but Apple killed that API years ago.
- JKCalhoun 4y agoI believe you can choose the kind of store you want for Core Data. (SQL is probably the most popular though.)
- tl 4y agoYou can choose between: 1. XML-based (the entire database is one xml file) 2. SQLite-based 3. Roll your own (by providing an implemention) #1 is terribly slow for most cases and not publicly available on iOS. #3 was released early in the ioS lifecycle (iOS 3 or iOS 5, depending which piece we're talking about) SQL is, for most purposes, the only implementation, except for iTunes which used #1 for a decade after #2 clearly won.
- astrange 4y agoiTunes uses a proprietary database format IIRC, but keeps a copy of it in XML on the side so other people can read it if they want.
- CSDude 4y agoExample for Photos.app https://medium.com/@mustafaakin/how-to-organize-your-messy-albums-on-photos-app-on-macos-with-some-sql-and-image-processing-9e6a2566f6f0 https://medium.com/@mustafaakin/how-to-organize-your-messy-a...
- nerdponx 4y agoYou can also use this for things like exporting your notes and imessage history to plain text.
- mittermayr 4y agoDidn't know that, had to look into it immediately! Looks like a lot of the stuff in the Notes sqlite file is encrypted, though. Will need to look into this some more, would be nice to make a nice little exporter.
- dunham 4y agoLast I checked it was not encrypted (unless you explicitly asked it to encrypt a note). Just gzipped protobuf data. I wrote an extractor about six years ago, maybe it'll help: https://github.com/dunhamsteve/notesutils https://github.com/dunhamsteve/notesutils It seems to still work for text. It looks like it doesn't dump tables anymore, so that bit may have changed (it was pretty convoluted). I think the extraction of drawing information doesn't work anymore, but they provide a fallback PNG file. I wanted it to be self-contained, so it has a little hand-rolled protobuf decoder and might be a little code-golfy.
- mittermayr 4y agoThat's fantastic, thanks for sharing this! I'll give it a spin on the weekend and see what I can get out of it.
- Dachande663 4y agoProbably find *.etilqs files as well (believe Firefox uses that). It’s sqlite backwards to avoid rampant google hordes descending on the developer.
- saltypal 4y agoOh wow, thank you! I'd been poking through them with plain old `sqlite3` and then deserializing all the plist data with something like `pbpaste | xxd -r -p > foo.plist` for examination, but had no idea datasette existed and https://datasette.io/plugins/datasette-bplist#user-content-trying-it-out https://datasette.io/plugins/datasette-bplist#user-content-t... seems like the ticket for browsing these. For others: all the Caches.db files are the per-process HTTP cache that NSURLRequest/NSURLSession keeps, so if you peek at it you can see (partially) a history of network requests that process has made. Most of them seem to pull feature flag configuration from https://bag.itunes.apple.com/bag.xml https://bag.itunes.apple.com/bag.xml, but others do more interesting things.