diff options
| -rw-r--r-- | benchmark/benchmark_test.go | 153 | ||||
| -rw-r--r-- | internal/plan.go | 66 | ||||
| -rw-r--r-- | internal/plan_test.go | 46 | ||||
| -rw-r--r-- | oblast.go | 51 | ||||
| -rw-r--r-- | query.go | 120 | ||||
| -rw-r--r-- | select.go | 20 | ||||
| -rw-r--r-- | store.go | 35 |
7 files changed, 390 insertions, 101 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) + } + }) +} diff --git a/internal/plan.go b/internal/plan.go index 7dc3361..f619a5f 100644 --- a/internal/plan.go +++ b/internal/plan.go @@ -22,8 +22,12 @@ type Plan struct { // Argument for reflect.Value.FieldByIndex() for each column name. IndexByColumnName map[string][]int + // In dialects with UsesLastInsertID() == true, whether the ID column must be written with reflect.Value.SetInt() or reflect.Value.SetUint(). + FillIDWithSetUint bool + FillIDWithSetInt bool + // Planned queries. - Select PlannedQuery // only `SELECT ... FROM ...` without WHERE or any of the other clauses + Select PlannedQuery // only `SELECT ... FROM ... WHERE `; user supplies the rest during Select{,One}Where() Insert PlannedQuery Update PlannedQuery Delete PlannedQuery @@ -31,12 +35,12 @@ type Plan struct { // PlannedQuery appears in type Plan. type PlannedQuery struct { - // Empty if the respective query type is not supported by this Plan - // for lack of the required marker types. + // Empty if the respective query type is not supported by this Plan for lack of the required marker types. Query string - // Arguments for reflect.Value.FieldByIndex() in the correct order - // for the query arguments of the above query. + // Arguments for reflect.Value.FieldByIndex() in the correct order for the query arguments of the above query. ArgumentIndexes [][]int + // Arguments for reflect.Value.FieldByIndex() in the correct order for the Scan() arguments of the above query. + ScanIndexes [][]int } // PlanOpts holds additional arguments to BuildPlan(). @@ -118,12 +122,31 @@ func buildPlan(t reflect.Type, dialect Dialect, opts PlanOpts) (Plan, error) { } } - // validation: LastInsertID() only works if at most one column is auto-filled - if dialect.UsesLastInsertID() && len(p.AutoColumnNames) > 1 { - return Plan{}, fmt.Errorf( - "multiple columns are marked as auto-filled (%s), but this SQL dialect only supports at most one per table", - strings.Join(p.AutoColumnNames, ", "), - ) + // validation: LastInsertID() only works if at most one column is auto-filled, and if that column holds an integer type + if dialect.UsesLastInsertID() { + switch len(p.AutoColumnNames) { + case 0: + // nothing to check + case 1: + columnName := p.AutoColumnNames[0] + field := t.FieldByIndex(p.IndexByColumnName[columnName]) + switch field.Type.Kind() { //nolint:exhaustive // false positive + case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64: + p.FillIDWithSetInt = true + case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64: + p.FillIDWithSetUint = true + default: + return Plan{}, fmt.Errorf( + "column is marked as auto-filled (%s), but this SQL dialect only supports auto-filling struct fields with integer types", + strings.Join(p.AutoColumnNames, ", "), + ) + } + default: + return Plan{}, fmt.Errorf( + "multiple columns are marked as auto-filled (%s), but this SQL dialect only supports at most one per table", + strings.Join(p.AutoColumnNames, ", "), + ) + } } // prepare query strings @@ -161,11 +184,11 @@ func (p Plan) buildSelectQueryIfPossible(dialect Dialect) PlannedQuery { } var ( - argumentIndexes = make([][]int, len(p.AllColumnNames)) + scanIndexes = make([][]int, len(p.AllColumnNames)) quotedColumnNames = make([]string, len(p.AllColumnNames)) ) for idx, columnName := range p.AllColumnNames { - argumentIndexes[idx] = p.IndexByColumnName[columnName] + scanIndexes[idx] = p.IndexByColumnName[columnName] quotedColumnNames[idx] = dialect.QuoteIdentifier(columnName) } @@ -174,7 +197,7 @@ func (p Plan) buildSelectQueryIfPossible(dialect Dialect) PlannedQuery { strings.Join(quotedColumnNames, ", "), dialect.QuoteIdentifier(p.TableName), ) - return PlannedQuery{query, argumentIndexes} + return PlannedQuery{query, nil, scanIndexes} } func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery { @@ -188,6 +211,7 @@ func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery { var ( argumentIndexes = make([][]int, len(nonAutoColumnNames)) + scanIndexes [][]int quotedColumnNames = make([]string, len(nonAutoColumnNames)) quotedPlaceholders = make([]string, len(nonAutoColumnNames)) ) @@ -196,6 +220,14 @@ func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery { quotedColumnNames[idx] = dialect.QuoteIdentifier(columnName) quotedPlaceholders[idx] = dialect.Placeholder(idx) } + if len(p.AutoColumnNames) > 0 { + // NOTE: This is filled even if dialect.UsesLastInsertID() is false. + // We need this index to find the right value on which to run SetInt() or SetUint(). + scanIndexes = make([][]int, len(p.AutoColumnNames)) + for idx, columnName := range p.AutoColumnNames { + scanIndexes[idx] = p.IndexByColumnName[columnName] + } + } query := fmt.Sprintf( `INSERT INTO %s (%s) VALUES (%s)`, @@ -206,7 +238,7 @@ func (p Plan) buildInsertQueryIfPossible(dialect Dialect) PlannedQuery { if len(p.AutoColumnNames) > 0 { query += dialect.InsertSuffixForAutoColumns(p.AutoColumnNames) } - return PlannedQuery{query, argumentIndexes} + return PlannedQuery{query, argumentIndexes, scanIndexes} } func (p Plan) buildUpdateQueryIfPossible(dialect Dialect) PlannedQuery { @@ -242,7 +274,7 @@ func (p Plan) buildUpdateQueryIfPossible(dialect Dialect) PlannedQuery { strings.Join(setClauses, ", "), strings.Join(whereClauses, " AND "), ) - return PlannedQuery{query, slices.Concat(setArgumentIndexes, whereArgumentIndexes)} + return PlannedQuery{query, slices.Concat(setArgumentIndexes, whereArgumentIndexes), nil} } func (p Plan) buildDeleteQueryIfPossible(dialect Dialect) PlannedQuery { @@ -264,5 +296,5 @@ func (p Plan) buildDeleteQueryIfPossible(dialect Dialect) PlannedQuery { dialect.QuoteIdentifier(p.TableName), strings.Join(clauses, " AND "), ) - return PlannedQuery{query, argumentIndexes} + return PlannedQuery{query, argumentIndexes, nil} } diff --git a/internal/plan_test.go b/internal/plan_test.go index db12943..e692556 100644 --- a/internal/plan_test.go +++ b/internal/plan_test.go @@ -76,13 +76,17 @@ func TestQueryConstructionBasic(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES ($1, $2) RETURNING "ID"`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}, {2}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}}) assert.Equal(t, plan.Update.Query, `UPDATE "basic_records" SET "Description" = $1, "CreatedAt" = $2 WHERE "ID" = $3`) assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}}) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, `DELETE FROM "basic_records" WHERE "ID" = $1`) assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}}) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) t.Run("SqliteDialect", func(t *testing.T) { @@ -91,13 +95,17 @@ func TestQueryConstructionBasic(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES (?, ?)`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}, {2}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}}) assert.Equal(t, plan.Update.Query, `UPDATE "basic_records" SET "Description" = ?, "CreatedAt" = ? WHERE "ID" = ?`) assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}}) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, `DELETE FROM "basic_records" WHERE "ID" = ?`) assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}}) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) } @@ -116,13 +124,17 @@ func TestQueryConstructionWithoutPrimaryKey(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES ($1, $2)`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, nil) assert.Equal(t, plan.Update.Query, "") assert.DeepEqual(t, plan.Update.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, "") assert.DeepEqual(t, plan.Delete.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) t.Run("SqliteDialect", func(t *testing.T) { @@ -131,13 +143,17 @@ func TestQueryConstructionWithoutPrimaryKey(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?)`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, nil) assert.Equal(t, plan.Update.Query, "") assert.DeepEqual(t, plan.Update.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, "") assert.DeepEqual(t, plan.Delete.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) } @@ -157,12 +173,16 @@ func TestQueryConstructionImpossble(t *testing.T) { assert.Equal(t, plan.Select.Query, "") assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, nil) assert.Equal(t, plan.Insert.Query, "") assert.DeepEqual(t, plan.Insert.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Insert.ScanIndexes, nil) assert.Equal(t, plan.Update.Query, "") assert.DeepEqual(t, plan.Update.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, "") assert.DeepEqual(t, plan.Delete.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) } } @@ -187,13 +207,17 @@ func TestQueryConstructionWithMultiplePrimaryKeyColumns(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "group_id", "name", "created_at" FROM "complex_records" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES ($1, $2, $3)`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, nil) assert.Equal(t, plan.Update.Query, `UPDATE "complex_records" SET "created_at" = $1 WHERE "group_id" = $2 AND "name" = $3`) assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{2}, {0}, {1}}) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, `DELETE FROM "complex_records" WHERE "group_id" = $1 AND "name" = $2`) assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}, {1}}) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) t.Run("SqliteDialect", func(t *testing.T) { @@ -202,13 +226,17 @@ func TestQueryConstructionWithMultiplePrimaryKeyColumns(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "group_id", "name", "created_at" FROM "complex_records" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES (?, ?, ?)`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, nil) assert.Equal(t, plan.Update.Query, `UPDATE "complex_records" SET "created_at" = ? WHERE "group_id" = ? AND "name" = ?`) assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{2}, {0}, {1}}) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, `DELETE FROM "complex_records" WHERE "group_id" = ? AND "name" = ?`) assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}, {1}}) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) } @@ -229,13 +257,17 @@ func TestQueryConstructionWithMultipleAutoColumns(t *testing.T) { t.Error(err) } assert.Equal(t, plan.Select.Query, `SELECT "id", "name", "created_at" FROM "autogenerated_records" WHERE `) - assert.DeepEqual(t, plan.Select.ArgumentIndexes, [][]int{{0}, {1}, {2}}) + assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil) + assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}}) assert.Equal(t, plan.Insert.Query, `INSERT INTO "autogenerated_records" ("name") VALUES ($1) RETURNING "id", "created_at"`) assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}}) + assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}, {2}}) assert.Equal(t, plan.Update.Query, `UPDATE "autogenerated_records" SET "name" = $1, "created_at" = $2 WHERE "id" = $3`) assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}}) + assert.DeepEqual(t, plan.Update.ScanIndexes, nil) assert.Equal(t, plan.Delete.Query, `DELETE FROM "autogenerated_records" WHERE "id" = $1`) assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}}) + assert.DeepEqual(t, plan.Delete.ScanIndexes, nil) }) t.Run("SqliteDialect", func(t *testing.T) { @@ -43,6 +43,7 @@ package oblast // import "go.xyrillian.de/oblast" import ( "database/sql" "errors" + "reflect" "go.xyrillian.de/oblast/internal" ) @@ -79,3 +80,53 @@ var ( // ErrMultipleRows is returned by [Store.SelectOne] if the query returned multiple rows. var ErrMultipleRows = errors.New("sql: multiple rows in result set") + +// Store is the main interface of this library. +// +// It holds information on how to read and write data into record type R, +// and can also be used to execute autogenerated queries if the respective [PlanOption] values were provided during [NewStore]. +type Store[R any] struct { + dialect Dialect + plan internal.Plan +} + +// NewStore initializes a store for record type R. +// Returns an error if R is not a struct type. +// +// For the purpose of loading and storing records (i.e. instances of type R) into the database, +// this function establishes a mapping between fields of type R and database columns by inspecting the "db" tag. +// For example: +// +// type MyRecord struct { +// ID int64 `db:"record_id,auto"` +// Foo string `db:"foo"` +// Bar string +// Cache map[string]any `db:"-"` +// action func() +// } +// +// In this type: +// - The fields "ID" and "Foo" correspond to the database columns "record_id" and "foo" because of the declaration in the "db" tag. +// - The field "Bar" corresponds to the database column "Bar" because, when no "db" tag is given, the column name is set equal to the field name. +// - The field "Cache" is not mapped to any database column because it is declared with a "db" tag of "-". Loads and stores will ignore it. +// - The field "action" is private, so loads and stores will ignore it, too. +// +// Besides the declaration of a column name, the following extra tags are understood (as a comma-separated list following the column name): +// - "auto": During [Store.Insert], do not store this field's value. Instead, the database will auto-generate a value, which will be read back into the record. +func NewStore[R any](dialect Dialect, opts ...PlanOption) (Store[R], error) { + var popts internal.PlanOpts + for _, opt := range opts { + opt(&popts) + } + plan, err := internal.BuildPlan(reflect.TypeFor[R](), dialect, popts) + return Store[R]{dialect, plan}, err +} + +// MustNewStore is like [NewStore], but panics on error. +func MustNewStore[R any](dialect Dialect, opts ...PlanOption) Store[R] { + store, err := NewStore[R](dialect, opts...) + if err != nil { + panic(err.Error()) + } + return store +} diff --git a/query.go b/query.go new file mode 100644 index 0000000..8b0d2cd --- /dev/null +++ b/query.go @@ -0,0 +1,120 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +import ( + "errors" + "fmt" + "reflect" +) + +// Insert executes an SQL INSERT statement for each of the provided records. +// +// Fields that are declared with the "auto" tag will not be written into the DB, +// and instead their value (as auto-generated by the DB on insert) will be placed in the record. +// +// Returns an error if [NewStore] was called without the [TableNameIs] option, which is required to generate a query for this method. +func (s Store[R]) Insert(db Handle, records ...*R) (returnedError error) { + // NOTE: This function body should be as short as possible to reduce the binary size after monomorphization. + // Any expression that does not depend on type R should be factored out into a reusable function. + // TODO: minimize + + if s.plan.Insert.Query == "" { + return errors.New("cannot execute Insert() because query could not be autogenerated") + } + + var ( + argumentIndexes = s.plan.Insert.ArgumentIndexes + argumentSlots = make([]any, len(argumentIndexes)) + scanIndexes = s.plan.Insert.ScanIndexes + scanSlots []any + ) + if len(scanIndexes) > 0 { + scanSlots = make([]any, len(scanIndexes)) + } + + stmt, err := db.Prepare(s.plan.Insert.Query) + if err != nil { + return fmt.Errorf("during Prepare(): %w", err) + } + defer func() { + returnedError = mergeCloseError("Stmt", returnedError, stmt.Close()) + }() + + for idx, r := range records { + v := reflect.ValueOf(r).Elem() + for idx, index := range argumentIndexes { + argumentSlots[idx] = v.FieldByIndex(index).Addr().Interface() + } + + if s.dialect.UsesLastInsertID() { + result, err := stmt.Exec(argumentSlots...) + if err != nil { + return fmt.Errorf("during Exec() for record with idx = %d: %w", idx, err) + } + id, err := result.LastInsertId() + if err != nil { + return fmt.Errorf("during LastInsertId() for record with idx = %d: %w", idx, err) + } + if s.plan.FillIDWithSetInt { + v.FieldByIndex(scanIndexes[0]).SetInt(id) + } else if s.plan.FillIDWithSetUint { + if id < 0 { + return fmt.Errorf("LastInsertId() = %d for record with idx = %d cannot be converted to uint", id, idx) + } + v.FieldByIndex(scanIndexes[0]).SetUint(uint64(id)) + } + } else { + for idx, index := range scanIndexes { + scanSlots[idx] = v.FieldByIndex(index).Addr().Interface() + } + err := stmt.QueryRow(argumentSlots...).Scan(scanSlots...) + if err != nil { + return fmt.Errorf("during QueryRow() for record with idx = %d: %w", idx, err) + } + } + } + + return nil +} + +// TODO: update + +// Delete executes an SQL DELETE statement for each of the provided records, using their primary keys to locate the respective table rows. +// +// Returns an error if [NewStore] was called without the [TableNameIs] or [PrimaryKeyIs] options, which are both required to generate a query for this method. +func (s Store[R]) Delete(db Handle, records ...R) (returnedError error) { + // NOTE: This function body should be as short as possible to reduce the binary size after monomorphization. + // Any expression that does not depend on type R should be factored out into a reusable function. + // TODO: minimize + + if s.plan.Delete.Query == "" { + return errors.New("cannot execute Delete() because query could not be autogenerated") + } + + var ( + argumentIndexes = s.plan.Delete.ArgumentIndexes + argumentSlots = make([]any, len(argumentIndexes)) + ) + + stmt, err := db.Prepare(s.plan.Delete.Query) + if err != nil { + return fmt.Errorf("during Prepare(): %w", err) + } + defer func() { + returnedError = mergeCloseError("Stmt", returnedError, stmt.Close()) + }() + + for idx, r := range records { + v := reflect.ValueOf(&r).Elem() + for idx, index := range argumentIndexes { + argumentSlots[idx] = v.FieldByIndex(index).Addr().Interface() + } + _, err := stmt.Exec(argumentSlots...) + if err != nil { + return fmt.Errorf("during Exec() for record with idx = %d: %w", idx, err) + } + } + return nil +} @@ -25,7 +25,7 @@ func (s Store[R]) Select(db Handle, query string, args ...any) (result []R, retu return nil, err } defer func() { - returnedError = mergeRowsCloseError(returnedError, rows.Close()) + returnedError = mergeCloseError("Rows", returnedError, rows.Close()) }() slots := make([]any, len(indexes)) @@ -63,7 +63,7 @@ func (s Store[R]) SelectWhere(db Handle, partialQuery string, args ...any) (resu return nil, err } defer func() { - returnedError = mergeRowsCloseError(returnedError, rows.Close()) + returnedError = mergeCloseError("Rows", returnedError, rows.Close()) }() slots := make([]any, len(indexes)) @@ -114,11 +114,11 @@ func startSelectQuery(db Handle, plan internal.Plan, query string, args ...any) func startSelectWhereQuery(db Handle, plan internal.Plan, partialQuery string, args ...any) (rows *sql.Rows, indexes [][]int, err error) { if plan.Select.Query == "" { - return nil, nil, errors.New("cannot execute SelectWhere() because SELECT query could not be autogenerated") + return nil, nil, errors.New("cannot execute SelectWhere() because query could not be autogenerated") } query := plan.Select.Query + partialQuery rows, err = db.Query(query, args...) - return rows, plan.Select.ArgumentIndexes, err + return rows, plan.Select.ScanIndexes, err } func collectRow(rows *sql.Rows, v reflect.Value, slots []any, indexes [][]int) error { @@ -132,14 +132,14 @@ func collectRow(rows *sql.Rows, v reflect.Value, slots []any, indexes [][]int) e return nil } -func mergeRowsCloseError(err, closeErr error) error { +func mergeCloseError(typeName string, err, closeErr error) error { switch { case closeErr == nil: return err case err == nil: - return fmt.Errorf("during rows.Close(): %w", closeErr) + return fmt.Errorf("during %s.Close(): %w", typeName, closeErr) default: - return fmt.Errorf("%w (additional error during rows.Close(): %s)", err, closeErr.Error()) + return fmt.Errorf("%w (additional error during %s.Close(): %s)", err, typeName, closeErr.Error()) } } @@ -184,11 +184,11 @@ func (s Store[R]) SelectOneWhere(db Handle, partialQuery string, args ...any) (r func selectOneWhere(db Handle, plan internal.Plan, v reflect.Value, partialQuery string, args []any) error { if plan.Select.Query == "" { - return errors.New("cannot execute SelectOneWhere() because SELECT query could not be autogenerated") + return errors.New("cannot execute SelectOneWhere() because query could not be autogenerated") } query := plan.Select.Query + partialQuery - slots := make([]any, len(plan.Select.ArgumentIndexes)) - for idx, index := range plan.Select.ArgumentIndexes { + slots := make([]any, len(plan.Select.ScanIndexes)) + for idx, index := range plan.Select.ScanIndexes { slots[idx] = v.FieldByIndex(index).Addr().Interface() } return db.QueryRow(query, args...).Scan(slots...) diff --git a/store.go b/store.go deleted file mode 100644 index 4ab0f4b..0000000 --- a/store.go +++ /dev/null @@ -1,35 +0,0 @@ -// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> -// SPDX-License-Identifier: Apache-2.0 - -package oblast - -import ( - "reflect" - - "go.xyrillian.de/oblast/internal" -) - -// Store is the main interface of this library. -// It holds information on how to read and write data into record type R, and can also be used to execute autogenerated queries if the respective [PlanOption]s were provided during [NewStore]. -type Store[R any] struct { - plan internal.Plan -} - -// NewStore initializes a store for record type R. -func NewStore[R any](dialect Dialect, opts ...PlanOption) (Store[R], error) { - var popts internal.PlanOpts - for _, opt := range opts { - opt(&popts) - } - plan, err := internal.BuildPlan(reflect.TypeFor[R](), dialect, popts) - return Store[R]{plan}, err -} - -// MustNewStore is like [NewStore], but panics on error. -func MustNewStore[R any](dialect Dialect, opts ...PlanOption) Store[R] { - store, err := NewStore[R](dialect, opts...) - if err != nil { - panic(err.Error()) - } - return store -} |