Skip to content

Commit 4803d7a

Browse files
author
Di Wu
committed
update the rest of the tests
1 parent bfa041b commit 4803d7a

15 files changed

Lines changed: 1049 additions & 340 deletions

‎test/dialects/join-tests.js‎

Lines changed: 96 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -6,71 +6,123 @@ var post = Harness.definePostTable();
66
var comment = Harness.defineCommentTable();
77

88
Harness.test({
9-
query : user.select(user.name, post.content).from(user.join(post).on(user.id.equals(post.userId))),
10-
pg : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
11-
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
12-
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
9+
query: user.select(user.name, post.content).from(user.join(post).on(user.id.equals(post.userId))),
10+
pg: {
11+
text: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
12+
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
13+
},
14+
sqlite: {
15+
text: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
16+
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
17+
},
18+
mysql: {
19+
text: 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)',
20+
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
21+
},
22+
params: []
1323
});
1424

1525
Harness.test({
16-
query : user.join(post).on(user.id.equals(post.userId)),
17-
pg : '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
18-
sqlite: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
19-
mysql : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
26+
query: user.join(post).on(user.id.equals(post.userId)),
27+
pg: {
28+
text: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
29+
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
30+
},
31+
sqlite: {
32+
text: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
33+
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
34+
},
35+
mysql: {
36+
text: '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)',
37+
string: '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
38+
},
39+
params: []
2040
});
2141

2242
Harness.test({
23-
query : user
24-
.select(user.name, post.content, comment.text)
25-
.from(
26-
user
27-
.join(post).on(user.id.equals(post.userId))
28-
.join(comment).on(post.id.equals(comment.postId))
29-
),
30-
pg : 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")' +
31-
' INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
32-
sqlite: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")' +
33-
' INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
34-
mysql : 'SELECT `user`.`name`, `post`.`content`, `comment`.`text` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)' +
35-
' INNER JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
43+
query: user
44+
.select(user.name, post.content, comment.text)
45+
.from(
46+
user
47+
.join(post).on(user.id.equals(post.userId))
48+
.join(comment).on(post.id.equals(comment.postId))),
49+
pg: {
50+
text: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
51+
string: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")'
52+
},
53+
sqlite: {
54+
text: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
55+
string: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")'
56+
},
57+
mysql: {
58+
text: 'SELECT `user`.`name`, `post`.`content`, `comment`.`text` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) INNER JOIN `comment` ON (`post`.`id` = `comment`.`postId`)',
59+
string: 'SELECT `user`.`name`, `post`.`content`, `comment`.`text` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) INNER JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
60+
},
61+
params: []
3662
});
3763

3864
Harness.test({
39-
query : user.select(user.name, post.content).from(user.leftJoin(post).on(user.id.equals(post.userId))),
40-
pg : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
41-
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
42-
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)'
65+
query: user.select(user.name, post.content).from(user.leftJoin(post).on(user.id.equals(post.userId))),
66+
pg: {
67+
text: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
68+
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")'
69+
},
70+
sqlite: {
71+
text: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
72+
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")'
73+
},
74+
mysql: {
75+
text: 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)',
76+
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)'
77+
},
78+
params: []
4379
});
4480

4581
Harness.test({
46-
query : user
47-
.select(user.name, post.content)
48-
.from(
49-
user
50-
.leftJoin(post).on(user.id.equals(post.userId))
51-
.leftJoin(comment).on(post.id.equals(comment.postId))
52-
),
53-
pg : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")' +
54-
' LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
55-
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")' +
56-
' LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
57-
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)' +
58-
' LEFT JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
82+
query: user
83+
.select(user.name, post.content)
84+
.from(
85+
user
86+
.leftJoin(post).on(user.id.equals(post.userId))
87+
.leftJoin(comment).on(post.id.equals(comment.postId))),
88+
pg: {
89+
text: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
90+
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")'
91+
},
92+
sqlite: {
93+
text: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
94+
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")'
95+
},
96+
mysql: {
97+
text: 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`) LEFT JOIN `comment` ON (`post`.`id` = `comment`.`postId`)',
98+
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`) LEFT JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
99+
},
100+
params: []
59101
});
60102

61103
var subposts = post
62104
.subQuery('subposts')
63105
.select(
64-
post.content,
65-
post.userId.as('subpostUserId'))
106+
post.content,
107+
post.userId.as('subpostUserId'))
66108
.from(post);
67109

68110
Harness.test({
69-
query : user
111+
query: user
70112
.select(user.name, subposts.content)
71113
.from(user.join(subposts)
72114
.on(user.id.equals(subposts.subpostUserId))),
73-
pg : 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
74-
sqlite: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
75-
mysql : 'SELECT `user`.`name`, `subposts`.`content` FROM `user` INNER JOIN (SELECT `post`.`content`, `post`.`userId` AS `subpostUserId` FROM `post`) subposts ON (`user`.`id` = `subposts`.`subpostUserId`)'
115+
pg: {
116+
text: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
117+
string: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")'
118+
},
119+
sqlite: {
120+
text: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
121+
string: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")'
122+
},
123+
mysql: {
124+
text: 'SELECT `user`.`name`, `subposts`.`content` FROM `user` INNER JOIN (SELECT `post`.`content`, `post`.`userId` AS `subpostUserId` FROM `post`) subposts ON (`user`.`id` = `subposts`.`subpostUserId`)',
125+
string: 'SELECT `user`.`name`, `subposts`.`content` FROM `user` INNER JOIN (SELECT `post`.`content`, `post`.`userId` AS `subpostUserId` FROM `post`) subposts ON (`user`.`id` = `subposts`.`subpostUserId`)'
126+
},
127+
params: []
76128
});

‎test/dialects/join-to-tests.js‎

Lines changed: 48 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,9 @@ var Harness = require('./support');
66
var user = sql.define({
77
name: 'user',
88
columns: {
9-
id: { primaryKey: true }
9+
id: {
10+
primaryKey: true
11+
}
1012
}
1113
});
1214

@@ -22,7 +24,9 @@ var photo = sql.define({
2224
var post = sql.define({
2325
name: 'post',
2426
columns: {
25-
id: { primaryKey: true },
27+
id: {
28+
primaryKey: true
29+
},
2630
ownerId: {
2731
references: {
2832
table: 'user',
@@ -33,22 +37,52 @@ var post = sql.define({
3337
});
3438

3539
Harness.test({
36-
query : user.joinTo(post),
37-
pg : '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
38-
sqlite: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
39-
mysql : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)'
40+
query: user.joinTo(post),
41+
pg: {
42+
text: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
43+
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")'
44+
},
45+
sqlite: {
46+
text: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
47+
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")'
48+
},
49+
mysql: {
50+
text: '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)',
51+
string: '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)'
52+
},
53+
params: []
4054
});
4155

4256
Harness.test({
43-
query : post.joinTo(user),
44-
pg : '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
45-
sqlite: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
46-
mysql : '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)'
57+
query: post.joinTo(user),
58+
pg: {
59+
text: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
60+
string: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")'
61+
},
62+
sqlite: {
63+
text: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
64+
string: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")'
65+
},
66+
mysql: {
67+
text: '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)',
68+
string: '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)'
69+
},
70+
params: []
4771
});
4872

4973
Harness.test({
50-
query : user.joinTo(photo),
51-
pg : '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
52-
sqlite: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
53-
mysql : '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)'
74+
query: user.joinTo(photo),
75+
pg: {
76+
text: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
77+
string: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")'
78+
},
79+
sqlite: {
80+
text: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
81+
string: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")'
82+
},
83+
mysql: {
84+
text: '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)',
85+
string: '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)'
86+
},
87+
params: []
5488
});

‎test/dialects/limit-and-offset-tests.js‎

Lines changed: 59 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -7,30 +7,73 @@ var user = Harness.defineUserTable();
77
// http://dev.mysql.com/doc/refman/5.0/en/select.html
88

99
Harness.test({
10-
query : user.select(user.star()).from(user).order(user.name.asc).limit(1),
11-
pg : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
12-
sqlite: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
13-
mysql : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1'
10+
query: user.select(user.star()).from(user).order(user.name.asc).limit(1),
11+
pg: {
12+
text: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
13+
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1'
14+
},
15+
sqlite: {
16+
text: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
17+
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1'
18+
},
19+
mysql: {
20+
text: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1',
21+
string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1'
22+
},
23+
params: []
1424
});
1525

1626
Harness.test({
17-
query : user.select(user.star()).from(user).order(user.name.asc).limit(3).offset(6),
18-
pg : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
19-
sqlite: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
20-
mysql : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6'
27+
query: user.select(user.star()).from(user).order(user.name.asc).limit(3).offset(6),
28+
pg: {
29+
text: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
30+
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6'
31+
},
32+
sqlite: {
33+
text: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
34+
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6'
35+
},
36+
mysql: {
37+
text: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6',
38+
string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6'
39+
},
40+
params: []
2141
});
2242

2343
Harness.test({
24-
query : user.select(user.star()).from(user).order(user.name.asc).offset(10),
25-
pg : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
26-
sqlite: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
27-
mysql : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10'
44+
query: user.select(user.star()).from(user).order(user.name.asc).offset(10),
45+
pg: {
46+
text: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
47+
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10'
48+
},
49+
sqlite: {
50+
text: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
51+
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10'
52+
},
53+
mysql: {
54+
text: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10',
55+
string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10'
56+
},
57+
params: []
2858
});
2959

3060
Harness.test({
31-
query : user.select(user.star()).where({name: 'John'}).offset(user.subQuery().select('FLOOR(RANDOM() * COUNT(*))').where({name: 'John'})).limit(1),
32-
pg : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
33-
sqlite: 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
34-
mysql : 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = ?)) LIMIT 1',
61+
query: user.select(user.star()).where({
62+
name: 'John'
63+
}).offset(user.subQuery().select('FLOOR(RANDOM() * COUNT(*))').where({
64+
name: 'John'
65+
})).limit(1),
66+
pg: {
67+
text: 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
68+
string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) LIMIT 1'
69+
},
70+
sqlite: {
71+
text: 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
72+
string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) LIMIT 1'
73+
},
74+
mysql: {
75+
text: 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = ?)) LIMIT 1',
76+
string: 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = \'John\')) LIMIT 1'
77+
},
3578
values: ['John', 'John']
3679
});

0 commit comments

Comments
 (0)