<div dir="ltr">Hi!<div><br></div><div>I have recently finished a script that extracts a selection of full paths from the database. Not for export, but for piping pictures to a slideshow (using feh). </div><div><br></div><div>A typical query would look like this:</div><div><br></div><div><div>SELECT distinct Images.name, Albums.RelativePath, AlbumRoots.id</div><div> FROM ImageTags t1</div><div> INNER JOIN Images ON (t1.imageid = Images.id)</div><div> INNER JOIN Albums ON (Albums.id = Images.album)</div><div> INNER JOIN AlbumRoots ON (AlbumRoots.id = Albums.albumRoot)</div><div> INNER JOIN ImageInformation ON (ImageInformation.imageid = Images.id)</div><div><br></div><div> INNER JOIN (select distinct imageid from ImageTags where tagid in (258,40)) t3 ON t3.imageid = t1.imageid </div><div> INNER JOIN (select distinct imageid from ImageTags where tagid in (255,40)) t4 ON t4.imageid = t1.imageid</div><div> </div><div> WHERE (</div><div> (t1.tagid in (25,39) AND AlbumRoots.id = 3 AND Albums.RelativePath LIKE '/01_redigering/%') OR</div><div> (t1.tagid in (25,39) AND AlbumRoots.id = 2) OR</div><div> (AlbumRoots.id = 5 AND ImageInformation.Rating >=4)</div><div> ) AND</div><div> Images.Category = 1</div><div> AND NOT EXISTS </div><div> ( SELECT NULL FROM ImageTags t2 WHERE t2.imageid = t1.imageid AND (t2.tagid = 838 OR t2.tagid = 837 OR t2.tagid = 1143 OR t2.tagid = 47) )</div><div> ORDER BY Images.name</div></div><div><br></div><div>This query selects pictures that matches several different tags from different collections and that not matches a couple of other tags (tags I use to indicate that these pictures doesn't fit in a public slideshow :)</div><div><br></div><div>From the result of the query I replace AlbumRoot.id with the path to the album (hard coded as digikam DB doesn't seem to store that) and build the complete path with that, the RelativePath and the Images.Name.<br></div><div><br></div><div>I don't know if that helps at all, but I thought I might share it since that seems somewhat related.</div><div><br></div><div>BR</div><div>Anders</div></div><div class="gmail_extra"><br><div class="gmail_quote">2017-03-22 15:31 GMT+01:00 Jens Benecke <span dir="ltr"><<a href="mailto:jens-digikam@spamfreemail.de" target="_blank">jens-digikam@spamfreemail.de</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
regarding your issue, I think I need Gilles’ help.<br>
I select the full path like<br>
<br>
SELECT DISTINCT r.specificPath AS root, a.relativePath AS path, <a href="http://i.name" rel="noreferrer" target="_blank">i.name</a> AS name<br>
FROM Images i<br>
LEFT JOIN ImageTags it ON it.imageid = <a href="http://i.id" rel="noreferrer" target="_blank">i.id</a><br>
LEFT JOIN ImageInformation ii ON ii.imageid = <a href="http://i.id" rel="noreferrer" target="_blank">i.id</a><br>
LEFT JOIN Tags t ON it.tagid = <a href="http://t.id" rel="noreferrer" target="_blank">t.id</a><br>
LEFT JOIN Albums a ON i.album = <a href="http://a.id" rel="noreferrer" target="_blank">a.id</a><br>
LEFT JOIN AlbumRoots r ON albumRoot = <a href="http://r.id" rel="noreferrer" target="_blank">r.id</a><br>
WHERE root != '' AND path != ‚'<br>
AND — more specific filter options like tags, rating, etc.<br>
<br>
and then just stick „root“, „path“ and „name“ together.<br>
This works fine for me.<br>
But it seems in your library „root“ is empty, while it should contain „/home“ to work.<br>
See <a href="https://github.com/jensb/digikam-select/issues/1" rel="noreferrer" target="_blank">https://github.com/jensb/<wbr>digikam-select/issues/1</a> ...<br>
<br>
So, @GIlles:<br>
What is the correct and reliable SELECT syntax to reconstruct a full image path using Digikam’s SQL db?<br>
<br>
Thanks for all help :)<br>
<span class="HOEnZb"><font color="#888888"><br>
<br>
--<br>
Jens Benecke - <a href="mailto:jens@spamfreemail.de">jens@spamfreemail.de</a><br>
Keine Lust auf Müll in ihrem Postfach? <a href="http://www.spamfreemail.de" rel="noreferrer" target="_blank">www.spamfreemail.de</a><br>
</font></span><div class="HOEnZb"><div class="h5"><br>
<br>
<br>
> Am 21.03.2017 um 11:00 schrieb Dmitri Popov <<a href="mailto:lazylegs@gmail.com">lazylegs@gmail.com</a>>:<br>
><br>
> Neat script, but it doesn't seem to work for me. I filed an issue at GitHub.<br>
><br>
> Thanks,<br>
> Dmitri<br>
><br>
> On Tue, Mar 21, 2017 at 9:52 AM, daniel bip<br>
> <<a href="mailto:daniel.mail.public@gmail.com">daniel.mail.public@gmail.com</a>> wrote:<br>
>> Hi Jens,<br>
>><br>
>> Your script seems so interesting, some days ago I was thinking of something<br>
>> similar, as I have a similar IT infrastructure for sharing DK's pictures at<br>
>> home, so such a script would be amazing. I'll try to test it during this<br>
>> week. Thank you very much for sharing!<br>
>><br>
>> BR,<br>
>><br>
>><br>
>> d.<br>
>><br>
>><br>
>><br>
>> On 20 March 2017 at 22:51, Jens Benecke <<a href="mailto:jens-digikam@spamfreemail.de">jens-digikam@spamfreemail.de</a>><br>
>> wrote:<br>
>>><br>
>>> Hi everybody,<br>
>>><br>
>>> I have finally finished scratching my very personal itch with Digikam and<br>
>>> that was to be able to automatically (!) (re)export a fixed set of images<br>
>>> from the Digikam database, without constantly having to perform the task<br>
>>> manually in Digikam.<br>
>>><br>
>>> May I humbly present "digikam-select.rb", first release:<br>
>>><br>
>>> <a href="https://github.com/jensb/digikam-select" rel="noreferrer" target="_blank">https://github.com/jensb/<wbr>digikam-select</a><br>
>>><br>
>>> The idea is to be able to write scripts that automatically select images<br>
>>> (and other files) from the digikam database based on rating, tags or album<br>
>>> name and put copies somewhere else (e.g. on a NAS). The images can<br>
>>> optionally be resized or recompressed using ImageMagick's "convert" utility.<br>
>>> It is also possible to create symlinks or hardlinks saving space, only<br>
>>> referencing matching images.<br>
>>><br>
>>> I use this for the rest of my family: once a day digikam-select.rb selects<br>
>>> all images tagged "for-Family", recompresses them using ImageMagick and<br>
>>> recreates my album structure on our home NAS using these recompressed<br>
>>> images. These folders are available to all family members and also<br>
>>> automatically synced to our family Android tablet.<br>
>>> So any time we want to go visit friends and maybe show some pictures we<br>
>>> just grab the tablet and are sure only "suitable" images (but all good ones)<br>
>>> are on there!<br>
>>><br>
>>> There are many possible use cases, this is just one of them. Have a look,<br>
>>> try it out, report bugs (on Github)!<br>
>>><br>
>>> Thanks & enjoy,<br>
>>><br>
>>> Jens<br>
>>><br>
>><br>
<br>
</div></div></blockquote></div><br></div>