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