digikam-select: Batch export from Digikam using the command line

[Anders Kamf]

Hi!

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).

A typical query would look like this:

SELECT distinct Images.name, Albums.RelativePath, AlbumRoots.id
 FROM ImageTags t1
   INNER JOIN Images ON (t1.imageid = Images.id)
   INNER JOIN Albums ON (Albums.id = Images.album)
   INNER JOIN AlbumRoots ON (AlbumRoots.id = Albums.albumRoot)
   INNER JOIN ImageInformation ON (ImageInformation.imageid = Images.id)

   INNER JOIN (select distinct imageid from ImageTags where tagid in
(258,40)) t3 ON t3.imageid = t1.imageid
   INNER JOIN (select distinct imageid from ImageTags where tagid in
(255,40)) t4 ON t4.imageid = t1.imageid

   WHERE (
     (t1.tagid in (25,39) AND AlbumRoots.id = 3 AND Albums.RelativePath
LIKE '/01_redigering/%') OR
     (t1.tagid in (25,39) AND AlbumRoots.id = 2) OR
     (AlbumRoots.id = 5  AND ImageInformation.Rating >=4)
   ) AND
     Images.Category = 1
     AND NOT EXISTS
     ( 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) )
  ORDER BY Images.name

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 :)

>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.

I don't know if that helps at all, but I thought I might share it since
that seems somewhat related.

BR
Anders

2017-03-22 15:31 GMT+01:00 Jens Benecke <jens-digikam at spamfreemail.de>:

> Hi,
>
> regarding your issue, I think I need Gilles’ help.
> I select the full path like
>
> SELECT DISTINCT r.specificPath AS root, a.relativePath AS path, i.name AS
> name
>     FROM Images i
>     LEFT JOIN ImageTags it ON it.imageid = i.id
>     LEFT JOIN ImageInformation ii ON ii.imageid = i.id
>     LEFT JOIN Tags t ON it.tagid = t.id
>     LEFT JOIN Albums a ON i.album = a.id
>     LEFT JOIN AlbumRoots r ON albumRoot = r.id
>     WHERE root != '' AND path != ‚'
>     AND — more specific filter options like tags, rating, etc.
>
> and then just stick „root“, „path“ and „name“ together.
> This works fine for me.
> But it seems in your library „root“ is empty, while it should contain
> „/home“ to work.
> See https://github.com/jensb/digikam-select/issues/1 ...
>
> So, @GIlles:
> What is the correct and reliable SELECT syntax to reconstruct a full image
> path using Digikam’s SQL db?
>
> Thanks for all help :)
>
>
> --
> Jens Benecke - jens at spamfreemail.de
> Keine Lust auf Müll in ihrem Postfach? www.spamfreemail.de
>
>
>
> > Am 21.03.2017 um 11:00 schrieb Dmitri Popov <lazylegs at gmail.com>:
> >
> > Neat script, but it doesn't seem to work for me. I filed an issue at
> GitHub.
> >
> > Thanks,
> > Dmitri
> >
> > On Tue, Mar 21, 2017 at 9:52 AM, daniel bip
> > <daniel.mail.public at gmail.com> wrote:
> >> Hi Jens,
> >>
> >> Your script seems so interesting, some days ago I was thinking of
> something
> >> similar, as I have a similar IT infrastructure for sharing DK's
> pictures at
> >> home, so such a script would be amazing. I'll try to test it during this
> >> week. Thank you very much for sharing!
> >>
> >> BR,
> >>
> >>
> >> d.
> >>
> >>
> >>
> >> On 20 March 2017 at 22:51, Jens Benecke <jens-digikam at spamfreemail.de>
> >> wrote:
> >>>
> >>> Hi everybody,
> >>>
> >>> I have finally finished scratching my very personal itch with Digikam
> and
> >>> that was to be able to automatically (!) (re)export a fixed set of
> images
> >>> from the Digikam database, without constantly having to perform the
> task
> >>> manually in Digikam.
> >>>
> >>> May I humbly present "digikam-select.rb", first release:
> >>>
> >>>    https://github.com/jensb/digikam-select
> >>>
> >>> The idea is to be able to write scripts that automatically select
> images
> >>> (and other files) from the digikam database based on rating, tags or
> album
> >>> name and put copies somewhere else (e.g. on a NAS). The images can
> >>> optionally be resized or recompressed using ImageMagick's "convert"
> utility.
> >>> It is also possible to create symlinks or hardlinks saving space, only
> >>> referencing matching images.
> >>>
> >>> I use this for the rest of my family: once a day digikam-select.rb
> selects
> >>> all images tagged "for-Family", recompresses them using ImageMagick and
> >>> recreates my album structure on our home NAS using these recompressed
> >>> images. These folders are available to all family members and also
> >>> automatically synced to our family Android tablet.
> >>> So any time we want to go visit friends and maybe show some pictures we
> >>> just grab the tablet and are sure only "suitable" images (but all good
> ones)
> >>> are on there!
> >>>
> >>> There are many possible use cases, this is just one of them. Have a
> look,
> >>> try it out, report bugs (on Github)!
> >>>
> >>> Thanks & enjoy,
> >>>
> >>> Jens
> >>>
> >>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20170323/66bde9d1/attachment.html>