| 1 | package dbquery |
| 2 | |
| 3 | import ( |
| 4 | "bytes" |
| 5 | "context" |
| 6 | "database/sql" |
| 7 | "errors" |
| 8 | "path/filepath" |
| 9 | "strings" |
| 10 | "testing" |
| 11 | |
| 12 | _ "modernc.org/sqlite" |
| 13 | ) |
| 14 | |
| 15 | func fixtureDB(t *testing.T) string { |
| 16 | t.Helper() |
| 17 | path := filepath.Join(t.TempDir(), "shop.db") |
| 18 | db, err := sql.Open("sqlite", path) |
| 19 | if err != nil { |
| 20 | t.Fatal(err) |
| 21 | } |
| 22 | defer db.Close() |
| 23 | for _, q := range []string{ |
| 24 | `CREATE TABLE orders (id INTEGER PRIMARY KEY, note TEXT, data BLOB)`, |
| 25 | `INSERT INTO orders (note, data) VALUES ('a tab', x'00ff'), (NULL, NULL), ('two |
| 26 | lines', 'x')`, |
| 27 | } { |
| 28 | if _, err := db.Exec(q); err != nil { |
| 29 | t.Fatal(err) |
| 30 | } |
| 31 | } |
| 32 | return path |
| 33 | } |
| 34 | |
| 35 | func TestRunContextFormatsLikeTheCLI(t *testing.T) { |
| 36 | t.Parallel() |
| 37 | path := fixtureDB(t) |
| 38 | for _, opt := range []Options{{}, {Immutable: true}} { |
| 39 | var out bytes.Buffer |
| 40 | if err := RunContext(context.Background(), &out, path, "SELECT id, note FROM orders ORDER BY id", opt); err != nil { |
| 41 | t.Fatalf("%+v: %v", opt, err) |
| 42 | } |
| 43 | want := "id\tnote\n1\ta\\ttab\n2\tNULL\n3\ttwo\\nlines\n" |
| 44 | if out.String() != want { |
| 45 | t.Fatalf("%+v: got %q, want %q", opt, out.String(), want) |
| 46 | } |
| 47 | } |
| 48 | } |
| 49 | |
| 50 | func TestRunContextRefusesWritesAndAttach(t *testing.T) { |
| 51 | t.Parallel() |
| 52 | path := fixtureDB(t) |
| 53 | for _, opt := range []Options{{}, {Immutable: true}} { |
| 54 | for _, q := range []string{ |
| 55 | "DELETE FROM orders", |
| 56 | "ATTACH DATABASE 'x.db' AS x", |
| 57 | "VACUUM INTO 'copy.db'", |
| 58 | } { |
| 59 | if err := RunContext(context.Background(), &bytes.Buffer{}, path, q, opt); err == nil { |
| 60 | t.Fatalf("%+v: %q ran", opt, q) |
| 61 | } |
| 62 | } |
| 63 | } |
| 64 | } |
| 65 | |
| 66 | func TestCheckSQLTable(t *testing.T) { |
| 67 | t.Parallel() |
| 68 | for _, c := range []struct { |
| 69 | q string |
| 70 | want error |
| 71 | }{ |
| 72 | {"SELECT 1", nil}, |
| 73 | {"SELECT 1; SELECT 2", ErrOneStatement}, |
| 74 | {"SELECT ';' AS x", nil}, |
| 75 | {"PRAGMA table_info(orders)", nil}, |
| 76 | {"PRAGMA query_only = 0", ErrPragmaChange}, |
| 77 | {"EXPLAIN QUERY PLAN PRAGMA writable_schema=1", ErrPragmaChange}, |
| 78 | {"SELECT pragma FROM t", nil}, |
| 79 | } { |
| 80 | if got := CheckSQL(c.q); !errors.Is(got, c.want) && !(got == nil && c.want == nil) { |
| 81 | t.Errorf("CheckSQL(%q) = %v, want %v", c.q, got, c.want) |
| 82 | } |
| 83 | } |
| 84 | } |
| 85 | |
| 86 | func TestImmutableOpenAddsTheFlagOnly(t *testing.T) { |
| 87 | t.Parallel() |
| 88 | if got := dsn("/x/copy.db", Options{}); strings.Contains(got, "immutable") { |
| 89 | t.Fatalf("the CLI's open changed: %s", got) |
| 90 | } |
| 91 | if got := dsn("/x/copy.db", Options{Immutable: true}); !strings.Contains(got, "mode=ro&immutable=1&") { |
| 92 | t.Fatalf("the worker's open lacks immutable=1: %s", got) |
| 93 | } |
| 94 | } |
| 95 | |
amadan