sqlite3.go 23 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962
  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. package sqlite3
  6. /*
  7. #cgo CFLAGS: -std=gnu99
  8. #cgo CFLAGS: -DSQLITE_ENABLE_RTREE -DSQLITE_THREADSAFE
  9. #cgo CFLAGS: -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_FTS4_UNICODE61
  10. #cgo CFLAGS: -DSQLITE_TRACE_SIZE_LIMIT=15
  11. #cgo CFLAGS: -Wno-deprecated-declarations
  12. #ifndef USE_LIBSQLITE3
  13. #include <sqlite3-binding.h>
  14. #else
  15. #include <sqlite3.h>
  16. #endif
  17. #include <stdlib.h>
  18. #include <string.h>
  19. #ifdef __CYGWIN__
  20. # include <errno.h>
  21. #endif
  22. #ifndef SQLITE_OPEN_READWRITE
  23. # define SQLITE_OPEN_READWRITE 0
  24. #endif
  25. #ifndef SQLITE_OPEN_FULLMUTEX
  26. # define SQLITE_OPEN_FULLMUTEX 0
  27. #endif
  28. #ifndef SQLITE_DETERMINISTIC
  29. # define SQLITE_DETERMINISTIC 0
  30. #endif
  31. static int
  32. _sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs) {
  33. #ifdef SQLITE_OPEN_URI
  34. return sqlite3_open_v2(filename, ppDb, flags | SQLITE_OPEN_URI, zVfs);
  35. #else
  36. return sqlite3_open_v2(filename, ppDb, flags, zVfs);
  37. #endif
  38. }
  39. static int
  40. _sqlite3_bind_text(sqlite3_stmt *stmt, int n, char *p, int np) {
  41. return sqlite3_bind_text(stmt, n, p, np, SQLITE_TRANSIENT);
  42. }
  43. static int
  44. _sqlite3_bind_blob(sqlite3_stmt *stmt, int n, void *p, int np) {
  45. return sqlite3_bind_blob(stmt, n, p, np, SQLITE_TRANSIENT);
  46. }
  47. #include <stdio.h>
  48. #include <stdint.h>
  49. static int
  50. _sqlite3_exec(sqlite3* db, const char* pcmd, long long* rowid, long long* changes)
  51. {
  52. int rv = sqlite3_exec(db, pcmd, 0, 0, 0);
  53. *rowid = (long long) sqlite3_last_insert_rowid(db);
  54. *changes = (long long) sqlite3_changes(db);
  55. return rv;
  56. }
  57. static int
  58. _sqlite3_step(sqlite3_stmt* stmt, long long* rowid, long long* changes)
  59. {
  60. int rv = sqlite3_step(stmt);
  61. sqlite3* db = sqlite3_db_handle(stmt);
  62. *rowid = (long long) sqlite3_last_insert_rowid(db);
  63. *changes = (long long) sqlite3_changes(db);
  64. return rv;
  65. }
  66. void _sqlite3_result_text(sqlite3_context* ctx, const char* s) {
  67. sqlite3_result_text(ctx, s, -1, &free);
  68. }
  69. void _sqlite3_result_blob(sqlite3_context* ctx, const void* b, int l) {
  70. sqlite3_result_blob(ctx, b, l, SQLITE_TRANSIENT);
  71. }
  72. int _sqlite3_create_function(
  73. sqlite3 *db,
  74. const char *zFunctionName,
  75. int nArg,
  76. int eTextRep,
  77. uintptr_t pApp,
  78. void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  79. void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  80. void (*xFinal)(sqlite3_context*)
  81. ) {
  82. return sqlite3_create_function(db, zFunctionName, nArg, eTextRep, (void*) pApp, xFunc, xStep, xFinal);
  83. }
  84. void callbackTrampoline(sqlite3_context*, int, sqlite3_value**);
  85. */
  86. import "C"
  87. import (
  88. "database/sql"
  89. "database/sql/driver"
  90. "errors"
  91. "fmt"
  92. "io"
  93. "net/url"
  94. "reflect"
  95. "runtime"
  96. "strconv"
  97. "strings"
  98. "time"
  99. "unsafe"
  100. "golang.org/x/net/context"
  101. )
  102. // SQLiteTimestampFormats is timestamp formats understood by both this module
  103. // and SQLite. The first format in the slice will be used when saving time
  104. // values into the database. When parsing a string from a timestamp or datetime
  105. // column, the formats are tried in order.
  106. var SQLiteTimestampFormats = []string{
  107. // By default, store timestamps with whatever timezone they come with.
  108. // When parsed, they will be returned with the same timezone.
  109. "2006-01-02 15:04:05.999999999-07:00",
  110. "2006-01-02T15:04:05.999999999-07:00",
  111. "2006-01-02 15:04:05.999999999",
  112. "2006-01-02T15:04:05.999999999",
  113. "2006-01-02 15:04:05",
  114. "2006-01-02T15:04:05",
  115. "2006-01-02 15:04",
  116. "2006-01-02T15:04",
  117. "2006-01-02",
  118. }
  119. func init() {
  120. sql.Register("sqlite3", &SQLiteDriver{})
  121. }
  122. // Version returns SQLite library version information.
  123. func Version() (libVersion string, libVersionNumber int, sourceID string) {
  124. libVersion = C.GoString(C.sqlite3_libversion())
  125. libVersionNumber = int(C.sqlite3_libversion_number())
  126. sourceID = C.GoString(C.sqlite3_sourceid())
  127. return libVersion, libVersionNumber, sourceID
  128. }
  129. // SQLiteDriver implement sql.Driver.
  130. type SQLiteDriver struct {
  131. Extensions []string
  132. ConnectHook func(*SQLiteConn) error
  133. }
  134. // SQLiteConn implement sql.Conn.
  135. type SQLiteConn struct {
  136. db *C.sqlite3
  137. loc *time.Location
  138. txlock string
  139. funcs []*functionInfo
  140. aggregators []*aggInfo
  141. }
  142. // SQLiteTx implemen sql.Tx.
  143. type SQLiteTx struct {
  144. c *SQLiteConn
  145. }
  146. // SQLiteStmt implement sql.Stmt.
  147. type SQLiteStmt struct {
  148. c *SQLiteConn
  149. s *C.sqlite3_stmt
  150. t string
  151. closed bool
  152. cls bool
  153. }
  154. // SQLiteResult implement sql.Result.
  155. type SQLiteResult struct {
  156. id int64
  157. changes int64
  158. }
  159. // SQLiteRows implement sql.Rows.
  160. type SQLiteRows struct {
  161. s *SQLiteStmt
  162. nc int
  163. cols []string
  164. decltype []string
  165. cls bool
  166. done chan struct{}
  167. }
  168. type functionInfo struct {
  169. f reflect.Value
  170. argConverters []callbackArgConverter
  171. variadicConverter callbackArgConverter
  172. retConverter callbackRetConverter
  173. }
  174. func (fi *functionInfo) Call(ctx *C.sqlite3_context, argv []*C.sqlite3_value) {
  175. args, err := callbackConvertArgs(argv, fi.argConverters, fi.variadicConverter)
  176. if err != nil {
  177. callbackError(ctx, err)
  178. return
  179. }
  180. ret := fi.f.Call(args)
  181. if len(ret) == 2 && ret[1].Interface() != nil {
  182. callbackError(ctx, ret[1].Interface().(error))
  183. return
  184. }
  185. err = fi.retConverter(ctx, ret[0])
  186. if err != nil {
  187. callbackError(ctx, err)
  188. return
  189. }
  190. }
  191. type aggInfo struct {
  192. constructor reflect.Value
  193. // Active aggregator objects for aggregations in flight. The
  194. // aggregators are indexed by a counter stored in the aggregation
  195. // user data space provided by sqlite.
  196. active map[int64]reflect.Value
  197. next int64
  198. stepArgConverters []callbackArgConverter
  199. stepVariadicConverter callbackArgConverter
  200. doneRetConverter callbackRetConverter
  201. }
  202. func (ai *aggInfo) agg(ctx *C.sqlite3_context) (int64, reflect.Value, error) {
  203. aggIdx := (*int64)(C.sqlite3_aggregate_context(ctx, C.int(8)))
  204. if *aggIdx == 0 {
  205. *aggIdx = ai.next
  206. ret := ai.constructor.Call(nil)
  207. if len(ret) == 2 && ret[1].Interface() != nil {
  208. return 0, reflect.Value{}, ret[1].Interface().(error)
  209. }
  210. if ret[0].IsNil() {
  211. return 0, reflect.Value{}, errors.New("aggregator constructor returned nil state")
  212. }
  213. ai.next++
  214. ai.active[*aggIdx] = ret[0]
  215. }
  216. return *aggIdx, ai.active[*aggIdx], nil
  217. }
  218. func (ai *aggInfo) Step(ctx *C.sqlite3_context, argv []*C.sqlite3_value) {
  219. _, agg, err := ai.agg(ctx)
  220. if err != nil {
  221. callbackError(ctx, err)
  222. return
  223. }
  224. args, err := callbackConvertArgs(argv, ai.stepArgConverters, ai.stepVariadicConverter)
  225. if err != nil {
  226. callbackError(ctx, err)
  227. return
  228. }
  229. ret := agg.MethodByName("Step").Call(args)
  230. if len(ret) == 1 && ret[0].Interface() != nil {
  231. callbackError(ctx, ret[0].Interface().(error))
  232. return
  233. }
  234. }
  235. func (ai *aggInfo) Done(ctx *C.sqlite3_context) {
  236. idx, agg, err := ai.agg(ctx)
  237. if err != nil {
  238. callbackError(ctx, err)
  239. return
  240. }
  241. defer func() { delete(ai.active, idx) }()
  242. ret := agg.MethodByName("Done").Call(nil)
  243. if len(ret) == 2 && ret[1].Interface() != nil {
  244. callbackError(ctx, ret[1].Interface().(error))
  245. return
  246. }
  247. err = ai.doneRetConverter(ctx, ret[0])
  248. if err != nil {
  249. callbackError(ctx, err)
  250. return
  251. }
  252. }
  253. // Commit transaction.
  254. func (tx *SQLiteTx) Commit() error {
  255. _, err := tx.c.exec(context.Background(), "COMMIT", nil)
  256. if err != nil && err.(Error).Code == C.SQLITE_BUSY {
  257. // sqlite3 will leave the transaction open in this scenario.
  258. // However, database/sql considers the transaction complete once we
  259. // return from Commit() - we must clean up to honour its semantics.
  260. tx.c.exec(context.Background(), "ROLLBACK", nil)
  261. }
  262. return err
  263. }
  264. // Rollback transaction.
  265. func (tx *SQLiteTx) Rollback() error {
  266. _, err := tx.c.exec(context.Background(), "ROLLBACK", nil)
  267. return err
  268. }
  269. // RegisterFunc makes a Go function available as a SQLite function.
  270. //
  271. // The Go function can have arguments of the following types: any
  272. // numeric type except complex, bool, []byte, string and
  273. // interface{}. interface{} arguments are given the direct translation
  274. // of the SQLite data type: int64 for INTEGER, float64 for FLOAT,
  275. // []byte for BLOB, string for TEXT.
  276. //
  277. // The function can additionally be variadic, as long as the type of
  278. // the variadic argument is one of the above.
  279. //
  280. // If pure is true. SQLite will assume that the function's return
  281. // value depends only on its inputs, and make more aggressive
  282. // optimizations in its queries.
  283. //
  284. // See _example/go_custom_funcs for a detailed example.
  285. func (c *SQLiteConn) RegisterFunc(name string, impl interface{}, pure bool) error {
  286. var fi functionInfo
  287. fi.f = reflect.ValueOf(impl)
  288. t := fi.f.Type()
  289. if t.Kind() != reflect.Func {
  290. return errors.New("Non-function passed to RegisterFunc")
  291. }
  292. if t.NumOut() != 1 && t.NumOut() != 2 {
  293. return errors.New("SQLite functions must return 1 or 2 values")
  294. }
  295. if t.NumOut() == 2 && !t.Out(1).Implements(reflect.TypeOf((*error)(nil)).Elem()) {
  296. return errors.New("Second return value of SQLite function must be error")
  297. }
  298. numArgs := t.NumIn()
  299. if t.IsVariadic() {
  300. numArgs--
  301. }
  302. for i := 0; i < numArgs; i++ {
  303. conv, err := callbackArg(t.In(i))
  304. if err != nil {
  305. return err
  306. }
  307. fi.argConverters = append(fi.argConverters, conv)
  308. }
  309. if t.IsVariadic() {
  310. conv, err := callbackArg(t.In(numArgs).Elem())
  311. if err != nil {
  312. return err
  313. }
  314. fi.variadicConverter = conv
  315. // Pass -1 to sqlite so that it allows any number of
  316. // arguments. The call helper verifies that the minimum number
  317. // of arguments is present for variadic functions.
  318. numArgs = -1
  319. }
  320. conv, err := callbackRet(t.Out(0))
  321. if err != nil {
  322. return err
  323. }
  324. fi.retConverter = conv
  325. // fi must outlast the database connection, or we'll have dangling pointers.
  326. c.funcs = append(c.funcs, &fi)
  327. cname := C.CString(name)
  328. defer C.free(unsafe.Pointer(cname))
  329. opts := C.SQLITE_UTF8
  330. if pure {
  331. opts |= C.SQLITE_DETERMINISTIC
  332. }
  333. rv := sqlite3_create_function(c.db, cname, C.int(numArgs), C.int(opts), newHandle(c, &fi), C.callbackTrampoline, nil, nil)
  334. if rv != C.SQLITE_OK {
  335. return c.lastError()
  336. }
  337. return nil
  338. }
  339. func sqlite3_create_function(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 {
  340. 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)))
  341. }
  342. // AutoCommit return which currently auto commit or not.
  343. func (c *SQLiteConn) AutoCommit() bool {
  344. return int(C.sqlite3_get_autocommit(c.db)) != 0
  345. }
  346. func (c *SQLiteConn) lastError() Error {
  347. return Error{
  348. Code: ErrNo(C.sqlite3_errcode(c.db)),
  349. ExtendedCode: ErrNoExtended(C.sqlite3_extended_errcode(c.db)),
  350. err: C.GoString(C.sqlite3_errmsg(c.db)),
  351. }
  352. }
  353. // Exec implements Execer.
  354. func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error) {
  355. list := make([]namedValue, len(args))
  356. for i, v := range args {
  357. list[i] = namedValue{
  358. Ordinal: i + 1,
  359. Value: v,
  360. }
  361. }
  362. return c.exec(context.Background(), query, list)
  363. }
  364. func (c *SQLiteConn) exec(ctx context.Context, query string, args []namedValue) (driver.Result, error) {
  365. start := 0
  366. for {
  367. s, err := c.prepare(ctx, query)
  368. if err != nil {
  369. return nil, err
  370. }
  371. var res driver.Result
  372. if s.(*SQLiteStmt).s != nil {
  373. na := s.NumInput()
  374. if len(args) < na {
  375. return nil, fmt.Errorf("Not enough args to execute query. Expected %d, got %d.", na, len(args))
  376. }
  377. for i := 0; i < na; i++ {
  378. args[i].Ordinal -= start
  379. }
  380. res, err = s.(*SQLiteStmt).exec(ctx, args[:na])
  381. if err != nil && err != driver.ErrSkip {
  382. s.Close()
  383. return nil, err
  384. }
  385. args = args[na:]
  386. start += na
  387. }
  388. tail := s.(*SQLiteStmt).t
  389. s.Close()
  390. if tail == "" {
  391. return res, nil
  392. }
  393. query = tail
  394. }
  395. }
  396. type namedValue struct {
  397. Name string
  398. Ordinal int
  399. Value driver.Value
  400. }
  401. // Query implements Queryer.
  402. func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error) {
  403. list := make([]namedValue, len(args))
  404. for i, v := range args {
  405. list[i] = namedValue{
  406. Ordinal: i + 1,
  407. Value: v,
  408. }
  409. }
  410. return c.query(context.Background(), query, list)
  411. }
  412. func (c *SQLiteConn) query(ctx context.Context, query string, args []namedValue) (driver.Rows, error) {
  413. start := 0
  414. for {
  415. s, err := c.prepare(ctx, query)
  416. if err != nil {
  417. return nil, err
  418. }
  419. s.(*SQLiteStmt).cls = true
  420. na := s.NumInput()
  421. if len(args) < na {
  422. return nil, fmt.Errorf("Not enough args to execute query. Expected %d, got %d.", na, len(args))
  423. }
  424. for i := 0; i < na; i++ {
  425. args[i].Ordinal -= start
  426. }
  427. rows, err := s.(*SQLiteStmt).query(ctx, args[:na])
  428. if err != nil && err != driver.ErrSkip {
  429. s.Close()
  430. return rows, err
  431. }
  432. args = args[na:]
  433. start += na
  434. tail := s.(*SQLiteStmt).t
  435. if tail == "" {
  436. return rows, nil
  437. }
  438. rows.Close()
  439. s.Close()
  440. query = tail
  441. }
  442. }
  443. // Begin transaction.
  444. func (c *SQLiteConn) Begin() (driver.Tx, error) {
  445. return c.begin(context.Background())
  446. }
  447. func (c *SQLiteConn) begin(ctx context.Context) (driver.Tx, error) {
  448. if _, err := c.exec(ctx, c.txlock, nil); err != nil {
  449. return nil, err
  450. }
  451. return &SQLiteTx{c}, nil
  452. }
  453. func errorString(err Error) string {
  454. return C.GoString(C.sqlite3_errstr(C.int(err.Code)))
  455. }
  456. // Open database and return a new connection.
  457. // You can specify a DSN string using a URI as the filename.
  458. // test.db
  459. // file:test.db?cache=shared&mode=memory
  460. // :memory:
  461. // file::memory:
  462. // go-sqlite3 adds the following query parameters to those used by SQLite:
  463. // _loc=XXX
  464. // Specify location of time format. It's possible to specify "auto".
  465. // _busy_timeout=XXX
  466. // Specify value for sqlite3_busy_timeout.
  467. // _txlock=XXX
  468. // Specify locking behavior for transactions. XXX can be "immediate",
  469. // "deferred", "exclusive".
  470. func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) {
  471. if C.sqlite3_threadsafe() == 0 {
  472. return nil, errors.New("sqlite library was not compiled for thread-safe operation")
  473. }
  474. var loc *time.Location
  475. txlock := "BEGIN"
  476. busyTimeout := 5000
  477. pos := strings.IndexRune(dsn, '?')
  478. if pos >= 1 {
  479. params, err := url.ParseQuery(dsn[pos+1:])
  480. if err != nil {
  481. return nil, err
  482. }
  483. // _loc
  484. if val := params.Get("_loc"); val != "" {
  485. if val == "auto" {
  486. loc = time.Local
  487. } else {
  488. loc, err = time.LoadLocation(val)
  489. if err != nil {
  490. return nil, fmt.Errorf("Invalid _loc: %v: %v", val, err)
  491. }
  492. }
  493. }
  494. // _busy_timeout
  495. if val := params.Get("_busy_timeout"); val != "" {
  496. iv, err := strconv.ParseInt(val, 10, 64)
  497. if err != nil {
  498. return nil, fmt.Errorf("Invalid _busy_timeout: %v: %v", val, err)
  499. }
  500. busyTimeout = int(iv)
  501. }
  502. // _txlock
  503. if val := params.Get("_txlock"); val != "" {
  504. switch val {
  505. case "immediate":
  506. txlock = "BEGIN IMMEDIATE"
  507. case "exclusive":
  508. txlock = "BEGIN EXCLUSIVE"
  509. case "deferred":
  510. txlock = "BEGIN"
  511. default:
  512. return nil, fmt.Errorf("Invalid _txlock: %v", val)
  513. }
  514. }
  515. if !strings.HasPrefix(dsn, "file:") {
  516. dsn = dsn[:pos]
  517. }
  518. }
  519. var db *C.sqlite3
  520. name := C.CString(dsn)
  521. defer C.free(unsafe.Pointer(name))
  522. rv := C._sqlite3_open_v2(name, &db,
  523. C.SQLITE_OPEN_FULLMUTEX|
  524. C.SQLITE_OPEN_READWRITE|
  525. C.SQLITE_OPEN_CREATE,
  526. nil)
  527. if rv != 0 {
  528. return nil, Error{Code: ErrNo(rv)}
  529. }
  530. if db == nil {
  531. return nil, errors.New("sqlite succeeded without returning a database")
  532. }
  533. rv = C.sqlite3_busy_timeout(db, C.int(busyTimeout))
  534. if rv != C.SQLITE_OK {
  535. return nil, Error{Code: ErrNo(rv)}
  536. }
  537. conn := &SQLiteConn{db: db, loc: loc, txlock: txlock}
  538. if len(d.Extensions) > 0 {
  539. if err := conn.loadExtensions(d.Extensions); err != nil {
  540. return nil, err
  541. }
  542. }
  543. if d.ConnectHook != nil {
  544. if err := d.ConnectHook(conn); err != nil {
  545. return nil, err
  546. }
  547. }
  548. runtime.SetFinalizer(conn, (*SQLiteConn).Close)
  549. return conn, nil
  550. }
  551. // Close the connection.
  552. func (c *SQLiteConn) Close() error {
  553. deleteHandles(c)
  554. rv := C.sqlite3_close_v2(c.db)
  555. if rv != C.SQLITE_OK {
  556. return c.lastError()
  557. }
  558. c.db = nil
  559. runtime.SetFinalizer(c, nil)
  560. return nil
  561. }
  562. // Prepare the query string. Return a new statement.
  563. func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error) {
  564. return c.prepare(context.Background(), query)
  565. }
  566. func (c *SQLiteConn) prepare(ctx context.Context, query string) (driver.Stmt, error) {
  567. pquery := C.CString(query)
  568. defer C.free(unsafe.Pointer(pquery))
  569. var s *C.sqlite3_stmt
  570. var tail *C.char
  571. rv := C.sqlite3_prepare_v2(c.db, pquery, -1, &s, &tail)
  572. if rv != C.SQLITE_OK {
  573. return nil, c.lastError()
  574. }
  575. var t string
  576. if tail != nil && *tail != '\000' {
  577. t = strings.TrimSpace(C.GoString(tail))
  578. }
  579. ss := &SQLiteStmt{c: c, s: s, t: t}
  580. runtime.SetFinalizer(ss, (*SQLiteStmt).Close)
  581. return ss, nil
  582. }
  583. // Close the statement.
  584. func (s *SQLiteStmt) Close() error {
  585. if s.closed {
  586. return nil
  587. }
  588. s.closed = true
  589. if s.c == nil || s.c.db == nil {
  590. return errors.New("sqlite statement with already closed database connection")
  591. }
  592. rv := C.sqlite3_finalize(s.s)
  593. if rv != C.SQLITE_OK {
  594. return s.c.lastError()
  595. }
  596. runtime.SetFinalizer(s, nil)
  597. return nil
  598. }
  599. // NumInput return a number of parameters.
  600. func (s *SQLiteStmt) NumInput() int {
  601. return int(C.sqlite3_bind_parameter_count(s.s))
  602. }
  603. type bindArg struct {
  604. n int
  605. v driver.Value
  606. }
  607. func (s *SQLiteStmt) bind(args []namedValue) error {
  608. rv := C.sqlite3_reset(s.s)
  609. if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE {
  610. return s.c.lastError()
  611. }
  612. for i, v := range args {
  613. if v.Name != "" {
  614. cname := C.CString(":" + v.Name)
  615. args[i].Ordinal = int(C.sqlite3_bind_parameter_index(s.s, cname))
  616. C.free(unsafe.Pointer(cname))
  617. }
  618. }
  619. for _, arg := range args {
  620. n := C.int(arg.Ordinal)
  621. switch v := arg.Value.(type) {
  622. case nil:
  623. rv = C.sqlite3_bind_null(s.s, n)
  624. case string:
  625. if len(v) == 0 {
  626. b := []byte{0}
  627. rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(0))
  628. } else {
  629. b := []byte(v)
  630. rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
  631. }
  632. case int64:
  633. rv = C.sqlite3_bind_int64(s.s, n, C.sqlite3_int64(v))
  634. case bool:
  635. if bool(v) {
  636. rv = C.sqlite3_bind_int(s.s, n, 1)
  637. } else {
  638. rv = C.sqlite3_bind_int(s.s, n, 0)
  639. }
  640. case float64:
  641. rv = C.sqlite3_bind_double(s.s, n, C.double(v))
  642. case []byte:
  643. if len(v) == 0 {
  644. rv = C._sqlite3_bind_blob(s.s, n, nil, 0)
  645. } else {
  646. rv = C._sqlite3_bind_blob(s.s, n, unsafe.Pointer(&v[0]), C.int(len(v)))
  647. }
  648. case time.Time:
  649. b := []byte(v.Format(SQLiteTimestampFormats[0]))
  650. rv = C._sqlite3_bind_text(s.s, n, (*C.char)(unsafe.Pointer(&b[0])), C.int(len(b)))
  651. }
  652. if rv != C.SQLITE_OK {
  653. return s.c.lastError()
  654. }
  655. }
  656. return nil
  657. }
  658. // Query the statement with arguments. Return records.
  659. func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error) {
  660. list := make([]namedValue, len(args))
  661. for i, v := range args {
  662. list[i] = namedValue{
  663. Ordinal: i + 1,
  664. Value: v,
  665. }
  666. }
  667. return s.query(context.Background(), list)
  668. }
  669. func (s *SQLiteStmt) query(ctx context.Context, args []namedValue) (driver.Rows, error) {
  670. if err := s.bind(args); err != nil {
  671. return nil, err
  672. }
  673. rows := &SQLiteRows{
  674. s: s,
  675. nc: int(C.sqlite3_column_count(s.s)),
  676. cols: nil,
  677. decltype: nil,
  678. cls: s.cls,
  679. done: make(chan struct{}),
  680. }
  681. go func() {
  682. select {
  683. case <-ctx.Done():
  684. C.sqlite3_interrupt(s.c.db)
  685. rows.Close()
  686. case <-rows.done:
  687. }
  688. }()
  689. return rows, nil
  690. }
  691. // LastInsertId teturn last inserted ID.
  692. func (r *SQLiteResult) LastInsertId() (int64, error) {
  693. return r.id, nil
  694. }
  695. // RowsAffected return how many rows affected.
  696. func (r *SQLiteResult) RowsAffected() (int64, error) {
  697. return r.changes, nil
  698. }
  699. // Exec execute the statement with arguments. Return result object.
  700. func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error) {
  701. list := make([]namedValue, len(args))
  702. for i, v := range args {
  703. list[i] = namedValue{
  704. Ordinal: i + 1,
  705. Value: v,
  706. }
  707. }
  708. return s.exec(context.Background(), list)
  709. }
  710. func (s *SQLiteStmt) exec(ctx context.Context, args []namedValue) (driver.Result, error) {
  711. if err := s.bind(args); err != nil {
  712. C.sqlite3_reset(s.s)
  713. C.sqlite3_clear_bindings(s.s)
  714. return nil, err
  715. }
  716. done := make(chan struct{})
  717. defer close(done)
  718. go func() {
  719. select {
  720. case <-ctx.Done():
  721. C.sqlite3_interrupt(s.c.db)
  722. case <-done:
  723. }
  724. }()
  725. var rowid, changes C.longlong
  726. rv := C._sqlite3_step(s.s, &rowid, &changes)
  727. if rv != C.SQLITE_ROW && rv != C.SQLITE_OK && rv != C.SQLITE_DONE {
  728. err := s.c.lastError()
  729. C.sqlite3_reset(s.s)
  730. C.sqlite3_clear_bindings(s.s)
  731. return nil, err
  732. }
  733. return &SQLiteResult{id: int64(rowid), changes: int64(changes)}, nil
  734. }
  735. // Close the rows.
  736. func (rc *SQLiteRows) Close() error {
  737. if rc.s.closed {
  738. return nil
  739. }
  740. if rc.done != nil {
  741. close(rc.done)
  742. }
  743. if rc.cls {
  744. return rc.s.Close()
  745. }
  746. rv := C.sqlite3_reset(rc.s.s)
  747. if rv != C.SQLITE_OK {
  748. return rc.s.c.lastError()
  749. }
  750. return nil
  751. }
  752. // Columns return column names.
  753. func (rc *SQLiteRows) Columns() []string {
  754. if rc.nc != len(rc.cols) {
  755. rc.cols = make([]string, rc.nc)
  756. for i := 0; i < rc.nc; i++ {
  757. rc.cols[i] = C.GoString(C.sqlite3_column_name(rc.s.s, C.int(i)))
  758. }
  759. }
  760. return rc.cols
  761. }
  762. // DeclTypes return column types.
  763. func (rc *SQLiteRows) DeclTypes() []string {
  764. if rc.decltype == nil {
  765. rc.decltype = make([]string, rc.nc)
  766. for i := 0; i < rc.nc; i++ {
  767. rc.decltype[i] = strings.ToLower(C.GoString(C.sqlite3_column_decltype(rc.s.s, C.int(i))))
  768. }
  769. }
  770. return rc.decltype
  771. }
  772. // Next move cursor to next.
  773. func (rc *SQLiteRows) Next(dest []driver.Value) error {
  774. rv := C.sqlite3_step(rc.s.s)
  775. if rv == C.SQLITE_DONE {
  776. return io.EOF
  777. }
  778. if rv != C.SQLITE_ROW {
  779. rv = C.sqlite3_reset(rc.s.s)
  780. if rv != C.SQLITE_OK {
  781. return rc.s.c.lastError()
  782. }
  783. return nil
  784. }
  785. rc.DeclTypes()
  786. for i := range dest {
  787. switch C.sqlite3_column_type(rc.s.s, C.int(i)) {
  788. case C.SQLITE_INTEGER:
  789. val := int64(C.sqlite3_column_int64(rc.s.s, C.int(i)))
  790. switch rc.decltype[i] {
  791. case "timestamp", "datetime", "date":
  792. var t time.Time
  793. // Assume a millisecond unix timestamp if it's 13 digits -- too
  794. // large to be a reasonable timestamp in seconds.
  795. if val > 1e12 || val < -1e12 {
  796. val *= int64(time.Millisecond) // convert ms to nsec
  797. } else {
  798. val *= int64(time.Second) // convert sec to nsec
  799. }
  800. t = time.Unix(0, val).UTC()
  801. if rc.s.c.loc != nil {
  802. t = t.In(rc.s.c.loc)
  803. }
  804. dest[i] = t
  805. case "boolean":
  806. dest[i] = val > 0
  807. default:
  808. dest[i] = val
  809. }
  810. case C.SQLITE_FLOAT:
  811. dest[i] = float64(C.sqlite3_column_double(rc.s.s, C.int(i)))
  812. case C.SQLITE_BLOB:
  813. p := C.sqlite3_column_blob(rc.s.s, C.int(i))
  814. if p == nil {
  815. dest[i] = nil
  816. continue
  817. }
  818. n := int(C.sqlite3_column_bytes(rc.s.s, C.int(i)))
  819. switch dest[i].(type) {
  820. case sql.RawBytes:
  821. dest[i] = (*[1 << 30]byte)(unsafe.Pointer(p))[0:n]
  822. default:
  823. slice := make([]byte, n)
  824. copy(slice[:], (*[1 << 30]byte)(unsafe.Pointer(p))[0:n])
  825. dest[i] = slice
  826. }
  827. case C.SQLITE_NULL:
  828. dest[i] = nil
  829. case C.SQLITE_TEXT:
  830. var err error
  831. var timeVal time.Time
  832. n := int(C.sqlite3_column_bytes(rc.s.s, C.int(i)))
  833. s := C.GoStringN((*C.char)(unsafe.Pointer(C.sqlite3_column_text(rc.s.s, C.int(i)))), C.int(n))
  834. switch rc.decltype[i] {
  835. case "timestamp", "datetime", "date":
  836. var t time.Time
  837. s = strings.TrimSuffix(s, "Z")
  838. for _, format := range SQLiteTimestampFormats {
  839. if timeVal, err = time.ParseInLocation(format, s, time.UTC); err == nil {
  840. t = timeVal
  841. break
  842. }
  843. }
  844. if err != nil {
  845. // The column is a time value, so return the zero time on parse failure.
  846. t = time.Time{}
  847. }
  848. if rc.s.c.loc != nil {
  849. t = t.In(rc.s.c.loc)
  850. }
  851. dest[i] = t
  852. default:
  853. dest[i] = []byte(s)
  854. }
  855. }
  856. }
  857. return nil
  858. }