Which table parameter stores the image file size in .lrcat dbase?

Status
Not open for further replies.

achrysos

Member
Joined
May 12, 2008
Messages
47
Location
Cheshire, UK
Lightroom Experience
Advanced
Lightroom Version
I'm sorry if this is the wrong discussion forum to post this question to, but it's the most appropriate one I could see.

I am trying to use some python and sql to extract different aspects of my LR catalogue. Does anybody know how I can extract the file size of the images in my database, and keep a track of how the file type of each image (I am able to extract the file types, e.g. dng, jpg, etc, and count them)? Basically, I already have a count of each image file type, I now want to determine the volume (GB) taken up by each file type.

I know that there are some gurus around that play with the LR database, so I'd appreciate any help or pointers.

I note that the metadata panel does display the file size. If this is not stored in the database, does LR then just extract the file size for each image from the filesystem? (my backup plan!)

Thank you,
Antonio
 
Joined
Jan 18, 2009
Messages
2,502
Location
Fort Myers, FL
Lightroom Experience
Advanced
Lightroom Version
Classic
I'm not sure it is kept in there. The AgLibraryFile is where the basic file information is kept, and I would have expected to find it there if stored. I did not check every other table, but ones like Adobe_images (and related) have virtual images involved as well.

It's pretty straightforward (but slow) to go through all the files, group by folder or type or whatever, then use the OS to get the file's characteristics and add them up. But a lot slower of course than just hitting the database. You would have to decide how to deal with associated files such as XMP, WAV, JPG (I think but am not sure that a jpg becomes a sidecar when they are grouped together with the preference option). You'd need to iterate over the (comma separated) sidecars and check each one.
 

achrysos

Member
Joined
May 12, 2008
Messages
47
Location
Cheshire, UK
Lightroom Experience
Advanced
Lightroom Version
I'm not sure it is kept in there. The AgLibraryFile is where the basic file information is kept, and I would have expected to find it there if stored. I did not check every other table, but ones like Adobe_images (and related) have virtual images involved as well.

Thank you Linwood. I looked, and AgLibraryFile doesn't have the file size. Lots of other things but not the file size. Now, AgLibraryFileAssetMetadata does have 'fileSize' as a table entry, but I'm not sure it's what I'm looking for as there aren't enough entries in that table to match the number of images in my catalogue. So I'm not sure what that is a fileSize for - the other entries in that table are 'fileId' and 'sha256'.
 

PhilBurton

Lightroom enthusiast (but still learning)
Premium Classic Member
Joined
Nov 16, 2015
Messages
2,702
Location
California, USA
Lightroom Experience
Intermediate
Lightroom Version
Classic
Thank you Linwood. I looked, and AgLibraryFile doesn't have the file size. Lots of other things but not the file size. Now, AgLibraryFileAssetMetadata does have 'fileSize' as a table entry, but I'm not sure it's what I'm looking for as there aren't enough entries in that table to match the number of images in my catalogue. So I'm not sure what that is a fileSize for - the other entries in that table are 'fileId' and 'sha256'.
I am not a developer but I've been around enough software projects and software developers to pick up on issues. For Lightroom and sqlite the big issue is that there is a lot of technical debt with the database. If you search in this forum and others on this topic you will find warnings against going into the database directly as opposed to working through Lightroom as the GUI, if you will for the database.

You might be better off working with the text utility and scanning XMP sidecar files for the relevant attribute entries to accomplish your goal.

Phil
 
Joined
Jan 18, 2009
Messages
2,502
Location
Fort Myers, FL
Lightroom Experience
Advanced
Lightroom Version
Classic
Thank you Linwood. I looked, and AgLibraryFile doesn't have the file size. Lots of other things but not the file size. Now, AgLibraryFileAssetMetadata does have 'fileSize' as a table entry, but I'm not sure it's what I'm looking for as there aren't enough entries in that table to match the number of images in my catalogue. So I'm not sure what that is a fileSize for - the other entries in that table are 'fileId' and 'sha256'.
Yeah, I had looked and saw it wasn't there, my point was I think that is where it would be if it was in the file.

The AgLibraryFileAssetMetadata appears to be related to Lightroom Mobile. It only includes files that have been synced, at least that is what appears to happen just from trial and error. But if true, that's not a viable way to get the size (though to be fair, it does include the size once you sync).

My suggestion is just step through the files from the catalog, form the complete path (work your way up the folder and root), then in the calling langage call the OS to get the file's size, and total them in the calling program. You can sort in the SQL any way you want and group in the calling language. Or you could group by folder in the SQL, and then let the OS give you the size of all files in the folder -- that might be quicker depending on language and OS than asking for each file individually.
 
Joined
Oct 8, 2007
Messages
5,980
Location
London
Lightroom Experience
Power User
If the OP insists on using Python and interrogating the SQL, plugins are of little use.

Plugins can access built-in Lightroom functions which make getting the file size is as trivial as this - photo:getRawMetadata( "fileSize" ). Once you've learned Lua, I should add! So my own ListView produces a tabular view and can access the file size. Another one is FolderSize which is more focussed on folders but can also switch to files.

But Antonio obviously has a reason for not using built-in methods!

John
 

achrysos

Member
Joined
May 12, 2008
Messages
47
Location
Cheshire, UK
Lightroom Experience
Advanced
Lightroom Version
Thank you for the feedback, Linwood.

Hi John. Thanks for the tips. My only reason for wanting to do this via a script is for a personal project. I use GeekTools to put useful information on my desktop. I have a script which gives me the number of different file types in my catalogue (dng, jpg, raw, etc) - and with GeekTools I can present that information on my desktop (see the screenshot in attached image). So next, I naturally wanted to compare those numbers to the actual volume they take up on disk. I did just presume (perhaps naively) that the file size for each image in the catalogue would have been stored somewhere in the .lrcat file. So out of interest, where does LR get the file size from?

As I said, it's just personal project not serious development.
 

Attachments

  • LRfileTypes.png
    LRfileTypes.png
    62.5 KB · Views: 365
Joined
Mar 21, 2015
Messages
1,506
Location
Dublin, Ireland.
Lightroom Experience
Power User
Lightroom Version
Use the database selection tools to select the images of interest (incl All photographs.)
Use Lr/Transporter or Listview Plugin to create a text file with a list of all the filename urls selected.
Use your python skills to analyse the data anyway you wish, by filetype, by folder, by drive, etc

For a simple GUI interface, import the list into an Access database. Then with a few lines of vb code add fields for file size. Now you have all the tools to slice and dice this info any way you wish. If you are familiar with other database tools use those instead of access.
 
Joined
Oct 8, 2007
Messages
5,980
Location
London
Lightroom Experience
Power User
So out of interest, where does LR get the file size from?

I presume it reads it dynamically, maybe as part of the missing photos routine. The EXIF+IPTC view of the Metadata panel shows the file size, and you can watch it change when you use the OS to delete an original. Maybe too if you have a TIF in LR and do some edits in PS.

John
 
Joined
Mar 21, 2015
Messages
1,506
Location
Dublin, Ireland.
Lightroom Experience
Power User
Lightroom Version
I did an experiment.
From Lr I opened a PSD file in Ps to edit the original.
I then duplicated the image layer several times and saved the psd ...so the file should get substantially bigger.
In Lr the psd still has the focus ... but the file size has not changed.
When I changed focus to another image and returned to the PSD file , Lr displayed the correct and larger file size.

I was keen to do this experiment because I remember in the past having issues with the file sizes reported in Lr. I am glad to see the behaviour as outlined above. It makes logical sense to get the size when checking if the file is missing.
 

achrysos

Member
Joined
May 12, 2008
Messages
47
Location
Cheshire, UK
Lightroom Experience
Advanced
Lightroom Version
Hi - forgot to post the update. In the end I just used python to look through each image in my photos library and extract the file sizes for each file type within. It is actually very fast that way considering that there are >30,000 files to look through. So now I have a little graphic at the bottom of my desktop that shows me a quick snapshot of the contents of my photo library (see attached).

Thank you to everybody for their help.
Antonio.
Screen Shot 2017-06-18 at 13.30.36.png
 
Status
Not open for further replies.
Top