database - tagtree - help please :-)

[Daniel Bauer-2]

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?

thanks a lot!

Daniel
--
Daniel Bauer photographer Basel Switzerland
professional photography: http://www.daniel-bauer.com
Madagascar special: http://www.sanic.ch
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[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
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Arnd Baecker]

Alright, now an explicit example:

Let's try to get to find the parent of the `Person4`:
  export tag=Person4
  sqlite3 digikam3.db "SELECT id,pid FROM Tags WHERE name=\""$tag"\"  "
  Output: 4|16

Therefore the call (all in one line!) should give the parent,
more precisely: the id of the parent, its parent id, and name:
  sqlite3 digikam3.db " SELECT id,pid,name FROM Tags WHERE id=(SELECT pid
FROM Tags WHERE name=\""$tag"\")"
  Output: 16|0|Personen

Note that pid=0 if there is no further parent.

Hope this helps,

Arnd
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Daniel Bauer-2]

On Freitag, 9. März 2007, Arnd Baecker wrote:
> Hi Daniel,
>
>(...)
>
> Using `pid` (standing for `parent id`, I'd guess),
> one can get the id of the parent.
>
> Does this already help enough?

Yes, thanks a lot!

After looking thru these databases yesterday my head was so much filled up
with numbers that there was no more room to imagine what "pid" could stand
for... :-)

Have a nice day.

Daniel
--
Daniel Bauer photographer Basel Switzerland
professional photography: http://www.daniel-bauer.com
Madagascar special: http://www.sanic.ch
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users