1package fileio23import (4 "context"5 "database/sql"6 "errors"7 "os"8 "path/filepath"9 "strings"10 "testing"1112 "src.rest/cat/012/internal/sheet"13)1415// sqliteFixture makes a database with two tables and a view.16func sqliteFixture(t *testing.T) string {17 t.Helper()18 name := filepath.Join(t.TempDir(), "shop.sqlite")19 db, err := sql.Open("sqlite", name)20 if err != nil {21 t.Fatal(err)22 }23 defer db.Close()24 for _, stmt := range []string{25 `CREATE TABLE orders (id INTEGER, customer TEXT, total REAL, placed TEXT, zip TEXT, note BLOB)`,26 `INSERT INTO orders VALUES (1, 'Ada', 12.5, '2026-09-26', '02139', NULL)`,27 `INSERT INTO orders VALUES (2, '=cmd()', 7, '2026-09-27 14:30:00', '10001', x'00ff10')`,28 `CREATE TABLE customers (name TEXT, since INTEGER)`,29 `INSERT INTO customers VALUES ('Ada', 2020)`,30 `CREATE VIEW big AS SELECT * FROM orders WHERE total > 10`,31 } {32 if _, err := db.Exec(stmt); err != nil {33 t.Fatal(err)34 }35 }36 return name37}3839func TestSQLiteTables(t *testing.T) {40 name := sqliteFixture(t)41 ts, err := Tables(context.Background(), name)42 if err != nil {43 t.Fatal(err)44 }45 var got []string46 for _, tb := range ts {47 got = append(got, tb.Name+":"+strings.Join(tb.Cols, "/")+":"+thousands(tb.Rows))48 }49 want := "big:id/customer/total/placed/zip/note:1 customers:name/since:1 orders:id/customer/total/placed/zip/note:2"50 if strings.Join(got, " ") != want {51 t.Errorf("tables %q", got)52 }53 if !ts[0].View {54 t.Errorf("big isn't a view")55 }56}5758func TestSQLiteImport(t *testing.T) {59 name := sqliteFixture(t)60 ctx := context.Background()6162 _, err := Import(ctx, name, Options{})63 var need *ErrNeedTable64 if !errors.As(err, &need) || len(need.Tables) != 3 {65 t.Fatalf("import without a table: %v", err)66 }6768 res, err := Import(ctx, name, Options{Table: "orders"})69 if err != nil {70 t.Fatal(err)71 }72 s := res.Sheet73 for cell, want := range map[string]string{74 "A1": "id", "F1": "note", "A2": "1", "C2": "12.5", "D2": "2026-09-26", "E2": "02139",75 "B3": "=cmd()", "D3": "9/27/2026 14:30:00", "F3": "(3 bytes)",76 } {77 if g := shown(s, addr(t, cell)); g != want {78 t.Errorf("%s shows %q, want %q", cell, g, want)79 }80 }81 if !s.Cell(addr(t, "A1")).Style.Bold {82 t.Errorf("header isn't bold")83 }84 if res.Rows != 3 || !strings.Contains(strings.Join(res.Notes, ";"), "1 binary value shown as sizes") {85 t.Errorf("rows %d, notes %q", res.Rows, res.Notes)86 }8788 res, err = Import(ctx, name, Options{Query: "SELECT customer, total*2 AS twice FROM orders ORDER BY id DESC"})89 if err != nil {90 t.Fatal(err)91 }92 if g := shown(res.Sheet, addr(t, "B1")) + " " + shown(res.Sheet, addr(t, "B3")); g != "twice 25" {93 t.Errorf("query result %q", g)94 }9596 // Queries can't change the database.97 if _, err := Import(ctx, name, Options{Query: "DELETE FROM orders"}); err == nil {98 t.Errorf("DELETE ran")99 }100 if _, err := Import(ctx, name, Options{Query: "SELECT nope FROM orders"}); err == nil || !strings.Contains(err.Error(), "no such column: nope") {101 t.Errorf("bad query error: %v", err)102 }103 // A file that isn't a database says so.104 junk := filepath.Join(t.TempDir(), "junk.db")105 os.WriteFile(junk, []byte("hello, this is not sqlite at all, not even close......"), 0o644)106 if _, err := Import(ctx, junk, Options{}); err == nil || !strings.Contains(err.Error(), "not a database") {107 t.Errorf("junk error: %v", err)108 }109 // Opening doesn't create missing files.110 missing := filepath.Join(t.TempDir(), "missing.sqlite")111 if _, err := Import(ctx, missing, Options{}); err == nil {112 t.Errorf("imported a missing file")113 }114 if _, err := os.Stat(missing); err == nil {115 t.Errorf("import created %s", missing)116 }117}118119// TestSQLitePastLimit keeps the rows that fit and says how many didn't,120// for a table (counted, not read) and for a query (read to the end).121func TestSQLitePastLimit(t *testing.T) {122 name := filepath.Join(t.TempDir(), "nums.sqlite")123 db, err := sql.Open("sqlite", name)124 if err != nil {125 t.Fatal(err)126 }127 _, err = db.Exec(`CREATE TABLE nums AS WITH RECURSIVE c(n) AS128 (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 8195) SELECT n FROM c`)129 db.Close()130 if err != nil {131 t.Fatal(err)132 }133 for _, opt := range []Options{{Table: "nums", MaxCells: 8192}, {Query: "SELECT n FROM nums", MaxCells: 8192}} {134 res, err := Import(context.Background(), name, opt)135 if err != nil {136 t.Fatal(err)137 }138 if g := shown(res.Sheet, sheet.Addr{Row: 8191}); g != "8191" {139 t.Errorf("%+v: last row shows %q, want 8191", opt, g)140 }141 if res.Rows != 8196 || len(res.Notes) != 1 || res.Notes[0] != "only the first 8,192 rows fit in max-cells (8,192 cells); 4 rows left out" {142 t.Errorf("%+v: rows %d, notes %q", opt, res.Rows, res.Notes)143 }144 }145}146147func TestSQLiteExportRoundTrip(t *testing.T) {148 src := build(t, map[string]string{149 "A1": "Item", "B1": "Qty", "C1": "Price", "D1": "Due", "E1": "", "F1": "Item",150 "A2": "Rent", "B2": "1", "C2": "$1,450.50", "D2": "10/1/2026", "E2": "x", "F2": "TRUE",151 "A3": "Food", "B3": "=B2+2", "C3": "612.4", "D3": "9/28/2026",152 "A5": "Gym", "B5": "4", "C5": "=1/0",153 })154 name := sqliteFixture(t)155 ctx := context.Background()156 res, err := Export(ctx, name, SQLite, Snap(src, sheet.Rect{}, "my bills!"), ExportOptions{})157 if err != nil {158 t.Fatal(err)159 }160 if res.Rows != 3 {161 t.Errorf("wrote %d rows", res.Rows)162 }163 db, _ := sql.Open("sqlite", name)164 defer db.Close()165 var ddl string166 db.QueryRow(`SELECT sql FROM sqlite_master WHERE name = 'my_bills'`).Scan(&ddl)167 want := `CREATE TABLE "my_bills" ("Item" TEXT, "Qty" INTEGER, "Price" REAL, "Due" TEXT, "column_E" TEXT, "Item_2" INTEGER)`168 if ddl != want {169 t.Errorf("table\n%s\nwant\n%s", ddl, want)170 }171 var n int172 db.QueryRow(`SELECT count(*) FROM orders`).Scan(&n)173 if n != 2 {174 t.Errorf("other tables changed: orders has %d rows", n)175 }176177 back, err := Import(ctx, name, Options{Table: "my_bills"})178 if err != nil {179 t.Fatal(err)180 }181 for cell, want := range map[string]string{182 "A2": "Rent", "B3": "3", "C2": "1450.5", "D2": "2026-10-01", "F2": "1", "A4": "Gym", "C4": "",183 } {184 if g := shown(back.Sheet, addr(t, cell)); g != want {185 t.Errorf("%s shows %q, want %q", cell, g, want)186 }187 }188189 // Exporting a selection writes just it, its first row as the header;190 // exporting again replaces the table.191 r := sheet.NewRect(addr(t, "A2"), addr(t, "B3"))192 if _, err := Export(ctx, name, SQLite, Snap(src, r, "x"), ExportOptions{Table: "my_bills"}); err != nil {193 t.Fatal(err)194 }195 db.QueryRow(`SELECT count(*) FROM my_bills WHERE Rent = 'Food'`).Scan(&n)196 if n != 1 {197 t.Errorf("selection export: %d rows", n)198 }199}200201func TestTableName(t *testing.T) {202 for in, want := range map[string]string{"budget": "budget", "my bills!": "my_bills", "2026 sales": "t_2026_sales", "": "sheet", "é": "sheet"} {203 if got := TableName(in); got != want {204 t.Errorf("TableName(%q) = %q, want %q", in, got, want)205 }206 }207}