-
Notifications
You must be signed in to change notification settings - Fork 57
/
sqlite.go
174 lines (152 loc) · 4.61 KB
/
sqlite.go
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
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
package aperturedb
import (
"database/sql"
"fmt"
"net/url"
"path/filepath"
"testing"
"time"
sqlite_migrate "github.com/golang-migrate/migrate/v4/database/sqlite"
"github.com/lightninglabs/aperture/aperturedb/sqlc"
"github.com/stretchr/testify/require"
_ "modernc.org/sqlite" // Register relevant drivers.
)
const (
// sqliteOptionPrefix is the string prefix sqlite uses to set various
// options. This is used in the following format:
// * sqliteOptionPrefix || option_name = option_value.
sqliteOptionPrefix = "_pragma"
// sqliteTxLockImmediate is a dsn option used to ensure that write
// transactions are started immediately.
sqliteTxLockImmediate = "_txlock=immediate"
// defaultMaxConns is the number of permitted active and idle
// connections. We want to limit this so it isn't unlimited. We use the
// same value for the number of idle connections as, this can speed up
// queries given a new connection doesn't need to be established each
// time.
defaultMaxConns = 25
// connIdleLifetime is the amount of time a connection can be idle.
connIdleLifetime = 5 * time.Minute
)
// SqliteConfig holds all the config arguments needed to interact with our
// sqlite DB.
type SqliteConfig struct {
// SkipMigrations if true, then all the tables will be created on start
// up if they don't already exist.
SkipMigrations bool `long:"skipmigrations" description:"Skip applying migrations on startup."`
// DatabaseFileName is the full file path where the database file can be
// found.
DatabaseFileName string `long:"dbfile" description:"The full path to the database."`
}
// SqliteStore is a database store implementation that uses a sqlite backend.
type SqliteStore struct {
cfg *SqliteConfig
*BaseDB
}
// NewSqliteStore attempts to open a new sqlite database based on the passed
// config.
func NewSqliteStore(cfg *SqliteConfig) (*SqliteStore, error) {
// The set of pragma options are accepted using query options. For now
// we only want to ensure that foreign key constraints are properly
// enforced.
pragmaOptions := []struct {
name string
value string
}{
{
name: "foreign_keys",
value: "on",
},
{
name: "journal_mode",
value: "WAL",
},
{
name: "busy_timeout",
value: "5000",
},
{
// With the WAL mode, this ensures that we also do an
// extra WAL sync after each transaction. The normal
// sync mode skips this and gives better performance,
// but risks durability.
name: "synchronous",
value: "full",
},
{
// This is used to ensure proper durability for users
// running on Mac OS. It uses the correct fsync system
// call to ensure items are fully flushed to disk.
name: "fullfsync",
value: "true",
},
}
sqliteOptions := make(url.Values)
for _, option := range pragmaOptions {
sqliteOptions.Add(
sqliteOptionPrefix,
fmt.Sprintf("%v=%v", option.name, option.value),
)
}
// Construct the DSN which is just the database file name, appended
// with the series of pragma options as a query URL string. For more
// details on the formatting here, see the modernc.org/sqlite docs:
// https://pkg.go.dev/modernc.org/sqlite#Driver.Open.
dsn := fmt.Sprintf(
"%v?%v&%v", cfg.DatabaseFileName, sqliteOptions.Encode(),
sqliteTxLockImmediate,
)
db, err := sql.Open("sqlite", dsn)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(defaultMaxConns)
db.SetMaxIdleConns(defaultMaxConns)
db.SetConnMaxLifetime(connIdleLifetime)
if !cfg.SkipMigrations {
// Now that the database is open, populate the database with
// our set of schemas based on our embedded in-memory file
// system.
//
// First, we'll need to open up a new migration instance for
// our current target database: sqlite.
driver, err := sqlite_migrate.WithInstance(
db, &sqlite_migrate.Config{},
)
if err != nil {
return nil, err
}
err = applyMigrations(
sqlSchemas, driver, "sqlc/migrations", "sqlc",
)
if err != nil {
return nil, err
}
}
queries := sqlc.New(db)
return &SqliteStore{
cfg: cfg,
BaseDB: &BaseDB{
DB: db,
Queries: queries,
},
}, nil
}
// NewTestSqliteDB is a helper function that creates an SQLite database for
// testing.
func NewTestSqliteDB(t *testing.T) *SqliteStore {
t.Helper()
t.Logf("Creating new SQLite DB for testing")
// TODO(roasbeef): if we pass :memory: for the file name, then we get
// an in mem version to speed up tests
dbFileName := filepath.Join(t.TempDir(), "tmp.db")
sqlDB, err := NewSqliteStore(&SqliteConfig{
DatabaseFileName: dbFileName,
SkipMigrations: false,
})
require.NoError(t, err)
t.Cleanup(func() {
require.NoError(t, sqlDB.DB.Close())
})
return sqlDB
}