Migrate macOS Photos Folders and Albums to plain tree of directories
Tags: raku, macos, photos
Published: 2022-12-15 23:17:43

## 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](https://raku.org/), its package manager Zef, database connector DBIish and support for SQLite:

```bash
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](https://sqlitebrowser.org/dl/). 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](https://en.wikipedia.org/wiki/Universally_unique_identifier). 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:

```perl
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:

```perl
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](https://raku.org/) 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:

```perl
    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:

```perl
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](https://raku.org/) 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:

```perl
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`:

```perl
    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:

```perl
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:

```perl
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`:

```perl
    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:

```perl
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:

```perl
    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](https://raku.org/) 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:

```perl
        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](https://raku.org/) 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](https://github.com/bbkr/MacPhotosExporter). 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.