Merge libraries feature

[RhetTbull]

Based on a question from an osxphotos user, I've prototyped a "merge" command for osxphotos that would merge two libraries into a single Photos library. This is similar to what PowerPhotos does. The major limitation is that persons/face regions cannot be merged as Apple provides no programmatic interface to persons (this was the original motivation behind osxphotos). I do plan to experiment with directly modifying the Photos SQLite database to see if I can add the persons and face regions.

Is this something that would be useful? If osxphotos implemented such a feature, would you use it?

[RhetTbull]

I've done some prototyping with this and think it's feasible. The following metadata can be imported either via AppleScript interface (easier) or the PhotoKit interface (more robust):

• keywords
• title
• description
• location
• favorite flag

Adjustments (edits) cannot be added via AppleScript so merge would have to add either the original version or the edited version if there is one (equivalent to the current -skip-original-if-edited option). However, I've been tinkering with the PhotoKit interface and I think the following would be possible:

• Add the original image with either AppleScript or PhotoKit
• request a ContentEditingInput
• send back the edited photo from source library as the edited image

This would result in the editing photo having a timestamp of now as opposed to when it was originally edited but both the original image and the editing image would be in the library. I'm not sure if I'd also be able to save the adjustment data from the source library this way to preserve all edit history.

Persons in image/face regions cannot be imported this way. Apple provides no programmatic access to persons/faces (at least not in a public API--there's got to be a private API that photoanalysisd uses to add detected faces but I've not been able to find it). However, I think I can successfully modify the database directly to add these as the face regions and persons touch relatively few tables and seem fairly straightforward. I have been able to successfully transfer a face region and person from one database to another using this method.

• Merge all photos per above, keeping a log to map old UUID to new UUID
• Make a copy of the merged sqlite database
• Edit this copy to add the faces by referencing the old UUID in the source database
• Delete the Photos database
• Move the edited database back into the Photos library

Lots could go wrong with this but I think it's feasible.

[RhetTbull]

Here's the minimum update needed to insert a face region and person name from one library to another:

• Add the face region to ZGENERICASSET
• Add record to ZDETECTEDFACE
• Add record to ZPERSON
• Update the Z_PK max in Z_PRIMARYKEY

INSERT INTO ZDETECTEDFACE(Z_PK,Z_ENT,Z_OPT,ZAGETYPE,ZASSETVISIBLE,ZBALDTYPE,ZCLOUDLOCALSTATE,ZCLOUDNAMESOURCE,ZCLUSTERSEQUENCENUMBER,ZCONFIRMEDFACECROPGENERATIONSTATE,ZEYEMAKEUPTYPE,ZEYESSTATE,ZFACEALGORITHMVERSION,ZFACIALHAIRTYPE,ZGENDERTYPE,ZGLASSESTYPE,ZHAIRCOLORTYPE,ZHASSMILE,ZHIDDEN,ZISINTRASH,ZISLEFTEYECLOSED,ZISRIGHTEYECLOSED,ZLIPMAKEUPTYPE,ZMANUAL,ZNAMESOURCE,ZQUALITYMEASURE,ZSMILETYPE,ZSOURCEHEIGHT,ZSOURCEWIDTH,ZTRAININGTYPE,ZASSET,Z34_ASSET,ZFACECROP,ZFACEGROUP,ZFACEGROUPBEINGKEYFACE,ZFACEPRINT,ZPERSON,ZPERSONBEINGKEYFACE,ZADJUSTMENTVERSION,ZBLURSCORE,ZCENTERX,ZCENTERY,ZLEFTEYEX,ZLEFTEYEY,ZMOUTHX,ZMOUTHY,ZPOSEYAW,ZQUALITY,ZRIGHTEYEX,ZRIGHTEYEY,ZROLL,ZSIZE,ZYAW,ZGROUPINGIDENTIFIER,ZMASTERIDENTIFIER,ZUUID) VALUES(1,17,5,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,0,0,2048,1365,0,1,35,NULL,NULL,NULL,NULL,1,1,NULL,0.3,0.608270754955464,0.736055903247632,0.0,0.0,0.0,0.0,0.0,-1.0,0.0,0.0,0.0,0.181449130177498,0.0,NULL,NULL,'CAFF8C0A-FB6F-4742-9405-D63CC144BF03');
UPDATE ZGENERICASSET SET Z_OPT=5, ZFACEAREAPOINTS=14073963587502135, ZMODIFICATIONDATE=633588360.693856 WHERE Z_PK=1;
INSERT INTO ZPERSON(Z_PK,Z_ENT,Z_OPT,ZAGETYPE,ZCLOUDDELETESTATE,ZCLOUDLOCALSTATE,ZCLOUDVERIFIEDTYPE,ZFACECOUNT,ZGENDERTYPE,ZINPERSONNAMINGMODEL,ZKEYFACEPICKSOURCE,ZMANUALORDER,ZQUESTIONTYPE,ZTYPE,ZVERIFIEDTYPE,ZASSOCIATEDFACEGROUP,ZKEYFACE,ZMERGETARGETPERSON,ZDISPLAYNAME,ZFULLNAME,ZPERSONUUID,ZPERSONURI,ZCONTACTMATCHINGDICTIONARY) VALUES(1,45,1,0,0,0,1,1,0,0,1,1,0,0,1,NULL,1,NULL,'TestGirl','TestGirl','04958DA9-9BBF-4F9D-8FFD-9AEE272B11D5',NULL,NULL);
UPDATE Z_PRIMARYKEY SET Z_MAX=1 WHERE Z_ENT=17;
UPDATE Z_PRIMARYKEY SET Z_MAX=1 WHERE Z_ENT=45;
UPDATE Z_PRIMARYKEY SET Z_MAX=78 WHERE Z_ENT=16001;
UPDATE Z_PRIMARYKEY SET Z_MAX=15 WHERE Z_ENT=16002;

Because the Photos database is a CoreData database, not plain sqlite, it includes triggers that call NSCoreDataTriggerUpdateAffectedObjectValue which fail when that function isn't present.

In experimentation it seems like I can define this in python as a user defined function to avoid the trigger errors without any harm to the database but this is a topic I am very unfamiliar with so will need more testing.

def NSCoreDataTriggerUpdateAffectedObjectValue_(*args, **kwargs):
    pass


conn.create_function(
    "NSCoreDataTriggerUpdateAffectedObjectValue",
    5,
    NSCoreDataTriggerUpdateAffectedObjectValue_,
)

[RhetTbull]

For this interested in following along or contributing, I've created a new (temporary) repo over here to implement the merge feature without having to deal with the complexities of the osxphotos code. Once complete, I'll merge back into osxphotos. The prototype is fully usable now but does not yet implement persons/face regions.

[RhetTbull]

Get ZDETECTEDFACE details for each UUID:

SELECT ZGENERICASSET.ZUUID, ZGENERICASSET.Z_PK, ZDETECTEDFACE.* 
FROM ZDETECTEDFACE 
JOIN ZGENERICASSET 
ON ZGENERICASSET.Z_PK = ZDETECTEDFACE.ZASSET

Match ZPERSON record to ZDETECTEDFACE

ZDETECTEDFACE.ZPERSON = ZPERSON.Z_PK

Also may need to update ZGENERICASSET.ZFACEAREAPOINTS. This has value of -100 when first imported. It then is updated, either by photoanalysisd or manually adding a face to be either zero (I assume this means no face detected) or a positive int such as 14073963587502135 if there's a face. I don't know what this data means but assume it should be copied to the merged database.

[RhetTbull]

Looks like I'll also need to merge ZDETECTEDFACEPRINT and ZDETECTEDFACEGROUP tables

[bell0q]

A merge would be very useful to me, yes. I do already use PowerPhotos, and I have used the iCloud Photos method to merge multiple libraries before. But what irks me is that my programmed slideshow projects are never copied. Only the projects from the most recent library is left intact using the iCloud Photos method. I have yet to find a way to preserve my slideshows from multiple libraries. Projects does have its own db.

[RhetTbull]

I'm making progress on the ability to merge persons/faces but I've made no attempt to figure out slideshows and other project types. This is currently outside the scope of osxphotos.

[bell0q]

This is currently outside the scope of osxphotos.

I understand. Hopefully it makes it in scope someday so that a merge does include all objects and parts of a library. Thanks for the reply!