1// Copyright (C) 2014 Yasuhiro Matsumoto <mattn.jp@gmail.com>. 2// 3// Use of this source code is governed by an MIT-style 4// license that can be found in the LICENSE file. 5 6package sqlite3 7 8/* 9#cgo CFLAGS: -std=gnu99 10#cgo CFLAGS: -DSQLITE_ENABLE_RTREE -DSQLITE_THREADSAFE=1 11#cgo CFLAGS: -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_FTS4_UNICODE61 12#cgo CFLAGS: -DSQLITE_TRACE_SIZE_LIMIT=15 13#cgo CFLAGS: -DSQLITE_DISABLE_INTRINSIC 14#cgo CFLAGS: -Wno-deprecated-declarations 15#ifndef USE_LIBSQLITE3 16#include <sqlite3-binding.h> 17#else 18#include <sqlite3.h> 19#endif 20#include <stdlib.h> 21#include <string.h> 22 23#ifdef __CYGWIN__ 24# include <errno.h> 25#endif 26 27#ifndef SQLITE_OPEN_READWRITE 28# define SQLITE_OPEN_READWRITE 0 29#endif 30 31#ifndef SQLITE_OPEN_FULLMUTEX 32# define SQLITE_OPEN_FULLMUTEX 0 33#endif 34 35#ifndef SQLITE_DETERMINISTIC 36# define SQLITE_DETERMINISTIC 0 37#endif 38 39static int 40_sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs) { 41#ifdef SQLITE_OPEN_URI 42 return sqlite3_open_v2(filename, ppDb, flags | SQLITE_OPEN_URI, zVfs); 43#else 44 return sqlite3_open_v2(filename, ppDb, flags, zVfs); 45#endif 46} 47 48static int 49_sqlite3_bind_text(sqlite3_stmt *stmt, int n, char *p, int np) { 50 return sqlite3_bind_text(stmt, n, p, np, SQLITE_TRANSIENT); 51} 52 53static int 54_sqlite3_bind_blob(sqlite3_stmt *stmt, int n, void *p, int np) { 55 return sqlite3_bind_blob(stmt, n, p, np, SQLITE_TRANSIENT); 56} 57 58#include <stdio.h> 59#include <stdint.h> 60 61static int 62_sqlite3_exec(sqlite3* db, const char* pcmd, long long* rowid, long long* changes) 63{ 64 int rv = sqlite3_exec(db, pcmd, 0, 0, 0); 65 *rowid = (long long) sqlite3_last_insert_rowid(db); 66 *changes = (long long) sqlite3_changes(db); 67 return rv; 68} 69 70static int 71_sqlite3_step(sqlite3_stmt* stmt, long long* rowid, long long* changes) 72{ 73 int rv = sqlite3_step(stmt); 74 sqlite3* db = sqlite3_db_handle(stmt); 75 *rowid = (long long) sqlite3_last_insert_rowid(db); 76 *changes = (long long) sqlite3_changes(db); 77 return rv; 78} 79 80void _sqlite3_result_text(sqlite3_context* ctx, const char* s) { 81 sqlite3_result_text(ctx, s, -1, &free); 82} 83 84void _sqlite3_result_blob(sqlite3_context* ctx, const void* b, int l) { 85 sqlite3_result_blob(ctx, b, l, SQLITE_TRANSIENT); 86} 87 88 89int _sqlite3_create_function( 90 sqlite3 *db, 91 const char *zFunctionName, 92 int nArg, 93 int eTextRep, 94 uintptr_t pApp, 95 void (*xFunc)(sqlite3_context*,int,sqlite3_value**), 96 void (*xStep)(sqlite3_context*,int,sqlite3_value**), 97 void (*xFinal)(sqlite3_context*) 98) { 99 return sqlite3_create_function(db, zFunctionName, nArg, eTextRep, (void*) pApp, xFunc, xStep, xFinal); 100} 101 102void callbackTrampoline(sqlite3_context*, int, sqlite3_value**); 103void stepTrampoline(sqlite3_context*, int, sqlite3_value**); 104void doneTrampoline(sqlite3_context*); 105 106int compareTrampoline(void*, int, char*, int, char*); 107int commitHookTrampoline(void*); 108void rollbackHookTrampoline(void*); 109void updateHookTrampoline(void*, int, char*, char*, sqlite3_int64); 110 111#ifdef SQLITE_LIMIT_WORKER_THREADS 112# define _SQLITE_HAS_LIMIT 113# define SQLITE_LIMIT_LENGTH 0 114# define SQLITE_LIMIT_SQL_LENGTH 1 115# define SQLITE_LIMIT_COLUMN 2 116# define SQLITE_LIMIT_EXPR_DEPTH 3 117# define SQLITE_LIMIT_COMPOUND_SELECT 4 118# define SQLITE_LIMIT_VDBE_OP 5 119# define SQLITE_LIMIT_FUNCTION_ARG 6 120# define SQLITE_LIMIT_ATTACHED 7 121# define SQLITE_LIMIT_LIKE_PATTERN_LENGTH 8 122# define SQLITE_LIMIT_VARIABLE_NUMBER 9 123# define SQLITE_LIMIT_TRIGGER_DEPTH 10 124# define SQLITE_LIMIT_WORKER_THREADS 11 125# else 126# define SQLITE_LIMIT_WORKER_THREADS 11 127#endif 128 129static int _sqlite3_limit(sqlite3* db, int limitId, int newLimit) { 130#ifndef _SQLITE_HAS_LIMIT 131 return -1; 132#else 133 return sqlite3_limit(db, limitId, newLimit); 134#endif 135} 136*/ 137import "C" 138import ( 139 "context" 140 "database/sql" 141 "database/sql/driver" 142 "errors" 143 "fmt" 144 "io" 145 "net/url" 146 "reflect" 147 "runtime" 148 "strconv" 149 "strings" 150 "sync" 151 "time" 152 "unsafe" 153) 154 155// SQLiteTimestampFormats is timestamp formats understood by both this module 156// and SQLite. The first format in the slice will be used when saving time 157// values into the database. When parsing a string from a timestamp or datetime 158// column, the formats are tried in order. 159var SQLiteTimestampFormats = []string{ 160 // By default, store timestamps with whatever timezone they come with. 161 // When parsed, they will be returned with the same timezone. 162 "2006-01-02 15:04:05.999999999-07:00", 163 "2006-01-02T15:04:05.999999999-07:00", 164 "2006-01-02 15:04:05.999999999", 165 "2006-01-02T15:04:05.999999999", 166 "2006-01-02 15:04:05", 167 "2006-01-02T15:04:05", 168 "2006-01-02 15:04", 169 "2006-01-02T15:04", 170 "2006-01-02", 171} 172 173func init() { 174 sql.Register("sqlite3", &SQLiteDriver{}) 175} 176 177// Version returns SQLite library version information. 178func Version() (libVersion string, libVersionNumber int, sourceID string) { 179 libVersion = C.GoString(C.sqlite3_libversion()) 180 libVersionNumber = int(C.sqlite3_libversion_number()) 181 sourceID = C.GoString(C.sqlite3_sourceid()) 182 return libVersion, libVersionNumber, sourceID 183} 184 185const ( 186 SQLITE_DELETE = C.SQLITE_DELETE 187 SQLITE_INSERT = C.SQLITE_INSERT 188 SQLITE_UPDATE = C.SQLITE_UPDATE 189) 190 191// SQLiteDriver implement sql.Driver. 192type SQLiteDriver struct { 193 Extensions []string 194 ConnectHook func(*SQLiteConn) error 195} 196 197// SQLiteConn implement sql.Conn. 198type SQLiteConn struct { 199 mu sync.Mutex 200 db *C.sqlite3 201 loc *time.Location 202 txlock string 203 funcs []*functionInfo 204 aggregators []*aggInfo 205} 206 207// SQLiteTx implemen sql.Tx. 208type SQLiteTx struct { 209 c *SQLiteConn 210} 211 212// SQLiteStmt implement sql.Stmt. 213type SQLiteStmt struct { 214 mu sync.Mutex 215 c *SQLiteConn 216 s *C.sqlite3_stmt 217 t string 218 closed bool 219 cls bool 220} 221 222// SQLiteResult implement sql.Result. 223type SQLiteResult struct { 224 id int64 225 changes int64 226} 227 228// SQLiteRows implement sql.Rows. 229type SQLiteRows struct { 230 s *SQLiteStmt 231 nc int 232 cols []string 233 decltype []string 234 cls bool 235 closed bool 236 done chan struct{} 237} 238 239type functionInfo struct { 240 f reflect.Value 241 argConverters []callbackArgConverter 242 variadicConverter callbackArgConverter 243 retConverter callbackRetConverter 244} 245 246func (fi *functionInfo) Call(ctx *C.sqlite3_context, argv []*C.sqlite3_value) { 247 args, err := callbackConvertArgs(argv, fi.argConverters, fi.variadicConverter) 248 if err != nil { 249 callbackError(ctx, err) 250 return 251 } 252 253 ret := fi.f.Call(args) 254 255 if len(ret) == 2 && ret[1].Interface() != nil { 256 callbackError(ctx, ret[1].Interface().(error)) 257 return 258 } 259 260 err = fi.retConverter(ctx, ret[0]) 261 if err != nil { 262 callbackError(ctx, err) 263 return 264 } 265} 266 267type aggInfo struct { 268 constructor reflect.Value 269 270 // Active aggregator objects for aggregations in flight. The 271 // aggregators are indexed by a counter stored in the aggregation 272 // user data space provided by sqlite. 273 active map[int64]reflect.Value 274 next int64 275 276 stepArgConverters []callbackArgConverter 277 stepVariadicConverter callbackArgConverter 278 279 doneRetConverter callbackRetConverter 280} 281 282func (ai *aggInfo) agg(ctx *C.sqlite3_context) (int64, reflect.Value, error) { 283 aggIdx := (*int64)(C.sqlite3_aggregate_context(ctx, C.int(8))) 284 if *aggIdx == 0 { 285 *aggIdx = ai.next 286 ret := ai.constructor.Call(nil) 287 if len(ret) == 2 && ret[1].Interface() != nil { 288 return 0, reflect.Value{}, ret[1].Interface().(error) 289 } 290 if ret[0].IsNil() { 291 return 0, reflect.Value{}, errors.New("aggregator constructor returned nil state") 292 } 293 ai.next++ 294 ai.active[*aggIdx] = ret[0] 295 } 296 return *aggIdx, ai.active[*aggIdx], nil 297} 298 299func (ai *aggInfo) Step(ctx *C.sqlite3_context, argv []*C.sqlite3_value) { 300 _, agg, err := ai.agg(ctx) 301 if err != nil { 302 callbackError(ctx, err) 303 return 304 } 305 306 args, err := callbackConvertArgs(argv, ai.stepArgConverters, ai.stepVariadicConverter) 307 if err != nil { 308 callbackError(ctx, err) 309 return 310 } 311 312 ret := agg.MethodByName("Step").Call(args) 313 if len(ret) == 1 && ret[0].Interface() != nil { 314 callbackError(ctx, ret[0].Interface().(error)) 315 return 316 } 317} 318 319func (ai *aggInfo) Done(ctx *C.sqlite3_context) { 320 idx, agg, err := ai.agg(ctx) 321 if err != nil { 322 callbackError(ctx, err) 323 return 324 } 325 defer func() { delete(ai.active, idx) }() 326 327 ret := agg.MethodByName("Done").Call(nil) 328 if len(ret) == 2 && ret[1].Interface() != nil { 329 callbackError(ctx, ret[1].Interface().(error)) 330 return 331 } 332 333 err = ai.doneRetConverter(ctx, ret[0]) 334 if err != nil { 335 callbackError(ctx, err) 336 return 337 } 338} 339 340// Commit transaction. 341func (tx *SQLiteTx) Commit() error { 342 _, err := tx.c.exec(context.Background(), "COMMIT", nil) 343 if err != nil && err.(Error).Code == C.SQLITE_BUSY { 344 // sqlite3 will leave the transaction open in this scenario. 345 // However, database/sql considers the transaction complete once we 346 // return from Commit() - we must clean up to honour its semantics. 347 tx.c.exec(context.Background(), "ROLLBACK", nil) 348 } 349 return err 350} 351 352// Rollback transaction. 353func (tx *SQLiteTx) Rollback() error { 354 _, err := tx.c.exec(context.Background(), "ROLLBACK", nil) 355 return err 356} 357 358// RegisterCollation makes a Go function available as a collation. 359// 360// cmp receives two UTF-8 strings, a and b. The result should be 0 if 361// a==b, -1 if a < b, and +1 if a > b. 362// 363// cmp must always return the same result given the same 364// inputs. Additionally, it must have the following properties for all 365// strings A, B and C: if A==B then B==A; if A==B and B==C then A==C; 366// if A<B then B>A; if A<B and B<C then A<C. 367// 368// If cmp does not obey these constraints, sqlite3's behavior is 369// undefined when the collation is used. 370func (c *SQLiteConn) RegisterCollation(name string, cmp func(string, string) int) error { 371 handle := newHandle(c, cmp) 372 cname := C.CString(name) 373 defer C.free(unsafe.Pointer(cname)) 374 rv := C.sqlite3_create_collation(c.db, cname, C.SQLITE_UTF8, unsafe.Pointer(handle), (*[0]byte)(unsafe.Pointer(C.compareTrampoline))) 375 if rv != C.SQLITE_OK { 376 return c.lastError() 377 } 378 return nil 379} 380 381// RegisterCommitHook sets the commit hook for a connection. 382// 383// If the callback returns non-zero the transaction will become a rollback. 384// 385// If there is an existing commit hook for this connection, it will be 386// removed. If callback is nil the existing hook (if any) will be removed 387// without creating a new one. 388func (c *SQLiteConn) RegisterCommitHook(callback func() int) { 389 if callback == nil { 390 C.sqlite3_commit_hook(c.db, nil, nil) 391 } else { 392 C.sqlite3_commit_hook(c.db, (*[0]byte)(unsafe.Pointer(C.commitHookTrampoline)), unsafe.Pointer(newHandle(c, callback))) 393 } 394} 395 396// RegisterRollbackHook sets the rollback hook for a connection. 397// 398// If there is an existing rollback hook for this connection, it will be 399// removed. If callback is nil the existing hook (if any) will be removed 400// without creating a new one. 401func (c *SQLiteConn) RegisterRollbackHook(callback func()) { 402 if callback == nil { 403 C.sqlite3_rollback_hook(c.db, nil, nil) 404 } else { 405 C.sqlite3_rollback_hook(c.db, (*[0]byte)(unsafe.Pointer(C.rollbackHookTrampoline)), unsafe.Pointer(newHandle(c, callback))) 406 } 407} 408 409// RegisterUpdateHook sets the update hook for a connection. 410// 411// The parameters to the callback are the operation (one of the constants 412// SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE), the database name, the 413// table name, and the rowid. 414// 415// If there is an existing update hook for this connection, it will be 416// removed. If callback is nil the existing hook (if any) will be removed 417// without creating a new one. 418func (c *SQLiteConn) RegisterUpdateHook(callback func(int, string, string, int64)) { 419 if callback == nil { 420 C.sqlite3_update_hook(c.db, nil, nil) 421 } else { 422 C.sqlite3_update_hook(c.db, (*[0]byte)(unsafe.Pointer(C.updateHookTrampoline)), unsafe.Pointer(newHandle(c, callback))) 423 } 424} 425 426// RegisterFunc makes a Go function available as a SQLite function. 427// 428// The Go function can have arguments of the following types: any 429// numeric type except complex, bool, []byte, string and 430// interface{}. interface{} arguments are given the direct translation 431// of the SQLite data type: int64 for INTEGER, float64 for FLOAT, 432// []byte for BLOB, string for TEXT. 433// 434// The function can additionally be variadic, as long as the type of 435// the variadic argument is one of the above. 436// 437// If pure is true. SQLite will assume that the function's return 438// value depends only on its inputs, and make more aggressive 439// optimizations in its queries. 440// 441// See _example/go_custom_funcs for a detailed example. 442func (c *SQLiteConn) RegisterFunc(name string, impl interface{}, pure bool) error { 443 var fi functionInfo 444 fi.f = reflect.ValueOf(impl) 445 t := fi.f.Type() 446 if t.Kind() != reflect.Func { 447 return errors.New("Non-function passed to RegisterFunc") 448 } 449 if t.NumOut() != 1 && t.NumOut() != 2 { 450 return errors.New("SQLite functions must return 1 or 2 values") 451 } 452 if t.NumOut() == 2 && !t.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) { 453 return errors.New("Second return value of SQLite function must be error") 454 } 455 456 numArgs := t.NumIn() 457 if t.IsVariadic() { 458 numArgs-- 459 } 460 461 for i := 0; i < numArgs; i++ { 462 conv, err := callbackArg(t.In(i)) 463 if err != nil { 464 return err 465 } 466 fi.argConverters = append(fi.argConverters, conv) 467 } 468 469 if t.IsVariadic() { 470 conv, err := callbackArg(t.In(numArgs).Elem()) 471 if err != nil { 472 return err 473 } 474 fi.variadicConverter = conv 475 // Pass -1 to sqlite so that it allows any number of 476 // arguments. The call helper verifies that the minimum number 477 // of arguments is present for variadic functions. 478 numArgs = -1 479 } 480 481 conv, err := callbackRet(t.Out(0)) 482 if err != nil { 483 return err 484 } 485 fi.retConverter = conv 486 487 // fi must outlast the database connection, or we'll have dangling pointers. 488 c.funcs = append(c.funcs, &fi) 489 490 cname := C.CString(name) 491 defer C.free(unsafe.Pointer(cname)) 492 opts := C.SQLITE_UTF8 493 if pure { 494 opts |= C.SQLITE_DETERMINISTIC 495 } 496 rv := sqlite3CreateFunction(c.db, cname, C.int(numArgs), C.int(opts), newHandle(c, &fi), C.callbackTrampoline, nil, nil) 497 if rv != C.SQLITE_OK { 498 return c.lastError() 499 } 500 return nil 501} 502 503func sqlite3CreateFunction(db *C.sqlite3, zFunctionName *C.char, nArg C.int, eTextRep C.int, pApp uintptr, xFunc unsafe.Pointer, xStep unsafe.Pointer, xFinal unsafe.Pointer) C.int { 504 return C._sqlite3_create_function(db, zFunctionName, nArg, eTextRep, C.uintptr_t(pApp), (*[0]byte)(unsafe.Pointer(xFunc)), (*[0]byte)(unsafe.Pointer(xStep)), (*[0]byte)(unsafe.Pointer(xFinal))) 505} 506 507// RegisterAggregator makes a Go type available as a SQLite aggregation function. 508// 509// Because aggregation is incremental, it's implemented in Go with a 510// type that has 2 methods: func Step(values) accumulates one row of 511// data into the accumulator, and func Done() ret finalizes and 512// returns the aggregate value. "values" and "ret" may be any type 513// supported by RegisterFunc. 514// 515// RegisterAggregator takes as implementation a constructor function 516// that constructs an instance of the aggregator type each time an 517// aggregation begins. The constructor must return a pointer to a 518// type, or an interface that implements Step() and Done(). 519// 520// The constructor function and the Step/Done methods may optionally 521// return an error in addition to their other return values. 522// 523// See _example/go_custom_funcs for a detailed example. 524func (c *SQLiteConn) RegisterAggregator(name string, impl interface{}, pure bool) error { 525 var ai aggInfo 526 ai.constructor = reflect.ValueOf(impl) 527 t := ai.constructor.Type() 528 if t.Kind() != reflect.Func { 529 return errors.New("non-function passed to RegisterAggregator") 530 } 531 if t.NumOut() != 1 && t.NumOut() != 2 { 532 return errors.New("SQLite aggregator constructors must return 1 or 2 values") 533 } 534 if t.NumOut() == 2 && !t.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) { 535 return errors.New("Second return value of SQLite function must be error") 536 } 537 if t.NumIn() != 0 { 538 return errors.New("SQLite aggregator constructors must not have arguments") 539 } 540 541 agg := t.Out(0) 542 switch agg.Kind() { 543 case reflect.Ptr, reflect.Interface: 544 default: 545 return errors.New("SQlite aggregator constructor must return a pointer object") 546 } 547 stepFn, found := agg.MethodByName("Step") 548 if !found { 549 return errors.New("SQlite aggregator doesn't have a Step() function") 550 } 551 step := stepFn.Type 552 if step.NumOut() != 0 && step.NumOut() != 1 { 553 return errors.New("SQlite aggregator Step() function must return 0 or 1 values") 554 } 555 if step.NumOut() == 1 && !step.Out(0).Implements(reflect.TypeOf((*error)(nil)).Elem()) { 556 return errors.New("type of SQlite aggregator Step() return value must be error") 557 } 558 559 stepNArgs := step.NumIn() 560 start := 0 561 if agg.Kind() == reflect.Ptr { 562 // Skip over the method receiver 563 stepNArgs-- 564 start++ 565 } 566 if step.IsVariadic() { 567 stepNArgs-- 568 } 569 for i := start; i < start+stepNArgs; i++ { 570 conv, err := callbackArg(step.In(i)) 571 if err != nil { 572 return err 573 } 574 ai.stepArgConverters = append(ai.stepArgConverters, conv) 575 } 576 if step.IsVariadic() { 577 conv, err := callbackArg(t.In(start + stepNArgs).Elem()) 578 if err != nil { 579 return err 580 } 581 ai.stepVariadicConverter = conv 582 // Pass -1 to sqlite so that it allows any number of 583 // arguments. The call helper verifies that the minimum number 584 // of arguments is present for variadic functions. 585 stepNArgs = -1 586 } 587 588 doneFn, found := agg.MethodByName("Done") 589 if !found { 590 return errors.New("SQlite aggregator doesn't have a Done() function") 591 } 592 done := doneFn.Type 593 doneNArgs := done.NumIn() 594 if agg.Kind() == reflect.Ptr { 595 // Skip over the method receiver 596 doneNArgs-- 597 } 598 if doneNArgs != 0 { 599 return errors.New("SQlite aggregator Done() function must have no arguments") 600 } 601 if done.NumOut() != 1 && done.NumOut() != 2 { 602 return errors.New("SQLite aggregator Done() function must return 1 or 2 values") 603 } 604 if done.NumOut() == 2 && !done.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) { 605 return errors.New("second return value of SQLite aggregator Done() function must be error") 606 } 607 608 conv, err := callbackRet(done.Out(0)) 609 if err != nil { 610 return err 611 } 612 ai.doneRetConverter = conv 613 ai.active = make(map[int64]reflect.Value) 614 ai.next = 1 615 616 // ai must outlast the database connection, or we'll have dangling pointers. 617 c.aggregators = append(c.aggregators, &ai) 618 619 cname := C.CString(name) 620 defer C.free(unsafe.Pointer(cname)) 621 opts := C.SQLITE_UTF8 622 if pure { 623 opts |= C.SQLITE_DETERMINISTIC 624 } 625 rv := sqlite3CreateFunction(c.db, cname, C.int(stepNArgs), C.int(opts), newHandle(c, &ai), nil, C.stepTrampoline, C.doneTrampoline) 626 if rv != C.SQLITE_OK { 627 return c.lastError() 628 } 629 return nil 630} 631 632// AutoCommit return which currently auto commit or not. 633func (c *SQLiteConn) AutoCommit() bool { 634 return int(C.sqlite3_get_autocommit(c.db)) != 0 635} 636 637func (c *SQLiteConn) lastError() error { 638 return lastError(c.db) 639} 640 641func lastError(db *C.sqlite3) error { 642 rv := C.sqlite3_errcode(db) 643 if rv == C.SQLITE_OK { 644 return nil 645 } 646 return Error{ 647 Code: ErrNo(rv), 648 ExtendedCode: ErrNoExtended(C.sqlite3_extended_errcode(db)), 649 err: C.GoString(C.sqlite3_errmsg(db)), 650 } 651} 652 653// Exec implements Execer. 654func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error) { 655 list := make([]namedValue, len(args)) 656 for i, v := range args { 657 list[i] = namedValue{ 658 Ordinal: i + 1, 659 Value: v, 660 } 661 } 662 return c.exec(context.Background(), query, list) 663} 664 665func (c *SQLiteConn) exec(ctx context.Context, query string, args []namedValue) (driver.Result, error) { 666 start := 0 667 for { 668 s, err := c.prepare(ctx, query) 669 if err != nil { 670 return nil, err 671 } 672 var res driver.Result 673 if s.(*SQLiteStmt).s != nil { 674 na := s.NumInput() 675 if len(args) < na { 676 s.Close() 677 return nil, fmt.Errorf("not enough args to execute query: want %d got %d", na, len(args)) 678 } 679 for i := 0; i < na; i++ { 680 args[i].Ordinal -= start 681 } 682 res, err = s.(*SQLiteStmt).exec(ctx, args[:na]) 683 if err != nil && err != driver.ErrSkip { 684 s.Close() 685 return nil, err 686 } 687 args = args[na:] 688 start += na 689 } 690 tail := s.(*SQLiteStmt).t 691 s.Close() 692 if tail == "" { 693 return res, nil 694 } 695 query = tail 696 } 697} 698 699type namedValue struct { 700 Name string 701 Ordinal int 702 Value driver.Value 703} 704 705// Query implements Queryer. 706func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error) { 707 list := make([]namedValue, len(args)) 708 for i, v := range args { 709 list[i] = namedValue{ 710 Ordinal: i + 1, 711 Value: v, 712 } 713 } 714 return c.query(context.Background(), query, list) 715} 716 717func (c *SQLiteConn) query(ctx context.Context, query string, args []namedValue) (driver.Rows, error) { 718 start := 0 719 for { 720 s, err := c.prepare(ctx, query) 721 if err != nil { 722 return nil, err 723 } 724 s.(*SQLiteStmt).cls = true 725 na := s.NumInput() 726 if len(args) < na { 727 return nil, fmt.Errorf("not enough args to execute query: want %d got %d", na, len(args)) 728 } 729 for i := 0; i < na; i++ { 730 args[i].Ordinal -= start 731 } 732 rows, err := s.(*SQLiteStmt).query(ctx, args[:na]) 733 if err != nil && err != driver.ErrSkip { 734 s.Close() 735 return rows, err 736 } 737 args = args[na:] 738 start += na 739 tail := s.(*SQLiteStmt).t 740 if tail == "" { 741 return rows, nil 742 } 743 rows.Close() 744 s.Close() 745 query = tail 746 } 747} 748 749// Begin transaction. 750func (c *SQLiteConn) Begin() (driver.Tx, error) { 751 return c.begin(context.Background()) 752} 753 754func (c *SQLiteConn) begin(ctx context.Context) (driver.Tx, error) { 755 if _, err := c.exec(ctx, c.txlock, nil); err != nil { 756 return nil, err 757 } 758 return &SQLiteTx{c}, nil 759} 760 761func errorString(err Error) string { 762 return C.GoString(C.sqlite3_errstr(C.int(err.Code))) 763} 764 765// Open database and return a new connection. 766// You can specify a DSN string using a URI as the filename. 767// test.db 768// file:test.db?cache=shared&mode=memory 769// :memory: 770// file::memory: 771// go-sqlite3 adds the following query parameters to those used by SQLite: 772// _loc=XXX 773// Specify location of time format. It's possible to specify "auto". 774// _busy_timeout=XXX 775// Specify value for sqlite3_busy_timeout. 776// _txlock=XXX 777// Specify locking behavior for transactions. XXX can be "immediate", 778// "deferred", "exclusive". 779// _foreign_keys=X 780// Enable or disable enforcement of foreign keys. X can be 1 or 0. 781// _recursive_triggers=X 782// Enable or disable recursive triggers. X can be 1 or 0. 783func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { 784 if C.sqlite3_threadsafe() == 0 { 785 return nil, errors.New("sqlite library was not compiled for thread-safe operation") 786 } 787 788 var loc *time.Location 789 txlock := "BEGIN" 790 busyTimeout := 5000 791 foreignKeys := -1 792 recursiveTriggers := -1 793 pos := strings.IndexRune(dsn, '?') 794 if pos >= 1 { 795 params, err := url.ParseQuery(dsn[pos+1:]) 796 if err != nil { 797 return nil, err 798 } 799 800 // _loc 801 if val := params.Get("_loc"); val != "" { 802 if val == "auto" { 803 loc = time.Local 804 } else { 805 loc, err = time.LoadLocation(val) 806 if err != nil { 807 return nil, fmt.Errorf("Invalid _loc: %v: %v", val, err) 808 } 809 } 810 } 811 812 // _busy_timeout 813 if val := params.Get("_busy_timeout"); val != "" { 814 iv, err := strconv.ParseInt(val, 10, 64) 815 if err != nil { 816 return nil, fmt.Errorf("Invalid _busy_timeout: %v: %v", val, err) 817 } 818 busyTimeout = int(iv) 819 } 820 821 // _txlock 822 if val := params.Get("_txlock"); val != "" { 823 switch val { 824 case "immediate": 825 txlock = "BEGIN IMMEDIATE" 826 case "exclusive": 827 txlock = "BEGIN EXCLUSIVE" 828 case "deferred": 829 txlock = "BEGIN" 830 default: 831 return nil, fmt.Errorf("Invalid _txlock: %v", val) 832 } 833 } 834 835 // _foreign_keys 836 if val := params.Get("_foreign_keys"); val != "" { 837 switch val { 838 case "1": 839 foreignKeys = 1 840 case "0": 841 foreignKeys = 0 842 default: 843 return nil, fmt.Errorf("Invalid _foreign_keys: %v", val) 844 } 845 } 846 847 // _recursive_triggers 848 if val := params.Get("_recursive_triggers"); val != "" { 849 switch val { 850 case "1": 851 recursiveTriggers = 1 852 case "0": 853 recursiveTriggers = 0 854 default: 855 return nil, fmt.Errorf("Invalid _recursive_triggers: %v", val) 856 } 857 } 858 859 if !strings.HasPrefix(dsn, "file:") { 860 dsn = dsn[:pos] 861 } 862 } 863 864 var db *C.sqlite3 865 name := C.CString(dsn) 866 defer C.free(unsafe.Pointer(name)) 867 rv := C._sqlite3_open_v2(name, &db, 868 C.SQLITE_OPEN_FULLMUTEX| 869 C.SQLITE_OPEN_READWRITE| 870 C.SQLITE_OPEN_CREATE, 871 nil) 872 if rv != 0 { 873 return nil, Error{Code: ErrNo(rv)} 874 } 875 if db == nil { 876 return nil, errors.New("sqlite succeeded without returning a database") 877 } 878 879 rv = C.sqlite3_busy_timeout(db, C.int(busyTimeout)) 880 if rv != C.SQLITE_OK { 881 C.sqlite3_close_v2(db) 882 return nil, Error{Code: ErrNo(rv)} 883 } 884 885 exec := func(s string) error { 886 cs := C.CString(s) 887 rv := C.sqlite3_exec(db, cs, nil, nil, nil) 888 C.free(unsafe.Pointer(cs)) 889 if rv != C.SQLITE_OK { 890 return lastError(db) 891 } 892 return nil 893 } 894 if foreignKeys == 0 { 895 if err := exec("PRAGMA foreign_keys = OFF;"); err != nil { 896 C.sqlite3_close_v2(db) 897 return nil, err 898 } 899 } else if foreignKeys == 1 { 900 if err := exec("PRAGMA foreign_keys = ON;"); err != nil { 901 C.sqlite3_close_v2(db) 902 return nil, err 903 } 904 } 905 if recursiveTriggers == 0 { 906 if err := exec("PRAGMA recursive_triggers = OFF;"); err != nil { 907 C.sqlite3_close_v2(db) 908 return nil, err 909 } 910 } else if recursiveTriggers == 1 { 911 if err := exec("PRAGMA recursive_triggers = ON;"); err != nil { 912 C.sqlite3_close_v2(db) 913 return nil, err 914 } 915 } 916 917 conn := &SQLiteConn{db: db, loc: loc, txlock: txlock} 918 919 if len(d.Extensions) > 0 { 920 if err := conn.loadExtensions(d.Extensions); err != nil { 921 conn.Close() 922 return nil, err 923 } 924 } 925 926 if d.ConnectHook != nil { 927 if err := d.ConnectHook(conn); err != nil { 928 conn.Close() 929 return nil, err 930 } 931 } 932 runtime.SetFinalizer(conn, (*SQLiteConn).Close) 933 return conn, nil 934} 935 936// Close the connection. 937func (c *SQLiteConn) Close() error { 938 rv := C.sqlite3_close_v2(c.db) 939 if rv != C.SQLITE_OK { 940 return c.lastError() 941 } 942 deleteHandles(c) 943 c.mu.Lock() 944 c.db = nil 945 c.mu.Unlock() 946 runtime.SetFinalizer(c, nil) 947 return nil 948} 949 950func (c *SQLiteConn) dbConnOpen() bool { 951 if c == nil { 952 return false 953 } 954 c.mu.Lock() 955 defer c.mu.Unlock() 956 return c.db != nil 957} 958 959// Prepare the query string. Return a new statement. 960func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error) { 961 return c.prepare(context.Background(), query) 962} 963 964func (c *SQLiteConn) prepare(ctx context.Context, query string) (driver.Stmt, error) { 965 pquery := C.CString(query) 966 defer C.free(unsafe.Pointer(pquery)) 967 var s *C.sqlite3_stmt 968 var tail *C.char 969 rv := C.sqlite3_prepare_v2(c.db, pquery, -1, &s, &tail) 970 if rv != C.SQLITE_OK { 971 return nil, c.lastError() 972 } 973 var t string 974 if tail != nil && *tail != '\000' { 975 t = strings.TrimSpace(C.GoString(tail)) 976 } 977 ss := &SQLiteStmt{c: c, s: s, t: t} 978 runtime.SetFinalizer(ss, (*SQLiteStmt).Close) 979 return ss, nil 980} 981 982// Run-Time Limit Categories. 983// See: http://www.sqlite.org/c3ref/c_limit_attached.html 984const ( 985 SQLITE_LIMIT_LENGTH = C.SQLITE_LIMIT_LENGTH 986 SQLITE_LIMIT_SQL_LENGTH = C.SQLITE_LIMIT_SQL_LENGTH 987 SQLITE_LIMIT_COLUMN = C.SQLITE_LIMIT_COLUMN 988 SQLITE_LIMIT_EXPR_DEPTH = C.SQLITE_LIMIT_EXPR_DEPTH 989 SQLITE_LIMIT_COMPOUND_SELECT = C.SQLITE_LIMIT_COMPOUND_SELECT 990 SQLITE_LIMIT_VDBE_OP = C.SQLITE_LIMIT_VDBE_OP 991 SQLITE_LIMIT_FUNCTION_ARG = C.SQLITE_LIMIT_FUNCTION_ARG 992 SQLITE_LIMIT_ATTACHED = C.SQLITE_LIMIT_ATTACHED 993 SQLITE_LIMIT_LIKE_PATTERN_LENGTH = C.SQLITE_LIMIT_LIKE_PATTERN_LENGTH 994 SQLITE_LIMIT_VARIABLE_NUMBER = C.SQLITE_LIMIT_VARIABLE_NUMBER 995 SQLITE_LIMIT_TRIGGER_DEPTH = C.SQLITE_LIMIT_TRIGGER_DEPTH 996 SQLITE_LIMIT_WORKER_THREADS = C.SQLITE_LIMIT_WORKER_THREADS 997) 998 999// GetLimit returns the current value of a run-time limit. 1000// See: sqlite3_limit, http://www.sqlite.org/c3ref/limit.html 1001func (c *SQLiteConn) GetLimit(id int) int { 1002 return int(C._sqlite3_limit(c.db, C.int(id), -1)) 1003} 1004 1005// SetLimit changes the value of a run-time limits. 1006// Then this method returns the prior value of the limit. 1007// See: sqlite3_limit, http://www.sqlite.org/c3ref/limit.html 1008func (c *SQLiteConn) SetLimit(id int, newVal int) int { 1009 return int(C._sqlite3_limit(c.db, C.int(id), C.int(newVal))) 1010} 1011 1012// Close the statement. 1013func (s *SQLiteStmt) Close() error { 1014 s.mu.Lock() 1015 defer s.mu.Unlock() 1016 if s.closed { 1017 return nil 1018 } 1019 s.closed = true 1020 if !s.c.dbConnOpen() { 1021 return errors.New("sqlite statement with already closed database connection") 1022 } 1023 rv := C.sqlite3_finalize(s.s) 1024 s.s = nil 1025 if rv != C.SQLITE_OK { 1026 return s.c.lastError() 1027 } 1028 runtime.SetFinalizer(s, nil) 1029 return nil 1030} 1031 1032// NumInput return a number of parameters. 1033func (s *SQLiteStmt) NumInput() int { 1034 return int(C.sqlite3_bind_parameter_count(s.s)) 1035} 1036 1037type bindArg struct { 1038 n int 1039 v driver.Value 1040} 1041 1042var placeHolder = []byte{0} 1043 1044func (s *SQLiteStmt) bind(args []namedValue) error { 1045 rv := C.sqlite3_reset(s.s) 1046 if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE { 1047 return s.c.lastError() 1048 } 1049 1050 for i, v := range args { 1051 if v.Name != "" { 1052 cname := C.CString(":" + v.Name) 1053 args[i].Ordinal = int(C.sqlite3_bind_parameter_index(s.s, cname)) 1054 C.free(unsafe.Pointer(cname)) 1055 } 1056 } 1057 1058 for _, arg := range args { 1059 n := C.int(arg.Ordinal) 1060 switch v := arg.Value.(type) { 1061 case nil: 1062 rv = C.sqlite3_bind_null(s.s, n) 1063 case string: 1064 if len(v) == 0 { 1065 rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&placeHolder[0])), C.int(0)) 1066 } else { 1067 b := []byte(v) 1068 rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b))) 1069 } 1070 case int64: 1071 rv = C.sqlite3_bind_int64(s.s, n, C.sqlite3_int64(v)) 1072 case bool: 1073 if bool(v) { 1074 rv = C.sqlite3_bind_int(s.s, n, 1) 1075 } else { 1076 rv = C.sqlite3_bind_int(s.s, n, 0) 1077 } 1078 case float64: 1079 rv = C.sqlite3_bind_double(s.s, n, C.double(v)) 1080 case []byte: 1081 ln := len(v) 1082 if ln == 0 { 1083 v = placeHolder 1084 } 1085 rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(&v[0]), C.int(ln)) 1086 case time.Time: 1087 b := []byte(v.Format(SQLiteTimestampFormats[0])) 1088 rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b))) 1089 } 1090 if rv != C.SQLITE_OK { 1091 return s.c.lastError() 1092 } 1093 } 1094 return nil 1095} 1096 1097// Query the statement with arguments. Return records. 1098func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error) { 1099 list := make([]namedValue, len(args)) 1100 for i, v := range args { 1101 list[i] = namedValue{ 1102 Ordinal: i + 1, 1103 Value: v, 1104 } 1105 } 1106 return s.query(context.Background(), list) 1107} 1108 1109func (s *SQLiteStmt) query(ctx context.Context, args []namedValue) (driver.Rows, error) { 1110 if err := s.bind(args); err != nil { 1111 return nil, err 1112 } 1113 1114 rows := &SQLiteRows{ 1115 s: s, 1116 nc: int(C.sqlite3_column_count(s.s)), 1117 cols: nil, 1118 decltype: nil, 1119 cls: s.cls, 1120 closed: false, 1121 done: make(chan struct{}), 1122 } 1123 1124 go func(db *C.sqlite3) { 1125 select { 1126 case <-ctx.Done(): 1127 select { 1128 case <-rows.done: 1129 default: 1130 C.sqlite3_interrupt(db) 1131 rows.Close() 1132 } 1133 case <-rows.done: 1134 } 1135 }(s.c.db) 1136 1137 return rows, nil 1138} 1139 1140// LastInsertId teturn last inserted ID. 1141func (r *SQLiteResult) LastInsertId() (int64, error) { 1142 return r.id, nil 1143} 1144 1145// RowsAffected return how many rows affected. 1146func (r *SQLiteResult) RowsAffected() (int64, error) { 1147 return r.changes, nil 1148} 1149 1150// Exec execute the statement with arguments. Return result object. 1151func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error) { 1152 list := make([]namedValue, len(args)) 1153 for i, v := range args { 1154 list[i] = namedValue{ 1155 Ordinal: i + 1, 1156 Value: v, 1157 } 1158 } 1159 return s.exec(context.Background(), list) 1160} 1161 1162func (s *SQLiteStmt) exec(ctx context.Context, args []namedValue) (driver.Result, error) { 1163 if err := s.bind(args); err != nil { 1164 C.sqlite3_reset(s.s) 1165 C.sqlite3_clear_bindings(s.s) 1166 return nil, err 1167 } 1168 1169 done := make(chan struct{}) 1170 defer close(done) 1171 go func(db *C.sqlite3) { 1172 select { 1173 case <-done: 1174 case <-ctx.Done(): 1175 select { 1176 case <-done: 1177 default: 1178 C.sqlite3_interrupt(db) 1179 } 1180 } 1181 }(s.c.db) 1182 1183 var rowid, changes C.longlong 1184 rv := C._sqlite3_step(s.s, &rowid, &changes) 1185 if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE { 1186 err := s.c.lastError() 1187 C.sqlite3_reset(s.s) 1188 C.sqlite3_clear_bindings(s.s) 1189 return nil, err 1190 } 1191 1192 return &SQLiteResult{id: int64(rowid), changes: int64(changes)}, nil 1193} 1194 1195// Close the rows. 1196func (rc *SQLiteRows) Close() error { 1197 rc.s.mu.Lock() 1198 if rc.s.closed || rc.closed { 1199 rc.s.mu.Unlock() 1200 return nil 1201 } 1202 rc.closed = true 1203 if rc.done != nil { 1204 close(rc.done) 1205 } 1206 if rc.cls { 1207 rc.s.mu.Unlock() 1208 return rc.s.Close() 1209 } 1210 rv := C.sqlite3_reset(rc.s.s) 1211 if rv != C.SQLITE_OK { 1212 rc.s.mu.Unlock() 1213 return rc.s.c.lastError() 1214 } 1215 rc.s.mu.Unlock() 1216 return nil 1217} 1218 1219// Columns return column names. 1220func (rc *SQLiteRows) Columns() []string { 1221 rc.s.mu.Lock() 1222 defer rc.s.mu.Unlock() 1223 if rc.s.s != nil && rc.nc != len(rc.cols) { 1224 rc.cols = make([]string, rc.nc) 1225 for i := 0; i < rc.nc; i++ { 1226 rc.cols[i] = C.GoString(C.sqlite3_column_name(rc.s.s, C.int(i))) 1227 } 1228 } 1229 return rc.cols 1230} 1231 1232func (rc *SQLiteRows) declTypes() []string { 1233 if rc.s.s != nil && rc.decltype == nil { 1234 rc.decltype = make([]string, rc.nc) 1235 for i := 0; i < rc.nc; i++ { 1236 rc.decltype[i] = strings.ToLower(C.GoString(C.sqlite3_column_decltype(rc.s.s, C.int(i)))) 1237 } 1238 } 1239 return rc.decltype 1240} 1241 1242// DeclTypes return column types. 1243func (rc *SQLiteRows) DeclTypes() []string { 1244 rc.s.mu.Lock() 1245 defer rc.s.mu.Unlock() 1246 return rc.declTypes() 1247} 1248 1249// Next move cursor to next. 1250func (rc *SQLiteRows) Next(dest []driver.Value) error { 1251 if rc.s.closed { 1252 return io.EOF 1253 } 1254 rc.s.mu.Lock() 1255 defer rc.s.mu.Unlock() 1256 rv := C.sqlite3_step(rc.s.s) 1257 if rv == C.SQLITE_DONE { 1258 return io.EOF 1259 } 1260 if rv != C.SQLITE_ROW { 1261 rv = C.sqlite3_reset(rc.s.s) 1262 if rv != C.SQLITE_OK { 1263 return rc.s.c.lastError() 1264 } 1265 return nil 1266 } 1267 1268 rc.declTypes() 1269 1270 for i := range dest { 1271 switch C.sqlite3_column_type(rc.s.s, C.int(i)) { 1272 case C.SQLITE_INTEGER: 1273 val := int64(C.sqlite3_column_int64(rc.s.s, C.int(i))) 1274 switch rc.decltype[i] { 1275 case "timestamp", "datetime", "date": 1276 var t time.Time 1277 // Assume a millisecond unix timestamp if it's 13 digits -- too 1278 // large to be a reasonable timestamp in seconds. 1279 if val > 1e12 || val < -1e12 { 1280 val *= int64(time.Millisecond) // convert ms to nsec 1281 t = time.Unix(0, val) 1282 } else { 1283 t = time.Unix(val, 0) 1284 } 1285 t = t.UTC() 1286 if rc.s.c.loc != nil { 1287 t = t.In(rc.s.c.loc) 1288 } 1289 dest[i] = t 1290 case "boolean": 1291 dest[i] = val > 0 1292 default: 1293 dest[i] = val 1294 } 1295 case C.SQLITE_FLOAT: 1296 dest[i] = float64(C.sqlite3_column_double(rc.s.s, C.int(i))) 1297 case C.SQLITE_BLOB: 1298 p := C.sqlite3_column_blob(rc.s.s, C.int(i)) 1299 if p == nil { 1300 dest[i] = nil 1301 continue 1302 } 1303 n := int(C.sqlite3_column_bytes(rc.s.s, C.int(i))) 1304 switch dest[i].(type) { 1305 case sql.RawBytes: 1306 dest[i] = (*[1 << 30]byte)(unsafe.Pointer(p))[0:n] 1307 default: 1308 slice := make([]byte, n) 1309 copy(slice[:], (*[1 << 30]byte)(unsafe.Pointer(p))[0:n]) 1310 dest[i] = slice 1311 } 1312 case C.SQLITE_NULL: 1313 dest[i] = nil 1314 case C.SQLITE_TEXT: 1315 var err error 1316 var timeVal time.Time 1317 1318 n := int(C.sqlite3_column_bytes(rc.s.s, C.int(i))) 1319 s := C.GoStringN((*C.char)(unsafe.Pointer(C.sqlite3_column_text(rc.s.s, C.int(i)))), C.int(n)) 1320 1321 switch rc.decltype[i] { 1322 case "timestamp", "datetime", "date": 1323 var t time.Time 1324 s = strings.TrimSuffix(s, "Z") 1325 for _, format := range SQLiteTimestampFormats { 1326 if timeVal, err = time.ParseInLocation(format, s, time.UTC); err == nil { 1327 t = timeVal 1328 break 1329 } 1330 } 1331 if err != nil { 1332 // The column is a time value, so return the zero time on parse failure. 1333 t = time.Time{} 1334 } 1335 if rc.s.c.loc != nil { 1336 t = t.In(rc.s.c.loc) 1337 } 1338 dest[i] = t 1339 default: 1340 dest[i] = []byte(s) 1341 } 1342 1343 } 1344 } 1345 return nil 1346} 1347