<commit_before>The Lightroom catalog is in a sqlite database: file *.lrcat.


This document is for Lightroom 4 and seems to be valid for Lightroom 6.

Tables
------

Common concepts:

Lots of tables contain id_local and id_global.

id_local is an integer that is supposed to be uniquely incremented
across the database.
id_global is a UUID (string) that is supposed to be unique.

Genealogy is also often found. It is a string denoting the path in the
hierarchy. Each component is the local_id, but prefix with the lenght
of the id. They are separated by '/' and starts with a '/'.

dateCreated: a time stamp of some sort. Not UNIX epoch.

## Settings

Adobe_variablesTable

This table contain settings for Lightroom. Most of it is irrelevant.
A few exceptions:

* Adobe_DBVersion:
    0300025 for Lightroom 3 (currently not supported)
    0400020 for Lightroom 4.4.1
    0600008 for Lightroom 6.0 - 6.13
* AgLibraryKeyword_rootTagID: the root keyword local_id (int as string)

## Keywords

AgLibraryKeyword: keyword definitions.

* id_local: local id
* id_global: uuid
* dateCreated: creation date timestamp
* genealogy: the hierarchy
* lc_name: the (localized?) tag name
* name: the tag nane
* parent: the parent (local_id)

## Folders

Two kinds. Root and Folders. Root are top level folders and don't have
a parent.

AgLibraryRootFolder: root folder.

* id_local: local id
* id_global: uuid
* absolutePath: Absolute path to the root folder.
* name: name of the folder.
* relativePathFromCatalog: may be NULL if on a different volume.

AgLibraryFolder: folder. Attached to a root folder.

* id_local: local id
* id_global: uuid
* pathFromRoot: path from the root folder.
* rootFolder: id of the root folder.

There is always a folder with an empty `pathFromRoot` for a root
folder (does this mean an AgLibraryFile is attached to only folders?)

## Content

Collections and folder have a "content" defined to declare what's
inside. They is a per containe type table.

This is done by defining key/values of properties. `owningValue` is
the key. `content` is a value.

common columns:
* id_local: local id
* owningModule: the key.
* content: value of the property

AgFolderContent:
* id_global: uuid
* containingFolder: the folder this content applies to
* name: ????

AgLibraryCollectionContent:
* collection: the collection this content applies to.

## Images

Adobe_Images: image. This doesn't represent physical files.

* id_local: local id
* id_global: uuid
* fileFormat: string representing the format.
              Possible values: RAW, JPG, VIDEO, DNG
* pick: (it's a float in the database) not 1 if picked, -1 if rejected, 0 if unpicked.
* rating: rating value or NULL
* rootFile: the id of the physical file (in `AgLibraryFile`)
* orientation: text marking the orientation. ex. AB, DA. May be NULL
               for video.
   Mapping to Exif values
   * AB -> 1
   * DA -> 8
   * BC -> 6
   * CD -> 3
   * Not sure if the "mirrored" orientation follow the same scheme.
* captureTime: date capture time (likely from Exif originally or as reajusted in Lr)
* masterImage: id of master if this is a copy. NULL otherwise.
* copyName: the name of the virtual copy. masterImage not NULL.

AgLibraryFile: physical files.

* id_local: local id
* id_global: uuid
* baseName: name without extension
* extension: extension
* idx_filename: index entry
* importHash: hash at import time
* md5: md5 digest
* originalFilename: filename before renaming at import time
* sidecarExtensions: extensions of sidecars. Comma separated strings.
   JPG,xmp => when RAW + JPEG with xmp sidecar.
   Can be empty

## Collections

AgLibraryCollection

* id_local: local id
* creationId: "type" of collection. Possible values:
  - com.adobe.ag.library.group
  - com.adobe.ag.library.collection
  - com.adobe.ag.library.smart_collection
* genealogy: the hierarchy
* imageCount: ???? (NULL)
* name: String name of the collection
* parent: NULL is id_local of parent
* systemOnly: (seems to only apply to the quick collection)<commit_msg>format documentation: added more details of the format both Lr4 and Lr6<commit_after>The Lightroom catalog is in a sqlite database: file *.lrcat.


This document is for Lightroom 4 and Lightroom 6.
Unless mentionned, info applies to both versions.

Tables
------

Common concepts:

Lots of tables contain id_local and id_global.

id_local is an integer that is supposed to be uniquely incremented
across the database.
id_global is a UUID (string) that is supposed to be unique.

Genealogy is also often found. It is a string denoting the path in the
hierarchy. Each component is the local_id, but prefix with the lenght
of the id. They are separated by '/' and starts with a '/'.

dateCreated: a time stamp of some sort. Not UNIX epoch.

## Settings

Adobe_variablesTable

This table contain settings for Lightroom. Most of it is irrelevant.
A few exceptions:

* Adobe_DBVersion:
    0300025 for Lightroom 3 (currently not supported)
    0400020 for Lightroom 4.4.1
    0600008 for Lightroom 6.0 - 6.13
* AgLibraryKeyword_rootTagID: the root keyword local_id (int as string)

## Keywords

AgLibraryKeyword: keyword definitions.

* id_local: local id
* id_global: uuid
* dateCreated: creation date timestamp
* genealogy: the hierarchy
* lc_name: the (localized?) tag name
* name: the tag nane
* parent: the parent (local_id)

AgLibraryKeywordImage: keyword relation with images

* id_local: local id
* image: associated image `Adobe_images`
* tag: addociated keyword `AgLibraryKeyword`

## Folders

Two kinds. Root and Folders. Root are top level folders and don't have
a parent.

AgLibraryRootFolder: root folder.

* id_local: local id
* id_global: uuid
* absolutePath: Absolute path to the root folder.
* name: name of the folder.
* relativePathFromCatalog: may be NULL if on a different volume.

AgLibraryFolder: folder. Attached to a root folder.

* id_local: local id
* id_global: uuid
* pathFromRoot: path from the root folder.
* rootFolder: id of the root folder.

There is always a folder with an empty `pathFromRoot` for a root
folder (does this mean an AgLibraryFile is attached to only folders?)

## Content

Collections and folder have a "content" defined to declare what's
inside. They is a per containe type table.

This is done by defining key/values of properties. `owningValue` is
the key. `content` is a value.

common columns:
* id_local: local id
* owningModule: the key.
* content: value of the property

AgFolderContent:
* id_global: uuid
* containingFolder: the folder this content applies to
* name: ????

AgLibraryCollectionContent:
* collection: the collection this content applies to.

## Images

Adobe_Images: image. This doesn't represent physical files.

* id_local: local id
* id_global: uuid
* fileFormat: string representing the format.
              Possible values: RAW, JPG, VIDEO, DNG
* pick: (it's a float in the database) not 1 if picked, -1 if rejected, 0 if unpicked.
* rating: rating value or NULL
* rootFile: the id of the physical file (in `AgLibraryFile`)
* orientation: text marking the orientation. ex. AB, DA. May be NULL
               for video.
   Mapping to Exif values
   * AB -> 1
   * DA -> 8
   * BC -> 6
   * CD -> 3
   * Not sure if the "mirrored" orientation follow the same scheme.
* captureTime: date capture time (likely from Exif originally or as reajusted in Lr)
* masterImage: id of master if this is a copy. NULL otherwise.
* copyName: the name of the virtual copy. masterImage not NULL.

AgLibraryFile: physical files.

* id_local: local id
* id_global: uuid
* baseName: name without extension
* extension: extension
* idx_filename: index entry
* importHash: hash at import time
* md5: md5 digest
* originalFilename: filename before renaming at import time
* sidecarExtensions: extensions of sidecars. Comma separated strings.
   JPG,xmp => when RAW + JPEG with xmp sidecar.
   Can be empty

## Collections

AgLibraryCollection - collections definitions

* id_local: local id
* creationId: "type" of collection. Possible values:
  - com.adobe.ag.library.group
  - com.adobe.ag.library.collection
  - com.adobe.ag.library.smart_collection
* genealogy: the hierarchy
* imageCount: ???? (NULL)
* name: String name of the collection
* parent: NULL is id_local of parent
* systemOnly: (seems to only apply to the quick collection)

AgLibraryCollectionImage - image to collection relation

* id_local: local id
* collection: local id of the collection
* image: local id of the image
* pick
* positionInCollection

AgLibraryCollectionCoverImage (Lr6) - The cover image for collections

* collection: local id for `AgLibraryCollection`
* collectionImage: local id for `AgLibraryCollectionImage`

## Faces (Lr 6 only)

AgLibraryFace - Define face detected.

* id_local: local id
* cluster: id of the cluster `AgLibraryFaceCluster`
* image: id of the image

AgLibraryFaceCluster - Group faces together

* id_local: local id of the cluster.
* keyFace: (NULL?)

AgLibraryFaceData - Data of each face

* id_local: local id
* face: local id of the face.
* data: blob. Apparently JP2.

AgLibraryKeywordFace - Face to keyword equivalence

* id_local: local id
* face: local id of the face.
* tag: local id of the keyword tag.
* userPick: user said yes.
* userReject: (rejected by user?)
* keyFace: (NULL?)
* rankOrder: ?
{'fraction_non_alphanumeric': 0.07267441860465117, 'fraction_numerical': 0.009366925064599484, 'mean_word_length': 4.021081081081081, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1313028', 'n_tokens_mistral': 2809, 'n_tokens_neox': 2710, 'n_words': 1247}