[Digikam-devel] is creating a tag as simple as adding new entries to the Tags table?

[Ram Bhamidipaty]

Hi,

Thank you!! This is exactly what I was looking for.

-Ram


On Fri, Jan 27, 2012 at 5:42 AM, Francesco R. <francesco at pnpitalia.it> wrote:
> It's sligtly different from sqlite and mysql, assuming sqlite something like
> this could work:
>
> Run digikam at least once, it will create the tables and populate them with
> some tags.
> Also create a tag on your own, then close digikam, open the db, find your tag.
>
> Use the pid of your tag as a starting point for all your tag tree, the
> triggers should take care of the  "TagsTree" table, you need only to insert in
> the "Tags" one.
>
> you can then relate the tag with the image using the "ImageTags" table.
>
> in case you have a mysql-workbench available I've also attached a mysql-
> workbench file (intended for mysql doh) that should be near to the actual
> sqlite database schema.
>
>
> the SQL:
> INSERT INTO Tags (pid, name) VALUES( :tagPID, :tagname);
>
> INSERT INTO Tags (id, pid, name, icon, iconkde)
> VALUES (:id, :pid, :name, :icon, :iconkde);
>
> DELETE FROM Tags WHERE id=:tagID;
>
>
> the tables:
>
> CREATE TABLE IF NOT EXISTS Tags
>                            (id INTEGER PRIMARY KEY,
>                            pid INTEGER,
>                            name TEXT NOT NULL,
>                            icon INTEGER,
>                            iconkde TEXT,
>                            UNIQUE (name, pid))
>
> CREATE TABLE IF NOT EXISTS TagsTree
>                            (id INTEGER NOT NULL,
>                            pid INTEGER NOT NULL,
>                            UNIQUE (id, pid))
>
> CREATE TABLE IF NOT EXISTS ImageTags
>                            (imageid INTEGER NOT NULL,
>                            tagid INTEGER NOT NULL,
>                            UNIQUE (imageid, tagid))
>
> the triggers:
> CREATE TRIGGER delete_tag DELETE ON Tags
>                    BEGIN
>                        DELETE FROM ImageTags WHERE tagid=OLD.id;
>                        DELETE FROM TagProperties WHERE tagid=OLD.id;
>                        DELETE FROM ImageTagProperties WHERE tagid=OLD.id;
>                    END;
>
> CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags
>                BEGIN
>                INSERT INTO TagsTree
>                    SELECT NEW.id, NEW.pid
>                    UNION
>                    SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid;
>                END;
>
> CREATE TRIGGER delete_tag DELETE ON Tags
>                            BEGIN
>                            DELETE FROM ImageTags WHERE tagid=OLD.id;
>                            DELETE FROM TagProperties WHERE tagid=OLD.id;
>                            DELETE FROM ImageTagProperties WHERE tagid=OLD.id;
>                            END;
>
> CREATE TRIGGER delete_tagstree DELETE ON Tags
>                BEGIN
>                DELETE FROM Tags
>                WHERE id  IN (SELECT id FROM TagsTree WHERE pid=OLD.id);
>                DELETE FROM TagsTree
>                WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);
>                DELETE FROM TagsTree
>                    WHERE id=OLD.id;
>                END;
>
> CREATE TRIGGER move_tagstree UPDATE OF pid ON Tags
>                BEGIN
>                DELETE FROM TagsTree
>                    WHERE
>                    ((id = OLD.id)
>                    OR
>                    id IN (SELECT id FROM TagsTree WHERE pid=OLD.id))
>                    AND
>                    pid IN (SELECT pid FROM TagsTree WHERE id=OLD.id);
>                INSERT INTO TagsTree
>                    SELECT NEW.id, NEW.pid
>                    UNION
>                    SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid
>                    UNION
>                    SELECT id, NEW.pid FROM TagsTree WHERE pid=NEW.id
>                    UNION
>                    SELECT A.id, B.pid FROM TagsTree A, TagsTree B
>                    WHERE
>                    A.pid = NEW.id AND B.id = NEW.pid;
>                END;
>
> On Friday 27 January 2012 07:24:47 Ram Bhamidipaty wrote:
>> I did find the file core/project/documents/DBSCHEMA.ODS
>>
>> That spread sheet does mention the Tags table as well as the meaning of
>> the columns.  But I don't see anything about the TagTree and TagProperties
>> tables. Did I miss it?
>>
>> If I add an entry into Tags is that enough to create a tag? In my tests I
>> see many entries in TagTree and TagProperties -- can I just ignore those
>> tables?
>>
>> Thanks for any help.
>>
>> -Ram
>>
>> On Thu, Jan 26, 2012 at 9:51 PM, Gilles Caulier
>>
>> <caulier.gilles at gmail.com> wrote:
>> > Do you take a look into digiKam database documentation from git/master
>> > reprository. Look into "project" sub dir...
>> >
>> > Gilles Caulier
>> >
>> > 2012/1/27 Ram Bhamidipaty <rambham at gmail.com>:
>> >> Hi,
>> >>
>> >> I am in the process of importing a large number of pictures and their
>> >> associated tags
>> >> into digikam.  My pictures do not have the tag information in the
>> >> image file - but it is
>> >> in another database.
>> >>
>> >> I would like to write a script that can create the appropriate tags in
>> >> digikam. The
>> >> structure of the Tags table seems simple, but I am not so sure about the
>> >> TagTree and TagProperties tables. Do I need to create entries there as
>> >> well?
>> >>
>> >> Or -- is there a digikam supported API that I can use to create tags?
>> >>
>> >> Thanks for any info.
>> >> -Ram