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

  • 961 Posts
  • 199 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 john beardsworth

john beardsworth

  • 961 Posts
  • 199 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
  • 372 Reply Likes
I was being sincere, not sarcastic.
Photo of john beardsworth

john beardsworth

  • 961 Posts
  • 199 Reply Likes
No, you were being juvenile.
Photo of Rob Cole

Rob Cole

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

john beardsworth

  • 961 Posts
  • 199 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

  • 961 Posts
  • 199 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
  • 371 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
  • 371 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

  • 961 Posts
  • 199 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
  • 372 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

  • 3818 Posts
  • 1263 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
  • 372 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
  • 372 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
  • 371 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
  • 371 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

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

Rob Cole

  • 4831 Posts
  • 372 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

  • 961 Posts
  • 199 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

  • 956 Posts
  • 196 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
  • 371 Reply Likes
Glad you got it working. Thanks for providing so much helpful information...
Photo of Rob Cole

Rob Cole

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

UnDNG
----------