How is the XnView.db 'Tags' table 'ID' value determined?

[danjcla]

In the XnView.db SQLite database, how is the 'Tags' table 'ID' value determined?



The others columns seem obvious, but I haven't been able to see a pattern with 'ID'. Or find it referenced anywhere else in the schema. Interested because I'm playing around with adding triggers to the DB to insert computed labels and TagsTree pairings to those labels for an image after a new TagsTree Label:Image mapping is inserted for that image. I'm hoping with this info it'll be possible to create a new Tag (aka Label aka Category) without XnView MP running.

[CameronD]

It seems just like an auto-increment key, except the ordering is obviously not the sequence in which I created them.
So maybe a full "optimize" renumbers them.

[danjcla]

It seems just like an auto-increment key, except the ordering is obviously not the sequence in which I created them.
So maybe a full "optimize" renumbers them.

Yeah it increments up but is the same on a ton of files. For my photos I get "1" for around 1747 files, then "2" for 2 files, and then "3" for a lot more, totally seemingly random up until 7. There are also a bunch of rows with no (SQL NULL) ID.

I'm kinda reticent to do stuff purely DB side without knowing what exactly Tags.ID is/does. But I guess I can just make the tags within the GUI, and then just do assignation with DB Triggers.

I found a nifty utility that can show exactly what a app does in a SQLite DB, Instant SQLite Audit Trail. It crashes XnView MP after a while, but can get the queries it uses for stuff before that happens.

~~

SELECT Images.ImageID, Tags.TagID, Tags.Label, Tags.ParentID, Tags.ID
FROM Tags
JOIN TagsTree on TagsTree.TagID = Tags.TagID
JOIN Images on Images.ImageID = TagsTree.ImageID
JOIN Folders on Folders.FolderID = Images.FolderID
ORDER BY Tags.ID, Tags.ParentID, Tags.TagID, Images.ImageID

[m.Th.]

I don't know what it is. Only Pierre can clear this up.

However, it seems that plays some 'History' role. Either the last assigned tags either the last created/imported tags.

[CameronD]

Yeah it increments up but is the same on a ton of files. For my photos I get "1" for around 1747 files, then "2" for 2 files, and then "3" for a lot more, totally seemingly random up until 7. There are also a bunch of rows with no (SQL NULL) ID....

Sorry, I can't read! I was looking at TagID, not Tags.ID

You're right - ID looks odd, and I cannot see any obvious pattern to it

On my system, most tags have ID = -1, 10 - 20 are zero, and zero to two entries for small positive integers. I have no NULLs. This is looking at several versions of the db.

Code: Select all

 select ID, count(TagID) from Tags group by ID

Your count in relation to files will simply be numbers of files that have certain tags selected.

I cannot see any obvious link to history, unless it only gets triggered when accessing it only in certain ways.

[danjcla]

Yay! Pierre replied to a PM I sent with:

"Tags.ID is not used currently."

So, that's easy