1package fileio23import (4 "context"5 "database/sql"6 "errors"7 "fmt"8 "net/url"9 "os"10 "path/filepath"11 "slices"12 "strconv"13 "strings"14 "sync"1516 "src.rest/cat/012/internal/sheet"17)1819// sqliteSource is a SQLite table, view or query read in place, through20// connections that can only read (mode=ro, query_only), so the database21// is never written. Its rows are in rowid order for a table, and as the22// query or view gives them otherwise.23//24// A row's number is found cheaply only in a table whose rowids are 1 to25// its row count (dense), the common case of a table only ever appended26// to: row n is rowid n+1. Any other source is copied, when something27// first asks for rows by number, into a table of a work database in28// the temporary directory (sourcesqlwork.go), where it is dense; views'29// row orders are tables there too.30type sqliteSource struct {31 spec SourceSpec32 db *sql.DB33 query string // the SELECT giving the source's rows, in order34 table string // the table, quoted, when the source is one with rowids35 names []string36 meta []SourceColumn37 rows int6438 dense bool3940 mu sync.Mutex41 work *sqlWork // made when first needed42 base *sqlBase // where rows are found by number, once known43}4445// sqliteURI is the URI opening a database file to read only.46func sqliteURI(path string, params ...string) (string, error) {47 abs, err := filepath.Abs(path)48 if err != nil {49 return "", err50 }51 u := url.URL{Scheme: "file", Path: filepath.ToSlash(abs), RawQuery: strings.Join(params, "&")}52 return u.String(), nil53}5455func openSQLiteSource(ctx context.Context, spec SourceSpec) (*sqliteSource, error) {56 if _, err := os.Stat(spec.Path); err != nil {57 return nil, err58 }59 uri, err := sqliteURI(spec.Path, "mode=ro", "_pragma=query_only(1)", "_pragma=busy_timeout(10000)")60 if err != nil {61 return nil, err62 }63 db, err := sql.Open("sqlite", uri)64 if err != nil {65 return nil, err66 }67 s := &sqliteSource{spec: spec, db: db}68 if err := s.open(ctx); err != nil {69 db.Close()70 return nil, err71 }72 return s, nil73}7475// open finds what the source reads, its columns and its row count.76func (s *sqliteSource) open(ctx context.Context) error {77 s.query = s.spec.Query78 if s.query == "" {79 table := s.spec.Table80 if table == "" {81 ts, err := tables(ctx, s.db)82 switch {83 case err != nil:84 return err85 case len(ts) == 0:86 return errors.New("the database has no tables")87 case len(ts) > 1:88 return &ErrNeedTable{Tables: ts}89 }90 table = ts[0].Name91 }92 s.query = "SELECT * FROM " + quoteIdent(table)93 if s.hasRowid(ctx, table) {94 s.table = quoteIdent(table)95 s.query += " ORDER BY rowid"96 }97 }98 if err := s.columns(ctx); err != nil {99 return err100 }101 return s.count(ctx)102}103104// hasRowid reports whether table is a table with rowids (not a view or105// a WITHOUT ROWID table).106func (s *sqliteSource) hasRowid(ctx context.Context, table string) bool {107 rows, err := s.db.QueryContext(ctx, "SELECT rowid FROM "+quoteIdent(table)+" LIMIT 0")108 if err != nil {109 return false110 }111 rows.Close()112 var typ string113 err = s.db.QueryRowContext(ctx, "SELECT type FROM sqlite_master WHERE name = ?", table).Scan(&typ)114 return err == nil && typ == "table"115}116117// count counts the rows, and for a table whether its rowids are dense.118func (s *sqliteSource) count(ctx context.Context) error {119 if s.table == "" {120 err := s.db.QueryRowContext(ctx, "SELECT count(*) FROM ("+s.query+")").Scan(&s.rows)121 return sqliteErr(err)122 }123 var lo, hi sql.NullInt64124 err := s.db.QueryRowContext(ctx, "SELECT count(*), min(rowid), max(rowid) FROM "+s.table).Scan(&s.rows, &lo, &hi)125 if err != nil {126 return sqliteErr(err)127 }128 s.dense = s.rows == 0 || lo.Int64 == 1 && hi.Int64 == s.rows129 return nil130}131132// columns reads the columns' names and types: a declared type says133// whether one holds numbers or dates; an undeclared one (a query's134// expressions) is judged by its first rows.135func (s *sqliteSource) columns(ctx context.Context) error {136 rows, err := s.db.QueryContext(ctx, "SELECT * FROM ("+s.query+") LIMIT 0")137 if err != nil {138 return sqliteErr(err)139 }140 types, err := rows.ColumnTypes()141 rows.Close()142 if err != nil {143 return sqliteErr(err)144 }145 var untyped []int146 for i, t := range types {147 c := SourceColumn{Name: t.Name()}148 decl := strings.ToUpper(t.DatabaseTypeName())149 if decl == "" {150 untyped = append(untyped, i)151 } else {152 c.Format, c.Numeric = declaredFormat(decl)153 }154 s.names = append(s.names, t.Name())155 s.meta = append(s.meta, c)156 }157 if len(untyped) > 0 {158 return s.sample(ctx, untyped)159 }160 return nil161}162163// declaredFormat is the format a declared type shows its column's164// values in, and whether it holds numbers: dates and times, the165// decimals of a DECIMAL(10,2) or NUMERIC(10,2), and money as currency.166func declaredFormat(decl string) (sheet.Format, bool) {167 switch {168 case strings.Contains(decl, "DATETIME"), strings.Contains(decl, "TIMESTAMP"):169 return sheet.Format{Kind: sheet.FmtDateTime}, true170 case strings.Contains(decl, "DATE"):171 return sheet.Format{Kind: sheet.FmtDate}, true172 case strings.Contains(decl, "TIME"):173 return sheet.Format{Kind: sheet.FmtTime}, true174 case strings.Contains(decl, "MONEY"), strings.Contains(decl, "CURRENCY"):175 return sheet.Format{Kind: sheet.FmtCurrency, Decimals: 2}, true176 case strings.HasPrefix(decl, "DECIMAL"), strings.HasPrefix(decl, "NUMERIC"):177 if scale, ok := declaredScale(decl); ok {178 return sheet.Format{Kind: sheet.FmtNumber, Decimals: scale}, true179 }180 }181 return sheet.Format{}, numericAffinity(decl)182}183184// declaredScale reads the scale of a type declared as DECIMAL(10,2).185func declaredScale(decl string) (int, bool) {186 _, args, found := strings.Cut(decl, "(")187 args, _, closed := strings.Cut(args, ")")188 _, scale, two := strings.Cut(args, ",")189 if !found || !closed || !two {190 return 0, false191 }192 n, err := strconv.Atoi(strings.TrimSpace(scale))193 if err != nil || n < 0 || n > sheet.MaxDecimals {194 return 0, false195 }196 return n, true197}198199// numericAffinity reports whether a declared type gives a column200// SQLite's INTEGER, REAL or NUMERIC affinity.201func numericAffinity(decl string) bool {202 switch {203 case strings.Contains(decl, "CHAR"), strings.Contains(decl, "CLOB"), strings.Contains(decl, "TEXT"),204 strings.Contains(decl, "BLOB"):205 return false206 }207 return true208}209210// sampleRows is how many rows judge an undeclared column's type.211const sampleRows = 100212213// sample marks the columns cols numeric when their first rows hold214// numbers and nothing else.215func (s *sqliteSource) sample(ctx context.Context, cols []int) error {216 num := make([]bool, len(s.meta))217 seen := make([]bool, len(s.meta))218 for _, c := range cols {219 num[c] = true220 }221 err := s.scanSQL(ctx, "SELECT * FROM ("+s.query+") LIMIT "+strconv.Itoa(sampleRows), nil, func(vals []any) bool {222 for _, c := range cols {223 switch vals[c].(type) {224 case nil:225 case int64, float64:226 seen[c] = true227 default:228 num[c] = false229 }230 }231 return true232 })233 for _, c := range cols {234 s.meta[c].Numeric = num[c] && seen[c]235 }236 return err237}238239func (s *sqliteSource) Columns() []SourceColumn { return slices.Clone(s.meta) }240241func (s *sqliteSource) Rows() int64 { return s.rows }242243// selectCols is the select list reading cols, and whether the columns244// come back as asked: a table's by name, anything else whole, picked245// from.246func (s *sqliteSource) selectCols(cols []int) (string, bool) {247 if s.table == "" {248 return "*", false249 }250 names := make([]string, len(cols))251 for i, c := range cols {252 names[i] = quoteIdent(s.names[c])253 }254 return strings.Join(names, ", "), true255}256257// Scan streams the rows from row from on.258func (s *sqliteSource) Scan(ctx context.Context, from int64, cols []int, fn func(int64, []sheet.LiveCell) bool) error {259 cols = allCols(cols, len(s.names))260 if err := checkCols(cols, len(s.names)); err != nil {261 return err262 }263 list, picked := s.selectCols(cols)264 var q string265 var args []any266 switch {267 case s.table != "" && s.dense:268 q, args = "SELECT "+list+" FROM "+s.table+" WHERE rowid > ? ORDER BY rowid", []any{from}269 case s.table != "":270 q, args = "SELECT "+list+" FROM "+s.table+" ORDER BY rowid LIMIT -1 OFFSET ?", []any{from}271 default:272 q, args = "SELECT * FROM ("+s.query+") LIMIT -1 OFFSET ?", []any{from}273 }274 out := make([]sheet.LiveCell, len(cols))275 row := max(from, 0)276 return s.scanSQL(ctx, q, args, func(vals []any) bool {277 for i, c := range cols {278 v := vals[i]279 if !picked {280 v = vals[c]281 }282 cell, _ := sqliteCell(v)283 out[i] = cell.typed()284 }285 row++286 return fn(row-1, out)287 })288}289290// scanSQL runs a query on the source, calling fn with each row's values291// until it returns false.292func (s *sqliteSource) scanSQL(ctx context.Context, q string, args []any, fn func([]any) bool) error {293 return queryRows(ctx, s.db, q, args, fn)294}295296// queryRows runs a query, calling fn with each row's values (reused)297// until it returns false.298func queryRows(ctx context.Context, db interface {299 QueryContext(context.Context, string, ...any) (*sql.Rows, error)300}, q string, args []any, fn func([]any) bool) error {301 rows, err := db.QueryContext(ctx, q, args...)302 if err != nil {303 return sqliteErr(err)304 }305 defer rows.Close()306 cols, err := rows.Columns()307 if err != nil {308 return sqliteErr(err)309 }310 vals := make([]any, len(cols))311 ptrs := make([]any, len(cols))312 for i := range vals {313 ptrs[i] = &vals[i]314 }315 for rows.Next() {316 if err := rows.Scan(ptrs...); err != nil {317 return sqliteErr(err)318 }319 if !fn(vals) {320 return nil321 }322 }323 if err := rows.Err(); err != nil {324 if ctx.Err() != nil {325 return ctx.Err()326 }327 return sqliteErr(err)328 }329 return nil330}331332// Fetch reads the rows numbered rows.333func (s *sqliteSource) Fetch(ctx context.Context, rows []int64, cols []int) ([][]sheet.LiveCell, error) {334 cols = allCols(cols, len(s.names))335 if err := checkCols(cols, len(s.names)); err != nil {336 return nil, err337 }338 for _, r := range rows {339 if r < 0 || r >= s.rows {340 return nil, fmt.Errorf("the source has no row %d", r+1)341 }342 }343 b, err := s.byNumber(ctx)344 if err != nil {345 return nil, err346 }347 return b.fetch(ctx, rows, cols)348}349350func (s *sqliteSource) Close() error {351 s.mu.Lock()352 w := s.work353 s.work, s.base = nil, nil354 s.mu.Unlock()355 var err error356 if w != nil {357 err = w.close()358 }359 return errors.Join(err, s.db.Close())360}