oppi.li
an ORM-free SQL experience
1package norm
2
3import (
4 "slices"
5 "testing"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10func TestInsertBuild_Success(t *testing.T) {
11 tests := []struct {
12 name string
13 stmt Compiler
14 expectedSql string
15 expectedArgs []any
16 }{
17 {
18 name: "Simple insert",
19 stmt: Insert().Into("users").Value("name", "John"),
20 expectedSql: "INSERT INTO users (name) VALUES (?)",
21 expectedArgs: []any{"John"},
22 },
23 {
24 name: "Abort clause",
25 stmt: Insert().Or(Abort).Into("users").Value("name", "John"),
26 expectedSql: "INSERT OR ABORT INTO users (name) VALUES (?)",
27 expectedArgs: []any{"John"},
28 },
29 {
30 name: "Ignore clause",
31 stmt: Insert().Or(Ignore).Into("users").Value("name", "John"),
32 expectedSql: "INSERT OR IGNORE INTO users (name) VALUES (?)",
33 expectedArgs: []any{"John"},
34 },
35 {
36 name: "Fail clause",
37 stmt: Insert().Or(Fail).Into("users").Value("name", "John"),
38 expectedSql: "INSERT OR FAIL INTO users (name) VALUES (?)",
39 expectedArgs: []any{"John"},
40 },
41 {
42 name: "Replace clause",
43 stmt: Insert().Or(Replace).Into("users").Value("name", "John"),
44 expectedSql: "INSERT OR REPLACE INTO users (name) VALUES (?)",
45 expectedArgs: []any{"John"},
46 },
47 {
48 name: "Rollback clause",
49 stmt: Insert().Or(Rollback).Into("users").Value("name", "John"),
50 expectedSql: "INSERT OR ROLLBACK INTO users (name) VALUES (?)",
51 expectedArgs: []any{"John"},
52 },
53 {
54 name: "Default clause",
55 stmt: Insert().Or(InsertOr(10)).Into("users").Value("name", "John"),
56 expectedSql: "INSERT INTO users (name) VALUES (?)",
57 expectedArgs: []any{"John"},
58 },
59 {
60 name: "More values",
61 stmt: Insert().Into("users").Value("name", "John").Value("age", 35),
62 expectedSql: "INSERT INTO users (name, age) VALUES (?, ?)",
63 expectedArgs: []any{"John", 35},
64 },
65 }
66
67 for _, test := range tests {
68 t.Run(test.name, func(t *testing.T) {
69 sql, args := test.stmt.MustCompile()
70
71 if sql != test.expectedSql {
72 t.Errorf("Expected '%s', got '%s'", test.expectedSql, sql)
73 }
74
75 if len(args) != len(test.expectedArgs) {
76 t.Errorf("Expected '%d' args, got '%d' args", len(test.expectedArgs), len(args))
77 }
78
79 for i := range len(args) {
80 if args[i] != test.expectedArgs[i] {
81 t.Errorf("Expected '%v', got '%v' at index %d", test.expectedArgs[i], args[i], i)
82 }
83 }
84 })
85 }
86}
87
88func TestInsertValueSet_Build(t *testing.T) {
89 tests := []struct {
90 name string
91 stmt Compiler
92 expectedConfig []struct {
93 sql string
94 args []any
95 }
96 }{
97 {
98 name: "Simple insert",
99 stmt: Insert().Into("users").Values(map[string]any{
100 "name": "John",
101 "age": 25,
102 }),
103 expectedConfig: []struct {
104 sql string
105 args []any
106 }{
107 {
108 sql: "INSERT INTO users (name, age) VALUES (?, ?)",
109 args: []any{"John", 25},
110 },
111 {
112 sql: "INSERT INTO users (age, name) VALUES (?, ?)",
113 args: []any{25, "John"},
114 },
115 },
116 },
117 }
118
119 for _, test := range tests {
120 t.Run(test.name, func(t *testing.T) {
121 sql, args := test.stmt.MustCompile()
122
123 any := false
124 idx := 0
125 for i, config := range test.expectedConfig {
126 idx = i
127 equalSql := config.sql == sql
128 equalArgs := slices.Equal(config.args, args)
129 if equalSql && equalArgs {
130 any = true
131 }
132 }
133
134 if !any {
135 t.Errorf("Config did not match: %d: %q; got %q, %q", idx, test.expectedConfig[idx], sql, args)
136 }
137
138 })
139 }
140}
141
142func TestInsertCompileFail(t *testing.T) {
143 tests := []struct {
144 name string
145 stmt Compiler
146 expectedError string
147 }{
148 {
149 name: "No into clause",
150 stmt: Insert(),
151 expectedError: "INTO clause is required",
152 },
153 {
154 name: "No into clause",
155 stmt: Insert().Into("users"),
156 expectedError: "no values supplied",
157 },
158 }
159
160 for _, test := range tests {
161 t.Run(test.name, func(t *testing.T) {
162 sql, args, err := test.stmt.Compile()
163 if err == nil {
164 t.Error("Expected error, got nil")
165 }
166
167 if err.Error() != test.expectedError {
168 t.Errorf("Expected error '%s', got '%s'", test.expectedError, err.Error())
169 }
170
171 if sql != "" {
172 t.Errorf("Expected empty SQL on error, got '%s'", sql)
173 }
174
175 if args != nil {
176 t.Errorf("Expected empty args on error, got '%q'", args)
177 }
178 })
179 }
180}
181
182func TestInsertIntegration(t *testing.T) {
183 tests := []struct {
184 name string
185 stmt Execer
186 expectedRows int64
187 }{
188 {
189 name: "Delete all users",
190 stmt: Delete().From("users"),
191 expectedRows: 6,
192 },
193 {
194 name: "Delete active users only",
195 stmt: Delete().
196 From("users").
197 Where(Eq("active", true)),
198 expectedRows: 4,
199 },
200 {
201 name: "Select users in Engineering",
202 stmt: Delete().
203 From("users").
204 Where(Eq("department", "Engineering")),
205 expectedRows: 3,
206 },
207 {
208 name: "Delete users with age > 30",
209 stmt: Delete().
210 From("users").
211 Where(Gt("age", 30)),
212 expectedRows: 2,
213 },
214 {
215 name: "Delete users with salary between 70000 and 80000",
216 stmt: Delete().
217 From("users").
218 Where(Gte("salary", 70000.0).And(Lte("salary", 80000.0))),
219 expectedRows: 3,
220 },
221 }
222
223 for _, test := range tests {
224 t.Run(test.name, func(t *testing.T) {
225 db := setupTestDB(t)
226 defer db.Close()
227
228 res, err := test.stmt.Exec(db)
229 if err != nil {
230 t.Fatalf("Failed to execute query: %v", err)
231 }
232
233 count, err := res.RowsAffected()
234 if err != nil {
235 t.Fatalf("Failed to execute query: %v", err)
236 }
237
238 if count != test.expectedRows {
239 t.Errorf("Expected %d rows, got %d", test.expectedRows, count)
240 }
241 })
242 }
243}