LoginSignup
0
0

More than 1 year has passed since last update.

@makainuma

Lightroom カタログを解析して欲しい情報を取り出す

Last updated at Posted at 2022-03-15

intro

古いLightroomをずっと使っていたが、そろそろ、いよいよの時に備えて中の情報を引っ張り出せるようにしておこうと、ちょっと調査。

私の場合は、フィルムカメラの写真に対して、カメラ、レンズ、フィルムなどの情報をキーワードで持たせていたので、これはサルベージしておかなないとなにがなんだかわからなくなる。また、そこそこイケてる写真だけ旗つけて、星の数で成績つけて、普段はそれらだけみていたので、その辺の情報も抜いておける必要がある。
EXIF関係はどうせ画像から取れるので、今は気にしない。

exp

テーブル構造

Lightroomのカタログファイルlrcatは、実はただのSQLiteファイルなのでSQLiteコマンドでいくらでも中は確認できる。
構造把握を目的に、ファイルを3つだけ登録したカタログを作ってSQLiteコマンドで中を見てみた。

とりあえず、.tablesコマンドでSQLiteの場合はテーブル一覧が取れる。

$ sqlite3 lr_exp/lr_exp.lrcat
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .tables
Adobe_AdditionalMetadata               
Adobe_faceProperties                   
Adobe_imageDevelopBeforeSettings       
Adobe_imageDevelopSettings             
Adobe_imageProofSettings               
Adobe_imageProperties                  
Adobe_images                           
Adobe_libraryImageDevelopHistoryStep   
Adobe_libraryImageDevelopSnapshot      
Adobe_libraryImageFaceProcessHistory   
Adobe_namedIdentityPlate               
Adobe_variables                        
Adobe_variablesTable                   
AgDNGProxyInfo                         
AgDNGProxyInfoUpdater                  
AgDeletedOzAlbumAssetIds               
AgDeletedOzAlbumIds                    
AgDeletedOzAssetIds                    
AgDeletedOzSpaceIds                    
AgFolderContent                        
AgHarvestedDNGMetadata                 
AgHarvestedExifMetadata                
AgHarvestedIptcMetadata                
AgHarvestedMetadataWorklist            
AgInternedExifCameraModel              
AgInternedExifCameraSN                 
AgInternedExifLens                     
AgInternedIptcCity                     
AgInternedIptcCountry                  
AgInternedIptcCreator                  
AgInternedIptcIsoCountryCode           
AgInternedIptcJobIdentifier            
AgInternedIptcLocation                 
AgInternedIptcState                    
AgLastCatalogExport                    
AgLibraryCollection                    
AgLibraryCollectionChangeCounter       
AgLibraryCollectionContent             
AgLibraryCollectionCoverImage          
AgLibraryCollectionImage               
AgLibraryCollectionImageChangeCounter  
AgLibraryCollectionImageOzAlbumAssetIds
AgLibraryCollectionImageOzSortOrder    
AgLibraryCollectionOzAlbumIds          
AgLibraryCollectionStack               
AgLibraryCollectionStackData           
AgLibraryCollectionStackImage          
AgLibraryCollectionSyncedAlbumData     
AgLibraryCollectionTrackedAssets       
AgLibraryFace                          
AgLibraryFaceCluster                   
AgLibraryFaceData                      
AgLibraryFile                          
AgLibraryFileAssetMetadata             
AgLibraryFolder                        
AgLibraryFolderStack                   
AgLibraryFolderStackData               
AgLibraryFolderStackImage              
AgLibraryIPTC                          
AgLibraryImageChangeCounter            
AgLibraryImageOzAssetIds               
AgLibraryImageSearchData               
AgLibraryImageSyncedAssetData          
AgLibraryImageXMPUpdater               
AgLibraryImport                        
AgLibraryImportImage                   
AgLibraryKeyword                       
AgLibraryKeywordCooccurrence           
AgLibraryKeywordFace                   
AgLibraryKeywordImage                  
AgLibraryKeywordPopularity             
AgLibraryKeywordSynonym                
AgLibraryOzCommentIds                  
AgLibraryOzFavoriteIds                 
AgLibraryOzFeedbackInfo                
AgLibraryPublishedCollection           
AgLibraryPublishedCollectionContent    
AgLibraryPublishedCollectionImage      
AgLibraryRootFolder                    
AgLibraryUpdatedImages                 
AgMRULists                             
AgMetadataSearchIndex                  
AgOutputImageAsset                     
AgOzSpaceAlbumIds                      
AgOzSpaceIds                           
AgPendingOzAlbumAssetIds               
AgPendingOzAssetBinaryDownloads        
AgPendingOzAssets                      
AgPhotoComment                         
AgPhotoProperty                        
AgPhotoPropertyArrayElement            
AgPhotoPropertySpec                    
AgPublishListenerWorklist              
AgRemotePhoto                          
AgSearchablePhotoProperty              
AgSearchablePhotoPropertyArrayElement  
AgSourceColorProfileConstants          
AgSpecialSourceContent                 
AgTempImages                           
AgUnsupportedOzAssets                  
AgVideoInfo                            
LrMobileSyncChangeCounter

ぱっと見、大量にあるが、重要なのは以下のもの

Adobe_images: 画像の管理情報本体
AgLibraryFile: 画像に対するファイル情報(ファイル名、拡張子など)
AgLibraryFolder: ファイルのディレクトリネーム部（ある起点からの相対パス）
AgLibraryRootFolder: 起点のフルパス
AgLibraryKeyword: キーワード
AgLibraryKeywordImage: キーワードと画像を対応づけるピボットテーブル

.schemaコマンドとselectで中の構造を見てみる。

Adobe_images

sqlite> .schema Adobe_images
CREATE TABLE Adobe_images (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    aspectRatioCache NOT NULL DEFAULT -1,
    bitDepth NOT NULL DEFAULT 0,
    captureTime,
    colorChannels NOT NULL DEFAULT 0,
    colorLabels NOT NULL DEFAULT '',
    colorMode NOT NULL DEFAULT -1,
    copyCreationTime NOT NULL DEFAULT -63113817600,
    copyName,
    copyReason,
    developSettingsIDCache,
    fileFormat NOT NULL DEFAULT 'unset',
    fileHeight,
    fileWidth,
    hasMissingSidecars INTEGER,
    masterImage INTEGER,
    orientation,
    originalCaptureTime,
    originalRootEntity INTEGER,
    panningDistanceH,
    panningDistanceV,
    pick NOT NULL DEFAULT 0,
    positionInFolder NOT NULL DEFAULT 'z',
    propertiesCache,
    pyramidIDCache,
    rating,
    rootFile INTEGER NOT NULL DEFAULT 0,
    sidecarStatus,
    touchCount NOT NULL DEFAULT 0,
    touchTime NOT NULL DEFAULT 0
);
CREATE INDEX index_Adobe_images_rootFile ON Adobe_images( rootFile );
CREATE INDEX index_Adobe_images_ratingAndCaptureTime ON Adobe_images( rating, captureTime );
CREATE INDEX index_Adobe_images_originalRootEntity ON Adobe_images( originalRootEntity );
CREATE INDEX index_Adobe_images_masterImage ON Adobe_images( masterImage );
CREATE INDEX index_Adobe_images_captureTime ON Adobe_images( captureTime );
sqlite> select * from Adobe_images;
34|33181815-1BC8-466B-A619-D4D1FB6B6900|1.33333333333333|8.0|2022-02-23T12:34:49.11|3.0|グリーン|2.0|-63113817600.0|||40.0|JPG|3888.0|5184.0|||AB|||||1.0|z|46.0|none|3.0|35|1.0|2.0|668776062.218388
50|34B7BF05-BB21-4B3D-9FF7-2C574CC063E2|0.662337662337662|12.0|2022-02-27T14:10:27|3.0|グリーン|32803.0|-63113817600.0|||60.0|DNG|3264.0|4928.0|||DA|||||-1.0|z|67.0|none|4.0|51|1.0|3.0|668776058.608349
72|4804C9DE-DEAB-43AA-BC15-C79361A9696B|1.5087890625|8.0|2022-03-01T19:01:58|3.0|パープル|2.0|-63113817600.0|||78.0|JPG|2048.0|3090.0|||AB|1970-01-01T00:00:00||||1.0|z|83.0|none||73|1.0|3.0|668776055.257344

カラーラベルが何故か文字列でそのまま入っている（colorLabels）。
pickとratingがそれぞれ旗と星。
rootFileがちょっとわかりにくいが、これがAgLibraryFileのid_localで、これで連結できる。

AgLibraryFile

sqlite> .schema AgLibraryFile
CREATE TABLE AgLibraryFile (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    baseName NOT NULL DEFAULT '',
    errorMessage,
    errorTime,
    extension NOT NULL DEFAULT '',
    externalModTime,
    folder INTEGER NOT NULL DEFAULT 0,
    idx_filename NOT NULL DEFAULT '',
    importHash,
    lc_idx_filename NOT NULL DEFAULT '',
    lc_idx_filenameExtension NOT NULL DEFAULT '',
    md5,
    modTime,
    originalFilename NOT NULL DEFAULT '',
    sidecarExtensions
);
CREATE UNIQUE INDEX index_AgLibraryFile_nameAndFolder ON AgLibraryFile( lc_idx_filename, folder );
CREATE INDEX index_AgLibraryFile_folder ON AgLibraryFile( folder );
CREATE INDEX index_AgLibraryFile_importHash ON AgLibraryFile( importHash );
CREATE TRIGGER trigger_AgDNGProxyInfo_fileDeleted
											AFTER DELETE ON AgLibraryFile
											FOR EACH ROW
											BEGIN
												UPDATE AgDNGProxyInfo
												SET status = 'D', statusDateTime = datetime( 'now' )
												WHERE fileUUID = OLD.id_global;
											END;
CREATE TRIGGER trigger_AgDNGProxyInfo_fileInserted
						   					 AFTER INSERT ON AgLibraryFile
											 FOR EACH ROW
											 BEGIN
												UPDATE AgDNGProxyInfo
												SET status = 'U', statusDateTime = datetime( 'now' )
												WHERE fileUUID = NEW.id_global;
											END;
sqlite> select * from AgLibraryFile;
35|260EC0D7-91F1-4EFA-BE7A-36FA873D7D1D|IMG_5916|||JPG|667280088.0|31|IMG_5916.JPG||img_5916.jpg|jpg||667280088.0|IMG_5916.JPG|
51|B36FAF57-DB7D-4D4C-A771-6141D8407EBC|IMGP8927|||DNG|667631428.0|49|IMGP8927.DNG||imgp8927.dng|dng||667631428.0|IMGP8927.DNG|JPG
73|3A608CC2-F1D1-4FC5-B725-89EDA858497A|22180002|||JPG|667821718.0|70|22180002.JPG||22180002.jpg|jpg||667821718.0|22180002.JPG|

ファイル名（basename）が入っている。そのほか拡張子やらsidecarやらいろいろあるが、今は不要。
folderがAgLibraryFolderのid_localのはず。

AgLibraryFolder

sqlite> .schema AgLibraryFolder
CREATE TABLE AgLibraryFolder (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    pathFromRoot NOT NULL DEFAULT '',
    rootFolder INTEGER NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX index_AgLibraryFolder_rootFolderAndPath ON AgLibraryFolder( rootFolder, pathFromRoot );
sqlite> select * from AgLibraryFolder;
30|DB4E3531-7B96-4787-BBCB-561F0437684F||29
31|FF8DF976-78AD-42FD-BE61-D683DCD1F522|2022/02/23/|29
32|2D88FA00-3BE5-44EA-A654-B4CFA44D2108|2022/02/|29
33|485ECD43-F63E-4CE7-AE5E-82A65F31654E|2022/|29
49|DD08570C-5021-4860-963F-3AF302132677|2022/02/27/|29
70|FDC646A8-3FFF-4874-B119-02F1BF02ADB3|2022/03/01/|29
71|86FA2D0A-CEA2-4AE6-8295-51DF56921B48|2022/03/|29

こちらは、使われないものも含めてディレクトリ（Lightroomの都合で年月日）を様々持っている。
rootFolderがAgLibraryRootFolderのid_local。

AgLibraryRootFolder

sqlite> .schema AgLibraryRootFolder
CREATE TABLE AgLibraryRootFolder (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    absolutePath UNIQUE NOT NULL DEFAULT '',
    name NOT NULL DEFAULT '',
    relativePathFromCatalog
);
sqlite> select * from AgLibraryRootFolder;
29|6693A5CB-A878-4814-A67B-1CABA4F28068|/Users/makainuma/Pictures/|Pictures|../../../Pictures/

こちらは、フォルダパスの起点のフルパス。

以上、joinすれば以下のようになる。

sqlite> SELECT AgLibraryRootFolder.absolutePath,
   ...> AgLibraryFolder.pathFromRoot,
   ...> AgLibraryFile.originalFilename,
   ...> Adobe_images.rating,
   ...> Adobe_images.pick,
   ...> Adobe_images.colorLabels
   ...> FROM Adobe_images 
   ...> INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local
   ...> INNER JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local 
   ...> INNER JOIN AgLibraryRootFolder ON AgLibraryFolder.rootFolder = AgLibraryRootFolder.id_local;
/Users/makainuma/Pictures/|2022/02/23/|IMG_5916.JPG|3.0|1.0|グリーン
/Users/makainuma/Pictures/|2022/02/27/|IMGP8927.DNG|4.0|-1.0|グリーン
/Users/makainuma/Pictures/|2022/03/01/|22180002.JPG||1.0|パープル

AgLibraryKeyword

sqlite> .schema AgLibraryKeyword
CREATE TABLE AgLibraryKeyword (
    id_local INTEGER PRIMARY KEY,
    id_global UNIQUE NOT NULL,
    dateCreated NOT NULL DEFAULT '',
    genealogy NOT NULL DEFAULT '',
    imageCountCache DEFAULT -1,
    includeOnExport INTEGER NOT NULL DEFAULT 1,
    includeParents INTEGER NOT NULL DEFAULT 1,
    includeSynonyms INTEGER NOT NULL DEFAULT 1,
    keywordType,
    lastApplied,
    lc_name,
    name,
    parent INTEGER
);
CREATE INDEX index_AgLibraryKeyword_genealogy ON AgLibraryKeyword( genealogy );
CREATE INDEX index_AgLibraryKeyword_parentAndLcName ON AgLibraryKeyword( parent, lc_name );
sqlite> select * from AgLibraryKeyword;
218|A7C0C6E9-F46E-4517-AEA2-EB51D4DE6ABD|668523074.058827|/3218||1|1|1|||||
219|D0E0B761-685F-4E63-9548-2054D97BBF7E|668523074.127923|/3218/3219||1|1|1||668523074.612765|contax rts|CONTAX RTS|218
220|D0F0F27F-6F0C-4310-AB9E-3237F1F41BAE|668523074.197048|/3218/3220||1|1|1||668523074.474512|planar 50mmf1.4|Planar 50mmF1.4|218
221|07A1391D-2922-4037-B670-9F5DF6A15294|668523074.266056|/3218/3221||1|1|1||668523074.336434|superia premium 400|Superia Premium 400|218

大文字小文字の表記ゆれ対策でlc_nameとnameと二つ持っていることがわかる。
これは、ではどうやって画像と紐づけているのかというと次。

AgLibraryKeywordImage

sqlite> .schema AgLibraryKeywordImage
CREATE TABLE AgLibraryKeywordImage (
    id_local INTEGER PRIMARY KEY,
    image INTEGER NOT NULL DEFAULT 0,
    tag INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX index_AgLibraryKeywordImage_tag ON AgLibraryKeywordImage( tag );
CREATE INDEX index_AgLibraryKeywordImage_image ON AgLibraryKeywordImage( image );
sqlite> select * from AgLibraryKeywordImage;
222|72|221
224|72|220
226|72|219

imageが画像の方のid_localでtagがAgLibraryKeywordのAgLibraryKeyword。

画像とキーワードが多対多の関係で、本来なら右にOUTER JOINしたいところだが、

RIGHT and FULL OUTER JOINs are not currently supported

と言われてしまうので、諦めてLEFT OUTER JOINしてみる。

sqlite> SELECT Adobe_images.id_local, AgLibraryKeyword.name
   ...> from Adobe_images
   ...> LEFT OUTER JOIN AgLibraryKeywordImage ON Adobe_images.id_local = AgLibraryKeywordImage.image
   ...> INNER JOIN AgLibraryKeyword ON AgLibraryKeywordImage.tag = AgLibraryKeyword.id_local;
72|Superia Premium 400
72|Planar 50mmF1.4
72|CONTAX RTS

悪魔の発明group_concat()を使って

sqlite> SELECT
   ...> AgLibraryFolder.pathFromRoot,
   ...> AgLibraryFile.originalFilename,
   ...> group_concat(AgLibraryKeyword.name)
   ...> from Adobe_images
   ...> LEFT OUTER JOIN AgLibraryKeywordImage ON Adobe_images.id_local = AgLibraryKeywordImage.image
   ...> INNER JOIN AgLibraryKeyword ON AgLibraryKeywordImage.tag = AgLibraryKeyword.id_local
   ...> INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local
   ...> INNER JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local
   ...> GROUP BY Adobe_images.id_local;
2022/03/01/|22180002.JPG|Superia Premium 400,Planar 50mmF1.4,CONTAX RTS
2022/03/01/|22180023.JPG|Superia Premium 400,Tessar 45mmF2.8,CONTAX RTS

ま、こんなもんでしょう

追加

キーワードに対して、それがつけられている画像一覧（id_local）

SELECT
AgLibraryKeywordImage.tag,AgLibraryKeyword.name,group_concat(AgLibraryKeywordImage.image)
from AgLibraryKeywordImage
INNER JOIN AgLibraryKeyword ON AgLibraryKeywordImage.tag = AgLibraryKeyword.id_local
GROUP BY AgLibraryKeywordImage.tag;

画像idとファイル名の対応

SELECT Adobe_images.id_local,
AgLibraryFolder.pathFromRoot,
AgLibraryFile.originalFilename
FROM Adobe_images 
INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local
INNER JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local;

キーワードに対するファイル名の一覧

sqlite> SELECT
   ...> AgLibraryKeywordImage.tag,AgLibraryKeyword.name,AgLibraryKeywordImage.image,
   ...> group_concat(AgLibraryFolder.pathFromRoot || AgLibraryFile.originalFilename)
   ...> from AgLibraryKeywordImage
   ...> INNER JOIN AgLibraryKeyword ON AgLibraryKeywordImage.tag = AgLibraryKeyword.id_local
   ...> INNER JOIN Adobe_images ON AgLibraryKeywordImage.image = Adobe_images.id_local
   ...> INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local
   ...> INNER JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local
   ...> GROUP BY AgLibraryKeywordImage.tag;
219|CONTAX RTS|240|2022/03/01/22180002.JPG,2022/03/01/22180023.JPG
220|Planar 50mmF1.4|72|2022/03/01/22180002.JPG
221|Superia Premium 400|240|2022/03/01/22180002.JPG,2022/03/01/22180023.JPG
255|Tessar 45mmF2.8|240|2022/03/01/22180023.JPG

追加2

コレクション

コレクションについても調べておく。ざっと見、AgLibraryCollectionとAgLibraryCollectionImageで構成されているようである。

sqlite> .schema AgLibraryCollection
CREATE TABLE AgLibraryCollection (
    id_local INTEGER PRIMARY KEY,
    creationId NOT NULL DEFAULT '',
    genealogy NOT NULL DEFAULT '',
    imageCount,
    name NOT NULL DEFAULT '',
    parent INTEGER,
    systemOnly NOT NULL DEFAULT ''
);
CREATE INDEX index_AgLibraryCollection_parentAndName ON AgLibraryCollection( parent, name );
CREATE INDEX index_AgLibraryCollection_genealogy ON AgLibraryCollection( genealogy );
CREATE TRIGGER trigger_AgLibraryCollectionCoverImage_delete_collection
	AFTER DELETE on AgLibraryCollection
	FOR EACH ROW
	BEGIN

		DELETE FROM AgLibraryCollectionCoverImage
		WHERE collection = OLD.id_local;

	END;
sqlite> select * from AgLibraryCollection;
2|com.adobe.ag.library.collection|/12||quick collection||1.0
262|com.adobe.ag.library.collection|/3262||collection1||0.0
266|com.adobe.ag.library.group|/3266||collectionS||0.0
267|com.adobe.ag.library.collection|/3266/3267||collection2|266|0.0

sqlite> .schema AgLibraryCollectionImage
CREATE TABLE AgLibraryCollectionImage (
    id_local INTEGER PRIMARY KEY,
    collection INTEGER NOT NULL DEFAULT 0,
    image INTEGER NOT NULL DEFAULT 0,
    pick NOT NULL DEFAULT 0,
    positionInCollection
);
CREATE INDEX index_AgLibraryCollectionImage_collection ON AgLibraryCollectionImage( collection );
CREATE INDEX index_AgLibraryCollectionImage_imageCollection ON AgLibraryCollectionImage( image, collection );
CREATE TRIGGER trigger_AgLibraryCollectionCoverImage_delete_collectionImage
	AFTER DELETE on AgLibraryCollectionImage
	FOR EACH ROW
	BEGIN


		DELETE FROM AgLibraryCollectionCoverImage
		WHERE collection = OLD.collection AND collectionImage = OLD.id_local;

	END;
sqlite> select * from AgLibraryCollectionImage;
268|267|34|0.0|1.0
272|262|240|0.0|
274|267|72|0.0|
275|267|240|0.0|

コレクションセットの階層構造をSQLで再現するのは難しそうなので一旦諦めて、
コレクション名とファイル名の連結だけやってみる

sqlite> SELECT AgLibraryCollection.name,
   ...> AgLibraryFile.originalFilename
   ...> FROM AgLibraryCollectionImage
   ...> INNER JOIN AgLibraryCollection ON AgLibraryCollectionImage.collection = AgLibraryCollection.id_local 
   ...> INNER JOIN Adobe_images ON AgLibraryCollectionImage.image = Adobe_images.id_local
   ...> INNER JOIN AgLibraryFile ON Adobe_images.rootFile = AgLibraryFile.id_local
   ...> INNER JOIN AgLibraryFolder ON AgLibraryFile.folder = AgLibraryFolder.id_local
   ...> ORDER BY AgLibraryCollection.name;
collection1|22180023.JPG
collection2|IMG_5916.JPG
collection2|22180002.JPG
collection2|22180023.JPG
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0