comparison pyspark/ilm/assetDB.py @ 0:e34cf1b6fe09 tip

commit

+# Part of DML (Digital Music Laboratory)
+#
+# This program is free software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 2
+# of the License, or (at your option) any later version.
+# 
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+# 
+# You should have received a copy of the GNU General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
+
+#!/usr/bin/env python
+# encoding: utf-8
+"""
+assetDB.py
+
+Created by George Fazekas on 2012-01-16. Modifications by Mathieu Barthet in 2013-12,
+Steven Hargreaves 22/12/2014.
+Copyright (c) 2013 . All rights reserved.
+"""
+
+import sys,os,logging
+import sqlalchemy as sal
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy import Column, Integer, String, Sequence, Enum
+from sqlalchemy.orm import sessionmaker
+from sqlalchemy.dialects import mysql
+from hashlib import md5
+
+class assetDB(object):
+
+    asset_types = ['wav','mpeg/320kbps','mpeg/64kbps']
+    extensions = ['wav','mp3','mp3']
+    ext = dict(zip(asset_types,extensions))
+
+    def __init__(self, prefix, pref=list(), config=None):
+        self.log = logging.getLogger('spark_feat_extract')
+        self.log.info("ORM Version: %s",sal.__version__)
+        self.config = config
+        self.session = None
+        self.Assets = None
+        self.prefix = prefix
+        if pref :
+            self.asset_prefs = pref
+        else :
+            self.asset_prefs = assetDB.asset_types
+        # reporting errors:
+        self.found_different_asset_type = 0
+        self.errata_file = None
+        if config and hasattr(config,"db_errata_file") :
+            self.errata_file = config.db_errata_file
+
+
+    def connect(self,echo=False):
+        '''Connect to the MySQL database and create a session.'''
+        URL = "mysql://%s:%s@%s/%s" %(self.config.get('Commercial Asset Database', 'user'),self.config.get('Commercial Asset Database', 'passwd'),self.config.get('Commercial Asset Database', 'host'),self.config.get('Commercial Asset Database', 'name'))
+        self.log.info("Connecting to database server at: %s",URL.replace(self.config.get('Commercial Asset Database', 'passwd'),'*****'))
+        engine=sal.create_engine(URL, echo=echo)
+        Session = sessionmaker(bind=engine)
+        self.session = Session()
+        self.log.debug("MySQL session created successfully.")
+        return self
+
+    def close(self):
+        '''Close the database session'''
+        if self.session :
+            self.session.close()
+            self.log.info("Database closed.")
+        return self
+
+    def create_mapper(self):
+        '''Create an Object-Relational Mapper'''
+        Base = declarative_base()
+        class Assets(Base):
+            #change
+            #__tablename__ = 'assets'
+            __tablename__ = self.config.get('Commercial Asset Database', 'tablename')
+            # map all table columns to variables here, e.g.
+            # album_id = Column(Integer, primary_key=True)
+            # song_title = Column(String)
+            # genre_id = Column(Integer)
+        self.Assets = Assets
+        return self
+
+    def get_assets(self,start=0,limit=10,asset_type='audio/x-wav'):
+        '''Returns some assets from the database.
+        If the path given by the specified asset type does not exists, 
+        try to find the assets given the preference list provided in self.asset_prefs.
+        If no valid path can be found for an asset, log the error and yield None for the path.'''
+        # limit = start + limit # this changes the semantics of the SQL limit
+
+        # create the ORM mapper object if doesn't exist
+        if self.Assets == None :
+            self.create_mapper()
+
+        # generate an SQL query and for each asset in the results, yield a (validated) path name for the asset, or yield None if not found
+        for asset in self.session.query(self.Assets)[start:limit]:
+            path = self.generate_path(asset,asset_type)
+            if self.validate_path(path) and self.validate_size(path,asset_type):
+                yield path,asset
+            elif not self.asset_prefs :
+                self.log.error("Requested file for asset not found: %s. (Album ID: %i)",asset.song_title,asset.album_id)
+                yield None,asset
+            else :
+                #change
+                self.log.warning("Requested file for asset bad or not found: %s. (Album ID: %i)",asset.song_title,asset.album_id)
+                self.log.warning("Trying other asset types.")
+                path = self.find_preferred_asset_path(asset)
+                if path == None :
+                    yield None,asset
+                else :
+                    yield path,asset
+            # ensure each asset yields only once
+            pass
+        pass
+
+    def get_assets_by_genre(self,genre_id,start=0,limit=10,asset_type='audio/x-wav'):
+        '''Returns some assets of the given genre_id from the database.
+        If the path given by the specified asset type does not exists, 
+        try to find the assets given the preference list provided in self.asset_prefs.
+        If no valid path can be found for an asset, log the error and yield None for the path.'''
+        # limit = start + limit # this changes the semantics of the SQL limit
+
+        # create the ORM mapper object if doesn't exist
+        if self.Assets == None :
+            self.create_mapper()
+
+        # generate an SQL query and for each asset in the results, yield a (validated) path name for the asset, or yield None if not found
+        for asset in self.session.query(self.Assets).filter(self.Assets.genre_id == genre_id).all()[start:limit]:
+            path = self.generate_path(asset,asset_type)
+            if self.validate_path(path) and self.validate_size(path,asset_type):
+                yield path,asset
+            elif not self.asset_prefs :
+                self.log.error("Requested file for asset not found: %s. (Album ID: %i)",asset.song_title,asset.album_id)
+                yield None,asset
+            else :
+                #change
+                self.log.warning("Requested file for asset bad or not found: %s. (Album ID: %i)",asset.song_title,asset.album_id)
+                self.log.warning("Trying other asset types.")
+                path = self.find_preferred_asset_path(asset)
+                if path == None :
+                    yield None,asset
+                else :
+                    yield path,asset
+            # ensure each asset yields only once
+            pass
+        pass
+
+    def find_preferred_asset_path(self,asset):
+        '''Iteratively find a path name for each asset type in asset_prefs and return the first one available. 
+        Return None if not found and log this event for error management.'''
+        path = unicode()
+        for asset_type in self.asset_prefs :
+            path = self.generate_path(asset,asset_type)
+            if self.validate_path(path):
+                self.log.info("Asset found but type is different from requested: %s. (Album ID: %i) ",asset.song_title,asset.album_id)
+                self.append_db_errata(path,"Found different asset type for problem case. (%s)"%asset_type)
+                self.found_different_asset_type += 1
+                if self.validate_size(path,asset_type):
+                    return path
+                else :
+                    self.log.error("Requested file for asset is worng size, probably corrupt: %s. (Album ID: %i)",asset.song_title,asset.album_id)
+                    continue
+            else:
+                self.append_db_errata(path,"File not found.")
+        if len(path) == 0 :
+            self.log.warning("Asset not found for: %s. (Album ID: %i)",asset.song_title,asset.album_id)
+            return None
+
+    def generate_path(self,asset,asset_type):
+        '''Generate the path name given a asset database object and a requested asset type'''
+        path = '' # need to generate audio file path here
+        return path
+
+    def validate_path(self,path):
+        '''Validate the generated path name.'''
+        return os.path.isfile(path)
+
+    def validate_size(self,path,asset_type):
+        '''Check if the file size makes sense.'''
+        size = -1
+        try :
+            size = int(os.path.getsize(path))
+        except Exception, e:
+            self.append_db_errata(path,"Unable to determine file size.")
+            self.log.error("Unable to determine file size: %s." %path)
+            self.log.error("Exception %s."%str(e))
+            return False
+        if size == 0 :
+            self.append_db_errata(path,"File has zero size.")
+            self.log.error("File has zero size: %s."%path)
+            return False
+        if 'wav' in asset_type :
+            # rationale: with very small files some feature extractor plugins fail or output junk
+            if size > 209715200 or size < 209715 :
+                self.append_db_errata(path,"Rejected file size is: %f KB" %(size/1024.0))
+                return False
+        if 'mpeg' in asset_type :
+            # same assuming about 1:10 compression
+            if size > 41943040 or size < 65536 :
+                self.append_db_errata(path,"Rejected file size is: %f KB" %(size/1024.0))
+                return False
+        return True
+
+    def get_different_asset_no(self):
+        '''Return a count of the cases where the preferred asset type was not found'''
+        return self.found_different_asset_type
+
+    def reset_different_asset_no(self):
+        '''Reset the asset type was not found counter'''
+        self.found_different_asset_type = 0
+
+    def append_db_errata(self,filename,reason,metadata=""):
+        '''Append to a file collecting assets present in the DB but not found on disk.'''
+        if not self.errata_file : return False
+        try :
+            with open(self.errata_file,"a+") as ef:
+                if metadata :
+                    ef.write("%(filename)s,%(reason)s,%(metadata)s\n"%locals())
+                else:
+                    ef.write("%(filename)s,%(reason)s\n"%locals())
+        except:
+            self.log.error("Failed to append database errata.")
+