Creating a list of missing files in Organizer ?

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

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

1. Query the thumbs.5.cache database for the missing files, and save the results.
2. 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

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:

1. 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.
2. 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.
3. 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.
4. 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

Here we go, using Database.Net:

1. Connect to thumb.5.cache.
2. Right-click on thumbnail_info_table, and select "Script As...Create".
3. Use Edit...Save As to save it to any convenient directory as
   thumbnail_info_table_create.sql
4. 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".)
5. Disconnect from thumb.5.cache.
6. Close any open queries.
7. Connect to catalog.pse10db.
8. Use Edit...Open and open that
   thumbnail_info_table_create.sql
   file.
9. That query will open as a new query window -- click on the "Execute" button.
10. Repeat steps 8 and 9 for the
    thumbnail_info_table_data.sql
    file.
11. 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:

Ken

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

)

GO

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"

GO

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:

1. Connect to thumb.5.cache.
2. Run the "temp_thumbnail_info_create.sql" script.
3. Run the "temp_thumbnail_info_t5c_insert.sql" script.
4. 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".)
5. Disconnect from thumb.5.cache.
6. Close any open SQL queries.
7. Connect to catalog.pse10db.
8. Run the "temp_thumbnail_info_create.sql" script.
9. Run the "temp_thumbnail_info_data.sql" script.
10. 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.