Augmenting Apple iCloud PhotoStream

As discussed previously, if you want to share in the photos of an Apple user, you have to do it the Apple way.

But once my windows PC has an incrementally downloading folder of photos, I want to upload them to Google Photos where everyone can view them.

I can upload the photos, but Google Photo's uses the file system dates when ordering the photos and ignores any timestamps in the exif tags; so somthing needs coding to extract that information from the tags to touch the files.

Furthermore, photos (and more likely, movies) that aren't tagged are totally without timestamp information.

And yet... iCloud photos viewer on windows has this information, along with comments -- none of which is stored in the images or movies.

The task

My self-appointed task is to find the source of this information for iCloud Photostream, embed it into the images and movies, and update file timestamps with it prior to upload to Google Photos.

Clues

There are some clues here: https://www.braxtonehle.com/posts/replicating-shared-photostream-to-dropbox/ but I'd already got that far by using Process Explorer to examine what files iCloud Photo was accessing.

The App

My original intention was to write a window app to upload to the Windows App Store, as the only one there to help with this seems highly priced at 

But having examined the database schema I see that this could be about 10 lines of bash scripting that works for just me; is it worth the extra work to produce a windows app?

No, but I'll do both anyway; downloading Visual Studio Express Community Edition. 

The bash script

The bash script is plain sqlite3 query, with the results piping into a loop at updates each file; with the usual bash presumptions that filenames won't contain a newline character, etc.

#! /bin/bash

USERDIR=/mnt/c/Users
WINUSER=flipflap
ICLOUD=AppData/Roaming/Apple\ Computer/MediaStream
DBNAME=local.db

DB="$USERDIR/$WINUSER/$ICLOUD/$DBNAME"

sq() {
  sqlite3 -line "$DB" "$@"
}

albums() {
  sq "select albumName from MSASAlbums"
}

photos() {
  sq "select assetfilepath, caption, downloaded, deleted, datetime(dateCreated + 978307200, 'unixepoch') || 'Z' as datetime, createdbyme from MSASAlbumAssets left join MSASAlbums on MSASAlbumAssets.albumGuid = MSASAlbums.albumGuid where albumName = '$1' order by dateCreated"
}

readRecord() {
  local field value count
  while read -r field _ value && test -n "$field"
  do printf -v "$field" "%s" "$value"
     count=$(( count +1 ))
  done
  test -n "$count"
}

updatePhotos() {
  while readRecord
  do file="${assetfilepath//C://mnt/c}"
     file="${file//\\//}"
     echo update "$datetime $file"
     touch -c -d "$datetime" "$file"
  done
}


if test -z "$1"
then albums
     exit
fi

photos "$@" | updatePhotos

And that works well enough.