1package paged23import (4 "context"5 "os"6 "path/filepath"7 "strings"8 "testing"9 "time"1011 "github.com/parquet-go/parquet-go"1213 "src.rest/cat/012/internal/fileio"14 "src.rest/cat/012/internal/sheet"15)1617// sale is a row of the Parquet files the tests link.18type sale struct {19 ID int64 `parquet:"id"`20 Cat *string `parquet:"cat,optional"`21 Amount *float64 `parquet:"amount,optional"`22 Day int32 `parquet:"day,date"`23 Tags []string `parquet:"tags,list"`24}2526var cats = []string{"North", "south", "East", "west", "", "north"}2728// writeSales writes n rows to name, amounts shifted by shift, in row29// groups of 64 rows.30func writeSales(t testing.TB, name string, n int, shift float64) {31 t.Helper()32 f, err := os.Create(name)33 if err != nil {34 t.Fatal(err)35 }36 w := parquet.NewGenericWriter[sale](f, parquet.MaxRowsPerRowGroup(64))37 day := int32(time.Date(2026, 1, 1, 0, 0, 0, 0, time.UTC).Unix() / 86400)38 rows := make([]sale, n)39 for i := range rows {40 r := &rows[i]41 r.ID, r.Day = int64(i), day+int32(i%40)42 if i%9 != 4 {43 c := cats[i%len(cats)]44 r.Cat = &c45 }46 if i%11 != 7 {47 a := float64((i*37)%101) - 20.5 + shift48 r.Amount = &a49 }50 if i%5 == 0 {51 r.Tags = []string{"a", "b"}52 }53 }54 if _, err := w.Write(rows); err != nil {55 t.Fatal(err)56 }57 if err := w.Close(); err != nil {58 t.Fatal(err)59 }60 if err := f.Close(); err != nil {61 t.Fatal(err)62 }63}6465// linked is a workbook holding the file at name imported on Sheet1, as66// the table ref, and linked as the source sales, settled.67func linked(t *testing.T, name string, budget int) (*sheet.Workbook, *Host) {68 t.Helper()69 res, err := fileio.Import(context.Background(), name, fileio.Options{})70 if err != nil {71 t.Fatal(err)72 }73 w := res.Sheet.Book()74 s := w.Sheet(0)75 if err := w.RenameSheet(s, "Data"); err != nil {76 t.Fatal(err)77 }78 b, _ := s.UsedRange()79 if err := s.CreateTable("ref", b); err != nil {80 t.Fatal(err)81 }82 if _, err := w.AddSource("sales", sheet.LinkSource{Path: name}, s); err != nil {83 t.Fatal(err)84 }85 h := NewHost(budget)86 h.Settle(context.Background(), w, Links(w, same, ""))87 return w, h88}8990// sameFormulas are formulas that read the source as {T}: each gives the91// same over the source as over the table ref holding the same rows.92var sameFormulas = []string{93 "=SUM({T}[amount])", "=AVERAGE({T}[amount])", "=COUNT({T}[amount])", "=COUNTA({T}[cat])",94 "=MIN({T}[amount])", "=MAX({T}[amount])", "=COUNTA({T}[tags])",95 `=COUNTIF({T}[cat],"north")`, `=COUNTIF({T}[amount],">10")`, `=COUNTIFS({T}[cat],"North",{T}[amount],"<0")`,96 `=SUMIF({T}[cat],"East",{T}[amount])`, `=SUMIFS({T}[amount],{T}[cat],"west",{T}[id],">100")`,97 `=AVERAGEIF({T}[cat],"south",{T}[amount])`, `=AVERAGEIFS({T}[amount],{T}[cat],"<>East")`,98 "=COUNTBLANK({T}[cat])", "=SUMPRODUCT({T}[amount],{T}[id])", `=COUNTIF({T}[day],">="&DATE(2026,1,20))`,99 "=MATCH(17,{T}[id],0)", "=MATCH(50.5,{T}[amount],0)", "=XLOOKUP(42,{T}[id],{T}[amount])",100 `=XLOOKUP("east",{T}[cat],{T}[id],"none",0,-1)`, "=XLOOKUP(10,{T}[amount],{T}[id],,1)",101 "=XLOOKUP(10,{T}[amount],{T}[id],,-1,-1)", `=XLOOKUP("nowhere",{T}[cat],{T}[id],"none")`,102 "=VLOOKUP(7,{T},3,FALSE)", "=INDEX({T}[amount],10)", "=ROWS({T})", "=COLUMNS({T}[#All])",103 "=MEDIAN({T}[amount])", "=SUM({T}[amount]*2)", "=ROUND(SUM({T}[amount]),2)",104}105106func TestFormulasOverASource(t *testing.T) {107 dir := t.TempDir()108 name := filepath.Join(dir, "sales.parquet")109 writeSales(t, name, 5000, 0)110 w, h := linked(t, name, 100000)111 s := w.Sheet(0)112 for i, f := range sameFormulas {113 at := sheet.Addr{Col: 10, Row: i}114 ref := sheet.Addr{Col: 11, Row: i}115 if err := s.Set(at, strings.ReplaceAll(f, "{T}", "sales")); err != nil {116 t.Fatalf("%s: %v", f, err)117 }118 if err := s.Set(ref, strings.ReplaceAll(f, "{T}", "ref")); err != nil {119 t.Fatalf("%s: %v", f, err)120 }121 }122 h.Settle(context.Background(), w, Links(w, same, ""))123 for i, f := range sameFormulas {124 got, want := s.Value(sheet.Addr{Col: 10, Row: i}), s.Value(sheet.Addr{Col: 11, Row: i})125 if testing.Verbose() {126 t.Logf("%s: %v", f, got)127 }128 if got.Kind == sheet.Error && !strings.Contains(f, "nowhere") {129 t.Errorf("%s: %v", f, got)130 }131 if got != want {132 t.Errorf("%s: %v over the source, %v over the sheet", f, got, want)133 }134 }135}136137func TestPendingUntilAnswered(t *testing.T) {138 dir := t.TempDir()139 name := filepath.Join(dir, "sales.parquet")140 writeSales(t, name, 100, 0)141 w, h := linked(t, name, 1000)142 s := w.Sheet(0)143 a := sheet.Addr{Col: 10}144 if err := s.Set(a, "=SUM(sales[amount])"); err != nil {145 t.Fatal(err)146 }147 if v := s.Value(a); !sheet.IsPending(v) {148 t.Fatalf("before the answer: %v, want Loading…", v)149 }150 jobs := h.Jobs()151 if len(jobs) != 1 {152 t.Fatalf("%d jobs queued, want 1", len(jobs))153 }154 jobs[0].Run(context.Background())155 Apply(w, h.Store(jobs[0]))156 if v := s.Value(a); v.Kind != sheet.Number {157 t.Fatalf("after the answer: %v", v)158 }159 // Asked again, the answer is kept: no job.160 if err := s.Set(sheet.Addr{Col: 11}, "=SUM(sales[amount])+1"); err != nil {161 t.Fatal(err)162 }163 if jobs := h.Jobs(); len(jobs) != 0 {164 t.Errorf("%d jobs for a kept answer", len(jobs))165 }166}167168func TestPastTheBudget(t *testing.T) {169 dir := t.TempDir()170 name := filepath.Join(dir, "sales.parquet")171 writeSales(t, name, 300, 0)172 w, h := linked(t, name, 100)173 s := w.Sheet(0)174 med, sum := sheet.Addr{Col: 10}, sheet.Addr{Col: 10, Row: 1}175 s.Set(med, "=STDEV(sales[amount])")176 s.Set(sum, "=SUM(sales[amount])")177 h.Settle(context.Background(), w, Links(w, same, ""))178 if v := s.Value(med); v != sheet.ErrValue {179 t.Errorf("STDEV past the budget: %v, want #VALUE!", v)180 }181 if why := s.ExplainError(med); !strings.Contains(why, "max-cells") {182 t.Errorf("STDEV past the budget explained as %q", why)183 }184 if v := s.Value(sum); v.Kind != sheet.Number {185 t.Errorf("SUM streams past the budget: %v", v)186 }187}188189func TestPivotOverASource(t *testing.T) {190 dir := t.TempDir()191 name := filepath.Join(dir, "sales.parquet")192 writeSales(t, name, 500, 0)193 w, h := linked(t, name, 1000)194 ref := w.Sheet(0)195 src, _ := w.LookupSource("sales")196 p := sheet.Pivot{197 Rows: []sheet.PivotGroup{{Col: 1}},198 Columns: []sheet.PivotGroup{{Col: 3}},199 Values: []sheet.PivotValue{{Col: 2, Summarize: sheet.SumBy}, {Col: 2, Summarize: sheet.CountUniqueBy}},200 RowTotals: true, ColumnTotals: true,201 }202 b, _ := ref.UsedRange()203 want, err := w.CreatePivot(ref, b, "want", p)204 if err != nil {205 t.Fatal(err)206 }207 got, err := w.CreatePivot(src.Sheet, sheet.Rect{To: sheet.Addr{Col: 4, Row: sheet.MaxRows - 1}}, "got", p)208 if err != nil {209 t.Fatal(err)210 }211 h.Settle(context.Background(), w, Links(w, same, ""))212 wb, _ := want.UsedRange()213 gb, _ := got.UsedRange()214 if wb != gb {215 t.Fatalf("pivot over the source covers %v, over the sheet %v", gb, wb)216 }217 for row := wb.From.Row; row <= wb.To.Row; row++ {218 for col := wb.From.Col; col <= wb.To.Col; col++ {219 a := sheet.Addr{Col: col, Row: row}220 if g, x := got.Value(a), want.Value(a); g != x && !(col == 0 && row == 0) {221 t.Errorf("%s: %v over the source, %v over the sheet", a, g, x)222 }223 }224 }225}226227func TestSourceChanges(t *testing.T) {228 dir := t.TempDir()229 name := filepath.Join(dir, "sales.parquet")230 writeSales(t, name, 100, 0)231 w, h := linked(t, name, 1000)232 s := w.Sheet(0)233 a := sheet.Addr{Col: 10}234 s.Set(a, "=SUM(sales[amount])")235 h.Settle(context.Background(), w, Links(w, same, ""))236 before := s.Value(a)237 now := time.Now()238 h.Now = func() time.Time { return now }239 writeSales(t, name, 100, 1)240 if h.Poll() {241 t.Fatal("read again before the file held still")242 }243 now = now.Add(time.Second)244 if !h.Poll() {245 t.Fatal("a changed file isn't read again")246 }247 h.Settle(context.Background(), w, Links(w, same, ""))248 after := s.Value(a)249 if after.Kind != sheet.Number || after.Num-before.Num < 80 {250 t.Errorf("SUM %v before the file changed, %v after", before, after)251 }252}253254func TestMissingSource(t *testing.T) {255 w := sheet.NewBook()256 s := w.Sheet(0)257 if _, err := w.AddSource("gone", sheet.LinkSource{Path: filepath.Join(t.TempDir(), "gone.parquet")}, s); err != nil {258 t.Fatal(err)259 }260 a := sheet.Addr{}261 s.Set(a, "=SUM(gone[x])")262 NewHost(1000).Settle(context.Background(), w, Links(w, same, ""))263 if v := s.Value(a); v != sheet.ErrRef {264 t.Errorf("a missing source: %v, want #REF!", v)265 }266 if why := s.ExplainError(a); !strings.Contains(why, "isn't there") {267 t.Errorf("explained as %q", why)268 }269}270271// same is a path as it is: the tests' files are named whole.272func same(p string) string { return p }