def store_package_rows(package_name, user, filepath, tag):
"""
The complete storing of an entire package
"""
# this is used to detect if the package already existed before this
# attempt. aids in cleanup
curr_package_id = lookup_package_id(package_name=package_name)
package_id = curr_package_id
if None == package_id:
package_id = store_package_name(package_name=package_name, user=user)
curr_filestore_id = lookup_filestore_id_by_path(filepath=filepath)
filestore_id = curr_filestore_id
if None == filestore_id:
try:
filestore_id = store_filestore(filepath=filepath)
except Exception as err:
current_app.logger.error("Exception in store_filestore")
cleanup_add(package_id=package_id if
(curr_package_id == None) else None,
filestore_id=None)
raise UnhandledError()
tag_id = lookup_tag_id(package_id=package_id, tag=tag)
if None != tag_id:
# this means the tag already exists for adding this package
cleanup_add(package_id=package_id if
(curr_package_id == None) else None,
filestore_id=filestore_id if
(curr_filestore_id == None) else None)
raise IntegrityError(message='tag is already in use for package')
# store the tag in the database
try:
tag_id = store_tag(package_id=package_id,
filestore_id=filestore_id,
tag=tag)
except IntegrityError as err:
cleanup_add(package_id=package_id if
(curr_package_id == None) else None,
filestore_id=filestore_id if
(curr_filestore_id == None) else None)
raise err
except Exception as err:
current_app.logger.error("Exception in store_tag")
cleanup_add(package_id=package_id if
(curr_package_id == None) else None,
filestore_id=filestore_id if
(curr_filestore_id == None) else None)
raise UnhandledError()
return {'package_id': package_id, 'tag_id': tag_id}
def store_filestore(filepath):
"""
stores a new filestore in the database, should only be used after checking lookup_filestore_id_by_path
"""
try:
add_filestore_id = insert_db(table='filestore',
fields=['package_filepath'],
values=[filepath])
return add_filestore_id
except sqlite3.IntegrityError as err:
current_app.logger.error(
"Package location is not unique : {f}".format(f=filepath))
raise UnhandledError()
except Exception as err:
current_app.logger.error(
"Unhandled Error in store_filestore filepath={f} : {e}".format(
f=filepath, e=err))
raise UnhandledError()
def lookup_filepath_from_id(filestore_id):
"""
returns the filepath for a specified filestore_id
"""
try:
search_query = "SELECT package_filepath FROM filestore WHERE id = ?"
filepath = query_db(query=search_query, args=[filestore_id], one=True)
return filepath['package_filepath'] if filepath is not None else None
except Exception as err:
current_app.logger.error(
"Unhandled Error in lookup_filepath: filestore_id {fi} : {e}".
format(fi=filestore_id, e=err))
raise UnhandledError()
def lookup_tag_id(package_id, tag):
"""
looks up the tag_id of a package and file location
"""
try:
search_query = "SELECT id FROM tags WHERE package_id = ? AND tag = ?"
tag_id = query_db(query=search_query, args=[package_id, tag], one=True)
return tag_id['id'] if tag_id is not None else None
except Exception as err:
current_app.logger.error(
"Unhandled Error lookup_tag_id package_id={pi} tag={t} : {e} {et}".
format(pi=package_id, t=tag, e=err, et=type(err)))
raise UnhandledError()
def delete_filestore(filestore_id):
"""
deletes a filestore from the database, used for cleanup from errors
"""
try:
delete_query = "DELETE FROM filestore WHERE id = ?"
query_db(query=delete_query, args=[filestore_id])
return True
except Exception as err:
current_app.logger.error(
"could not delete filestore id {fi} from filestore : {e}".format(
fi=filestore_id, e=err))
raise UnhandledError()
def lookup_filestore_id_by_path(filepath):
"""
looks up a current filestore_id from a package location
"""
try:
search_query = "SELECT id FROM filestore WHERE package_filepath = ?"
filestore_id = query_db(query=search_query, args=[filepath], one=True)
return filestore_id['id'] if filestore_id is not None else None
except Exception as err:
current_app.logger.error(
"Unhandled Error in lookup_filestore_id_by_path filestore={f} : {e}"
.format(f=filepath, e=err))
raise UnhandledError()
def delete_package(package_id):
"""
removes a package from the database package table, used for cleanup from errors
"""
try:
delete_query = "DELETE FROM packages WHERE id = ?"
query_db(query=delete_query, args=[package_id])
return True
except Exception as err:
current_app.logger.error(
"could not delete package {pd} from table : {e}".format(
pd=package_id, e=err))
raise UnhandledError()
def lookup_user_id(username):
"""
looks up a user id by their username
"""
try:
search_query = "SELECT id FROM users WHERE username = ?"
user_id = query_db(query=search_query, args=[username], one=True)
return user_id['id'] if user_id != None else None
except Exception as err:
current_app.logger.error(
"Unable to lookup_user_id username={u} : {e}".format(u=username,
e=err))
raise UnhandledError()
def search_all_packages():
"""
returns a list of all packages currently being stored
"""
try:
all_packages_query = "SELECT title, id FROM packages"
packages = query_db(query=all_packages_query)
if None == packages:
packages = []
return packages
except Exception as err:
current_app.logger.error(
"Unhandled Error in search_all_packages : {e}".format(e=err))
raise UnhandledError()
def add_user(username, password, key):
"""
adds a user to the database
"""
try:
add_query = insert_db(table='users',
fields=['username', 'password', 'apikey'],
values=[username, password, key])
except sqlite3.IntegrityError as err:
raise IntegrityError(message='username is already in use')
except Exception as err:
current_app.logger.error(
"Unable to add user {u}, password {p}, apikey {k} because {e}({et})"
.format(u=username, p=password, k=key, e=err, et=type(err)))
raise UnhandledError()
def search_all_tags_by_id(package_id):
"""
returns a list of all tags for a specific package
"""
try:
all_tags_query = "SELECT tag, id FROM tags WHERE package_id = ?"
tags = query_db(query=all_tags_query, args=[package_id])
if None == tags:
tags = []
return tags
except Exception as err:
current_app.logger.error(
"Unhandled Error in search_all_tags_by_id: package_id {pi} : {e}".
format(pi=package_id, e=err))
raise UnhandledError()
def lookup_filestore_id_from_tag(package_id, tag):
"""
makes the database search for the filestore_id of a file referenced by package_id and tag
"""
try:
search_query = "SELECT filestore_id FROM tags WHERE package_id = ? AND tag = ?"
filestore_id = query_db(query=search_query,
args=[package_id, tag],
one=True)
return filestore_id[
'filestore_id'] if filestore_id is not None else None
except Exception as err:
current_app.logger.error(
"Unhandled Error in lookup_filestore_id_from_tag: package_id {pi}, tag {t} : {e}"
.format(pi=package_id, t=tag, e=err))
raise UnhandledError()
def search_tags_by_id_and_term(package_id, tag_search_term):
"""
Allows users to search for specific text within a packages' tag
"""
try:
search_query = "SELECT tag, id FROM tags WHERE package_id = ? AND tag LIKE ?"
search_term = "%{tst}%".format(tst=tag_search_term)
tags = query_db(query=search_query, args=[package_id, search_term])
if None == tags:
tags = []
return tags
except Exception as err:
current_app.logger.error(
"Unhandled Error in search_tags_by_id_and_term: package_id {pi}, tag_search_term {tst} : {e}"
.format(pi=package_id, tst=tag_search_term, e=err))
raise UnhandledError()
def search_packages(search_term):
"""
Allows users to search for specific text within a package name
"""
try:
search_query = "SELECT title, id FROM packages WHERE title LIKE ?"
search_term = "%{st}%".format(st=search_term)
packages = query_db(query=search_query, args=[search_term])
if None == packages:
packages = []
return packages
except Exception as err:
current_app.logger.error(
"Unhandled Error in search_packages {st} : {e}".format(
st=search_term, e=err))
raise UnhandledError()
def store_package_name(package_name, user):
"""
stores a new package in the database, should only be used after checking lookup_package_id
"""
try:
add_package_id = insert_db(table='packages',
fields=['title', 'user_id'],
values=[package_name, user])
return add_package_id
except sqlite3.IntegrityError as err:
raise IntegrityError(message='package name already in use')
except Exception as err:
current_app.logger.error(
"Unhandled Error in store_package_name package_name={pn} user={u} : {e}"
.format(pn=package_name, u=user, e=err))
raise UnhandledError()
def lookup_package_id(package_name):
"""
looks up the package id for the given package name, if exists
"""
try:
search_query = "SELECT id FROM packages WHERE title = ?"
package_id = query_db(query=search_query,
args=[package_name],
one=True)
return package_id['id'] if package_id is not None else None
except Exception as err:
current_app.logger.error(
"Unhandled Error in lookup_package_id {pn} : {e}".format(
pn=package_name, e=err))
raise UnhandledError()
def lookup_password(user):
"""
Lookup a users password. This is not a good way of doing this. The
database is not salted or hashed. But this is a quick way of doing this.
"""
try:
password_query = "SELECT password FROM users WHERE username = ?"
password = query_db(query=password_query, args=[user])
if None == password:
raise NotFoundError(message='username not found')
return password[0]['password']
except NotFoundError as err:
raise err
except Exception as err:
current_app.logger.error(
"Unhandled Error lookup up user {u} : {e}".format(u=user, e=err))
raise UnhandledError()
def store_tag(package_id, filestore_id, tag):
"""
This is used to store a new tag in the tag table
"""
try:
add_tag_id = insert_db(table='tags',
fields=['tag', 'package_id', 'filestore_id'],
values=[tag, package_id, filestore_id])
return add_tag_id
except sqlite3.IntegrityError as err:
current_app.logger.error(
"Tag and package_id are not unique. package_id={pi} filestore_id={fi} tag={t}: {e}"
.format(pi=package_id, fi=filestore_id, t=tag, e=err))
raise IntegrityError(message='Tag is not unique to package')
except Exception as err:
current_app.logger.error(
"Unhandled Error in store_tag_id package_id={pi} filestore_id={fi} tag={t} : {e}"
.format(pi=package_id, fi=filestore_id, t=tag, e=err))
raise UnhandledError()