--- a/imms.py	Sat Apr 03 13:00:44 2004 -0500
+++ b/imms.py	Sun Dec 07 16:05:33 2008 -0500
@@ -1,33 +1,34 @@
 import os.path
 from sys import stderr
-import sqlite
+from pysqlite2 import dbapi2 as sqlite
 from utils import sql_quote, unique
 
 _log = stderr
 
 def rating_to_color(rating):
-    i = rating - 75
+    i = rating
     red = green = blue = 0
     if i <= 25:
         red = 255
         green = i * 255 / 25
-    elif i <= 50:
-        red = (50-i) * 255 / 25
+    elif i <= 75:
+        red = (75-i) * 255 / 50
         green = 255
     else:
         green = 255
-        blue = (i-50) * 255 / 25
+        blue = (i-75) * 255 / 25
+    print >>stderr,"Rating:",rating,"R:",red,"G:",green,"B:",blue
     return "#%02x%02x%02x" % (red, green, blue)
 
 class IMMSDb:
     def __init__(self, dbname = None):
         if not dbname:
-            dbname = os.environ['HOME'] + '/.imms/imms.db'
+            dbname = os.environ['HOME'] + '/.imms/imms2.db'
         # autocommit = 1 disable autocommit!
-        self.cx = sqlite.connect(dbname, autocommit = 1, timeout = 5)
+        self.cx = sqlite.connect(dbname, timeout = 5)
         self.cu = self.cx.cursor()
     def get_library_entry(self, **kw):
-        qry = "SELECT path, uid, sid FROM Library";
+        qry = "SELECT path, l.uid, l.sid FROM Library l NATURAL INNER JOIN Identify"
         first = 1
         for key in kw.keys():
             if first:
@@ -36,7 +37,7 @@
             else:
                 qry += " AND "
             if key in ['uid', 'sid']:
-                qry += "%s = %d" % (key, kw[key])
+                qry += "l.%s = %d" % (key, kw[key])
             else:
                 qry += "%s = '%s'" % (key, sql_quote(kw[key]))
         qry += ";"
@@ -54,7 +55,7 @@
     def erase_uid(self, uid):
         self.cu.execute("""BEGIN TRANSACTION;
              DELETE FROM Library WHERE uid = %d;
-             DELETE FROM Rating WHERE uid = %d;
+             DELETE FROM Ratings WHERE uid = %d;
              DELETE FROM Acoustic WHERE uid = %d;
              COMMIT;""" % (uid, uid, uid))
     def erase_sid(self, sid):
@@ -87,11 +88,11 @@
         self.cu.execute(qry)
         return self.cu.fetchall()        
     def get_ratings(self, min = 0, max = 150):
-        self.cu.execute('''SELECT Rating.uid, Rating.rating 
-                   FROM Rating
-                   WHERE Rating.rating >= %d
-                   AND Rating.rating <= %d
-                   ORDER BY Rating.rating;''' % (min, max))
+        self.cu.execute('''SELECT Ratings.uid, Ratings.rating 
+                   FROM Ratings
+                   WHERE Ratings.rating >= %d
+                   AND Ratings.rating <= %d
+                   ORDER BY Ratings.rating;''' % (min, max))
         return self.cu.fetchall()
     def get_acoustics(self, uids = None):
         qry = "SELECT uid, bpm, spectrum FROM Acoustic"
@@ -151,13 +152,12 @@
                 res[sid].append(int(uid))
         return res
     def get_ratings_and_paths(self, uids = None):
-        qry = '''SELECT l.uid, r.rating, l.path, ls.last
-               FROM Library l, Rating r, Last ls
-               WHERE l.uid = r.uid AND l.sid = ls.sid'''
+        qry = '''SELECT i.uid, rating, path, last
+               FROM Identify i NATURAL INNER JOIN Ratings NATURAL INNER JOIN Library NATURAL INNER JOIN Last'''
         if uids:
-            qry += ' AND (l.uid = %d' % (uids.pop())
+            qry += ' WHERE (i.uid = %d' % (uids.pop())
             for uid in uids:
-                qry += ' OR l.uid = %d' % uid
+                qry += ' OR i.uid = %d' % uid
             qry += ')'
         qry += ';'
         self.cu.execute(qry)
@@ -178,7 +178,7 @@
         return results
     def get_ratings_and_infos(self):
         self.cu.execute('''SELECT r.rating, i.artist, i.title
-            FROM Library l, Rating r, Info i
+            FROM Library l, Ratings r, Info i
             WHERE l.uid = r.uid AND l.sid = i.sid;''')
         return self.cu.fetchall()
     def clean_info(self):
@@ -190,7 +190,7 @@
             WHERE sid NOT IN
             (SELECT sid FROM Library);""")
     def clean_rating(self):
-        self.cu.execute("""DELETE FROM Rating
+        self.cu.execute("""DELETE FROM Ratings
             WHERE uid NOT IN
             (SELECT uid FROM Library);""")
     def clean_acoustic(self):
@@ -267,7 +267,7 @@
         map(self.check_uid, unique(deleted_uids))
         map(self.check_sid, unique(deleted_sids))
 ##     def clean_rating(self):
-## 	print >> _log, "Clean Rating"
+## 	print >> _log, "Clean Ratings"
 ##         rates = self.db.get_ratings()
 ##         rates = unique(map(lambda x: x[0], rates))
 ##         map(self.check_uid, rates)