package schema import ( "context" "database/sql" "fmt" "strconv" "strings" "time" "archer/internal/row" "archer/internal/sqlutil" ) // ShardKind is how a shard column's date is derived from its raw value. type ShardKind int const ( ShardNone ShardKind = iota ShardDate ShardDateTime ShardEpoch ) // ShardSpec is the resolved shard column and how to interpret it. type ShardSpec struct { Column string Kind ShardKind Unit int64 // epoch multiplier to nanoseconds: 1e9, 1e6, 1e3 Type string } // ResolveShardColumn validates --shard-column against the table and the unit // flag. It returns a descriptive error listing candidates when the column is // missing or the type is unsupported. func ResolveShardColumn(ctx context.Context, db *sql.DB, t *Table, column, unitFlag, where string, loc *time.Location) (*ShardSpec, error) { if column == "" { return nil, fmt.Errorf("--shard-column is required for --shard-by date\ncandidate columns:\n%s", describeTimeColumns(t)) } c, ok := t.Column(column) if !ok { return nil, fmt.Errorf("shard column %q does not exist; candidate columns:\n%s", column, describeTimeColumns(t)) } switch c.DataType { case "date": return &ShardSpec{Column: c.Name, Kind: ShardDate, Unit: 1, Type: c.DataType}, nil case "datetime", "timestamp": return &ShardSpec{Column: c.Name, Kind: ShardDateTime, Unit: 1, Type: c.DataType}, nil } if row.IsInteger(c.DataType) { unit, err := ParseEpochUnit(unitFlag) if err != nil { minV, maxV, ok, serr := SampleEpochRange(ctx, db, t, c.Name, where) if serr != nil { return nil, fmt.Errorf("%w (also failed to sample %s: %v)", err, c.Name, serr) } if !ok { return nil, fmt.Errorf("%w; column %s has no non-NULL values to sample", err, c.Name) } return nil, fmt.Errorf("%w\nsampled %s range: %d .. %d\n seconds: %s .. %s\n milliseconds: %s .. %s\n microseconds: %s .. %s", err, c.Name, minV, maxV, epochString(minV, 1, loc), epochString(maxV, 1, loc), epochString(minV, 1_000, loc), epochString(maxV, 1_000, loc), epochString(minV, 1_000_000, loc), epochString(maxV, 1_000_000, loc)) } return &ShardSpec{Column: c.Name, Kind: ShardEpoch, Unit: unit, Type: c.DataType}, nil } return nil, fmt.Errorf("column %q has unsupported type %s; v1 supports DATE, DATETIME, TIMESTAMP and integer epoch columns with --shard-column-type\ncandidate columns:\n%s", c.Name, c.DataType, describeTimeColumns(t)) } func describeTimeColumns(t *Table) string { var b strings.Builder found := false for _, c := range t.TimeColumns() { found = true fmt.Fprintf(&b, " - %s %s%s\n", c.Name, c.ColumnType, nullableSuffix(c)) } for _, c := range t.Columns { if row.IsInteger(c.DataType) { found = true fmt.Fprintf(&b, " - %s %s (integer; requires --shard-column-type)%s\n", c.Name, c.ColumnType, nullableSuffix(c)) } } if !found { return " (none)" } return strings.TrimRight(b.String(), "\n") } func nullableSuffix(c Column) string { if c.Nullable { return " NULL" } return " NOT NULL" } // ParseEpochUnit maps --shard-column-type to raw units per second: 1 for // seconds, 1000 for milliseconds, 1000000 for microseconds. func ParseEpochUnit(flag string) (int64, error) { switch strings.ToLower(strings.TrimSpace(flag)) { case "epoch-seconds", "seconds", "second", "s", "1": return 1, nil case "epoch-milliseconds", "epoch-millis", "milliseconds", "millisecond", "ms", "1000": return 1_000, nil case "epoch-microseconds", "epoch-micros", "microseconds", "microsecond", "us", "1000000": return 1_000_000, nil case "": return 0, fmt.Errorf("integer shard column requires --shard-column-type (epoch-seconds, epoch-milliseconds or epoch-microseconds)") default: return 0, fmt.Errorf("unsupported --shard-column-type %q; use epoch-seconds, epoch-milliseconds or epoch-microseconds", flag) } } // SampleEpochRange returns MIN/MAX of an integer column under the base where. func SampleEpochRange(ctx context.Context, db *sql.DB, t *Table, col, where string) (int64, int64, bool, error) { q := fmt.Sprintf("SELECT MIN(%s), MAX(%s) FROM %s", sqlutil.Ident(col), sqlutil.Ident(col), sqlutil.Qualified(t.DB, t.Name)) if strings.TrimSpace(where) != "" { q += " WHERE (" + where + ")" } var minRaw, maxRaw sql.NullString if err := db.QueryRowContext(ctx, q).Scan(&minRaw, &maxRaw); err != nil { return 0, 0, false, err } if !minRaw.Valid || !maxRaw.Valid { return 0, 0, false, nil } minV, err := strconv.ParseInt(strings.TrimSpace(minRaw.String), 10, 64) if err != nil { return 0, 0, false, fmt.Errorf("parse min %q: %w", minRaw.String, err) } maxV, err := strconv.ParseInt(strings.TrimSpace(maxRaw.String), 10, 64) if err != nil { return 0, 0, false, fmt.Errorf("parse max %q: %w", maxRaw.String, err) } return minV, maxV, true, nil } func epochString(v, unit int64, loc *time.Location) string { return EpochTime(v, unit, loc).Format("2006-01-02 15:04:05") } // EpochTime converts an epoch value with the given nanoseconds-per-unit to a // time in loc. func EpochTime(v, unit int64, loc *time.Location) time.Time { sec := v / unit frac := v % unit if frac < 0 { sec-- frac += unit } nsec := frac * (1_000_000_000 / unit) return time.Unix(sec, nsec).In(loc) } // TimeToEpoch is the inverse of EpochTime. func TimeToEpoch(t time.Time, unit int64) int64 { return t.Unix()*unit + int64(t.Nanosecond())/(1_000_000_000/unit) } const nullBucket = "_null" const zeroBucket = "0000-00-00" // BucketOf maps a raw shard value to its date bucket. key is used only in // error messages. func BucketOf(v row.Value, spec *ShardSpec, loc *time.Location, zeroDate string, key row.Key) (string, error) { if v.IsNull() { return nullBucket, nil } switch spec.Kind { case ShardEpoch: n, err := strconv.ParseInt(strings.TrimSpace(string(v.Raw)), 10, 64) if err != nil { return "", fmt.Errorf("row %s: cannot parse epoch value %q in column %s: %w", key, string(v.Raw), spec.Column, err) } return EpochTime(n, spec.Unit, loc).Format("2006-01-02"), nil case ShardDate, ShardDateTime: s := string(v.Raw) if len(s) < 10 { return "", fmt.Errorf("row %s: cannot parse date value %q in column %s", key, s, spec.Column) } day := s[:10] if strings.HasPrefix(day, "0000-") { if zeroDate == "bucket" { return zeroBucket, nil } return "", fmt.Errorf("row %s has zero date %q in column %s; pass --zero-date bucket to route it to date=0000-00-00", key, s, spec.Column) } if _, err := time.Parse("2006-01-02", day); err != nil { return "", fmt.Errorf("row %s: invalid date %q in column %s", key, s, spec.Column) } return day, nil } return "", fmt.Errorf("internal: shard kind not resolved") } // BucketLess orders buckets in scan order: NULL first, then zero dates, then // real dates. func BucketLess(a, b string) bool { ca, ka := bucketClass(a) cb, kb := bucketClass(b) if ca != cb { return ca < cb } return ka < kb } func bucketClass(b string) (int, string) { switch b { case "": return -1, "" case nullBucket: return 0, "" case zeroBucket: return 1, "" default: return 2, b } }