diff options
Diffstat (limited to 'oblast/select.go')
| -rw-r--r-- | oblast/select.go | 474 |
1 files changed, 474 insertions, 0 deletions
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 +} |