sqlite3_test.go 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170
  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. import (
  7. "crypto/rand"
  8. "database/sql"
  9. "database/sql/driver"
  10. "encoding/hex"
  11. "errors"
  12. "fmt"
  13. "net/url"
  14. "os"
  15. "path/filepath"
  16. "reflect"
  17. "regexp"
  18. "strings"
  19. "sync"
  20. "testing"
  21. "time"
  22. "github.com/mattn/go-sqlite3/sqlite3_test"
  23. )
  24. func TempFilename() string {
  25. randBytes := make([]byte, 16)
  26. rand.Read(randBytes)
  27. return filepath.Join(os.TempDir(), "foo"+hex.EncodeToString(randBytes)+".db")
  28. }
  29. func doTestOpen(t *testing.T, option string) (string, error) {
  30. var url string
  31. tempFilename := TempFilename()
  32. if option != "" {
  33. url = tempFilename + option
  34. } else {
  35. url = tempFilename
  36. }
  37. db, err := sql.Open("sqlite3", url)
  38. if err != nil {
  39. return "Failed to open database:", err
  40. }
  41. defer os.Remove(tempFilename)
  42. defer db.Close()
  43. _, err = db.Exec("drop table foo")
  44. _, err = db.Exec("create table foo (id integer)")
  45. if err != nil {
  46. return "Failed to create table:", err
  47. }
  48. if stat, err := os.Stat(tempFilename); err != nil || stat.IsDir() {
  49. return "Failed to create ./foo.db", nil
  50. }
  51. return "", nil
  52. }
  53. func TestOpen(t *testing.T) {
  54. cases := map[string]bool{
  55. "": true,
  56. "?_txlock=immediate": true,
  57. "?_txlock=deferred": true,
  58. "?_txlock=exclusive": true,
  59. "?_txlock=bogus": false,
  60. }
  61. for option, expectedPass := range cases {
  62. result, err := doTestOpen(t, option)
  63. if result == "" {
  64. if !expectedPass {
  65. errmsg := fmt.Sprintf("_txlock error not caught at dbOpen with option: %s", option)
  66. t.Fatal(errmsg)
  67. }
  68. } else if expectedPass {
  69. if err == nil {
  70. t.Fatal(result)
  71. } else {
  72. t.Fatal(result, err)
  73. }
  74. }
  75. }
  76. }
  77. func TestClose(t *testing.T) {
  78. tempFilename := TempFilename()
  79. db, err := sql.Open("sqlite3", tempFilename)
  80. if err != nil {
  81. t.Fatal("Failed to open database:", err)
  82. }
  83. defer os.Remove(tempFilename)
  84. _, err = db.Exec("drop table foo")
  85. _, err = db.Exec("create table foo (id integer)")
  86. if err != nil {
  87. t.Fatal("Failed to create table:", err)
  88. }
  89. stmt, err := db.Prepare("select id from foo where id = ?")
  90. if err != nil {
  91. t.Fatal("Failed to select records:", err)
  92. }
  93. db.Close()
  94. _, err = stmt.Exec(1)
  95. if err == nil {
  96. t.Fatal("Failed to operate closed statement")
  97. }
  98. }
  99. func TestInsert(t *testing.T) {
  100. tempFilename := TempFilename()
  101. db, err := sql.Open("sqlite3", tempFilename)
  102. if err != nil {
  103. t.Fatal("Failed to open database:", err)
  104. }
  105. defer os.Remove(tempFilename)
  106. defer db.Close()
  107. _, err = db.Exec("drop table foo")
  108. _, err = db.Exec("create table foo (id integer)")
  109. if err != nil {
  110. t.Fatal("Failed to create table:", err)
  111. }
  112. res, err := db.Exec("insert into foo(id) values(123)")
  113. if err != nil {
  114. t.Fatal("Failed to insert record:", err)
  115. }
  116. affected, _ := res.RowsAffected()
  117. if affected != 1 {
  118. t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
  119. }
  120. rows, err := db.Query("select id from foo")
  121. if err != nil {
  122. t.Fatal("Failed to select records:", err)
  123. }
  124. defer rows.Close()
  125. rows.Next()
  126. var result int
  127. rows.Scan(&result)
  128. if result != 123 {
  129. t.Errorf("Fetched %q; expected %q", 123, result)
  130. }
  131. }
  132. func TestUpdate(t *testing.T) {
  133. tempFilename := TempFilename()
  134. db, err := sql.Open("sqlite3", tempFilename)
  135. if err != nil {
  136. t.Fatal("Failed to open database:", err)
  137. }
  138. defer os.Remove(tempFilename)
  139. defer db.Close()
  140. _, err = db.Exec("drop table foo")
  141. _, err = db.Exec("create table foo (id integer)")
  142. if err != nil {
  143. t.Fatal("Failed to create table:", err)
  144. }
  145. res, err := db.Exec("insert into foo(id) values(123)")
  146. if err != nil {
  147. t.Fatal("Failed to insert record:", err)
  148. }
  149. expected, err := res.LastInsertId()
  150. if err != nil {
  151. t.Fatal("Failed to get LastInsertId:", err)
  152. }
  153. affected, _ := res.RowsAffected()
  154. if err != nil {
  155. t.Fatal("Failed to get RowsAffected:", err)
  156. }
  157. if affected != 1 {
  158. t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
  159. }
  160. res, err = db.Exec("update foo set id = 234")
  161. if err != nil {
  162. t.Fatal("Failed to update record:", err)
  163. }
  164. lastId, err := res.LastInsertId()
  165. if err != nil {
  166. t.Fatal("Failed to get LastInsertId:", err)
  167. }
  168. if expected != lastId {
  169. t.Errorf("Expected %q for last Id, but %q:", expected, lastId)
  170. }
  171. affected, _ = res.RowsAffected()
  172. if err != nil {
  173. t.Fatal("Failed to get RowsAffected:", err)
  174. }
  175. if affected != 1 {
  176. t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
  177. }
  178. rows, err := db.Query("select id from foo")
  179. if err != nil {
  180. t.Fatal("Failed to select records:", err)
  181. }
  182. defer rows.Close()
  183. rows.Next()
  184. var result int
  185. rows.Scan(&result)
  186. if result != 234 {
  187. t.Errorf("Fetched %q; expected %q", 234, result)
  188. }
  189. }
  190. func TestDelete(t *testing.T) {
  191. tempFilename := TempFilename()
  192. db, err := sql.Open("sqlite3", tempFilename)
  193. if err != nil {
  194. t.Fatal("Failed to open database:", err)
  195. }
  196. defer os.Remove(tempFilename)
  197. defer db.Close()
  198. _, err = db.Exec("drop table foo")
  199. _, err = db.Exec("create table foo (id integer)")
  200. if err != nil {
  201. t.Fatal("Failed to create table:", err)
  202. }
  203. res, err := db.Exec("insert into foo(id) values(123)")
  204. if err != nil {
  205. t.Fatal("Failed to insert record:", err)
  206. }
  207. expected, err := res.LastInsertId()
  208. if err != nil {
  209. t.Fatal("Failed to get LastInsertId:", err)
  210. }
  211. affected, err := res.RowsAffected()
  212. if err != nil {
  213. t.Fatal("Failed to get RowsAffected:", err)
  214. }
  215. if affected != 1 {
  216. t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected)
  217. }
  218. res, err = db.Exec("delete from foo where id = 123")
  219. if err != nil {
  220. t.Fatal("Failed to delete record:", err)
  221. }
  222. lastId, err := res.LastInsertId()
  223. if err != nil {
  224. t.Fatal("Failed to get LastInsertId:", err)
  225. }
  226. if expected != lastId {
  227. t.Errorf("Expected %q for last Id, but %q:", expected, lastId)
  228. }
  229. affected, err = res.RowsAffected()
  230. if err != nil {
  231. t.Fatal("Failed to get RowsAffected:", err)
  232. }
  233. if affected != 1 {
  234. t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected)
  235. }
  236. rows, err := db.Query("select id from foo")
  237. if err != nil {
  238. t.Fatal("Failed to select records:", err)
  239. }
  240. defer rows.Close()
  241. if rows.Next() {
  242. t.Error("Fetched row but expected not rows")
  243. }
  244. }
  245. func TestBooleanRoundtrip(t *testing.T) {
  246. tempFilename := TempFilename()
  247. db, err := sql.Open("sqlite3", tempFilename)
  248. if err != nil {
  249. t.Fatal("Failed to open database:", err)
  250. }
  251. defer os.Remove(tempFilename)
  252. defer db.Close()
  253. _, err = db.Exec("DROP TABLE foo")
  254. _, err = db.Exec("CREATE TABLE foo(id INTEGER, value BOOL)")
  255. if err != nil {
  256. t.Fatal("Failed to create table:", err)
  257. }
  258. _, err = db.Exec("INSERT INTO foo(id, value) VALUES(1, ?)", true)
  259. if err != nil {
  260. t.Fatal("Failed to insert true value:", err)
  261. }
  262. _, err = db.Exec("INSERT INTO foo(id, value) VALUES(2, ?)", false)
  263. if err != nil {
  264. t.Fatal("Failed to insert false value:", err)
  265. }
  266. rows, err := db.Query("SELECT id, value FROM foo")
  267. if err != nil {
  268. t.Fatal("Unable to query foo table:", err)
  269. }
  270. defer rows.Close()
  271. for rows.Next() {
  272. var id int
  273. var value bool
  274. if err := rows.Scan(&id, &value); err != nil {
  275. t.Error("Unable to scan results:", err)
  276. continue
  277. }
  278. if id == 1 && !value {
  279. t.Error("Value for id 1 should be true, not false")
  280. } else if id == 2 && value {
  281. t.Error("Value for id 2 should be false, not true")
  282. }
  283. }
  284. }
  285. func TestTimestamp(t *testing.T) {
  286. tempFilename := TempFilename()
  287. db, err := sql.Open("sqlite3", tempFilename)
  288. if err != nil {
  289. t.Fatal("Failed to open database:", err)
  290. }
  291. defer os.Remove(tempFilename)
  292. defer db.Close()
  293. _, err = db.Exec("DROP TABLE foo")
  294. _, err = db.Exec("CREATE TABLE foo(id INTEGER, ts timeSTAMP, dt DATETIME)")
  295. if err != nil {
  296. t.Fatal("Failed to create table:", err)
  297. }
  298. timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC)
  299. timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC)
  300. timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC)
  301. tests := []struct {
  302. value interface{}
  303. expected time.Time
  304. }{
  305. {"nonsense", time.Time{}},
  306. {"0000-00-00 00:00:00", time.Time{}},
  307. {timestamp1, timestamp1},
  308. {timestamp1.Unix(), timestamp1},
  309. {timestamp1.UnixNano() / int64(time.Millisecond), timestamp1},
  310. {timestamp1.In(time.FixedZone("TEST", -7*3600)), timestamp1},
  311. {timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1},
  312. {timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1},
  313. {timestamp1.Format("2006-01-02 15:04:05"), timestamp1},
  314. {timestamp1.Format("2006-01-02T15:04:05"), timestamp1},
  315. {timestamp2, timestamp2},
  316. {"2006-01-02 15:04:05.123456789", timestamp2},
  317. {"2006-01-02T15:04:05.123456789", timestamp2},
  318. {"2012-11-04", timestamp3},
  319. {"2012-11-04 00:00", timestamp3},
  320. {"2012-11-04 00:00:00", timestamp3},
  321. {"2012-11-04 00:00:00.000", timestamp3},
  322. {"2012-11-04T00:00", timestamp3},
  323. {"2012-11-04T00:00:00", timestamp3},
  324. {"2012-11-04T00:00:00.000", timestamp3},
  325. }
  326. for i := range tests {
  327. _, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value)
  328. if err != nil {
  329. t.Fatal("Failed to insert timestamp:", err)
  330. }
  331. }
  332. rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC")
  333. if err != nil {
  334. t.Fatal("Unable to query foo table:", err)
  335. }
  336. defer rows.Close()
  337. seen := 0
  338. for rows.Next() {
  339. var id int
  340. var ts, dt time.Time
  341. if err := rows.Scan(&id, &ts, &dt); err != nil {
  342. t.Error("Unable to scan results:", err)
  343. continue
  344. }
  345. if id < 0 || id >= len(tests) {
  346. t.Error("Bad row id: ", id)
  347. continue
  348. }
  349. seen++
  350. if !tests[id].expected.Equal(ts) {
  351. t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
  352. }
  353. if !tests[id].expected.Equal(dt) {
  354. t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
  355. }
  356. }
  357. if seen != len(tests) {
  358. t.Errorf("Expected to see %d rows", len(tests))
  359. }
  360. }
  361. func TestBoolean(t *testing.T) {
  362. tempFilename := TempFilename()
  363. db, err := sql.Open("sqlite3", tempFilename)
  364. if err != nil {
  365. t.Fatal("Failed to open database:", err)
  366. }
  367. defer os.Remove(tempFilename)
  368. defer db.Close()
  369. _, err = db.Exec("CREATE TABLE foo(id INTEGER, fbool BOOLEAN)")
  370. if err != nil {
  371. t.Fatal("Failed to create table:", err)
  372. }
  373. bool1 := true
  374. _, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(1, ?)", bool1)
  375. if err != nil {
  376. t.Fatal("Failed to insert boolean:", err)
  377. }
  378. bool2 := false
  379. _, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(2, ?)", bool2)
  380. if err != nil {
  381. t.Fatal("Failed to insert boolean:", err)
  382. }
  383. bool3 := "nonsense"
  384. _, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(3, ?)", bool3)
  385. if err != nil {
  386. t.Fatal("Failed to insert nonsense:", err)
  387. }
  388. rows, err := db.Query("SELECT id, fbool FROM foo where fbool = ?", bool1)
  389. if err != nil {
  390. t.Fatal("Unable to query foo table:", err)
  391. }
  392. counter := 0
  393. var id int
  394. var fbool bool
  395. for rows.Next() {
  396. if err := rows.Scan(&id, &fbool); err != nil {
  397. t.Fatal("Unable to scan results:", err)
  398. }
  399. counter++
  400. }
  401. if counter != 1 {
  402. t.Fatalf("Expected 1 row but %v", counter)
  403. }
  404. if id != 1 && fbool != true {
  405. t.Fatalf("Value for id 1 should be %v, not %v", bool1, fbool)
  406. }
  407. rows, err = db.Query("SELECT id, fbool FROM foo where fbool = ?", bool2)
  408. if err != nil {
  409. t.Fatal("Unable to query foo table:", err)
  410. }
  411. counter = 0
  412. for rows.Next() {
  413. if err := rows.Scan(&id, &fbool); err != nil {
  414. t.Fatal("Unable to scan results:", err)
  415. }
  416. counter++
  417. }
  418. if counter != 1 {
  419. t.Fatalf("Expected 1 row but %v", counter)
  420. }
  421. if id != 2 && fbool != false {
  422. t.Fatalf("Value for id 2 should be %v, not %v", bool2, fbool)
  423. }
  424. // make sure "nonsense" triggered an error
  425. rows, err = db.Query("SELECT id, fbool FROM foo where id=?;", 3)
  426. if err != nil {
  427. t.Fatal("Unable to query foo table:", err)
  428. }
  429. rows.Next()
  430. err = rows.Scan(&id, &fbool)
  431. if err == nil {
  432. t.Error("Expected error from \"nonsense\" bool")
  433. }
  434. }
  435. func TestFloat32(t *testing.T) {
  436. tempFilename := TempFilename()
  437. db, err := sql.Open("sqlite3", tempFilename)
  438. if err != nil {
  439. t.Fatal("Failed to open database:", err)
  440. }
  441. defer os.Remove(tempFilename)
  442. defer db.Close()
  443. _, err = db.Exec("CREATE TABLE foo(id INTEGER)")
  444. if err != nil {
  445. t.Fatal("Failed to create table:", err)
  446. }
  447. _, err = db.Exec("INSERT INTO foo(id) VALUES(null)")
  448. if err != nil {
  449. t.Fatal("Failed to insert null:", err)
  450. }
  451. rows, err := db.Query("SELECT id FROM foo")
  452. if err != nil {
  453. t.Fatal("Unable to query foo table:", err)
  454. }
  455. if !rows.Next() {
  456. t.Fatal("Unable to query results:", err)
  457. }
  458. var id interface{}
  459. if err := rows.Scan(&id); err != nil {
  460. t.Fatal("Unable to scan results:", err)
  461. }
  462. if id != nil {
  463. t.Error("Expected nil but not")
  464. }
  465. }
  466. func TestNull(t *testing.T) {
  467. tempFilename := TempFilename()
  468. db, err := sql.Open("sqlite3", tempFilename)
  469. if err != nil {
  470. t.Fatal("Failed to open database:", err)
  471. }
  472. defer os.Remove(tempFilename)
  473. defer db.Close()
  474. rows, err := db.Query("SELECT 3.141592")
  475. if err != nil {
  476. t.Fatal("Unable to query foo table:", err)
  477. }
  478. if !rows.Next() {
  479. t.Fatal("Unable to query results:", err)
  480. }
  481. var v interface{}
  482. if err := rows.Scan(&v); err != nil {
  483. t.Fatal("Unable to scan results:", err)
  484. }
  485. f, ok := v.(float64)
  486. if !ok {
  487. t.Error("Expected float but not")
  488. }
  489. if f != 3.141592 {
  490. t.Error("Expected 3.141592 but not")
  491. }
  492. }
  493. func TestTransaction(t *testing.T) {
  494. tempFilename := TempFilename()
  495. db, err := sql.Open("sqlite3", tempFilename)
  496. if err != nil {
  497. t.Fatal("Failed to open database:", err)
  498. }
  499. defer os.Remove(tempFilename)
  500. defer db.Close()
  501. _, err = db.Exec("CREATE TABLE foo(id INTEGER)")
  502. if err != nil {
  503. t.Fatal("Failed to create table:", err)
  504. }
  505. tx, err := db.Begin()
  506. if err != nil {
  507. t.Fatal("Failed to begin transaction:", err)
  508. }
  509. _, err = tx.Exec("INSERT INTO foo(id) VALUES(1)")
  510. if err != nil {
  511. t.Fatal("Failed to insert null:", err)
  512. }
  513. rows, err := tx.Query("SELECT id from foo")
  514. if err != nil {
  515. t.Fatal("Unable to query foo table:", err)
  516. }
  517. err = tx.Rollback()
  518. if err != nil {
  519. t.Fatal("Failed to rollback transaction:", err)
  520. }
  521. if rows.Next() {
  522. t.Fatal("Unable to query results:", err)
  523. }
  524. tx, err = db.Begin()
  525. if err != nil {
  526. t.Fatal("Failed to begin transaction:", err)
  527. }
  528. _, err = tx.Exec("INSERT INTO foo(id) VALUES(1)")
  529. if err != nil {
  530. t.Fatal("Failed to insert null:", err)
  531. }
  532. err = tx.Commit()
  533. if err != nil {
  534. t.Fatal("Failed to commit transaction:", err)
  535. }
  536. rows, err = tx.Query("SELECT id from foo")
  537. if err == nil {
  538. t.Fatal("Expected failure to query")
  539. }
  540. }
  541. func TestWAL(t *testing.T) {
  542. tempFilename := TempFilename()
  543. db, err := sql.Open("sqlite3", tempFilename)
  544. if err != nil {
  545. t.Fatal("Failed to open database:", err)
  546. }
  547. defer os.Remove(tempFilename)
  548. defer db.Close()
  549. if _, err = db.Exec("PRAGMA journal_mode=WAL;"); err != nil {
  550. t.Fatal("Failed to Exec PRAGMA journal_mode:", err)
  551. }
  552. if _, err = db.Exec("PRAGMA locking_mode=EXCLUSIVE;"); err != nil {
  553. t.Fatal("Failed to Exec PRAGMA locking_mode:", err)
  554. }
  555. if _, err = db.Exec("CREATE TABLE test (id SERIAL, user TEXT NOT NULL, name TEXT NOT NULL);"); err != nil {
  556. t.Fatal("Failed to Exec CREATE TABLE:", err)
  557. }
  558. if _, err = db.Exec("INSERT INTO test (user, name) VALUES ('user','name');"); err != nil {
  559. t.Fatal("Failed to Exec INSERT:", err)
  560. }
  561. trans, err := db.Begin()
  562. if err != nil {
  563. t.Fatal("Failed to Begin:", err)
  564. }
  565. s, err := trans.Prepare("INSERT INTO test (user, name) VALUES (?, ?);")
  566. if err != nil {
  567. t.Fatal("Failed to Prepare:", err)
  568. }
  569. var count int
  570. if err = trans.QueryRow("SELECT count(user) FROM test;").Scan(&count); err != nil {
  571. t.Fatal("Failed to QueryRow:", err)
  572. }
  573. if _, err = s.Exec("bbbb", "aaaa"); err != nil {
  574. t.Fatal("Failed to Exec prepared statement:", err)
  575. }
  576. if err = s.Close(); err != nil {
  577. t.Fatal("Failed to Close prepared statement:", err)
  578. }
  579. if err = trans.Commit(); err != nil {
  580. t.Fatal("Failed to Commit:", err)
  581. }
  582. }
  583. func TestTimezoneConversion(t *testing.T) {
  584. zones := []string{"UTC", "US/Central", "US/Pacific", "Local"}
  585. for _, tz := range zones {
  586. tempFilename := TempFilename()
  587. db, err := sql.Open("sqlite3", tempFilename+"?_loc="+url.QueryEscape(tz))
  588. if err != nil {
  589. t.Fatal("Failed to open database:", err)
  590. }
  591. defer os.Remove(tempFilename)
  592. defer db.Close()
  593. _, err = db.Exec("DROP TABLE foo")
  594. _, err = db.Exec("CREATE TABLE foo(id INTEGER, ts TIMESTAMP, dt DATETIME)")
  595. if err != nil {
  596. t.Fatal("Failed to create table:", err)
  597. }
  598. loc, err := time.LoadLocation(tz)
  599. if err != nil {
  600. t.Fatal("Failed to load location:", err)
  601. }
  602. timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC)
  603. timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC)
  604. timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC)
  605. tests := []struct {
  606. value interface{}
  607. expected time.Time
  608. }{
  609. {"nonsense", time.Time{}.In(loc)},
  610. {"0000-00-00 00:00:00", time.Time{}.In(loc)},
  611. {timestamp1, timestamp1.In(loc)},
  612. {timestamp1.Unix(), timestamp1.In(loc)},
  613. {timestamp1.In(time.FixedZone("TEST", -7*3600)), timestamp1.In(loc)},
  614. {timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1.In(loc)},
  615. {timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1.In(loc)},
  616. {timestamp1.Format("2006-01-02 15:04:05"), timestamp1.In(loc)},
  617. {timestamp1.Format("2006-01-02T15:04:05"), timestamp1.In(loc)},
  618. {timestamp2, timestamp2.In(loc)},
  619. {"2006-01-02 15:04:05.123456789", timestamp2.In(loc)},
  620. {"2006-01-02T15:04:05.123456789", timestamp2.In(loc)},
  621. {"2012-11-04", timestamp3.In(loc)},
  622. {"2012-11-04 00:00", timestamp3.In(loc)},
  623. {"2012-11-04 00:00:00", timestamp3.In(loc)},
  624. {"2012-11-04 00:00:00.000", timestamp3.In(loc)},
  625. {"2012-11-04T00:00", timestamp3.In(loc)},
  626. {"2012-11-04T00:00:00", timestamp3.In(loc)},
  627. {"2012-11-04T00:00:00.000", timestamp3.In(loc)},
  628. }
  629. for i := range tests {
  630. _, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value)
  631. if err != nil {
  632. t.Fatal("Failed to insert timestamp:", err)
  633. }
  634. }
  635. rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC")
  636. if err != nil {
  637. t.Fatal("Unable to query foo table:", err)
  638. }
  639. defer rows.Close()
  640. seen := 0
  641. for rows.Next() {
  642. var id int
  643. var ts, dt time.Time
  644. if err := rows.Scan(&id, &ts, &dt); err != nil {
  645. t.Error("Unable to scan results:", err)
  646. continue
  647. }
  648. if id < 0 || id >= len(tests) {
  649. t.Error("Bad row id: ", id)
  650. continue
  651. }
  652. seen++
  653. if !tests[id].expected.Equal(ts) {
  654. t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, ts)
  655. }
  656. if !tests[id].expected.Equal(dt) {
  657. t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
  658. }
  659. if tests[id].expected.Location().String() != ts.Location().String() {
  660. t.Errorf("Location for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected.Location().String(), ts.Location().String())
  661. }
  662. if tests[id].expected.Location().String() != dt.Location().String() {
  663. t.Errorf("Location for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected.Location().String(), dt.Location().String())
  664. }
  665. }
  666. if seen != len(tests) {
  667. t.Errorf("Expected to see %d rows", len(tests))
  668. }
  669. }
  670. }
  671. func TestSuite(t *testing.T) {
  672. db, err := sql.Open("sqlite3", ":memory:")
  673. if err != nil {
  674. t.Fatal(err)
  675. }
  676. defer db.Close()
  677. sqlite3_test.RunTests(t, db, sqlite3_test.SQLITE)
  678. }
  679. // TODO: Execer & Queryer currently disabled
  680. // https://github.com/mattn/go-sqlite3/issues/82
  681. func TestExecer(t *testing.T) {
  682. tempFilename := TempFilename()
  683. db, err := sql.Open("sqlite3", tempFilename)
  684. if err != nil {
  685. t.Fatal("Failed to open database:", err)
  686. }
  687. defer os.Remove(tempFilename)
  688. defer db.Close()
  689. _, err = db.Exec(`
  690. create table foo (id integer); -- one comment
  691. insert into foo(id) values(?);
  692. insert into foo(id) values(?);
  693. insert into foo(id) values(?); -- another comment
  694. `, 1, 2, 3)
  695. if err != nil {
  696. t.Error("Failed to call db.Exec:", err)
  697. }
  698. }
  699. func TestQueryer(t *testing.T) {
  700. tempFilename := TempFilename()
  701. db, err := sql.Open("sqlite3", tempFilename)
  702. if err != nil {
  703. t.Fatal("Failed to open database:", err)
  704. }
  705. defer os.Remove(tempFilename)
  706. defer db.Close()
  707. _, err = db.Exec(`
  708. create table foo (id integer);
  709. `)
  710. if err != nil {
  711. t.Error("Failed to call db.Query:", err)
  712. }
  713. rows, err := db.Query(`
  714. insert into foo(id) values(?);
  715. insert into foo(id) values(?);
  716. insert into foo(id) values(?);
  717. select id from foo order by id;
  718. `, 3, 2, 1)
  719. if err != nil {
  720. t.Error("Failed to call db.Query:", err)
  721. }
  722. defer rows.Close()
  723. n := 1
  724. if rows != nil {
  725. for rows.Next() {
  726. var id int
  727. err = rows.Scan(&id)
  728. if err != nil {
  729. t.Error("Failed to db.Query:", err)
  730. }
  731. if id != n {
  732. t.Error("Failed to db.Query: not matched results")
  733. }
  734. }
  735. }
  736. }
  737. func TestStress(t *testing.T) {
  738. tempFilename := TempFilename()
  739. db, err := sql.Open("sqlite3", tempFilename)
  740. if err != nil {
  741. t.Fatal("Failed to open database:", err)
  742. }
  743. db.Exec("CREATE TABLE foo (id int);")
  744. db.Exec("INSERT INTO foo VALUES(1);")
  745. db.Exec("INSERT INTO foo VALUES(2);")
  746. db.Close()
  747. for i := 0; i < 10000; i++ {
  748. db, err := sql.Open("sqlite3", tempFilename)
  749. if err != nil {
  750. t.Fatal("Failed to open database:", err)
  751. }
  752. for j := 0; j < 3; j++ {
  753. rows, err := db.Query("select * from foo where id=1;")
  754. if err != nil {
  755. t.Error("Failed to call db.Query:", err)
  756. }
  757. for rows.Next() {
  758. var i int
  759. if err := rows.Scan(&i); err != nil {
  760. t.Errorf("Scan failed: %v\n", err)
  761. }
  762. }
  763. if err := rows.Err(); err != nil {
  764. t.Errorf("Post-scan failed: %v\n", err)
  765. }
  766. rows.Close()
  767. }
  768. db.Close()
  769. }
  770. }
  771. func TestDateTimeLocal(t *testing.T) {
  772. zone := "Asia/Tokyo"
  773. tempFilename := TempFilename()
  774. db, err := sql.Open("sqlite3", tempFilename+"?_loc="+zone)
  775. if err != nil {
  776. t.Fatal("Failed to open database:", err)
  777. }
  778. db.Exec("CREATE TABLE foo (dt datetime);")
  779. db.Exec("INSERT INTO foo VALUES('2015-03-05 15:16:17');")
  780. row := db.QueryRow("select * from foo")
  781. var d time.Time
  782. err = row.Scan(&d)
  783. if err != nil {
  784. t.Fatal("Failed to scan datetime:", err)
  785. }
  786. if d.Hour() == 15 || !strings.Contains(d.String(), "JST") {
  787. t.Fatal("Result should have timezone", d)
  788. }
  789. db.Close()
  790. db, err = sql.Open("sqlite3", tempFilename)
  791. if err != nil {
  792. t.Fatal("Failed to open database:", err)
  793. }
  794. row = db.QueryRow("select * from foo")
  795. err = row.Scan(&d)
  796. if err != nil {
  797. t.Fatal("Failed to scan datetime:", err)
  798. }
  799. if d.UTC().Hour() != 15 || !strings.Contains(d.String(), "UTC") {
  800. t.Fatalf("Result should not have timezone %v %v", zone, d.String())
  801. }
  802. _, err = db.Exec("DELETE FROM foo")
  803. if err != nil {
  804. t.Fatal("Failed to delete table:", err)
  805. }
  806. dt, err := time.Parse("2006/1/2 15/4/5 -0700 MST", "2015/3/5 15/16/17 +0900 JST")
  807. if err != nil {
  808. t.Fatal("Failed to parse datetime:", err)
  809. }
  810. db.Exec("INSERT INTO foo VALUES(?);", dt)
  811. db.Close()
  812. db, err = sql.Open("sqlite3", tempFilename+"?_loc="+zone)
  813. if err != nil {
  814. t.Fatal("Failed to open database:", err)
  815. }
  816. row = db.QueryRow("select * from foo")
  817. err = row.Scan(&d)
  818. if err != nil {
  819. t.Fatal("Failed to scan datetime:", err)
  820. }
  821. if d.Hour() != 15 || !strings.Contains(d.String(), "JST") {
  822. t.Fatalf("Result should have timezone %v %v", zone, d.String())
  823. }
  824. }
  825. func TestVersion(t *testing.T) {
  826. s, n, id := Version()
  827. if s == "" || n == 0 || id == "" {
  828. t.Errorf("Version failed %q, %d, %q\n", s, n, id)
  829. }
  830. }
  831. func TestNumberNamedParams(t *testing.T) {
  832. tempFilename := TempFilename()
  833. db, err := sql.Open("sqlite3", tempFilename)
  834. if err != nil {
  835. t.Fatal("Failed to open database:", err)
  836. }
  837. defer os.Remove(tempFilename)
  838. defer db.Close()
  839. _, err = db.Exec(`
  840. create table foo (id integer, name text, extra text);
  841. `)
  842. if err != nil {
  843. t.Error("Failed to call db.Query:", err)
  844. }
  845. _, err = db.Exec(`insert into foo(id, name, extra) values($1, $2, $2)`, 1, "foo")
  846. if err != nil {
  847. t.Error("Failed to call db.Exec:", err)
  848. }
  849. row := db.QueryRow(`select id, extra from foo where id = $1 and extra = $2`, 1, "foo")
  850. if row == nil {
  851. t.Error("Failed to call db.QueryRow")
  852. }
  853. var id int
  854. var extra string
  855. err = row.Scan(&id, &extra)
  856. if err != nil {
  857. t.Error("Failed to db.Scan:", err)
  858. }
  859. if id != 1 || extra != "foo" {
  860. t.Error("Failed to db.QueryRow: not matched results")
  861. }
  862. }
  863. func TestStringContainingZero(t *testing.T) {
  864. tempFilename := TempFilename()
  865. db, err := sql.Open("sqlite3", tempFilename)
  866. if err != nil {
  867. t.Fatal("Failed to open database:", err)
  868. }
  869. defer os.Remove(tempFilename)
  870. defer db.Close()
  871. _, err = db.Exec(`
  872. create table foo (id integer, name, extra text);
  873. `)
  874. if err != nil {
  875. t.Error("Failed to call db.Query:", err)
  876. }
  877. const text = "foo\x00bar"
  878. _, err = db.Exec(`insert into foo(id, name, extra) values($1, $2, $2)`, 1, text)
  879. if err != nil {
  880. t.Error("Failed to call db.Exec:", err)
  881. }
  882. row := db.QueryRow(`select id, extra from foo where id = $1 and extra = $2`, 1, text)
  883. if row == nil {
  884. t.Error("Failed to call db.QueryRow")
  885. }
  886. var id int
  887. var extra string
  888. err = row.Scan(&id, &extra)
  889. if err != nil {
  890. t.Error("Failed to db.Scan:", err)
  891. }
  892. if id != 1 || extra != text {
  893. t.Error("Failed to db.QueryRow: not matched results")
  894. }
  895. }
  896. const CurrentTimeStamp = "2006-01-02 15:04:05"
  897. type TimeStamp struct{ *time.Time }
  898. func (t TimeStamp) Scan(value interface{}) error {
  899. var err error
  900. switch v := value.(type) {
  901. case string:
  902. *t.Time, err = time.Parse(CurrentTimeStamp, v)
  903. case []byte:
  904. *t.Time, err = time.Parse(CurrentTimeStamp, string(v))
  905. default:
  906. err = errors.New("invalid type for current_timestamp")
  907. }
  908. return err
  909. }
  910. func (t TimeStamp) Value() (driver.Value, error) {
  911. return t.Time.Format(CurrentTimeStamp), nil
  912. }
  913. func TestDateTimeNow(t *testing.T) {
  914. tempFilename := TempFilename()
  915. db, err := sql.Open("sqlite3", tempFilename)
  916. if err != nil {
  917. t.Fatal("Failed to open database:", err)
  918. }
  919. defer db.Close()
  920. var d time.Time
  921. err = db.QueryRow("SELECT datetime('now')").Scan(TimeStamp{&d})
  922. if err != nil {
  923. t.Fatal("Failed to scan datetime:", err)
  924. }
  925. }
  926. func TestFunctionRegistration(t *testing.T) {
  927. addi_8_16_32 := func(a int8, b int16) int32 { return int32(a) + int32(b) }
  928. addi_64 := func(a, b int64) int64 { return a + b }
  929. addu_8_16_32 := func(a uint8, b uint16) uint32 { return uint32(a) + uint32(b) }
  930. addu_64 := func(a, b uint64) uint64 { return a + b }
  931. addiu := func(a int, b uint) int64 { return int64(a) + int64(b) }
  932. addf_32_64 := func(a float32, b float64) float64 { return float64(a) + b }
  933. not := func(a bool) bool { return !a }
  934. regex := func(re, s string) (bool, error) {
  935. return regexp.MatchString(re, s)
  936. }
  937. sql.Register("sqlite3_FunctionRegistration", &SQLiteDriver{
  938. ConnectHook: func(conn *SQLiteConn) error {
  939. if err := conn.RegisterFunc("addi_8_16_32", addi_8_16_32, true); err != nil {
  940. return err
  941. }
  942. if err := conn.RegisterFunc("addi_64", addi_64, true); err != nil {
  943. return err
  944. }
  945. if err := conn.RegisterFunc("addu_8_16_32", addu_8_16_32, true); err != nil {
  946. return err
  947. }
  948. if err := conn.RegisterFunc("addu_64", addu_64, true); err != nil {
  949. return err
  950. }
  951. if err := conn.RegisterFunc("addiu", addiu, true); err != nil {
  952. return err
  953. }
  954. if err := conn.RegisterFunc("addf_32_64", addf_32_64, true); err != nil {
  955. return err
  956. }
  957. if err := conn.RegisterFunc("not", not, true); err != nil {
  958. return err
  959. }
  960. if err := conn.RegisterFunc("regex", regex, true); err != nil {
  961. return err
  962. }
  963. return nil
  964. },
  965. })
  966. db, err := sql.Open("sqlite3_FunctionRegistration", ":memory:")
  967. if err != nil {
  968. t.Fatal("Failed to open database:", err)
  969. }
  970. defer db.Close()
  971. ops := []struct {
  972. query string
  973. expected interface{}
  974. }{
  975. {"SELECT addi_8_16_32(1,2)", int32(3)},
  976. {"SELECT addi_64(1,2)", int64(3)},
  977. {"SELECT addu_8_16_32(1,2)", uint32(3)},
  978. {"SELECT addu_64(1,2)", uint64(3)},
  979. {"SELECT addiu(1,2)", int64(3)},
  980. {"SELECT addf_32_64(1.5,1.5)", float64(3)},
  981. {"SELECT not(1)", false},
  982. {"SELECT not(0)", true},
  983. {`SELECT regex("^foo.*", "foobar")`, true},
  984. {`SELECT regex("^foo.*", "barfoobar")`, false},
  985. }
  986. for _, op := range ops {
  987. ret := reflect.New(reflect.TypeOf(op.expected))
  988. err = db.QueryRow(op.query).Scan(ret.Interface())
  989. if err != nil {
  990. t.Errorf("Query %q failed: %s", op.query, err)
  991. } else if !reflect.DeepEqual(ret.Elem().Interface(), op.expected) {
  992. t.Errorf("Query %q returned wrong value: got %v (%T), want %v (%T)", op.query, ret.Elem().Interface(), ret.Elem().Interface(), op.expected, op.expected)
  993. }
  994. }
  995. }
  996. var customFunctionOnce sync.Once
  997. func BenchmarkCustomFunctions(b *testing.B) {
  998. customFunctionOnce.Do(func() {
  999. custom_add := func(a, b int64) int64 {
  1000. return a + b
  1001. }
  1002. sql.Register("sqlite3_BenchmarkCustomFunctions", &SQLiteDriver{
  1003. ConnectHook: func(conn *SQLiteConn) error {
  1004. // Impure function to force sqlite to reexecute it each time.
  1005. if err := conn.RegisterFunc("custom_add", custom_add, false); err != nil {
  1006. return err
  1007. }
  1008. return nil
  1009. },
  1010. })
  1011. })
  1012. db, err := sql.Open("sqlite3_BenchmarkCustomFunctions", ":memory:")
  1013. if err != nil {
  1014. b.Fatal("Failed to open database:", err)
  1015. }
  1016. defer db.Close()
  1017. b.ResetTimer()
  1018. for i := 0; i < b.N; i++ {
  1019. var i int64
  1020. err = db.QueryRow("SELECT custom_add(1,2)").Scan(&i)
  1021. if err != nil {
  1022. b.Fatal("Failed to run custom add:", err)
  1023. }
  1024. }
  1025. }