Monday, June 10, 2013

More about Lightroom and sqlite

If you follow the instructions in my previous post you'll note that I was actually just renaming files, not moving them. For example, I was suggesting to rename "2012/January 03" to "2012/01_03", but you can also move the entire folder to a nested subfolder, replacing the space with '/', so replace "2012/January 03" with "2012/January/03".
However this requires an additional step:

  • the folder "January" in LR database needs to be an entity of its own: if you blindly replace "January 03" with "January/03", LR will see folder "03" correctly as part of the catalog, but not its parent "January"; so, for example, you'll see "January (500 pictures)" in LR folder tree, but clicking on "January" you won't see anything, even with "display pictures from subfolders" enabled.
  • the simplest fix for this is: copy a single jpeg file in all the folders 2013/January, 2013/February, ... 2012/January, 2012/February... 
for n in $(find . -type d -d 2 | grep "\./20"); do cp ~/Temp/test.jpg $n/; done
  • then run "Synchronize folder" from LR, deselect "don't import duplicates", import all the copies of your jpeg and trash them immediately after

Wednesday, January 23, 2013

Lightroom folder batch-rename with sqlite

Here's a quick tutorial for doing batch-renames of folders in Lighroom 4.
Warning #1: the tutorial is written for mac, but it would possibly work with minor modifications on Windows if you have the sqlite3.exe binary available, however I cannot test it.
Warning #2: don't trust blindly what I write here, the idea is absolutely correct, but I may make mistakes while I type, or omit some steps, so backup everything before you start and don't do it unless you know what you are doing.

Let' suppose my folders are of the form "2013/January 01" and I want to change them to "2013/01_01". From LR interface, you could right-click on the folder and select "Rename", but if you have thousands of them, it would take forever.

Close LR. Open a text editor, instead, and create a plaintext file (say "print.txt"), enter 12 lines like the following:

SELECT 'mv ' || QUOTE(pathFromRoot) || ' ' || QUOTE(REPLACE(pathFromRoot, 'January ', '01_')) from AgLibraryFolder WHERE pathFromRoot LIKE '%January %';

SELECT 'mv ' || QUOTE(pathFromRoot) || ' ' || QUOTE(REPLACE(pathFromRoot, 'February ', '02_')) from AgLibraryFolder WHERE pathFromRoot LIKE '%February %';


Windows users should probably type 'move ' instead of 'mv ' (don't forget the space).
Now create a different plaintext file (say "update.txt"), with 12 lines like this:

UPDATE "AgLibraryFolder" SET pathFromRoot = REPLACE(pathFromRoot, 'January ', '01_') WHERE pathFromRoot LIKE '%January%';


Open a terminal prompt and cd to the folder where the catalog lives. Let's assume that the catalog file name is LR4.lrcat.
Enter the following commands:

cat print.txt | sqlite3 LR4.lrcat >
# check that everything is ok, then:
cat update.txt | sqlite3 LR4.lrcat

Windows users should write something like this:

type print.txt | sqlite3 LR4.lrcat > batch_rename.bat
# check that everything is ok, then:
type update.txt | sqlite3 LR4.lrcat
del batch_rename.bat