coffee_db.py

   1 import sqlite3
   2 import os
   3
   4 dbdir = os.path.dirname(__file__)
   5 dbdef = os.path.join(dbdir, "coffee_db.sql")
   6 dbfile = os.path.join(dbdir, "coffee.db")
   7
   8
   9 def open_db():
  10     conn = sqlite3.connect(dbfile)
  11     c = conn.cursor()
  12     return conn, c
  13
  14
  15 def close_db(conn):
  16     conn.commit()
  17     conn.close()
  18
  19
  20 def init_db():
  21     conn, c = open_db()
  22     with open(dbdef, "r") as f:
  23         c.executescript(f.read())
  24     close_db(conn)
  25
  26
  27 def add_user(uid):
  28     conn, c = open_db()
  29     c.execute("insert or ignore into users (id) values (?)", (uid,))
  30     close_db(conn)
  31
  32
  33 def add_user_identifier(uid, iid, name):
  34     # Check if this identifier is not currently associated with different account
  35     if not get_uid(iid):
  36         conn, c = open_db()
  37
  38         # Try to remove old relation of identifier
  39         # As 'delete or ignore' does not exist, workaround is used
  40         res = c.execute("select * from identifiers where id = ? and active = 0", (iid, ))
  41
  42         # This is True when some rows were found before; delete old relation
  43         if res.fetchone():
  44             print("Deleting uid:%s id:%s" % (uid, iid))
  45             res = c.execute("delete from identifiers where id = ? and active = 0", (iid, ))
  46
  47         # Add new relation
  48         res = c.execute("insert into identifiers (userid, id, name) values (?, ?, ?)", (uid, iid, name, ))
  49
  50         close_db(conn)
  51
  52
  53 def disable_user_identifier(uid, iid):
  54     conn, c = open_db()
  55     c.execute("update identifiers set active = 0 where userid = ? and id = ?", (uid, iid, ))
  56     close_db(conn)
  57
  58
  59 def get_name(uid):
  60     conn, c = open_db()
  61     for name, in c.execute("select name from users where id = ?", (uid,)):
  62         close_db(conn)
  63         return name
  64     close_db(conn)
  65     return None
  66
  67
  68 def get_uid(iid):
  69     conn, c = open_db()
  70     res = list(c.execute("""
  71             select userid from identifiers where id = ? and active
  72             """, (iid,)))
  73     close_db(conn)
  74
  75     return res[0][0] if len(res) > 0 else None
  76
  77
  78 def name_user(uid, name):
  79     conn, c = open_db()
  80     c.execute("update users set name = ? where id = ?", (name, uid))
  81     close_db(conn)
  82
  83
  84 def rename_user_identifier(uid, iid, name):
  85     conn, c = open_db()
  86     c.execute("update identifiers set name = ? where userid = ? and id = ?", (name, uid, iid, ))
  87     close_db(conn)
  88
  89
  90 def list_users():
  91     conn, c = open_db()
  92     for row in c.execute("select * from users"):
  93         print(row)
  94     close_db(conn)
  95
  96
  97 def list_user_identifiers(uid):
  98     conn, c = open_db()
  99     res = list(c.execute("""
 100             select * from identifiers where userid = ? and active
 101             """, (uid,)))
 102     close_db(conn)
 103     return res
 104
 105
 106 def add_coffee(uid, flavor, time=None):
 107     conn, c = open_db()
 108     if time is None:
 109         c.execute("insert into coffees (id, flavor) values (?,?)", (uid, flavor))
 110     else:
 111         c.execute("insert or ignore into coffees (id, flavor, time) values (?,?,?)", (uid, flavor, time))
 112     close_db(conn)
 113
 114
 115 def add_event(uid, event_name, time):
 116     conn, c = open_db()
 117     c.execute("""insert into events (user_id, event_type, time)
 118               values (?, (SELECT id FROM event_types WHERE name = ?), ?)""",
 119               (uid, event_name, time))
 120     close_db(conn)
 121
 122
 123 def flavors():
 124     conn, c = open_db()
 125     res = list(c.execute("select distinct name, ord from flavors"))
 126     close_db(conn)
 127     return res
 128
 129
 130 def coffee_flavors(uid=None, days=0, start=0):
 131     """Returns flavor statistics for team/user during selected
 132     period/since beginning.
 133
 134     days -- number of days for computation
 135     start -- shift size from the current time
 136
 137     When 'days' is not given or 0 return statistics since the beginning.
 138
 139     e.g. (7, 7) returns statistics for 7 days, 7 days ago.
 140     """
 141
 142     conn, c = open_db()
 143
 144     query = ""
 145     variables = list()
 146
 147     if days is not None and days != 0:
 148         query += " where date(time) between date('now', 'localtime', '-" + str(days+start-1) + " days') and date('now', 'localtime', '-" + str(start) + " days')"
 149
 150         if uid is not None:
 151             query += " and ids.userid = ? and ids.active"
 152             variables.append(uid)
 153     elif uid is not None:
 154         query += " where ids.userid = ? and ids.active"
 155         variables.append(uid)
 156
 157     res = list(c.execute("""
 158         select f.name, count(c.flavor) from flavors f
 159         left join (select * from coffees co left join identifiers ids on co.id=ids.id
 160         """+query+""") c
 161         on f.name=c.flavor group by f.name
 162         order by f.ord asc
 163         """, variables))
 164
 165     close_db(conn)
 166     return res
 167
 168
 169 def coffee_history(uid=None):
 170     conn, c = open_db()
 171
 172     if uid is None:
 173         res = list(c.execute("""
 174             select strftime('%s', ds.d),count(c.flavor),c.flavor from
 175             (select num,date('now', 'localtime', -num || ' days') as d from days) ds
 176             left join
 177             (select time,flavor from coffees) c
 178             on d = date(c.time) group by d, c.flavor
 179             """))
 180     else:
 181         res = list(c.execute(
 182             """
 183             select strftime('%s', ds.d),count(c.flavor),c.flavor from
 184             (select num,date('now', 'localtime', -num || ' days') as d from days) ds
 185             left join
 186             (select date(time, 'localtime') as time,flavor from coffees co left join identifiers ids on co.id = ids.id where ids.userid = ? and ids.active) c
 187             on d = date(c.time) group by d, c.flavor
 188             """,
 189             (uid,)))
 190
 191     close_db(conn)
 192     return res
 193
 194
 195 def drink_count(uid=None, start=None, stop=None):
 196     """Return a list of tuples ('<drink type>', <count>).
 197
 198     >>> drink_count(stop=0)
 199     [('coffee', 7066), ('Club-Mate', 497), ('tea', 1)]
 200     """
 201     conn, c = open_db()
 202
 203     args = []
 204     clauses = []
 205
 206     if uid is not None:
 207         clauses.append("ids.userid = ? and ids.active")
 208         args.append(uid)
 209
 210     if start is not None:
 211         clauses.append("date(time, 'localtime') >= date('now', 'localtime', '-%d days')" % int(start))
 212
 213     if stop is not None:
 214         clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
 215
 216     return list(c.execute("select fl.type, count(*) from coffees co "
 217                           "left join flavors fl on co.flavor = fl.name "
 218                           "left join identifiers ids on co.id = ids.id where "
 219                           + " and ".join(clauses) + " group by fl.type "
 220                           "order by fl.ord asc", args))
 221
 222
 223 def last_events():
 224     """Return mapping with event names as keys and SQLite time string of
 225     the last event as values.
 226     """
 227     conn, c = open_db()
 228     res = dict(c.execute("""select name, MAX(time)
 229                             from events as e left join event_types as et on e.event_type = et.id
 230                             group by name"""))
 231     close_db(conn)
 232     return res