PowerShell Script to explore Adobe Lightroom catalogs

lightroom.ps1

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

I love it. Thanks for sharing!