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 def flavors():
 103     conn, c = open_db()
 104     res = list(c.execute("select distinct name, ord from flavors"))
 105     close_db(conn)
 106     return res
 107
 108 def coffee_flavors(uid=None, days=0, start=0):
 109     """Returns flavor statistics for team/user during selected period/since beginning.
 110
 111     days -- number of days for computation
 112     start -- shift size from the current time
 113
 114     When 'days' is not given or 0 return statistics since the beginning.
 115
 116     e.g. (7, 7) returns statistics for 7 days, 7 days ago.
 117     """
 118
 119     conn, c = open_db()
 120
 121     query = ""
 122     variables = list()
 123
 124     if days is not None and days != 0:
 125         query += " where date(time) between date('now', 'localtime', '-"+ str(days+start-1) +" days') and date('now', 'localtime', '-"+ str(start) +" days')"
 126
 127         if uid is not None:
 128             query += " and ids.userid = ? and ids.active"
 129             variables.append(uid)
 130     elif uid is not None:
 131         query += " where ids.userid = ? and ids.active"
 132         variables.append(uid)
 133
 134     res = list(c.execute("""
 135         select f.name, count(c.flavor) from flavors f
 136         left join (select * from coffees co left join identifiers ids on co.id=ids.id
 137         """+query+""") c
 138         on f.name=c.flavor group by f.name
 139         order by f.ord asc
 140         """, variables))
 141
 142     close_db(conn)
 143     return res
 144
 145 def coffee_history(uid=None):
 146     conn, c = open_db()
 147
 148     if uid is None:
 149         res = list(c.execute("""
 150             select strftime('%s', ds.d),count(c.flavor),c.flavor from
 151             (select num,date('now', 'localtime', -num || ' days') as d from days) ds
 152             left join
 153             (select time,flavor from coffees) c
 154             on d = date(c.time) group by d, c.flavor
 155             """))
 156     else:
 157         res = list(c.execute(
 158             """
 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 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
 163             on d = date(c.time) group by d, c.flavor
 164             """
 165             , (uid,)))
 166
 167     close_db(conn)
 168     return res
 169
 170 def drink_count(uid=None, start=None, stop=None):
 171     """Return a list of tuples ('<drink type>', <count>).
 172
 173     >>> drink_count(stop=0)
 174     [('coffee', 7066), ('Club-Mate', 497), ('tea', 1)]
 175     """
 176     conn, c = open_db()
 177
 178     args = []
 179     clauses = []
 180
 181     if uid is not None:
 182         clauses.append("ids.userid = ? and ids.active")
 183         args.append(uid)
 184
 185     if start is not None:
 186         clauses.append("date(time, 'localtime') >= date('now', 'localtime', '-%d days')" % int(start))
 187
 188     if stop is not None:
 189         clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
 190
 191     return list(c.execute("select fl.type, count(*) from coffees co "
 192                           "left join flavors fl on co.flavor = fl.name "
 193                           "left join identifiers ids on co.id = ids.id where "
 194                           + " and ".join(clauses) + " group by fl.type "
 195                           "order by fl.ord asc", args))