Fix creating of test db

[hubacji1/coffee-getter.git] / ut / test_db.py

diff --git a/ut/test_db.py b/ut/test_db.py
index 4320f56ba70a2918a656dc0001ec7b1f50f3de3a..62fe493e618b528b896b1b082954cc77dc271fa9 100644 (file)
--- a/ut/test_db.py
+++ b/ut/test_db.py
@@ -12,9 +12,9 @@ class Queries(TestCase):
 
         SELECT count(*), flavor
         FROM coffees
-        WHERE time BETWEEN
+        WHERE (coffees.time BETWEEN
             datetime('now', 'localtime', '-7 days')
-            AND datetime('now', 'localtime')
+            AND datetime('now', 'localtime'))
         GROUP BY flavor
 
         """
@@ -23,37 +23,37 @@ class Queries(TestCase):
         assert Q("get_drinkers_of", "FOO") == """
 
         SELECT count(*), users.name FROM coffees
-        LEFT JOIN identifiers on coffees.id = identifiers.userid
-        LEFT JOIN users on identifiers.userid = users.id
+        INNER JOIN identifiers on coffees.id = identifiers.id
+        INNER JOIN users on identifiers.userid = users.id
         WHERE flavor = 'FOO'
-        AND coffees.time BETWEEN
+        AND (coffees.time BETWEEN
             datetime('now', 'localtime', '-7 days')
-            AND datetime('now', 'localtime')
-        GROUP BY identifiers.userid
+            AND datetime('now', 'localtime'))
+        GROUP BY users.id
 
         """
         assert Q("get_drinkers_of", "BAR", ["FROM", "TO"]) == """
 
         SELECT count(*), users.name FROM coffees
-        LEFT JOIN identifiers on coffees.id = identifiers.userid
-        LEFT JOIN users on identifiers.userid = users.id
+        INNER JOIN identifiers on coffees.id = identifiers.id
+        INNER JOIN users on identifiers.userid = users.id
         WHERE flavor = 'BAR'
-        AND coffees.time BETWEEN
+        AND (coffees.time BETWEEN
             datetime('FROM', 'localtime')
-            AND datetime('TO', 'localtime')
-        GROUP BY identifiers.userid
+            AND datetime('TO', 'localtime'))
+        GROUP BY users.id
 
         """
         assert Q("get_drinkers_of", ["FOO", "BAR", "B"], ["FROM", "TO"]) == """
 
         SELECT count(*), users.name FROM coffees
-        LEFT JOIN identifiers on coffees.id = identifiers.userid
-        LEFT JOIN users on identifiers.userid = users.id
-        WHERE flavor = 'FOO' OR flavor = 'BAR' OR flavor = 'B'
-        AND coffees.time BETWEEN
+        INNER JOIN identifiers on coffees.id = identifiers.id
+        INNER JOIN users on identifiers.userid = users.id
+        WHERE (flavor = 'FOO' OR flavor = 'BAR' OR flavor = 'B')
+        AND (coffees.time BETWEEN
             datetime('FROM', 'localtime')
-            AND datetime('TO', 'localtime')
-        GROUP BY identifiers.userid
+            AND datetime('TO', 'localtime'))
+        GROUP BY users.id
 
         """
 
@@ -123,11 +123,11 @@ CREATE TABLE if not exists identifiers (
     """)
     cur.executescript("""
 INSERT OR IGNORE INTO users VALUES('1111','tester');
-insert into identifiers (userid, id, name) values ('1111', '10', 'chip 10');
+insert into identifiers (userid, id, name) values ('1111', '1111', 'chip 10');
 INSERT OR IGNORE INTO users VALUES('2222','newer');
-insert into identifiers (userid, id, name) values ('2222', '20', 'chip 20');
+insert into identifiers (userid, id, name) values ('2222', '2222', 'chip 20');
 INSERT OR IGNORE INTO users VALUES('3333','clone');
-insert into identifiers (userid, id, name) values ('3333', '30', 'chip 30');
+insert into identifiers (userid, id, name) values ('3333', '3333', 'chip 30');
 INSERT or ignore INTO coffees (id, flavor, time) VALUES
 ('1111', 'espresso lungo', datetime('now', 'localtime', '-99 seconds')),
 ('1111', 'Club-Mate 0,5 l', datetime('now', 'localtime', '-99 seconds')),