// Package purge deletes archived rows from the source table, either by an // explicit key IN list or by key range. package purge import ( "context" "database/sql" "fmt" "strconv" "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 maxParams = 1000 // Purger deletes rows chunk by chunk. type Purger struct { DB *sql.DB DBName string Table string KeyCols []string Where string Bulk bool SafeAutoInc bool AutoIncKey bool KeyIdx []int } // New builds a Purger. keyIdx is unused for deletion but kept for symmetry // with the scan configuration. func New(db *sql.DB, dbName, table string, keyCols []string, where string, bulk, safe, autoInc bool) *Purger { return &Purger{ DB: db, DBName: dbName, Table: table, KeyCols: keyCols, Where: where, Bulk: bulk, SafeAutoInc: safe, AutoIncKey: autoInc, } } // DeleteChunk removes the rows read in this chunk using exec. It returns the // number of affected rows. func (p *Purger) DeleteChunk(ctx context.Context, exec Execer, c *row.Chunk) (int64, error) { if len(c.Keys) == 0 { return 0, nil } keys := c.Keys var maxKey *row.KeyPart if p.SafeAutoInc && p.AutoIncKey && len(p.KeyCols) == 1 { v, err := p.currentMax(ctx) if err != nil { return 0, err } maxKey = v } if p.Bulk { return p.deleteRange(ctx, exec, c, maxKey) } return p.deleteInList(ctx, exec, keys, maxKey) } func (p *Purger) table() string { return sqlutil.Qualified(p.DBName, p.Table) } func (p *Purger) currentMax(ctx context.Context) (*row.KeyPart, error) { q := fmt.Sprintf("SELECT MAX(%s) FROM %s", sqlutil.Ident(p.KeyCols[0]), p.table()) var v sql.NullString if err := p.DB.QueryRowContext(ctx, q).Scan(&v); err != nil { return nil, fmt.Errorf("read max auto-increment key: %w", err) } if !v.Valid { return nil, nil } return &row.KeyPart{Val: v.String}, nil } func (p *Purger) deleteRange(ctx context.Context, exec Execer, c *row.Chunk, maxKey *row.KeyPart) (int64, error) { // Date scanners rewrite LastKey into a (shard column, key...) cursor for // resumption, so the bare key range must come from Keys. if len(c.Keys) == 0 { return 0, nil } lo, hi := c.Keys[0], c.Keys[len(c.Keys)-1] if lo.IsZero() || hi.IsZero() { return 0, nil } var ( q string args []any ) if len(p.KeyCols) == 1 { col := sqlutil.Ident(p.KeyCols[0]) if maxKey != nil { // Keep safe-auto-increment semantics: the current max row is // excluded even when it falls inside the range. q = fmt.Sprintf("DELETE FROM %s WHERE %s BETWEEN ? AND ? AND %s <> ? AND (%s)", p.table(), col, col, p.Where) args = []any{lo[0].DriverValue(), hi[0].DriverValue(), maxKey.DriverValue()} } else { q = fmt.Sprintf("DELETE FROM %s WHERE %s BETWEEN ? AND ? AND (%s)", p.table(), col, p.Where) args = []any{lo[0].DriverValue(), hi[0].DriverValue()} } } else { q = fmt.Sprintf("DELETE FROM %s WHERE %s >= %s AND %s <= %s AND (%s)", p.table(), sqlutil.RowTuple(p.KeyCols), sqlutil.ParamTuple(len(p.KeyCols)), sqlutil.RowTuple(p.KeyCols), sqlutil.ParamTuple(len(p.KeyCols)), p.Where) args = append(args, lo.Args()...) args = append(args, hi.Args()...) } res, err := exec.ExecContext(ctx, q, args...) if err != nil { return 0, fmt.Errorf("bulk delete: %w", err) } return res.RowsAffected() } func (p *Purger) deleteInList(ctx context.Context, exec Execer, keys []row.Key, maxKey *row.KeyPart) (int64, error) { batch := maxParams / len(p.KeyCols) if batch < 1 { batch = 1 } var total int64 for start := 0; start < len(keys); start += batch { end := start + batch if end > len(keys) { end = len(keys) } var args [][]any for _, k := range keys[start:end] { if maxKey != nil && keyEquals(k[len(k)-1], *maxKey) { continue } args = append(args, k.Args()) } if len(args) == 0 { continue } q := fmt.Sprintf("DELETE FROM %s WHERE %s AND (%s)", p.table(), sqlutil.InList(p.KeyCols, len(args)), p.Where) flat := make([]any, 0, len(args)*len(p.KeyCols)) for _, a := range args { flat = append(flat, a...) } res, err := exec.ExecContext(ctx, q, flat...) if err != nil { return total, fmt.Errorf("delete chunk: %w", err) } n, err := res.RowsAffected() if err != nil { return total, err } total += n } return total, nil } func keyEquals(a, b row.KeyPart) bool { if a.Null || b.Null { return a.Null && b.Null } if a.Val == b.Val { return true } ai, err1 := strconv.ParseInt(a.Val, 10, 64) bi, err2 := strconv.ParseInt(b.Val, 10, 64) return err1 == nil && err2 == nil && ai == bi } // DryRunSQL returns representative delete statements. func (p *Purger) DryRunSQL() []string { in := fmt.Sprintf("DELETE FROM %s WHERE %s IN (?, ?) AND (%s)", p.table(), sqlutil.RowTuple(p.KeyCols), p.Where) if len(p.KeyCols) == 1 { in = fmt.Sprintf("DELETE FROM %s WHERE %s IN (?, ?) AND (%s)", p.table(), sqlutil.Ident(p.KeyCols[0]), p.Where) } out := []string{in} if p.Bulk { if len(p.KeyCols) == 1 { out = append(out, fmt.Sprintf("DELETE FROM %s WHERE %s BETWEEN ? AND ? AND (%s) -- --bulk-delete", p.table(), sqlutil.Ident(p.KeyCols[0]), p.Where)) } else { out = append(out, fmt.Sprintf("DELETE FROM %s WHERE %s >= %s AND %s <= %s AND (%s) -- --bulk-delete", p.table(), sqlutil.RowTuple(p.KeyCols), sqlutil.ParamTuple(len(p.KeyCols)), sqlutil.RowTuple(p.KeyCols), sqlutil.ParamTuple(len(p.KeyCols)), p.Where)) } } return out }