import logging import sqlalchemy_utils from sqlalchemy.orm import sessionmaker from pcep.database.LSPEngine import LSPEngine from pcep.database.LSPModel import LspModel as LspModel from common.method_wrappers.ServiceExceptions import ( AlreadyExistsException, OperationFailedException , NotFoundException) LOGGER = logging.getLogger(__name__) DB_NAME = "tfs_lsp_mgmt" class LspDB: def __init__(self): self.db_engine = LSPEngine.get_engine() if self.db_engine is None: LOGGER.error('Unable to get SQLAlchemy DB Engine...') return None #return False self.db_name = DB_NAME self.Session = sessionmaker(bind=self.db_engine) def create_database(self) -> None: if not sqlalchemy_utils.database_exists(self.db_engine.url): sqlalchemy_utils.create_database(self.db_engine.url) LOGGER.debug("Database created. {:}".format(self.db_engine.url)) def drop_database(self) -> None: if sqlalchemy_utils.database_exists(self.db_engine.url): sqlalchemy_utils.drop_database(self.db_engine.url) def create_tables(self): try: LspModel.metadata.create_all(self.db_engine) # type: ignore LOGGER.debug("Tables created in the DB Name: {:}".format(self.db_name)) except Exception as e: LOGGER.debug("Tables cannot be created in the lsp database. {:s}".format(str(e))) raise OperationFailedException ("Tables can't be created", extra_details=["unable to create table {:}".format(e)]) def verify_tables(self): try: with self.db_engine.connect() as connection: result = connection.execute("SHOW TABLES;") tables = result.fetchall() # type: ignore LOGGER.debug("Tables verified: {:}".format(tables)) except Exception as e: LOGGER.debug("Unable to fetch Table names. {:s}".format(str(e))) def add_row_to_db(self, row): session = self.Session() try: session.add(row) session.commit() LOGGER.debug(f"Row inserted into {row.__class__.__name__} table.") return True except Exception as e: session.rollback() if "psycopg2.errors.UniqueViolation" in str(e): LOGGER.error(f"Unique key violation: {row.__class__.__name__} table. {str(e)}") raise AlreadyExistsException(row.__class__.__name__, row, extra_details=["Unique key violation: {:}".format(e)] ) else: LOGGER.error(f"Failed to insert new row into {row.__class__.__name__} table. {str(e)}") raise OperationFailedException ("Insertion failed", extra_details=["unable to insert row {:}".format(e)]) finally: session.close() def search_db_row_by_id(self, model, col_name, id_to_search): session = self.Session() try: entity = session.query(model).filter_by(**{col_name: id_to_search}).first() if entity: return entity else: LOGGER.debug(f"{model.__name__} ID not found, No matching row: {str(id_to_search)}") return None except Exception as e: LOGGER.debug(f"Failed to retrieve {model.__name__} ID. {str(e)}") raise OperationFailedException ("search by column id", extra_details=["unable to search row {:}".format(e)]) finally: session.close() def delete_db_row_by_id(self, model, col_name, id_to_search): session = self.Session() try: record = session.query(model).filter_by(**{col_name: id_to_search}).first() if record: session.delete(record) session.commit() LOGGER.debug("Deleted %s with %s: %s", model.__name__, col_name, id_to_search) else: LOGGER.debug("%s with %s %s not found", model.__name__, col_name, id_to_search) return None except Exception as e: session.rollback() LOGGER.error("Error deleting %s with %s %s: %s", model.__name__, col_name, id_to_search, e) raise OperationFailedException ("Deletion by column id", extra_details=["unable to delete row {:}".format(e)]) finally: session.close() def select_with_filter(self, model, filter_object): session = self.Session() try: query = session.query(LspModel) # Apply filters based on the filter_object if filter_object.lsp_id: query = query.filter(LspModel.lsp_id.in_([l.lsp_id.uuid for l in filter_object.lsp_id])) if filter_object.source: query = query.filter(LspModel.source.in_([s.source_uuid.uuid for s in filter_object.source])) if filter_object.destination: query = query.filter(LspModel.destination.in_([d.destination_uuid.uuid for d in filter_object.destination])) result = query.all() if result: LOGGER.debug(f"Fetched filtered rows from {model.__name__} table with filters: {filter_object}") else: LOGGER.debug(f"No matching row found in {model.__name__} table with filters: {filter_object}") return result except Exception as e: LOGGER.error(f"Error fetching filtered rows from {model.__name__} table with filters {filter_object} ::: {e}") raise OperationFailedException ("Select by filter", extra_details=["unable to apply the filter {:}".format(e)]) finally: session.close()