1package fileio23import (4 "context"5 "database/sql"6 "errors"7 "fmt"8 "os"9 "strings"10 "sync/atomic"1112 "src.rest/cat/012/internal/sheet"13)1415// A SQLite source's work database: a file of its own in the temporary16// directory, never the source's, holding what reading rows by number17// needs: a copy of a source whose rows can't be found by number18// otherwise (_012_rows), and each view's rows in its order as a table of19// rowids (_012_v1, ...), paged by that table's own rowid, so a page20// deep in a view costs what a page at its top does. Tables are built21// in SQLite, the source attached read-only as src; SQLite sorts in22// temporary files, so memory stays bounded. The file goes when the23// source closes.2425type sqlWork struct {26 path string27 db *sql.DB28 n atomic.Int64 // views made, naming their tables29}3031func newSQLWork(dir string) (*sqlWork, error) {32 f, err := os.CreateTemp(dir, "012-source-*.db")33 if err != nil {34 return nil, err35 }36 f.Close()37 uri, err := sqliteURI(f.Name(), "_pragma=journal_mode(OFF)", "_pragma=synchronous(OFF)",38 "_pragma=busy_timeout(60000)", "_pragma=temp_store(1)")39 if err != nil {40 os.Remove(f.Name())41 return nil, err42 }43 db, err := sql.Open("sqlite", uri)44 if err != nil {45 os.Remove(f.Name())46 return nil, err47 }48 return &sqlWork{path: f.Name(), db: db}, nil49}5051func (w *sqlWork) close() error {52 err := w.db.Close()53 for _, suffix := range []string{"", "-journal", "-wal", "-shm"} {54 if rerr := os.Remove(w.path + suffix); rerr != nil && !errors.Is(rerr, os.ErrNotExist) {55 err = errors.Join(err, rerr)56 }57 }58 return err59}6061// build runs statements on a connection of the work database with the62// source attached as src.63func (w *sqlWork) build(ctx context.Context, source string, stmts ...sqlStmt) (int64, error) {64 conn, err := w.db.Conn(ctx)65 if err != nil {66 return 0, err67 }68 defer conn.Close()69 uri, err := sqliteURI(source, "mode=ro")70 if err != nil {71 return 0, err72 }73 if _, err := conn.ExecContext(ctx, "ATTACH DATABASE ? AS src", uri); err != nil {74 return 0, sqliteErr(err)75 }76 defer conn.ExecContext(context.WithoutCancel(ctx), "DETACH DATABASE src")77 var n int6478 for _, q := range stmts {79 res, err := conn.ExecContext(ctx, q.q, q.args...)80 if err != nil {81 return 0, sqliteErr(err)82 }83 n, _ = res.RowsAffected()84 }85 return n, nil86}8788// workDB is the source's work database, made when first needed. It is89// called with s.mu held.90func (s *sqliteSource) workDB() (*sqlWork, error) {91 if s.work == nil {92 w, err := newSQLWork(s.spec.TempDir)93 if err != nil {94 return nil, err95 }96 s.work = w97 }98 return s.work, nil99}100101// sqlBase is a table whose rowids are its rows' numbers plus one: a102// dense source table, or the copy in the work database.103type sqlBase struct {104 db *sql.DB105 table string // quoted106 cols []string // its columns, quoted107 work bool // in the work database108}109110// byNumber is where rows are found by number, copying the source into111// the work database the first time when it must.112func (s *sqliteSource) byNumber(ctx context.Context) (*sqlBase, error) {113 s.mu.Lock()114 defer s.mu.Unlock()115 if s.base != nil {116 return s.base, nil117 }118 if s.table != "" && s.dense {119 s.base = &sqlBase{db: s.db, table: s.table, cols: quoteAll(s.names)}120 return s.base, nil121 }122 w, err := s.workDB()123 if err != nil {124 return nil, err125 }126 if _, err := w.build(ctx, s.spec.Path, sqlStmt{q: "CREATE TABLE _012_rows AS " + s.query}); err != nil {127 w.db.ExecContext(context.WithoutCancel(ctx), "DROP TABLE IF EXISTS _012_rows")128 return nil, err129 }130 var names []string131 err = queryRows(ctx, w.db, "SELECT name FROM pragma_table_info('_012_rows') ORDER BY cid", nil, func(v []any) bool {132 names = append(names, fmt.Sprint(v[0]))133 return true134 })135 if err != nil {136 return nil, err137 }138 s.base = &sqlBase{db: w.db, table: "_012_rows", cols: quoteAll(names), work: true}139 return s.base, nil140}141142func quoteAll(names []string) []string {143 out := make([]string, len(names))144 for i, n := range names {145 out[i] = quoteIdent(n)146 }147 return out148}149150// list is the select list of cols after the rowid.151func (b *sqlBase) list(cols []int) string {152 parts := make([]string, 0, len(cols)+1)153 parts = append(parts, "rowid")154 for _, c := range cols {155 parts = append(parts, b.cols[c])156 }157 return strings.Join(parts, ", ")158}159160// fetchChunk is how many rowids one query asks for.161const fetchChunk = 500162163// fetch reads the rows numbered rows, in the order given.164func (b *sqlBase) fetch(ctx context.Context, rows []int64, cols []int) ([][]sheet.LiveCell, error) {165 got := make(map[int64][]sheet.LiveCell, len(rows))166 for i := 0; i < len(rows); i += fetchChunk {167 part := rows[i:min(i+fetchChunk, len(rows))]168 marks := strings.Repeat("?, ", len(part))169 args := make([]any, len(part))170 for j, r := range part {171 args[j] = r + 1172 }173 q := "SELECT " + b.list(cols) + " FROM " + b.table + " WHERE rowid IN (" + marks[:len(marks)-2] + ")"174 if err := b.collect(ctx, q, args, got); err != nil {175 return nil, err176 }177 }178 out := make([][]sheet.LiveCell, len(rows))179 for i, r := range rows {180 out[i] = got[r]181 }182 return out, nil183}184185// page reads n rows from row from on.186func (b *sqlBase) page(ctx context.Context, from int64, n int, rows int64) ([]int64, [][]sheet.LiveCell, error) {187 if from < 0 || from >= rows || n <= 0 {188 return nil, nil, nil189 }190 got := map[int64][]sheet.LiveCell{}191 q := "SELECT " + b.list(allCols(nil, len(b.cols))) + " FROM " + b.table + " WHERE rowid BETWEEN ? AND ?"192 if err := b.collect(ctx, q, []any{from + 1, from + int64(n)}, got); err != nil {193 return nil, nil, err194 }195 var nums []int64196 var out [][]sheet.LiveCell197 for r := from; r < min(from+int64(n), rows); r++ {198 nums, out = append(nums, r), append(out, got[r])199 }200 return nums, out, nil201}202203// collect runs a query of a rowid and values, keeping each row's204// values by its number.205func (b *sqlBase) collect(ctx context.Context, q string, args []any, got map[int64][]sheet.LiveCell) error {206 return queryRows(ctx, b.db, q, args, func(v []any) bool {207 cells := make([]sheet.LiveCell, len(v)-1)208 for i, x := range v[1:] {209 c, _ := sqliteCell(x)210 cells[i] = c.typed()211 }212 id, _ := v[0].(int64)213 got[id-1] = cells214 return true215 })216}