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.bioinfo.genotoul@inra.fr'
__status__ = 'beta'

import time
import calendar
import logging
import pymysql
import pymysql.cursors
import collections


from .config_reader import NG6ConfigReader

_QResult = collections.namedtuple( '_QResult', 'rowcount rows lastrowid' )

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


    def get_connection(self, dictc = False):
        """
            Yield a connection to the database
            @param dictc: if True, the connection will use a dictionary cursor type
        """
        #
        # rmq: the 'with' statement used with connection returns a cursor for the
        # opened connection
        #

        try:
            if dictc :
                return pymysql.connect(host = self.host,
                              user = self.user,
                              password = self.passwd,
                              db = self.db,
                              cursorclass = pymysql.cursors.DictCursor)

            return pymysql.connect(host = self.host,
                          user = self.user,
                          password = self.passwd,
                          db = self.db)
        except Exception as e:
            logging.getLogger("t3MySQLdb.get_connection").error("Exception while connecting to the database " + self.db + " on the server " + self.host + " with user "+self.user + ":" +str(e))
            raise e

    def execute(self,sql, commit = False,  dictc = False, exc_msg = None):
        """
            Execute a sql query
            @param sql: the sql to execute
            @param commit: if true will commit to database (use when inserting)
            @param dictc: use a dictionary cursor to get dictionary results
            @param exc_msg:  exception message, to be passed to the raised exception
        """
        logging.getLogger("t3MySQLdb.execute").debug("Entering execute")
        connection = self.get_connection(dictc)
        logging.getLogger("t3MySQLdb.execute").debug("after connection " + str(connection))
        try:
            id , rows = None, None
            with connection.cursor() as cursor:
                rowcount = 0
                rowcount = cursor.execute(sql)
                id = cursor.lastrowid
                rows = cursor.fetchall()
            if commit :
                connection.commit()
            logging.getLogger("t3MySQLdb.execute").debug("before returning")
            return _QResult( rowcount = rowcount, rows = rows, lastrowid = id)
        except Exception as e:
            logging.getLogger("t3MySQLdb.execute").error("Exception" + str(e))
            if exc_msg : raise Exception(e, exc_msg)
            else : raise e
        finally:
            connection.close()

    def esc_q(self, val):
        """
            Escape quotes on a string value
        """
        return str(val).replace("'", "\'").replace('\"', '\"')

    #--------------------------------------------------------------------
    # User functions

    def get_user_info(self, login):
        """
            Select informations regarding a user
            @return: a dictionary of values
            @param login: the user login
        """
        sql = "SELECT uid, username, usergroup, first_name, last_name, address, telephone, \
        fax, email, cruser_id FROM fe_users WHERE username = '%s'" % login
        qresult = self.execute(sql, exc_msg = 't3MySQLdb : the login "%s" does not exist.'%login)
        resa = qresult.rows[0]
        return {
            'id' : resa[0],
            'username' : resa[1],
            'usergroup' : resa[2],
            'first_name' : resa[3],
            'last_name' : resa[4],
            'address' : resa[5],
            'telephone' : resa[6],
            'fax' : resa[7],
            'email' : resa[8],
            'cruser_id' : resa[9]
        }

    def get_user_id(self, login):
        """
            Select the user id from login
            @return               : tthe user id
            @param login          : the login name
        """
        sql = "SELECT uid FROM fe_users WHERE username = '%s'" % login
        qresult= self.execute(sql, exc_msg = 't3MySQLdb : the login "%s" does not exist.'%login)
        return qresult.rows[0][0]

    def is_ng6admin(self, login):
        """
            Return true if the login is a ng6 administrator
            @return: id
            @param login: the login name
        """
        sql = "SELECT usergroup FROM fe_users WHERE username = '%s'" % login
        qresult = self.execute(sql, exc_msg = 't3MySQLdb : the login "%s" does not exist.'%login)
        grp = qresult.rows[0][0].split(',')
        return True if "1" in grp else False

    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)
        """
        sql = "SELECT r.fe_user_id FROM fe_rights_levels l , fe_rights r  \
        WHERE  l.right_level_label = '%s' AND  r.project_id='%s' \
        AND l.right_level_id = r.right_id;" % (right, project_id)
        qresult = self.execute(sql, exc_msg = 't3MySQLdb : Unable to retrieve right for level %s and project %s .'%(right,project_id))
        users_ids = [ r[0] for r in qresult.rows ]
        logging.getLogger("t3MySQLdb.get_users").debug(str(users_ids))
        return users_ids

    #--------------------------------------------------------------------
    # Project functions

    def add_project(self, name, pdescription, cruser_login, space_id, 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
        """
        if self.project_exists(name) :
            raise ValueError("Impossible to create project %s, this name is already used!"%name)

        crdate = str(time.time()).split(".")[0]
        if cruser_login: # If the administrator login is specified
            admin_id = self.get_user_id(cruser_login)

            sql = "SELECT right_level_id FROM fe_rights_levels WHERE right_level_label = 'administrator'"
            qresult = self.execute(sql,  exc_msg = 't3MySQLdb : The administration value does not exist in the fe_rights_levels table.')
            right_level_id = qresult.rows[0][0]
            sql = "INSERT INTO tx_nG6_project (pid, tstamp, crdate, cruser_id, description, name, hidden, space_id) \
            VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')" % (self.cfg_reader.get_pid(), crdate, crdate, admin_id,
                                                            self.esc_q(pdescription), self.esc_q(name), hidden, space_id)
            qresult = self.execute(sql, commit = True)
            project_id = qresult.lastrowid
            self.execute("INSERT INTO fe_rights (fe_user_id, project_id, right_id) VALUES ('%s','%s','%s')" % (admin_id,  project_id, right_level_id),
                         commit = True)

            return project_id
        else :
            raise Exception('t3MySQLdb', 'The administration login is required.')

    def project_exists(self,name):
        """
            Return true if the project associated with name exists
        """
        exists = False
        try :
            qresult = self.execute("SELECT uid FROM tx_nG6_project WHERE name = '%s'" % name )
            if qresult.rowcount > 0 :
                exists = True
        except :
            pass
        return exists

    def select_project(self, project_id):
        """
        Return the project infos
          @param project_id     : the project id to select
          @return: [name, description]
        """
        try:
            logging.getLogger("t3MySQLdb.select_project").debug("Selecting project id=" + str(project_id))
            sql = "SELECT name, description, space_id FROM tx_nG6_project WHERE tx_nG6_project.uid ='" + str(project_id) + "'"
            logging.getLogger("t3MySQLdb.select_project").debug(sql)
            qresult = self.execute(sql)
            res = qresult.rows[0]
            logging.getLogger("t3MySQLdb.select_project").debug("Returning [" + str([res[0], res[1], res[2]]) + "]")
            return [res[0], res[1], res[2]]
        except Exception as e:
            logging.getLogger("t3MySQLdb.select_project").error("Exception while getting the project : " +str(e))
            raise e

    def select_project_from_name(self, name):
        """
            Return the project infos
            @param name: the name of the project
            @return: [id,description]
        """
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("Selecting project name=" + str(name))
        sql = "SELECT uid, description, space_id FROM tx_nG6_project WHERE tx_nG6_project.name ='" + str(name) + "'"
        logging.getLogger("t3MySQLdb.select_project_from_name").debug(sql)
        qresult = self.execute(sql)
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("before getting qresult")
        res = qresult.rows[0]
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("Returning [" + str([res[0], res[1], res[2]]) + "]")
        return [res[0], res[1], res[2]]

    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
        """
        sql = "SELECT analyze_id FROM tx_nG6_project_analyze WHERE project_id='%s'"  % project_id
        qresult = self.execute(sql)
        return [ r[0] for r in qresult.rows ]

    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:
            sql = "SELECT project_id FROM tx_nG6_project_analyze WHERE analyze_id='%s'" % analysis_id
            qresult = self.execute(sql)
            return qresult.rows[0][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
        """
        sql = "SELECT run_id FROM tx_nG6_project_run WHERE project_id='%s'" % project_id
        qresult = self.execute(sql)
        return [ r[0] for r in qresult.rows ]

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

    def add_run(self, name, date, directory, species, data_nature,
                type, nb_sequences, full_seq_size, description, sequencer, cruser_login, retention_date, 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 retention_date: the retention date for storage.
          @param hidden        : is the analysis hidden
        """
        logging.getLogger("t3MySQLdb.add_run").debug("Inserting run with date of run =" + str(date))
        cruser_id = self.get_user_id(cruser_login)
        crdate = str(time.time()).split(".")[0]
        sql = "INSERT INTO tx_nG6_run (pid, tstamp, crdate, cruser_id, name, date, directory, species, data_nature,\
        type, nb_sequences, full_seq_size, description, hidden, sequencer, retention_date, data_state, purged_date, purged_size, mail_sent_date) VALUES ('%s', '%s', '%s', %s, '%s', '%s','%s',\
        '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, cruser_id, self.esc_q(name),
                                                             calendar.timegm(date.timetuple()), directory, self.esc_q(species),
                                                             self.esc_q(data_nature), self.esc_q(type), nb_sequences,
                                                             full_seq_size, self.esc_q(description), hidden, self.esc_q(sequencer),retention_date, "stored","0","0","0")
        qresult = self.execute(sql, commit = True)
        run_id = qresult.lastrowid

        # Check the run_id
        qresult = self.execute("SELECT directory FROM tx_nG6_run WHERE uid=%s" % run_id )
        run_directory = qresult.rows[0][0]

        if( str(run_directory) != 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
        """
        crdate = str(time.time()).split(".")[0]
        sql = "INSERT INTO tx_nG6_project_run (pid, tstamp, crdate, project_id, run_id) \
        VALUES('%s', '%s', '%s', '%s','%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, project_id, run_id)
        self.execute(sql, commit = True)

    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))
        sql = "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(sql)
        qresult = self.execute(sql)
        logging.getLogger("t3MySQLdb.select_run").debug("Returning " + str(qresult.rows[0][0]))
        return qresult.rows[0][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))
        sql = "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(sql)
        qresult = self.execute(sql)
        res = qresult.rows[0]
        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_informations(self, run_id):
        """
            Return run informations allong with project information
        """

        parts = [
            'SELECT',
                'tx_nG6_project.uid AS project_id,',
                'tx_nG6_project.name AS project_name,',
                'tx_nG6_run.uid AS run_id,',
                'tx_nG6_run.directory AS run_directory,',
                'tx_nG6_run.description AS run_description, ',
                'tx_nG6_run.species AS run_species, ',
                'tx_nG6_run.name AS run_name, ',
                'tx_nG6_run.data_nature AS run_data_nature, ',
                'tx_nG6_run.sequencer AS run_sequencer, ',
                'tx_nG6_run.type AS run_type, ',
                'tx_nG6_run.full_seq_size, ',
                'tx_nG6_run.hidden AS run_hidden, ',
                'tx_nG6_run.nb_sequences, ',
                'tx_nG6_run.storage_size, ',
                'tx_nG6_run.date AS run_date',
            'FROM',
                'fe_rights ',
                'INNER JOIN tx_nG6_project ON tx_nG6_project.uid=fe_rights.project_id ',
                'INNER JOIN tx_nG6_project_run ON tx_nG6_project.uid=tx_nG6_project_run.project_id ',
                'INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id=tx_nG6_run.uid ',
            'WHERE',
                'tx_nG6_run.uid=%s'%run_id,
            'ORDER BY',
                'tx_nG6_run.date DESC'
        ]
        sql = " ".join(parts)
        qresult = self.execute(sql, dictc = True)
        row = qresult.rows[0]
        return {
                'id'            : row['run_id'],
                'project_id'    : row['project_id'],
                'project_name'  : row['project_name'],
                'directory'     : row['run_directory'],
                'name'          : row['run_name'],
                'hidden'        : row['run_hidden'],
                'species'       : row['run_species'],
                'nb_sequences'  : row['nb_sequences'],
                'full_seq_size' : row['full_seq_size'],
                'date'          : row['run_date'],
                'data_nature'   : row['run_data_nature'],
                'sequencer'     : row['run_sequencer'],
                'type'          : row['run_type'],
                'description'   : row['run_description']
        }

    def select_run_directory(self, run_id):
        """
        Return the run directory
          @param run_id  : the run ID
          @return        : the run directory
        """
        sql = "SELECT directory FROM tx_nG6_run WHERE uid='%s'" % run_id
        qresult = self.execute(sql)
        return qresult.rows[0][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, storage_size=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
          @param storage_size  : the size of the run
        """
        set_values = []
        if name:
            set_values.append( "name='%s'" % self.esc_q(name) )
        if date:
            set_values.append( "date='%s'" % time.mktime(date.timetuple()) )
        if directory:
            set_values.append(  "directory='%s'" % directory )
        if species:
            set_values.append(  "species='%s'" % self.esc_q(species))
        if data_nature:
            set_values.append(  "data_nature='%s'" % self.esc_q(data_nature) )
        if type:
            set_values.append(  "type='%s'" % self.esc_q(type))
        if nb_sequences is not None:
            set_values.append(  "nb_sequences='%s'" % nb_sequences )
        if full_seq_size is not None:
            set_values.append( "full_seq_size='%s'" % full_seq_size )
        if description:
            set_values.append(  "description='%s'" % self.esc_q(description))
        if hidden:
            set_values.append(  "hidden=%s" % hidden )
        if sequencer:
            set_values.append(  "sequencer='%s'" % self.esc_q(sequencer))
        if storage_size:
            set_values.append(  "storage_size='%s'" % storage_size )

        sql = req =  "UPDATE tx_nG6_run SET " + ",".join(set_values)+ "  WHERE uid = '"+ str(run_id) + "'"
        self.execute(sql, commit = True)

    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
        """
        sql = "SELECT analyze_id FROM tx_nG6_run_analyze WHERE run_id='" + str(run_id) + "'"
        qresult = self.execute(sql)
        return [ r[0] for r in qresult.rows  ]

    def get_user_run_analysis(self, user_id, run_id, order_by = "", limit = ""):

        if user_id :
            where = 'fe_users.uid=' + str(user_id) + ' AND tx_nG6_run_analyze.run_id=' + str(run_id) + ' AND ((fe_rights.right_id<>2 AND tx_nG6_analyze.hidden=0) OR fe_rights.right_id=2)'
        else :
            where = 'tx_nG6_run_analyze.run_id=' + str(run_id) +' AND tx_nG6_project.public=0 AND tx_nG6_analyze.hidden=0'

        parts = [
            'SELECT' ,
                'tx_nG6_analyze.uid AS analyze_id,',
                'tx_nG6_analyze.directory AS analyze_directory,',
                'tx_nG6_analyze.name AS analyze_name, ',
                'tx_nG6_analyze.params AS analyze_params, ',
                'tx_nG6_analyze.class AS analyze_class, ',
                'tx_nG6_analyze.date AS analyze_date, ',
                'tx_nG6_analyze.software AS analyze_software, ',
                'tx_nG6_analyze.version AS analyze_version, ',
                'tx_nG6_analyze.hidden AS analyze_hidden, ',
                'tx_nG6_analyze.description AS analyze_description, ',
                'tx_nG6_analyze.is_editable AS analyze_is_editable, ',
                'tx_nG6_analyze.parent_uid AS analyze_parent_uid ',
            'FROM',
                'tx_nG6_analyze ',
                    'INNER JOIN tx_nG6_run_analyze ON tx_nG6_analyze.uid = tx_nG6_run_analyze.analyze_id ',
                    'INNER JOIN tx_nG6_run ON tx_nG6_run_analyze.run_id = tx_nG6_run.uid ',
                    'INNER JOIN tx_nG6_project_run ON tx_nG6_run.uid = tx_nG6_project_run.run_id ',
                    'INNER JOIN tx_nG6_project ON tx_nG6_project_run.project_id = tx_nG6_project.uid ',
                    'INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id ',
                    'INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid ',
            'WHERE',
                where
        ]
        if order_by :
            parts.extend(['ORDER BY', order_by])
        if limit :
            parts.extend(['LIMIT', limit])

        sql = " ".join(parts)
        qresult = self.execute(sql, dictc = True)
        results = {}

        for row in qresult.rows :
            analyze_id = str(row['analyze_id']);

            if analyze_id not in results :
                results[analyze_id] = {
                    'directory' : row['analyze_directory'],
                    'name'      : row['analyze_name'],
                    'params'    : row['analyze_params'],
                    'class'     : row['analyze_class'],
                    'id'        : int(row['analyze_id']),
                    'hidden'    : row['analyze_hidden'],
                    'software'  : row['analyze_software'],
                    'version'   : row['analyze_version'],
                    'date'      : row['analyze_date'],
                    'description' : row['analyze_description'],
                    'is_editable' : row['analyze_is_editable'],
                    'parent_id'   : row['analyze_parent_uid']
                }

        return results

    def update_sample_info(self, id , sample_id = None, name = None, reads1 = None, reads2 = None,
                           description = None, type = None, insert_size = None, nb_sequences = None, species = None,
                           full_seq_size = None):
        set_values = []
        if sample_id:
            set_values.append( "sample_id='%s'" % sample_id )
        if name:
            set_values.append( "name='%s'" % self.esc_q(name) )
        if reads1:
            if isinstance(reads1, list):
                reads1 = ','.join( [ str(e) for e in reads1 ])
            set_values.append( "reads1='%s'" % reads1 )
        if reads2:
            if isinstance(reads2, list):
                reads2 = ','.join( [ str(e) for e in reads2 ])
            set_values.append( "reads2='%s'" % reads2 )
        if description:
            set_values.append( "description='%s'" % self.esc_q(description) )
        if type:
            set_values.append( "type='%s'" % self.esc_q(type))
        if insert_size:
            set_values.append( "insert_size='%s'" % self.esc_q(insert_size) )
        if nb_sequences is not None:
            set_values.append( "nb_sequences='%s'" % nb_sequences )
        if full_seq_size is not None:
            set_values.append( "full_seq_size='%s'" % full_seq_size )
        if species:
            set_values.append( "species='%s'" % self.esc_q(species) )

        sql = "UPDATE tx_nG6_sample SET " + ",".join(set_values) + "  WHERE uid = '"+ str(id) + "'"

        self.execute(sql, commit = True)


    def add_sample_to_run(self, run_id, sample_id, reads1, reads2 = None, name = None , description = None,
                          type = None, insert_size = None, species = None, nb_sequences = None):
        """
        add a sample to the run
          @param run_id      : the run id
          @param sample_id   : the samples uniq identifier
          @param name        : the sample name
          @return sample id
        """
        crdate = str(time.time()).split(".")[0]
        column_names = [ 'pid', 'tstamp', 'run_id', 'sample_id', 'reads1' ]
        column_vals = [ "'%s'" % self.cfg_reader.get_pid(),
                       "'%s'" % crdate,
                       "'%s'" % run_id,
                       "'%s'" % self.esc_q(sample_id),
                        "'%s'" % self.esc_q(','.join(reads1))]
        if name :
            column_names.append('name')
            column_vals.append("'%s'" % self.esc_q(name))

        if description :
            column_names.append('description')
            column_vals.append("'%s'" % self.esc_q(description))

        if reads2 :
            column_names.append('reads2')
            column_vals.append("'%s'" % self.esc_q(','.join(reads2)) )

        if type :
            column_names.append('type')
            column_vals.append("'%s'" % self.esc_q(type) )

        if insert_size :
            column_names.append('insert_size')
            column_vals.append("'%s'" % self.esc_q(insert_size))

        if species :
            column_names.append('species')
            column_vals.append("'%s'" % self.esc_q(species) )

        if nb_sequences :
            column_names.append('nb_sequences')
            column_vals.append("'%s'" % self.esc_q(nb_sequences))

        sql = "INSERT INTO tx_nG6_sample (%s) VALUES (%s)" % (", ".join(column_names), ", ".join(column_vals) )
        qresult = self.execute(sql, commit = True)

        sample_id = qresult.lastrowid

        # Check the sample_id
        sql = "SELECT pid FROM tx_nG6_sample WHERE uid=" + str(sample_id)
        qresult = self.execute(sql)
        sample_pid = qresult.rows[0][0]

        if( str(sample_pid) != str(self.cfg_reader.get_pid()) ):
            raise Exception('t3MySQLdb', 'The id of the sample ' + name + ' 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 list of dictionary
        """
        sql = "SELECT run_id, uid, sample_id, name, description, reads1, reads2, type, insert_size, species, nb_sequences, full_seq_size FROM tx_nG6_sample WHERE run_id='" + str(run_id) + "'"
        qresult = self.execute(sql)
        samples = []
        for result in qresult.rows:
            samples.append({
                'run_id'        : result[0],
                'id'            : result[1],
                'sample_id'     : result[2],
                'name'          : result[3],
                'description'   : result[4],
                'reads1'        : result[5],
                'reads2'        : result[6],
                'type'          : result[7],
                'insert_size'   : result[8],
                'species'       : result[9],
                'nb_sequences'  : result[10],
                'full_seq_size' : result[11],
            })
        return samples

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

    def add_analysis(self, aclass, name, description, cruser_login, date, directory, software, version, params, is_editable, retention_date, 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 retention_date:the retention date for storage period 
          @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)
        crdate = str(time.time()).split(".")[0]

        if is_editable : analysis_is_editable = 1
        else : analysis_is_editable = 0

        sql = "INSERT INTO tx_nG6_analyze (pid, tstamp, crdate, cruser_id, class, name, \
        description, date, directory, software, version, \
        hidden, params, is_editable, parent_uid, retention_date, data_state, purged_date, purged_size, mail_sent_date) \
        VALUES ('%s', '%s', '%s', %s, '%s','%s', '%s', \
        '%s', '%s', '%s', '%s', '%s', '%s' ,'%s', '%s', '%s', '%s', '%s', '%s', '%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, cruser_id, aclass,
                                                          self.esc_q(name), self.esc_q(description), crdate,
                                                          directory, self.esc_q(software), self.esc_q(version),
                                                          hidden, self.esc_q(params), analysis_is_editable, parent_uid, retention_date, "stored","0","0","0")
        qresult = self.execute(sql, commit= True)
        analysis_id = qresult.lastrowid

        sql = "SELECT directory FROM tx_nG6_analyze WHERE uid=%s" % analysis_id
        qresult = self.execute(sql)

        analysis_directory = qresult.rows[0][0]
        if( str(analysis_directory) != 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
        """
        crdate = str(time.time()).split(".")[0]
        sql = "INSERT INTO tx_nG6_project_analyze (pid, tstamp, crdate, project_id,\
         analyze_id) VALUES('%s','%s','%s','%s','%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, project_id, analysis_id )
        self.execute(sql, commit = True)

    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
        """
        crdate = str(time.time()).split(".")[0]
        sql = "INSERT INTO tx_nG6_run_analyze (pid, tstamp, crdate, run_id, analyze_id) \
        VALUES('%s', '%s', '%s', '%s', '%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, run_id, analysis_id)
        self.execute(sql, commit = True )

    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
        """
        crdate = str(time.time()).split(".")[0]
        sql = "INSERT INTO tx_nG6_result (pid, tstamp, crdate, analyze_id, file, rkey, rvalue, rgroup) \
        VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % ( self.cfg_reader.get_pid(), crdate, crdate,
                                                                    analysis_id, file, key, value, group )
        logging.getLogger("t3MySQL").debug("add_result. sql created : " + sql)
        self.execute(sql, commit = True )
        logging.getLogger("t3MySQL").debug("add_result. executed")

    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
        """
        sql = "UPDATE tx_nG6_analyze SET %s = '%s'  WHERE uid= '%s' "% (field, self.esc_q(value), analysis_id )
        self.execute(sql, commit = True )

    def select_analysis_directory(self, analysis_id):
        """
        Return the analysis directory
          @param analysis_id  : the analysis ID
          @return            : the analysis directory
        """
        sql = "SELECT directory FROM tx_nG6_analyze WHERE uid='%s'" % analysis_id
        qresult = self.execute(sql)
        return qresult.rows[0][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:
            sql = "SELECT run_id FROM tx_nG6_run_analyze WHERE analyze_id='%s'" % analysis_id
            qresult = self.execute(sql)
            return qresult.rows[0][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))
        sql = "SELECT name, date, description, software, params, version FROM tx_nG6_analyze WHERE tx_nG6_analyze.uid ='%s'" % analysis_id
        logging.getLogger("t3MySQLdb.select_analysis").debug(sql)
        qresult = self.execute(sql,
                                exc_msg = "The analysis id=%s does not exists in the database." % analysis_id )
        res = qresult.rows[0]
        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]]

    def select_analysis_informations(self, analysis_id):
        """
            Select all information on an analyse
            @param analysis_id:
        """

        parts = [
            'SELECT',
                'tx_nG6_project.uid AS project_id,',
                'tx_nG6_project.name AS project_name,',
                'tx_nG6_analyze.uid AS analyze_id,',
                'tx_nG6_analyze.directory AS analyze_directory,',
                'tx_nG6_analyze.name AS analyze_name, ',
                'tx_nG6_analyze.params AS analyze_params, ',
                'tx_nG6_analyze.software AS analyze_software, ',
                'tx_nG6_analyze.class AS analyze_class, ',
                'tx_nG6_analyze.date AS analyze_date, ',
                'tx_nG6_analyze.description AS analyze_description, ',
                'tx_nG6_analyze.version AS analyze_version, ',
                'tx_nG6_analyze.is_editable AS analyze_is_editable, ',
                'tx_nG6_analyze.storage_size AS analyze_storage_size, ',
                'tx_nG6_analyze.parent_uid AS analyze_parent_uid ',
            'FROM',
                'tx_nG6_project ',
                'INNER JOIN tx_nG6_project_analyze ON tx_nG6_project.uid = tx_nG6_project_analyze.project_id ',
                'INNER JOIN tx_nG6_analyze ON tx_nG6_analyze.uid=tx_nG6_project_analyze.analyze_id ',
            'WHERE',
                'tx_nG6_analyze.uid='+str(analysis_id),
        ]

        sql = " ".join(parts)
        qresult = self.execute(sql, dictc = True)

        # project analysis, add project informations
        if qresult.rowcount > 0:
            row = qresult.rows[0]
            return {
                    'project_name'  : row['project_name'],
                    'project_id'    : row['project_id'],
                    'run_name'      : None,
                    'run_id'        : None,
                    'directory'     : row['analyze_directory'],
                    'name'          : row['analyze_name'],
                    'class'         : row['analyze_class'],
                    'params'        : row['analyze_params'],
                    'date'          : row['analyze_date'],
                    'description'   : row['analyze_description'],
                    'parent_id'     : row['analyze_parent_uid'],
                    'software'      : row['analyze_software'],
                    'version'       : row['analyze_version'],
                    'is_editable'   : row['analyze_is_editable']
            }
        else :

            parts = [
                'SELECT',
                    'tx_nG6_project.name AS project_name,',
                    'tx_nG6_project.uid AS project_id,',
                    'tx_nG6_run.uid AS run_id,',
                    'tx_nG6_run.name AS run_name,',
                    'tx_nG6_analyze.uid AS analyze_id,',
                    'tx_nG6_analyze.directory AS analyze_directory,',
                    'tx_nG6_analyze.name AS analyze_name, ',
                    'tx_nG6_analyze.params AS analyze_params, ',
                    'tx_nG6_analyze.class AS analyze_class, ',
                    'tx_nG6_analyze.date AS analyze_date, ',
                    'tx_nG6_analyze.software AS analyze_software, ',
                    'tx_nG6_analyze.version AS analyze_version, ',
                    'tx_nG6_analyze.description AS analyze_description,',
                    'tx_nG6_analyze.is_editable AS analyze_is_editable,',
                    'tx_nG6_analyze.storage_size AS analyze_storage_size, ',
                    'tx_nG6_analyze.parent_uid AS analyze_parent_uid ',
                'FROM',
                    'tx_nG6_project ',
                    'INNER JOIN tx_nG6_project_run ON tx_nG6_project_run.project_id = tx_nG6_project.uid ',
                    'INNER JOIN tx_nG6_run ON tx_nG6_run.uid = tx_nG6_project_run.run_id ',
                    'INNER JOIN tx_nG6_run_analyze ON tx_nG6_run_analyze.run_id = tx_nG6_run.uid ',
                    'INNER JOIN tx_nG6_analyze ON tx_nG6_analyze.uid = tx_nG6_run_analyze.analyze_id ',
                'WHERE',
                    'tx_nG6_analyze.uid=' + str(analysis_id),
                'ORDER BY',
                    'tx_nG6_analyze.name'
            ]


            sql = " ".join(parts)
            qresult = self.execute(sql, dictc = True)
            row = qresult.rows[0]

            return {
                'project_name' : row['project_name'],
                'project_id'   : row['project_id'],
                'run_name'     : row['run_name'],
                'run_id'       : row['run_id'],
                'directory'    : row['analyze_directory'],
                'name'         : row['analyze_name'],
                'params'       : row['analyze_params'],
                'class'        : row['analyze_class'],
                'date'         : row['analyze_date'],
                'description'  : row['analyze_description'],
                'software'     : row['analyze_software'],
                'parent_id'    : row['analyze_parent_uid'],
                'version'      : row['analyze_version'],
                'is_editable'  : row['analyze_is_editable']
            }

    def select_purge_demand_directories(self, demand_id):
            """
                Select all directories to purge
                @param purge_demand_id:
            """
            dirs=[]

            if ',' in demand_id :
                parts = [
                    'SELECT',
                        'tx_nG6_purge_demand.uid AS demand_id,',
                        'tx_nG6_purge_demand.analyze_ids AS analyze_ids,',
                        'tx_nG6_purge_demand.run_ids AS run_ids',
                    'FROM',
                        'tx_nG6_purge_demand ',
                    'WHERE',
                        'tx_nG6_purge_demand.uid in ('+str(demand_id)+')',
                ]
            else:
                parts = [
                    'SELECT',
                        'tx_nG6_purge_demand.uid AS demand_id,',
                        'tx_nG6_purge_demand.analyze_ids AS analyze_ids,',
                        'tx_nG6_purge_demand.run_ids AS run_ids',
                    'FROM',
                        'tx_nG6_purge_demand ',
                    'WHERE',
                        'tx_nG6_purge_demand.uid = '+str(demand_id),
                ]

            sql = " ".join(parts)
            qresult = self.execute(sql, dictc = True)
            # analysis directories
            if qresult.rowcount >= 1:
                analysis_ids=[]
                run_ids=[]
                for res in qresult.rows :
                    analysis_ids.append(res['analyze_ids'] )
                    run_ids.append(res['run_ids'] )
                parts = [
                    'SELECT',
                        'tx_nG6_analyze.directory AS analyze_directory',
                    'FROM',
                        'tx_nG6_analyze',
                    'WHERE',
                        'tx_nG6_analyze.uid IN (' + ','.join(analysis_ids) +')'
                ]
                sql = " ".join(parts)
                qresult = self.execute(sql, dictc = True)

                for result in qresult.rows:
                    dirs.append(self.cfg_reader.get_save_directory() + result['analyze_directory'])

                # run directories

                parts = [
                    'SELECT',
                        'tx_nG6_run.directory AS run_directory',
                    'FROM',
                        'tx_nG6_run',
                    'WHERE',
                        'tx_nG6_run.uid IN (' + ','.join(run_ids) +')'
                ]
                sql = " ".join(parts)
                qresult = self.execute(sql, dictc = True)

                for result in qresult.rows:
                    dirs.append(self.cfg_reader.get_save_directory() + result['run_directory'])
            return dirs

    def update_fields(self, table, uid, field, value, no_quote=[]):
        if 'uid' in field :
            return "Unable to execute 'update_fields' on field uid"

        parts =  [
                    'UPDATE',
                        table,
                    'SET']
        update_part=[]
        for f,v in zip(field,value):