database.py 6.21 KB
Newer Older
Julien David's avatar
Julien David committed
1
2
3
import mysql.connector
from get_docker_secret import get_docker_secret
import os
4
import random
Julien David's avatar
Julien David committed
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 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


__rdos_password__ = None

# 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
# Returns a password 
def get_password():
    if __rdos_password__ == None:
        password = get_docker_secret('rdos_secret')
        if password == None:
            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__

30

Julien David's avatar
Julien David committed
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# Connect to the local MYSQL database on lipn-rdos
def database_connection():
    mydb = mysql.connector.connect(
        host="192.168.90.101",
        user="rdos",
        password = get_password(),
        database="rdos"
    )
    return mydb


def update_database(url:str, job_id: str, before:str, after:str):
    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)
    update = update.replace('{{url}}', url)
    update = update.replace('{{job_id}}', job_id)
    print(update)

Ismail Moumni's avatar
Ismail Moumni committed
53
54
    mycursor.execute(update)
    database.commit()
Julien David's avatar
Julien David committed
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
    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)
    update = "UPDATE jobs set status='100' where jobs.id='"+job_id+"' and status='10'" 
    mycursor.execute(update)
    database.commit()
    return mycursor.rowcount


81
# Returns a dictionary containing each generator's parameters as a key. The value associated to each key is "is this parameter's value written in the comand line or in a separated file
Julien David's avatar
Julien David committed
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
# 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)
        mycursor.execute(sql_get_new_job)    
        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
Ismail Moumni committed
102
103


104
# Returns a dictionary containing all database generators existing in database
105
# The result returned contains every generators in database with parameters and default values for each field
Ismail Moumni's avatar
Ismail Moumni committed
106
107
108
109
110
def get_generators():
    database = database_connection()
    generators = {}
    sql_get_new_job = "SELECT generators.tool, name, basefile, defaultValue from parameters inner join generators on parameters.idGenerator=generators.id"
    mycursor = database.cursor(buffered=False)
Ismail Moumni's avatar
Ismail Moumni committed
111
    mycursor.execute(sql_get_new_job)
Ismail Moumni's avatar
Ismail Moumni committed
112
113
114
    myresult = mycursor.fetchall()
    mycursor.close()
    for result in myresult:
Ismail Moumni's avatar
Ismail Moumni committed
115
        generators[result[0]] = [result]
116
117


118
119
120
121
122
123
124
125
def list_generator():
    database = database_connection()
    generators = {}
    sql_get_generators = "SELECT id, tool from generators"
    mycur = database.cursor(buffered=False)
    mycur.execute(sql_get_generators)
    res = mycur.fetchall()
    mycur.close()
126
    for a, b in res:
127
128
129
130
        generators[b] = a
    return generators


131
# Insert in database query send from client and returns insert status 1 if inserted
132
133
134
def db_insert(query: dict):
    if(query is not None):
        database = database_connection()
135
136
        if(db_job_check(query) != 0):
            query['id'] = str(random.getrandbits(64))
137
138
        try:
            values = query.values()
139
            print("values received :", values)
140
141
            cols = query.keys()
            sql_new_job_row = "INSERT INTO jobs (%s) VALUES ('%s');" % (", ".join(cols), "','".join(values))
142
            mycursor = database.cursor(buffered=True)
143
            mycursor.execute(sql_new_job_row)
Ismail Moumni's avatar
Ismail Moumni committed
144
            print("Record inserteds in DB :", query.values)
145
146
            database.commit()
            mycursor.close()
Ismail Moumni's avatar
Ismail Moumni committed
147
            database.close()
148
149
150
            res = db_job_check(query)
            print("res:", res)
            return res
151
152
153
154
        except mysql.connector.Error as err:
            print("MYSQL Error :{}".format(err))


Ismail Moumni's avatar
Ismail Moumni committed
155
# Checks existance of query ID in database and returns the status
156
# db_job_check returns 1 if query exist in database else returns 0
157
158
159
160
161
162
163
164
165
166
167
168
def db_job_check(query: dict):
    if (query is not None):
        db = database_connection()
        try:
            ret = 0
            val = query['id']
            sql_new_job_row = "SELECT * FROM jobs WHERE ID=('%s') LIMIT 1;" % (val)
            mycursor = db.cursor(buffered=True)
            mycursor.execute(sql_new_job_row)
            db.commit()
            ret = mycursor.rowcount
            mycursor.close()
Ismail Moumni's avatar
Ismail Moumni committed
169
            db.close()
170
            return ret
171
172
        except mysql.connector.Error as err:
            print("MYSQL Error :{}".format(err))