Hacker News Reader

Comment by simonw | Hacker News Reader

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

View on HN

Oh 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-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, but others do more interesting things.

Example for Photos.app https://medium.com/@mustafaakin/how-to-organize-your-messy-a...

You can also use this for things like exporting your notes and imessage history to plain text.

Didn'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.

Last 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

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.

That's fantastic, thanks for sharing this! I'll give it a spin on the weekend and see what I can get out of it.

Probably find *.etilqs files as well (believe Firefox uses that). It’s sqlite backwards to avoid rampant google hordes descending on the developer.

I think because Core data is SQL lite?

Yup - as a result, there's usually a TON of interesting SQLite files on any Mac (and iPhone too if you can get at them).

Sometimes 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.

I believe you can choose the kind of store you want for Core Data. (SQL is probably the most popular though.)

You 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.

iTunes 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.

Reply on news.ycombinator.com