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



## achrysos (May 23, 2017)

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


----------



## Linwood Ferguson (May 23, 2017)

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 (May 23, 2017)

Ferguson said:


> 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 (May 23, 2017)

achrysos said:


> 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


----------



## Linwood Ferguson (May 24, 2017)

achrysos said:


> 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.


----------



## Wernfried (May 24, 2017)

Have a look at Plugin Export List, as far as I know it also provides property "FileSize" to be exported.


----------



## johnbeardy (May 24, 2017)

*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 (May 24, 2017)

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.


----------



## Gnits (May 25, 2017)

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.


----------



## johnbeardy (May 25, 2017)

achrysos said:


> 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


----------



## Gnits (May 25, 2017)

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 (May 27, 2017)

Thanks for feedback everybody - it's very much appreciated. I'll let you know what I work out!


----------



## achrysos (Jun 18, 2017)

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.


----------



## Linwood Ferguson (Jun 18, 2017)

Glad it worked out.  Now you know you have insufficient images, and need to go shoot more.


----------



## rob211 (Jun 18, 2017)

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.


----------

