{"id":221,"date":"2006-07-29T03:53:37","date_gmt":"2006-07-28T18:53:37","guid":{"rendered":"https:\/\/regex.info\/blog\/2006-07-29\/221"},"modified":"2007-06-30T20:25:42","modified_gmt":"2007-06-30T11:25:42","slug":"accessing-lightrooms-sqlite-db-directly","status":"publish","type":"post","link":"https:\/\/regex.info\/blog\/2006-07-29\/221","title":{"rendered":"Accessing Lightroom&#8217;s SQLite DB Directly"},"content":{"rendered":"\n<p>(This post is mostly for the search engines, to help others looking for the solution that this post provides)<\/p>\n\n<div style='border: solid 1px red; margin: 20px 50px; padding: 10px 30px'>\nNote that some of the details presented in this post, such as the database\nfilename and the actual schema itself, are for the &#8220;beta 3&#8221; version of\nLightroom, and don't apply to <a\nhref=\"\/blog\/2007-01-29\/377\">Lightroom <span class='nobr'>Version 1.0<\/span><\/a> or later.\n<\/div>\n\n<p><a href=\"http:\/\/www.adobe.com\/products\/photoshoplightroom\/\">Lightroom<\/a>,\nAdobe's high-volume photo workflow application, stores all kinds of\nuser-preference and per-image data in an <a\nhref=\"http:\/\/www.sqlite.org\/\">SQLite<\/a> database. <span class='nobr'>As of the<\/span> Beta 3\nrelease, the database is stored in one file, the Mac filename being\n<span class='nobr'>&#8220;<b>\/Users\/<i>username<\/i>\/Pictures\/Lightroom\/Lightroom B3\nLibrary.aglib<\/b>&#8221;<\/span>\n\n<\/p><p>\n\nIf you're technically-minded and want to peek at the database directly, you\nmight try to access it with the <b>sqlite3<\/b> command, which comes\nstandard with OSX, and be disappointed to find that it doesn't work: <\/p>\n\n<style type=\"text\/css\">pre {padding:10px; margin-left:30px; background: #555}<\/style>\n<pre>\n$ <b>sqlite3 'Lightroom B3 Library.aglib'<\/b>\nSQLite version 3.1.3\nEnter \".help\" for instructions\nsqlite> <b>.schema<\/b>\nError: unsupported file format\n<\/pre>\n\n<p><a href=\"http:\/\/blog.ericscouten.com\/\">Eric Scouten<\/a> provided the answer in <a\nhref=\"http:\/\/www.adobe.com\/cfusion\/webforums\/forum\/messageview.cfm?catid=589&amp;threadid=1177025\">this\nthread<\/a> on the Adobe's Lightroom Beta discussion forum. <span class='nobr'>It turns<\/span> out\nthat Lightroom uses features of SQLite introduced in its version 3.2. OSX\n(as of 10.4.7) ships with SQLite version 3.1.3, and so it can't read Lightroom's database.<\/p>\n\n<p>If you have Apple's <a\nhref=\"http:\/\/developer.apple.com\/referencelibrary\/GettingStarted\/GS_MacOSX\/index.html\">XCode\ntools<\/a> installed, you can download the source from <a\nhref=\"http:\/\/www.sqlite.org\/download.html\">sqlite.org<\/a> and build <span class='nobr'>a more<\/span>\nrecent version (3.3.6 is the latest as of this writing), and then it all\nworks:<\/p>\n   \n<pre>\n<b>sqlite3 'Lightroom B3 Library.aglib'<\/b>\nSQLite version 3.3.6\nEnter &#8220;.help&#8221; for instructions\nsqlite> <b>.schema<\/b>\n<span style='font-size:80%; line-height: 90%'>CREATE TABLE Adobe_AdditionalMetadata (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    image INTEGER,\n    metadataPresetID,\n    xmp\n);\nCREATE TABLE Adobe_FTPPresets (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    passive,\n    password,\n    path,\n    port,\n    protocol,\n    server,\n    title,\n    username\n);\nCREATE TABLE Adobe_cameraRawSettings (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    name,\n    rawSettings UNIQUE\n);\nCREATE TABLE Adobe_imageDevelopSettings (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    croppedHeight,\n    croppedWidth,\n    digest,\n    fileHeight,\n    fileWidth,\n    grayscale INTEGER,\n    historySettingsID,\n    image INTEGER,\n    settingsID,\n    text,\n    whiteBalance\n);\nCREATE TABLE Adobe_imageFiles (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    absolutePath NOT NULL DEFAULT '',\n    idx_filename NOT NULL DEFAULT '',\n    image INTEGER NOT NULL DEFAULT 0,\n    importHash,\n    lc_idx_filename NOT NULL DEFAULT '',\n    markedMissing,\n    md5,\n    modTime,\n    relativePath NOT NULL DEFAULT '',\n    robustRepresentation\n);\nCREATE TABLE Adobe_imageProperties (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    image INTEGER,\n    propertiesString\n);\nCREATE TABLE Adobe_images (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    aspectRatioCache,\n    captureTime,\n    developSettingsIDCache,\n    fileHeight,\n    fileWidth,\n    hidden INTEGER NOT NULL DEFAULT 0,\n    importedTime,\n    orientation,\n    originalCaptureTime,\n    originalRootEntity INTEGER,\n    panningDistanceH,\n    panningDistanceV,\n    photoBinNominators INTEGER,\n    photoBinPosition,\n    printSharpening,\n    propertiesCache,\n    pyramidIDCache,\n    rating,\n    rawSettings INTEGER,\n    rootFile INTEGER,\n    sidecarStatus\n);\nCREATE TABLE Adobe_libraryImageSavedSettings3 (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    dateCreated INTEGER,\n    image INTEGER,\n    isUserCheckpoint INTEGER NOT NULL DEFAULT 0,\n    name,\n    text,\n    uuid\n);\nCREATE TABLE Adobe_namedIdentityPlate (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    description,\n    identityPlate,\n    identityPlateHash,\n    moduleFont,\n    moduleSelectedTextColor,\n    moduleTextColor\n);\nCREATE TABLE Adobe_oldMetadataCache (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    image INTEGER,\n    xmp\n);\nCREATE TABLE Adobe_variables (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    name,\n    value\n);\nCREATE TABLE Adobe_variablesTable (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    name UNIQUE,\n    value NOT NULL DEFAULT ''\n);\nCREATE TABLE AgLibraryContent (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    containingTag INTEGER,\n    content,\n    owningModule\n);\nCREATE TABLE AgLibraryImageSearchIndex (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    dirty INTEGER NOT NULL DEFAULT 0,\n    image INTEGER NOT NULL DEFAULT 0,\n    searchIndex\n);\nCREATE TABLE AgLibrarySpecialCollectionInfo (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    kind UNIQUE NOT NULL DEFAULT '',\n    sortDirection,\n    sortOrder\n);\nCREATE TABLE AgLibraryTag (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    dateCreated NOT NULL DEFAULT '',\n    flattenedParams,\n    genealogy NOT NULL DEFAULT '',\n    imageCountFlag INTEGER NOT NULL DEFAULT 1,\n    kindName NOT NULL DEFAULT '',\n    lastApplied,\n    name,\n    parent INTEGER,\n    sortDirection,\n    sortOrder\n);\nCREATE TABLE AgLibraryTagImage (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    flagged,\n    flattenedParams,\n    image INTEGER NOT NULL DEFAULT 0,\n    positionInCollection NOT NULL DEFAULT 'z',\n    tag INTEGER NOT NULL DEFAULT 0,\n    tagKind NOT NULL DEFAULT ''\n);\nCREATE TABLE AgLibraryTagSearchWord (\n    id_local INTEGER PRIMARY KEY,\n    id_global UNIQUE NOT NULL,\n    implied INTEGER NOT NULL DEFAULT 0,\n    tag INTEGER NOT NULL DEFAULT 0,\n    word NOT NULL DEFAULT ''\n);\n<\/span>\nsqlite> <b>.quit<\/b>\n$\n\n<\/pre>\n\n<p>It would be prudent at this point to quote directly from Eric's post:<\/p>\n\n<style type=\"text\/css\">.bq {font-size: 300%; font-family:times}<\/style>\n\n<table border='0' style='margin-left:40px' cellspacing='5' cellpadding='0' width='75%'>\n<tr valign='bottom'>\n  <td width='30' align='right'><span class='bq'>&#8220;<\/span><\/td>\n  <td align='left'>Of course, any mention of accessing the database file directly should come\n  with the standard disclaimer that you're doing so at your own risk and you\n  should make backup copies.<\/td>\n  <td valign='bottom' align='right'><span class='bq'>&#8221;<\/span><\/td>\n<\/tr><\/table>\n\n\n\n\n\n\n\n","protected":false},"excerpt":{"rendered":"<p>(This post is mostly for the search engines, to help others looking for the solution that this post provides)<\/p> <p>Lightroom, Adobe's high-volume photo workflow application, stores all kinds of user-preference and per-image data in an SQLite database. As of the Beta 3 release, the database is stored in one file, the Mac filename being \"<b>\/Users\/username\/Pictures\/Lightroom\/Lightroom B3 Library.aglib<\/b>\" <\/p><p> If you're technically-minded and want to peek at the database directly, you might try to access it with the <b>sqlite3<\/b> command, which comes standard with OSX, and be disappointed to find that it doesn't work: <\/p> <p>Eric Scouten provided the answer in [...]","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,13,4],"tags":[],"_links":{"self":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/posts\/221"}],"collection":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/comments?post=221"}],"version-history":[{"count":0,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/posts\/221\/revisions"}],"wp:attachment":[{"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/media?parent=221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/categories?post=221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regex.info\/blog\/wp-json\/wp\/v2\/tags?post=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}