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
|
// GoToSocial
// Copyright (C) GoToSocial Authors admin@gotosocial.org
// SPDX-License-Identifier: AGPL-3.0-or-later
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program. If not, see <http://www.gnu.org/licenses/>.
//go:build !nosqlite
package bundb
import (
"context"
"database/sql"
"fmt"
"net/url"
"strings"
"time"
"code.superseriousbusiness.org/gotosocial/internal/config"
"code.superseriousbusiness.org/gotosocial/internal/db/sqlite"
"code.superseriousbusiness.org/gotosocial/internal/log"
"codeberg.org/gruf/go-bytesize"
"github.com/google/uuid"
"github.com/uptrace/bun/dialect/sqlitedialect"
"github.com/uptrace/bun/schema"
)
func init() {
// register our SQL driver implementations.
sql.Register("sqlite-gts", &sqlite.Driver{})
}
func sqliteConn(ctx context.Context) (*sql.DB, func() schema.Dialect, error) {
// validate db address has actually been set
address := config.GetDbAddress()
if address == "" {
return nil, nil, fmt.Errorf("'%s' was not set when attempting to start sqlite", config.DbAddressFlag)
}
// Build SQLite connection address with prefs.
address, inMem := buildSQLiteAddress(address)
// Open new DB instance
sqldb, err := sql.Open("sqlite-gts", address)
if err != nil {
return nil, nil, fmt.Errorf("could not open sqlite db with address %s: %w", address, err)
}
// Tune db connections for sqlite, see:
// - https://bun.uptrace.dev/guide/running-bun-in-production.html#database-sql
// - https://www.alexedwards.net/blog/configuring-sqldb
sqldb.SetMaxOpenConns(maxOpenConns()) // x number of conns per CPU
sqldb.SetMaxIdleConns(1) // only keep max 1 idle connection around
if inMem {
log.Warn(nil, "using sqlite in-memory mode; all data will be deleted when gts shuts down; this mode should only be used for debugging or running tests")
// Don't close aged connections as this may wipe the DB.
sqldb.SetConnMaxLifetime(0)
} else {
sqldb.SetConnMaxLifetime(5 * time.Minute)
}
// ping to check the db is there and listening
if err := sqldb.PingContext(ctx); err != nil {
return nil, nil, fmt.Errorf("sqlite ping: %w", err)
}
log.Infof(ctx, "connected to SQLITE database with address %s", address)
return sqldb, func() schema.Dialect { return sqlitedialect.New() }, nil
}
// buildSQLiteAddress will build an SQLite address string from given config input,
// appending user defined SQLite connection preferences (e.g. cache_size, journal_mode etc).
// The returned bool indicates whether this is an in-memory address or not.
func buildSQLiteAddress(addr string) (string, bool) {
// Notes on SQLite preferences:
//
// - SQLite by itself supports setting a subset of its configuration options
// via URI query arguments in the connection. Namely `mode` and `cache`.
// This is the same situation for our supported SQLite implementations.
//
// - Both implementations have a "shim" around them in the form of a
// `database/sql/driver.Driver{}` implementation.
//
// - The SQLite shims we interface with add support for setting ANY of the
// configuration options via query arguments, through using a special `_pragma`
// query key that specifies SQLite PRAGMAs to set upon opening each connection.
// As such you will see below that most config is set with the `_pragma` key.
//
// - As for why we're setting these PRAGMAs by connection string instead of
// directly executing the PRAGMAs ourselves? That's to ensure that all of
// configuration options are set across _all_ of our SQLite connections, given
// that we are a multi-threaded (not directly in a C way) application and that
// each connection is a separate SQLite instance opening the same database.
// And the `database/sql` package provides transparent connection pooling.
// Some data is shared between connections, for example the `journal_mode`
// as that is set in a bit of the file header, but to be sure with the other
// settings we just add them all to the connection URI string.
//
// - We specifically set the `busy_timeout` PRAGMA before the `journal_mode`.
// When Write-Ahead-Logging (WAL) is enabled, in order to handle the issues
// that may arise between separate concurrent read/write threads racing for
// the same database file (and write-ahead log), SQLite will sometimes return
// an `SQLITE_BUSY` error code, which indicates that the query was aborted
// due to a data race and must be retried. The `busy_timeout` PRAGMA configures
// a function handler that SQLite can use internally to handle these data races,
// in that it will attempt to retry the query until the `busy_timeout` time is
// reached. And for whatever reason (:shrug:) SQLite is very particular about
// setting this BEFORE the `journal_mode` is set, otherwise you can end up
// running into more of these `SQLITE_BUSY` return codes than you might expect.
// Drop anything fancy from DB address
addr = strings.Split(addr, "?")[0] // drop any provided query strings
addr = strings.TrimPrefix(addr, "file:") // we'll prepend this later ourselves
// build our own SQLite preferences
// as a series of URL encoded values
prefs := make(url.Values)
// use immediate transaction lock mode to fail quickly if tx can't lock
// see https://pkg.go.dev/modernc.org/sqlite#Driver.Open
prefs.Add("_txlock", "immediate")
inMem := false
if addr == ":memory:" {
// Use random name for in-memory instead of ':memory:', so
// multiple in-mem databases can be created without conflict.
inMem = true
addr = "/" + uuid.NewString()
prefs.Add("vfs", "memdb")
}
if dur := config.GetDbSqliteBusyTimeout(); dur > 0 {
// Set the user provided SQLite busy timeout
// NOTE: MUST BE SET BEFORE THE JOURNAL MODE.
prefs.Add("_pragma", fmt.Sprintf("busy_timeout(%d)", dur.Milliseconds()))
}
if mode := config.GetDbSqliteJournalMode(); mode != "" {
// Set the user provided SQLite journal mode.
prefs.Add("_pragma", fmt.Sprintf("journal_mode(%s)", mode))
}
if mode := config.GetDbSqliteSynchronous(); mode != "" {
// Set the user provided SQLite synchronous mode.
prefs.Add("_pragma", fmt.Sprintf("synchronous(%s)", mode))
}
if sz := config.GetDbSqliteCacheSize(); sz > 0 {
// Set the user provided SQLite cache size (in kibibytes)
// Prepend a '-' character to this to indicate to sqlite
// that we're giving kibibytes rather than num pages.
// https://www.sqlite.org/pragma.html#pragma_cache_size
prefs.Add("_pragma", fmt.Sprintf("cache_size(-%d)", uint64(sz/bytesize.KiB)))
}
var b strings.Builder
b.WriteString("file:")
b.WriteString(addr)
b.WriteString("?")
b.WriteString(prefs.Encode())
return b.String(), inMem
}
|
