1package fileio23import (4 "context"5 "database/sql"6 "errors"7 "fmt"8 "math"9 "os"10 "strings"11 "time"1213 _ "modernc.org/sqlite" // registers the "sqlite" driver1415 "src.rest/cat/012/internal/sheet"16)1718// TableInfo describes a table or view in a SQLite database, for the19// table picker.20type TableInfo struct {21 Name string22 View bool23 Rows int24 Cols []string25}2627// openSQLite opens an existing database for reading only.28func openSQLite(ctx context.Context, name string) (*sql.DB, error) {29 if _, err := os.Stat(name); err != nil {30 return nil, err31 }32 db, err := sql.Open("sqlite", name)33 if err != nil {34 return nil, err35 }36 db.SetMaxOpenConns(1)37 if _, err := db.ExecContext(ctx, "PRAGMA query_only = 1"); err != nil {38 db.Close()39 return nil, sqliteErr(err)40 }41 return db, nil42}4344// sqliteErr shortens the driver's errors, e.g. "file is not a database45// (26)" for a file that isn't SQLite.46func sqliteErr(err error) error {47 if err == nil {48 return nil49 }50 msg := err.Error()51 if i := strings.Index(msg, ": "); i >= 0 && strings.HasPrefix(msg, "SQL logic error") {52 msg = msg[i+2:]53 }54 msg = strings.TrimSuffix(msg, " (1)")55 return errors.New(msg)56}5758// quoteIdent quotes a table or column name for SQL.59func quoteIdent(s string) string {60 return `"` + strings.ReplaceAll(s, `"`, `""`) + `"`61}6263// Tables lists the tables and views of a SQLite database with their row64// counts and columns.65func Tables(ctx context.Context, name string) ([]TableInfo, error) {66 db, err := openSQLite(ctx, name)67 if err != nil {68 return nil, err69 }70 defer db.Close()71 return tables(ctx, db)72}7374func tables(ctx context.Context, db *sql.DB) ([]TableInfo, error) {75 rows, err := db.QueryContext(ctx, `SELECT name, type FROM sqlite_master76 WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%' ORDER BY name`)77 if err != nil {78 return nil, sqliteErr(err)79 }80 var out []TableInfo81 for rows.Next() {82 var t TableInfo83 var typ string84 if err := rows.Scan(&t.Name, &typ); err != nil {85 rows.Close()86 return nil, err87 }88 t.View = typ == "view"89 out = append(out, t)90 }91 rows.Close()92 if err := rows.Err(); err != nil {93 return nil, sqliteErr(err)94 }95 for i := range out {96 t := &out[i]97 db.QueryRowContext(ctx, "SELECT count(*) FROM "+quoteIdent(t.Name)).Scan(&t.Rows)98 if r, err := db.QueryContext(ctx, "SELECT * FROM "+quoteIdent(t.Name)+" LIMIT 0"); err == nil {99 t.Cols, _ = r.Columns()100 r.Close()101 }102 }103 return out, nil104}105106func importSQLite(ctx context.Context, name string, opt Options) (*Result, error) {107 db, err := openSQLite(ctx, name)108 if err != nil {109 return nil, err110 }111 defer db.Close()112 query, total, err := sqliteQuery(ctx, db, opt)113 if err != nil {114 return nil, err115 }116 rows, err := db.QueryContext(ctx, query)117 if err != nil {118 return nil, sqliteErr(err)119 }120 defer rows.Close()121 cols, err := rows.Columns()122 if err != nil {123 return nil, sqliteErr(err)124 }125126 b := newBuilder(ctx, opt.MaxCells)127 header := sheet.Style{Bold: true}128 for c, name := range cols {129 b.text(sheet.Addr{Col: c}, name, sheet.Format{}, header)130 }131 row, blobs, err := readSQLiteRows(ctx, rows, b, len(cols), total, opt.Progress)132 if err != nil {133 return nil, err134 }135 var notes []string136 if blobs > 0 {137 notes = append(notes, count(blobs, "binary value", "binary values")+" shown as sizes")138 }139 s, notes := b.finish(notes)140 return &Result{Sheet: s, Rows: row, Notes: notes}, nil141}142143// sqliteQuery is the query an import runs: the options' own, or all of144// the table they name, or of the only table, with its row count; the145// count is -1 for a query.146func sqliteQuery(ctx context.Context, db *sql.DB, opt Options) (string, int, error) {147 if opt.Query != "" {148 return opt.Query, -1, nil149 }150 table := opt.Table151 if table == "" {152 ts, err := tables(ctx, db)153 switch {154 case err != nil:155 return "", 0, err156 case len(ts) == 0:157 return "", 0, errors.New("the database has no tables")158 case len(ts) > 1:159 return "", 0, &ErrNeedTable{Tables: ts}160 }161 table = ts[0].Name162 }163 total := 0164 if err := db.QueryRowContext(ctx, "SELECT count(*) FROM "+quoteIdent(table)).Scan(&total); err != nil {165 return "", 0, sqliteErr(err)166 }167 return "SELECT * FROM " + quoteIdent(table), total, nil168}169170// readSQLiteRows stores the rows below the header and returns the rows171// read, counting the header, and how many values were binary. With a172// known total, rows past the sheet's last are counted, not read.173func readSQLiteRows(ctx context.Context, rows *sql.Rows, b *builder, ncols, total int, prog *Progress) (row, blobs int, err error) {174 vals := make([]any, ncols)175 ptrs := make([]any, ncols)176 for i := range vals {177 ptrs[i] = &vals[i]178 }179 for row = 1; rows.Next(); row++ {180 if row%256 == 0 {181 if err := ctx.Err(); err != nil {182 return 0, 0, err183 }184 prog.setRows(row)185 prog.setFrac(int64(row), int64(total))186 }187 if row >= sheet.MaxRows || b.isFull() {188 if total >= 0 {189 row = max(row, total+1)190 break191 }192 b.fits(sheet.Addr{Row: row})193 continue194 }195 if err := rows.Scan(ptrs...); err != nil {196 return 0, 0, sqliteErr(err)197 }198 for c, v := range vals {199 cell, blob := sqliteCell(v)200 cell.put(b, sheet.Addr{Col: c, Row: row})201 if blob {202 blobs++203 }204 }205 }206 if err := rows.Err(); err != nil {207 return 0, 0, sqliteErr(err)208 }209 b.fits(sheet.Addr{Row: row - 1})210 prog.setRows(row)211 return row, blobs, nil212}213214// textOrDate stores database text as text, except dates and times such215// as 2026-09-26, which become dates as they would when typed. Numbers216// stored as text (zip codes, IDs) stay text.217func textOrDate(b *builder, a sheet.Addr, s string) {218 if _, f, ok := sheet.ParseValue(s); ok {219 switch f.Kind {220 case sheet.FmtDate, sheet.FmtTime, sheet.FmtDateTime:221 b.entry(a, s)222 return223 }224 }225 b.text(a, s, sheet.Format{}, sheet.Style{})226}227228// epoch is day 0 of serial dates.229var epoch = time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)230231// serialOf is t's serial date, reading its wall clock.232func serialOf(t time.Time) float64 {233 wall := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), time.UTC)234 return wall.Sub(epoch).Hours() / 24235}236237// timeOf is the time of a serial date, to the second.238func timeOf(v float64) time.Time {239 days := math.Floor(v)240 secs := math.Round((v - days) * 86400)241 return epoch.AddDate(0, 0, int(days)).Add(time.Duration(secs) * time.Second)242}243244// sqlColumn is a column of an exported table.245type sqlColumn struct {246 name string247 typ string // INTEGER, REAL or TEXT248}249250// exportSQLite writes the snapshot as a table, its first row naming the251// columns. An existing table of that name is replaced; the rest of the252// database is left alone.253func exportSQLite(ctx context.Context, name string, snap *Snapshot, opt ExportOptions) (*ExportResult, error) {254 table := opt.Table255 if table == "" {256 table = TableName(snap.Name)257 }258 r := snap.Range259 cols := make([]sqlColumn, r.To.Col-r.From.Col+1)260 seen := map[string]bool{}261 for i := range cols {262 a := sheet.Addr{Col: r.From.Col + i, Row: r.From.Row}263 n := strings.TrimSpace(snap.Cells[a].Text())264 if n == "" {265 n = "column_" + sheet.ColName(a.Col)266 }267 base := n268 for k := 2; seen[strings.ToLower(n)]; k++ {269 n = fmt.Sprintf("%s_%d", base, k)270 }271 seen[strings.ToLower(n)] = true272 cols[i] = sqlColumn{name: n, typ: columnType(snap, a.Col)}273 }274275 db, err := sql.Open("sqlite", name)276 if err != nil {277 return nil, err278 }279 defer db.Close()280 db.SetMaxOpenConns(1)281 tx, err := db.BeginTx(ctx, nil)282 if err != nil {283 return nil, sqliteErr(err)284 }285 defer tx.Rollback()286 defs := make([]string, len(cols))287 marks := make([]string, len(cols))288 for i, c := range cols {289 defs[i] = quoteIdent(c.name) + " " + c.typ290 marks[i] = "?"291 }292 for _, stmt := range []string{293 "DROP TABLE IF EXISTS " + quoteIdent(table),294 "CREATE TABLE " + quoteIdent(table) + " (" + strings.Join(defs, ", ") + ")",295 } {296 if _, err := tx.ExecContext(ctx, stmt); err != nil {297 return nil, sqliteErr(err)298 }299 }300 ins, err := tx.PrepareContext(ctx, "INSERT INTO "+quoteIdent(table)+" VALUES ("+strings.Join(marks, ", ")+")")301 if err != nil {302 return nil, sqliteErr(err)303 }304 defer ins.Close()305 args := make([]any, len(cols))306 n := 0307 for row := r.From.Row + 1; row <= r.To.Row; row++ {308 empty := true309 for i, col := range cols {310 c, ok := snap.Cells[sheet.Addr{Col: r.From.Col + i, Row: row}]311 args[i] = nil312 if ok {313 args[i] = sqlValue(c, col.typ)314 empty = false315 }316 }317 if empty {318 continue319 }320 if _, err := ins.ExecContext(ctx, args...); err != nil {321 return nil, sqliteErr(err)322 }323 n++324 }325 if err := tx.Commit(); err != nil {326 return nil, sqliteErr(err)327 }328 return &ExportResult{Rows: n}, nil329}330331// columnType picks a column's type from its cells below the header:332// INTEGER or REAL when every value is a number (or boolean), TEXT when333// any is text or a date.334func columnType(snap *Snapshot, col int) string {335 typ := ""336 for row := snap.Range.From.Row + 1; row <= snap.Range.To.Row; row++ {337 c, ok := snap.Cells[sheet.Addr{Col: col, Row: row}]338 if !ok || c.Value.Kind == sheet.Empty || c.Value.Kind == sheet.Error {339 continue340 }341 switch {342 case c.Value.Kind == sheet.Text, c.Format.Kind == sheet.FmtDate, c.Format.Kind == sheet.FmtTime,343 c.Format.Kind == sheet.FmtDateTime, c.Format.Kind == sheet.FmtText:344 return "TEXT"345 case c.Value.Kind == sheet.Number && c.Value.Num != math.Trunc(c.Value.Num):346 typ = "REAL"347 case typ == "":348 typ = "INTEGER"349 }350 }351 if typ == "" {352 return "TEXT"353 }354 return typ355}356357// sqlValue is a cell's value for a column of type typ.358func sqlValue(c SnapCell, typ string) any {359 v := c.Value360 switch v.Kind {361 case sheet.Empty, sheet.Error:362 return nil363 case sheet.Bool:364 if typ == "TEXT" {365 return v.String()366 }367 return int64(v.Num)368 case sheet.Text:369 return v.Str370 }371 switch typ {372 case "INTEGER":373 return int64(v.Num)374 case "REAL":375 return v.Num376 }377 t := timeOf(v.Num)378 switch c.Format.Kind {379 case sheet.FmtDate:380 return t.Format(time.DateOnly)381 case sheet.FmtTime:382 return t.Format(time.TimeOnly)383 case sheet.FmtDateTime:384 return t.Format(time.DateTime)385 }386 return v.String()387}388389// TableName makes a table name from a file or sheet name: letters,390// digits and underscores, not starting with a digit.391func TableName(s string) string {392 var b strings.Builder393 for _, r := range s {394 switch {395 case r >= 'a' && r <= 'z', r >= 'A' && r <= 'Z', r == '_', r >= '0' && r <= '9':396 b.WriteRune(r)397 default:398 b.WriteByte('_')399 }400 }401 out := strings.Trim(b.String(), "_")402 if out == "" {403 return "sheet"404 }405 if out[0] >= '0' && out[0] <= '9' {406 out = "t_" + out407 }408 return out409}