database.py

import mysql.connector
from get_docker_secret import get_docker_secret
import os


# 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__ = "test"


# 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__ is None:
        password = get_docker_secret('rdos_secret')
        if password is 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__


# 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(u: 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('{{u}}', u)
    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)
    update = "UPDATE jobs set status='100' where jobs.id='"+job_id+"' and status='10'"
    mycursor.execute(update)
    database.commit()
    return mycursor.rowcount


# 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
# 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)


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