def get_many_one(self, id: int, parent: str, child: str):
"""Retrieve the many to many relationship data of a parent and child.
Args:
id (int): Given ID of type parent
parent (str): Type of parent
child (str): Type of child
"""
join_table = JuncHolder.lookup_table(parent, child)
# This should not be reachable
# if join_table is None:
# return {'error': f"relationship '{child}' of '{parent}' not found."}
try:
session = Session()
parent_col_str = f"{parent}_id"
child_col_str = f"{child}_id"
cols = {parent_col_str: id}
query = session.query(join_table).filter_by(**cols).all()
children = []
for row in query:
# example - {'programs_id': 2, 'credentials_id': 3}
row_dict = row._asdict()
children.append(row_dict[child_col_str])
except Exception:
raise InternalServerError()
finally:
session.close()
return {f"{child}": children}, 200
def get_stored_descriptors(self) -> list:
"""Gets stored json models from database.
Returns:
list: List of JSON dictionaries
"""
session = Session()
descriptor_list = [] # list of json dict
try:
query = session.query(Checksum)
for _row in query.all():
try:
if not _row.descriptor_json:
continue
except Exception:
logger.exception(
"Checksum table or row does not have a value for the descriptor_json column."
)
descriptor_list.append(_row.descriptor_json)
except Exception:
logger.info("Error retrieving stored models")
finally:
session.close()
return descriptor_list
def patch_many_one(self, id: int, parent: str, child: str, values):
"""put data for a many to many relationship of a parent and child.
Args:
id (int): Given ID of type parent
parent (str): Type of parent
child (str): Type of child
values (list or int): list of values to patch
"""
try:
session = Session()
many_query = []
junc_table = JuncHolder.lookup_table(parent, child)
if junc_table is not None:
if not isinstance(values, list):
values = [values]
many_query.append([child, values, junc_table])
for field, values, table in many_query:
# TODO this should only insert when it doesnt already exist
self.process_many_query(session, table, id, field, parent,
values)
except Exception:
raise InternalServerError()
finally:
session.close()
return self.get_many_one(id, parent, child)
def delete_many_one(self, id: int, parent: str, child: str, values):
try:
session = Session()
junc_table = JuncHolder.lookup_table(parent, child)
if not isinstance(values, list):
values = [values]
for value in values:
parent_col = getattr(junc_table.c, f"{parent}_id")
child_col = getattr(junc_table.c, f"{child}_id")
del_st = junc_table.delete().where(
and_(parent_col == id, child_col == value))
res = session.execute(del_st)
print(res)
session.commit()
except Exception:
session.rollback()
raise InternalServerError()
finally:
session.close()
return self.get_many_one(id, parent, child)
def get_migrations_from_db_and_save_locally(self) -> None:
logger.info("Restoring migration files from DB...")
session = Session()
try:
query = session.query(Migrations)
for _row in query.all():
self.save_migrations_to_local_file(_row.file_name, _row.file_blob)
except Exception:
logger.info("Failed to restore migration files from DB.")
finally:
session.close()
def query(
self,
data_model,
data_resource_name,
restricted_fields,
table_schema,
request_obj,
):
"""Query the data resource."""
try:
request_obj = request_obj.json
except Exception:
raise ApiError("No request body found.", 400)
errors = []
_ = Schema(table_schema)
accepted_fields = []
response = OrderedDict()
response["results"] = []
if validate(table_schema):
for field in table_schema["fields"]:
if field["name"] not in restricted_fields:
accepted_fields.append(field["name"])
for field in request_obj.keys():
if field not in accepted_fields:
errors.append(
"Unknown or restricted field '{}' found.".format(
field))
if len(errors) > 0:
raise ApiUnhandledError("Invalid request body.", 400, errors)
else:
try:
session = Session()
results = session.query(data_model).filter_by(
**request_obj)
for row in results:
response["results"].append(
self.build_json_from_object(
row, restricted_fields))
if len(response["results"]) == 0:
return {"message": "No matches found"}, 404
else:
return response, 200
except Exception:
raise ApiUnhandledError("Failed to create new resource.",
400)
finally:
session.close()
else:
raise SchemaValidationFailure()
return {"message": "querying data resource"}, 200
def get_stored_checksums(self) -> list:
session = Session()
checksums = [] # list of json dict
try:
query = session.query(Checksum)
for _row in query.all():
checksums.append((_row.data_resource, _row.model_checksum))
except Exception:
logger.exception("Error retrieving stored models")
finally:
session.close()
return checksums
def initalize_base_models(self):
self.logger.info("Initalizing base models...")
db_active = False
max_retries = 10
retries = 0
exponential_time = exponential_backoff(1, 1.5)
while not db_active and retries <= max_retries:
if retries != 0:
sleep_time = exponential_time()
self.logger.info(
f"Sleeping for {sleep_time} with exponential backoff...")
sleep(sleep_time)
retries += 1
self.logger.info("Checking database availability...")
try:
self.logger.info("Looking for checksum...")
session = Session()
_ = session.query(Checksum).all()
db_active = True
self.logger.info("Successfully found checksum.")
except Exception as e:
self.logger.info("Hit exception looking for checksum...")
# UndefinedTable
if e.code == "f405":
self.logger.info(
"Checksum table was not found; Running inital migration..."
)
# The migration file that describes checksums, logs, and migrations
# is present in the migrations folder.
self.db.upgrade()
db_active = True
self.logger.info("Successfully ran upgrade.")
elif e.code == "e3q8":
self.logger.info(
"Database is not available yet exception.")
self.logger.info(
"Waiting on database to become available.... {}/{}".
format(retries, max_retries))
else:
self.logger.exception(f"Error occured upgrading database.")
finally:
session.close()
self.logger.info("Base models initalized.")
def update_model_checksum(
self, table_name: str, model_checksum: str, descriptor_json: dict = {}
):
"""Updates a checksum for a data model.
Args:
table_name (str): Name of the table to add the checksum.
checksum (str): Checksum value.
Returns:
bool: True if checksum was updated. False otherwise.
"""
session = Session()
updated = False
try:
checksum = (
session.query(Checksum)
.filter(Checksum.data_resource == table_name)
.first()
)
checksum.model_checksum = model_checksum
checksum.descriptor_json = descriptor_json
session.commit()
updated = True
except Exception:
logger.exception("Error updating checksum")
finally:
session.close()
return updated
def wait_for_db(self):
db_active = False
max_retries = 10
retries = 0
exponential_time = exponential_backoff(1, 1.5)
while not db_active and retries <= max_retries:
if retries != 0:
sleep_time = exponential_time()
self.logger.info(
f"Sleeping for {sleep_time} with exponential backoff...")
sleep(sleep_time)
retries += 1
self.logger.info("Checking database availability...")
try:
self.logger.info("Looking for DB...")
session = Session()
_ = session.query(Checksum).all()
db_active = True
self.logger.info("Successfully connected to DB.")
except Exception as e:
self.logger.info("Hit exception looking for checksum...")
# UndefinedTable
if e.code == "f405":
# TODO this is a race condition with DMM?
db_active = True
self.logger.info("Successfully connected to DB.")
elif e.code == "e3q8":
self.logger.info(
"Database is not available yet exception.")
self.logger.info(
"Waiting on database to become available.... {}/{}".
format(retries, max_retries))
else:
self.logger.exception(f"Error occured upgrading database.")
finally:
session.close()
self.logger.info("Connected to DB.")
def check_for_checksum_column():
session = Session()
query = """
SELECT 1
FROM information_schema.columns
WHERE table_name='checksums' and column_name='descriptor_json'
"""
rs = session.execute(query)
session.close()
count = 0
for _row in rs:
count += 1
if count == 1:
print("Found the descriptor_json column -- skipping import")
return True
return False
def check_for_migrations_table():
session = Session()
query = """
SELECT *
FROM information_schema.tables
WHERE table_name='migrations';
"""
rs = session.execute(query)
session.close()
count = 0
for _row in rs:
count += 1
if count == 1:
print("Found the migrations table -- skipping import")
return True
return False
def upgrade_checksum():
session = Session()
query = """
ALTER TABLE checksums ADD COLUMN descriptor_json jsonb;
"""
try:
session.execute(query)
session.commit()
except BaseException:
logger.exception("Failed to upgrade checksum")
finally:
session.close()
def is_migrations_loaded():
session = Session()
result = session.query(Migrations).count()
logger.info(f"Found {result} rows in migrations table")
if result == 0:
session.close()
return False
session.close()
return True
def push_descriptors():
session = Session()
descriptors = DescriptorsLoader([SCHEMA_DIR], [])
for desc in descriptors.iter_descriptors():
try:
row = (session.query(Checksum).filter(
Checksum.data_resource == desc.table_name).first())
row.descriptor_json = desc.descriptor
session.add(row)
session.commit()
except Exception:
logger.exception("Error pushing descriptors")
continue
finally:
session.close()
def get_model_checksum(self, table_name: str):
"""Retrieves a checksum by table name.
Args:
table_name (str): Name of the table to add the checksum.
Returns:
object: The checksum object if it exists, None otherwise.
"""
session = Session()
checksum = None
try:
checksum = (
session.query(Checksum)
.filter(Checksum.data_resource == table_name)
.first()
)
except Exception:
logger.exception("Error retrieving checksum")
finally:
session.close()
return checksum
def create_migrations():
session = Session()
query = """
CREATE TABLE migrations (
file_name TEXT PRIMARY KEY,
file_blob BYTEA
);
"""
try:
session.execute(query)
session.commit()
except BaseException:
logger.exception("Failed to create table migrations")
finally:
session.close()
def emit(self, record):
session = Session()
trace = None
exc = record.__dict__["exc_info"]
if exc:
trace = traceback.format_exc()
log = Log(
logger=record.__dict__["name"],
level=record.__dict__["levelname"],
trace=trace,
msg=record.__dict__["msg"],
)
session.add(log)
session.commit()
session.close()
def put_many_one(self, id: int, parent: str, child: str, values):
"""put data for a many to many relationship of a parent and child.
Args:
id (int): Given ID of type parent
parent (str): Type of parent
child (str): Type of child
"""
try:
session = Session()
junc_table = JuncHolder.lookup_table(parent, child)
# delete all relations
parent_col = getattr(junc_table.c, f"{parent}_id")
del_st = junc_table.delete().where(parent_col == id)
_ = session.execute(del_st)
# put the items
many_query = []
if junc_table is not None:
if not isinstance(values, list):
values = [values]
many_query.append([child, values, junc_table])
for field, values, table in many_query:
self.process_many_query(session, table, id, field, parent,
values)
except Exception:
raise InternalServerError()
finally:
session.close()
return self.get_many_one(id, parent, child)
def get_one(self, id, data_model, data_resource_name, table_schema):
"""Retrieve a single object from the data model based on it's primary
key.
Args:
id (any): The primary key for the specific object.
data_model (object): SQLAlchemy ORM model.
data_resource_name (str): Name of the data resource.
table_schema (dict): The Table Schema object to use for validation.
Return:
dict, int: The response object and the HTTP status code.
"""
try:
primary_key = table_schema["primaryKey"]
session = Session()
result = (session.query(data_model).filter(
getattr(data_model, primary_key) == id).first())
response = self.build_json_from_object(result)
return response, 200
except Exception:
raise ApiUnhandledError(f"Resource with id '{id}' not found.", 404)
finally:
session.close()
def save_migration(file_name: str, file_blob) -> None:
"""This function is called by alembic as a post write hook.
It will take a migration file and save it to the database.
"""
logger.info("Trying to save migration files to DB...")
session = Session()
try:
new_migration = Migrations()
new_migration.file_name = file_name
new_migration.file_blob = file_blob
result = (
session.query(Migrations)
.filter(Migrations.file_name == file_name)
.count()
)
if result == 0:
session.add(new_migration)
session.commit()
except Exception:
logger.exception("Failed to save migration files to DB.")
finally:
session.close()
def add_model_checksum(
self, table_name: str, model_checksum: str = "0", descriptor_json: dict = {}
):
"""Adds a new checksum for a data model.
Args:
table_name (str): Name of the table to add the checksum.
checksum (str): Checksum value.
"""
session = Session()
try:
checksum = Checksum()
checksum.data_resource = table_name
checksum.model_checksum = model_checksum
checksum.descriptor_json = descriptor_json
session.add(checksum)
session.commit()
except Exception:
logger.exception("Error adding checksum")
finally:
session.close()
def get_all(self,
data_model,
data_resource_name,
restricted_fields,
offset=0,
limit=1):
"""Retrieve a paginated list of items.
Args:
data_model (object): SQLAlchemy ORM model.
data_resource_name (str): Name of the data resource.
offset (int): Pagination offset.
limit (int): Result limit.
Return:
dict, int: The response object and associated HTTP status code.
"""
session = Session()
response = OrderedDict()
response[data_resource_name] = []
response["links"] = []
links = []
try:
results = session.query(data_model).limit(limit).offset(
offset).all()
for row in results:
response[data_resource_name].append(
self.build_json_from_object(row, restricted_fields))
row_count = session.query(data_model).count()
if row_count > 0:
links = self.build_links(data_resource_name, offset, limit,
row_count)
response["links"] = links
except Exception:
raise InternalServerError()
session.close()
return response, 200
def update_one(
self,
id,
data_model,
data_resource_name,
table_schema,
restricted_fields,
request_obj,
mode="PATCH",
):
"""Update a single object from the data model based on it's primary
key.
Args:
id (any): The primary key for the specific object.
data_model (object): SQLAlchemy ORM model.
data_resource_name (str): Name of the data resource.
table_schema (dict): The Table Schema object to use for validation.
Return:
dict, int: The response object and the HTTP status code.
"""
try:
request_obj = request_obj.json
except Exception:
raise ApiError("No request body found.", 400)
try:
primary_key = table_schema["primaryKey"]
session = Session()
data_obj = (session.query(data_model).filter(
getattr(data_model, primary_key) == id).first())
if data_obj is None:
session.close()
raise ApiUnhandledError(f"Resource with id '{id}' not found.",
404)
except Exception:
raise ApiUnhandledError(f"Resource with id '{id}' not found.", 404)
_ = Schema(table_schema)
errors = []
accepted_fields = []
if validate(table_schema):
for field in table_schema["fields"]:
accepted_fields.append(field["name"])
for field in request_obj.keys():
if field not in accepted_fields:
errors.append(f"Unknown field '{field}' found.")
elif field in restricted_fields:
errors.append(f"Cannot update restricted field '{field}'.")
else:
session.close()
raise ApiError("Data schema validation error.", 400)
if len(errors) > 0:
session.close()
raise ApiError("Invalid request body.", 400, errors)
if mode == "PATCH":
for key, value in request_obj.items():
setattr(data_obj, key, value)
session.commit()
elif mode == "PUT":
for field in table_schema["fields"]:
if field["required"] and field["name"] not in request_obj.keys(
):
errors.append(
f"Required field '{field['name']}' is missing.")
if len(errors) > 0:
session.close()
raise ApiError("Invalid request body.", 400, errors)
for key, value in request_obj.items():
setattr(data_obj, key, value)
session.commit()
session.close()
return {"message": f"Successfully updated resource '{id}'."}, 201
def insert_one(self, data_model, data_resource_name, table_schema,
request_obj):
"""Insert a new object.
Args:
data_model (object): SQLAlchemy ORM model.
data_resource_name (str): Name of the data resource.
table_schema (dict): The Table Schema object to use for validation.
request_obj (dict): HTTP request object.
Return:
dict, int: The response object and associated HTTP status code.
"""
try:
request_obj = request_obj.json
except Exception:
raise ApiError("No request body found.", 400)
_ = Schema(table_schema)
errors = []
accepted_fields = []
if not validate(table_schema):
raise SchemaValidationFailure()
# Check for required fields
for field in table_schema["fields"]:
accepted_fields.append(field["name"])
if field["required"] and not field["name"] in request_obj.keys():
errors.append(f"Required field '{field['name']}' is missing.")
valid_fields = []
many_query = []
for field in request_obj.keys():
if field in accepted_fields:
valid_fields.append(field)
else:
junc_table = JuncHolder.lookup_table(field, data_resource_name)
if junc_table is not None:
values = request_obj[field]
if not isinstance(values, list):
values = [values]
many_query.append([field, values, junc_table])
else:
errors.append(f"Unknown field '{field}' found.")
if len(errors) > 0:
raise ApiError("Invalid request body.", 400, errors)
try:
session = Session()
new_object = data_model()
for field in valid_fields:
value = request_obj[field]
setattr(new_object, field, value)
session.add(new_object)
session.commit()
id_value = getattr(new_object, table_schema["primaryKey"])
# process the many_query
for field, values, table in many_query:
self.process_many_query(session, table, id_value, field,
data_resource_name, values)
return {
"message": "Successfully added new resource.",
"id": id_value
}, 201
except Exception:
raise ApiUnhandledError("Failed to create new resource.", 400)
finally:
session.close()
