github.com/pjdufour-truss/pop@v4.11.2-0.20190705085848-4c90b0ff4d5a+incompatible/query_test.go (about) 1 package pop 2 3 import ( 4 "fmt" 5 "testing" 6 7 "github.com/gofrs/uuid" 8 "github.com/stretchr/testify/require" 9 ) 10 11 func Test_Where(t *testing.T) { 12 if PDB == nil { 13 t.Skip("skipping integration tests") 14 } 15 a := require.New(t) 16 m := &Model{Value: &Enemy{}} 17 18 q := PDB.Where("id = ?", 1) 19 sql, _ := q.ToSQL(m) 20 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE id = ?"), sql) 21 22 q.Where("first_name = ? and last_name = ?", "Mark", "Bates") 23 sql, _ = q.ToSQL(m) 24 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE id = ? AND first_name = ? and last_name = ?"), sql) 25 26 q = PDB.Where("name = ?", "Mark 'Awesome' Bates") 27 sql, _ = q.ToSQL(m) 28 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE name = ?"), sql) 29 30 q = PDB.Where("name = ?", "'; truncate users; --") 31 sql, _ = q.ToSQL(m) 32 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies WHERE name = ?"), sql) 33 } 34 35 func Test_Where_In(t *testing.T) { 36 if PDB == nil { 37 t.Skip("skipping integration tests") 38 } 39 r := require.New(t) 40 transaction(func(tx *Connection) { 41 u1 := &Song{Title: "A"} 42 u2 := &Song{Title: "B"} 43 u3 := &Song{Title: "C"} 44 err := tx.Create(u1) 45 r.NoError(err) 46 err = tx.Create(u2) 47 r.NoError(err) 48 err = tx.Create(u3) 49 r.NoError(err) 50 51 var songs []Song 52 err = tx.Where("id in (?)", u1.ID, u3.ID).All(&songs) 53 r.NoError(err) 54 r.Len(songs, 2) 55 }) 56 } 57 58 func Test_Where_In_Slice(t *testing.T) { 59 if PDB == nil { 60 t.Skip("skipping integration tests") 61 } 62 r := require.New(t) 63 transaction(func(tx *Connection) { 64 u1 := &Song{Title: "A"} 65 u2 := &Song{Title: "A"} 66 u3 := &Song{Title: "A"} 67 err := tx.Create(u1) 68 r.NoError(err) 69 err = tx.Create(u2) 70 r.NoError(err) 71 err = tx.Create(u3) 72 r.NoError(err) 73 74 var songs []Song 75 err = tx.Where("id in (?)", []uuid.UUID{u1.ID, u3.ID}).Where("title = ?", "A").All(&songs) 76 r.NoError(err) 77 r.Len(songs, 2) 78 }) 79 } 80 81 func Test_Where_In_Complex(t *testing.T) { 82 if PDB == nil { 83 t.Skip("skipping integration tests") 84 } 85 r := require.New(t) 86 transaction(func(tx *Connection) { 87 u1 := &Song{Title: "A"} 88 u2 := &Song{Title: "A"} 89 u3 := &Song{Title: "A"} 90 err := tx.Create(u1) 91 r.NoError(err) 92 err = tx.Create(u2) 93 r.NoError(err) 94 err = tx.Create(u3) 95 r.NoError(err) 96 97 var songs []Song 98 err = tx.Where("id in (?)", u1.ID, u3.ID).Where("title = ?", "A").All(&songs) 99 r.NoError(err) 100 r.Len(songs, 2) 101 }) 102 } 103 104 func Test_Order(t *testing.T) { 105 if PDB == nil { 106 t.Skip("skipping integration tests") 107 } 108 a := require.New(t) 109 110 m := &Model{Value: &Enemy{}} 111 q := PDB.Order("id desc") 112 sql, _ := q.ToSQL(m) 113 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies ORDER BY id desc"), sql) 114 115 q.Order("name desc") 116 sql, _ = q.ToSQL(m) 117 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies ORDER BY id desc, name desc"), sql) 118 } 119 120 func Test_GroupBy(t *testing.T) { 121 if PDB == nil { 122 t.Skip("skipping integration tests") 123 } 124 a := require.New(t) 125 126 m := &Model{Value: &Enemy{}} 127 q := PDB.Q() 128 q.GroupBy("A") 129 sql, _ := q.ToSQL(m) 130 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A"), sql) 131 132 q = PDB.Q() 133 q.GroupBy("A", "B") 134 sql, _ = q.ToSQL(m) 135 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B"), sql) 136 137 q = PDB.Q() 138 q.GroupBy("A", "B").Having("enemies.A=?", "test") 139 sql, _ = q.ToSQL(m) 140 if PDB.Dialect.Details().Dialect == "postgres" { 141 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=$1"), sql) 142 } else { 143 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=?"), sql) 144 } 145 146 q = PDB.Q() 147 q.GroupBy("A", "B").Having("enemies.A=?", "test").Having("enemies.B=enemies.A") 148 sql, _ = q.ToSQL(m) 149 if PDB.Dialect.Details().Dialect == "postgres" { 150 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=$1 AND enemies.B=enemies.A"), sql) 151 } else { 152 a.Equal(ts("SELECT enemies.A FROM enemies AS enemies GROUP BY A, B HAVING enemies.A=? AND enemies.B=enemies.A"), sql) 153 } 154 } 155 156 func Test_ToSQL(t *testing.T) { 157 if PDB == nil { 158 t.Skip("skipping integration tests") 159 } 160 a := require.New(t) 161 transaction(func(tx *Connection) { 162 user := &Model{Value: &User{}} 163 164 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" 165 166 query := Q(tx) 167 q, _ := query.ToSQL(user) 168 a.Equal(s, q) 169 170 query.Order("id desc") 171 q, _ = query.ToSQL(user) 172 a.Equal(fmt.Sprintf("%s ORDER BY id desc", s), q) 173 174 q, _ = query.ToSQL(&Model{Value: &User{}, As: "u"}) 175 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) 176 177 q, _ = query.ToSQL(&Model{Value: &Family{}}) 178 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) 179 180 query = tx.Where("id = 1") 181 q, _ = query.ToSQL(user) 182 a.Equal(fmt.Sprintf("%s WHERE id = 1", s), q) 183 184 query = tx.Where("id = 1").Where("name = 'Mark'") 185 q, _ = query.ToSQL(user) 186 a.Equal(fmt.Sprintf("%s WHERE id = 1 AND name = 'Mark'", s), q) 187 188 query.Order("id desc") 189 q, _ = query.ToSQL(user) 190 a.Equal(fmt.Sprintf("%s WHERE id = 1 AND name = 'Mark' ORDER BY id desc", s), q) 191 192 query.Order("name asc") 193 q, _ = query.ToSQL(user) 194 a.Equal(fmt.Sprintf("%s WHERE id = 1 AND name = 'Mark' ORDER BY id desc, name asc", s), q) 195 196 query = tx.Limit(10) 197 q, _ = query.ToSQL(user) 198 a.Equal(fmt.Sprintf("%s LIMIT 10", s), q) 199 200 query = tx.Paginate(3, 10) 201 q, _ = query.ToSQL(user) 202 a.Equal(fmt.Sprintf("%s LIMIT 10 OFFSET 20", s), q) 203 204 // join must come first 205 query = Q(tx).Where("id = ?", 1).Join("books b", "b.user_id=?", "xx").Order("name asc") 206 q, args := query.ToSQL(user) 207 208 if tx.Dialect.Details().Dialect == "postgres" { 209 a.Equal(fmt.Sprintf("%s JOIN books b ON b.user_id=$1 WHERE id = $2 ORDER BY name asc", s), q) 210 } else { 211 a.Equal(fmt.Sprintf("%s JOIN books b ON b.user_id=? WHERE id = ? ORDER BY name asc", s), q) 212 } 213 // join arguments comes 1st 214 a.Equal(args[0], "xx") 215 a.Equal(args[1], 1) 216 217 query = Q(tx) 218 q, _ = query.ToSQL(user, "distinct on (users.name, users.email) users.*", "users.bio") 219 a.Equal("SELECT distinct on (users.name, users.email) users.*, users.bio FROM users AS users", q) 220 221 query = Q(tx) 222 q, _ = query.ToSQL(user, "distinct on (users.id) users.*", "users.bio") 223 a.Equal("SELECT distinct on (users.id) users.*, users.bio FROM users AS users", q) 224 225 query = Q(tx) 226 q, _ = query.ToSQL(user, "id,r", "users.bio,r", "users.email,w") 227 a.Equal("SELECT id, users.bio FROM users AS users", q) 228 229 query = Q(tx) 230 q, _ = query.ToSQL(user, "distinct on (id) id,r", "users.bio,r", "email,w") 231 a.Equal("SELECT distinct on (id) id, users.bio FROM users AS users", q) 232 233 query = Q(tx) 234 q, _ = query.ToSQL(user, "distinct id", "users.bio,r", "email,w") 235 a.Equal("SELECT distinct id, users.bio FROM users AS users", q) 236 237 query = Q(tx) 238 q, _ = query.ToSQL(user, "distinct id", "concat(users.name,'-',users.email)") 239 a.Equal("SELECT concat(users.name,'-',users.email), distinct id FROM users AS users", q) 240 241 query = Q(tx) 242 q, _ = query.ToSQL(user, "id", "concat(users.name,'-',users.email) name_email") 243 a.Equal("SELECT concat(users.name,'-',users.email) name_email, id FROM users AS users", q) 244 245 query = Q(tx) 246 q, _ = query.ToSQL(user, "distinct id", "concat(users.name,'-',users.email),r") 247 a.Equal("SELECT concat(users.name,'-',users.email), distinct id FROM users AS users", q) 248 249 query = Q(tx) 250 q, _ = query.ToSQL(user, "distinct id", "concat(users.name,'-',users.email) AS x") 251 a.Equal("SELECT concat(users.name,'-',users.email) AS x, distinct id FROM users AS users", q) 252 253 query = Q(tx) 254 q, _ = query.ToSQL(user, "distinct id", "users.name as english_name", "email private_email") 255 a.Equal("SELECT distinct id, email private_email, users.name as english_name FROM users AS users", q) 256 }) 257 } 258 259 func Test_ToSQLInjection(t *testing.T) { 260 if PDB == nil { 261 t.Skip("skipping integration tests") 262 } 263 a := require.New(t) 264 transaction(func(tx *Connection) { 265 user := &Model{Value: &User{}} 266 query := tx.Where("name = '?'", "\\\u0027 or 1=1 limit 1;\n-- ") 267 q, _ := query.ToSQL(user) 268 a.NotEqual("SELECT * FROM users AS users WHERE name = '\\'' or 1=1 limit 1;\n-- '", q) 269 }) 270 } 271 272 func Test_ToSQL_RawQuery(t *testing.T) { 273 if PDB == nil { 274 t.Skip("skipping integration tests") 275 } 276 a := require.New(t) 277 transaction(func(tx *Connection) { 278 query := tx.RawQuery("this is some ? raw ?", "random", "query") 279 q, args := query.ToSQL(nil) 280 a.Equal(q, tx.Dialect.TranslateSQL("this is some ? raw ?")) 281 a.Equal(args, []interface{}{"random", "query"}) 282 }) 283 }