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