Sqlite Fts and Normal Like Test

main.go

package main

import (
	"bufio"
	"database/sql"
	"log"
	"math/rand"
	"os"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB
var wordList []string
var documents []string

func init() {
	var err error
	db, err = sql.Open("sqlite3", "test.db")
	if err != nil {
		panic(err)
	}

	createDocumentTable()
	createFtsTable()
	var documentCount int
	err = db.QueryRow("select count(*) from documents").Scan(&documentCount)
	if err != nil {
		panic(err)
	}

	if documentCount <= 0 {
		loadWords()
		generateDocuments()
		insertDocument()
	}
}

func insertDocument() {
	tx, err := db.Begin()
	if err != nil {
		panic(err)
	}

	stmt, err := tx.Prepare("insert into documents(name) values(?)")
	if err != nil {
		panic(err)
	}

	for _, document := range documents {
		_, err = stmt.Exec(document)
		if err != nil {
			panic(err)
		}
	}
	tx.Commit()

	tx, err = db.Begin()
	if err != nil {
		panic(err)
	}

	stmt, err = tx.Prepare("insert into documents_fts(name) values(?)")
	if err != nil {
		panic(err)
	}

	for _, document := range documents {
		_, err = stmt.Exec(document)
		if err != nil {
			panic(err)
		}
	}
	tx.Commit()
}
func createDocumentTable() {
	sqlStmt := `
	create table if not exists documents (id integer not null primary key, name text);
	`

	_, err := db.Exec(sqlStmt)
	if err != nil {
		panic(err)
	}
	//create index
	sqlStmt = `
	create index if not exists documents_name on documents(name);
	`
	_, err = db.Exec(sqlStmt)
	if err != nil {
		panic(err)
	}

}

func createFtsTable() {
	sqlStmt := `
	create virtual table if not exists documents_fts using fts5(name);
	`
	_, err := db.Exec(sqlStmt)
	if err != nil {
		panic(err)
	}
}

func loadWords() {
	file, err := os.Open("wordlist.txt")
	if err != nil {
		panic(err)
	}
	defer file.Close()

	scanner := bufio.NewScanner(file)
	for scanner.Scan() {
		wordList = append(wordList, scanner.Text())
	}
	if err := scanner.Err(); err != nil {
		panic(err)
	}
}

func generateDocument() string {
	var length = len(wordList)
	var document string
	for i := 0; i < 100; i++ {
		document += wordList[rand.Int()%length] + " "
	}
	return document
}

func generateDocuments() {
	for i := 0; i < 1000000; i++ {
		documents = append(documents, generateDocument())
	}
}

func benchQuery() int64 {
	start := time.Now()
	var document string
	rows, err := db.Query("select name from documents where name like '%yakışıklı operasyonu sudan%' limit 10;")
	if err != nil {
		panic(err)

	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&document)
		if err != nil {
			panic(err)
		}
		println(document)
	}

	err = rows.Err()
	if err != nil {
		panic(err)
	}

	return time.Since(start).Milliseconds()
}

func benchFtsQuery() int64 {
	start := time.Now()
	var document string
	rows, err := db.Query("select name from documents_fts where name match 'yakışıklı operasyonu sudan' limit 10;")
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&document)
		if err != nil {
			panic(err)
		}
		println(document)
	}
	err = rows.Err()
	if err != nil {
		panic(err)
	}
	return time.Since(start).Milliseconds()
}

func main() {

	queryTime := benchQuery()
	ftsQueryTime := benchFtsQuery()

	log.Printf("Query time: %d ms", queryTime)
	log.Printf("Fts Query time: %d ms", ftsQueryTime)

}
//go build --tags "fts5" -ldflags "-s -w" .
//go run --tags "fts5" .