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         if len(results) > 0:
 221             music_dicts = []
 222             for result in results:
 223                 music_dict = json.loads(result[3])
 224                 music_dict['type'] = result[1]
 225                 music_dict['title'] = result[2]
 226                 music_dict['tags'] = result[4].strip(",").split(",")
 227                 music_dict['id'] = result[0]
 228                 music_dicts.append(music_dict)
 229
 230             return music_dicts
 231         else:
 232             return None
 233
 234     def query_music_by_keywords(self, keywords):
 235         condition = []
 236         filler = []
 237
 238         for keyword in keywords:
 239             condition.append('title LIKE ?')
 240             filler.append("%{:s}%".format(keyword))
 241
 242
 243         condition_str = " AND ".join(condition)
 244
 245         conn = sqlite3.connect(self.db_path)
 246         cursor = conn.cursor()
 247         results = cursor.execute("SELECT id, type, title, metadata, tags FROM music "
 248                                  "WHERE %s" % condition_str, filler).fetchall()
 249         conn.close()
 250
 251         if len(results) > 0:
 252             music_dicts = []
 253             for result in results:
 254                 music_dict = json.loads(result[3])
 255                 music_dict['type'] = result[1]
 256                 music_dict['title'] = result[2]
 257                 music_dict['id'] = result[0]
 258                 music_dict['tags'] = result[4].strip(",").split(",")
 259                 if not music_dict['tags'][0]:
 260                     music_dict['tags'] = []
 261
 262                 music_dicts.append(music_dict)
 263
 264             return music_dicts
 265         else:
 266             return None
 267
 268     def query_music_by_tags(self, tags):
 269         condition = []
 270         filler = []
 271
 272         for tag in tags:
 273             condition.append('tags LIKE ?')
 274             filler.append("%{:s},%".format(tag))
 275
 276
 277         condition_str = " AND ".join(condition)
 278
 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 %s" % condition_str, filler).fetchall()
 283         conn.close()
 284
 285         if len(results) > 0:
 286             music_dicts = []
 287             for result in results:
 288                 music_dict = json.loads(result[3])
 289                 music_dict['type'] = result[1]
 290                 music_dict['title'] = result[2]
 291                 music_dict['id'] = result[0]
 292                 music_dict['tags'] = result[4].strip(",").split(",")
 293                 if not music_dict['tags'][0]:
 294                     music_dict['tags'] = []
 295
 296                 music_dicts.append(music_dict)
 297
 298             return music_dicts
 299         else:
 300             return None
 301
 302     def query_tags_by_id(self, id):
 303         conn = sqlite3.connect(self.db_path)
 304         cursor = conn.cursor()
 305         results = cursor.execute("SELECT tags FROM music "
 306                                  "WHERE id=?", (id, )).fetchall()
 307         conn.close()
 308
 309         if len(results) > 0:
 310             tags = results[0][0].strip(",").split(",")
 311
 312             return tags if tags[0] else []
 313         else:
 314             return None
 315
 316     def delete_music(self, **kwargs):
 317         condition = []
 318         filler = []
 319
 320         for key, value in kwargs.items():
 321             if isinstance(value, str):
 322                 condition.append(key + "=?")
 323                 filler.append(value)
 324             else:
 325                 condition.append(key + " " + value[0] + " ?")
 326                 filler.append(value[1])
 327
 328         condition_str = " AND ".join(condition)
 329
 330         conn = sqlite3.connect(self.db_path)
 331         cursor = conn.cursor()
 332         cursor.execute("DELETE FROM music "
 333                                  "WHERE %s" % condition_str, filler)
 334         conn.commit()
 335         conn.close()
 336
 337
 338     def drop_table(self):
 339         conn = sqlite3.connect(self.db_path)
 340         cursor = conn.cursor()
 341         cursor.execute("DROP TABLE music")
 342         conn.close()