database.py

   1 import sqlite3
   2 import json
   3 import datetime
   4
   5
   6 class DatabaseError(Exception):
   7     pass
   8
   9
  10 class SettingsDatabase:
  11     version = 1
  12
  13     def __init__(self, db_path):
  14         self.db_path = db_path
  15
  16         # connect
  17         conn = sqlite3.connect(self.db_path)
  18
  19         self.db_version_check_and_create()
  20
  21         conn.commit()
  22         conn.close()
  23
  24     def has_table(self):
  25         conn = sqlite3.connect(self.db_path)
  26         cursor = conn.cursor()
  27         tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='botamusique';").fetchall()
  28         conn.close()
  29         if len(tables) == 0:
  30             return False
  31         return True
  32
  33     def db_version_check_and_create(self):
  34         conn = sqlite3.connect(self.db_path)
  35         cursor = conn.cursor()
  36
  37         if self.has_table():
  38             # check version
  39             result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?",
  40                                     ("bot", "db_version")).fetchall()
  41
  42             if len(result) == 0 or int(result[0][0]) != self.version:
  43                 old_name = "botamusique_old_%s" % datetime.datetime.now().strftime("%Y%m%d")
  44                 cursor.execute("ALTER TABLE botamusique RENAME TO %s" % old_name)
  45                 conn.commit()
  46                 self.create_table()
  47                 self.set("bot", "old_db_name", old_name)
  48         else:
  49             self.create_table()
  50
  51         conn.close()
  52
  53     def create_table(self):
  54         conn = sqlite3.connect(self.db_path)
  55         cursor = conn.cursor()
  56         cursor.execute("CREATE TABLE IF NOT EXISTS botamusique ("
  57                        "section TEXT, "
  58                        "option TEXT, "
  59                        "value TEXT, "
  60                        "UNIQUE(section, option))")
  61         cursor.execute("INSERT INTO botamusique (section, option, value) "
  62                        "VALUES (?, ?, ?)", ("bot", "db_version", "1"))
  63         cursor.execute("INSERT INTO botamusique (section, option, value) "
  64                        "VALUES (?, ?, ?)", ("bot", "music_db_version", "0"))
  65         conn.commit()
  66         conn.close()
  67
  68     def get(self, section, option, **kwargs):
  69         conn = sqlite3.connect(self.db_path)
  70         cursor = conn.cursor()
  71         result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?", (section, option)).fetchall()
  72         conn.close()
  73
  74         if len(result) > 0:
  75             return result[0][0]
  76         else:
  77             if 'fallback' in kwargs:
  78                 return kwargs['fallback']
  79             else:
  80                 raise DatabaseError("Item not found")
  81
  82     def getboolean(self, section, option, **kwargs):
  83         return bool(int(self.get(section, option, **kwargs)))
  84
  85     def getfloat(self, section, option, **kwargs):
  86         return float(self.get(section, option, **kwargs))
  87
  88     def getint(self, section, option, **kwargs):
  89         return int(self.get(section, option, **kwargs))
  90
  91     def set(self, section, option, value):
  92         conn = sqlite3.connect(self.db_path)
  93         cursor = conn.cursor()
  94         cursor.execute("INSERT OR REPLACE INTO botamusique (section, option, value) "
  95                        "VALUES (?, ?, ?)", (section, option, value))
  96         conn.commit()
  97         conn.close()
  98
  99     def has_option(self, section, option):
 100         conn = sqlite3.connect(self.db_path)
 101         cursor = conn.cursor()
 102         result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?", (section, option)).fetchall()
 103         conn.close()
 104         if len(result) > 0:
 105             return True
 106         else:
 107             return False
 108
 109     def remove_option(self, section, option):
 110         conn = sqlite3.connect(self.db_path)
 111         cursor = conn.cursor()
 112         cursor.execute("DELETE FROM botamusique WHERE section=? AND option=?", (section, option))
 113         conn.commit()
 114         conn.close()
 115
 116     def remove_section(self, section):
 117         conn = sqlite3.connect(self.db_path)
 118         cursor = conn.cursor()
 119         cursor.execute("DELETE FROM botamusique WHERE section=?", (section, ))
 120         conn.commit()
 121         conn.close()
 122
 123     def items(self, section):
 124         conn = sqlite3.connect(self.db_path)
 125         cursor = conn.cursor()
 126         results = cursor.execute("SELECT option, value FROM botamusique WHERE section=?", (section, )).fetchall()
 127         conn.close()
 128
 129         if len(results) > 0:
 130             return list(map(lambda v: (v[0], v[1]), results))
 131         else:
 132             return []
 133
 134     def drop_table(self):
 135         conn = sqlite3.connect(self.db_path)
 136         cursor = conn.cursor()
 137         cursor.execute("DROP TABLE botamusique")
 138         conn.close()
 139
 140
 141 class MusicDatabase:
 142     def __init__(self, db_path):
 143         self.db_path = db_path
 144
 145         # connect
 146         conn = sqlite3.connect(self.db_path)
 147         cursor = conn.cursor()
 148
 149         # check if table exists, or create one
 150         cursor.execute("CREATE TABLE IF NOT EXISTS music ("
 151                        "id TEXT PRIMARY KEY, "
 152                        "type TEXT, "
 153                        "title TEXT, "
 154                        "metadata TEXT, "
 155                        "tags TEXT)")
 156         conn.commit()
 157         conn.close()
 158
 159     def insert_music(self, music_dict):
 160         conn = sqlite3.connect(self.db_path)
 161         cursor = conn.cursor()
 162
 163         id = music_dict['id']
 164         title = music_dict['title']
 165         type = music_dict['type']
 166         tags = ",".join(music_dict['tags']) + ","
 167
 168         del music_dict['id']
 169         del music_dict['title']
 170         del music_dict['type']
 171         del music_dict['tags']
 172
 173         cursor.execute("INSERT OR REPLACE INTO music (id, type, title, metadata, tags) VALUES (?, ?, ?, ?, ?)",
 174                        (id,
 175                         type,
 176                         title,
 177                         json.dumps(music_dict),
 178                         tags))
 179
 180         conn.commit()
 181         conn.close()
 182
 183     def query_all_ids(self):
 184         conn = sqlite3.connect(self.db_path)
 185         cursor = conn.cursor()
 186         results = cursor.execute("SELECT id FROM music").fetchall()
 187         conn.close()
 188         return list(map(lambda i: i[0], results))
 189
 190     def query_all_tags(self):
 191         conn = sqlite3.connect(self.db_path)
 192         cursor = conn.cursor()
 193         results = cursor.execute("SELECT tags FROM music").fetchall()
 194         tags = []
 195         for result in results:
 196             for tag in result[0].strip(",").split(","):
 197                 if tag and tag not in tags:
 198                     tags.append(tag)
 199         conn.close()
 200         return tags
 201
 202     def query_music(self, **kwargs):
 203         condition = []
 204         filler = []
 205
 206         for key, value in kwargs.items():
 207             if isinstance(value, str):
 208                 condition.append(key + "=?")
 209                 filler.append(value)
 210             elif isinstance(value, dict):
 211                 condition.append(key + " " + value[0] + " ?")
 212                 filler.append(value[1])
 213
 214         condition_str = " AND ".join(condition)
 215
 216         conn = sqlite3.connect(self.db_path)
 217         cursor = conn.cursor()
 218         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 219                                  "WHERE %s" % condition_str, filler).fetchall()
 220         conn.close()
 221
 222         return self._result_to_dict(results)
 223
 224     def query_music_by_keywords(self, keywords):
 225         condition = []
 226         filler = []
 227
 228         for keyword in keywords:
 229             condition.append('LOWER(title) LIKE ?')
 230             filler.append("%{:s}%".format(keyword.lower()))
 231
 232         condition_str = " AND ".join(condition)
 233
 234         conn = sqlite3.connect(self.db_path)
 235         cursor = conn.cursor()
 236         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 237                                  "WHERE %s" % condition_str, filler).fetchall()
 238         conn.close()
 239
 240         return self._result_to_dict(results)
 241
 242     def query_music_by_tags(self, tags):
 243         condition = []
 244         filler = []
 245
 246         for tag in tags:
 247             condition.append('LOWER(tags) LIKE ?')
 248             filler.append("%{:s},%".format(tag.lower()))
 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         conn = sqlite3.connect(self.db_path)
 262         cursor = conn.cursor()
 263         results = []
 264
 265         for i in range(int(len(ids)/990) + 1):
 266             condition_str = " OR ".join(['id=?'] * min(990, len(ids) - i*990))
 267
 268             _results = cursor.execute("SELECT id, tags FROM music "
 269                                       "WHERE %s" % condition_str,
 270                                       ids[i*990: i*990 + min(990, len(ids) - i*990)]).fetchall()
 271             if _results:
 272                 results.extend(_results)
 273
 274         conn.close()
 275
 276         lookup = {}
 277         if len(results) > 0:
 278             for result in results:
 279                 id = result[0]
 280                 tags = result[1].strip(",").split(",")
 281                 lookup[id] = tags if tags[0] else []
 282
 283         return lookup
 284
 285     def query_random_music(self, count):
 286         conn = sqlite3.connect(self.db_path)
 287         cursor = conn.cursor()
 288         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 289                                  "WHERE id IN (SELECT id FROM music ORDER BY RANDOM() LIMIT ?)", (count,)).fetchall()
 290         conn.close()
 291
 292         return self._result_to_dict(results)
 293
 294     def _result_to_dict(self, results):
 295         if len(results) > 0:
 296             music_dicts = []
 297             for result in results:
 298                 music_dict = json.loads(result[3])
 299                 music_dict['type'] = result[1]
 300                 music_dict['title'] = result[2]
 301                 music_dict['id'] = result[0]
 302                 music_dict['tags'] = result[4].strip(",").split(",")
 303                 if not music_dict['tags'][0]:
 304                     music_dict['tags'] = []
 305
 306                 music_dicts.append(music_dict)
 307
 308             return music_dicts
 309         else:
 310             return []
 311
 312     def delete_music(self, **kwargs):
 313         condition = []
 314         filler = []
 315
 316         for key, value in kwargs.items():
 317             if isinstance(value, str):
 318                 condition.append(key + "=?")
 319                 filler.append(value)
 320             else:
 321                 condition.append(key + " " + value[0] + " ?")
 322                 filler.append(value[1])
 323
 324         condition_str = " AND ".join(condition)
 325
 326         conn = sqlite3.connect(self.db_path)
 327         cursor = conn.cursor()
 328         cursor.execute("DELETE FROM music "
 329                        "WHERE %s" % condition_str, filler)
 330         conn.commit()
 331         conn.close()
 332
 333     def drop_table(self):
 334         conn = sqlite3.connect(self.db_path)
 335         cursor = conn.cursor()
 336         cursor.execute("DROP TABLE music")
 337         conn.close()