insert_test.go

package sqlinsert

import (
	"context"
	"database/sql"
	"errors"
	"reflect"
	"strings"
	"testing"
	"time"
)

// Compile-time proof of which database/sql types satisfy the package interfaces.
var (
	_ Execer        = (*sql.DB)(nil)
	_ Execer        = (*sql.Tx)(nil)
	_ ExecerContext = (*sql.DB)(nil)
	_ ExecerContext = (*sql.Tx)(nil)
	_ ExecerContext = (*sql.Conn)(nil)
)

/* Hand-written expected SQL fragments. Deliberately not derived from package output,
so the tests fail if generation regresses. */

const (
	questionParams  = `(?,?,?,?,?,?,?)`
	ordinalParams   = `($1,$2,$3,$4,$5,$6,$7)`
	atOrdinalParams = `(@p1,@p2,@p3,@p4,@p5,@p6,@p7)`
	atColumnParams  = `(@id,@candy_name,@form_factor,@description,@manufacturer,@weight_grams,@ts)`
	colonParams     = `(:id,:candy_name,:form_factor,:description,:manufacturer,:weight_grams,:ts)`

	questionParamsFiveRows = `(?,?,?,?,?,?,?),(?,?,?,?,?,?,?),(?,?,?,?,?,?,?),(?,?,?,?,?,?,?),(?,?,?,?,?,?,?)`
	ordinalParamsFiveRows  = `($1,$2,$3,$4,$5,$6,$7),($8,$9,$10,$11,$12,$13,$14),($15,$16,$17,$18,$19,$20,$21),` +
		`($22,$23,$24,$25,$26,$27,$28),($29,$30,$31,$32,$33,$34,$35)`
	atOrdinalParamsFiveRows = `(@p1,@p2,@p3,@p4,@p5,@p6,@p7),(@p8,@p9,@p10,@p11,@p12,@p13,@p14),` +
		`(@p15,@p16,@p17,@p18,@p19,@p20,@p21),(@p22,@p23,@p24,@p25,@p26,@p27,@p28),` +
		`(@p29,@p30,@p31,@p32,@p33,@p34,@p35)`
)

func wantErrContaining(t *testing.T, err error, substr string) {
	t.Helper()
	if err == nil {
		t.Fatalf(`expected error containing %q, got nil`, substr)
	}
	if !strings.Contains(err.Error(), substr) {
		t.Fatalf(`expected error containing %q, got %q`, substr, err.Error())
	}
}

/* Insert.Columns */

func TestColumns(t *testing.T) {
	t.Parallel()
	for name, data := range map[string]any{
		`OneRecValue`:      recValue,
		`OneRecPointer`:    recPointer,
		`ManyRecsValues`:   fiveRecsValues,
		`ManyRecsPointers`: fiveRecsPointers,
	} {
		data := data
		t.Run(name, func(t *testing.T) {
			t.Parallel()
			ins := Insert{Table: tbl, Data: data}
			columns, err := ins.Columns()
			if err != nil {
				t.Fatalf(`unexpected error: %s`, err)
			}
			if candyColumns != columns {
				t.Fatalf(`expected %q, got %q`, candyColumns, columns)
			}
		})
	}
}

/* Insert.Params */

func TestParams(t *testing.T) {
	t.Parallel()
	tests := []struct {
		name      string
		tokenType TokenType
		data      any
		want      string
		wantErr   string
	}{
		{`QuestionMarkOneRec`, QuestionMarkTokenType, recValue, questionParams, ``},
		{`QuestionMarkOneRecPointer`, QuestionMarkTokenType, recPointer, questionParams, ``},
		{`OrdinalOneRec`, OrdinalNumberTokenType, recValue, ordinalParams, ``},
		{`AtOrdinalOneRec`, AtOrdinalTokenType, recValue, atOrdinalParams, ``},
		{`AtColumnNameOneRec`, AtColumnNameTokenType, recValue, atColumnParams, ``},
		{`ColonOneRec`, ColonTokenType, recValue, colonParams, ``},
		{`QuestionMarkManyRecs`, QuestionMarkTokenType, fiveRecsValues, questionParamsFiveRows, ``},
		{`OrdinalManyRecs`, OrdinalNumberTokenType, fiveRecsValues, ordinalParamsFiveRows, ``},
		{`OrdinalManyRecsPointers`, OrdinalNumberTokenType, fiveRecsPointers, ordinalParamsFiveRows, ``},
		{`AtOrdinalManyRecs`, AtOrdinalTokenType, fiveRecsValues, atOrdinalParamsFiveRows, ``},
		{`AtColumnNameManyRecsErrors`, AtColumnNameTokenType, fiveRecsValues, ``, `single-row inserts only`},
		{`ColonManyRecsErrors`, ColonTokenType, fiveRecsValues, ``, `single-row inserts only`},
		{`UnknownTokenTypeErrors`, TokenType(99), recValue, ``, `unknown TokenType(99)`},
	}
	for _, tc := range tests {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()
			ins := Insert{Table: tbl, Data: tc.data, TokenType: tc.tokenType}
			params, err := ins.Params()
			if tc.wantErr != `` {
				wantErrContaining(t, err, tc.wantErr)
				return
			}
			if err != nil {
				t.Fatalf(`unexpected error: %s`, err)
			}
			if tc.want != params {
				t.Fatalf(`expected %q, got %q`, tc.want, params)
			}
		})
	}
}

/* Insert.SQL */

func TestSQL(t *testing.T) {
	t.Parallel()
	tests := []struct {
		name      string
		tokenType TokenType
		data      any
		want      string
	}{
		{`OneRecDefaultTokenType`, QuestionMarkTokenType, recValue,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + questionParams},
		{`OneRecOrdinal`, OrdinalNumberTokenType, recPointer,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + ordinalParams},
		{`ManyRecsOrdinal`, OrdinalNumberTokenType, fiveRecsValues,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + ordinalParamsFiveRows},
		{`ManyRecsPointersOrdinal`, OrdinalNumberTokenType, fiveRecsPointers,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + ordinalParamsFiveRows},
	}
	for _, tc := range tests {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()
			ins := Insert{Table: tbl, Data: tc.data, TokenType: tc.tokenType}
			insertSQL, err := ins.SQL()
			if err != nil {
				t.Fatalf(`unexpected error: %s`, err)
			}
			if tc.want != insertSQL {
				t.Fatalf(`expected %q, got %q`, tc.want, insertSQL)
			}
		})
	}
}

func TestSQLEmptyTableErrors(t *testing.T) {
	t.Parallel()
	ins := Insert{Data: recValue}
	_, err := ins.SQL()
	wantErrContaining(t, err, `Table is empty`)
}

/* Insert.Args */

func TestArgs(t *testing.T) {
	t.Parallel()
	tests := []struct {
		name string
		data any
		want []any
	}{
		{`OneRecValue`, recValue, recValueArgs},
		{`OneRecPointer`, recPointer, recValueArgs},
		{`ManyRecsValues`, fiveRecsValues, fiveRecsArgs},
		{`ManyRecsPointers`, fiveRecsPointers, fiveRecsArgs},
	}
	for _, tc := range tests {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()
			ins := Insert{Table: tbl, Data: tc.data}
			args, err := ins.Args()
			if err != nil {
				t.Fatalf(`unexpected error: %s`, err)
			}
			if !reflect.DeepEqual(tc.want, args) {
				t.Fatalf(`expected %v, got %v`, tc.want, args)
			}
		})
	}
}

/* Field selection: skipping, embedding, tag errors */

type Audit struct {
	CreatedBy string    `col:"created_by"`
	CreatedAt time.Time `col:"created_at"`
}

func TestFieldSkipping(t *testing.T) {
	t.Parallel()
	type skippy struct {
		Id     string `col:"id"`
		NoTag  string
		Dash   string `col:"-"`
		hidden string
		Empty  string `col:""`
		Name   string `col:"name"`
	}
	_ = skippy{}.hidden
	ins := Insert{Table: `t`, Data: skippy{Id: `1`, NoTag: `x`, Dash: `x`, Empty: `x`, Name: `n`}}
	columns, err := ins.Columns()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := `(id,name)`; expected != columns {
		t.Fatalf(`expected %q, got %q`, expected, columns)
	}
	args, err := ins.Args()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := []any{`1`, `n`}; !reflect.DeepEqual(expected, args) {
		t.Fatalf(`expected %v, got %v`, expected, args)
	}
}

func TestEmbeddedStructFlattening(t *testing.T) {
	t.Parallel()
	type widget struct {
		Audit
		Id string `col:"id"`
	}
	w := widget{Audit: Audit{CreatedBy: `zv`}, Id: `1`}
	ins := Insert{Table: `t`, Data: w}
	columns, err := ins.Columns()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := `(created_by,created_at,id)`; expected != columns {
		t.Fatalf(`expected %q, got %q`, expected, columns)
	}
	args, err := ins.Args()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := []any{`zv`, time.Time{}, `1`}; !reflect.DeepEqual(expected, args) {
		t.Fatalf(`expected %v, got %v`, expected, args)
	}
}

func TestTaggedEmbeddedStructIsSingleColumn(t *testing.T) {
	t.Parallel()
	type holder struct {
		Audit `col:"audit"`
		Id    string `col:"id"`
	}
	ins := Insert{Table: `t`, Data: holder{Id: `1`}}
	columns, err := ins.Columns()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := `(audit,id)`; expected != columns {
		t.Fatalf(`expected %q, got %q`, expected, columns)
	}
}

func TestTaggedUnexportedFieldErrors(t *testing.T) {
	t.Parallel()
	type badTag struct {
		Id   string `col:"id"`
		oops string `col:"oops"`
	}
	_ = badTag{}.oops
	ins := Insert{Table: `t`, Data: badTag{}}
	_, err := ins.Columns()
	wantErrContaining(t, err, `unexported`)
}

func TestNoInsertableFieldsErrors(t *testing.T) {
	t.Parallel()
	type bare struct{ A, B string }
	ins := Insert{Table: `t`, Data: bare{}}
	_, err := ins.Columns()
	wantErrContaining(t, err, `no insertable fields`)
	_, err = ins.Params()
	wantErrContaining(t, err, `no insertable fields`)
	_, err = ins.SQL()
	wantErrContaining(t, err, `no insertable fields`)
	_, err = ins.Args()
	wantErrContaining(t, err, `no insertable fields`)
}

func TestCustomStructTag(t *testing.T) {
	t.Parallel()
	type dbTagged struct {
		Id string `db:"id"`
		N  int    `db:"n"`
	}
	ins := Insert{Table: `t`, Data: dbTagged{Id: `1`, N: 2}, StructTag: `db`}
	insertSQL, err := ins.SQL()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := `INSERT INTO t (id,n) VALUES (?,?)`; expected != insertSQL {
		t.Fatalf(`expected %q, got %q`, expected, insertSQL)
	}
}

/* Data validation */

func TestDataValidation(t *testing.T) {
	t.Parallel()
	type other struct {
		X string `col:"x"`
	}
	tests := []struct {
		name    string
		data    any
		wantErr string
	}{
		{`Nil`, nil, `Data is nil`},
		{`NilPointer`, (*candyInsert)(nil), `Data is nil`},
		{`NonStruct`, 42, `want a struct`},
		{`NonStructString`, `nope`, `want a struct`},
		{`EmptySlice`, []candyInsert{}, `slice is empty`},
		{`SliceWithNilPointer`, []*candyInsert{nil}, `Data[0] is nil`},
		{`SliceOfNonStruct`, []int{1, 2}, `want a struct`},
		{`MixedSlice`, []any{recValue, other{}}, `share one struct type`},
	}
	for _, tc := range tests {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()
			ins := Insert{Table: tbl, Data: tc.data}
			_, err := ins.SQL()
			wantErrContaining(t, err, tc.wantErr)
			_, err = ins.Args()
			wantErrContaining(t, err, tc.wantErr)
		})
	}
}

func TestValidationErrorsMatchSentinel(t *testing.T) {
	t.Parallel()
	for name, ins := range map[string]Insert{
		`NilData`:        {Table: tbl, Data: nil},
		`EmptyTable`:     {Data: recValue},
		`EmptySlice`:     {Table: tbl, Data: []candyInsert{}},
		`NamedMultiRow`:  {Table: tbl, Data: fiveRecsValues, TokenType: ColonTokenType},
		`UnknownToken`:   {Table: tbl, Data: recValue, TokenType: TokenType(99)},
		`NoUsableFields`: {Table: tbl, Data: struct{ A string }{}},
	} {
		ins := ins
		t.Run(name, func(t *testing.T) {
			t.Parallel()
			_, err := ins.SQL()
			if !errors.Is(err, ErrInvalidInsert) {
				t.Fatalf(`expected errors.Is(err, ErrInvalidInsert), got %v`, err)
			}
		})
	}
}

func TestHomogeneousInterfaceSlice(t *testing.T) {
	t.Parallel()
	ins := Insert{Table: tbl, Data: []any{recValue, recPointer}}
	params, err := ins.Params()
	if err != nil {
		t.Fatalf(`unexpected error: %s`, err)
	}
	if expected := questionParams + `,` + questionParams; expected != params {
		t.Fatalf(`expected %q, got %q`, expected, params)
	}
}

/* Insert.Exec, Insert.ExecContext */

func TestExec(t *testing.T) {
	t.Parallel()
	tests := []struct {
		name      string
		tokenType TokenType
		data      any
		wantSQL   string
		wantArgs  []any
		wantRows  int64
	}{
		{`OneRecQuestionMark`, QuestionMarkTokenType, recValue,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + questionParams, recValueArgs, 1},
		{`OneRecOrdinal`, OrdinalNumberTokenType, recValue,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + ordinalParams, recValueArgs, 1},
		{`OneRecAtOrdinal`, AtOrdinalTokenType, recValue,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + atOrdinalParams, recValueArgs, 1},
		{`OneRecAtColumnName`, AtColumnNameTokenType, recValue,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + atColumnParams, recValueArgs, 1},
		{`OneRecColon`, ColonTokenType, recValue,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + colonParams, recValueArgs, 1},
		{`ManyRecsQuestionMark`, QuestionMarkTokenType, fiveRecsValues,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + questionParamsFiveRows, fiveRecsArgs, 5},
		{`ManyRecsOrdinal`, OrdinalNumberTokenType, fiveRecsPointers,
			`INSERT INTO candy ` + candyColumns + ` VALUES ` + ordinalParamsFiveRows, fiveRecsArgs, 5},
	}
	for _, tc := range tests {
		tc := tc
		t.Run(tc.name, func(t *testing.T) {
			t.Parallel()
			db, conn := newFakeDB(tc.wantRows)
			defer func() { _ = db.Close() }()
			ins := Insert{Table: tbl, Data: tc.data, TokenType: tc.tokenType}
			result, err := ins.Exec(db)
			if err != nil {
				t.Fatalf(`Exec failed: %s`, err)
			}
			rows, err := result.RowsAffected()
			if err != nil {
				t.Fatalf(`RowsAffected failed: %s`, err)
			}
			if tc.wantRows != rows {
				t.Fatalf(`expected %d rows affected, got %d`, tc.wantRows, rows)
			}
			if len(conn.queries) != 1 {
				t.Fatalf(`expected 1 executed statement, got %d`, len(conn.queries))
			}
			query, args := conn.call(0)
			if tc.wantSQL != query {
				t.Fatalf(`expected query %q, got %q`, tc.wantSQL, query)
			}
			if !reflect.DeepEqual(tc.wantArgs, args) {
				t.Fatalf(`expected args %v, got %v`, tc.wantArgs, args)
			}
		})
	}
}

func TestExecContext(t *testing.T) {
	t.Parallel()
	db, conn := newFakeDB(5)
	defer func() { _ = db.Close() }()
	ins := Insert{Table: tbl, Data: fiveRecsValues, TokenType: OrdinalNumberTokenType}
	result, err := ins.ExecContext(context.Background(), db)
	if err != nil {
		t.Fatalf(`ExecContext failed: %s`, err)
	}
	rows, err := result.RowsAffected()
	if err != nil {
		t.Fatalf(`RowsAffected failed: %s`, err)
	}
	if rows != 5 {
		t.Fatalf(`expected 5 rows affected, got %d`, rows)
	}
	query, args := conn.call(0)
	if wantSQL := `INSERT INTO candy ` + candyColumns + ` VALUES ` + ordinalParamsFiveRows; wantSQL != query {
		t.Fatalf(`expected query %q, got %q`, wantSQL, query)
	}
	if !reflect.DeepEqual(fiveRecsArgs, args) {
		t.Fatalf(`expected args %v, got %v`, fiveRecsArgs, args)
	}
}

// TestExecContextOnConn proves ExecerContext works with *sql.Conn, which only has
// context-variant methods.
func TestExecContextOnConn(t *testing.T) {
	t.Parallel()
	db, fake := newFakeDB(1)
	defer func() { _ = db.Close() }()
	ctx := context.Background()
	conn, err := db.Conn(ctx)
	if err != nil {
		t.Fatalf(`failed to get connection: %s`, err)
	}
	defer func() { _ = conn.Close() }()
	ins := Insert{Table: tbl, Data: recValue}
	if _, err = ins.ExecContext(ctx, conn); err != nil {
		t.Fatalf(`ExecContext on *sql.Conn failed: %s`, err)
	}
	query, args := fake.call(0)
	if wantSQL := `INSERT INTO candy ` + candyColumns + ` VALUES ` + questionParams; wantSQL != query {
		t.Fatalf(`expected query %q, got %q`, wantSQL, query)
	}
	if !reflect.DeepEqual(recValueArgs, args) {
		t.Fatalf(`expected args %v, got %v`, recValueArgs, args)
	}
}

func TestExecPropagatesValidationErrors(t *testing.T) {
	t.Parallel()
	db, conn := newFakeDB(0)
	defer func() { _ = db.Close() }()
	ins := Insert{Table: tbl, Data: []candyInsert{}}
	_, err := ins.Exec(db)
	wantErrContaining(t, err, `slice is empty`)
	if !errors.Is(err, ErrInvalidInsert) {
		t.Fatalf(`expected errors.Is(err, ErrInvalidInsert), got %v`, err)
	}
	_, err = ins.ExecContext(context.Background(), db)
	wantErrContaining(t, err, `slice is empty`)
	if len(conn.queries) != 0 {
		t.Fatalf(`expected no executed statements, got %d`, len(conn.queries))
	}
}