forked from brianc/node-sql
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsubquery-tests.js
More file actions
98 lines (92 loc) · 4.32 KB
/
subquery-tests.js
File metadata and controls
98 lines (92 loc) · 4.32 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
'use strict';
var Harness = require('./support');
var customer = Harness.defineCustomerTable();
var user = Harness.defineUserTable();
var Sql = require('../../lib');
Harness.test({
query: user.name.in(
customer.subQuery().select(customer.name).where(
user.name.in(
customer.subQuery().select(customer.name).where(
user.name.like('%HELLO%'))))),
pg: {
text : '("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" LIKE $1)))))',
string: '("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" LIKE \'%HELLO%\')))))'
},
sqlite: {
text : '("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" LIKE $1)))))',
string: '("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" IN (SELECT "customer"."name" FROM "customer" WHERE ("user"."name" LIKE \'%HELLO%\')))))'
},
mysql: {
text : '(`user`.`name` IN (SELECT `customer`.`name` FROM `customer` WHERE (`user`.`name` IN (SELECT `customer`.`name` FROM `customer` WHERE (`user`.`name` LIKE ?)))))',
string: '(`user`.`name` IN (SELECT `customer`.`name` FROM `customer` WHERE (`user`.`name` IN (SELECT `customer`.`name` FROM `customer` WHERE (`user`.`name` LIKE \'%HELLO%\')))))'
},
params: ['%HELLO%']
});
Harness.test({
query: Sql.select('*').from(user.subQuery()),
pg: {
text : 'SELECT * FROM (SELECT * FROM "user")',
string: 'SELECT * FROM (SELECT * FROM "user")'
},
sqlite: {
text : 'SELECT * FROM (SELECT * FROM "user")',
string: 'SELECT * FROM (SELECT * FROM "user")'
},
mysql: {
text : 'SELECT * FROM (SELECT * FROM `user`)',
string: 'SELECT * FROM (SELECT * FROM `user`)'
},
params: []
});
Harness.test({
query: Sql.select('*').from(customer.subQuery('T1')).from(user.subQuery('T2')),
pg: {
text : 'SELECT * FROM (SELECT * FROM "customer") T1 , (SELECT * FROM "user") T2',
string: 'SELECT * FROM (SELECT * FROM "customer") T1 , (SELECT * FROM "user") T2'
},
sqlite: {
text : 'SELECT * FROM (SELECT * FROM "customer") T1 , (SELECT * FROM "user") T2',
string: 'SELECT * FROM (SELECT * FROM "customer") T1 , (SELECT * FROM "user") T2'
},
mysql: {
text : 'SELECT * FROM (SELECT * FROM `customer`) T1 , (SELECT * FROM `user`) T2',
string: 'SELECT * FROM (SELECT * FROM `customer`) T1 , (SELECT * FROM `user`) T2'
},
params: []
});
Harness.test({
query: customer.name.between(
customer.subQuery().select(Sql.functions.MIN(customer.name)),
customer.subQuery().select(Sql.functions.MAX(customer.name))
),
pg: {
text : '("customer"."name" BETWEEN (SELECT MIN("customer"."name") FROM "customer") AND (SELECT MAX("customer"."name") FROM "customer"))',
string: '("customer"."name" BETWEEN (SELECT MIN("customer"."name") FROM "customer") AND (SELECT MAX("customer"."name") FROM "customer"))'
},
sqlite: {
text : '("customer"."name" BETWEEN (SELECT MIN("customer"."name") FROM "customer") AND (SELECT MAX("customer"."name") FROM "customer"))',
string: '("customer"."name" BETWEEN (SELECT MIN("customer"."name") FROM "customer") AND (SELECT MAX("customer"."name") FROM "customer"))'
},
mysql: {
text : '(`customer`.`name` BETWEEN (SELECT MIN(`customer`.`name`) FROM `customer`) AND (SELECT MAX(`customer`.`name`) FROM `customer`))',
string: '(`customer`.`name` BETWEEN (SELECT MIN(`customer`.`name`) FROM `customer`) AND (SELECT MAX(`customer`.`name`) FROM `customer`))'
},
params: []
});
Harness.test({
query: user.subQuery().where(user.name.equals(customer.name)).exists(),
pg: {
text : '(EXISTS (SELECT * FROM "user" WHERE ("user"."name" = "customer"."name")))',
string: '(EXISTS (SELECT * FROM "user" WHERE ("user"."name" = "customer"."name")))'
},
sqlite: {
text : '(EXISTS (SELECT * FROM "user" WHERE ("user"."name" = "customer"."name")))',
string: '(EXISTS (SELECT * FROM "user" WHERE ("user"."name" = "customer"."name")))'
},
mysql: {
text : '(EXISTS (SELECT * FROM `user` WHERE (`user`.`name` = `customer`.`name`)))',
string: '(EXISTS (SELECT * FROM `user` WHERE (`user`.`name` = `customer`.`name`)))'
},
params: []
});