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