flocklab.py 51.9 KB
Newer Older
Reto Da Forno's avatar
Reto Da Forno committed
1
#!/usr/bin/env python3
2

3
4
5
6
7
##############################################################################
# FlockLab library, runs on the test management server
##############################################################################

import sys, os, smtplib, MySQLdb, MySQLdb.cursors, configparser, time, re, errno, random, subprocess, string, logging, logging.config, traceback, numpy, calendar, matplotlib.figure, matplotlib.backends.backend_agg, tempfile, lxml.etree
8
9
10
11
12
13
14
15
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.utils import formatdate, make_msgid

### Global variables ###
SUCCESS = 0
Reto Da Forno's avatar
Reto Da Forno committed
16
FAILED  = -2    # note: must be negative, and -1 (= 255) is reserved for SSH error
17
scriptpath = os.path.dirname(os.path.abspath(sys.argv[0]))
Reto Da Forno's avatar
Reto Da Forno committed
18
scriptname = os.path.basename(os.path.abspath(sys.argv[0]))   # name of caller script
19
20
configfile = "/home/flocklab/flocklab_config.ini"
loggerconf = scriptpath + '/logging.conf'
21
config = None
Reto Da Forno's avatar
Reto Da Forno committed
22
23
logger = None
debug = True
24
25
26
27
28

# Set timezone to UTC ---
os.environ['TZ'] = 'UTC'
time.tzset()

29

30
31
32
33
34
35
36
37
38
39
40
41
##############################################################################
#
# log_fallback - a way to log errors if the regular log file is unavailable
#
##############################################################################
def log_fallback(msg):
    #syslog.syslog(syslog.LOG_ERR, msg)    # -> requires 'import syslog'
    #print(msg, file=sys.stderr)
    print(msg)
### END log_fallback()


42
43
44
45
46
47
##############################################################################
#
# load_config - loads the config from the ini file and stores it in a global variable
#
##############################################################################
def load_config():
48
    global config
Reto Da Forno's avatar
Reto Da Forno committed
49
    if config:
50
51
        if logger:
            logger.warn("Config already loaded")
Reto Da Forno's avatar
Reto Da Forno committed
52
        return SUCCESS
53
54
55
    config = get_config()
    if not config:
        error_logandexit("Could not load config file.")
56
### END load_config()
57
58
59
60


##############################################################################
#
Reto Da Forno's avatar
Reto Da Forno committed
61
# get_config - read config file and return it to caller.
62
63
#
##############################################################################
64
65
def get_config():
    global config
Reto Da Forno's avatar
Reto Da Forno committed
66
67
68
69
    # if already loaded, return
    if config:
        return config
    try:
Reto Da Forno's avatar
Reto Da Forno committed
70
        config = configparser.SafeConfigParser(comment_prefixes=('#', ';'), inline_comment_prefixes=(';'))
71
        config.read(configfile)
Reto Da Forno's avatar
Reto Da Forno committed
72
73
    except:
        logger = get_logger()
74
        logger.error("Could not read '%s' because: %s, %s" % (configfile, str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
75
76
        config = None
    return config
77
78
79
### END get_config()


Reto Da Forno's avatar
Reto Da Forno committed
80
81
82
83
84
##############################################################################
#
# init_logger - Open a logger and keep it in a global variable.
#
##############################################################################
85
def init_logger(loggername=scriptname):
Reto Da Forno's avatar
Reto Da Forno committed
86
87
88
89
    global logger
    if logger:
        logger.warn("Logger already initialized.")
        return SUCCESS        # already initialized
90
91
92
    logger = get_logger(loggername)
    if not logger:
        error_logandexit("Failed to init logger.")
Reto Da Forno's avatar
Reto Da Forno committed
93
94
95
### END init_logger()


96
97
98
99
100
##############################################################################
#
# get_logger - Open a logger for the caller.
#
##############################################################################
101
def get_logger(loggername=scriptname, debug=False):
Reto Da Forno's avatar
Reto Da Forno committed
102
103
104
105
    global logger
    # if it already exists, return logger
    if logger:
        return logger
106
107
    if not os.path.isfile(loggerconf):
        log_fallback("[FlockLab] File '%s' not found." % (loggerconf))
Reto Da Forno's avatar
Reto Da Forno committed
108
        return None
Reto Da Forno's avatar
Reto Da Forno committed
109
    try:
110
        logging.config.fileConfig(loggerconf)
Reto Da Forno's avatar
Reto Da Forno committed
111
        logger = logging.getLogger(loggername)
112
113
        if debug:
            logger.setLevel(logging.DEBUG)
114
115
        else:
            logger.setLevel(logging.INFO)
Reto Da Forno's avatar
Reto Da Forno committed
116
    except:
117
        log_fallback("[FlockLab %s] Could not open logger because: %s, %s" %(str(loggername), str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
118
119
        logger = None
    return logger
120
121
122
### END get_logger()


123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
##############################################################################
#
# logging helpers
#
##############################################################################
def log_info(msg=""):
    global logger
    logger.info(msg)
### END log_info()

def log_error(msg=""):
    global logger
    logger.error(msg)
### END log_error()

def log_warning(msg=""):
    global logger
    logger.warn(msg)
### END log_warning()

def log_debug(msg=""):
    global logger
    logger.debug(msg)
### END log_debug()


149
150
151
152
153
##############################################################################
#
# connect_to_db - Connect to the FlockLab database
#
##############################################################################
Reto Da Forno's avatar
Reto Da Forno committed
154
def connect_to_db():
155
156
    global config
    # if config not yet available, then load it
Reto Da Forno's avatar
Reto Da Forno committed
157
    if not config or not isinstance(config, configparser.SafeConfigParser):
158
        load_config()
Reto Da Forno's avatar
Reto Da Forno committed
159
160
161
    try:
        cn = MySQLdb.connect(host=config.get('database','host'), user=config.get('database','user'), passwd=config.get('database','password'), db=config.get('database','database'), charset='utf8', use_unicode=True) 
        cur = cn.cursor()
Reto Da Forno's avatar
Reto Da Forno committed
162
        #cur.execute("SET sql_mode=''")     # TODO check whether this is needed
Reto Da Forno's avatar
Reto Da Forno committed
163
    except:
164
        logger = get_logger()
Reto Da Forno's avatar
Reto Da Forno committed
165
166
        logger.error("Could not connect to the database because: %s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        raise Exception
Reto Da Forno's avatar
Reto Da Forno committed
167
    return (cn, cur)
168
169
170
### END connect_to_db()


Reto Da Forno's avatar
Reto Da Forno committed
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
##############################################################################
#
# is_user_admin - Check if a user ID belongs to an admin.
#
##############################################################################
def is_user_admin(cursor=None, userid=0):
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(userid) != int) or (userid <= 0)):
        return False
    # Get the addresses from the database:
    try:
        cursor.execute("SELECT `role` FROM `tbl_serv_users` WHERE `serv_users_key` = %d" %userid)
        rs = cursor.fetchone()
        if ((rs != None) and (rs[0] == 'admin')):
            return True
    except:
187
        logger = get_logger()
Reto Da Forno's avatar
Reto Da Forno committed
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
        logger.error("Failed to fetch user role from database: %s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return False
    return False
### END is_user_admin()


##############################################################################
#
# is_user_internal - Check if an ID belongs to an internal user.
#
##############################################################################
def is_user_internal(cursor=None, userid=0):
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(userid) != int) or (userid <= 0)):
        return False
    # Get the addresses from the database:
    try:
        cursor.execute("SELECT `role` FROM `tbl_serv_users` WHERE `serv_users_key` = %d" %userid)
        rs = cursor.fetchone()
        if ((rs != None) and (rs[0] == 'internal')):
            return True
    except:
        # There was an error in the database connection:
211
        logger = get_logger()
Reto Da Forno's avatar
Reto Da Forno committed
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
        logger.error("Failed to fetch user role from database: %s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return False
    return False
### END is_user_internal()


##############################################################################
#
# get_user_role - Get the user role (user, admin or internal).
#
##############################################################################
def get_user_role(cursor=None, userid=0):
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(userid) != int) or (userid <= 0)):
        return None
    # Get the addresses from the database:
    try:
229
        cursor.execute("SELECT `role` FROM `tbl_serv_users` WHERE `serv_users_key` = %d" % userid)
Reto Da Forno's avatar
Reto Da Forno committed
230
        rs = cursor.fetchone()
231
        if rs:
Reto Da Forno's avatar
Reto Da Forno committed
232
233
234
            return rs[0]
    except:
        # There was an error in the database connection:
235
        logger = get_logger()
Reto Da Forno's avatar
Reto Da Forno committed
236
237
238
239
240
241
        logger.error("Failed to fetch user role from database: %s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return None
    return None
### END get_user_role()


242
243
244
245
246
247
##############################################################################
#
# send_mail - send a mail to the specified user(s)
#
##############################################################################
def send_mail(subject="[FlockLab]", message="", recipients="", attachments=[]):
248
249
    if not config:
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
250
251
    # Check the arguments:
    if ((type(message) != str) or ((type(recipients) != str) and (type(recipients) != list) and (type(recipients) != tuple)) or (type(attachments) != list)):
Reto Da Forno's avatar
Reto Da Forno committed
252
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
253
254
255
256
    # Check if attachments exist in file system:
    if (len(attachments) > 0):
        for path in attachments:
            if not os.path.isfile(path):
Reto Da Forno's avatar
Reto Da Forno committed
257
                return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
258
259
260
261
262
263
264
265
266

    # Create the email:
    mail = MIMEMultipart()
    
    # Attach the message text:
    mail.attach(MIMEText(str(message)))
    
    # Set header fields:
    mail['Subject'] = str(subject)
267
    mail['From'] = "FlockLab <%s>" % config.get('email', 'flocklab_email')
Reto Da Forno's avatar
Reto Da Forno committed
268
269
270
271
272
273
274
    mail['Date'] = formatdate(localtime=True)
    mail['Message-ID'] = make_msgid()
    if ((type(recipients) == tuple) or (type(recipients) == list)):
        mail['To'] = ', '.join(recipients)
    elif (type(recipients) == str):
        mail['To'] = recipients
    else:
Reto Da Forno's avatar
Reto Da Forno committed
275
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
276
277
278
279
280
281
282
283
284
285
286
287
288
289
    
    # If there are attachments, attach them to the email:
    for path in attachments:
        fp = open(path, 'rb')
        fil = MIMEBase('application', 'octet-stream')
        fil.set_payload(fp.read())
        fp.close()
        encoders.encode_base64(fil)
        fil.add_header('Content-Disposition', 'attachment', filename=os.path.basename(path))
        mail.attach(fil)

    # Establish an SMTP object and connect to your mail server
    try:
        s = smtplib.SMTP()
Reto Da Forno's avatar
Reto Da Forno committed
290
        s.connect(config.get('email', 'mailserver'))
Reto Da Forno's avatar
Reto Da Forno committed
291
        # Send the email - real from, real to, extra headers and content ...
Reto Da Forno's avatar
Reto Da Forno committed
292
        s.sendmail(config.get('email', 'flocklab_email'), recipients, mail.as_string())
Reto Da Forno's avatar
Reto Da Forno committed
293
294
295
296
        s.close()
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
297
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
298
299
    
    return (0)
300
301
302
### END send_mail()


303
304
305
306
307
308
309
310
311
312
313
##############################################################################
#
# batch_send_mail - send a mail to several users (if recipient list empty, mail will be sent to all active users) with 10s delay, can be aborted with ctrl+c
#
##############################################################################
def batch_send_mail(subject="[FlockLab]", message="", recipients=[], attachments=[]):
    if not message:
        return
    if not recipients:
        # no email provided -> extract all addresses from the database
        try:
Reto Da Forno's avatar
Reto Da Forno committed
314
315
316
          config = flocklab.get_config()
          logger = flocklab.get_logger()
          (cn, cur) = flocklab.connect_to_db()
317
318
319
          cur.execute("""SELECT email FROM `tbl_serv_users` WHERE is_active=1;""")
          ret = cur.fetchall()
          if not ret:
Reto Da Forno's avatar
Reto Da Forno committed
320
              logger.error("failed to get user emails from database")
321
322
              cur.close()
              cn.close()
Reto Da Forno's avatar
Reto Da Forno committed
323
              return FAILED
324
325
326
327
328
329
          recipients = []
          for elem in ret:
              recipients.append(elem[0])
          cur.close()
          cn.close()
        except Exception as e:
Reto Da Forno's avatar
Reto Da Forno committed
330
331
332
            logger.error("could not connect to database: " + sys.exc_info()[1][0])
            return FAILED
    # interactive, user can abort this process at any time
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
    print("mail content:\n" + message)
    sys.stdout.write("sending mail with subject '" + subject + "' to " + str(len(recipients)) + " recipient(s) in  ")
    sys.stdout.flush()
    try:
        for x in range(9, 0, -1):
            sys.stdout.write('\b' + str(x))
            sys.stdout.flush()
            time.sleep(1)
        print(" ")
        for usermail in r:
            send_mail(subject=s, message=msg, recipients=usermail)
            print("email sent to " + usermail)
    except KeyboardInterrupt:
        print("\naborted")
### END batch_send_mail()

349
350
351
352
353
354
355

##############################################################################
#
# check_test_id - Check if a test id is present in the flocklab database.
#
##############################################################################
def check_test_id(cursor=None, testid=0):
Reto Da Forno's avatar
Reto Da Forno committed
356
357
358
359
360
361
362
363
364
365
366
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            testid: test ID which should be checked
       Return value:
            0 if test ID exists in database
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request
            3 if test ID does not exist in the database
       """
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(testid) != int) or (testid <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
367
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
368
369
370
371
372
373
374
375

    # Check if the test ID is in the database:            
    try:
        # Check if the test ID exists in tbl_serv_tests.serv_tests_key
        cursor.execute("SELECT COUNT(serv_tests_key) FROM `tbl_serv_tests` WHERE serv_tests_key = %d" %testid)
        rs = cursor.fetchone()[0]
        
        if (rs == 0):
Reto Da Forno's avatar
Reto Da Forno committed
376
            return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
377
378
379
380
381
382
        else: 
            return(0)
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
383
        return FAILED
384
385
386
387
388
389
390
391
392
### END check_test_id()


##############################################################################
#
# get_test_obs - Get all observer IDs, keys and node IDs which are used in a test.
#
##############################################################################
def get_test_obs(cursor=None, testid=0):
Reto Da Forno's avatar
Reto Da Forno committed
393
394
395
396
397
398
399
400
401
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            testid: test ID
       Return value:
            Dictionary with observer IDs, keys and node IDs
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request
    """
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(testid) != int) or (testid <= 0) or (check_test_id(cursor, testid) != 0)):
Reto Da Forno's avatar
Reto Da Forno committed
402
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
403
404
405

    try:
        cursor.execute("SELECT `a`.serv_observer_key, `a`.observer_id, `b`.node_id \
Reto Da Forno's avatar
Reto Da Forno committed
406
407
408
409
410
                        FROM tbl_serv_observer AS `a` \
                        LEFT JOIN tbl_serv_map_test_observer_targetimages AS `b` \
                        ON `a`.serv_observer_key = `b`.observer_fk \
                        WHERE `b`.test_fk = %d \
                        ORDER BY `a`.observer_id" % testid)
Reto Da Forno's avatar
Reto Da Forno committed
411
412
413
414
415
416
417
418
419
420
        rs = cursor.fetchall()
        obsdict_bykey = {}
        obsdict_byid = {}
        for row in rs:
            obsdict_bykey[row[0]] = (row[1], row[2])
            obsdict_byid[row[1]] = (row[0], row[2])
        return (obsdict_bykey, obsdict_byid)
            
    except:
        logger = get_logger()
Reto Da Forno's avatar
Reto Da Forno committed
421
        logger.error("%s: %s" % (str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
422
        return FAILED
423
424
425
426
427
428
429
430
431
### END get_test_obs()


##############################################################################
#
# get_fetcher_pid - Returns the process ID of the oldest running fetcher.
#
##############################################################################
def get_fetcher_pid(testid):
Reto Da Forno's avatar
Reto Da Forno committed
432
    try:
Reto Da Forno's avatar
Reto Da Forno committed
433
        searchterm = "flocklab_fetcher.py (.)*-(-)?t(estid=)?%d" % (testid)
Reto Da Forno's avatar
Reto Da Forno committed
434
435
436
437
438
439
        cmd = ['pgrep', '-o', '-f', searchterm]
        p = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True)
        out, err = p.communicate()
        if (p.returncode == 0):
            return int(out)
        else:
Reto Da Forno's avatar
Reto Da Forno committed
440
            return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
441
442
443
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
444
        return FAILED
445
### END get_fetcher_pid()
446
447
448
449
450
451
452
453


##############################################################################
#
# get_test_owner - Get information about the owner of a test
#
##############################################################################
def get_test_owner(cursor=None, testid=0):
Reto Da Forno's avatar
Reto Da Forno committed
454
455
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(testid) != int) or (testid <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
456
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
457
    try:
458
459
460
461
462
        sql = "SELECT `a`.serv_users_key, `a`.lastname, `a`.firstname, `a`.username, `a`.email, `a`.disable_infomails \
               FROM tbl_serv_users AS `a` \
               LEFT JOIN tbl_serv_tests AS `b` \
               ON `a`.serv_users_key = `b`.owner_fk WHERE `b`.serv_tests_key=%d;"
        cursor.execute(sql % testid)
Reto Da Forno's avatar
Reto Da Forno committed
463
        rs = cursor.fetchone()
464
        return (rs[0], rs[1], rs[2], rs[3], rs[4], rs[5])
Reto Da Forno's avatar
Reto Da Forno committed
465
466
467
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
468
        return FAILED
469
470
471
472
473
474
475
476
477
### END get_test_owner()


##############################################################################
#
# get_pinmappings - Get all pin mappings from the database
#
##############################################################################
def get_pinmappings(cursor=None):
Reto Da Forno's avatar
Reto Da Forno committed
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
       Return value:
            Dictionary with pin number, pin_name
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request

       """
    if ((type(cursor) != MySQLdb.cursors.Cursor)):
        return 1
            
    try:
        cursor.execute("SELECT `a`.`pin_number`, `a`.`pin_name` , `b`.`service` \
                        FROM `tbl_serv_pinmappings` AS `a` \
                            LEFT JOIN `tbl_serv_services` AS `b` \
                            ON `a`.`services_fk` = `b`.`serv_services_key` \
                        ")
        rs = cursor.fetchall()
        
        pindict = {}
        for row in rs:
            pindict[row[0]] = (row[1], row[2])
        if len(pindict) == 0:
            raise
        return pindict
            
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return 2
508
509
510
511
512
513
514
515
516
### END get_pinmappings()


##############################################################################
#
# get_servicemappings - Get all service mappings from the database
#
##############################################################################
def get_servicemappings(cursor=None):
Reto Da Forno's avatar
Reto Da Forno committed
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
       Return value:
            Dictionary with mappings
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request

       """
    if ((type(cursor) != MySQLdb.cursors.Cursor)):
        return 1
        
    try:
        cursor.execute("SELECT `serv_services_key`, `service`, `abbreviation` FROM `tbl_serv_services`")
        rs = cursor.fetchall()
        
        servicedict = {}
        for row in rs:
            servicedict[row[0]] = (row[1], row[2])
        return servicedict
            
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return 2
541
542
543
544
545
546
547
548
549
### END get_servicemappings()


##############################################################################
#
# get_slot - Get slot for specific observer and platform from the database
#
##############################################################################
def get_slot(cursor=None, obs_fk=None, platname=None):
Reto Da Forno's avatar
Reto Da Forno committed
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            obs_fk: key of the observer which has to be queried
            platname: name of the platform which the slot has to host  
       Return value:
            slot number on success
            0 if no suitable slot was found
            -1 if there is an error in the arguments passed to the function
            -2 if there was an error in processing the request

       """
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(obs_fk) != int) or (type(platname) != str)):
        return -1
        
    try:
        # First, get a list of all possible adapt_list keys:
        sql =    """    SELECT `l`.`serv_tg_adapt_list_key` FROM `tbl_serv_tg_adapt_types` AS `t` 
                    LEFT JOIN `tbl_serv_platforms` AS `p` 
                        ON `t`.`platforms_fk` = `p`.`serv_platforms_key` 
                    LEFT JOIN `tbl_serv_tg_adapt_list` AS `l` 
                        ON `l`.`tg_adapt_types_fk` = `t`.`serv_tg_adapt_types_key` 
                    WHERE LOWER(p.name) = '%s' 
                """ 
        cursor.execute(sql%(platname))
        ret = cursor.fetchall()
        al_keys = []
        for r in ret:
            al_keys.append(r[0])
        # Now get all adapt_list FK's used on the particular observer and see if there is a match:
        sql =    """    SELECT `slot_1_tg_adapt_list_fk`, `slot_2_tg_adapt_list_fk`, `slot_3_tg_adapt_list_fk`, `slot_4_tg_adapt_list_fk` 
                    FROM `tbl_serv_observer`
                    WHERE `serv_observer_key` = %d
                """ 
        cursor.execute(sql%(obs_fk))
        slotlist = cursor.fetchone()
        slot = None
        if (slotlist[0] in al_keys):
            slot = 1
        elif (slotlist[1] in al_keys):
            slot = 2
        elif (slotlist[2] in al_keys):
            slot = 3
        elif (slotlist[3] in al_keys):
            slot = 4
        if not slot:
            slot = 0
        return slot
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return -2
601
602
603
604
605
606
607
608
609
### END get_slot()


##############################################################################
#
# get_obs_from_id - Get information about an observer from its ID
#
##############################################################################
def get_obs_from_id(cursor=None, obsid=0):
Reto Da Forno's avatar
Reto Da Forno committed
610
611
612
613
614
615
616
617
618
619
620
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            testid: observer ID
       Return value:
            On success, tuple with information
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request
       """

    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(obsid) != int) or (obsid <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
621
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
622
623
624
625
626
627
628
629
630
631
632
633
634
    
    try:
        sql = "    SELECT `ethernet_address`, `status` \
                FROM `tbl_serv_observer` \
                WHERE `observer_id`=%d;"
        cursor.execute(sql %obsid)
        rs = cursor.fetchone()
        
        return (rs[0], rs[1])
    except:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return (2)
635
636
637
### END get_obs_from_id()


638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
##############################################################################
#
# get_obsids - Get a list of currently available observer IDs of a certain platform.
#
##############################################################################
def get_obsids(cursor=None, platform=None, status=None):
    if not cursor or not platform or not status:
        return None
    cursor.execute("""
                   SELECT obs.observer_id AS obsid FROM flocklab.tbl_serv_observer AS obs
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_list AS a ON obs.slot_1_tg_adapt_list_fk = a.serv_tg_adapt_list_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_types AS slot1 ON a.tg_adapt_types_fk = slot1.serv_tg_adapt_types_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_list AS b ON obs.slot_2_tg_adapt_list_fk = b.serv_tg_adapt_list_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_types AS slot2 ON b.tg_adapt_types_fk = slot2.serv_tg_adapt_types_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_list AS c ON obs.slot_3_tg_adapt_list_fk = c.serv_tg_adapt_list_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_types AS slot3 ON c.tg_adapt_types_fk = slot3.serv_tg_adapt_types_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_list AS d ON obs.slot_4_tg_adapt_list_fk = d.serv_tg_adapt_list_key
                   LEFT JOIN flocklab.tbl_serv_tg_adapt_types AS slot4 ON d.tg_adapt_types_fk = slot4.serv_tg_adapt_types_key
                   WHERE obs.status IN (%s) AND '%s' IN (slot1.name, slot2.name, slot3.name, slot4.name)
                   ORDER BY obs.observer_id;
                   """ % (status, platform))
    obslist = []
    for rs in cursor.fetchall():
        obslist.append(rs[0])
    return obslist
### END get_obsids()

665
666
667
668

##############################################################################
#
# check_observer_id - Check if an observer id is present in the flocklab 
Reto Da Forno's avatar
Reto Da Forno committed
669
#     database and return its key if present.
670
671
672
#
##############################################################################
def check_observer_id(cursor=None, obsid=0):
Reto Da Forno's avatar
Reto Da Forno committed
673
674
675
676
677
678
679
680
681
682
683
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            obsid:  observer ID which should be checked
       Return value:
            key if observer ID exists in database
            -1 if there is an error in the arguments passed to the function
            -2 if there was an error in processing the request
            -3 if observer ID does not exist in the database
       """
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(obsid) != int) or (obsid <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
684
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700

    # Check if the test ID is in the database:            
    try:
        # Check if the test ID exists in tbl_serv_tests.serv_tests_key
        cursor.execute("SELECT serv_observer_key FROM `tbl_serv_observer` WHERE observer_id = %d" %obsid)
        rs = cursor.fetchone()
        
        if (rs == None):
            return(-3)
        else: 
            return(rs[0])
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return(-2)
701
702
703
704
705
706
707
708
709
### END check_observer_id()


##############################################################################
#
# set_test_status - Set the status of a test in the flocklab database.
#
##############################################################################
def set_test_status(cursor=None, conn=None, testid=0, status=None):
Reto Da Forno's avatar
Reto Da Forno committed
710
711
712
713
714
715
716
717
718
719
720
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            conn:   database connection
            testid: test ID for which the status is to be set
       Return value:
            0 on success
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request
       """
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(conn) != MySQLdb.connections.Connection) or (type(testid) != int) or (testid <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
721
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
722
723
724
725
726
    # Get all possible test stati and check the status argument:
    try:
        cursor.execute("SHOW COLUMNS FROM `tbl_serv_tests` WHERE Field = 'test_status'")
        possible_stati = cursor.fetchone()[1][5:-1].split(",")
        if ("'%s'"%status not in possible_stati):
Reto Da Forno's avatar
Reto Da Forno committed
727
            return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
728
729
730
731
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
732
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
733
734
735
736
737
738
739
740
741

    # Set the status in the database            
    try:
        cursor.execute("UPDATE `tbl_serv_tests` SET `test_status` = '%s', `dispatched` = 0 WHERE `serv_tests_key` = %d;" %(status, testid))
        conn.commit()
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
742
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
743
    return(0)
744
745
746
747
748
749
750
751
752
### END set_test_status()


##############################################################################
#
# get_test_status - Get the status of a test in the flocklab database.
#
##############################################################################
def get_test_status(cursor=None, conn=None, testid=0):
Reto Da Forno's avatar
Reto Da Forno committed
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(conn) != MySQLdb.connections.Connection) or (type(testid) != int) or (testid <= 0)):
        return -1

    # Get the status in the database
    try:
        # To read changed values directly, one needs to change the isolation level to "READ UNCOMMITTED"
        cursor.execute("SELECT @@session.tx_isolation")
        isolation_old = cursor.fetchone()[0]
        if isolation_old != 'READ-UNCOMMITTED':
            cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
            conn.commit()
        # Now get the value:
        cursor.execute("SELECT `test_status` FROM `tbl_serv_tests` WHERE `serv_tests_key` = %d;" %testid)
        status = cursor.fetchone()[0]
        # Reset the isolation level:
        if isolation_old != 'READ-UNCOMMITTED':
            cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s"%(str.replace(isolation_old, '-', ' ')))
            conn.commit()
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        return -2
    return status
778
779
### END get_test_status()

780

781
782
783
784
785
786
##############################################################################
#
# set_test_dispatched - Set the dispatched flag of a test in the flocklab database.
#
##############################################################################
def set_test_dispatched(cursor=None, conn=None, testid=0):
Reto Da Forno's avatar
Reto Da Forno committed
787
788
789
790
791
792
793
794
795
796
797
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            conn:   database connection
            testid: test ID for which the status is to be set
       Return value:
            0 on success
            1 if there is an error in the arguments passed to the function
            2 if there was an error in processing the request
       """
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(conn) != MySQLdb.connections.Connection) or (type(testid) != int) or (testid <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
798
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
799
800
801
802
803
804
805
806
807

    # Set the flag in the database            
    try:
        cursor.execute("UPDATE `tbl_serv_tests` SET `dispatched` = 1 WHERE `serv_tests_key` = %d;" %(testid))
        conn.commit()
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
808
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
809
    return(0)
810
811
### END set_test_dispatched()

812

813
814
815
816
817
818
819
##############################################################################
#
# acquire_db_lock - try to get db lock on the specified key
# this is a blocking operation.
#
##############################################################################
def acquire_db_lock(cursor, conn, key, expiry_time=10):
Reto Da Forno's avatar
Reto Da Forno committed
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            conn:   database connection
            key: key to lock
       """
    try:
        spin = True
        while spin:
            spin = False
            try:
                cursor.execute("DELETE FROM `tbl_serv_locks` WHERE (`name`='%s' AND `expiry_time` < now());" %(key))
                conn.commit() # this is needed to release a potential shared lock on the table
                cursor.execute("INSERT INTO `tbl_serv_locks` (`name`, `expiry_time`) values ('%s', now() + %d);" %(key, expiry_time))
                conn.commit()
            except MySQLdb.IntegrityError:
                time.sleep(1)
                spin = True
            except MySQLdb.OperationalError as e: # retry if deadlock
838
                if e.args[0] == MySQLdb.constants.ER.LOCK_DEADLOCK:
Reto Da Forno's avatar
Reto Da Forno committed
839
840
841
842
843
844
845
846
847
848
                    time.sleep(1)
                    spin = True
                else:
                    raise
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
        raise
    return(0)
849
850
### END acquire_db_lock()

851

852
853
854
855
856
857
##############################################################################
#
# release_db_lock - release db lock on the specified key
#
##############################################################################
def release_db_lock(cursor, conn, key, expiry_time=10):
Reto Da Forno's avatar
Reto Da Forno committed
858
859
860
861
862
863
864
865
866
867
868
869
870
    """Arguments: 
            cursor: cursor of the database connection to be used for the query
            conn:   database connection
            key: key to lock
       """
    spin = True
    try:
        while spin:
            spin = False
            try:
                cursor.execute("DELETE FROM `tbl_serv_locks` WHERE (`name`='%s');" %(key))
                conn.commit()
            except MySQLdb.OperationalError as e: # retry if deadlock
871
                if e.args[0] == MySQLdb.constants.ER.LOCK_DEADLOCK:
Reto Da Forno's avatar
Reto Da Forno committed
872
873
874
875
876
877
878
879
                    time.sleep(1)
                    spin = True
                else:
                    raise
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("%s: %s" %(str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
880
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
881
    return(0)
882
883
### END release_db_lock()

884

885
886
887
888
889
890
##############################################################################
#
# write_errorlog - Writes a message to the errorlog table tbl_serv_errorlog.
#
##############################################################################
def write_errorlog(cursor=None, conn=None, testid=0, obsid=0, message="", timestamp=0.0):
Reto Da Forno's avatar
Reto Da Forno committed
891
892
    # Check the arguments:
    if ((type(cursor) != MySQLdb.cursors.Cursor) or (type(conn) != MySQLdb.connections.Connection) or (type(testid) != int) or (type(obsid) != int) or (type(message) != str) or (len(message) <= 0) or (type(timestamp) != float) or (timestamp < 0.0)):
Reto Da Forno's avatar
Reto Da Forno committed
893
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
894
    if ((testid != 0) and (check_test_id(cursor, testid) != 0)):
Reto Da Forno's avatar
Reto Da Forno committed
895
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
896
    if ((obsid != 0) and (check_observer_id(cursor, obsid) <= 0)):
Reto Da Forno's avatar
Reto Da Forno committed
897
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
    else: 
        obskey = check_observer_id(cursor, obsid)
    
    # Prepare timestamp:
    if (timestamp <= 0.0):
        timestamp = time.time()

    # Set the status in the database
    sql = "INSERT INTO `tbl_serv_errorlog` (`errormessage`, `timestamp`, `test_fk`, `observer_fk`) VALUES ('%s', %f" %(re.escape(message), timestamp)
    if testid != 0:
        sql += ", %d"%testid
    else:
        sql += ", NULL"
    if obsid != 0:
        sql += ", %d"%obskey
    else:
        sql += ", NULL"
    sql += ");"
    try:
        cursor.execute(sql)
        conn.commit()
    except:
        # There was an error in the database connection:
        logger = get_logger()
        logger.error("Error when executing %s: %s: %s" %(sql, str(sys.exc_info()[0]), str(sys.exc_info()[1])))
Reto Da Forno's avatar
Reto Da Forno committed
923
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
924
    return(0)
925
926
927
928
929
930
931
932
### END write_errorlog()


##############################################################################
#
# error_logandexit - Logs an error (to log and email to admins) and exits the script
#
##############################################################################
Reto Da Forno's avatar
Reto Da Forno committed
933
934
def error_logandexit(message=None, exitcode=FAILED):
    global logger, config
Reto Da Forno's avatar
Reto Da Forno committed
935
    # Check the arguments:
Reto Da Forno's avatar
Reto Da Forno committed
936
937
    if (type(message) != str) or (message == "") or (type(exitcode) != int):
        return FAILED
Reto Da Forno's avatar
Reto Da Forno committed
938
    # Log error - if available, use logger, otherwise get it first:
939
940
941
942
    if logger:
        logger.error(message)
    else:
        log_fallback(message)
Reto Da Forno's avatar
Reto Da Forno committed
943
944
    # Send email to admin:
    try:
Reto Da Forno's avatar
Reto Da Forno committed
945
946
        admin_emails = get_admin_emails()
        if admin_emails == FAILED:
Reto Da Forno's avatar
Reto Da Forno committed
947
948
949
950
951
            msg = "Error when getting admin emails from database"
            if logger:
                logger.error(msg)
            else:
                logger.error(msg)
Reto Da Forno's avatar
Reto Da Forno committed
952
953
            raise Exception
        send_mail(subject="[FlockLab %s]" % (scriptname.replace('.', '_').split('_')[1].capitalize()), message=message, recipients=admin_emails)
Reto Da Forno's avatar
Reto Da Forno committed
954
    except:
955
956
957
958
        if logger:
            logger.error("error_logandexit(): Failed to send email to admin.")
        else:
            log_fallback("error_logandexit(): Failed to send email to admin.")
Reto Da Forno's avatar
Reto Da Forno committed
959
    # Exit program
960
961
    if logger:
        logger.debug("Exiting with error code %u." % exitcode)
Reto Da Forno's avatar
Reto Da Forno committed
962
    sys.exit(exitcode)
963
964
965
966
967
968
969
970
971
### END error_logandexit()


##############################################################################
#
# count_running_instances - Check how many instances of a script are running  
#
##############################################################################
def count_running_instances(scriptname=None):
Reto Da Forno's avatar
Reto Da Forno committed
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
    # Check the arguments:
    if ((type(scriptname) != str) or (len(scriptname) <= 0)):
        return(-1)

    cmd = ['pgrep', '-l', '-f', scriptname]
    p = subprocess.Popen(cmd, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True)
    out, err = p.communicate()
    if (p.returncode == 0):
        # If a script is called from a cronjob, this will add an additional line in pgrep which needs to be filtered.
        count = 0
        for line in out.split('\n'):
            if ((len(line) > 0) and (line.find('python') != -1)):
                count += 1
        # Return the total instance count (including the instance which called this function):
        return count
    else:
        return(-2)
989
990
991
992
993
### END count_running_instances()


##############################################################################
#
994
# get_admin_emails - Get the email addresses of all admins from the FlockLab database or the config file if admin_email is present.
995
996
#
##############################################################################
Reto Da Forno's avatar
Reto Da Forno committed
997
def get_admin_emails(cursor=None):
Reto Da Forno's avatar
Reto Da Forno committed
998
    email_list = []
Reto Da Forno's avatar
Reto Da Forno committed
999
    if cursor and type(cursor) == MySQLdb.cursors.Cursor:
1000
        # Get the addresses from the database:
For faster browsing, not all history is shown. View entire blame