sqlite3.go raw
1 // Copyright (C) 2019 Yasuhiro Matsumoto <mattn.jp@gmail.com>.
2 // Copyright (C) 2018 G.J.R. Timmer <gjr.timmer@gmail.com>.
3 //
4 // Use of this source code is governed by an MIT-style
5 // license that can be found in the LICENSE file.
6
7 //go:build cgo
8 // +build cgo
9
10 package sqlite3
11
12 /*
13 #cgo CFLAGS: -std=gnu99
14 #cgo CFLAGS: -DSQLITE_ENABLE_RTREE
15 #cgo CFLAGS: -DSQLITE_THREADSAFE=1
16 #cgo CFLAGS: -DHAVE_USLEEP=1
17 #cgo CFLAGS: -DSQLITE_ENABLE_FTS3
18 #cgo CFLAGS: -DSQLITE_ENABLE_FTS3_PARENTHESIS
19 #cgo CFLAGS: -DSQLITE_TRACE_SIZE_LIMIT=15
20 #cgo CFLAGS: -DSQLITE_OMIT_DEPRECATED
21 #cgo CFLAGS: -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1
22 #cgo CFLAGS: -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT
23 #cgo CFLAGS: -Wno-deprecated-declarations
24 #cgo openbsd CFLAGS: -I/usr/local/include
25 #cgo openbsd LDFLAGS: -L/usr/local/lib
26 #ifndef USE_LIBSQLITE3
27 #include "sqlite3-binding.h"
28 #else
29 #include <sqlite3.h>
30 #endif
31 #include <stdlib.h>
32 #include <string.h>
33
34 #ifdef __CYGWIN__
35 # include <errno.h>
36 #endif
37
38 #ifndef SQLITE_OPEN_READWRITE
39 # define SQLITE_OPEN_READWRITE 0
40 #endif
41
42 #ifndef SQLITE_OPEN_FULLMUTEX
43 # define SQLITE_OPEN_FULLMUTEX 0
44 #endif
45
46 #ifndef SQLITE_DETERMINISTIC
47 # define SQLITE_DETERMINISTIC 0
48 #endif
49
50 #if defined(HAVE_PREAD64) && defined(HAVE_PWRITE64)
51 # undef USE_PREAD
52 # undef USE_PWRITE
53 # define USE_PREAD64 1
54 # define USE_PWRITE64 1
55 #elif defined(HAVE_PREAD) && defined(HAVE_PWRITE)
56 # undef USE_PREAD
57 # undef USE_PWRITE
58 # define USE_PREAD64 1
59 # define USE_PWRITE64 1
60 #endif
61
62 static int
63 _sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs) {
64 #ifdef SQLITE_OPEN_URI
65 return sqlite3_open_v2(filename, ppDb, flags | SQLITE_OPEN_URI, zVfs);
66 #else
67 return sqlite3_open_v2(filename, ppDb, flags, zVfs);
68 #endif
69 }
70
71 static int
72 _sqlite3_bind_text(sqlite3_stmt *stmt, int n, char *p, int np) {
73 return sqlite3_bind_text(stmt, n, p, np, SQLITE_TRANSIENT);
74 }
75
76 static int
77 _sqlite3_bind_blob(sqlite3_stmt *stmt, int n, void *p, int np) {
78 return sqlite3_bind_blob(stmt, n, p, np, SQLITE_TRANSIENT);
79 }
80
81 #include <stdio.h>
82 #include <stdint.h>
83
84 static int
85 _sqlite3_exec(sqlite3* db, const char* pcmd, long long* rowid, long long* changes)
86 {
87 int rv = sqlite3_exec(db, pcmd, 0, 0, 0);
88 *rowid = (long long) sqlite3_last_insert_rowid(db);
89 *changes = (long long) sqlite3_changes(db);
90 return rv;
91 }
92
93 #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
94 extern int _sqlite3_step_blocking(sqlite3_stmt *stmt);
95 extern int _sqlite3_step_row_blocking(sqlite3_stmt* stmt, long long* rowid, long long* changes);
96 extern int _sqlite3_prepare_v2_blocking(sqlite3 *db, const char *zSql, int nBytes, sqlite3_stmt **ppStmt, const char **pzTail);
97
98 static int
99 _sqlite3_step_internal(sqlite3_stmt *stmt)
100 {
101 return _sqlite3_step_blocking(stmt);
102 }
103
104 static int
105 _sqlite3_step_row_internal(sqlite3_stmt* stmt, long long* rowid, long long* changes)
106 {
107 return _sqlite3_step_row_blocking(stmt, rowid, changes);
108 }
109
110 static int
111 _sqlite3_prepare_v2_internal(sqlite3 *db, const char *zSql, int nBytes, sqlite3_stmt **ppStmt, const char **pzTail)
112 {
113 return _sqlite3_prepare_v2_blocking(db, zSql, nBytes, ppStmt, pzTail);
114 }
115
116 #else
117 static int
118 _sqlite3_step_internal(sqlite3_stmt *stmt)
119 {
120 return sqlite3_step(stmt);
121 }
122
123 static int
124 _sqlite3_step_row_internal(sqlite3_stmt* stmt, long long* rowid, long long* changes)
125 {
126 int rv = sqlite3_step(stmt);
127 sqlite3* db = sqlite3_db_handle(stmt);
128 *rowid = (long long) sqlite3_last_insert_rowid(db);
129 *changes = (long long) sqlite3_changes(db);
130 return rv;
131 }
132
133 static int
134 _sqlite3_prepare_v2_internal(sqlite3 *db, const char *zSql, int nBytes, sqlite3_stmt **ppStmt, const char **pzTail)
135 {
136 return sqlite3_prepare_v2(db, zSql, nBytes, ppStmt, pzTail);
137 }
138 #endif
139
140 void _sqlite3_result_text(sqlite3_context* ctx, const char* s) {
141 sqlite3_result_text(ctx, s, -1, &free);
142 }
143
144 void _sqlite3_result_blob(sqlite3_context* ctx, const void* b, int l) {
145 sqlite3_result_blob(ctx, b, l, SQLITE_TRANSIENT);
146 }
147
148
149 int _sqlite3_create_function(
150 sqlite3 *db,
151 const char *zFunctionName,
152 int nArg,
153 int eTextRep,
154 uintptr_t pApp,
155 void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
156 void (*xStep)(sqlite3_context*,int,sqlite3_value**),
157 void (*xFinal)(sqlite3_context*)
158 ) {
159 return sqlite3_create_function(db, zFunctionName, nArg, eTextRep, (void*) pApp, xFunc, xStep, xFinal);
160 }
161
162 void callbackTrampoline(sqlite3_context*, int, sqlite3_value**);
163 void stepTrampoline(sqlite3_context*, int, sqlite3_value**);
164 void doneTrampoline(sqlite3_context*);
165
166 int compareTrampoline(void*, int, char*, int, char*);
167 int commitHookTrampoline(void*);
168 void rollbackHookTrampoline(void*);
169 void updateHookTrampoline(void*, int, char*, char*, sqlite3_int64);
170
171 int authorizerTrampoline(void*, int, char*, char*, char*, char*);
172
173 #ifdef SQLITE_LIMIT_WORKER_THREADS
174 # define _SQLITE_HAS_LIMIT
175 # define SQLITE_LIMIT_LENGTH 0
176 # define SQLITE_LIMIT_SQL_LENGTH 1
177 # define SQLITE_LIMIT_COLUMN 2
178 # define SQLITE_LIMIT_EXPR_DEPTH 3
179 # define SQLITE_LIMIT_COMPOUND_SELECT 4
180 # define SQLITE_LIMIT_VDBE_OP 5
181 # define SQLITE_LIMIT_FUNCTION_ARG 6
182 # define SQLITE_LIMIT_ATTACHED 7
183 # define SQLITE_LIMIT_LIKE_PATTERN_LENGTH 8
184 # define SQLITE_LIMIT_VARIABLE_NUMBER 9
185 # define SQLITE_LIMIT_TRIGGER_DEPTH 10
186 # define SQLITE_LIMIT_WORKER_THREADS 11
187 # else
188 # define SQLITE_LIMIT_WORKER_THREADS 11
189 #endif
190
191 static int _sqlite3_limit(sqlite3* db, int limitId, int newLimit) {
192 #ifndef _SQLITE_HAS_LIMIT
193 return -1;
194 #else
195 return sqlite3_limit(db, limitId, newLimit);
196 #endif
197 }
198
199 #if SQLITE_VERSION_NUMBER < 3012000
200 static int sqlite3_system_errno(sqlite3 *db) {
201 return 0;
202 }
203 #endif
204 */
205 import "C"
206 import (
207 "context"
208 "database/sql"
209 "database/sql/driver"
210 "errors"
211 "fmt"
212 "io"
213 "net/url"
214 "reflect"
215 "runtime"
216 "strconv"
217 "strings"
218 "sync"
219 "syscall"
220 "time"
221 "unsafe"
222 )
223
224 // SQLiteTimestampFormats is timestamp formats understood by both this module
225 // and SQLite. The first format in the slice will be used when saving time
226 // values into the database. When parsing a string from a timestamp or datetime
227 // column, the formats are tried in order.
228 var SQLiteTimestampFormats = []string{
229 // By default, store timestamps with whatever timezone they come with.
230 // When parsed, they will be returned with the same timezone.
231 "2006-01-02 15:04:05.999999999-07:00",
232 "2006-01-02T15:04:05.999999999-07:00",
233 "2006-01-02 15:04:05.999999999",
234 "2006-01-02T15:04:05.999999999",
235 "2006-01-02 15:04:05",
236 "2006-01-02T15:04:05",
237 "2006-01-02 15:04",
238 "2006-01-02T15:04",
239 "2006-01-02",
240 }
241
242 const (
243 columnDate string = "date"
244 columnDatetime string = "datetime"
245 columnTimestamp string = "timestamp"
246 )
247
248 // This variable can be replaced with -ldflags like below:
249 // go build -ldflags="-X 'github.com/mattn/go-sqlite3.driverName=my-sqlite3'"
250 var driverName = "sqlite3"
251
252 func init() {
253 if driverName != "" {
254 sql.Register(driverName, &SQLiteDriver{})
255 }
256 }
257
258 // Version returns SQLite library version information.
259 func Version() (libVersion string, libVersionNumber int, sourceID string) {
260 libVersion = C.GoString(C.sqlite3_libversion())
261 libVersionNumber = int(C.sqlite3_libversion_number())
262 sourceID = C.GoString(C.sqlite3_sourceid())
263 return libVersion, libVersionNumber, sourceID
264 }
265
266 const (
267 // used by authorizer and pre_update_hook
268 SQLITE_DELETE = C.SQLITE_DELETE
269 SQLITE_INSERT = C.SQLITE_INSERT
270 SQLITE_UPDATE = C.SQLITE_UPDATE
271
272 // used by authorzier - as return value
273 SQLITE_OK = C.SQLITE_OK
274 SQLITE_IGNORE = C.SQLITE_IGNORE
275 SQLITE_DENY = C.SQLITE_DENY
276
277 // different actions query tries to do - passed as argument to authorizer
278 SQLITE_CREATE_INDEX = C.SQLITE_CREATE_INDEX
279 SQLITE_CREATE_TABLE = C.SQLITE_CREATE_TABLE
280 SQLITE_CREATE_TEMP_INDEX = C.SQLITE_CREATE_TEMP_INDEX
281 SQLITE_CREATE_TEMP_TABLE = C.SQLITE_CREATE_TEMP_TABLE
282 SQLITE_CREATE_TEMP_TRIGGER = C.SQLITE_CREATE_TEMP_TRIGGER
283 SQLITE_CREATE_TEMP_VIEW = C.SQLITE_CREATE_TEMP_VIEW
284 SQLITE_CREATE_TRIGGER = C.SQLITE_CREATE_TRIGGER
285 SQLITE_CREATE_VIEW = C.SQLITE_CREATE_VIEW
286 SQLITE_CREATE_VTABLE = C.SQLITE_CREATE_VTABLE
287 SQLITE_DROP_INDEX = C.SQLITE_DROP_INDEX
288 SQLITE_DROP_TABLE = C.SQLITE_DROP_TABLE
289 SQLITE_DROP_TEMP_INDEX = C.SQLITE_DROP_TEMP_INDEX
290 SQLITE_DROP_TEMP_TABLE = C.SQLITE_DROP_TEMP_TABLE
291 SQLITE_DROP_TEMP_TRIGGER = C.SQLITE_DROP_TEMP_TRIGGER
292 SQLITE_DROP_TEMP_VIEW = C.SQLITE_DROP_TEMP_VIEW
293 SQLITE_DROP_TRIGGER = C.SQLITE_DROP_TRIGGER
294 SQLITE_DROP_VIEW = C.SQLITE_DROP_VIEW
295 SQLITE_DROP_VTABLE = C.SQLITE_DROP_VTABLE
296 SQLITE_PRAGMA = C.SQLITE_PRAGMA
297 SQLITE_READ = C.SQLITE_READ
298 SQLITE_SELECT = C.SQLITE_SELECT
299 SQLITE_TRANSACTION = C.SQLITE_TRANSACTION
300 SQLITE_ATTACH = C.SQLITE_ATTACH
301 SQLITE_DETACH = C.SQLITE_DETACH
302 SQLITE_ALTER_TABLE = C.SQLITE_ALTER_TABLE
303 SQLITE_REINDEX = C.SQLITE_REINDEX
304 SQLITE_ANALYZE = C.SQLITE_ANALYZE
305 SQLITE_FUNCTION = C.SQLITE_FUNCTION
306 SQLITE_SAVEPOINT = C.SQLITE_SAVEPOINT
307 SQLITE_COPY = C.SQLITE_COPY
308 /*SQLITE_RECURSIVE = C.SQLITE_RECURSIVE*/
309 )
310
311 // Standard File Control Opcodes
312 // See: https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html
313 const (
314 SQLITE_FCNTL_LOCKSTATE = int(1)
315 SQLITE_FCNTL_GET_LOCKPROXYFILE = int(2)
316 SQLITE_FCNTL_SET_LOCKPROXYFILE = int(3)
317 SQLITE_FCNTL_LAST_ERRNO = int(4)
318 SQLITE_FCNTL_SIZE_HINT = int(5)
319 SQLITE_FCNTL_CHUNK_SIZE = int(6)
320 SQLITE_FCNTL_FILE_POINTER = int(7)
321 SQLITE_FCNTL_SYNC_OMITTED = int(8)
322 SQLITE_FCNTL_WIN32_AV_RETRY = int(9)
323 SQLITE_FCNTL_PERSIST_WAL = int(10)
324 SQLITE_FCNTL_OVERWRITE = int(11)
325 SQLITE_FCNTL_VFSNAME = int(12)
326 SQLITE_FCNTL_POWERSAFE_OVERWRITE = int(13)
327 SQLITE_FCNTL_PRAGMA = int(14)
328 SQLITE_FCNTL_BUSYHANDLER = int(15)
329 SQLITE_FCNTL_TEMPFILENAME = int(16)
330 SQLITE_FCNTL_MMAP_SIZE = int(18)
331 SQLITE_FCNTL_TRACE = int(19)
332 SQLITE_FCNTL_HAS_MOVED = int(20)
333 SQLITE_FCNTL_SYNC = int(21)
334 SQLITE_FCNTL_COMMIT_PHASETWO = int(22)
335 SQLITE_FCNTL_WIN32_SET_HANDLE = int(23)
336 SQLITE_FCNTL_WAL_BLOCK = int(24)
337 SQLITE_FCNTL_ZIPVFS = int(25)
338 SQLITE_FCNTL_RBU = int(26)
339 SQLITE_FCNTL_VFS_POINTER = int(27)
340 SQLITE_FCNTL_JOURNAL_POINTER = int(28)
341 SQLITE_FCNTL_WIN32_GET_HANDLE = int(29)
342 SQLITE_FCNTL_PDB = int(30)
343 SQLITE_FCNTL_BEGIN_ATOMIC_WRITE = int(31)
344 SQLITE_FCNTL_COMMIT_ATOMIC_WRITE = int(32)
345 SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE = int(33)
346 SQLITE_FCNTL_LOCK_TIMEOUT = int(34)
347 SQLITE_FCNTL_DATA_VERSION = int(35)
348 SQLITE_FCNTL_SIZE_LIMIT = int(36)
349 SQLITE_FCNTL_CKPT_DONE = int(37)
350 SQLITE_FCNTL_RESERVE_BYTES = int(38)
351 SQLITE_FCNTL_CKPT_START = int(39)
352 SQLITE_FCNTL_EXTERNAL_READER = int(40)
353 SQLITE_FCNTL_CKSM_FILE = int(41)
354 )
355
356 // SQLiteDriver implements driver.Driver.
357 type SQLiteDriver struct {
358 Extensions []string
359 ConnectHook func(*SQLiteConn) error
360 }
361
362 // SQLiteConn implements driver.Conn.
363 type SQLiteConn struct {
364 mu sync.Mutex
365 db *C.sqlite3
366 loc *time.Location
367 txlock string
368 funcs []*functionInfo
369 aggregators []*aggInfo
370 }
371
372 // SQLiteTx implements driver.Tx.
373 type SQLiteTx struct {
374 c *SQLiteConn
375 }
376
377 // SQLiteStmt implements driver.Stmt.
378 type SQLiteStmt struct {
379 mu sync.Mutex
380 c *SQLiteConn
381 s *C.sqlite3_stmt
382 t string
383 closed bool
384 cls bool // True if the statement was created by SQLiteConn.Query
385 }
386
387 // SQLiteResult implements sql.Result.
388 type SQLiteResult struct {
389 id int64
390 changes int64
391 }
392
393 // SQLiteRows implements driver.Rows.
394 type SQLiteRows struct {
395 s *SQLiteStmt
396 nc int32 // Number of columns
397 cls bool // True if we need to close the parent statement in Close
398 cols []string
399 decltype []string
400 ctx context.Context // no better alternative to pass context into Next() method
401 closemu sync.Mutex
402 }
403
404 type functionInfo struct {
405 f reflect.Value
406 argConverters []callbackArgConverter
407 variadicConverter callbackArgConverter
408 retConverter callbackRetConverter
409 }
410
411 func (fi *functionInfo) Call(ctx *C.sqlite3_context, argv []*C.sqlite3_value) {
412 args, err := callbackConvertArgs(argv, fi.argConverters, fi.variadicConverter)
413 if err != nil {
414 callbackError(ctx, err)
415 return
416 }
417
418 ret := fi.f.Call(args)
419
420 if len(ret) == 2 && ret[1].Interface() != nil {
421 callbackError(ctx, ret[1].Interface().(error))
422 return
423 }
424
425 err = fi.retConverter(ctx, ret[0])
426 if err != nil {
427 callbackError(ctx, err)
428 return
429 }
430 }
431
432 type aggInfo struct {
433 constructor reflect.Value
434
435 // Active aggregator objects for aggregations in flight. The
436 // aggregators are indexed by a counter stored in the aggregation
437 // user data space provided by sqlite.
438 active map[int64]reflect.Value
439 next int64
440
441 stepArgConverters []callbackArgConverter
442 stepVariadicConverter callbackArgConverter
443
444 doneRetConverter callbackRetConverter
445 }
446
447 func (ai *aggInfo) agg(ctx *C.sqlite3_context) (int64, reflect.Value, error) {
448 aggIdx := (*int64)(C.sqlite3_aggregate_context(ctx, C.int(8)))
449 if *aggIdx == 0 {
450 *aggIdx = ai.next
451 ret := ai.constructor.Call(nil)
452 if len(ret) == 2 && ret[1].Interface() != nil {
453 return 0, reflect.Value{}, ret[1].Interface().(error)
454 }
455 if ret[0].IsNil() {
456 return 0, reflect.Value{}, errors.New("aggregator constructor returned nil state")
457 }
458 ai.next++
459 ai.active[*aggIdx] = ret[0]
460 }
461 return *aggIdx, ai.active[*aggIdx], nil
462 }
463
464 func (ai *aggInfo) Step(ctx *C.sqlite3_context, argv []*C.sqlite3_value) {
465 _, agg, err := ai.agg(ctx)
466 if err != nil {
467 callbackError(ctx, err)
468 return
469 }
470
471 args, err := callbackConvertArgs(argv, ai.stepArgConverters, ai.stepVariadicConverter)
472 if err != nil {
473 callbackError(ctx, err)
474 return
475 }
476
477 ret := agg.MethodByName("Step").Call(args)
478 if len(ret) == 1 && ret[0].Interface() != nil {
479 callbackError(ctx, ret[0].Interface().(error))
480 return
481 }
482 }
483
484 func (ai *aggInfo) Done(ctx *C.sqlite3_context) {
485 idx, agg, err := ai.agg(ctx)
486 if err != nil {
487 callbackError(ctx, err)
488 return
489 }
490 defer func() { delete(ai.active, idx) }()
491
492 ret := agg.MethodByName("Done").Call(nil)
493 if len(ret) == 2 && ret[1].Interface() != nil {
494 callbackError(ctx, ret[1].Interface().(error))
495 return
496 }
497
498 err = ai.doneRetConverter(ctx, ret[0])
499 if err != nil {
500 callbackError(ctx, err)
501 return
502 }
503 }
504
505 // Commit transaction.
506 func (tx *SQLiteTx) Commit() error {
507 _, err := tx.c.exec(context.Background(), "COMMIT", nil)
508 if err != nil {
509 // sqlite3 may leave the transaction open in this scenario.
510 // However, database/sql considers the transaction complete once we
511 // return from Commit() - we must clean up to honour its semantics.
512 // We don't know if the ROLLBACK is strictly necessary, but according
513 // to sqlite's docs, there is no harm in calling ROLLBACK unnecessarily.
514 tx.c.exec(context.Background(), "ROLLBACK", nil)
515 }
516 return err
517 }
518
519 // Rollback transaction.
520 func (tx *SQLiteTx) Rollback() error {
521 _, err := tx.c.exec(context.Background(), "ROLLBACK", nil)
522 return err
523 }
524
525 // RegisterCollation makes a Go function available as a collation.
526 //
527 // cmp receives two UTF-8 strings, a and b. The result should be 0 if
528 // a==b, -1 if a < b, and +1 if a > b.
529 //
530 // cmp must always return the same result given the same
531 // inputs. Additionally, it must have the following properties for all
532 // strings A, B and C: if A==B then B==A; if A==B and B==C then A==C;
533 // if A<B then B>A; if A<B and B<C then A<C.
534 //
535 // If cmp does not obey these constraints, sqlite3's behavior is
536 // undefined when the collation is used.
537 func (c *SQLiteConn) RegisterCollation(name string, cmp func(string, string) int) error {
538 handle := newHandle(c, cmp)
539 cname := C.CString(name)
540 defer C.free(unsafe.Pointer(cname))
541 rv := C.sqlite3_create_collation(c.db, cname, C.SQLITE_UTF8, handle, (*[0]byte)(unsafe.Pointer(C.compareTrampoline)))
542 if rv != C.SQLITE_OK {
543 return c.lastError()
544 }
545 return nil
546 }
547
548 // RegisterCommitHook sets the commit hook for a connection.
549 //
550 // If the callback returns non-zero the transaction will become a rollback.
551 //
552 // If there is an existing commit hook for this connection, it will be
553 // removed. If callback is nil the existing hook (if any) will be removed
554 // without creating a new one.
555 func (c *SQLiteConn) RegisterCommitHook(callback func() int) {
556 if callback == nil {
557 C.sqlite3_commit_hook(c.db, nil, nil)
558 } else {
559 C.sqlite3_commit_hook(c.db, (*[0]byte)(C.commitHookTrampoline), newHandle(c, callback))
560 }
561 }
562
563 // RegisterRollbackHook sets the rollback hook for a connection.
564 //
565 // If there is an existing rollback hook for this connection, it will be
566 // removed. If callback is nil the existing hook (if any) will be removed
567 // without creating a new one.
568 func (c *SQLiteConn) RegisterRollbackHook(callback func()) {
569 if callback == nil {
570 C.sqlite3_rollback_hook(c.db, nil, nil)
571 } else {
572 C.sqlite3_rollback_hook(c.db, (*[0]byte)(C.rollbackHookTrampoline), newHandle(c, callback))
573 }
574 }
575
576 // RegisterUpdateHook sets the update hook for a connection.
577 //
578 // The parameters to the callback are the operation (one of the constants
579 // SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE), the database name, the
580 // table name, and the rowid.
581 //
582 // If there is an existing update hook for this connection, it will be
583 // removed. If callback is nil the existing hook (if any) will be removed
584 // without creating a new one.
585 func (c *SQLiteConn) RegisterUpdateHook(callback func(int, string, string, int64)) {
586 if callback == nil {
587 C.sqlite3_update_hook(c.db, nil, nil)
588 } else {
589 C.sqlite3_update_hook(c.db, (*[0]byte)(C.updateHookTrampoline), newHandle(c, callback))
590 }
591 }
592
593 // RegisterAuthorizer sets the authorizer for connection.
594 //
595 // The parameters to the callback are the operation (one of the constants
596 // SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE), and 1 to 3 arguments,
597 // depending on operation. More details see:
598 // https://www.sqlite.org/c3ref/c_alter_table.html
599 func (c *SQLiteConn) RegisterAuthorizer(callback func(int, string, string, string) int) {
600 if callback == nil {
601 C.sqlite3_set_authorizer(c.db, nil, nil)
602 } else {
603 C.sqlite3_set_authorizer(c.db, (*[0]byte)(C.authorizerTrampoline), newHandle(c, callback))
604 }
605 }
606
607 // RegisterFunc makes a Go function available as a SQLite function.
608 //
609 // The Go function can have arguments of the following types: any
610 // numeric type except complex, bool, []byte, string and any.
611 // any arguments are given the direct translation of the SQLite data type:
612 // int64 for INTEGER, float64 for FLOAT, []byte for BLOB, string for TEXT.
613 //
614 // The function can additionally be variadic, as long as the type of
615 // the variadic argument is one of the above.
616 //
617 // If pure is true. SQLite will assume that the function's return
618 // value depends only on its inputs, and make more aggressive
619 // optimizations in its queries.
620 //
621 // See _example/go_custom_funcs for a detailed example.
622 func (c *SQLiteConn) RegisterFunc(name string, impl any, pure bool) error {
623 var fi functionInfo
624 fi.f = reflect.ValueOf(impl)
625 t := fi.f.Type()
626 if t.Kind() != reflect.Func {
627 return errors.New("Non-function passed to RegisterFunc")
628 }
629 if t.NumOut() != 1 && t.NumOut() != 2 {
630 return errors.New("SQLite functions must return 1 or 2 values")
631 }
632 if t.NumOut() == 2 && !t.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) {
633 return errors.New("Second return value of SQLite function must be error")
634 }
635
636 numArgs := t.NumIn()
637 if t.IsVariadic() {
638 numArgs--
639 }
640
641 for i := 0; i < numArgs; i++ {
642 conv, err := callbackArg(t.In(i))
643 if err != nil {
644 return err
645 }
646 fi.argConverters = append(fi.argConverters, conv)
647 }
648
649 if t.IsVariadic() {
650 conv, err := callbackArg(t.In(numArgs).Elem())
651 if err != nil {
652 return err
653 }
654 fi.variadicConverter = conv
655 // Pass -1 to sqlite so that it allows any number of
656 // arguments. The call helper verifies that the minimum number
657 // of arguments is present for variadic functions.
658 numArgs = -1
659 }
660
661 conv, err := callbackRet(t.Out(0))
662 if err != nil {
663 return err
664 }
665 fi.retConverter = conv
666
667 // fi must outlast the database connection, or we'll have dangling pointers.
668 c.funcs = append(c.funcs, &fi)
669
670 cname := C.CString(name)
671 defer C.free(unsafe.Pointer(cname))
672 opts := C.SQLITE_UTF8
673 if pure {
674 opts |= C.SQLITE_DETERMINISTIC
675 }
676 rv := sqlite3CreateFunction(c.db, cname, C.int(numArgs), C.int(opts), newHandle(c, &fi), C.callbackTrampoline, nil, nil)
677 if rv != C.SQLITE_OK {
678 return c.lastError()
679 }
680 return nil
681 }
682
683 func sqlite3CreateFunction(db *C.sqlite3, zFunctionName *C.char, nArg C.int, eTextRep C.int, pApp unsafe.Pointer, xFunc unsafe.Pointer, xStep unsafe.Pointer, xFinal unsafe.Pointer) C.int {
684 return C._sqlite3_create_function(db, zFunctionName, nArg, eTextRep, C.uintptr_t(uintptr(pApp)), (*[0]byte)(xFunc), (*[0]byte)(xStep), (*[0]byte)(xFinal))
685 }
686
687 // RegisterAggregator makes a Go type available as a SQLite aggregation function.
688 //
689 // Because aggregation is incremental, it's implemented in Go with a
690 // type that has 2 methods: func Step(values) accumulates one row of
691 // data into the accumulator, and func Done() ret finalizes and
692 // returns the aggregate value. "values" and "ret" may be any type
693 // supported by RegisterFunc.
694 //
695 // RegisterAggregator takes as implementation a constructor function
696 // that constructs an instance of the aggregator type each time an
697 // aggregation begins. The constructor must return a pointer to a
698 // type, or an interface that implements Step() and Done().
699 //
700 // The constructor function and the Step/Done methods may optionally
701 // return an error in addition to their other return values.
702 //
703 // See _example/go_custom_funcs for a detailed example.
704 func (c *SQLiteConn) RegisterAggregator(name string, impl any, pure bool) error {
705 var ai aggInfo
706 ai.constructor = reflect.ValueOf(impl)
707 t := ai.constructor.Type()
708 if t.Kind() != reflect.Func {
709 return errors.New("non-function passed to RegisterAggregator")
710 }
711 if t.NumOut() != 1 && t.NumOut() != 2 {
712 return errors.New("SQLite aggregator constructors must return 1 or 2 values")
713 }
714 if t.NumOut() == 2 && !t.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) {
715 return errors.New("Second return value of SQLite function must be error")
716 }
717 if t.NumIn() != 0 {
718 return errors.New("SQLite aggregator constructors must not have arguments")
719 }
720
721 agg := t.Out(0)
722 switch agg.Kind() {
723 case reflect.Ptr, reflect.Interface:
724 default:
725 return errors.New("SQlite aggregator constructor must return a pointer object")
726 }
727 stepFn, found := agg.MethodByName("Step")
728 if !found {
729 return errors.New("SQlite aggregator doesn't have a Step() function")
730 }
731 step := stepFn.Type
732 if step.NumOut() != 0 && step.NumOut() != 1 {
733 return errors.New("SQlite aggregator Step() function must return 0 or 1 values")
734 }
735 if step.NumOut() == 1 && !step.Out(0).Implements(reflect.TypeOf((*error)(nil)).Elem()) {
736 return errors.New("type of SQlite aggregator Step() return value must be error")
737 }
738
739 stepNArgs := step.NumIn()
740 start := 0
741 if agg.Kind() == reflect.Ptr {
742 // Skip over the method receiver
743 stepNArgs--
744 start++
745 }
746 if step.IsVariadic() {
747 stepNArgs--
748 }
749 for i := start; i < start+stepNArgs; i++ {
750 conv, err := callbackArg(step.In(i))
751 if err != nil {
752 return err
753 }
754 ai.stepArgConverters = append(ai.stepArgConverters, conv)
755 }
756 if step.IsVariadic() {
757 conv, err := callbackArg(step.In(start + stepNArgs).Elem())
758 if err != nil {
759 return err
760 }
761 ai.stepVariadicConverter = conv
762 // Pass -1 to sqlite so that it allows any number of
763 // arguments. The call helper verifies that the minimum number
764 // of arguments is present for variadic functions.
765 stepNArgs = -1
766 }
767
768 doneFn, found := agg.MethodByName("Done")
769 if !found {
770 return errors.New("SQlite aggregator doesn't have a Done() function")
771 }
772 done := doneFn.Type
773 doneNArgs := done.NumIn()
774 if agg.Kind() == reflect.Ptr {
775 // Skip over the method receiver
776 doneNArgs--
777 }
778 if doneNArgs != 0 {
779 return errors.New("SQlite aggregator Done() function must have no arguments")
780 }
781 if done.NumOut() != 1 && done.NumOut() != 2 {
782 return errors.New("SQLite aggregator Done() function must return 1 or 2 values")
783 }
784 if done.NumOut() == 2 && !done.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) {
785 return errors.New("second return value of SQLite aggregator Done() function must be error")
786 }
787
788 conv, err := callbackRet(done.Out(0))
789 if err != nil {
790 return err
791 }
792 ai.doneRetConverter = conv
793 ai.active = make(map[int64]reflect.Value)
794 ai.next = 1
795
796 // ai must outlast the database connection, or we'll have dangling pointers.
797 c.aggregators = append(c.aggregators, &ai)
798
799 cname := C.CString(name)
800 defer C.free(unsafe.Pointer(cname))
801 opts := C.SQLITE_UTF8
802 if pure {
803 opts |= C.SQLITE_DETERMINISTIC
804 }
805 rv := sqlite3CreateFunction(c.db, cname, C.int(stepNArgs), C.int(opts), newHandle(c, &ai), nil, C.stepTrampoline, C.doneTrampoline)
806 if rv != C.SQLITE_OK {
807 return c.lastError()
808 }
809 return nil
810 }
811
812 // AutoCommit return which currently auto commit or not.
813 func (c *SQLiteConn) AutoCommit() bool {
814 c.mu.Lock()
815 defer c.mu.Unlock()
816 return int(C.sqlite3_get_autocommit(c.db)) != 0
817 }
818
819 func (c *SQLiteConn) lastError() error {
820 return lastError(c.db)
821 }
822
823 // Note: may be called with db == nil
824 func lastError(db *C.sqlite3) error {
825 rv := C.sqlite3_errcode(db) // returns SQLITE_NOMEM if db == nil
826 if rv == C.SQLITE_OK {
827 return nil
828 }
829 extrv := C.sqlite3_extended_errcode(db) // returns SQLITE_NOMEM if db == nil
830 errStr := C.GoString(C.sqlite3_errmsg(db)) // returns "out of memory" if db == nil
831
832 // https://www.sqlite.org/c3ref/system_errno.html
833 // sqlite3_system_errno is only meaningful if the error code was SQLITE_CANTOPEN,
834 // or it was SQLITE_IOERR and the extended code was not SQLITE_IOERR_NOMEM
835 var systemErrno syscall.Errno
836 if rv == C.SQLITE_CANTOPEN || (rv == C.SQLITE_IOERR && extrv != C.SQLITE_IOERR_NOMEM) {
837 systemErrno = syscall.Errno(C.sqlite3_system_errno(db))
838 }
839
840 return Error{
841 Code: ErrNo(rv),
842 ExtendedCode: ErrNoExtended(extrv),
843 SystemErrno: systemErrno,
844 err: errStr,
845 }
846 }
847
848 // Exec implements Execer.
849 func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error) {
850 list := make([]driver.NamedValue, len(args))
851 for i, v := range args {
852 list[i] = driver.NamedValue{
853 Ordinal: i + 1,
854 Value: v,
855 }
856 }
857 return c.exec(context.Background(), query, list)
858 }
859
860 func (c *SQLiteConn) exec(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error) {
861 start := 0
862 for {
863 s, err := c.prepare(ctx, query)
864 if err != nil {
865 return nil, err
866 }
867 var res driver.Result
868 if s.(*SQLiteStmt).s != nil {
869 stmtArgs := make([]driver.NamedValue, 0, len(args))
870 na := s.NumInput()
871 if len(args)-start < na {
872 s.Close()
873 return nil, fmt.Errorf("not enough args to execute query: want %d got %d", na, len(args))
874 }
875 // consume the number of arguments used in the current
876 // statement and append all named arguments not
877 // contained therein
878 if len(args[start:start+na]) > 0 {
879 stmtArgs = append(stmtArgs, args[start:start+na]...)
880 for i := range args {
881 if (i < start || i >= na) && args[i].Name != "" {
882 stmtArgs = append(stmtArgs, args[i])
883 }
884 }
885 for i := range stmtArgs {
886 stmtArgs[i].Ordinal = i + 1
887 }
888 }
889 res, err = s.(*SQLiteStmt).exec(ctx, stmtArgs)
890 if err != nil && err != driver.ErrSkip {
891 s.Close()
892 return nil, err
893 }
894 start += na
895 }
896 tail := s.(*SQLiteStmt).t
897 s.Close()
898 if tail == "" {
899 if res == nil {
900 // https://github.com/mattn/go-sqlite3/issues/963
901 res = &SQLiteResult{0, 0}
902 }
903 return res, nil
904 }
905 query = tail
906 }
907 }
908
909 // Query implements Queryer.
910 func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error) {
911 list := make([]driver.NamedValue, len(args))
912 for i, v := range args {
913 list[i] = driver.NamedValue{
914 Ordinal: i + 1,
915 Value: v,
916 }
917 }
918 return c.query(context.Background(), query, list)
919 }
920
921 func (c *SQLiteConn) query(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error) {
922 start := 0
923 for {
924 stmtArgs := make([]driver.NamedValue, 0, len(args))
925 s, err := c.prepare(ctx, query)
926 if err != nil {
927 return nil, err
928 }
929 s.(*SQLiteStmt).cls = true
930 na := s.NumInput()
931 if len(args)-start < na {
932 s.Close()
933 return nil, fmt.Errorf("not enough args to execute query: want %d got %d", na, len(args)-start)
934 }
935 // consume the number of arguments used in the current
936 // statement and append all named arguments not contained
937 // therein
938 stmtArgs = append(stmtArgs, args[start:start+na]...)
939 for i := range args {
940 if (i < start || i >= na) && args[i].Name != "" {
941 stmtArgs = append(stmtArgs, args[i])
942 }
943 }
944 for i := range stmtArgs {
945 stmtArgs[i].Ordinal = i + 1
946 }
947 rows, err := s.(*SQLiteStmt).query(ctx, stmtArgs)
948 if err != nil && err != driver.ErrSkip {
949 s.Close()
950 return rows, err
951 }
952 start += na
953 tail := s.(*SQLiteStmt).t
954 if tail == "" {
955 return rows, nil
956 }
957 rows.Close()
958 s.Close()
959 query = tail
960 }
961 }
962
963 // Begin transaction.
964 func (c *SQLiteConn) Begin() (driver.Tx, error) {
965 return c.begin(context.Background())
966 }
967
968 func (c *SQLiteConn) begin(ctx context.Context) (driver.Tx, error) {
969 if _, err := c.exec(ctx, c.txlock, nil); err != nil {
970 return nil, err
971 }
972 return &SQLiteTx{c}, nil
973 }
974
975 // Open database and return a new connection.
976 //
977 // A pragma can take either zero or one argument.
978 // The argument is may be either in parentheses or it may be separated from
979 // the pragma name by an equal sign. The two syntaxes yield identical results.
980 // In many pragmas, the argument is a boolean. The boolean can be one of:
981 //
982 // 1 yes true on
983 // 0 no false off
984 //
985 // You can specify a DSN string using a URI as the filename.
986 //
987 // test.db
988 // file:test.db?cache=shared&mode=memory
989 // :memory:
990 // file::memory:
991 //
992 // mode
993 // Access mode of the database.
994 // https://www.sqlite.org/c3ref/open.html
995 // Values:
996 // - ro
997 // - rw
998 // - rwc
999 // - memory
1000 //
1001 // cache
1002 // SQLite Shared-Cache Mode
1003 // https://www.sqlite.org/sharedcache.html
1004 // Values:
1005 // - shared
1006 // - private
1007 //
1008 // immutable=Boolean
1009 // The immutable parameter is a boolean query parameter that indicates
1010 // that the database file is stored on read-only media. When immutable is set,
1011 // SQLite assumes that the database file cannot be changed,
1012 // even by a process with higher privilege,
1013 // and so the database is opened read-only and all locking and change detection is disabled.
1014 // Caution: Setting the immutable property on a database file that
1015 // does in fact change can result in incorrect query results and/or SQLITE_CORRUPT errors.
1016 //
1017 // go-sqlite3 adds the following query parameters to those used by SQLite:
1018 //
1019 // _loc=XXX
1020 // Specify location of time format. It's possible to specify "auto".
1021 //
1022 // _mutex=XXX
1023 // Specify mutex mode. XXX can be "no", "full".
1024 //
1025 // _txlock=XXX
1026 // Specify locking behavior for transactions. XXX can be "immediate",
1027 // "deferred", "exclusive".
1028 //
1029 // _auto_vacuum=X | _vacuum=X
1030 // 0 | none - Auto Vacuum disabled
1031 // 1 | full - Auto Vacuum FULL
1032 // 2 | incremental - Auto Vacuum Incremental
1033 //
1034 // _busy_timeout=XXX"| _timeout=XXX
1035 // Specify value for sqlite3_busy_timeout.
1036 //
1037 // _case_sensitive_like=Boolean | _cslike=Boolean
1038 // https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
1039 // Default or disabled the LIKE operation is case-insensitive.
1040 // When enabling this options behaviour of LIKE will become case-sensitive.
1041 //
1042 // _defer_foreign_keys=Boolean | _defer_fk=Boolean
1043 // Defer Foreign Keys until outermost transaction is committed.
1044 //
1045 // _foreign_keys=Boolean | _fk=Boolean
1046 // Enable or disable enforcement of foreign keys.
1047 //
1048 // _ignore_check_constraints=Boolean
1049 // This pragma enables or disables the enforcement of CHECK constraints.
1050 // The default setting is off, meaning that CHECK constraints are enforced by default.
1051 //
1052 // _journal_mode=MODE | _journal=MODE
1053 // Set journal mode for the databases associated with the current connection.
1054 // https://www.sqlite.org/pragma.html#pragma_journal_mode
1055 //
1056 // _locking_mode=X | _locking=X
1057 // Sets the database connection locking-mode.
1058 // The locking-mode is either NORMAL or EXCLUSIVE.
1059 // https://www.sqlite.org/pragma.html#pragma_locking_mode
1060 //
1061 // _query_only=Boolean
1062 // The query_only pragma prevents all changes to database files when enabled.
1063 //
1064 // _recursive_triggers=Boolean | _rt=Boolean
1065 // Enable or disable recursive triggers.
1066 //
1067 // _secure_delete=Boolean|FAST
1068 // When secure_delete is on, SQLite overwrites deleted content with zeros.
1069 // https://www.sqlite.org/pragma.html#pragma_secure_delete
1070 //
1071 // _synchronous=X | _sync=X
1072 // Change the setting of the "synchronous" flag.
1073 // https://www.sqlite.org/pragma.html#pragma_synchronous
1074 //
1075 // _writable_schema=Boolean
1076 // When this pragma is on, the SQLITE_MASTER tables in which database
1077 // can be changed using ordinary UPDATE, INSERT, and DELETE statements.
1078 // Warning: misuse of this pragma can easily result in a corrupt database file.
1079 func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
1080 if C.sqlite3_threadsafe() == 0 {
1081 return nil, errors.New("sqlite library was not compiled for thread-safe operation")
1082 }
1083
1084 var pkey string
1085
1086 // Options
1087 var loc *time.Location
1088 authCreate := false
1089 authUser := ""
1090 authPass := ""
1091 authCrypt := ""
1092 authSalt := ""
1093 mutex := C.int(C.SQLITE_OPEN_FULLMUTEX)
1094 txlock := "BEGIN"
1095
1096 // PRAGMA's
1097 autoVacuum := -1
1098 busyTimeout := 5000
1099 caseSensitiveLike := -1
1100 deferForeignKeys := -1
1101 foreignKeys := -1
1102 ignoreCheckConstraints := -1
1103 var journalMode string
1104 lockingMode := "NORMAL"
1105 queryOnly := -1
1106 recursiveTriggers := -1
1107 secureDelete := "DEFAULT"
1108 synchronousMode := "NORMAL"
1109 writableSchema := -1
1110 vfsName := ""
1111 var cacheSize *int64
1112
1113 pos := strings.IndexRune(dsn, '?')
1114 if pos >= 1 {
1115 params, err := url.ParseQuery(dsn[pos+1:])
1116 if err != nil {
1117 return nil, err
1118 }
1119
1120 // Authentication
1121 if _, ok := params["_auth"]; ok {
1122 authCreate = true
1123 }
1124 if val := params.Get("_auth_user"); val != "" {
1125 authUser = val
1126 }
1127 if val := params.Get("_auth_pass"); val != "" {
1128 authPass = val
1129 }
1130 if val := params.Get("_auth_crypt"); val != "" {
1131 authCrypt = val
1132 }
1133 if val := params.Get("_auth_salt"); val != "" {
1134 authSalt = val
1135 }
1136
1137 // _loc
1138 if val := params.Get("_loc"); val != "" {
1139 switch strings.ToLower(val) {
1140 case "auto":
1141 loc = time.Local
1142 default:
1143 loc, err = time.LoadLocation(val)
1144 if err != nil {
1145 return nil, fmt.Errorf("Invalid _loc: %v: %v", val, err)
1146 }
1147 }
1148 }
1149
1150 // _mutex
1151 if val := params.Get("_mutex"); val != "" {
1152 switch strings.ToLower(val) {
1153 case "no":
1154 mutex = C.SQLITE_OPEN_NOMUTEX
1155 case "full":
1156 mutex = C.SQLITE_OPEN_FULLMUTEX
1157 default:
1158 return nil, fmt.Errorf("Invalid _mutex: %v", val)
1159 }
1160 }
1161
1162 // _txlock
1163 if val := params.Get("_txlock"); val != "" {
1164 switch strings.ToLower(val) {
1165 case "immediate":
1166 txlock = "BEGIN IMMEDIATE"
1167 case "exclusive":
1168 txlock = "BEGIN EXCLUSIVE"
1169 case "deferred":
1170 txlock = "BEGIN"
1171 default:
1172 return nil, fmt.Errorf("Invalid _txlock: %v", val)
1173 }
1174 }
1175
1176 // Auto Vacuum (_vacuum)
1177 //
1178 // https://www.sqlite.org/pragma.html#pragma_auto_vacuum
1179 //
1180 pkey = "" // Reset pkey
1181 if _, ok := params["_auto_vacuum"]; ok {
1182 pkey = "_auto_vacuum"
1183 }
1184 if _, ok := params["_vacuum"]; ok {
1185 pkey = "_vacuum"
1186 }
1187 if val := params.Get(pkey); val != "" {
1188 switch strings.ToLower(val) {
1189 case "0", "none":
1190 autoVacuum = 0
1191 case "1", "full":
1192 autoVacuum = 1
1193 case "2", "incremental":
1194 autoVacuum = 2
1195 default:
1196 return nil, fmt.Errorf("Invalid _auto_vacuum: %v, expecting value of '0 NONE 1 FULL 2 INCREMENTAL'", val)
1197 }
1198 }
1199
1200 // Busy Timeout (_busy_timeout)
1201 //
1202 // https://www.sqlite.org/pragma.html#pragma_busy_timeout
1203 //
1204 pkey = "" // Reset pkey
1205 if _, ok := params["_busy_timeout"]; ok {
1206 pkey = "_busy_timeout"
1207 }
1208 if _, ok := params["_timeout"]; ok {
1209 pkey = "_timeout"
1210 }
1211 if val := params.Get(pkey); val != "" {
1212 iv, err := strconv.ParseInt(val, 10, 64)
1213 if err != nil {
1214 return nil, fmt.Errorf("Invalid _busy_timeout: %v: %v", val, err)
1215 }
1216 busyTimeout = int(iv)
1217 }
1218
1219 // Case Sensitive Like (_cslike)
1220 //
1221 // https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
1222 //
1223 pkey = "" // Reset pkey
1224 if _, ok := params["_case_sensitive_like"]; ok {
1225 pkey = "_case_sensitive_like"
1226 }
1227 if _, ok := params["_cslike"]; ok {
1228 pkey = "_cslike"
1229 }
1230 if val := params.Get(pkey); val != "" {
1231 switch strings.ToLower(val) {
1232 case "0", "no", "false", "off":
1233 caseSensitiveLike = 0
1234 case "1", "yes", "true", "on":
1235 caseSensitiveLike = 1
1236 default:
1237 return nil, fmt.Errorf("Invalid _case_sensitive_like: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1238 }
1239 }
1240
1241 // Defer Foreign Keys (_defer_foreign_keys | _defer_fk)
1242 //
1243 // https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys
1244 //
1245 pkey = "" // Reset pkey
1246 if _, ok := params["_defer_foreign_keys"]; ok {
1247 pkey = "_defer_foreign_keys"
1248 }
1249 if _, ok := params["_defer_fk"]; ok {
1250 pkey = "_defer_fk"
1251 }
1252 if val := params.Get(pkey); val != "" {
1253 switch strings.ToLower(val) {
1254 case "0", "no", "false", "off":
1255 deferForeignKeys = 0
1256 case "1", "yes", "true", "on":
1257 deferForeignKeys = 1
1258 default:
1259 return nil, fmt.Errorf("Invalid _defer_foreign_keys: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1260 }
1261 }
1262
1263 // Foreign Keys (_foreign_keys | _fk)
1264 //
1265 // https://www.sqlite.org/pragma.html#pragma_foreign_keys
1266 //
1267 pkey = "" // Reset pkey
1268 if _, ok := params["_foreign_keys"]; ok {
1269 pkey = "_foreign_keys"
1270 }
1271 if _, ok := params["_fk"]; ok {
1272 pkey = "_fk"
1273 }
1274 if val := params.Get(pkey); val != "" {
1275 switch strings.ToLower(val) {
1276 case "0", "no", "false", "off":
1277 foreignKeys = 0
1278 case "1", "yes", "true", "on":
1279 foreignKeys = 1
1280 default:
1281 return nil, fmt.Errorf("Invalid _foreign_keys: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1282 }
1283 }
1284
1285 // Ignore CHECK Constrains (_ignore_check_constraints)
1286 //
1287 // https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints
1288 //
1289 if val := params.Get("_ignore_check_constraints"); val != "" {
1290 switch strings.ToLower(val) {
1291 case "0", "no", "false", "off":
1292 ignoreCheckConstraints = 0
1293 case "1", "yes", "true", "on":
1294 ignoreCheckConstraints = 1
1295 default:
1296 return nil, fmt.Errorf("Invalid _ignore_check_constraints: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1297 }
1298 }
1299
1300 // Journal Mode (_journal_mode | _journal)
1301 //
1302 // https://www.sqlite.org/pragma.html#pragma_journal_mode
1303 //
1304 pkey = "" // Reset pkey
1305 if _, ok := params["_journal_mode"]; ok {
1306 pkey = "_journal_mode"
1307 }
1308 if _, ok := params["_journal"]; ok {
1309 pkey = "_journal"
1310 }
1311 if val := params.Get(pkey); val != "" {
1312 switch strings.ToUpper(val) {
1313 case "DELETE", "TRUNCATE", "PERSIST", "MEMORY", "OFF":
1314 journalMode = strings.ToUpper(val)
1315 case "WAL":
1316 journalMode = strings.ToUpper(val)
1317
1318 // For WAL Mode set Synchronous Mode to 'NORMAL'
1319 // See https://www.sqlite.org/pragma.html#pragma_synchronous
1320 synchronousMode = "NORMAL"
1321 default:
1322 return nil, fmt.Errorf("Invalid _journal: %v, expecting value of 'DELETE TRUNCATE PERSIST MEMORY WAL OFF'", val)
1323 }
1324 }
1325
1326 // Locking Mode (_locking)
1327 //
1328 // https://www.sqlite.org/pragma.html#pragma_locking_mode
1329 //
1330 pkey = "" // Reset pkey
1331 if _, ok := params["_locking_mode"]; ok {
1332 pkey = "_locking_mode"
1333 }
1334 if _, ok := params["_locking"]; ok {
1335 pkey = "_locking"
1336 }
1337 if val := params.Get(pkey); val != "" {
1338 switch strings.ToUpper(val) {
1339 case "NORMAL", "EXCLUSIVE":
1340 lockingMode = strings.ToUpper(val)
1341 default:
1342 return nil, fmt.Errorf("Invalid _locking_mode: %v, expecting value of 'NORMAL EXCLUSIVE", val)
1343 }
1344 }
1345
1346 // Query Only (_query_only)
1347 //
1348 // https://www.sqlite.org/pragma.html#pragma_query_only
1349 //
1350 if val := params.Get("_query_only"); val != "" {
1351 switch strings.ToLower(val) {
1352 case "0", "no", "false", "off":
1353 queryOnly = 0
1354 case "1", "yes", "true", "on":
1355 queryOnly = 1
1356 default:
1357 return nil, fmt.Errorf("Invalid _query_only: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1358 }
1359 }
1360
1361 // Recursive Triggers (_recursive_triggers)
1362 //
1363 // https://www.sqlite.org/pragma.html#pragma_recursive_triggers
1364 //
1365 pkey = "" // Reset pkey
1366 if _, ok := params["_recursive_triggers"]; ok {
1367 pkey = "_recursive_triggers"
1368 }
1369 if _, ok := params["_rt"]; ok {
1370 pkey = "_rt"
1371 }
1372 if val := params.Get(pkey); val != "" {
1373 switch strings.ToLower(val) {
1374 case "0", "no", "false", "off":
1375 recursiveTriggers = 0
1376 case "1", "yes", "true", "on":
1377 recursiveTriggers = 1
1378 default:
1379 return nil, fmt.Errorf("Invalid _recursive_triggers: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1380 }
1381 }
1382
1383 // Secure Delete (_secure_delete)
1384 //
1385 // https://www.sqlite.org/pragma.html#pragma_secure_delete
1386 //
1387 if val := params.Get("_secure_delete"); val != "" {
1388 switch strings.ToLower(val) {
1389 case "0", "no", "false", "off":
1390 secureDelete = "OFF"
1391 case "1", "yes", "true", "on":
1392 secureDelete = "ON"
1393 case "fast":
1394 secureDelete = "FAST"
1395 default:
1396 return nil, fmt.Errorf("Invalid _secure_delete: %v, expecting boolean value of '0 1 false true no yes off on fast'", val)
1397 }
1398 }
1399
1400 // Synchronous Mode (_synchronous | _sync)
1401 //
1402 // https://www.sqlite.org/pragma.html#pragma_synchronous
1403 //
1404 pkey = "" // Reset pkey
1405 if _, ok := params["_synchronous"]; ok {
1406 pkey = "_synchronous"
1407 }
1408 if _, ok := params["_sync"]; ok {
1409 pkey = "_sync"
1410 }
1411 if val := params.Get(pkey); val != "" {
1412 switch strings.ToUpper(val) {
1413 case "0", "OFF", "1", "NORMAL", "2", "FULL", "3", "EXTRA":
1414 synchronousMode = strings.ToUpper(val)
1415 default:
1416 return nil, fmt.Errorf("Invalid _synchronous: %v, expecting value of '0 OFF 1 NORMAL 2 FULL 3 EXTRA'", val)
1417 }
1418 }
1419
1420 // Writable Schema (_writeable_schema)
1421 //
1422 // https://www.sqlite.org/pragma.html#pragma_writeable_schema
1423 //
1424 if val := params.Get("_writable_schema"); val != "" {
1425 switch strings.ToLower(val) {
1426 case "0", "no", "false", "off":
1427 writableSchema = 0
1428 case "1", "yes", "true", "on":
1429 writableSchema = 1
1430 default:
1431 return nil, fmt.Errorf("Invalid _writable_schema: %v, expecting boolean value of '0 1 false true no yes off on'", val)
1432 }
1433 }
1434
1435 // Cache size (_cache_size)
1436 //
1437 // https://sqlite.org/pragma.html#pragma_cache_size
1438 //
1439 if val := params.Get("_cache_size"); val != "" {
1440 iv, err := strconv.ParseInt(val, 10, 64)
1441 if err != nil {
1442 return nil, fmt.Errorf("Invalid _cache_size: %v: %v", val, err)
1443 }
1444 cacheSize = &iv
1445 }
1446
1447 if val := params.Get("vfs"); val != "" {
1448 vfsName = val
1449 }
1450
1451 if !strings.HasPrefix(dsn, "file:") {
1452 dsn = dsn[:pos]
1453 }
1454 }
1455
1456 var db *C.sqlite3
1457 name := C.CString(dsn)
1458 defer C.free(unsafe.Pointer(name))
1459 var vfs *C.char
1460 if vfsName != "" {
1461 vfs = C.CString(vfsName)
1462 defer C.free(unsafe.Pointer(vfs))
1463 }
1464 rv := C._sqlite3_open_v2(name, &db,
1465 mutex|C.SQLITE_OPEN_READWRITE|C.SQLITE_OPEN_CREATE,
1466 vfs)
1467 if rv != 0 {
1468 // Save off the error _before_ closing the database.
1469 // This is safe even if db is nil.
1470 err := lastError(db)
1471 if db != nil {
1472 C.sqlite3_close_v2(db)
1473 }
1474 return nil, err
1475 }
1476 if db == nil {
1477 return nil, errors.New("sqlite succeeded without returning a database")
1478 }
1479
1480 exec := func(s string) error {
1481 cs := C.CString(s)
1482 rv := C.sqlite3_exec(db, cs, nil, nil, nil)
1483 C.free(unsafe.Pointer(cs))
1484 if rv != C.SQLITE_OK {
1485 return lastError(db)
1486 }
1487 return nil
1488 }
1489
1490 // Busy timeout
1491 if err := exec(fmt.Sprintf("PRAGMA busy_timeout = %d;", busyTimeout)); err != nil {
1492 C.sqlite3_close_v2(db)
1493 return nil, err
1494 }
1495
1496 // USER AUTHENTICATION
1497 //
1498 // User Authentication is always performed even when
1499 // sqlite_userauth is not compiled in, because without user authentication
1500 // the authentication is a no-op.
1501 //
1502 // Workflow
1503 // - Authenticate
1504 // ON::SUCCESS => Continue
1505 // ON::SQLITE_AUTH => Return error and exit Open(...)
1506 //
1507 // - Activate User Authentication
1508 // Check if the user wants to activate User Authentication.
1509 // If so then first create a temporary AuthConn to the database
1510 // This is possible because we are already successfully authenticated.
1511 //
1512 // - Check if `sqlite_user`` table exists
1513 // YES => Add the provided user from DSN as Admin User and
1514 // activate user authentication.
1515 // NO => Continue
1516 //
1517
1518 // Create connection to SQLite
1519 conn := &SQLiteConn{db: db, loc: loc, txlock: txlock}
1520
1521 // Password Cipher has to be registered before authentication
1522 if len(authCrypt) > 0 {
1523 switch strings.ToUpper(authCrypt) {
1524 case "SHA1":
1525 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA1, true); err != nil {
1526 return nil, fmt.Errorf("CryptEncoderSHA1: %s", err)
1527 }
1528 case "SSHA1":
1529 if len(authSalt) == 0 {
1530 return nil, fmt.Errorf("_auth_crypt=ssha1, requires _auth_salt")
1531 }
1532 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA1(authSalt), true); err != nil {
1533 return nil, fmt.Errorf("CryptEncoderSSHA1: %s", err)
1534 }
1535 case "SHA256":
1536 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA256, true); err != nil {
1537 return nil, fmt.Errorf("CryptEncoderSHA256: %s", err)
1538 }
1539 case "SSHA256":
1540 if len(authSalt) == 0 {
1541 return nil, fmt.Errorf("_auth_crypt=ssha256, requires _auth_salt")
1542 }
1543 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA256(authSalt), true); err != nil {
1544 return nil, fmt.Errorf("CryptEncoderSSHA256: %s", err)
1545 }
1546 case "SHA384":
1547 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA384, true); err != nil {
1548 return nil, fmt.Errorf("CryptEncoderSHA384: %s", err)
1549 }
1550 case "SSHA384":
1551 if len(authSalt) == 0 {
1552 return nil, fmt.Errorf("_auth_crypt=ssha384, requires _auth_salt")
1553 }
1554 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA384(authSalt), true); err != nil {
1555 return nil, fmt.Errorf("CryptEncoderSSHA384: %s", err)
1556 }
1557 case "SHA512":
1558 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA512, true); err != nil {
1559 return nil, fmt.Errorf("CryptEncoderSHA512: %s", err)
1560 }
1561 case "SSHA512":
1562 if len(authSalt) == 0 {
1563 return nil, fmt.Errorf("_auth_crypt=ssha512, requires _auth_salt")
1564 }
1565 if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA512(authSalt), true); err != nil {
1566 return nil, fmt.Errorf("CryptEncoderSSHA512: %s", err)
1567 }
1568 }
1569 }
1570
1571 // Preform Authentication
1572 if err := conn.Authenticate(authUser, authPass); err != nil {
1573 return nil, err
1574 }
1575
1576 // Register: authenticate
1577 // Authenticate will perform an authentication of the provided username
1578 // and password against the database.
1579 //
1580 // If a database contains the SQLITE_USER table, then the
1581 // call to Authenticate must be invoked with an
1582 // appropriate username and password prior to enable read and write
1583 //access to the database.
1584 //
1585 // Return SQLITE_OK on success or SQLITE_ERROR if the username/password
1586 // combination is incorrect or unknown.
1587 //
1588 // If the SQLITE_USER table is not present in the database file, then
1589 // this interface is a harmless no-op returnning SQLITE_OK.
1590 if err := conn.RegisterFunc("authenticate", conn.authenticate, true); err != nil {
1591 return nil, err
1592 }
1593 //
1594 // Register: auth_user_add
1595 // auth_user_add can be used (by an admin user only)
1596 // to create a new user. When called on a no-authentication-required
1597 // database, this routine converts the database into an authentication-
1598 // required database, automatically makes the added user an
1599 // administrator, and logs in the current connection as that user.
1600 // The AuthUserAdd only works for the "main" database, not
1601 // for any ATTACH-ed databases. Any call to AuthUserAdd by a
1602 // non-admin user results in an error.
1603 if err := conn.RegisterFunc("auth_user_add", conn.authUserAdd, true); err != nil {
1604 return nil, err
1605 }
1606 //
1607 // Register: auth_user_change
1608 // auth_user_change can be used to change a users
1609 // login credentials or admin privilege. Any user can change their own
1610 // login credentials. Only an admin user can change another users login
1611 // credentials or admin privilege setting. No user may change their own
1612 // admin privilege setting.
1613 if err := conn.RegisterFunc("auth_user_change", conn.authUserChange, true); err != nil {
1614 return nil, err
1615 }
1616 //
1617 // Register: auth_user_delete
1618 // auth_user_delete can be used (by an admin user only)
1619 // to delete a user. The currently logged-in user cannot be deleted,
1620 // which guarantees that there is always an admin user and hence that
1621 // the database cannot be converted into a no-authentication-required
1622 // database.
1623 if err := conn.RegisterFunc("auth_user_delete", conn.authUserDelete, true); err != nil {
1624 return nil, err
1625 }
1626
1627 // Register: auth_enabled
1628 // auth_enabled can be used to check if user authentication is enabled
1629 if err := conn.RegisterFunc("auth_enabled", conn.authEnabled, true); err != nil {
1630 return nil, err
1631 }
1632
1633 // Auto Vacuum
1634 // Moved auto_vacuum command, the user preference for auto_vacuum needs to be implemented directly after
1635 // the authentication and before the sqlite_user table gets created if the user
1636 // decides to activate User Authentication because
1637 // auto_vacuum needs to be set before any tables are created
1638 // and activating user authentication creates the internal table `sqlite_user`.
1639 if autoVacuum > -1 {
1640 if err := exec(fmt.Sprintf("PRAGMA auto_vacuum = %d;", autoVacuum)); err != nil {
1641 C.sqlite3_close_v2(db)
1642 return nil, err
1643 }
1644 }
1645
1646 // Check if user wants to activate User Authentication
1647 if authCreate {
1648 // Before going any further, we need to check that the user
1649 // has provided an username and password within the DSN.
1650 // We are not allowed to continue.
1651 if len(authUser) == 0 {
1652 return nil, fmt.Errorf("Missing '_auth_user' while user authentication was requested with '_auth'")
1653 }
1654 if len(authPass) == 0 {
1655 return nil, fmt.Errorf("Missing '_auth_pass' while user authentication was requested with '_auth'")
1656 }
1657
1658 // Check if User Authentication is Enabled
1659 authExists := conn.AuthEnabled()
1660 if !authExists {
1661 if err := conn.AuthUserAdd(authUser, authPass, true); err != nil {
1662 return nil, err
1663 }
1664 }
1665 }
1666
1667 // Case Sensitive LIKE
1668 if caseSensitiveLike > -1 {
1669 if err := exec(fmt.Sprintf("PRAGMA case_sensitive_like = %d;", caseSensitiveLike)); err != nil {
1670 C.sqlite3_close_v2(db)
1671 return nil, err
1672 }
1673 }
1674
1675 // Defer Foreign Keys
1676 if deferForeignKeys > -1 {
1677 if err := exec(fmt.Sprintf("PRAGMA defer_foreign_keys = %d;", deferForeignKeys)); err != nil {
1678 C.sqlite3_close_v2(db)
1679 return nil, err
1680 }
1681 }
1682
1683 // Foreign Keys
1684 if foreignKeys > -1 {
1685 if err := exec(fmt.Sprintf("PRAGMA foreign_keys = %d;", foreignKeys)); err != nil {
1686 C.sqlite3_close_v2(db)
1687 return nil, err
1688 }
1689 }
1690
1691 // Ignore CHECK Constraints
1692 if ignoreCheckConstraints > -1 {
1693 if err := exec(fmt.Sprintf("PRAGMA ignore_check_constraints = %d;", ignoreCheckConstraints)); err != nil {
1694 C.sqlite3_close_v2(db)
1695 return nil, err
1696 }
1697 }
1698
1699 // Journal Mode
1700 if journalMode != "" {
1701 if err := exec(fmt.Sprintf("PRAGMA journal_mode = %s;", journalMode)); err != nil {
1702 C.sqlite3_close_v2(db)
1703 return nil, err
1704 }
1705 }
1706
1707 // Locking Mode
1708 // Because the default is NORMAL and this is not changed in this package
1709 // by using the compile time SQLITE_DEFAULT_LOCKING_MODE this PRAGMA can always be executed
1710 if err := exec(fmt.Sprintf("PRAGMA locking_mode = %s;", lockingMode)); err != nil {
1711 C.sqlite3_close_v2(db)
1712 return nil, err
1713 }
1714
1715 // Query Only
1716 if queryOnly > -1 {
1717 if err := exec(fmt.Sprintf("PRAGMA query_only = %d;", queryOnly)); err != nil {
1718 C.sqlite3_close_v2(db)
1719 return nil, err
1720 }
1721 }
1722
1723 // Recursive Triggers
1724 if recursiveTriggers > -1 {
1725 if err := exec(fmt.Sprintf("PRAGMA recursive_triggers = %d;", recursiveTriggers)); err != nil {
1726 C.sqlite3_close_v2(db)
1727 return nil, err
1728 }
1729 }
1730
1731 // Secure Delete
1732 //
1733 // Because this package can set the compile time flag SQLITE_SECURE_DELETE with a build tag
1734 // the default value for secureDelete var is 'DEFAULT' this way
1735 // you can compile with secure_delete 'ON' and disable it for a specific database connection.
1736 if secureDelete != "DEFAULT" {
1737 if err := exec(fmt.Sprintf("PRAGMA secure_delete = %s;", secureDelete)); err != nil {
1738 C.sqlite3_close_v2(db)
1739 return nil, err
1740 }
1741 }
1742
1743 // Synchronous Mode
1744 //
1745 // Because default is NORMAL this statement is always executed
1746 if err := exec(fmt.Sprintf("PRAGMA synchronous = %s;", synchronousMode)); err != nil {
1747 conn.Close()
1748 return nil, err
1749 }
1750
1751 // Writable Schema
1752 if writableSchema > -1 {
1753 if err := exec(fmt.Sprintf("PRAGMA writable_schema = %d;", writableSchema)); err != nil {
1754 C.sqlite3_close_v2(db)
1755 return nil, err
1756 }
1757 }
1758
1759 // Cache Size
1760 if cacheSize != nil {
1761 if err := exec(fmt.Sprintf("PRAGMA cache_size = %d;", *cacheSize)); err != nil {
1762 C.sqlite3_close_v2(db)
1763 return nil, err
1764 }
1765 }
1766
1767 if len(d.Extensions) > 0 {
1768 if err := conn.loadExtensions(d.Extensions); err != nil {
1769 conn.Close()
1770 return nil, err
1771 }
1772 }
1773
1774 if d.ConnectHook != nil {
1775 if err := d.ConnectHook(conn); err != nil {
1776 conn.Close()
1777 return nil, err
1778 }
1779 }
1780 runtime.SetFinalizer(conn, (*SQLiteConn).Close)
1781 return conn, nil
1782 }
1783
1784 // Close the connection.
1785 func (c *SQLiteConn) Close() error {
1786 rv := C.sqlite3_close_v2(c.db)
1787 if rv != C.SQLITE_OK {
1788 return c.lastError()
1789 }
1790 deleteHandles(c)
1791 c.mu.Lock()
1792 c.db = nil
1793 c.mu.Unlock()
1794 runtime.SetFinalizer(c, nil)
1795 return nil
1796 }
1797
1798 func (c *SQLiteConn) dbConnOpen() bool {
1799 if c == nil {
1800 return false
1801 }
1802 c.mu.Lock()
1803 defer c.mu.Unlock()
1804 return c.db != nil
1805 }
1806
1807 // Prepare the query string. Return a new statement.
1808 func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error) {
1809 return c.prepare(context.Background(), query)
1810 }
1811
1812 func (c *SQLiteConn) prepare(ctx context.Context, query string) (driver.Stmt, error) {
1813 pquery := C.CString(query)
1814 defer C.free(unsafe.Pointer(pquery))
1815 var s *C.sqlite3_stmt
1816 var tail *C.char
1817 rv := C._sqlite3_prepare_v2_internal(c.db, pquery, C.int(-1), &s, &tail)
1818 if rv != C.SQLITE_OK {
1819 return nil, c.lastError()
1820 }
1821 var t string
1822 if tail != nil && *tail != '\000' {
1823 t = strings.TrimSpace(C.GoString(tail))
1824 }
1825 ss := &SQLiteStmt{c: c, s: s, t: t}
1826 runtime.SetFinalizer(ss, (*SQLiteStmt).Close)
1827 return ss, nil
1828 }
1829
1830 // Run-Time Limit Categories.
1831 // See: http://www.sqlite.org/c3ref/c_limit_attached.html
1832 const (
1833 SQLITE_LIMIT_LENGTH = C.SQLITE_LIMIT_LENGTH
1834 SQLITE_LIMIT_SQL_LENGTH = C.SQLITE_LIMIT_SQL_LENGTH
1835 SQLITE_LIMIT_COLUMN = C.SQLITE_LIMIT_COLUMN
1836 SQLITE_LIMIT_EXPR_DEPTH = C.SQLITE_LIMIT_EXPR_DEPTH
1837 SQLITE_LIMIT_COMPOUND_SELECT = C.SQLITE_LIMIT_COMPOUND_SELECT
1838 SQLITE_LIMIT_VDBE_OP = C.SQLITE_LIMIT_VDBE_OP
1839 SQLITE_LIMIT_FUNCTION_ARG = C.SQLITE_LIMIT_FUNCTION_ARG
1840 SQLITE_LIMIT_ATTACHED = C.SQLITE_LIMIT_ATTACHED
1841 SQLITE_LIMIT_LIKE_PATTERN_LENGTH = C.SQLITE_LIMIT_LIKE_PATTERN_LENGTH
1842 SQLITE_LIMIT_VARIABLE_NUMBER = C.SQLITE_LIMIT_VARIABLE_NUMBER
1843 SQLITE_LIMIT_TRIGGER_DEPTH = C.SQLITE_LIMIT_TRIGGER_DEPTH
1844 SQLITE_LIMIT_WORKER_THREADS = C.SQLITE_LIMIT_WORKER_THREADS
1845 )
1846
1847 // GetFilename returns the absolute path to the file containing
1848 // the requested schema. When passed an empty string, it will
1849 // instead use the database's default schema: "main".
1850 // See: sqlite3_db_filename, https://www.sqlite.org/c3ref/db_filename.html
1851 func (c *SQLiteConn) GetFilename(schemaName string) string {
1852 if schemaName == "" {
1853 schemaName = "main"
1854 }
1855 return C.GoString(C.sqlite3_db_filename(c.db, C.CString(schemaName)))
1856 }
1857
1858 // GetLimit returns the current value of a run-time limit.
1859 // See: sqlite3_limit, http://www.sqlite.org/c3ref/limit.html
1860 func (c *SQLiteConn) GetLimit(id int) int {
1861 return int(C._sqlite3_limit(c.db, C.int(id), C.int(-1)))
1862 }
1863
1864 // SetLimit changes the value of a run-time limits.
1865 // Then this method returns the prior value of the limit.
1866 // See: sqlite3_limit, http://www.sqlite.org/c3ref/limit.html
1867 func (c *SQLiteConn) SetLimit(id int, newVal int) int {
1868 return int(C._sqlite3_limit(c.db, C.int(id), C.int(newVal)))
1869 }
1870
1871 // SetFileControlInt invokes the xFileControl method on a given database. The
1872 // dbName is the name of the database. It will default to "main" if left blank.
1873 // The op is one of the opcodes prefixed by "SQLITE_FCNTL_". The arg argument
1874 // and return code are both opcode-specific. Please see the SQLite documentation.
1875 //
1876 // This method is not thread-safe as the returned error code can be changed by
1877 // another call if invoked concurrently.
1878 //
1879 // Use SetFileControlInt64 instead if the argument for the opcode is documented
1880 // as a pointer to a sqlite3_int64.
1881 //
1882 // See: sqlite3_file_control, https://www.sqlite.org/c3ref/file_control.html
1883 func (c *SQLiteConn) SetFileControlInt(dbName string, op int, arg int) error {
1884 if dbName == "" {
1885 dbName = "main"
1886 }
1887
1888 cDBName := C.CString(dbName)
1889 defer C.free(unsafe.Pointer(cDBName))
1890
1891 cArg := C.int(arg)
1892 rv := C.sqlite3_file_control(c.db, cDBName, C.int(op), unsafe.Pointer(&cArg))
1893 if rv != C.SQLITE_OK {
1894 return c.lastError()
1895 }
1896 return nil
1897 }
1898
1899 // SetFileControlInt64 invokes the xFileControl method on a given database. The
1900 // dbName is the name of the database. It will default to "main" if left blank.
1901 // The op is one of the opcodes prefixed by "SQLITE_FCNTL_". The arg argument
1902 // and return code are both opcode-specific. Please see the SQLite documentation.
1903 //
1904 // This method is not thread-safe as the returned error code can be changed by
1905 // another call if invoked concurrently.
1906 //
1907 // Only use this method if the argument for the opcode is documented as a pointer
1908 // to a sqlite3_int64.
1909 //
1910 // See: sqlite3_file_control, https://www.sqlite.org/c3ref/file_control.html
1911 func (c *SQLiteConn) SetFileControlInt64(dbName string, op int, arg int64) error {
1912 if dbName == "" {
1913 dbName = "main"
1914 }
1915
1916 cDBName := C.CString(dbName)
1917 defer C.free(unsafe.Pointer(cDBName))
1918
1919 cArg := C.sqlite3_int64(arg)
1920 rv := C.sqlite3_file_control(c.db, cDBName, C.int(op), unsafe.Pointer(&cArg))
1921 if rv != C.SQLITE_OK {
1922 return c.lastError()
1923 }
1924 return nil
1925 }
1926
1927 // Close the statement.
1928 func (s *SQLiteStmt) Close() error {
1929 s.mu.Lock()
1930 defer s.mu.Unlock()
1931 if s.closed {
1932 return nil
1933 }
1934 s.closed = true
1935 if !s.c.dbConnOpen() {
1936 return errors.New("sqlite statement with already closed database connection")
1937 }
1938 rv := C.sqlite3_finalize(s.s)
1939 s.s = nil
1940 if rv != C.SQLITE_OK {
1941 return s.c.lastError()
1942 }
1943 s.c = nil
1944 runtime.SetFinalizer(s, nil)
1945 return nil
1946 }
1947
1948 // NumInput return a number of parameters.
1949 func (s *SQLiteStmt) NumInput() int {
1950 return int(C.sqlite3_bind_parameter_count(s.s))
1951 }
1952
1953 var placeHolder = []byte{0}
1954
1955 func (s *SQLiteStmt) bind(args []driver.NamedValue) error {
1956 rv := C.sqlite3_reset(s.s)
1957 if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE {
1958 return s.c.lastError()
1959 }
1960
1961 bindIndices := make([][3]int, len(args))
1962 prefixes := []string{":", "@", "$"}
1963 for i, v := range args {
1964 bindIndices[i][0] = args[i].Ordinal
1965 if v.Name != "" {
1966 for j := range prefixes {
1967 cname := C.CString(prefixes[j] + v.Name)
1968 bindIndices[i][j] = int(C.sqlite3_bind_parameter_index(s.s, cname))
1969 C.free(unsafe.Pointer(cname))
1970 }
1971 args[i].Ordinal = bindIndices[i][0]
1972 }
1973 }
1974
1975 for i, arg := range args {
1976 for j := range bindIndices[i] {
1977 if bindIndices[i][j] == 0 {
1978 continue
1979 }
1980 n := C.int(bindIndices[i][j])
1981 switch v := arg.Value.(type) {
1982 case nil:
1983 rv = C.sqlite3_bind_null(s.s, n)
1984 case string:
1985 if len(v) == 0 {
1986 rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&placeHolder[0])), C.int(0))
1987 } else {
1988 b := []byte(v)
1989 rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
1990 }
1991 case int64:
1992 rv = C.sqlite3_bind_int64(s.s, n, C.sqlite3_int64(v))
1993 case bool:
1994 if v {
1995 rv = C.sqlite3_bind_int(s.s, n, 1)
1996 } else {
1997 rv = C.sqlite3_bind_int(s.s, n, 0)
1998 }
1999 case float64:
2000 rv = C.sqlite3_bind_double(s.s, n, C.double(v))
2001 case []byte:
2002 if v == nil {
2003 rv = C.sqlite3_bind_null(s.s, n)
2004 } else {
2005 ln := len(v)
2006 if ln == 0 {
2007 v = placeHolder
2008 }
2009 rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(&v[0]), C.int(ln))
2010 }
2011 case time.Time:
2012 b := []byte(v.Format(SQLiteTimestampFormats[0]))
2013 rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
2014 }
2015 if rv != C.SQLITE_OK {
2016 return s.c.lastError()
2017 }
2018 }
2019 }
2020 return nil
2021 }
2022
2023 // Query the statement with arguments. Return records.
2024 func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error) {
2025 list := make([]driver.NamedValue, len(args))
2026 for i, v := range args {
2027 list[i] = driver.NamedValue{
2028 Ordinal: i + 1,
2029 Value: v,
2030 }
2031 }
2032 return s.query(context.Background(), list)
2033 }
2034
2035 func (s *SQLiteStmt) query(ctx context.Context, args []driver.NamedValue) (driver.Rows, error) {
2036 if err := s.bind(args); err != nil {
2037 return nil, err
2038 }
2039
2040 rows := &SQLiteRows{
2041 s: s,
2042 nc: int32(C.sqlite3_column_count(s.s)),
2043 cls: s.cls,
2044 cols: nil,
2045 decltype: nil,
2046 ctx: ctx,
2047 }
2048
2049 return rows, nil
2050 }
2051
2052 // LastInsertId return last inserted ID.
2053 func (r *SQLiteResult) LastInsertId() (int64, error) {
2054 return r.id, nil
2055 }
2056
2057 // RowsAffected return how many rows affected.
2058 func (r *SQLiteResult) RowsAffected() (int64, error) {
2059 return r.changes, nil
2060 }
2061
2062 // Exec execute the statement with arguments. Return result object.
2063 func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error) {
2064 list := make([]driver.NamedValue, len(args))
2065 for i, v := range args {
2066 list[i] = driver.NamedValue{
2067 Ordinal: i + 1,
2068 Value: v,
2069 }
2070 }
2071 return s.exec(context.Background(), list)
2072 }
2073
2074 func isInterruptErr(err error) bool {
2075 sqliteErr, ok := err.(Error)
2076 if ok {
2077 return sqliteErr.Code == ErrInterrupt
2078 }
2079 return false
2080 }
2081
2082 // exec executes a query that doesn't return rows. Attempts to honor context timeout.
2083 func (s *SQLiteStmt) exec(ctx context.Context, args []driver.NamedValue) (driver.Result, error) {
2084 if ctx.Done() == nil {
2085 return s.execSync(args)
2086 }
2087
2088 type result struct {
2089 r driver.Result
2090 err error
2091 }
2092 resultCh := make(chan result)
2093 defer close(resultCh)
2094 go func() {
2095 r, err := s.execSync(args)
2096 resultCh <- result{r, err}
2097 }()
2098 var rv result
2099 select {
2100 case rv = <-resultCh:
2101 case <-ctx.Done():
2102 select {
2103 case rv = <-resultCh: // no need to interrupt, operation completed in db
2104 default:
2105 // this is still racy and can be no-op if executed between sqlite3_* calls in execSync.
2106 C.sqlite3_interrupt(s.c.db)
2107 rv = <-resultCh // wait for goroutine completed
2108 if isInterruptErr(rv.err) {
2109 return nil, ctx.Err()
2110 }
2111 }
2112 }
2113 return rv.r, rv.err
2114 }
2115
2116 func (s *SQLiteStmt) execSync(args []driver.NamedValue) (driver.Result, error) {
2117 if err := s.bind(args); err != nil {
2118 C.sqlite3_reset(s.s)
2119 C.sqlite3_clear_bindings(s.s)
2120 return nil, err
2121 }
2122
2123 var rowid, changes C.longlong
2124 rv := C._sqlite3_step_row_internal(s.s, &rowid, &changes)
2125 if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE {
2126 err := s.c.lastError()
2127 C.sqlite3_reset(s.s)
2128 C.sqlite3_clear_bindings(s.s)
2129 return nil, err
2130 }
2131
2132 return &SQLiteResult{id: int64(rowid), changes: int64(changes)}, nil
2133 }
2134
2135 // Readonly reports if this statement is considered readonly by SQLite.
2136 //
2137 // See: https://sqlite.org/c3ref/stmt_readonly.html
2138 func (s *SQLiteStmt) Readonly() bool {
2139 return C.sqlite3_stmt_readonly(s.s) == 1
2140 }
2141
2142 // Close the rows.
2143 func (rc *SQLiteRows) Close() error {
2144 rc.closemu.Lock()
2145 defer rc.closemu.Unlock()
2146 s := rc.s
2147 if s == nil {
2148 return nil
2149 }
2150 rc.s = nil // remove reference to SQLiteStmt
2151 s.mu.Lock()
2152 if s.closed {
2153 s.mu.Unlock()
2154 return nil
2155 }
2156 if rc.cls {
2157 s.mu.Unlock()
2158 return s.Close()
2159 }
2160 rv := C.sqlite3_reset(s.s)
2161 if rv != C.SQLITE_OK {
2162 s.mu.Unlock()
2163 return s.c.lastError()
2164 }
2165 s.mu.Unlock()
2166 return nil
2167 }
2168
2169 // Columns return column names.
2170 func (rc *SQLiteRows) Columns() []string {
2171 rc.s.mu.Lock()
2172 defer rc.s.mu.Unlock()
2173 if rc.s.s != nil && int(rc.nc) != len(rc.cols) {
2174 rc.cols = make([]string, rc.nc)
2175 for i := 0; i < int(rc.nc); i++ {
2176 rc.cols[i] = C.GoString(C.sqlite3_column_name(rc.s.s, C.int(i)))
2177 }
2178 }
2179 return rc.cols
2180 }
2181
2182 func (rc *SQLiteRows) declTypes() []string {
2183 if rc.s.s != nil && rc.decltype == nil {
2184 rc.decltype = make([]string, rc.nc)
2185 for i := 0; i < int(rc.nc); i++ {
2186 rc.decltype[i] = strings.ToLower(C.GoString(C.sqlite3_column_decltype(rc.s.s, C.int(i))))
2187 }
2188 }
2189 return rc.decltype
2190 }
2191
2192 // DeclTypes return column types.
2193 func (rc *SQLiteRows) DeclTypes() []string {
2194 rc.s.mu.Lock()
2195 defer rc.s.mu.Unlock()
2196 return rc.declTypes()
2197 }
2198
2199 // Next move cursor to next. Attempts to honor context timeout from QueryContext call.
2200 func (rc *SQLiteRows) Next(dest []driver.Value) error {
2201 rc.s.mu.Lock()
2202 defer rc.s.mu.Unlock()
2203
2204 if rc.s.closed {
2205 return io.EOF
2206 }
2207
2208 if rc.ctx.Done() == nil {
2209 return rc.nextSyncLocked(dest)
2210 }
2211 resultCh := make(chan error)
2212 defer close(resultCh)
2213 go func() {
2214 resultCh <- rc.nextSyncLocked(dest)
2215 }()
2216 select {
2217 case err := <-resultCh:
2218 return err
2219 case <-rc.ctx.Done():
2220 select {
2221 case <-resultCh: // no need to interrupt
2222 default:
2223 // this is still racy and can be no-op if executed between sqlite3_* calls in nextSyncLocked.
2224 C.sqlite3_interrupt(rc.s.c.db)
2225 <-resultCh // ensure goroutine completed
2226 }
2227 return rc.ctx.Err()
2228 }
2229 }
2230
2231 // nextSyncLocked moves cursor to next; must be called with locked mutex.
2232 func (rc *SQLiteRows) nextSyncLocked(dest []driver.Value) error {
2233 rv := C._sqlite3_step_internal(rc.s.s)
2234 if rv == C.SQLITE_DONE {
2235 return io.EOF
2236 }
2237 if rv != C.SQLITE_ROW {
2238 rv = C.sqlite3_reset(rc.s.s)
2239 if rv != C.SQLITE_OK {
2240 return rc.s.c.lastError()
2241 }
2242 return nil
2243 }
2244
2245 rc.declTypes()
2246
2247 for i := range dest {
2248 switch C.sqlite3_column_type(rc.s.s, C.int(i)) {
2249 case C.SQLITE_INTEGER:
2250 val := int64(C.sqlite3_column_int64(rc.s.s, C.int(i)))
2251 switch rc.decltype[i] {
2252 case columnTimestamp, columnDatetime, columnDate:
2253 var t time.Time
2254 // Assume a millisecond unix timestamp if it's 13 digits -- too
2255 // large to be a reasonable timestamp in seconds.
2256 if val > 1e12 || val < -1e12 {
2257 val *= int64(time.Millisecond) // convert ms to nsec
2258 t = time.Unix(0, val)
2259 } else {
2260 t = time.Unix(val, 0)
2261 }
2262 t = t.UTC()
2263 if rc.s.c.loc != nil {
2264 t = t.In(rc.s.c.loc)
2265 }
2266 dest[i] = t
2267 case "boolean":
2268 dest[i] = val > 0
2269 default:
2270 dest[i] = val
2271 }
2272 case C.SQLITE_FLOAT:
2273 dest[i] = float64(C.sqlite3_column_double(rc.s.s, C.int(i)))
2274 case C.SQLITE_BLOB:
2275 p := C.sqlite3_column_blob(rc.s.s, C.int(i))
2276 if p == nil {
2277 dest[i] = []byte{}
2278 continue
2279 }
2280 n := C.sqlite3_column_bytes(rc.s.s, C.int(i))
2281 dest[i] = C.GoBytes(p, n)
2282 case C.SQLITE_NULL:
2283 dest[i] = nil
2284 case C.SQLITE_TEXT:
2285 var err error
2286 var timeVal time.Time
2287
2288 n := int(C.sqlite3_column_bytes(rc.s.s, C.int(i)))
2289 s := C.GoStringN((*C.char)(unsafe.Pointer(C.sqlite3_column_text(rc.s.s, C.int(i)))), C.int(n))
2290
2291 switch rc.decltype[i] {
2292 case columnTimestamp, columnDatetime, columnDate:
2293 var t time.Time
2294 s = strings.TrimSuffix(s, "Z")
2295 for _, format := range SQLiteTimestampFormats {
2296 if timeVal, err = time.ParseInLocation(format, s, time.UTC); err == nil {
2297 t = timeVal
2298 break
2299 }
2300 }
2301 if err != nil {
2302 // The column is a time value, so return the zero time on parse failure.
2303 t = time.Time{}
2304 }
2305 if rc.s.c.loc != nil {
2306 t = t.In(rc.s.c.loc)
2307 }
2308 dest[i] = t
2309 default:
2310 dest[i] = s
2311 }
2312 }
2313 }
2314 return nil
2315 }
2316