1package fileio23import (4 "context"5 "database/sql"6 "fmt"7 "os"8 "path/filepath"9 "slices"10 "testing"1112 "src.rest/cat/012/internal/sheet"13)1415// writeSalesDB writes a database whose table sales has n rows (fewer,16// with rowids missing, when sparse), a view of it, a WITHOUT ROWID17// table, and a column of mixed kinds with no declared type.18func writeSalesDB(t testing.TB, dir string, n int, sparse bool) string {19 t.Helper()20 name := filepath.Join(dir, fmt.Sprintf("sales-%d-%v.sqlite", n, sparse))21 db, err := sql.Open("sqlite", name)22 if err != nil {23 t.Fatal(err)24 }25 defer db.Close()26 tx, err := db.Begin()27 if err != nil {28 t.Fatal(err)29 }30 for _, q := range []string{31 "CREATE TABLE sales (id INTEGER, cat TEXT, amount REAL, day TEXT, mixed)",32 "CREATE VIEW big AS SELECT * FROM sales WHERE amount > 50",33 "CREATE TABLE keyed (k TEXT PRIMARY KEY, v REAL) WITHOUT ROWID",34 "CREATE TABLE other (x)",35 } {36 if _, err := tx.Exec(q); err != nil {37 t.Fatal(err)38 }39 }40 ins, err := tx.Prepare("INSERT INTO sales VALUES (?, ?, ?, ?, ?)")41 if err != nil {42 t.Fatal(err)43 }44 for i := range n {45 var cat, amount, mixed any46 if i%9 != 4 {47 cat = saleCats[i%len(saleCats)]48 }49 if i%11 != 7 {50 amount = float64((i*37)%101) - 20.551 }52 switch i % 6 {53 case 0:54 mixed = int64(i % 13)55 case 1:56 mixed = fmt.Sprintf("t%d", i%7)57 case 2:58 mixed = 2.559 case 3:60 mixed = "12"61 case 4:62 mixed = ""63 }64 day := fmt.Sprintf("2026-01-%02d", 1+i%28)65 if _, err := ins.Exec(i, cat, amount, day, mixed); err != nil {66 t.Fatal(err)67 }68 if _, err := tx.Exec("INSERT INTO keyed VALUES (?, ?)", fmt.Sprintf("k%05d", n-i), i); err != nil {69 t.Fatal(err)70 }71 }72 if sparse {73 if _, err := tx.Exec("DELETE FROM sales WHERE id % 7 = 3"); err != nil {74 t.Fatal(err)75 }76 }77 if err := tx.Commit(); err != nil {78 t.Fatal(err)79 }80 return name81}8283// sqlOrders are orders only a SQLite source's table has columns for.84var sqlOrders = []SourceOrder{85 {Sort: []SourceSort{{Col: 4}}},86 {Sort: []SourceSort{{Col: 4, Desc: true}}},87 {Filter: []SourceFilter{{Col: 4, Cond: sheet.Condition{Op: sheet.CondGreater, Arg: "2"}}}},88 {Filter: []SourceFilter{{Col: 4, Cond: sheet.Condition{Op: sheet.CondLess, Arg: "T3"}}}},89 {Filter: []SourceFilter{{Col: 4, Cond: sheet.Condition{Op: sheet.CondEqual, Arg: "12"}}}},90}9192func TestSQLiteSource(t *testing.T) {93 dir := t.TempDir()94 for _, sparse := range []bool{false, true} {95 t.Run(fmt.Sprint("sparse=", sparse), func(t *testing.T) { checkSQLiteTable(t, writeSalesDB(t, dir, 700, sparse)) })96 }97}9899// checkSQLiteTable reads the table sales of name every way a source100// reads, as the sheet its import makes shows it.101func checkSQLiteTable(t *testing.T, name string) {102 tmp := t.TempDir()103 src, err := OpenSource(context.Background(), SourceSpec{Path: name, Table: "sales", TempDir: tmp})104 if err != nil {105 t.Fatal(err)106 }107 s, r := imported(t, name, Options{Table: "sales"})108 want := sheetRows(s, r)109 if src.Rows() != int64(len(want)) {110 t.Fatalf("%d rows, want %d", src.Rows(), len(want))111 }112 var cols []string113 for _, c := range src.Columns() {114 cols = append(cols, fmt.Sprintf("%s:%v", c.Name, c.Numeric))115 }116 if !slices.Equal(cols, []string{"id:true", "cat:false", "amount:true", "day:false", "mixed:false"}) {117 t.Errorf("columns %q", cols)118 }119 var got []string120 err = src.Scan(context.Background(), 300, nil, func(row int64, v []sheet.LiveCell) bool {121 got = append(got, cellsText(v))122 return true123 })124 if err != nil || !slices.Equal(got, want[300:]) {125 t.Errorf("scan from 300: %v %q", err, head(got))126 }127 rows, err := src.Fetch(context.Background(), []int64{int64(len(want) - 1), 0, 350}, nil)128 if err != nil {129 t.Fatal(err)130 }131 for i, n := range []int{len(want) - 1, 0, 350} {132 if g := cellsText(rows[i]); g != want[n] {133 t.Errorf("row %d is %q, want %q", n, g, want[n])134 }135 }136 checkOrders(t, src, name, Options{Table: "sales"}, append(slices.Clone(orders), sqlOrders...))137 if err := src.Close(); err != nil {138 t.Fatal(err)139 }140 if left, _ := os.ReadDir(tmp); len(left) != 0 {141 t.Errorf("closing left %d files", len(left))142 }143}144145// TestSQLiteSourceKinds reads a view, a WITHOUT ROWID table and a146// query, which are paged through a copy in the work database.147func TestSQLiteSourceKinds(t *testing.T) {148 dir := t.TempDir()149 name := writeSalesDB(t, dir, 300, false)150 for _, spec := range []SourceSpec{151 {Path: name, Table: "big"},152 {Path: name, Table: "keyed"},153 {Path: name, Query: "SELECT cat, sum(amount) AS total, count(*) AS n FROM sales GROUP BY cat"},154 {Path: name, Query: "SELECT id, id FROM sales WHERE id < 40"},155 } {156 spec.TempDir = t.TempDir()157 src, err := OpenSource(context.Background(), spec)158 if err != nil {159 t.Fatalf("%+v: %v", spec, err)160 }161 s, r := imported(t, name, Options{Table: spec.Table, Query: spec.Query})162 want := sheetRows(s, r)163 v, err := src.View(context.Background(), SourceOrder{})164 if err != nil {165 t.Fatal(err)166 }167 if _, got := viewRows(t, v, 50); !slices.Equal(got, want) {168 t.Errorf("%+v: %q, want %q", spec, head(got), head(want))169 }170 sorted := []SourceOrder{{Sort: []SourceSort{{Col: 1, Desc: true}}}}171 checkOrders(t, src, name, Options{Table: spec.Table, Query: spec.Query}, sorted)172 src.Close()173 if left, _ := os.ReadDir(spec.TempDir); len(left) != 0 {174 t.Errorf("%+v: closing left %d files", spec, len(left))175 }176 }177 if _, err := OpenSource(context.Background(), SourceSpec{Path: name}); err == nil {178 t.Error("a database of several tables opened without naming one")179 }180 if _, err := OpenSource(context.Background(), SourceSpec{Path: name, Query: "SELECT nope FROM sales"}); err == nil {181 t.Error("a query that fails opened")182 }183}184185// TestSQLiteSourceReadOnly checks the database file is never written.186func TestSQLiteSourceReadOnly(t *testing.T) {187 name := writeSalesDB(t, t.TempDir(), 200, true)188 before, err := os.ReadFile(name)189 if err != nil {190 t.Fatal(err)191 }192 src, err := OpenSource(context.Background(), SourceSpec{Path: name, Table: "sales", TempDir: t.TempDir()})193 if err != nil {194 t.Fatal(err)195 }196 v, err := src.View(context.Background(), SourceOrder{Sort: []SourceSort{{Col: 2}}})197 if err != nil {198 t.Fatal(err)199 }200 v.Page(context.Background(), 100, 20)201 src.Close()202 after, _ := os.ReadFile(name)203 if !slices.Equal(before, after) {204 t.Error("the database changed")205 }206 if _, err := os.Stat(name + "-journal"); err == nil {207 t.Error("the database has a journal")208 }209}