| 22 | ) |
| 23 | |
| 24 | func TestRoundtrip(t *testing.T) { |
| 25 | tests := []struct { |
| 26 | name string |
| 27 | sql string |
| 28 | }{ |
| 29 | {"simple select", "SELECT * FROM users"}, |
| 30 | {"select columns", "SELECT id, name FROM users"}, |
| 31 | {"select where", "SELECT id FROM users WHERE active = TRUE"}, |
| 32 | {"select and", "SELECT id FROM users WHERE active = TRUE AND age > 18"}, |
| 33 | {"select or", "SELECT id FROM users WHERE a = 1 OR b = 2"}, |
| 34 | {"select distinct", "SELECT DISTINCT status FROM orders"}, |
| 35 | {"select limit offset", "SELECT * FROM users LIMIT 10 OFFSET 20"}, |
| 36 | {"select order by", "SELECT * FROM users ORDER BY name"}, |
| 37 | {"select order by desc", "SELECT * FROM users ORDER BY name DESC"}, |
| 38 | {"select group by having", "SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 5"}, |
| 39 | {"select alias", "SELECT COUNT(*) AS total FROM users"}, |
| 40 | {"insert values", "INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com')"}, |
| 41 | {"insert multi row", "INSERT INTO users (name) VALUES ('Alice'), ('Bob')"}, |
| 42 | {"update simple", "UPDATE users SET name = 'Bob' WHERE id = 1"}, |
| 43 | {"delete simple", "DELETE FROM users WHERE id = 1"}, |
| 44 | {"select in list", "SELECT * FROM users WHERE id IN (1, 2, 3)"}, |
| 45 | {"select between", "SELECT * FROM users WHERE age BETWEEN 18 AND 65"}, |
| 46 | {"select is null", "SELECT * FROM users WHERE email IS NULL"}, |
| 47 | {"select like", "SELECT * FROM users WHERE name LIKE '%alice%'"}, |
| 48 | {"select subquery", "SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)"}, |
| 49 | {"select exists", "SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)"}, |
| 50 | {"select case", "SELECT CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END FROM t"}, |
| 51 | {"select cast", "SELECT CAST(price AS INTEGER) FROM products"}, |
| 52 | {"left join", "SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id"}, |
| 53 | {"inner join", "SELECT * FROM a INNER JOIN b ON a.id = b.a_id"}, |
| 54 | {"create table", "CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL)"}, |
| 55 | {"drop table", "DROP TABLE IF EXISTS users CASCADE"}, |
| 56 | {"union", "SELECT id FROM a UNION SELECT id FROM b"}, |
| 57 | {"union all", "SELECT id FROM a UNION ALL SELECT id FROM b"}, |
| 58 | } |
| 59 | |
| 60 | for _, tt := range tests { |
| 61 | t.Run(tt.name, func(t *testing.T) { |
| 62 | ast1, err := gosqlx.Parse(tt.sql) |
| 63 | if err != nil { |
| 64 | t.Fatalf("Failed to parse original SQL %q: %v", tt.sql, err) |
| 65 | } |
| 66 | generated := ast1.SQL() |
| 67 | if generated == "" { |
| 68 | t.Fatalf("SQL() returned empty string for %q", tt.sql) |
| 69 | } |
| 70 | ast2, err := gosqlx.Parse(generated) |
| 71 | if err != nil { |
| 72 | t.Fatalf("Failed to parse generated SQL %q (from %q): %v", generated, tt.sql, err) |
| 73 | } |
| 74 | generated2 := ast2.SQL() |
| 75 | if generated != generated2 { |
| 76 | t.Errorf("Non-idempotent roundtrip:\n original: %s\n generated1: %s\n generated2: %s", tt.sql, generated, generated2) |
| 77 | } |
| 78 | if len(ast1.Statements) != len(ast2.Statements) { |
| 79 | t.Errorf("Statement count mismatch: %d vs %d", len(ast1.Statements), len(ast2.Statements)) |
| 80 | } |
| 81 | }) |