t3MySQLdb.py 47.8 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# 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'
22
__email__ = 'support.bioinfo.genotoul@inra.fr'
23
24
__status__ = 'beta'

Celine Noirot's avatar
Celine Noirot committed
25
26
27
import time
import calendar
import logging
Penom Nom's avatar
Penom Nom committed
28
29
30
31
import pymysql
import pymysql.cursors
import collections

32

Penom Nom's avatar
Penom Nom committed
33
from .config_reader import NG6ConfigReader
34

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

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

57

Penom Nom's avatar
Penom Nom committed
58
59
60
61
62
63
64
65
66
    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
        #
67

68
69
        try:
            if dictc :
70
71
                return pymysql.connect(host = self.host,
                              user = self.user,
Penom Nom's avatar
Penom Nom committed
72
73
74
75
                              password = self.passwd,
                              db = self.db,
                              cursorclass = pymysql.cursors.DictCursor)

76
77
            return pymysql.connect(host = self.host,
                          user = self.user,
Penom Nom's avatar
Penom Nom committed
78
79
                          password = self.passwd,
                          db = self.db)
80
81
82
        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
83

Penom Nom's avatar
Penom Nom committed
84
85
86
87
88
89
90
91
    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
        """
92
        logging.getLogger("t3MySQLdb.execute").debug("Entering execute")
Penom Nom's avatar
Penom Nom committed
93
        connection = self.get_connection(dictc)
94
        logging.getLogger("t3MySQLdb.execute").debug("after connection " + str(connection))
Penom Nom's avatar
Penom Nom committed
95
96
97
98
99
100
101
102
103
        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()
104
            logging.getLogger("t3MySQLdb.execute").debug("before returning")
Penom Nom's avatar
Penom Nom committed
105
106
            return _QResult( rowcount = rowcount, rows = rows, lastrowid = id)
        except Exception as e:
107
            logging.getLogger("t3MySQLdb.execute").error("Exception" + str(e))
Penom Nom's avatar
Penom Nom committed
108
109
110
111
            if exc_msg : raise Exception(e, exc_msg)
            else : raise e
        finally:
            connection.close()
112

Penom Nom's avatar
Penom Nom committed
113
114
115
116
117
    def esc_q(self, val):
        """
            Escape quotes on a string value
        """
        return str(val).replace("'", "\'").replace('\"', '\"')
118

Celine Noirot's avatar
Celine Noirot committed
119
120
    #--------------------------------------------------------------------
    # User functions
121

122
123
124
125
126
127
    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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
        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
145
146
147
148
149
150
    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
151
152
153
        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]
154

Penom Nom's avatar
Penom Nom committed
155
156
157
158
    def is_ng6admin(self, login):
        """
            Return true if the login is a ng6 administrator
            @return: id
159
            @param login: the login name
Penom Nom's avatar
Penom Nom committed
160
        """
161
        sql = "SELECT usergroup FROM fe_users WHERE username = '%s'" % login
Penom Nom's avatar
Penom Nom committed
162
163
164
        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
165

Penom Nom's avatar
Penom Nom committed
166
    def get_users (self, project_id, right):
Celine Noirot's avatar
Celine Noirot committed
167
168
169
170
171
172
        """
            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
173
174
175
176
177
178
        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
179
        return users_ids
180

181
182
183
    #--------------------------------------------------------------------
    # Project functions

184
    def add_project(self, name, pdescription, cruser_login, space_id, hidden=1):
185
186
187
188
189
        """
        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
190
          @param cruser_login         : the administrator login
191
192
          @param hidden               : is the project hidden
        """
Penom Nom's avatar
Penom Nom committed
193
194
        if self.project_exists(name) :
            raise ValueError("Impossible to create project %s, this name is already used!"%name)
195

196
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
197
198
        if cruser_login: # If the administrator login is specified
            admin_id = self.get_user_id(cruser_login)
199

Penom Nom's avatar
Penom Nom committed
200
201
202
            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]
203
204
205
            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)
Penom Nom's avatar
Penom Nom committed
206
207
208
209
            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)
210

Penom Nom's avatar
Penom Nom committed
211
212
213
            return project_id
        else :
            raise Exception('t3MySQLdb', 'The administration login is required.')
214

Penom Nom's avatar
Penom Nom committed
215
216
217
218
219
    def project_exists(self,name):
        """
            Return true if the project associated with name exists
        """
        exists = False
Penom Nom's avatar
Penom Nom committed
220
221
222
223
        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
224
225
226
        except :
            pass
        return exists
227

228
229
    def select_project(self, project_id):
        """
230
        Return the project infos
231
232
233
          @param project_id     : the project id to select
          @return: [name, description]
        """
234
235
        try:
            logging.getLogger("t3MySQLdb.select_project").debug("Selecting project id=" + str(project_id))
236
            sql = "SELECT name, description, space_id FROM tx_nG6_project WHERE tx_nG6_project.uid ='" + str(project_id) + "'"
237
238
239
            logging.getLogger("t3MySQLdb.select_project").debug(sql)
            qresult = self.execute(sql)
            res = qresult.rows[0]
240
241
            logging.getLogger("t3MySQLdb.select_project").debug("Returning [" + str([res[0], res[1], res[2]]) + "]")
            return [res[0], res[1], res[2]]
242
243
244
        except Exception as e:
            logging.getLogger("t3MySQLdb.select_project").error("Exception while getting the project : " +str(e))
            raise e
245

246
247
248
249
250
251
    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
252
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("Selecting project name=" + str(name))
253
        sql = "SELECT uid, description, space_id FROM tx_nG6_project WHERE tx_nG6_project.name ='" + str(name) + "'"
254
        logging.getLogger("t3MySQLdb.select_project_from_name").debug(sql)
Penom Nom's avatar
Penom Nom committed
255
        qresult = self.execute(sql)
256
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("before getting qresult")
Penom Nom's avatar
Penom Nom committed
257
        res = qresult.rows[0]
258
259
260
        logging.getLogger("t3MySQLdb.select_project_from_name").debug("Returning [" + str([res[0], res[1], res[2]]) + "]")
        return [res[0], res[1], res[2]]

261
262
    def get_project_analysis_ids(self, project_id):
        """
Jerome Mariette's avatar
Jerome Mariette committed
263
        Return a list of analysis ids belonging to the project
264
          @param project_id  : the project id
Jerome Mariette's avatar
Jerome Mariette committed
265
          @return            : table of analysis ids
266
        """
Penom Nom's avatar
Penom Nom committed
267
268
269
        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 ]
270

271
272
273
274
275
276
277
    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
278
279
280
            sql = "SELECT project_id FROM tx_nG6_project_analyze WHERE analyze_id='%s'" % analysis_id
            qresult = self.execute(sql)
            return qresult.rows[0][0]
281
282
        except:
            return None
283
284
285
286
287
288
289

    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
290
291
292
        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 ]
293
294
295
296
297

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

    def add_run(self, name, date, directory, species, data_nature,
Celine Noirot's avatar
Celine Noirot committed
298
                type, nb_sequences, full_seq_size, description, sequencer, cruser_login, retention_date, hidden=1):
299
300
301
302
303
304
305
306
307
308
309
310
        """
        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
311
          @param cruser_login  : the create user login
Celine Noirot's avatar
Celine Noirot committed
312
          @param retention_date: the retention date for storage.
Jerome Mariette's avatar
Jerome Mariette committed
313
          @param hidden        : is the analysis hidden
314
        """
315
        logging.getLogger("t3MySQLdb.add_run").debug("Inserting run with date of run =" + str(date))
Penom Nom's avatar
Penom Nom committed
316
        cruser_id = self.get_user_id(cruser_login)
317
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
318
        sql = "INSERT INTO tx_nG6_run (pid, tstamp, crdate, cruser_id, name, date, directory, species, data_nature,\
319
        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',\
320
321
322
        '%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,
323
                                                             full_seq_size, self.esc_q(description), hidden, self.esc_q(sequencer),retention_date, "stored","0","0","0")
Penom Nom's avatar
Penom Nom committed
324
325
        qresult = self.execute(sql, commit = True)
        run_id = qresult.lastrowid
326

Penom Nom's avatar
Penom Nom committed
327
328
329
330
331
332
        # 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.')
333
334
335
336
337
338
339
340
        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
        """
341
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
342
343
344
        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)
345

Celine Noirot's avatar
Celine Noirot committed
346
347
348
349
350
351
352
    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
353
354
355
356
357
        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]
358

359
360
361
362
363
364
365
    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
366
367
368
369
        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]
370
371
372
        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
373
374
375
376
    def select_run_informations(self, run_id):
        """
            Return run informations allong with project information
        """
377

Penom Nom's avatar
Penom Nom committed
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
        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
405
        sql = " ".join(parts)
Penom Nom's avatar
Penom Nom committed
406
        qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
407
        row = qresult.rows[0]
408
409
410
411
412
        return {
                'id'            : row['run_id'],
                'project_id'    : row['project_id'],
                'project_name'  : row['project_name'],
                'directory'     : row['run_directory'],
Penom Nom's avatar
Penom Nom committed
413
414
415
416
417
418
419
420
421
                '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'],
422
423
424
                'description'   : row['run_description']
        }

425
426
427
428
429
430
    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
431
432
433
        sql = "SELECT directory FROM tx_nG6_run WHERE uid='%s'" % run_id
        qresult = self.execute(sql)
        return qresult.rows[0][0]
434

435
    def update_run_info(self, run_id, name=None, date=None, directory=None, species=None, data_nature=None, type=None, nb_sequences=None,
Penom Nom's avatar
Penom Nom committed
436
                        full_seq_size=None, description=None, sequencer=None, hidden=None, storage_size=None):
437
438
439
440
441
442
443
444
445
446
447
448
449
450
        """
        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
451
          @param hidden        : is the analysis hidden
452
          @param storage_size  : the size of the run
453
        """
Penom Nom's avatar
Penom Nom committed
454
        set_values = []
455
        if name:
Penom Nom's avatar
Penom Nom committed
456
            set_values.append( "name='%s'" % self.esc_q(name) )
457
        if date:
458
            set_values.append( "date='%s'" % time.mktime(date.timetuple()) )
459
        if directory:
Penom Nom's avatar
Penom Nom committed
460
            set_values.append(  "directory='%s'" % directory )
461
        if species:
Penom Nom's avatar
Penom Nom committed
462
            set_values.append(  "species='%s'" % self.esc_q(species))
463
        if data_nature:
Penom Nom's avatar
Penom Nom committed
464
            set_values.append(  "data_nature='%s'" % self.esc_q(data_nature) )
465
        if type:
Penom Nom's avatar
Penom Nom committed
466
            set_values.append(  "type='%s'" % self.esc_q(type))
Penom Nom's avatar
Penom Nom committed
467
        if nb_sequences is not None:
468
            set_values.append(  "nb_sequences='%s'" % nb_sequences )
Penom Nom's avatar
Penom Nom committed
469
        if full_seq_size is not None:
Penom Nom's avatar
Penom Nom committed
470
            set_values.append( "full_seq_size='%s'" % full_seq_size )
471
        if description:
Penom Nom's avatar
Penom Nom committed
472
            set_values.append(  "description='%s'" % self.esc_q(description))
473
        if hidden:
474
            set_values.append(  "hidden=%s" % hidden )
475
        if sequencer:
Penom Nom's avatar
Penom Nom committed
476
            set_values.append(  "sequencer='%s'" % self.esc_q(sequencer))
477
        if storage_size:
478
479
            set_values.append(  "storage_size='%s'" % storage_size )

Penom Nom's avatar
Penom Nom committed
480
481
        sql = req =  "UPDATE tx_nG6_run SET " + ",".join(set_values)+ "  WHERE uid = '"+ str(run_id) + "'"
        self.execute(sql, commit = True)
482
483
484

    def get_run_analysis_ids(self, run_id):
        """
Jerome Mariette's avatar
Jerome Mariette committed
485
        Return a list of analysis ids belonging to the run
486
          @param run_id  : the run id
Jerome Mariette's avatar
Jerome Mariette committed
487
          @return        : table of analysis ids
488
        """
Penom Nom's avatar
Penom Nom committed
489
490
491
        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  ]
492

Penom Nom's avatar
Penom Nom committed
493
    def get_user_run_analysis(self, user_id, run_id, order_by = "", limit = ""):
494

Penom Nom's avatar
Penom Nom committed
495
        if user_id :
496
            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)'
Penom Nom's avatar
Penom Nom committed
497
498
        else :
            where = 'tx_nG6_run_analyze.run_id=' + str(run_id) +' AND tx_nG6_project.public=0 AND tx_nG6_analyze.hidden=0'
499

Penom Nom's avatar
Penom Nom committed
500
        parts = [
501
502
503
            'SELECT' ,
                'tx_nG6_analyze.uid AS analyze_id,',
                'tx_nG6_analyze.directory AS analyze_directory,',
Penom Nom's avatar
Penom Nom committed
504
505
506
507
508
509
510
511
512
513
                '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 ',
514
            'FROM',
Penom Nom's avatar
Penom Nom committed
515
516
517
518
                '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 ',
519
                    'INNER JOIN tx_nG6_project ON tx_nG6_project_run.project_id = tx_nG6_project.uid ',
Penom Nom's avatar
Penom Nom committed
520
521
522
523
524
525
                    '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 :
526
            parts.extend(['ORDER BY', order_by])
Penom Nom's avatar
Penom Nom committed
527
528
        if limit :
            parts.extend(['LIMIT', limit])
529

Penom Nom's avatar
Penom Nom committed
530
531
        sql = " ".join(parts)
        qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
532
        results = {}
533

Penom Nom's avatar
Penom Nom committed
534
        for row in qresult.rows :
Penom Nom's avatar
Penom Nom committed
535
            analyze_id = str(row['analyze_id']);
536

Penom Nom's avatar
Penom Nom committed
537
            if analyze_id not in results :
Penom Nom's avatar
Penom Nom committed
538
                results[analyze_id] = {
539
                    'directory' : row['analyze_directory'],
Penom Nom's avatar
Penom Nom committed
540
541
542
543
544
545
546
547
548
549
550
551
                    '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']
                }
552

Penom Nom's avatar
Penom Nom committed
553
554
        return results

555
    def update_sample_info(self, id , sample_id = None, name = None, reads1 = None, reads2 = None,
Penom Nom's avatar
Penom Nom committed
556
557
                           description = None, type = None, insert_size = None, nb_sequences = None, species = None,
                           full_seq_size = None):
Penom Nom's avatar
Penom Nom committed
558
        set_values = []
Penom Nom's avatar
Penom Nom committed
559
        if sample_id:
Penom Nom's avatar
Penom Nom committed
560
            set_values.append( "sample_id='%s'" % sample_id )
Penom Nom's avatar
Penom Nom committed
561
        if name:
Penom Nom's avatar
Penom Nom committed
562
            set_values.append( "name='%s'" % self.esc_q(name) )
Penom Nom's avatar
Penom Nom committed
563
        if reads1:
Penom Nom's avatar
Penom Nom committed
564
565
566
            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
567
        if reads2:
Penom Nom's avatar
Penom Nom committed
568
569
570
            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
571
        if description:
Penom Nom's avatar
Penom Nom committed
572
            set_values.append( "description='%s'" % self.esc_q(description) )
Penom Nom's avatar
Penom Nom committed
573
        if type:
Penom Nom's avatar
Penom Nom committed
574
            set_values.append( "type='%s'" % self.esc_q(type))
Penom Nom's avatar
Penom Nom committed
575
        if insert_size:
Penom Nom's avatar
Penom Nom committed
576
            set_values.append( "insert_size='%s'" % self.esc_q(insert_size) )
Penom Nom's avatar
Penom Nom committed
577
        if nb_sequences is not None:
Penom Nom's avatar
Penom Nom committed
578
            set_values.append( "nb_sequences='%s'" % nb_sequences )
Penom Nom's avatar
Penom Nom committed
579
        if full_seq_size is not None:
Penom Nom's avatar
Penom Nom committed
580
            set_values.append( "full_seq_size='%s'" % full_seq_size )
Penom Nom's avatar
Penom Nom committed
581
        if species:
Penom Nom's avatar
Penom Nom committed
582
            set_values.append( "species='%s'" % self.esc_q(species) )
583

Penom Nom's avatar
Penom Nom committed
584
        sql = "UPDATE tx_nG6_sample SET " + ",".join(set_values) + "  WHERE uid = '"+ str(id) + "'"
585

Penom Nom's avatar
Penom Nom committed
586
        self.execute(sql, commit = True)
587
588


Penom Nom's avatar
Penom Nom committed
589
    def add_sample_to_run(self, run_id, sample_id, reads1, reads2 = None, name = None , description = None,
Penom Nom's avatar
Penom Nom committed
590
                          type = None, insert_size = None, species = None, nb_sequences = None):
591
592
593
        """
        add a sample to the run
          @param run_id      : the run id
Penom Nom's avatar
Penom Nom committed
594
595
          @param sample_id   : the samples uniq identifier
          @param name        : the sample name
596
597
          @return sample id
        """
598
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
599
        column_names = [ 'pid', 'tstamp', 'run_id', 'sample_id', 'reads1' ]
600
601
602
        column_vals = [ "'%s'" % self.cfg_reader.get_pid(),
                       "'%s'" % crdate,
                       "'%s'" % run_id,
Penom Nom's avatar
Penom Nom committed
603
604
                       "'%s'" % self.esc_q(sample_id),
                        "'%s'" % self.esc_q(','.join(reads1))]
Penom Nom's avatar
Penom Nom committed
605
        if name :
Penom Nom's avatar
Penom Nom committed
606
607
            column_names.append('name')
            column_vals.append("'%s'" % self.esc_q(name))
608

Penom Nom's avatar
Penom Nom committed
609
        if description :
Penom Nom's avatar
Penom Nom committed
610
611
            column_names.append('description')
            column_vals.append("'%s'" % self.esc_q(description))
612

Penom Nom's avatar
Penom Nom committed
613
        if reads2 :
Penom Nom's avatar
Penom Nom committed
614
615
616
            column_names.append('reads2')
            column_vals.append("'%s'" % self.esc_q(','.join(reads2)) )

Penom Nom's avatar
Penom Nom committed
617
        if type :
Penom Nom's avatar
Penom Nom committed
618
619
            column_names.append('type')
            column_vals.append("'%s'" % self.esc_q(type) )
620

Penom Nom's avatar
Penom Nom committed
621
        if insert_size :
Penom Nom's avatar
Penom Nom committed
622
623
            column_names.append('insert_size')
            column_vals.append("'%s'" % self.esc_q(insert_size))
624

Penom Nom's avatar
Penom Nom committed
625
        if species :
Penom Nom's avatar
Penom Nom committed
626
627
            column_names.append('species')
            column_vals.append("'%s'" % self.esc_q(species) )
628

Penom Nom's avatar
Penom Nom committed
629
        if nb_sequences :
Penom Nom's avatar
Penom Nom committed
630
631
            column_names.append('nb_sequences')
            column_vals.append("'%s'" % self.esc_q(nb_sequences))
632

Penom Nom's avatar
Penom Nom committed
633
634
        sql = "INSERT INTO tx_nG6_sample (%s) VALUES (%s)" % (", ".join(column_names), ", ".join(column_vals) )
        qresult = self.execute(sql, commit = True)
635

Penom Nom's avatar
Penom Nom committed
636
        sample_id = qresult.lastrowid
637

Penom Nom's avatar
Penom Nom committed
638
639
640
641
        # 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]
642

Penom Nom's avatar
Penom Nom committed
643
644
        if( str(sample_pid) != str(self.cfg_reader.get_pid()) ):
            raise Exception('t3MySQLdb', 'The id of the sample ' + name + ' cannot be to retrieve.')
645
        return sample_id
646
647
648
649
650

    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
651
          @return a list of dictionary
652
        """
Penom Nom's avatar
Penom Nom committed
653
654
        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
655
        samples = []
Penom Nom's avatar
Penom Nom committed
656
        for result in qresult.rows:
Penom Nom's avatar
Penom Nom committed
657
658
659
660
661
662
663
664
665
666
667
            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
668
669
                'nb_sequences'  : result[10],
                'full_seq_size' : result[11],
Penom Nom's avatar
Penom Nom committed
670
671
            })
        return samples
672
673
674
675

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

Celine Noirot's avatar
Celine Noirot committed
676
    def add_analysis(self, aclass, name, description, cruser_login, date, directory, software, version, params, is_editable, retention_date, hidden=1, parent_uid=0):
677
678
        """
        Update an Analyze
Celine Noirot's avatar
Celine Noirot committed
679
680
681
682
683
684
685
686
687
          @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
Celine Noirot's avatar
Celine Noirot committed
688
          @param retention_date:the retention date for storage period 
Celine Noirot's avatar
Celine Noirot committed
689
690
          @param hidden       : is the analysis hidden
          @param parent_uid   : the analysis parent uid
691
          @param is_editable : True if analysis has been added by an user
692
        """
Penom Nom's avatar
Penom Nom committed
693
        cruser_id = self.get_user_id(cruser_login)
694
        crdate = str(time.time()).split(".")[0]
695
696
697
698

        if is_editable : analysis_is_editable = 1
        else : analysis_is_editable = 0

Penom Nom's avatar
Penom Nom committed
699
700
        sql = "INSERT INTO tx_nG6_analyze (pid, tstamp, crdate, cruser_id, class, name, \
        description, date, directory, software, version, \
701
        hidden, params, is_editable, parent_uid, retention_date, data_state, purged_date, purged_size, mail_sent_date) \
Penom Nom's avatar
Penom Nom committed
702
        VALUES ('%s', '%s', '%s', %s, '%s','%s', '%s', \
703
704
705
        '%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),
706
                                                          hidden, self.esc_q(params), analysis_is_editable, parent_uid, retention_date, "stored","0","0","0")
Penom Nom's avatar
Penom Nom committed
707
708
        qresult = self.execute(sql, commit= True)
        analysis_id = qresult.lastrowid
709

Penom Nom's avatar
Penom Nom committed
710
711
        sql = "SELECT directory FROM tx_nG6_analyze WHERE uid=%s" % analysis_id
        qresult = self.execute(sql)
712

Penom Nom's avatar
Penom Nom committed
713
714
715
        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
716
        return analysis_id
717

Jerome Mariette's avatar
Jerome Mariette committed
718
    def add_analysis_to_project(self, project_id, analysis_id):
719
720
        """
        Add to the database a new Analyze done on a project
Jerome Mariette's avatar
Jerome Mariette committed
721
722
          @param project_id  : the project_id the analysis has been done on
          @param analysis_id  : the analysis id
723
        """
724
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
725
        sql = "INSERT INTO tx_nG6_project_analyze (pid, tstamp, crdate, project_id,\
726
         analyze_id) VALUES('%s','%s','%s','%s','%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, project_id, analysis_id )
Penom Nom's avatar
Penom Nom committed
727
        self.execute(sql, commit = True)
728

Jerome Mariette's avatar
Jerome Mariette committed
729
    def add_analysis_to_run(self, run_id, analysis_id):
730
731
        """
        Add to the database a new Analyze done on a run
Jerome Mariette's avatar
Jerome Mariette committed
732
733
          @param run_id      : the run_id the analysis has been done on
          @param analysis_id  : the analysis id
734
        """
735
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
736
        sql = "INSERT INTO tx_nG6_run_analyze (pid, tstamp, crdate, run_id, analyze_id) \
737
        VALUES('%s', '%s', '%s', '%s', '%s')" % ( self.cfg_reader.get_pid(), crdate, crdate, run_id, analysis_id)
Penom Nom's avatar
Penom Nom committed
738
        self.execute(sql, commit = True )
739

Jerome Mariette's avatar
Jerome Mariette committed
740
    def add_result(self, analysis_id, file, key, value, group):
741
        """
Jerome Mariette's avatar
Jerome Mariette committed
742
743
744
        Add to the analysis a new result
          @param analysis_id : the analysis id
          @param file       : the file on which the analysis has been performed
745
746
747
748
          @param key        : the result key
          @param value      : the result value
          @param group      : the group value
        """
749
        crdate = str(time.time()).split(".")[0]
Penom Nom's avatar
Penom Nom committed
750
        sql = "INSERT INTO tx_nG6_result (pid, tstamp, crdate, analyze_id, file, rkey, rvalue, rgroup) \
751
        VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % ( self.cfg_reader.get_pid(), crdate, crdate,
Penom Nom's avatar
Penom Nom committed
752
                                                                    analysis_id, file, key, value, group )
753
        logging.getLogger("t3MySQL").debug("add_result. sql created : " + sql)
Penom Nom's avatar
Penom Nom committed
754
        self.execute(sql, commit = True )
755
        logging.getLogger("t3MySQL").debug("add_result. executed")
756

Jerome Mariette's avatar
Jerome Mariette committed
757
    def update_analysis_field(self, analysis_id, field, value):
758
759
        """
        Update an Analyze
Jerome Mariette's avatar
Jerome Mariette committed
760
          @param analysis_id  : the analysis id
761
762
763
          @param field       : the field to update
          @param value       : the value
        """
Penom Nom's avatar
Penom Nom committed
764
765
        sql = "UPDATE tx_nG6_analyze SET %s = '%s'  WHERE uid= '%s' "% (field, self.esc_q(value), analysis_id )
        self.execute(sql, commit = True )
766

Jerome Mariette's avatar
Jerome Mariette committed
767
    def select_analysis_directory(self, analysis_id):
768
        """
Jerome Mariette's avatar
Jerome Mariette committed
769
770
771
        Return the analysis directory
          @param analysis_id  : the analysis ID
          @return            : the analysis directory
772
        """
Penom Nom's avatar
Penom Nom committed
773
774
775
        sql = "SELECT directory FROM tx_nG6_analyze WHERE uid='%s'" % analysis_id
        qresult = self.execute(sql)
        return qresult.rows[0][0]
776

Jerome Mariette's avatar
Jerome Mariette committed
777
    def select_analysis_run_id(self, analysis_id):
778
        """
Jerome Mariette's avatar
Jerome Mariette committed
779
780
781
        Return the analysis run id or None
          @param analysis_id  : the analysis ID
          @return            : the run id the analysis belongs to
782
        """
783
        try:
Penom Nom's avatar
Penom Nom committed
784
785
786
            sql = "SELECT run_id FROM tx_nG6_run_analyze WHERE analyze_id='%s'" % analysis_id
            qresult = self.execute(sql)
            return qresult.rows[0][0]
787
788
        except:
            return None
789

Jerome Mariette's avatar
Jerome Mariette committed
790
    def select_analysis(self, analysis_id):
791
        """
Jerome Mariette's avatar
Jerome Mariette committed
792
793
        Return the analysis infos
          @param analysis_id    : the analysis id to select
794
795
          @return: [name, date, description, software, options, version]
        """
Jerome Mariette's avatar
Jerome Mariette committed
796
        logging.getLogger("t3MySQLdb.select_analysis").debug("Selecting analysis id=" + str(analysis_id))
Penom Nom's avatar
Penom Nom committed
797
798
        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)
799
        qresult = self.execute(sql,
Penom Nom's avatar
Penom Nom committed
800
801
802
803
                                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]]
804

Penom Nom's avatar
Penom Nom committed
805
806
807
    def select_analysis_informations(self, analysis_id):
        """
            Select all information on an analyse
808
            @param analysis_id:
Penom Nom's avatar
Penom Nom committed
809
        """
810

Penom Nom's avatar
Penom Nom committed
811
812
        parts = [
            'SELECT',
813
814
815
816
                '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,',
Penom Nom's avatar
Penom Nom committed
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
                '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',
832
                'tx_nG6_analyze.uid='+str(analysis_id),
Penom Nom's avatar
Penom Nom committed
833
        ]
834

Penom Nom's avatar
Penom Nom committed
835
        sql = " ".join(parts)
Penom Nom's avatar
Penom Nom committed
836
        qresult = self.execute(sql, dictc = True)
837

Penom Nom's avatar
Penom Nom committed
838
        # project analysis, add project informations
Penom Nom's avatar
Penom Nom committed
839
840
        if qresult.rowcount > 0:
            row = qresult.rows[0]
Penom Nom's avatar
Penom Nom committed
841
            return {
842
                    'project_name'  : row['project_name'],
Penom Nom's avatar
Penom Nom committed
843
                    'project_id'    : row['project_id'],
844
845
846
                    'run_name'      : None,
                    'run_id'        : None,
                    'directory'     : row['analyze_directory'],
Penom Nom's avatar
Penom Nom committed
847
848
849
850
851
852
853
854
855
856
857
                    '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 :
858

Penom Nom's avatar
Penom Nom committed
859
860
861
862
863
864
            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,',
865
866
                    'tx_nG6_analyze.uid AS analyze_id,',
                    'tx_nG6_analyze.directory AS analyze_directory,',
Penom Nom's avatar
Penom Nom committed
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
                    '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',
884
                    'tx_nG6_analyze.uid=' + str(analysis_id),
Penom Nom's avatar
Penom Nom committed
885
                'ORDER BY',
Penom Nom's avatar
Penom Nom committed
886
887
                    'tx_nG6_analyze.name'
            ]
888
889


Penom Nom's avatar
Penom Nom committed
890
            sql = " ".join(parts)
Penom Nom's avatar
Penom Nom committed
891
            qresult = self.execute(sql, dictc = True)
Penom Nom's avatar
Penom Nom committed
892
893
            row = qresult.rows[0]

Penom Nom's avatar
Penom Nom committed
894
            return {
895
                'project_name' : row['project_name'],
Penom Nom's avatar
Penom Nom committed
896
                'project_id'   : row['project_id'],
897
898
899
                'run_name'     : row['run_name'],
                'run_id'       : row['run_id'],
                'directory'    : row['analyze_directory'],
Penom Nom's avatar
Penom Nom committed
900
901
902
903
904
905
906
                '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'],
907
                'version'      : row['analyze_version'],
Penom Nom's avatar
Penom Nom committed
908
909
                'is_editable'  : row['analyze_is_editable']
            }
910

911
912
913
    def select_purge_demand_directories(self, demand_id):
            """
                Select all directories to purge
914
                @param purge_demand_id:
915
916
            """
            dirs=[]
917

918
919
920
            if ',' in demand_id :
                parts = [
                    'SELECT',
921
922
923
                        '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',
924
925
926
                    'FROM',
                        'tx_nG6_purge_demand ',
                    'WHERE',
927
                        'tx_nG6_purge_demand.uid in ('+str(demand_id)+')',
928
929
930
931
                ]
            else:
                parts = [
                    'SELECT',
932
933
934
                        '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',
935
936
937
                    'FROM',
                        'tx_nG6_purge_demand ',
                    'WHERE',
938
                        'tx_nG6_purge_demand.uid = '+str(demand_id),
939
                ]
940

941
942
943
944
945
946
947
948
949
950
951
            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',
952
                        'tx_nG6_analyze.directory AS analyze_directory',
953
954
955
956
957
958
959
                    'FROM',
                        'tx_nG6_analyze',
                    'WHERE',
                        'tx_nG6_analyze.uid IN (' + ','.join(analysis_ids) +')'
                ]
                sql = " ".join(parts)
                qresult = self.execute(sql, dictc = True)
960

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

964
                # run directories
965

966
967
                parts = [
                    'SELECT',
968
                        'tx_nG6_run.directory AS run_directory',
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
                    '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"
984

985
986
        parts =  [
                    'UPDATE',
987
                        table,
988
989
990
                    'SET']
        update_part=[]
        for f,v in zip(field,value):