1 import os |
1 import os.path |
|
2 from sys import stderr |
2 import sqlite |
3 import sqlite |
3 |
4 from utils import sql_quote, unique |
4 def quote_sql(str): |
5 |
5 return str.replace("'", "''") |
6 _log = stderr |
6 |
7 |
|
8 def rating_to_color(rating): |
|
9 i = rating - 75 |
|
10 red = green = blue = 0 |
|
11 if i <= 25: |
|
12 red = 255 |
|
13 green = i * 255 / 25 |
|
14 elif i <= 50: |
|
15 red = (50-i) * 255 / 25 |
|
16 green = 255 |
|
17 else: |
|
18 green = 255 |
|
19 blue = (i-50) * 255 / 25 |
|
20 return "#%02x%02x%02x" % (red, green, blue) |
|
21 |
7 class IMMSDb: |
22 class IMMSDb: |
8 def __init__(self, dbname = None): |
23 def __init__(self, dbname = None): |
9 if not dbname: |
24 if not dbname: |
10 dbname = os.environ['HOME'] + '/.imms/imms.db' |
25 dbname = os.environ['HOME'] + '/.imms/imms.db' |
11 # autocommit = 1 disable autocommit! |
26 # autocommit = 1 disable autocommit! |
12 self.cx = sqlite.connect(dbname, autocommit = 1, |
27 self.cx = sqlite.connect(dbname, autocommit = 1, timeout = 5) |
13 timeout = 2, encoding = ('utf-8', 'replace')) |
|
14 self.cu = self.cx.cursor() |
28 self.cu = self.cx.cursor() |
15 def get_library_entry(self): |
29 def get_library_entry(self, **kw): |
16 qry = "SELECT path, uid, sid FROM Library"; |
30 qry = "SELECT path, uid, sid FROM Library"; |
17 first = 1 |
31 first = 1 |
18 for key in kw.keys(): |
32 for key in kw.keys(): |
19 if first: |
33 if first: |
20 qry += " WHERE " |
34 qry += " WHERE " |
22 else: |
36 else: |
23 qry += " AND " |
37 qry += " AND " |
24 if key in ['uid', 'sid']: |
38 if key in ['uid', 'sid']: |
25 qry += "%s = %d" % (key, kw[key]) |
39 qry += "%s = %d" % (key, kw[key]) |
26 else: |
40 else: |
27 qry += "%s = '%s'" % (key, quote_sql(kw[key])) |
41 qry += "%s = '%s'" % (key, sql_quote(kw[key])) |
28 qry += ";" |
42 qry += ";" |
29 self.cu.execute(qry) |
43 self.cu.execute(qry) |
30 return self.cu.fetchall() |
44 return self.cu.fetchall() |
|
45 return res |
31 def update_filename(self, oldname, newname): |
46 def update_filename(self, oldname, newname): |
32 self.cu.execute("""UPDATE Library |
47 self.cu.execute("""UPDATE Library |
33 SET path = '%s' |
48 SET path = '%s' |
34 WHERE path = '%s';""" % (quote_sql(newname), |
49 WHERE path = '%s';""" % (sql_quote(newname), |
35 quote_sql(oldname))) |
50 sql_quote(oldname))) |
36 def erase_filename(self, name): |
51 def erase_filename(self, name): |
37 self.cu.execute("""DELETE FROM Library |
52 self.cu.execute("""DELETE FROM Library |
38 WHERE path = '%s';""" % quote_sql(name)) |
53 WHERE path = '%s';""" % sql_quote(name)) |
39 def erase_uid(self, uid): |
54 def erase_uid(self, uid): |
40 self.cu.execute("""BEGIN TRANSACTION; |
55 self.cu.execute("""BEGIN TRANSACTION; |
41 DELETE FROM Library WHERE uid = %d; |
56 DELETE FROM Library WHERE uid = %d; |
42 DELETE FROM Rating WHERE uid = %d; |
57 DELETE FROM Rating WHERE uid = %d; |
43 DELETE FROM Acoustic WHERE uid = %d; |
58 DELETE FROM Acoustic WHERE uid = %d; |
45 def erase_sid(self, sid): |
60 def erase_sid(self, sid): |
46 self.cu.execute("""BEGIN TRANSACTION; |
61 self.cu.execute("""BEGIN TRANSACTION; |
47 DELETE FROM Library WHERE sid = %d; |
62 DELETE FROM Library WHERE sid = %d; |
48 DELETE FROM Info WHERE sid = %d; |
63 DELETE FROM Info WHERE sid = %d; |
49 DELETE FROM Last WHERE sid = %d; |
64 DELETE FROM Last WHERE sid = %d; |
50 COMMIT;""") |
65 COMMIT;""" % (sid, sid, sid)) |
51 def erase_path(self, path): |
66 def erase_path(self, path): |
52 self.cu.execute("DELETE FROM Library WHERE path = '%s';" \ |
67 self.cu.execute("DELETE FROM Library WHERE path = '%s';" \ |
53 % quote_sql(path)) |
68 % sql_quote(path)) |
54 def get_paths(self, uids = None, sids = None): |
69 def get_paths(self, uids = None, sids = None): |
55 qry = "SELECT uid, sid, path FROM Library" |
70 qry = "SELECT uid, sid, path FROM Library" |
56 first = 1 |
71 first = 1 |
57 for uid in uids: |
72 for uid in uids: |
58 if first: |
73 if first: |
77 WHERE Rating.rating >= %d |
92 WHERE Rating.rating >= %d |
78 AND Rating.rating <= %d |
93 AND Rating.rating <= %d |
79 ORDER BY Rating.rating;''' % (min, max)) |
94 ORDER BY Rating.rating;''' % (min, max)) |
80 return self.cu.fetchall() |
95 return self.cu.fetchall() |
81 def get_acoustics(self, uids = None): |
96 def get_acoustics(self, uids = None): |
82 qry = "SELECT uid, bpm. spectrum FROM Acoustic" |
97 qry = "SELECT uid, bpm, spectrum FROM Acoustic" |
83 first = 1 |
98 first = 1 |
84 for uid in uids: |
99 if uids: |
85 if first: |
100 for uid in uids: |
86 qry += ' WHERE' |
101 if first: |
87 first = 0 |
102 qry += ' WHERE' |
88 else: |
103 first = 0 |
89 qry += ' OR' |
104 else: |
90 qry += " uid = %d" % uid |
105 qry += ' OR' |
|
106 qry += " uid = %d" % uid |
91 qry += ';' |
107 qry += ';' |
92 self.cu.execute(qry) |
108 self.cu.execute(qry) |
93 return self.cu.fetchall() |
109 return self.cu.fetchall() |
94 def get_infos(self, sids = None): |
110 def get_infos(self, sids = None): |
95 qry = "SELECT sid, artist, title FROM Infos" |
111 qry = "SELECT sid, artist, title FROM Info" |
96 first = 1 |
112 first = 1 |
97 for sid in sids: |
113 if sids: |
98 if first: |
114 for sid in sids: |
99 qry += ' WHERE' |
115 if first: |
100 first = 0 |
116 qry += ' WHERE' |
101 else: |
117 first = 0 |
102 qry += ' OR' |
118 else: |
103 qry += " sid = %d" % id |
119 qry += ' OR' |
|
120 qry += " sid = %d" % id |
104 qry += ';' |
121 qry += ';' |
105 self.cu.execute(qry) |
122 self.cu.execute(qry) |
106 return self.cu.fetchall() |
123 return self.cu.fetchall() |
107 def get_last(self, sids = None): |
124 def get_last(self, sids = None): |
108 qry = "SELECT sid, last FROM Last" |
125 qry = "SELECT sid, last FROM Last" |
109 first = 1 |
126 first = 1 |
110 for sid in sids: |
127 if sids: |
111 if first: |
128 for sid in sids: |
112 qry += ' WHERE' |
129 if first: |
113 first = 0 |
130 qry += ' WHERE' |
114 else: |
131 first = 0 |
115 qry += ' OR' |
132 else: |
116 qry += " sid = %d" % id |
133 qry += ' OR' |
|
134 qry += " sid = %d" % id |
117 qry += ';' |
135 qry += ';' |
118 self.cu.execute(qry) |
136 self.cu.execute(qry) |
119 return self.cu.fetchall() |
137 return self.cu.fetchall() |
120 def get_uid_by_path(self, path): |
138 def get_uid_by_path(self, path): |
121 entries = self.get_library_entries(path = path) |
139 entries = self.get_library_entry(path = path) |
122 return map(lambda x: x[1], entries) |
140 return map(lambda x: x[1], entries) |
123 def get_ratings_and_info(self, uids = None): |
141 def get_ratings_and_paths(self, uids = None): |
124 qry = '''SELECT l.uid, r.rating, l.path, ls.last |
142 qry = '''SELECT l.uid, r.rating, l.path, ls.last |
125 FROM Library l, Rating r, Last ls |
143 FROM Library l, Rating r, Last ls |
126 WHERE l.uid = r.uid AND l.sid = ls.sid''' |
144 WHERE l.uid = r.uid AND l.sid = ls.sid''' |
127 if uids: |
145 if uids: |
128 qry += ' AND (l.uid = %d' % (uids.pop()) |
146 qry += ' AND (l.uid = %d' % (uids.pop()) |
134 # Better to fetch everything since locking can really mess |
152 # Better to fetch everything since locking can really mess |
135 # things in imms plugin. |
153 # things in imms plugin. |
136 results = {} |
154 results = {} |
137 tune = self.cu.fetchone() |
155 tune = self.cu.fetchone() |
138 while tune: |
156 while tune: |
139 try: |
157 uid = int(tune[0]) |
140 uid = int(tune[0]) |
158 if results.has_key(uid): |
141 if results.has_key(uid): |
159 results[uid]['path'].append(tune[2]) |
142 results[uid]['path'].append( |
160 else: |
143 tune[2].decode('utf-8', 'replace')) |
161 results[uid] = { |
144 else: |
162 'rating' : int(tune[1]), |
145 results[uid] = { |
163 'path' : [ tune[2] ], |
146 'rating' : int(tune[1]), |
164 'last' : int(tune[3])} |
147 'path' : [ tune[2].decode('utf-8', 'replace') ], |
165 tune = self.cu.fetchone() |
148 'last' : int(tune[3])} |
|
149 except UnicodeDecodeError: |
|
150 print tune[2] |
|
151 tune = self.cu.fetchone() |
|
152 return results |
166 return results |
|
167 def get_ratings_and_infos(self): |
|
168 self.cu.execute('''SELECT r.rating, i.artist, i.title |
|
169 FROM Library l, Rating r, Info i |
|
170 WHERE l.uid = r.uid AND l.sid = i.sid;''') |
|
171 return self.cu.fetchall() |
|
172 |
|
173 class IMMSCleaner: |
|
174 def __init__(self, db): |
|
175 self.db = db |
|
176 def check_uid(self, uid): |
|
177 lib = self.db.get_library_entry(uid = uid) |
|
178 if len(lib) == 0: |
|
179 print >> _log, "Erased uid = ", uid |
|
180 self.db.erase_uid(uid) |
|
181 def check_sid(self, sid): |
|
182 lib = self.db.get_library_entry(sid = sid) |
|
183 if len(lib) == 0: |
|
184 print >> _log, "Erased sid = ", sid |
|
185 self.db.erase_sid(sid) |
|
186 def is_path_in_db(self, path): |
|
187 return len(self.db.get_library_entry(path = path)) |
|
188 # Note: I doesn't much how I handle the two following functions... |
|
189 # May be I must just have the second one and handle everything |
|
190 # else in the derived class. |
|
191 def check_and_edit_path(self, path, uid, sid): |
|
192 """Must return the new path, None to remove |
|
193 it. If the new file name is already in the Db, |
|
194 it will be skip. The skip is more efficient if path |
|
195 is return. |
|
196 This is the default handler which always skip the file by |
|
197 returning path directly. |
|
198 """ |
|
199 # The right thing (but not safe) would be to erase the |
|
200 # file if it already exist in the db... But I find it |
|
201 # too much unsafe... Erasing a file shouldn't be easy to |
|
202 # do. |
|
203 return path |
|
204 def clean_library(self): |
|
205 lib = self.db.get_library_entry() |
|
206 print >> _log, "Processing %d entries" % len(lib) |
|
207 deleted_uids = [] |
|
208 deleted_sids = [] |
|
209 for entry in lib: |
|
210 path, uid, sid = entry |
|
211 uid = int(uid) |
|
212 sid = int(sid) |
|
213 newfile = self.check_and_edit_path(path, uid, sid) |
|
214 if not newfile: |
|
215 print >> _log, "Erasing ", path |
|
216 self.db.erase_filename(path) |
|
217 deleted_uids.append(uid) |
|
218 deleted_sids.append(sid) |
|
219 elif (path == newfile): |
|
220 pass |
|
221 elif self.is_path_in_db(newfile): |
|
222 print >> _log, "Skipping ", path |
|
223 pass |
|
224 else: |
|
225 print >> _log, "Renaming ", path, " into ", newfile |
|
226 self.db.update_filename(path, newfile) |
|
227 map(self.check_uid, unique(deleted_uids)) |
|
228 map(self.check_sid, unique(deleted_sids)) |
|
229 def clean_rating(self): |
|
230 print >> _log, "Clean Rating" |
|
231 rates = self.db.get_ratings() |
|
232 rates = unique(map(lambda x: x[0], rates)) |
|
233 map(self.check_uid, rates) |
|
234 def clean_acoustic(self): |
|
235 print >> _log, "Clean Acoustic" |
|
236 uids = self.db.get_acoustics() |
|
237 uids = map(lambda x: x[0], uids ) |
|
238 map(self.check_uid, uids) |
|
239 def clean_info(self): |
|
240 print >> _log, "Clean Info" |
|
241 sids = map(lambda x: x[0], self.db.get_infos()) |
|
242 map(self.check_sid, sids) |
|
243 def clean_last(self): |
|
244 print >> _log, "Clean Last" |
|
245 sids = map(lambda x: x[0], self.db.get_last()) |
|
246 map(self.check_sid, sids) |
|
247 def clean_all(self): |
|
248 self.clean_library() |
|
249 self.clean_rating() |
|
250 self.clean_acoustic() |
|
251 self.clean_info() |
|
252 self.clean_last() |