1package functions23import (4 "fmt"5 "strings"67 "src.rest/cat/012/internal/formula"8 "src.rest/cat/012/internal/value"9)1011// Calls over paged sources. A linked source (the engine's paged sheets:12// a Parquet file or a SQLite table read in place) is too big to hold,13// so a function given one of its ranges isn't evaluated where the14// formula is: the Reader describes the call, its source ranges and the15// values of its other arguments, as a StreamCall and asks the Book for16// the answer, which is worked out in the background by EvalStream over17// a Book that streams the source, and kept until the file changes.18// Until it is known the cell shows Loading…, as a JEV question does.19//20// The functions in streaming read any size in one pass and hold only21// what they compute, and an order statistic of one range (MEDIAN,22// PERCENTILE; order.go) has the Book sort its numbers on disk; any other23// reads what its ranges hold, so it is given a source range only while24// that holds no more cells than the budget (max-cells), and past it25// answers #VALUE! saying why.2627// StreamCall is a call of a function some of whose arguments are ranges28// of paged sheets. It isn't comparable: Key identifies it by content.29type StreamCall struct {30 Fn string31 // Dec is set when the call is computed in decimal (decimal.go).32 Dec bool33 Args []StreamArg34}3536// StreamArg is an argument of a StreamCall: a range of a paged sheet37// (Sheet set, as the engine names the source), or a value.38type StreamArg struct {39 Sheet string40 R Rect41 V Value42}4344// StreamAnswer is what a StreamCall computes: a value, or an array of45// them, and for an error that needs one, why.46type StreamAnswer struct {47 V Value48 A *Array49 Why string50}5152// Key identifies the call by its content.53func (c StreamCall) Key() string {54 var b strings.Builder55 b.WriteString(c.Fn)56 if c.Dec {57 b.WriteString("#dec")58 }59 for _, a := range c.Args {60 b.WriteByte('\x1f')61 if a.Sheet != "" {62 fmt.Fprintf(&b, "r%s\x1e%d:%d:%d:%d", a.Sheet, a.R.From.Col, a.R.From.Row, a.R.To.Col, a.R.To.Row)63 continue64 }65 fmt.Fprintf(&b, "v%d\x1e%v\x1e%s", a.V.Kind, a.V.Num, a.V.Str)66 }67 return b.String()68}6970// streaming are the functions that read a source range of any size in71// one pass, holding only what they compute: aggregates, criteria72// functions, RANK and lookups.73var streaming = map[string]bool{74 "SUM": true, "AVERAGE": true, "COUNT": true, "COUNTA": true, "MIN": true, "MAX": true, "PRODUCT": true,75 "COUNTIF": true, "COUNTIFS": true, "SUMIF": true, "SUMIFS": true, "AVERAGEIF": true, "AVERAGEIFS": true,76 "COUNTBLANK": true, "SUMPRODUCT": true, "RANK": true, "RANK.EQ": true,77 "MATCH": true, "XLOOKUP": true, "VLOOKUP": true, "HLOOKUP": true, "INDEX": true, "ROWS": true, "COLUMNS": true,78}7980// Streams reports whether the function named name reads a source of81// any size (see streaming), or one range of it (orderFuncs).82func Streams(name string) bool {83 _, sorts := orderFuncs[name]84 return streaming[name] || sorts85}8687// StreamingFuncs lists the functions that read a source of any size, in88// alphabetical order, for the docs and messages.89func StreamingFuncs() []string {90 var out []string91 for _, f := range Funcs() {92 if Streams(f.Name) {93 out = append(out, f.Name)94 }95 }96 return out97}9899// streamed asks the Book for the call of f with args when one of its100// arguments is a range of a paged sheet, reporting whether it asked. A101// call whose other arguments aren't single values (a range of another102// sheet, an array) is evaluated as usual, reading the source through103// the Book's cells, which it holds only up to the budget.104func (rd *Reader) streamed(f *FuncDef, args []Node) (Value, bool) {105 if rd.stream || f.remote != nil || f.binds != formula.BindNone || !rd.pagedArg(args) {106 return Value{}, false107 }108 call := StreamCall{Fn: f.Name, Dec: funcs[f.Name] != f, Args: make([]StreamArg, len(args))}109 for i, a := range args {110 switch r := rd.refOf(a).(type) {111 case formula.Range:112 if r.Sheet == "" || !rd.book.Paged(r.Sheet) {113 return Value{}, false114 }115 call.Args[i] = StreamArg{Sheet: r.Sheet, R: r.Rect}116 continue117 }118 v := eval1(a, rd)119 if v.Kind == value.Array {120 return Value{}, false121 }122 call.Args[i] = StreamArg{V: v}123 }124 v, a := rd.book.Stream(call)125 if a != nil {126 return rd.arrayValue(a), true127 }128 return v, true129}130131// pagedArg reports whether an argument is a range of a paged sheet.132func (rd *Reader) pagedArg(args []Node) bool {133 for _, a := range args {134 if r, ok := rd.refOf(a).(formula.Range); ok && r.Sheet != "" && rd.book.Paged(r.Sheet) {135 return true136 }137 }138 return false139}140141// constArg is a StreamCall's value argument, as a node.142type constArg struct{ v Value }143144// EvalStream computes c over book, a Book that reads the paged sheets145// its ranges name by streaming them: in the background, where reading146// takes as long as the source is big. A function not in streaming is147// given the source's ranges only up to budget cells.148func EvalStream(c StreamCall, book Book, budget int) StreamAnswer {149 f, ok := LookupFunc(c.Fn)150 if !ok {151 return StreamAnswer{V: value.ErrName}152 }153 if twin, ok := decFuncs()[c.Fn]; ok && c.Dec {154 f = twin155 }156 if why := overBudget(c, book, budget); why != "" {157 return StreamAnswer{V: value.ErrValue, Why: why}158 }159 args := make([]Node, len(c.Args))160 const fixed = formula.AbsCol | formula.AbsRow161 for i, a := range c.Args {162 if a.Sheet != "" {163 args[i] = formula.Range{Rect: a.R, Sheet: a.Sheet, Abs: [2]formula.Abs{fixed, fixed}}164 } else {165 args[i] = constArg{a.V}166 }167 }168 depth := 0169 rd := NewReader(book, &depth, false)170 rd.stream = true171 v := EvalCell(formula.Call{Fn: f, Args: args}, rd, Addr{})172 return StreamAnswer{V: v, A: rd.Spilled()}173}174175// overBudget says why c can't be computed, when it names a function176// that doesn't stream over more cells of its sources than budget.177func overBudget(c StreamCall, book Book, budget int) string {178 if streaming[c.Fn] || sortsSource(c) {179 return ""180 }181 cells, most := 0, 0182 biggest := ""183 for _, a := range c.Args {184 if a.Sheet == "" {185 continue186 }187 b, any, _ := book.Bounds(a.Sheet, a.R)188 if !any {189 continue190 }191 n := (b.To.Row - b.From.Row + 1) * (b.To.Col - b.From.Col + 1)192 if cells += n; n > most {193 most, biggest = n, a.Sheet194 }195 }196 if cells <= budget {197 return ""198 }199 return fmt.Sprintf("Raise max-cells to %s for %s over %s. It is %s, and %s holds what it reads; SUMIFS, MEDIAN, XLOOKUP and the like stream a source of any size.",200 grouped(cells), c.Fn, biggest, grouped(budget), c.Fn)201}202203// grouped is n with thousands separators: 12,000,000.204func grouped(n int) string {205 s := fmt.Sprint(n)206 for i := len(s) - 3; i > 0 && s[i-1] != '-'; i -= 3 {207 s = s[:i] + "," + s[i:]208 }209 return s210}211212// Const is a node that evaluates to v: what the engine binds a name to213// while what it names can't be read yet (a source being opened shows214// Loading…).215func Const(v Value) Node { return constArg{v} }