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

[Jens Benecke-2]

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

[AndriusWild]

Jens,

That script of yours is simply amazing!
Thanks a ton for sharing this!

On Monday, March 20, 2017 3:51:17 PM MDT Jens Benecke 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

[daniel bip]

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 <[hidden email]> 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

[Dmitri Popov-2]

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
<[hidden email]> 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 <[hidden email]>
> 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
>>
>

[Jens Benecke-2]

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 - [hidden email]
Keine Lust auf Müll in ihrem Postfach? www.spamfreemail.de

> Am 21.03.2017 um 11:00 schrieb Dmitri Popov <[hidden email]>:
>
> 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
> <[hidden email]> 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 <[hidden email]>
>> 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
>>>
>>

[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 <[hidden email]>:

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 - [hidden email]
Keine Lust auf Müll in ihrem Postfach? www.spamfreemail.de

> Am 21.03.2017 um 11:00 schrieb Dmitri Popov <[hidden email]>:
>
> 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
> <[hidden email]> 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 <[hidden email]>
>> 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
>>>
>>