t3MySQLdb.py 40.6 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#
# 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
Penom Nom's avatar
Penom Nom committed
26
27
28
29
import pymysql
import pymysql.cursors
import collections

30
    
Penom Nom's avatar
Penom Nom committed
31
from .config_reader import NG6ConfigReader
32

Penom Nom's avatar
Penom Nom committed
33
_QResult = collections.namedtuple( '_QResult', 'rowcount rows lastrowid' )
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

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

Penom Nom's avatar
Penom Nom committed
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
    
    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
        #
        
        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)
    
    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
        """
        connection = self.get_connection(dictc)
        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()
            return _QResult( rowcount = rowcount, rows = rows, lastrowid = id)
        except Exception as 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('\"', '\"')
    
Celine Noirot's avatar
Celine Noirot committed
109
110
111
    #--------------------------------------------------------------------
    # User functions
    
112
113
114
115
116
117
    def get_user_info(self, login):
        """
            Select informations regarding a user
            @return: a dictionary of values
            @param login: the user login
        """
Penom Nom's avatar
Penom Nom committed
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
        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]
        }

Celine Noirot's avatar
Celine Noirot committed
135
136
137
138
139
140
    def get_user_id(self, login):
        """
            Select the user id from login
            @return               : tthe user id
            @param login          : the login name
        """
Penom Nom's avatar
Penom Nom committed
141
142
143
        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]
Celine Noirot's avatar
Celine Noirot committed
144
    
Penom Nom's avatar
Penom Nom committed
145
146
147
148
149
150
    def is_ng6admin(self, login):
        """
            Return true if the login is a ng6 administrator
            @return: id
            @param login: the login name 
        """
Penom Nom's avatar
Penom Nom committed
151
152
153
154
        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
Penom Nom's avatar
Penom Nom committed
155
    
Penom Nom's avatar
Penom Nom committed
156
    def get_users (self, project_id, right):
Celine Noirot's avatar
Celine Noirot committed
157
158
159
160
161
162
        """
            Select the users ids for a project and a level of rigth
            @return               : [user ids]
            @param project_id     : project id
            @param rigth          : right (string)
        """
Penom Nom's avatar
Penom Nom committed
163
164
165
166
167
168
        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))
Celine Noirot's avatar
Celine Noirot committed
169
170
        return users_ids
        
171
172
173
    #--------------------------------------------------------------------
    # Project functions

Celine Noirot's avatar
Celine Noirot committed
174
    def add_project(self, name, pdescription, cruser_login, hidden=1):
175
176
177
178
179
        """
        Add to the database a new Project
          @return                     : the Project ID added
          @param name                 : the project name
          @param pdescription         : the project description
Celine Noirot's avatar
Celine Noirot committed
180
          @param cruser_login         : the administrator login
181
182
          @param hidden               : is the project hidden
        """
Penom Nom's avatar
Penom Nom committed
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
        if self.project_exists(name) :
            raise ValueError("Impossible to create project %s, this name is already used!"%name)
        
        crdate = str(time.mktime(datetime.date.today().timetuple())).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) \
            VALUES ('%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)
            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.')
Penom Nom's avatar
Penom Nom committed
206
207
208
209
210
211
    
    def project_exists(self,name):
        """
            Return true if the project associated with name exists
        """
        exists = False
Penom Nom's avatar
Penom Nom committed
212
213
214
215
        try :
            qresult = self.execute("SELECT uid FROM tx_nG6_project WHERE name = '%s'" % name )
            if qresult.rowcount > 0 :
                exists = True
Penom Nom's avatar
Penom Nom committed
216
217
218
219
        except :
            pass
        return exists
    
220
221
    def select_project(self, project_id):
        """
222
        Return the project infos
223
224
225
226
          @param project_id     : the project id to select
          @return: [name, description]
        """
        logging.getLogger("t3MySQLdb.select_project").debug("Selecting project id=" + str(project_id))
Penom Nom's avatar
Penom Nom committed
227
228
229
230
        sql = "SELECT name, description 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]
231
232
233
        logging.getLogger("t3MySQLdb.select_project").debug("Returning [" + str([res[0], res[1]]) + "]")
        return [res[0], res[1]]

234
235
236
237
238
239
    def select_project_from_name(self, name):
        """
            Return the project infos
            @param name: the name of the project
            @return: [id,description]
        """
Penom Nom's avatar
Penom Nom committed
240
241
242
243
244
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("Selecting project name=" + str(name))
        sql = "SELECT uid, description FROM tx_nG6_project WHERE tx_nG6_project.name ='" + str(name) + "'"
        logging.getLogger("t3MySQLdb.select_project").debug(sql)
        qresult = self.execute(sql)
        res = qresult.rows[0]
245
246
247
        logging.getLogger("t3MySQLdb.select_project").debug("Returning [" + str([res[0], res[1]]) + "]")
        return [res[0], res[1]]
        
248
249
    def get_project_analysis_ids(self, project_id):
        """
Jerome Mariette's avatar
Jerome Mariette committed
250
        Return a list of analysis ids belonging to the project
251
          @param project_id  : the project id
Jerome Mariette's avatar
Jerome Mariette committed
252
          @return            : table of analysis ids
253
        """
Penom Nom's avatar
Penom Nom committed
254
255
256
        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 ]
257

258
259
260
261
262
263
264
    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:
Penom Nom's avatar
Penom Nom committed
265
266
267
            sql = "SELECT project_id FROM tx_nG6_project_analyze WHERE analyze_id='%s'" % analysis_id
            qresult = self.execute(sql)
            return qresult.rows[0][0]
268
269
        except:
            return None
270
271
272
273
274
275
276

    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
        """
Penom Nom's avatar
Penom Nom committed
277
278
279
        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 ]
280
281
282
283
284

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

    def add_run(self, name, date, directory, species, data_nature,
Celine Noirot's avatar
Celine Noirot committed
285
                type, nb_sequences, full_seq_size, description, sequencer, cruser_login, hidden=1):
286
287
288
289
290
291
292
293
294
295
296
297
        """
        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
Celine Noirot's avatar
Celine Noirot committed
298
          @param cruser_login  : the create user login
Jerome Mariette's avatar
Jerome Mariette committed
299
          @param hidden        : is the analysis hidden
300
        """
Penom Nom's avatar
Penom Nom committed
301
        cruser_id = self.get_user_id(cruser_login)
302
303
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
        
Penom Nom's avatar
Penom Nom committed
304
305
306
307
308
309
310
311
        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) VALUES ('%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), 
                                                             time.mktime(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))
        qresult = self.execute(sql, commit = True)
        run_id = qresult.lastrowid
312
        
Penom Nom's avatar
Penom Nom committed
313
314
315
316
317
318
        # 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.')
319
320
321
322
323
324
325
326
327
        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.mktime(datetime.date.today().timetuple())).split(".")[0]
Penom Nom's avatar
Penom Nom committed
328
329
330
        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)
331

Celine Noirot's avatar
Celine Noirot committed
332
333
334
335
336
337
338
    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))
Penom Nom's avatar
Penom Nom committed
339
340
341
342
343
        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]
Celine Noirot's avatar
Celine Noirot committed
344
    
345
346
347
348
349
350
351
    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))
Penom Nom's avatar
Penom Nom committed
352
353
354
355
        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]
356
357
358
        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]]

Penom Nom's avatar
Penom Nom committed
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
    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'
        ]
Penom Nom's avatar
Penom Nom committed
391
        sql = " ".join(parts)
Penom Nom's avatar
Penom Nom committed
392
        qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
393
        row = qresult.rows[0]
Penom Nom's avatar
Penom Nom committed
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
        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']                
        }                  
        
411
412
413
414
415
416
    def select_run_directory(self, run_id):
        """
        Return the run directory
          @param run_id  : the run ID
          @return        : the run directory
        """
Penom Nom's avatar
Penom Nom committed
417
418
419
        sql = "SELECT directory FROM tx_nG6_run WHERE uid='%s'" % run_id
        qresult = self.execute(sql)
        return qresult.rows[0][0]
420

Penom Nom's avatar
Penom Nom committed
421
422
    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):
423
424
425
426
427
428
429
430
431
432
433
434
435
436
        """
        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
Jerome Mariette's avatar
Jerome Mariette committed
437
          @param hidden        : is the analysis hidden
438
          @param storage_size  : the size of the run
439
        """
Penom Nom's avatar
Penom Nom committed
440
        set_values = []
441
        if name:
Penom Nom's avatar
Penom Nom committed
442
            set_values.append( "name='%s'" % self.esc_q(name) )
443
        if date:
Penom Nom's avatar
Penom Nom committed
444
            set_values.append( "date='%s'" % time.mktime(date.timetuple()) ) 
445
        if directory:
Penom Nom's avatar
Penom Nom committed
446
            set_values.append(  "directory='%s'" % directory )
447
        if species:
Penom Nom's avatar
Penom Nom committed
448
            set_values.append(  "species='%s'" % self.esc_q(species))
449
        if data_nature:
Penom Nom's avatar
Penom Nom committed
450
            set_values.append(  "data_nature='%s'" % self.esc_q(data_nature) )
451
        if type:
Penom Nom's avatar
Penom Nom committed
452
            set_values.append(  "type='%s'" % self.esc_q(type))
Penom Nom's avatar
Penom Nom committed
453
        if nb_sequences is not None:
Penom Nom's avatar
Penom Nom committed
454
            set_values.append(  "nb_sequences='%s'" % nb_sequences ) 
Penom Nom's avatar
Penom Nom committed
455
        if full_seq_size is not None:
Penom Nom's avatar
Penom Nom committed
456
            set_values.append( "full_seq_size='%s'" % full_seq_size )
457
        if description:
Penom Nom's avatar
Penom Nom committed
458
            set_values.append(  "description='%s'" % self.esc_q(description))
459
        if hidden:
Penom Nom's avatar
Penom Nom committed
460
            set_values.append(  "hidden=%s" % hidden ) 
461
        if sequencer:
Penom Nom's avatar
Penom Nom committed
462
            set_values.append(  "sequencer='%s'" % self.esc_q(sequencer))
463
        if storage_size:
Penom Nom's avatar
Penom Nom committed
464
465
466
467
            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)
468
469
470

    def get_run_analysis_ids(self, run_id):
        """
Jerome Mariette's avatar
Jerome Mariette committed
471
        Return a list of analysis ids belonging to the run
472
          @param run_id  : the run id
Jerome Mariette's avatar
Jerome Mariette committed
473
          @return        : table of analysis ids
474
        """
Penom Nom's avatar
Penom Nom committed
475
476
477
        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  ]
478

Penom Nom's avatar
Penom Nom committed
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
    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])
        
Penom Nom's avatar
Penom Nom committed
516
517
        sql = " ".join(parts)
        qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
518
        results = {}
Penom Nom's avatar
Penom Nom committed
519
520
        
        for row in qresult.rows :
Penom Nom's avatar
Penom Nom committed
521
522
            analyze_id = str(row['analyze_id']);
            
Penom Nom's avatar
Penom Nom committed
523
            if analyze_id not in results :
Penom Nom's avatar
Penom Nom committed
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
                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

Penom Nom's avatar
Penom Nom committed
541
542
543
    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):
Penom Nom's avatar
Penom Nom committed
544
        set_values = []
Penom Nom's avatar
Penom Nom committed
545
        if sample_id:
Penom Nom's avatar
Penom Nom committed
546
            set_values.append( "sample_id='%s'" % sample_id )
Penom Nom's avatar
Penom Nom committed
547
        if name:
Penom Nom's avatar
Penom Nom committed
548
            set_values.append( "name='%s'" % self.esc_q(name) )
Penom Nom's avatar
Penom Nom committed
549
        if reads1:
Penom Nom's avatar
Penom Nom committed
550
551
552
            if isinstance(reads1, list):
                reads1 = ','.join( [ str(e) for e in reads1 ])
            set_values.append( "reads1='%s'" % reads1 )
Penom Nom's avatar
Penom Nom committed
553
        if reads2:
Penom Nom's avatar
Penom Nom committed
554
555
556
            if isinstance(reads2, list):
                reads2 = ','.join( [ str(e) for e in reads2 ])
            set_values.append( "reads2='%s'" % reads2 )
Penom Nom's avatar
Penom Nom committed
557
        if description:
Penom Nom's avatar
Penom Nom committed
558
            set_values.append( "description='%s'" % self.esc_q(description) )
Penom Nom's avatar
Penom Nom committed
559
        if type:
Penom Nom's avatar
Penom Nom committed
560
            set_values.append( "type='%s'" % self.esc_q(type))
Penom Nom's avatar
Penom Nom committed
561
        if insert_size:
Penom Nom's avatar
Penom Nom committed
562
            set_values.append( "insert_size='%s'" % self.esc_q(insert_size) )
Penom Nom's avatar
Penom Nom committed
563
        if nb_sequences is not None:
Penom Nom's avatar
Penom Nom committed
564
            set_values.append( "nb_sequences='%s'" % nb_sequences )
Penom Nom's avatar
Penom Nom committed
565
        if full_seq_size is not None:
Penom Nom's avatar
Penom Nom committed
566
            set_values.append( "full_seq_size='%s'" % full_seq_size )
Penom Nom's avatar
Penom Nom committed
567
        if species:
Penom Nom's avatar
Penom Nom committed
568
569
570
571
572
573
            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)
        
Penom Nom's avatar
Penom Nom committed
574
    
Penom Nom's avatar
Penom Nom committed
575
    def add_sample_to_run(self, run_id, sample_id, reads1, reads2 = None, name = None , description = None,
Penom Nom's avatar
Penom Nom committed
576
                          type = None, insert_size = None, species = None, nb_sequences = None):
577
578
579
        """
        add a sample to the run
          @param run_id      : the run id
Penom Nom's avatar
Penom Nom committed
580
581
          @param sample_id   : the samples uniq identifier
          @param name        : the sample name
582
583
584
          @return sample id
        """
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
Penom Nom's avatar
Penom Nom committed
585
586
587
588
589
590
        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))]
Penom Nom's avatar
Penom Nom committed
591
        if name :
Penom Nom's avatar
Penom Nom committed
592
593
            column_names.append('name')
            column_vals.append("'%s'" % self.esc_q(name))
Penom Nom's avatar
Penom Nom committed
594
595
        
        if description :
Penom Nom's avatar
Penom Nom committed
596
597
            column_names.append('description')
            column_vals.append("'%s'" % self.esc_q(description))
Penom Nom's avatar
Penom Nom committed
598
599
        
        if reads2 :
Penom Nom's avatar
Penom Nom committed
600
601
602
            column_names.append('reads2')
            column_vals.append("'%s'" % self.esc_q(','.join(reads2)) )

Penom Nom's avatar
Penom Nom committed
603
        if type :
Penom Nom's avatar
Penom Nom committed
604
605
            column_names.append('type')
            column_vals.append("'%s'" % self.esc_q(type) )
Penom Nom's avatar
Penom Nom committed
606
607
            
        if insert_size :
Penom Nom's avatar
Penom Nom committed
608
609
            column_names.append('insert_size')
            column_vals.append("'%s'" % self.esc_q(insert_size))
Penom Nom's avatar
Penom Nom committed
610
611
            
        if species :
Penom Nom's avatar
Penom Nom committed
612
613
            column_names.append('species')
            column_vals.append("'%s'" % self.esc_q(species) )
Penom Nom's avatar
Penom Nom committed
614
            
Penom Nom's avatar
Penom Nom committed
615
        if nb_sequences :
Penom Nom's avatar
Penom Nom committed
616
617
            column_names.append('nb_sequences')
            column_vals.append("'%s'" % self.esc_q(nb_sequences))
Penom Nom's avatar
Penom Nom committed
618
        
Penom Nom's avatar
Penom Nom committed
619
620
        sql = "INSERT INTO tx_nG6_sample (%s) VALUES (%s)" % (", ".join(column_names), ", ".join(column_vals) )
        qresult = self.execute(sql, commit = True)
621
        
Penom Nom's avatar
Penom Nom committed
622
623
624
625
626
627
        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]
628
        
Penom Nom's avatar
Penom Nom committed
629
630
        if( str(sample_pid) != str(self.cfg_reader.get_pid()) ):
            raise Exception('t3MySQLdb', 'The id of the sample ' + name + ' cannot be to retrieve.')
631
        return sample_id    
632
633
634
635
636

    def select_run_samples(self, run_id):
        """
        select all samples linked to the run
          @param run_id : the run id
Penom Nom's avatar
Penom Nom committed
637
          @return a list of dictionary
638
        """
Penom Nom's avatar
Penom Nom committed
639
640
        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)
Penom Nom's avatar
Penom Nom committed
641
        samples = []
Penom Nom's avatar
Penom Nom committed
642
        for result in qresult.rows:
Penom Nom's avatar
Penom Nom committed
643
644
645
646
647
648
649
650
651
652
653
            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],
Penom Nom's avatar
Penom Nom committed
654
655
                'nb_sequences'  : result[10],
                'full_seq_size' : result[11],
Penom Nom's avatar
Penom Nom committed
656
657
            })
        return samples
658
659
660
661

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

Celine Noirot's avatar
Celine Noirot committed
662
    def add_analysis(self, aclass, name, description, cruser_login, date, directory, software, version, params, is_editable, hidden=1, parent_uid=0):
663
664
        """
        Update an Analyze
Celine Noirot's avatar
Celine Noirot committed
665
666
667
668
669
670
671
672
673
674
675
          @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
676
          @param is_editable : True if analysis has been added by an user
677
        """
Penom Nom's avatar
Penom Nom committed
678
        cruser_id = self.get_user_id(cruser_login)
679
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
Celine Noirot's avatar
Celine Noirot committed
680
        
681
682
        if is_editable : analysis_is_editable = 1 
        else : analysis_is_editable = 0      
683
        
Penom Nom's avatar
Penom Nom committed
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
        sql = "INSERT INTO tx_nG6_analyze (pid, tstamp, crdate, cruser_id, class, name, \
        description, date, directory, software, version, \
        hidden, params, is_editable, parent_uid) \
        VALUES ('%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 )
        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.')
Jerome Mariette's avatar
Jerome Mariette committed
701
        return analysis_id
702

Jerome Mariette's avatar
Jerome Mariette committed
703
    def add_analysis_to_project(self, project_id, analysis_id):
704
705
        """
        Add to the database a new Analyze done on a project
Jerome Mariette's avatar
Jerome Mariette committed
706
707
          @param project_id  : the project_id the analysis has been done on
          @param analysis_id  : the analysis id
708
709
        """
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
Penom Nom's avatar
Penom Nom committed
710
711
712
        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)
713

Jerome Mariette's avatar
Jerome Mariette committed
714
    def add_analysis_to_run(self, run_id, analysis_id):
715
716
        """
        Add to the database a new Analyze done on a run
Jerome Mariette's avatar
Jerome Mariette committed
717
718
          @param run_id      : the run_id the analysis has been done on
          @param analysis_id  : the analysis id
719
720
        """
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
Penom Nom's avatar
Penom Nom committed
721
722
723
        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 )
724

Jerome Mariette's avatar
Jerome Mariette committed
725
    def add_result(self, analysis_id, file, key, value, group):
726
        """
Jerome Mariette's avatar
Jerome Mariette committed
727
728
729
        Add to the analysis a new result
          @param analysis_id : the analysis id
          @param file       : the file on which the analysis has been performed
730
731
732
733
734
          @param key        : the result key
          @param value      : the result value
          @param group      : the group value
        """
        crdate = str(time.mktime(datetime.date.today().timetuple())).split(".")[0]
Penom Nom's avatar
Penom Nom committed
735
736
737
738
        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 )
        self.execute(sql, commit = True )
739
        
Jerome Mariette's avatar
Jerome Mariette committed
740
    def update_analysis_field(self, analysis_id, field, value):
741
742
        """
        Update an Analyze
Jerome Mariette's avatar
Jerome Mariette committed
743
          @param analysis_id  : the analysis id
744
745
746
          @param field       : the field to update
          @param value       : the value
        """
Penom Nom's avatar
Penom Nom committed
747
748
        sql = "UPDATE tx_nG6_analyze SET %s = '%s'  WHERE uid= '%s' "% (field, self.esc_q(value), analysis_id )
        self.execute(sql, commit = True )
749

Jerome Mariette's avatar
Jerome Mariette committed
750
    def select_analysis_directory(self, analysis_id):
751
        """
Jerome Mariette's avatar
Jerome Mariette committed
752
753
754
        Return the analysis directory
          @param analysis_id  : the analysis ID
          @return            : the analysis directory
755
        """
Penom Nom's avatar
Penom Nom committed
756
757
758
        sql = "SELECT directory FROM tx_nG6_analyze WHERE uid='%s'" % analysis_id
        qresult = self.execute(sql)
        return qresult.rows[0][0]
759

Jerome Mariette's avatar
Jerome Mariette committed
760
    def select_analysis_run_id(self, analysis_id):
761
        """
Jerome Mariette's avatar
Jerome Mariette committed
762
763
764
        Return the analysis run id or None
          @param analysis_id  : the analysis ID
          @return            : the run id the analysis belongs to
765
        """
766
        try:
Penom Nom's avatar
Penom Nom committed
767
768
769
            sql = "SELECT run_id FROM tx_nG6_run_analyze WHERE analyze_id='%s'" % analysis_id
            qresult = self.execute(sql)
            return qresult.rows[0][0]
770
771
        except:
            return None
772

Jerome Mariette's avatar
Jerome Mariette committed
773
    def select_analysis(self, analysis_id):
774
        """
Jerome Mariette's avatar
Jerome Mariette committed
775
776
        Return the analysis infos
          @param analysis_id    : the analysis id to select
777
778
          @return: [name, date, description, software, options, version]
        """
Jerome Mariette's avatar
Jerome Mariette committed
779
        logging.getLogger("t3MySQLdb.select_analysis").debug("Selecting analysis id=" + str(analysis_id))
Penom Nom's avatar
Penom Nom committed
780
781
782
783
784
785
786
        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]]
787
        
Penom Nom's avatar
Penom Nom committed
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
    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',
815
                'tx_nG6_analyze.uid='+str(analysis_id),   
Penom Nom's avatar
Penom Nom committed
816
817
        ]
        
Penom Nom's avatar
Penom Nom committed
818
        sql = " ".join(parts)
Penom Nom's avatar
Penom Nom committed
819
        qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
820
821
        
        # project analysis, add project informations
Penom Nom's avatar
Penom Nom committed
822
823
        if qresult.rowcount > 0:
            row = qresult.rows[0]
Penom Nom's avatar
Penom Nom committed
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
            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',
867
                    'tx_nG6_analyze.uid=' + str(analysis_id),
Penom Nom's avatar
Penom Nom committed
868
                'ORDER BY',
Penom Nom's avatar
Penom Nom committed
869
870
871
                    'tx_nG6_analyze.name'
            ]
            
Penom Nom's avatar
Penom Nom committed
872
873
            
            sql = " ".join(parts)
Penom Nom's avatar
Penom Nom committed
874
            qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
875
876
            row = qresult.rows[0]

Penom Nom's avatar
Penom Nom committed
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
            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']
            }