t3MySQLdb.py

#
# Copyright (C) 2009 INRA
#
# 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 3 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 program.  If not, see <http://www.gnu.org/licenses/>.
#

__author__ = 'Plateforme bioinformatique Midi Pyrenees'
__copyright__ = 'Copyright (C) 2009 INRA'
__license__ = 'GNU General Public License'
__version__ = '1.0'
__email__ = 'support.genopole@toulouse.inra.fr'
__status__ = 'beta'

import datetime, time, logging
try:
    from MySQLdb import *
except:
    print "Import Error: MySQLdb is required to use t3MySQLdb object !"
    
from config_reader import NG6ConfigReader


class t3MySQLdb(object):
    """
    Class t3MySQLdb: in charge to execute common requets on mysql db
    """

    def __init__(self):
        """
        Build a t3MySQLdb
          @param host   : the host where the database is installed
          @param user   : the user name
          @param passwd : the user password
          @param db     : the database name to connect
        """
        self.cfg_reader = NG6ConfigReader()
        [host, user, passwd, db] = self.cfg_reader.get_typo3_db_params()
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db

    #--------------------------------------------------------------------
    # User functions
    
    def get_user_id(self, login):
        """
            Select the user id from login
            @return               : tthe user id
            @param login          : the login name
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT uid FROM fe_users WHERE username = '" + str(login) + "'"
        curs.execute(req)
        try:
            id = curs.fetchall()[0][0]
        except:
            raise Exception('t3MySQLdb', 'The login ' + login + ' does not exist.')
        curs.close()
        conn.close()
        return id
    
    def get_users (self, project_id, right):   
        """
            Select the users ids for a project and a level of rigth
            @return               : [user ids]
            @param project_id     : project id
            @param rigth          : right (string)
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()

        req = "SELECT r.fe_user_id FROM fe_rights_levels l , fe_rights r  WHERE \
        l.right_level_label = '" + right + "' AND \
        r.project_id=" + str(project_id) + " AND \
        l.right_level_id = r.right_id;"
        curs.execute(req)
        users_ids=[]
        try:
            result_set = curs.fetchall()
            for user_id in result_set:
                users_ids.append(user_id[0])
            
            logging.getLogger("t3MySQLdb.get_users").debug(str(users_ids))
        except:
            raise Exception('t3MySQLdb', 'Unable to retrieve right for level ' + right + ' and project '+ str(project_id))
        return users_ids
        
    #--------------------------------------------------------------------
    # Project functions

    def add_project(self, name, pdescription, cruser_login, hidden=1):
        """
        Add to the database a new Project
          @return                     : the Project ID added
          @param name                 : the project name
          @param pdescription         : the project description
          @param cruser_login         : the administrator login
          @param hidden               : is the project hidden
        """
        exists = False
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT uid FROM tx_nG6_project WHERE name = '" + str(name) + "'"
        curs.execute(req)
        try:
            project_id = curs.fetchall()[0][0]
            exists = True
        except:
            crdate = time.mktime(datetime.date.today().timetuple())

            if cruser_login: # If the administrator login is specified
                admin_id=self.get_user_id(cruser_login)
                req = "SELECT right_level_id FROM fe_rights_levels WHERE right_level_label = 'administrator'"
                curs.execute(req)
                try:
                    right_level_id = curs.fetchall()[0][0]
                except:
                    raise Exception('t3MySQLdb', 'The administration value does not exist in the fe_rights_levels table.')

                req = "INSERT INTO tx_nG6_project (pid, tstamp, crdate, cruser_id, description, name, hidden) VALUES ('" + str(self.cfg_reader.get_pid()) + "','" + str(crdate) + "','" + str(crdate) + "','" + str(admin_id) +  "','" + pdescription.replace("'", "\'") + "','" + name.replace("'", "\'") + "','" + str(hidden) + "')"
                curs.execute(req)
                project_id = conn.insert_id()
                req = "INSERT INTO fe_rights (fe_user_id, project_id, right_id) VALUES ('" + str(admin_id) + "','" + str(project_id) + "','" + str(right_level_id) + "')"
                curs.execute(req)
                
            else :
                raise Exception('t3MySQLdb', 'The administration login is required.')
        conn.commit()
        curs.close()
        conn.close()
        if exists: raise ValueError("Impossible to create project {0}, this name is already used!".format(name))
        return project_id

    def select_project(self, project_id):
        """
        Return the run infos
          @param project_id     : the project id to select
          @return: [name, description]
        """
        logging.getLogger("t3MySQLdb.select_project").debug("Selecting project id=" + str(project_id))
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT name, description FROM tx_nG6_project WHERE tx_nG6_project.uid ='" + str(project_id) + "'"
        logging.getLogger("t3MySQLdb.select_project").debug(req)
        curs.execute(req)
        res = curs.fetchall()[0]
        curs.close()
        conn.close()
        logging.getLogger("t3MySQLdb.select_project").debug("Returning [" + str([res[0], res[1]]) + "]")
        return [res[0], res[1]]

    def get_project_analysis_ids(self, project_id):
        """
        Return a list of analysis ids belonging to the project
          @param project_id  : the project id
          @return            : table of analysis ids
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT analyze_id FROM tx_nG6_project_analyze WHERE project_id='" + str(project_id) + "'"
        curs.execute(req)
        analysis_ids = []
        result_set = curs.fetchall()
        for analysis_id in result_set:
            analysis_ids.append(analysis_id[0])
        curs.close()
        conn.close()
        return analysis_ids

    def select_analysis_project_id(self, analysis_id):
        """
        Return the analysis project id or None
          @param analysis_id : the analysis ID
          @return            : the project id the analysis belongs to
        """
        try:
            conn = connect(self.host, self.user, self.passwd, self.db)
            curs = conn.cursor()
            req = "SELECT project_id FROM tx_nG6_project_analyze WHERE analyze_id='" + str(analysis_id) + "'"
            curs.execute(req)
            res = curs.fetchall()[0]
            curs.close()
            conn.close()
            return res[0]
        except:
            return None

    def get_project_runs_ids(self, project_id):
        """
        Return a list of run ids belonging to the project
          @param project_id  : the project id
          @return            : table of run ids
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT run_id FROM tx_nG6_project_run WHERE project_id='" + str(project_id) + "'"
        curs.execute(req)
        run_ids = []
        result_set = curs.fetchall()
        for run_id in result_set:
            run_ids.append(run_id[0])
        curs.close()
        conn.close()
        return run_ids

    #--------------------------------------------------------------------
    # Run functions

    def add_run(self, name, date, directory, species, data_nature,
                type, nb_sequences, full_seq_size, description, sequencer, cruser_login, hidden=1):
        """
        Add to the database a new run
          @param name          : the run name
          @param date          : the run date in datetime format
          @param directory     : the directory where run results are stored
          @param species       : the species used in the run
          @param data_nature   : the data nature used in the run
          @param type          : the run type
          @param nb_sequences  : the number of sequences returned by the sequencing
          @param full_seq_size : the full sequences size in the run
          @param description   : the run description
          @param sequencer     : the sequencer used
          @param cruser_login  : the create user login
          @param hidden        : is the analysis hidden
        """
        cruser_id=self.get_user_id(cruser_login)

        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        req = "INSERT INTO tx_nG6_run (pid,tstamp,crdate,cruser_id,name,date,directory,species,data_nature,"
        req += "type,nb_sequences,full_seq_size,description,hidden,sequencer) VALUES ('" + str(self.cfg_reader.get_pid())+ "','"
        req += str(crdate) + "'," + "'" + str(crdate) + "'," + str(cruser_id)+ ",'" + name.replace("'", "\'") + "','" + str(time.mktime(date.timetuple())) + "','"
        req += directory + "','" + species.replace("'", "\'") + "','" + data_nature.replace("'", "\'") + "','" + type.replace("'", "\'") + "','"
        req += str(nb_sequences) + "','" + str(full_seq_size) + "','" + str(description.replace("'", "\'")) + "','" + str(hidden) + "','"
        req += sequencer.replace("'", "\'") + "')"
        curs.execute(req)
        run_id = conn.insert_id()
        
        "Check the run_id"
        req = "SELECT directory FROM tx_nG6_run WHERE uid=" + str(run_id)
        curs.execute(req)
        run_directory = curs.fetchone()
        conn.commit()
        curs.close()
        conn.close()
        if( str(run_directory[0]) != str(directory) ):
            raise Exception('t3MySQLdb', 'The id of the run ' + name + ' cannot be to retrieve.')
        
        return run_id

    def add_run_to_project(self, project_id, run_id):
        """
        Add to the database a new run
          @param project_id    : the project_id the run belong to
          @param run_id        : the run to add
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        req = "INSERT INTO tx_nG6_project_run (pid, tstamp, crdate, project_id, run_id) VALUES('" + str(self.cfg_reader.get_pid()) + "','" + str(crdate)
        req += "','" + str(crdate) + "','" + str(project_id) + "','" + str(run_id) + "')"
        curs.execute(req)
        conn.commit()
        curs.close()
        conn.close()

    def select_project_id_from_run_id(self, run_id):
        """
        Return the project id
          @param run_id         : the run id to select
          @return: project_id    : the project_id
        """
        logging.getLogger("t3MySQLdb.select_project_id_from_run_id").debug("Selecting run id=" + str(run_id))
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT distinct(project_id) FROM tx_nG6_project_run WHERE tx_nG6_project_run.run_id ='" + str(run_id) + "'"
        logging.getLogger("t3MySQLdb.select_run").debug(req)
        curs.execute(req)
        res = curs.fetchall()[0]
        curs.close()
        conn.close()
        logging.getLogger("t3MySQLdb.select_run").debug("Returning " + str(res[0]))
        return res[0]
    
    def select_run(self, run_id):
        """
        Return the run infos
          @param run_id         : the run id to select
          @return: [name, date, species, data_nature, type, description, sequencer]
        """
        logging.getLogger("t3MySQLdb.select_run").debug("Selecting run id=" + str(run_id))
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT name, date, species, data_nature, type, description, sequencer FROM tx_nG6_run WHERE tx_nG6_run.uid ='" + str(run_id) + "'"
        logging.getLogger("t3MySQLdb.select_run").debug(req)
        curs.execute(req)
        res = curs.fetchall()[0]
        curs.close()
        conn.close()
        logging.getLogger("t3MySQLdb.select_run").debug("Returning [" + str([res[0], res[1], res[2], res[3], res[4], res[5], res[6]]) + "]")
        return [res[0], res[1], res[2], res[3], res[4], res[5], res[6]]

    def select_run_directory(self, run_id):
        """
        Return the run directory
          @param run_id  : the run ID
          @return        : the run directory
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT directory FROM tx_nG6_run WHERE uid='" + str(run_id) + "'"
        curs.execute(req)
        res = curs.fetchall()[0]
        curs.close()
        conn.close()
        return res[0]

    def update_run_info(self, run_id, name=None, date=None, directory=None, species=None, data_nature=None, type=None, nb_sequences=None, full_seq_size=None, description=None, sequencer=None, hidden=None):
        """
        Add to the database a new Analyze done on a run
          @param project_id    : the project_id the run belong to
          @param run_id        : the run to add
          @param name          : the run name
          @param date          : the run date in datetime format
          @param directory     : the directory where run results are stored
          @param species       : the species used in the run
          @param data_nature   : the data nature used in the run
          @param type          : the run type
          @param nb_sequences  : the number of sequences returned by the sequencing
          @param full_seq_size : the full sequences size in the run
          @param description   : the run description
          @param sequencer     : the sequencer used
          @param hidden        : is the analysis hidden
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req =  "UPDATE tx_nG6_run "
        req += "SET "
        if name:
            req += "name='" + name.replace("'", "\'") + "',"
        if date:
            req += "date='" + str(time.mktime(date.timetuple())) + "',"
        if directory:
            req += "directory='" + directory + "',"
        if species:
            req += "species='" + species.replace("'", "\'") + "',"
        if data_nature:
            req += "data_nature='" + data_nature.replace("'", "\'") + "',"
        if type:
            req += "type='" + type.replace("'", "\'") + "',"
        if nb_sequences is not None:
            req += "nb_sequences='" + str(nb_sequences) + "',"
        if full_seq_size is not None:
            req += "full_seq_size='" + str(full_seq_size) + "',"
        if description:
            req += "description='" + str(description.replace("'", "\'")) + "',"
        if hidden:
            req += "hidden=" + str(hidden) + ","
        if sequencer:
            req += "sequencer='" + sequencer.replace("'", "\'") + "', "
        req = req[:-1] #remove last comma for WHERE
        req += " WHERE uid = '"+ str(run_id) + "'"
        curs.execute(req)
        conn.commit()
        conn.close()

    def get_run_analysis_ids(self, run_id):
        """
        Return a list of analysis ids belonging to the run
          @param run_id  : the run id
          @return        : table of analysis ids
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT analyze_id FROM tx_nG6_run_analyze WHERE run_id='" + str(run_id) + "'"
        curs.execute(req)
        analysis_ids = []
        result_set = curs.fetchall()
        for analysis_id in result_set:
            analysis_ids.append(analysis_id[0])
        curs.close()
        conn.close()
        return analysis_ids

    def add_sample_to_run(self, run_id, mid, description):
        """
        add a sample to the run
          @param run_id      : the run id
          @param mid         : the mid name
          @param description : the mid description
          @return sample id
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        req = "INSERT INTO tx_nG6_sample (pid,tstamp,run_id,mid,description) "
        req += "VALUES ('" + str(self.cfg_reader.get_pid())+ "','" + str(crdate) + "','"
        req += str(run_id) + "','" + mid.replace("'", "\'") + "','"
        req += description.replace("'", "\'") + "')"
        curs.execute(req)
        sample_id = conn.insert_id()
        
        "Check the sample_id"
        req = "SELECT pid FROM tx_nG6_sample WHERE uid=" + str(sample_id)
        curs.execute(req)
        sample_pid = curs.fetchone()
        conn.commit()
        curs.close()
        conn.close()
        if( str(sample_pid[0]) != str(self.cfg_reader.get_pid()) ):
            raise Exception('t3MySQLdb', 'The id of the sample ' + mid + ' cannot be to retrieve.')
        
        return sample_id    

    def select_run_samples(self, run_id):
        """
        select all samples linked to the run
          @param run_id : the run id
          @return a mid description table {"MID1":"desc1", ...}
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT mid, description FROM tx_nG6_sample WHERE run_id='" + str(run_id) + "'"
        curs.execute(req)
        mids = {}
        result_set = curs.fetchall()
        for result in result_set:
            mids[result[0]] = result[1]
        return mids

    #--------------------------------------------------------------------
    # Analyze functions

    def add_analysis(self, aclass, name, description, cruser_login, date, directory, software, version, params, is_editable, hidden=1, parent_uid=0):

        """
        Update an Analyze
          @param aclass       : the class of the analysis
          @param name         : the analysis name
          @param description  : the analysis description
          @param cruser_login : the user login
          @param date         : the analysis date in datetime format
          @param directory    : the directory where analysis results are stored
          @param software     : the software name used for the analysis
          @param version      : the software version used for the analysis
          @param params       : the software params used
          @param hidden       : is the analysis hidden
          @param parent_uid   : the analysis parent uid
          @param is_editable : True if analysis has been added by an user
        """
        cruser_id=self.get_user_id(cruser_login)
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        
        if is_editable : analysis_is_editable = 1 
        else : analysis_is_editable = 0      
        req = "INSERT INTO tx_nG6_analyze (pid,tstamp,crdate,cruser_id,class,name,description,date,directory,software,version,"
        req += "hidden, params, is_editable, parent_uid) VALUES ('" + str(self.cfg_reader.get_pid())+ "','" + str(crdate) + "','"
        req += str(crdate) + "'," + str(cruser_id) + ",'" + aclass + "','" + name.replace("'", "\'") + "','" + description.replace("'", "\'") + "','"
        req += str(crdate) + "','" + directory + "','" + software.replace("'", "\'") + "','"
        req += version.replace("'", "\'") + "','" + str(hidden) + "','" + params.replace("'", "\'") + "','"
        req += str(analysis_is_editable) + "','"  + str(parent_uid) + "')"
        curs.execute(req)
        analysis_id = conn.insert_id()

        req = "SELECT directory FROM tx_nG6_analyze WHERE uid=" + str(analysis_id)
        curs.execute(req)
        analysis_directory = curs.fetchone()
        conn.commit()
        curs.close()
        conn.close()
        if( str(analysis_directory[0]) != str(directory) ):
            raise Exception('t3MySQLdb', 'The id of the analysis ' + name + ' cannot be to retrieve.')
        
        return analysis_id

    def add_analysis_to_project(self, project_id, analysis_id):
        """
        Add to the database a new Analyze done on a project
          @param project_id  : the project_id the analysis has been done on
          @param analysis_id  : the analysis id
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        req = "INSERT INTO tx_nG6_project_analyze (pid, tstamp, crdate, project_id, analyze_id) VALUES('" + str(self.cfg_reader.get_pid()) + "','" + str(crdate)
        req += "','" + str(crdate) + "','" + str(project_id) + "','" + str(analysis_id) + "')"
        curs.execute(req)
        conn.commit()
        curs.close()
        conn.close()

    def add_analysis_to_run(self, run_id, analysis_id):
        """
        Add to the database a new Analyze done on a run
          @param run_id      : the run_id the analysis has been done on
          @param analysis_id  : the analysis id
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        req = "INSERT INTO tx_nG6_run_analyze (pid, tstamp, crdate, run_id, analyze_id) VALUES('" + str(self.cfg_reader.get_pid()) + "','" + str(crdate)
        req += "','" + str(crdate) + "','" + str(run_id) + "','" + str(analysis_id) + "')"
        curs.execute(req)
        conn.commit()
        curs.close()
        conn.close()

    def add_result(self, analysis_id, file, key, value, group):
        """
        Add to the analysis a new result
          @param analysis_id : the analysis id
          @param file       : the file on which the analysis has been performed
          @param key        : the result key
          @param value      : the result value
          @param group      : the group value
        """
        # First select the sample
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        req = "INSERT INTO tx_nG6_result (pid, tstamp, crdate, analyze_id, file, rkey, rvalue, rgroup) " 
        req += "VALUES('" + str(self.cfg_reader.get_pid()) + "','" + str(crdate)
        req += "','" + str(crdate) + "','" + str(analysis_id) + "','" + file
        req += "','" + str(key) + "','" + str(value) + "','" + str(group) + "')"
        curs.execute(req)
        conn.commit()
        curs.close()
        conn.close()
        
    def update_analysis_field(self, analysis_id, field, value):
        """
        Update an Analyze
          @param analysis_id  : the analysis id
          @param field       : the field to update
          @param value       : the value
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req =  "UPDATE tx_nG6_analyze "
        req += "SET " + field + "='" + str(value).replace("'", "\'") + "' "
        req += "WHERE uid = '"+ str(analysis_id) + "'"
        curs.execute(req)
        conn.commit()
        curs.close()
        conn.close()

    def select_analysis_directory(self, analysis_id):
        """
        Return the analysis directory
          @param analysis_id  : the analysis ID
          @return            : the analysis directory
        """
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT directory FROM tx_nG6_analyze WHERE uid='" + str(analysis_id) + "'"
        curs.execute(req)
        res = curs.fetchall()[0]
        curs.close()
        conn.close()
        return res[0]

    def select_analysis_run_id(self, analysis_id):
        """
        Return the analysis run id or None
          @param analysis_id  : the analysis ID
          @return            : the run id the analysis belongs to
        """
        try:
            conn = connect(self.host, self.user, self.passwd, self.db)
            curs = conn.cursor()
            req = "SELECT run_id FROM tx_nG6_run_analyze WHERE analyze_id='" + str(analysis_id) + "'"
            curs.execute(req)
            res = curs.fetchall()[0]
            curs.close()
            conn.close()
            return res[0]
        except:
            return None

    def select_analysis(self, analysis_id):
        """
        Return the analysis infos
          @param analysis_id    : the analysis id to select
          @return: [name, date, description, software, options, version]
        """
        logging.getLogger("t3MySQLdb.select_analysis").debug("Selecting analysis id=" + str(analysis_id))
        conn = connect(self.host, self.user, self.passwd, self.db)
        curs = conn.cursor()
        req = "SELECT name, date, description, software, params, version FROM tx_nG6_analyze WHERE tx_nG6_analyze.uid ='" + str(analysis_id) + "'"
        logging.getLogger("t3MySQLdb.select_analysis").debug(req)
        curs.execute(req)
        if curs.rowcount == 0:
            curs.close()
            conn.close()
            raise Exception("The analysis id=" + str(analysis_id) + " does not exists in the database.\n")
        else:
            res = curs.fetchall()[0]
            curs.close()
            conn.close()
            logging.getLogger("t3MySQLdb.select_analysis").debug("Returning [" + str([res[0], res[1], res[2], res[3], res[4], res[5]]) + "]")
            return [res[0], res[1], res[2], res[3], res[4], res[5]]