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
"""
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
"""
""")
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')),
("tea", 3))
def test_top_mate_drinkers(self):
- assert self.db.getTopMateDrinkers() == (
+ assert self.db.get_top_mate_drinkers() == (
("tester", 4*0.5 + 4*0.33),
("clone", 0.5 + 2*0.33),
("newer", 2*0.33))
- top_sorted = list(self.db.getTopMateDrinkers())
+ top_sorted = list(self.db.get_top_mate_drinkers())
top_sorted.sort(key=lambda x: (x[1], x[0]), reverse=True)
- assert self.db.getTopMateDrinkers() == tuple(top_sorted)
+ assert self.db.get_top_mate_drinkers() == tuple(top_sorted)
def test_top_tea_drinkers(self):
assert self.db.get_top_tea_drinkers() == (("clone", 2), ("tester", 1))
def test_coffee_sums(self):
- s = self.db.getDrunkSum("espresso", self.lwstr, self.tdystr)
+ s = self.db.get_drunk_sum("espresso", self.lwstr, self.tdystr)
assert s == ()
- s = self.db.getDrunkSum("espresso lungo", self.lwstr, self.tdystr)
+ s = self.db.get_drunk_sum("espresso lungo", self.lwstr, self.tdystr)
assert s == (("tester", 4), ("newer", 2), ("clone", 2))
def test_mate_sums(self):
- s = self.db.getDrunkSum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
+ s = self.db.get_drunk_sum("Club-Mate 0,33 l", self.lwstr, self.tdystr)
assert s == (("tester", 4), ("newer", 2), ("clone", 2))
- s = self.db.getDrunkSum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
+ s = self.db.get_drunk_sum("Club-Mate 0,5 l", self.lwstr, self.tdystr)
assert s == (("tester", 4), ("clone", 1))
- s = self.db.getDrunkSum(
+ s = self.db.get_drunk_sum(
"Club-Mate 0,5 l;Club-Mate 0,33 l",
self.lwstr,
self.tdystr)
assert s == (("tester", 8), ("newer", 2), ("clone", 3))
- s = self.db.getDrunkList(
+ s = self.db.get_drunk_list(
"Club-Mate 0,5 l;Club-Mate 0,33 l",
self.lwstr,
self.tdystr)
assert s == {"tester": [4, 4], "newer": [0, 2], "clone": [1, 2]}
def test_assert_error_raised_for_bad_number_of_arguments(self):
- self.assertRaises(AssertionError, self.db.getDrunkSum)
- self.assertRaises(AssertionError, self.db.getDrunkSum, "")
- self.assertRaises(AssertionError, self.db.getDrunkSum, [])
- self.assertRaises(AssertionError, self.db.getDrunkSum, "espresso")
+ self.assertRaises(TypeError, self.db.get_drunk_sum)
+ self.assertRaises(TypeError, self.db.get_drunk_sum, "")
+ self.assertRaises(TypeError, self.db.get_drunk_sum, [])
+ self.assertRaises(TypeError, self.db.get_drunk_sum, "espresso")
self.assertRaises(
- AssertionError,
- self.db.getDrunkSum,
+ TypeError,
+ self.db.get_drunk_sum,
"espresso",
"2010-10-20")
self.assertRaises(
- AssertionError,
- self.db.getDrunkSum,
+ TypeError,
+ self.db.get_drunk_sum,
"espresso",
"lungo",
"2010-10-20",