source: mauRepo/MolesManager/trunk/src/libs/commons_db.py @ 8344

'''
Created on 17 Nov 2011

@author: mnagni
'''
from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.sql.expression import text
class DbManagerCollection(object):
    """
        Represents a collection of DbManagers. The key of the dictionary passed as argument to the constructor 
        will be used to retrieve a specific db instance.         
    """
    def __init__(self, dbManagers = {}): 
        self._dbManagers = dbManagers
        
    def createSession(self, name):
        if self._dbManagers.has_key(name):
            return self._dbManagers[name].createDbSession()

class DbManager(object):
    

    """
        Represents a database instance managed by an SQLAlchemy engine.        
    """
    def __init__(self, connection, script = None, sql_echo=False, session_event_manager = None):
        """
          @param connection: the URL used to open a connection to the database
          @param script: a python module defining the classes mapping
          @param sql_echo: sets the SQLalchemy create_engine.echo param. Default is False 
          @param session_event_manager: a python method defining session events \
          (see http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=session%20events#sqlalchemy.orm.events.SessionEvents)     
        """
        self._connection = connection        
        self._script = script
        self.engine = None
        try:
            self.engine = create_engine(self._connection, echo=sql_echo)
        except Exception as e:
            print e
        self.metadata = MetaData(bind=self.engine)        
        self._session = None
        self._session_event_manager = session_event_manager
        if self._script:
            self._script(self.metadata)
            self.metadata.create_all(bind=self.engine)
            #self._session = scoped_session(sessionmaker())
        if self.engine:
            self.engine.connect()
            
    def createDbSession(self):
        """
           Returns a brand new connection to the DB.        
           @return: a sqlalchemy.orm.session.Session instance if the ORM is fully configured, otherwise a sqlalchemy.engine.base.Connection instance 
        """
        #session = scoped_session(sessionmaker(bind=self.engine))()
        session = sessionmaker(bind=self.engine)()        
        if self._session_event_manager:
            self._session_event_manager(session)
        return session        
        #if self._session:
        #    ret = self._session()
        #    if self._session_event_manager:
        #        self._session_event_manager(ret)
        #    return ret
        #if self.engine:
        #    return self.engine.connect()

    def closeDbSession(self, dbSession):
        """
          Commits and closes the given session/connection instance
          :param dbSession: a Session/Connection instance
        """
        try:
            dbSession.commit()
            dbSession.close()
        except:
            dbSession.rollback()
            dbSession.close()
            raise
        
    def executeNative(self, sqlNative):
        t = text(sqlNative)
        return self._connection.execute(t)

def doInsertOrUpdate(items, session, update = True):
    if update:
        session.commit()
    else:
        session.add_all(items)

def getObjectId(clazz, obj_id, session):  
    '''
        Retrieves an entity
        @param clazz: the entity type
        @param obj_id: the entity id  
    '''       
    res = session.query(clazz).get(obj_id)
    if res is None:
        return None
    return res

def getAllObjects(clazz, session):     
    res = session.query(clazz)
    if res is None:
        return None
    return res