keiji/pkg/storage/storage_test.go

package storage

import (
	"database/sql"
	"errors"
	"log"
	"testing"

	_ "github.com/mattn/go-sqlite3"
	"github.com/stretchr/testify/assert"
)

const badPostsTable = `
    CREATE TABLE IF NOT EXISTS posts(
        row INTEGER PRIMARY KEY AUTOINCREMENT
    );
    `
const badImagesTable = `
	CREATE TABLE IF NOT EXISTS images(
		row INTEGER PRIMARY KEY AUTOINCREMENT
	);
	`
const badMenuItemsTable = `
	CREATE TABLE IF NOT EXISTS menu(
		row INTEGER PRIMARY KEY AUTOINCREMENT
	);
	`
const badNavbarItemsTable = `
	CREATE TABLE IF NOT EXISTS navbar(
		row INTEGER PRIMARY KEY AUTOINCREMENT
	);`
const badAssetTable = `
	CREATE TABLE IF NOT EXISTS assets(
		row INTEGER PRIMARY KEY AUTOINCREMENT
	);
	`
const badAdminTable = `
	CREATE TABLE IF NOT EXISTS admin(
		row INTEGER PRIMARY KEY AUTOINCREMENT
	);
	`

var unpopulatedTables = []string{badPostsTable, badImagesTable, badNavbarItemsTable, badMenuItemsTable, badAssetTable, badAdminTable}

/*
creates in memory db and SQLiteRepo struct

	:param tmp: path to the temp directory for the filesystem IO struct to write images to
	:param migrate: choose to 'migrate' the database and create all the tables
*/
func newTestDb(tmp string, migrate bool) (*SQLiteRepo, *sql.DB) {

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	testDb := &SQLiteRepo{db: db, imageIO: FilesystemImageIO{RootDir: tmp}}
	if migrate {
		err = testDb.Migrate(RequiredTables)
	} else {
		err = testDb.Migrate(unpopulatedTables)
	}
	if err != nil {
		log.Fatal("failed to start the test database: ", err)
	}
	return testDb, db
}

func TestMigrate(t *testing.T) {
	requiredTables := []string{
		"posts",
		"images",
		"menu",
		"navbar",
		"assets",
		"admin",
	}

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	testDb := &SQLiteRepo{db: db}
	err = testDb.Migrate(RequiredTables)
	if err != nil {
		t.Error(err)
	}
	for i := range requiredTables {
		name := requiredTables[i]
		row := db.QueryRow("SELECT name FROM sqlite_master WHERE type='table' AND name='?'", name)
		if row.Err() != nil {
			t.Errorf("error querying table: %s", name)
		}
		if row == nil {
			t.Errorf("no table returned: %s", name)
		}
	}

}

func TestGetDropdownElements(t *testing.T) {
	type testcase struct {
		seed []LinkPair
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			seed: []LinkPair{
				{
					Text: "abc123",
					Link: "/abc/123",
				},
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO menu(link, text) VALUES (?,?)")
		for i := range tc.seed {
			_, err := stmt.Exec(tc.seed[i].Link, tc.seed[i].Text)
			if err != nil {
				t.Errorf("failed to seed: %s", err)
			}
		}
		got := testDb.GetDropdownElements()
		assert.Equal(t, got, tc.seed)

	}

}
func TestGetNavBarLinks(t *testing.T) {
	type testcase struct {
		seed []NavBarItem
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			seed: []NavBarItem{
				{
					Link:     "/abc/123",
					Redirect: "/abc/123/site",
					Png:      []byte("xzy123abc098"),
				},
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO navbar(png, link, redirect) VALUES (?,?,?)")
		for i := range tc.seed {
			_, err := stmt.Exec(tc.seed[i].Png, tc.seed[i].Link, tc.seed[i].Redirect)
			if err != nil {
				t.Errorf("failed to seed: %s", err)
			}
		}
		got := testDb.GetNavBarLinks()
		assert.Equal(t, tc.seed, got)

	}
}
func TestGetAssets(t *testing.T) {
	type testcase struct {
		seed []Asset
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			seed: []Asset{
				{
					Data: []byte("abc123xyz098"),
					Name: "asset1",
				},
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO assets(data, name) VALUES (?,?)")
		for i := range tc.seed {
			_, err := stmt.Exec(tc.seed[i].Data, tc.seed[i].Name)
			if err != nil {
				t.Error(err)
			}
		}
		got := testDb.GetAssets()
		assert.Equal(t, tc.seed, got)

	}
}

func TestGetAdminTables(t *testing.T) {

	type testcase struct {
		seed AdminPage
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			seed: AdminPage{
				Tables: map[string][]TableData{
					"test": {
						{
							DisplayName: "abc123",
							Link:        "xyz098",
						},
					},
				},
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO admin(display_name, link, category) VALUES (?,?,?)")
		for k, table := range tc.seed.Tables {
			for i := range table {
				_, err := stmt.Exec(table[i].DisplayName, table[i].Link, k)
				if err != nil {
					t.Error(err)
				}

			}
		}
		got := testDb.GetAdminTables()
		assert.Equal(t, tc.seed, got)

	}
}
func TestGetDocument(t *testing.T) {

	type testcase struct {
		seed Document
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			seed: Document{
				Ident:    Identifier("qwerty"),
				Title:    "abc 123",
				Created:  "2024-12-31",
				Body:     "blog post body etc",
				Category: BLOG,
				Sample:   "this is a sample",
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO posts(id, title, created, body, category, sample) VALUES (?,?,?,?,?,?)")
		_, err := stmt.Exec(tc.seed.Ident, tc.seed.Title, tc.seed.Created, tc.seed.Body, tc.seed.Category, tc.seed.Sample)
		if err != nil {
			t.Error(err)
		}
		got, _ := testDb.GetDocument(Identifier("qwerty"))
		assert.Equal(t, tc.seed, got)

	}
}
func TestGetByCategory(t *testing.T) {

	type testcase struct {
		seed []Document
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			seed: []Document{
				{
					Row:      1,
					Ident:    Identifier("qwerty"),
					Title:    "abc 123",
					Created:  "2024-12-31",
					Body:     "blog post body etc",
					Category: BLOG,
					Sample:   "this is a sample",
				},
				{
					Row:      2,
					Ident:    Identifier("poiuyt"),
					Title:    "abc 123",
					Created:  "2024-12-31",
					Body:     "blog post body etc",
					Category: BLOG,
					Sample:   "this is a sample",
				},
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO posts(id, title, created, body, category, sample) VALUES (?,?,?,?,?,?)")
		for i := range tc.seed {
			_, err := stmt.Exec(tc.seed[i].Ident, tc.seed[i].Title, tc.seed[i].Created, tc.seed[i].Body, tc.seed[i].Category, tc.seed[i].Sample)
			if err != nil {
				t.Error(err)
			}
		}
		got := testDb.GetByCategory(BLOG)
		assert.Equal(t, tc.seed, got)
	}

}
func TestGetImage(t *testing.T) {

	testDb, db := newTestDb(t.TempDir(), true)
	type testcase struct {
		seed       Image
		shouldSeed bool
		err        error
	}
	for _, tc := range []testcase{
		{
			seed: Image{
				Ident:    Identifier("abc123"),
				Title:    "xyz098",
				Desc:     "description",
				Created:  "2024-12-31",
				Category: "homepage",
				Data:     []byte("abc123xyz098"),
			},
			shouldSeed: true,
			err:        nil,
		},
		{
			seed: Image{
				Ident: Identifier("zxcvbnm"),
			},
			shouldSeed: false,
			err:        ErrNotExists,
		},
	} {
		if tc.shouldSeed {
			_, err := db.Exec("INSERT INTO images (id, title, desc, created, category) VALUES (?,?,?,?,?)", string(tc.seed.Ident), tc.seed.Title, tc.seed.Desc, "2024-12-31", tc.seed.Category)
			if err != nil {
				t.Error(err)
			}
			testDb.imageIO.Put(tc.seed.Data, tc.seed.Ident)
		}
		got, err := testDb.GetImage(tc.seed.Ident)
		if err != nil {
			assert.Equal(t, tc.err, err)
		} else {
			assert.Equal(t, tc.seed, got)
		}
	}
}
func TestGetAllImages(t *testing.T) {

	testDb, db := newTestDb(t.TempDir(), true)
	type testcase struct {
		seed []Image
	}
	for _, tc := range []testcase{
		{
			seed: []Image{
				{
					Ident:    Identifier("abc123"),
					Title:    "xyz098",
					Data:     []byte("abc123xyz098"),
					Created:  "2024-12-31",
					Desc:     "description",
					Category: "homepage",
				},
				{
					Ident:    Identifier("xyz098"),
					Title:    "abc123",
					Data:     []byte("abc123xyz098"),
					Created:  "2024-12-31",
					Desc:     "description",
					Category: "homepage",
				},
			},
		},
	} {
		for i := range tc.seed {
			_, err := db.Exec("INSERT INTO images (id, title, desc, created, category) VALUES (?,?,?,?,?)", string(tc.seed[i].Ident), tc.seed[i].Title, tc.seed[i].Desc, tc.seed[i].Created, tc.seed[i].Category)
			if err != nil {
				t.Error(err)
			}
			testDb.imageIO.Put(tc.seed[i].Data, tc.seed[i].Ident)
		}
		got := testDb.GetAllImages()
		for i := range tc.seed {
			tc.seed[i].Data = nil
		}
		assert.Equal(t, tc.seed, got)
	}

}

func TestAllDocuments(t *testing.T) {
	testDb, db := newTestDb(t.TempDir(), true)

	type testcase struct {
		seed []Document
	}
	for _, tc := range []testcase{
		{
			seed: []Document{
				{
					Row:      1,
					Ident:    Identifier("qwerty"),
					Title:    "abc 123",
					Created:  "2024-12-31",
					Body:     "blog post body etc",
					Category: BLOG,
					Sample:   "this is a sample",
				},
				{
					Row:      2,
					Ident:    Identifier("poiuyt"),
					Title:    "abc 123",
					Created:  "2024-12-31",
					Body:     "blog post body etc",
					Category: BLOG,
					Sample:   "this is a sample",
				},
			},
		},
	} {
		stmt, _ := db.Prepare("INSERT INTO posts(id, title, created, body, category, sample) VALUES (?,?,?,?,?,?)")
		for i := range tc.seed {
			_, err := stmt.Exec(tc.seed[i].Ident, tc.seed[i].Title, tc.seed[i].Created, tc.seed[i].Body, tc.seed[i].Category, tc.seed[i].Sample)
			if err != nil {
				t.Error(err)
			}
		}
		got := testDb.AllDocuments()
		assert.Equal(t, tc.seed, got)
	}

}

func TestUpdateDocument(t *testing.T) {
	type testcase struct {
		migrate bool
		seed    Document
		input   Document
		err     error
	}

	for _, tc := range []testcase{
		{
			migrate: true,
			seed: Document{
				Row:      1,
				Ident:    Identifier("qwerty"),
				Title:    "abc 123",
				Created:  "2024-12-31",
				Body:     "blog post body etc",
				Category: BLOG,
				Sample:   "this is a sample",
			},
			input: Document{
				Row:      1,
				Ident:    Identifier("qwerty"),
				Title:    "new title",
				Created:  "2024-12-31",
				Body:     "new updated post that must be reflected after the update",
				Category: BLOG,
				Sample:   "new updated post that must be reflected after the update",
			},
			err: nil,
		},
		{
			migrate: true,
			seed: Document{
				Row:      1,
				Ident:    Identifier("asdf"),
				Title:    "abc 123",
				Created:  "2024-12-31",
				Body:     "blog post body etc",
				Category: BLOG,
				Sample:   "this is a sample",
			},
			input: Document{
				Row:      1,
				Ident:    Identifier("This id does not exist"),
				Title:    "new title",
				Created:  "2024-12-31",
				Body:     "new updated post that must be reflected after the update",
				Category: BLOG,
				Sample:   "new updated post that must be reflected after the update",
			},
			err: ErrNotExists,
		},
		{
			migrate: false, // not creating the database tables so we can error out the SQL statement execution
			seed: Document{
				Row:      1,
				Ident:    Identifier("asdf"),
				Title:    "abc 123",
				Created:  "2024-12-31",
				Body:     "blog post body etc",
				Category: BLOG,
				Sample:   "this is a sample",
			},
			input: Document{
				Row:      1,
				Ident:    Identifier("This id does not exist"),
				Title:    "new title",
				Created:  "2024-12-31",
				Body:     "new updated post that must be reflected after the update",
				Category: BLOG,
				Sample:   "new updated post that must be reflected after the update",
			},
			err: errors.New("no such column: title"),
		},
	} {
		testDb, db := newTestDb(t.TempDir(), tc.migrate)
		if tc.migrate {
			stmt, _ := db.Prepare("INSERT INTO posts(id, title, created, body, category, sample) VALUES (?,?,?,?,?,?)")
			_, err := stmt.Exec(tc.seed.Ident, tc.seed.Title, tc.seed.Created, tc.seed.Body, tc.seed.Category, tc.seed.Sample)
			if err != nil {
				t.Error(err)
			}
		}
		err := testDb.UpdateDocument(tc.input)
		if err != nil {
			assert.Equal(t, tc.err.Error(), err.Error())
		} else {

			row := db.QueryRow("SELECT * FROM posts WHERE id = ?", tc.seed.Ident)
			var got Document
			if err := row.Scan(&got.Row, &got.Ident, &got.Title, &got.Created, &got.Body, &got.Category, &got.Sample); err != nil {
				assert.Equal(t, tc.err, err)
			}
			assert.Equal(t, tc.input, got)
		}

	}
}

func TestAddImage(t *testing.T) {

	type testcase struct {
		data     []byte
		title    string
		desc     string
		category string
		err      error
	}
	testDb, _ := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			data:     []byte("abc123xyz098"),
			title:    "dont matter",
			desc:     "also dont matter",
			category: "homepage",
		},
	} {
		id, err := testDb.AddImage(tc.data, tc.title, tc.desc, tc.category, "image/jpeg")
		if err != nil {
			assert.Equal(t, tc.err, err)
		} else {
			b, err := testDb.imageIO.Get(id)
			if err != nil {
				t.Error(err)
			}
			assert.Equal(t, tc.data, b)
		}

	}

}
func TestAddMenuItem(t *testing.T) {

	type testcase struct {
		input []LinkPair
		err   error
	}
	testDb, _ := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			input: []LinkPair{
				{
					Text: "abc 123",
					Link: "/abc/123",
				},
			},
			err: nil,
		},
	} {
		for i := range tc.input {
			err := testDb.AddMenuItem(tc.input[i])
			if err != nil {
				// assert.Equal(expected, actual)
				assert.Equal(t, tc.err, err)
			}
			rows, err := testDb.db.Query("SELECT * FROM menu")
			var got []LinkPair
			defer rows.Close()
			for rows.Next() {
				var item LinkPair
				var id int
				err = rows.Scan(&id, &item.Link, &item.Text)
				if err != nil {
					t.Errorf("failed: %s", err.Error())
				}
				got = append(got, item)
			}
			assert.Equal(t, tc.input, got)
		}
	}
}
func TestAddNavbarItem(t *testing.T) {
	type testcase struct {
		input []NavBarItem
		err   error
	}
	testDb, _ := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			input: []NavBarItem{
				{
					Redirect: "http://whatever.com",
					Link:     "api/stuff/picture.jpeg",
					Png:      []byte(""),
				},
			},
		},
	} {
		for i := range tc.input {
			err := testDb.AddNavbarItem(tc.input[i])
			if err != nil {
				assert.Equal(t, tc.err, err)
			}

		}
		rows, err := testDb.db.Query("SELECT * FROM navbar")
		var got []NavBarItem
		defer rows.Close()
		for rows.Next() {
			var item NavBarItem
			var id int
			err = rows.Scan(&id, &item.Png, &item.Link, &item.Redirect)
			if err != nil {
				log.Fatal(err)
			}
			got = append(got, item)
		}
		assert.Equal(t, tc.input, got)

	}
}
func TestAddAsset(t *testing.T) {
	type testcase struct {
		input []Asset
		err   error
	}
	testDb, _ := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			input: []Asset{
				{
					Data: []byte(""),
					Name: "",
				},
			},
		},
	} {
		for i := range tc.input {
			err := testDb.AddAsset(tc.input[i].Name, tc.input[i].Data)
			if err != nil {
				assert.Equal(t, tc.err, err)
			}

		}
		rows, err := testDb.db.Query("SELECT * FROM assets")
		var assets []Asset
		defer rows.Close()
		for rows.Next() {
			var item Asset
			var id int
			err = rows.Scan(&id, &item.Name, &item.Data)
			if err != nil {
				log.Fatal(err)
			}
			assets = append(assets, item)
		}
	}
}
func TestAddDocument(t *testing.T) {

	testDb, db := newTestDb(t.TempDir(), true)
	type testcase struct {
		seed Document
		err  error
	}
	for _, tc := range []testcase{
		{
			seed: Document{
				Title:    "abc 123",
				Body:     "blog post body etc",
				Created:  "2024-12-31",
				Category: BLOG,
				Sample:   "this is a sample",
			},
			err: nil,
		},
	} {
		id, err := testDb.AddDocument(tc.seed)
		if err != nil {
			assert.Equal(t, tc.err, err)
		}
		row := db.QueryRow("SELECT * FROM posts WHERE id = ?", id)
		var got Document
		var rowNum int
		if err := row.Scan(&rowNum, &got.Ident, &got.Title, &got.Created, &got.Body, &got.Category, &got.Sample); err != nil {
			assert.Equal(t, tc.err, err)
		}
		want := Document{
			Ident:    id,
			Title:    tc.seed.Title,
			Body:     tc.seed.Body,
			Category: tc.seed.Category,
			Created:  tc.seed.Created,
			Sample:   tc.seed.MakeSample(),
		}

		assert.Equal(t, want, got)
	}
}
func TestAddAdminTableEntry(t *testing.T) {
	type testcase struct {
		input AdminPage
		err   error
	}
	testDb, _ := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			input: AdminPage{
				Tables: map[string][]TableData{
					"test category": {
						{
							DisplayName: "abc 123",
							Link:        "/abc/123",
						},
					},
				},
			},
			err: nil,
		},
	} {
		for ctg, tables := range tc.input.Tables {
			for i := range tables {
				err := testDb.AddAdminTableEntry(tables[i], ctg)
				if err != nil {
					assert.Equal(t, tc.err, err)
				}
			}
		}
		rows, err := testDb.db.Query("SELECT * FROM admin")
		got := AdminPage{Tables: map[string][]TableData{}}
		defer rows.Close()
		for rows.Next() {
			var item TableData
			var id int
			var category string
			err = rows.Scan(&id, &item.DisplayName, &item.Link, &category)
			if err != nil {
				log.Fatal(err)
			}
			got.Tables[category] = append(got.Tables[category], item)

		}
		assert.Equal(t, tc.input, got)
	}
}

func TestDeleteDocument(t *testing.T) {
	type testcase struct {
		input Document
		err   error
	}
	testDb, db := newTestDb(t.TempDir(), true)
	for _, tc := range []testcase{
		{
			input: Document{
				Title:    "abc 123",
				Body:     "blog post body etc",
				Created:  "2024-12-31",
				Category: BLOG,
				Sample:   "this is a sample",
			},
			err: nil,
		},
	} {
		id, err := testDb.AddDocument(tc.input)
		if err != nil {
			t.Error("failed to add doc: ", err)
		}
		err = testDb.DeleteDocument(id)
		if err != nil {
			assert.Equal(t, tc.err, err)
		}
		row, _ := db.Query("SELECT * FROM posts")
		if row.Next() {
			t.Error("Too many rows returned after deleting")
		}

	}
}

func TestGetImageStore(t *testing.T) {

	// testDb, db := newTestDb(t.TempDir(), true)
}
func TestNewIdentifier(t *testing.T) {

	// testDb, db := newTestDb(t.TempDir(), true)
}

func TestImageMediaTypeRoundTrip(t *testing.T) {
	repo, db := newTestDb(t.TempDir(), true)
	defer db.Close()

	id, err := repo.AddImage([]byte("image bytes"), "title", "description", DIGITAL_ART, "image/webp")
	if err != nil {
		t.Fatal(err)
	}
	image, err := repo.GetImage(id)
	if err != nil {
		t.Fatal(err)
	}
	assert.Equal(t, "image/webp", image.MediaType)
	assert.Equal(t, []byte("image bytes"), image.Data)
	image.Data = nil
	assert.Equal(t, []Image{image}, repo.GetAllImages())
	assert.Equal(t, []Image{image}, repo.GetImagesByCategory(DIGITAL_ART))
	assert.Empty(t, repo.GetImagesByCategory(MISC_ASSET))
}

func TestLegacyImagesMigration(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	repo := NewSQLiteRepo(db, FilesystemImageIO{RootDir: t.TempDir()})
	// An extra column and a different physical order simulate an older database.
	_, err = db.Exec(`CREATE TABLE images (
		row INTEGER PRIMARY KEY AUTOINCREMENT,
		id TEXT NOT NULL, legacy_extra TEXT, category TEXT NOT NULL,
		title TEXT NOT NULL, desc TEXT NOT NULL, created TEXT NOT NULL
	)`)
	if err != nil {
		t.Fatal(err)
	}
	_, err = db.Exec(`INSERT INTO images (id, legacy_extra, category, title, desc, created)
		VALUES ('old', 'unused', 'digital_art', 'old title', 'old desc', 'yesterday')`)
	if err != nil {
		t.Fatal(err)
	}
	if err := repo.imageIO.Put([]byte("old bytes"), "old"); err != nil {
		t.Fatal(err)
	}
	for i := 0; i < 2; i++ {
		if err := repo.Migrate(RequiredTables); err != nil {
			t.Fatal(err)
		}
	}
	old, err := repo.GetImage("old")
	if err != nil {
		t.Fatal(err)
	}
	assert.Equal(t, Image{Ident: "old", Title: "old title", Desc: "old desc", Created: "yesterday", Category: DIGITAL_ART, Data: []byte("old bytes")}, old)
	id, err := repo.AddImage([]byte("new bytes"), "new title", "new desc", DIGITAL_ART, "image/png")
	if err != nil {
		t.Fatal(err)
	}
	newImage, err := repo.GetImage(id)
	if err != nil {
		t.Fatal(err)
	}
	assert.Equal(t, "image/png", newImage.MediaType)
	old.Data = nil
	newImage.Data = nil
	assert.Equal(t, []Image{old, newImage}, repo.GetAllImages())
	assert.Equal(t, []Image{old, newImage}, repo.GetImagesByCategory(DIGITAL_ART))
}