2

A few months back my disk had a file system corruption (this was the moment I realised my backup strategy sucked big way). I could recover most of my files but only using a low level recovery which meant all my media files were renamed with a sequential 8 digit number and the file extention (eg. 00030893.raf).

After the file recovery finished (and proper backups were done) I re-imported all files back into LightRoom but of course the old files, which now display an ! mark, no longer match the new names and I cannot automatically get the development data for my developed files. When I really need a file development data this is what I do:

  1. I find the new named file,
  2. note down it's time stamp,
  3. remove it from the library (needed for step 4),
  4. using the noted time stamp I find the old named entry in the catalogue and click the exclamation mark to Locate it's new place (this step is the reason for step 3 being necessary; if the new named file would remain in the catalogue, then the Locate action of the old record to the new file would not work because the file already exists in the catalogue).

First I looked at automating this with direct access to the SQLite database. For that I did an export of the database to JSON format, then did a manual change as described above (only up to step 3) and generated another JSON export. diff gave a nice result of what had changed but some of the changes are just too opaque to try without in depth knowledge of the database scheme.

Now I am looking into developing a plugin and my questions are: would it be possible to do these actions using a plugin? Or would any of these actions not be feasible?

If it would be possible I will investigate further and dive into the SDK and whatever is involved to develop this plugin.

On the other hand, if you believe this wouldn't be possible technically, I would also like to ask if anyone has any other suggestions to help sort out my problem.

  • 1
    Were the files originally named in a way such that the names could possibly be recovered from metadata? – junkyardsparkle Sep 5 '17 at 0:29
  • 1
    Hmm... it might still be worth considering a file renaming approach rather than messing with the db... maybe dump a table of datetime <> file path, then iterate through the files doing a lookup/rename? – junkyardsparkle Sep 5 '17 at 9:00
  • 1
    Ugh. Well... will LR let you export .xmp sidecar files with the editing data for files that it can't find? If so, you could dump those, match them to files with datetime, then re-import the whole mess into a clean new db... – junkyardsparkle Sep 5 '17 at 17:38
  • 1
    No, since files do not exist (as well as the location) .xmp files cannot be created. I'm now fiddling with python and sqlite library. I'll post an answer if I manage to sort the mess but right now it looks promising. :-) Thanks again for the tip. – Fabricio Sep 7 '17 at 22:33
  • 1
    Thanks again for your tip junkyardsparkle! It made me think the problem from a different angle and after a few days of coding and testing I managed to sort my way out of the mess I got myself into. :-) – Fabricio Sep 15 '17 at 20:47
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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