Scripting and querying Lightroom's database

[ron634876]

Hi,

I've been using Apple Aperture for many years, and with Apple stopping development of Aperture, I'm now looking for another solution.

When I started with Aperture, I compared it against Lightroom. At the time, I actually liked Lightroom better, but I'm a programmer, and I was able to figure out how to access and control Aperture's database via perl scripts, and being able to customize and automate Aperture was the reason I chose it.

My perl scripts can see what photos are in the Aperture library, what projects / folders they are stored in, etc. My scripts can also rearrange the photos and projects inside of Aperture. When I take new photos, my scripts automatically organize and place the photos on disk and in Aperture in the right hierarchy. They also copy the files to my wife's computer and update her Aperture database as well.

Is there anything similar with Lightroom? I've looked around a bit, but all I can find is the ability to create plug-ins via Lua.

Is there any way for a script to query the Lightroom database? Is there any way to have a script tell Lightroom to import photos? Does Lightroom support Applescript?

Thanks for any help, and my apologies if this information is somewhere else. I did spend a bit of time searching around, but I couldn't find anything (which makes me think that I won't be able to do with Lightroom what I want to do).

Ron

 

[clee01l]

Welcome to the forum. In a former life I was a database analyst. The LR database (catalog file) is SQLite. I invite you to make a copy of your LR catalog and poke around inside. Because it is a relational database, there are quite a few dependencies and doing anything more than query might break referential integrity and the last thing you want to do is corrupt a LR catalog containing tens of thousands of image references. With LR, you can store your master images anywhere even on a network drive and LR will maintain a pointer to the path to the master original copy. As long as you manage your image inside of LR, LR will keep the pointers up to date, intact and maintain referential integrity.

Lightroom manages communications between catalogs with an "Export to catalog/Import from another catalog" complementary functions. To share your work between your wife's computer and yours you will need to create a intermediate catalog to extract the metadata and post-processing adjustments from one catalog to import these into another catalog. You will need the folders containing the master image copies either packaged with the intermediate catalog or on a shared network drive where that are universally accessible. The best option here is a shared network file server to store one set of master original images to be shared between both LR catalogs. (Because the LR catalog is a single user database, the file can not be located on a network drive.)

Plugins let you work with the LR API. So if you have mastered perl and Applescript, you can master lua. There is a wealth of 3rd party plugins that do a lot of the heavy lifting mainly in the LR publish area. Take a look at Jeffrey Freidl's plugins. He covers most bases. John Beardsworth (one of the Gurus here) has a series of plugins that do lots of housekeeping chores

LR has a Auto Import function and a watch folder. Any image files dropped in the watched folder get automatically imported into LR. (I use Photostream2Folder app to intercept my iCloud Photostream and bypass iPhoto.

Lightroom does not interface with Applescript. However you can write applescripts to send keystrokes to the LR app.

I use LR Smart Collections (Aperture Smart Albums??) I have a modified version of John Beardworth's Workflow Smart Collections to manage my workflow. There are three overarching steps to my workflow.

1. Insert a camera card and press the {import} button in the Import Dialog.
2. Add Keywords, Titles and Captions and then post processing development tracking the progress through John Beardworth's Workflow Smart Collections
3. Using additional Publish smart collections that I developed based upon John Beardworth's Workflow Smart Collections, completed images are automatically collected in various publish services. I then press the {Publish} button.

There are embellishments that I have made to the three steps, but it really is that simple.

 

[ron634876]

Thank you so much!

Welcome to the forum. In a former life I was a database analyst. The LR database (catalog file) is SQLite. I invite you to make a copy of your LR catalog and poke around inside. Because it is a relational database, there are quite a few dependencies and doing anything more than query might break referential integrity and the last thing you want to do is corrupt a LR catalog containing tens of thousands of image references. With LR, you can store your master images anywhere even on a network drive and LR will maintain a pointer to the path to the master original copy. As long as you manage your image inside of LR, LR will keep the pointers up to date, intact and maintain referential integrity.

Lightroom manages communications between catalogs with an "Export to catalog/Import from another catalog" complementary functions. To share your work between your wife's computer and yours you will need to create a intermediate catalog to extract the metadata and post-processing adjustments from one catalog to import these into another catalog. You will need the folders containing the master image copies either packaged with the intermediate catalog or on a shared network drive where that are universally accessible. The best option here is a shared network file server to store one set of master original images to be shared between both LR catalogs. (Because the LR catalog is a single user database, the file can not be located on a network drive.)

Plugins let you work with the LR API. So if you have mastered perl and Applescript, you can master lua. There is a wealth of 3rd party plugins that do a lot of the heavy lifting mainly in the LR publish area. Take a look at Jeffrey Freidl's plugins. He covers most bases. John Beardsworth (one of the Gurus here) has a series of plugins that do lots of housekeeping chores

LR has a Auto Import function and a watch folder. Any image files dropped in the watched folder get automatically imported into LR. (I use Photostream2Folder app to intercept my iCloud Photostream and bypass iPhoto.

Lightroom does not interface with Applescript. However you can write applescripts to send keystrokes to the LR app.

I use LR Smart Collections (Aperture Smart Albums??) I have a modified version of John Beardworth's Workflow Smart Collections to manage my workflow. There are three overarching steps to my workflow.

1. Insert a camera card and press the {import} button in the Import Dialog.
2. Add Keywords, Titles and Captions and then post processing development tracking the progress through John Beardworth's Workflow Smart Collections
3. Using additional Publish smart collections that I developed based upon John Beardworth's Workflow Smart Collections, completed images are automatically collected in various publish services. I then press the {Publish} button.

There are embellishments that I have made to the three steps, but it really is that simple.

Thank you so much for the great feedback!

It seems with a bit of work, I can probably replicate most of my flow in Lightroom.

 

[bubba123]

Thank you so much for the great feedback!

It seems with a bit of work, I can probably replicate most of my flow in Lightroom.

Ron, I'm curious what you came up with on your sqlite research. I will soon be moving to Lightroom from iPhoto/Aperture after seeing Photos, but my issue is that I leverage the iPhoto/Aperture database to manage my album names, photos, comments, stars, etc. I sync this data to MySQL and then leverage that data to automatically push and populate my photo archive/viewing site (currently Flickr). Anything you have come up with (schema, etc) would be helpful.

Thanks,
Brian

 

[clee01l]

Brian, Welcome to the forum. In Aperture/iPhoto, there was/is no functional equivalent to the LR Web module. If you have your own personal website, you can access it directly using the LR Web module. There are included templates that might do what you are doing now. If not there are several third party plugins (The Turning Gate is one example) that can be highly customized to manage the interface between LR and the web host.

As an aside, Aperture/iPhoto also use SQLite. Why did you transfer everything to MySQL for your web interface?

 

[ron634876]

Ron, I'm curious what you came up with on your sqlite research. I will soon be moving to Lightroom from iPhoto/Aperture after seeing Photos, but my issue is that I leverage the iPhoto/Aperture database to manage my album names, photos, comments, stars, etc. I sync this data to MySQL and then leverage that data to automatically push and populate my photo archive/viewing site (currently Flickr). Anything you have come up with (schema, etc) would be helpful.

Thanks,
Brian

Hi Brian ... I have actually not made the switch yet. I will probably eventually switch to Lightroom, but I am sticking with Aperture for now until Photos is released. If "Photos" does what I need, I may stick with it, although Apple seems to be falling behind Adobe in the RAW processing and image editing side of things, so I will probably switch to Lightroom at some point in the future.

 

[clee01l]

I don't know that Apple is falling behind Adobe in RAW processing. Instead they have adopted a "one size fits all" approach and moved their whole focus to providing simplistic tools for the mass consumer market that integrate all of their product lines both iOS and OS X. Apple will continue to update the OS CODEC to provide support for new proprietary RAW formats as new camera are released. This is no different from what Adobe does with ACR.

 

[gYab61zH]

I am currently investigating the possibility of linking LR's SQLite catalogue to another SQL database which will supply me with the extra fields I need (MS SQL?, MySQL?). I hope to have a better idea about how to implement this in the not too distant future. If someone else has already done something similar I would love to hear it.

 

[clee01l]

I am currently investigating the possibility of linking LR's SQLite catalogue to another SQL database which will supply me with the extra fields I need (MS SQL?, MySQL?). I hope to have a better idea about how to implement this in the not too distant future. If someone else has already done something similar I would love to hear it.

SQLite is pretty robust. Just create your tables in SQLite. You might even be able to include this tables in the LR catalog file (I haven't tried this, but Plugin developers have the ability to expand the LR structure through the SDK.)

Any time I want to access the LR catalog directly outside of LR I use Navicat. Keep in mint that The LR Catalog is a single user database and you can not run LR and open the database file with another tool at the same time.

 

[tspear]

Ron,

I have used Java, Bash and few other tools to dump data from Lr. It is pretty straight forward programming. Lua is pretty complete, and the majority of what you want to do could easily be done inside Lua but that is probably more aggravation then it is worth.
If your wife is mostly just looking at the photos, and wants them available on her computer I would suggest another approach.
Use Jeff Freidl's plugin; either collection or folder depending on your workflow; to create a publish service. Have this be a publish service push the images to a file mount on your wive's computer or a network share. From there use AppleScript on her computer to keep the iPhoto, Aperture or Photos library up to date.

Tim

 

[darekk]

Lightroom Lightroom Catalog.lrcat file can be browsed also in DB Browser for SQLite:
sqlitebrowser.org

Few days ago I found out, that it can be opened in Microsoft Access database. But do it using a COPY of your file just in case. Steps for 32-bit version of Access:

• Install the SQLite ODBC Driver driver for Access (SQLite 2 Drivers option only)
  You can download the driver created by Christian Werner:
  www.ch-werner.de/sqliteodbc
• Set up ODBC data source:
  Control Panel:
  
  Search: ODBC Data Sources
  or:
  Control Panel\System and Security\Administrative Tools
  
  ODBC Data Sources (32-bit):
  Set up ODBC data sources (32-bit) - User DSN - Add -
  Create New Data Source - SQLite 3 ODBC Driver - Finish -
  SQLite3 ODBC DSN Configuration:
  Data Source Name: Lightroom Catalog
  Database Name: C:\Users\user name\Pictures\Lightroom\Lightroom Catalog - Copy.lrcat
  No WCHAR option checked !*
  - OK
• Link the ODBC data source to the Microsoft Access database:
  Microsoft Access database:
  External Data - ODBC Database - Link the data source by creating a linked table - OK -
  Select Data Source:
  Machine Data Source - SQLite3 Datasource - OK -
  SQLite3 ODBC Driver Connect:
  Data Source Name: SQLite3 Datasource (perhaps more unique name would be better)
  Database Name: C:\Users\user name\Pictures\Lightroom\Lightroom Catalog - Copy.lrcat
  - OK
  Link Tables ...

This database requires the "Lightroom Catalog - Copy.lrcat" file, because tables are connected only. To copy (export) all tables to the Access database choose:
Import the source data into a new table in the current database
instead of:
Link the data source by creating a linked table.
* No WCHAR option checked - because very large Memo (Long Text) fields would not be updated. If you created a linked table with No WCHAR unchecked, delete all ODBC linked tables and create them again with No WCHAR option checked (http://stackoverflow.com/questions/41099934/microsoft-access-cant-update-large-memo-long-t ext-fields-on-a-linked-sqlite3 ).

A little more, including example queries:
forums.adobe.com/message/9186542#9186542

Note that data uploading or modifying in .lrcat files like the "Lightroom Catalog.lrcat" using applications other than Lightroom (or its SDK) is not only risky (can corrupt such file if you did it improperly), but you also probably would not get any support from Adobe in such case:
forums.adobe.com/message/9210122#9210122 Data modifing in the Lightroom Catalog.lrcat file and the Lightroom license