sqlite3_go18_test.go 8.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371
  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. // +build go1.8
  6. package sqlite3
  7. import (
  8. "context"
  9. "database/sql"
  10. "fmt"
  11. "io/ioutil"
  12. "math/rand"
  13. "os"
  14. "testing"
  15. "time"
  16. )
  17. func TestNamedParams(t *testing.T) {
  18. tempFilename := TempFilename(t)
  19. defer os.Remove(tempFilename)
  20. db, err := sql.Open("sqlite3", tempFilename)
  21. if err != nil {
  22. t.Fatal("Failed to open database:", err)
  23. }
  24. defer db.Close()
  25. _, err = db.Exec(`
  26. create table foo (id integer, name text, extra text);
  27. `)
  28. if err != nil {
  29. t.Error("Failed to call db.Query:", err)
  30. }
  31. _, err = db.Exec(`insert into foo(id, name, extra) values(:id, :name, :name)`, sql.Named("name", "foo"), sql.Named("id", 1))
  32. if err != nil {
  33. t.Error("Failed to call db.Exec:", err)
  34. }
  35. row := db.QueryRow(`select id, extra from foo where id = :id and extra = :extra`, sql.Named("id", 1), sql.Named("extra", "foo"))
  36. if row == nil {
  37. t.Error("Failed to call db.QueryRow")
  38. }
  39. var id int
  40. var extra string
  41. err = row.Scan(&id, &extra)
  42. if err != nil {
  43. t.Error("Failed to db.Scan:", err)
  44. }
  45. if id != 1 || extra != "foo" {
  46. t.Error("Failed to db.QueryRow: not matched results")
  47. }
  48. }
  49. var (
  50. testTableStatements = []string{
  51. `DROP TABLE IF EXISTS test_table`,
  52. `
  53. CREATE TABLE IF NOT EXISTS test_table (
  54. key1 VARCHAR(64) PRIMARY KEY,
  55. key_id VARCHAR(64) NOT NULL,
  56. key2 VARCHAR(64) NOT NULL,
  57. key3 VARCHAR(64) NOT NULL,
  58. key4 VARCHAR(64) NOT NULL,
  59. key5 VARCHAR(64) NOT NULL,
  60. key6 VARCHAR(64) NOT NULL,
  61. data BLOB NOT NULL
  62. );`,
  63. }
  64. letterBytes = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
  65. )
  66. func randStringBytes(n int) string {
  67. b := make([]byte, n)
  68. for i := range b {
  69. b[i] = letterBytes[rand.Intn(len(letterBytes))]
  70. }
  71. return string(b)
  72. }
  73. func initDatabase(t *testing.T, db *sql.DB, rowCount int64) {
  74. for _, query := range testTableStatements {
  75. _, err := db.Exec(query)
  76. if err != nil {
  77. t.Fatal(err)
  78. }
  79. }
  80. for i := int64(0); i < rowCount; i++ {
  81. query := `INSERT INTO test_table
  82. (key1, key_id, key2, key3, key4, key5, key6, data)
  83. VALUES
  84. (?, ?, ?, ?, ?, ?, ?, ?);`
  85. args := []interface{}{
  86. randStringBytes(50),
  87. fmt.Sprint(i),
  88. randStringBytes(50),
  89. randStringBytes(50),
  90. randStringBytes(50),
  91. randStringBytes(50),
  92. randStringBytes(50),
  93. randStringBytes(50),
  94. randStringBytes(2048),
  95. }
  96. _, err := db.Exec(query, args...)
  97. if err != nil {
  98. t.Fatal(err)
  99. }
  100. }
  101. }
  102. func TestShortTimeout(t *testing.T) {
  103. srcTempFilename := TempFilename(t)
  104. defer os.Remove(srcTempFilename)
  105. db, err := sql.Open("sqlite3", srcTempFilename)
  106. if err != nil {
  107. t.Fatal(err)
  108. }
  109. defer db.Close()
  110. initDatabase(t, db, 100)
  111. ctx, cancel := context.WithTimeout(context.Background(), 1*time.Microsecond)
  112. defer cancel()
  113. query := `SELECT key1, key_id, key2, key3, key4, key5, key6, data
  114. FROM test_table
  115. ORDER BY key2 ASC`
  116. _, err = db.QueryContext(ctx, query)
  117. if err != nil && err != context.DeadlineExceeded {
  118. t.Fatal(err)
  119. }
  120. if ctx.Err() != nil && ctx.Err() != context.DeadlineExceeded {
  121. t.Fatal(ctx.Err())
  122. }
  123. }
  124. func TestExecCancel(t *testing.T) {
  125. db, err := sql.Open("sqlite3", ":memory:")
  126. if err != nil {
  127. t.Fatal(err)
  128. }
  129. defer db.Close()
  130. if _, err = db.Exec("create table foo (id integer primary key)"); err != nil {
  131. t.Fatal(err)
  132. }
  133. for n := 0; n < 100; n++ {
  134. ctx, cancel := context.WithCancel(context.Background())
  135. _, err = db.ExecContext(ctx, "insert into foo (id) values (?)", n)
  136. cancel()
  137. if err != nil {
  138. t.Fatal(err)
  139. }
  140. }
  141. }
  142. func doTestOpenContext(t *testing.T, option string) (string, error) {
  143. tempFilename := TempFilename(t)
  144. url := tempFilename + option
  145. defer func() {
  146. err := os.Remove(tempFilename)
  147. if err != nil {
  148. t.Error("temp file remove error:", err)
  149. }
  150. }()
  151. db, err := sql.Open("sqlite3", url)
  152. if err != nil {
  153. return "Failed to open database:", err
  154. }
  155. defer func() {
  156. err = db.Close()
  157. if err != nil {
  158. t.Error("db close error:", err)
  159. }
  160. }()
  161. ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
  162. err = db.PingContext(ctx)
  163. cancel()
  164. if err != nil {
  165. return "ping error:", err
  166. }
  167. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  168. _, err = db.ExecContext(ctx, "drop table foo")
  169. cancel()
  170. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  171. _, err = db.ExecContext(ctx, "create table foo (id integer)")
  172. cancel()
  173. if err != nil {
  174. return "Failed to create table:", err
  175. }
  176. if stat, err := os.Stat(tempFilename); err != nil || stat.IsDir() {
  177. return "Failed to create ./foo.db", nil
  178. }
  179. return "", nil
  180. }
  181. func TestOpenContext(t *testing.T) {
  182. cases := map[string]bool{
  183. "": true,
  184. "?_txlock=immediate": true,
  185. "?_txlock=deferred": true,
  186. "?_txlock=exclusive": true,
  187. "?_txlock=bogus": false,
  188. }
  189. for option, expectedPass := range cases {
  190. result, err := doTestOpenContext(t, option)
  191. if result == "" {
  192. if !expectedPass {
  193. errmsg := fmt.Sprintf("_txlock error not caught at dbOpen with option: %s", option)
  194. t.Fatal(errmsg)
  195. }
  196. } else if expectedPass {
  197. if err == nil {
  198. t.Fatal(result)
  199. } else {
  200. t.Fatal(result, err)
  201. }
  202. }
  203. }
  204. }
  205. func TestFileCopyTruncate(t *testing.T) {
  206. var err error
  207. tempFilename := TempFilename(t)
  208. defer func() {
  209. err = os.Remove(tempFilename)
  210. if err != nil {
  211. t.Error("temp file remove error:", err)
  212. }
  213. }()
  214. db, err := sql.Open("sqlite3", tempFilename)
  215. if err != nil {
  216. t.Fatal("open error:", err)
  217. }
  218. defer func() {
  219. err = db.Close()
  220. if err != nil {
  221. t.Error("db close error:", err)
  222. }
  223. }()
  224. ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
  225. err = db.PingContext(ctx)
  226. cancel()
  227. if err != nil {
  228. t.Fatal("ping error:", err)
  229. }
  230. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  231. _, err = db.ExecContext(ctx, "drop table foo")
  232. cancel()
  233. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  234. _, err = db.ExecContext(ctx, "create table foo (id integer)")
  235. cancel()
  236. if err != nil {
  237. t.Fatal("create table error:", err)
  238. }
  239. // copy db to new file
  240. var data []byte
  241. data, err = ioutil.ReadFile(tempFilename)
  242. if err != nil {
  243. t.Fatal("read file error:", err)
  244. }
  245. var f *os.File
  246. f, err = os.Create(tempFilename + "-db-copy")
  247. if err != nil {
  248. t.Fatal("create file error:", err)
  249. }
  250. defer func() {
  251. err = os.Remove(tempFilename + "-db-copy")
  252. if err != nil {
  253. t.Error("temp file moved remove error:", err)
  254. }
  255. }()
  256. _, err = f.Write(data)
  257. if err != nil {
  258. f.Close()
  259. t.Fatal("write file error:", err)
  260. }
  261. err = f.Close()
  262. if err != nil {
  263. t.Fatal("close file error:", err)
  264. }
  265. // truncate current db file
  266. f, err = os.OpenFile(tempFilename, os.O_WRONLY|os.O_TRUNC, 0666)
  267. if err != nil {
  268. t.Fatal("open file error:", err)
  269. }
  270. err = f.Close()
  271. if err != nil {
  272. t.Fatal("close file error:", err)
  273. }
  274. // test db after file truncate
  275. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  276. err = db.PingContext(ctx)
  277. cancel()
  278. if err != nil {
  279. t.Fatal("ping error:", err)
  280. }
  281. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  282. _, err = db.ExecContext(ctx, "drop table foo")
  283. cancel()
  284. if err == nil {
  285. t.Fatal("drop table no error")
  286. }
  287. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  288. _, err = db.ExecContext(ctx, "create table foo (id integer)")
  289. cancel()
  290. if err != nil {
  291. t.Fatal("create table error:", err)
  292. }
  293. err = db.Close()
  294. if err != nil {
  295. t.Error("db close error:", err)
  296. }
  297. // test copied file
  298. db, err = sql.Open("sqlite3", tempFilename+"-db-copy")
  299. if err != nil {
  300. t.Fatal("open error:", err)
  301. }
  302. defer func() {
  303. err = db.Close()
  304. if err != nil {
  305. t.Error("db close error:", err)
  306. }
  307. }()
  308. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  309. err = db.PingContext(ctx)
  310. cancel()
  311. if err != nil {
  312. t.Fatal("ping error:", err)
  313. }
  314. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  315. _, err = db.ExecContext(ctx, "drop table foo")
  316. cancel()
  317. if err != nil {
  318. t.Fatal("drop table error:", err)
  319. }
  320. ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
  321. _, err = db.ExecContext(ctx, "create table foo (id integer)")
  322. cancel()
  323. if err != nil {
  324. t.Fatal("create table error:", err)
  325. }
  326. }