def delete_file_from_db(self, file_id, admin=False):
"""remove a file for the database
Parameters
----------
file_id : int
the file id to remove
"""
database = Database(self.app, self.session)
if admin and self.session['user']['admin']:
query_params = (file_id, )
where_query = ""
else:
query_params = (file_id, self.session['user']['id'])
where_query = "AND user_id=?"
query = '''
DELETE FROM files
WHERE id=?
{}
'''.format(where_query)
database.execute_sql_query(query, query_params)
def get_all_files_infos(self):
if not self.session['user']['admin']:
return []
database = Database(self.app, self.session)
query = '''
SELECT files.id, files.name, files.type, files.size, files.date, files.status, users.username
FROM files
INNER JOIN users ON files.user_id=users.user_id
'''
rows = database.execute_sql_query(query, ())
files = []
for row in rows:
file = {
'id': row[0],
'name': row[1],
'type': row[2],
'size': row[3],
'date': row[4],
'status': row[5],
'user': row[6]
}
files.append(file)
return files
def is_username_in_db(self, username):
"""
Check if the username is present in the database
Parameters
----------
username : str
Username
Returns
-------
bool
True if the user exist
"""
database = Database(self.app, self.session)
query = '''
SELECT username, password FROM users
WHERE username=?
'''
rows = database.execute_sql_query(query, (username, ))
if len(rows) <= 0:
return False
return True
def save_in_db(self):
"""Save the dataset into the database"""
database = Database(self.app, self.session)
query = '''
INSERT INTO datasets VALUES(
NULL,
?,
?,
?,
?,
NULL,
?,
"queued",
strftime('%s', 'now'),
NULL,
?,
NULL,
NULL,
NULL
)
'''
self.id = database.execute_sql_query(query, (
self.session["user"]["id"],
self.celery_id,
self.file_id,
self.name,
self.public,
0
), get_id=True)
def create_reset_token(self, login):
"""Insert a token into the db
Parameters
----------
login : str
username or email
Returns
-------
str
The reset token
"""
token = "{}:{}".format(int(time.time()), Utils.get_random_string(20))
database_field = 'username'
if validate_email(login):
database_field = 'email'
database = Database(self.app, self.session)
query = """
UPDATE users
SET reset_token=?
WHERE {}=?
""".format(database_field)
database.execute_sql_query(query, (token, login))
return token
def toggle_public(self, new_status):
"""Change public status of a dataset (triplestore and db)
Parameters
----------
new_status : bool
True if public
"""
# Update in TS
query = SparqlQuery(self.app, self.session)
tse = TriplestoreExplorer(self.app, self.session)
string_status = "true" if new_status else "false"
query.toggle_public(self.graph_name, string_status)
# Update in DB
database = Database(self.app, self.session)
query = '''
UPDATE datasets SET
public=?
WHERE user_id = ? AND id = ?
'''
database.execute_sql_query(query, (new_status, self.session["user"]["id"], self.id))
# Uncache abstraction
tse.uncache_abstraction()
def get_size_occupied_by_user(self):
"""Get disk size occuped by file user (uploaded files and results)
Returns
-------
int
size un bytes
"""
database = Database(self.app, self.session)
query = '''
SELECT SUM(size)
FROM (
SELECT size
FROM results
WHERE user_id = ?
UNION ALL
SELECT size
FROM files
WHERE user_id = ?
)
'''
result = database.execute_sql_query(
query, (self.session["user"]["id"], self.session["user"]["id"]))
return 0 if result[0][0] is None else result[0][0]
def update_status_in_db(self, status):
"""Update the status of a datasets in the database
Parameters
----------
status : string
The new status (started, success or deleting)
Returns
-------
list
Remaining datasets
"""
database = Database(self.app, self.session)
where_str = '(' + ' OR '.join(['id = ?'] * len(self.datasets)) + ')'
datasets_id = [dataset.id for dataset in self.datasets]
query = '''
UPDATE datasets SET
status=?
WHERE user_id=?
AND {}
'''.format(where_str)
database.execute_sql_query(
query, (status, self.session['user']['id']) + tuple(datasets_id))
return self.get_datasets()
def update_apikey(self, user):
"""Create a new api key and store in the database
Parameters
----------
user : dict
The current user
Returns
-------
dict
error, error message and updated user
"""
error = False
error_message = ''
database = Database(self.app, self.session)
# get a new api key
new_apikey = Utils.get_random_string(20)
query = '''
UPDATE users SET
apikey=?
WHERE username=?
'''
database.execute_sql_query(query, (new_apikey, user['username']))
user['apikey'] = new_apikey
return {'error': error, 'error_message': error_message, 'user': user}
def update_last_action(self, username):
"""Update last login time into user database
Parameters
----------
username : str
Username
Returns
-------
int
timestamp
"""
database = Database(self.app, self.session)
now = int(time.time())
query = '''
UPDATE users SET
last_action=?
WHERE username=?
'''
database.execute_sql_query(query, (now, username))
return now
def delete_user_database(self, username, delete_user=True):
"""Delete a user in database
Parameters
----------
username : string
Username to delete
"""
user = self.get_user(username)
database = Database(self.app, self.session)
queries = [
"DELETE FROM datasets WHERE user_id = ?",
"DELETE FROM files WHERE user_id = ?",
"DELETE FROM galaxy_accounts WHERE user_id = ?",
"DELETE FROM results WHERE user_id = ?"
]
if delete_user:
queries.append("DELETE FROM users WHERE user_id = ?")
queries.append("DELETE FROM abstraction WHERE user_id = ?")
queries.append("DELETE FROM galaxy_accounts WHERE user_id = ?")
for query in queries:
database.execute_sql_query(query, (user["id"], ))
def is_email_in_db(self, email):
"""
Check if the email is present in the database
Parameters
----------
email : str
Email
Returns
-------
bool
True if the email exist
"""
database = Database(self.app, self.session)
query = '''
SELECT email FROM users
WHERE email=?
'''
rows = database.execute_sql_query(query, (email, ))
if len(rows) <= 0:
return False
return True
def update_celery(self, celery_id, admin=False):
"""Update celery id of dataset in database
Parameters
----------
celery_id : string
DescriThe celery idption
"""
if admin and self.session['user']['admin']:
query_params = (celery_id, self.id)
where_query = ""
else:
query_params = (celery_id, self.id, self.session['user']['id'])
where_query = "AND user_id = ? "
database = Database(self.app, self.session)
query = '''
UPDATE datasets SET
celery_id=?
WHERE id = ?
{}
'''.format(where_query)
database.execute_sql_query(query, query_params)
def toggle_public(self, new_status, admin=False):
"""Change public status of a dataset (triplestore and db)
Parameters
----------
new_status : bool
True if public
"""
# Update in TS
query = SparqlQuery(self.app, self.session)
tse = TriplestoreExplorer(self.app, self.session)
string_status = "true" if new_status else "false"
query.toggle_public(self.graph_name, string_status)
if admin and self.session['user']['admin']:
query_params = (new_status, self.id)
where_query = ""
else:
query_params = (new_status, self.id, self.session["user"]["id"])
where_query = "AND user_id = ?"
# Update in DB
database = Database(self.app, self.session)
query = '''
UPDATE datasets SET
public=?
WHERE id = ?
{}
'''.format(where_query)
database.execute_sql_query(query, query_params)
# Uncache abstraction
tse.uncache_abstraction()
def set_info_from_db_with_id(self):
"""Set result info from the db"""
database = Database(self.app, self.session)
if "user" in self.session:
query = '''
SELECT celery_id, path, graph_state, start, end, nrows, sparql_query, graphs_and_endpoints
FROM results
WHERE (user_id = ? OR public = ?) AND id = ?
'''
rows = database.execute_sql_query(query, (self.session["user"]["id"], True, self.id))
else:
query = '''
SELECT celery_id, path, graph_state, start, end, nrows, sparql_query, graphs_and_endpoints
FROM results
WHERE public = ? AND id = ?
'''
rows = database.execute_sql_query(query, (True, self.id))
self.celery_id = rows[0][0] if rows[0][0] else ''
self.file_path = rows[0][1] if rows[0][1] else ''
self.file_name = os.path.basename(self.file_path)
self.graph_state = json.loads(rows[0][2])
self.start = rows[0][3]
self.end = rows[0][4]
self.nrows = rows[0][5]
self.sparql_query = rows[0][6]
gne = json.loads(rows[0][7]) if rows[0][7] else {"graphs": [], "endpoints": []}
self.graphs = gne["graphs"]
self.endpoints = gne["endpoints"]
def publish_query(self, public, admin=False):
"""Set public to True or False, and template to True if public is True"""
database = Database(self.app, self.session)
# If query is set to public, template or form (if available) have to be True
sql_substr = ''
if admin and self.session['user']['admin']:
sql_var = (public, self.id)
where_query = ""
# Should not happen
else:
sql_var = (public, self.id, self.session["user"]["id"])
where_query = "AND user_id=?"
if public:
if self.has_form_attr and not self.template:
sql_substr = 'form=?,'
else:
sql_substr = 'template=?,'
sql_var = (public, ) + sql_var
query = '''
UPDATE results SET
{}
public=?
WHERE id=?
{}
'''.format(sql_substr, where_query)
database.execute_sql_query(query, sql_var)
def form_query(self, form):
"""Set form to True or False, Set Template to False if True, public to False if template and form are False"""
database = Database(self.app, self.session)
if not self.has_form_attr:
raise Exception(
"This query does not has any form template attribute")
sql_substr = ''
sql_var = (form, self.session["user"]["id"], self.id)
if form and self.template:
sql_substr = 'template=?,'
sql_var = (False, form, self.session["user"]["id"], self.id)
if not (form or self.template):
sql_substr = 'public=?,'
sql_var = (form, form, self.session["user"]["id"], self.id)
query = '''
UPDATE results SET
{}
form=?
WHERE user_id=? AND id=?
'''.format(sql_substr)
database.execute_sql_query(query, sql_var)
def update_db_status(self,
status,
size=None,
update_celery=False,
update_date=False,
error=False,
error_message=None,
traceback=None):
"""Update status of results in db
Parameters
----------
error : bool, optional
True if error during integration
error_message : bool, optional
Error string if error is True
"""
message = error_message if error else ""
update_celery_substr = ""
if update_celery:
update_celery_substr = "celery_id=?,"
update_date_substr = "start=strftime('%s', 'now')," if update_date else ""
size_string = ""
if size:
size_string = "size=?,"
self.end = int(time.time())
database = Database(self.app, self.session)
query = '''
UPDATE results SET
{celery}
{size}
{date}
status=?,
end=?,
path=?,
nrows=?,
error=?,
traceback=?
WHERE user_id=? AND id=?
'''.format(celery=update_celery_substr,
size=size_string,
date=update_date_substr)
variables = [
status, self.end, self.file_path, self.nrows, message, traceback,
self.session["user"]["id"], self.id
]
if size:
variables.insert(0, size)
if update_celery:
variables.insert(0, self.celery_id)
database.execute_sql_query(query, tuple(variables))
def get_public_queries(self):
"""Get id and description of published queries
Returns
-------
List
List of published queries (id and description)
"""
database = Database(self.app, self.session)
where_substring = ""
sql_var = (True, )
if "user" in self.session:
where_substring = " or (template = ? and user_id = ?)"
sql_var = (True, True, self.session["user"]["id"])
query = '''
SELECT id, description, public
FROM results
WHERE public = ?{}
'''.format(where_substring)
rows = database.execute_sql_query(query, sql_var)
queries = []
for row in rows:
queries.append({
"id": row[0],
"description": row[1],
"public": row[2]
})
return queries
def get_cached_asbtraction(self):
"""Get cached abstraction from database
Returns
-------
(bool, dict):
bool: True if no row exist, else False if row exist
dict: {} if no abstraction, else, return abstraction
"""
if "user" not in self.session:
return True, {}
database = Database(self.app, self.session)
query = """
SELECT abstraction
FROM abstraction
WHERE user_id=?
"""
results = database.execute_sql_query(query, (self.session["user"]["id"], ))
if results:
if results[0][0]:
return False, json.loads(results[0][0])
else:
return False, {}
return True, {}
def set_info_from_db(self, admin=False):
"""Set the info in from the database"""
database = Database(self.app, self.session)
if admin and self.session['user']['admin']:
query_params = (self.id, )
where_query = ""
else:
query_params = (self.id, self.session['user']['id'])
where_query = "AND user_id = ?"
query = '''
SELECT celery_id, file_id, name, graph_name, public, start, end
FROM datasets
WHERE id = ?
{}
'''.format(where_query)
rows = database.execute_sql_query(query, query_params)
self.celery_id = rows[0][0]
self.file_id = rows[0][1]
self.name = rows[0][2]
self.graph_name = rows[0][3]
self.public = rows[0][4]
self.start = rows[0][5]
self.end = rows[0][6]
def update_profile(self, inputs, user):
"""Update the profile of a user
Parameters
----------
inputs : dict
fields to update
user : dict
The current user
Returns
-------
dict
error, error message and updated user
"""
error = False
error_message = ''
database = Database(self.app, self.session)
update = []
values = []
new_fname = user['fname']
new_lname = user['lname']
new_email = user['email']
# update only if one value are provided
if not list(inputs.values()) == ['', '', '']:
if inputs['newFname']:
update.append('fname=?')
values.append(inputs['newFname'])
new_fname = inputs['newFname']
if inputs['newLname']:
update.append('lname=?')
values.append(inputs['newLname'])
new_lname = inputs['newLname']
if inputs['newEmail']:
update.append('email=?')
values.append(inputs['newEmail'])
new_email = inputs['newEmail']
update_str = ', '.join(update)
query = '''
UPDATE users SET
{}
WHERE username=?
'''.format(update_str)
database.execute_sql_query(query,
tuple(values) + (user['username'], ))
user['fname'] = new_fname
user['lname'] = new_lname
user['email'] = new_email
return {'error': error, 'error_message': error_message, 'user': user}
def delete_db_entry(self):
"""Delete results from db"""
database = Database(self.app, self.session)
query = '''
DELETE FROM results
WHERE id = ? AND user_id = ?
'''
database.execute_sql_query(query, (self.id, self.session["user"]["id"]))
def get_files_infos(self, files_id=None, return_path=False):
"""Get files info
Parameters
----------
files_id : None, optional
list of files id
return_path : bool, optional
return the path if True
Returns
-------
list
list of files info
"""
database = Database(self.app, self.session)
if files_id:
subquery_str = '(' + ' OR '.join(['id = ?'] * len(files_id)) + ')'
query = '''
SELECT id, name, type, size, path, date
FROM files
WHERE user_id = ?
AND {}
'''.format(subquery_str)
rows = database.execute_sql_query(
query, (self.session['user']['id'], ) + tuple(files_id))
else:
query = '''
SELECT id, name, type, size, path, date
FROM files
WHERE user_id = ?
'''
rows = database.execute_sql_query(query,
(self.session['user']['id'], ))
files = []
for row in rows:
file = {
'id': row[0],
'name': row[1],
'type': row[2],
'size': row[3],
'date': row[5]
}
if return_path:
file['path'] = row[4]
files.append(file)
return files
def update_in_db(self,
status,
update_celery=False,
update_date=False,
update_graph=False,
error=False,
error_message=None,
ntriples=0,
traceback=None):
"""Update the dataset when integration is done
Parameters
----------
error : bool, optional
True if error during integration
error_message : None, optional
Error string if error is True
ntriples : int, optional
Number of triples integrated
"""
message = error_message if error else ""
update_celery_id_substr = "celery_id=?," if update_celery else ""
update_date_substr = "start=strftime('%s', 'now')," if update_date else ""
update_graph_substr = "graph_name=?," if update_graph else ""
database = Database(self.app, self.session)
query = '''
UPDATE datasets SET
{}
{}
{}
status=?,
end=strftime('%s', 'now'),
ntriples=?,
error_message=?,
traceback=?
WHERE user_id = ? AND id=?
'''.format(update_celery_id_substr, update_date_substr,
update_graph_substr)
variables = [
status, ntriples, message, traceback, self.session['user']['id'],
self.id
]
if update_graph:
variables.insert(0, self.graph_name)
if update_celery:
variables.insert(0, self.celery_id)
database.execute_sql_query(query, tuple(variables))
def delete_from_db(self):
"""Delete a dataset from the database"""
database = Database(self.app, self.session)
query = '''
DELETE FROM datasets
WHERE user_id = ?
AND id = ?
'''
database.execute_sql_query(query, (self.session['user']['id'], self.id))
def update_description(self, description):
"""Change the result description"""
database = Database(self.app, self.session)
query = '''
UPDATE results SET
description=?
WHERE user_id=? AND id=?
'''
database.execute_sql_query(
query, (description, self.session["user"]["id"], self.id))
def update_graph(self, newGraph):
"""Change the result description"""
database = Database(self.app, self.session)
query = '''
UPDATE results SET
graph_state=?
WHERE user_id=? AND id=?
'''
database.execute_sql_query(
query, (json.dumps(newGraph), self.session["user"]["id"], self.id))
def get_user_from_db(self, login):
"""Get a user from database
Parameters
----------
login : str
email or username
Returns
-------
dict
User
"""
database = Database(self.app, self.session)
query = '''
SELECT u.user_id, u.ldap, u.fname, u.lname, u.username, u.email, u.password, u.salt, u.apikey, u.admin, u.blocked, u.quota, u.last_action, g.url, g.apikey
FROM users u
LEFT JOIN galaxy_accounts g ON u.user_id=g.user_id
WHERE {} = ?
GROUP BY u.user_id
'''.format(self.get_login_type(login))
rows = database.execute_sql_query(query, (login, ))
user = {}
if len(rows) > 0:
user = {
'id': rows[0][0],
'ldap': rows[0][1],
'fname': rows[0][2],
'lname': rows[0][3],
'username': rows[0][4],
'email': rows[0][5],
'password': rows[0][6],
'salt': rows[0][7],
'apikey': rows[0][8],
'admin': rows[0][9],
'blocked': rows[0][10],
'quota': rows[0][11],
'last_action': rows[0][12],
'galaxy': None
}
if rows[0][13] is not None and rows[0][14] is not None:
user['galaxy'] = {
'url': rows[0][13],
'apikey': rows[0][14]
}
return user
def get_all_users(self):
"""Get all user info
Returns
-------
list
All user info
"""
database = Database(self.app, self.session)
ldap_auth = LdapAuth(self.app, self.session)
query = '''
SELECT u.user_id, u.ldap, u.fname, u.lname, u.username, u.email, u.admin, u.blocked, u.quota, u.last_action, g.url, g.apikey
FROM users u
LEFT JOIN galaxy_accounts g ON u.user_id=g.user_id
GROUP BY u.user_id
'''
rows = database.execute_sql_query(query)
users = []
if rows:
for row in rows:
user = {}
user['ldap'] = row[1]
user['fname'] = row[2]
user['lname'] = row[3]
user['username'] = row[4]
user['email'] = row[5]
user['admin'] = row[6]
user['blocked'] = row[7]
user['quota'] = row[8]
user['last_action'] = row[9]
user['galaxy'] = None
if row[10] is not None and row[11] is not None:
user['galaxy'] = {
'url': row[10],
'apikey': row[11]
}
if user["ldap"] == 1:
ldap_user = ldap_auth.get_user(user["username"])
user["fname"] = ldap_user["fname"]
user["lname"] = ldap_user["lname"]
user["email"] = ldap_user["email"]
users.append(user)
return users
