yovision/Sense/server/app/sense/device/store.go

package device

import (
	"context"
	"database/sql"
	"errors"
	"sort"
	"strings"
	"sync"
	"time"
)

var (
	ErrNotFound = errors.New("device not found")
	ErrConflict = errors.New("device version conflict")
)

type Store interface {
	Create(context.Context, Device) error
	Get(context.Context, string) (Device, error)
	Update(context.Context, Device, int64) error
	List(context.Context, ListFilter) (Page, error)
}

type MemoryStore struct {
	mu      sync.RWMutex
	devices map[string]Device
}

func NewMemoryStore() *MemoryStore { return &MemoryStore{devices: map[string]Device{}} }

func (s *MemoryStore) Create(_ context.Context, item Device) error {
	s.mu.Lock()
	defer s.mu.Unlock()
	s.devices[item.ID] = item
	return nil
}

func (s *MemoryStore) Get(_ context.Context, id string) (Device, error) {
	s.mu.RLock()
	defer s.mu.RUnlock()
	item, ok := s.devices[id]
	if !ok {
		return Device{}, ErrNotFound
	}
	return item, nil
}

func (s *MemoryStore) Update(_ context.Context, item Device, expected int64) error {
	s.mu.Lock()
	defer s.mu.Unlock()
	current, ok := s.devices[item.ID]
	if !ok {
		return ErrNotFound
	}
	if current.Version != expected {
		return ErrConflict
	}
	s.devices[item.ID] = item
	return nil
}

func (s *MemoryStore) List(_ context.Context, filter ListFilter) (Page, error) {
	s.mu.RLock()
	defer s.mu.RUnlock()
	items := make([]Device, 0, len(s.devices))
	keyword := strings.ToLower(strings.TrimSpace(filter.Keyword))
	for _, item := range s.devices {
		if keyword == "" || strings.Contains(strings.ToLower(item.Name+" "+item.Location), keyword) {
			items = append(items, item)
		}
	}
	sort.Slice(items, func(i, j int) bool { return items[i].CreatedAt.After(items[j].CreatedAt) })
	page, size := normalizePage(filter.Page, filter.PageSize)
	total := len(items)
	start := (page - 1) * size
	if start > total {
		start = total
	}
	end := start + size
	if end > total {
		end = total
	}
	return Page{Items: append([]Device(nil), items[start:end]...), Total: total, Page: page, PageSize: size}, nil
}

type PostgresStore struct{ database *sql.DB }

func NewPostgresStore(database *sql.DB) *PostgresStore { return &PostgresStore{database: database} }

func (s *PostgresStore) Create(ctx context.Context, item Device) error {
	_, err := s.database.ExecContext(ctx, `INSERT INTO sense_devices(id,name,location,modality,capabilities,status,adapter_status,credential_ciphertext,rtsp_credential_ciphertext,rtsp_credential_same_as_onvif,version,created_at,updated_at) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)`, item.ID, item.Name, item.Location, item.Modality, strings.Join(item.Capabilities, ","), item.Status, item.AdapterStatus, item.CredentialCiphertext, item.RTSPCredentialCiphertext, item.RTSPCredentialSameAsONVIF, item.Version, item.CreatedAt, item.UpdatedAt)
	return err
}
func (s *PostgresStore) Get(ctx context.Context, id string) (Device, error) {
	var item Device
	var capabilities string
	err := s.database.QueryRowContext(ctx, `SELECT id,name,location,modality,capabilities,status,adapter_status,credential_ciphertext,rtsp_credential_ciphertext,rtsp_credential_same_as_onvif,version,created_at,updated_at FROM sense_devices WHERE id=$1`, id).Scan(&item.ID, &item.Name, &item.Location, &item.Modality, &capabilities, &item.Status, &item.AdapterStatus, &item.CredentialCiphertext, &item.RTSPCredentialCiphertext, &item.RTSPCredentialSameAsONVIF, &item.Version, &item.CreatedAt, &item.UpdatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return Device{}, ErrNotFound
	}
	item.Capabilities = splitCapabilities(capabilities)
	item.CredentialConfigured = len(item.CredentialCiphertext) > 0
	item.RTSPCredentialConfigured = len(item.RTSPCredentialCiphertext) > 0
	return item, err
}
func (s *PostgresStore) Update(ctx context.Context, item Device, expected int64) error {
	result, err := s.database.ExecContext(ctx, `UPDATE sense_devices SET name=$2,location=$3,modality=$4,capabilities=$5,status=$6,adapter_status=$7,credential_ciphertext=$8,rtsp_credential_ciphertext=$9,rtsp_credential_same_as_onvif=$10,version=$11,updated_at=$12 WHERE id=$1 AND version=$13`, item.ID, item.Name, item.Location, item.Modality, strings.Join(item.Capabilities, ","), item.Status, item.AdapterStatus, item.CredentialCiphertext, item.RTSPCredentialCiphertext, item.RTSPCredentialSameAsONVIF, item.Version, item.UpdatedAt, expected)
	if err != nil {
		return err
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrConflict
	}
	return nil
}
func (s *PostgresStore) List(ctx context.Context, filter ListFilter) (Page, error) {
	page, size := normalizePage(filter.Page, filter.PageSize)
	keyword := "%" + strings.TrimSpace(filter.Keyword) + "%"
	var total int
	if err := s.database.QueryRowContext(ctx, `SELECT count(*) FROM sense_devices WHERE name ILIKE $1 OR location ILIKE $1`, keyword).Scan(&total); err != nil {
		return Page{}, err
	}
	rows, err := s.database.QueryContext(ctx, `SELECT id,name,location,modality,capabilities,status,adapter_status,(credential_ciphertext IS NOT NULL),(rtsp_credential_ciphertext IS NOT NULL),rtsp_credential_same_as_onvif,version,created_at,updated_at FROM sense_devices WHERE name ILIKE $1 OR location ILIKE $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3`, keyword, size, (page-1)*size)
	if err != nil {
		return Page{}, err
	}
	defer rows.Close()
	items := []Device{}
	for rows.Next() {
		var item Device
		var caps string
		if err := rows.Scan(&item.ID, &item.Name, &item.Location, &item.Modality, &caps, &item.Status, &item.AdapterStatus, &item.CredentialConfigured, &item.RTSPCredentialConfigured, &item.RTSPCredentialSameAsONVIF, &item.Version, &item.CreatedAt, &item.UpdatedAt); err != nil {
			return Page{}, err
		}
		item.Capabilities = splitCapabilities(caps)
		items = append(items, item)
	}
	return Page{Items: items, Total: total, Page: page, PageSize: size}, rows.Err()
}
func normalizePage(page, size int) (int, int) {
	if page < 1 {
		page = 1
	}
	if size < 1 {
		size = 20
	}
	if size > 100 {
		size = 100
	}
	return page, size
}
func splitCapabilities(value string) []string {
	if value == "" {
		return []string{}
	}
	return strings.Split(value, ",")
}

var _ = time.Time{}