add initial sketches for Store.Insert, Store.Update

Currently extremely bad performance for some reason. Need to investigate.

1 files changed, 121 insertions, 32 deletions

diff --git a/benchmark/benchmark_test.go b/benchmark/benchmark_test.go
index edebc52..d7399b6 100644
--- a/benchmark/benchmark_test.go
+++ b/benchmark/benchmark_test.go
@@ -16,56 +16,65 @@ import (
 	"go.xyrillian.de/oblast/internal/assert"
 )
 
-const totalRecordCount = 1000
+const totalRecordCountForSelect = 10000
 
-func makeTestDB(t testing.TB) (*sql.DB, error) {
+func makeTestDB(t testing.TB, recordCount int) (*sql.DB, error) {
 	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=memory&cache=shared", t.Name()))
 	if err != nil {
 		return nil, err
 	}
-
-	// fill in some random-looking, but deterministic data
-	_, err = db.Exec(`CREATE TABLE entries (id INTEGER, message TEXT)`)
+	_, err = db.Exec(`CREATE TABLE entries (id INTEGER, message TEXT, PRIMARY KEY (id AUTOINCREMENT))`)
 	if err != nil {
 		return nil, err
 	}
-	stmt, err := db.Prepare(`INSERT INTO entries (id, message) VALUES (?, ?)`)
-	if err != nil {
-		return nil, err
-	}
-	for idx := range totalRecordCount {
-		buf := sha256.Sum256([]byte(strconv.Itoa(idx)))
-		_, err = stmt.Exec(idx, fmt.Sprintf("sha256:%x", buf[:]))
+
+	if recordCount > 0 {
+		// fill in some random-looking, but deterministic data
+		stmt, err := db.Prepare(`INSERT INTO entries (id, message) VALUES (?, ?)`)
+		if err != nil {
+			return nil, err
+		}
+		for idx := range recordCount {
+			buf := sha256.Sum256([]byte(strconv.Itoa(idx)))
+			_, err = stmt.Exec(idx, fmt.Sprintf("sha256:%x", buf[:]))
+			if err != nil {
+				return nil, err
+			}
+		}
+		err = stmt.Close()
 		if err != nil {
 			return nil, err
 		}
-	}
-	err = stmt.Close()
-	if err != nil {
-		return nil, err
 	}
 
 	return db, nil
 }
 
+type OblastEntry struct {
+	ID      int    `db:"id,auto"`
+	Message string `db:"message"`
+}
+
+type GorpEntry struct {
+	ID      int    `db:"id"`
+	Message string `db:"message"`
+}
+
 func BenchmarkSelectMany(b *testing.B) {
-	db, err := makeTestDB(b)
+	db, err := makeTestDB(b, totalRecordCountForSelect)
 	if err != nil {
 		b.Fatal(err)
 	}
 
 	// test with different sizes of resultsets (N=1 is an OLTP-like workload,
 	// then the larger N lean more towards the OLAP side of things)
-	for selectedRecordCount := 1; selectedRecordCount < totalRecordCount; selectedRecordCount *= 10 {
+	for selectedRecordCount := 1; selectedRecordCount < totalRecordCountForSelect; selectedRecordCount *= 10 {
 		b.Run("N="+strconv.Itoa(selectedRecordCount), func(b *testing.B) {
 			// prepare the functions that will be benched
-			type record struct {
-				ID      int    `db:"id"`
-				Message string `db:"message"`
-			}
-			store, err := oblast.NewStore[record](
+			store, err := oblast.NewStore[OblastEntry](
 				oblast.SqliteDialect(),
 				oblast.TableNameIs("entries"),
+				oblast.PrimaryKeyIs("id"),
 			)
 			if err != nil {
 				b.Fatal(err)
@@ -91,7 +100,7 @@ func BenchmarkSelectMany(b *testing.B) {
 			}
 
 			selectWithGorp := func(b *testing.B) {
-				var records []record
+				var records []GorpEntry
 				_, err := gdb.Select(&records, query)
 				if err != nil {
 					b.Error(err)
@@ -158,22 +167,19 @@ func BenchmarkSelectMany(b *testing.B) {
 }
 
 func BenchmarkSelectOne(b *testing.B) {
-	db, err := makeTestDB(b)
+	db, err := makeTestDB(b, totalRecordCountForSelect)
 	if err != nil {
 		b.Fatal(err)
 	}
 
 	// grab a "random" record from the DB, not just the first or the last
-	recordID := min(totalRecordCount*2/3, totalRecordCount)
+	recordID := min(totalRecordCountForSelect*2/3, totalRecordCountForSelect)
 
 	// prepare the functions that will be benched
-	type record struct {
-		ID      int    `db:"id"`
-		Message string `db:"message"`
-	}
-	store, err := oblast.NewStore[record](
+	store, err := oblast.NewStore[OblastEntry](
 		oblast.SqliteDialect(),
 		oblast.TableNameIs("entries"),
+		oblast.PrimaryKeyIs("id"),
 	)
 	if err != nil {
 		b.Fatal(err)
@@ -199,7 +205,7 @@ func BenchmarkSelectOne(b *testing.B) {
 	}
 
 	selectWithGorp := func(b *testing.B) {
-		var r record
+		var r GorpEntry
 		err := gdb.SelectOne(&r, query)
 		if err != nil {
 			b.Error(err)
@@ -248,3 +254,86 @@ func BenchmarkSelectOne(b *testing.B) {
 		}
 	})
 }
+
+func BenchmarkInsertAndDeleteOne(b *testing.B) {
+	db, err := makeTestDB(b, 0)
+	if err != nil {
+		b.Fatal(err)
+	}
+
+	// prepare the functions that will be benched
+	store, err := oblast.NewStore[OblastEntry](
+		oblast.SqliteDialect(),
+		oblast.TableNameIs("entries"),
+		oblast.PrimaryKeyIs("id"),
+	)
+	if err != nil {
+		b.Fatal(err)
+	}
+	gdb := gorp.DbMap{Db: db, Dialect: gorp.SqliteDialect{}}
+	gdb.AddTableWithName(GorpEntry{}, "entries").SetKeys(true, "id")
+
+	insertAndDeleteWithOblast := func(b *testing.B) {
+		record := OblastEntry{Message: "hello"}
+		err := store.Insert(db, &record)
+		if err != nil {
+			b.Error(err)
+		}
+		if record.ID == 0 {
+			b.Errorf("ID was not filled!")
+		}
+		err = store.Delete(db, record)
+		if err != nil {
+			b.Error(err)
+		}
+	}
+	insertAndDeleteWithGorp := func(b *testing.B) {
+		record := GorpEntry{Message: "hello"}
+		err := gdb.Insert(&record)
+		if err != nil {
+			b.Error(err)
+		}
+		if record.ID == 0 {
+			b.Errorf("ID was not filled!")
+		}
+		_, err = gdb.Delete(&record)
+		if err != nil {
+			b.Error(err)
+		}
+	}
+	insertAndDeleteWithSqlite := func(b *testing.B) {
+		result, err := db.Exec(`INSERT INTO entries (message) VALUES (?)`, "hello")
+		if err != nil {
+			b.Error(err)
+		}
+		id, err := result.LastInsertId()
+		if err != nil {
+			b.Error(err)
+		}
+		_, err = db.Exec(`DELETE FROM entries WHERE id = ?`, id)
+		if err != nil {
+			b.Error(err)
+		}
+	}
+
+	// run once to prewarm caches
+	insertAndDeleteWithOblast(b)
+	insertAndDeleteWithGorp(b)
+
+	b.Run("via Gorp", func(b *testing.B) {
+		for range b.N {
+			insertAndDeleteWithGorp(b)
+		}
+	})
+	b.Run("via Oblast", func(b *testing.B) {
+		// TODO: extremely bad results for the insert/delete benchmark -> investigate
+		for range b.N {
+			insertAndDeleteWithOblast(b)
+		}
+	})
+	b.Run("via SQLite", func(b *testing.B) {
+		for range b.N {
+			insertAndDeleteWithSqlite(b)
+		}
+	})
+}