diff options
Diffstat (limited to 'oblast')
| -rw-r--r-- | oblast/README.md | 41 | ||||
| -rw-r--r-- | oblast/dialect.go | 161 | ||||
| -rw-r--r-- | oblast/errors.go | 28 | ||||
| -rw-r--r-- | oblast/internal/testhelpers/mock/mock.go | 320 | ||||
| -rw-r--r-- | oblast/internal/testhelpers/must/must.go | 26 | ||||
| -rw-r--r-- | oblast/oblast.go | 185 | ||||
| -rw-r--r-- | oblast/plan.go | 488 | ||||
| -rw-r--r-- | oblast/plan_test.go | 657 | ||||
| -rw-r--r-- | oblast/query.go | 340 | ||||
| -rw-r--r-- | oblast/query_test.go | 522 | ||||
| -rw-r--r-- | oblast/select.go | 474 | ||||
| -rw-r--r-- | oblast/select_test.go | 646 |
12 files changed, 3888 insertions, 0 deletions
diff --git a/oblast/README.md b/oblast/README.md new file mode 100644 index 0000000..d4e3b73 --- /dev/null +++ b/oblast/README.md @@ -0,0 +1,41 @@ +<!-- +SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +SPDX-License-Identifier: Apache-2.0 +--> + +# Oblast + +A small ORM library for Go, focused on type safety and performance. Inspired by [Gorp](https://pkg.go.dev/gopkg.in/gorp.v3), but without the bits that make Gorp slow. + +You may think that the name refers to the type of administrative division that exists in several Slavic countries, but it's actually just an acronym for what this library does: **Ob**ject **L**oading **A**nd **St**oring. + +## How to use + +Please refer to the [package documentation](https://pkg.go.dev/go.xyrillian.de/gg/oblast). + +## How to contribute + +Please refer to the [README on module level](https://pkg.go.dev/go.xyrillian.de/gg). + +## Design goals and priorities + +The design goals, ordered by priority (most important comes first), are: + +- An intuitive API that encodes type safety through the use of generics. +- A minimal amount of memory allocations in hot paths. +- A minimal amount of CPU usage. +- As few library dependencies as possible. + +Explicit non-goals include: + +- A fully featured API for query construction: + Oblast does not offer methods like `table.Where("created_at < ?", time.Now()).Order("name").Join("products")`; it only deals with mapping between database columns and fields of struct types, nothing else. + This is not just a question of performance. + The author of this library does not believe that it is worthwhile to have an API like this. + Writing SQL queries by hand is significantly simpler, and does not take away any convenience, except for rare edge cases. +- Support for schema generation or manipulation: + Another thing that the author of this library does not believe to be worthwhile in an ORM library. + In real-world applications, you will need to manage the schema using versioned schema migrations. + Schemas generated by ORM libraries from type declarations cannot really offer this, especially once you get into stored functions, triggers and so on. + +The author realizes that this means that Oblast is technically only an OM library, not an ORM library. Sometimes, optimization means getting rid of on of th lttrs. diff --git a/oblast/dialect.go b/oblast/dialect.go new file mode 100644 index 0000000..11842eb --- /dev/null +++ b/oblast/dialect.go @@ -0,0 +1,161 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +import ( + "database/sql" + "fmt" + "strconv" + "strings" +) + +var ( + // force imports to make docstring links work + _ = sql.Result(nil) +) + +// Dialect accounts for differences between different SQL dialects +// that are relevant to query generation within Oblast. +// +// # Compatibility notice +// +// This interface may be extended, even within minor versions, when doing so is +// required to add support for new DB dialects that differ from previously +// supported dialects in unexpected ways. +type Dialect interface { + // Placeholder returns the placeholder for the i-th query argument. + // Most dialects use "?", but e.g. PostgreSQL uses "$1", "$2" and so on. + // The argument numbers from 0 like a slice index. + Placeholder(i int) string + + // QuoteIdentifier wraps the name of a column or table in quotes, + // in order to avoid the name from being interpreted as a keyword. + QuoteIdentifier(name string) string + + // CanUseLastInsertId returns true if this type of database system can report + // a single auto-generated int primary key using [sql.Result.LastInsertId]. + // If true, the RETURNING clause will be omitted for matching INSERT queries. + CanUseLastInsertId() bool + + // UpsertClause generates an "ON CONFLICT" or similar clause + // that can be appended to an INSERT query to make it fall back to + // behave like UPDATE if a record with the same primary key already exists. + // This is only used for record types that have a primary key. + UpsertClause(pkColumns, otherColumns []string) string + + // String returns a unique identifier for this Dialect instance. + // Different instances shall return the same string only if all their methods behave identically. + // This information is used to cache generated query plans. + String() string +} + +// MariaDBDialect is the dialect of MariaDB 10.5+ databases. +// +// This dialect does NOT support MySQL, as well as ancient MariaDB versions (10.5 was released 2020-06-24), +// because those do not understand the "INSERT ... RETURNING" syntax. +func MariaDBDialect() Dialect { + return mariadbDialect{} +} + +type mariadbDialect struct{} + +func (mariadbDialect) Placeholder(_ int) string { + return "?" +} + +func (mariadbDialect) QuoteIdentifier(name string) string { + return "`" + strings.ReplaceAll(name, "`", "``") + "`" +} + +func (mariadbDialect) CanUseLastInsertId() bool { + return true +} + +func (d mariadbDialect) UpsertClause(pkColumns, otherColumns []string) string { + clauses := make([]string, max(1, len(otherColumns))) + if len(otherColumns) == 0 { + // we need at least one UPDATE clause; if there are no non-PK columns, + // we can just use one of the PK columns, updating those is a safe no-op + clauses[0] = fmt.Sprintf(`%[1]s = VALUES(%[1]s)`, d.QuoteIdentifier(pkColumns[0])) + } else { + for idx, name := range otherColumns { + clauses[idx] = fmt.Sprintf(`%[1]s = VALUES(%[1]s)`, d.QuoteIdentifier(name)) + } + } + return ` ON DUPLICATE KEY UPDATE ` + strings.Join(clauses, ", ") +} + +func (mariadbDialect) String() string { + return "mariadb" +} + +// PostgresDialect is the dialect of PostgreSQL databases. +func PostgresDialect() Dialect { + return postgresDialect{} +} + +type postgresDialect struct{} + +func (postgresDialect) Placeholder(i int) string { + return "$" + strconv.Itoa(i+1) +} + +func (postgresDialect) QuoteIdentifier(name string) string { + return `"` + strings.ReplaceAll(name, `"`, `""`) + `"` +} + +func (postgresDialect) CanUseLastInsertId() bool { + return false +} + +func (d postgresDialect) UpsertClause(pkColumns, otherColumns []string) string { + quotedPkColumns := make([]string, len(pkColumns)) + for idx, name := range pkColumns { + quotedPkColumns[idx] = d.QuoteIdentifier(name) + } + clauses := make([]string, len(otherColumns)) + for idx, name := range otherColumns { + clauses[idx] = fmt.Sprintf(`%[1]s = EXCLUDED.%[1]s`, d.QuoteIdentifier(name)) + } + if len(otherColumns) == 0 { + return fmt.Sprintf(` ON CONFLICT (%s) DO NOTHING`, strings.Join(quotedPkColumns, ", ")) + } else { + return fmt.Sprintf(` ON CONFLICT (%s) DO UPDATE SET %s`, + strings.Join(quotedPkColumns, ", "), strings.Join(clauses, ", ")) + } +} + +func (postgresDialect) String() string { + return "postgres" +} + +// SqliteDialect is the dialect of SQLite 3.35.0+ databases. +// +// This dialect does NOT support ancient SQLite versions (3.35.0 was released 2021-03-12) +// that do not understand the "INSERT ... RETURNING" syntax. +func SqliteDialect() Dialect { + return sqliteDialect{} +} + +type sqliteDialect struct{} + +func (sqliteDialect) Placeholder(_ int) string { + return "?" +} + +func (sqliteDialect) QuoteIdentifier(name string) string { + return `"` + strings.ReplaceAll(name, `"`, `""`) + `"` +} + +func (sqliteDialect) CanUseLastInsertId() bool { + return true +} + +func (sqliteDialect) UpsertClause(pkColumns, otherColumns []string) string { + return postgresDialect{}.UpsertClause(pkColumns, otherColumns) +} + +func (sqliteDialect) String() string { + return "sqliteDialect" +} diff --git a/oblast/errors.go b/oblast/errors.go new file mode 100644 index 0000000..0a58340 --- /dev/null +++ b/oblast/errors.go @@ -0,0 +1,28 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +import ( + "fmt" + "reflect" + "strings" +) + +// MissingRecordError is returned by [Store.Update] if one of the rows to be updated does not exist in the DB. +type MissingRecordError[R any] struct { + // The record that was provided to [Store.Update], + // but for which no row with the same primary key values could be located. + Record R + plan plan +} + +// Error implements the builtin/error interface. +func (e MissingRecordError[R]) Error() string { + keyDescs := make([]string, len(e.plan.PrimaryKeyColumnNames)) + v := reflect.ValueOf(e.Record) + for idx, columnName := range e.plan.PrimaryKeyColumnNames { + keyDescs[idx] = fmt.Sprintf("%s = %#v", columnName, v.FieldByIndex(e.plan.IndexByColumnName[columnName])) + } + return "could not UPDATE record that does not exist in the database: " + strings.Join(keyDescs, ", ") +} diff --git a/oblast/internal/testhelpers/mock/mock.go b/oblast/internal/testhelpers/mock/mock.go new file mode 100644 index 0000000..626366e --- /dev/null +++ b/oblast/internal/testhelpers/mock/mock.go @@ -0,0 +1,320 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package mock + +import ( + "context" + "database/sql/driver" + "errors" + "fmt" + "io" + "reflect" + "slices" + "strings" +) + +//////////////////////////////////////////////////////////////////////////////// +// type Driver + +// Driver is a mock SQL driver that only accepts queries that were preannounced. +type Driver struct { + responseSetsByQuery map[string]*ResponseSet +} + +// assert that interface is implemented +var _ driver.Connector = &Driver{} + +// NewDriver instantiates a new driver. +// The result returns [driver.Connector] and can be given to [sql.OpenDB]. +func NewDriver() *Driver { + return &Driver{ + responseSetsByQuery: make(map[string]*ResponseSet), + } +} + +// Connect implements the [driver.Connector] interface. +func (d *Driver) Connect(ctx context.Context) (driver.Conn, error) { + return &connection{d: d}, nil +} + +// Driver implements the [driver.Connector] interface. +func (d *Driver) Driver() driver.Driver { + // Not needed. Implementing the Driver interface would only be necessary if + // we wanted to use sql.Open() instead of sql.OpenDB(), or if we wanted to + // use sql.DB.Driver(). + panic("unimplemented") +} + +// ForQuery tells the driver to expect the given query string to be sent soon. +// The return value can be used to plan what to return when the query is actually executed. +func (d *Driver) ForQuery(query string) *ResponseSet { + if d.responseSetsByQuery[query] == nil { + d.responseSetsByQuery[query] = &ResponseSet{} + } + return d.responseSetsByQuery[query] +} + +//////////////////////////////////////////////////////////////////////////////// +// type ResponseSet + +// ResponseSet is a set of mock responses for a query sent to type [Driver]. +type ResponseSet struct { + expectedExecs []expectation[Result] + expectedQueries []expectation[Rows] +} + +type expectation[T any] struct { + args []driver.Value + output *T +} + +func newExpectation[T any](args []any) expectation[T] { + e := expectation[T]{ + args: make([]driver.Value, len(args)), + output: new(T), + } + for idx, arg := range args { + var err error + e.args[idx], err = driver.DefaultParameterConverter.ConvertValue(arg) + if err != nil { + panic(fmt.Sprintf("could not convert value %#v into driver.Value: %s", arg, err.Error())) + } + } + return e +} + +// ExpectExecWithArgs plans a response to an Exec() call. +func (rs *ResponseSet) ExpectExecWithArgs(args ...any) *Result { + e := newExpectation[Result](args) + rs.expectedExecs = append(rs.expectedExecs, e) + return e.output +} + +// ExpectQueryWithArgs plans a response to a Query() or QueryRows() call. +func (rs *ResponseSet) ExpectQueryWithArgs(args ...any) *Rows { + e := newExpectation[Rows](args) + rs.expectedQueries = append(rs.expectedQueries, e) + return e.output +} + +//////////////////////////////////////////////////////////////////////////////// +// type connection + +type connection struct { + d *Driver + closed bool +} + +// Prepare implements the [driver.Conn] interface. +func (c *connection) Prepare(query string) (driver.Stmt, error) { + rs := c.d.responseSetsByQuery[query] + if rs == nil { + return nil, fmt.Errorf("unexpected query: %s", query) + } + return &statement{c: c, query: query, rs: rs}, nil +} + +// Close implements the [driver.Conn] interface. +func (c *connection) Close() error { + c.closed = true + return nil +} + +// Begin implements the [driver.Conn] interface. +func (c *connection) Begin() (driver.Tx, error) { + return transaction{}, nil +} + +//////////////////////////////////////////////////////////////////////////////// +// type transaction + +type transaction struct{} + +// Commit implements the [driver.Tx] interface. +func (t transaction) Commit() error { + return nil // unused +} + +// Rollback implements the [driver.Tx] interface. +func (t transaction) Rollback() error { + return nil // unused +} + +//////////////////////////////////////////////////////////////////////////////// +// type statement + +type statement struct { + c *connection + query string + rs *ResponseSet + closed bool +} + +// Close implements the [driver.Stmt] interface. +func (s *statement) Close() error { + return nil +} + +// NumInput implements the [driver.Stmt] interface. +func (s *statement) NumInput() int { + // option 1: when using SQLite dialect, count `?` + count := strings.Count(s.query, "?") + if count > 0 { + return count + } + + // option 2: when using PostgreSQL dialect, find `$1`, `$2`, etc. + for strings.Contains(s.query, fmt.Sprintf("$%d", count+1)) { + count++ + } + return count +} + +// Exec implements the [driver.Stmt] interface. +func (s *statement) Exec(args []driver.Value) (driver.Result, error) { + if s.closed { + return nil, errors.New("statement was closed") + } + if s.c.closed { + return nil, errors.New("connection was closed") + } + for idx, e := range s.rs.expectedExecs { + if reflect.DeepEqual(e.args, args) { + s.rs.expectedExecs = slices.Delete(s.rs.expectedExecs, idx, idx+1) + return result{r: *e.output}, nil + } + } + return nil, fmt.Errorf("unexpected arguments for query %q: %#v", s.query, args) +} + +// Query implements the [driver.Stmt] interface. +func (s *statement) Query(args []driver.Value) (driver.Rows, error) { + if s.closed { + return nil, errors.New("statement was closed") + } + if s.c.closed { + return nil, errors.New("connection was closed") + } + for idx, e := range s.rs.expectedQueries { + if reflect.DeepEqual(e.args, args) { + s.rs.expectedQueries = slices.Delete(s.rs.expectedQueries, idx, idx+1) + return &rows{r: *e.output}, nil + } + } + return nil, fmt.Errorf("unexpected arguments for query %q: %#v", s.query, args) +} + +/////////////////////////////////////////////////////////////////////////////////////////// +// type Result + +// Result is a mock response for an Exec() call. +// It is constructed by [ResponseSet.ExpectExec]. +type Result struct { + lastInsertId *int64 + rowsAffected *int64 +} + +// AndReturnLastInsertId configures a mock LastInsertId() value for this Result. +// Returns the same Result instance to allow chaining additional method calls. +func (r *Result) AndReturnLastInsertId(id int64) *Result { + r.lastInsertId = &id + return r +} + +// AndReturnRowsAffected configures a mock RowsAffected() value for this Result. +// Returns the same Result instance to allow chaining additional method calls. +func (r *Result) AndReturnRowsAffected(count int64) *Result { + r.rowsAffected = &count + return r +} + +type result struct { + r Result +} + +// LastInsertId implements the [driver.Result] interface. +func (r result) LastInsertId() (int64, error) { + if r.r.lastInsertId == nil { + return 0, errors.New("AndReturnLastInsertId() was not called for this Result") + } + return *r.r.lastInsertId, nil +} + +// RowsAffected implements the [driver.Result] interface. +func (r result) RowsAffected() (int64, error) { + if r.r.rowsAffected == nil { + return 0, errors.New("AndReturnRowsAffected() was not called for this Result") + } + return *r.r.rowsAffected, nil +} + +// ///////////////////////////////////////////////////////////////////////////////////////// +// type Rows + +// Rows is a mock response for a Query() or QueryRow() call. +// It is constructed by [ResponseSet.ExpectQuery]. +type Rows struct { + columns []string + results [][]any + closeError error +} + +// AndReturnColumns configures the set of column names that will be returned by this query. +// Returns the same Result instance to allow chaining additional method calls. +func (r *Rows) AndReturnColumns(columns ...string) *Rows { + if len(r.columns) > 0 { + panic("AndReturnColumns() called multiple times for the same Rows object") + } + r.columns = columns + return r +} + +// WithRow adds a row to the result set that will be returned by this query. +// This may only be called after AndReturnColumns(). +func (r *Rows) WithRow(values ...any) *Rows { + if len(r.columns) == 0 { + panic("AndReturnColumns() has not been called for this Rows object yet") + } + if len(r.columns) != len(values) { + panic("WithRow() must be called with the same number of args as the preceding AndReturnColumns() call") + } + r.results = append(r.results, values) + return r +} + +// AndCloseFailsWith sets up Close() for this Rows to fail with the provided error message. +func (r *Rows) AndCloseFailsWith(err error) { + r.closeError = err +} + +type rows struct { + r Rows + closed bool +} + +// Columns implements the [driver.Rows] interface. +func (r *rows) Columns() []string { + return r.r.columns +} + +// Close implements the [driver.Rows] interface. +func (r *rows) Close() error { + r.closed = true + return r.r.closeError +} + +// Next implements the [driver.Rows] interface. +func (r *rows) Next(dest []driver.Value) error { + if r.closed { + return errors.New("rows object was closed") + } + if len(r.r.results) == 0 { + return io.EOF + } + for idx, value := range r.r.results[0] { + dest[idx] = value + } + r.r.results = r.r.results[1:] + return nil +} diff --git a/oblast/internal/testhelpers/must/must.go b/oblast/internal/testhelpers/must/must.go new file mode 100644 index 0000000..7a137c6 --- /dev/null +++ b/oblast/internal/testhelpers/must/must.go @@ -0,0 +1,26 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package must + +import "testing" + +// Succeed fails the test if err is not nil. +func Succeed(t testing.TB, err error) { + t.Helper() + if err != nil { + t.Fatal(err.Error()) + } +} + +// Return wraps a function returning two output values, +// and either forwards the result value on success, or fails the test on error. +func Return[V any](value V, err error) func(testing.TB) V { + return func(t testing.TB) V { + t.Helper() + if err != nil { + t.Fatal(err.Error()) + } + return value + } +} diff --git a/oblast/oblast.go b/oblast/oblast.go new file mode 100644 index 0000000..57d0c63 --- /dev/null +++ b/oblast/oblast.go @@ -0,0 +1,185 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +// Package oblast is an ORM library for Go, focusing specifically on just the loading and storing of records in the most efficient manner possible. +// No utilities are provided for generating DDL or managing schema migrations, or for building complex OLAP queries. +// +// # Usage pattern +// +// Oblast can load or store any struct type by matching individual fields to column names (on load) or query arguments (on store). +// Struct types that are suitable for this kind of mapping are called "record types" throughout this package documentation. +// +// To use this library, first declare a record type, and create a [Store] for it once to analyze the type and prepare the respective OLTP queries: +// +// type LogEntry struct { +// ID int64 `db:"id,auto"` +// CreatedAt time.Time `db:"created_at"` +// Message string `db:"message"` +// } +// var logEntryStore = oblast.NewStore[LogEntry]( +// oblast.PostgresDialect(), +// oblast.TableNameIs("log_entries"), +// oblast.PrimaryKeyIs("id"), +// ) +// +// Then use it many times to perform load and store operations: +// +// func doStuff(db *gsql.DB) error { +// newEntry := LogEntry{ +// CreatedAt: time.Now(), +// Message: "Hello World.", +// } +// err := logEntryStore.Insert(dbh, &newEntry) +// if err != nil { +// return err +// } +// fmt.Printf("created log entry %d", newEntry.ID) +// +// allEntries, err := logEntryStore.SelectWhere(dbh, `created_at < NOW()`) +// if err != nil { +// return err +// } +// fmt.Printf("there are %d log entries so far", len(allEntries)) +// } +// +// In this example, [*gsql.DB] is a thin wrapper around [*sql.DB], which can be obtained with the [gsql.NewDB] function. +// A [*gsql.DB] can be used in the same way as an [*sql.DB], but if Oblast is only to be used for specific functions, +// then individual [*sql.Conn] or [*sql.Tx] instances can also be wrapped with the [gsql.NewConn] and [gsql.NewTx] functions. +// +// The gsql package serves as an abstraction around different database driver libraries, +// allowing Oblast to also be used with different database drivers such as pgx (see documentation in package gsql for details). +// +// # Mapping rules for record types +// +// If the database column has a different name (or casing, e.g. "id" vs. "ID") than the field name, provide it in the field tag "db". +// The field tag may also contain additional options, separated from the column name by commas. +// To have Oblast ignore a field, either make it private or declare its column name as "-". +// For example: +// +// type Example struct { +// FirstValue string `db:"first_value"` // maps to DB column "first_value" +// SecondValue string // maps to DB column "SecondValue" +// ThirdValue string `db:"third_value,auto"` // maps to DB column "third_value" with "auto" option +// FourthValue string `db:",auto"` // maps to DB column "FourthValue" with "auto" option +// Cache map[string]any `db:"-"` // ignored by Oblast because of column name "-" +// action func() // ignored by Oblast because field is private +// } +// +// The following field options are understood: +// - "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. In SQL dialects that use [sql.Result.LastInsertId] for this (as opposed to a RETURNING clause), only at most one field per record type may have this option, and it must be of an integer type. +// +// It is possible to place mapped fields within sub-structs, including within embedded types. +// This is useful e.g. to avoid code duplication for database columns that are repeated across multiple types: +// +// type Timestamps struct { +// CreatedAt time.Time `db:"created_at"` +// UpdatedAt *time.Time `db:"updated_at"` +// DeletedAt *time.Time `db:"deleted_at"` +// } +// +// type FooRecord struct { +// ID int64 `db:"id,auto"` +// Name string `db:"name"` +// Timestamps Timestamps +// } +// // ... and other struct types that use type Timestamps ... +// +// This behavior may be undesirable on custom struct types that implement [sql.Scanner] and/or [driver.Valuer], or are understood by a [driver.NamedValueChecker] set up by your SQL driver. +// To keep Oblast from recursing into struct types and mapping their fields, provide an explicit `db:"..."` tag on them: +// +// type GeoPoint struct { +// Longitude, Latitude int +// } +// func (p *GeoPoint) Scan(src any) error {...} +// func (p GeoPoint) Value() (driver.Value, error) {...} +// +// type Event struct { +// ID int64 `db:",auto"` +// Description string +// Time time.Time +// // explicit tag ensures that Location.Longitude and Location.Latitude are not mapped individually +// Location GeoPoint `db:"Location"` +// } +package oblast // import "go.xyrillian.de/oblast" + +import ( + "database/sql" + "database/sql/driver" + "fmt" + "reflect" + + "go.xyrillian.de/gg/gsql" +) + +var ( + // the following types appear in docstring links + _ sql.Scanner = nil + _ driver.NamedValueChecker = nil + _ *gsql.DB = nil +) + +// PlanOption is an option that can be given to [NewStore] to influence query planning for a certain type of record. +type PlanOption func(*planOpts) + +// TableNameIs is a PlanOption for record types that correspond to exactly one database table (as opposed to a join of multiple tables). +// This option is required to enable any of the methods of [Store] that use partially or fully auto-generated query strings. +func TableNameIs(name string) PlanOption { + return func(opts *planOpts) { opts.TableName = name } +} + +// PrimaryKeyIs is a PlanOption for record types that correspond to a database table with a primary key. +// This option is required to enable use of the [Store.Update] and [Store.Delete] methods. +func PrimaryKeyIs(columnNames ...string) PlanOption { + return func(opts *planOpts) { opts.PrimaryKeyColumnNames = columnNames } +} + +// StructTagKeyIs is a PlanOption for record types that allows renaming the struct tag key that Oblast inspects from its default value of "db". +// For example, providing StructTagKeyIs("oblast") means that a struct tag like `db:",auto"` must be written as `oblast:",auto"` instead. +// +// This is useful when migrating from or to another ORM library that uses the same `db:"..."` tag as Oblast, but with conflicting semantics. +func StructTagKeyIs(key string) PlanOption { + return func(opts *planOpts) { opts.StructTagKey = key } +} + +// ReadOnly is a PlanOption that disables all write operations for the resulting [Store] type +// (i.e., [Store.Insert], [Store.Update], [Store.Upsert] and [Store.Delete]). +// Besides read-only tables (i.e. tables where the current user lacks write permissions), +// this is useful for record types that only model a few columns of a table and which, +// when used in write operations, might result in incomplete records. +func ReadOnly() PlanOption { + return func(opts *planOpts) { opts.ReadOnly = true } +} + +// Store 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 { + plan plan +} + +// NewStore initializes a store for record type R. +// Returns an error if R is not a struct type. +// +// In most situations, the intended usage pattern is to call NewStore (or [MustNewStore]) once per record type, +// and hold the result in a global variable. +// +// When dealing with private one-off record types that are declared within the function or method using them, +// NewStore (or [MustNewStore]) may also be called once per function call. +// NewStore will internally cache its results and return a cheap copy on subsequent calls with the same arguments, +// only incurring the cost of a read lock on a mutex. +func NewStore[R any](dialect Dialect, opts ...PlanOption) (Store[R], error) { + plan, err := getOrBuildPlan(reflect.TypeFor[R](), dialect, collectPlanOptions(opts)) + if err != nil { + var zero R + return Store[R]{}, fmt.Errorf("cannot use type %T for queries: %w", zero, err) + } + 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 +} diff --git a/oblast/plan.go b/oblast/plan.go new file mode 100644 index 0000000..6b5f002 --- /dev/null +++ b/oblast/plan.go @@ -0,0 +1,488 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +import ( + "cmp" + "errors" + "fmt" + "reflect" + "slices" + "strings" + "sync" +) + +// planOpts holds additional arguments to buildPlan(). +type planOpts struct { + ReadOnly bool + StructTagKey string // defaults to "db" + TableName string + PrimaryKeyColumnNames []string +} + +func collectPlanOptions(popts []PlanOption) planOpts { + opts := planOpts{ + StructTagKey: "db", + } + for _, popt := range popts { + popt(&opts) + } + return opts +} + +type planCacheKey struct { + Type reflect.Type + Dialect string + ReadOnly bool + StructTagKey string + TableName string + PrimaryKeyColumnNames string +} + +var ( + generatedPlans = make(map[planCacheKey]plan) + generatedPlansMutex sync.RWMutex + generatedTuplePlans = make(map[reflect.Type]plan) + generatedTuplePlansMutex sync.Mutex +) + +// getOrBuildPlan is like [buildPlan], but caches generated plans and tries to reuse cached plans. +func getOrBuildPlan(t reflect.Type, dialect Dialect, opts planOpts) (plan, error) { + key := planCacheKey{ + Type: t, + Dialect: dialect.String(), + ReadOnly: opts.ReadOnly, + StructTagKey: opts.StructTagKey, + TableName: opts.TableName, + PrimaryKeyColumnNames: strings.Join(opts.PrimaryKeyColumnNames, "\000"), + } + + generatedPlansMutex.RLock() + p, ok := generatedPlans[key] + generatedPlansMutex.RUnlock() + if ok { + return p, nil + } + + p, err := buildPlan(t, dialect, opts) + if err != nil { + return plan{}, err + } + generatedPlansMutex.Lock() + generatedPlans[key] = p + generatedPlansMutex.Unlock() + return p, nil +} + +// getOrBuildTuplePlan is like [getOrBuildPlan], but generates the plans used by TupleSelect() et al +func getOrBuildTuplePlan(t reflect.Type) plan { + generatedTuplePlansMutex.Lock() + defer generatedTuplePlansMutex.Unlock() + + p, ok := generatedTuplePlans[t] + if !ok { + indexes := make([][]int, t.NumField()) + for idx := range indexes { + indexes[idx] = []int{idx} + } + p = plan{ + TypeName: t.Name(), + StaticIndexes: indexes, + } + generatedTuplePlans[t] = p + } + return p +} + +// plan holds all information that we can derive from reflecting on a given type. +// The queries held within are only valid within the context of a given SQL dialect. +type plan struct { + TypeName string // for use in error messages + TableName string // from info.TableNameIs marker (if any) + AllColumnNames []string // in order of struct fields (not set for TupleSelect() plans) + PrimaryKeyColumnNames []string // from info.PrimaryKeyIs marker (if any) + AutoColumnNames []string // subset of AllColumnNames where field has `,auto` marker + + // Field index (i.e. argument for reflect.Value.FieldByIndex()) for each column name. Not set for TupleSelect() plans. + IndexByColumnName map[string][]int + // Select indexes for TupleSelect() plans. Always of the form [[0], [1], [2], ..., [N]]. Not set for regular plans. + StaticIndexes [][]int + // Pointer-typed fields that need to be initialized before scanning into this type. + TransparentPointerStructFields []fieldInfo + + // Whether the INSERT query uses QueryRow or Exec. + // - When no auto-generated values are collected, or when a single value can be collected through LastInsertId(), + // this will be false because Exec() is more memory-efficient than QueryRow(); it does not have to allocate an *sql.Rows instance. + // - Otherwise, i.e. when auto-generated values are collected with a RETURNING clause, + // this will be true because Exec() does not support scanning result values. + InsertUsesQueryRow bool + // If InsertUsesQueryRow = false and a primary key is collected from LastInsertId(), + // this decides whether we write it with reflect.Value.SetInt() or reflect.Value.SetUint(). + LastInsertIdIsUnsigned bool + + // Planned queries. + Select plannedQuery // only `SELECT ... FROM ... WHERE `; user supplies the rest during Select{,One}Where() + Insert plannedQuery + Upsert plannedQuery + Update plannedQuery + Delete plannedQuery + + // Whether Insert/Upsert/Update/Delete query planning was inhibited by the ReadOnly option. + // This information is preserved in order to render more useful error messages. + ReadOnly bool +} + +// fieldInfo appears in type plan. +type fieldInfo struct { + Name string + Index []int + ContainsPrimaryKey bool +} + +// 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. + Query string + // 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 +} + +// buildPlan creates a new plan for the given struct type. +func buildPlan(t reflect.Type, dialect Dialect, opts planOpts) (plan, error) { + if t.Kind() != reflect.Struct { + return plan{}, fmt.Errorf("expected struct type, but got kind %q", t.Kind().String()) + } + + var p = plan{ + TypeName: t.Name(), + TableName: opts.TableName, + PrimaryKeyColumnNames: opts.PrimaryKeyColumnNames, + IndexByColumnName: make(map[string][]int), + ReadOnly: opts.ReadOnly, + } + + var ( + indexesOfOpaqueStructs [][]int + indexesOfUnusedTransparentStructs [][]int + ) + isWithin := func(fieldIndex, structIndex []int) bool { + // returns whether `structIndex` is a prefix of `fieldIndex` (i.e. whether the field is contained within the struct) + return len(fieldIndex) > len(structIndex) && slices.Equal(fieldIndex[0:len(structIndex)], structIndex) + } + + // discover addressable fields in this type, collect information from markers and tags + for _, field := range assignableFields(t) { + // recurse into struct fields (i.e. ignore the struct itself and consider its members instead) + // unless the field itself has a `db:"..."` tag + if field.Type.Kind() == reflect.Struct || (field.Type.Kind() == reflect.Pointer && field.Type.Elem().Kind() == reflect.Struct) { + if field.Tag.Get(opts.StructTagKey) == "" { + indexesOfUnusedTransparentStructs = append(indexesOfUnusedTransparentStructs, field.Index) + if field.Type.Kind() == reflect.Pointer { + // remember that, when scanning into a record of type `t`, we need to write a non-nil zeroed struct into this field + // to enable taking an address of its mapped member fields + p.TransparentPointerStructFields = append(p.TransparentPointerStructFields, fieldInfo{ + Name: field.Name, + Index: field.Index, + ContainsPrimaryKey: false, // might be set later + }) + } + continue + } + indexesOfOpaqueStructs = append(indexesOfOpaqueStructs, field.Index) + } + + // ignore fields that are within a struct type that is mapped as a whole + if slices.ContainsFunc(indexesOfOpaqueStructs, func(index []int) bool { + return isWithin(field.Index, index) + }) { + continue + } + + // check `db:"..."` tag, ignore fields that are declared with column name "-" + tags := strings.Split(strings.TrimSpace(field.Tag.Get(opts.StructTagKey)), ",") + columnName, extraTags := cmp.Or(tags[0], field.Name), tags[1:] + if columnName == "-" { + continue + } + + if otherIndex := p.IndexByColumnName[columnName]; otherIndex != nil { + return plan{}, fmt.Errorf( + "duplicate tag `%s:%q` on field index %v, but also on field index %v", + opts.StructTagKey, columnName, otherIndex, field.Index, + ) + } + p.IndexByColumnName[columnName] = field.Index + p.AllColumnNames = append(p.AllColumnNames, columnName) + + // track whether transparent structs contain fields that are mapped + restartIteration: + for idx, index := range indexesOfUnusedTransparentStructs { + if isWithin(field.Index, index) { + indexesOfUnusedTransparentStructs = slices.Delete(indexesOfUnusedTransparentStructs, idx, idx+1) + goto restartIteration + } + } + + // track which transparent pointer structs contain PK fields + if slices.Contains(p.PrimaryKeyColumnNames, columnName) { + for idx, tpsField := range p.TransparentPointerStructFields { + if isWithin(field.Index, tpsField.Index) { + p.TransparentPointerStructFields[idx].ContainsPrimaryKey = true + } + } + } + + for _, tag := range extraTags { + switch tag { + case "auto": + p.AutoColumnNames = append(p.AutoColumnNames, columnName) + default: + return plan{}, fmt.Errorf("unknown option `%s:%q` on field %q", opts.StructTagKey, ","+tag, field.Name) + } + } + } + + // validation: transparent structs need to have at least one of their members mapped + // (this property is most often violated when a user of a library-defined type is not aware that this type is a struct under the hood, + // e.g. a field like "CreatedAt time.Time" needs to have a tag like `db:"created_at"`, + // otherwise nothing will be mapped because time.Time does not have any exported fields) + for _, index := range indexesOfUnusedTransparentStructs { + field := t.FieldByIndex(index) + return plan{}, fmt.Errorf( + "field %q of type %s does not contain any mapped fields (to map this whole field to a DB column, add an explicit `%s:\"...\"` tag)", + field.Name, field.Type.String(), opts.StructTagKey, + ) + } + + // validation: defining a primary key only makes sense for records that map onto a single table + if len(p.PrimaryKeyColumnNames) > 0 && p.TableName == "" { + return plan{}, errors.New("cannot declare a primary key without also providing the TableNameIs option") + } + + // validation: oblast.PrimaryKeyInfo must refer to columns that exist + for _, columnName := range p.PrimaryKeyColumnNames { + _, ok := p.IndexByColumnName[columnName] + if !ok { + return plan{}, fmt.Errorf("no field has tag `%s:%q`, but a field of this name was declared in the primary key", opts.StructTagKey, columnName) + } + } + + // pick strategy for INSERT + if p.TableName != "" { + switch len(p.AutoColumnNames) { + case 0: + p.InsertUsesQueryRow = false + case 1: + if dialect.CanUseLastInsertId() { + 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.InsertUsesQueryRow = false + p.LastInsertIdIsUnsigned = false + case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64: + p.InsertUsesQueryRow = false + p.LastInsertIdIsUnsigned = true + default: + p.InsertUsesQueryRow = true + } + } else { + p.InsertUsesQueryRow = true + } + default: + p.InsertUsesQueryRow = true + } + } + + // prepare query strings + p.Select = p.buildSelectQueryIfPossible(dialect) + if !opts.ReadOnly { + p.Insert = p.buildInsertQueryIfPossible(dialect, false) + p.Upsert = p.buildInsertQueryIfPossible(dialect, true) + p.Update = p.buildUpdateQueryIfPossible(dialect) + p.Delete = p.buildDeleteQueryIfPossible(dialect) + } + + return p, nil +} + +// Like reflect.VisibleFields(), but considers all fields within the type that +// are assignable (i.e. `v.FieldByIndex(...).Set(...)` does not panic). +func assignableFields(t reflect.Type) (result []reflect.StructField) { + for field := range t.Fields() { + // assignment is allowed for exported or embedded fields only + if field.IsExported() || field.Anonymous { + result = append(result, field) + + // recurse into struct fields + ft := field.Type + if ft.Kind() == reflect.Pointer { + ft = ft.Elem() + } + if ft.Kind() == reflect.Struct { + for _, subfield := range assignableFields(ft) { + subfield.Index = append(slices.Clone(field.Index), subfield.Index...) + result = append(result, subfield) + } + } + } + } + + return result +} + +func (p plan) getNonAutoColumnNames() []string { + result := make([]string, 0, len(p.AllColumnNames)-len(p.AutoColumnNames)) + for _, columnName := range p.AllColumnNames { + if !slices.Contains(p.AutoColumnNames, columnName) { + result = append(result, columnName) + } + } + return result +} + +func (p plan) getNonPrimaryKeyColumnNames() []string { + result := make([]string, 0, len(p.AllColumnNames)-len(p.PrimaryKeyColumnNames)) + for _, columnName := range p.AllColumnNames { + if !slices.Contains(p.PrimaryKeyColumnNames, columnName) { + result = append(result, columnName) + } + } + return result +} + +func (p plan) buildSelectQueryIfPossible(dialect Dialect) plannedQuery { + if p.TableName == "" { + return plannedQuery{Query: ""} + } + + var ( + scanIndexes = make([][]int, len(p.AllColumnNames)) + quotedColumnNames = make([]string, len(p.AllColumnNames)) + ) + for idx, columnName := range p.AllColumnNames { + scanIndexes[idx] = p.IndexByColumnName[columnName] + quotedColumnNames[idx] = dialect.QuoteIdentifier(columnName) + } + + query := fmt.Sprintf( + `SELECT %s FROM %s WHERE `, + strings.Join(quotedColumnNames, ", "), + dialect.QuoteIdentifier(p.TableName), + ) + return plannedQuery{query, nil, scanIndexes} +} + +func (p plan) buildInsertQueryIfPossible(dialect Dialect, isUpsert bool) plannedQuery { + if p.TableName == "" || len(p.AllColumnNames) == 0 { + return plannedQuery{Query: ""} + } + nonAutoColumnNames := p.getNonAutoColumnNames() + if len(nonAutoColumnNames) == 0 { + return plannedQuery{Query: ""} + } + + // UPSERT queries specifically are only generated if we have non-auto primary keys: + // - cannot hit a key conflict if there are no keys + // - cannot hit a key conflict on insert if all keys are autogenerated (and thus we never supply them during INSERT) + if isUpsert && !slices.ContainsFunc(p.PrimaryKeyColumnNames, func(n string) bool { return !slices.Contains(p.AutoColumnNames, n) }) { + return plannedQuery{Query: ""} + } + + var ( + argumentIndexes = make([][]int, len(nonAutoColumnNames)) + scanIndexes [][]int + quotedColumnNames = make([]string, len(nonAutoColumnNames)) + quotedPlaceholders = make([]string, len(nonAutoColumnNames)) + ) + for idx, columnName := range nonAutoColumnNames { + argumentIndexes[idx] = p.IndexByColumnName[columnName] + quotedColumnNames[idx] = dialect.QuoteIdentifier(columnName) + quotedPlaceholders[idx] = dialect.Placeholder(idx) + } + if len(p.AutoColumnNames) > 0 { + 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)`, + dialect.QuoteIdentifier(p.TableName), + strings.Join(quotedColumnNames, ", "), + strings.Join(quotedPlaceholders, ", "), + ) + if isUpsert { + query += dialect.UpsertClause(p.PrimaryKeyColumnNames, p.getNonPrimaryKeyColumnNames()) + } + if len(p.AutoColumnNames) > 0 && p.InsertUsesQueryRow { + quotedAutoColumns := make([]string, len(p.AutoColumnNames)) + for idx, name := range p.AutoColumnNames { + quotedAutoColumns[idx] = dialect.QuoteIdentifier(name) + } + query += ` RETURNING ` + strings.Join(quotedAutoColumns, ", ") + } + return plannedQuery{query, argumentIndexes, scanIndexes} +} + +func (p plan) buildUpdateQueryIfPossible(dialect Dialect) plannedQuery { + if p.TableName == "" || len(p.PrimaryKeyColumnNames) == 0 { + return plannedQuery{Query: ""} + } + nonPrimaryKeyColumnNames := p.getNonPrimaryKeyColumnNames() + if len(nonPrimaryKeyColumnNames) == 0 { + return plannedQuery{Query: ""} + } + + var ( + setArgumentIndexes = make([][]int, len(nonPrimaryKeyColumnNames)) + setClauses = make([]string, len(nonPrimaryKeyColumnNames)) + ) + for idx, columnName := range nonPrimaryKeyColumnNames { + setArgumentIndexes[idx] = p.IndexByColumnName[columnName] + setClauses[idx] = fmt.Sprintf("%s = %s", dialect.QuoteIdentifier(columnName), dialect.Placeholder(idx)) + } + + var ( + whereArgumentIndexes = make([][]int, len(p.PrimaryKeyColumnNames)) + whereClauses = make([]string, len(p.PrimaryKeyColumnNames)) + ) + for idx, columnName := range p.PrimaryKeyColumnNames { + whereArgumentIndexes[idx] = p.IndexByColumnName[columnName] + whereClauses[idx] = fmt.Sprintf("%s = %s", dialect.QuoteIdentifier(columnName), dialect.Placeholder(idx+len(setClauses))) + } + + query := fmt.Sprintf( + `UPDATE %s SET %s WHERE %s`, + dialect.QuoteIdentifier(p.TableName), + strings.Join(setClauses, ", "), + strings.Join(whereClauses, " AND "), + ) + return plannedQuery{query, slices.Concat(setArgumentIndexes, whereArgumentIndexes), nil} +} + +func (p plan) buildDeleteQueryIfPossible(dialect Dialect) plannedQuery { + if p.TableName == "" || len(p.PrimaryKeyColumnNames) == 0 { + return plannedQuery{Query: ""} + } + + var ( + argumentIndexes = make([][]int, len(p.PrimaryKeyColumnNames)) + clauses = make([]string, len(p.PrimaryKeyColumnNames)) + ) + for idx, columnName := range p.PrimaryKeyColumnNames { + argumentIndexes[idx] = p.IndexByColumnName[columnName] + clauses[idx] = fmt.Sprintf("%s = %s", dialect.QuoteIdentifier(columnName), dialect.Placeholder(idx)) + } + + query := fmt.Sprintf( + `DELETE FROM %s WHERE %s`, + dialect.QuoteIdentifier(p.TableName), + strings.Join(clauses, " AND "), + ) + return plannedQuery{query, argumentIndexes, nil} +} diff --git a/oblast/plan_test.go b/oblast/plan_test.go new file mode 100644 index 0000000..45666d7 --- /dev/null +++ b/oblast/plan_test.go @@ -0,0 +1,657 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +// ^ NOTE: This is testing internal types and thus must reside in the same package. + +import ( + "reflect" + "testing" + "time" + + "go.xyrillian.de/gg/assert" +) + +// Clears all fields of `p` that are not used when actually running queries, +// i.e. intermediate fields holding analysis results that went into query planning. +// This is used to shorten the assertions in TestQueryConstruction...() below. +func onlyQueryPlans(p plan) plan { + p.AllColumnNames = nil + p.AutoColumnNames = nil + p.IndexByColumnName = nil + p.PrimaryKeyColumnNames = nil + p.TableName = "" + p.TypeName = "" + return p +} + +// Basically the opposite of onlyQueryPlans. +// TestPlanFieldTraversal() only cares about the analysis part, not the query construction phase. +func onlyAnalysisResult(p plan) plan { + p.Select = plannedQuery{} + p.Insert = plannedQuery{} + p.Upsert = plannedQuery{} + p.Update = plannedQuery{} + p.Delete = plannedQuery{} + return p +} + +func TestPlanFieldTraversal(t *testing.T) { + type Timestamps struct { + CreatedAt time.Time `db:"created_at"` + UpdatedAt *time.Time `db:"updated_at"` + } + type yetMoreTimestamps struct { + DeletedAt *time.Time `db:"deleted_at"` + } + type Log struct { + ID int64 `db:"id,auto"` + Message string + private1 bool `db:"private1"` //nolint:unused + Ignored any `db:"-"` + Timestamps + *yetMoreTimestamps + MoreText struct { + Description string + } + YetMoreText struct { + Payload string + } `db:"-"` + OpaqueText struct { + ShortMessage string + LongMessage string + } `db:"OpaqueText"` + } + + // check that the plan for Log: + // 1. uses the field name as a column name for "Message" + // 2. ignores "private1" because it cannot be written through reflection + // 3. ignores "Ignored" because its column name is "-" + // 4. traverses into "Timestamps" and includes its fields as well + // 5. traverses into "yetMoreTimestamps" as well (despite the extra pointer and the type being private) + // 6. traverses into "MoreText" and includes its fields as well + // 7. does not traverse into "YetMoreText" and does not include its fields because of `db:"-"` + // 8. does not traverse into "OpaqueText" because the struct is mapped as a whole + // 9. recognizes "id" as an autofilled column + p, err := buildPlan(reflect.TypeFor[Log](), PostgresDialect(), planOpts{ + StructTagKey: "db", + TableName: "log_entries", + PrimaryKeyColumnNames: []string{"id"}, + }) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyAnalysisResult(p), plan{ + TypeName: "Log", + TableName: "log_entries", + AllColumnNames: []string{"id", "Message", "created_at", "updated_at", "deleted_at", "Description", "OpaqueText"}, + PrimaryKeyColumnNames: []string{"id"}, + AutoColumnNames: []string{"id"}, + IndexByColumnName: map[string][]int{ + "id": {0}, + "Message": {1}, + "created_at": {4, 0}, + "updated_at": {4, 1}, + "deleted_at": {5, 0}, + "Description": {6, 0}, + "OpaqueText": {8}, + }, + InsertUsesQueryRow: true, + TransparentPointerStructFields: []fieldInfo{{ + Name: "yetMoreTimestamps", + Index: []int{5}, + }}, + }) +} + +func TestQueryConstructionBasic(t *testing.T) { + type record struct { + ID int64 `db:",auto"` + Description string + CreatedAt time.Time `db:"CreatedAt"` + } + opts := planOpts{ + StructTagKey: "db", + TableName: "basic_records", + PrimaryKeyColumnNames: []string{"ID"}, + } + + t.Run("MariaDBDialect", func(t *testing.T) { + opts.ReadOnly = false + p, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: "SELECT `ID`, `Description`, `CreatedAt` FROM `basic_records` WHERE ", + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: "INSERT INTO `basic_records` (`Description`, `CreatedAt`) VALUES (?, ?)", + ArgumentIndexes: [][]int{{1}, {2}}, + ScanIndexes: [][]int{{0}}, + }, + Update: plannedQuery{ + Query: "UPDATE `basic_records` SET `Description` = ?, `CreatedAt` = ? WHERE `ID` = ?", + ArgumentIndexes: [][]int{{1}, {2}, {0}}, + }, + Delete: plannedQuery{ + Query: "DELETE FROM `basic_records` WHERE `ID` = ?", + ArgumentIndexes: [][]int{{0}}, + }, + }) + + opts.ReadOnly = true + p2, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p2), plan{ + ReadOnly: true, + Select: p.Select, + }) + }) + + t.Run("PostgresDialect", func(t *testing.T) { + opts.ReadOnly = false + p, err := buildPlan(reflect.TypeFor[record](), PostgresDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + InsertUsesQueryRow: true, + Select: plannedQuery{ + Query: `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `, + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES ($1, $2) RETURNING "ID"`, + ArgumentIndexes: [][]int{{1}, {2}}, + ScanIndexes: [][]int{{0}}, + }, + Update: plannedQuery{ + Query: `UPDATE "basic_records" SET "Description" = $1, "CreatedAt" = $2 WHERE "ID" = $3`, + ArgumentIndexes: [][]int{{1}, {2}, {0}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "basic_records" WHERE "ID" = $1`, + ArgumentIndexes: [][]int{{0}}, + }, + }) + + opts.ReadOnly = true + p2, err := buildPlan(reflect.TypeFor[record](), PostgresDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p2), plan{ + InsertUsesQueryRow: true, + ReadOnly: true, + Select: p.Select, + }) + }) + + t.Run("SqliteDialect", func(t *testing.T) { + opts.ReadOnly = false + p, err := buildPlan(reflect.TypeFor[record](), SqliteDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `, + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES (?, ?)`, + ArgumentIndexes: [][]int{{1}, {2}}, + ScanIndexes: [][]int{{0}}, + }, + Update: plannedQuery{ + Query: `UPDATE "basic_records" SET "Description" = ?, "CreatedAt" = ? WHERE "ID" = ?`, + ArgumentIndexes: [][]int{{1}, {2}, {0}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "basic_records" WHERE "ID" = ?`, + ArgumentIndexes: [][]int{{0}}, + }, + }) + + opts.ReadOnly = true + p2, err := buildPlan(reflect.TypeFor[record](), SqliteDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p2), plan{ + ReadOnly: true, + Select: p.Select, + }) + }) +} + +func TestQueryConstructionWithOnlyPrimaryKey(t *testing.T) { + type relation struct { + FooID int64 `db:"foo_id"` + BarID int64 `db:"bar_id"` + } + opts := planOpts{ + StructTagKey: "db", + TableName: "foo_bar_relations", + PrimaryKeyColumnNames: []string{"foo_id", "bar_id"}, + } + + t.Run("MariaDBDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[relation](), MariaDBDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: "SELECT `foo_id`, `bar_id` FROM `foo_bar_relations` WHERE ", + ScanIndexes: [][]int{{0}, {1}}, + }, + Insert: plannedQuery{ + Query: "INSERT INTO `foo_bar_relations` (`foo_id`, `bar_id`) VALUES (?, ?)", + ArgumentIndexes: [][]int{{0}, {1}}, + }, + Upsert: plannedQuery{ + Query: "INSERT INTO `foo_bar_relations` (`foo_id`, `bar_id`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `foo_id` = VALUES(`foo_id`)", + ArgumentIndexes: [][]int{{0}, {1}}, + }, + Delete: plannedQuery{ + Query: "DELETE FROM `foo_bar_relations` WHERE `foo_id` = ? AND `bar_id` = ?", + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) + + t.Run("PostgresDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[relation](), PostgresDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `, + ScanIndexes: [][]int{{0}, {1}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES ($1, $2)`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + Upsert: plannedQuery{ + Query: `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES ($1, $2) ON CONFLICT ("foo_id", "bar_id") DO NOTHING`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "foo_bar_relations" WHERE "foo_id" = $1 AND "bar_id" = $2`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) + + t.Run("SqliteDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[relation](), SqliteDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `, + ScanIndexes: [][]int{{0}, {1}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?)`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + Upsert: plannedQuery{ + Query: `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?) ON CONFLICT ("foo_id", "bar_id") DO NOTHING`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "foo_bar_relations" WHERE "foo_id" = ? AND "bar_id" = ?`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) +} + +func TestQueryConstructionWithoutPrimaryKey(t *testing.T) { + type relation struct { + FooID int64 `db:"foo_id"` + BarID int64 `db:"bar_id"` + } + opts := planOpts{ + StructTagKey: "db", + TableName: "foo_bar_relations", + } + + t.Run("MariaDBDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[relation](), MariaDBDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: "SELECT `foo_id`, `bar_id` FROM `foo_bar_relations` WHERE ", + ScanIndexes: [][]int{{0}, {1}}, + }, + Insert: plannedQuery{ + Query: "INSERT INTO `foo_bar_relations` (`foo_id`, `bar_id`) VALUES (?, ?)", + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) + + t.Run("PostgresDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[relation](), PostgresDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `, + ScanIndexes: [][]int{{0}, {1}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES ($1, $2)`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) + + t.Run("SqliteDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[relation](), SqliteDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "foo_id", "bar_id" FROM "foo_bar_relations" WHERE `, + ScanIndexes: [][]int{{0}, {1}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?)`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) +} + +func TestQueryConstructionImpossible(t *testing.T) { + type unstructuredData struct { + Foo int + Bar *string + } + opts := planOpts{ + StructTagKey: "db", + } + + testWith := func(dialect Dialect) func(*testing.T) { + return func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[unstructuredData](), dialect, opts) + if err != nil { + t.Error(err) + } + + assert.Equal(t, onlyQueryPlans(p), plan{}) + } + } + + t.Run("MariaDBDialect", testWith(MariaDBDialect())) + t.Run("PostgresDialect", testWith(PostgresDialect())) + t.Run("SqliteDialect", testWith(SqliteDialect())) +} + +func TestQueryConstructionWithMultiplePrimaryKeyColumns(t *testing.T) { + type record struct { + GroupID int64 `db:"group_id"` + Name string `db:"name"` + CreatedAt time.Time `db:"created_at"` + } + opts := planOpts{ + StructTagKey: "db", + TableName: "complex_records", + PrimaryKeyColumnNames: []string{"group_id", "name"}, + } + + t.Run("MariaDBDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: "SELECT `group_id`, `name`, `created_at` FROM `complex_records` WHERE ", + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: "INSERT INTO `complex_records` (`group_id`, `name`, `created_at`) VALUES (?, ?, ?)", + ArgumentIndexes: [][]int{{0}, {1}, {2}}, + }, + Upsert: plannedQuery{ + Query: "INSERT INTO `complex_records` (`group_id`, `name`, `created_at`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `created_at` = VALUES(`created_at`)", + ArgumentIndexes: [][]int{{0}, {1}, {2}}, + }, + Update: plannedQuery{ + Query: "UPDATE `complex_records` SET `created_at` = ? WHERE `group_id` = ? AND `name` = ?", + ArgumentIndexes: [][]int{{2}, {0}, {1}}, + }, + Delete: plannedQuery{ + Query: "DELETE FROM `complex_records` WHERE `group_id` = ? AND `name` = ?", + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) + + t.Run("PostgresDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[record](), PostgresDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "group_id", "name", "created_at" FROM "complex_records" WHERE `, + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES ($1, $2, $3)`, + ArgumentIndexes: [][]int{{0}, {1}, {2}}, + }, + Upsert: plannedQuery{ + Query: `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES ($1, $2, $3) ON CONFLICT ("group_id", "name") DO UPDATE SET "created_at" = EXCLUDED."created_at"`, + ArgumentIndexes: [][]int{{0}, {1}, {2}}, + }, + Update: plannedQuery{ + Query: `UPDATE "complex_records" SET "created_at" = $1 WHERE "group_id" = $2 AND "name" = $3`, + ArgumentIndexes: [][]int{{2}, {0}, {1}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "complex_records" WHERE "group_id" = $1 AND "name" = $2`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) + + t.Run("SqliteDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[record](), SqliteDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + Select: plannedQuery{ + Query: `SELECT "group_id", "name", "created_at" FROM "complex_records" WHERE `, + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES (?, ?, ?)`, + ArgumentIndexes: [][]int{{0}, {1}, {2}}, + }, + Upsert: plannedQuery{ + Query: `INSERT INTO "complex_records" ("group_id", "name", "created_at") VALUES (?, ?, ?) ON CONFLICT ("group_id", "name") DO UPDATE SET "created_at" = EXCLUDED."created_at"`, + ArgumentIndexes: [][]int{{0}, {1}, {2}}, + }, + Update: plannedQuery{ + Query: `UPDATE "complex_records" SET "created_at" = ? WHERE "group_id" = ? AND "name" = ?`, + ArgumentIndexes: [][]int{{2}, {0}, {1}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "complex_records" WHERE "group_id" = ? AND "name" = ?`, + ArgumentIndexes: [][]int{{0}, {1}}, + }, + }) + }) +} + +func TestQueryConstructionWithMultipleAutoColumns(t *testing.T) { + type record struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + CreatedAt time.Time `db:"created_at,auto"` + } + opts := planOpts{ + StructTagKey: "db", + TableName: "autogenerated_records", + PrimaryKeyColumnNames: []string{"id"}, + } + + t.Run("MariaDBDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + InsertUsesQueryRow: true, + Select: plannedQuery{ + Query: "SELECT `id`, `name`, `created_at` FROM `autogenerated_records` WHERE ", + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: "INSERT INTO `autogenerated_records` (`name`) VALUES (?) RETURNING `id`, `created_at`", + ArgumentIndexes: [][]int{{1}}, + ScanIndexes: [][]int{{0}, {2}}, + }, + Update: plannedQuery{ + Query: "UPDATE `autogenerated_records` SET `name` = ?, `created_at` = ? WHERE `id` = ?", + ArgumentIndexes: [][]int{{1}, {2}, {0}}, + }, + Delete: plannedQuery{ + Query: "DELETE FROM `autogenerated_records` WHERE `id` = ?", + ArgumentIndexes: [][]int{{0}}, + }, + }) + }) + + t.Run("PostgresDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[record](), PostgresDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + InsertUsesQueryRow: true, + Select: plannedQuery{ + Query: `SELECT "id", "name", "created_at" FROM "autogenerated_records" WHERE `, + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "autogenerated_records" ("name") VALUES ($1) RETURNING "id", "created_at"`, + ArgumentIndexes: [][]int{{1}}, + ScanIndexes: [][]int{{0}, {2}}, + }, + Update: plannedQuery{ + Query: `UPDATE "autogenerated_records" SET "name" = $1, "created_at" = $2 WHERE "id" = $3`, + ArgumentIndexes: [][]int{{1}, {2}, {0}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "autogenerated_records" WHERE "id" = $1`, + ArgumentIndexes: [][]int{{0}}, + }, + }) + }) + + t.Run("SqliteDialect", func(t *testing.T) { + p, err := buildPlan(reflect.TypeFor[record](), SqliteDialect(), opts) + if err != nil { + t.Error(err) + } + assert.Equal(t, onlyQueryPlans(p), plan{ + InsertUsesQueryRow: true, + Select: plannedQuery{ + Query: `SELECT "id", "name", "created_at" FROM "autogenerated_records" WHERE `, + ScanIndexes: [][]int{{0}, {1}, {2}}, + }, + Insert: plannedQuery{ + Query: `INSERT INTO "autogenerated_records" ("name") VALUES (?) RETURNING "id", "created_at"`, + ArgumentIndexes: [][]int{{1}}, + ScanIndexes: [][]int{{0}, {2}}, + }, + Update: plannedQuery{ + Query: `UPDATE "autogenerated_records" SET "name" = ?, "created_at" = ? WHERE "id" = ?`, + ArgumentIndexes: [][]int{{1}, {2}, {0}}, + }, + Delete: plannedQuery{ + Query: `DELETE FROM "autogenerated_records" WHERE "id" = ?`, + ArgumentIndexes: [][]int{{0}}, + }, + }) + }) +} + +func TestPlanErrorCases(t *testing.T) { + type recordUsedViaPointer struct { + ID int64 `db:"id"` + } + + _, err := NewStore[*recordUsedViaPointer](SqliteDialect()) + assert.Equal(t, err.Error(), `cannot use type *oblast.recordUsedViaPointer for queries: `+ + `expected struct type, but got kind "ptr"`) + + type recordWithDuplicateTags struct { + Foo int64 `db:"Bar"` + Qux float64 + Bar string + } + _, err = NewStore[recordWithDuplicateTags](SqliteDialect()) + assert.Equal(t, err.Error(), `cannot use type oblast.recordWithDuplicateTags for queries: `+ + "duplicate tag `db:\"Bar\"` on field index [0], but also on field index [2]") + + type recordWithUnusedTransparentStruct struct { + ID int64 + CreatedAt time.Time // has no exported fields! + } + _, err = NewStore[recordWithUnusedTransparentStruct](SqliteDialect()) + assert.Equal(t, err.Error(), `cannot use type oblast.recordWithUnusedTransparentStruct for queries: `+ + "field \"CreatedAt\" of type time.Time does not contain any mapped fields (to map this whole field to a DB column, add an explicit `db:\"...\"` tag)") + + type recordWithPKButNoTableName struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + _, err = NewStore[recordWithPKButNoTableName](SqliteDialect(), + PrimaryKeyIs("id"), + ) + assert.Equal(t, err.Error(), `cannot use type oblast.recordWithPKButNoTableName for queries: `+ + `cannot declare a primary key without also providing the TableNameIs option`) + + type recordWithUnknownPK struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + _, err = NewStore[recordWithUnknownPK](SqliteDialect(), + TableNameIs("records"), + PrimaryKeyIs("record_id"), + ) + assert.Equal(t, err.Error(), `cannot use type oblast.recordWithUnknownPK for queries: `+ + "no field has tag `db:\"record_id\"`, but a field of this name was declared in the primary key") + + type recordWithWeirdTagOption struct { + ID int64 `db:",auto"` + Name string `db:",unique"` + Description string + } + _, err = NewStore[recordWithWeirdTagOption](SqliteDialect()) + assert.Equal(t, err.Error(), `cannot use type oblast.recordWithWeirdTagOption for queries: `+ + "unknown option `db:\",unique\"` on field \"Name\"") +} diff --git a/oblast/query.go b/oblast/query.go new file mode 100644 index 0000000..6e375e4 --- /dev/null +++ b/oblast/query.go @@ -0,0 +1,340 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +import ( + "context" + "database/sql" + "fmt" + "reflect" + + "go.xyrillian.de/gg/errext" + "go.xyrillian.de/gg/gsql" +) + +// PrepareThreshold is a tuning parameter for the strategy used by all methods of [Store] operating on batches of records provided by the caller +// (specifically, [Store.Insert], [Store.Update] and [Store.Delete]). +// +// For large amounts of records, it is obviously advantageous to build a prepared statement for the one query that will be used repeatedly on all of them. +// However, building a prepared statement is associated with some amount of bookkeeping on the level of the database/sql library. +// When operating on individual records or small amounts of records at a time (that is, in OLTP rather than OLAP workloads), this overhead becomes a measurable performance burden. +// +// This tuning parameter defines the minimum number of records that will justify maintaining a prepared statement. +// Our benchmarking with the mattn/go-sqlite3 driver (and last checked with Go 1.26.2 on x86_64) indicates that this becomes a worthwhile investment at 8 or more records, so this is our default. +// If your benchmarking indicates a different tradeoff depending on your choice of Go version or SQL driver, you may adjust this variable accordingly. +// +// The actual effect of this setting is to control the value of the "repeated" argument in [Handle.Prepare]. +var PrepareThreshold int = 8 + +// prepare behaves like [Handle.Prepare]. +func prepare(ctx context.Context, db gsql.Handle, readOnly bool, query, operation string, inputSize int) (gsql.Statement, error) { + if query == "" { + if readOnly { + return nil, fmt.Errorf("cannot execute %s() because query planning used the ReadOnly() option", operation) + } + return nil, fmt.Errorf("cannot execute %s() because query could not be autogenerated", operation) + } + + return db.GSQLPrepare(ctx, query, inputSize >= PrepareThreshold) +} + +// 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. +// (This is why this method, as well as [Store.Upsert], need to take their arguments by-pointer instead of by-value). +// +// Returns an error if [NewStore] was called without the [TableNameIs] option, which is required to generate a query for this method. +// +// Returns an error if any of the `records` has a non-zero value in any column marked as `db:",auto"`. +// Records that already exist in the database should be handled with [Store.Update] instead. +// To automatically decide between INSERT and UPDATE on a per-record basis, use [Store.Upsert] instead. +func (s Store[R]) Insert(ctx context.Context, db gsql.Handle, records ...*R) 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. + + stmt, err := prepare(ctx, db, s.plan.ReadOnly, s.plan.Insert.Query, "Insert", len(records)) + if err != nil { + return err + } + return s.insertUsing(ctx, stmt, db, records) +} + +func (s Store[R]) insertUsing(ctx context.Context, stmt gsql.Statement, db gsql.Handle, records []*R) 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. + + var ( + argumentIndexes = s.plan.Insert.ArgumentIndexes + argumentSlots = make([]any, len(argumentIndexes)) + scanIndexes = s.plan.Insert.ScanIndexes + scanSlots = make([]any, len(scanIndexes)) + ) + + for idx, r := range records { + v := reflect.ValueOf(r).Elem() + err := checkTransparentPointerStructFieldsInitialized("INSERT", idx, v, s.plan, false) + if err != nil { + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) + } + err = insertRecord(ctx, s.plan, v, idx, stmt, argumentIndexes, argumentSlots, scanIndexes, scanSlots) + if err != nil { + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) + } + } + + return errext.WithCleanup(nil, "Stmt.Close", stmt.Close()) +} + +func insertRecord(ctx context.Context, plan plan, v reflect.Value, recordIndex int, stmt gsql.Statement, argumentIndexes [][]int, argumentSlots []any, scanIndexes [][]int, scanSlots []any) error { + for idx, index := range argumentIndexes { + argumentSlots[idx] = v.FieldByIndex(index).Interface() + } + for idx, index := range scanIndexes { + f := v.FieldByIndex(index) + if !f.IsZero() { + return fmt.Errorf(`refusing to INSERT record with idx = %d that already has non-zero values in its "auto" columns`, recordIndex) + } + scanSlots[idx] = f.Addr().Interface() + } + + var ( + result sql.Result + err error + ) + switch { + case len(scanSlots) == 0: + _, err = stmt.Exec(ctx, argumentSlots) + case plan.InsertUsesQueryRow: + err = stmt.QueryRow(ctx, argumentSlots, scanSlots) + default: + result, err = stmt.Exec(ctx, argumentSlots) + } + if err != nil { + return fmt.Errorf("while inserting record with idx = %d: %w", recordIndex, err) + } + + if result != nil { + id, err := result.LastInsertId() + if err != nil { + return fmt.Errorf("while getting LastInsertId for record with idx = %d: %w", recordIndex, err) + } + if plan.LastInsertIdIsUnsigned { + if id < 0 { + return fmt.Errorf("LastInsertId() = %d for record with idx = %d cannot be converted to uint", id, recordIndex) + } + v.FieldByIndex(scanIndexes[0]).SetUint(uint64(id)) + } else { + v.FieldByIndex(scanIndexes[0]).SetInt(id) + } + } + + return nil +} + +// This check must be performed within all query functions that access existing values using FieldByIndex(), +// to ensure that FieldByIndex() does not panic on indirection through a nil pointer. +func checkTransparentPointerStructFieldsInitialized(operation string, recordIndex int, v reflect.Value, plan plan, onlyPK bool) error { + for _, field := range plan.TransparentPointerStructFields { + f := v.FieldByIndex(field.Index) + if !f.IsZero() { + continue + } + if onlyPK { + if field.ContainsPrimaryKey { + return fmt.Errorf(`refusing to %s record with idx = %d: cannot access all primary key fields because field %q holds a nil pointer`, + operation, recordIndex, field.Name) + } + } else { + return fmt.Errorf(`refusing to %s record with idx = %d: cannot access all mapped fields because field %q holds a nil pointer`, + operation, recordIndex, field.Name) + } + } + return nil +} + +// Update executes an SQL UPDATE statement for each of the provided records, updating all non-primary-key columns with the values in the records. +// Returns [MissingRecordError] if any of the records does not exist in the database, that is, if for any of the records, the database contains no row with the same primary key values. +// +// 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]) Update(ctx context.Context, db gsql.Handle, records ...R) 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. + + stmt, err := prepare(ctx, db, s.plan.ReadOnly, s.plan.Update.Query, "Update", len(records)) + if err != nil { + return err + } + + var ( + argumentIndexes = s.plan.Update.ArgumentIndexes + argumentSlots = make([]any, len(argumentIndexes)) + ) + + for idx := range records { + v := reflect.ValueOf(&records[idx]).Elem() + err := checkTransparentPointerStructFieldsInitialized("UPDATE", idx, v, s.plan, false) + if err != nil { + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) + } + rowsAffected, err := updateRecord(ctx, v, idx, stmt, argumentIndexes, argumentSlots) + if err == nil && rowsAffected == 0 { + err = MissingRecordError[R]{records[idx], s.plan} + } + if err != nil { + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) + } + } + return errext.WithCleanup(nil, "Stmt.Close", stmt.Close()) +} + +func updateRecord(ctx context.Context, v reflect.Value, recordIndex int, stmt gsql.Statement, argumentIndexes [][]int, argumentSlots []any) (int64, error) { + for idx, index := range argumentIndexes { + argumentSlots[idx] = v.FieldByIndex(index).Interface() + } + result, err := stmt.Exec(ctx, argumentSlots) + if err != nil { + return 0, fmt.Errorf("while updating record with idx = %d: %w", recordIndex, err) + } + rowsAffected, err := result.RowsAffected() + if err != nil { + return 0, fmt.Errorf("during RowsAffected() for record with idx = %d: %w", recordIndex, err) + } + return rowsAffected, nil +} + +// 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(ctx context.Context, db gsql.Handle, records ...R) 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. + + stmt, err := prepare(ctx, db, s.plan.ReadOnly, s.plan.Delete.Query, "Delete", len(records)) + if err != nil { + return err + } + + var ( + argumentIndexes = s.plan.Delete.ArgumentIndexes + argumentSlots = make([]any, len(argumentIndexes)) + ) + + for idx := range records { + v := reflect.ValueOf(&records[idx]).Elem() + err := deleteRecord(ctx, s.plan, v, idx, stmt, argumentIndexes, argumentSlots) + if err != nil { + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) + } + } + + return errext.WithCleanup(nil, "Stmt.Close", stmt.Close()) +} + +func deleteRecord(ctx context.Context, plan plan, v reflect.Value, recordIndex int, stmt gsql.Statement, argumentIndexes [][]int, argumentSlots []any) error { + err := checkTransparentPointerStructFieldsInitialized("DELETE", recordIndex, v, plan, true) + if err != nil { + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) + } + for idx, index := range argumentIndexes { + argumentSlots[idx] = v.FieldByIndex(index).Interface() + } + _, err = stmt.Exec(ctx, argumentSlots) + if err != nil { + return fmt.Errorf("while deleting record with idx = %d: %w", recordIndex, err) + } + return nil +} + +// Upsert executes either an SQL INSERT or UPDATE statement for each of the provided records, +// based on whether the record already exists in the DB or not. +// +// - For record types that have fields declared with the "auto" tag, INSERT is chosen if and only if those fields hold zero values. +// Returns an error if only some of the respective fields hold zero values while others don't. +// Returns an error if [NewStore] was called without the [TableNameIs] or [PrimaryKeyIs] options, which are both required to generate the respective queries for this method. +// - For record types that do not have fields declared with the "auto" tag, an INSERT ... ON CONFLICT statement is used. +// 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]) Upsert(ctx context.Context, db gsql.Handle, records ...*R) 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. + + if len(s.plan.AutoColumnNames) == 0 { + stmt, err := prepare(ctx, db, s.plan.ReadOnly, s.plan.Upsert.Query, "Upsert", len(records)) + if err != nil { + return err + } + return s.insertUsing(ctx, stmt, db, records) + } + + // TODO: respect PrepareThreshold (or not? may be too much bookkeeping overhead for not a whole lot of benefit) + insertStmt, err := prepare(ctx, db, s.plan.ReadOnly, s.plan.Insert.Query, "Insert", 0) + if err != nil { + return err + } + updateStmt, err := prepare(ctx, db, s.plan.ReadOnly, s.plan.Update.Query, "Update", 0) + if err != nil { + return errext.WithCleanup(err, "InsertStmt.Close", insertStmt.Close()) + } + + err = s.doUpsert(ctx, db, insertStmt, updateStmt, records) + err = errext.WithCleanup(err, "InsertStmt.Close", insertStmt.Close()) + err = errext.WithCleanup(err, "UpdateStmt.Close", updateStmt.Close()) + return err +} + +func (s Store[R]) doUpsert(ctx context.Context, db gsql.Handle, insertStmt, updateStmt gsql.Statement, records []*R) 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. + + var ( + insertArgumentIndexes = s.plan.Insert.ArgumentIndexes + insertArgumentSlots = make([]any, len(insertArgumentIndexes)) + insertScanIndexes = s.plan.Insert.ScanIndexes + insertScanSlots = make([]any, len(insertScanIndexes)) + updateArgumentIndexes = s.plan.Update.ArgumentIndexes + updateArgumentSlots = make([]any, len(updateArgumentIndexes)) + ) + + for idx, r := range records { + v := reflect.ValueOf(r).Elem() + err := checkTransparentPointerStructFieldsInitialized("INSERT or UPDATE", idx, v, s.plan, false) + if err != nil { + return err + } + isInsert, err := upsertDecideStrategy(v, idx, insertScanIndexes) + if err != nil { + return err + } + + if isInsert { + err = insertRecord(ctx, s.plan, v, idx, insertStmt, insertArgumentIndexes, insertArgumentSlots, insertScanIndexes, insertScanSlots) + } else { + var rowsAffected int64 + rowsAffected, err = updateRecord(ctx, v, idx, updateStmt, updateArgumentIndexes, updateArgumentSlots) + if err == nil && rowsAffected == 0 { + err = MissingRecordError[R]{*r, s.plan} + } + } + if err != nil { + return err + } + } + + return nil +} + +func upsertDecideStrategy(v reflect.Value, recordIndex int, scanIndexes [][]int) (isInsert bool, err error) { + var isUpdate bool + for _, index := range scanIndexes { + if v.FieldByIndex(index).IsZero() { + isInsert = true + } else { + isUpdate = true + } + } + if isInsert && isUpdate { + return false, fmt.Errorf(`cannot decide whether to INSERT or UPDATE record with idx = %d: some "auto" columns are zero, others are not`, recordIndex) + } + return isInsert, nil +} diff --git a/oblast/query_test.go b/oblast/query_test.go new file mode 100644 index 0000000..94c819b --- /dev/null +++ b/oblast/query_test.go @@ -0,0 +1,522 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast_test + +import ( + "database/sql" + "strconv" + "testing" + "time" + + "go.xyrillian.de/gg/assert" + "go.xyrillian.de/gg/gsql" + "go.xyrillian.de/gg/oblast" + "go.xyrillian.de/gg/oblast/internal/testhelpers/mock" + "go.xyrillian.de/gg/oblast/internal/testhelpers/must" +) + +func TestInsertBasic(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + + // testing with the SQLite dialect exercises the Exec()-based codepath + t.Run("driver=sqlite", func(t *testing.T) { + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + for _, batchSize := range []int{1, oblast.PrepareThreshold - 1, oblast.PrepareThreshold + 1} { + t.Run("N="+strconv.Itoa(batchSize), func(t *testing.T) { + records := make([]*basicRecord, batchSize) + for idx := range batchSize { + records[idx] = &basicRecord{Name: "new"} + md.ForQuery(`INSERT INTO "basic_records" ("name") VALUES (?)`). + ExpectExecWithArgs("new"). + AndReturnLastInsertId(int64(42 + idx)) + } + must.Succeed(t, store.Insert(ctx, db, records...)) + for idx, r := range records { + assert.Equal(t, r.ID, int64(42+idx)) + } + }) + } + }) + + // testing with the Postgres dialect exercises the QueryRow()-based codepath + t.Run("driver=postgres", func(t *testing.T) { + store := oblast.MustNewStore[basicRecord]( + oblast.PostgresDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + for _, batchSize := range []int{1, oblast.PrepareThreshold - 1, oblast.PrepareThreshold + 1} { + t.Run("N="+strconv.Itoa(batchSize), func(t *testing.T) { + records := make([]*basicRecord, batchSize) + for idx := range batchSize { + records[idx] = &basicRecord{Name: "new"} + md.ForQuery(`INSERT INTO "basic_records" ("name") VALUES ($1) RETURNING "id"`). + ExpectQueryWithArgs("new"). + AndReturnColumns("id"). + WithRow(int64(42 + idx)) + } + must.Succeed(t, store.Insert(ctx, db, records...)) + for idx, r := range records { + assert.Equal(t, r.ID, int64(42+idx)) + } + }) + } + }) +} + +func TestInsertWithUintPrimaryKey(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type exoticRecord struct { + ID uint64 `oblast:"id,auto"` + Name string `oblast:"name"` + } + store := oblast.MustNewStore[exoticRecord]( + oblast.SqliteDialect(), + oblast.StructTagKeyIs("oblast"), // this test also randomly provides coverage for this option + oblast.TableNameIs("exotic_records"), + oblast.PrimaryKeyIs("id"), + ) + + // success case: positive ID fits into uint64 + md.ForQuery(`INSERT INTO "exotic_records" ("name") VALUES (?)`). + ExpectExecWithArgs("new"). + AndReturnLastInsertId(42) + record := exoticRecord{Name: "new"} + must.Succeed(t, store.Insert(ctx, db, &record)) + assert.Equal(t, record.ID, 42) + + // error case: negative ID cannot be converted to uint64 + md.ForQuery(`INSERT INTO "exotic_records" ("name") VALUES (?)`). + ExpectExecWithArgs("another"). + AndReturnLastInsertId(-42) + record = exoticRecord{Name: "another"} + err := store.Insert(ctx, db, &record) + assert.ErrEqual(t, err, "LastInsertId() = -42 for record with idx = 0 cannot be converted to uint") +} + +func TestUpdateBasic(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + for _, batchSize := range []int{1, oblast.PrepareThreshold - 1, oblast.PrepareThreshold + 1} { + t.Run("N="+strconv.Itoa(batchSize), func(t *testing.T) { + records := make([]basicRecord, batchSize) + for idx := range batchSize { + r := basicRecord{ID: int64(42 + idx), Name: "updated"} + records[idx] = r + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs(r.Name, r.ID). + AndReturnRowsAffected(1) + } + must.Succeed(t, store.Update(ctx, db, records...)) + }) + } +} + +func TestDeleteBasic(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + for _, batchSize := range []int{1, oblast.PrepareThreshold - 1, oblast.PrepareThreshold + 1} { + t.Run("N="+strconv.Itoa(batchSize), func(t *testing.T) { + records := make([]basicRecord, batchSize) + for idx := range batchSize { + r := basicRecord{ID: int64(42 + idx), Name: "removed"} + records[idx] = r + md.ForQuery(`DELETE FROM "basic_records" WHERE "id" = ?`). + ExpectExecWithArgs(r.ID). + AndReturnRowsAffected(1) + } + must.Succeed(t, store.Delete(ctx, db, records...)) + }) + } +} + +func TestUpsertBasicWithAutoColumn(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + md.ForQuery(`INSERT INTO "basic_records" ("name") VALUES (?)`). + ExpectExecWithArgs("first needs insert"). + AndReturnLastInsertId(1) + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("second needs update", 2). + AndReturnRowsAffected(1) + md.ForQuery(`INSERT INTO "basic_records" ("name") VALUES (?)`). + ExpectExecWithArgs("third needs insert"). + AndReturnLastInsertId(3) + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("fourth needs update", 4). + AndReturnRowsAffected(1) + + records := []*basicRecord{ + {Name: "first needs insert"}, + {ID: 2, Name: "second needs update"}, + {Name: "third needs insert"}, + {ID: 4, Name: "fourth needs update"}, + } + must.Succeed(t, store.Upsert(ctx, db, records...)) + + assert.Equal(t, records, []*basicRecord{ + {ID: 1, Name: "first needs insert"}, + {ID: 2, Name: "second needs update"}, + {ID: 3, Name: "third needs insert"}, + {ID: 4, Name: "fourth needs update"}, + }) +} + +func TestWriteQueriesNotPossible(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + // no TableNameIs() or PrimaryKeyIs() given + ) + + r := basicRecord{Name: "foo"} + err := store.Insert(ctx, db, &r) + assert.ErrEqual(t, err, "cannot execute Insert() because query could not be autogenerated") + + err = store.Upsert(ctx, db, &r) + assert.ErrEqual(t, err, "cannot execute Insert() because query could not be autogenerated") + + r.ID = 42 + err = store.Update(ctx, db, r) + assert.ErrEqual(t, err, "cannot execute Update() because query could not be autogenerated") + + err = store.Delete(ctx, db, r) + assert.ErrEqual(t, err, "cannot execute Delete() because query could not be autogenerated") + + store = oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("records"), + oblast.PrimaryKeyIs("id"), + oblast.ReadOnly(), + ) + + r = basicRecord{Name: "foo"} + err = store.Insert(ctx, db, &r) + assert.ErrEqual(t, err, "cannot execute Insert() because query planning used the ReadOnly() option") + + err = store.Upsert(ctx, db, &r) + assert.ErrEqual(t, err, "cannot execute Insert() because query planning used the ReadOnly() option") + + r.ID = 42 + err = store.Update(ctx, db, r) + assert.ErrEqual(t, err, "cannot execute Update() because query planning used the ReadOnly() option") + + err = store.Delete(ctx, db, r) + assert.ErrEqual(t, err, "cannot execute Delete() because query planning used the ReadOnly() option") +} + +func TestWriteQueriesFailDuringPrepare(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + for _, batchSize := range []int{1, oblast.PrepareThreshold - 1, oblast.PrepareThreshold + 1} { + records := make([]basicRecord, batchSize) + recordsForInsert := make([]*basicRecord, batchSize) + for idx := range batchSize { + records[idx] = basicRecord{ID: int64(42 + idx), Name: "foo"} + recordsForInsert[idx] = &basicRecord{Name: "foo"} + } + + err := store.Insert(ctx, db, recordsForInsert...) + baseError := `unexpected query: INSERT INTO "basic_records" ("name") VALUES (?)` + if batchSize < oblast.PrepareThreshold { + assert.ErrEqual(t, err, "while inserting record with idx = 0: "+baseError) + } else { + assert.ErrEqual(t, err, "during Prepare(): "+baseError) + } + + err = store.Update(ctx, db, records...) + baseError = `unexpected query: UPDATE "basic_records" SET "name" = ? WHERE "id" = ?` + if batchSize < oblast.PrepareThreshold { + assert.ErrEqual(t, err, "while updating record with idx = 0: "+baseError) + } else { + assert.ErrEqual(t, err, "during Prepare(): "+baseError) + } + + err = store.Delete(ctx, db, records...) + baseError = `unexpected query: DELETE FROM "basic_records" WHERE "id" = ?` + if batchSize < oblast.PrepareThreshold { + assert.ErrEqual(t, err, "while deleting record with idx = 0: "+baseError) + } else { + assert.ErrEqual(t, err, "during Prepare(): "+baseError) + } + } +} + +func TestUpdateOrUpsertFailsOnMissingRecord(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + // test Update() + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("changed", 42). + AndReturnRowsAffected(0) + err := store.Update(ctx, db, basicRecord{ID: 42, Name: "changed"}) + assert.ErrEqual(t, err, "could not UPDATE record that does not exist in the database: id = 42") + _, hasCorrectType := err.(oblast.MissingRecordError[basicRecord]) //nolint:errorlint // we explicitly do not want a wrapped error + assert.Equal(t, hasCorrectType, true) + + // test Upsert() -> this will not try inserting because the strategy + // is chosen based on the fill state of the "auto" field + md.ForQuery(`UPDATE "basic_records" SET "name" = ? WHERE "id" = ?`). + ExpectExecWithArgs("changed", 42). + AndReturnRowsAffected(0) + err = store.Upsert(ctx, db, &basicRecord{ID: 42, Name: "changed"}) + assert.ErrEqual(t, err, "could not UPDATE record that does not exist in the database: id = 42") + _, hasCorrectType = err.(oblast.MissingRecordError[basicRecord]) //nolint:errorlint // we explicitly do not want a wrapped error + assert.Equal(t, hasCorrectType, true) +} + +func TestInsertFailsOnFilledAutoField(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + err := store.Insert(ctx, db, &basicRecord{ID: 23, Name: "third"}) + assert.ErrEqual(t, err, `refusing to INSERT record with idx = 0 that already has non-zero values in its "auto" columns`) +} + +func TestInsertAndUpsertWithNoAutoColumns(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type relation struct { + FooID int64 `db:"foo_id"` + BarID int64 `db:"bar_id"` + } + store := oblast.MustNewStore[relation]( + oblast.SqliteDialect(), + oblast.TableNameIs("foo_bar_relations"), + oblast.PrimaryKeyIs("foo_id", "bar_id"), + ) + + // test Insert() + md.ForQuery(`INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?)`). + ExpectExecWithArgs(23, 42). + AndReturnRowsAffected(1) + must.Succeed(t, store.Insert(ctx, db, &relation{23, 42})) + + // test Upsert() + md.ForQuery(`INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?) ON CONFLICT ("foo_id", "bar_id") DO NOTHING`). + ExpectExecWithArgs(1, 2). + AndReturnRowsAffected(1) + md.ForQuery(`INSERT INTO "foo_bar_relations" ("foo_id", "bar_id") VALUES (?, ?) ON CONFLICT ("foo_id", "bar_id") DO NOTHING`). + ExpectExecWithArgs(3, 4). + AndReturnRowsAffected(1) + must.Succeed(t, store.Upsert(ctx, db, &relation{1, 2}, &relation{3, 4})) +} + +func TestUpsertFailsOnMixedAutoFieldState(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type complexRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + CreatedAt time.Time `db:"created_at,auto"` + } + store := oblast.MustNewStore[complexRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("complex_records"), + oblast.PrimaryKeyIs("id"), + ) + + brokenRecord := complexRecord{ + ID: 42, // this looks like we need to UPDATE + Name: "foo", + CreatedAt: time.Time{}, // this looks like we need to INSERT + } + err := store.Upsert(ctx, db, &brokenRecord) + assert.ErrEqual(t, err, `cannot decide whether to INSERT or UPDATE record with idx = 0: some "auto" columns are zero, others are not`) +} + +func TestUninitializedTransparentPointerStructs(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + // declare a record type that has a transparent pointer struct containing non-primary-key fields + type timestamps struct { + CreatedAt time.Time `db:"created_at"` + DeletedAt *time.Time `db:"deleted_at"` + } + type nestedRecord struct { + ID int64 `db:"id,auto"` + Name string `db:"name"` + *timestamps + } + nestedRecordStore := oblast.MustNewStore[nestedRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("nested_records"), + oblast.PrimaryKeyIs("id"), + ) + + // declare another record type that has a primary key field within a transparent pointer struct + type commonFields struct { + ID int64 `db:"id,auto"` + CreatedAt time.Time `db:"created_at"` + DeletedAt *time.Time `db:"deleted_at"` + } + type weirdRecord struct { + *commonFields + Name string `db:"name"` + } + weirdRecordStore := oblast.MustNewStore[weirdRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("weird_records"), + oblast.PrimaryKeyIs("id"), + ) + + // check detection on INSERT + freshBrokenRecord := nestedRecord{ + Name: "foo", + timestamps: nil, // problem: cannot access `freshBrokenRecord.CreatedAt` or `freshBrokenRecord.DeletedAt` + } + err := nestedRecordStore.Insert(ctx, db, &freshBrokenRecord) + assert.ErrEqual(t, err, `refusing to INSERT record with idx = 0: cannot access all mapped fields because field "timestamps" holds a nil pointer`) + err = nestedRecordStore.Upsert(ctx, db, &freshBrokenRecord) + assert.ErrEqual(t, err, `refusing to INSERT or UPDATE record with idx = 0: cannot access all mapped fields because field "timestamps" holds a nil pointer`) + + // check success case on INSERT + now := time.Now() + freshIntactRecord := nestedRecord{ + Name: "foo", + timestamps: ×tamps{CreatedAt: now, DeletedAt: nil}, + } + md.ForQuery(`INSERT INTO "nested_records" ("name", "created_at", "deleted_at") VALUES (?, ?, ?)`). + ExpectExecWithArgs("foo", now, (*time.Time)(nil)). + AndReturnLastInsertId(1) + must.Succeed(t, nestedRecordStore.Insert(ctx, db, &freshIntactRecord)) + assert.Equal(t, freshIntactRecord.ID, 1) + + // check detection on UPDATE + existingBrokenRecord := nestedRecord{ + ID: 42, + Name: "bar", + timestamps: nil, // same problem as above + } + err = nestedRecordStore.Update(ctx, db, existingBrokenRecord) + assert.ErrEqual(t, err, `refusing to UPDATE record with idx = 0: cannot access all mapped fields because field "timestamps" holds a nil pointer`) + err = nestedRecordStore.Upsert(ctx, db, &freshBrokenRecord) + assert.ErrEqual(t, err, `refusing to INSERT or UPDATE record with idx = 0: cannot access all mapped fields because field "timestamps" holds a nil pointer`) + + // check success case on UPDATE + now = time.Now() + existingIntactRecord := nestedRecord{ + ID: 42, + Name: "bar", + timestamps: ×tamps{CreatedAt: now, DeletedAt: nil}, + } + md.ForQuery(`UPDATE "nested_records" SET "name" = ?, "created_at" = ?, "deleted_at" = ? WHERE "id" = ?`). + ExpectExecWithArgs("bar", now, (*time.Time)(nil), 42). + AndReturnRowsAffected(1) + must.Succeed(t, nestedRecordStore.Update(ctx, db, existingIntactRecord)) + + // check that detection on DELETE does not care about transparent pointer structs as long as they do not contain PK fields + md.ForQuery(`DELETE FROM "nested_records" WHERE "id" = ?`). + ExpectExecWithArgs(42). + AndReturnRowsAffected(1) + must.Succeed(t, nestedRecordStore.Delete(ctx, db, existingBrokenRecord)) + + // check detection on DELETE where it matters + existingWeirdRecord := weirdRecord{ + commonFields: nil, // problem: cannot access `existingWeirdRecord.ID` + Name: "qux", + } + err = weirdRecordStore.Delete(ctx, db, existingWeirdRecord) + assert.ErrEqual(t, err, `refusing to DELETE record with idx = 0: cannot access all primary key fields because field "commonFields" holds a nil pointer`) +} diff --git a/oblast/select.go b/oblast/select.go new file mode 100644 index 0000000..66ba9e8 --- /dev/null +++ b/oblast/select.go @@ -0,0 +1,474 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast + +import ( + "context" + "database/sql" + "errors" + "fmt" + "reflect" + + "go.xyrillian.de/gg/errext" + "go.xyrillian.de/gg/gsql" +) + +// Select executes the provided SQL query and fills an instance of the record type R for each row in the result set, +// according to the column names reported by the database as part of the result set. +// +// An error is returned if any column name in the result set does not correspond to an addressable field in R. +// Errors can be retrieved through the methods on type [Selection]. +func (s Store[R]) Select(ctx context.Context, db gsql.Handle, query string, args ...any) Selection[R] { + // 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. + + return Selection[R]{startSelectQuery(ctx, db, s.plan, query, args...)} +} + +// SelectWhere is like [Store.Select], but you only provide the part of the SELECT query that comes after the WHERE. +// The initial part ("SELECT ... FROM ... WHERE") is autogenerated and prepended to partialQuery. +// This has two benefits: +// - It is more efficient because the strategy for loading result rows into the record type R has already been precomputed during [NewStore], +// whereas a regular [Store.Select] must inspect the column names in the result set for each [Store.Select] call. +// - For record types that contain only some of the columns of the corresponding database table, +// the autogenerated SELECT query will only load exactly the necessary fields and nothing else. +// +// partialQuery is implied to start right after the WHERE keyword, which is added automatically. +// To select all records unconditionally, provide a partialQuery of "TRUE", leading to a full query of "SELECT ... FROM ... WHERE TRUE". +// Besides a condition for the WHERE clause, it may contain additional clauses, such as ORDER BY or LIMIT. +// +// Returns an error if [NewStore] was called without the [TableNameIs] option, which is required to generate a query for this method. +// Errors can be retrieved through the methods on type [Selection]. +func (s Store[R]) SelectWhere(ctx context.Context, db gsql.Handle, partialQuery string, args ...any) Selection[R] { + // 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. + + return Selection[R]{startSelectWhereQuery(ctx, db, s.plan, partialQuery, args...)} +} + +func startSelectQuery(ctx context.Context, db gsql.Handle, plan plan, query string, args ...any) selection { + rows, err := db.GSQLQuery(ctx, query, args) + if err != nil { + return selection{Err: fmt.Errorf("during Query(): %w", err)} + } + + // fast exit for TupleSelect() + if len(plan.IndexByColumnName) == 0 { + return selection{ + Rows: rows, + Slots: make([]any, len(plan.StaticIndexes)), + Indexes: plan.StaticIndexes, + } + } + + columnNames, err := rows.Columns() + if err != nil { + err = fmt.Errorf("during rows.Columns(): %w", err) + return selection{Err: errext.WithCleanup(err, "Rows.Close", rows.Close())} + } + indexes := make([][]int, len(columnNames)) + for idx, columnName := range columnNames { + var ok bool + indexes[idx], ok = plan.IndexByColumnName[columnName] + if !ok { + err := fmt.Errorf( + "result has column %q in position %d, but no field in type %s has `db:%[1]q`", + columnName, idx, plan.TypeName, + ) + return selection{Err: errext.WithCleanup(err, "Rows.Close", rows.Close())} + } + } + + return selection{ + Rows: rows, + Slots: make([]any, len(indexes)), + Err: nil, + Indexes: indexes, + TransparentPointerStructFields: plan.TransparentPointerStructFields, + } +} + +func startSelectWhereQuery(ctx context.Context, db gsql.Handle, plan plan, partialQuery string, args ...any) selection { + if plan.Select.Query == "" { + return selection{Err: errors.New("cannot execute SelectWhere() because query could not be autogenerated")} + } + query := plan.Select.Query + partialQuery + rows, err := db.GSQLQuery(ctx, query, args) + if err != nil { + return selection{Err: fmt.Errorf("during Query(): %w", err)} + } + return selection{ + Rows: rows, + Slots: make([]any, len(plan.Select.ScanIndexes)), + Err: nil, + Indexes: plan.Select.ScanIndexes, + TransparentPointerStructFields: plan.TransparentPointerStructFields, + } +} + +// SelectOne executes the provided SQL query and fills an instance of the record type R if there is exactly one row in the result set, +// according to the column names reported by the database as part of the result set. +// +// If there are no rows in the result set, [sql.ErrNoRows] is returned. +// +// Warning: Because of limitations in the interface of database/sql, this function is built on [Store.Select] and cannot be any faster than it. +// For maximum performance, use [Store.SelectOneWhere] which avoids the overhead of potentially having to read multiple rows. +func (s Store[R]) SelectOne(ctx context.Context, db gsql.Handle, query string, args ...any) (R, 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. + // + // NOTE: The "limitation in the interface of database/sql" is that type sql.Row does not have the Columns() method, + // which we need when mapping result columns to struct fields for user-provided queries. + + return s.Select(ctx, db, query, args...).First() +} + +// SelectOneWhere is like [Store.SelectOne], but you only provide the part of the SELECT query that comes after the WHERE. +// See [Store.SelectWhere] for an explanation of how the full query is constructed from this partial query. +// +// This method is more efficient than [Store.SelectOne] on CPU runtime, but has a slight memory allocation overhead per call from query preparation. +// This can be avoided by using [Store.PrepareSelectQueryWhere] instead. +func (s Store[R]) SelectOneWhere(ctx context.Context, db gsql.Handle, partialQuery string, args ...any) (R, 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. + + var result R + err := selectOneWhere(ctx, db, s.plan, reflect.ValueOf(&result).Elem(), partialQuery, args) + return result, err +} + +func selectOneWhere(ctx context.Context, db gsql.Handle, plan plan, v reflect.Value, partialQuery string, args []any) error { + if plan.Select.Query == "" { + return errors.New("cannot execute SelectOneWhere() because query could not be autogenerated") + } + query := plan.Select.Query + partialQuery + return selectOne(ctx, db, plan, v, query, args) +} + +func selectOne(ctx context.Context, db gsql.Handle, plan plan, v reflect.Value, query string, args []any) error { + for _, field := range plan.TransparentPointerStructFields { + f := v.FieldByIndex(field.Index) + f.Set(reflect.New(f.Type().Elem())) + } + slots := make([]any, len(plan.Select.ScanIndexes)) + for idx, index := range plan.Select.ScanIndexes { + slots[idx] = v.FieldByIndex(index).Addr().Interface() + } + stmt, err := db.GSQLPrepare(ctx, query, false) + if err != nil { + return err + } + err = stmt.QueryRow(ctx, args, slots) + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) +} + +// PrepareSelectQueryWhere performs the same query string preparation as [Store.SelectWhere] or [Store.SelectOneWhere]. +// The resulting query can then be executed multiple times without incurring repeated memory allocation overhead from this preparation step. +func (s Store[R]) PrepareSelectQueryWhere(partialQuery string) (PreparedSelectQuery[R], 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. + + query, err := prepareSelectQueryWhere(s.plan, partialQuery) + return PreparedSelectQuery[R]{s, query}, err +} + +// MustPrepareSelectQueryWhere is like [Store.PrepareSelectQueryWhere], but panics on error. +func (s Store[R]) MustPrepareSelectQueryWhere(partialQuery string) PreparedSelectQuery[R] { + q, err := s.PrepareSelectQueryWhere(partialQuery) + if err != nil { + panic(err.Error()) + } + return q +} + +func prepareSelectQueryWhere(plan plan, partialQuery string) (string, error) { + if plan.Select.Query == "" { + return "", errors.New("cannot execute PrepareSelectQueryWhere() because query could not be autogenerated") + } + return plan.Select.Query + partialQuery, nil +} + +// PreparedSelectQuery holds a pre-computed SELECT query that was customized by the user. +// This type is an optimization to avoid performing the same query string manipulations over and over again in hot paths. +// +// It is returned by [Store.PrepareSelectQueryWhere]. +type PreparedSelectQuery[R any] struct { + store Store[R] + query string +} + +// Select behaves the same as [Store.SelectWhere], but uses the query that was precomputed when q was constructed. +func (q PreparedSelectQuery[R]) Select(ctx context.Context, db gsql.Handle, args ...any) Selection[R] { + // 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. + return Selection[R]{startSelectQuery(ctx, db, q.store.plan, q.query, args...)} +} + +// SelectOne behaves the same as [Store.SelectOneWhere], but uses the query that was precomputed when q was constructed. +func (q PreparedSelectQuery[R]) SelectOne(ctx context.Context, db gsql.Handle, args ...any) (R, 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. + + var result R + err := selectOne(ctx, db, q.store.plan, reflect.ValueOf(&result).Elem(), q.query, args) + return result, err +} + +// TupleSelect executes the provided SQL query and fills an instance of the record type R for each row in the result set. +// Unlike [Store.Select], struct fields are matched to the result columns not based on names or struct tags, but purely based on order: +// Values from the first column are stored in the first result field, and so on. +// +// This is usually more convenient when defining an ad-hoc record type for a single query. Compare: +// +// const query = `SELECT given_name, COUNT(*) AS user_count FROM users WHERE family_name = $1 GROUP BY first_name` +// type record struct { +// GivenName string `db:"given_name"` +// UserCount uint64 `db:"user_count"` +// } +// err = oblast.MustNewStore[record](config.DB.Dialect).Select(ctx, db, query, lastName).Foreach(func(r record) error { +// return doSomethingWith(r.GivenName, r.UserCount) +// }) +// +// With: +// +// const query = `SELECT given_name, COUNT(*) FROM users WHERE family_name = $1 GROUP BY first_name` +// type record struct { +// GivenName string +// UserCount uint64 +// } +// err = oblast.TupleSelect[record](ctx, db, query, lastName).Foreach(func(r record) error { +// return doSomethingWith(r.GivenName, r.UserCount) +// }) +// +// Do not use this function with queries of the form `SELECT * FROM ...`, +// where the order of columns is not well-defined and may vary between otherwise compatible DB schemas. +func TupleSelect[R any](ctx context.Context, db gsql.Handle, query string, args ...any) Selection[R] { + // 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. + + plan := getOrBuildTuplePlan(reflect.TypeFor[R]()) + return Selection[R]{startSelectQuery(ctx, db, plan, query, args...)} +} + +// TupleSelectOne executes the provided SQL query and fills an instance of the record type R if there is exactly one row in the result set, +// following the same behavior as [TupleSelect] for mapping a row into a record. +// +// If there are no rows in the result set, [sql.ErrNoRows] is returned. +func TupleSelectOne[R any](ctx context.Context, db gsql.Handle, query string, args ...any) (R, 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. + + return TupleSelect[R](ctx, db, query, args...).First() +} + +//////////////////////////////////////////////////////////////////////////////// +// non-record selections + +// Select executes the provided SQL query that returns rows that each contain exactly one value. +func Select[T any](ctx context.Context, db gsql.Handle, query string, args ...any) Selection[T] { + // 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. + + return Selection[T]{startSelectValueQuery(ctx, db, query, args)} +} + +func startSelectValueQuery(ctx context.Context, db gsql.Handle, query string, args []any) selection { + rows, err := db.GSQLQuery(ctx, query, args) + if err != nil { + return selection{Err: fmt.Errorf("during Query(): %w", err)} + } + return selection{Rows: rows} // all other members are nil because this is a non-record selection +} + +// SelectOne executes the provided SQL query that returns exactly one row containing exactly one value. +// +// This is the same as declaring a value of type T and then saying db.QueryRow(query, args...).Scan(&value) +// or whatever the equivalent for the DB handle in question is. +// +// If there are no rows in the result set, [sql.ErrNoRows] is returned. +func SelectOne[T any](ctx context.Context, db gsql.Handle, query string, args ...any) (T, 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. + + var result T + err := selectOneValue(ctx, db, &result, query, args) + return result, err +} + +func selectOneValue(ctx context.Context, db gsql.Handle, target any, query string, args []any) error { + stmt, err := db.GSQLPrepare(ctx, query, false) + if err != nil { + return err + } + err = stmt.QueryRow(ctx, args, []any{target}) + return errext.WithCleanup(err, "Stmt.Close", stmt.Close()) +} + +//////////////////////////////////////////////////////////////////////////////// +// type Selection + +// Selection provides access to the result set from a [Select], [Store.Select], [Store.SelectWhere] or [PreparedSelectQuery.Select] call. +// +// Instances of this type are not meant to be held in variables. +// Instead, chain one of its method calls directly after the Select or SelectWhere call to choose how to process the result set. +type Selection[R any] struct { + selection +} + +// selection contains the payload of [Selection]. +// This separate type does not have type arguments and thus is not duplicated by monomorphization. +type selection struct { + // from startSelectQuery() + Rows gsql.Rows + Slots []any // NOTE: len(s.Slots) == len(s.Indexes); will be empty for non-record selections (created by Select[T]) + Err error // NOTE: if this field is set, all other fields will be unset + // from plan; will all be empty for non-record selections (created by Select[T]) + Indexes [][]int + TransparentPointerStructFields []fieldInfo +} + +func (s selection) collectRowOrValue(pointerToTarget any) error { + if len(s.Slots) > 0 { + return s.collectRow(reflect.ValueOf(pointerToTarget).Elem(), s.Slots) + } else { + return s.collectValue(pointerToTarget) + } +} + +func (s selection) collectRow(v reflect.Value, slots []any) error { + for _, field := range s.TransparentPointerStructFields { + f := v.FieldByIndex(field.Index) + f.Set(reflect.New(f.Type().Elem())) + } + for idx, index := range s.Indexes { + slots[idx] = v.FieldByIndex(index).Addr().Interface() + } + err := s.Rows.Scan(slots...) + if err != nil { + return errext.WithCleanup(err, "Rows.Close", s.Rows.Close()) + } + return nil +} + +func (s selection) collectValue(pointerToTarget any) error { + err := s.Rows.Scan(pointerToTarget) + if err != nil { + return errext.WithCleanup(err, "Rows.Close", s.Rows.Close()) + } + return nil +} + +// Collect returns all of the selected records as a slice. +// This is the most versatile output format for type [Selection], but may cause a spike in memory usage for big result sets. +func (s Selection[R]) Collect() ([]R, 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. + + if s.Err != nil { + return nil, s.Err + } + + var result []R + for s.Rows.Next() { + var target *R + result, target = growRecordSlice(result) + err := s.collectRowOrValue(target) + if err != nil { + return nil, err + } + } + + return result, s.Rows.Err() +} + +// Appends an empty R to the slice and returns a pointer to it, as well as the updated slice. +// It is more efficient to write: +// +// var result []R +// for rows.Next() { +// var target *R +// result, target = growRecordSlice(result) +// doSomethingWith(rows, reflect.ValueOf(target).Elem()) +// } +// +// Instead of the more obvious: +// +// var result []R +// for rows.Next() { +// var target R +// doSomethingWith(rows, reflect.ValueOf(&target).Elem()) +// result = append(result, target) +// } +// +// In the second phrasing, `target` escapes to the heap because of `reflect.ValueOf(&target)`, +// causing an additional allocation for `target` as well as a memcpy of `target` during `append()`. +func growRecordSlice[R any](records []R) (newRecords []R, target *R) { + var zero R + newRecords = append(records, zero) + return newRecords, &newRecords[len(newRecords)-1] +} + +// Foreach retrieves the selected records one at a time, and calls the provided callback once for each record in order. +// An error is returned if a database error occurs, of if any of the callback invocations returns an error. +// In either case, subsequent records from the result set will not be loaded and the callbgck will not be invoked again. +func (s Selection[R]) Foreach(action func(R) error) 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. + + if s.Err != nil { + return s.Err + } + + // NOTE: `record` will escape to the heap because of the reflect.ValueOf() call. + // By reusing the same `record` throughout the loop, this function will only allocate at most one instance of R on the heap. + var ( + record R + v reflect.Value + isRecord = len(s.Slots) > 0 + ) + if isRecord { + v = reflect.ValueOf(&record).Elem() + } + for s.Rows.Next() { + var ( + zero R + err error + ) + record = zero + if isRecord { + err = s.collectRow(v, s.Slots) + } else { + err = s.collectValue(&record) + } + if err != nil { + return err + } + err = action(record) + if err != nil { + return errext.WithCleanup(err, "Rows.Close", s.Rows.Close()) + } + } + return nil +} + +// First retrieves just the first record from the result set, and then closes the result set without checking for additional records. +// If there are no rows in the result set, [sql.ErrNoRows] is returned. +// Using this method results in similar behavior to [Store.SelectOne]. +func (s Selection[R]) First() (R, 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. + + var record R + if s.Err != nil { + return record, s.Err + } + if !s.Rows.Next() { + return record, sql.ErrNoRows + } + err := s.collectRowOrValue(&record) + if err == nil { + err = s.Rows.Close() + } + return record, err +} diff --git a/oblast/select_test.go b/oblast/select_test.go new file mode 100644 index 0000000..0318d4e --- /dev/null +++ b/oblast/select_test.go @@ -0,0 +1,646 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package oblast_test + +import ( + "database/sql" + "errors" + "testing" + "time" + + "go.xyrillian.de/gg/assert" + "go.xyrillian.de/gg/gsql" + "go.xyrillian.de/gg/oblast" + "go.xyrillian.de/gg/oblast/internal/testhelpers/mock" + "go.xyrillian.de/gg/oblast/internal/testhelpers/must" +) + +func TestSelectReturningSomeRecords(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + t.Run("using Store.Select", func(t *testing.T) { + md.ForQuery(`SELECT * FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name", "id"). + WithRow("foo", 1). + WithRow("bar", 2) + records := must.Return(store.Select(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3).Collect())(t) + assert.Equal(t, records, []basicRecord{ + {1, "foo"}, + {2, "bar"}, + }) + }) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name"). + WithRow(1, "ffoo"). + WithRow(2, "bbar") + records := must.Return(store.SelectWhere(ctx, db, `id < ?`, 3).Collect())(t) + assert.Equal(t, records, []basicRecord{ + {1, "ffoo"}, + {2, "bbar"}, + }) + }) + + t.Run("using PreparedSelectQuery.Select", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name"). + WithRow(1, "fffoo"). + WithRow(2, "bbbar") + query := store.MustPrepareSelectQueryWhere(`id < ?`) + records := must.Return(query.Select(ctx, db, 3).Collect())(t) + assert.Equal(t, records, []basicRecord{ + {1, "fffoo"}, + {2, "bbbar"}, + }) + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + md.ForQuery(`SELECT * FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name", "id"). + WithRow("ffffoo", 1). + WithRow("bbbbar", 2) + record := must.Return(store.SelectOne(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3))(t) + assert.Equal(t, record, basicRecord{1, "ffffoo"}) + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name"). + WithRow(1, "fffffoo"). + WithRow(2, "bbbbbar") + record := must.Return(store.SelectOneWhere(ctx, db, `id < ?`, 3))(t) + assert.Equal(t, record, basicRecord{1, "fffffoo"}) + }) + + t.Run("using PreparedSelectQuery.SelectOne", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name"). + WithRow(1, "ffffffoo"). + WithRow(2, "bbbbbbar") + query := store.MustPrepareSelectQueryWhere(`id < ?`) + record := must.Return(query.SelectOne(ctx, db, 3))(t) + assert.Equal(t, record, basicRecord{1, "ffffffoo"}) + }) + + commonSetupForValueSelect := func() { + md.ForQuery(`SELECT name FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name"). + WithRow("foo"). + WithRow("bar") + } + + t.Run("using oblast.Select", func(t *testing.T) { + commonSetupForValueSelect() + names := must.Return(oblast.Select[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3).Collect())(t) + assert.Equal(t, names, []string{"foo", "bar"}) + }) + + t.Run("using oblast.SelectOne", func(t *testing.T) { + commonSetupForValueSelect() + name := must.Return(oblast.SelectOne[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3))(t) + assert.Equal(t, name, "foo") + }) + + type tupleRecord struct { + ID int64 + Name string + } + commonSetupForTupleSelect := func() { + md.ForQuery(`SELECT id, name FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name"). + WithRow(1, "foo"). + WithRow(2, "bar") + } + + t.Run("using TupleSelect", func(t *testing.T) { + commonSetupForTupleSelect() + records := must.Return(oblast.TupleSelect[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3).Collect())(t) + assert.Equal(t, records, []tupleRecord{ + {1, "foo"}, + {2, "bar"}, + }) + }) + + t.Run("using TupleSelectOne", func(t *testing.T) { + commonSetupForTupleSelect() + record := must.Return(oblast.TupleSelectOne[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3))(t) + assert.Equal(t, record, tupleRecord{1, "foo"}) + }) +} + +func TestSelectReturningNoRecords(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + t.Run("using Store.Select", func(t *testing.T) { + md.ForQuery(`SELECT * FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name", "id") + records := must.Return(store.Select(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3).Collect())(t) + assert.Equal(t, records, nil) + }) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name") + records := must.Return(store.SelectWhere(ctx, db, `id < ?`, 3).Collect())(t) + assert.Equal(t, records, nil) + }) + + t.Run("using PreparedSelectQuery.Select", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name") + query := store.MustPrepareSelectQueryWhere(`id < ?`) + records := must.Return(query.Select(ctx, db, 3).Collect())(t) + assert.Equal(t, records, nil) + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + md.ForQuery(`SELECT * FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name", "id") + _, err := store.SelectOne(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, sql.ErrNoRows.Error()) + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name") + _, err := store.SelectOneWhere(ctx, db, `id < ?`, 3) + assert.ErrEqual(t, err, sql.ErrNoRows.Error()) + }) + + t.Run("using PreparedSelectQuery.SelectOne", func(t *testing.T) { + md.ForQuery(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name") + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.SelectOne(ctx, db, 3) + assert.ErrEqual(t, err, sql.ErrNoRows.Error()) + }) + + commonSetupForValueSelect := func() { + md.ForQuery(`SELECT name FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name") + } + + t.Run("using oblast.Select", func(t *testing.T) { + commonSetupForValueSelect() + names := must.Return(oblast.Select[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3).Collect())(t) + assert.Equal(t, names, nil) + }) + + t.Run("using oblast.SelectOne", func(t *testing.T) { + commonSetupForValueSelect() + _, err := oblast.SelectOne[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, sql.ErrNoRows.Error()) + }) + + type tupleRecord struct { + ID int64 + Name string + } + commonSetupForTupleSelect := func() { + md.ForQuery(`SELECT id, name FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name") + } + + t.Run("using TupleSelect", func(t *testing.T) { + commonSetupForTupleSelect() + records := must.Return(oblast.TupleSelect[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3).Collect())(t) + assert.Equal(t, records, nil) + }) + + t.Run("using TupleSelectOne", func(t *testing.T) { + commonSetupForTupleSelect() + _, err := oblast.TupleSelectOne[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, sql.ErrNoRows.Error()) + }) +} + +func TestSelectIntoUnexpectedField(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + Description string `db:"desc"` // but DB knows only the field "name"! + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + expectedError := "result has column \"name\" in position 0, but no field in type basicRecord has `db:\"name\"`" + commonSetup := func() { + md.ForQuery(`SELECT * FROM basic_records WHERE id < ?`). + ExpectQueryWithArgs(3). + AndReturnColumns("name", "id"). + WithRow("foo", 1). + WithRow("bar", 2) + } + + // NOTE: This problem cannot occur with SelectWhere() and SelectOneWhere() because of their use of query generation. + + t.Run("using Store.Select", func(t *testing.T) { + commonSetup() + _, err := store.Select(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, expectedError) + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + commonSetup() + _, err := store.SelectOne(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, expectedError) + }) +} + +func TestSelectWithScanError(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + CreatedAt time.Time `db:"created_at"` // but the DB will give us strings that are not timestamps + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + expectedError := `sql: Scan error on column index 1, name "created_at": unsupported Scan, storing driver.Value type string into type *time.Time` + commonSetup := func(query string) { + md.ForQuery(query). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "created_at"). + WithRow(1, "foo"). + WithRow(2, "bar") + } + + t.Run("using Store.Select", func(t *testing.T) { + commonSetup(`SELECT * FROM basic_records WHERE id < ?`) + _, err := store.Select(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, expectedError) + }) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at" FROM "basic_records" WHERE id < ?`) + _, err := store.SelectWhere(ctx, db, `id < ?`, 3).Collect() + assert.ErrEqual(t, err, expectedError) + }) + + t.Run("using PreparedSelectQuery.Select", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at" FROM "basic_records" WHERE id < ?`) + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.Select(ctx, db, 3).Collect() + assert.ErrEqual(t, err, expectedError) + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + commonSetup(`SELECT * FROM basic_records WHERE id < ?`) + _, err := store.SelectOne(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, expectedError) + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at" FROM "basic_records" WHERE id < ?`) + _, err := store.SelectOneWhere(ctx, db, `id < ?`, 3) + assert.ErrEqual(t, err, expectedError) + }) + + t.Run("using PreparedSelectQuery.SelectOne", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at" FROM "basic_records" WHERE id < ?`) + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.SelectOne(ctx, db, 3) + assert.ErrEqual(t, err, expectedError) + }) +} + +func TestSelectIntoEmbeddedTypes(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type HasCreatedAt struct { + CreatedAt time.Time `db:"created_at"` + } + type HasUpdatedAt struct { + UpdatedAt *time.Time `db:"updated_at"` + } + type compositeRecord struct { + ID int64 `db:"id"` + HasCreatedAt + // This test specifically wants to see that this field gets initialized + // whenever one of the Store.Select methods creates a compositeRecord instance. + *HasUpdatedAt + } + store := oblast.MustNewStore[compositeRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("composite_records"), + oblast.PrimaryKeyIs("id"), + ) + + commonSetup := func(query string) { + md.ForQuery(query). + ExpectQueryWithArgs(nil...). + AndReturnColumns("id", "created_at", "updated_at"). + WithRow(1, time.Unix(1, 0), time.Unix(3, 0)). + WithRow(2, time.Unix(2, 0), nil) + } + + t.Run("using Store.Select", func(t *testing.T) { + commonSetup(`SELECT * FROM composite_records`) + records := must.Return(store.Select(ctx, db, `SELECT * FROM composite_records`).Collect())(t) + assert.Equal(t, records, []compositeRecord{ + {1, HasCreatedAt{time.Unix(1, 0)}, &HasUpdatedAt{new(time.Unix(3, 0))}}, + {2, HasCreatedAt{time.Unix(2, 0)}, &HasUpdatedAt{nil}}, + }) + }) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at", "updated_at" FROM "composite_records" WHERE TRUE`) + records := must.Return(store.SelectWhere(ctx, db, `TRUE`).Collect())(t) + assert.Equal(t, records, []compositeRecord{ + {1, HasCreatedAt{time.Unix(1, 0)}, &HasUpdatedAt{new(time.Unix(3, 0))}}, + {2, HasCreatedAt{time.Unix(2, 0)}, &HasUpdatedAt{nil}}, + }) + }) + + t.Run("using PreparedSelectQuery.Select", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at", "updated_at" FROM "composite_records" WHERE TRUE`) + query := store.MustPrepareSelectQueryWhere(`TRUE`) + records := must.Return(query.Select(ctx, db).Collect())(t) + assert.Equal(t, records, []compositeRecord{ + {1, HasCreatedAt{time.Unix(1, 0)}, &HasUpdatedAt{new(time.Unix(3, 0))}}, + {2, HasCreatedAt{time.Unix(2, 0)}, &HasUpdatedAt{nil}}, + }) + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + commonSetup(`SELECT * FROM composite_records`) + record := must.Return(store.SelectOne(ctx, db, `SELECT * FROM composite_records`))(t) + assert.Equal(t, record, + compositeRecord{1, HasCreatedAt{time.Unix(1, 0)}, &HasUpdatedAt{new(time.Unix(3, 0))}}, + ) + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at", "updated_at" FROM "composite_records" WHERE TRUE`) + record := must.Return(store.SelectOneWhere(ctx, db, `TRUE`))(t) + assert.Equal(t, record, + compositeRecord{1, HasCreatedAt{time.Unix(1, 0)}, &HasUpdatedAt{new(time.Unix(3, 0))}}, + ) + }) + + t.Run("using PreparedSelectQuery.SelectOne", func(t *testing.T) { + commonSetup(`SELECT "id", "created_at", "updated_at" FROM "composite_records" WHERE TRUE`) + query := store.MustPrepareSelectQueryWhere(`TRUE`) + record := must.Return(query.SelectOne(ctx, db))(t) + assert.Equal(t, record, + compositeRecord{1, HasCreatedAt{time.Unix(1, 0)}, &HasUpdatedAt{new(time.Unix(3, 0))}}, + ) + }) +} + +func TestSelectCapturingQueryError(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + t.Run("using Store.Select", func(t *testing.T) { + _, err := store.Select(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, "during Query(): unexpected query: SELECT * FROM basic_records WHERE id < ?") + }) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + _, err := store.SelectWhere(ctx, db, `id < ?`, 3).Collect() + assert.ErrEqual(t, err, `during Query(): unexpected query: SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + }) + + t.Run("using PreparedSelectQuery.Select", func(t *testing.T) { + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.Select(ctx, db, 3).Collect() + assert.ErrEqual(t, err, `during Query(): unexpected query: SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + _, err := store.SelectOne(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, "during Query(): unexpected query: SELECT * FROM basic_records WHERE id < ?") + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + _, err := store.SelectOneWhere(ctx, db, `id < ?`, 3) + assert.ErrEqual(t, err, `unexpected query: SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + }) + + t.Run("using PreparedSelectQuery.SelectOne", func(t *testing.T) { + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.SelectOne(ctx, db, 3) + assert.ErrEqual(t, err, `unexpected query: SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + }) + + t.Run("using oblast.Select", func(t *testing.T) { + _, err := oblast.Select[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, "during Query(): unexpected query: SELECT name FROM basic_records WHERE id < ?") + }) + + t.Run("using oblast.SelectOne", func(t *testing.T) { + _, err := oblast.SelectOne[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, "unexpected query: SELECT name FROM basic_records WHERE id < ?") + }) + + type tupleRecord struct { + ID int64 + Name string + } + + t.Run("using oblast.TupleSelect", func(t *testing.T) { + _, err := oblast.TupleSelect[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, "during Query(): unexpected query: SELECT id, name FROM basic_records WHERE id < ?") + }) + + t.Run("using oblast.TupleSelectOne", func(t *testing.T) { + _, err := oblast.TupleSelectOne[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, "during Query(): unexpected query: SELECT id, name FROM basic_records WHERE id < ?") + }) +} + +func TestSelectCapturingCloseError(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord]( + oblast.SqliteDialect(), + oblast.TableNameIs("basic_records"), + oblast.PrimaryKeyIs("id"), + ) + + commonSetup := func(query string) { + md.ForQuery(query). + ExpectQueryWithArgs(3). + AndReturnColumns("id", "name"). + WithRow(1, "foo"). + WithRow(2, "bar"). + AndCloseFailsWith(errors.New("datacenter on fire")) + } + + t.Run("using Store.Select", func(t *testing.T) { + commonSetup(`SELECT * FROM basic_records WHERE id < ?`) + _, err := store.Select(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + commonSetup(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + _, err := store.SelectWhere(ctx, db, `id < ?`, 3).Collect() + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using PreparedSelectQuery.Select", func(t *testing.T) { + commonSetup(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.Select(ctx, db, 3).Collect() + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using Store.SelectOne", func(t *testing.T) { + commonSetup(`SELECT * FROM basic_records WHERE id < ?`) + _, err := store.SelectOne(ctx, db, `SELECT * FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + commonSetup(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + _, err := store.SelectOneWhere(ctx, db, `id < ?`, 3) + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using PreparedSelectQuery.SelectOne", func(t *testing.T) { + commonSetup(`SELECT "id", "name" FROM "basic_records" WHERE id < ?`) + query := store.MustPrepareSelectQueryWhere(`id < ?`) + _, err := query.SelectOne(ctx, db, 3) + assert.ErrEqual(t, err, "datacenter on fire") + }) + + type tupleRecord struct { + ID int64 + Name string + } + + t.Run("using oblast.TupleSelect", func(t *testing.T) { + commonSetup(`SELECT id, name FROM basic_records WHERE id < ?`) + _, err := oblast.TupleSelect[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using oblast.TupleSelectOne", func(t *testing.T) { + commonSetup(`SELECT id, name FROM basic_records WHERE id < ?`) + _, err := oblast.TupleSelectOne[tupleRecord](ctx, db, `SELECT id, name FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, "datacenter on fire") + }) + + commonSetup = func(query string) { + md.ForQuery(query). + ExpectQueryWithArgs(3). + AndReturnColumns("name"). + WithRow("foo"). + WithRow("bar"). + AndCloseFailsWith(errors.New("datacenter on fire")) + } + + t.Run("using oblast.Select", func(t *testing.T) { + commonSetup(`SELECT name FROM basic_records WHERE id < ?`) + _, err := oblast.Select[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3).Collect() + assert.ErrEqual(t, err, "datacenter on fire") + }) + + t.Run("using oblast.SelectOne", func(t *testing.T) { + commonSetup(`SELECT name FROM basic_records WHERE id < ?`) + _, err := oblast.SelectOne[string](ctx, db, `SELECT name FROM basic_records WHERE id < ?`, 3) + assert.ErrEqual(t, err, "datacenter on fire") + }) +} + +func TestSelectNotPossibleWithoutTableName(t *testing.T) { + ctx := t.Context() + md := mock.NewDriver() + db := gsql.NewDB(sql.OpenDB(md)) + + type basicRecord struct { + ID int64 `db:"id"` + Name string `db:"name"` + } + store := oblast.MustNewStore[basicRecord](oblast.SqliteDialect()) + + t.Run("using Store.SelectWhere", func(t *testing.T) { + _, err := store.SelectWhere(ctx, db, `id < ?`, 3).Collect() + assert.ErrEqual(t, err, "cannot execute SelectWhere() because query could not be autogenerated") + }) + + t.Run("using Store.SelectOneWhere", func(t *testing.T) { + _, err := store.SelectOneWhere(ctx, db, `id < ?`, 3) + assert.ErrEqual(t, err, "cannot execute SelectOneWhere() because query could not be autogenerated") + }) + + t.Run("using PreparedSelectQuery", func(t *testing.T) { + _, err := store.PrepareSelectQueryWhere(`id < ?`) + assert.ErrEqual(t, err, "cannot execute PrepareSelectQueryWhere() because query could not be autogenerated") + }) +} |