File: sqlite3_fts3_test.go

package info (click to toggle)
golang-github-mattn-go-sqlite3 1.6.0~ds1-2
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, sid
  • size: 452 kB
  • sloc: cpp: 1,132; ansic: 537; makefile: 41
file content (130 lines) | stat: -rw-r--r-- 3,309 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
// Copyright (C) 2015 Yasuhiro Matsumoto <mattn.jp@gmail.com>.
//
// Use of this source code is governed by an MIT-style
// license that can be found in the LICENSE file.

package sqlite3

import (
	"database/sql"
	"os"
	"testing"
)

func TestFTS3(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	db, err := sql.Open("sqlite3", tempFilename)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts3(id INTEGER PRIMARY KEY, value TEXT)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 1, `今日の 晩御飯は 天麩羅よ`)
	if err != nil {
		t.Fatal("Failed to insert value:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 2, `今日は いい 天気だ`)
	if err != nil {
		t.Fatal("Failed to insert value:", err)
	}

	rows, err := db.Query("SELECT id, value FROM foo WHERE value MATCH '今日* 天*'")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var value string

		if err := rows.Scan(&id, &value); err != nil {
			t.Error("Unable to scan results:", err)
			continue
		}

		if id == 1 && value != `今日の 晩御飯は 天麩羅よ` {
			t.Error("Value for id 1 should be `今日の 晩御飯は 天麩羅よ`, but:", value)
		} else if id == 2 && value != `今日は いい 天気だ` {
			t.Error("Value for id 2 should be `今日は いい 天気だ`, but:", value)
		}
	}

	rows, err = db.Query("SELECT value FROM foo WHERE value MATCH '今日* 天麩羅*'")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	var value string
	if !rows.Next() {
		t.Fatal("Result should be only one")
	}

	if err := rows.Scan(&value); err != nil {
		t.Fatal("Unable to scan results:", err)
	}

	if value != `今日の 晩御飯は 天麩羅よ` {
		t.Fatal("Value should be `今日の 晩御飯は 天麩羅よ`, but:", value)
	}

	if rows.Next() {
		t.Fatal("Result should be only one")
	}
}

func TestFTS4(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	db, err := sql.Open("sqlite3", tempFilename)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts4(tokenize=unicode61, id INTEGER PRIMARY KEY, value TEXT)")
	switch {
	case err != nil && err.Error() == "unknown tokenizer: unicode61":
		t.Skip("FTS4 not supported")
	case err != nil:
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 1, `février`)
	if err != nil {
		t.Fatal("Failed to insert value:", err)
	}

	rows, err := db.Query("SELECT value FROM foo WHERE value MATCH 'fevrier'")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	var value string
	if !rows.Next() {
		t.Fatal("Result should be only one")
	}

	if err := rows.Scan(&value); err != nil {
		t.Fatal("Unable to scan results:", err)
	}

	if value != `février` {
		t.Fatal("Value should be `février`, but:", value)
	}

	if rows.Next() {
		t.Fatal("Result should be only one")
	}
}