1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
package database
import (
"os"
"path/filepath"
"time"
"log/slog"
"github.com/jmoiron/sqlx"
_ "github.com/mattn/go-sqlite3"
"github.com/pkg/errors"
"demoon/internal/models"
)
var (
log = slog.New(slog.NewJSONHandler(os.Stdout, nil))
dbDriver = "sqlite3"
)
// VerifyDB checks if required tables exist
func (d *DB) VerifyDB() error {
var exists int
err := d.Conn.Get(&exists, `SELECT 1 FROM sqlite_master WHERE type='table' AND name='questions' LIMIT 1`)
if err != nil {
return errors.Wrap(err, "database verification failed")
}
if exists != 1 {
return errors.New("required tables not found in database")
}
return nil
}
type DB struct {
Conn *sqlx.DB
URI string
}
func (d *DB) DBGetQuestion(id string) (*models.Question, error) {
var question models.Question
err := d.Conn.Get(&question, "SELECT * FROM questions WHERE id = ?", id)
if err != nil {
return nil, err
}
return &question, nil
}
func (d *DB) DBGetDefaultsMap() (map[string]string, error) {
rows, err := d.Conn.Queryx(`SELECT key, value FROM defaults`)
if err != nil {
return nil, err
}
defer rows.Close()
result := make(map[string]string)
for rows.Next() {
var key, value string
if err := rows.Scan(&key, &value); err != nil {
return nil, err
}
result[key] = value
}
return result, nil
}
func (d *DB) CloseAll() error {
for _, conn := range []*sqlx.DB{d.Conn} {
if err := closeConn(conn); err != nil {
return err
}
}
return nil
}
func closeConn(conn *sqlx.DB) error {
return conn.Close()
}
func Init(dbPath string) (*DB, error) {
var result DB
var err error
// Default to in-memory DB if no path specified
if dbPath == "" {
dbPath = ":memory:"
} else if !filepath.IsAbs(dbPath) {
// Convert relative paths to absolute
dbPath = filepath.Join(".", dbPath)
}
result.Conn, err = openDBConnection(dbPath, dbDriver)
if err != nil {
return nil, err
}
result.URI = dbPath
if err := testConnection(result.Conn); err != nil {
return nil, err
}
if err := result.VerifyDB(); err != nil {
return nil, errors.Wrap(err, "database schema verification failed")
}
return &result, nil
}
func openDBConnection(dbPath, driver string) (*sqlx.DB, error) {
// Ensure the directory exists
if err := os.MkdirAll(filepath.Dir(dbPath), 0755); err != nil {
return nil, errors.Wrap(err, "failed to create db directory")
}
conn, err := sqlx.Open(driver, dbPath)
if err != nil {
return nil, err
}
// SQLite-specific optimizations
conn.SetMaxOpenConns(1) // SQLite only supports one writer at a time
conn.SetConnMaxLifetime(0) // Connections don't need to be closed/reopened
return conn, nil
}
func testConnection(conn *sqlx.DB) error {
err := conn.Ping()
if err != nil {
return errors.Wrap(err, "can't ping database")
}
return nil
}
func (d *DB) PingRoutine(interval time.Duration) {
ticker := time.NewTicker(interval)
done := make(chan bool)
for {
select {
case <-done:
return
case t := <-ticker.C:
if err := testConnection(d.Conn); err != nil {
log.Error("failed to ping postrges db", "error", err, "ping_at", t)
// reconnect
if err := closeConn(d.Conn); err != nil {
log.Error("failed to close db connection", "error", err, "ping_at", t)
}
d.Conn, err = openDBConnection(d.URI, dbDriver)
if err != nil {
log.Error("failed to reconnect", "error", err, "ping_at", t)
}
}
}
}
}
|
