• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

joohoi / acme-dns / 21649490119

03 Feb 2026 09:59PM UTC coverage: 77.593% (+4.3%) from 73.276%
21649490119

Pull #325

github

joohoi
Fix linter and umask setting
Pull Request #325: Refactoring

578 of 754 new or added lines in 20 files covered. (76.66%)

3 existing lines in 1 file now uncovered.

793 of 1022 relevant lines covered (77.59%)

7.23 hits per line

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

81.68
/pkg/database/db.go
1
package database
2

3
import (
4
        "database/sql"
5
        "encoding/json"
6
        "fmt"
7
        "regexp"
8
        "strconv"
9
        "sync"
10
        "time"
11

12
        _ "github.com/glebarez/go-sqlite"
13
        _ "github.com/lib/pq"
14

15
        "github.com/google/uuid"
16
        "go.uber.org/zap"
17
        "golang.org/x/crypto/bcrypt"
18

19
        "github.com/joohoi/acme-dns/pkg/acmedns"
20
)
21

22
type acmednsdb struct {
23
        DB     *sql.DB
24
        Mutex  sync.Mutex
25
        Logger *zap.SugaredLogger
26
        Config *acmedns.AcmeDnsConfig
27
}
28

29
// DBVersion shows the database version this code uses. This is used for update checks.
30
var DBVersion = 1
31

32
var acmeTable = `
33
        CREATE TABLE IF NOT EXISTS acmedns(
34
                Name TEXT,
35
                Value TEXT
36
        );`
37

38
var userTable = `
39
        CREATE TABLE IF NOT EXISTS records(
40
        Username TEXT UNIQUE NOT NULL PRIMARY KEY,
41
        Password TEXT UNIQUE NOT NULL,
42
        Subdomain TEXT UNIQUE NOT NULL,
43
                AllowFrom TEXT
44
    );`
45

46
var txtTable = `
47
    CREATE TABLE IF NOT EXISTS txt(
48
                Subdomain TEXT NOT NULL,
49
                Value   TEXT NOT NULL DEFAULT '',
50
                LastUpdate INT
51
        );`
52

53
var txtTablePG = `
54
    CREATE TABLE IF NOT EXISTS txt(
55
                rowid SERIAL,
56
                Subdomain TEXT NOT NULL,
57
                Value   TEXT NOT NULL DEFAULT '',
58
                LastUpdate INT
59
        );`
60

61
// getSQLiteStmt replaces all PostgreSQL prepared statement placeholders (eg. $1, $2) with SQLite variant "?"
62
func getSQLiteStmt(s string) string {
30✔
63
        re, _ := regexp.Compile(`\$[0-9]`)
30✔
64
        return re.ReplaceAllString(s, "?")
30✔
65
}
30✔
66

67
func Init(config *acmedns.AcmeDnsConfig, logger *zap.SugaredLogger) (acmedns.AcmednsDB, error) {
9✔
68
        var d = &acmednsdb{Config: config, Logger: logger}
9✔
69
        d.Mutex.Lock()
9✔
70
        defer d.Mutex.Unlock()
9✔
71
        db, err := sql.Open(config.Database.Engine, config.Database.Connection)
9✔
72
        if err != nil {
9✔
NEW
73
                return d, err
×
74
        }
×
75
        d.DB = db
9✔
76
        // Check version first to try to catch old versions without version string
9✔
77
        var versionString string
9✔
78
        _ = d.DB.QueryRow("SELECT Value FROM acmedns WHERE Name='db_version'").Scan(&versionString)
9✔
79
        if versionString == "" {
18✔
80
                versionString = "0"
9✔
81
        }
9✔
82
        _, _ = d.DB.Exec(acmeTable)
9✔
83
        _, _ = d.DB.Exec(userTable)
9✔
84
        if config.Database.Engine == "sqlite" {
18✔
85
                _, _ = d.DB.Exec(txtTable)
9✔
86
        } else {
9✔
87
                _, _ = d.DB.Exec(txtTablePG)
×
88
        }
×
89
        // If everything is fine, handle db upgrade tasks
90
        if err == nil {
18✔
91
                err = d.checkDBUpgrades(versionString)
9✔
92
        }
9✔
93
        if err == nil {
18✔
94
                if versionString == "0" {
18✔
95
                        // No errors so we should now be in version 1
9✔
96
                        insversion := fmt.Sprintf("INSERT INTO acmedns (Name, Value) values('db_version', '%d')", DBVersion)
9✔
97
                        _, err = db.Exec(insversion)
9✔
98
                }
9✔
99
        }
100
        return d, err
9✔
101
}
102

103
func (d *acmednsdb) checkDBUpgrades(versionString string) error {
9✔
104
        var err error
9✔
105
        version, err := strconv.Atoi(versionString)
9✔
106
        if err != nil {
9✔
107
                return err
×
108
        }
×
109
        if version != DBVersion {
18✔
110
                return d.handleDBUpgrades(version)
9✔
111
        }
9✔
112
        return nil
×
113

114
}
115

116
func (d *acmednsdb) handleDBUpgrades(version int) error {
9✔
117
        if version == 0 {
18✔
118
                return d.handleDBUpgradeTo1()
9✔
119
        }
9✔
120
        return nil
×
121
}
122

123
func (d *acmednsdb) handleDBUpgradeTo1() error {
9✔
124
        var err error
9✔
125
        var subdomains []string
9✔
126
        rows, err := d.DB.Query("SELECT Subdomain FROM records")
9✔
127
        if err != nil {
9✔
NEW
128
                d.Logger.Errorw("Error in DB upgrade",
×
NEW
129
                        "error", err.Error())
×
130
                return err
×
131
        }
×
132
        defer rows.Close()
9✔
133
        for rows.Next() {
9✔
134
                var subdomain string
×
135
                err = rows.Scan(&subdomain)
×
136
                if err != nil {
×
NEW
137
                        d.Logger.Errorw("Error in DB upgrade while reading values",
×
NEW
138
                                "error", err.Error())
×
139
                        return err
×
140
                }
×
141
                subdomains = append(subdomains, subdomain)
×
142
        }
143
        err = rows.Err()
9✔
144
        if err != nil {
9✔
NEW
145
                d.Logger.Errorw("Error in DB upgrade while inserting values",
×
NEW
146
                        "error", err.Error())
×
147
                return err
×
148
        }
×
149
        tx, err := d.DB.Begin()
9✔
150
        // Rollback if errored, commit if not
9✔
151
        defer func() {
18✔
152
                if err != nil {
9✔
153
                        _ = tx.Rollback()
×
154
                        return
×
155
                }
×
156
                _ = tx.Commit()
9✔
157
        }()
158
        _, _ = tx.Exec("DELETE FROM txt")
9✔
159
        for _, subdomain := range subdomains {
9✔
160
                if subdomain != "" {
×
161
                        // Insert two rows for each subdomain to txt table
×
162
                        err = d.NewTXTValuesInTransaction(tx, subdomain)
×
163
                        if err != nil {
×
NEW
164
                                d.Logger.Errorw("Error in DB upgrade while inserting values",
×
NEW
165
                                        "error", err.Error())
×
166
                                return err
×
167
                        }
×
168
                }
169
        }
170
        // SQLite doesn't support dropping columns
171
        if d.Config.Database.Engine != "sqlite" {
9✔
172
                _, _ = tx.Exec("ALTER TABLE records DROP COLUMN IF EXISTS Value")
×
173
                _, _ = tx.Exec("ALTER TABLE records DROP COLUMN IF EXISTS LastActive")
×
174
        }
×
175
        _, err = tx.Exec("UPDATE acmedns SET Value='1' WHERE Name='db_version'")
9✔
176
        return err
9✔
177
}
178

179
// NewTXTValuesInTransaction creates two rows for subdomain to the txt table
180
func (d *acmednsdb) NewTXTValuesInTransaction(tx *sql.Tx, subdomain string) error {
11✔
181
        var err error
11✔
182
        instr := fmt.Sprintf("INSERT INTO txt (Subdomain, LastUpdate) values('%s', 0)", subdomain)
11✔
183
        _, _ = tx.Exec(instr)
11✔
184
        _, _ = tx.Exec(instr)
11✔
185
        return err
11✔
186
}
11✔
187

188
func (d *acmednsdb) Register(afrom acmedns.Cidrslice) (acmedns.ACMETxt, error) {
12✔
189
        d.Mutex.Lock()
12✔
190
        defer d.Mutex.Unlock()
12✔
191
        var err error
12✔
192
        tx, err := d.DB.Begin()
12✔
193
        // Rollback if errored, commit if not
12✔
194
        defer func() {
24✔
195
                if err != nil {
13✔
196
                        _ = tx.Rollback()
1✔
197
                        return
1✔
198
                }
1✔
199
                _ = tx.Commit()
11✔
200
        }()
201
        a := acmedns.NewACMETxt()
12✔
202
        a.AllowFrom = acmedns.Cidrslice(afrom.ValidEntries())
12✔
203
        passwordHash, err := bcrypt.GenerateFromPassword([]byte(a.Password), 10)
12✔
204
        regSQL := `
12✔
205
    INSERT INTO records(
12✔
206
        Username,
12✔
207
        Password,
12✔
208
        Subdomain,
12✔
209
                AllowFrom) 
12✔
210
        values($1, $2, $3, $4)`
12✔
211
        if d.Config.Database.Engine == "sqlite" {
24✔
212
                regSQL = getSQLiteStmt(regSQL)
12✔
213
        }
12✔
214
        sm, err := tx.Prepare(regSQL)
12✔
215
        if err != nil {
12✔
NEW
216
                d.Logger.Errorw("Database error in prepare",
×
NEW
217
                        "error", err.Error())
×
NEW
218
                return a, fmt.Errorf("failed to prepare registration statement: %w", err)
×
219
        }
×
220
        defer sm.Close()
12✔
221
        _, err = sm.Exec(a.Username.String(), passwordHash, a.Subdomain, a.AllowFrom.JSON())
12✔
222
        if err == nil {
23✔
223
                err = d.NewTXTValuesInTransaction(tx, a.Subdomain)
11✔
224
        }
11✔
225
        return a, err
12✔
226
}
227

228
func (d *acmednsdb) GetByUsername(u uuid.UUID) (acmedns.ACMETxt, error) {
9✔
229
        d.Mutex.Lock()
9✔
230
        defer d.Mutex.Unlock()
9✔
231
        var results []acmedns.ACMETxt
9✔
232
        getSQL := `
9✔
233
        SELECT Username, Password, Subdomain, AllowFrom
9✔
234
        FROM records
9✔
235
        WHERE Username=$1 LIMIT 1
9✔
236
        `
9✔
237
        if d.Config.Database.Engine == "sqlite" {
18✔
238
                getSQL = getSQLiteStmt(getSQL)
9✔
239
        }
9✔
240

241
        sm, err := d.DB.Prepare(getSQL)
9✔
242
        if err != nil {
10✔
243
                return acmedns.ACMETxt{}, err
1✔
244
        }
1✔
245
        defer sm.Close()
8✔
246
        rows, err := sm.Query(u.String())
8✔
247
        if err != nil {
9✔
248
                return acmedns.ACMETxt{}, fmt.Errorf("failed to query user: %w", err)
1✔
249
        }
1✔
250
        defer rows.Close()
7✔
251

7✔
252
        // It will only be one row though
7✔
253
        for rows.Next() {
14✔
254
                txt, err := d.getModelFromRow(rows)
7✔
255
                if err != nil {
9✔
256
                        return acmedns.ACMETxt{}, err
2✔
257
                }
2✔
258
                results = append(results, txt)
5✔
259
        }
260
        if len(results) > 0 {
10✔
261
                return results[0], nil
5✔
262
        }
5✔
NEW
263
        return acmedns.ACMETxt{}, fmt.Errorf("user not found: %s", u.String())
×
264
}
265

266
func (d *acmednsdb) GetTXTForDomain(domain string) ([]string, error) {
5✔
267
        d.Mutex.Lock()
5✔
268
        defer d.Mutex.Unlock()
5✔
269
        domain = acmedns.SanitizeString(domain)
5✔
270
        var txts []string
5✔
271
        getSQL := `
5✔
272
        SELECT Value FROM txt WHERE Subdomain=$1 LIMIT 2
5✔
273
        `
5✔
274
        if d.Config.Database.Engine == "sqlite" {
10✔
275
                getSQL = getSQLiteStmt(getSQL)
5✔
276
        }
5✔
277

278
        sm, err := d.DB.Prepare(getSQL)
5✔
279
        if err != nil {
6✔
280
                return txts, err
1✔
281
        }
1✔
282
        defer sm.Close()
4✔
283
        rows, err := sm.Query(domain)
4✔
284
        if err != nil {
5✔
285
                return txts, err
1✔
286
        }
1✔
287
        defer rows.Close()
3✔
288

3✔
289
        for rows.Next() {
6✔
290
                var rtxt string
3✔
291
                err = rows.Scan(&rtxt)
3✔
292
                if err != nil {
4✔
293
                        return txts, err
1✔
294
                }
1✔
295
                txts = append(txts, rtxt)
2✔
296
        }
297
        return txts, nil
2✔
298
}
299

300
func (d *acmednsdb) Update(a acmedns.ACMETxtPost) error {
4✔
301
        d.Mutex.Lock()
4✔
302
        defer d.Mutex.Unlock()
4✔
303
        var err error
4✔
304
        // Data in a is already sanitized
4✔
305
        timenow := time.Now().Unix()
4✔
306

4✔
307
        updSQL := `
4✔
308
        UPDATE txt SET Value=$1, LastUpdate=$2
4✔
309
        WHERE rowid=(
4✔
310
                SELECT rowid FROM txt WHERE Subdomain=$3 ORDER BY LastUpdate LIMIT 1)
4✔
311
        `
4✔
312
        if d.Config.Database.Engine == "sqlite" {
8✔
313
                updSQL = getSQLiteStmt(updSQL)
4✔
314
        }
4✔
315

316
        sm, err := d.DB.Prepare(updSQL)
4✔
317
        if err != nil {
4✔
318
                return err
×
319
        }
×
320
        defer sm.Close()
4✔
321
        _, err = sm.Exec(a.Value, timenow, a.Subdomain)
4✔
322
        if err != nil {
5✔
323
                return err
1✔
324
        }
1✔
325
        return nil
3✔
326
}
327

328
func (d *acmednsdb) getModelFromRow(r *sql.Rows) (acmedns.ACMETxt, error) {
7✔
329
        txt := acmedns.ACMETxt{}
7✔
330
        afrom := ""
7✔
331
        err := r.Scan(
7✔
332
                &txt.Username,
7✔
333
                &txt.Password,
7✔
334
                &txt.Subdomain,
7✔
335
                &afrom)
7✔
336
        if err != nil {
9✔
337
                d.Logger.Errorw("Row scan error",
2✔
338
                        "error", err.Error())
2✔
339
        }
2✔
340

341
        cslice := acmedns.Cidrslice{}
7✔
342
        err = json.Unmarshal([]byte(afrom), &cslice)
7✔
343
        if err != nil {
9✔
344
                d.Logger.Errorw("JSON unmarshall error",
2✔
345
                        "error", err.Error())
2✔
346
        }
2✔
347
        txt.AllowFrom = cslice
7✔
348
        return txt, err
7✔
349
}
350

NEW
351
func (d *acmednsdb) Close() {
×
352
        d.DB.Close()
×
353
}
×
354

355
func (d *acmednsdb) GetBackend() *sql.DB {
4✔
356
        return d.DB
4✔
357
}
4✔
358

359
func (d *acmednsdb) SetBackend(backend *sql.DB) {
8✔
360
        d.DB = backend
8✔
361
}
8✔
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc