Repository navigation
Expand file tree
/
Copy pathsqlite.go
More file actions
459 lines (420 loc) · 18.3 KB
/
Copy pathsqlite.go
File metadata and controls
459 lines (420 loc) · 18.3 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
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
// Package sqlite provides a Starlark module for SQLite database operations.
package sqlite
import (
"database/sql"
"fmt"
"net/url"
"strconv"
"strings"
"time"
"github.com/1set/starlet"
"github.com/1set/starlet/dataconv/types"
"github.com/starpkg/base"
"github.com/starpkg/base/util"
"github.com/tursodatabase/libsql-client-go/libsql"
"go.starlark.net/starlark"
_ "modernc.org/sqlite"
)
// ============================================================================
// Module Constants and Configuration
// ============================================================================
// Module constants
const (
// ModuleName defines the expected name for this module when used in Starlark's load() function
ModuleName = "sqlite"
)
// Default configuration values
const (
defaultTimeout = 30.0 // Default per-operation deadline in seconds (0 = no deadline).
defaultBusyTimeout = 5.0 // Default busy timeout in seconds. SQLite will wait for this duration if the database is locked.
defaultDatabase = ":memory:" // Default database path. Use ":memory:" for an in-memory database, or provide a file path.
defaultForeignKeys = true // Default for foreign key constraints. Set to true to enable, false to disable. Enabling enforces referential integrity.
defaultJournalMode = "DELETE" // Default journal mode. "DELETE" uses a rollback journal and removes journal files on commit.
// Other options: "WAL" (Write-Ahead Logging, good for concurrency), "MEMORY", "OFF", "TRUNCATE", "PERSIST".
// "DELETE" is often paired with SYNCHRONOUS=FULL for data safety.
defaultSynchronous = "FULL" // Default synchronous mode. "FULL" ensures all data is written to disk before continuing, providing maximum safety.
// Other options: "NORMAL" (safer with WAL, faster than FULL), "OFF" (fastest but less safe).
defaultCacheSize = -2000 // Default cache size. A negative value (e.g., -2000) instructs SQLite to use its
// default page cache size (typically 2000 pages, e.g., 8MB if page size is 4KB).
// A positive value sets the cache size in number of pages. 0 means no cache. The TEMP database has a default suggested cache size of 0 pages.
defaultMaxRows = 0 // Default maximum rows returned by query helpers. 0 means unlimited.
)
// Configuration key constants
const (
configKeyDatabase = "database"
configKeyTimeout = "timeout"
configKeyBusyTimeout = "busy_timeout"
configKeyForeignKeys = "foreign_keys"
configKeyJournalMode = "journal_mode"
configKeySynchronous = "synchronous"
configKeyCacheSize = "cache_size"
configKeyMaxRows = "max_rows"
)
// ============================================================================
// Module Structure and Creation
// ============================================================================
// Module wraps the ConfigurableModule with specific functionality for SQLite operations.
type Module struct {
cfgMod *base.ConfigurableModule
ext *base.ConfigurableModuleExt
restrictFileAccess bool
disableCustomFunctions bool
}
// HostPolicy contains opt-in restrictions copied into a module at construction.
// The zero value preserves NewModule's behavior; scripts cannot change these flags.
type HostPolicy struct {
// RestrictFileAccess applies the same local file gate as NewModuleWithFileAccess(false).
RestrictFileAccess bool
// DisableCustomFunctions prevents registration and use of process-wide custom
// functions on local connections. Built-in SQLite functions remain available.
// Remote server functions are controlled by the server, not by this flag.
DisableCustomFunctions bool
}
// NewModuleWithHostPolicy creates a module with fixed, host-owned restrictions.
// It does not add Starlark setters or environment options for the policy.
func NewModuleWithHostPolicy(policy HostPolicy) *Module {
// A host-only option snapshots its environment at module construction,
// before any script can change the process environment or its database.
databaseOpt := genConfigOption(configKeyDatabase, "Path to SQLite database (use :memory: for in-memory)", defaultDatabase)
if policy.RestrictFileAccess {
databaseOpt.SetHostOnly(true)
}
m := newModuleWithOptions(
databaseOpt,
genConfigOption(configKeyTimeout, "Per-operation deadline in seconds (0 = no deadline)", defaultTimeout),
genConfigOption(configKeyBusyTimeout, "Busy timeout in seconds", defaultBusyTimeout),
genConfigOption(configKeyForeignKeys, "Enable foreign key constraints", defaultForeignKeys),
genConfigOption(configKeyJournalMode, "Journal mode (WAL, DELETE, TRUNCATE, PERSIST, MEMORY, OFF)", defaultJournalMode),
genConfigOption(configKeySynchronous, "Synchronous mode (FULL, NORMAL, OFF)", defaultSynchronous),
genConfigOption(configKeyCacheSize, "Cache size in number of pages", defaultCacheSize),
genConfigOption(configKeyMaxRows, "Maximum rows returned by query helpers (0 means unlimited)", defaultMaxRows),
)
m.restrictFileAccess = policy.RestrictFileAccess
m.disableCustomFunctions = policy.DisableCustomFunctions
return m
}
// NewModule creates a new module with default configuration.
func NewModule() *Module {
return NewModuleWithHostPolicy(HostPolicy{})
}
// NewModuleWithFileAccess creates a new module and optionally restricts file database access.
func NewModuleWithFileAccess(allowed bool) *Module {
return NewModuleWithHostPolicy(HostPolicy{RestrictFileAccess: !allowed})
}
// genConfigOption creates a configuration option with common settings.
// It sets up the name, description, default value, and environment variable.
func genConfigOption[T any](name, description string, defaultValue T) *base.ConfigOption[T] {
return base.NewNamedConfigOption(ModuleName, name, description, defaultValue)
}
// newModuleWithOptions creates a Module with the given configuration options.
func newModuleWithOptions(
databaseOpt *base.ConfigOption[string],
timeoutOpt *base.ConfigOption[float64],
busyTimeoutOpt *base.ConfigOption[float64],
foreignKeysOpt *base.ConfigOption[bool],
journalModeOpt *base.ConfigOption[string],
synchronousOpt *base.ConfigOption[string],
cacheSizeOpt *base.ConfigOption[int],
maxRowsOpt *base.ConfigOption[int],
) *Module {
cm, _ := base.NewConfigurableModuleWithConfigOptions(
databaseOpt,
timeoutOpt,
busyTimeoutOpt,
foreignKeysOpt,
journalModeOpt,
synchronousOpt,
cacheSizeOpt,
maxRowsOpt,
)
return &Module{
cfgMod: cm,
ext: cm.Extend(),
}
}
// ============================================================================
// Module Loading and Starlark Integration
// ============================================================================
// LoadModule returns the Starlark module loader with SQLite-specific functions.
func (m *Module) LoadModule() starlet.ModuleLoader {
// Prepare methods dictionary
additionalFuncs := starlark.StringDict{
"connect": starlark.NewBuiltin(ModuleName+".connect", m.connect),
"connect_remote": starlark.NewBuiltin(ModuleName+".connect_remote", m.connectRemote),
"register_function": starlark.NewBuiltin(ModuleName+".register_function", m.registerFunction),
}
// Return the module
return m.cfgMod.LoadModule(ModuleName, additionalFuncs)
}
// ============================================================================
// Database Connection Functions
// ============================================================================
// connect implements the connect function that creates a new database connection.
func (m *Module) connect(thread *starlark.Thread, fn *starlark.Builtin, args starlark.Tuple, kwargs []starlark.Tuple) (starlark.Value, error) {
var database string
var timeout types.FloatOrInt
var busyTimeout types.FloatOrInt
var foreignKeys = types.NewNullableBool(false)
var journalMode string
var synchronous string
var cacheSize int
if err := starlark.UnpackArgs(fn.Name(), args, kwargs,
"database?", &database,
"timeout?", &timeout,
"busy_timeout?", &busyTimeout,
"foreign_keys?", foreignKeys,
"journal_mode?", &journalMode,
"synchronous?", &synchronous,
"cache_size?", &cacheSize,
); err != nil {
return nil, err
}
// Use default values from config if not provided
if database == "" {
database = m.ext.GetString(configKeyDatabase, defaultDatabase)
}
configuredDatabase := m.ext.GetString(configKeyDatabase, defaultDatabase)
if m.restrictFileAccess && !isInMemoryDSN(database) && database != configuredDatabase {
return nil, fmt.Errorf("file database access is restricted by the host; only in-memory or the host-configured database is allowed")
}
if timeout == 0 {
timeout = types.FloatOrInt(m.ext.GetFloat(configKeyTimeout, defaultTimeout))
}
if busyTimeout == 0 {
busyTimeout = types.FloatOrInt(m.ext.GetFloat(configKeyBusyTimeout, defaultBusyTimeout))
}
// Handle foreignKeys using NullableBool
var foreignKeysValue bool
if !foreignKeys.IsNull() {
foreignKeysValue = bool(foreignKeys.Value().Truth())
} else {
foreignKeysValue = m.ext.GetBool(configKeyForeignKeys, defaultForeignKeys)
}
if journalMode == "" {
journalMode = m.ext.GetString(configKeyJournalMode, defaultJournalMode)
}
if synchronous == "" {
synchronous = m.ext.GetString(configKeySynchronous, defaultSynchronous)
}
if cacheSize == 0 {
cacheSize = m.ext.GetInt(configKeyCacheSize, defaultCacheSize)
}
maxRows := m.ext.GetInt(configKeyMaxRows, defaultMaxRows)
// Create a new database connection
db, err := m.openDatabase(database, busyTimeout.GoFloat(), foreignKeysValue, journalMode, synchronous, cacheSize)
if err != nil {
return nil, fmt.Errorf("failed to open database: %w", err)
}
// Create and return the database object. `timeout` now bounds each operation
// (via a per-query context), replacing its former misuse as the connection's
// max lifetime.
return newDatabaseInstance(db, maxRows, m.restrictFileAccess, secondsToDuration(timeout.GoFloat())), nil
}
// secondsToDuration converts a fractional-seconds timeout into a time.Duration.
// A non-positive value means "no per-operation deadline".
func secondsToDuration(sec float64) time.Duration {
if sec <= 0 {
return 0
}
return time.Duration(sec * float64(time.Second))
}
// connectRemote opens a connection to a remote libSQL server — a self-hosted
// sqld or Turso Cloud — over the pure-Go libSQL client (no cgo). The returned
// object exposes the same query/exec/table API as a local connection, because
// the libSQL remote client is a standard database/sql driver.
//
// Cancellation caveat: queries (Ping/Query/Exec) over the HTTP(S)/Turso client
// honour the operation context, but the pinned libSQL driver on Go 1.20 does not
// thread a context into COMMIT/ROLLBACK, nor into a ws://wss:// connection dial
// (a fixed background dial). So a commit/rollback, or a reconnect over ws/wss,
// can still outlast the per-operation deadline / thread cancellation until the
// driver's own timeout. The documented HTTP(S)/Turso paths are the unaffected
// common case.
func (m *Module) connectRemote(thread *starlark.Thread, fn *starlark.Builtin, args starlark.Tuple, kwargs []starlark.Tuple) (starlark.Value, error) {
var dbURL, authToken string
if err := starlark.UnpackArgs(fn.Name(), args, kwargs,
"url", &dbURL,
"auth_token?", &authToken,
); err != nil {
return nil, err
}
if dbURL == "" {
return nil, fmt.Errorf("connect_remote: url is required")
}
parsed, err := url.Parse(dbURL)
if err != nil {
return nil, fmt.Errorf("failed to create remote connector: %w", err)
}
if parsed.Scheme == "file" {
// libSQL otherwise opens the raw default SQLite driver, bypassing both
// local host policy and register/open serialization.
return m.connect(thread, fn, starlark.Tuple{starlark.String(dbURL)}, nil)
}
var opts []libsql.Option
if authToken != "" {
opts = append(opts, libsql.WithAuthToken(authToken))
}
connector, err := libsql.NewConnector(dbURL, opts...)
if err != nil {
return nil, fmt.Errorf("failed to create remote connector: %w", err)
}
db := sql.OpenDB(connector)
opTimeout := secondsToDuration(m.ext.GetFloat(configKeyTimeout, defaultTimeout))
// A remote connect/query is a network round-trip: bound the Ping (and, below,
// every query) with a context so an unreachable or slow host cannot block the
// host goroutine indefinitely and so a cancelled script thread aborts it.
pingCtx, cancel := util.OpContext(thread, opTimeout)
defer cancel()
if err := db.PingContext(pingCtx); err != nil {
db.Close()
return nil, fmt.Errorf("failed to connect to remote database: %w", err)
}
maxRows := m.ext.GetInt(configKeyMaxRows, defaultMaxRows)
return newDatabaseInstance(db, maxRows, m.restrictFileAccess, opTimeout), nil
}
// isInMemoryDSN reports whether a SQLite DSN refers to an in-memory (or private
// temporary) database rather than a real file on disk. The check is deliberately
// strict: a loose substring match on ":memory:" / "mode=memory" would let a file
// DSN such as "file:/etc/passwd?x=:memory:" slip past the file-access restriction.
func isInMemoryDSN(s string) bool {
// Empty = private temporary database; ":memory:" = the in-memory database.
if s == "" || s == ":memory:" {
return true
}
// A DSN without the "file:" scheme is a literal filename to SQLite — even one
// containing "?mode=memory" or ":memory:" is a real on-disk file, so it must
// NOT be treated as in-memory (that was a file-access bypass).
if !strings.HasPrefix(s, "file:") {
return false
}
u, err := url.Parse(s)
if err != nil {
return false
}
// Only the PATH decides disk-vs-memory. We deliberately do NOT trust a
// mode=memory query parameter to reclassify a disk path: Go's url parsing and
// SQLite's DSN parsing disagree on malformed/edge queries (%00 truncation,
// duplicate keys), and any such disagreement on a disk path would be a
// file-access-gate bypass. An empty path is a private temporary database;
// a ":memory:" path (file::memory:[?…]) is the in-memory database.
path := u.Opaque
if path == "" {
path = u.Path
}
return path == "" || path == ":memory:"
}
// openDatabase creates a new local SQLite database connection. The connection
// PRAGMAs are set through the DSN (modernc's _pragma= parameter) so they apply
// to EVERY physical connection the pool opens; applying them once with db.Exec
// only configures a single borrowed connection, leaving every later connection
// on SQLite's defaults (notably foreign_keys OFF, so constraint-violating writes
// silently succeed). An in-memory or private-temporary database is additionally
// pinned to one never-recycled connection, because its schema and data live only
// inside that connection — a second (empty) connection or a recycled one would
// otherwise make earlier data vanish ("no such table").
func (m *Module) openDatabase(connStr string, busyTimeout float64, foreignKeys bool, journalMode, synchronous string, cacheSize int) (*sql.DB, error) {
if !pragmaIdentifier(journalMode) {
return nil, fmt.Errorf("invalid journal_mode %q", journalMode)
}
if !pragmaIdentifier(synchronous) {
return nil, fmt.Errorf("invalid synchronous %q", synchronous)
}
settings := pragmaSettings(busyTimeout, foreignKeys, journalMode, synchronous, cacheSize)
memory := isInMemoryDSN(connStr)
dsn := connStr
if !memory {
// A file database is served by a POOL of connections; carry the PRAGMAs in
// the DSN so every connection the pool opens is configured identically.
dsn = appendPragmas(connStr, settings)
}
// Open through the funcMutex-serialized driver so a lazy connection Open
// (which reads modernc's global UDF map) can't race a concurrent
// register_function call (see ensureLocalDriver).
ensureLocalDriver()
driverName := localDriverName
if m.disableCustomFunctions {
driverName = noFunctionsDriverName
}
db, err := sql.Open(driverName, dsn)
if err != nil {
return nil, err
}
if memory {
err = configureMemoryConnection(db, settings)
} else {
err = db.Ping()
}
if err != nil {
db.Close()
return nil, err
}
return db, nil
}
// configureMemoryConnection pins an in-memory / private-temporary database to a
// single never-recycled connection (so its schema and data don't vanish when a
// second connection opens or the first is retired) and applies the PRAGMAs
// directly to that one connection — which also verifies it opens, sidestepping
// the DSN quirk that an empty connStr would be misparsed.
func configureMemoryConnection(db *sql.DB, settings [][2]string) error {
db.SetMaxOpenConns(1)
db.SetConnMaxLifetime(0)
db.SetConnMaxIdleTime(0)
for _, stmt := range pragmaStatements(settings) {
if _, err := db.Exec(stmt); err != nil {
return fmt.Errorf("failed to apply %s: %w", stmt, err)
}
}
return nil
}
// pragmaIdentifier reports whether v is a bare alphanumeric PRAGMA keyword/value,
// so it is safe to place into a PRAGMA statement or the DSN _pragma= expression
// without risking injection (a value like "WAL); DROP …" is rejected).
func pragmaIdentifier(v string) bool {
if v == "" {
return false
}
for _, r := range v {
if !isASCIIAlphanumeric(r) {
return false
}
}
return true
}
// isASCIIAlphanumeric reports whether r is an ASCII letter or digit.
func isASCIIAlphanumeric(r rune) bool {
return r >= 'a' && r <= 'z' || r >= 'A' && r <= 'Z' || r >= '0' && r <= '9'
}
// pragmaSettings returns the ordered (name, value) connection PRAGMAs. journal_mode
// and synchronous must already be validated by pragmaIdentifier; the rest are
// integer-formatted.
func pragmaSettings(busyTimeout float64, foreignKeys bool, journalMode, synchronous string, cacheSize int) [][2]string {
return [][2]string{
{"busy_timeout", strconv.Itoa(int(busyTimeout * 1000))},
{"foreign_keys", strconv.Itoa(boolToInt(foreignKeys))},
{"journal_mode", journalMode},
{"synchronous", synchronous},
{"cache_size", strconv.Itoa(cacheSize)},
}
}
// appendPragmas builds the DSN carrying the PRAGMAs as modernc _pragma=
// parameters so every pooled (file) connection is configured identically.
func appendPragmas(connStr string, settings [][2]string) string {
parts := make([]string, len(settings))
for i, s := range settings {
parts[i] = "_pragma=" + s[0] + "(" + s[1] + ")"
}
sep := "?"
if strings.Contains(connStr, "?") {
sep = "&"
}
return connStr + sep + strings.Join(parts, "&")
}
// pragmaStatements returns the PRAGMA statements for the single-connection
// (in-memory) path.
func pragmaStatements(settings [][2]string) []string {
stmts := make([]string, len(settings))
for i, s := range settings {
stmts[i] = "PRAGMA " + s[0] + " = " + s[1]
}
return stmts
}