bernsteinbear.com
this repo has no description
1import unittest
2from db import Database, Table, query
3
4__import__("sys").modules["unittest.util"]._MAX_LENGTH = 999999999
5
6
7FRIENDS = Table(
8 "friends",
9 [
10 {"id": 1, "city": "Denver", "state": "Colorado"},
11 {"id": 2, "city": "Colorado Springs", "state": "Colorado"},
12 {"id": 3, "city": "South Park", "state": "Colorado"},
13 {"id": 4, "city": "Corpus Christi", "state": "Texas"},
14 {"id": 5, "city": "Houston", "state": "Texas"},
15 {"id": 6, "city": "Denver", "state": "Colorado"},
16 {"id": 7, "city": "Corpus Christi", "state": "Texas"},
17 {"id": 8, "city": "Houston", "state": "Elsewhere"},
18 ],
19)
20
21
22class DatabaseTests(unittest.TestCase):
23 def test_create_table(self):
24 db = Database()
25 self.assertNotIn("foo", db.tables)
26 db.CREATE_TABLE("foo")
27 self.assertIn("foo", db.tables)
28
29 def test_create_table_creates_empty_table(self):
30 db = Database()
31 table = db.CREATE_TABLE("foo")
32 self.assertEqual(table.rows, ())
33
34 def test_create_table_sets_colnames(self):
35 db = Database()
36 table = db.CREATE_TABLE("foo", ["a", "b"])
37 self.assertEqual(table.colnames(), ("a", "b"))
38
39 def test_insert_into_adds_row(self):
40 db = Database()
41 table = db.CREATE_TABLE("stories")
42 row = {
43 "id": 1,
44 "name": "The Elliptical Machine that ate Manhattan",
45 "author_id": 1,
46 }
47 db.INSERT_INTO("stories", [row])
48 self.assertIn(row, table.rows)
49
50 def test_insert_into_adds_rows(self):
51 db = Database()
52 table = db.CREATE_TABLE("stories")
53 rows = [
54 {
55 "id": 1,
56 "name": "The Elliptical Machine that ate Manhattan",
57 "author_id": 1,
58 },
59 {"id": 2, "name": "Queen of the Bats", "author_id": 2},
60 ]
61 db.INSERT_INTO("stories", rows)
62 self.assertIn(rows[0], table.rows)
63 self.assertIn(rows[1], table.rows)
64
65 def test_insert_into_appends_row(self):
66 db = Database()
67 table = db.CREATE_TABLE("stories")
68 rows = [
69 {
70 "id": 1,
71 "name": "The Elliptical Machine that ate Manhattan",
72 "author_id": 1,
73 },
74 {"id": 2, "name": "Queen of the Bats", "author_id": 2},
75 ]
76 db.INSERT_INTO("stories", rows)
77 new_row = {"id": 4, "name": "Something", "author_id": 5}
78 db.INSERT_INTO("stories", [new_row])
79 self.assertIn(rows[0], table.rows)
80 self.assertIn(rows[1], table.rows)
81 self.assertIn(new_row, table.rows)
82
83 def test_drop_table_removes_table(self):
84 db = Database()
85 db.CREATE_TABLE("foo")
86 self.assertIn("foo", db.tables)
87 db.DROP_TABLE("foo")
88 self.assertNotIn("foo", db.tables)
89
90 def test_from_with_no_tables_raises(self):
91 db = Database()
92 with self.assertRaises(TypeError):
93 db.FROM()
94
95 def test_from_with_one_table_returns_table(self):
96 db = Database()
97 table = db.CREATE_TABLE("foo")
98 result = db.FROM("foo")
99 self.assertEqual(result, table)
100
101 def test_from_with_two_tables_returns_cartesian_product(self):
102 db = Database()
103 db.CREATE_TABLE("foo")
104 db.INSERT_INTO("foo", [{"a": 1}, {"a": 2}])
105 db.CREATE_TABLE("bar")
106 db.INSERT_INTO("bar", [{"b": 1}, {"b": 2}])
107 self.assertEqual(
108 db.FROM("foo", "bar").rows,
109 (
110 {"foo.a": 1, "bar.b": 1},
111 {"foo.a": 1, "bar.b": 2},
112 {"foo.a": 2, "bar.b": 1},
113 {"foo.a": 2, "bar.b": 2},
114 ),
115 )
116
117 def test_from_with_three_tables_returns_cartesian_product(self):
118 db = Database()
119 db.CREATE_TABLE("foo")
120 db.INSERT_INTO("foo", [{"a": 1}, {"a": 2}])
121 db.CREATE_TABLE("bar")
122 db.INSERT_INTO("bar", [{"b": 1}, {"b": 2}])
123 db.CREATE_TABLE("baz")
124 db.INSERT_INTO("baz", [{"c": 1}, {"c": 2}])
125 self.assertEqual(
126 db.FROM("foo", "bar", "baz").rows,
127 (
128 {"bar.b": 1, "baz.c": 1, "foo.a": 1},
129 {"bar.b": 1, "baz.c": 2, "foo.a": 1},
130 {"bar.b": 2, "baz.c": 1, "foo.a": 1},
131 {"bar.b": 2, "baz.c": 2, "foo.a": 1},
132 {"bar.b": 1, "baz.c": 1, "foo.a": 2},
133 {"bar.b": 1, "baz.c": 2, "foo.a": 2},
134 {"bar.b": 2, "baz.c": 1, "foo.a": 2},
135 {"bar.b": 2, "baz.c": 2, "foo.a": 2},
136 ),
137 )
138
139 def test_select_returns_table_with_no_columns(self):
140 db = Database()
141 table = Table("foo", [{"a": 1, "b": 2, "c": 3}, {"a": 4, "b": 5, "c": 6}])
142 result = db.SELECT(table, [])
143 self.assertEqual(result.rows, ({}, {}))
144
145 def test_select_returns_table_with_given_columns(self):
146 db = Database()
147 table = Table("foo", [{"a": 1, "b": 2, "c": 3}, {"a": 4, "b": 5, "c": 6}])
148 result = db.SELECT(table, ["a", "c"])
149 self.assertEqual(result.rows, ({"a": 1, "c": 3}, {"a": 4, "c": 6}))
150
151 def test_select_returns_table_with_aliases(self):
152 db = Database()
153 table = Table("foo", [{"a": 1, "b": 2, "c": 3}, {"a": 4, "b": 5, "c": 6}])
154 result = db.SELECT(table, ["a", "c"], {"a": "x"}) # a as x
155 self.assertEqual(result.rows, ({"x": 1, "c": 3}, {"x": 4, "c": 6}))
156
157 def test_where_returns_matching_rows(self):
158 db = Database()
159 table = Table("foo", [{"a": 1}, {"a": 2}, {"a": 3}, {"a": 4}])
160 result = db.WHERE(table, lambda row: row["a"] % 2 == 0)
161 self.assertEqual(result.rows, ({"a": 2}, {"a": 4}))
162
163 def test_update_returns_updated_table_with_all_rows_modified(self):
164 db = Database()
165 rows = (
166 {"id": 1, "name": "Josh", "department_id": 1, "salary": 50000},
167 {"id": 2, "name": "Ruth", "department_id": 2, "salary": 60000},
168 {"id": 3, "name": "Greg", "department_id": 5, "salary": 70000},
169 {"id": 4, "name": "Pat", "department_id": 1, "salary": 80000},
170 )
171 table = Table("employee", rows)
172 result = db.UPDATE(table, {"salary": 10})
173 self.assertEqual(result.name, table.name)
174 self.assertEqual(
175 result.rows,
176 (
177 {"id": 1, "name": "Josh", "department_id": 1, "salary": 10},
178 {"id": 2, "name": "Ruth", "department_id": 2, "salary": 10},
179 {"id": 3, "name": "Greg", "department_id": 5, "salary": 10},
180 {"id": 4, "name": "Pat", "department_id": 1, "salary": 10},
181 ),
182 )
183 self.assertEqual(table.rows[0]["salary"], 50000)
184
185 def test_update_returns_updated_table_with_pred(self):
186 db = Database()
187 rows = (
188 {"id": 1, "name": "Josh", "department_id": 1, "salary": 50000},
189 {"id": 2, "name": "Ruth", "department_id": 2, "salary": 60000},
190 {"id": 3, "name": "Greg", "department_id": 5, "salary": 70000},
191 {"id": 4, "name": "Pat", "department_id": 1, "salary": 80000},
192 )
193 table = Table("employee", rows)
194 result = db.UPDATE(
195 table, {"name": "JOSH", "salary": 10}, lambda row: row["name"] == "Josh"
196 )
197 self.assertEqual(result.name, table.name)
198 self.assertEqual(
199 result.rows,
200 (
201 {"id": 1, "name": "JOSH", "department_id": 1, "salary": 10},
202 {"id": 2, "name": "Ruth", "department_id": 2, "salary": 60000},
203 {"id": 3, "name": "Greg", "department_id": 5, "salary": 70000},
204 {"id": 4, "name": "Pat", "department_id": 1, "salary": 80000},
205 ),
206 )
207 self.assertEqual(table.rows[0]["salary"], 50000)
208
209 def test_update_does_not_modify_table(self):
210 db = Database()
211 rows = ({"id": 1, "name": "Josh", "department_id": 1, "salary": 50000},)
212 table = Table("employee", rows)
213 db.UPDATE(
214 table, {"name": "JOSH", "salary": 10}, lambda row: row["name"] == "Josh"
215 )
216 self.assertEqual(table.rows[0]["salary"], 50000)
217
218 def test_cross_join_returns_cartesian_product(self):
219 db = Database()
220 foo = Table("foo", [{"a": 1}, {"a": 2}])
221 bar = Table("bar", [{"b": 1}, {"b": 2}])
222 self.assertEqual(
223 db.CROSS_JOIN(foo, bar).rows,
224 (
225 {"foo.a": 1, "bar.b": 1},
226 {"foo.a": 1, "bar.b": 2},
227 {"foo.a": 2, "bar.b": 1},
228 {"foo.a": 2, "bar.b": 2},
229 ),
230 )
231
232 def test_inner_join_returns_matching_cross_product(self):
233 user = Table("user", [{"id": 1, "name": "Alice"}])
234 post = Table(
235 "post",
236 [
237 {"id": 1, "user_id": 1, "title": "Hello"},
238 {"id": 2, "user_id": 2, "title": "Hello world"},
239 {"id": 3, "user_id": 1, "title": "Goodbye world"},
240 {"id": 2, "user_id": 3, "title": "Hello world again"},
241 ],
242 )
243 db = Database()
244 result = db.JOIN(user, post, lambda row: row["user.id"] == row["post.user_id"])
245 self.assertEqual(
246 result.rows,
247 (
248 {
249 "post.id": 1,
250 "post.title": "Hello",
251 "post.user_id": 1,
252 "user.id": 1,
253 "user.name": "Alice",
254 },
255 {
256 "post.id": 3,
257 "post.title": "Goodbye world",
258 "post.user_id": 1,
259 "user.id": 1,
260 "user.name": "Alice",
261 },
262 ),
263 )
264
265 def test_left_join_returns_matching_rows_on_right(self):
266 employee = Table(
267 "employee",
268 [
269 {"id": 1, "name": "Alice", "department_id": 1},
270 {"id": 2, "name": "Bob", "department_id": 2},
271 ],
272 )
273 department = Table(
274 "department",
275 [
276 {"id": 1, "title": "Accounting"},
277 {"id": 2, "title": "Engineering"},
278 ],
279 )
280 db = Database()
281 result = db.LEFT_JOIN(
282 employee,
283 department,
284 lambda row: row["employee.department_id"] == row["department.id"],
285 )
286 self.assertEqual(
287 result.rows,
288 (
289 {
290 "department.id": 1,
291 "department.title": "Accounting",
292 "employee.department_id": 1,
293 "employee.id": 1,
294 "employee.name": "Alice",
295 },
296 {
297 "department.id": 2,
298 "department.title": "Engineering",
299 "employee.department_id": 2,
300 "employee.id": 2,
301 "employee.name": "Bob",
302 },
303 ),
304 )
305
306 def test_left_join_fills_in_null_for_non_matching_rows(self):
307 employee = Table(
308 "employee",
309 [
310 {"id": 1, "name": "Alice", "department_id": 100},
311 {"id": 2, "name": "Bob", "department_id": 2},
312 ],
313 )
314 department = Table(
315 "department",
316 [
317 {"id": 1, "title": "Accounting"},
318 {"id": 2, "title": "Engineering"},
319 ],
320 )
321 db = Database()
322 result = db.LEFT_JOIN(
323 employee,
324 department,
325 lambda row: row["employee.department_id"] == row["department.id"],
326 )
327 self.assertEqual(
328 result.rows,
329 (
330 {
331 "department.id": None,
332 "department.title": None,
333 "employee.department_id": 100,
334 "employee.id": 1,
335 "employee.name": "Alice",
336 },
337 {
338 "department.id": 2,
339 "department.title": "Engineering",
340 "employee.department_id": 2,
341 "employee.id": 2,
342 "employee.name": "Bob",
343 },
344 ),
345 )
346
347 def test_right_join_returns_matching_rows_on_left(self):
348 employee = Table(
349 "employee",
350 [
351 {"id": 1, "name": "Alice", "department_id": 1},
352 {"id": 2, "name": "Bob", "department_id": 2},
353 ],
354 )
355 department = Table(
356 "department",
357 [
358 {"id": 1, "title": "Accounting"},
359 {"id": 2, "title": "Engineering"},
360 ],
361 )
362 db = Database()
363 result = db.RIGHT_JOIN(
364 employee,
365 department,
366 lambda row: row["employee.department_id"] == row["department.id"],
367 )
368 self.assertEqual(
369 result.rows,
370 (
371 {
372 "department.id": 1,
373 "department.title": "Accounting",
374 "employee.department_id": 1,
375 "employee.id": 1,
376 "employee.name": "Alice",
377 },
378 {
379 "department.id": 2,
380 "department.title": "Engineering",
381 "employee.department_id": 2,
382 "employee.id": 2,
383 "employee.name": "Bob",
384 },
385 ),
386 )
387
388 def test_right_join_fills_in_null_for_non_matching_rows(self):
389 employee = Table(
390 "employee",
391 [
392 {"id": 1, "name": "Alice", "department_id": 100},
393 {"id": 2, "name": "Bob", "department_id": 2},
394 {"id": 3, "name": "Charles", "department_id": 2},
395 ],
396 )
397 department = Table(
398 "department",
399 [
400 {"id": 1, "title": "Accounting"},
401 {"id": 2, "title": "Engineering"},
402 ],
403 )
404 db = Database()
405 result = db.RIGHT_JOIN(
406 employee,
407 department,
408 lambda row: row["employee.department_id"] == row["department.id"],
409 )
410 self.assertEqual(
411 result.rows,
412 (
413 {
414 "department.id": 1,
415 "department.title": "Accounting",
416 "employee.department_id": None,
417 "employee.id": None,
418 "employee.name": None,
419 },
420 {
421 "department.id": 2,
422 "department.title": "Engineering",
423 "employee.department_id": 2,
424 "employee.id": 2,
425 "employee.name": "Bob",
426 },
427 {
428 "department.id": 2,
429 "department.title": "Engineering",
430 "employee.department_id": 2,
431 "employee.id": 3,
432 "employee.name": "Charles",
433 },
434 ),
435 )
436
437 def tests_limit_returns_empty_table(self):
438 db = Database()
439 table = Table("foo", [])
440 result = db.LIMIT(table, 1)
441 self.assertEqual(result.rows, ())
442
443 def tests_limit_returns_no_more_than_limit(self):
444 db = Database()
445 table = Table("foo", [{"a": 1}, {"a": 2}, {"a": 3}])
446 result = db.LIMIT(table, 2)
447 self.assertEqual(result.rows, ({"a": 1}, {"a": 2}))
448
449 def test_order_by_sorts_by_single_column(self):
450 db = Database()
451 table = Table("foo", [{"a": 3, "b": 2}, {"a": 1, "b": 2}, {"a": 2, "b": 2}])
452 result = db.ORDER_BY(table, lambda row: row["a"])
453 self.assertEqual(
454 result.rows, ({"a": 1, "b": 2}, {"a": 2, "b": 2}, {"a": 3, "b": 2})
455 )
456
457 def test_having_returns_matching_rows(self):
458 db = Database()
459 table = Table("foo", [{"a": 1}, {"a": 2}, {"a": 3}, {"a": 4}])
460 result = db.HAVING(table, lambda row: row["a"] % 2 == 0)
461 self.assertEqual(result.rows, ({"a": 2}, {"a": 4}))
462
463 def test_offset_starts_at_given_row(self):
464 db = Database()
465 table = Table("foo", [{"a": 1}, {"a": 2}, {"a": 3}, {"a": 4}])
466 result = db.OFFSET(table, 2)
467 self.assertEqual(result.rows, ({"a": 3}, {"a": 4}))
468
469 def test_distinct_unique_on_one_column_name(self):
470 db = Database()
471 result = db.DISTINCT(FRIENDS, ["city"])
472 self.assertEqual(
473 result.rows,
474 (
475 {"city": "Denver"},
476 {"city": "Colorado Springs"},
477 {"city": "South Park"},
478 {"city": "Corpus Christi"},
479 {"city": "Houston"},
480 ),
481 )
482
483 def test_distinct_unique_on_two_column_names(self):
484 db = Database()
485 result = db.DISTINCT(FRIENDS, ["city", "state"])
486 self.assertEqual(
487 result.rows,
488 (
489 {"city": "Denver", "state": "Colorado"},
490 {"city": "Colorado Springs", "state": "Colorado"},
491 {"city": "South Park", "state": "Colorado"},
492 {"city": "Corpus Christi", "state": "Texas"},
493 {"city": "Houston", "state": "Texas"},
494 {"city": "Houston", "state": "Elsewhere"},
495 ),
496 )
497
498 def test_group_by_returns_group_rows(self):
499 db = Database()
500 result = db.GROUP_BY(FRIENDS, ["state"])
501 self.assertEqual(
502 result.rows,
503 (
504 {
505 "_groupRows": [
506 {"id": 1, "city": "Denver", "state": "Colorado"},
507 {"id": 2, "city": "Colorado Springs", "state": "Colorado"},
508 {"id": 3, "city": "South Park", "state": "Colorado"},
509 {"id": 6, "city": "Denver", "state": "Colorado"},
510 ],
511 "state": "Colorado",
512 },
513 {
514 "_groupRows": [
515 {"id": 4, "city": "Corpus Christi", "state": "Texas"},
516 {"id": 5, "city": "Houston", "state": "Texas"},
517 {"id": 7, "city": "Corpus Christi", "state": "Texas"},
518 ],
519 "state": "Texas",
520 },
521 {
522 "_groupRows": [{"id": 8, "city": "Houston", "state": "Elsewhere"}],
523 "state": "Elsewhere",
524 },
525 ),
526 )
527
528 def test_group_by_multiple_columns(self):
529 db = Database()
530 result = db.GROUP_BY(FRIENDS, ["city", "state"])
531 self.assertEqual(
532 result.rows,
533 (
534 {
535 "_groupRows": [
536 {"id": 1, "city": "Denver", "state": "Colorado"},
537 {"id": 6, "city": "Denver", "state": "Colorado"},
538 ],
539 "city": "Denver",
540 "state": "Colorado",
541 },
542 {
543 "_groupRows": [
544 {"id": 2, "city": "Colorado Springs", "state": "Colorado"}
545 ],
546 "city": "Colorado Springs",
547 "state": "Colorado",
548 },
549 {
550 "_groupRows": [
551 {"id": 3, "city": "South Park", "state": "Colorado"}
552 ],
553 "city": "South Park",
554 "state": "Colorado",
555 },
556 {
557 "_groupRows": [
558 {"id": 4, "city": "Corpus Christi", "state": "Texas"},
559 {"id": 7, "city": "Corpus Christi", "state": "Texas"},
560 ],
561 "city": "Corpus Christi",
562 "state": "Texas",
563 },
564 {
565 "_groupRows": [{"id": 5, "city": "Houston", "state": "Texas"}],
566 "city": "Houston",
567 "state": "Texas",
568 },
569 {
570 "_groupRows": [{"id": 8, "city": "Houston", "state": "Elsewhere"}],
571 "city": "Houston",
572 "state": "Elsewhere",
573 },
574 ),
575 )
576
577 def test_count_returns_count(self):
578 friends = Table(
579 "friends",
580 [
581 {"id": 1, "city": "Denver", "state": "Colorado"},
582 {"id": 2, "city": "Houston", "state": "Texas"},
583 {"id": 3, "city": "Colorado Springs", "state": "Colorado"},
584 ],
585 )
586 db = Database()
587 result = db.COUNT(friends, "city")
588 self.assertEqual(result.name, "friends")
589 self.assertEqual(result.rows, ({"COUNT(city)": 3},))
590
591 def test_count_group_by_returns_count(self):
592 friends = Table(
593 "friends",
594 [
595 {"id": 1, "city": "Denver", "state": "Colorado"},
596 {"id": 2, "city": "Houston", "state": "Texas"},
597 {"id": 3, "city": "Colorado Springs", "state": "Colorado"},
598 ],
599 )
600 db = Database()
601 result = db.GROUP_BY(friends, ["state"])
602 result = db.COUNT(result, "city")
603 self.assertEqual(result.name, "friends")
604 self.assertEqual(
605 result.rows,
606 (
607 {"COUNT(city)": 2, "state": "Colorado"},
608 {"COUNT(city)": 1, "state": "Texas"},
609 ),
610 )
611
612 def test_max_group_by_returns_max(self):
613 scores = Table(
614 "scores",
615 [
616 {"id": 1, "name": "Alice", "test": 0, "score": 80},
617 {"id": 4, "name": "Bob", "test": 0, "score": 89},
618 {"id": 7, "name": "Charles", "test": 0, "score": 34},
619 {"id": 2, "name": "Alice", "test": 1, "score": 85},
620 {"id": 5, "name": "Bob", "test": 1, "score": 85},
621 {"id": 8, "name": "Charles", "test": 1, "score": 33},
622 {"id": 3, "name": "Alice", "test": 2, "score": 79},
623 {"id": 9, "name": "Charles", "test": 2, "score": 32},
624 {"id": 6, "name": "Bob", "test": 2, "score": 87},
625 ],
626 )
627 db = Database()
628 result = db.GROUP_BY(scores, ["name"])
629 result = db.MAX(result, "score")
630 self.assertEqual(result.name, "scores")
631 self.assertEqual(
632 result.rows,
633 (
634 {"MAX(score)": 85, "name": "Alice"},
635 {"MAX(score)": 89, "name": "Bob"},
636 {"MAX(score)": 34, "name": "Charles"},
637 ),
638 )
639
640 def test_sum_group_by_returns_sum(self):
641 scores = Table(
642 "scores",
643 [
644 {"id": 1, "name": "Alice", "test": 0, "score": 80},
645 {"id": 4, "name": "Bob", "test": 0, "score": 89},
646 {"id": 7, "name": "Charles", "test": 0, "score": 34},
647 {"id": 2, "name": "Alice", "test": 1, "score": 85},
648 {"id": 5, "name": "Bob", "test": 1, "score": 85},
649 {"id": 8, "name": "Charles", "test": 1, "score": 33},
650 {"id": 3, "name": "Alice", "test": 2, "score": 79},
651 {"id": 9, "name": "Charles", "test": 2, "score": 32},
652 {"id": 6, "name": "Bob", "test": 2, "score": 87},
653 ],
654 )
655 db = Database()
656 result = db.GROUP_BY(scores, ["name"])
657 result = db.SUM(result, "score")
658 self.assertEqual(result.name, "scores")
659 self.assertEqual(
660 result.rows,
661 (
662 {"SUM(score)": 244, "name": "Alice"},
663 {"SUM(score)": 261, "name": "Bob"},
664 {"SUM(score)": 99, "name": "Charles"},
665 ),
666 )
667
668
669class EndToEndTests(unittest.TestCase):
670 def test_query(self):
671 db = Database()
672 friend = db.CREATE_TABLE("friend")
673 db.INSERT_INTO(
674 "friend",
675 [
676 {"id": 1, "name": "Alice", "city": "Denver", "state": "Colorado"},
677 {
678 "id": 2,
679 "name": "Bob",
680 "city": "Colorado Springs",
681 "state": "Colorado",
682 },
683 {"id": 3, "name": "Charles", "city": "South Park", "state": "Colorado"},
684 {"id": 4, "name": "Dave", "city": "Fort Collins", "state": "Colorado"},
685 {"id": 5, "name": "Edna", "city": "Houston", "state": "Texas"},
686 {"id": 6, "name": "Francis", "city": "Denver", "state": "Colorado"},
687 {"id": 7, "name": "Gloria", "city": "Corpus Christi", "state": "Texas"},
688 {"id": 9, "name": "Homer", "city": "Denver", "state": "Colorado"},
689 ],
690 )
691 employee = db.CREATE_TABLE("employee")
692 db.INSERT_INTO(
693 "employee",
694 [
695 {"id": 1, "name": "Alice", "department_id": 100, "salary": 100},
696 {"id": 2, "name": "Bob", "department_id": 2, "salary": 150},
697 {"id": 3, "name": "Charles", "department_id": 2, "salary": 200},
698 {"id": 4, "name": "Dave", "department_id": 1, "salary": 180},
699 ],
700 )
701 department = db.CREATE_TABLE("department")
702 db.INSERT_INTO(
703 "department",
704 [
705 {"id": 1, "title": "Accounting"},
706 {"id": 2, "title": "Engineering"},
707 ],
708 )
709 result = query(
710 db,
711 select=["employee.name", "department.title", "friend.state"],
712 select_as={
713 "employee.name": "Name",
714 "department.title": "Dept",
715 "friend.state": "From",
716 },
717 from_=["employee"],
718 join=[
719 [
720 "department",
721 lambda row: row["employee.department_id"] == row["department.id"],
722 ],
723 ["friend", lambda row: row["friend.name"] == row["employee.name"]],
724 ],
725 where=[
726 lambda row: row["employee.salary"] > 150,
727 lambda row: row["friend.state"] == "Colorado",
728 ],
729 order_by=lambda row: row["Dept"],
730 )
731 self.assertEqual(
732 result.rows,
733 (
734 {"Name": "Dave", "Dept": "Accounting", "From": "Colorado"},
735 {"Name": "Charles", "Dept": "Engineering", "From": "Colorado"},
736 ),
737 )
738
739
740if __name__ == "__main__":
741 unittest.main()