Feature: Propagate and Collect Deletes

[fxstein]

Imported and processed files of goprox are mainly imported into other image and video processing platforms. For images, a common path is to import all processed files into Apple Photos. This allows for easy browsing, filtering, and sorting (based on goprox metadata and tags). Once imported it is very common to review the images for favorites, decent shots as well as outright junk. Often bad images get deleted quickly from Photos to reduce the amount of overhead to photo libraries.

Once deleted - sometimes as much as 80-90% of imported images, the problem becomes that the goprox library continues to hold the deleted images and future imports create the problem of re-importing previously deleted images, especially after 30-40 days when the deleted items list in Photos gets purged.

However it is this list of deleted items, that is programmatically available via Photos internal SQLite database for said 30-40 days. As such it would be easy to extract the list of deleted images and media files from photos, apply some filters and in return remove them from the processed or even imported library paths. The originals would still be available in the original SDcard images if there is ever a need to recover a deleted file.

In order to make this permanent, goprox will need to keep an ever-growing list of deleted media files and eliminate them from future processing as well as from the current library storage.

The Photos SQLite store is commonly found here:

~/Pictures/Photos\ Library.photoslibrary/database/Photos.sqlite

The query to extract recently deleted media files from Photos would be like this:

SELECT 
  zAddAssetAttr.ZORIGINALFILENAME AS 'zAddAssetAttr-Original Filename'
FROM ZASSET zAsset
  JOIN ZADDITIONALASSETATTRIBUTES zAddAssetAttr ON zAddAssetAttr.Z_PK = zAsset.ZADDITIONALATTRIBUTES
WHERE zAsset.ZTRASHEDSTATE = 1
ORDER BY zAddAssetAttr.ZORIGINALFILENAME;

and results in something like this:

IMG_0922.PNG
P_20220923092015_GoPro_Max_6013_GS__4797.jpg
P_20220923092019_GoPro_Max_6013_GS__4798.jpg
P_20220923092026_GoPro_Hero11_8909_GOPR0089.jpg
P_20220923092030_GoPro_Hero11_8909_GOPR0090.jpg
P_20220923092033_GoPro_Hero11_5131_GOPR0100.jpg
P_20220923092037_GoPro_Hero11_5131_GOPR0101.jpg
P_20220923125107_GoPro_Max_6013_GS__4802.jpg
P_20220923125118_GoPro_Hero11_4632_GOPR0075.jpg
P_20220923125120_GoPro_Hero11_8909_GOPR0094.jpg
P_20220923125127_GoPro_Hero11_5131_GOPR0105.jpg
P_20220923125157_GoPro_Max_6013_GS__4803.jpg
P_20220923125209_GoPro_Hero11_4632_GOPR0076.jpg
P_20220923125210_GoPro_Hero11_8909_GOPR0095.jpg
P_20220923125217_GoPro_Hero11_5131_GOPR0106.jpg

It contains any recently deleted media files and would need to be filtered for goprox specific files.

To store the list of deleted files a new deleted substructure would need to be introduced as part of the library. Deleted images could be grouped into list files by year for ease of processing.

[fxstein]

Shell version of SQLite query:

sqlite3 -readonly ~/Pictures/Photos\ Library.photoslibrary/database/Photos.sqlite "SELECT zAddAssetAttr.ZORIGINALFILENAME AS 'zAddAssetAttr-Original Filename' FROM ZASSET zAsset JOIN ZADDITIONALASSETATTRIBUTES zAddAssetAttr ON zAddAssetAttr.Z_PK = zAsset.ZADDITIONALATTRIBUTES WHERE zAsset.ZTRASHEDSTATE = 1 ORDER BY zAddAssetAttr.ZORIGINALFILENAME;"