SQLITE INSERT

[gunksta]

HI,

I am slowly but surely figuring out how to reproduce my F-Spot database in
Digikam.

I know I accomplsh most of this with exiv2, but I have decided this "problem"
gives me an excuse to learn more about SQL. Thus, my solution *must* be a SQL
solution, for educational type reasons.

I have pulled my information out of F-Spot's database. Multiple table SQL
queries are easy. I can use regular expressions magic to reformat the f-spot
info into the type of info digikam needs. So far, so good.

Digikam has indexed all of my photos, and I reproduced my tag structure in
Digikam. Now all I need to do is to re-associate photos with tags. Therein
lies my problem.

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');

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!
--andydigik

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

[Duncan Hill-5]

Andy Choens wrote:

> 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');

Really and truly, I should only answer this question with a copy of the
digikam DB in front of me.  Unfortunately, it's on a dying drive, and
that drive has been off-lined until I can get the data archived off.

On the assumption that Images looks like:
Images.id int
Images.dirid string
Images.name string

and Image.id auto-increments when a dirid+name is inserted

and that ImageTags looks like:
ImageTags.id int
ImageTags.tagid int

and that neither of those values increments automatically, but instead
ImageTags serves as a joining table for Images and Tags, then your
443,150 insertion is correct without any additional parameters.  443
already uniquely identifies the image that is being tagged.
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

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

[gunksta]

Thanks! This has really helped me figure this all out.

--andy

On Monday 30 April 2007 01:36:20 pm Marcel Wiesweg wrote:
> > 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!
> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users
>

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