Function TestNoteFTS

pkg/cli/database/models_test.go:769–851

TestNoteFTS tests that note full text search indices stay in sync with the notes after insert, update and delete

(t *testing.T)

// TestNoteFTS tests that note full text search indices stay in sync with the notes after insert, update and delete
func TestNoteFTS(t *testing.T) {
	// set up
	db := InitTestMemoryDB(t)

	// execute - insert
	n := Note{
		UUID:     "n1-uuid",
		BookUUID: "b1-uuid",
		Body:     "foo bar",
		AddedOn:  1542058875,
		EditedOn: 0,
		USN:      0,
		Deleted:  false,
		Dirty:    false,
	}

	tx, err := db.Begin()
	if err != nil {
		t.Fatal(errors.Wrap(err, "beginning a transaction").Error())
	}

	if err := n.Insert(tx); err != nil {
		tx.Rollback()
		t.Fatal(errors.Wrap(err, "inserting").Error())
	}

	tx.Commit()

	// test
	var noteCount, noteFtsCount, noteSearchCount int
	MustScan(t, "counting notes", db.QueryRow("SELECT count(*) FROM notes"), &noteCount)
	MustScan(t, "counting note_fts", db.QueryRow("SELECT count(*) FROM note_fts"), &noteFtsCount)
	MustScan(t, "counting search results", db.QueryRow("SELECT count(*) FROM note_fts WHERE note_fts MATCH ?", "foo"), &noteSearchCount)

	assert.Equal(t, noteCount, 1, "noteCount mismatch")
	assert.Equal(t, noteFtsCount, 1, "noteFtsCount mismatch")
	assert.Equal(t, noteSearchCount, 1, "noteSearchCount mismatch")

	// execute - update
	tx, err = db.Begin()
	if err != nil {
		t.Fatal(errors.Wrap(err, "beginning a transaction").Error())
	}

	n.Body = "baz quz"
	if err := n.Update(tx); err != nil {
		tx.Rollback()
		t.Fatal(errors.Wrap(err, "updating").Error())
	}

	tx.Commit()

	// test
	MustScan(t, "counting notes", db.QueryRow("SELECT count(*) FROM notes"), &noteCount)
	MustScan(t, "counting note_fts", db.QueryRow("SELECT count(*) FROM note_fts"), &noteFtsCount)
	assert.Equal(t, noteCount, 1, "noteCount mismatch")
	assert.Equal(t, noteFtsCount, 1, "noteFtsCount mismatch")