Ok. I managed to sort my way out.
Following some reverse engineering and trial and error I managed to use Python (which I had absolutely no knowledge of before I started) and data mine the LightRoom SQLite database to recreate the original folder structure of my pictures archive and rename and relocate all the recovered files. This didn't involve any changes in the database. After that was done, all I had to do was to reassign the missing top folder of the repository to the new structure and like magic everything was back in place.
It's very specific but for what is worth here is the code I developed. I use comments abundantly so it should be fairly straight forward to follow and understand it.
#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3 as lite
import sys
import re
import os
import shutil
con = None
namesOfRepeatedTimestamps = set()
try:
con = lite.connect('lr.db')
with con:
cur = con.cursor()
# Read all files in LighRoom Library
cur.execute("SELECT id_local, folder, originalFilename, baseName, lc_idx_filenameExtension FROM AgLibraryFile")
files = cur.fetchall()
for file in files: # Loop through all files
# gather data into more readable variables
currentFile_id_local = file[0]
currentFile_folder = file[1]
currentFile_originalFilename = file[2]
currentFile_baseName = file[3] # originalFilename without extension
currentFile_lc_idx_filenameExtension = file[4]
# Process file if it is not the result of the low level recovery (base names of recovered files all have 8 digits)
if not re.match("^[0-9]{8}$", currentFile_baseName) and (currentFile_lc_idx_filenameExtension in ["tif"]):
# Get capture time of image being processed
cur.execute("SELECT captureTime, fileFormat FROM Adobe_images WHERE rootFile = (?)", (currentFile_id_local,))
image = cur.fetchone()
currentImage_captureTime = image[0]
currentImage_fileFormat = image[1]
# Get id of file with the same capture time but not the same AgLibraryFile id (stored in rootFile field).
# This can result in several images beeing return because some images were processed into TIFF format (via plugins) which in turn inherited the same time stamp.
cur.execute("SELECT DISTINCT rootFile FROM Adobe_images WHERE captureTime = (?) and rootFile <> (?) and fileFormat = (?)", (currentImage_captureTime, currentFile_id_local, currentImage_fileFormat))
imagesWithSameTime = cur.fetchall()
nrOfAltImages = len(imagesWithSameTime) # Claculate number of images with same time stamp
for imageWithSameTime in imagesWithSameTime:
currentImage_rootFile = imageWithSameTime[0]
# Get the old name of the file
cur.execute("SELECT lc_idx_filename, baseName, folder FROM AgLibraryFile WHERE id_local = (?)", (currentImage_rootFile,))
recoveredFile = cur.fetchone()
recoveredFilename = recoveredFile[0]
recoveredBaseName = recoveredFile[1]
recoveredFolder = recoveredFile[2]
if nrOfAltImages == 1: # Only one image was found, so use it
if re.match("^[0-9]{8}$", recoveredBaseName):
# Lightroom stores stores the file path in two separate tables:
# AgLibraryFolder, which stores the lower part of the path to the folder and
# AgLibraryRootFolder, which stores the root of the path to the folder
# Get the lower path of the folder of the original filename
cur.execute("SELECT pathFromRoot, rootFolder FROM AgLibraryFolder WHERE id_local = (?)", (currentFile_folder,))
Folder = cur.fetchone()
originalPathFromRoot = Folder[0]
originalRootFolderID = Folder[1]
# Get the upper part of the folder (the root) of the original filename
cur.execute("SELECT absolutePath FROM AgLibraryRootFolder WHERE id_local = (?)", (originalRootFolderID,))
Root = cur.fetchone()
originalAbsolutePath = Root[0]
# calculate original file name path
originalPath = u''.join((originalAbsolutePath.replace("//0001D2136933/fabricio/Backup", "/cygdrive/e", 1), originalPathFromRoot)).encode('utf-8').strip()
# calculate name of original file name with path
originalFullFilename = u''.join((originalPath, currentFile_originalFilename)).encode('utf-8').strip()
# Get the lower path of the folder of the recovered filename
cur.execute("SELECT pathFromRoot, rootFolder FROM AgLibraryFolder WHERE id_local = (?)", (recoveredFolder,))
Folder = cur.fetchone()
recoveredPathFromRoot = Folder[0]
recoveredRootFolder = Folder[1]
# Get the upper part of the folder (the root) of the recovered filename
cur.execute("SELECT absolutePath FROM AgLibraryRootFolder WHERE id_local = (?)", (recoveredRootFolder,))
Root = cur.fetchone()
recoveredAbsolutePath = Root[0]
# calculate recovered file name path
recoveredFilePath = u''.join((recoveredAbsolutePath.replace("E:", "/cygdrive/e", 1), recoveredPathFromRoot)).encode('utf-8').strip()
# calculate name of recovered file name with path
recoveredFullFilename = u''.join((recoveredFilePath, recoveredFilename)).encode('utf-8').strip()
# Check if original file path already exists in new structure
if not os.path.exists(originalPath):
# It may not exist because some original folders were custom named.
# Other early folders (2003-2011) were also named at the lowest level as "YYYY_MM_DD" instead of just "DD" as was created by the import of recovered files
os.makedirs(originalPath)
# check if file already exists. Files may have already been renamed by a prior pass of the script
if not os.path.isfile(originalFullFilename):
# File doesn't exist, rename the recovered file to its old name
shutil.move(recoveredFullFilename, originalFullFilename)
else:
# This means several files have the same time stamp which can result due to camera bursts where up to 8 images per second can be taken
# (since the camera doesn't record miliseconds they all get the same timestamp) or the file was recovered multiple times from different locations in the broken disk.
# These will probably need manual handling because there is no way to know exactly which repeat corresponds to the image being processedbut but
# for now we will not repeat file names using the set: namesOfRepeatedTimestamps
if re.match("^[0-9]{8}$", recoveredBaseName) and not recoveredBaseName in namesOfRepeatedTimestamps:
namesOfRepeatedTimestamps.add(recoveredBaseName)
# Lightroom stores stores the file path in two separate tables:
# AgLibraryFolder, which stores the lower part of the path to the folder and
# AgLibraryRootFolder, which stores the root of the path to the folder
# Get the lower path of the folder of the original filename
cur.execute("SELECT pathFromRoot, rootFolder FROM AgLibraryFolder WHERE id_local = (?)", (currentFile_folder,))
Folder = cur.fetchone()
originalPathFromRoot = Folder[0]
originalRootFolderID = Folder[1]
# Get the upper part of the folder (the root) of the original filename
cur.execute("SELECT absolutePath FROM AgLibraryRootFolder WHERE id_local = (?)", (originalRootFolderID,))
Root = cur.fetchone()
originalAbsolutePath = Root[0]
# calculate original file name path
originalPath = u''.join((originalAbsolutePath.replace("//0001D2136933/fabricio/Backup", "/cygdrive/e", 1), originalPathFromRoot)).encode('utf-8').strip()
# calculate name of original file name with path
originalFullFilename = u''.join((originalPath, currentFile_originalFilename)).encode('utf-8').strip()
# Get the lower path of the folder of the recovered filename
cur.execute("SELECT pathFromRoot, rootFolder FROM AgLibraryFolder WHERE id_local = (?)", (recoveredFolder,))
Folder = cur.fetchone()
recoveredPathFromRoot = Folder[0]
recoveredRootFolder = Folder[1]
# Get the upper part of the folder (the root) of the recovered filename
cur.execute("SELECT absolutePath FROM AgLibraryRootFolder WHERE id_local = (?)", (recoveredRootFolder,))
Root = cur.fetchone()
recoveredAbsolutePath = Root[0]
# calculate recovered file name path
recoveredFilePath = u''.join((recoveredAbsolutePath.replace("E:", "/cygdrive/e", 1), recoveredPathFromRoot)).encode('utf-8').strip()
# calculate name of recovered file name with path
recoveredFullFilename = u''.join((recoveredFilePath, recoveredFilename)).encode('utf-8').strip()
# Check if original file path already exists in new structure
if not os.path.exists(originalPath):
# During the import of recovered images into LightRoom all folders were renamed using a structure like \YYYY\MM\DD but the original structure
# included some custom named folders and earlier folders (2003-2011) were also imported with the lowest level as "YYYY_MM_DD" instead of just "DD"
os.makedirs(originalPath) # Creates lower and intermediate folders in one go
# check if file already exists. Files may have already been renamed by a prior pass of the script
if not os.path.isfile(originalFullFilename):
# File doesn't exist, rename the recovered file to its old name
shutil.move(recoveredFullFilename, originalFullFilename)
break
except lite.Error, e:
print "Error %s:" % e.args[0]
sys.exit(1)
finally:
if con:
con.close()
datetime, then re-import the whole mess into a clean new db... \$\endgroup\$