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
            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,
205
                                                            self.esc_q(pdescription), self.esc_q(name), int(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), int(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" % int(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

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

        if is_editable : analysis_is_editable = 1
        else : analysis_is_editable = 0

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

965
                # run directories
966

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

986
987
        parts =  [
                    'UPDATE',
988
                        table,