Adobe Community: Message List - Creating a list of missing files in Organizer ?

Re: Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Tue, 11 Sep 2012 08:08:22 GMT

Ken,

Many thanks and congrats for the excellent job!

It is obvious that few users will use those queries but we have learnt many things from that exercice.

- It would be so easy for Adobe to provide a way to export the list shown in the reconnect dialog as a text file

- We know where the info is stored (thumbs.5.cache) which is a sqlite database

- We already knew that that cache file could be optimized in the 'optimize' process and re-created automatically if deleted.

- If we compare an optimized version of a catalog with missing files and the re-created catalog, of course the size and date created of the original files are lost. The status "2" for missing files has been confirmed. "0" is for normal files, "1" is not known.

We'll see if there is some change in the next PSE version.

Now, before deleting the cache, I'll make a copy not to lose the original data of the missing files...

Re: Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Tue, 11 Sep 2012 07:34:19 GMT

I've done some refinements -- the data extraction and insertion take a long time to run and include beaucoup data that we don't need. Using a small subset of the data speeds things up noticeably.

Copy this SQL query into a text editor:

CREATE TABLE [temp_thumbnail_info] (

[media_id] integer NOT NULL

)

and save the file as

temp_thumbnail_info_create.sql

Copy this SQL query into a text editor:

INSERT INTO [temp_thumbnail_info] ([media_id])

SELECT media_id

FROM thumbnail_info_table

where status = "2"

and save the file as

temp_thumbnail_info_t5c_insert.sql

Copy this SQL query into a text editor:

SELECT full_filepath

FROM media_table

WHERE id IN (

SELECT media_id

FROM temp_thumbnail_info

)

ORDER BY full_filepath

and save the file as

missing_files.sql

Then, the optimized procedure goes like this:

Connect to thumb.5.cache.
Run the "temp_thumbnail_info_create.sql" script.
Run the "temp_thumbnail_info_t5c_insert.sql" script.
Right-click on temp_thumbnail_info, and select "Script As...Data (Inserts)...File, saving it to that same convenient directory as
temp_thumbnail_info_data.sql
(In the Export Options dialog, simply click "OK".)
Disconnect from thumb.5.cache.
Close any open SQL queries.
Connect to catalog.pse10db.
Run the "temp_thumbnail_info_create.sql" script.
Run the "temp_thumbnail_info_data.sql" script.
Run the "missing_files.sql" script.

Save the result set as before in a format of your choosing.

Ken

Message was edited by: photodrawken to correct table name.

Re: Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Tue, 11 Sep 2012 04:54:35 GMT

Here we go, using Database.Net:

Connect to thumb.5.cache.
Right-click on thumbnail_info_table, and select "Script As...Create".
Use Edit...Save As to save it to any convenient directory as
thumbnail_info_table_create.sql
Righ-click on thumbnail_info_table, and select "Script As...Data (Inserts)...File, saving it to that same convenient directory as
thumbnail_info_table_data.sql
(In the Export Options dialog, simply click "OK".)
Disconnect from thumb.5.cache.
Close any open queries.
Connect to catalog.pse10db.
Use Edit...Open and open that
thumbnail_info_table_create.sql
file.
That query will open as a new query window -- click on the "Execute" button.
Repeat steps 8 and 9 for the
thumbnail_info_table_data.sql
file.
Click on the "New Query" button, and paste this query into the editing window:

SELECT full_filepath

FROM media_table

WHERE id IN (

SELECT media_id

FROM thumbnail_info_table

WHERE STATUS = "2"

)

ORDER BY full_filepath

12. Click on the "Execute" button.

You can click the "Export" button to save the results to a file in the format of your choice. Here, I've used the "HTML" option:

full_filepath

/Pics/nnpcs/000700AB.jpg

/Test/000700A2.jpg

/Test/000700A5.jpg

/Test/000700A8.jpg

Ken

Re: Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Tue, 11 Sep 2012 04:21:08 GMT

Michel,

OK, I got it working. I didn't see the ID links because I was lazy and only looking at the Top 500 rows of the thumbnail_info_table. When I looked at all the rows, sure enough, there were IDs matching the IDs in the media_table.

I'll post the procedure in a follow-up message, but this is a heads up: you're finally going to have to break down and install Database.Net:

http://fishcodelib.com/Database.htm

The procedure is a 4-step process:

Connect to the thumb.5.cache database and generate a table creation script for the thumbnail_info_table, saving that script as a SQL text file.
Still connected to thumb.5.cache database, generate a data insertion script for all the data in the thumbnail_info_table, saving that script as another SQL text file.
Connect to the catalog.pse10db database and run those two SQL scripts, in order, to create the thumbnail_info_table and populate it with the data.
Run a SQL query to get a list of the fully-qualified filenames of the missing files.

You were right that the thumbnail_info_table.status value of "2" denotes a missing file. I tested by importing 4 images into PSE, closing PSE and deleting those 4 images. I opened PSE, and used "Reconnect...All Missing Files". When that finished, those 4 images were marked as missing. When I finished step#4, I got precisely those 4 images listed.

Ken

Re: Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Mon, 10 Sep 2012 20:33:24 GMT

Well, if it can be done, it would be a two-stage process:

Query the thumbs.5.cache database for the missing files, and save the results.
Open the catalog.pse10db database and create a SQL query using the results from #1 as parameters.

However, I can't determine how the items in the thumbs.5.cache database are linked to items in the catalog.pse10db database: the thumbs.5.cache.thumbnail_info_table.media_id values don't match anything I'm seeing in the catalog.pse10db.media_table.id values....

Ken

Re: Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Mon, 10 Sep 2012 20:00:29 GMT

Michel,

That's a very interesting idea. A SQL query would be quite easy to devise. Give me a little time to create some missing files and I'll see what I can do....

Ken

Creating a list of missing files in Organizer ?

forums_noreply@adobe.com — Mon, 10 Sep 2012 17:36:21 GMT

It is a pity the dialog of the 'reconnect missing files' can list the missing files in a small window, but there is no option to export the list with full paths as a text file.

Knowing that the list is saved somewhere (probably in the catalog folder) I have searched if I could find how the information about missing files is stored in the Organizer. Natural candidates like the catalog.psexxdb and thumbs.5.cache sqlite databases did not show anything showing they stored the information.

So, I had a look at the thumbs.5.cache, which is interesting. It shows that two sizes of thumbnails are stored there, and that the criteria for matching are date taken and file size.

Now, I compared an optimized version of that file with a reconstructed one (I deleted the cache file).

Interestingly, you can see which media_id still have the matching criteria. I conclude that the other media_id are the missing files...

If Photodrawken is looking at this post :

Could you check this supposition and see if a sqlite query could extract the list of the missing media?

It will be interesting to see if the next PSE version has a better handling of missing files... Seems that LR handles that problem much better.

Edit:

The 'status' field in the thumbnail_info_table of the thumbs.5.cache database seems to be set at '2' for missing files.