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