def clean_db(days=2):
"""cleanup API for cleaning up all the ingestion jobs.
"""
message = None
tables = []
try:
conn = pg_conn()
with conn.cursor() as cursor:
sql = """SELECT * FROM {}.{}
WHERE entrydate < DATE(NOW() - INTERVAL '{} DAY')
AND table_exist = TRUE
"""
cursor.execute(sql.format(DB_SCHEMA, AUDIT_TABLE, days))
rows = cursor.fetchall()
if len(rows) > 0:
for row in rows:
tables.append(row['tablename'])
sql_delete_table = """DROP TABLE {}.{}"""
cursor.execute(
sql_delete_table.format(DB_SCHEMA, row['tablename']))
sql_update_audit = """UPDATE {}.{} SET table_exist=FALSE WHERE tablename='{}'"""
sql_update_audit = sql_update_audit.format(
DB_SCHEMA, AUDIT_TABLE, row['tablename'])
cursor.execute(sql_update_audit)
conn.commit()
conn.close()
message = "Tables that got deleted: " + ", ".join(tables)
except Exception as err:
logger.error(err)
message = str(err)
return jsonify(message=message), 200
def get_ops_details(team_name):
"""get the ops details for the given team on chub.
:param team_name: name of the team for which ops details are requested
:type team_name: string
:return: list of dict objects with the ops info
:rtype: list
"""
try:
rows = ()
conn = pg_conn()
with conn.cursor() as cursor:
sql = '''
select
o.name as ops_name, o.wiki, og.name as grp_name, oo.name as owner_name, oo.email, t.name as team
from ops o, ops_group og, ops_owner oo, teams t
where o.ownerid = oo.id
AND o.opsgroupid = og.id
AND og.teamid = t.id
AND t.name = %s
'''
cursor.execute(sql, [unquote(team_name)])
rows = cursor.fetchall()
conn.close()
return jsonify(rows)
except Exception as e:
logger.error(e)
return jsonify("ERROR"), 400
def post_satellite_advertise():
"""
Satellites will advertise themselves by pinging this interface.
"""
try:
body = request.get_json()
# Should we accept a custom satellite host?
default_host = request.headers.get('x-forwarded-for',
request.remote_addr)
satellite_host = body.get('satellite_host', default_host)
satellite_port = body.get('satellite_port')
satellite_url = f'http://{satellite_host}:{satellite_port}'
data = body["data"]
name = body["name"]
ttl = data['ttl']
info = data['info']
with pg_conn() as conn:
with conn.cursor() as cursor:
cursor.execute(
'''
INSERT INTO public.satellite(satellite_url, name, expires, info) VALUES (%s, %s, %s, %s)
ON CONFLICT (satellite_url) DO UPDATE SET expires = EXCLUDED.expires, info = EXCLUDED.info
''', [
satellite_url, name,
datetime.datetime.utcnow() +
datetime.timedelta(seconds=ttl), info
])
return jsonify({"status": "ok"})
except Exception as e:
logger.error(str(e))
return jsonify({"status": "error", "error": str(e)}), 400
def machine_pooling():
"""
API function to get a list of all machines along with its
GPU information which belong to any Compiler-Graphics machine pool
:return: list of dict of machine and machine pool details
:rtype: list
"""
try:
conn = vrl_conn()
with conn.cursor() as cursor:
sql = """
SELECT m.name as machine_name, m.gpu as gpu,mp.name as pool_name
FROM machine m, machinePools mp, machinePools_machine mpm
WHERE m.id = mpm.machineId
AND mp.id = mpm.machinePoolId
AND mp.name like 'Compiler-Graphics%'
ORDER BY 3"""
cursor.execute(sql)
rows = cursor.fetchall()
conn.close()
return jsonify(rows)
except Exception as err:
print(err)
logger.error(err)
return jsonify("ERROR", str(err)), 400
def clean_files(filename):
"""removes a given file.
The file has to be present in the UPLOAD_FOLDER
"""
try:
file_path = os.path.join(UPLOAD_FOLDER, filename)
os.remove(file_path)
return True
except Exception as err:
logger.error(err)
return False
def copy_csv_to_staging_table(conn, csv_file):
"""
Importer function to ingest the entire csv.
:param csv_file: csv file the user is trying upload
:return: status: true of false
"""
status = False
try:
tail = os.path.split(csv_file)[1]
csv_name = tail.split('.')[0]
csv_name = DB_SCHEMA + "." + csv_name
data = open(csv_file, 'r')
column_headers = data.readline().rstrip()
column_headers = column_headers.split(',')
sql_create = """CREATE TABLE {} ({})"""
sql_delete = """DROP TABLE IF EXISTS {}"""
sql_audit = """INSERT INTO {}.{}(tablename, entrydate, table_exist) VALUES('{}', '{}', TRUE)"""
column_headers = [
header + " VARCHAR(255)" for header in column_headers
]
column_headers = ", ".join(column_headers)
# open db connection
logger.info("Opening connection to the Database")
with conn.cursor() as cursor:
# check if the staging table exists and drop it if it exists
logger.info("Check whether the old staging table exists")
cursor.execute(sql_delete.format(csv_name))
# create a new staging table
logger.info("Creating the staging table")
cursor.execute(sql_create.format(csv_name, column_headers))
# get the file ready to load it in the db
logger.info("Copying the csv data to the Database")
cursor.copy_from(data, csv_name, sep=',')
# commit the changes
logger.info("Committing the changes")
update_audit_table(cursor, csv_name.split('.')[1])
conn.commit()
status = True
data.close()
message = "Success"
except Exception as e:
logger.error(e)
message = str(e)
logger.info(
"#################### END OF TRANSACTIONS #######################")
return status, message
def get_machine_details_from_postgres(days=7, filter_by="totaltime"):
"""
api funtion used by the UI to get the machine details.
Currently the limit is set for the last 10 days.
In futute this 10 days limit will be controlled via the UI.
:return: list of dict of machine details
:rtype: list
"""
filters = ['totaltime', 'jobcount']
try:
days = days + 2
if filter_by not in filters:
filter_by = filters[0]
sql = """
SELECT *
FROM machine_monitoring
WHERE jobdate >DATE(NOW() - INTERVAL '%s DAY')
ORDER BY jobdate"""
result = {}
dates = []
conn = pg_conn()
with conn.cursor() as cursor:
cursor.execute(sql, [days])
rows = cursor.fetchall()
for row in rows:
job_date = str(row['jobdate'])
machine = row['name']
gpu = row['gpu']
total_time = int(row[filter_by])
if job_date not in dates:
dates.append(job_date)
if gpu in result:
result_gpu = result[gpu]
if machine in result_gpu:
result[gpu][machine].append(total_time)
else:
result[gpu][machine] = [total_time]
else:
temp = {}
temp[machine] = [total_time]
result[gpu] = temp
return jsonify(results=result, dates=dates, filter_by=filter_by)
except Exception as e:
print(e)
logger.error(e)
return jsonify("ERROR"), 400
def get_all_teams():
"""get all the teams name and id
:return: a list of dict with team name and team id
:rtype: list
"""
try:
conn = pg_conn()
with conn.cursor() as cursor:
sql = '''
SELECT id, name
FROM teams
'''
cursor.execute(sql)
rows = cursor.fetchall()
conn.close()
return jsonify(rows), 200
except Exception as e:
logger.error(e)
return jsonify("ERROR"), 400
def update_audit_table(cursor, table_name):
"""update audit tables
"""
try:
sql = """SELECT * from {}.{} WHERE tablename='{}'"""
cursor.execute(sql.format(DB_SCHEMA, AUDIT_TABLE, table_name))
rows = cursor.fetchall()
if len(rows) > 0:
sql_update = """UPDATE {}.{} SET entrydate='{}', table_exist=TRUE
WHERE tablename='{}'"""
cursor.execute(
sql_update.format(DB_SCHEMA, AUDIT_TABLE, get_date(),
table_name))
else:
sql_audit = """INSERT INTO {}.{}(tablename, entrydate, table_exist) VALUES('{}', '{}', TRUE)"""
cursor.execute(
sql_audit.format(DB_SCHEMA, AUDIT_TABLE, table_name,
get_date()))
except Exception as err:
logger.error(err)
raise Exception(str(err))
def post_satellite_unadvertise():
"""
satellites will advertise themselves by pinging this interface.
"""
try:
body = request.get_json()
# Should we accept a custom satellite host?
default_host = request.headers.get('x-forwarded-for',
request.remote_addr)
satellite_host = body.get('satellite_host', default_host)
satellite_port = body.get('satellite_port')
satellite_url = f'http://{satellite_host}:{satellite_port}'
with pg_conn() as conn:
with conn.cursor() as cursor:
cursor.execute(
'''
DELETE FROM public.satellite WHERE satellite_url = %s
''', [satellite_url])
return jsonify({"status": "ok"})
except Exception as e:
logger.error(str(e))
return jsonify({"status": "error", "error": str(e)}), 400
def get_all_running_jobs_on_vrl():
"""get all the running job on VRL for user DVS
:return: a list of rows
:rtype: list
"""
try:
rows = ()
conn = vrl_conn()
with conn.cursor() as cursor:
sql = '''
SELECT vrl.job.gpu, vrl.job.status, vrl.job.submitted, vrl.job.jobstarted, vrl.job.notes, vrl.machine.name as MachineName, vrl.tests.testname as TestName FROM vrl.job
JOIN vrl.tests on vrl.job.testId = vrl.tests.id
JOIN vrl.machine on vrl.job.machineId = vrl.machine.id
WHERE username='******' AND status='RUNNING' LiMIT 1000
'''
cursor.execute(sql)
rows = cursor.fetchall()
conn.close()
json_res = jsonify(rows)
return json_res, 200
except Exception as e:
logger.error(e)
return jsonify("ERROR"), 400
def get_all_ops():
"""get the ops information for all the teams on chub
:return: list of dict containing ops information for all the teams
:rtype: list
"""
try:
rows = ()
conn = pg_conn()
with conn.cursor() as cursor:
sql = '''
select
o.name as ops_name, o.wiki, og.name as grp_name, oo.name as owner_name, oo.email, t.name as team
from ops o, ops_group og, ops_owner oo, teams t
where o.ownerid = oo.id
AND o.opsgroupid = og.id
AND og.teamid = t.id
'''
cursor.execute(sql)
rows = cursor.fetchall()
conn.close()
return jsonify(rows)
except Exception as e:
logger.error(e)
return jsonify("ERROR"), 400