Querying QuMagie Photos via SQL

QNAP NASesses include QuMagie for searching through photos. It's fine, I guess; it does indeed allow searching for faces or things. Unfortunately, the search functionality is fairly basic: it doesn't allow searching for all photos containing a specific set of people taken within a particular time range.

Doing that requires querying the underlying MySQL DB that QuMagie uses for storing image metadata. This is mildly clunky but very possible. First, start up a MySQL 5.x server:

    sudo docker run --name qumagie -v $(pwd)/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 mysql:5

Then, copy the QuMagie database to that local DB:

    rsync -aP user@nas:/share/CE_CACHEDEV1_DATA/.system/data/s01/ mysql/qumagie/

The schema is fairly intuitive: Two main things to note:

1. To get an image's path on the NAS, join pictureTable and dirTable on iDirId and concatenate the cFullPath and cFileName columns.
2. To get the faces on an image, join pictureTable, faceTable, faceGroupClusterMapping, and faceGroupTable together. The face name is in the TagName column.

Putting it all together, here's a query to find all images containing all of the listed people:

select 
    CONCAT(
        '/share/', dirTable.cFullPath, pictureTable.cFileName) as 'image_path', count(*)
from pictureTable
join dirTable on dirTable.iDirId=pictureTable.iDirId
join faceTable on (pictureTable.iPictureId = faceTable.iPictureId)
join faceGroupClusterMapping on (faceTable.ClusterId = faceGroupClusterMapping.ClusterId)
join faceGroupTable on (faceGroupTable.GroupId = faceGroupClusterMapping.GroupId)
where faceGroupTable.TagName in ('A Person', 'Another Person')
group by 1
having count(*) = 2 -- Note the having clause to make sure everyone appears