1 import os.path |
1 import os.path |
2 from sys import stderr |
2 from sys import stderr |
3 import sqlite |
3 from pysqlite2 import dbapi2 as sqlite |
4 from utils import sql_quote, unique |
4 from utils import sql_quote, unique |
5 |
5 |
6 _log = stderr |
6 _log = stderr |
7 |
7 |
8 def rating_to_color(rating): |
8 def rating_to_color(rating): |
9 i = rating - 75 |
9 i = rating |
10 red = green = blue = 0 |
10 red = green = blue = 0 |
11 if i <= 25: |
11 if i <= 25: |
12 red = 255 |
12 red = 255 |
13 green = i * 255 / 25 |
13 green = i * 255 / 25 |
14 elif i <= 50: |
14 elif i <= 75: |
15 red = (50-i) * 255 / 25 |
15 red = (75-i) * 255 / 50 |
16 green = 255 |
16 green = 255 |
17 else: |
17 else: |
18 green = 255 |
18 green = 255 |
19 blue = (i-50) * 255 / 25 |
19 blue = (i-75) * 255 / 25 |
|
20 print >>stderr,"Rating:",rating,"R:",red,"G:",green,"B:",blue |
20 return "#%02x%02x%02x" % (red, green, blue) |
21 return "#%02x%02x%02x" % (red, green, blue) |
21 |
22 |
22 class IMMSDb: |
23 class IMMSDb: |
23 def __init__(self, dbname = None): |
24 def __init__(self, dbname = None): |
24 if not dbname: |
25 if not dbname: |
25 dbname = os.environ['HOME'] + '/.imms/imms.db' |
26 dbname = os.environ['HOME'] + '/.imms/imms2.db' |
26 # autocommit = 1 disable autocommit! |
27 # autocommit = 1 disable autocommit! |
27 self.cx = sqlite.connect(dbname, autocommit = 1, timeout = 5) |
28 self.cx = sqlite.connect(dbname, timeout = 5) |
28 self.cu = self.cx.cursor() |
29 self.cu = self.cx.cursor() |
29 def get_library_entry(self, **kw): |
30 def get_library_entry(self, **kw): |
30 qry = "SELECT path, uid, sid FROM Library"; |
31 qry = "SELECT path, l.uid, l.sid FROM Library l NATURAL INNER JOIN Identify" |
31 first = 1 |
32 first = 1 |
32 for key in kw.keys(): |
33 for key in kw.keys(): |
33 if first: |
34 if first: |
34 qry += " WHERE " |
35 qry += " WHERE " |
35 first = 0 |
36 first = 0 |
36 else: |
37 else: |
37 qry += " AND " |
38 qry += " AND " |
38 if key in ['uid', 'sid']: |
39 if key in ['uid', 'sid']: |
39 qry += "%s = %d" % (key, kw[key]) |
40 qry += "l.%s = %d" % (key, kw[key]) |
40 else: |
41 else: |
41 qry += "%s = '%s'" % (key, sql_quote(kw[key])) |
42 qry += "%s = '%s'" % (key, sql_quote(kw[key])) |
42 qry += ";" |
43 qry += ";" |
43 self.cu.execute(qry) |
44 self.cu.execute(qry) |
44 return self.cu.fetchall() |
45 return self.cu.fetchall() |
52 self.cu.execute("""DELETE FROM Library |
53 self.cu.execute("""DELETE FROM Library |
53 WHERE path = '%s';""" % sql_quote(name)) |
54 WHERE path = '%s';""" % sql_quote(name)) |
54 def erase_uid(self, uid): |
55 def erase_uid(self, uid): |
55 self.cu.execute("""BEGIN TRANSACTION; |
56 self.cu.execute("""BEGIN TRANSACTION; |
56 DELETE FROM Library WHERE uid = %d; |
57 DELETE FROM Library WHERE uid = %d; |
57 DELETE FROM Rating WHERE uid = %d; |
58 DELETE FROM Ratings WHERE uid = %d; |
58 DELETE FROM Acoustic WHERE uid = %d; |
59 DELETE FROM Acoustic WHERE uid = %d; |
59 COMMIT;""" % (uid, uid, uid)) |
60 COMMIT;""" % (uid, uid, uid)) |
60 def erase_sid(self, sid): |
61 def erase_sid(self, sid): |
61 self.cu.execute("""BEGIN TRANSACTION; |
62 self.cu.execute("""BEGIN TRANSACTION; |
62 DELETE FROM Library WHERE sid = %d; |
63 DELETE FROM Library WHERE sid = %d; |
85 qry += " sid = %d" % uid |
86 qry += " sid = %d" % uid |
86 qry += ';' |
87 qry += ';' |
87 self.cu.execute(qry) |
88 self.cu.execute(qry) |
88 return self.cu.fetchall() |
89 return self.cu.fetchall() |
89 def get_ratings(self, min = 0, max = 150): |
90 def get_ratings(self, min = 0, max = 150): |
90 self.cu.execute('''SELECT Rating.uid, Rating.rating |
91 self.cu.execute('''SELECT Ratings.uid, Ratings.rating |
91 FROM Rating |
92 FROM Ratings |
92 WHERE Rating.rating >= %d |
93 WHERE Ratings.rating >= %d |
93 AND Rating.rating <= %d |
94 AND Ratings.rating <= %d |
94 ORDER BY Rating.rating;''' % (min, max)) |
95 ORDER BY Ratings.rating;''' % (min, max)) |
95 return self.cu.fetchall() |
96 return self.cu.fetchall() |
96 def get_acoustics(self, uids = None): |
97 def get_acoustics(self, uids = None): |
97 qry = "SELECT uid, bpm, spectrum FROM Acoustic" |
98 qry = "SELECT uid, bpm, spectrum FROM Acoustic" |
98 first = 1 |
99 first = 1 |
99 if uids: |
100 if uids: |
149 res[sid] = [] |
150 res[sid] = [] |
150 for uid in self.cu.fetchall(): |
151 for uid in self.cu.fetchall(): |
151 res[sid].append(int(uid)) |
152 res[sid].append(int(uid)) |
152 return res |
153 return res |
153 def get_ratings_and_paths(self, uids = None): |
154 def get_ratings_and_paths(self, uids = None): |
154 qry = '''SELECT l.uid, r.rating, l.path, ls.last |
155 qry = '''SELECT i.uid, rating, path, last |
155 FROM Library l, Rating r, Last ls |
156 FROM Identify i NATURAL INNER JOIN Ratings NATURAL INNER JOIN Library NATURAL INNER JOIN Last''' |
156 WHERE l.uid = r.uid AND l.sid = ls.sid''' |
|
157 if uids: |
157 if uids: |
158 qry += ' AND (l.uid = %d' % (uids.pop()) |
158 qry += ' WHERE (i.uid = %d' % (uids.pop()) |
159 for uid in uids: |
159 for uid in uids: |
160 qry += ' OR l.uid = %d' % uid |
160 qry += ' OR i.uid = %d' % uid |
161 qry += ')' |
161 qry += ')' |
162 qry += ';' |
162 qry += ';' |
163 self.cu.execute(qry) |
163 self.cu.execute(qry) |
164 # Better to fetch everything since locking can really mess |
164 # Better to fetch everything since locking can really mess |
165 # things in imms plugin. |
165 # things in imms plugin. |
176 'last' : int(tune[3])} |
176 'last' : int(tune[3])} |
177 tune = self.cu.fetchone() |
177 tune = self.cu.fetchone() |
178 return results |
178 return results |
179 def get_ratings_and_infos(self): |
179 def get_ratings_and_infos(self): |
180 self.cu.execute('''SELECT r.rating, i.artist, i.title |
180 self.cu.execute('''SELECT r.rating, i.artist, i.title |
181 FROM Library l, Rating r, Info i |
181 FROM Library l, Ratings r, Info i |
182 WHERE l.uid = r.uid AND l.sid = i.sid;''') |
182 WHERE l.uid = r.uid AND l.sid = i.sid;''') |
183 return self.cu.fetchall() |
183 return self.cu.fetchall() |
184 def clean_info(self): |
184 def clean_info(self): |
185 self.cu.execute("""DELETE FROM Info |
185 self.cu.execute("""DELETE FROM Info |
186 WHERE sid NOT IN |
186 WHERE sid NOT IN |
188 def clean_last(self): |
188 def clean_last(self): |
189 self.cu.execute("""DELETE FROM Last |
189 self.cu.execute("""DELETE FROM Last |
190 WHERE sid NOT IN |
190 WHERE sid NOT IN |
191 (SELECT sid FROM Library);""") |
191 (SELECT sid FROM Library);""") |
192 def clean_rating(self): |
192 def clean_rating(self): |
193 self.cu.execute("""DELETE FROM Rating |
193 self.cu.execute("""DELETE FROM Ratings |
194 WHERE uid NOT IN |
194 WHERE uid NOT IN |
195 (SELECT uid FROM Library);""") |
195 (SELECT uid FROM Library);""") |
196 def clean_acoustic(self): |
196 def clean_acoustic(self): |
197 self.cu.execute("""DELETE FROM Acoustic |
197 self.cu.execute("""DELETE FROM Acoustic |
198 WHERE uid NOT IN |
198 WHERE uid NOT IN |
265 print >> _log, "Renaming ", path, " into ", newfile |
265 print >> _log, "Renaming ", path, " into ", newfile |
266 self.db.update_filename(path, newfile) |
266 self.db.update_filename(path, newfile) |
267 map(self.check_uid, unique(deleted_uids)) |
267 map(self.check_uid, unique(deleted_uids)) |
268 map(self.check_sid, unique(deleted_sids)) |
268 map(self.check_sid, unique(deleted_sids)) |
269 ## def clean_rating(self): |
269 ## def clean_rating(self): |
270 ## print >> _log, "Clean Rating" |
270 ## print >> _log, "Clean Ratings" |
271 ## rates = self.db.get_ratings() |
271 ## rates = self.db.get_ratings() |
272 ## rates = unique(map(lambda x: x[0], rates)) |
272 ## rates = unique(map(lambda x: x[0], rates)) |
273 ## map(self.check_uid, rates) |
273 ## map(self.check_uid, rates) |
274 ## def clean_acoustic(self): |
274 ## def clean_acoustic(self): |
275 ## print >> _log, "Clean Acoustic" |
275 ## print >> _log, "Clean Acoustic" |