1package sqlite3
2
3import (
4 "context"
5 "fmt"
6 "iter"
7 "math"
8 "math/rand"
9 "net/url"
10 "runtime"
11 "strings"
12 "time"
13
14 "github.com/tetratelabs/wazero/api"
15
16 "github.com/ncruces/go-sqlite3/internal/util"
17 "github.com/ncruces/go-sqlite3/vfs"
18)
19
20// Conn is a database connection handle.
21// A Conn is not safe for concurrent use by multiple goroutines.
22//
23// https://sqlite.org/c3ref/sqlite3.html
24type Conn struct {
25 *sqlite
26
27 interrupt context.Context
28 stmts []*Stmt
29 busy func(context.Context, int) bool
30 log func(xErrorCode, string)
31 collation func(*Conn, string)
32 wal func(*Conn, string, int) error
33 trace func(TraceEvent, any, any) error
34 authorizer func(AuthorizerActionCode, string, string, string, string) AuthorizerReturnCode
35 update func(AuthorizerActionCode, string, string, int64)
36 commit func() bool
37 rollback func()
38
39 busy1st time.Time
40 busylst time.Time
41 arena arena
42 handle ptr_t
43 gosched uint8
44}
45
46// Open calls [OpenFlags] with [OPEN_READWRITE], [OPEN_CREATE] and [OPEN_URI].
47func Open(filename string) (*Conn, error) {
48 return newConn(context.Background(), filename, OPEN_READWRITE|OPEN_CREATE|OPEN_URI)
49}
50
51// OpenContext is like [Open] but includes a context,
52// which is used to interrupt the process of opening the connection.
53func OpenContext(ctx context.Context, filename string) (*Conn, error) {
54 return newConn(ctx, filename, OPEN_READWRITE|OPEN_CREATE|OPEN_URI)
55}
56
57// OpenFlags opens an SQLite database file as specified by the filename argument.
58//
59// If none of the required flags are used, a combination of [OPEN_READWRITE] and [OPEN_CREATE] is used.
60// If a URI filename is used, PRAGMA statements to execute can be specified using "_pragma":
61//
62// sqlite3.Open("file:demo.db?_pragma=busy_timeout(10000)")
63//
64// https://sqlite.org/c3ref/open.html
65func OpenFlags(filename string, flags OpenFlag) (*Conn, error) {
66 if flags&(OPEN_READONLY|OPEN_READWRITE|OPEN_CREATE) == 0 {
67 flags |= OPEN_READWRITE | OPEN_CREATE
68 }
69 return newConn(context.Background(), filename, flags)
70}
71
72type connKey = util.ConnKey
73
74func newConn(ctx context.Context, filename string, flags OpenFlag) (ret *Conn, _ error) {
75 err := ctx.Err()
76 if err != nil {
77 return nil, err
78 }
79
80 c := &Conn{interrupt: ctx}
81 c.sqlite, err = instantiateSQLite()
82 if err != nil {
83 return nil, err
84 }
85 defer func() {
86 if ret == nil {
87 c.Close()
88 c.sqlite.close()
89 } else {
90 c.interrupt = context.Background()
91 }
92 }()
93
94 c.ctx = context.WithValue(c.ctx, connKey{}, c)
95 if logger := defaultLogger.Load(); logger != nil {
96 c.ConfigLog(*logger)
97 }
98 c.arena = c.newArena()
99 c.handle, err = c.openDB(filename, flags)
100 if err == nil {
101 err = initExtensions(c)
102 }
103 if err != nil {
104 return nil, err
105 }
106 return c, nil
107}
108
109func (c *Conn) openDB(filename string, flags OpenFlag) (ptr_t, error) {
110 defer c.arena.mark()()
111 connPtr := c.arena.new(ptrlen)
112 namePtr := c.arena.string(filename)
113
114 flags |= OPEN_EXRESCODE
115 rc := res_t(c.call("sqlite3_open_v2", stk_t(namePtr), stk_t(connPtr), stk_t(flags), 0))
116
117 handle := util.Read32[ptr_t](c.mod, connPtr)
118 if err := c.sqlite.error(rc, handle); err != nil {
119 c.closeDB(handle)
120 return 0, err
121 }
122
123 c.call("sqlite3_progress_handler_go", stk_t(handle), 1000)
124 if flags|OPEN_URI != 0 && strings.HasPrefix(filename, "file:") {
125 var pragmas strings.Builder
126 if _, after, ok := strings.Cut(filename, "?"); ok {
127 query, _ := url.ParseQuery(after)
128 for _, p := range query["_pragma"] {
129 pragmas.WriteString(`PRAGMA `)
130 pragmas.WriteString(p)
131 pragmas.WriteString(`;`)
132 }
133 }
134 if pragmas.Len() != 0 {
135 pragmaPtr := c.arena.string(pragmas.String())
136 rc := res_t(c.call("sqlite3_exec", stk_t(handle), stk_t(pragmaPtr), 0, 0, 0))
137 if err := c.sqlite.error(rc, handle, pragmas.String()); err != nil {
138 err = fmt.Errorf("sqlite3: invalid _pragma: %w", err)
139 c.closeDB(handle)
140 return 0, err
141 }
142 }
143 }
144 return handle, nil
145}
146
147func (c *Conn) closeDB(handle ptr_t) {
148 rc := res_t(c.call("sqlite3_close_v2", stk_t(handle)))
149 if err := c.sqlite.error(rc, handle); err != nil {
150 panic(err)
151 }
152}
153
154// Close closes the database connection.
155//
156// If the database connection is associated with unfinalized prepared statements,
157// open blob handles, and/or unfinished backup objects,
158// Close will leave the database connection open and return [BUSY].
159//
160// It is safe to close a nil, zero or closed Conn.
161//
162// https://sqlite.org/c3ref/close.html
163func (c *Conn) Close() error {
164 if c == nil || c.handle == 0 {
165 return nil
166 }
167
168 rc := res_t(c.call("sqlite3_close", stk_t(c.handle)))
169 if err := c.error(rc); err != nil {
170 return err
171 }
172
173 c.handle = 0
174 return c.close()
175}
176
177// Exec is a convenience function that allows an application to run
178// multiple statements of SQL without having to use a lot of code.
179//
180// https://sqlite.org/c3ref/exec.html
181func (c *Conn) Exec(sql string) error {
182 if c.interrupt.Err() != nil {
183 return INTERRUPT
184 }
185 return c.exec(sql)
186}
187
188func (c *Conn) exec(sql string) error {
189 defer c.arena.mark()()
190 textPtr := c.arena.string(sql)
191 rc := res_t(c.call("sqlite3_exec", stk_t(c.handle), stk_t(textPtr), 0, 0, 0))
192 return c.error(rc, sql)
193}
194
195// Prepare calls [Conn.PrepareFlags] with no flags.
196func (c *Conn) Prepare(sql string) (stmt *Stmt, tail string, err error) {
197 return c.PrepareFlags(sql, 0)
198}
199
200// PrepareFlags compiles the first SQL statement in sql;
201// tail is left pointing to what remains uncompiled.
202// If the input text contains no SQL (if the input is an empty string or a comment),
203// both stmt and err will be nil.
204//
205// https://sqlite.org/c3ref/prepare.html
206func (c *Conn) PrepareFlags(sql string, flags PrepareFlag) (stmt *Stmt, tail string, err error) {
207 if len(sql) > _MAX_SQL_LENGTH {
208 return nil, "", TOOBIG
209 }
210 if c.interrupt.Err() != nil {
211 return nil, "", INTERRUPT
212 }
213
214 defer c.arena.mark()()
215 stmtPtr := c.arena.new(ptrlen)
216 tailPtr := c.arena.new(ptrlen)
217 textPtr := c.arena.string(sql)
218
219 rc := res_t(c.call("sqlite3_prepare_v3", stk_t(c.handle),
220 stk_t(textPtr), stk_t(len(sql)+1), stk_t(flags),
221 stk_t(stmtPtr), stk_t(tailPtr)))
222
223 stmt = &Stmt{c: c, sql: sql}
224 stmt.handle = util.Read32[ptr_t](c.mod, stmtPtr)
225 if sql := sql[util.Read32[ptr_t](c.mod, tailPtr)-textPtr:]; sql != "" {
226 tail = sql
227 }
228
229 if err := c.error(rc, sql); err != nil {
230 return nil, "", err
231 }
232 if stmt.handle == 0 {
233 return nil, "", nil
234 }
235 c.stmts = append(c.stmts, stmt)
236 return stmt, tail, nil
237}
238
239// DBName returns the schema name for n-th database on the database connection.
240//
241// https://sqlite.org/c3ref/db_name.html
242func (c *Conn) DBName(n int) string {
243 ptr := ptr_t(c.call("sqlite3_db_name", stk_t(c.handle), stk_t(n)))
244 if ptr == 0 {
245 return ""
246 }
247 return util.ReadString(c.mod, ptr, _MAX_NAME)
248}
249
250// Filename returns the filename for a database.
251//
252// https://sqlite.org/c3ref/db_filename.html
253func (c *Conn) Filename(schema string) *vfs.Filename {
254 var ptr ptr_t
255 if schema != "" {
256 defer c.arena.mark()()
257 ptr = c.arena.string(schema)
258 }
259 ptr = ptr_t(c.call("sqlite3_db_filename", stk_t(c.handle), stk_t(ptr)))
260 return vfs.GetFilename(c.ctx, c.mod, ptr, vfs.OPEN_MAIN_DB)
261}
262
263// ReadOnly determines if a database is read-only.
264//
265// https://sqlite.org/c3ref/db_readonly.html
266func (c *Conn) ReadOnly(schema string) (ro bool, ok bool) {
267 var ptr ptr_t
268 if schema != "" {
269 defer c.arena.mark()()
270 ptr = c.arena.string(schema)
271 }
272 b := int32(c.call("sqlite3_db_readonly", stk_t(c.handle), stk_t(ptr)))
273 return b > 0, b < 0
274}
275
276// GetAutocommit tests the connection for auto-commit mode.
277//
278// https://sqlite.org/c3ref/get_autocommit.html
279func (c *Conn) GetAutocommit() bool {
280 b := int32(c.call("sqlite3_get_autocommit", stk_t(c.handle)))
281 return b != 0
282}
283
284// LastInsertRowID returns the rowid of the most recent successful INSERT
285// on the database connection.
286//
287// https://sqlite.org/c3ref/last_insert_rowid.html
288func (c *Conn) LastInsertRowID() int64 {
289 return int64(c.call("sqlite3_last_insert_rowid", stk_t(c.handle)))
290}
291
292// SetLastInsertRowID allows the application to set the value returned by
293// [Conn.LastInsertRowID].
294//
295// https://sqlite.org/c3ref/set_last_insert_rowid.html
296func (c *Conn) SetLastInsertRowID(id int64) {
297 c.call("sqlite3_set_last_insert_rowid", stk_t(c.handle), stk_t(id))
298}
299
300// Changes returns the number of rows modified, inserted or deleted
301// by the most recently completed INSERT, UPDATE or DELETE statement
302// on the database connection.
303//
304// https://sqlite.org/c3ref/changes.html
305func (c *Conn) Changes() int64 {
306 return int64(c.call("sqlite3_changes64", stk_t(c.handle)))
307}
308
309// TotalChanges returns the number of rows modified, inserted or deleted
310// by all INSERT, UPDATE or DELETE statements completed
311// since the database connection was opened.
312//
313// https://sqlite.org/c3ref/total_changes.html
314func (c *Conn) TotalChanges() int64 {
315 return int64(c.call("sqlite3_total_changes64", stk_t(c.handle)))
316}
317
318// ReleaseMemory frees memory used by a database connection.
319//
320// https://sqlite.org/c3ref/db_release_memory.html
321func (c *Conn) ReleaseMemory() error {
322 rc := res_t(c.call("sqlite3_db_release_memory", stk_t(c.handle)))
323 return c.error(rc)
324}
325
326// GetInterrupt gets the context set with [Conn.SetInterrupt].
327func (c *Conn) GetInterrupt() context.Context {
328 return c.interrupt
329}
330
331// SetInterrupt interrupts a long-running query when a context is done.
332//
333// Subsequent uses of the connection will return [INTERRUPT]
334// until the context is reset by another call to SetInterrupt.
335//
336// To associate a timeout with a connection:
337//
338// ctx, cancel := context.WithTimeout(context.TODO(), 100*time.Millisecond)
339// conn.SetInterrupt(ctx)
340// defer cancel()
341//
342// SetInterrupt returns the old context assigned to the connection.
343//
344// https://sqlite.org/c3ref/interrupt.html
345func (c *Conn) SetInterrupt(ctx context.Context) (old context.Context) {
346 if ctx == nil {
347 panic("nil Context")
348 }
349 old = c.interrupt
350 c.interrupt = ctx
351 return old
352}
353
354func progressCallback(ctx context.Context, mod api.Module, _ ptr_t) (interrupt int32) {
355 if c, ok := ctx.Value(connKey{}).(*Conn); ok {
356 if c.gosched++; c.gosched%16 == 0 {
357 runtime.Gosched()
358 }
359 if c.interrupt.Err() != nil {
360 interrupt = 1
361 }
362 }
363 return interrupt
364}
365
366// BusyTimeout sets a busy timeout.
367//
368// https://sqlite.org/c3ref/busy_timeout.html
369func (c *Conn) BusyTimeout(timeout time.Duration) error {
370 ms := min((timeout+time.Millisecond-1)/time.Millisecond, math.MaxInt32)
371 rc := res_t(c.call("sqlite3_busy_timeout", stk_t(c.handle), stk_t(ms)))
372 return c.error(rc)
373}
374
375func timeoutCallback(ctx context.Context, mod api.Module, count, tmout int32) (retry int32) {
376 // https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/
377 if c, ok := ctx.Value(connKey{}).(*Conn); ok && c.interrupt.Err() == nil {
378 switch {
379 case count == 0:
380 c.busy1st = time.Now()
381 case time.Since(c.busy1st) >= time.Duration(tmout)*time.Millisecond:
382 return 0
383 }
384 if time.Since(c.busylst) < time.Millisecond {
385 const sleepIncrement = 2*1024*1024 - 1 // power of two, ~2ms
386 time.Sleep(time.Duration(rand.Int63() & sleepIncrement))
387 }
388 c.busylst = time.Now()
389 return 1
390 }
391 return 0
392}
393
394// BusyHandler registers a callback to handle [BUSY] errors.
395//
396// https://sqlite.org/c3ref/busy_handler.html
397func (c *Conn) BusyHandler(cb func(ctx context.Context, count int) (retry bool)) error {
398 var enable int32
399 if cb != nil {
400 enable = 1
401 }
402 rc := res_t(c.call("sqlite3_busy_handler_go", stk_t(c.handle), stk_t(enable)))
403 if err := c.error(rc); err != nil {
404 return err
405 }
406 c.busy = cb
407 return nil
408}
409
410func busyCallback(ctx context.Context, mod api.Module, pDB ptr_t, count int32) (retry int32) {
411 if c, ok := ctx.Value(connKey{}).(*Conn); ok && c.handle == pDB && c.busy != nil {
412 if interrupt := c.interrupt; interrupt.Err() == nil &&
413 c.busy(interrupt, int(count)) {
414 retry = 1
415 }
416 }
417 return retry
418}
419
420// Status retrieves runtime status information about a database connection.
421//
422// https://sqlite.org/c3ref/db_status.html
423func (c *Conn) Status(op DBStatus, reset bool) (current, highwater int, err error) {
424 defer c.arena.mark()()
425 hiPtr := c.arena.new(intlen)
426 curPtr := c.arena.new(intlen)
427
428 var i int32
429 if reset {
430 i = 1
431 }
432
433 rc := res_t(c.call("sqlite3_db_status", stk_t(c.handle),
434 stk_t(op), stk_t(curPtr), stk_t(hiPtr), stk_t(i)))
435 if err = c.error(rc); err == nil {
436 current = int(util.Read32[int32](c.mod, curPtr))
437 highwater = int(util.Read32[int32](c.mod, hiPtr))
438 }
439 return
440}
441
442// TableColumnMetadata extracts metadata about a column of a table.
443//
444// https://sqlite.org/c3ref/table_column_metadata.html
445func (c *Conn) TableColumnMetadata(schema, table, column string) (declType, collSeq string, notNull, primaryKey, autoInc bool, err error) {
446 defer c.arena.mark()()
447
448 var schemaPtr, columnPtr ptr_t
449 declTypePtr := c.arena.new(ptrlen)
450 collSeqPtr := c.arena.new(ptrlen)
451 notNullPtr := c.arena.new(ptrlen)
452 autoIncPtr := c.arena.new(ptrlen)
453 primaryKeyPtr := c.arena.new(ptrlen)
454 if schema != "" {
455 schemaPtr = c.arena.string(schema)
456 }
457 tablePtr := c.arena.string(table)
458 if column != "" {
459 columnPtr = c.arena.string(column)
460 }
461
462 rc := res_t(c.call("sqlite3_table_column_metadata", stk_t(c.handle),
463 stk_t(schemaPtr), stk_t(tablePtr), stk_t(columnPtr),
464 stk_t(declTypePtr), stk_t(collSeqPtr),
465 stk_t(notNullPtr), stk_t(primaryKeyPtr), stk_t(autoIncPtr)))
466 if err = c.error(rc); err == nil && column != "" {
467 if ptr := util.Read32[ptr_t](c.mod, declTypePtr); ptr != 0 {
468 declType = util.ReadString(c.mod, ptr, _MAX_NAME)
469 }
470 if ptr := util.Read32[ptr_t](c.mod, collSeqPtr); ptr != 0 {
471 collSeq = util.ReadString(c.mod, ptr, _MAX_NAME)
472 }
473 notNull = util.ReadBool(c.mod, notNullPtr)
474 autoInc = util.ReadBool(c.mod, autoIncPtr)
475 primaryKey = util.ReadBool(c.mod, primaryKeyPtr)
476 }
477 return
478}
479
480func (c *Conn) error(rc res_t, sql ...string) error {
481 return c.sqlite.error(rc, c.handle, sql...)
482}
483
484// Stmts returns an iterator for the prepared statements
485// associated with the database connection.
486//
487// https://sqlite.org/c3ref/next_stmt.html
488func (c *Conn) Stmts() iter.Seq[*Stmt] {
489 return func(yield func(*Stmt) bool) {
490 for _, s := range c.stmts {
491 if !yield(s) {
492 break
493 }
494 }
495 }
496}