17185300238

Committed 24 Aug 2025 06:29AM UTC coverage: 71.544% (+0.2%) from 71.34%

Build # 17185300238

Build Type

push

github

Committed by

umputun

Commit Message

refactor(web): extract persistence layer with clean interface abstraction

- Create persistence subpackage with SQLite implementation
- Define Persistence interface for storage operations
- Move JobInfo type to persistence package where it belongs
- Replace direct SQL operations with interface-based abstraction
- Update all tests to use real SQLite with temp databases (no mocks)
- Fix mutex deadlock by releasing lock before I/O operations
- Improve error handling with proper resource cleanup
- Enable SQLite WAL mode for better concurrency

This refactoring improves separation of concerns, testability, and maintainability
by isolating all database operations in a dedicated persistence layer.

Run Details

149 of 186 new or added lines in 2 files covered. (80.11%)

2 existing lines in 1 file now uncovered.

1677 of 2344 relevant lines covered (71.54%)

7.74 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

81.58

/app/web/persistence/sqlite.go

package persistence

import (
        "context"
        "database/sql"
        "fmt"
        "time"

        log "github.com/go-pkgz/lgr"
        _ "modernc.org/sqlite" // sqlite driver

        "github.com/umputun/cronn/app/web/enums"
)

// JobInfo represents a cron job with its execution state
type JobInfo struct {
        ID         string // SHA256 of command
        Command    string
        Schedule   string
        NextRun    time.Time
        LastRun    time.Time
        LastStatus enums.JobStatus
        IsRunning  bool
        Enabled    bool
        CreatedAt  time.Time
        UpdatedAt  time.Time
        SortIndex  int // original order in crontab
}

// SQLiteStore implements persistence using SQLite
type SQLiteStore struct {
        db *sql.DB
}

// NewSQLiteStore creates a new SQLite store
func NewSQLiteStore(dbPath string) (*SQLiteStore, error) {
        db, err := sql.Open("sqlite", dbPath)
        if err != nil {
                return nil, fmt.Errorf("failed to open database: %w", err)
        }

        // enable WAL mode for better concurrency
        if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {
                if closeErr := db.Close(); closeErr != nil {
                        return nil, fmt.Errorf("failed to set WAL mode: %w (also failed to close db: %v)", err, closeErr)
                }
                return nil, fmt.Errorf("failed to set WAL mode: %w", err)
        }

        return &SQLiteStore{db: db}, nil
}

// Initialize creates the database schema
func (s *SQLiteStore) Initialize() error {
        queries := []string{
                `CREATE TABLE IF NOT EXISTS jobs (
                        id TEXT PRIMARY KEY,
                        command TEXT NOT NULL,
                        schedule TEXT NOT NULL,
                        next_run INTEGER,
                        last_run INTEGER,
                        last_status TEXT,
                        enabled BOOLEAN DEFAULT 1,
                        created_at INTEGER,
                        updated_at INTEGER,
                        sort_index INTEGER DEFAULT 0
                )`,
                `CREATE TABLE IF NOT EXISTS executions (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        job_id TEXT,
                        started_at INTEGER,
                        finished_at INTEGER,
                        status TEXT,
                        exit_code INTEGER,
                        FOREIGN KEY (job_id) REFERENCES jobs(id)
                )`,
                `CREATE INDEX IF NOT EXISTS idx_executions_job_id ON executions(job_id)`,
                `CREATE INDEX IF NOT EXISTS idx_executions_started_at ON executions(started_at)`,
        }

        for _, query := range queries {
                if _, err := s.db.Exec(query); err != nil {
                        return fmt.Errorf("failed to execute query: %w", err)
                }
        }

        return nil
}

// LoadJobs retrieves all jobs from the database
func (s *SQLiteStore) LoadJobs() ([]JobInfo, error) {
        rows, err := s.db.Query(`
                SELECT id, command, schedule, next_run, last_run, last_status, enabled, created_at, updated_at 
                FROM jobs`)
        if err != nil {
                return nil, fmt.Errorf("failed to query jobs: %w", err)
        }
        defer rows.Close()

        jobs := []JobInfo{}
        for rows.Next() {
                var job JobInfo
                var nextRun, lastRun, createdAt, updatedAt sql.NullInt64
                var lastStatus sql.NullString

                err := rows.Scan(
                        &job.ID,
                        &job.Command,
                        &job.Schedule,
                        &nextRun,
                        &lastRun,
                        &lastStatus,
                        &job.Enabled,
                        &createdAt,
                        &updatedAt,
                )
                if err != nil {
                        log.Printf("[WARN] failed to scan job row: %v", err)
                        continue
                }

                // convert timestamps
                if nextRun.Valid && nextRun.Int64 > 0 {
                        job.NextRun = time.Unix(nextRun.Int64, 0)
                }
                if lastRun.Valid && lastRun.Int64 > 0 {
                        job.LastRun = time.Unix(lastRun.Int64, 0)
                }
                if createdAt.Valid {
                        job.CreatedAt = time.Unix(createdAt.Int64, 0)
                }
                if updatedAt.Valid {
                        job.UpdatedAt = time.Unix(updatedAt.Int64, 0)
                }

                // parse status
                if lastStatus.Valid && lastStatus.String != "" {
                        if status, err := enums.ParseJobStatus(lastStatus.String); err == nil {
                                job.LastStatus = status
                        } else {
                                log.Printf("[WARN] invalid job status %q for job %s: %v", lastStatus.String, job.ID, err)
                                job.LastStatus = enums.JobStatusIdle
                        }
                } else {
                        job.LastStatus = enums.JobStatusIdle
                }

                jobs = append(jobs, job)
        }

        if err := rows.Err(); err != nil {
                return nil, fmt.Errorf("error iterating rows: %w", err)
        }

        return jobs, nil
}

// SaveJobs persists multiple jobs in a transaction
func (s *SQLiteStore) SaveJobs(jobs []JobInfo) error {
        tx, err := s.db.Begin()
        if err != nil {
                return fmt.Errorf("failed to begin transaction: %w", err)
        }
        defer tx.Rollback()

        for idx, job := range jobs {
                var nextRun, lastRun int64
                if !job.NextRun.IsZero() {
                        nextRun = job.NextRun.Unix()
                }
                if !job.LastRun.IsZero() {
                        lastRun = job.LastRun.Unix()
                }

                _, err := tx.Exec(`
                        INSERT OR REPLACE INTO jobs 
                        (id, command, schedule, next_run, last_run, last_status, enabled, created_at, updated_at, sort_index)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                        job.ID,
                        job.Command,
                        job.Schedule,
                        nextRun,
                        lastRun,
                        job.LastStatus.String(),
                        job.Enabled,
                        job.CreatedAt.Unix(),
                        job.UpdatedAt.Unix(),
                        idx,
                )
                if err != nil {
                        return fmt.Errorf("failed to save job %s: %w", job.ID, err)
                }
        }

        if err := tx.Commit(); err != nil {
                return fmt.Errorf("failed to commit transaction: %w", err)
        }

        return nil
}

// RecordExecution logs a job execution event
func (s *SQLiteStore) RecordExecution(jobID string, started, finished time.Time, status enums.JobStatus, exitCode int) error {
        ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
        defer cancel()

        _, err := s.db.ExecContext(ctx, `
                INSERT INTO executions (job_id, started_at, finished_at, status, exit_code)
                VALUES (?, ?, ?, ?, ?)`,
                jobID, started.Unix(), finished.Unix(), status.String(), exitCode)

        if err != nil {
                return fmt.Errorf("failed to record execution: %w", err)
        }

        return nil
}

// Close closes the database connection
func (s *SQLiteStore) Close() error {
        return s.db.Close()
}

1	package persistence
2
3	import (
4	"context"
5	"database/sql"
6	"fmt"
7	"time"
8
9	log "github.com/go-pkgz/lgr"
10	_ "modernc.org/sqlite" // sqlite driver
11
12	"github.com/umputun/cronn/app/web/enums"
13	)
14
15	// JobInfo represents a cron job with its execution state
16	type JobInfo struct {
17	ID string // SHA256 of command
18	Command string
19	Schedule string
20	NextRun time.Time
21	LastRun time.Time
22	LastStatus enums.JobStatus
23	IsRunning bool
24	Enabled bool
25	CreatedAt time.Time
26	UpdatedAt time.Time
27	SortIndex int // original order in crontab
28	}
29
30	// SQLiteStore implements persistence using SQLite
31	type SQLiteStore struct {
32	db *sql.DB
33	}
34
35	// NewSQLiteStore creates a new SQLite store
36	func NewSQLiteStore(dbPath string) (*SQLiteStore, error) {	7✔
37	db, err := sql.Open("sqlite", dbPath)	7✔
38	if err != nil {	7✔
NEW 39	return nil, fmt.Errorf("failed to open database: %w", err)	×
NEW 40	}	×
41
42	// enable WAL mode for better concurrency
43	if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {	7✔
NEW 44	if closeErr := db.Close(); closeErr != nil {	×
NEW 45	return nil, fmt.Errorf("failed to set WAL mode: %w (also failed to close db: %v)", err, closeErr)	×
NEW 46	}	×
NEW 47	return nil, fmt.Errorf("failed to set WAL mode: %w", err)	×
48	}
49
50	return &SQLiteStore{db: db}, nil	7✔
51	}
52
53	// Initialize creates the database schema
54	func (s *SQLiteStore) Initialize() error {	6✔
55	queries := []string{	6✔
56	`CREATE TABLE IF NOT EXISTS jobs (	6✔
57	id TEXT PRIMARY KEY,	6✔
58	command TEXT NOT NULL,	6✔
59	schedule TEXT NOT NULL,	6✔
60	next_run INTEGER,	6✔
61	last_run INTEGER,	6✔
62	last_status TEXT,	6✔
63	enabled BOOLEAN DEFAULT 1,	6✔
64	created_at INTEGER,	6✔
65	updated_at INTEGER,	6✔
66	sort_index INTEGER DEFAULT 0	6✔
67	)`,	6✔
68	`CREATE TABLE IF NOT EXISTS executions (	6✔
69	id INTEGER PRIMARY KEY AUTOINCREMENT,	6✔
70	job_id TEXT,	6✔
71	started_at INTEGER,	6✔
72	finished_at INTEGER,	6✔
73	status TEXT,	6✔
74	exit_code INTEGER,	6✔
75	FOREIGN KEY (job_id) REFERENCES jobs(id)	6✔
76	)`,	6✔
77	`CREATE INDEX IF NOT EXISTS idx_executions_job_id ON executions(job_id)`,	6✔
78	`CREATE INDEX IF NOT EXISTS idx_executions_started_at ON executions(started_at)`,	6✔
79	}	6✔
80		6✔
81	for _, query := range queries {	30✔
82	if _, err := s.db.Exec(query); err != nil {	24✔
NEW 83	return fmt.Errorf("failed to execute query: %w", err)	×
NEW 84	}	×
85	}
86
87	return nil	6✔
88	}
89
90	// LoadJobs retrieves all jobs from the database
91	func (s *SQLiteStore) LoadJobs() ([]JobInfo, error) {	3✔
92	rows, err := s.db.Query(`	3✔
93	SELECT id, command, schedule, next_run, last_run, last_status, enabled, created_at, updated_at	3✔
94	FROM jobs`)	3✔
95	if err != nil {	3✔
NEW 96	return nil, fmt.Errorf("failed to query jobs: %w", err)	×
NEW 97	}	×
98	defer rows.Close()	3✔
99		3✔
100	jobs := []JobInfo{}	3✔
101	for rows.Next() {	6✔
102	var job JobInfo	3✔
103	var nextRun, lastRun, createdAt, updatedAt sql.NullInt64	3✔
104	var lastStatus sql.NullString	3✔
105		3✔
106	err := rows.Scan(	3✔
107	&job.ID,	3✔
108	&job.Command,	3✔
109	&job.Schedule,	3✔
110	&nextRun,	3✔
111	&lastRun,	3✔
112	&lastStatus,	3✔
113	&job.Enabled,	3✔
114	&createdAt,	3✔
115	&updatedAt,	3✔
116	)	3✔
117	if err != nil {	3✔
NEW 118	log.Printf("[WARN] failed to scan job row: %v", err)	×
NEW 119	continue	×
120	}
121
122	// convert timestamps
123	if nextRun.Valid && nextRun.Int64 > 0 {	5✔
124	job.NextRun = time.Unix(nextRun.Int64, 0)	2✔
125	}	2✔
126	if lastRun.Valid && lastRun.Int64 > 0 {	5✔
127	job.LastRun = time.Unix(lastRun.Int64, 0)	2✔
128	}	2✔
129	if createdAt.Valid {	6✔
130	job.CreatedAt = time.Unix(createdAt.Int64, 0)	3✔
131	}	3✔
132	if updatedAt.Valid {	6✔
133	job.UpdatedAt = time.Unix(updatedAt.Int64, 0)	3✔
134	}	3✔
135
136	// parse status
137	if lastStatus.Valid && lastStatus.String != "" {	6✔
138	if status, err := enums.ParseJobStatus(lastStatus.String); err == nil {	6✔
139	job.LastStatus = status	3✔
140	} else {	3✔
NEW 141	log.Printf("[WARN] invalid job status %q for job %s: %v", lastStatus.String, job.ID, err)	×
NEW 142	job.LastStatus = enums.JobStatusIdle	×
NEW 143	}	×
NEW 144	} else {	×
NEW 145	job.LastStatus = enums.JobStatusIdle	×
NEW 146	}	×
147
148	jobs = append(jobs, job)	3✔
149	}
150
151	if err := rows.Err(); err != nil {	3✔
NEW 152	return nil, fmt.Errorf("error iterating rows: %w", err)	×
NEW 153	}	×
154
155	return jobs, nil	3✔
156	}
157
158	// SaveJobs persists multiple jobs in a transaction
159	func (s *SQLiteStore) SaveJobs(jobs []JobInfo) error {	3✔
160	tx, err := s.db.Begin()	3✔
161	if err != nil {	3✔
NEW 162	return fmt.Errorf("failed to begin transaction: %w", err)	×
NEW 163	}	×
164	defer tx.Rollback()	3✔
165		3✔
166	for idx, job := range jobs {	7✔
167	var nextRun, lastRun int64	4✔
168	if !job.NextRun.IsZero() {	6✔
169	nextRun = job.NextRun.Unix()	2✔
170	}	2✔
171	if !job.LastRun.IsZero() {	6✔
172	lastRun = job.LastRun.Unix()	2✔
173	}	2✔
174
175	_, err := tx.Exec(`	4✔
176	INSERT OR REPLACE INTO jobs	4✔
177	(id, command, schedule, next_run, last_run, last_status, enabled, created_at, updated_at, sort_index)	4✔
178	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,	4✔
179	job.ID,	4✔
180	job.Command,	4✔
181	job.Schedule,	4✔
182	nextRun,	4✔
183	lastRun,	4✔
184	job.LastStatus.String(),	4✔
185	job.Enabled,	4✔
186	job.CreatedAt.Unix(),	4✔
187	job.UpdatedAt.Unix(),	4✔
188	idx,	4✔
189	)	4✔
190	if err != nil {	4✔
NEW 191	return fmt.Errorf("failed to save job %s: %w", job.ID, err)	×
NEW 192	}	×
193	}
194
195	if err := tx.Commit(); err != nil {	3✔
NEW 196	return fmt.Errorf("failed to commit transaction: %w", err)	×
NEW 197	}	×
198
199	return nil	3✔
200	}
201
202	// RecordExecution logs a job execution event
203	func (s *SQLiteStore) RecordExecution(jobID string, started, finished time.Time, status enums.JobStatus, exitCode int) error {	1✔
204	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)	1✔
205	defer cancel()	1✔
206		1✔
207	_, err := s.db.ExecContext(ctx, `	1✔
208	INSERT INTO executions (job_id, started_at, finished_at, status, exit_code)	1✔
209	VALUES (?, ?, ?, ?, ?)`,	1✔
210	jobID, started.Unix(), finished.Unix(), status.String(), exitCode)	1✔
211		1✔
212	if err != nil {	1✔
NEW 213	return fmt.Errorf("failed to record execution: %w", err)	×
NEW 214	}	×
215
216	return nil	1✔
217	}
218
219	// Close closes the database connection
220	func (s *SQLiteStore) Close() error {	7✔
221	return s.db.Close()	7✔
222	}	7✔

umputun / cronn / 17185300238

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous