I use Lightroom for managing my photos and a custom piece of software to run my website. In order to have my collections published, I need to have a file with the names of the items in the collection.

Up to a few weeks ago I used a paid plugin from the past, that was able to create a full gallery using Adobe Flash. I quickly dropped the use of Flash in my galleries, but I have used the plugin until recently because it produced an XML file with the list of items. Unfortunately, the plugin doesn't work any longer with Lightroom 6.

I'm not aware of any other plugin doing the same (XML is not a requirement: any text format is ok; I don't ever need the path of the items, just their name + copy name).

To be clear: I just need a text file with the list of items - for what concerns photos to be published, I use the standard features of Lightroom.

PS I need that the items are exported in the same order as they appear in the collection.

  • What do you mean by "copy name"? And what is your OS? – Saaru Lindestøkke Jun 27 '15 at 17:28
  • "Copy name" is something that Lightroom uses to distinguish "virtual copies" from the original file. Typically, I have a file named 20150627-0244 and, if I have a virtual copy, it's named 'a' or 'b'. The resulting full name should be 20150627-0244a. The OS is Mac OS X. – Fabrizio Giudici Jun 27 '15 at 20:28
up vote 4 down vote accepted

You might be able to exploit the fact that a LR catalog is actually a SQLite database. I've written ruby scripts in the past to generate statistics on my photos. The sort of data you're after probably isn't much more difficult to produce.

Mac OS ships with the sqlite command line tools. If command line interfaces aren't your thing, there are several different SQLite GUIs in the Mac App Store.

See this for a (older, but still valid) quick intro to pulling the data.

Edit

I cracked open my LR CC catalog and mocked up a quick test of my theory. I ended up with the following query:

SELECT lf.idx_filename
FROM AgLibraryCollection AS c,
  AgLibraryCollectionImage AS ci,
  Adobe_images AS i,
  AgLibraryFile AS lf

WHERE c.id_local = ci.collection
  AND i.id_local = ci.image
  AND lf.id_local = i.rootFile
  AND c.name = 'Collection Name'

The easiest way to execute this is to install SQLite Free - Datum from the Mac App store, open your Lightroom catalog (make sure LR is closed) and stick the query in the SQL tab. A more complex (but easier to repeat) method would be to stick the query in a text file, and run the following in Terminal:

sqlite3 /path/to/lightroom_catalog.lrcat < query.txt
  • Well, if it turns up there's no plugin, this is a valid suggestion. I can write scripts, but I feel more at ease with Java, and I know how to connect to SQLite with Java. I'll start playing with the query you suggested. Do you know whether there's a good tutorial about the schema used by Lightroom? Thanks. – Fabrizio Giudici Jun 29 '15 at 21:39

I don't know of a plugin that does this, but on a Mac I would do it as follows (maybe a Windows machine does the same, don't know):

  1. Open TextEdit.app in addition to Lightroom
  2. Select all photos in the collection
  3. Drag them to the TextEdit.app window, now you have the pathname of the dragged photos in a text file.
  4. Do some search + replace to get rid of the path and only keep the filename

A regex to match the path but not the filename would be ^/.*/.

  • Thanks for the tip. It would be nice as a temporary workaround... Unfortunately, I tried it and found that the order is not preserved. It is almost ok, but the 2nd image always appears at the end of the list. I suppose it's a bug. – Fabrizio Giudici Jun 27 '15 at 19:21
  • @FabrizioGiudici OK, too bad. You should probably add the order requirement to your question. – Saaru Lindestøkke Jun 27 '15 at 19:46
  • Anyway, Bart, I upvoted your solution. In most cases entries are in temporal order, and my naming schema matches date/time. So I just added a 'sort' in the processing pipeline of the file. It is a decent workaround for avoiding being blocked, until someone suggests another approach, or points out a plugin. Thanks. – Fabrizio Giudici Jun 27 '15 at 20:07
  • @FabrizioGiudici Thank you. LR also had a "Web" module with which I'm not familiar at all, but you could see if that has a more suitable solution. Good luck! – Saaru Lindestøkke Jun 27 '15 at 20:15

I'm posting the solution I developed starting from the hint by speshak. In the end I did it with a bash script. It exports all the web galleries in my catalog and prepares some files according to my CMS. There's some stuff that is specific for my needs, but I think the script could be useful to others anyway.

There's still a problem with sorting. I was unable to sort the images as they are in the collection - probably because there's no info in the database as an explicit index, and Lightroom perhaps sorts results in function of the properties of the collection. I still used sorting by file name, which works for all my collections, but one.

#!/bin/bash

#
# Exports web galleries from a Lightroom 6.x database and prepares files with gallery contents.
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
# an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations under the License.
#
# $Id: ExportGalleries.sh,v ce1c71b3125a 2015/07/04 19:38:11 fabrizio $
#

TARGET_PATH="$HOME/Personal/WebSites/StoppingDown.net/structure"
CATALOG="$HOME/Personal/Photography/Lightroom/Fabrizio Lightroom Catalog/Fabrizio Lightroom Catalog.lrcat"



#
# Input         $1      a string
# Return                the string, trimmed
#
function trim
        {
                echo "$1" | sed 's/^ *//' | sed 's/ *$//'
        }

#
# Input         $1      a SQL query
# Return                the result of the query separated by spaces
#
function query
        {
                trim "`sqlite3 "$CATALOG" "$1" | tr '\n' ' '`"
        }

#
# Input         $1      the id of a collection
# Return                the name of the collection
#
function getCollectionName
        {
                query "SELECT name FROM AgLibraryCollection WHERE AgLibraryCollection.id_local = $1;"
        }

#
# Input         $1      the id of a collection
# Return                the id of the parent collection
#
function getCollectionParent
        {
                if [ "$1" != "" ] ; then
                        query "SELECT parent FROM AgLibraryCollection WHERE AgLibraryCollection.id_local = $1;"
                        fi
        }

#
# Input         $1      the id of a collection
# Return                the file name + copy name of the images contained in the collection
#
# FIXME: problems with sorting: ci.positionInCollection is null for many items; i.captureTime doesn't work as expected.
#
function getImageNamesInCollection
        {
                echo `query "SELECT lf.baseName,i.copyName FROM AgLibraryCollection AS c,
                                                                AgLibraryCollectionImage AS ci,
                                                                Adobe_images AS i,
                                                                AgLibraryFile AS lf
                                                           WHERE c.id_local = ci.collection
                                                                AND i.id_local = ci.image
                                                                AND lf.id_local = i.rootFile
                                                                AND c.id_local = $1
                                                           ORDER BY lf.baseName;"` | sed 's/|//g'
##                                                           ORDER BY i.captureTime;"` | sed 's/|//g'
##                                                           ORDER BY ci.positionInCollection;"` | sed 's/|//g'
        }

#
# Return                the ids of the collections that are a web gallery, separated by spaces
#
function getGalleryIds
        {
                query "SELECT DISTINCT id_local FROM AgLibraryCollection WHERE creationId='com.adobe.ag.webGallery';"
        }

#
# Input         $1      the id of a collection
#
function exportCollection
        {
                local PARENT=$(getCollectionParent $1)
                local GRAND_PARENT=$(getCollectionParent $PARENT)
                local GRAND_GRAND_PARENT=$(getCollectionParent $GRAND_PARENT)
                local NAME="$(getCollectionName $1)"
                local GALLERY_PATH=""
                local EXPOSED_URI=""

                # Diary
                if [ "$GRAND_GRAND_PARENT" == "2918721" ] ; then
                        local YEAR=$(getCollectionName $GRAND_PARENT)
                        local MONTH=`echo "$(getCollectionName $PARENT)" | sed -e 's/\..*$//g'`
                        local DAY=`echo "$(getCollectionName $1)" | sed -e 's/\..*$//g'`
                        local PATH_NAME="$YEAR/$MONTH/$DAY"
                        GALLERY_PATH="$TARGET_PATH/Diary/$PATH_NAME"

                # Lenses
                elif [ "$GRAND_PARENT" == "9495573" ] ; then
                        local LENS_NAME=$(getCollectionName $PARENT)
                        GALLERY_PATH=`echo "$TARGET_PATH/Themes/Lens/$LENS_NAME/$NAME" | sed 's/ /+/g'`

                # Visual Colour Check
                elif [ "$NAME" == "Visual Color Check" ]; then
                        GALLERY_PATH="$TARGET_PATH/Themes/VisualColourCheck"

                # Splash Slideshow
                elif [ "$NAME" == "Splash Slideshow" ]; then
                        GALLERY_PATH="$TARGET_PATH"
                        fi

                if [ "$GALLERY_PATH" == "" ] ; then
                        echo "Ignoring $GRAND_GRAND_PARENT/$GRAND_PARENT/$PARENT ($NAME)..."
                else
                        local IMAGES="$GALLERY_PATH/images.xml"
                        echo "Exporting $IMAGES ($NAME / $1)..."

                        if [ ! -d "$GALLERY_PATH" ]; then
                                echo "   Creating new gallery..."
                                mkdir -p $GALLERY_PATH
                                local DATE=`date -u "+%Y-%m-%dT%H:%M:%S.000+00:00"`
                                cat << EOF > "$GALLERY_PATH/Properties.xml"
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<properties version="1.0">
    <property name="creationDateTime">
        <value>$DATE</value>
    </property>
    <property name="latestModificationDateTime">
        <value>$DATE</value>
    </property>
    <property name="exposedUri">
        <value>$EXPOSED_URI</value>
    </property>
</properties>
EOF
                                fi

                        echo '<?xml version="1.0" encoding="UTF-8"?>'                  > $IMAGES
                        echo '<gallery>'                                              >> $IMAGES
                        echo '    <album>'                                            >> $IMAGES

                        for i in $(getImageNamesInCollection $1); do
                                echo "        <img src=\"$i.jpg\"/>"                  >> $IMAGES
                                done

                        echo '    </album>'                                           >> $IMAGES
                        echo '</gallery>'                                             >> $IMAGES
                        fi
        }

function exportGalleries
        {
                for i in $(getGalleryIds); do
                        exportCollection $i
                        done
        }

exportGalleries
  • Just a reminder: SQLite, the database used by Lightroom, is single-user, that is it can't be used by two applications at the same time, otherwise it might get corrupted. Remember not to run the script when Lightroom is running. – Fabrizio Giudici Jul 15 '15 at 20:56
  • 1
    I would add following snippet to the top of this script. This way it's impossible to run the script while Lightroom is open. if pgrep "Lightroom" > /dev/null then exit; fi – Wim Mostmans Aug 1 '16 at 9:55
  • Thanks. I'm posting below another solution that I introduced a few months ago. – Fabrizio Giudici Aug 2 '16 at 10:56

This other approach to avoid corruption of the database checks the existence of a lock file.

LOCK_FILE="$CATALOG.lock"

if [ -f "$LOCK_FILE" ]; then
    >&2 echo "Please quit Lightroom before running this script."
    exit 1
    fi

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.