1// Package stress builds synthetic worst-case sheets for the stress2// benchmarks (see docs/contributing/limits.md and `make stress`). Every generator is3// deterministic, so runs compare, and builds through the public API the4// way importers do: Load cell by cell, then one RecalcAll.5package stress67import (8 "fmt"9 "math/rand/v2"10 "strings"1112 "src.rest/cat/012/internal/sheet"13)1415// Shape is one synthetic sheet: a name for benchmark output, and a16// builder. Rows and Cols describe the populated area.17type Shape struct {18 Name string19 Build func() *sheet.Sheet20 // Edit is a cell whose change touches the shape's interesting21 // topology, for single-edit latency, and the entry to type there.22 Edit sheet.Addr23 Input string24}2526// Rows and Cols are the size of the shapes: the grid of Lotus 1-2-3 that27// 012 had before it grew to Excel's, so runs before and after compare.28const (29 Rows = 819230 Cols = 25631)3233// MaxRows x MaxCols is sheet.DefaultMaxCells of numbers, the most an34// import keeps by default: a tall table, as imports make.35const (36 MaxRows = 1_000_00037 MaxCols = 1038)3940func at(col, row int) sheet.Addr { return sheet.Addr{Col: col, Row: row} }4142func load(s *sheet.Sheet, a sheet.Addr, input string) {43 if err := s.Load(a, input, sheet.Format{}, sheet.Style{}); err != nil {44 panic(fmt.Sprintf("%s %q: %v", a, input, err))45 }46}4748// Dense fills rows x cols with numbers: the storage and render bound.49func Dense(rows, cols int) *sheet.Sheet {50 s := sheet.New()51 r := rand.New(rand.NewPCG(1, 2))52 for row := range rows {53 for col := range cols {54 load(s, at(col, row), fmt.Sprintf("%.2f", r.Float64()*1000))55 }56 }57 s.RecalcAll()58 return s59}6061// Chain is A1=1 and A(n)=A(n-1)+1 down n rows: the deepest dependency62// path, evaluated recursively.63func Chain(n int) *sheet.Sheet {64 s := sheet.New()65 load(s, at(0, 0), "1")66 for row := 1; row < n; row++ {67 load(s, at(0, row), fmt.Sprintf("=A%d+1", row))68 }69 s.RecalcAll()70 return s71}7273// FanIn is a column of n numbers and k formulas that each SUM all of74// it: k range users over the same big range.75func FanIn(n, k int) *sheet.Sheet {76 s := sheet.New()77 for row := range n {78 load(s, at(0, row), fmt.Sprint(row%100))79 }80 for i := range k {81 load(s, at(1+i/n, i%n), fmt.Sprintf("=SUM(A1:A%d)", n))82 }83 s.RecalcAll()84 return s85}8687// FanInColumns is FanIn with whole-column ranges, =SUM(A:A): the same88// data, read through a range of a million rows.89func FanInColumns(n, k int) *sheet.Sheet {90 s := sheet.New()91 for row := range n {92 load(s, at(0, row), fmt.Sprint(row%100))93 }94 for i := range k {95 load(s, at(1+i/n, i%n), "=SUM(A:A)")96 }97 s.RecalcAll()98 return s99}100101// Sparse is a tall, sparse sheet: n numbers every stride rows down102// column A, a label beside each, and k of each of three formulas over103// whole columns: =SUM(A:A), running totals to every thousandth row, and104// =VLOOKUP over A:B. Its cost should follow its n cells, not the million105// rows they span.106func Sparse(n, stride, k int) *sheet.Sheet {107 s := sheet.New()108 for i := range n {109 load(s, at(0, i*stride), fmt.Sprint(i%100))110 load(s, at(1, i*stride), fmt.Sprintf("item %d", i))111 }112 for i := range k {113 load(s, at(2, i), "=SUM(A:A)")114 load(s, at(3, i), fmt.Sprintf("=SUM($A$1:A%d)", (i+1)*1000))115 load(s, at(4, i), fmt.Sprintf("=VLOOKUP(%d, A:B, 2, FALSE)", i%100))116 }117 s.RecalcAll()118 return s119}120121// Criteria is a table of n rows (a number, a category, an amount) and k122// conditional aggregates over whole columns of it: SUMIF, COUNTIFS with123// two conditions and AVERAGEIF in turn, each testing every row.124func Criteria(n, k int) *sheet.Sheet {125 s := sheet.New()126 for row := range n {127 load(s, at(0, row), fmt.Sprint(row%100))128 load(s, at(1, row), fmt.Sprintf("c%d", row%10))129 load(s, at(2, row), fmt.Sprint(row%7))130 }131 for i := range k {132 var f string133 switch i % 3 {134 case 0:135 f = fmt.Sprintf(`=SUMIF(B:B, "c%d", C:C)`, i%10)136 case 1:137 f = fmt.Sprintf(`=COUNTIFS(A:A, ">%d", B:B, "c%d")`, i%100, i%10)138 default:139 f = fmt.Sprintf(`=AVERAGEIF(A:A, "<%d", C:C)`, i%100)140 }141 load(s, at(4, i), f)142 }143 s.RecalcAll()144 return s145}146147// Lookup is n keys with a value beside each and k exact lookups into148// them: VLOOKUP, MATCH and XLOOKUP in turn, each key found somewhere149// along the column.150func Lookup(n, k int) *sheet.Sheet {151 s := sheet.New()152 for row := range n {153 load(s, at(0, row), fmt.Sprint(row))154 load(s, at(1, row), fmt.Sprintf("v%d", row))155 }156 for i := range k {157 key := (i * 37) % n158 var f string159 switch i % 3 {160 case 0:161 f = fmt.Sprintf("=VLOOKUP(%d, A:B, 2, FALSE)", key)162 case 1:163 f = fmt.Sprintf("=MATCH(%d, A:A, 0)", key)164 default:165 f = fmt.Sprintf("=XLOOKUP(%d, A:A, B:B)", key)166 }167 load(s, at(3, i), f)168 }169 s.RecalcAll()170 return s171}172173// FanOut is one cell, A1, read directly by k formulas.174func FanOut(k int) *sheet.Sheet {175 s := sheet.New()176 load(s, at(0, 0), "1")177 for i := range k {178 load(s, at(1+i/Rows, i%Rows), fmt.Sprintf("=$A$1*%d", i))179 }180 s.RecalcAll()181 return s182}183184// RunningTotals is n numbers with a running SUM beside each: n185// overlapping ranges of growing size, O(n^2) cells read in all.186func RunningTotals(n int) *sheet.Sheet {187 s := sheet.New()188 for row := range n {189 load(s, at(0, row), fmt.Sprint(row%10))190 load(s, at(1, row), fmt.Sprintf("=SUM($A$1:A%d)", row+1))191 }192 s.RecalcAll()193 return s194}195196// Arrays is a column of n numbers beside a column of eight categories,197// and k formulas in row 1 spilling from them, alternating a FILTER of the198// numbers by one category (n/8 rows each) and the UNIQUE categories: k199// arrays over n rows, every number's change recomputing half of them.200func Arrays(n, k int) *sheet.Sheet {201 s := sheet.New()202 cats := []string{"alpha", "beta", "gamma", "delta", "epsilon", "zeta", "eta", "theta"}203 for row := range n {204 load(s, at(0, row), fmt.Sprint(row%100))205 load(s, at(1, row), cats[row%len(cats)])206 }207 for i := range k {208 f := fmt.Sprintf(`=FILTER(A1:A%d, B1:B%d="%s")`, n, n, cats[i/2%len(cats)])209 if i%2 == 1 {210 f = fmt.Sprintf("=UNIQUE(B1:B%d)", n)211 }212 load(s, at(2+i, 0), f)213 }214 s.RecalcAll()215 return s216}217218// Volatile is k cells alternating TODAY() and RAND(), recalculated on219// every change anywhere.220func Volatile(k int) *sheet.Sheet {221 s := sheet.New()222 for i := range k {223 f := "=RAND()"224 if i%2 == 1 {225 f = "=TODAY()"226 }227 load(s, at(i/Rows, i%Rows), f)228 }229 s.RecalcAll()230 return s231}232233// Nested is a formula nested depth levels deep, e.g. =((((1+1)+1)+1)+1),234// in A1: the parser's and evaluator's recursion.235func Nested(depth int) string {236 return "=" + strings.Repeat("(", depth) + "1" + strings.Repeat("+1)", depth)237}238239// NestedIF is =IF(A1>0,IF(A1>1,...,0),0) depth levels deep.240func NestedIF(depth int) string {241 var b strings.Builder242 b.WriteString("=")243 for i := range depth {244 fmt.Fprintf(&b, "IF(A1>%d,", i)245 }246 b.WriteString("1")247 b.WriteString(strings.Repeat(",0)", depth))248 return b.String()249}250251// LongText puts a length-n line of text, mixing ASCII, accents, CJK and252// emoji, in column A of rows rows and leaves everything else blank, so253// every row overflows across the whole screen.254func LongText(rows, n int) *sheet.Sheet {255 s := sheet.New()256 const alphabet = "The quick brown fox jumps over the lazy dog. Ça déjà vu, naïve café. 東京都の天気は晴れ。 🙂🚀 "257 src := []rune(alphabet)258 for row := range rows {259 var b strings.Builder260 for i := range n {261 b.WriteRune(src[(i+row)%len(src)])262 }263 load(s, at(0, row), b.String())264 }265 s.RecalcAll()266 return s267}268269// Names defines k named ranges over a column of 8192 numbers and a270// formula using each: every changed cell is checked against every name.271func Names(k int) *sheet.Sheet {272 s := sheet.New()273 for row := range Rows {274 load(s, at(0, row), fmt.Sprint(row%100))275 }276 for i := range k {277 from := (i * 7) % (Rows - 100)278 name := fmt.Sprintf("Region_%d", i)279 if err := s.DefineName(name, sheet.NewRect(at(0, from), at(0, from+99))); err != nil {280 panic(err)281 }282 load(s, at(1+i/Rows, i%Rows), "=SUM("+name+")")283 }284 s.RecalcAll()285 return s286}287288// Table is a filterable, sortable table: a header row and rows rows of289// an id, a category, a number and a date-like text, in cols columns.290func Table(rows, cols int) *sheet.Sheet {291 s := sheet.New()292 r := rand.New(rand.NewPCG(3, 4))293 cats := []string{"alpha", "beta", "gamma", "delta", "epsilon", "zeta", "eta", "theta"}294 for col := range cols {295 load(s, at(col, 0), fmt.Sprintf("H%d", col))296 }297 for row := 1; row <= rows; row++ {298 for col := range cols {299 var v string300 switch col % 4 {301 case 0:302 v = fmt.Sprint(row)303 case 1:304 v = cats[r.IntN(len(cats))]305 case 2:306 v = fmt.Sprintf("%.2f", r.Float64()*1e4)307 default:308 v = fmt.Sprintf("item %d", r.IntN(1000))309 }310 load(s, at(col, row), v)311 }312 }313 s.RecalcAll()314 return s315}316317// Charts adds k small column charts over a data block to s.318func Charts(s *sheet.Sheet, k int) {319 for i := range k {320 s.AddChart(sheet.Chart{321 Type: sheet.ChartColumn, Data: sheet.NewRect(at(0, 0), at(2, 20)), Header: true, Labels: true,322 At: at(4+(i%8)*3, (i/8)*3), W: 24, H: 10,323 })324 }325 s.ClearHistory()326}327328// Shapes are the edit-latency topologies at their stress sizes.329func Shapes() []Shape {330 return []Shape{331 {"dense-8192x26", func() *sheet.Sheet { return Dense(Rows, 26) }, at(0, 0), "5"},332 {"chain-8192", func() *sheet.Sheet { return Chain(Rows) }, at(0, 0), "2"},333 {"fanin-1000xSUM8192", func() *sheet.Sheet { return FanIn(Rows, 1000) }, at(0, 4000), "7"},334 {"fanout-8192", func() *sheet.Sheet { return FanOut(Rows) }, at(0, 0), "2"},335 {"running-8192", func() *sheet.Sheet { return RunningTotals(Rows) }, at(0, 0), "3"},336 {"volatile-8192", func() *sheet.Sheet { return Volatile(Rows) }, at(200, 0), "1"},337 {"names-1000", func() *sheet.Sheet { return Names(1000) }, at(0, 50), "9"},338 {"fanin-1000xSUM(A:A)", func() *sheet.Sheet { return FanInColumns(Rows, 1000) }, at(0, 4000), "7"},339 {"sparse-1M", func() *sheet.Sheet { return Sparse(10000, 100, 1000) }, at(0, 500000), "5"},340 {"criteria-60xSUMIF8192", func() *sheet.Sheet { return Criteria(Rows, 60) }, at(0, 4000), "7"},341 {"lookup-300xVLOOKUP8192", func() *sheet.Sheet { return Lookup(Rows, 300) }, at(0, 4000), "4000"},342 {"arrays-1000xFILTER8192", func() *sheet.Sheet { return Arrays(Rows, 1000) }, at(0, 4000), "7"},343 {"dense-1Mx10", func() *sheet.Sheet { return Dense(MaxRows, MaxCols) }, at(0, 500000), "5"},344 }345}346347// Laidout is a report of rows x cols: every cell bordered, a last column348// of notes that wrap over three lines, and a title merged across the349// top: what borders, tall rows and merged cells cost a frame.350func Laidout(rows, cols int) *sheet.Sheet {351 s := sheet.New()352 r := rand.New(rand.NewPCG(5, 6))353 lines := sheet.Style{Borders: sheet.BordersOf(sheet.LineThin, sheet.LineThin, sheet.LineThin, sheet.LineThin)}354 notes := lines355 notes.Wrap = sheet.WrapOn356 put := func(a sheet.Addr, input string, st sheet.Style) {357 if err := s.Load(a, input, sheet.Format{}, st); err != nil {358 panic(err)359 }360 }361 put(at(0, 0), "Report", sheet.Style{Bold: true})362 s.LoadMerge(sheet.Rect{From: at(0, 0), To: at(cols-1, 0)})363 for row := 1; row <= rows; row++ {364 for col := range cols - 1 {365 put(at(col, row), fmt.Sprintf("%.2f", r.Float64()*1000), lines)366 }367 put(at(cols-1, row), "a note that wraps over three lines", notes)368 }369 s.RecalcAll()370 return s371}