[Digikam-users] SQLITE INSERT

[Marcel Wiesweg]

> If someone could help me with the following SQLITE problem, I'll be able to
> take the last, crucial step.
>
> I need to insert the appropriate imageid and tag id into ImageTags. As I
> understand SQL that is as simple as:
>
> INSERT INTO "ImageTags" VALUES(443,150);
>
> But, I need to be able to match the imageid in ImageTags to specific values
> of dirid and name. I would like to make a statement along the lines of.
>
> INSERT INTO ImageTags VALUES(Images.id, 1)
> WHERE (Images.dirid = '2003/') AND (Images.name='dscn1234.jpg');

The dirid is a number, from the statement above I assume you want to filter by 
the relative album path (in the current schema misleadingly, the 
album "url").

A possible SELECT statement :
SELECT Images.id FROM Images JOIN Albums ON Images.dirid = Albums.id WHERE 
Albums.url='/Digikam Sample/JPEG' AND Images.name='0.jpg';

and INSERT takes a select statement as well:
INSERT INTO ImageTags SELECT Images.id, 1 FROM ...

For reference, I recommend
http://www.sqlite.org/lang.html

>
> I believe the logic is sound, but I can't figure out the SQL. Should I use
> some kind of INNER JOIN to make a temporary database with the necessary
> properties? If so, can I use the value from Images.id as a VALUES
> parameter?
>
> Thanks!