viye/internal/plumbing/sql/sql_test.go (view raw)
| 1 | package sql |
| 2 | |
| 3 | import ( |
| 4 | "context" |
| 5 | "database/sql" |
| 6 | "strings" |
| 7 | "testing" |
| 8 | |
| 9 | "github.com/olexsmir/viye/internal/viye" |
| 10 | "olexsmir.xyz/x/is" |
| 11 | ) |
| 12 | |
| 13 | func TestMatchSQL(t *testing.T) { |
| 14 | for tname, tt := range map[string]struct { |
| 15 | cmd string |
| 16 | args []string |
| 17 | want bool |
| 18 | }{ |
| 19 | "select": {"select", []string{"*", "from", "users"}, true}, |
| 20 | "select with no expression": {"select", nil, false}, |
| 21 | "insert": {"insert", []string{"into", "users"}, true}, |
| 22 | "insert without into": {"insert", []string{"users"}, false}, |
| 23 | "update": {"update", []string{"users", "set", "x=1"}, true}, |
| 24 | "update without set": {"update", []string{"users", "x=1"}, false}, |
| 25 | "delete": {"delete", []string{"from", "users"}, true}, |
| 26 | "delete missing table": {"delete", []string{"from"}, false}, |
| 27 | "create": {"create", []string{"table", "t"}, true}, |
| 28 | "create incomplete": {"create", []string{"table"}, false}, |
| 29 | "drop": {"drop", []string{"table", "t"}, true}, |
| 30 | "alter": {"alter", []string{"table", "t"}, true}, |
| 31 | "pragma": {"pragma", []string{"table_info(t)"}, true}, |
| 32 | "explain": {"explain", []string{"select", "1"}, true}, |
| 33 | "analyze alone": {"analyze", nil, true}, |
| 34 | "uppercase": {"SELECT", []string{"*", "from", "t"}, true}, |
| 35 | "not sql": {"curl", []string{"https://x"}, false}, |
| 36 | } { |
| 37 | t.Run(tname, func(t *testing.T) { |
| 38 | if got := (Tool{}).Match(&viye.Context{Cmd: tt.cmd, Args: tt.args}); got != tt.want { |
| 39 | t.Errorf("Match(%q, %v) = %v, want %v", tt.cmd, tt.args, got, tt.want) |
| 40 | } |
| 41 | }) |
| 42 | } |
| 43 | } |
| 44 | |
| 45 | func TestRunQuery_select(t *testing.T) { |
| 46 | db := newTestDB(t) |
| 47 | ctx := context.Background() |
| 48 | _, err := db.Exec("insert into users values (1, 'alice', 30), (2, 'bob', NULL)") |
| 49 | is.Err(t, err, nil) |
| 50 | |
| 51 | want := `: id name age |
| 52 | : 1 alice 30 |
| 53 | : 2 bob NULL |
| 54 | ` |
| 55 | for _, query := range []string{ |
| 56 | "select * from users order by id", |
| 57 | "SELECT * FROM users ORDER BY id", |
| 58 | } { |
| 59 | out, err := runQuery(ctx, db, query) |
| 60 | is.Err(t, err, nil) |
| 61 | is.Equal(t, want, out) |
| 62 | } |
| 63 | } |
| 64 | |
| 65 | func TestRunQuery_truncation(t *testing.T) { |
| 66 | db := newTestDB(t) |
| 67 | _, err := db.Exec("with recursive c(x) as (select 1 union all select x+1 from c where x < 105) insert into users select x, 'u'||x, x from c") |
| 68 | is.Err(t, err, nil) |
| 69 | |
| 70 | out, err := runQuery(context.Background(), db, "select * from users") |
| 71 | is.Err(t, err, nil) |
| 72 | if !strings.Contains(out, "... 100+ more row(s), output truncated at 100 rows") { |
| 73 | t.Errorf("output not truncated:\n%s", out) |
| 74 | } |
| 75 | } |
| 76 | |
| 77 | func TestWithLimit(t *testing.T) { |
| 78 | for _, tt := range []struct{ in, want string }{ |
| 79 | {"select * from users;", "select * from users limit 101"}, |
| 80 | {"select * from users limit 10", "select * from users limit 10"}, |
| 81 | {"select * from users limit 10 offset 5", "select * from users limit 10 offset 5"}, |
| 82 | {"pragma table_info(users)", "pragma table_info(users)"}, |
| 83 | {"update users set age = 1", "update users set age = 1"}, |
| 84 | } { |
| 85 | is.Equal(t, tt.want, withLimit(tt.in)) |
| 86 | } |
| 87 | } |
| 88 | |
| 89 | func TestRunQuery_write(t *testing.T) { |
| 90 | db := newTestDB(t) |
| 91 | ctx := context.Background() |
| 92 | _, err := db.Exec("insert into users values (1, 'alice', 30)") |
| 93 | is.Err(t, err, nil) |
| 94 | |
| 95 | for _, tt := range []struct{ query, want string }{ |
| 96 | {"update users set age = 40 where id = 1", "| 1 row(s) affected"}, |
| 97 | {"insert into users values (3, 'carol', 40)", "| 1 row(s) affected"}, |
| 98 | {"delete from users where id = 3", "| 1 row(s) affected"}, |
| 99 | } { |
| 100 | out, err := runQuery(ctx, db, tt.query) |
| 101 | is.Err(t, err, nil) |
| 102 | is.Equal(t, tt.want, out) |
| 103 | } |
| 104 | } |
| 105 | |
| 106 | func TestUpdateFromBody(t *testing.T) { |
| 107 | db := newTestDB(t) |
| 108 | ctx := context.Background() |
| 109 | _, err := db.Exec("insert into users values (1, 'alice', 30)") |
| 110 | is.Err(t, err, nil) |
| 111 | |
| 112 | out, err := updateFromBody(ctx, db, "sqlite", "update users", |
| 113 | []string{"id name age", "1 alice 31", "3 carol 40", "garbage"}) |
| 114 | is.Err(t, err, nil) |
| 115 | is.Equal(t, "| 1 updated, 1 inserted, 1 skipped (bad column count)", out) |
| 116 | |
| 117 | var count, aliceAge int |
| 118 | err = db.QueryRow("select count(*) from users").Scan(&count) |
| 119 | is.Err(t, err, nil) |
| 120 | is.Equal(t, 2, count) |
| 121 | err = db.QueryRow("select age from users where id = 1").Scan(&aliceAge) |
| 122 | is.Err(t, err, nil) |
| 123 | is.Equal(t, 31, aliceAge) |
| 124 | } |
| 125 | |
| 126 | func TestExtractTableName(t *testing.T) { |
| 127 | for _, tt := range []struct { |
| 128 | name, query, want string |
| 129 | ok bool |
| 130 | }{ |
| 131 | {"update", "update users set name = 'x' where id = 1", "users", true}, |
| 132 | {"update with subquery", "update users set n = (select 1 from roles) where id = 1", "users", true}, |
| 133 | {"select", "select * from users;", "users", true}, |
| 134 | {"insert", "insert into logs (a) values (1)", "logs", true}, |
| 135 | } { |
| 136 | t.Run(tt.name, func(t *testing.T) { |
| 137 | got, ok := extractTableName(tt.query) |
| 138 | is.Equal(t, tt.want, got) |
| 139 | is.Equal(t, tt.ok, ok) |
| 140 | }) |
| 141 | } |
| 142 | } |
| 143 | |
| 144 | func TestDriverForDSN(t *testing.T) { |
| 145 | for _, tt := range []struct { |
| 146 | dsn, want string |
| 147 | err any |
| 148 | }{ |
| 149 | {"postgres://user:pass@host/db", "postgres", nil}, |
| 150 | {"host=db user=alice", "postgres", nil}, |
| 151 | {"postgres://host/name.db", "postgres", nil}, |
| 152 | {"/tmp/data.db", "sqlite", nil}, |
| 153 | {"/tmp/app.sqlite3", "sqlite", nil}, |
| 154 | {"mongodb://host/db", "", "cannot determine driver"}, |
| 155 | } { |
| 156 | got, err := driverForDSN(tt.dsn) |
| 157 | is.Err(t, err, tt.err) |
| 158 | is.Equal(t, tt.want, got) |
| 159 | } |
| 160 | } |
| 161 | |
| 162 | func TestPlaceholder(t *testing.T) { |
| 163 | is.Equal(t, "$3", placeholder("postgres", 2)) |
| 164 | for _, i := range []int{0, 3} { |
| 165 | is.Equal(t, "?", placeholder("sqlite", i)) |
| 166 | } |
| 167 | } |
| 168 | |
| 169 | func newTestDB(t *testing.T) *sql.DB { |
| 170 | t.Helper() |
| 171 | db, err := sql.Open("sqlite", ":memory:") |
| 172 | is.Err(t, err, nil) |
| 173 | // Single connection keeps the in-memory database shared across queries. |
| 174 | db.SetMaxOpenConns(1) |
| 175 | t.Cleanup(func() { db.Close() }) |
| 176 | _, err = db.Exec("create table users (id integer primary key, name text, age integer)") |
| 177 | is.Err(t, err, nil) |
| 178 | return db |
| 179 | } |