1package ui23import (4 "fmt"5 "path/filepath"6 "slices"7 "strconv"8 "strings"9 "unicode"1011 tea "charm.land/bubbletea/v2"1213 "src.rest/cat/012/internal/fileio"14 "src.rest/cat/012/internal/macro"15 "src.rest/cat/012/internal/sheet"16 "src.rest/cat/012/internal/ui/picker"17)1819// Commands on linked sources: Data > Linked file > Link a source makes20// one, and on its tab the commands that read a sheet's data act on the21// source instead: sorting and filtering order the rows the tab shows22// (pushed down to SQLite, streamed over Parquet), a pivot table or a23// frequency table summarizes the whole source, and Copy copies the24// active cell. Everything that would change cells says the tab is25// read-only.2627func init() {28 register(&command{id: "data.link_source", macro: macroNever, title: "Link a source",29 desc: "Read a Parquet file or a SQLite table or query in place, on a tab of its own, however big: formulas and pivot tables stream over it",30 run: (*Model).openSourcePicker})31 register(&command{id: "data.source_all", title: "Original order",32 desc: "Show the linked source's rows in its own order, unsorted and unfiltered",33 enabled: func(m *Model) bool { info, ok := m.sheet.Source(); return ok && !info.Source.Order.IsZero() },34 run: func(m *Model) tea.Cmd {35 info, _ := m.sheet.Source()36 return m.setSourceOrder(info, nil)37 }})38 register(&command{id: "data.source_reload", macro: macroNever, title: "Read the source again",39 desc: "Read the linked source's file again, forgetting what formulas and pivot tables found in it",40 enabled: func(m *Model) bool { return m.sheet.IsSource() },41 run: func(m *Model) tea.Cmd {42 info, _ := m.sheet.Source()43 m.sources().host.Reload(info.Name)44 m.note = "Reading " + filepath.Base(info.Source.Path) + " again"45 return nil46 }})47}4849// sourceTakes are the commands that act on a source's tab, the source50// standing for the tab's cells.51var sourceTakes = map[string]bool{52 "edit.copy": true, "data.sort_sheet_az": true, "data.sort_sheet_za": true, "data.sort_range_az": true,53 "data.sort_range_za": true, "data.filter": true, "data.filter_column": true, "data.filter_remove": true,54 "data.pivot": true, "data.frequency": true, "data.source_all": true, "data.source_reload": true,55 "data.link_source": true, "data.link": true, "goto": true,56}5758// sourceCommand runs id on a source's tab, reporting whether it did:59// the commands that read data act on the source, and the rest, which60// would change cells, say they can't.61func (m *Model) sourceCommand(c *command) (tea.Cmd, bool) {62 info, ok := m.sheet.Source()63 switch {64 case !ok || sourceOwn(c.id) || c.id == "data.link_source" || c.id == "data.link":65 return nil, false66 case !sourceTakes[c.id]:67 m.note = c.title + " works on a sheet's cells: this tab is a linked source, read-only"68 return nil, true69 case !info.Known:70 m.note = "The source isn't open yet"71 return nil, true72 case edits(c) && !m.mayEdit():73 return nil, true // another writes in the room74 case m.rec != nil && c.macro == macroRecord && c.id != "data.filter" && c.id != "data.filter_column":75 m.rec.flush(m)76 cmd := m.sourceRun(c, info)77 m.rec.add(m, macro.Call("run", c.id)) // replayed on the tab, as here78 return cmd, true79 }80 return m.sourceRun(c, info), true81}8283// sourceRun runs c on the tab of the source info, acting on the source.84func (m *Model) sourceRun(c *command, info sheet.SourceInfo) tea.Cmd {85 v := m.srcView()86 _, col := v.Active()87 switch c.id {88 case "edit.copy":89 _, text := m.sourceCell(v)90 m.note = "Copied " + text91 return tea.SetClipboard(text)92 case "data.sort_sheet_az", "data.sort_range_az", "data.sort_sheet_za", "data.sort_range_za":93 desc := c.id == "data.sort_sheet_za" || c.id == "data.sort_range_za"94 o := copyOrder(info.Source.Order)95 o.Sort = []sheet.SourceSort{{Col: col, Desc: desc}}96 return m.setSourceOrder(info, o)97 case "data.filter", "data.filter_column":98 m.openSourceFilter(info, col)99 case "data.filter_remove":100 o := copyOrder(info.Source.Order)101 o.Filter = nil102 return m.setSourceOrder(info, o)103 case "data.pivot", "data.frequency":104 return m.sourcePivot(info, col, c.id == "data.frequency")105 case "goto":106 m.openText("Go to a row, or a cell such as C5000000:", "", (*Model).sourceGoto)107 }108 return nil109}110111// sourceOwn reports whether a command runs on a source's tab as it runs112// anywhere: the session's (files, sheets, settings, macros, undo) and113// the source's own commands.114func sourceOwn(id string) bool {115 return notebookSafe(id) || strings.HasPrefix(id, "macro.") || id == "data.source_all" || id == "data.source_reload"116}117118// sourceEnabled says whether a command runs on a source's tab, when119// the tab decides it: those that would change cells don't, and those120// acting on the source do, whatever they say of a sheet.121func sourceEnabled(m *Model, id string) (on, decided bool) {122 switch {123 case sourceOwn(id):124 return false, false125 case !sourceTakes[id]:126 return false, true127 case id == "data.filter_remove":128 info, _ := m.sheet.Source()129 return info.Source.Order != nil && len(info.Source.Order.Filter) > 0, true130 }131 return true, true132}133134// copyOrder is a copy of o to change, never nil.135func copyOrder(o *sheet.SourceOrder) *sheet.SourceOrder {136 out := &sheet.SourceOrder{}137 if o != nil {138 out.Sort, out.Filter = slices.Clone(o.Sort), slices.Clone(o.Filter)139 }140 return out141}142143// setSourceOrder orders the source's tab as o says, as an undo step.144func (m *Model) setSourceOrder(info sheet.SourceInfo, o *sheet.SourceOrder) tea.Cmd {145 if err := m.book().SetSourceOrder(info.Name, o); err != nil {146 m.fail(err.Error())147 }148 return nil149}150151// openSourceFilter asks for a condition on column col of the source,152// replacing the column's own.153func (m *Model) openSourceFilter(info sheet.SourceInfo, col int) {154 var cond sheet.Condition155 o := info.Source.Order156 if o != nil {157 for _, f := range o.Filter {158 if f.Col == col {159 cond = f.Cond160 }161 }162 }163 title := "Filter " + sheet.ColName(col) + " " + m.sourceCol(info, col)164 p := m.openValuesPicker(title, m.srcView().ColX(col), nil, cond, func(cr sheet.Criteria) {165 o := copyOrder(info.Source.Order)166 o.Filter = slices.DeleteFunc(o.Filter, func(f sheet.SourceFilter) bool { return f.Col == col })167 if cr.Cond.Op != sheet.CondNone {168 o.Filter = append(o.Filter, sheet.SourceFilter{Col: col, Cond: cr.Cond})169 }170 m.setSourceOrder(info, o)171 })172 p.OnlyCondition()173}174175// sourcePivot makes a pivot table over the whole source on a new sheet,176// or with frequency a frequency table of column col.177func (m *Model) sourcePivot(info sheet.SourceInfo, col int, frequency bool) tea.Cmd {178 src := info.Sheet179 r := sheet.Rect{To: sheet.Addr{Col: len(info.Shape.Cols) - 1, Row: sheet.MaxRows - 1}}180 p, name := sheet.NewPivot(src, r), ""181 if frequency {182 p = sheet.FrequencyPivot(src, r, col)183 name = "Frequency of " + m.sourceCol(info, col)184 }185 start := m.sheet.StateID()186 s, err := m.book().CreatePivot(src, r, name, p)187 if err != nil {188 m.fail(err.Error())189 return nil190 }191 m.showSheet(s)192 if !frequency {193 m.openPivotEditor(start, src)194 }195 return nil196}197198// openSourcePicker asks for the Parquet file or SQLite database to link199// as a source: those here, or a path typed.200func (m *Model) openSourcePicker() tea.Cmd {201 var items []picker.Item202 names, _ := importable(m.root)203 for _, name := range names {204 k, _ := fileio.KindOf(name)205 if k != fileio.Parquet && k != fileio.SQLite {206 continue207 }208 items = append(items, picker.Item{Title: name, Name: len(name), Detail: k.Label(), Desc: "Link " + name + " as a source",209 Pick: func() tea.Cmd {210 m.closeOverlay()211 return m.linkSource(name)212 }})213 }214 p := m.newPicker("Link a source", "Type to filter, or a path", 72, items)215 p.Action = "link"216 p.Enter = func(query string) (tea.Cmd, bool) {217 if query == "" || len(p.Shown()) > 0 && !filepath.IsAbs(query) {218 return nil, false219 }220 m.closeOverlay()221 return m.linkSource(query), true222 }223 m.openOverlay(p)224 return nil225}226227// linkSource links the file typed as name as a source, asking which228// table of a database with several.229func (m *Model) linkSource(name string) tea.Cmd {230 spec := fileio.SourceSpec{Path: name}231 if k, err := fileio.SourceKind(spec); err != nil {232 m.fail(fmt.Sprintf("Can't link %s: %v", filepath.Base(name), err))233 return nil234 } else if k == fileio.Parquet {235 m.addSource(sheet.LinkSource{Path: linkPath(name, m.filename)})236 return nil237 }238 src := sheet.LinkSource{Path: linkPath(name, m.filename)}239 query := picker.Item{Title: "A query…", Name: len("A query"), Desc: "Link what a SELECT reads from " + filepath.Base(name),240 Pick: func() tea.Cmd {241 m.closeOverlay()242 m.openText("Link the rows of the query:", "SELECT * FROM ", func(m *Model, text string) tea.Cmd {243 src.Query = text244 m.addSource(src)245 return nil246 })247 return nil248 }}249 m.chooseTable(name, src, []picker.Item{query}, m.addSource)250 return nil251}252253// addSource adds a tab linking src after the sheet shown, and shows it.254func (m *Model) addSource(src sheet.LinkSource) {255 before := len(m.book().Sources())256 s, err := m.book().AddSource("", src, m.sheet)257 if err != nil {258 m.fail(err.Error())259 return260 }261 m.afterSheetsChange(s, m.book().Index(s))262 m.linkedHere(before)263 m.note = "Linked " + filepath.Base(src.Path) + " as " + s.Name() + ": formulas read it as " + s.Name() + "[column]"264}265266// sourceGoto moves the active cell on a source's tab to where text267// says: a row number, or a column's letters and a row number, as the268// tab numbers its rows (the source's first row is 2).269func (m *Model) sourceGoto(text string) tea.Cmd {270 v := m.srcView()271 if v == nil {272 return nil273 }274 text = strings.ToUpper(strings.ReplaceAll(strings.TrimSpace(text), ",", ""))275 letters := strings.TrimRightFunc(text, unicode.IsDigit)276 row, err := strconv.ParseInt(text[len(letters):], 10, 64)277 _, col := v.Active()278 if letters != "" {279 c, ok := sheet.ParseCol(letters)280 if !ok {281 err = strconv.ErrSyntax282 }283 col = c284 }285 if err != nil || row < 1 {286 m.fail("Type a row, such as 5000000, or a cell, such as C5000000")287 return nil288 }289 v.MoveTo(max(row-2, 0), col)290 return nil291}