Migrate macOS Photos Folders and Albums to plain tree of directories

Intro

For various objective and subjective reasons I'm leaving Apple ecosystem and moving to Arch Linux + KDE desktop. Transition was going super smoothly until... macOS Photos. This application does not allow to easily export multiple internal albums to tree of directories. So it was time for quick Christmas hacking 🎄.

Cleanup

In Photos I have smart Unsorted folder that shows "pictures not assigned to any album". When I import something to Photos it appears in this folder and once it is assigned to Album it disappears from this folder. I made sure this folder is empty, meaning I have no lose photos (not assigned to any album).

And I also cleaned internal Photos trash to avoid dealing with deleted stuff during migration.

Tools

I started Docker Alpine as follows from terminal:

docker run --volume /Users/bbkr/Pictures:/Pictures --interactive --tty alpine sh

And installed inside Docker container few packages needed for the task - Raku language, its package manager Zef, database connector DBIish and support for SQLite:

apk update
apk add rakudo zef sqlite-libs
zef install --/test DBIish

What is actually inside Photos?

Photos library Photos Library.photoslibrary is not file but regular folder. It contains SQLite database in database/photos.db with all definitions. It can be analyzed for example with SQLite browser. Photos files are in Masters folder structure, unfortunately organized by date and not by album.

Warning

Database is big and messy. Some things are referenced by primary key modelId columns and some by UUIDs. To make things worse UUID name is used quite liberally and often have nothing to do with UUID format. Everything uses myEyesAreBleeding camel-case format abomination. So I used my own naming in code. And there are legacy features, I've found traces of my data added from MacOS Snow Leopard when Photos was still named iPhoto.

Plan of action

• Recreate Folders and Albums tree from SQLite database.
• Find all files in Masters directory for each album.
• Move those files to corresponding plain directory paths.

Let's start hacking

I've created migrate.raku file in my Pictures in macOS and this allows me to call this script as raku /Pictures/migrate.raku from within Docker.

Connecting to database was super simple:

use DBIish;

my $handle = DBIish.connect( 'SQLite', database => '/Pictures/Photos Library.photoslibrary/database/photos.db' );

Folders

Folders are defined in RKFolder table. Let's create model to represent them:

class Folder {
    has $.uuid;
    has $.name;

    method new ( :$uuid ) {

        state $query = $handle.prepare('
            SELECT uuid, name
            FROM RKFolder
            WHERE uuid = ?
        ');

        return self.bless( |$query.execute( $uuid ).allrows( :array-of-hash )[ 0 ] );
    }

}

We need to create Folder instances by UUIDs, because this identifier is used for folder tree definition.

Quick Raku tutorial:

• $.foo is class attribute.
• method new ( :$uuid ) creates constructor accepting named uuid param.
• self.bless is low level method of creating class instance directly from list of named attributes.
• | before $query... flattens row Hash to list of named attributes expected by new constructor.

We also need Folder instance to be able to return its subfolders, so let's add this method to Folder class:

    method subfolders {

        state $query = $handle.prepare('
            SELECT uuid
            FROM RKFolder
            WHERE parentFolderUuid = ?
            ORDER BY name
        ');

        return $query.execute( $.uuid ).allrows( :array-of-hash ).map: { Folder.new( |$_ ) }
    }

And verify that we can recreate Folders tree by adding folowing method after Folder class:

sub traverse ( $current-folder, *@parent-folders ) {

    my $indent = '  ' x @parent-folders.elems;
    say $indent, '/' ,$current-folder.name;

    for $current-folder.subfolders.eager -> $subfolder {
        samewith( $subfolder, @parent-folders, $current-folder );
    }
}

traverse( Folder.new( uuid => 'TopLevelAlbums' ) );

Quick Raku tutorial:

• *@parent-folders means slurpy param that consumes all positional params remaining. This allows to track Folders history as we descent into subfolders by providing @parent-folders, $current-folder to deeper iteration.
• samewith is cool way to call recursive function with different set of params but without repeating function name.
• eager means do not use lazy lists, load everything to memory right away.
• x repeats string, so the deeper we are the bigger indentation for debug printing.

Everything starts at TopLevelAlbums, which is hardcoded pseudo-UUID in database. After calling raku /Pictures/migrate.raku inside Docker it should print something like this:

/Vacations
  /Europe
    /Italy
    /Poland
  /Asia
    /Emirates
/Conferences
  /YAPC
  /Workshops

Albums

This is tricky part. Folder itself cannot contain Photos. Each Folder contains implicit Album, optional explicit Albums and optional subFolders. Meaning each path will be something like: Folder -> Folder -> Folder -> Album -> Photo file.

Let's create representation of Albums from RKAlbum table:

class Album {
    has $.id;
    has $.name;

    method new ( :$id ) {

        state $query = $handle.prepare('
            SELECT modelId AS id, name
            FROM RKAlbum
            WHERE modelId = ?
        ');

        return self.bless( |$query.execute( $id ).allrows( :array-of-hash )[ 0 ] );
    }

}

Very similar to Folder, but this time we need primary key modelID instead of UUID.

Folder class must be now extended with new method to return Albums:

    method albums {

        state $query = $handle.prepare('
            SELECT modelId AS id
            FROM RKAlbum
            WHERE folderUuid = ?
            ORDER BY name
        ');

        return $query.execute( $.uuid ).allrows( :array-of-hash ).map: { Album.new( |$_ ) };
    }

And traversal should be modified as well:

sub traverse ( $current-folder, *@parent-folders ) {

    my $indent = '  ' x @parent-folders.elems;
    say $indent, '/' ,$current-folder.name;

    # new code
    for $current-folder.albums.eager -> $album {
        say $indent, '  *' , $album.name;
    }

    for $current-folder.subfolders.eager -> $subfolder {
        samewith( $subfolder, @parent-folders, $current-folder );
    }
}

After calling raku /Pictures/migrate.raku inside Docker it should print something like this:

/Vacations
  *(Any)
  /Europe
    *(Any)
    /Italy
        *(Any)
        *Rome
        *Sardinia
    /Poland
        *(Any)
        *Gdańsk
        *Warsaw
  /Asia
    *(Any)
    /Emirates
      *(Any)
/Conferences
  *(Any)
  /YAPC
    *(Any)
    *Orlando
    *London
  /Workshops
    *(Any)

This should give better understanding what is going on here. Folders are displayed with /, Albums are displayed with *, Albums marked as *(Any) are implicit ones because Folder itself cannot contain photos.

Photos

Last piece of puzzle. Let's create model representing picture from RKMaster table:

class Picture {
    has $.id;
    has $.name;
    has $.path;

    method new ( :$id ) {

        state $query = $handle.prepare('
            SELECT modelId AS id, originalFileName AS name, imagePath AS path
            FROM RKMaster
            WHERE modelId = ?
        ');

        return self.bless( |$query.execute( $id ).allrows( :array-of-hash )[ 0 ] );
    }

}

At this point it should be self-explanatory. And add new method to Album class to return Pictures:

    method pictures {

        state $query = $handle.prepare('
            SELECT RKVersion.masterId AS id
            FROM RKAlbumVersion, RKVersion
            WHERE RKAlbumVersion.versionId = RKVersion.modelId
                AND RKAlbumVersion.albumId = ?
        ');

        return $query.execute( $.id ).allrows( :array-of-hash ).map: { Picture.new( |$_ ) };
    }

Worth noting that Pictures do not belong to Album directly, they are versioned. But since I didn't use versioning in Photos all my pictures have only one version.

Finally we can traverse Folders tree, Albums and Pictures in them at once:

sub traverse ( $current-folder, *@parent-folders ) {

    my $indent = '  ' x @parent-folders.elems;
    say $indent, '/' ,$current-folder.name;

    for $current-folder.albums.eager -> $album {
        say $indent, '  *' , $album.name;

        # new code
        for $album.pictures.eager -> $picture {
            say $indent, '    -' , $picture.name;
        }
    }

    for $current-folder.subfolders.eager -> $subfolder {
        samewith( $subfolder, @parent-folders, $current-folder );
    }
}

Which will print:

/Vacations
  *(Any)
     -IMG0001.jpg
  /Europe
    *(Any)
    /Italy
        *(Any)
        *Rome
           -IMG0001.jpg
           -IMG0002.jpg
...

Migration

Once we discovered to which Folders and Album each Picture belong and we know Picture.path in Masters folder we can simply create directory structure and move files there.

First let's create in our traversal logic directory to move files to:

    for $current-folder.albums.eager -> $album {
        say $indent, ' *' , $album.name;

        # new code
        my $destination-path = IO::Path.new( '/Pictures/' );
        $destination-path .= add( .name ) for @parent-folders;
        $destination-path .= add( $current-folder.name );
        $destination-path .= add( $_ ) with $album.name;
        $destination-path.mkdir();

So directory level is created for each Folder in path, for current Folder and optionally for Album if it was not implicit one.

Time for quick Raku tutorial:

• .= is short for execute method and assign result. Same as $foo = $foo.bar().
• with is the best thing that happened to programming languages since sliced bread. It means "do something with operand if operand is defined" and allows to avoid repeating operand name in traditional do-sth( $foo ) if defined $foo manner. And yes, it has twin brother without.
• mkdir in Raku creates full path, just like mkdir -p in Linux.

And finally move files to those created directories:

        for $album.pictures.eager -> $picture {
            say $indent, '  -' , $picture.name;

            # new code
            my $source-file = IO::Path.new( '/Pictures/Photos Library.photoslibrary/Masters/' );
            $source-file .= add( $picture.path );
            try {
                $source-file.copy( $destination-path.add( $source-file.basename ), :createonly );
            }
            $source-file.copy( $destination-path.add( $picture.id ~ '.' ~ $source-file.extension ), :createonly ) if $!;
        }

Source file path is combined with fixed path to Masters Folder and path taken from database for Picture. But there is a catch. I want to keep original file names, because sometimes they are meaningful. However Album can have two master files with the same name from two different directories in Masters folder. That is why I have fallback. If original name is taken then use database ID and original file extension.

And last Raku tutorial:

• $! keeps Exception from last try{} block. Checking it's presence is just a lazy way to avoiding typing fully blown catch {}.
• Built in IO::Path is really feature rich. It allows to combine, paths, normalize paths, extract base name, 1st 2nd and 3rd extensions and more. Excellent tool for quick hacking.
• There is even successor generator in IO::Path, "foo1.jpg".IO.succ will return foo2.jpg. But i haven't used it as fallback because successor for party.jpg is partz.jpg, not exactly what I wanted.
• :createonly is a way of passing named boolean param to a function, same as createonly => True. The opposite is :!createonly.

Done!

After running the script it created TopLevelAlbums directory with all my Folders and Albums structure perfectly restored.

Full script is available here. It can be executed on any system capable of running Docker, which may be especially useful if you have Photos library but no longer have access to Mac computer.