fix: optimize database logic, fixed #93

diff --git a/database.py b/database.py
index cdc6363ff4a3f6cc11fc03561c70985db81db7b3..f3096b15ebccb1d3fb5fcd0c4916c27a4d76d9a5 100644 (file)
--- a/database.py
+++ b/database.py
@@ -257,19 +257,23 @@ class MusicDatabase:
 
         return self._result_to_dict(results)
 
-    def query_tags_by_id(self, id):
+    def query_tags_by_ids(self, ids):
+        condition_str = " OR ".join(['id=?'] * len(ids))
+
         conn = sqlite3.connect(self.db_path)
         cursor = conn.cursor()
-        results = cursor.execute("SELECT tags FROM music "
-                                 "WHERE id=?", (id, )).fetchall()
+        results = cursor.execute("SELECT id, tags FROM music "
+                                 "WHERE %s" % condition_str, ids).fetchall()
         conn.close()
 
+        lookup = {}
         if len(results) > 0:
-            tags = results[0][0].strip(",").split(",")
+            for result in results:
+                id = result[0]
+                tags = result[1].strip(",").split(",")
+                lookup[id] = tags if tags[0] else []
 
-            return tags if tags[0] else []
-        else:
-            return None
+        return lookup
 
     def query_random_music(self, count):
         conn = sqlite3.connect(self.db_path)
@@ -297,7 +301,7 @@ class MusicDatabase:
 
             return music_dicts
         else:
-            return None
+            return []
 
     def delete_music(self, **kwargs):
         condition = []

diff --git a/interface.py b/interface.py
index 43ad53bbfdec3e7079bbb41f2bef907074961c3a..c2257dcd0a607658f1808d8256350d2ee50d291a 100644 (file)
--- a/interface.py
+++ b/interface.py
@@ -118,12 +118,13 @@ def build_tags_color_lookup():
     return color_lookup
 
 def build_path_tags_lookup():
-    path_lookup = {}
-    items = var.cache.file_id_lookup.items()
-    for path, id in items:
-        path_lookup[path] = var.music_db.query_tags_by_id(id)
+    id_tags_lookup = var.music_db.query_tags_by_ids(list(var.cache.file_id_lookup.values()))
 
-    return path_lookup
+    path_tags_lookup = {}
+    for path, id in var.cache.file_id_lookup.items():
+        path_tags_lookup[path] = id_tags_lookup[id]
+
+    return path_tags_lookup
 
 def recur_dir(dirobj):
     for name, dir in dirobj.get_subdirs().items():

diff --git a/media/playlist.py b/media/playlist.py
index 9387cd7fb830e6af2712c2784b54bf1a12e68cdb..eb0300347eb71082984a1a74e42c4bd93f91db27 100644 (file)
--- a/media/playlist.py
+++ b/media/playlist.py
@@ -456,8 +456,9 @@ class AutoPlaylist(OneshotPlaylist):
 
     def refresh(self):
         dicts = var.music_db.query_random_music(var.config.getint("bot", "autoplay_length", fallback=5))
-        _list = [get_item_wrapper_from_dict(var.bot, _dict, "AutoPlay") for _dict in dicts]
-        self.from_list(_list, -1)
+        if dicts:
+            _list = [get_item_wrapper_from_dict(var.bot, _dict, "AutoPlay") for _dict in dicts]
+            self.from_list(_list, -1)
 
     # def from_list(self, _list, current_index):
     #     self.version += 1