photographe's profile

242 Messages

 • 

9.1K Points

Wed, Jun 8, 2011 4:05 AM

Closed

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

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.

1.3K Messages

 • 

22.5K Points

11 y ago

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.

4.5K Messages

 • 

76.3K Points

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.

1.3K Messages

 • 

22.5K Points

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.

4.5K Messages

 • 

76.3K Points

I was being sincere, not sarcastic.

1.3K Messages

 • 

22.5K Points

No, you were being juvenile.

4.5K Messages

 • 

76.3K Points

No I wasn't - you were. Thpppppppp.

242 Messages

 • 

9.1K Points

11 y ago

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.

1.3K Messages

 • 

22.5K Points

11 y ago

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.

242 Messages

 • 

9.1K Points

11 y ago

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?

4.5K Messages

 • 

76.3K Points

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.

242 Messages

 • 

9.1K Points

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?

4.5K Messages

 • 

76.3K Points

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.

242 Messages

 • 

9.1K Points

Do you know the official website of sqlitespy?

1.3K Messages

 • 

22.5K Points

11 y ago

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.

4.5K Messages

 • 

76.3K Points

11 y ago

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.

Champion

 • 

6.7K Messages

 • 

112K Points

How would you imagine LR 'scaring up the original raws' unless they've been embedded in the DNG file?

Victoria Bampton a.k.a. The Lightroom Queen

www.lightroomqueen.com

Author of Adobe Lightroom Classic - The Missing FAQ and Adobe Lightroom - Edit Like a Pro books.

4.5K Messages

 • 

76.3K Points

I think more people keep a backup of the original raw than embed it, but I think both options should be supported.

242 Messages

 • 

9.1K Points

11 y ago

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!

4.5K Messages

 • 

76.3K Points

11 y ago

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?

242 Messages

 • 

9.1K Points

11 y ago

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.

4.5K Messages

 • 

76.3K Points

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...

242 Messages

 • 

9.1K Points

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.

4.5K Messages

 • 

76.3K Points

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.

242 Messages

 • 

9.1K Points

11 y ago

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.

1.3K Messages

 • 

22.5K Points

Yes. There's an open source ODBC driver. http://www.ch-werner.de/sqliteodbc/

4.5K Messages

 • 

76.3K Points

11 y ago

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?

1.3K Messages

 • 

22.5K Points

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.

242 Messages

 • 

9.1K Points

11 y ago

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.

1.3K Messages

 • 

22.5K Points

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.

242 Messages

 • 

9.1K Points

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.

242 Messages

 • 

9.1K Points

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.

242 Messages

 • 

9.1K Points

11 y ago

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.

4.5K Messages

 • 

76.3K Points

Glad you got it working. Thanks for providing so much helpful information...

4.5K Messages

 • 

76.3K Points

8 y ago

One can now revert from DNG using

UnDNG
----------

1 Message

 • 

82 Points

I was happy to find this as I'm regretting converting all my files to DNG.  I had decided to do it for the embedded checksum, but that's not at all worth it as I'm seeing huge (10x) file size increase for non-raw files:

5.3M Dec 15 15:55 IMG_0951.dng
354K Nov 10  2003 IMG_0951.jpg

3.2M Sep  6 16:48 IMG_2510.HEIC
30M Jan  6 21:41 IMG_2510.dng

Unfortunately, http://robcole.com seems to be down.  Is there any where else that we can get this plugin these days?  Or is there any other solution that has since become available?

Champion

 • 

6.7K Messages

 • 

112K Points

Sadly Rob died a few years ago, so it would be a question of finding someone who still has a copy, or convincing one of the current plug-in developers to create something similar.

One option would be to import the original format and then use the Syncomatic plug-in to copy the settings over from the DNG's to the originals. https://www.photographers-toolbox.com/products/jbeardsworth/syncomatic/

Victoria Bampton a.k.a. The Lightroom Queen

www.lightroomqueen.com

Author of Adobe Lightroom Classic - The Missing FAQ and Adobe Lightroom - Edit Like a Pro books.

294 Messages

 • 

4.5K Points

OMG, I did not know that he passed.  His plugins were terrific.  Unfortunately, I don't think I have a copy, but I will take a look-see.