UNPKG

18.7 kBtext/coffeescriptView Raw
1mohair = require '../src/mohair'
2
3module.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()