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