Skip to content

LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows

12.3 KB
Raw Blame
import datetime
import json
import logging
from typing import Any

from utils.constants import Common
from workflow_manager.endpoint_v2.constants import TableColumns
from workflow_manager.endpoint_v2.enums import FileProcessingStatus
from workflow_manager.endpoint_v2.exceptions import UnstractDBException
from workflow_manager.workflow_v2.enums import AgentName, ColumnModes

from unstract.connectors.databases import connectors as db_connectors
from unstract.connectors.databases.exceptions import UnstractDBConnectorException
from unstract.connectors.databases.unstract_db import UnstractDB
from unstract.connectors.exceptions import ConnectorError

logger = logging.getLogger(__name__)


class DatabaseUtils:
    @staticmethod
    def _create_safe_error_json(data_description: str, original_error: Exception) -> dict:
        """Create a standardized error JSON object that can be safely serialized.

        Args:
            data_description (str): Description of the data being serialized
            original_error (Exception): The original exception that occurred

        Returns:
            dict: A safely serializable JSON object with error details
        """
        return {
            "error": "JSON serialization failed",
            "error_type": original_error.__class__.__name__,
            "error_message": str(original_error),
            "data_type": str(type(original_error)),
            "data_description": data_description,
            "timestamp": datetime.datetime.now().isoformat(),
        }

    @staticmethod
    def get_column_types(
        conn_cls: Any,
        table_name: str,
    ) -> Any:
        """Function to return connector db column and types by calling
        connector table information schema.

        Args:
            conn_cls (Any): DB Connection class
            table_name (str): DB table-name

        Raises:
            UnstractDBException: _description_

        Returns:
            Any: db column name and db column types of corresponding table
        """
        try:
            return conn_cls.get_information_schema(table_name=table_name)
        except ConnectorError as e:
            raise UnstractDBException(detail=e.message, status_code=e.status_code) from e

    @staticmethod
    def get_sql_values_for_query(
        conn_cls: Any,
        values: dict[str, Any],
        column_types: dict[str, str],
    ) -> dict[str, Any]:
        """Function to prepare SQL values by calling connector method.

        Args:
            conn_cls (Any): DB Connection class
            values (dict[str, Any]): dictionary of columns and values
            column_types (dict[str, str]): types of columns from database schema

        Returns:
            dict[str, Any]: Dictionary of column names to SQL values
        """
        return conn_cls.get_sql_values_for_query(values=values, column_types=column_types)

    @staticmethod
    def get_columns_and_values(
        column_mode_str: str,
        data: Any,
        file_path: str,
        execution_id: str,
        file_path_name: str = "file_path",
        execution_id_name: str = "execution_id",
        include_timestamp: bool = False,
        include_agent: bool = False,
        agent_name: str | None = AgentName.UNSTRACT_DBWRITER.value,
        single_column_name: str = "data",
        table_info: dict[str, str] | None = None,
        metadata: dict[str, Any] | None = None,
        error: str | None = None,
    ) -> dict[str, Any]:
        """Generate a dictionary of columns and values based on specified
        parameters.

        Args:
            column_mode_str (str): The string representation of the column mode,
                which determines how data is stored in the dictionary.
            data (Any): The data to be stored in the dictionary.
            include_timestamp (bool, optional): Whether to include the
                current timestamp in the dictionary. Defaults to False.
            include_agent (bool, optional): Whether to include the agent's name
                in the dictionary. Defaults to False.
            agent_name (str, optional): The name of the agent when include_agent
                is true. Defaults to AgentName.UNSTRACT_DBWRITER.
            single_column_name (str, optional): The name of the single column
                when using 'WRITE_JSON_TO_A_SINGLE_COLUMN' mode.
                Defaults to "data".

        Returns:
            dict: A dictionary containing columns and values based on
                the specified parameters.
        """
        values: dict[str, Any] = {}
        try:
            column_mode = ColumnModes(column_mode_str)
        except ValueError:
            # Handle the case where the string is not a valid enum value
            column_mode = ColumnModes.WRITE_JSON_TO_A_SINGLE_COLUMN

        if include_agent and agent_name:
            values[TableColumns.CREATED_BY] = agent_name

        if include_timestamp:
            values[TableColumns.CREATED_AT] = datetime.datetime.now()

        has_metadata_col = (
            (table_info is None)
            or any(k.lower() == TableColumns.METADATA.lower() for k in table_info)
            if table_info
            else True
        )
        has_error_col = (
            (table_info is None)
            or any(k.lower() == TableColumns.ERROR_MESSAGE.lower() for k in table_info)
            if table_info
            else True
        )
        has_status_col = (
            (table_info is None)
            or any(k.lower() == TableColumns.STATUS.lower() for k in table_info)
            if table_info
            else True
        )

        if metadata and has_metadata_col:
            try:
                values[TableColumns.METADATA] = json.dumps(metadata)
            except (TypeError, ValueError) as e:
                logger.error(f"Failed to serialize metadata to JSON: {e}")
                # Create a safe fallback error object
                fallback_metadata = DatabaseUtils._create_safe_error_json("metadata", e)
                values[TableColumns.METADATA] = json.dumps(fallback_metadata)

        if error and has_error_col:
            values[TableColumns.ERROR_MESSAGE] = error
        if has_status_col:
            values[TableColumns.STATUS] = (
                FileProcessingStatus.ERROR if error else FileProcessingStatus.SUCCESS
            )
        if column_mode == ColumnModes.WRITE_JSON_TO_A_SINGLE_COLUMN:
            v2_col_name = f"{single_column_name}_v2"
            has_v2_col = (
                (table_info is None)
                or any(k.lower() == v2_col_name.lower() for k in table_info)
                if table_info
                else True
            )
            if isinstance(data, str):
                wrapped_dict = {"result": data}
                values[single_column_name] = wrapped_dict
                if has_v2_col:
                    values[v2_col_name] = wrapped_dict
            else:
                values[single_column_name] = data
                if has_v2_col:
                    values[v2_col_name] = data
        values[file_path_name] = file_path
        values[execution_id_name] = execution_id
        logger.debug(f"database_utils.py get_columns_and_values  values: {values}")
        return values

    @staticmethod
    def get_sql_query_data(
        conn_cls: Any,
        table_name: str,
        values: dict[str, Any],
    ) -> dict[str, Any]:
        """Generate SQL columns and values for an insert query based on the
        provided values and table schema.

        Args:
            connector_cls: DB connection class
            table_name (str): The name of the target table for the insert query.
            values (dict[str, Any]): A dictionary containing column-value pairs
                for the insert query.

        Returns:
            list[str]: A list of SQL values suitable for use in an insert query.

        Note:
            - This function determines the database type based on the
                `engine` parameter.
            - If the database is Snowflake (DBConnectionClass.SNOWFLAKE),
                it handles Snowflake-specific SQL generation.
            - For other SQL databases, it uses default SQL generation
                based on column types.
        """
        column_types: dict[str, str] = DatabaseUtils.get_column_types(
            conn_cls=conn_cls, table_name=table_name
        )
        logger.debug(f"database_utils.py get_sql_query_data column_types: {column_types}")

        sql_columns_and_values = DatabaseUtils.get_sql_values_for_query(
            conn_cls=conn_cls,
            values=values,
            column_types=column_types,
        )
        logger.debug(
            f"database_utils.py get_sql_query_data sql_columns_and_values: {sql_columns_and_values}"
        )

        return sql_columns_and_values

    @staticmethod
    def execute_write_query(
        db_class: UnstractDB,
        engine: Any,
        table_name: str,
        sql_keys: list[str],
        sql_values: list[str],
    ) -> None:
        """Execute Insert Query.

        Args:
            engine (Any): _description_
            table_name (str): table name
            sql_keys (list[str]): columns
            sql_values (list[str]): values
        Notes:
        - Snowflake does not support INSERT INTO ... VALUES ...
          syntax when VARIANT columns are present (JSON).
          So we need to use INSERT INTO ... SELECT ... syntax
        - sql values can contain data with single quote. It needs to
        """
        sql = db_class.get_sql_insert_query(
            table_name=table_name, sql_keys=sql_keys, sql_values=sql_values
        )
        logger.debug(f"inserting into table_name: {table_name} with sql_query: {sql}")
        logger.debug(f"sql_values: {sql_values}")

        try:
            db_class.execute_query(
                engine=engine,
                sql_query=sql,
                sql_values=sql_values,
                table_name=table_name,
                sql_keys=sql_keys,
            )
        except UnstractDBConnectorException as e:
            raise UnstractDBException(detail=e.detail) from e
        logger.debug(f"sucessfully inserted into table {table_name} with: {sql} query")

    @staticmethod
    def get_db_class(connector_id: str, connector_settings: dict[str, Any]) -> UnstractDB:
        connector = db_connectors[connector_id][Common.METADATA][Common.CONNECTOR]
        connector_class: UnstractDB = connector(connector_settings)
        return connector_class

    @staticmethod
    def create_table_if_not_exists(
        db_class: UnstractDB,
        engine: Any,
        table_name: str,
        database_entry: dict[str, Any],
    ) -> None:
        """Creates table if not exists.

        Args:
            class_name (UnstractDB): Type of Unstract DB connector
            table_name (str): _description_
            database_entry (dict[str, Any]): _description_

        Raises:
            e: _description_
        """
        sql = db_class.create_table_query(
            table=table_name,
            database_entry=database_entry,
            permanent_columns=TableColumns.PERMANENT_COLUMNS,
        )
        logger.info(f"creating table {table_name} with: {sql} query")

        try:
            db_class.execute_query(
                engine=engine, sql_query=sql, sql_values=None, table_name=table_name
            )
        except UnstractDBConnectorException as e:
            raise UnstractDBException(detail=e.detail) from e
        logger.info(f"successfully created table {table_name} with: {sql} query")

    @staticmethod
    def migrate_table_to_v2(
        db_class: UnstractDB,
        engine: Any,
        table_name: str,
        column_name: str,
    ) -> dict[str, str]:
        """Migrate table to v2 by adding _v2 columns.

        Args:
            db_class (UnstractDB): DB Connection class
            engine (Any): Database engine
            table_name (str): Name of the table to migrate
            column_name (str): Base column name for v2 migration

        Returns:
            dict[str, str]: Updated table information schema

        Raises:
            UnstractDBException: If migration fails
        """
        try:
            result: dict[str, str] = db_class.migrate_table_to_v2(
                table_name=table_name,
                column_name=column_name,
                engine=engine,
            )
            return result
        except UnstractDBConnectorException as e:
            raise UnstractDBException(detail=e.detail) from e