github.com/friesencr/pop/v6@v6.1.6/query_test.go (about) 1 package pop 2 3 import ( 4 "context" 5 "fmt" 6 "testing" 7 8 "github.com/gofrs/uuid/v5" 9 "github.com/stretchr/testify/require" 10 ) 11 12 func Test_Where(t *testing.T) { 13 if PDB == nil { 14 t.Skip("skipping integration tests") 15 } 16 a := require.New(t) 17 m := NewModel(new(Enemy), context.Background()) 18 19 q := PDB.Where("id = ?", 1) 20 sql, args := q.ToSQL(m) 21 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE id = ?"), sql) 22 a.Equal([]interface{}{1}, args) 23 24 q.Where("first_name = ? and last_name = ?", "Mark", "Bates") 25 sql, args = q.ToSQL(m) 26 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE id = ? AND first_name = ? and last_name = ?"), sql) 27 a.Equal([]interface{}{1, "Mark", "Bates"}, args) 28 29 q = PDB.Where("name = ?", "Mark 'Awesome' Bates") 30 sql, args = q.ToSQL(m) 31 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE name = ?"), sql) 32 a.Equal([]interface{}{"Mark 'Awesome' Bates"}, args) 33 34 q = PDB.Where("name = ?", "'; truncate users; --") 35 sql, _ = q.ToSQL(m) 36 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE name = ?"), sql) 37 38 q = PDB.Where("id is not null") // no args 39 sql, args = q.ToSQL(m) 40 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE id is not null"), sql) 41 a.Equal([]interface{}{}, args) 42 } 43 44 func Test_Where_In(t *testing.T) { 45 if PDB == nil { 46 t.Skip("skipping integration tests") 47 } 48 r := require.New(t) 49 transaction(func(tx *Connection) { 50 u1 := &Song{Title: "A"} 51 u2 := &Song{Title: "B"} 52 u3 := &Song{Title: "C"} 53 r.NoError(tx.Create(u1)) 54 r.NoError(tx.Create(u2)) 55 r.NoError(tx.Create(u3)) 56 57 var songs []Song 58 err := tx.Where("id in (?)", u1.ID, u3.ID).All(&songs) 59 r.NoError(err) 60 r.Len(songs, 2) 61 }) 62 } 63 64 func Test_Where_In_Slice(t *testing.T) { 65 if PDB == nil { 66 t.Skip("skipping integration tests") 67 } 68 r := require.New(t) 69 transaction(func(tx *Connection) { 70 u1 := &Song{Title: "A"} 71 u2 := &Song{Title: "A"} 72 u3 := &Song{Title: "A"} 73 r.NoError(tx.Create(u1)) 74 r.NoError(tx.Create(u2)) 75 r.NoError(tx.Create(u3)) 76 77 Debug = true 78 defer func() { Debug = false }() 79 80 var songs []Song 81 err := tx.Where("id in (?)", []uuid.UUID{u1.ID, u3.ID}).Where("title = ?", "A").All(&songs) 82 r.NoError(err) 83 r.Len(songs, 2) 84 85 // especially https://github.com/gobuffalo/pop/issues/699 86 err = tx.Where("id in (?)", []uuid.UUID{u1.ID, u3.ID}).Delete(&Song{}) 87 r.NoError(err) 88 89 var remainingSongs []Song 90 r.NoError(tx.All(&remainingSongs)) 91 r.Len(remainingSongs, 1) 92 }) 93 } 94 95 func Test_Where_In_One(t *testing.T) { 96 if PDB == nil { 97 t.Skip("skipping integration tests") 98 } 99 r := require.New(t) 100 transaction(func(tx *Connection) { 101 u1 := &Song{Title: "A"} 102 u2 := &Song{Title: "B"} 103 u3 := &Song{Title: "C"} 104 r.NoError(tx.Create(u1)) 105 r.NoError(tx.Create(u2)) 106 r.NoError(tx.Create(u3)) 107 108 Debug = true 109 defer func() { Debug = false }() 110 111 var songs []Song 112 err := tx.Where("id in (?)", u1.ID).All(&songs) 113 r.NoError(err) 114 r.Len(songs, 1) 115 }) 116 } 117 118 func Test_Where_In_Complex(t *testing.T) { 119 if PDB == nil { 120 t.Skip("skipping integration tests") 121 } 122 r := require.New(t) 123 transaction(func(tx *Connection) { 124 u1 := &Song{Title: "A"} 125 u2 := &Song{Title: "A"} 126 u3 := &Song{Title: "A"} 127 r.NoError(tx.Create(u1)) 128 r.NoError(tx.Create(u2)) 129 r.NoError(tx.Create(u3)) 130 131 var songs []Song 132 err := tx.Where("id in (?)", u1.ID, u3.ID).Where("title = ?", "A").All(&songs) 133 r.NoError(err) 134 r.Len(songs, 2) 135 }) 136 } 137 138 func Test_Where_In_Complex_One(t *testing.T) { 139 if PDB == nil { 140 t.Skip("skipping integration tests") 141 } 142 r := require.New(t) 143 transaction(func(tx *Connection) { 144 u1 := &Song{Title: "A"} 145 u2 := &Song{Title: "A"} 146 u3 := &Song{Title: "A"} 147 r.NoError(tx.Create(u1)) 148 r.NoError(tx.Create(u2)) 149 r.NoError(tx.Create(u3)) 150 151 Debug = true 152 defer func() { Debug = false }() 153 154 var songs []Song 155 err := tx.Where("id in (?)", u3.ID).Where("title = ?", "A").All(&songs) 156 r.NoError(err) 157 r.Len(songs, 1) 158 }) 159 } 160 161 func Test_Where_In_Space(t *testing.T) { 162 if PDB == nil { 163 t.Skip("skipping integration tests") 164 } 165 r := require.New(t) 166 transaction(func(tx *Connection) { 167 u1 := &Song{Title: "A"} 168 u2 := &Song{Title: "B"} 169 u3 := &Song{Title: "C"} 170 r.NoError(tx.Create(u1)) 171 r.NoError(tx.Create(u2)) 172 r.NoError(tx.Create(u3)) 173 174 Debug = true 175 defer func() { Debug = false }() 176 177 var songs []Song 178 err := tx.Where("id in ( ? )", u1.ID, u3.ID).All(&songs) 179 r.NoError(err) 180 r.Len(songs, 2) 181 }) 182 } 183 184 func Test_Order(t *testing.T) { 185 if PDB == nil { 186 t.Skip("skipping integration tests") 187 } 188 a := require.New(t) 189 190 m := NewModel(&Enemy{}, context.Background()) 191 q := PDB.Order("id desc") 192 sql, _ := q.ToSQL(m) 193 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies ORDER BY id desc"), sql) 194 195 q.Order("name desc") 196 sql, _ = q.ToSQL(m) 197 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies ORDER BY id desc, name desc"), sql) 198 } 199 200 func Test_Order_With_Args(t *testing.T) { 201 if PDB == nil { 202 t.Skip("skipping integration tests") 203 } 204 r := require.New(t) 205 transaction(func(tx *Connection) { 206 u1 := &Song{Title: "A"} 207 u2 := &Song{Title: "B"} 208 u3 := &Song{Title: "C"} 209 r.NoError(tx.Create(u1)) 210 r.NoError(tx.Create(u2)) 211 r.NoError(tx.Create(u3)) 212 213 var songs []Song 214 err := tx.Where("id in (?)", []uuid.UUID{u1.ID, u2.ID, u3.ID}). 215 Order("title > ? DESC", "A").Order("title"). 216 All(&songs) 217 r.NoError(err) 218 r.Len(songs, 3) 219 r.Equal("B", songs[0].Title) 220 r.Equal("C", songs[1].Title) 221 r.Equal("A", songs[2].Title) 222 }) 223 } 224 225 func Test_GroupBy(t *testing.T) { 226 if PDB == nil { 227 t.Skip("skipping integration tests") 228 } 229 a := require.New(t) 230 231 m := NewModel(&Enemy{}, context.Background()) 232 q := PDB.Q() 233 q.GroupBy("A") 234 sql, _ := q.ToSQL(m) 235 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A"), sql) 236 237 q = PDB.Q() 238 q.GroupBy("A", "B") 239 sql, _ = q.ToSQL(m) 240 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B"), sql) 241 242 q = PDB.Q() 243 q.GroupBy("A", "B").Having("enemies.A=?", "test") 244 sql, _ = q.ToSQL(m) 245 if PDB.Dialect.Details().Dialect == "postgres" { 246 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=$1"), sql) 247 } else { 248 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=?"), sql) 249 } 250 251 q = PDB.Q() 252 q.GroupBy("A", "B").Having("enemies.A=?", "test").Having("enemies.B=enemies.A") 253 sql, _ = q.ToSQL(m) 254 if PDB.Dialect.Details().Dialect == "postgres" { 255 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=$1 AND enemies.B=enemies.A"), sql) 256 } else { 257 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=? AND enemies.B=enemies.A"), sql) 258 } 259 } 260 261 func Test_ToSQL(t *testing.T) { 262 if PDB == nil { 263 t.Skip("skipping integration tests") 264 } 265 a := require.New(t) 266 transaction(func(tx *Connection) { 267 user := NewModel(&User{}, tx.Context()) 268 269 s := "SELECT name as full_name, users.alive, users.bio, users.birth_date, users.created_at, users.email, users.id, users.name, users.price, users.updated_at, users.user_name FROM users AS users" 270 271 query := Q(tx) 272 q, _ := query.ToSQL(user) 273 a.Equal(s, q) 274 275 query.Order("id desc") 276 q, _ = query.ToSQL(user) 277 a.Equal(fmt.Sprintf("%s ORDER BY id desc", s), q) 278 279 q, _ = query.ToSQL(&Model{Value: &User{}, As: "u", ctx: tx.Context()}) 280 a.Equal("SELECT name as full_name, u.alive, u.bio, u.birth_date, u.created_at, u.email, u.id, u.name, u.price, u.updated_at, u.user_name FROM users AS u ORDER BY id desc", q) 281 282 q, _ = query.ToSQL(&Model{Value: &Family{}, ctx: tx.Context()}) 283 a.Equal("SELECT family_members.created_at, family_members.first_name, family_members.id, family_members.last_name, family_members.updated_at FROM family.members AS family_members ORDER BY id desc", q) 284 285 query = tx.Where("id = 1") 286 q, _ = query.ToSQL(user) 287 a.Equal(fmt.Sprintf("%s WHERE id = 1", s), q) 288 289 query = tx.Where("id = 1").Where("name = 'Mark'") 290 q, _ = query.ToSQL(user) 291 a.Equal(fmt.Sprintf("%s WHERE id = 1 AND name = 'Mark'", s), q) 292 293 query.Order("id desc") 294 q, _ = query.ToSQL(user) 295 a.Equal(fmt.Sprintf("%s WHERE id = 1 AND name = 'Mark' ORDER BY id desc", s), q) 296 297 query.Order("name asc") 298 q, _ = query.ToSQL(user) 299 a.Equal(fmt.Sprintf("%s WHERE id = 1 AND name = 'Mark' ORDER BY id desc, name asc", s), q) 300 301 query = tx.Limit(10) 302 q, _ = query.ToSQL(user) 303 a.Equal(fmt.Sprintf("%s LIMIT 10", s), q) 304 305 query = tx.Paginate(3, 10) 306 q, _ = query.ToSQL(user) 307 a.Equal(fmt.Sprintf("%s LIMIT 10 OFFSET 20", s), q) 308 309 // join must come first 310 query = Q(tx).Where("id = ?", 1).Join("books b", "b.user_id=?", "xx").Order("name asc") 311 q, args := query.ToSQL(user) 312 313 if tx.Dialect.Details().Dialect == "postgres" { 314 a.Equal(fmt.Sprintf("%s JOIN books b ON b.user_id=$1 WHERE id = $2 ORDER BY name asc", s), q) 315 } else { 316 a.Equal(fmt.Sprintf("%s JOIN books b ON b.user_id=? WHERE id = ? ORDER BY name asc", s), q) 317 } 318 // join arguments comes 1st 319 a.Equal(args[0], "xx") 320 a.Equal(args[1], 1) 321 322 query = Q(tx) 323 q, _ = query.ToSQL(user, "distinct on (users.name, users.email) users.*", "users.bio") 324 a.Equal("SELECT distinct on (users.name, users.email) users.*, users.bio FROM users AS users", q) 325 326 query = Q(tx) 327 q, _ = query.ToSQL(user, "distinct on (users.id) users.*", "users.bio") 328 a.Equal("SELECT distinct on (users.id) users.*, users.bio FROM users AS users", q) 329 330 query = Q(tx) 331 q, _ = query.ToSQL(user, "id,r", "users.bio,r", "users.email,w") 332 a.Equal("SELECT id, users.bio FROM users AS users", q) 333 334 query = Q(tx) 335 q, _ = query.ToSQL(user, "distinct on (id) id,r", "users.bio,r", "email,w") 336 a.Equal("SELECT distinct on (id) id, users.bio FROM users AS users", q) 337 338 query = Q(tx) 339 q, _ = query.ToSQL(user, "distinct id", "users.bio,r", "email,w") 340 a.Equal("SELECT distinct id, users.bio FROM users AS users", q) 341 342 query = Q(tx) 343 q, _ = query.ToSQL(user, "distinct id", "concat(users.name,'-',users.email)") 344 a.Equal("SELECT concat(users.name,'-',users.email), distinct id FROM users AS users", q) 345 346 query = Q(tx) 347 q, _ = query.ToSQL(user, "id", "concat(users.name,'-',users.email) name_email") 348 a.Equal("SELECT concat(users.name,'-',users.email) name_email, id FROM users AS users", q) 349 350 query = Q(tx) 351 q, _ = query.ToSQL(user, "distinct id", "concat(users.name,'-',users.email),r") 352 a.Equal("SELECT concat(users.name,'-',users.email), distinct id FROM users AS users", q) 353 354 query = Q(tx) 355 q, _ = query.ToSQL(user, "distinct id", "concat(users.name,'-',users.email) AS x") 356 a.Equal("SELECT concat(users.name,'-',users.email) AS x, distinct id FROM users AS users", q) 357 358 query = Q(tx) 359 q, _ = query.ToSQL(user, "distinct id", "users.name as english_name", "email private_email") 360 a.Equal("SELECT distinct id, email private_email, users.name as english_name FROM users AS users", q) 361 }) 362 } 363 364 func Test_ToSQLInjection(t *testing.T) { 365 if PDB == nil { 366 t.Skip("skipping integration tests") 367 } 368 a := require.New(t) 369 transaction(func(tx *Connection) { 370 user := NewModel(new(User), tx.Context()) 371 query := tx.Where("name = '?'", "\\\u0027 or 1=1 limit 1;\n-- ") 372 q, _ := query.ToSQL(user) 373 a.NotEqual("SELECT * FROM users AS users WHERE name = '\\'' or 1=1 limit 1;\n-- '", q) 374 }) 375 } 376 377 func Test_ToSQL_RawQuery(t *testing.T) { 378 if PDB == nil { 379 t.Skip("skipping integration tests") 380 } 381 a := require.New(t) 382 transaction(func(tx *Connection) { 383 query := tx.RawQuery("this is some ? raw ?", "random", "query") 384 q, args := query.ToSQL(nil) 385 a.Equal(q, tx.Dialect.TranslateSQL("this is some ? raw ?")) 386 a.Equal(args, []interface{}{"random", "query"}) 387 }) 388 } 389 390 func Test_RawQuery_Empty(t *testing.T) { 391 if PDB == nil { 392 t.Skip("skipping integration tests") 393 } 394 t.Run("EmptyQuery", func(t *testing.T) { 395 r := require.New(t) 396 transaction(func(tx *Connection) { 397 r.Error(tx.Q().Exec()) 398 }) 399 }) 400 401 t.Run("EmptyRawQuery", func(t *testing.T) { 402 r := require.New(t) 403 transaction(func(tx *Connection) { 404 r.Error(tx.RawQuery("").Exec()) 405 }) 406 }) 407 }