Skip to content

Instantly share code, notes, and snippets.

@jhoneill
Created April 18, 2020 11:54
Show Gist options
  • Save jhoneill/a902c37d2c239584461af3ccecae44c9 to your computer and use it in GitHub Desktop.
Save jhoneill/a902c37d2c239584461af3ccecae44c9 to your computer and use it in GitHub Desktop.
PowerShell Script to explore Adobe Lightroom catalogs
##########################################################
# #
# Tools for getting information from the Lightroom DB #
# To use this script you need to download the SQL Lite #
# ODBC driver from http://www.ch-werner.de/sqliteodbc/ #
# #
# and set your default catlog path #
# #
##########################################################
#Catalog is in <<my pictures>>\lightroom\Lightroom4 catalog.lrcat - UPDATE AS Needws
$Global:catlogPath = (Join-Path ([environment]::GetFolderPath([System.Environment+SpecialFolder]::MyPictures)) "Lightroom\Lightroom 4 Catalog.lrcat")
<#
.Synopsis
Returns files or Folders known to LightRoom
.EXAMPLE
Get-LightRoomItem -ListFolders -Include $pwd
Lists folders below the current one, in the LightRoom Library
.EXAMPLE
Get-LightRoomItem -include "dive"
Lists files in LightRoom Library where the path contains
"dive" in the folder or filename
.EXAMPLE
$Paths = (Get-LightRoomItem -include "$pwd%dng" | select -ExpandProperty path) ; dir *.dng | where {$Paths -notcontains $_.FullName} | move -Destination scrap -whatif
Stores paths of lightroom items in the current folder ending in .DNG in $Paths;
then gets files in the current folder and moves those which are not in lightroom.
-Whatif allows the files to be confirmed before being moved.
.EXAMPLE
Get-LightRoomItem | Group-Object -no -Property "Lens" | sort count | ft -a count,name
Produces a summary of lightroom items by lens used.
#>
function Get-LightRoomItem{[CmdletBinding()]
[OutputType([System.Data.DataRow])]
param(
# Path to the LightRoom catalog file
$Path = $Global:CatlogPath,
# Files to include
[Parameter(ValueFromPipelineByPropertyName=$true)]
[string]$Include = "C:\" ,
# Switch to list known folders instead of files
[Switch]$ListFolders
)
process { foreach ($I in $Include) {
if ($listFolders) {$sql = @"
SELECT RootFolder.absolutePath || Folder.pathFromRoot as FullName
FROM AgLibraryFolder Folder
JOIN AgLibraryRootFolder RootFolder ON RootFolder.id_local = Folder.rootFolder
WHERE RootFolder.absolutePath || Folder.pathFromRoot like '%$($i -replace "\\","/")%'
ORDER BY FullName
"@ }
Else {$sql = @"
SELECT rootFolder.absolutePath || folder.pathFromRoot || rootfile.baseName || '.' || rootfile.extension AS fullName,
LensRef.value AS Lens, image.id_global, colorLabels, Camera.Value AS cameraModel,
fileFormat, fileHeight, fileWidth, orientation ,
captureTime, dateDay, dateMonth, dateYear,
hasGPS , gpsLatitude, gpsLongitude, flashFired,
focalLength, isoSpeedRating , caption, copyright
FROM AgLibraryIPTC IPTC
JOIN Adobe_images image ON image.id_local = IPTC.image
JOIN AgLibraryFile rootFile ON rootfile.id_local = image.rootFile
JOIN AgLibraryFolder folder ON folder.id_local = rootfile.folder
JOIN AgLibraryRootFolder rootFolder ON rootFolder.id_local = folder.rootFolder
JOIN AgharvestedExifMetadata metadata ON image.id_local = metadata.image
LEFT JOIN AgInternedExifLens LensRef ON LensRef.id_Local = metadata.lensRef
LEFT JOIN AgInternedExifCameraModel Camera ON Camera.id_local = metadata.cameraModelRef
WHERE RootFolder.absolutePath || Folder.pathFromRoot || RootFile.baseName || '.' || RootFile.extension like '%$($i -replace "\\","/")%'
ORDER BY FullName
"@ }
Write-Verbose $sql
$dt = New-Object System.Data.DataTable
$ODBCConn = New-Object System.Data.Odbc.OdbcConnection("Driver={SQLite3 ODBC Driver};Database=$Path")
$da = New-Object system.Data.Odbc.OdbcDataAdapter($sql,$ODBCConn)
$rowCount = $da.Fill($dt)
$ODBCConn.Close()
Write-Verbose "$rowcount rows"
If ($rowCount) {$dt | Add-Member -MemberType ScriptProperty -Name "Path" -Value {$this.fullname -replace "/","\"} -PassThru }
}}
}
<#
.Synopsis
Returns Collection items known to LightRoom
.EXAMPLE
Get-LightRoomCollectionItem | out-gridview
Displays all items in all collections
.EXAMPLE
Get-LightRoomCollectionItem -include music
Displays items in the music collection
.EXAMPLE
Get-LightRoomCollectionItem -include music | copy -Destination e:\raw\music
Copies the original files in the music collection
#>
function Get-LightRoomCollectionItem {[CmdletBinding()]
[OutputType([System.Data.DataRow])]
Param (
# Path to the LightRoom catalog file
$Path = $Global:catlogPath,
# Param2 Files to include
[Parameter(ValueFromPipelineByPropertyName=$true)]
[String]$Include = ""
)
Process { ForEach ($i in $Include) {
$sql = @"
SELECT Collection.name AS CollectionName ,
RootFolder.absolutePath || Folder.pathFromRoot || RootFile.baseName || '.' || RootFile.extension AS FullName
FROM AgLibraryCollection Collection
JOIN AgLibraryCollectionimage cimage ON collection.id_local = cimage.Collection
JOIN Adobe_images Image ON Image.id_local = cimage.image
JOIN AgLibraryFile RootFile ON Rootfile.id_local = image.rootFile
JOIN AgLibraryFolder Folder ON folder.id_local = RootFile.folder
JOIN AgLibraryRootFolder RootFolder ON RootFolder.id_local = Folder.rootFolder
WHERE Collection.name LIKE '$i%'
ORDER BY CollectionName, FullName
"@
$dt = New-Object System.Data.DataTable
$ODBCConn = New-Object System.Data.Odbc.OdbcConnection("Driver={SQLite3 ODBC Driver};Database=$Path")
$da = New-Object system.Data.Odbc.OdbcDataAdapter($sql,$ODBCConn)
$rowCount = $da.Fill($dt)
$ODBCConn.Close()
If ($rowCount) {$dt | Add-Member -MemberType ScriptProperty -Name "Path" -Value {$this.fullname -replace "/","\"} -PassThru }
}}
}
<#
.Synopsis
Returns Lightroom Collections
.EXAMPLE
Get-LightRoomCollection
Lists all collections
.EXAMPLE
Get-LightRoomCollection -include unsaved
Lists collections with names that begin "unsaved"
#>
function Get-LightRoomCollection {[CmdletBinding()]
[OutputType([System.Data.DataRow])]
Param(
# Path to the LightRoom catalog file
$Path = $Global:catlogPath,
# Param2 Files to include
[Parameter(ValueFromPipelineByPropertyName=$true)]
[String]$Include = ""
)
Process { ForEach ($i in $Include) {
$sql = @"
SELECT Collection.name AS CollectionName
FROM AgLibraryCollection Collection
WHERE Collection.name LIKE '$i%'
ORDER BY CollectionName
"@
$dt = New-Object System.Data.DataTable
$ODBCConn = New-Object System.Data.Odbc.OdbcConnection("Driver={SQLite3 ODBC Driver};Database=$Path")
$da = New-Object system.Data.Odbc.OdbcDataAdapter($sql,$ODBCConn)
$rowCount = $da.Fill($dt)
$ODBCConn.Close()
If ($rowCount) {$dt}
}}
}
# AgLibraryKeyword JOINs via AgLibraryKeywordImage to Adobe_images
# AgLibraryCollectionContent defines search / sort settings
# AgLibraryImport / AgLibraryImportimage
@RobertWaiteREPAY
Copy link

I love it. Thanks for sharing!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment