db_test.go

package main

import (
	"context"
	"testing"

	"github.com/jmoiron/sqlx"
	_ "modernc.org/ql/driver"
)

func assertNoError(t *testing.T, err error) {
	if err != nil {
		t.Fatalf("unexpected error: %s", err)
	}
}

func expectNoError(t *testing.T, err error) {
	if err != nil {
		t.Errorf("unexpected error: %s", err)
	}
}

func expectError(t *testing.T, err error) {
	if err == nil {
		t.Error("expected an error")
	}
}

func TestInitSQLdb(t *testing.T) {
	ctx := context.Background()

	db, err := sqlx.Open("ql-mem", "unit-test")
	assertNoError(t, err)
	defer db.Close()

	// Try to init the DB twice. No call should error, and we should have a migrations
	// table afterwards.

	err = initDB(ctx, db)
	assertNoError(t, err)

	err = initDB(ctx, db)
	assertNoError(t, err)

	// Assert that there is one migration
	var count int
	err = db.Get(&count, `SELECT count(*) FROM migrations`)
	assertNoError(t, err)

	if count != 3 {
		t.Errorf("unexpected number of applied migrations: %d, want 3", count)
	}

	// Try to insert the same migration twice, ensure that the second insert fails
	tx, err := db.Beginx()
	assertNoError(t, err)

	_, err = tx.ExecContext(ctx, `INSERT INTO migrations VALUES (?1)`, "test")
	assertNoError(t, err)

	err = tx.Commit()
	assertNoError(t, err)

	tx, err = db.Beginx()
	assertNoError(t, err)

	_, err = tx.ExecContext(ctx, `INSERT INTO migrations VALUES (?1)`, "test")
	if err == nil {
		t.Error("expected an error, got nil")
	}

	err = tx.Rollback()
	assertNoError(t, err)
}

func TestInitialDBStructure(t *testing.T) {
	ctx := context.Background()

	db, err := sqlx.Open("ql-mem", "unit-test")
	assertNoError(t, err)
	defer db.Close()

	// Try to init the DB twice. No call should error, and we should have a migrations
	// table afterwards.

	err = initDB(ctx, db)
	assertNoError(t, err)

	// Run a few tests on the DB structure:
	t.Run("wines-country", func(t *testing.T) {
		// This tests the "country" column on wines
		tx, err := db.Begin()
		assertNoError(t, err)
		defer tx.Rollback() //nolint:errcheck

		// - 2 characters
		_, err = tx.Exec(`INSERT INTO wines (name, country) VALUES (?1, ?2)`, "test-wine", "DE")
		expectNoError(t, err)

		// - more than 2 characters
		_, err = tx.Exec(`INSERT INTO wines (name, country) VALUES (?1, ?2)`, "test-wine", "foobar")
		expectError(t, err)

		// - less than 2 characters
		_, err = tx.Exec(`INSERT INTO wines (name, country) VALUES (?1, ?2)`, "test-wine", "a")
		expectError(t, err)

		// - null
		_, err = tx.Exec(`INSERT INTO wines (name) VALUES (?1)`, "test-wine")
		expectNoError(t, err)
	})

	t.Run("wine-comment", func(t *testing.T) {
		// This test ensures that to insert a comment, there has to be a matching wine entry
		tx, err := db.Begin()
		assertNoError(t, err)
		defer tx.Rollback() //nolint:errcheck

		res, err := tx.ExecContext(ctx, `INSERT INTO wines (name) VALUES (?1)`, "test-wine")
		assertNoError(t, err)

		id, err := res.LastInsertId()
		assertNoError(t, err)

		if id == 0 {
			t.Fatal("unexpected insert id, want anything but 0")
		}

		// - insert a comment with no existing wine
		_, err = tx.ExecContext(ctx, `INSERT INTO comments (content, wine) VALUES(?1, ?2)`, "test!", id+1)
		if err == nil {
			t.Error("expected error when adding comment without wine")
		}

		// - insert comment for the test wine
		_, err = tx.ExecContext(ctx, `INSERT INTO comments (content, wine) VALUES(?1, ?2)`, "test!", id)
		assertNoError(t, err)

		// - delete test wine
		_, err = tx.ExecContext(ctx, `DELETE FROM wines`)
		assertNoError(t, err) // Actually, I want this to error since there's a comment that references this wine.

		// - delete test comment
		_, err = tx.ExecContext(ctx, `DELETE FROM comments`)
		assertNoError(t, err)
	})

	t.Run("state", func(t *testing.T) {
		tx, err := db.Begin()
		assertNoError(t, err)
		defer tx.Rollback() //nolint:errcheck

		// Insert the same key twice
		_, err = tx.Exec(`INSERT INTO state (key, val) VALUES (?1, ?2)`, "test", "fnord")
		expectNoError(t, err)

		_, err = tx.Exec(`INSERT INTO state (key, val) VALUES (?1, ?2)`, "test", "fnord")
		expectError(t, err)
	})

	t.Run("users", func(t *testing.T) {
		// This test ensures that to insert a comment, there has to be a matching wine entry
		tx, err := db.Begin()
		assertNoError(t, err)
		defer tx.Rollback() //nolint:errcheck

		// Insert the same user twice
		_, err = tx.Exec(`INSERT INTO users (name, password) VALUES (?1, ?2)`, "test", "fnord")
		expectNoError(t, err)

		_, err = tx.Exec(`INSERT INTO users (name, password) VALUES (?1, ?2)`, "test", "fnord")
		expectError(t, err)
	})
}