+from sqlite3 import connect
+from os import remove
+from coffee_getter.db import Db, Q
+import coffee_getter.conf as conf
+
+
+class Queries(TestCase):
+ def test_get_drinks(self):
+ assert Q("get_drinks") == """
+
+ SELECT count(*), flavor
+ FROM coffees
+ WHERE time BETWEEN
+ datetime('now', 'localtime', '-7 days')
+ AND datetime('now', 'localtime')
+ GROUP BY flavor
+
+ """
+
+ def test_get_drinkers(self):
+ 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
+ WHERE flavor = 'FOO'
+ AND coffees.time BETWEEN
+ datetime('now', 'localtime', '-7 days')
+ AND datetime('now', 'localtime')
+ GROUP BY identifiers.userid
+
+ """
+ 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
+ WHERE flavor = 'BAR'
+ AND coffees.time BETWEEN
+ datetime('FROM', 'localtime')
+ AND datetime('TO', 'localtime')
+ GROUP BY identifiers.userid
+
+ """
+ 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
+ datetime('FROM', 'localtime')
+ AND datetime('TO', 'localtime')
+ GROUP BY identifiers.userid
+
+ """
+
+ def test_assert_error_raised(self):
+ self.assertRaises(AssertionError, Q, "get_drinkers_of")
+ self.assertRaises(AssertionError, Q, "get_drinkers_of", "")
+ self.assertRaises(AssertionError, Q, "get_drinkers_of", [])
+ self.assertRaises(AssertionError, Q, "get_drinkers_of", "foo", "")
+ self.assertRaises(AssertionError, Q, "get_drinkers_of", "foo", "bar")
+ self.assertRaises(AssertionError, Q, "get_drinkers_of", "foo", [])
+
+
+TESTDB_PATH = ".tmptest.db"