Lightroom: Is there a way to re-associate converted DNG files with the original RAW files?

  • 2
  • Question
  • Updated 5 years ago
  • (Edited)
After converting most of my library from RAW to DNG, I now realize that I need to stick with RAW for various reasons. Luckily, I kept the RAW files but I can't figure out how to re-associate them with the catalogue wholesale. I can do it one at a time, but even if I have "Find nearby missing photos" checked it does not associate other photos.

Btw, the file names are the same except for the extension.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
  • frustrated

Posted 7 years ago

  • 2
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
You could try renaming the raws in Bridge, fooling LR by giving them a DNG extension. If that works, save back the metadata, rename again in Bridge, and reimport. You'll lose VC info, stacks etc. Or import the raws and use my Syncomatic plugin to copy the DNGs' metadata and (almost all) adjustments to the raw files.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
John - thank you for bringing up your Syncomatic plugin. All the long-time regular forum dwellers know about it by now, but Photographe and new readers may not have.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
So predictably childish. You'd have a point if I pushed my plug-ins for everything from world peace to haemorrhoids, but there's only one Rob Cole. Jog on.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
I was being sincere, not sarcastic.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
No, you were being juvenile.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
No I wasn't - you were. Thpppppppp.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
I know, I shouldn't feed the troll....
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
John--I don't need to preserve VCs and stacks, but I do need to preserve collections. Both methods you outline would lose collections, right?

I tried renaming the DNGs within LR, but LR unfortunately does not allow changing the file extension.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
The renaming trick (I said do it in Bridge as it will change the extension) would lose the collections because of the re-importing. My plugin would preserve them but would lose the crop (as it's not saved in Develop presets). One other method, by the way, is to hack the SQL.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
With Syncomatic plugin, how are collections preserved after reimport of RAW? Losing the crops would be unfortunate though.

Can you tell me what tools are needed to hack the SQL?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Photographe asked: "Can you tell me what tools are needed to hack the SQL?"

I use SqliteSpy - works very well, but I've never used any others.

Another option is to use textpipe. Its a text search/replace utility that includes support for databases built in - you do not need to know SQL... - its not cheap.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Rob--Which one would you recommend for a beginner, SqliteSpy or Textpipe wanting to do just this task? What if I wanted to learn more about SQL?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Textpipe is like $400, supposedly usable without much technical prowess, but still a learning curve..., sqlitespy is free and would be a good choice if you want to learn SQL. I've never used the one John B. mentioned so can't compare.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Do you know the official website of sqlitespy?
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Ok, there is a table called AgLibraryFile that has 16 columns. One of them is called extension. I ran the following SQL:

UPDATE AgLibraryFile
SET extension='NEF'
WHERE extension='DNG'

Is that all there is to it?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Be sure to check the idx_... and lc_idx... fields in AgLibraryFile too.

And, Adobe_images has stackParent_extension & stackParent_fileName as well. There may be others - dunno - I've never done this, maybe Beardy has...

Its possible you could generate an SQL to look for 'DNG' (or 'dng'...) in any field of any table, but I don't know how without a lot of typing.

e.g. ref: http://vyaskn.tripod.com/search_all_c...

If it were me, I'd want to make damm shure though - so you don't have a problem in 3 months from now when you try to access a virtual copy or something...

And, either check the preview databases too, or just delete the previews (lrdata folder).

(obviously modify a test copy before committing...)
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Ok, so it's a bit more complicated. I am working from a copy. I haven't used stacks or VCs much. I will get rid of stacks before hand, and I'll probably just convert the VCs to individuals files. I will look around for DNGs before I proceed. I will definitely delete the previews, too.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Without knowing more about how the code/database actually works, I would not rest until I'd made sure I'd found every occurrence of Dng (case insensitve) in every table and double checked it (not necessarily every instance, but to identify field names in tables...) I don't know if others with more experience or guidance have done this and know what's critical and what's not, but... - I don't mean to scare you, but it may be relatively cheap insurance...

one person's solution: http://vyaskn.tripod.com/search_all_c...

(there are others)
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Adobe_Images also has "fileFormat". John-can you shed any light on this?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
You'll need to change that from 'DNG' to 'RAW'.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Btw, do you know which is the Primary Key in Lightroom database tables? Is it id_local or id_global?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
id_local - you can see this in sqlitespy by opening 'Columns' (checkmark under 'PK')
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Got it! It's interesting, id_local does not match across tables. For example, in Adobe_Images, id_local seems to be 1 less than the corresponding image in AgLibraryFile.

One other thing I will need to figure out: in SQLiteSpy, how do you manipulate an entry, i.e. change "abcdef.DNG" to "abcdef.NEF"?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
I'm pretty lousy with SQL, but pretty good at programming, so if it were me, I'd write a Python script to do it - has built in high-level sqlite3 library.

Use simple statements to get the strings into python, then use regex or simple string substitution to do the search & replace, then use a simple update statement to write the db.

A hot SQL guy would just execute an SQL statement in sqlite3 client app, using something like:

UPDATE dbo.authors
SET city = REPLACE(city, 'Salt', 'Olympic')
WHERE city LIKE 'Salt%';

PS - In case you haven't figured it out, sqlite3 does not support stored procedures so the technique presented early to check all fields of all tables wouldn't work.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Python script example excerpt:

import sqlite3

lrCatCon = None
try:
lrCatCon = sqlite3.connect( self.catPath )
# works: cur = lrCatCon.execute( "select Adobe_images.id_global, Adobe_images.orientation, AgLibraryFile.id_global from Adobe_images INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local" )
cur = lrCatCon.execute( "select Adobe_images.id_global, Adobe_images.orientation, AgLibraryFile.id_global, AgLibraryFile.idx_filename, AgLibraryFolder.pathFromRoot, AgLibraryRootFolder.absolutePath from Adobe_images INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local INNER JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local INNER JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local" )
all = cur.fetchall()
print "initializing photo-info from db"
count = 0
for row in all:
entry = dict()
entry["photoId"] = row[0] # redundent, but convenient.
entry["orientation"] = row[1]
entry["previewId"] = row[2]
filename = row[3]
pathFromRoot = row[4]
rootFolder = row[5]
photoFolder = os.path.join( rootFolder, pathFromRoot )
photoPath = os.path.normpath( os.path.join( photoFolder, filename ) ) # *** beware: join does not use backslashes on windows unless it starts that way or something.
entry["photoPath"] = photoPath
###2 more?
# print photoPath # very verbose if catalog large.
self.photoIdToPhotoInfo[row[0]] = entry
self.photoPathToPhotoInfo[photoPath] = entry
count = count + 1
print "{0} db rows processed".format( count )
break
except sqlite3.OperationalError:
errm = str( sys.exc_info()[1] )
print errm
if errm == "database is locked":
answer = MsgBox.showWarning( self.frame, "Lightroom catalog ({0}) is locked - if Lightroom is already running, close it now, then click 'OK' to try again (use 'Task Manager/Force Quit' if necessary).".format(self.catPath), wx.OK | wx.CANCEL )
if answer != wx.ID_OK:
self.close( False )
else:
MsgBox.showError( self.frame, "Operational error accessing lrcat file ({0}) - try again? (possibly bad sql)".format( self.catPath ), errm, wx.OK | wx.CANCEL )
if answer != wx.ID_OK:
self.close( False )
except:
errm = str( sys.exc_info()[1] )
print errm
MsgBox.showError( self.frame, "Unknown error accessing lrcat file ({0}) - try again?".format( self.catPath ), errm, wx.OK | wx.CANCEL )
if answer != wx.ID_OK:
self.close( False )
finally:
if lrCatCon:
lrCatCon.close()
lrCatCon = None
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Thanks Rob! I think I got that part. The only thing remains to figure what how to do an Update Join statement. The normal syntax doesn't seem to be working.

I tried this statement, to limit changes to DNG taken by a certain camera.

Update Adobe_images
Set Adobe_images.stackParent_extension='nef'
FROM AgHarvestedExifMetadata INNER JOIN Adobe_images
ON AgHarvestedExifMetadata.image = Adobe_images.id_local
WHERE AgHarvestedExifMetadata.cameraModelRef=123456 and Adobe_images.fileFormat='DNG'

Got a syntax error
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Beats the heck outta me - maybe you have to do sub-select? e.g.


update table_a set name = (select table_b.name from table_b where table_b.id = table_a.id)
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Rob-I just noticed the Python code you posted. To be honest, I don't have a clue what that is or how I would go about using it. But I am willing to learn.

Fr idea, huh? And you'd explain to the sharks how this fits into the workflow :)
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
I am surprise that SQLite does support JOINs in UPDATE statements--it seems to be the whole point of a relational database. Per that article, I am trying this subquery:

Update Adobe_images
Set stackParent_extension='nef'
WHERE id_local IN (SELECT image FROM AgHarvestedExifMetadata WHERE (AgHarvestedExifMetadata.cameraModelRef=123456 and Adobe_images.fileFormat='DNG'))
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
I thought maybe since you were masterminding SQL you might want to mastermind the Python too. Anyway, if you come up with a sequence of SQL statements that will do the job, but need some programmatic glue, I'll roll them into a script for you. I mean sqlite3 does not support stored procedures, but python does...

re: "Sharks" - I know some people on this forum think I'm an idiot with mostly ridiculous ideas..., but I'm not sure what Adobe thinks about me, nor what they'd think about an onslaught of nay-saying... - but probably best if the idea came from you ;-} I'd support as best I could.

PS - I bet somebody at Adobe could write a script to convert from DNG in short order, and post it in the lab.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
With Syncomatic, there's no reimport. You import the raws into the catalogue as well as the dngs, and the plugin then copies data between files based on matching their filenames. Collections are included. Afterwards you could get rid of the dngs.

The SQL alternative is to use the open source SQLite Database browser. I can't remember the exact SQL code, but I wouldn't recommend this method to anyone with no experience of writing SQL. Essentially you'd be rewriting the path field(s) and replacing the dng extension with cr2, nef or whatever.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Photographe - Consider converting this question to an "FR/Idea" - "Convert *from* DNG". Being able to convert to DNG without being able to convert back is a bit like being able to install software but not being able to uninstall it... Or how about this: being able to start a subscription to a magazine, but not being able to stop it. I had a better one on the tip of my tongue but it slipped off...

"Obviously" this feature would just scare up the original raws and tweak the DB (like what Photographe is doing now the hard way), as opposed to being able to create original raw files from DNG.

This would allow people to try DNG, but then revert, if for whatever reason it is no longer working out... - Could be billed as a selling point - "free to try...". There may be a lot of people who would try DNG if it wasn't a one way street.
Photo of Victoria Bampton - Lightroom Queen

Victoria Bampton - Lightroom Queen, Champion

  • 4380 Posts
  • 1602 Reply Likes
How would you imagine LR 'scaring up the original raws' unless they've been embedded in the DNG file?
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
I think more people keep a backup of the original raw than embed it, but I think both options should be supported.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Rob and John--thank you very much for your insights. I have finally put together a total of 28 SQL instructions that should turn back the clock and turn my library into RAW files of different flavors. I am sure that someone with more experience (Adobe?) could have done it much more efficiently and robustly. For me it has been a real learning experience. A couple of things I've noted:

-my LR catalogue was not free from errors. One particular problem/error that was causing one of the SQL instructions to crash was that one image out of 50K had both a RAW and a DNG version on disk and in the library.

-running the 28 SQL instructions in SQliteSpy takes over an hour.

-unfortunately, my SQLs would not be usable by someone else without tweaking as they only address 9 camera models from 3 manufacturers, and for 1000 images I was forced to use a shortcut that would not work on someone else's database. Furthermore, lots of unanticipated database properties could render them unusable (see for example the first point above).

-MUCH more interesting: after perusing my LR catalogue, I now understand why others have noted some issues in the database design:

*the relationship between image and file appears to me to be quite weak, as it depends on the name and location of the file, rather than something more intrinsic about the file.

*not unrelated to that, the stack implementation seems to lack power and finesse.

But, hey I'm just a newbie.

Rob--I would be interested in knowing how Python would fit into this process. Thanks!
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
28? - Wow.

I'd be curious to know which fields you had to change.

Anyway, what Python could do is execute the same thing but with different camera models, or avoid the sub-select by capturing intermediate results and reusing them in the next sql statement. Maybe providing looping mechanisms, and automating dependencies - if one set of SQL commands could depend on the results of others...

I "heard" the sub-select mechanism in sqlite3 is extremely inefficient - prohibitively so in some cases - not sure why, but over an hour seems like a *long* time to be crunching SQL.

So, your catalog is back in shape?
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
These are the fields I changed:

Adobe_images.stackParent_extension
Adobe_images.stackParent_fileName
Adobe_images.stackParent____fileFormat
Adobe_images.fileFormat
AgLibraryFile.extension
AgLibraryFile.idx_filename
AgLibraryFile.lc_idx_filename
AgLibraryFile.lc_idx_filenameExtension

I also changed AgLibraryFile.originalFilename for a handful. For some reason AgLibraryFile.originalFilename has a DNG extension for some photos--probably these are the ones that I converted to DNG upon import rather than later on.

I am hoping to be up and running in a few days. I have to bring the drive with the RAW images back online, validate the files, and back them up. Initial testing on a handful of files seemed ok. I have my fingers crossed. I will keep a dng copy for a while, until I am sure the RAW files are healthy.

I do believe that the sub-select mechanism is inefficient. The sub-select commands are the only that took any time at all to execute. Every thing else was a matter of seconds.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Thanks Photographe. I assume you have already or will soon cut over to this so-modified catalog. Please let us know if everything still seems OK after a while, or if you experience problems in future which may have been due to these mods, eh? Again, not trying to scare anyone, just aware that there are risks...
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
One more field that probably should be changed: Adobe_AdditionalMetadata.embeddedXMP. I believe that I have successfully gone back to RAW, but it will take some more testing before I can be sure.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
I may automate filename search/replace (via sql app) if not supported come Lr4, unless somebody already has(?) - based on your research Photographe. I have most files named one way, but started a new scheme... rather have all files named one way. I don't know how else to do it.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Rob--This just occurred to me: can Microsoft Access be used to open and manipulate LR database? It seems it would require an ODBC or other (meaning that basically the answer is no), but I wanted to double check.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
Yes. There's an open source ODBC driver. http://www.ch-werner.de/sqliteodbc/
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Photographe,

I'm not a database expert, but:

- I *think* you can only open adb files in Access.

- I don't know if sqlite3 supports ODBC connections, but if it did, I think it would only be valuable in the context of external "offline" applications, no?
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
You have an Access mdb file, but it can have both its own tables (stored inside the mdb) and connect to external data via ODBC sources. You can then run queries and build UIs / reports from both types of data source. SQLlite's ODBC driver is a bit primitive though (I used to hook Access up to big SQL Server and Oracle systems) and I never feel the connection is totally stable / reliable. Also the LR database design changed a few times in little ways. I eventually decided the pain of learning Lua and the SDK was preferable.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
I've run into a problem, which seems to be insurmountable.

On the surface, everything seems to be working fine. LR found the RAW files and made the proper link to them. Stored information in the catalgue, like star ratings and so on is fine.

But when I try to synchronise folders I run into problems.

First, LR searches for files that are not in the catalogue and comes back with the right number. Let's say it finds 5 files in a given folder that are not in the catalogue.

Now, when I click synchronize, it changes it's mind and decides that all RAW files are not in the catalogue. And this persists even if I click "Don't import suspected duplicates".

My guess is that LR must assign a sort-of-unique ID to each picture, and makes use of it at odd times.
Photo of john beardsworth

john beardsworth

  • 1080 Posts
  • 254 Reply Likes
Try importing the entire catalogue into a fresh one. It's proved helpful on the occasions when I have hacked a catalogue's SQL and succeeded in breaking something.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
John-I tried a couple of variations of your idea:

(1) importing catalogue into a fresh did not solve the problem.

(2) exporting catalogue into a fresh did not solve the problem.

(3) exporting catalogue into a fresh one and choosing "export negative files" DID solve the problem in the new catalogue BUT if I point the new catalogue to the original files the same problem happens. I compared the old and new negative files using Syncback (bit for bit) and they're identical. What do you make of this? Also, if I copy the old files to the new location (using Syncback), the new catalogue messes up again.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
UPDATE: I did some further testing. Here is what I did:

Exported catalogue into a fresh one and Chose "export negative files". Now, I pointed the OLD catalogue to the news files. No problems. Then, I copied the new files (using Syncback), back to their original locations on the original drive. As result, most appear ok, but a random few appear to the old catalogue to be new files.

There is a chance I messed up the catalogue in some subtle way, but given the complexity of the problem and the way it shows up randomly and inconsistently depending on the stage in the synchronization, I'm inclined to think there is a bug in the way LR recognizes file changes possibly as a result of different security settings in Windows 7 file structure.

I'm going to play it safe and write out xmps to all my files and reimport them into a new catalogue, thereby losing VC, Stacks, Develop History, Collections and possibly more.

Morals of the story: don't convert to dng unless you really mean it, and don't depend on LR functions that cannot be exported via xmp.
Photo of Photographe

Photographe

  • 243 Posts
  • 31 Reply Likes
Everything is up and running now. In order to retain collections for the future, I created a Keyword hierarchy that mirrors my collections hierarchy. Thank you both for your help.
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
Glad you got it working. Thanks for providing so much helpful information...
Photo of Rob Cole

Rob Cole

  • 4831 Posts
  • 384 Reply Likes
One can now revert from DNG using

UnDNG
----------