internal/entity/query/covers.go - develop - githubmirror/photoprism

internal/entity/query/covers.go
602 строки20 KB
Michael Mayer
Config: Centralize SQL driver identifiers in pkg/dsn #5588
17 май 2026, 17:47
17 май 2026, 17:47c8390a2
package query

import (
	"fmt"
	"strings"
	"sync/atomic"
	"time"

	"github.com/dustin/go-humanize/english"
	"github.com/jinzhu/gorm"

	"github.com/photoprism/photoprism/internal/entity"
	"github.com/photoprism/photoprism/internal/mutex"
	"github.com/photoprism/photoprism/pkg/clean"
	"github.com/photoprism/photoprism/pkg/dsn"
	"github.com/photoprism/photoprism/pkg/media"
)

// coversBusy is true when the covers are currently updating.
var coversBusy = atomic.Bool{}

// UpdateAlbumManualCovers updates manual album cover thumbs. When albums are
// provided, the update is limited to auto-managed entries from that list.
func UpdateAlbumManualCovers(albums ...entity.Album) (err error) {
	mutex.Index.Lock()
	defer mutex.Index.Unlock()

	start := time.Now()

	var res *gorm.DB

	if len(albums) > 0 {
		for _, album := range albums {
			if album.AlbumType != entity.AlbumManual || album.ThumbSrc != entity.SrcAuto || album.AlbumUID == "" {
				continue
			}

			if err = refreshAlbumCover(album); err != nil {
				return err
			}
		}

		return nil
	}

	condition := gorm.Expr("album_type = ? AND thumb_src = ?", entity.AlbumManual, entity.SrcAuto)

	switch DbDialect() {
	case dsn.DriverMySQL:
		res = Db().Exec(`UPDATE albums LEFT JOIN (
	    	SELECT p2.album_uid, f.file_hash FROM files f, (
	        	SELECT pa.album_uid, max(p.id) AS photo_id FROM photos p
	            JOIN photos_albums pa ON pa.photo_uid = p.photo_uid AND pa.hidden = 0 AND pa.missing = 0
	        	WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
	        	GROUP BY pa.album_uid) p2 WHERE p2.photo_id = f.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			) b ON b.album_uid = albums.album_uid
		SET thumb = b.file_hash WHERE ?`, media.PreviewExpr, condition)
	case dsn.DriverSQLite3:
		res = Db().Table(entity.Album{}.TableName()).
			UpdateColumn("thumb", gorm.Expr(`(
		SELECT f.file_hash FROM files f 
			JOIN photos_albums pa ON pa.album_uid = albums.album_uid AND pa.photo_uid = f.photo_uid AND pa.hidden = 0 AND pa.missing = 0
			JOIN photos p ON p.id = f.photo_id AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_quality > 0
			WHERE f.deleted_at IS NULL AND f.file_missing = 0 AND f.file_hash <> '' AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			ORDER BY p.taken_at DESC LIMIT 1
		) WHERE ?`, media.PreviewExpr, condition))
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
		return nil
	}

	err = res.Error

	if err == nil {
		log.Debugf("covers: updated %s [%s]", english.Plural(int(res.RowsAffected), "album", "albums"), time.Since(start))
	} else if strings.Contains(err.Error(), "Error 1054") {
		log.Errorf("covers: failed to update albums, potentially incompatible database version")
		log.Errorf("%s see https://jira.mariadb.org/browse/MDEV-25362", err)
		return nil
	}

	return err
}

// UpdateAlbumFolderCovers updates folder album cover thumbs. When albums are
// provided, the update is limited to auto-managed folders from that list.
func UpdateAlbumFolderCovers(albums ...entity.Album) (err error) {
	mutex.Index.Lock()
	defer mutex.Index.Unlock()

	start := time.Now()

	var res *gorm.DB

	if len(albums) > 0 {
		for _, album := range albums {
			if album.AlbumType != entity.AlbumFolder || album.ThumbSrc != entity.SrcAuto || album.AlbumUID == "" {
				continue
			}

			if err = refreshAlbumCover(album); err != nil {
				return err
			}
		}

		return nil
	}

	condition := gorm.Expr("album_type = ? AND thumb_src = ?", entity.AlbumFolder, entity.SrcAuto)

	switch DbDialect() {
	case dsn.DriverMySQL:
		res = Db().Exec(`UPDATE albums LEFT JOIN (
		SELECT p2.photo_path, f.file_hash FROM files f, (
			SELECT p.photo_path, max(p.id) AS photo_id FROM photos p
			WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
			GROUP BY p.photo_path) p2 WHERE p2.photo_id = f.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			) b ON b.photo_path = albums.album_path
		SET thumb = b.file_hash WHERE ?`, media.PreviewExpr, condition)
	case dsn.DriverSQLite3:
		res = Db().Table(entity.Album{}.TableName()).UpdateColumn("thumb", gorm.Expr(`(
		SELECT f.file_hash FROM files f,(
			SELECT p.photo_path, max(p.id) AS photo_id FROM photos p
			  WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
			  GROUP BY p.photo_path
			) b
		WHERE f.photo_id = b.photo_id  AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
		AND b.photo_path = albums.album_path LIMIT 1)
		WHERE ?`, media.PreviewExpr, condition))
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
		return nil
	}

	err = res.Error

	if err == nil {
		log.Debugf("covers: updated %s [%s]", english.Plural(int(res.RowsAffected), "folder", "folders"), time.Since(start))
	} else if strings.Contains(err.Error(), "Error 1054") {
		log.Errorf("covers: failed to update folders, potentially incompatible database version")
		log.Errorf("%s see https://jira.mariadb.org/browse/MDEV-25362", err)
		return nil
	}

	return err
}

// UpdateAlbumMonthCovers updates month album cover thumbs. When albums are
// provided, the update is limited to auto-managed months from that list.
func UpdateAlbumMonthCovers(albums ...entity.Album) (err error) {
	mutex.Index.Lock()
	defer mutex.Index.Unlock()

	start := time.Now()

	var res *gorm.DB

	if len(albums) > 0 {
		for _, album := range albums {
			if album.AlbumType != entity.AlbumMonth || album.ThumbSrc != entity.SrcAuto || album.AlbumUID == "" {
				continue
			}

			if err = refreshAlbumCover(album); err != nil {
				return err
			}
		}

		return nil
	}

	condition := gorm.Expr("album_type = ? AND thumb_src = ?", entity.AlbumMonth, entity.SrcAuto)

	switch DbDialect() {
	case dsn.DriverMySQL:
		res = Db().Exec(`UPDATE albums LEFT JOIN (
		SELECT p2.photo_year, p2.photo_month, f.file_hash FROM files f, (
			SELECT p.photo_year, p.photo_month, max(p.id) AS photo_id FROM photos p
			WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
			GROUP BY p.photo_year, p.photo_month) p2 WHERE p2.photo_id = f.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			) b ON b.photo_year = albums.album_year AND b.photo_month = albums.album_month
		SET thumb = b.file_hash WHERE ?`, media.PreviewExpr, condition)
	case dsn.DriverSQLite3:
		res = Db().Table(entity.Album{}.TableName()).UpdateColumn("thumb", gorm.Expr(`(
		SELECT f.file_hash FROM files f,(
			SELECT p.photo_year, p.photo_month, max(p.id) AS photo_id FROM photos p
			  WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
			  GROUP BY p.photo_year, p.photo_month
			) b
		WHERE f.photo_id = b.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
		AND b.photo_year = albums.album_year AND b.photo_month = albums.album_month LIMIT 1)
		WHERE ?`, media.PreviewExpr, condition))
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
		return nil
	}

	err = res.Error

	if err == nil {
		log.Debugf("covers: updated %s [%s]", english.Plural(int(res.RowsAffected), "month", "months"), time.Since(start))
	} else if strings.Contains(err.Error(), "Error 1054") {
		log.Errorf("covers: failed to update calendar, potentially incompatible database version")
		log.Errorf("%s see https://jira.mariadb.org/browse/MDEV-25362", err)
		return nil
	}

	return err
}

// UpdateAlbumCovers updates album cover thumbs. When albums are provided, only
// those auto-managed entries are refreshed.
func UpdateAlbumCovers(albums ...entity.Album) (err error) {
	if len(albums) == 0 {
		if err = UpdateAlbumManualCovers(); err != nil {
			return err
		}

		if err = UpdateAlbumFolderCovers(); err != nil {
			return err
		}

		if err = UpdateAlbumMonthCovers(); err != nil {
			return err
		}

		return nil
	}

	var manualAlbums, folderAlbums, monthAlbums []entity.Album

	for _, album := range albums {
		if album.ThumbSrc != entity.SrcAuto {
			continue
		}

		switch album.AlbumType {
		case entity.AlbumManual:
			manualAlbums = append(manualAlbums, album)
		case entity.AlbumFolder:
			folderAlbums = append(folderAlbums, album)
		case entity.AlbumMonth:
			monthAlbums = append(monthAlbums, album)
		}
	}

	if len(manualAlbums) > 0 {
		if err = UpdateAlbumManualCovers(manualAlbums...); err != nil {
			return err
		}
	}

	if len(folderAlbums) > 0 {
		if err = UpdateAlbumFolderCovers(folderAlbums...); err != nil {
			return err
		}
	}

	if len(monthAlbums) > 0 {
		if err = UpdateAlbumMonthCovers(monthAlbums...); err != nil {
			return err
		}
	}

	return nil
}

// refreshAlbumCover recomputes the cover thumb for a single album when the
// cover is managed automatically.
func refreshAlbumCover(album entity.Album) error {
	if album.AlbumUID == "" {
		return nil
	}

	var err error

	switch album.AlbumType {
	case entity.AlbumManual:
		err = refreshManualAlbumCover(album)
	case entity.AlbumFolder:
		err = refreshFolderAlbumCover(album)
	case entity.AlbumMonth:
		err = refreshMonthAlbumCover(album)
	default:
		return nil
	}

	if err != nil {
		if strings.Contains(err.Error(), "no rows") || strings.Contains(err.Error(), "no cover") {
			return nil
		}

		return err
	}

	entity.FlushAlbumCache()

	return nil
}

// refreshManualAlbumCover updates the cover for a single manual album.
func refreshManualAlbumCover(album entity.Album) error {
	file, err := AlbumCoverByUID(album.AlbumUID, false)
	if err != nil {
		if strings.Contains(err.Error(), "no cover") {
			return nil
		}

		return err
	}

	if file.FileHash == "" {
		return nil
	}

	return entity.UpdateAlbum(album.AlbumUID, entity.Values{"thumb": file.FileHash})
}

// refreshFolderAlbumCover updates the cover for a single folder album.
func refreshFolderAlbumCover(album entity.Album) error {
	if album.AlbumPath == "" {
		return nil
	}

	switch DbDialect() {
	case dsn.DriverMySQL:
		res := Db().Exec(`UPDATE albums LEFT JOIN (
		SELECT p2.photo_path, f.file_hash FROM files f, (
			SELECT p.photo_path, max(p.id) AS photo_id FROM photos p
			WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_path = ?
			GROUP BY p.photo_path) p2 WHERE p2.photo_id = f.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			) b ON b.photo_path = albums.album_path
		SET thumb = b.file_hash WHERE albums.album_uid = ? AND albums.album_type = ? AND albums.thumb_src = ?`,
			album.AlbumPath,
			media.PreviewExpr,
			album.AlbumUID,
			entity.AlbumFolder,
			entity.SrcAuto,
		)

		return res.Error
	case dsn.DriverSQLite3:
		res := Db().Table(entity.Album{}.TableName()).
			Where("album_uid = ? AND album_type = ? AND thumb_src = ?", album.AlbumUID, entity.AlbumFolder, entity.SrcAuto).
			UpdateColumn("thumb", gorm.Expr(`(
		SELECT f.file_hash FROM files f,(
			SELECT p.photo_path, max(p.id) AS photo_id FROM photos p
			  WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_path = ?
			  GROUP BY p.photo_path
			) b
		WHERE f.photo_id = b.photo_id  AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
		AND b.photo_path = albums.album_path LIMIT 1
		)`, album.AlbumPath, media.PreviewExpr))

		return res.Error
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
	}

	return nil
}

// refreshMonthAlbumCover updates the cover for a single month album.
func refreshMonthAlbumCover(album entity.Album) error {
	if album.AlbumYear == 0 && album.AlbumMonth == 0 {
		return nil
	}

	switch DbDialect() {
	case dsn.DriverMySQL:
		res := Db().Exec(`UPDATE albums LEFT JOIN (
		SELECT p2.photo_year, p2.photo_month, f.file_hash FROM files f, (
			SELECT p.photo_year, p.photo_month, max(p.id) AS photo_id FROM photos p
			WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_year = ? AND p.photo_month = ?
			GROUP BY p.photo_year, p.photo_month) p2 WHERE p2.photo_id = f.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			) b ON b.photo_year = albums.album_year AND b.photo_month = albums.album_month
		SET thumb = b.file_hash WHERE albums.album_uid = ? AND albums.album_type = ? AND albums.thumb_src = ?`,
			album.AlbumYear,
			album.AlbumMonth,
			media.PreviewExpr,
			album.AlbumUID,
			entity.AlbumMonth,
			entity.SrcAuto,
		)

		return res.Error
	case dsn.DriverSQLite3:
		res := Db().Table(entity.Album{}.TableName()).
			Where("album_uid = ? AND album_type = ? AND thumb_src = ?", album.AlbumUID, entity.AlbumMonth, entity.SrcAuto).
			UpdateColumn("thumb", gorm.Expr(`(
		SELECT f.file_hash FROM files f,(
			SELECT p.photo_year, p.photo_month, max(p.id) AS photo_id FROM photos p
			  WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_year = ? AND p.photo_month = ?
			  GROUP BY p.photo_year, p.photo_month
			) b
		WHERE f.photo_id = b.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
		AND b.photo_year = albums.album_year AND b.photo_month = albums.album_month LIMIT 1
		)`, album.AlbumYear, album.AlbumMonth, media.PreviewExpr))

		return res.Error
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
	}

	return nil
}

// UpdateLabelCovers updates label cover thumbs.
func UpdateLabelCovers() (err error) {
	mutex.Index.Lock()
	defer mutex.Index.Unlock()

	start := time.Now()

	var res *gorm.DB

	condition := gorm.Expr("thumb_src = ?", entity.SrcAuto)

	switch DbDialect() {
	case dsn.DriverMySQL:
		res = Db().Exec(`UPDATE labels LEFT JOIN (
		SELECT p2.label_id, f.file_hash FROM files f, (
			SELECT pl.label_id as label_id, max(p.id) AS photo_id FROM photos p
				JOIN photos_labels pl ON pl.photo_id = p.id AND pl.uncertainty < 100
			WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
			GROUP BY pl.label_id
			UNION
			SELECT c.category_id as label_id, max(p.id) AS photo_id FROM photos p
				JOIN photos_labels pl ON pl.photo_id = p.id AND pl.uncertainty < 100
				JOIN categories c ON c.label_id = pl.label_id
			WHERE p.photo_quality > 0 AND p.photo_private = 0 AND p.deleted_at IS NULL
			GROUP BY c.category_id
			) p2 WHERE p2.photo_id = f.photo_id AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?) AND f.file_missing = 0
		) b ON b.label_id = labels.id
		SET thumb = b.file_hash WHERE ?`, media.PreviewExpr, condition)
	case dsn.DriverSQLite3:
		res = Db().Table(entity.Label{}.TableName()).UpdateColumn("thumb", gorm.Expr(`(
		SELECT f.file_hash FROM files f 
			JOIN photos_labels pl ON pl.label_id = labels.id AND pl.photo_id = f.photo_id AND pl.uncertainty < 100
			JOIN photos p ON p.id = f.photo_id AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_quality > 0
			WHERE f.deleted_at IS NULL AND f.file_hash <> '' AND f.file_missing = 0 AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			ORDER BY p.photo_quality DESC, pl.uncertainty ASC, p.taken_at DESC LIMIT 1
		) WHERE ?`, media.PreviewExpr, condition))

		if res.Error == nil {
			catRes := Db().Table(entity.Label{}.TableName()).UpdateColumn("thumb", gorm.Expr(`(
			SELECT f.file_hash FROM files f 
			JOIN photos_labels pl ON pl.photo_id = f.photo_id AND pl.uncertainty < 100
			JOIN categories c ON c.label_id = pl.label_id AND c.category_id = labels.id
			JOIN photos p ON p.id = f.photo_id AND p.photo_private = 0 AND p.deleted_at IS NULL AND p.photo_quality > 0
			WHERE f.deleted_at IS NULL AND f.file_hash <> '' AND f.file_missing = 0 AND f.file_primary = 1 AND f.file_error = '' AND f.file_type IN (?)
			ORDER BY p.photo_quality DESC, pl.uncertainty ASC, p.taken_at DESC LIMIT 1
			) WHERE thumb IS NULL`, media.PreviewExpr))

			res.RowsAffected += catRes.RowsAffected
		}
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
		return nil
	}

	err = res.Error

	if err == nil {
		log.Debugf("covers: updated %s [%s]", english.Plural(int(res.RowsAffected), "label", "labels"), time.Since(start))
	} else if strings.Contains(err.Error(), "Error 1054") {
		log.Errorf("covers: failed to update labels, potentially incompatible database version")
		log.Errorf("%s see https://jira.mariadb.org/browse/MDEV-25362", err)
		return nil
	}

	return err
}

// UpdateSubjectCovers updates subject cover thumbs.
func UpdateSubjectCovers(public bool) (err error) {
	mutex.Index.Lock()
	defer mutex.Index.Unlock()

	start := time.Now()

	var res *gorm.DB
	var photosJoin *gorm.SqlExpr

	// Use faces tagged on private pictures as cover images?
	// see https://github.com/photoprism/photoprism/issues/4238
	// and https://github.com/photoprism/photoprism/issues/2570#issuecomment-1231690056
	if public {
		photosJoin = gorm.Expr("p.id = f.photo_id AND p.deleted_at IS NULL AND p.photo_private = 0")
	} else {
		photosJoin = gorm.Expr("p.id = f.photo_id AND p.deleted_at IS NULL")
	}

	condition := gorm.Expr("subjects.subj_type = ? AND thumb_src = ?", entity.SubjPerson, entity.SrcAuto)

	// Compose SQL update query.
	switch DbDialect() {
	case dsn.DriverMySQL:
		res = Db().Exec(`UPDATE subjects LEFT JOIN (
    	SELECT m.subj_uid, m.q, MAX(m.thumb) AS marker_thumb
    		FROM markers m
    	    JOIN files f ON f.file_uid = m.file_uid AND f.deleted_at IS NULL
			JOIN photos p ON ?
			WHERE m.subj_uid <> '' AND m.subj_uid IS NOT NULL
			  AND m.marker_invalid = 0 AND m.thumb IS NOT NULL AND m.thumb <> ''
			GROUP BY m.subj_uid, m.q
			) b ON b.subj_uid = subjects.subj_uid
		SET thumb = marker_thumb WHERE ?`,
			photosJoin,
			condition,
		)
	case dsn.DriverSQLite3:
		// from := gorm.Expr(fmt.Sprintf("%s m WHERE m.subj_uid = %s.subj_uid ", markerTable, subjTable))
		res = Db().Table(entity.Subject{}.TableName()).UpdateColumn("thumb",
			gorm.Expr(`(
                SELECT m.thumb
					FROM markers m 
					JOIN files f ON f.file_uid = m.file_uid AND f.deleted_at IS NULL
					JOIN photos p ON ?
					WHERE m.subj_uid = subjects.subj_uid AND m.thumb <> ''
					ORDER BY m.subj_src DESC, m.q DESC LIMIT 1
				) WHERE ?`,
				photosJoin,
				condition,
			),
		)
	default:
		log.Warnf("sql: unsupported dialect %s", DbDialect())
		return nil
	}

	err = res.Error

	if err == nil {
		log.Debugf("covers: updated %s [%s]", english.Plural(int(res.RowsAffected), "subject", "subjects"), time.Since(start))
	} else if strings.Contains(err.Error(), "Error 1054") {
		log.Errorf("covers: failed to update subjects, potentially incompatible database version")
		log.Errorf("%s see https://jira.mariadb.org/browse/MDEV-25362", err)
		return nil
	}

	return err
}

// UpdateCoversAsync runs UpdateCovers in a goroutine and logs the
// returned error, if any, as a warning. The launched goroutine is
// registered with the shared entity package WaitGroup so config.CloseDb
// can drain in-flight work via entity.WaitForAsyncJobs before tearing
// down the database connection. A deferred recover guards against any
// future shutdown race producing a process-killing panic instead of a
// clean log line.
func UpdateCoversAsync() {
	entity.AsyncJobAdd()
	go func() {
		defer entity.AsyncJobDone()
		defer func() {
			if r := recover(); r != nil {
				log.Errorf("index: recovered from panic in UpdateCoversAsync (%v)", r)
			}
		}()
		if err := UpdateCovers(); err != nil {
			log.Warnf("index: %s (update covers)", clean.Error(err))
		}
	}()
}

// UpdateCovers updates album, subject, and label cover thumbs. It
// returns nil without doing any work when the entity database provider
// has been torn down (e.g. during test shutdown), so a stray async
// invocation after CloseDb does not panic on a nil dialect lookup.
func UpdateCovers() (err error) {
	if !coversBusy.CompareAndSwap(false, true) {
		log.Debugf("index: skipped updating covers because it is already in progress")
		return nil
	}

	defer coversBusy.Store(false)

	if entity.Db() == nil {
		log.Debugf("index: skipped updating covers because database is not connected")
		return nil
	}

	log.Debugf("index: updating covers")

	// Update Albums.
	if err = UpdateAlbumCovers(); err != nil {
		return fmt.Errorf("%s while updating album covers", err)
	}

	// Update Labels.
	if err = UpdateLabelCovers(); err != nil {
		return fmt.Errorf("%s while updating label covers", err)
	}

	// Update Subjects.
	if err = UpdateSubjectCovers(true); err != nil {
		return fmt.Errorf("%s while updating subject covers", err)
	}

	return nil
}