Live Photo enabled / disabled database entry?

[jmmelko]

Dear devs,

I’d like to know where Photos stores the attribute “enable Live Photo” for each photo.

I have tried comparing two copies of the database using osxphotos after manually switching this property for a specific photo inside Photos (right-click->disable Live Photo), but it seems that this attribute isn’t stored in the PhotoInfo object (I have made a side-by-side comparison of the values of the _info dict for my photo identified by its UUID)

question1: does anybody knows where that property is stored in Photos.sqlite?

I have also tried comparing the Photos.sqlite databases but the problem is that recent changes are stored in the WAL file (Photos.sqlite-wal) and I am unable to open this file with my favorite SQL inspector App (SQLiteStudio).

question2: Is there a way to force Photos to commit recent changes to the database?

thank you.

[RhetTbull]

question1: does anybody knows where that property is stored in Photos.sqlite?

I used the osxphotos snap and osxphotos diff tools (which are hidden -- not listed in the help unless you use OSXPHOTOS_SHOW_HIDDEN=1) to perform a snapshot of the database before and after changing a photo's live photo on/off status which shows:

Live Video on:
ZASSET table:
ZPLAYBACKSTYLE=3, ZVIDEOCPVISIBILITYSTATE=0

Live Video off:
ZASSET table:
ZPLAYBACKSTYLE=1, ZVIDEOCPVISIBILITYSTATE=15

When Live Video turned back on:
ZASSET table:
ZPLAYBACKSTYLE=3, ZVIDEOCPVISIBILITYSTATE=10

These are not currently exposed in OSXPhotos.

question2: Is there a way to force Photos to commit recent changes to the database?

You cannot do this. You need to use a SQLite viewer that understands WAL mode. I like DB Browser for SQLite. I also recommend you ALWAYS copy the Photos.sqlite and the -shm and -wal files before viewing them. You risk corruption if you open them directly from the Photos library.

[RhetTbull]

You can access these in OSXPhotos using the following:

Find live photos with live mode turned off:

osxphotos query --query-eval "photo.tables().ZASSET.ZPLAYBACKSTYLE[0] == 1" --live

Find live photos with live mode turned on:

osxphotos query --query-eval "photo.tables().ZASSET.ZPLAYBACKSTYLE[0] == 3" --live

[RhetTbull]

and I am unable to open this file with my favorite SQL inspector App (SQLiteStudio).

By the way, you cannot open the WAL file. You copy the Photos.sqlite and the associated -shm and -wal files somewhere then open the SQLite file. The DB viewer should open this in a way that the SQLite engine sees the WAL and reads it.

[jmmelko]

Thank you, it worked. According to Google you are the only person on this planet to understand Photo's database structure (besides Apple's employees)

I modified my copy of osxphoto to add this database line as a new property.

My goal was to remove the .mov files associated to these photos. That's no so simple because does not behave well after deletion, and since it is risky to modify the database directly I'll have to export ans re-import the photos.

Also, I have an old macOS so I cannot use the new feature "duplicate as still image".

I think I will use an AppleScript to create an album in which to put these photos, export the photos with osxphotos to keep all EXIF data, manually delete the photos using Photos, and then re-import only the .HEIC files.

[RhetTbull]

My goal was to remove the .mov files associated to these photos. That's no so simple because does not behave well after deletion, and since it is risky to modify the database directly I'll have to export ans re-import the photos.

I have a feature planned to do this sort of thing and other transformations on the photos: #909

I think I will use an AppleScript to create an album in which to put these photos, export the photos with osxphotos to keep all EXIF data, manually delete the photos using Photos, and then re-import only the .HEIC files.

Take a look at strip_live.py in the examples folder. It might be sufficient for your needs. It works on currently selected photos so you might still need a script to put all the photos that are live but have the live video disabled into a album:

Using your modified version of osxphotos, you could do something like this (this assumes the new property is PhotoInfo.live_enabled)

(Not tested but this should get you started assuming you are familiar with python)

import osxphotos

photosdb = osxphotos.PhotosDB()
live_disabled_photos = [p for p in photosdb.photos() if p.live_photo and  not p.live_enabled]
album = osxphotos.PhotosAlbum("live_disabled")
album.add_list(live_disabled_photos)

[jmmelko]

Thanks again! It totally works!
I had completely overlooked the fact that osxphotos is able to modify the database in a safe way via Applescript. Sorry about that ^^'

Thus I modified your example script this way:

"""Export selected Live photos and re-import just the image portion"""

import pathlib
import sys
import time
from tempfile import TemporaryDirectory
from typing import List

import click
from photoscript import Album, Photo, PhotosLibrary
from rich import print

from osxphotos import PhotoInfo, PhotosDB, PhotosAlbum

DEFAULT_DELETE_ALBUM = "Live Photos to Delete"
DEFAULT_NEW_ALBUM = "Imported Live Photos"


def rename_photos(photo_paths: List[str]) -> List[str]:
    """Given a list of photo paths, rename the photos so names don't clash as duplicated on re-import"""
    # use perf_counter_ns as a simple unique ID to ensure each photo has a different name
    new_paths = []
    for path in photo_paths:
        path = pathlib.Path(path)
        stem = f"{path.stem}_{time.perf_counter_ns()}"
        new_path = path.rename(path.parent / f"{stem}{path.suffix}")
        new_paths.append(str(new_path))
    return new_paths


def set_metadata_from_photo(source_photo: PhotoInfo, dest_photos: List[Photo]):
    """Set metadata (keywords, albums, title, description, favorite) for dest_photos from source_photo"""
    title = source_photo.title
    description = source_photo.description
    keywords = source_photo.keywords
    favorite = source_photo.favorite

    # apply metadata to each photo
    for dest_photo in dest_photos:
        dest_photo.title = title
        dest_photo.description = description
        dest_photo.keywords = keywords
        dest_photo.favorite = favorite

    # add photos to albums
    album_ids = [a.uuid for a in source_photo.album_info]
    for album_id in album_ids:
        album = Album(album_id)
        album.add(dest_photos)


def process_photo(
    photo: Photo,
    photosdb: PhotosDB,
    keep_originals: bool,
    download_missing: bool,
    new_album: Album,
    delete_album: Album,
):
    """Process each Live Photo to export/re-import it"""
    with TemporaryDirectory() as tempdir:
        p = photosdb.get_photo(photo.uuid)
        if not p.live_photo:
            print(
                f"[yellow]Skipping non-Live photo {p.original_filename} ({p.uuid})[/]"
            )
            return

        # versions to download (True for edited, False for original)
        versions = []

        # use photos_export to download from iCloud
        photos_export = False

        # try to download missing photos only if photo is missing and --download-missing
        if keep_originals or not p.hasadjustments:
            # export original photo
            if not p.path and not download_missing:
                print(
                    f"[yellow]Skipping missing original version of photo {p.original_filename} ({p.uuid}) (you may want to try --download-missing)[/]"
                )
                return
            photos_export = download_missing and not p.path
            versions.append(False)

        if p.hasadjustments:
            if not p.path_edited and not download_missing:
                print(
                    f"[yellow]Skipping missing edited version of photo {p.original_filename} ({p.uuid}) (you may want to try --download-missing)[/]"
                )
                return
            photos_export = photos_export or (download_missing and not p.path_edited)
            versions.append(True)

        exported = []
        for version in versions:
            # export the actual photo (without the Live video)
            print(
                f"Exporting {'edited' if version else 'original'} photo {p.original_filename} ({p.uuid})"
            )
            if exports := p.export(
                tempdir,
                live_photo=False,
                edited=version,
                use_photos_export=photos_export,
            ):
                exported.extend(exports)
            else:
                print(
                    f"[red]Error exporting photo {p.original_filename} ({p.uuid})[/]",
                    file=sys.stderr,
                )

        if not exported:
            return

        exported = rename_photos(exported)
        print(
            f"Re-importing {', '.join([pathlib.Path(p).name for p in exported])} to album '{new_album.name}'"
        )
        new_photos = new_album.import_photos(exported)

        print("Applying metadata to newly imported photos")
        set_metadata_from_photo(p, new_photos)

        print(f"Moving {p.original_filename} to album '{delete_album.name}'")
        delete_album.add([Photo(p.uuid)])


@click.command()
@click.option(
    "--album",
    "album_name",
    default="",
    help="Album to search photos in. "
    "default = ''",
)
@click.option(
    "--download-missing", is_flag=True, help="Download missing files from iCloud."
)
@click.option(
    "--keep-originals",
    is_flag=True,
    help="If photo is edited, also keep the original, unedited photo. "
    "Without --keep-originals, only the edited version of a Live photo that has been edited will be kept.",
)
@click.option(
    "--delete-album",
    "delete_album_name",
    default=DEFAULT_DELETE_ALBUM,
    help="Album to put Live photos in when they're ready to be deleted; "
    f"default = '{DEFAULT_DELETE_ALBUM}'",
)
@click.option(
    "--new-album",
    "new_album_name",
    default=DEFAULT_NEW_ALBUM,
    help="Album to put Live photos in when they've been re-imported after stripping the video component; "
    f"default = '{DEFAULT_NEW_ALBUM}'",
)

def strip_live_photos(
    album_name, download_missing, keep_originals, delete_album_name, new_album_name
):
    """Export selected Live photos and re-import just the image portion.

    This script can be used to free space in your Photos library by allowing you
    to effectively delete just the Live video portion of a Live photo.

    The photo part of the Live photo will be exported to a temporary directory then
    reimported into Photos. Albums, keywords, title/caption, favorite, and description
    will be preserved. Unfortunately person/face data cannot be preserved.

    After export Live photos will be moved to an album (which can be set using
    --delete-album) so they can be deleted. You can use Command + Delete to put the
    photos in the trash after selecting them in the album.
    """
    photoslib = PhotosLibrary()
    photos = []
    
    if album_name:
        album = photoslib.album(album_name)
        if album: photos = album.photos()
        if not photos:
            print("No photos in album {album_name}...nothing to do", file=sys.stderr)
            sys.exit(1)        
    else:
        photos = photoslib.selection
        if not photos:
            print("No photos selected...nothing to do", file=sys.stderr)
            sys.exit(1)

    print(f"Processing {len(photos)} photo(s)")
    print("Loading Photos database")
    photosdb = PhotosDB()

    new_album = photoslib.album(
        new_album_name, top_level=True
    ) or photoslib.create_album(new_album_name)
    delete_album = photoslib.album(
        delete_album_name, top_level=True
    ) or photoslib.create_album(delete_album_name)

    num_photos = len(photos)

    for i, photo in enumerate(photos):
        process_photo(
            photo, photosdb, keep_originals, download_missing, new_album, delete_album
        )
        
        if i > 0 and i % 10 == 0: print(f"{i}/{num_photos} processed...")

    new_album.spotlight()

def find_disabled_live_photos():

    photosdb = PhotosDB()
    live_disabled_photos = [p for p in photosdb.photos() if p.live_photo and  not p.live_enabled]
    
    if len(live_disabled_photos) > 0:
        album = PhotosAlbum("live_disabled")
        album.add_list(live_disabled_photos)
        return album.name
    else:
        print('No disabled live photo')
        return ""

if __name__ == "__main__":
    
    album_name = find_disabled_live_photos()
        
    strip_live_photos(['--album', album_name])

[RhetTbull]

Great, glad it worked!