Created
April 18, 2020 11:54
-
-
Save jhoneill/a902c37d2c239584461af3ccecae44c9 to your computer and use it in GitHub Desktop.
PowerShell Script to explore Adobe Lightroom catalogs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
########################################################## | |
# # | |
# 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I love it. Thanks for sharing!