6 class DatabaseError(Exception):
10 class SettingsDatabase:
13 def __init__(self, db_path):
14 self.db_path = db_path
17 conn = sqlite3.connect(self.db_path)
19 self.db_version_check_and_create()
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()
33 def db_version_check_and_create(self):
34 conn = sqlite3.connect(self.db_path)
35 cursor = conn.cursor()
39 result = cursor.execute("SELECT value FROM botamusique WHERE section=? AND option=?",
40 ("bot", "db_version")).fetchall()
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)
47 self.set("bot", "old_db_name", old_name)
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 ("
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"))
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()
77 if 'fallback' in kwargs:
78 return kwargs['fallback']
80 raise DatabaseError("Item not found")
82 def getboolean(self, section, option, **kwargs):
83 return bool(int(self.get(section, option, **kwargs)))
85 def getfloat(self, section, option, **kwargs):
86 return float(self.get(section, option, **kwargs))
88 def getint(self, section, option, **kwargs):
89 return int(self.get(section, option, **kwargs))
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))
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()
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))
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, ))
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()
130 return list(map(lambda v: (v[0], v[1]), results))
134 def drop_table(self):
135 conn = sqlite3.connect(self.db_path)
136 cursor = conn.cursor()
137 cursor.execute("DROP TABLE botamusique")
142 def __init__(self, db_path):
143 self.db_path = db_path
146 conn = sqlite3.connect(self.db_path)
147 cursor = conn.cursor()
149 # check if table exists, or create one
150 cursor.execute("CREATE TABLE IF NOT EXISTS music ("
151 "id TEXT PRIMARY KEY, "
159 def insert_music(self, music_dict):
160 conn = sqlite3.connect(self.db_path)
161 cursor = conn.cursor()
163 id = music_dict['id']
164 title = music_dict['title']
165 type = music_dict['type']
166 tags = ",".join(music_dict['tags']) + ","
169 del music_dict['title']
170 del music_dict['type']
171 del music_dict['tags']
173 cursor.execute("INSERT OR REPLACE INTO music (id, type, title, metadata, tags) VALUES (?, ?, ?, ?, ?)",
177 json.dumps(music_dict),
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()
188 return list(map(lambda i: i[0], results))
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()
195 for result in results:
196 for tag in result[0].strip(",").split(","):
197 if tag and tag not in tags:
202 def query_music(self, **kwargs):
206 for key, value in kwargs.items():
207 if isinstance(value, str):
208 condition.append(key + "=?")
210 elif isinstance(value, dict):
211 condition.append(key + " " + value[0] + " ?")
212 filler.append(value[1])
214 condition_str = " AND ".join(condition)
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()
222 return self._result_to_dict(results)
224 def query_music_by_keywords(self, keywords):
228 for keyword in keywords:
229 condition.append('LOWER(title) LIKE ?')
230 filler.append("%{:s}%".format(keyword.lower()))
232 condition_str = " AND ".join(condition)
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()
240 return self._result_to_dict(results)
242 def query_music_by_tags(self, tags):
247 condition.append('LOWER(tags) LIKE ?')
248 filler.append("%{:s},%".format(tag.lower()))
250 condition_str = " AND ".join(condition)
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()
258 return self._result_to_dict(results)
260 def query_tags_by_ids(self, ids):
261 conn = sqlite3.connect(self.db_path)
262 cursor = conn.cursor()
265 for i in range(int(len(ids)/990) + 1):
266 condition_str = " OR ".join(['id=?'] * min(990, len(ids) - i*990))
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()
272 results.extend(_results)
278 for result in results:
280 tags = result[1].strip(",").split(",")
281 lookup[id] = tags if tags[0] else []
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()
292 return self._result_to_dict(results)
294 def _result_to_dict(self, results):
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'] = []
306 music_dicts.append(music_dict)
312 def delete_music(self, **kwargs):
316 for key, value in kwargs.items():
317 if isinstance(value, str):
318 condition.append(key + "=?")
321 condition.append(key + " " + value[0] + " ?")
322 filler.append(value[1])
324 condition_str = " AND ".join(condition)
326 conn = sqlite3.connect(self.db_path)
327 cursor = conn.cursor()
328 cursor.execute("DELETE FROM music "
329 "WHERE %s" % condition_str, filler)
333 def drop_table(self):
334 conn = sqlite3.connect(self.db_path)
335 cursor = conn.cursor()
336 cursor.execute("DROP TABLE music")