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
 166         del music_dict['id']
 167         del music_dict['title']
 168         del music_dict['type']
 169         del music_dict['tags']
 170
 171         cursor.execute("INSERT OR REPLACE INTO music (id, type, title, metadata, tags) VALUES (?, ?, ?, ?, ?)",
 172                        (id,
 173                         type,
 174                         title,
 175                         json.dumps(music_dict),
 176                         tags))
 177
 178         conn.commit()
 179         conn.close()
 180
 181     def query_all_ids(self):
 182         conn = sqlite3.connect(self.db_path)
 183         cursor = conn.cursor()
 184         results = cursor.execute("SELECT id FROM music").fetchall()
 185         conn.close()
 186         return list(map(lambda i: i[0], results))
 187
 188     def query_all_tags(self):
 189         conn = sqlite3.connect(self.db_path)
 190         cursor = conn.cursor()
 191         results = cursor.execute("SELECT tags FROM music").fetchall()
 192         tags = []
 193         for result in results:
 194             for tag in result[0].strip(",").split(","):
 195                 if tag and tag not in tags:
 196                     tags.append(tag)
 197         conn.close()
 198         return tags
 199
 200     def query_music(self, **kwargs):
 201         condition = []
 202         filler = []
 203
 204         for key, value in kwargs.items():
 205             if isinstance(value, str):
 206                 condition.append(key + "=?")
 207                 filler.append(value)
 208             elif isinstance(value, dict):
 209                 condition.append(key + " " + value[0] + " ?")
 210                 filler.append(value[1])
 211
 212         condition_str = " AND ".join(condition)
 213
 214         conn = sqlite3.connect(self.db_path)
 215         cursor = conn.cursor()
 216         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 217                                 "WHERE %s" % condition_str, filler).fetchall()
 218         conn.close()
 219
 220         return self._result_to_dict(results)
 221
 222     def query_music_by_keywords(self, keywords):
 223         condition = []
 224         filler = []
 225
 226         for keyword in keywords:
 227             condition.append('LOWER(title) LIKE ?')
 228             filler.append("%{:s}%".format(keyword.lower()))
 229
 230
 231         condition_str = " AND ".join(condition)
 232
 233         conn = sqlite3.connect(self.db_path)
 234         cursor = conn.cursor()
 235         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 236                                  "WHERE %s" % condition_str, filler).fetchall()
 237         conn.close()
 238
 239         return self._result_to_dict(results)
 240
 241     def query_music_by_tags(self, tags):
 242         condition = []
 243         filler = []
 244
 245         for tag in tags:
 246             condition.append('LOWER(tags) LIKE ?')
 247             filler.append("%{:s},%".format(tag.lower()))
 248
 249
 250         condition_str = " AND ".join(condition)
 251
 252         conn = sqlite3.connect(self.db_path)
 253         cursor = conn.cursor()
 254         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 255                                  "WHERE %s" % condition_str, filler).fetchall()
 256         conn.close()
 257
 258         return self._result_to_dict(results)
 259
 260     def query_tags_by_ids(self, ids):
 261         condition_str = " OR ".join(['id=?'] * len(ids))
 262
 263         conn = sqlite3.connect(self.db_path)
 264         cursor = conn.cursor()
 265         results = cursor.execute("SELECT id, tags FROM music "
 266                                  "WHERE %s" % condition_str, ids).fetchall()
 267         conn.close()
 268
 269         lookup = {}
 270         if len(results) > 0:
 271             for result in results:
 272                 id = result[0]
 273                 tags = result[1].strip(",").split(",")
 274                 lookup[id] = tags if tags[0] else []
 275
 276         return lookup
 277
 278     def query_random_music(self, count):
 279         conn = sqlite3.connect(self.db_path)
 280         cursor = conn.cursor()
 281         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 282                                  "WHERE id IN (SELECT id FROM music ORDER BY RANDOM() LIMIT ?)", (count,)).fetchall()
 283         conn.close()
 284
 285         return self._result_to_dict(results)
 286
 287
 288     def _result_to_dict(self, results):
 289         if len(results) > 0:
 290             music_dicts = []
 291             for result in results:
 292                 music_dict = json.loads(result[3])
 293                 music_dict['type'] = result[1]
 294                 music_dict['title'] = result[2]
 295                 music_dict['id'] = result[0]
 296                 music_dict['tags'] = result[4].strip(",").split(",")
 297                 if not music_dict['tags'][0]:
 298                     music_dict['tags'] = []
 299
 300                 music_dicts.append(music_dict)
 301
 302             return music_dicts
 303         else:
 304             return []
 305
 306     def delete_music(self, **kwargs):
 307         condition = []
 308         filler = []
 309
 310         for key, value in kwargs.items():
 311             if isinstance(value, str):
 312                 condition.append(key + "=?")
 313                 filler.append(value)
 314             else:
 315                 condition.append(key + " " + value[0] + " ?")
 316                 filler.append(value[1])
 317
 318         condition_str = " AND ".join(condition)
 319
 320         conn = sqlite3.connect(self.db_path)
 321         cursor = conn.cursor()
 322         cursor.execute("DELETE FROM music "
 323                                  "WHERE %s" % condition_str, filler)
 324         conn.commit()
 325         conn.close()
 326
 327
 328     def drop_table(self):
 329         conn = sqlite3.connect(self.db_path)
 330         cursor = conn.cursor()
 331         cursor.execute("DROP TABLE music")
 332         conn.close()