[Digikam-users] database - tagtree - help please :-)

[Arnd Baecker]

Hi Daniel,

On Fri, 9 Mar 2007, Daniel Bauer wrote:

> Hi there,
>
> I need to work with the tags and the tag tree in another database (MySql). I
> can find out which tag is assigned to which picture, but even if I stand
> upside-down, I just can't find out, to which parent a tag belongs.
>
> I see the ways from
>
> Images.id -> ImageTags.imageid, that gives me tagid, then
> ImageTags.tagid -> Tags.id, that gives me the name of the tag
>
> so I can assign the tags name to the image. But how do I find out the partents
> of that tag?
>
> Can you help me, please?

Well, I am by no means an expert on this sql stuff, but
I fiddled around with a few bits in the past, so let's see:

First let's get a readable ASCII dump of the database:

  sqlite3 digikam3.db
  .help
  .output digikam3.dump
  .dump

Looking at this one finds the place where the Tags Table is defined:

CREATE TABLE Tags
 (id INTEGER PRIMARY KEY,
  pid INTEGER,
  name TEXT NOT NULL,
  icon INTEGER,
  iconkde TEXT,
  UNIQUE (name, pid));
INSERT INTO "Tags" VALUES(1, 16, 'Person1', 0, NULL);
INSERT INTO "Tags" VALUES(2, 16, 'Person2', 0, NULL);
INSERT INTO "Tags" VALUES(3, 16, 'Person3', 0, NULL);
INSERT INTO "Tags" VALUES(4, 16, 'Person4', 0, NULL);
INSERT INTO "Tags" VALUES(5, 0, 'Dresden', 0, NULL);
[..]
INSERT INTO "Tags" VALUES(16, 0, 'Personen', 0, NULL);

So this part corresponds to a tree
- Personen
  - Person1
  - Person2
  - Person3
  - Person4
- Dresden

Using `pid` (standing for `parent id`, I'd guess),
one can get the id of the parent.

Does this already help enough?
(I am just playing around with the sqlite3 commands to
do that explicitly.)

Best, Arnd