1 import os

     2 import sqlite

     3 

     4 def quote_sql(str):

     5     return str.replace("'", "''")

     6     

     7 class IMMSDb:

     8     def __init__(self, dbname = None):

     9         if not dbname:

    10             dbname = os.environ['HOME'] + '/.imms/imms.db'

    11         # autocommit = 1 disable autocommit!

    12         self.cx = sqlite.connect(dbname, autocommit = 1,

    13                                  timeout = 2, encoding = ('utf-8', 'replace'))

    14         self.cu = self.cx.cursor()

    15     def get_library_entries(self, **kw):

    16         qry = "SELECT path, uid, sid FROM Library";

    17         first = 1

    18         for key in kw.keys():

    19             if first:

    20                 qry += " WHERE "

    21                 first = 0

    22             else:

    23                 qry += " AND "

    24             if key in ['uid', 'sid']:

    25                 qry += "%s = %d" % (key, kw[key])

    26             else:

    27                 qry += "%s = '%s'" % (key, quote_sql(kw[key]))

    28         qry += ";"

    29         self.cu.execute(qry)

    30         return self.cu.fetchall()

    31     def update_filename(self, oldname, newname):

    32         self.cu.execute("""UPDATE Library

    33             SET path = '%s'

    34             WHERE path = '%s';""" % (quote_sql(newname),

    35                                      quote_sql(oldname)))

    36     def erase_filename(self, name):

    37         self.cu.execute("""DELETE FROM Library

    38             WHERE path = '%s';""" % quote_sql(name))

    39     def erase_uid(self, uid):

    40         self.cu.execute("""BEGIN TRANSACTION;

    41              DELETE FROM Library WHERE uid = %d;

    42              DELETE FROM Rating WHERE uid = %d;

    43              DELETE FROM Acoustic WHERE uid = %d;

    44              COMMIT;""" % (uid, uid, uid))

    45     def erase_sid(self, sid):

    46         self.cu.execute("""BEGIN TRANSACTION;

    47               DELETE FROM Library WHERE sid = %d;

    48               DELETE FROM Info WHERE sid = %d;

    49               DELETE FROM Last WHERE sid = %d;

    50               COMMIT;""")

    51     def erase_path(self, path):

    52         self.cu.execute("DELETE FROM Library WHERE path = '%s';" \

    53                         % quote_sql(path))

    54     def get_ratings(self, min = 0, max = 250):

    55         self.cu.execute('''SELECT Rating.uid, Rating.rating 

    56                    FROM Rating

    57                    WHERE Rating.rating >= %d

    58                    AND Rating.rating <= %d

    59                    ORDER BY Rating.rating;''' % (min, max))

    60         return cu.fetchall()

    61     def get_acoustics(self, uids = None):

    62         qry = "SELECT uid, bpm. spectrum FROM Acoustic"

    63         first = 1

    64         for uid in uids:

    65             if first:

    66                 qry += ' WHERE'

    67                 first = 0

    68             else:

    69                 qry += ' OR'

    70             qry += " uid = %d" % uid

    71         qry += ';'

    72         self.cu.execute(qry)

    73         return cu.fetchall()

    74     def get_infos(self, sids = None):

    75         qry = "SELECT sid, artist, title FROM Infos"

    76         first = 1

    77         for sid in sids:

    78             if first:

    79                 qry += ' WHERE'

    80                 first = 0

    81             else:

    82                 qry += ' OR'

    83             qry += " sid = %d" % id

    84         qry += ';'

    85         self.cu.execute(qry)

    86         return cu.fetchall()

    87     def get_last(self, sids = None):

    88         qry = "SELECT sid, last FROM Last"

    89         first = 1

    90         for sid in sids:

    91             if first:

    92                 qry += ' WHERE'

    93                 first = 0

    94             else:

    95                 qry += ' OR'

    96             qry += " sid = %d" % id

    97         qry += ';'

    98         self.cu.execute(qry)

    99         return cu.fetchall()

   100     def get_uid_by_path(self, path):

   101         entries = self.get_library_entries(path = path)

   102         return map(lambda x: x[1], entries)

   103     def get_ratings_and_info(self, uids = None):

   104         cu = self.cx.cursor()

   105         qry = '''SELECT l.uid, r.rating, l.path, ls.last

   106                    FROM Library l, Rating r, Last ls

   107                    WHERE l.uid = r.uid AND l.sid = ls.sid'''

   108         if uids:

   109             qry += ' AND (l.uid = %d' % (uids.pop())

   110             for uid in uids:

   111                 qry += ' OR l.uid = %d' % uid

   112             qry += ')'

   113         qry += ';'

   114         cu.execute(qry)

   115         # Better to fetch everything since locking can really mess

   116         # things in imms plugin.

   117 	results = {}

   118 	tune = cu.fetchone()

   119         while tune:

   120             try:

   121                 uid = int(tune[0])

   122                 if results.has_key(uid):

   123                     results[uid]['path'].append(

   124                         tune[2].decode('utf-8', 'replace'))

   125                 else:

   126                     results[uid] = {

   127                         'rating' : int(tune[1]),

   128                         'path' : [ tune[2].decode('utf-8', 'replace') ],

   129                         'last' : int(tune[3])}

   130             except UnicodeDecodeError:

   131                 print tune[2]

   132 	    tune = cu.fetchone()

   133         return results