sqlite.go

package store

import (

	"database/sql"

	"fmt"

	"regexp"

	"strings"

	"sync"

	_ "modernc.org/sqlite"

	"example.com/curator/internal/config"

)

// Store is a database-backed module store implementing ModuleResolver.

// It supports both SQLite and PostgreSQL backends.

type Store struct {

	db      *sql.DB

	dialect dialect

	// Compiled pattern cache for fast resolution.

	mu       sync.RWMutex

	patterns []compiledPattern

}

type compiledPattern struct {

	PatternRow

	re *regexp.Regexp

}

// Open opens a database with the given driver and DSN, then runs migrations.

// Supported drivers: "sqlite", "postgres".

//

// For SQLite the DSN is a file path (WAL mode and busy timeout are set automatically).

// For PostgreSQL the DSN is a connection string (e.g. "postgres://user:pass@host/db?sslmode=disable").

func Open(driver, dsn string) (*Store, error) {

	d, err := dialectFor(driver)

	if err != nil {

		return nil, err

	}

	// SQLite: append pragmas if not already present.

	if driver == "sqlite" && !strings.Contains(dsn, "_pragma") {

		dsn = dsn + "?_pragma=journal_mode(wal)&_pragma=busy_timeout(5000)"

	}

	db, err := sql.Open(d.driverName, dsn)

	if err != nil {

		return nil, fmt.Errorf("open database: %w", err)

	}

	if err := db.Ping(); err != nil {

		db.Close()

		return nil, fmt.Errorf("ping database: %w", err)

	}

	if _, err := db.Exec(d.migrationSQL); err != nil {

		db.Close()

		return nil, fmt.Errorf("migrate database: %w", err)

	}

	st := &Store{db: db, dialect: d}

	if err := st.refreshPatterns(); err != nil {

		db.Close()

		return nil, fmt.Errorf("load patterns: %w", err)

	}

	return st, nil

}

// Close closes the database connection.

func (st *Store) Close() error {

	return st.db.Close()

}

// ResolveModule looks up a module by name: exact match first, then patterns.

func (st *Store) ResolveModule(name string) (config.Module, bool) {

	// Try exact match from database.

	var mod config.Module

	var private int

	err := st.db.QueryRow(st.dialect.resolveModuleSQL, name).Scan(&mod.VCS, &mod.Repo, &mod.Web, &private)

	if err == nil {

		mod.Private = private != 0

		return mod, true

	}

	// Try compiled patterns.

	st.mu.RLock()

	defer st.mu.RUnlock()

	for _, cp := range st.patterns {

		matches := cp.re.FindStringSubmatch(name)

		if matches == nil {

			continue

		}

		groups := matches[1:]

		return config.Module{

			VCS:     expandTemplate(cp.VCS, name, groups),

			Repo:    expandTemplate(cp.Repo, name, groups),

			Web:     expandTemplate(cp.Web, name, groups),

			Private: cp.Private,

		}, true

	}

	return config.Module{}, false

}

// ListModules returns all modules.

func (st *Store) ListModules() ([]ModuleRow, error) {

	rows, err := st.db.Query("SELECT name, vcs, repo, web, private, created_at FROM modules ORDER BY name")

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var modules []ModuleRow

	for rows.Next() {

		var m ModuleRow

		var private int

		if err := rows.Scan(&m.Name, &m.VCS, &m.Repo, &m.Web, &private, &m.CreatedAt); err != nil {

			return nil, err

		}

		m.Private = private != 0

		modules = append(modules, m)

	}

	return modules, rows.Err()

}

// AddModule adds a module to the database.

func (st *Store) AddModule(m ModuleRow) error {

	private := 0

	if m.Private {

		private = 1

	}

	_, err := st.db.Exec(st.dialect.addModuleSQL, m.Name, m.VCS, m.Repo, m.Web, private)

	return err

}

// DeleteModule removes a module from the database.

func (st *Store) DeleteModule(name string) error {

	result, err := st.db.Exec(st.dialect.deleteModuleSQL, name)

	if err != nil {

		return err

	}

	n, err := result.RowsAffected()

	if err != nil {

		return err

	}

	if n == 0 {

		return sql.ErrNoRows

	}

	return nil

}

// ListPatterns returns all module patterns ordered by priority.

func (st *Store) ListPatterns() ([]PatternRow, error) {

	rows, err := st.db.Query("SELECT id, pattern, vcs, repo, web, private, priority, created_at FROM module_patterns ORDER BY priority, id")

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var patterns []PatternRow

	for rows.Next() {

		var p PatternRow

		var private int

		if err := rows.Scan(&p.ID, &p.Pattern, &p.VCS, &p.Repo, &p.Web, &private, &p.Priority, &p.CreatedAt); err != nil {

			return nil, err

		}

		p.Private = private != 0

		patterns = append(patterns, p)

	}

	return patterns, rows.Err()

}

// AddPattern adds a module pattern to the database and refreshes the cache.

func (st *Store) AddPattern(p PatternRow) error {

	private := 0

	if p.Private {

		private = 1

	}

	_, err := st.db.Exec(st.dialect.addPatternSQL, p.Pattern, p.VCS, p.Repo, p.Web, private, p.Priority)

	if err != nil {

		return err

	}

	return st.refreshPatterns()

}

// DeletePattern removes a module pattern and refreshes the cache.

func (st *Store) DeletePattern(id int64) error {

	result, err := st.db.Exec(st.dialect.deletePatternSQL, id)

	if err != nil {

		return err

	}

	n, err := result.RowsAffected()

	if err != nil {

		return err

	}

	if n == 0 {

		return sql.ErrNoRows

	}

	return st.refreshPatterns()

}

// ModuleCount returns the total number of configured modules (exact + patterns).

func (st *Store) ModuleCount() int {

	var count int

	st.db.QueryRow("SELECT COUNT(*) FROM modules").Scan(&count)

	st.mu.RLock()

	count += len(st.patterns)

	st.mu.RUnlock()

	return count

}

func (st *Store) refreshPatterns() error {

	rows, err := st.db.Query("SELECT id, pattern, vcs, repo, web, private, priority FROM module_patterns ORDER BY priority, id")

	if err != nil {

		return err

	}

	defer rows.Close()

	var patterns []compiledPattern

	for rows.Next() {

		var cp compiledPattern

		var private int

		if err := rows.Scan(&cp.ID, &cp.Pattern, &cp.VCS, &cp.Repo, &cp.Web, &private, &cp.Priority); err != nil {

			return err

		}

		cp.Private = private != 0

		re, err := regexp.Compile("^" + cp.Pattern + "$")

		if err != nil {

			return fmt.Errorf("compile pattern %q: %w", cp.Pattern, err)

		}

		cp.re = re

		patterns = append(patterns, cp)

	}

	st.mu.Lock()

	st.patterns = patterns

	st.mu.Unlock()

	return rows.Err()

}

func expandTemplate(tmpl, name string, groups []string) string {

	s := strings.ReplaceAll(tmpl, "{name}", name)

	for i, g := range groups {

		s = strings.ReplaceAll(s, fmt.Sprintf("{%d}", i+1), g)

	}

	return s

}