1package fileio23import (4 "context"5 "fmt"6 "strings"78 "src.rest/cat/012/internal/sheet"9)1011// SQLite views push their sort and filter down to SQL: building one is12// one INSERT ... SELECT rowid ... WHERE ... ORDER BY into a table of the13// work database (sourcesqlwork.go), and a page reads its rowids there,14// by position, then the rows.15//16// The SQL follows a sheet's sort and filter, with these differences:17// SQLite folds case in ASCII only; text holding a date (2026-09-26),18// which the source shows as that date, is compared and sorted as text;19// and the text conditions (contains, starts with ...) test a number's20// text as SQLite writes it (1e+20), where a sheet tests it as shown.2122// sqlStmt is a statement and its arguments.23type sqlStmt struct {24 q string25 args []any26}2728// View orders the rows as o says.29func (s *sqliteSource) View(ctx context.Context, o SourceOrder) (SourceView, error) {30 b, err := s.byNumber(ctx)31 if err != nil {32 return nil, err33 }34 if o.IsZero() {35 return sqlPlainView{s: s, b: b}, nil36 }37 where, args, order, err := viewSQL(o, b.cols)38 if err != nil {39 return nil, err40 }41 s.mu.Lock()42 w, err := s.workDB()43 s.mu.Unlock()44 if err != nil {45 return nil, err46 }47 name := fmt.Sprintf("_012_v%d", w.n.Add(1))48 from := b.table49 if !b.work {50 from = "src." + b.table51 }52 n, err := w.build(ctx, s.spec.Path,53 sqlStmt{q: "CREATE TABLE " + name + " (r INTEGER)"},54 sqlStmt{q: "INSERT INTO " + name + " (r) SELECT rowid FROM " + from + where + " ORDER BY " + order, args: args})55 if err != nil {56 w.db.ExecContext(context.WithoutCancel(ctx), "DROP TABLE IF EXISTS "+name)57 return nil, err58 }59 return &sqlView{s: s, b: b, w: w, name: name, rows: n}, nil60}6162// viewSQL is a view's WHERE clause (with its leading space), its63// arguments and its ORDER BY, over the columns cols.64func viewSQL(o SourceOrder, cols []string) (string, []any, string, error) {65 var conds []string66 var args []any67 for _, f := range o.Filter {68 if f.Col < 0 || f.Col >= len(cols) {69 return "", nil, "", fmt.Errorf("the source has no column %d", f.Col+1)70 }71 c, a := condSQL(cols[f.Col], f.Cond)72 conds, args = append(conds, c), append(args, a...)73 }74 var keys []string75 for _, k := range o.Sort {76 if k.Col < 0 || k.Col >= len(cols) {77 return "", nil, "", fmt.Errorf("the source has no column %d", k.Col+1)78 }79 keys = append(keys, orderSQL(cols[k.Col], k.Desc))80 }81 where := ""82 if len(conds) > 0 {83 where = " WHERE " + strings.Join(conds, " AND ")84 }85 return where, args, strings.Join(append(keys, "rowid"), ", "), nil86}8788// orderSQL orders column x as a sheet sorts: numbers, text ignoring89// case, then the rest (booleans are numbers in SQLite, blobs last), and90// blanks last in either direction.91func orderSQL(x string, desc bool) string {92 dir := ""93 if desc {94 dir = " DESC"95 }96 v := "nullif(" + x + ", '')" // a blank, NULL or '', is NULL97 return "(" + v + " IS NULL), " +98 "CASE typeof(" + v + ") WHEN 'null' THEN 0 WHEN 'integer' THEN 0 WHEN 'real' THEN 0 WHEN 'text' THEN 1 ELSE 2 END" + dir + ", " +99 v + " COLLATE NOCASE" + dir100}101102// condSQL is a filter's condition on column x as SQL, as103// sheet.Condition tests a cell: text conditions on the text a value104// shows, blank for NULL; comparisons with a number (or a date, $1,200,105// 12%) only of numbers, with text only of text ignoring case; neither106// matching a blank.107func condSQL(x string, c sheet.Condition) (string, []any) {108 shown := "lower(coalesce(CAST(" + x + " AS TEXT), ''))"109 arg := strings.ToLower(c.Arg)110 switch c.Op {111 case sheet.CondEmpty:112 return "(" + x + " IS NULL OR " + x + " = '')", nil113 case sheet.CondNotEmpty:114 return "(" + x + " IS NOT NULL AND " + x + " <> '')", nil115 case sheet.CondContains:116 return "instr(" + shown + ", ?) > 0", []any{arg}117 case sheet.CondNotContains:118 return "instr(" + shown + ", ?) = 0", []any{arg}119 case sheet.CondStartsWith:120 return "substr(" + shown + ", 1, length(?)) = ?", []any{arg, arg}121 case sheet.CondEndsWith:122 return "(length(?) = 0 OR substr(" + shown + ", -length(?)) = ?)", []any{arg, arg, arg}123 case sheet.CondExactly:124 return shown + " = ?", []any{arg}125 case sheet.CondGreater, sheet.CondGreaterEq, sheet.CondLess, sheet.CondLessEq, sheet.CondEqual:126 return compareSQL(x, c)127 case sheet.CondNotEqual:128 c.Op = sheet.CondEqual129 q, args := compareSQL(x, c)130 return "NOT " + q, args131 }132 return "1", nil133}134135var compareOps = map[sheet.CondOp]string{136 sheet.CondGreater: ">", sheet.CondGreaterEq: ">=", sheet.CondLess: "<", sheet.CondLessEq: "<=", sheet.CondEqual: "=",137}138139// compareSQL compares column x with a condition's value as the sheet's140// filter does: a number with numbers, text with text.141func compareSQL(x string, c sheet.Condition) (string, []any) {142 op := compareOps[c.Op]143 if n, _, ok := sheet.ParseValue(strings.TrimSpace(c.Arg)); ok {144 return "(typeof(" + x + ") IN ('integer', 'real') AND " + x + " " + op + " ?)", []any{n}145 }146 return "(typeof(" + x + ") = 'text' AND " + x + " <> '' AND upper(" + x + ") " + op + " upper(?))", []any{c.Arg}147}148149// sqlView is a sorted or filtered view: its rows' rowids in a table of150// the work database.151type sqlView struct {152 s *sqliteSource153 b *sqlBase154 w *sqlWork155 name string156 rows int64157}158159func (v *sqlView) Rows() int64 { return v.rows }160161func (v *sqlView) Page(ctx context.Context, from int64, n int) ([]int64, [][]sheet.LiveCell, error) {162 if from < 0 || from >= v.rows || n <= 0 {163 return nil, nil, nil164 }165 var nums []int64166 err := queryRows(ctx, v.w.db, "SELECT r FROM "+v.name+" WHERE rowid BETWEEN ? AND ? ORDER BY rowid",167 []any{from + 1, from + int64(n)}, func(x []any) bool {168 r, _ := x[0].(int64)169 nums = append(nums, r-1)170 return true171 })172 if err != nil {173 return nil, nil, err174 }175 rows, err := v.b.fetch(ctx, nums, allCols(nil, len(v.b.cols)))176 if err != nil {177 return nil, nil, err178 }179 return nums, rows, nil180}181182func (v *sqlView) Close() error {183 v.s.mu.Lock()184 defer v.s.mu.Unlock()185 if v.s.work != v.w {186 return nil // closed with the source187 }188 _, err := v.w.db.Exec("DROP TABLE IF EXISTS " + v.name)189 return sqliteErr(err)190}191192// sqlPlainView is the rows in the source's own order.193type sqlPlainView struct {194 s *sqliteSource195 b *sqlBase196}197198func (v sqlPlainView) Rows() int64 { return v.s.rows }199200func (v sqlPlainView) Page(ctx context.Context, from int64, n int) ([]int64, [][]sheet.LiveCell, error) {201 return v.b.page(ctx, from, n, v.s.rows)202}203204func (sqlPlainView) Close() error { return nil }