coffee_db.py

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