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