#!/usr/bin/env python import os import os.path import ogg.vorbis import sys import types import urllib import time import mjukconf import ID3 madImported=False try: import mad madImported=True except ImportError: print "Didn't find the mad module so i can't guess how long mp3 tracks are" IsSpecialUser=False try: import pwd if pwd.getpwuid(os.getuid())[0]=="magnus": IsSpecialUser=True except ImportError: pass # import psycopg # db = psycopg.connect ("user=mjuk password=mjukis database=mjuk host=localhost") # c = db.cursor () # s = c.execute ("SELECT artistId FROM Artist WHERE name='Anything Box';") # c.rowcount # c.fetchall () # c.rowcount # Constants used to differentiate the DB-modules MYSQL = 0 POSTGRES = 1 class Importer: """import a directory tree into the database.""" def __init__(self,path): self.path=path self.count=0 if databasetype == 'postgresql': self.dbtype = POSTGRES import psycopg #db = PgSQL.connect (user="mjuk", password="mjukis", database="mjuk", host="localhost") self.db = psycopg.connect("user=%s password=%s dbname=%s host=%s"% (databaseuser, databasepwd, database, databasehost)) elif databasetype == 'mysql': self.dbtype = MYSQL import MySQLdb self.db=MySQLdb.connect(user=databaseuser,passwd=databasepwd, db=database,host=databasehost) else: print "You have somehow specified an incorrect databasetype." sys.exit (1) self.broken=[] self.previous={'artist':'', 'album':'', 'cddbId':'' } def import_dir(self): self.find_files(self.path) def URIExist(self, uri): c=self.db.cursor() numuri=c.execute("""SELECT URI FROM Track WHERE URI=%s""", (uri,)) if (not c.rowcount): c.close() return False else: c.close() return True def find_files(self,path): files=os.listdir(path) for filename in files: fullname=path+"/"+filename # print fullname; try: if (os.path.isdir(fullname)==1): #print fullname self.find_files(fullname) elif (self.URIExist(fullname)==False): ext=os.path.splitext(filename)[1] if (ext=='.ogg'): self.found_ogg(fullname) pass elif (ext=='.mp3'): self.found_mp3(fullname) pass elif (ext=='.mp2'): self.found_mp3(fullname) pass except: raise pass def insertGenre(self, genre, trackId, albumId=None): c=self.db.cursor() numGenre=c.execute("""SELECT genreId FROM Genre WHERE name=%s""", (genre.encode('utf-8'))) GenreId=None if (not c.rowcount): status=c.execute("""INSERT INTO Genre (name) values (%s)""", (genre.encode('utf-8'))) genreId=(c.lastrowid,) elif (c.rowcount==1): genreId=c.fetchone() else: print "Hmmmmm. To many (%d) genres. FIXME"%(numGenre) genreId=-1 exit status=c.execute("""INSERT INTO GenreMapping (genreId,referenceType,referenceId,userId) VALUES (%s,'track',%s,NULL)""", (genreId[0],trackId,)) if (albumId!=None): numAlbumGenre=c.execute("""SELECT genreId FROM GenreMapping WHERE """+ """ userId IS NULL AND referenceType='album' AND """+ """ genreId=%s AND referenceId=%s""", (genreId[0],albumId)) if (not c.rowcount): status=c.execute("""INSERT INTO GenreMapping """+ """(genreId,referenceType,referenceId,userId) VALUES """+ """(%s,'album',%s,NULL)""", (genreId[0],albumId,)) c.close() def findCddbId(self,cddbid): c=self.db.cursor() numResults=c.execute("""SELECT Album.albumId,Album.name,Record.recordId,Record.name """+ """FROM Record,Album WHERE """+ """Record.cddbId=%s and Record.albumId=Album.albumId""", (cddbid.encode('utf-8'),)) result=c.fetchall() c.close() return result def findArtists(self,artist): c=self.db.cursor() numArtist=c.execute("""SELECT artistId FROM Artist WHERE name=%s""", (artist.encode('utf-8'),)) result=c.fetchall() c.close() return result def findAlbums(self, album): c=self.db.cursor() numAlbum=c.execute("""SELECT albumId FROM Album WHERE name=%s""", (album.encode('utf-8'),)) result=c.fetchall() c.close() return result def insertArtist(self, artist): artists=self.findArtists(artist) if (len(artists)==0): c=self.db.cursor() status=c.execute("""INSERT INTO Artist (name) values (%s)""", (artist.encode('utf-8'),)) artistId=(c.lastrowid,) c.close() status="new" elif (len(artists)==1): artistId=artists[0] status="existed" else: print "Hmmmmm. To many (%d) artists. FIXME"%(numArtist) artistId=-1 status="many" return (artistId,status) def insertAlbum(self, album, artistId, year, create,collection): albums=self.findAlbums(album) if ((len(albums)==0) or create): c=self.db.cursor() status=c.execute("""INSERT INTO Album (name, copyrightYear,collection) values (%s, %s, %s)""", (album.encode('utf-8'), year,collection)) albumId=(c.lastrowid,) ### AlbumArtist status=c.execute("""INSERT INTO AlbumArtist (albumId,artistId) values (%s,%s)""", (albumId[0],artistId[0])) albumArtistId=(c.lastrowid,) c.close() elif (len(albums)==1): albumId=albums[0] else: print "Hmmmmm. To many (%d) albums. FIXME %s %s"%(len(albums),album,albums) albumId=(-1,) return albumId[0] def insertRecord(self, albumId, gain=None, peak=None, cddbId=None, vol=0): c=self.db.cursor() if (cddbId==None): numRecords=c.execute("""SELECT recordId FROM Record WHERE albumId=%s AND (volume=%s OR volume IS NULL) and cddbId is NULL""", (albumId, vol)) else: numRecords=c.execute("""SELECT recordId FROM Record WHERE albumId=%s AND (volume=%s OR volume IS NULL) AND cddbId=%s""", (albumId, vol,cddbId)) if (not c.rowcount): status=c.execute("""INSERT INTO Record (albumId,gain,peak,cddbId,volume) values (%s,%s,%s,%s,%s)""", (albumId, gain, peak, cddbId, vol)) recordId=(c.lastrowid,) elif (c.rowcount==1): recordId=c.fetchone() else: print "Hmmmmm. To many (%d) records. FIXME"%(numRecords) recordId=-1 c.close() return recordId def insertTrack(self, recordId, tracknumber, title, artistId, uri, length, year, fileTime, gain=None, peak=None, quality=None, vendor=None, version=None): c=self.db.cursor() numTracks=c.execute("""SELECT trackId FROM Track WHERE (recordId=%s and title=%s and trackno=%s)""", (recordId[0],title.encode('utf-8'),tracknumber)) if (not c.rowcount): d=self.db.cursor() numMime=d.execute("""SELECT mimeId FROM MimeType WHERE sub=%s""", (uri[-3:],)) if (d.rowcount==1): mimeId=d.fetchone()[0] else: mimeId=None status=c.execute("""INSERT INTO Track (recordId,trackno,title,URI,length,gain,peak,copyrightYear,mimeId,quality,codecVersion,codecVendor,fileTime) values (%s,%s,%s,%s,SEC_TO_TIME(%s),%s,%s,%s,%s,%s,%s,%s,%s)""", (recordId[0],tracknumber,title.encode('utf-8'), uri,length,gain,peak,year,mimeId,quality, version,vendor,fileTime)) trackId=(c.lastrowid,) ### TrackArtist status=c.execute("""INSERT INTO TrackArtist (trackId,artistId) values (%s,%s)""", (trackId[0],artistId[0])) trackArtistId=(c.lastrowid,) elif (c.rowcount == 1): trackId=c.fetchone() print "Warning!" print "The file %s uri conflicts with trackId %s."%(uri,trackId[0]) print "You may edit the tags in one of the files." print "The file %s is not imported."%(uri) print else: print "Hmmmmm. To many (%d) tracks. FIXME"%(numTracks) trackId=-1 c.close() return trackId def doInsert(self,items): try: if items['cddbId'] is not None: artistId,artistStatus=self.insertArtist(items['artist']) cddbid=self.findCddbId(items['cddbId']) if (len(cddbid)==0): albumId=self.insertAlbum(items['album'], artistId, items['year'], True, items['collection']) recordId=self.insertRecord(albumId, items['albumgain'], items['albumpeak'], items['cddbId'], items['vol']) trackId=self.insertTrack(recordId, items['tracknumber'], items['title'], artistId, items['uri'], items['length'], items['year'], items['modtime'], items['trackgain'], items['trackpeak'], items['quality'],items['vendor'], items['version']) elif (len(cddbid)==1): albumId=cddbid[0][0] recordId=(cddbid[0][2],) trackId=self.insertTrack(recordId, items['tracknumber'], items['title'], artistId, items['uri'], items['length'], items['year'], items['modtime'], items['trackgain'], items['trackpeak'], items['quality'], items['vendor'], items['version']) else: raise ValueError("Too many cddbid results: %s",cddbid) self.previous['artist']=items['artist'] self.previous['artistId']=artistId self.previous['album']=items['album'] self.previous['albumId']=albumId self.previous['cddbId']=items['cddbId'] elif (self.previous['album']==items['album']): if ((not self.previous['artist']==items['artist']) and (self.previous['cddbId']==items['cddbId'])): artistId,artistStatus=self.insertArtist(items['artist']) self.previous['artist']=items['artist'] self.previous['artistId']=artistId self.previous['cddbId']=items['cddbId'] artistId=self.previous['artistId'] albumId=self.previous['albumId'] recordId=self.insertRecord(albumId, items['albumgain'], items['albumpeak'], items['cddbId'], items['vol']) trackId=self.insertTrack(recordId, items['tracknumber'], items['title'], artistId, items['uri'], items['length'], items['year'], items['modtime'], items['trackgain'], items['trackpeak'], items['quality'], items['vendor'], items['version']) else: artistId,artistStatus=self.insertArtist(items['artist']) if (artistStatus=="new"): albumId=self.insertAlbum(items['album'], artistId, items['year'], True, items['collection']) elif (artistStatus=="existed"): albumId=self.insertAlbum(items['album'], artistId, items['year'], False, items['collection']) recordId=self.insertRecord(albumId, items['albumgain'], items['albumpeak'], items['cddbId'], items['vol']) trackId=self.insertTrack(recordId, items['tracknumber'], items['title'], artistId, items['uri'], items['length'], items['year'], items['modtime'], items['trackgain'], items['trackpeak'], items['quality'],items['vendor'], items['version']) self.previous['artist']=items['artist'] self.previous['artistId']=artistId self.previous['album']=items['album'] self.previous['albumId']=albumId self.previous['cddbId']=items['cddbId'] genre=items['genre'] if genre is not None: for i in genre: self.insertGenre(i,trackId[0],albumId) except: print items['uri'] self.db.rollback() raise self.count=self.count+1 self.db.commit() ### OGG import def found_ogg(self, name): items={} vf = ogg.vorbis.VorbisFile(name) vc = vf.comment() vi = vf.info() a=vc.as_dict() a['Channels']=vi.channels a['Rate']=vi.rate a['Type']='ogg' a['Filename']=name if (IsSpecialUser): print "**** ",name print a ### Artist try: items['artist']=a['ARTIST'][0] items['album']=a['ALBUM'][0] items['title']=a['TITLE'][0] items['uri']=a['Filename'] except KeyError: self.broken=self.broken+[name] return ### Album + AlbumArtist try: items['year']=a['DATE'][0] if (len(items['year'])==0): items['year']=None except KeyError: try: items['year']=a['YEAR'][0] if (len(items['year'])==0): items['year']=None except KeyError: items['year']=None try: items['albumgain']=a['REPLAYGAIN_ALBUM_GAIN'][0][0:-3] items['albumpeak']=a['REPLAYGAIN_ALBUM_PEAK'][0] if (len(items['albumgain'])==0): items['albumgain']=None if (len(items['albumpeak'])==0): items['albumpeak']=None except KeyError: items['albumgain']=None items['albumpeak']=None ### Record if (a.has_key('CDDISCID')): items['cddbId']=a['CDDISCID'][0] elif (a.has_key('cddiscid')): items['cddbId']=a['cddiscid'][0] elif (a.has_key('CDDISKID')): items['cddbId']=a['CDDISKID'][0] elif (a.has_key('cddiskid')): items['cddbId']=a['cddiskid'][0] elif (a.has_key('DISCID')): items['cddbId']=a['DISCID'][0] elif (a.has_key('discid')): items['cddbId']=a['discid'][0] elif (a.has_key('CDDB')): items['cddbId']=a['CDDB'][0] elif (a.has_key('cddb')): items['cddbId']=a['cddb'][0] else: print "name:",name items['cddbId']=None try: items['vol']=a['VOLUME'][0] except KeyError: items['vol']=0 ### Track + TrackArtist try: items['tracknumber']=a['TRACKNUMBER'][0] except KeyError: items['tracknumber']=0 try: items['trackgain']=a['REPLAYGAIN_TRACK_GAIN'][0][0:-3] items['trackpeak']=a['REPLAYGAIN_TRACK_PEAK'][0] if (len(items['trackgain'])==0): items['trackgain']=None if (len(items['trackpeak'])==0): items['trackpeak']=None except KeyError: items['trackgain']=None items['trackpeak']=None try: items['quality']=a['QUALITY'][0] except KeyError: items['quality']=None try: items['vendor']=a['VENDOR'][0] except KeyError: items['vendor']=None items['modtime']=time.strftime("%Y%m%d%H%M%S", time.localtime(os.stat(name).st_mtime)) items['length']=vf.time_total(-1) items['version']=vi.version items['collection']=False try: items['genre']=a['GENRE'] except KeyError: items['genre']=None self.doInsert(items) ### MP3 import def found_mp3(self, name): encoding=mjukconf.importClass().id3_encoding() items={} a = ID3.ID3(name) if len(a.items())==1: self.broken=self.broken+[name] return if (IsSpecialUser): print "#### ",name print a ### Artist try: items['artist']=unicode(a['ARTIST'],encoding) items['album']=unicode(a['ALBUM'],encoding) items['title']=unicode(a['TITLE'],encoding) items['uri']=name except KeyError: self.broken=self.broken+[name] return ### Record # empty for now... no multiple record handling implemented ### Track + TrackArtist try: items['tracknumber']=a['TRACKNUMBER'] except KeyError: items['tracknumber']=0 try: items['year']=a['YEAR'] except KeyError: items['year']=None try: items['genre']=a['GENRE'] except KeyError: itmes['genre']=None return if madImported: mf = mad.MadFile(name) items['length']=mf.total_time()/1000 items['modtime']=time.strftime("%Y%m%d%H%M%S", time.localtime(os.stat(name).st_mtime)) else: items['modtime']=None items['tracktime']=None items['albumgain']=None items['albumpeak']=None items['cddbId']=None items['vol']=0 items['trackgain']=None items['trackpeak']=None items['quality']=None items['vendor']=None items['version']=None items['collection']=False self.doInsert(items) if __name__ == '__main__': config=mjukconf.serverClass(); databaseuser = config.databaseuser() databasepwd = config.databasepassword() database = config.database() databasehost = config.databasehost() databasetype = config.databasetype() import_directories = mjukconf.importClass().import_directories() if (len(sys.argv)>1): import_directories=sys.argv[1:] for i in import_directories: a=Importer(i) a.import_dir() if not len(a.broken)==0: print "*************************************************************" print "** broken" print "" for i in a.broken: print i