import os
import sqlite

def quote_sql(str):
    return str.replace("'", "''")
    
class IMMSDb:
    def __init__(self, dbname = None):
        if not dbname:
            dbname = os.environ['HOME'] + '/.imms/imms.db'
        # autocommit = 1 disable autocommit!
        self.cx = sqlite.connect(dbname, autocommit = 1,
                                 timeout = 2, encoding = ('utf-8', 'replace'))
        self.cu = self.cx.cursor()
    def get_library_entry(self):
        qry = "SELECT path, uid, sid FROM Library";
        first = 1
        for key in kw.keys():
            if first:
                qry += " WHERE "
                first = 0
            else:
                qry += " AND "
            if key in ['uid', 'sid']:
                qry += "%s = %d" % (key, kw[key])
            else:
                qry += "%s = '%s'" % (key, quote_sql(kw[key]))
        qry += ";"
        self.cu.execute(qry)
        return self.cu.fetchall()
    def update_filename(self, oldname, newname):
        self.cu.execute("""UPDATE Library
            SET path = '%s'
            WHERE path = '%s';""" % (quote_sql(newname),
                                     quote_sql(oldname)))
    def erase_filename(self, name):
        self.cu.execute("""DELETE FROM Library
            WHERE path = '%s';""" % quote_sql(name))
    def erase_uid(self, uid):
        self.cu.execute("""BEGIN TRANSACTION;
             DELETE FROM Library WHERE uid = %d;
             DELETE FROM Rating WHERE uid = %d;
             DELETE FROM Acoustic WHERE uid = %d;
             COMMIT;""" % (uid, uid, uid))
    def erase_sid(self, sid):
        self.cu.execute("""BEGIN TRANSACTION;
              DELETE FROM Library WHERE sid = %d;
              DELETE FROM Info WHERE sid = %d;
              DELETE FROM Last WHERE sid = %d;
              COMMIT;""")
    def erase_path(self, path):
        self.cu.execute("DELETE FROM Library WHERE path = '%s';" \
                        % quote_sql(path))
    def get_paths(self, uids = None, sids = None):
        qry = "SELECT uid, sid, path FROM Library"
        first = 1
        for uid in uids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " uid = %d" % uid
        for uid in uids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " sid = %d" % uid
        qry += ';'
        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))
        return self.cu.fetchall()
    def get_acoustics(self, uids = None):
        qry = "SELECT uid, bpm. spectrum FROM Acoustic"
        first = 1
        for uid in uids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " uid = %d" % uid
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()
    def get_infos(self, sids = None):
        qry = "SELECT sid, artist, title FROM Infos"
        first = 1
        for sid in sids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " sid = %d" % id
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()
    def get_last(self, sids = None):
        qry = "SELECT sid, last FROM Last"
        first = 1
        for sid in sids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " sid = %d" % id
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()
    def get_uid_by_path(self, path):
        entries = self.get_library_entries(path = path)
        return map(lambda x: x[1], entries)
    def get_ratings_and_info(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'''
        if uids:
            qry += ' AND (l.uid = %d' % (uids.pop())
            for uid in uids:
                qry += ' OR l.uid = %d' % uid
            qry += ')'
        qry += ';'
        self.cu.execute(qry)
        # Better to fetch everything since locking can really mess
        # things in imms plugin.
	results = {}
	tune = self.cu.fetchone()
        while tune:
            try:
                uid = int(tune[0])
                if results.has_key(uid):
                    results[uid]['path'].append(
                        tune[2].decode('utf-8', 'replace'))
                else:
                    results[uid] = {
                        'rating' : int(tune[1]),
                        'path' : [ tune[2].decode('utf-8', 'replace') ],
                        'last' : int(tune[3])}
            except UnicodeDecodeError:
                print tune[2]
	    tune = self.cu.fetchone()
        return results