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;
1|Bread|thumb0000000000007016,|1605965778

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

sqlite> select filename from PhotoTable where id = 7016;
/home/bugmagnet/Pictures/2016/01/29/img_0431.jpg

Hmm … not the filename we were expecting.

sqlite> select * from PhotoTable where filename = ‘/home/bugmagnet/Pictures/2020/04/27/20200427_084229.jpg’;
28694|/home/bugmagnet/Pictures/2020/04/27/20200427_084229.jpg|4032|1960|2582140|1605712661|1587948149|8|8|1605793148|958||89dc923fdad4f93a2785bcb645089d99|cb8f3854dccb52cd7068696d6a4f3746||1605795237|0|0|0||||-1|1|SHOTWELL|-1|-1|-1|

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.