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)

	}

	// Add credential_name column to existing tables (idempotent).

	addColumnIfNotExists(db, "modules", "credential_name", "TEXT NOT NULL DEFAULT ''")

	addColumnIfNotExists(db, "module_patterns", "credential_name", "TEXT NOT NULL DEFAULT ''")

	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

	var credName string

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

	if err == nil {

		mod.Private = private != 0

		mod.CredentialName = credName

		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,

			CredentialName: cp.CredentialName,

		}, 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, credential_name, 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.CredentialName, &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, m.CredentialName)

	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, credential_name, 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.CredentialName, &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, p.CredentialName)

	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, credential_name 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, &cp.CredentialName); 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

}

// addColumnIfNotExists runs ALTER TABLE ADD COLUMN and ignores errors

// indicating the column already exists (for idempotent migrations).

func addColumnIfNotExists(db *sql.DB, table, column, colDef string) {

	_, _ = db.Exec(fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s %s", table, column, colDef))

}

// ListCredentials returns all credentials ordered by name.

func (st *Store) ListCredentials() ([]CredentialRow, error) {

	rows, err := st.db.Query(st.dialect.listCredentialsSQL)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var creds []CredentialRow

	for rows.Next() {

		var c CredentialRow

		if err := rows.Scan(&c.Name, &c.Type, &c.Data, &c.CreatedAt); err != nil {

			return nil, err

		}

		creds = append(creds, c)

	}

	return creds, rows.Err()

}

// GetCredential returns a credential by name.

func (st *Store) GetCredential(name string) (CredentialRow, error) {

	var c CredentialRow

	err := st.db.QueryRow(st.dialect.getCredentialSQL, name).Scan(&c.Name, &c.Type, &c.Data, &c.CreatedAt)

	return c, err

}

// AddCredential inserts a new credential.

func (st *Store) AddCredential(c CredentialRow) error {

	_, err := st.db.Exec(st.dialect.addCredentialSQL, c.Name, c.Type, c.Data)

	return err

}

// UpdateCredential updates an existing credential's type and data.

func (st *Store) UpdateCredential(c CredentialRow) error {

	result, err := st.db.Exec(st.dialect.updateCredentialSQL, c.Type, c.Data, c.Name)

	if err != nil {

		return err

	}

	n, err := result.RowsAffected()

	if err != nil {

		return err

	}

	if n == 0 {

		return sql.ErrNoRows

	}

	return nil

}

// DeleteCredential removes a credential by name.

// It returns an error if the credential is referenced by any module or pattern.

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

	// Check for references in modules.

	var count int

	st.db.QueryRow("SELECT COUNT(*) FROM modules WHERE credential_name = ?", name).Scan(&count)

	if count > 0 {

		return fmt.Errorf("credential %q is referenced by %d module(s)", name, count)

	}

	// Check for references in patterns.

	st.db.QueryRow("SELECT COUNT(*) FROM module_patterns WHERE credential_name = ?", name).Scan(&count)

	if count > 0 {

		return fmt.Errorf("credential %q is referenced by %d pattern(s)", name, count)

	}

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

	if err != nil {

		return err

	}

	n, err := result.RowsAffected()

	if err != nil {

		return err

	}

	if n == 0 {

		return sql.ErrNoRows

	}

	return nil

}

// CredentialCount returns the total number of credentials.

func (st *Store) CredentialCount() int {

	var count int

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

	return count

}