cmsp/internal/store/store.go

// Package store 负责本地 SQLite 数据库。
//
// 本项目的所有状态（商品、视频、下载和上传进度、货憨憨登录态）
// 都保存在这里，它是唯一的事实来源。不要再用 JSON 文件另存一份，
// 那样两边一定会不一致。
//
// 用的是 modernc.org/sqlite —— 纯 Go 实现的 SQLite，不需要 CGO，
// 也就不需要在机器上装 gcc。换成 mattn/go-sqlite3 会让新同事
// 第一次编译就卡在编译器上，所以不要换。
package store

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

	"cmsp/internal/config"

	_ "modernc.org/sqlite" // 注册名为 "sqlite" 的驱动
)

// Store 是数据库连接。用完要 Close。
type Store struct {
	db *sql.DB
}

// Open 打开（不存在就创建）数据库文件，并把表结构升级到最新。
//
// path 传 ":memory:" 可以开一个只存在于内存里的库，单元测试就用这个。
func Open(path string) (*Store, error) {
	if path != ":memory:" {
		if err := os.MkdirAll(filepath.Dir(path), 0o755); err != nil {
			return nil, fmt.Errorf("创建数据库目录失败：%w", err)
		}
	}

	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("打开数据库失败：%w", err)
	}

	// SQLite 同一时间只允许一个写入者。把连接数限制为 1，
	// 可以避免多个下载协程同时写时报 "database is locked"。
	// 本项目数据量很小，这点性能损失可以忽略。
	db.SetMaxOpenConns(1)

	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("连接数据库失败：%w", err)
	}

	s := &Store{db: db}
	if err := s.migrate(); err != nil {
		db.Close()
		return nil, err
	}
	return s, nil
}

// Close 关闭数据库。
func (s *Store) Close() error {
	return s.db.Close()
}

// DB 返回底层连接，只给同一个包里的文件用。
func (s *Store) DB() *sql.DB { return s.db }

// migrations 是按顺序执行的建表和改表语句。
//
// 怎么加一次改动（很重要，改错会丢数据）：
//  1. 在切片末尾追加一条，绝不要修改或删除已有的任何一条；
//  2. 已经发布过的语句改了，老用户的库就升不上来了；
//  3. 加完记得在 store_test.go 里补一个测试。
//
// 每条语句都必须能重复执行而不报错（用 IF NOT EXISTS），
// 因为程序每次启动都会把它们全跑一遍。
var migrations = []string{
	// 1. 商品表。一行对应货憨憨里的一个 Shopee 在线商品。
	`CREATE TABLE IF NOT EXISTS products (
		-- 货憨憨内部记录 ID，是后续所有写操作的关联键。
		-- 注意不是 Shopee 商品 ID，两者不同，别搞混。
		id                TEXT PRIMARY KEY,
		-- Shopee 商品 ID，界面上显示为「蝦皮ID」。
		item_id           TEXT NOT NULL DEFAULT '',
		item_name         TEXT NOT NULL DEFAULT '',
		main_image        TEXT NOT NULL DEFAULT '',
		shop_name         TEXT NOT NULL DEFAULT '',
		platform_shop_id  TEXT NOT NULL DEFAULT '',
		currency          TEXT NOT NULL DEFAULT '',
		min_sku_price     REAL NOT NULL DEFAULT 0,
		item_status       TEXT NOT NULL DEFAULT '',
		-- 货憨憨侧的创建时间，原样保存字符串，不做时区换算。
		created_at        TEXT NOT NULL DEFAULT '',
		-- 下面几个是本工具自己维护的状态，货憨憨不知道这些。
		-- 取值见 product.go 里的常量。
		video_status      TEXT NOT NULL DEFAULT 'pending',
		download_status   TEXT NOT NULL DEFAULT 'pending',
		upload_status     TEXT NOT NULL DEFAULT 'pending',
		last_error        TEXT NOT NULL DEFAULT '',
		-- 本地记录的同步时间，方便排查“这条什么时候拉下来的”。
		synced_at         TEXT NOT NULL DEFAULT ''
	)`,

	// 2. 按店铺查是最常用的操作，加个索引。
	`CREATE INDEX IF NOT EXISTS idx_products_shop
		ON products (platform_shop_id)`,

	// 3. 视频表。一个商品可能有多个视频，所以单独一张表。
	`CREATE TABLE IF NOT EXISTS videos (
		id            INTEGER PRIMARY KEY AUTOINCREMENT,
		-- 对应 products.id
		product_id    TEXT NOT NULL,
		-- 淘宝同款商品 ID 和视频原始地址，用于排查和去重
		source_item   TEXT NOT NULL DEFAULT '',
		source_url    TEXT NOT NULL DEFAULT '',
		-- 下载到本地后的文件路径
		local_path    TEXT NOT NULL DEFAULT '',
		file_size     INTEGER NOT NULL DEFAULT 0,
		-- 上传到货憨憨素材空间后拿到的地址
		remote_url    TEXT NOT NULL DEFAULT '',
		status        TEXT NOT NULL DEFAULT 'pending',
		last_error    TEXT NOT NULL DEFAULT '',
		created_at    TEXT NOT NULL DEFAULT ''
	)`,

	`CREATE INDEX IF NOT EXISTS idx_videos_product
		ON videos (product_id)`,

	// 4. 键值表。存货憨憨登录态这类零散数据。
	// 存进来的值可能含 token，读写时不要往日志里打。
	`CREATE TABLE IF NOT EXISTS kv (
		key        TEXT PRIMARY KEY,
		value      TEXT NOT NULL DEFAULT '',
		updated_at TEXT NOT NULL DEFAULT ''
	)`,

	// 6. 店铺缓存。只保存界面需要的白名单字段，不保存 token 或手机号。
	`CREATE TABLE IF NOT EXISTS shops (
		platform_shop_id TEXT PRIMARY KEY,
		id               TEXT NOT NULL DEFAULT '',
		shop_name        TEXT NOT NULL DEFAULT '',
		shop_alias       TEXT NOT NULL DEFAULT '',
		region           TEXT NOT NULL DEFAULT '',
		region_name      TEXT NOT NULL DEFAULT '',
		platform         TEXT NOT NULL DEFAULT '',
		status           TEXT NOT NULL DEFAULT '',
		updated_at       TEXT NOT NULL DEFAULT ''
	)`,

	`ALTER TABLE products ADD COLUMN video_diagnosis TEXT NOT NULL DEFAULT 'ok'`,

	`ALTER TABLE products ADD COLUMN quality_level TEXT NOT NULL DEFAULT ''`,

	`CREATE TABLE IF NOT EXISTS product_diagnoses (
		product_id TEXT NOT NULL,
		field      TEXT NOT NULL DEFAULT '',
		type       TEXT NOT NULL DEFAULT '',
		solution   TEXT NOT NULL DEFAULT '',
		updated_at TEXT NOT NULL DEFAULT ''
	)`,

	`CREATE INDEX IF NOT EXISTS idx_product_diagnoses_product
		ON product_diagnoses (product_id)`,

	// 11. 修正历史脏值。
	//
	// 开发期间有一版实现用过三态（missing / ok / unknown），那版迁移
	// 已经在部分机器上执行过，把整表刷成了 unknown。当前实现只认
	// missing 和 ok，unknown 会让「缺少视频」和「有视频」都筛不出东西。
	// 这里把任何非法值统一收敛成 ok，下次「下载数据」写入真实诊断。
	`UPDATE products SET video_diagnosis = 'ok'
		WHERE video_diagnosis NOT IN ('missing', 'ok')`,
}

// migrate 把表结构升级到最新。
//
// 用 schema_version 记录已经执行到第几条，这样已经跑过的语句不会重复跑。
func (s *Store) migrate() error {
	if _, err := s.db.Exec(
		`CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)`,
	); err != nil {
		return fmt.Errorf("创建版本表失败：%w", err)
	}

	var current int
	row := s.db.QueryRow(`SELECT COALESCE(MAX(version), 0) FROM schema_version`)
	if err := row.Scan(&current); err != nil {
		return fmt.Errorf("读取数据库版本失败：%w", err)
	}

	for i := current; i < len(migrations); i++ {
		if _, err := s.db.Exec(migrations[i]); err != nil {
			// 带上第几条，出问题时能直接定位到 migrations 切片。
			return fmt.Errorf("执行第 %d 条建表语句失败：%w", i+1, err)
		}
		if _, err := s.db.Exec(
			`INSERT INTO schema_version (version) VALUES (?)`, i+1,
		); err != nil {
			return fmt.Errorf("记录数据库版本失败：%w", err)
		}
	}
	return nil
}

// Version 返回当前数据库结构版本，等于已执行的建表语句条数。
func (s *Store) Version() (int, error) {
	var v int
	row := s.db.QueryRow(`SELECT COALESCE(MAX(version), 0) FROM schema_version`)
	if err := row.Scan(&v); err != nil {
		return 0, fmt.Errorf("读取数据库版本失败：%w", err)
	}
	return v, nil
}

// SetKV 写入一个键值对。已存在就覆盖。
func (s *Store) SetKV(key, value, now string) error {
	_, err := s.db.Exec(`
		INSERT INTO kv (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value = excluded.value,
		                               updated_at = excluded.updated_at`,
		key, value, now)
	if err != nil {
		return fmt.Errorf("写入 kv 失败：%w", err)
	}
	return nil
}

// GetKV 读取一个键值对。键不存在时返回空字符串和 false，不当成错误。
func (s *Store) GetKV(key string) (string, bool, error) {
	var value string
	row := s.db.QueryRow(`SELECT value FROM kv WHERE key = ?`, key)
	switch err := row.Scan(&value); {
	case err == sql.ErrNoRows:
		return "", false, nil
	case err != nil:
		return "", false, fmt.Errorf("读取 kv 失败：%w", err)
	default:
		return value, true, nil
	}
}

// DefaultPath 返回数据库文件的默认位置。
//
// 查找顺序和配置文件一致，原因见 internal/config 的 DefaultPath 注释：
// 开发模式下 exe 在 build\bin\ 里，不能只按 exe 目录算。
func DefaultPath() string {
	return config.ResolveDataPath("cmsp.db")
}