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  }