Source

pythaules / pythaules / database / database.py

import xmlrpclib, sys, time, os.path
from threading import Thread

import sqlite3

import pythaules.common.file as file
from pythaules.conf import PythaulesConf as conf

class collectionDatabase(object):
    def __init__(self, path_to_db_file):
        self.__conn    = sqlite3.connect(path_to_db_file)
        self.__conn.text_factory = str
        self.__cursor = self.__conn.cursor()
        self._frontend_rpc = xmlrpclib.ServerProxy('http://%s:%d' % (conf.FRONTEND_RPC['host'], conf.FRONTEND_RPC['port']))
        # check if database is empty
        try:
            self.__cursor.execute("SELECT * FROM artist LIMIT 1")
        except sqlite3.OperationalError:
            # here, we assume that the table artist doesn't exist
            # so create database tables
            self.createEmptyDatabase()
        self.update_thread_running = False

    def _dispatch(self, method, params):
        try:
            return getattr(self, method)(*params)
        except (AttributeError, TypeError), msg:
            print "collectionDatabase._dispatch exception: ", msg
            return None
        
    def createEmptyDatabase(self):
        queries = []
        queries.append('''DROP TABLE IF EXISTS artist''')
        queries.append('''CREATE TABLE artist
                          (id   INTEGER PRIMARY KEY,
                           name TEXT
                          )''')
        queries.append('''CREATE INDEX name_index1 ON artist (name)''')
        queries.append('''DROP TABLE IF EXISTS album''')
        queries.append('''CREATE TABLE album
                          (id   INTEGER PRIMARY KEY,
                           name TEXT
                          )''')
        queries.append('''CREATE INDEX name_index2 ON album (name)''')
        queries.append('''DROP TABLE IF EXISTS song''')
        queries.append('''CREATE TABLE song
                          (id   INTEGER PRIMARY KEY,
                           name TEXT,
                           path TEXT,
                           filetype TEXT,
                           track_no INTEGER
                          )''')
        queries.append('''CREATE INDEX name_index3 ON song (name, path)''')
        queries.append('''DROP TABLE IF EXISTS artist_to_album''')
        queries.append('''CREATE TABLE artist_to_album
                          (artist_id INTEGER,
                           album_id  INTEGER
                          )''')
        queries.append('''CREATE INDEX index1 ON artist_to_album (artist_id, album_id)''')

        queries.append('''DROP TABLE IF EXISTS album_to_song''')
        queries.append('''CREATE TABLE album_to_song
                          (album_id  INTEGER,
                           song_id   INTEGER,
                           order_key INTEGER
                          )''')
        queries.append('''DROP TABLE IF EXISTS artist_to_song''')
        queries.append('''CREATE TABLE artist_to_song
                          (artist_id INTEGER,
                           song_id  INTEGER
                          )''')
        queries.append('''CREATE INDEX index2 ON artist_to_song (artist_id, song_id)''')
        queries.append('''DROP TABLE IF EXISTS variables''')
        queries.append('''CREATE TABLE variables
                          (var_name TEXT,
                           var_value TEXT
                          )''')
        queries.append('''INSERT INTO variables (var_name, var_value) VALUES ('last_update','0')''')
        for query in queries:
            self.__cursor.execute(query)    

        self.__conn.commit()

        self.last_update = 0

    def start_update_thread(self):
        print >> sys.stderr, "entering start_update_thread"
        sys.stderr.flush()
        if not self.update_thread_running:
            self.update_thread_running = True
            self.update_thread = Thread(target = self.fillDatabase(conf.MUSIC_DIRS))
            self.update_thread.start()
        print >> sys.stderr, "leaving start_update_thread"
        sys.stderr.flush()

    def _get_last_update(self):
        self.__cursor.execute('''SELECT var_value FROM variables WHERE var_name = 'last_update' ''')
        return int(self.__cursor.fetchall()[0][0])

    def _set_last_update(self, unixtime):
        self.__cursor.execute('''UPDATE variables SET var_value='%d' WHERE var_name = 'last_update' ''' % unixtime)
        self.__conn.commit()

    def fillDatabase(self, pathList):
        self.__filelist = []
        self.last_update = self._get_last_update()
        self._set_last_update(int(time.time()))


        print "* scanning directories..."
        
        for path in pathList:
            os.path.walk(path, self.__insert, None)

        print "* importing into database..."
     
        cnt = 1
        length = len(self.__filelist)
        old_artist = None
        old_album = None
        no_of_files = len(self.__filelist)
        percentage_done_old = -1
        for filename in self.__filelist:
            percentage_done = (100*cnt)/no_of_files
            if percentage_done > percentage_done_old:
                print "%d%% done" % percentage_done
                percentage_done_old = percentage_done
                #self._frontend_rpc.set_scan_pos(percentage_done)
            try:
                f = file.File(filename)
            except file.UnsupportedFiletypeException:
                print >> sys.stderr, "unsupported filetype: %s" % filename
            tags = f.getTags()
            if (len(tags)>0):
                if tags['artist'] != old_artist:
                    artist_id = self.__insertArtist(tags['artist'])
                    old_artist = tags['artist']
                if tags['album'] != old_album:
                    album_id  = self.__insertAlbum(tags['album'])
                    old_album = tags['album']
                song_id   = self.__insertSong(tags['title'], f.getFilename(), f.getFiletype(), tags['tracknumber'])
                self.__insertArtistToAlbum(artist_id, album_id)
                self.__insertArtistToSong(artist_id, song_id)
                self.__insertAlbumToSong(album_id, song_id)
            cnt += 1
        self.__conn.commit()

        #self._frontend_rpc.announce_scan_ready()
        #self.update_thread_running = False

    def search(self, filter_str):
        sql_filter_str = "%%%s%%" % str.replace(filter_str," ", "%")
        # drop old filtered id tables
        self.__cursor.execute('''DROP TABLE IF EXISTS filtered_artist_ids''')
        self.__cursor.execute('''DROP TABLE IF EXISTS filtered_album_ids''')
        self.__cursor.execute('''DROP TABLE IF EXISTS filtered_song_ids''')
        # step 1: fill filtered id tables
        q = '''CREATE TABLE filtered_artist_ids AS SELECT id FROM artist WHERE name LIKE '%s' ''' % sql_filter_str
        #print >> sys.stderr,"q:",q
        sys.stderr.flush()
        self.__cursor.execute(q)
        self.__cursor.execute('''CREATE TABLE filtered_album_ids AS SELECT id FROM album WHERE name LIKE '%s' ''' % sql_filter_str)
        self.__cursor.execute('''CREATE TABLE filtered_song_ids AS SELECT id FROM song WHERE name LIKE '%s' ''' % sql_filter_str)
        # step 2: fill filtered id tables with 'cross references'
        self.__cursor.execute('''SELECT id FROM filtered_artist_ids''')
        filtered_artist_ids = ",".join(["%d" % id_tuple[0] for id_tuple in self.__cursor.fetchall()])
        self.__cursor.execute('''SELECT id FROM filtered_album_ids''')
        filtered_album_ids = ",".join(["%d" % id_tuple[0] for id_tuple in self.__cursor.fetchall()])
        self.__cursor.execute('''SELECT id FROM filtered_song_ids''')
        filtered_song_ids = ",".join(["%d" % id_tuple[0] for id_tuple in self.__cursor.fetchall()])

        #print >> sys.stderr, "filtered_artist_ids:", filtered_artist_ids
        #print >> sys.stderr, "filtered_album_ids:", filtered_album_ids
        #print >> sys.stderr, "filtered_song_ids:", filtered_song_ids
        sys.stderr.flush()

        self.__cursor.execute('''REPLACE INTO filtered_artist_ids (id) SELECT artist_id FROM artist_to_album WHERE album_id in (%s)''' % filtered_album_ids)
        self.__cursor.execute('''REPLACE INTO filtered_artist_ids (id) SELECT artist_id FROM artist_to_song WHERE song_id in (%s)''' % filtered_song_ids)

        self.__cursor.execute('''REPLACE INTO filtered_album_ids (id) SELECT album_id FROM artist_to_album WHERE artist_id in (%s)''' % filtered_artist_ids)
        self.__cursor.execute('''REPLACE INTO filtered_album_ids (id) SELECT album_id FROM album_to_song WHERE song_id in (%s)''' % filtered_song_ids)

        self.__cursor.execute('''REPLACE INTO filtered_song_ids (id) SELECT song_id FROM artist_to_song WHERE artist_id in (%s)''' % filtered_artist_ids)
        self.__cursor.execute('''REPLACE INTO filtered_song_ids (id) SELECT song_id FROM album_to_song WHERE album_id in (%s)''' % filtered_album_ids)

        self.__conn.commit()

        ## <debug>
        #self.__cursor.execute('''SELECT id FROM filtered_artist_ids''')
        #filtered_artist_ids = ",".join(["%d" % id_tuple[0] for id_tuple in self.__cursor.fetchall()])
        #self.__cursor.execute('''SELECT id FROM filtered_album_ids''')
        #filtered_album_ids = ",".join(["%d" % id_tuple[0] for id_tuple in self.__cursor.fetchall()])
        #self.__cursor.execute('''SELECT id FROM filtered_song_ids''')
        #filtered_song_ids = ",".join(["%d" % id_tuple[0] for id_tuple in self.__cursor.fetchall()])

        #print >> sys.stderr, "filtered_artist_ids:", filtered_artist_ids
        #print >> sys.stderr, "filtered_album_ids:", filtered_album_ids
        #print >> sys.stderr, "filtered_song_ids:", filtered_song_ids
        #sys.stderr.flush()
        ## </debug>


    def __insert(self, arg, dirname, names):
        for filename in names:
            fullpath = os.path.join(dirname, filename)
            if (os.path.isfile(fullpath)):
                stat = os.stat(fullpath)
                stat_mtime = stat[8]
                if stat_mtime > self.last_update:
                    (prefix, suffix) = os.path.splitext(fullpath)
                    if (suffix in conf.ALLOWED_SUFFIXES):
                        self.__filelist.append(fullpath)

    def __insertArtist(self, name):
        self.__cursor.execute('''SELECT id FROM artist WHERE name=? LIMIT 1''', [name])
        rows = self.__cursor.fetchall()
        if (len(rows)>0):
            return rows[0][0]
        else:
            self.__cursor.execute('''INSERT INTO artist (name) VALUES (?)''', [name])
            return self.__cursor.lastrowid

    def __insertAlbum(self, name):
        self.__cursor.execute('''SELECT id FROM album WHERE name=? LIMIT 1''', [name])
        rows = self.__cursor.fetchall()
        if (len(rows)>0):
            return rows[0][0]
        else:
            self.__cursor.execute('''INSERT INTO album (name) VALUES (?)''', [name])
            return self.__cursor.lastrowid

    def __insertSong(self, name, path, filetype, tracknumber):
        #try:
            #self.__cursor.execute('''SELECT id FROM song WHERE name=? AND path=? LIMIT 1''', [name, path])
            #rows = self.__cursor.fetchall()
            #if (len(rows)>0):
            #    return rows[0][0]
            #else:
                self.__cursor.execute('''INSERT INTO song 
                                     (name, path, filetype, track_no) 
                                     VALUES (?,?,?,?)''', [name, path, filetype, tracknumber])
                return self.__cursor.lastrowid
        #except sqlite3.ProgrammingError, msg:
        #    print "name=", name, "path=", path
        #    print msg
        #    return 0

    def __insertArtistToAlbum(self, artist_id, album_id):
        self.__cursor.execute('''SELECT * FROM artist_to_album 
                             WHERE artist_id=? AND album_id=? 
                 LIMIT 1''', [artist_id, album_id])
        rows = self.__cursor.fetchall()
        if (len(rows) == 0):
            self.__cursor.execute('''INSERT INTO artist_to_album 
                                 (artist_id, album_id) 
                     VALUES (?,?)''', [artist_id, album_id])

    def __insertArtistToSong(self, artist_id, song_id):
        self.__cursor.execute('''SELECT * FROM artist_to_song 
                             WHERE artist_id=? AND song_id=? 
                 LIMIT 1''', [artist_id, song_id])
        rows = self.__cursor.fetchall()
        if (len(rows) == 0):
            self.__cursor.execute('''INSERT INTO artist_to_song 
                                 (artist_id, song_id) 
                     VALUES (?,?)''', [artist_id, song_id])

    def __insertAlbumToSong(self, album_id, song_id):
        self.__cursor.execute('''SELECT * FROM album_to_song
                             WHERE album_id=? AND song_id=? 
                 LIMIT 1''', [album_id, song_id])
        rows = self.__cursor.fetchall()
        if (len(rows) == 0):
            self.__cursor.execute('''INSERT INTO album_to_song 
                                 (album_id, song_id) 
                     VALUES (?,?)''', [album_id, song_id])

    def get_random_songs(self, number = 20):
        self.__cursor.execute('''SELECT * FROM song ORDER BY RANDOM() LIMIT ?''', [number])
        return self.__cursor.fetchall()

    def getAllArtists(self, filtered = False):
        if filtered:
            self.__cursor.execute('''SELECT id, name 
                                 FROM artist WHERE id IN (SELECT id FROM filtered_artist_ids)
                                 ORDER BY name ASC''')
        else:
            self.__cursor.execute('''SELECT id, name 
                                 FROM artist 
                                 ORDER BY name ASC''')
        result = self.__cursor.fetchall()        
        return result
        
    def getAlbumsForArtistId(self, id, filtered = False):
        if filtered:
            self.__cursor.execute('''SELECT a.id, a.name FROM album AS a
                                 WHERE a.id
                                 IN (SELECT id FROM filtered_album_ids)
                                 AND a.id IN (SELECT ata.album_id FROM artist_to_album AS ata
                                     WHERE ata.artist_id=?
                                    )
                                 ORDER BY a.name ASC''', [id])
        else:
            self.__cursor.execute('''SELECT a.id, a.name FROM album AS a
                                 WHERE a.id
                                 IN (SELECT ata.album_id FROM artist_to_album AS ata
                                     WHERE ata.artist_id=?
                                    )
                                 ORDER BY a.name ASC''', [id])
        result = self.__cursor.fetchall()        
        return result

    def getSongsForAlbumId(self, id, filtered = False):
        if filtered:
            self.__cursor.execute('''SELECT s.id, s.name FROM song AS s 
                                 WHERE s.id IN (SELECT id FROM filtered_song_ids) AND s.id
                                 IN (SELECT ats.song_id FROM album_to_song AS ats 
                                     WHERE ats.album_id=? 
                                    )
                                 ORDER BY s.track_no ASC''', [id])
        else:
             self.__cursor.execute('''SELECT s.id, s.name FROM song AS s 
                                 WHERE s.id 
                                 IN (SELECT ats.song_id FROM album_to_song AS ats 
                                     WHERE ats.album_id=? 
                                    )
                                 ORDER BY s.track_no ASC''', [id])
           
        result = self.__cursor.fetchall()
        return result

    def getSongsForAlbumIdAndArtistId(self, album_id, artist_id, filtered = False):
        if filtered:
            self.__cursor.execute('''SELECT s.id, s.name, s.path, s.filetype, s.track_no FROM song AS s 
                                 WHERE s.id IN (SELECT id FROM filtered_song_ids) AND s.id
                                 IN (SELECT ats.song_id FROM album_to_song AS ats
                                 JOIN artist_to_song AS arts
                                 ON (ats.song_id = arts.song_id)
                                 WHERE arts.artist_id=? AND ats.album_id=?
                                    )
                                 ORDER BY s.track_no ASC''', [artist_id, album_id])
        else:
            self.__cursor.execute('''SELECT s.id, s.name, s.path, s.filetype, s.track_no FROM song AS s 
                                 WHERE s.id 
                                 IN (SELECT ats.song_id FROM album_to_song AS ats
                     JOIN artist_to_song AS arts
                     ON (ats.song_id = arts.song_id)
                     WHERE arts.artist_id=? AND ats.album_id=?
                                    )
                                 ORDER BY s.track_no ASC''', [artist_id, album_id])

        result = self.__cursor.fetchall()
        return result
        
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.