1// Copyright (C) 2019 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 6// +build cgo 7 8package sqlite3 9 10import ( 11 "database/sql" 12 "os" 13 "testing" 14) 15 16func TestFTS3(t *testing.T) { 17 tempFilename := TempFilename(t) 18 defer os.Remove(tempFilename) 19 db, err := sql.Open("sqlite3", tempFilename) 20 if err != nil { 21 t.Fatal("Failed to open database:", err) 22 } 23 defer db.Close() 24 25 _, err = db.Exec("DROP TABLE foo") 26 _, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts3(id INTEGER PRIMARY KEY, value TEXT)") 27 if err != nil { 28 t.Fatal("Failed to create table:", err) 29 } 30 31 _, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 1, `今日の 晩御飯は 天麩羅よ`) 32 if err != nil { 33 t.Fatal("Failed to insert value:", err) 34 } 35 36 _, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 2, `今日は いい 天気だ`) 37 if err != nil { 38 t.Fatal("Failed to insert value:", err) 39 } 40 41 rows, err := db.Query("SELECT id, value FROM foo WHERE value MATCH '今日* 天*'") 42 if err != nil { 43 t.Fatal("Unable to query foo table:", err) 44 } 45 defer rows.Close() 46 47 for rows.Next() { 48 var id int 49 var value string 50 51 if err := rows.Scan(&id, &value); err != nil { 52 t.Error("Unable to scan results:", err) 53 continue 54 } 55 56 if id == 1 && value != `今日の 晩御飯は 天麩羅よ` { 57 t.Error("Value for id 1 should be `今日の 晩御飯は 天麩羅よ`, but:", value) 58 } else if id == 2 && value != `今日は いい 天気だ` { 59 t.Error("Value for id 2 should be `今日は いい 天気だ`, but:", value) 60 } 61 } 62 63 rows, err = db.Query("SELECT value FROM foo WHERE value MATCH '今日* 天麩羅*'") 64 if err != nil { 65 t.Fatal("Unable to query foo table:", err) 66 } 67 defer rows.Close() 68 69 var value string 70 if !rows.Next() { 71 t.Fatal("Result should be only one") 72 } 73 74 if err := rows.Scan(&value); err != nil { 75 t.Fatal("Unable to scan results:", err) 76 } 77 78 if value != `今日の 晩御飯は 天麩羅よ` { 79 t.Fatal("Value should be `今日の 晩御飯は 天麩羅よ`, but:", value) 80 } 81 82 if rows.Next() { 83 t.Fatal("Result should be only one") 84 } 85} 86 87func TestFTS4(t *testing.T) { 88 tempFilename := TempFilename(t) 89 defer os.Remove(tempFilename) 90 db, err := sql.Open("sqlite3", tempFilename) 91 if err != nil { 92 t.Fatal("Failed to open database:", err) 93 } 94 defer db.Close() 95 96 _, err = db.Exec("DROP TABLE foo") 97 _, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts4(tokenize=unicode61, id INTEGER PRIMARY KEY, value TEXT)") 98 switch { 99 case err != nil && err.Error() == "unknown tokenizer: unicode61": 100 t.Skip("FTS4 not supported") 101 case err != nil: 102 t.Fatal("Failed to create table:", err) 103 } 104 105 _, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 1, `février`) 106 if err != nil { 107 t.Fatal("Failed to insert value:", err) 108 } 109 110 rows, err := db.Query("SELECT value FROM foo WHERE value MATCH 'fevrier'") 111 if err != nil { 112 t.Fatal("Unable to query foo table:", err) 113 } 114 defer rows.Close() 115 116 var value string 117 if !rows.Next() { 118 t.Fatal("Result should be only one") 119 } 120 121 if err := rows.Scan(&value); err != nil { 122 t.Fatal("Unable to scan results:", err) 123 } 124 125 if value != `février` { 126 t.Fatal("Value should be `février`, but:", value) 127 } 128 129 if rows.Next() { 130 t.Fatal("Result should be only one") 131 } 132} 133