1 | mohair = require '../src/mohair'
|
2 |
|
3 | module.exports =
|
4 |
|
5 | 'throw on':
|
6 |
|
7 | 'insert without table': (test) ->
|
8 | q = mohair.insert {name: 'foo', email: 'foo@example.com'}
|
9 | test.throws -> q.sql()
|
10 |
|
11 | test.done()
|
12 |
|
13 | 'create multiple records without matching keys': (test) ->
|
14 | test.throws ->
|
15 | mohair.table('user').insertMany [
|
16 | {name: 'foo', email: 'foo@example.com'}
|
17 | {name: 'bar'}
|
18 | ]
|
19 |
|
20 | test.throws ->
|
21 | mohair.table('user').insertMany [
|
22 | {name: 'foo', email: 'foo@example.com'}
|
23 | {name: 'bar', id: 9}
|
24 | ]
|
25 |
|
26 | test.done()
|
27 |
|
28 | 'insert':
|
29 | 'simple': (test) ->
|
30 | q = mohair.table('user').insert {name: 'foo', user_id: 5}
|
31 |
|
32 | test.equal q.sql(), 'INSERT INTO user(name, user_id) VALUES (?, ?)'
|
33 | test.deepEqual q.params(), ['foo', 5]
|
34 |
|
35 | test.done()
|
36 |
|
37 | 'with null values': (test) ->
|
38 | q = mohair.table('user').insert {name: 'foo', user_id: null}
|
39 |
|
40 | test.equal q.sql(), 'INSERT INTO user(name, user_id) VALUES (?, ?)'
|
41 | test.deepEqual q.params(), ['foo', null]
|
42 |
|
43 | test.done()
|
44 |
|
45 | 'with raw without params': (test) ->
|
46 | q = mohair.table('user').insert
|
47 | name: 'foo',
|
48 | user_id: 5
|
49 | created_at: mohair.raw('NOW()')
|
50 |
|
51 | test.equal q.sql(), 'INSERT INTO user(name, user_id, created_at) VALUES (?, ?, NOW())'
|
52 | test.deepEqual q.params(), ['foo', 5]
|
53 |
|
54 | test.done()
|
55 |
|
56 | 'with raw with params': (test) ->
|
57 | q = mohair.table('user').insert
|
58 | name: 'foo',
|
59 | user_id: 5
|
60 | created_at: mohair.raw('LOG(x, ?)', 3)
|
61 |
|
62 | test.equal q.sql(), 'INSERT INTO user(name, user_id, created_at) VALUES (?, ?, LOG(x, ?))'
|
63 | test.deepEqual q.params(), ['foo', 5, 3]
|
64 |
|
65 | test.done()
|
66 |
|
67 | 'insertMany':
|
68 |
|
69 | 'records with same key order': (test) ->
|
70 | q = mohair.table('user').insertMany [
|
71 | {name: 'foo', email: 'foo@example.com'}
|
72 | {name: 'bar', email: 'bar@example.com'}
|
73 | {name: 'baz', email: 'baz@example.com'}
|
74 | ]
|
75 |
|
76 | test.equal q.sql(),
|
77 | 'INSERT INTO user(name, email) VALUES (?, ?), (?, ?), (?, ?)'
|
78 | test.deepEqual q.params(),
|
79 | ['foo', 'foo@example.com', 'bar', 'bar@example.com', 'baz', 'baz@example.com']
|
80 |
|
81 | test.done()
|
82 |
|
83 | 'records with different key order': (test) ->
|
84 | q = mohair.table('user').insertMany [
|
85 | {name: 'foo', email: 'foo@example.com', age: 16}
|
86 | {email: 'bar@example.com', name: 'bar', age: 25}
|
87 | {age: 30, name: 'baz', email: 'baz@example.com'}
|
88 | ]
|
89 |
|
90 | test.equal q.sql(),
|
91 | 'INSERT INTO user(name, email, age) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)'
|
92 | test.deepEqual q.params(),
|
93 | ['foo', 'foo@example.com', 16, 'bar', 'bar@example.com', 25, 'baz', 'baz@example.com', 30]
|
94 |
|
95 | test.done()
|
96 |
|
97 | 'with raw without params': (test) ->
|
98 | q = mohair.table('user').insertMany [
|
99 | {
|
100 | name: 'foo',
|
101 | user_id: 5
|
102 | created_at: mohair.raw('BAR()')
|
103 | }
|
104 | {
|
105 | user_id: 6
|
106 | created_at: mohair.raw('BAZ()')
|
107 | name: 'bar',
|
108 | }
|
109 | {
|
110 | created_at: mohair.raw('FOO()')
|
111 | name: 'baz',
|
112 | user_id: 7
|
113 | }
|
114 | ]
|
115 |
|
116 | test.equal q.sql(), 'INSERT INTO user(name, user_id, created_at) VALUES (?, ?, BAR()), (?, ?, BAZ()), (?, ?, FOO())'
|
117 | test.deepEqual q.params(), ['foo', 5, 'bar', 6, 'baz', 7]
|
118 |
|
119 | test.done()
|
120 |
|
121 | 'with raw with params': (test) ->
|
122 | q = mohair.table('user').insertMany [
|
123 | {
|
124 | name: 'foo',
|
125 | user_id: 5
|
126 | created_at: mohair.raw('BAR(?, ?, ?)', 10, 11, 12)
|
127 | }
|
128 | {
|
129 | user_id: 6
|
130 | created_at: mohair.raw('BAZ(?)', 20)
|
131 | name: 'bar',
|
132 | }
|
133 | {
|
134 | created_at: mohair.raw('FOO(?, ?)', 30, 31)
|
135 | name: 'baz',
|
136 | user_id: 7
|
137 | }
|
138 | ]
|
139 |
|
140 | test.equal q.sql(), 'INSERT INTO user(name, user_id, created_at) VALUES (?, ?, BAR(?, ?, ?)), (?, ?, BAZ(?)), (?, ?, FOO(?, ?))'
|
141 | test.deepEqual q.params(), ['foo', 5, 10, 11, 12, 'bar', 6, 20, 'baz', 7, 30 ,31]
|
142 |
|
143 | test.done()
|
144 |
|
145 | 'delete':
|
146 |
|
147 | 'without criteria': (test) ->
|
148 | q = mohair.table('user').delete()
|
149 |
|
150 | test.equal q.sql(), 'DELETE FROM user'
|
151 | test.deepEqual q.params(), []
|
152 |
|
153 | test.done()
|
154 |
|
155 | 'with criteria': (test) ->
|
156 | q = mohair.table('user')
|
157 | .delete()
|
158 | .where('x BETWEEN ? AND ?', 50, 55)
|
159 | .where($or: {x: 10, y: 6})
|
160 |
|
161 | test.equal q.sql(), 'DELETE FROM user WHERE (x BETWEEN ? AND ?) AND ((x = ?) OR (y = ?))'
|
162 | test.deepEqual q.params(), [50, 55, 10, 6]
|
163 |
|
164 | test.done()
|
165 |
|
166 | 'update':
|
167 |
|
168 | 'without criteria': (test) ->
|
169 | q = mohair.table('user').update {name: 'bar', email: 'bar@example.com'}
|
170 |
|
171 | test.equal q.sql(), 'UPDATE user SET name = ?, email = ?'
|
172 | test.deepEqual q.params(), ['bar', 'bar@example.com']
|
173 |
|
174 | test.done()
|
175 |
|
176 | 'with criteria': (test) ->
|
177 | q = mohair.table('user')
|
178 | .where(id: 3, x: 5)
|
179 | .update {name: 'bar', email: 'bar@example.com'}
|
180 |
|
181 | test.equal q.sql(), 'UPDATE user SET name = ?, email = ? WHERE (id = ?) AND (x = ?)'
|
182 | test.deepEqual q.params(), ['bar', 'bar@example.com', 3, 5]
|
183 |
|
184 | test.done()
|
185 |
|
186 | 'with raw without params': (test) ->
|
187 | q = mohair.table('user')
|
188 | .where(id: 3, x: 5)
|
189 | .update {
|
190 | name: 'foo',
|
191 | user_id: 6
|
192 | modified_at: mohair.raw('NOW()')
|
193 | }
|
194 |
|
195 | test.equal q.sql(), 'UPDATE user SET name = ?, user_id = ?, modified_at = NOW() WHERE (id = ?) AND (x = ?)'
|
196 | test.deepEqual q.params(), ['foo', 6, 3, 5]
|
197 |
|
198 | test.done()
|
199 |
|
200 | 'with raw with params': (test) ->
|
201 | q = mohair.table('user')
|
202 | .where(id: 3, x: 5)
|
203 | .update {
|
204 | name: 'foo',
|
205 | user_id: mohair.raw('LOG(user_id, ?)', 4)
|
206 | modified_at: mohair.raw('NOW()')
|
207 | }
|
208 |
|
209 | test.equal q.sql(), 'UPDATE user SET name = ?, user_id = LOG(user_id, ?), modified_at = NOW() WHERE (id = ?) AND (x = ?)'
|
210 | test.deepEqual q.params(), ['foo', 4, 3, 5]
|
211 |
|
212 | test.done()
|
213 |
|
214 | 'with null values': (test) ->
|
215 | q = mohair.table('user').update {name: 'foo', user_id: null}
|
216 |
|
217 | test.equal q.sql(), 'UPDATE user SET name = ?, user_id = ?'
|
218 | test.deepEqual q.params(), ['foo', null]
|
219 |
|
220 | test.done()
|
221 |
|
222 | 'select':
|
223 |
|
224 | 'default is select *': (test) ->
|
225 | q = mohair.table('user')
|
226 |
|
227 | test.equal q.sql(), 'SELECT * FROM user'
|
228 | test.deepEqual q.params(), []
|
229 |
|
230 | test.done()
|
231 |
|
232 | 'all fields': (test) ->
|
233 | q = mohair.table('user').select()
|
234 |
|
235 | test.equal q.sql(), 'SELECT * FROM user'
|
236 | test.deepEqual q.params(), []
|
237 |
|
238 | test.done()
|
239 |
|
240 | 'specific fields': (test) ->
|
241 | q = mohair.table('user').select('name, timestamp AS created_at')
|
242 |
|
243 | test.equal q.sql(), 'SELECT name, timestamp AS created_at FROM user'
|
244 | test.deepEqual q.params(), []
|
245 |
|
246 | test.done()
|
247 |
|
248 | 'specific fields provided individually': (test) ->
|
249 | q = mohair.table('user').select('name', 'timestamp AS created_at')
|
250 |
|
251 | test.equal q.sql(), 'SELECT name, timestamp AS created_at FROM user'
|
252 | test.deepEqual q.params(), []
|
253 |
|
254 | test.done()
|
255 |
|
256 | 'with object': (test) ->
|
257 | q = mohair.table('user').select('name', {created_at: 'timestamp'})
|
258 |
|
259 | test.equal q.sql(), 'SELECT name, timestamp AS created_at FROM user'
|
260 | test.deepEqual q.params(), []
|
261 |
|
262 | test.done()
|
263 |
|
264 | 'with raw': (test) ->
|
265 | q = mohair.table('user').select('name', mohair.raw('count/?', 10))
|
266 |
|
267 | test.equal q.sql(), 'SELECT name, (count/?) FROM user'
|
268 | test.deepEqual q.params(), [10]
|
269 |
|
270 | test.done()
|
271 |
|
272 | 'with raw and object': (test) ->
|
273 | q = mohair.table('user').select('name', {number: mohair.raw('count/?', 10)})
|
274 |
|
275 | test.equal q.sql(), 'SELECT name, (count/?) AS number FROM user'
|
276 | test.deepEqual q.params(), [10]
|
277 |
|
278 | test.done()
|
279 |
|
280 | 'with subquery': (test) ->
|
281 | subquery = mohair
|
282 | .table('order')
|
283 | .where('user_id = user.id')
|
284 | .where('price > ?', 10)
|
285 | .select('count(1)')
|
286 | q = mohair
|
287 | .table('user')
|
288 | .select('name', {order_count: subquery})
|
289 |
|
290 | test.equal q.sql(), 'SELECT name, (SELECT count(1) FROM order WHERE (user_id = user.id) AND (price > ?)) AS order_count FROM user'
|
291 | test.deepEqual q.params(), [10]
|
292 |
|
293 | test.done()
|
294 |
|
295 | 'without table': (test) ->
|
296 | q = mohair.select('now()')
|
297 |
|
298 | test.equal q.sql(), 'SELECT now()'
|
299 | test.deepEqual q.params(), []
|
300 |
|
301 | test.done()
|
302 |
|
303 | 'with criteria': (test) ->
|
304 | q = mohair.table('user').where(id: 3).select()
|
305 |
|
306 | test.equal q.sql(), 'SELECT * FROM user WHERE id = ?'
|
307 | test.deepEqual q.params(), [3]
|
308 |
|
309 | test.done()
|
310 |
|
311 | 'criteria are anded together': (test) ->
|
312 | q = mohair.table('user').where(id: 3).where('name = ?', 'foo').select()
|
313 |
|
314 | test.equal q.sql(), 'SELECT * FROM user WHERE (id = ?) AND (name = ?)'
|
315 | test.deepEqual q.params(), [3, 'foo']
|
316 |
|
317 | test.done()
|
318 |
|
319 | 'order': (test) ->
|
320 | q = mohair.table('user').order('created DESC, name ASC')
|
321 |
|
322 | test.equal q.sql(), 'SELECT * FROM user ORDER BY created DESC, name ASC'
|
323 | test.deepEqual q.params(), []
|
324 |
|
325 | test.done()
|
326 |
|
327 | 'limit': (test) ->
|
328 | q = mohair.table('user').limit(10)
|
329 |
|
330 | test.equal q.sql(), 'SELECT * FROM user LIMIT ?'
|
331 | test.deepEqual q.params(), [10]
|
332 |
|
333 | test.done()
|
334 |
|
335 | 'offset': (test) ->
|
336 | q = mohair.table('user').offset(5)
|
337 |
|
338 | test.equal q.sql(), 'SELECT * FROM user OFFSET ?'
|
339 | test.deepEqual q.params(), [5]
|
340 |
|
341 | test.done()
|
342 |
|
343 | 'join': (test) ->
|
344 | q = mohair.table('user')
|
345 | .join('JOIN project ON user.id = project.user_id')
|
346 |
|
347 | test.equal q.sql(), 'SELECT * FROM user JOIN project ON user.id = project.user_id'
|
348 | test.deepEqual q.params(), []
|
349 |
|
350 | test.done()
|
351 |
|
352 | 'join with object criterion': (test) ->
|
353 | q = mohair.table('user')
|
354 | .join('JOIN project ON user.id = project.user_id', {'project.foo': {$null: true}, 'project.bar': 10})
|
355 |
|
356 | test.equal q.sql(),
|
357 | 'SELECT * FROM user JOIN project ON user.id = project.user_id AND ((project.foo IS NULL) AND (project.bar = ?))'
|
358 | test.deepEqual q.params(), [10]
|
359 |
|
360 | test.done()
|
361 |
|
362 | 'join with sql criterion': (test) ->
|
363 | q = mohair.table('user')
|
364 | .join('JOIN project ON user.id = project.user_id', 'project.foo = ?', 4)
|
365 |
|
366 | test.equal q.sql(),
|
367 | 'SELECT * FROM user JOIN project ON user.id = project.user_id AND (project.foo = ?)'
|
368 | test.deepEqual q.params(), [4]
|
369 |
|
370 | test.done()
|
371 |
|
372 | 'multiple joins': (test) ->
|
373 | q = mohair.table('user')
|
374 | .join('OUTER JOIN project ON user.id = project.user_id', 'project.foo = ?', 4)
|
375 | .join('INNER JOIN task ON project.id = task.project_id', {'task.bar': 10})
|
376 |
|
377 | test.equal q.sql(),
|
378 | 'SELECT * FROM user OUTER JOIN project ON user.id = project.user_id AND (project.foo = ?) INNER JOIN task ON project.id = task.project_id AND (task.bar = ?)'
|
379 | test.deepEqual q.params(), [4, 10]
|
380 |
|
381 | test.done()
|
382 |
|
383 | 'having': (test) ->
|
384 | q = mohair.table('user')
|
385 | .select('user.id, (select project.id from project) projectId')
|
386 | .having('projectId = user.id')
|
387 |
|
388 | test.equal q.sql(),
|
389 | 'SELECT user.id, (select project.id from project) projectId FROM user HAVING projectId = user.id'
|
390 |
|
391 | test.done()
|
392 |
|
393 | 'group': (test) ->
|
394 | q = mohair.table('user')
|
395 | .select('user.*, count(project.id) AS project_count')
|
396 | .join('JOIN project ON user.id = project.user_id')
|
397 | .group('user.id')
|
398 |
|
399 | test.equal q.sql(), 'SELECT user.*, count(project.id) AS project_count FROM user JOIN project ON user.id = project.user_id GROUP BY user.id'
|
400 | test.deepEqual q.params(), []
|
401 |
|
402 | test.done()
|
403 |
|
404 | 'everything together': (test) ->
|
405 | q = mohair.table('user')
|
406 | .select('user.*, count(project.id) AS project_count')
|
407 | .where(id: 3)
|
408 | .where('name = ?', 'foo')
|
409 | .join('JOIN project ON user.id = project.user_id')
|
410 | .group('user.id')
|
411 | .order('created DESC, name ASC')
|
412 | .limit(10)
|
413 | .offset(20)
|
414 |
|
415 | test.equal q.sql(), 'SELECT user.*, count(project.id) AS project_count FROM user JOIN project ON user.id = project.user_id WHERE (id = ?) AND (name = ?) GROUP BY user.id ORDER BY created DESC, name ASC LIMIT ? OFFSET ?'
|
416 | test.deepEqual q.params(), [3, 'foo', 10, 20]
|
417 |
|
418 | test.done()
|
419 |
|
420 | 'actions overwrite previous actions': (test) ->
|
421 | chain = mohair.table('user')
|
422 | .where(id: 3)
|
423 | .select('name')
|
424 |
|
425 | query = chain.insert(name: 'foo').table('project')
|
426 |
|
427 | test.equal chain.sql(), 'SELECT name FROM user WHERE id = ?'
|
428 | test.deepEqual chain.params(), [3]
|
429 |
|
430 | test.equal query.sql(), 'INSERT INTO project(name) VALUES (?)'
|
431 | test.deepEqual query.params(), ['foo']
|
432 |
|
433 | test.done()
|
434 |
|
435 | 'immutability': (test) ->
|
436 | visible = mohair.table('project').where(is_visible: true)
|
437 |
|
438 | updateQuery = visible.update({name: 'i am visible'}).where(id: 3)
|
439 | test.equal updateQuery.sql(),
|
440 | 'UPDATE project SET name = ? WHERE (is_visible = ?) AND (id = ?)'
|
441 | test.deepEqual updateQuery.params(), ['i am visible', true, 3]
|
442 |
|
443 | deleteQuery = visible.where({name: 'foo'}).delete()
|
444 |
|
445 | test.equal deleteQuery.sql(),
|
446 | 'DELETE FROM project WHERE (is_visible = ?) AND (name = ?)'
|
447 | test.deepEqual deleteQuery.params(), [true, 'foo']
|
448 |
|
449 | test.done()
|
450 |
|
451 | 'escape':
|
452 | 'select': (test) ->
|
453 | query = mohair
|
454 | .escape((string) -> "\"#{string}\"")
|
455 | .table('project')
|
456 | .where(is_visible: true)
|
457 |
|
458 | test.equal query.sql(),
|
459 | 'SELECT * FROM "project" WHERE is_visible = ?'
|
460 | test.deepEqual query.params(), [true]
|
461 |
|
462 | test.done()
|
463 |
|
464 | 'insert': (test) ->
|
465 | query = mohair
|
466 | .escape((string) -> "\"#{string}\"")
|
467 | .table('project')
|
468 | .insert {first_key: 'first_value', second_key: 'second_value'}
|
469 |
|
470 | test.equal query.sql(),
|
471 | 'INSERT INTO "project"("first_key", "second_key") VALUES (?, ?)'
|
472 | test.deepEqual query.params(), ['first_value', 'second_value']
|
473 |
|
474 | test.done()
|
475 |
|
476 | 'update': (test) ->
|
477 | query = mohair
|
478 | .escape((string) -> "\"#{string}\"")
|
479 | .table('project')
|
480 | .update {first_key: 'first_value', second_key: 'second_value'}
|
481 |
|
482 | test.equal query.sql(),
|
483 | 'UPDATE "project" SET "first_key" = ?, "second_key" = ?'
|
484 | test.deepEqual query.params(), ['first_value', 'second_value']
|
485 |
|
486 | test.done()
|
487 |
|
488 | 'common table expressions':
|
489 | example: (test) ->
|
490 | regionalSales = mohair
|
491 | .select('region, SUM(amount) AS total_sales')
|
492 | .table('orders')
|
493 | .group('region')
|
494 |
|
495 | topRegions = mohair
|
496 | .select('region')
|
497 | .table('regional_sales')
|
498 | .where('total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)')
|
499 |
|
500 | query = mohair
|
501 | .with(
|
502 | regional_sales: regionalSales
|
503 | top_regions: topRegions
|
504 | )
|
505 | .select("region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales")
|
506 | .table('orders')
|
507 | .where('region IN (SELECT region FROM top_regions)')
|
508 | .group('region, product')
|
509 |
|
510 | expected = "WITH regional_sales AS (SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS (SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product"
|
511 |
|
512 | test.equals query.sql(), expected
|
513 | test.deepEqual query.params(), []
|
514 |
|
515 | test.done()
|
516 |
|
517 | params: (test) ->
|
518 | regionalSales =
|
519 | sql: -> 'regional_sales'
|
520 | params: -> [1, 2, 3]
|
521 |
|
522 | topRegions =
|
523 | sql: -> 'top_regions'
|
524 | params: -> [4, 5, 6]
|
525 |
|
526 | query = mohair
|
527 | .with(
|
528 | regional_sales: regionalSales
|
529 | top_regions: topRegions
|
530 | )
|
531 | .table('orders')
|
532 | .where('type = ?', 'test')
|
533 |
|
534 | expected = "WITH regional_sales AS (regional_sales), top_regions AS (top_regions) SELECT * FROM orders WHERE type = ?"
|
535 |
|
536 | test.equals query.sql(), expected
|
537 | test.deepEqual query.params(), [1, 2, 3, 4, 5, 6, 'test']
|
538 |
|
539 | test.done()
|