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