/
query.js
159 lines (128 loc) · 7.12 KB
/
query.js
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
(function() {
describe('joli.query', function() {
var test = new joliTest();
var q;
it('joli.query.count()', function() {
q = new joli.query().count().from('human');
expect(q.getQuery()).toBe('select count(*) as total from human');
});
it('joli.query.destroy()', function() {
q = new joli.query().destroy().from('human');
expect(q.getQuery()).toBe('delete from human');
});
it('joli.query.from()', function() {
q = new joli.query().select().from('human');
expect(q.getQuery()).toBe('select * from human');
});
it('joli.query.groupBy()', function() {
// check beahvior with column names as a string
q = new joli.query().select('city_id, count(*) as total').from('human').groupBy('city_id');
expect(q.getQuery()).toBe('select city_id, count(*) as total from human group by city_id');
q = new joli.query().select('city_id, count(*) as total').from('human').groupBy('city_id, first_name');
expect(q.getQuery()).toBe('select city_id, count(*) as total from human group by city_id, first_name');
// check beahvior with an array of column names
q = new joli.query().select('city_id, count(*) as total').from('human').groupBy(['city_id', 'first_name']);
expect(q.getQuery()).toBe('select city_id, count(*) as total from human group by city_id, first_name');
});
it('joli.query.insertInto()', function() {
q = new joli.query().insertInto('human').values({
first_name: 'John',
last_name: 'Doe'
});
expect(q.getQuery()).toBe('insert into human (first_name, last_name) values (\'John\', \'Doe\')');
});
it('joli.query.join()', function() {
// check behavior with simple local id
q = new joli.query().select()
.from('human')
.join('city', 'id', 'human.city_id');
expect(q.getQuery()).toBe('select * from human left outer join city on city.id = human.city_id');
// check behavior with fully qualified local id
q = new joli.query().select()
.from('human')
.join('city', 'city.id', 'human.city_id');
expect(q.getQuery()).toBe('select * from human left outer join city on city.id = human.city_id');
});
it('joli.query.limit()', function() {
q = new joli.query().select().from('human').limit(10);
expect(q.getQuery()).toBe('select * from human limit 10');
});
it('joli.query.order()', function() {
// check behavior with a string
q = new joli.query().select().from('human').order('first_name');
expect(q.getQuery()).toBe('select * from human order by first_name');
q = new joli.query().select().from('human').order('first_name, last_name');
expect(q.getQuery()).toBe('select * from human order by first_name, last_name');
// check behavior with an array
q = new joli.query().select().from('human').order([
'first_name',
'last_name'
]);
expect(q.getQuery()).toBe('select * from human order by first_name, last_name');
});
it('joli.query.set()', function() {
// check behavior with a simple array
q = new joli.query().update('human').set({ first_name: 'Jane' });
expect(q.getQuery()).toBe('update human set first_name = \'Jane\'');
q = new joli.query().update('human').set({ first_name: 'Jane', last_name: 'Calamity' });
expect(q.getQuery()).toBe('update human set first_name = \'Jane\', last_name = \'Calamity\'');
// check behavior with an expression
var date = new Date().getTime();
q = new joli.query().update('view_count').set({ 'nb_views = nb_views + 1': '', last_viewed: date });
expect(q.getQuery()).toBe('update view_count set nb_views = nb_views + 1, last_viewed = ' + date);
});
it('joli.query.update()', function() {
q = new joli.query().update('human');
expect(q.getQuery()).toBe('update human set ');
});
it('joli.query.values()', function() {
q = new joli.query().insertInto('human').values({
first_name: 'John',
last_name: 'Doe'
});
expect(q.getQuery()).toBe('insert into human (first_name, last_name) values (\'John\', \'Doe\')');
});
it('joli.query.where()', function() {
q = new joli.query().select().from('human').where('last_name = ?', 'Doe');
expect(q.getQuery()).toBe('select * from human where last_name = "Doe"');
// another comparison expression
q = new joli.query().select().from('view_count').where('nb_views > ?', 1000);
expect(q.getQuery()).toBe('select * from view_count where nb_views > "1000"');
// array-based single replacement
q = new joli.query().select().from('view_count').where('nb_views > ?', [1000]);
expect(q.getQuery()).toBe('select * from view_count where nb_views > "1000"');
// multiple value replacements, for instance for between statements
q = new joli.query().select().from('view_count').where('nb_views between ? and ?', [1000, 2000]);
expect(q.getQuery()).toBe('select * from view_count where nb_views between "1000" and "2000"');
// check that replacements stop even if there are less arguments than expected
q = new joli.query().select().from('view_count').where('nb_views between ? and ?', [1000]);
expect(q.getQuery()).toBe('select * from view_count where nb_views between "1000" and ?');
// check that replacements work with 0 values
q = new joli.query().select().from('view_count').where('nb_views between ? and ?', [1000, 0]);
expect(q.getQuery()).toBe('select * from view_count where nb_views between "1000" and "0"');
// check that replacements work for values after 0
q = new joli.query().select().from('view_count').where('nb_views between ? and ? and ?', [1000, 0, 2000]);
expect(q.getQuery()).toBe('select * from view_count where nb_views between "1000" and "0" and "2000"');
// check with several chained calls
q = new joli.query().select().from('human').where('last_name = ?', 'Doe').where('first_name = ?', 'John');
expect(q.getQuery()).toBe('select * from human where last_name = "Doe" and first_name = "John"');
// check a comparison between two model fields
q = new joli.query().select().from('news_articles').where('updated_at > published_at');
expect(q.getQuery()).toBe('select * from news_articles where updated_at > published_at');
});
it('joli.query.whereIn()', function() {
// a simple string
q = new joli.query().select().from('human').whereIn('last_name', '(\'Doe\', \'Smith\')');
expect(q.getQuery()).toBe('select * from human where last_name in (\'Doe\', \'Smith\')');
// an array of values
q = new joli.query().select().from('human').whereIn('last_name', [ 'Doe', 'Smith' ]);
expect(q.getQuery()).toBe('select * from human where last_name in (\'Doe\', \'Smith\')');
// check with several chained calls
q = new joli.query().select().from('human').where('last_name = ?', 'Doe').whereIn('first_name', [ 'John', 'Jane' ]);
expect(q.getQuery()).toBe('select * from human where last_name = "Doe" and first_name in (\'John\', \'Jane\')');
});
/**
* @TODO: add hydratation test methods
*/
});
})();