macOS Monitor SQLite queries

[barenature]

This is a long shot, but I was wondering if there is a way to monitor the queries an application makes to its database? Take, for example, iPhoto 11. iPhoto uses a SQLite data store to store its data (or parts of it). Is there a way to see what queries it performs to its database?

Thanks.

 

[kainjow]

One way is to search its binary for SQL syntax. In Terminal:

strings /Applications/iPhoto.app/Contents/MacOS/iPhoto | grep -i "select "

will show you all of the select queries it is using (at least those that are hard coded into the code).

You can replace "select" with "delete", "update" or "insert" for other kinds of statements.

 

[jiminaus]

I would guess you'd need to high-jack which sqlite3 library iPhoto uses, replacing it with one which logs out the queries.

 

[barenature]

@kainjow: That's very interesting. It gave me a huge list of query strings. I will definitely take a look at this. Thanks!

@jiminaus: That sound promising. I know what databases iPhoto uses, but can you tell me how to make the database log its actions? Thanks.

 

[jiminaus]

@kainjow: That's very interesting. It gave me a huge list of query strings. I will definitely take a look at this. Thanks!

@jiminaus: That sound promising. I know what databases iPhoto uses, but can you tell me how to make the database log its actions? Thanks.

I was facetious somewhat. This isn't a simple solution. I would require changing the SQLite source code, recompiling it, and then change the iPhoto binary to point to the newly compiled SQLite library. In theory that would work, but I don't know if it would actually work in practise.

What do you actually want to learn? If it's the database structure, then just open the databases in sqlite3 and use the .schema command.

 

[barenature]

I'm writing an application that interfaces with iPhoto. This all works fine except for one issue, that is, certain updates I make to iPhoto's database are not visible in parts of the application.

My hypothesis is that iPhoto uses some sort of cache that doesn't get updated when I write to the database but does get updated when iPhoto itself writes to its database as it most likely refreshes/updates this cache. If I know what queries it performs for the action I want to do then I might be able to adjust my queries in such a way that the (hypothesized) cache does get updated.

No simple feat, but not all things are simple ;-).

 

[jiminaus]

Would you be able to see this by comparing the database(s) before and after the operation in iPhoto? If so, dump the database(s), perform the operation in iPhoto, dump the database(s) again, and diff.

 

[kainjow]

I'm writing an application that interfaces with iPhoto. This all works fine except for one issue, that is, certain updates I make to iPhoto's database are not visible in parts of the application.

My hypothesis is that iPhoto uses some sort of cache that doesn't get updated when I write to the database but does get updated when iPhoto itself writes to its database as it most likely refreshes/updates this cache. If I know what queries it performs for the action I want to do then I might be able to adjust my queries in such a way that the (hypothesized) cache does get updated.

Well it is not using the built-in SQLite library directly, it looks like it's using a static version built-in to its wrapper framework located at
/Library/Frameworks/iLifeSQLAccess.framework

So yes I'd be very surprised to hear that changes to its database outside of its app will be seen by it in real time.

Your best bet is to gain access into the process via some type of plugin and override certain methods (nasty though).

Can AppleScript not accomplish what you want to do?

 

[barenature]

Good advice. I did just that and might have found the location in the database where the changes are made. The only problem is that the data that is being written by iPhoto is a chunk of binary data (blob).

Again a long shot, but what can I do to know what is actually being written? In the application I use to explore the database, the data is displayed as


Ä








ÅÑÅÄÄà
âÄÄ@



Å

and the raw binary data look like

010801c3 84180801 01010101 01010101 c385c391 06c385c3 84c384c3 a001c3a2 c384c384 40010101 01c38501 01010101 01010101 01010101 010101

so I have no idea what is being store in the database.

Next time, I will think twice before building on top of an application that isn't my own ;-).

 

[kainjow]

Doesn't look like any recognizable format to me, but since there is no context to what that data is it's next to impossible to know.