miniflux_v2/internal/storage/icon.go

// SPDX-FileCopyrightText: Copyright The Miniflux Authors. All rights reserved.
// SPDX-License-Identifier: Apache-2.0

package storage // import "miniflux.app/v2/internal/storage"

import (
	"database/sql"
	"errors"
	"fmt"
	"strings"

	"miniflux.app/v2/internal/crypto"
	"miniflux.app/v2/internal/model"
)

// HasFeedIcon reports whether the specified feed already has an associated icon record.
func (s *Storage) HasFeedIcon(feedID int64) bool {
	var result bool
	query := `SELECT true FROM feed_icons WHERE feed_id=$1 LIMIT 1`
	s.db.QueryRow(query, feedID).Scan(&result)
	return result
}

// IconByUserAndIconID fetches a single icon by its internal identifier, scoped
// to the given user. It returns nil when the icon does not exist or is not
// associated with any feed owned by the user.
func (s *Storage) IconByUserAndIconID(userID, iconID int64) (*model.Icon, error) {
	var icon model.Icon
	query := `
		SELECT
			i.id,
			i.hash,
			i.mime_type,
			i.content,
			i.external_id
		FROM icons AS i
		WHERE i.id = $2
			AND EXISTS (
				SELECT 1
				FROM feeds AS f
				INNER JOIN feed_icons AS fi ON fi.feed_id = f.id
				WHERE f.user_id = $1
					AND fi.icon_id = $2
			)`
	err := s.db.QueryRow(query, userID, iconID).Scan(&icon.ID, &icon.Hash, &icon.MimeType, &icon.Content, &icon.ExternalID)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return nil, nil
	case err != nil:
		return nil, fmt.Errorf("store: cannot load icon id=%d for user_id=%d: %w", iconID, userID, err)
	default:
		return &icon, nil
	}
}

// IconByExternalID fetches an icon using its external identifier, returning nil when no match exists.
func (s *Storage) IconByExternalID(externalIconID string) (*model.Icon, error) {
	var icon model.Icon
	query := `
		SELECT
			id,
			hash,
			mime_type,
			content,
			external_id
		FROM icons
		WHERE external_id=$1
	`
	err := s.db.QueryRow(query, externalIconID).Scan(&icon.ID, &icon.Hash, &icon.MimeType, &icon.Content, &icon.ExternalID)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return nil, nil
	case err != nil:
		return nil, fmt.Errorf("store: cannot load icon external_id=%s: %w", externalIconID, err)
	default:
		return &icon, nil
	}
}

// IconByFeedID returns the icon linked to the given feed for the specified user, or nil if none is set.
func (s *Storage) IconByFeedID(userID, feedID int64) (*model.Icon, error) {
	query := `
		SELECT
			icons.id,
			icons.hash,
			icons.mime_type,
			icons.content,
			icons.external_id
		FROM icons
		INNER JOIN feed_icons ON feed_icons.icon_id=icons.id
		INNER JOIN feeds ON feeds.id=feed_icons.feed_id
		WHERE
			feeds.user_id=$1 AND feeds.id=$2
		LIMIT 1
	`
	var icon model.Icon
	err := s.db.QueryRow(query, userID, feedID).Scan(&icon.ID, &icon.Hash, &icon.MimeType, &icon.Content, &icon.ExternalID)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return nil, nil
	case err != nil:
		return nil, fmt.Errorf("store: cannot load icon for feed_id=%d user_id=%d: %w", feedID, userID, err)
	default:
		return &icon, nil
	}
}

// StoreFeedIcon creates or reuses an icon by hash and associates it with the given feed atomically.
func (s *Storage) StoreFeedIcon(feedID int64, icon *model.Icon) error {
	tx, err := s.db.Begin()
	if err != nil {
		return fmt.Errorf(`store: unable to start transaction: %v`, err)
	}

	err = tx.QueryRow(`SELECT id FROM icons WHERE hash=$1`, icon.Hash).Scan(&icon.ID)
	if errors.Is(err, sql.ErrNoRows) {
		query := `
			INSERT INTO icons
				(hash, mime_type, content, external_id)
			VALUES
				($1, $2, $3, $4)
			RETURNING
				id
		`
		err := tx.QueryRow(
			query,
			icon.Hash,
			normalizeMimeType(icon.MimeType),
			icon.Content,
			crypto.GenerateRandomStringHex(20),
		).Scan(&icon.ID)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf(`store: unable to create icon: %v`, err)
		}
	} else if err != nil {
		tx.Rollback()
		return fmt.Errorf(`store: unable to fetch icon by hash %q: %v`, icon.Hash, err)
	}

	if _, err := tx.Exec(`DELETE FROM feed_icons WHERE feed_id=$1`, feedID); err != nil {
		tx.Rollback()
		return fmt.Errorf(`store: unable to delete feed icon: %v`, err)
	}

	if _, err := tx.Exec(`INSERT INTO feed_icons (feed_id, icon_id) VALUES ($1, $2)`, feedID, icon.ID); err != nil {
		tx.Rollback()
		return fmt.Errorf(`store: unable to associate feed and icon: %v`, err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf(`store: unable to commit transaction: %v`, err)
	}

	return nil
}

// CleanupOrphanIcons removes icons that are no longer associated with any
// feed. Such rows accumulate when feeds are deleted (the cascade only removes
// the feed_icons mapping, not the dedup-by-hash icons row) or when a feed's
// icon is replaced by StoreFeedIcon.
func (s *Storage) CleanupOrphanIcons() (int64, error) {
	result, err := s.db.Exec(`
		DELETE FROM icons
		WHERE NOT EXISTS (
			SELECT 1 FROM feed_icons WHERE feed_icons.icon_id = icons.id
		)
	`)
	if err != nil {
		return 0, fmt.Errorf(`store: unable to clean orphan icons: %v`, err)
	}

	n, _ := result.RowsAffected()
	return n, nil
}

// Icons lists all icons currently associated with any feed owned by the given user.
func (s *Storage) Icons(userID int64) (model.Icons, error) {
	query := `
		SELECT
			icons.id,
			icons.hash,
			icons.mime_type,
			icons.content,
			icons.external_id
		FROM icons
		INNER JOIN feed_icons ON feed_icons.icon_id=icons.id
		INNER JOIN feeds ON feeds.id=feed_icons.feed_id
		WHERE
			feeds.user_id=$1
	`
	rows, err := s.db.Query(query, userID)
	if err != nil {
		return nil, fmt.Errorf(`store: unable to fetch icons: %v`, err)
	}
	defer rows.Close()

	var icons model.Icons
	for rows.Next() {
		var icon model.Icon
		err := rows.Scan(&icon.ID, &icon.Hash, &icon.MimeType, &icon.Content, &icon.ExternalID)
		if err != nil {
			return nil, fmt.Errorf(`store: unable to fetch icons row: %v`, err)
		}
		icons = append(icons, &icon)
	}

	return icons, nil
}

func normalizeMimeType(mimeType string) string {
	mimeType = strings.ToLower(mimeType)
	switch mimeType {
	case "image/png", "image/jpeg", "image/jpg", "image/webp", "image/svg+xml", "image/x-icon", "image/gif":
		return mimeType
	default:
		return "image/x-icon"
	}
}