package sink import ( "context" "database/sql" "fmt" "strings" "archer/internal/row" "archer/internal/sqlutil" ) // Execer is satisfied by *sql.DB, *sql.Tx and *sql.Conn. type Execer interface { ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) } const defaultMaxPacket = 4 << 20 // DB inserts chunk rows into the destination table in batches that stay under // max_allowed_packet. type DB struct { DBName string Table string Columns []string Mode string // "", "ignore", "replace" MaxPacket int64 OutIdx []int } // NewDB reads max_allowed_packet and returns a sink. cols are the output // column names; outIdx maps them to positions in a scanned row. func NewDB(ctx context.Context, db *sql.DB, dbName, table string, cols []string, mode string, outIdx []int) (*DB, error) { maxPacket := int64(defaultMaxPacket) var v sql.NullInt64 if err := db.QueryRowContext(ctx, "SELECT @@max_allowed_packet").Scan(&v); err == nil && v.Valid && v.Int64 > 0 { maxPacket = v.Int64 } return &DB{ DBName: dbName, Table: table, Columns: cols, Mode: mode, MaxPacket: maxPacket, OutIdx: outIdx, }, nil } func (d *DB) table() string { return sqlutil.Qualified(d.DBName, d.Table) } func (d *DB) verb() string { switch d.Mode { case "ignore": return "INSERT IGNORE INTO" case "replace": return "REPLACE INTO" default: return "INSERT INTO" } } // InsertChunk writes all chunk rows through exec. func (d *DB) InsertChunk(ctx context.Context, exec Execer, c *row.Chunk) (int64, error) { if len(c.Rows) == 0 { return 0, nil } limit := d.MaxPacket * 8 / 10 var total int64 start := 0 for start < len(c.Rows) { end := start var size int64 for end < len(c.Rows) { rowSize := int64(8 + len(d.Columns)*4) for _, i := range d.OutIdx { rowSize += int64(len(c.Rows[end].Values[i].Raw)) } if end > start && size+rowSize > limit { break } size += rowSize end++ } if end == start { end = start + 1 } n, err := d.insertBatch(ctx, exec, c.Rows[start:end]) if err != nil { return total, err } total += n start = end } return total, nil } func (d *DB) insertBatch(ctx context.Context, exec Execer, rows []row.Row) (int64, error) { var b strings.Builder b.WriteString(d.verb()) b.WriteByte(' ') b.WriteString(d.table()) b.WriteString(" (") b.WriteString(sqlutil.Columns(d.Columns)) b.WriteString(") VALUES ") tuple := sqlutil.ParamTuple(len(d.Columns)) args := make([]any, 0, len(rows)*len(d.Columns)) for n, r := range rows { if n > 0 { b.WriteByte(',') } b.WriteString(tuple) for _, i := range d.OutIdx { args = append(args, r.Values[i].DriverValue()) } } res, err := exec.ExecContext(ctx, b.String(), args...) if err != nil { return 0, fmt.Errorf("insert into %s: %w", d.Table, err) } n, err := res.RowsAffected() if err != nil { return 0, nil } return n, nil } // DryRunSQL returns a representative insert statement. func (d *DB) DryRunSQL() []string { tuple := sqlutil.ParamTuple(len(d.Columns)) return []string{fmt.Sprintf("%s %s (%s) VALUES %s, %s, ...", d.verb(), d.table(), sqlutil.Columns(d.Columns), tuple, tuple)} }