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