database.py

   1 import sqlite3
   2 import json
   3 import datetime
   4
   5 class DatabaseError(Exception):
   6     pass
   7
   8 class SettingsDatabase:
   9     version = 1
  10     def __init__(self, db_path):
  11         self.db_path = db_path
  12
  13         # connect
  14         conn = sqlite3.connect(self.db_path)
  15         cursor = conn.cursor()
  16
  17         self.db_version_check_and_create()
  18
  19         conn.commit()
  20         conn.close()
  21
  22     def has_table(self):
  23         conn = sqlite3.connect(self.db_path)
  24         cursor = conn.cursor()
  25         tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='botamusique';").fetchall()
  26         conn.close()
  27         if len(tables) == 0:
  28             return False
  29         return True
  30
  31     def db_version_check_and_create(self):
  32         conn = sqlite3.connect(self.db_path)
  33         cursor = conn.cursor()
  34
  35         if self.has_table():
  36             # check version
  37             result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?",
  38                                     ("bot", "db_version")).fetchall()
  39
  40             if len(result) == 0 or int(result[0][0]) != self.version:
  41                 old_name = "botamusique_old_%s" % datetime.datetime.now().strftime("%Y%m%d")
  42                 cursor.execute("ALTER TABLE botamusique RENAME TO %s" % old_name)
  43                 conn.commit()
  44                 self.create_table()
  45                 self.set("bot", "old_db_name", old_name)
  46         else:
  47             self.create_table()
  48
  49         conn.close()
  50
  51     def create_table(self):
  52         conn = sqlite3.connect(self.db_path)
  53         cursor = conn.cursor()
  54         cursor.execute("CREATE TABLE IF NOT EXISTS botamusique ("
  55                        "section TEXT, "
  56                        "option TEXT, "
  57                        "value TEXT, "
  58                        "UNIQUE(section, option))")
  59         cursor.execute("INSERT INTO botamusique (section, option, value) "
  60                        "VALUES (?, ?, ?)" , ("bot", "db_version", "1"))
  61         cursor.execute("INSERT INTO botamusique (section, option, value) "
  62                        "VALUES (?, ?, ?)" , ("bot", "music_db_version", "0"))
  63         conn.commit()
  64         conn.close()
  65
  66     def get(self, section, option, **kwargs):
  67         conn = sqlite3.connect(self.db_path)
  68         cursor = conn.cursor()
  69         result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?", (section, option)).fetchall()
  70         conn.close()
  71
  72         if len(result) > 0:
  73             return result[0][0]
  74         else:
  75             if 'fallback' in kwargs:
  76                 return kwargs['fallback']
  77             else:
  78                 raise DatabaseError("Item not found")
  79
  80     def getboolean(self, section, option, **kwargs):
  81         return bool(int(self.get(section, option, **kwargs)))
  82
  83     def getfloat(self, section, option, **kwargs):
  84         return float(self.get(section, option, **kwargs))
  85
  86     def getint(self, section, option, **kwargs):
  87         return int(self.get(section, option, **kwargs))
  88
  89     def set(self, section, option, value):
  90         conn = sqlite3.connect(self.db_path)
  91         cursor = conn.cursor()
  92         cursor.execute("INSERT OR REPLACE INTO botamusique (section, option, value) "
  93                        "VALUES (?, ?, ?)" , (section, option, value))
  94         conn.commit()
  95         conn.close()
  96
  97     def has_option(self, section, option):
  98         conn = sqlite3.connect(self.db_path)
  99         cursor = conn.cursor()
 100         result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?", (section, option)).fetchall()
 101         conn.close()
 102         if len(result) > 0:
 103             return True
 104         else:
 105             return False
 106
 107     def remove_option(self, section, option):
 108         conn = sqlite3.connect(self.db_path)
 109         cursor = conn.cursor()
 110         cursor.execute("DELETE FROM botamusique WHERE section=? AND option=?", (section, option))
 111         conn.commit()
 112         conn.close()
 113
 114     def remove_section(self, section):
 115         conn = sqlite3.connect(self.db_path)
 116         cursor = conn.cursor()
 117         cursor.execute("DELETE FROM botamusique WHERE section=?", (section, ))
 118         conn.commit()
 119         conn.close()
 120
 121     def items(self, section):
 122         conn = sqlite3.connect(self.db_path)
 123         cursor = conn.cursor()
 124         results = cursor.execute("SELECT option, value FROM botamusique WHERE section=?", (section, )).fetchall()
 125         conn.close()
 126
 127         if len(results) > 0:
 128             return list(map(lambda v: (v[0], v[1]), results))
 129         else:
 130             return []
 131
 132     def drop_table(self):
 133         conn = sqlite3.connect(self.db_path)
 134         cursor = conn.cursor()
 135         cursor.execute("DROP TABLE botamusique")
 136         conn.close()
 137
 138
 139 class MusicDatabase:
 140     def __init__(self, db_path):
 141         self.db_path = db_path
 142
 143         # connect
 144         conn = sqlite3.connect(self.db_path)
 145         cursor = conn.cursor()
 146
 147         # check if table exists, or create one
 148         cursor.execute("CREATE TABLE IF NOT EXISTS music ("
 149                        "id TEXT PRIMARY KEY, "
 150                        "type TEXT, "
 151                        "title TEXT, "
 152                        "metadata TEXT, "
 153                        "tags TEXT)")
 154         conn.commit()
 155         conn.close()
 156
 157     def insert_music(self, music_dict):
 158         conn = sqlite3.connect(self.db_path)
 159         cursor = conn.cursor()
 160
 161         id = music_dict['id']
 162         title = music_dict['title']
 163         type = music_dict['type']
 164         tags = ",".join(music_dict['tags'])
 165         del music_dict['id']
 166         del music_dict['title']
 167         del music_dict['type']
 168         del music_dict['tags']
 169
 170         cursor.execute("INSERT OR REPLACE INTO music (id, type, title, metadata, tags) VALUES (?, ?, ?, ?, ?)",
 171                        (id,
 172                         type,
 173                         title,
 174                         json.dumps(music_dict),
 175                         tags))
 176
 177         conn.commit()
 178         conn.close()
 179
 180     def query_music(self, **kwargs):
 181         condition = []
 182         filler = []
 183
 184         for key, value in kwargs.items():
 185             if isinstance(value, str):
 186                 condition.append(key + "=?")
 187                 filler.append(value)
 188             else:
 189                 condition.append(key + " " + value[0] + " ?")
 190                 filler.append(value[1])
 191
 192         condition_str = " AND ".join(condition)
 193
 194         conn = sqlite3.connect(self.db_path)
 195         cursor = conn.cursor()
 196         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 197                                 "WHERE %s" % condition_str, filler).fetchall()
 198         conn.close()
 199
 200         if len(results) > 0:
 201             music_dicts = []
 202             for result in results:
 203                 music_dict = json.loads(result[3])
 204                 music_dict['type'] = result[1]
 205                 music_dict['title'] = result[2]
 206                 music_dict['tags'] = result[4].split(",")
 207                 music_dict['id'] = result[0]
 208                 music_dicts.append(music_dict)
 209
 210             return music_dicts
 211         else:
 212             return None
 213
 214     def delete_music(self, **kwargs):
 215         condition = []
 216         filler = []
 217
 218         for key, value in kwargs.items():
 219             if isinstance(value, str):
 220                 condition.append(key + "=?")
 221                 filler.append(value)
 222             else:
 223                 condition.append(key + " " + value[0] + " ?")
 224                 filler.append(value[1])
 225
 226         condition_str = " AND ".join(condition)
 227
 228         conn = sqlite3.connect(self.db_path)
 229         cursor = conn.cursor()
 230         cursor.execute("DELETE FROM music "
 231                                  "WHERE %s" % condition_str, filler)
 232         conn.commit()
 233         conn.close()