How do entries in TagTable relate back to those in PhotoTable (photos.db structure question)?

I’m currently looking at the shotwell database using sqlite3.

I’ve noticed that when I create a tag the TagTable is updated with the name of the tag and a “thumb000…” id in the photo_id_list field that somehow relates to the photo in the PhotoTable.

How can one go from a TagTable entry back to the photo in PhotoTable?

I want to be able to collect, for example, all the 25x25 pictures and give them a tag. As far as I can tell, there’s no way to do that in Shotwell itself.

1 Like

Yes the design here is a bit unfortunate. the TagTable is a 1:n mapping of tag to media ids; However, the ids are represented as a CSV list.

What’s even more unfortunate: if one of the values in the CSV list starts with “thumb”, it’s a photo and needs to be mapped from the id column of PhotoTable with sprintf("thumb%016" G_GINT64_FORMAT) ; if it starts with “video”, it is the key in the VideoTable.

Correct; there is an issue open to implement searching by dimensions which would allow that.

Thank you for your quick response. Am I to take it then that if I peel off the thumb and leading zeros I will then have a number that is the id of a record in the photo table?

er yes, that was what I was trying to say in a very convoluted way :slight_smile:

It doesn’t seem to work that way, however.

In ~/Pictures/2020/04/27 there’s a photo called “20200427_084229.jpg”. It’s of some wild-yeast bread that I made earlier this year. I have tagged it “Bread”. According to the sqlite3:

sqlite> select * from TagTable;

We take “thumb0000000000007016” and strip it back to 7016.

sqlite> select filename from PhotoTable where id = 7016;

Hmm … not the filename we were expecting.

sqlite> select * from PhotoTable where filename = ‘/home/bugmagnet/Pictures/2020/04/27/20200427_084229.jpg’;

So I’m still a bit confused about how things work in shotwell. It’s not as intuitive as I might have hoped.

Oh sorry, the Ids in ThumbTable are actually hexadecimal, not decimal.

Ah, silly me. Should have thought of that possibility. Thanks.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.