1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Welcome to Lightroom Forums! We're a friendly bunch, so please feel free to register and join in the conversation. If you're not familiar with forums, you'll find step by step instructions on how to register and post your first thread under Help on the menubar above. You're also welcome to join our newsletter, download our free Lightroom Quick Start eBook and explore our other FAQ resources.
    Dismiss Notice

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

Discussion in 'Extending Lightroom' started by achrysos, May 23, 2017.

  1. achrysos

    achrysos New Member

    Joined:
    May 12, 2008
    Messages:
    46
    Location:
    Cheshire, UK
    Lightroom Experience:
    Advanced
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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
     
  2. Ferguson

    Ferguson Linwood Ferguson Staff Member Lightroom Guru

    Joined:
    Jan 18, 2009
    Messages:
    1,114
    Location:
    Cape Coral, FL
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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.
     
  3. achrysos

    achrysos New Member

    Joined:
    May 12, 2008
    Messages:
    46
    Location:
    Cheshire, UK
    Lightroom Experience:
    Advanced
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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'.
     
  4. PhilBurton

    PhilBurton Active Member

    Joined:
    Nov 16, 2015
    Messages:
    680
    Location:
    California, USA
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    Perpetual (Standalone)
    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
     
  5. Ferguson

    Ferguson Linwood Ferguson Staff Member Lightroom Guru

    Joined:
    Jan 18, 2009
    Messages:
    1,114
    Location:
    Cape Coral, FL
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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.
     
  6. Wernfried

    Wernfried New Member

    Joined:
    Jan 31, 2017
    Messages:
    51
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    Perpetual (Standalone)
    Have a look at Plugin Export List, as far as I know it also provides property "FileSize" to be exported.
     
  7. johnbeardy

    johnbeardy Senior Member Staff Member Moderator Lightroom Guru

    Joined:
    Oct 8, 2007
    Messages:
    4,174
    Location:
    London
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.6 / CC 2015.6
    Lightroom License:
    CC Subscription
    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
     
  8. achrysos

    achrysos New Member

    Joined:
    May 12, 2008
    Messages:
    46
    Location:
    Cheshire, UK
    Lightroom Experience:
    Advanced
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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.
     

    Attached Files:

  9. Gnits

    Gnits Active Member

    Joined:
    Mar 21, 2015
    Messages:
    714
    Location:
    Dublin, Ireland.
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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.
     
  10. johnbeardy

    johnbeardy Senior Member Staff Member Moderator Lightroom Guru

    Joined:
    Oct 8, 2007
    Messages:
    4,174
    Location:
    London
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.6 / CC 2015.6
    Lightroom License:
    CC Subscription
    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
     
  11. Gnits

    Gnits Active Member

    Joined:
    Mar 21, 2015
    Messages:
    714
    Location:
    Dublin, Ireland.
    Lightroom Experience:
    Power User
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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.
     
  12. achrysos

    achrysos New Member

    Joined:
    May 12, 2008
    Messages:
    46
    Location:
    Cheshire, UK
    Lightroom Experience:
    Advanced
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    Thanks for feedback everybody - it's very much appreciated. I'll let you know what I work out!
     
  13. achrysos

    achrysos New Member

    Joined:
    May 12, 2008
    Messages:
    46
    Location:
    Cheshire, UK
    Lightroom Experience:
    Advanced
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    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
     
  14. Ferguson

    Ferguson Linwood Ferguson Staff Member Lightroom Guru

    Joined:
    Jan 18, 2009
    Messages:
    1,114
    Location:
    Cape Coral, FL
    Lightroom Experience:
    Intermediate
    Primary OS:
    Windows 10
    Lightroom Version:
    6.10 / CC 2015.10
    Lightroom License:
    CC Subscription
    Glad it worked out. Now you know you have insufficient images, and need to go shoot more. :eek:
     
    achrysos likes this.
  15. rob211

    rob211 Active Member

    Joined:
    Mar 17, 2014
    Messages:
    987
    Location:
    Oakland, CA USA
    Lightroom Experience:
    Advanced
    Primary OS:
    Mac 10.12.x Sierra
    Lightroom Version:
    6.7 / CC 2015.7
    Lightroom License:
    CC Subscription
    And for those of you who wanna access the same data (although in pie chart, not bar) without using geektool (and kudos for getting that to work; it is truly geeky), try The Analytics Dashboard For Adobe Lightroom. Just drop your catalog on and you get a look at lotsa stats.
     

Share This Page