database.py 3.68 KB
Newer Older
Julien David's avatar
Julien David committed
1
2
3
4
import mysql.connector
from get_docker_secret import get_docker_secret
import os

Ismail Moumni's avatar
editing    
Ismail Moumni committed
5

Julien David's avatar
Julien David committed
6
7
8
9
10
11
# Exception thrown when no password has been defined to connect to the database
class NoPasswordDefineError(Exception):
    """No secret containing a password has been found"""
    pass


Ismail Moumni's avatar
edit    
Ismail Moumni committed
12
__rdos_password__ = "test"
Ismail Moumni's avatar
editing    
Ismail Moumni committed
13

Julien David's avatar
Julien David committed
14
15
16
17

# Procedures that returns an externally defined password to conect to the database
# First tries to check the docker secrets, then an environment variable.
# Throws NoPasswordDefineError if no password is found
Ismail Moumni's avatar
editing    
Ismail Moumni committed
18
# Returns a password
Julien David's avatar
Julien David committed
19
def get_password():
Ismail Moumni's avatar
editing    
Ismail Moumni committed
20
    if __rdos_password__ is None:
Julien David's avatar
Julien David committed
21
        password = get_docker_secret('rdos_secret')
Ismail Moumni's avatar
editing    
Ismail Moumni committed
22
        if password is None:
Julien David's avatar
Julien David committed
23
24
25
26
27
28
29
30
            if 'rdos_secret' in os.environ:
                password = os.environ['rdos_secret']
                print("Password stored in a non secured way, please make your code safer")
            else:
                raise NoPasswordDefineError
        return password
    return __rdos_password__

Ismail Moumni's avatar
editing    
Ismail Moumni committed
31

Julien David's avatar
Julien David committed
32
33
34
35
36
# Connect to the local MYSQL database on lipn-rdos
def database_connection():
    mydb = mysql.connector.connect(
        host="192.168.90.101",
        user="rdos",
Ismail Moumni's avatar
editing    
Ismail Moumni committed
37
        password=get_password(),
Julien David's avatar
Julien David committed
38
39
40
41
42
        database="rdos"
    )
    return mydb


Ismail Moumni's avatar
change    
Ismail Moumni committed
43
def update_database(u: str, job_id: str, before: str, after: str):
Julien David's avatar
Julien David committed
44
45
46
47
48
49
    database = database_connection()
    mycursor = database.cursor(buffered=False)

    with open('../database/templates/update_success.txt', 'r') as file:
        update = file.read()
    update = update.replace('{{before}}', before).replace('{{after}}', after)
Ismail Moumni's avatar
change    
Ismail Moumni committed
50
    update = update.replace('{{u}}', u)
Julien David's avatar
Julien David committed
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
    update = update.replace('{{job_id}}', job_id)
    print(update)

    mycursor.execute(update)
    database.commit()
    print(mycursor.rowcount, "record(s) affected")
    return mycursor.rowcount


# Get a row in the Job table that hasn't been computed yet.
def get_job(database):
    sql_get_new_job = "SELECT jobs.id,parametersJSON,directory,email,tool,jobs.idGenerator,outputFormat,command from jobs inner join generators on jobs.idGenerator=generators.id where status='10'"
    mycursor = database.cursor(buffered=True)
    mycursor.execute(sql_get_new_job)
    myresult = mycursor.fetchone()
    mycursor.close()
    return myresult


# Tells the database that the previously selected job will be taken care of by the deamon
# Returns 1 if no other deamon selected the job first, 0 otherwwise.
def apply_for_job(database, job_id: str):
    mycursor = database.cursor(buffered=False)
Ismail Moumni's avatar
editing    
Ismail Moumni committed
74
    update = "UPDATE jobs set status='100' where jobs.id='"+job_id+"' and status='10'"
Julien David's avatar
Julien David committed
75
76
77
78
79
    mycursor.execute(update)
    database.commit()
    return mycursor.rowcount


Ismail Moumni's avatar
editing    
Ismail Moumni committed
80
# Returns a dictionnary containing each generator's parameters as a key. The value associated to each key is "is this parameter's value written in the command line or in a separated file
Julien David's avatar
Julien David committed
81
82
83
84
85
86
87
88
89
90
# in which case the value is the filename.
def getParameters(generators: dict):
    database = database_connection()
    parameters_basefile = {}
    parameters_default = {}
    for generator in generators:
        parameters_basefile[generator] = {}
        parameters_default[generator] = {}
        sql_get_new_job = "SELECT name,basefile,defaultValue from parameters inner join generators on parameters.idGenerator=generators.id where generators.tool = '"+generator+"'"
        mycursor = database.cursor(buffered=False)
Ismail Moumni's avatar
editing    
Ismail Moumni committed
91
        mycursor.execute(sql_get_new_job)
Julien David's avatar
Julien David committed
92
93
94
95
96
97
98
        myresult = mycursor.fetchall()
        mycursor.close()
        for result in myresult:
            parameters_basefile[generator][result[0]] = result[1]
            parameters_default[generator][result[0]] = result[1]
    database.disconnect()
    return (parameters_basefile, parameters_default)
Ismail Moumni's avatar
editing    
Ismail Moumni committed
99
100
101
102


if __name__ == "__main__":
    db = getParameters("Regal")