10 dbname = os.environ['HOME'] + '/.imms/imms.db' |
10 dbname = os.environ['HOME'] + '/.imms/imms.db' |
11 # autocommit = 1 disable autocommit! |
11 # autocommit = 1 disable autocommit! |
12 self.cx = sqlite.connect(dbname, autocommit = 1, |
12 self.cx = sqlite.connect(dbname, autocommit = 1, |
13 timeout = 2, encoding = ('utf-8', 'replace')) |
13 timeout = 2, encoding = ('utf-8', 'replace')) |
14 self.cu = self.cx.cursor() |
14 self.cu = self.cx.cursor() |
15 def get_library_entries(self, **kw): |
15 def get_library_entry(self): |
16 qry = "SELECT path, uid, sid FROM Library"; |
16 qry = "SELECT path, uid, sid FROM Library"; |
17 first = 1 |
17 first = 1 |
18 for key in kw.keys(): |
18 for key in kw.keys(): |
19 if first: |
19 if first: |
20 qry += " WHERE " |
20 qry += " WHERE " |
49 DELETE FROM Last WHERE sid = %d; |
49 DELETE FROM Last WHERE sid = %d; |
50 COMMIT;""") |
50 COMMIT;""") |
51 def erase_path(self, path): |
51 def erase_path(self, path): |
52 self.cu.execute("DELETE FROM Library WHERE path = '%s';" \ |
52 self.cu.execute("DELETE FROM Library WHERE path = '%s';" \ |
53 % quote_sql(path)) |
53 % quote_sql(path)) |
54 def get_ratings(self, min = 0, max = 250): |
54 def get_paths(self, uids = None, sids = None): |
|
55 qry = "SELECT uid, sid, path FROM Library" |
|
56 first = 1 |
|
57 for uid in uids: |
|
58 if first: |
|
59 qry += ' WHERE' |
|
60 first = 0 |
|
61 else: |
|
62 qry += ' OR' |
|
63 qry += " uid = %d" % uid |
|
64 for uid in uids: |
|
65 if first: |
|
66 qry += ' WHERE' |
|
67 first = 0 |
|
68 else: |
|
69 qry += ' OR' |
|
70 qry += " sid = %d" % uid |
|
71 qry += ';' |
|
72 self.cu.execute(qry) |
|
73 return self.cu.fetchall() |
|
74 def get_ratings(self, min = 0, max = 150): |
55 self.cu.execute('''SELECT Rating.uid, Rating.rating |
75 self.cu.execute('''SELECT Rating.uid, Rating.rating |
56 FROM Rating |
76 FROM Rating |
57 WHERE Rating.rating >= %d |
77 WHERE Rating.rating >= %d |
58 AND Rating.rating <= %d |
78 AND Rating.rating <= %d |
59 ORDER BY Rating.rating;''' % (min, max)) |
79 ORDER BY Rating.rating;''' % (min, max)) |
60 return cu.fetchall() |
80 return self.cu.fetchall() |
61 def get_acoustics(self, uids = None): |
81 def get_acoustics(self, uids = None): |
62 qry = "SELECT uid, bpm. spectrum FROM Acoustic" |
82 qry = "SELECT uid, bpm. spectrum FROM Acoustic" |
63 first = 1 |
83 first = 1 |
64 for uid in uids: |
84 for uid in uids: |
65 if first: |
85 if first: |
68 else: |
88 else: |
69 qry += ' OR' |
89 qry += ' OR' |
70 qry += " uid = %d" % uid |
90 qry += " uid = %d" % uid |
71 qry += ';' |
91 qry += ';' |
72 self.cu.execute(qry) |
92 self.cu.execute(qry) |
73 return cu.fetchall() |
93 return self.cu.fetchall() |
74 def get_infos(self, sids = None): |
94 def get_infos(self, sids = None): |
75 qry = "SELECT sid, artist, title FROM Infos" |
95 qry = "SELECT sid, artist, title FROM Infos" |
76 first = 1 |
96 first = 1 |
77 for sid in sids: |
97 for sid in sids: |
78 if first: |
98 if first: |
94 else: |
114 else: |
95 qry += ' OR' |
115 qry += ' OR' |
96 qry += " sid = %d" % id |
116 qry += " sid = %d" % id |
97 qry += ';' |
117 qry += ';' |
98 self.cu.execute(qry) |
118 self.cu.execute(qry) |
99 return cu.fetchall() |
119 return self.cu.fetchall() |
100 def get_uid_by_path(self, path): |
120 def get_uid_by_path(self, path): |
101 entries = self.get_library_entries(path = path) |
121 entries = self.get_library_entries(path = path) |
102 return map(lambda x: x[1], entries) |
122 return map(lambda x: x[1], entries) |
103 def get_ratings_and_info(self, uids = None): |
123 def get_ratings_and_info(self, uids = None): |
104 cu = self.cx.cursor() |
|
105 qry = '''SELECT l.uid, r.rating, l.path, ls.last |
124 qry = '''SELECT l.uid, r.rating, l.path, ls.last |
106 FROM Library l, Rating r, Last ls |
125 FROM Library l, Rating r, Last ls |
107 WHERE l.uid = r.uid AND l.sid = ls.sid''' |
126 WHERE l.uid = r.uid AND l.sid = ls.sid''' |
108 if uids: |
127 if uids: |
109 qry += ' AND (l.uid = %d' % (uids.pop()) |
128 qry += ' AND (l.uid = %d' % (uids.pop()) |
110 for uid in uids: |
129 for uid in uids: |
111 qry += ' OR l.uid = %d' % uid |
130 qry += ' OR l.uid = %d' % uid |
112 qry += ')' |
131 qry += ')' |
113 qry += ';' |
132 qry += ';' |
114 cu.execute(qry) |
133 self.cu.execute(qry) |
115 # Better to fetch everything since locking can really mess |
134 # Better to fetch everything since locking can really mess |
116 # things in imms plugin. |
135 # things in imms plugin. |
117 results = {} |
136 results = {} |
118 tune = cu.fetchone() |
137 tune = self.cu.fetchone() |
119 while tune: |
138 while tune: |
120 try: |
139 try: |
121 uid = int(tune[0]) |
140 uid = int(tune[0]) |
122 if results.has_key(uid): |
141 if results.has_key(uid): |
123 results[uid]['path'].append( |
142 results[uid]['path'].append( |
127 'rating' : int(tune[1]), |
146 'rating' : int(tune[1]), |
128 'path' : [ tune[2].decode('utf-8', 'replace') ], |
147 'path' : [ tune[2].decode('utf-8', 'replace') ], |
129 'last' : int(tune[3])} |
148 'last' : int(tune[3])} |
130 except UnicodeDecodeError: |
149 except UnicodeDecodeError: |
131 print tune[2] |
150 print tune[2] |
132 tune = cu.fetchone() |
151 tune = self.cu.fetchone() |
133 return results |
152 return results |