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

[Francesco R.]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: digikamtest2.mwb
Type: application/zip
Size: 27799 bytes
Desc: not available
URL: <http://mail.kde.org/pipermail/digikam-devel/attachments/20120127/c9adb2db/attachment.zip>