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
|
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) 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)
}
}
}
}
}
|
