managementDB.py

# Copyright 2022-2023 ETSI TeraFlowSDN - TFS OSG (https://tfs.etsi.org/)
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import logging, time
import sqlalchemy
import sqlalchemy_utils
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from telemetry.database.TelemetryEngine import TelemetryEngine
from telemetry.database.TelemetryModel import Base

LOGGER = logging.getLogger(__name__)
DB_NAME = "telemetryfrontend"

# # Create a base class for declarative models
# Base = declarative_base()

class managementDB:
    def __init__(self):
        self.db_engine = TelemetryEngine.get_engine()
        if self.db_engine is None:
            LOGGER.error('Unable to get SQLAlchemy DB Engine...')
            return False
        self.db_name = DB_NAME
        self.Session = sessionmaker(bind=self.db_engine)

    @staticmethod
    def create_database(engine : sqlalchemy.engine.Engine) -> None:
        if not sqlalchemy_utils.database_exists(engine.url):
            LOGGER.info("Database created. {:}".format(engine.url))
            sqlalchemy_utils.create_database(engine.url)

    @staticmethod
    def drop_database(engine : sqlalchemy.engine.Engine) -> None:
        if sqlalchemy_utils.database_exists(engine.url):
            sqlalchemy_utils.drop_database(engine.url)

    # def create_database(self):
    #     try:
    #         with self.db_engine.connect() as connection:
    #             connection.execute(f"CREATE DATABASE {self.db_name};")
    #         LOGGER.info('managementDB initalizes database. Name: {self.db_name}')
    #         return True
    #     except: 
    #         LOGGER.exception('Failed to check/create the database: {:s}'.format(str(self.db_engine.url)))
    #         return False
    
    @staticmethod
    def create_tables(engine : sqlalchemy.engine.Engine):
        try:
            Base.metadata.create_all(engine)     # type: ignore
            LOGGER.info("Tables created in the DB Name: {:}".format(DB_NAME))
        except Exception as e:
            LOGGER.info("Tables cannot be created in the TelemetryFrontend database. {:s}".format(str(e)))

    def verify_tables(self):
        try:
            with self.db_engine.connect() as connection:
                result = connection.execute("SHOW TABLES;")
                tables = result.fetchall()      # type: ignore
                LOGGER.info("Tables verified: {:}".format(tables))
        except Exception as e:
            LOGGER.info("Unable to fetch Table names. {:s}".format(str(e)))

    @staticmethod
    def add_row_to_db(self, row):
        session = self.Session()
        try:
            session.add(row)
            session.commit()
            LOGGER.info(f"Row inserted into {row.__class__.__name__} table.")
        except Exception as e:
            session.rollback()
            LOGGER.error(f"Failed to insert new row into {row.__class__.__name__} table. {str(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:
                LOGGER.info(f"{model.__name__} ID found: {str(entity)}")
                return entity
            else:
                LOGGER.warning(f"{model.__name__} ID not found: {str(id_to_search)}")
                return None
        except Exception as e:
            session.rollback()
            LOGGER.info(f"Failed to retrieve {model.__name__} ID. {str(e)}")
            raise
        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.info("Deleted %s with %s: %s", model.__name__, col_name, id_to_search)
            else:
                LOGGER.warning("%s with %s %s not found", model.__name__, col_name, id_to_search)
        except Exception as e:
            session.rollback()
            LOGGER.error("Error deleting %s with %s %s: %s", model.__name__, col_name, id_to_search, e)
        finally:
            session.close()
    
    def select_with_filter(self, model, **filters):
        session = self.Session()
        try:
            query = session.query(model)
            for column, value in filters.items():
                query = query.filter(getattr(model, column) == value) # type: ignore   
            result = query.all()
            if result:
                LOGGER.info(f"Fetched filtered rows from {model.__name__} table with filters: {filters}") #  - Results: {result}
            else:
                LOGGER.warning(f"No matching row found in {model.__name__} table with filters: {filters}")
            return result
        except Exception as e:
            LOGGER.error(f"Error fetching filtered rows from {model.__name__} table with filters {filters} ::: {e}")
            return []
        finally:
            session.close()