I’ve posted this solution on SO: https://apple.stackexchange.com/a/449087/17533
Here’s the code again, for completeness and improvement suggestions:
use scripting additions
set path_to_photos_library to POSIX path of (choose file of type ¬
"com.apple.photos.library" with prompt ¬
"Choose the photos library that Photos.app currently uses" default location path to pictures folder)
--
-- Get the current selection from Photos
--
set selectedIDs to {}
if application "Photos" is running then
tell application "Photos"
local media_items
set media_items to selection
repeat with media_item in media_items
set end of selectedIDs to id of media_item
end repeat
quit -- need to quit Photos so that we can access its database
delay 0.5
end tell
end if
--
-- Make sure the database is accessible and determine which DB version to use
--
repeat
try
local files_dir
do shell script "sqlite3 " & quoted form of path_to_photos_library ¬
& "database/photos.db 'SELECT value FROM LiGlobals WHERE keyPath=\"metaSchemaVersion\"'"
ignoring white space
if result = "2" then
set db_version to 2
set files_dir to "Masters"
set db_name to "photos.db"
else if result = "3" then
set db_version to 3
set files_dir to "originals"
set db_name to "Photos.sqlite"
else
my showMessage("Sorry, the database appears to be in an unsupported format.")
quit
end if
end ignoring
set db_path to quoted form of path_to_photos_library & "database/" & db_name
set files_path to path_to_photos_library & files_dir & "/"
delay 0.1
exit repeat
on error
display dialog "Can't access database - make sure Photos.app is quit, then click OK"
end try
end repeat
--
-- Determine the file paths by querying the photos database
--
if selectedIDs is {} then
set where_clause to ""
else
if db_version = 2 then
set where_clause to ¬
" JOIN RKVersion v ON (m.uuid=v.masterUuid)" & ¬
" WHERE v.uuid IN (\"" & joinText(selectedIDs, "\",\"") & "\")"
else
-- need to remove the "/L0/001" suffix from the IDs first
set uuids to {}
repeat with anID in selectedIDs
set end of uuids to first item of splitText(anID, "/")
end repeat
set where_clause to " WHERE ZUUID IN (\"" & joinText(uuids, "\",\"") & "\")"
end if
end if
if db_version = 2 then
set sql to " 'SELECT m.imagePath FROM RKMaster m" & where_clause & "'"
else
set sql to " 'SELECT printf(\"%s/%s\",ZDIRECTORY,ZFILENAME) FROM ZASSET" & where_clause & "'"
end if
do shell script "sqlite3 " & db_path & sql
set check_paths to my splitText(result, return)
delay 0.1
--
-- Check the availability of every file
--
set missing_paths to {}
repeat with relPath in check_paths
set fullPath to (files_path & relPath)
set theFile to POSIX file fullPath
try
(theFile as alias)
on error
set end of missing_paths to relPath as string
end try
end repeat
if missing_paths is {} then
my showMessage("Found no missing files")
quit
end if
--
-- Query the database again to get the ids of the images that reference the missing files
--
if db_version = 2 then
do shell script "sqlite3 " & db_path & ¬
" 'SELECT v.uuid FROM RKMaster m JOIN RKVersion v ON (m.uuid=v.masterUuid)" & ¬
" WHERE m.imagePath IN (\"" & joinText(missing_paths, "\",\"") & "\")'"
set missing_ids to my splitText(result, return)
else
do shell script "sqlite3 " & db_path & ¬
" 'SELECT ZUUID FROM ZASSET" & ¬
" WHERE printf(\"%s/%s\",ZDIRECTORY,ZFILENAME) IN (\"" & joinText(missing_paths, "\",\"") & "\")'"
set missing_ids to my splitText(result, return)
end if
delay 0.1
--
-- Launch Photos, locate the images by their ids and add them to the "missing images" album
--
tell application "Photos"
set unlocated to {}
set missing_items to {}
set all_items to media items
repeat with anID in missing_ids
try
if db_version = 2 then
set anItem to media item id anID
else
set anItem to media item id (anID & "L0/001")
end if
set end of missing_items to anItem
on error
set end of unlocated to anID
end try
end repeat
if missing_items is {} then
my showMessage("Can't locate the images for the missing files." & return & return & ¬
"Make sure you choose the same library that Photos.app currently uses!")
else
if unlocated is not {} then
display dialog "" & (count of unlocated) & " missing items could not be found in Photos.app" & ¬
return & return & "This means that the script needs to be updated to handle this."
end if
if not (exists album "missing images") then
make new album named "missing images"
end if
set dest to album "missing images"
add missing_items to dest
my showMessage("Found " & (count of missing_items) & " missing files")
end if
end tell
--
-- Finished
--
on showMessage(theText)
display dialog theText buttons "OK" default button "OK"
end showMessage
on splitText(theText, theDelimiter)
set old_delimts to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
set theTextItems to every text item of theText
set AppleScript's text item delimiters to old_delimts
return theTextItems
end splitText
on joinText(theList, theDelimiter)
set old_delimts to AppleScript's text item delimiters
set AppleScript's text item delimiters to theDelimiter
set theText to theList as string
set AppleScript's text item delimiters to old_delimts
return theText
end joinText