Repository navigation
Expand file tree
/
Copy pathdatabase.go
More file actions
156 lines (129 loc) · 4.85 KB
/
Copy pathdatabase.go
File metadata and controls
156 lines (129 loc) · 4.85 KB
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
155
156
package database
import (
"database/sql"
"fmt"
"log"
"os"
"path/filepath"
_ "github.com/mattn/go-sqlite3"
)
// Database represents the database connection
type Database struct {
Connection *sql.DB
}
// DB is the global database connection
var DB *sql.DB
// Init initializes the database connection and runs migrations
func Init(dbPath string) error {
// Ensure the directory exists
dir := filepath.Dir(dbPath)
log.Printf("Creating database directory: %s", dir)
if err := os.MkdirAll(dir, 0755); err != nil {
return fmt.Errorf("failed to create database directory: %w", err)
}
// Check if directory is writable
testFile := filepath.Join(dir, ".write_test")
if err := os.WriteFile(testFile, []byte("test"), 0644); err != nil {
return fmt.Errorf("database directory is not writable: %w", err)
}
os.Remove(testFile)
log.Printf("Opening database at: %s", dbPath)
// Open database connection
db, err := sql.Open("sqlite3", dbPath+"?_foreign_keys=on")
if err != nil {
return fmt.Errorf("failed to open database: %w", err)
}
// Enable foreign keys
if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
return fmt.Errorf("failed to enable foreign keys: %w", err)
}
// Test connection
if err := db.Ping(); err != nil {
return fmt.Errorf("failed to ping database: %w", err)
}
DB = db
log.Printf("Database connected: %s", dbPath)
// Run migrations
if err := runMigrations(); err != nil {
return fmt.Errorf("failed to run migrations: %w", err)
}
return nil
}
// runMigrations executes database migrations
func runMigrations() error {
// Check if users table exists to determine if migrations are needed
var tableName string
err := DB.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name='users'").Scan(&tableName)
if err == sql.ErrNoRows {
// Tables don't exist, run initial migration
log.Println("Running initial database migration...")
migrationSQL, err := os.ReadFile("migrations/001_initial_schema.up.sql")
if err != nil {
return fmt.Errorf("failed to read migration file: %w", err)
}
if _, err := DB.Exec(string(migrationSQL)); err != nil {
return fmt.Errorf("failed to execute migration: %w", err)
}
log.Println("Initial migration completed successfully")
} else if err != nil {
return fmt.Errorf("failed to check for existing tables: %w", err)
}
// Check if app_settings table exists to determine if we need to run the second migration
var settingsTableName string
err = DB.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name='app_settings'").Scan(&settingsTableName)
if err == sql.ErrNoRows {
// app_settings table doesn't exist, run second migration
log.Println("Running app settings migration...")
migrationSQL, err := os.ReadFile("migrations/002_app_settings.up.sql")
if err != nil {
return fmt.Errorf("failed to read migration file: %w", err)
}
if _, err := DB.Exec(string(migrationSQL)); err != nil {
return fmt.Errorf("failed to execute migration: %w", err)
}
log.Println("App settings migration completed successfully")
} else if err != nil && err != sql.ErrNoRows {
return fmt.Errorf("failed to check for app_settings table: %w", err)
}
// Check if forms table has the new domain column to determine if we need to run the third migration
var domainColumn string
err = DB.QueryRow("SELECT name FROM pragma_table_info('forms') WHERE name = 'domain'").Scan(&domainColumn)
if err == sql.ErrNoRows {
// forms table doesn't have domain column, run third migration
log.Println("Running form schema update migration...")
migrationSQL, err := os.ReadFile("migrations/003_update_form_schema.up.sql")
if err != nil {
return fmt.Errorf("failed to read migration file: %w", err)
}
if _, err := DB.Exec(string(migrationSQL)); err != nil {
return fmt.Errorf("failed to execute migration: %w", err)
}
log.Println("Form schema update migration completed successfully")
} else if err != nil && err != sql.ErrNoRows {
return fmt.Errorf("failed to check for forms table columns: %w", err)
}
// Ensure callback_url column exists (005 migration)
var callbackColumn string
err = DB.QueryRow("SELECT name FROM pragma_table_info('forms') WHERE name = 'callback_url'").Scan(&callbackColumn)
if err == sql.ErrNoRows {
log.Println("Running callback_url column migration...")
migrationSQL, err := os.ReadFile("migrations/005_add_callback_url.up.sql")
if err != nil {
return fmt.Errorf("failed to read migration file: %w", err)
}
if _, err := DB.Exec(string(migrationSQL)); err != nil {
return fmt.Errorf("failed to execute migration: %w", err)
}
log.Println("callback_url column migration completed successfully")
} else if err != nil && err != sql.ErrNoRows {
return fmt.Errorf("failed to check for callback_url column: %w", err)
}
return nil
}
// Close closes the database connection
func Close() error {
if DB != nil {
return DB.Close()
}
return nil
}