qgis_snowflake_connector_algorithm.py

# -*- coding: utf-8 -*-

"""
/***************************************************************************
 Snowflake Connector for QGIS
 This package includes the Snowflake Connector for QGIS.
 Generated by Plugin Builder: http://g-sherman.github.io/Qgis-Plugin-Builder/
                              -------------------
        begin                : 2024-08-07
        copyright            : (C) 2024 by Snowflake
        email                : erick.cuberojimenez@snowflake.com
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is licensed under the MIT License. You may use, modify,  *
 *   and distribute it under the terms specified in the license.           *
 *                                                                         *
 *   MIT License                                                           *
 *                                                                         *
 *   Permission is hereby granted, free of charge, to any person obtaining *
 *   a copy of this software and associated documentation files (the       *
 *   "Software"), to deal in the Software without restriction, including   *
 *   without limitation the rights to use, copy, modify, merge, publish,   *
 *   distribute, sublicense, and/or sell copies of the Software, and to    *
 *   permit persons to whom the Software is furnished to do so, subject    *
 *   to the following conditions:                                          *
 *                                                                         *
 *   The above copyright notice and this permission notice shall be        *
 *   included in all copies or substantial portions of the Software.       *
 *                                                                         *
 *   THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,       *
 *   EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF    *
 *   MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.*
 *   IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY  *
 *   CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,  *
 *   TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE     *
 *   SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.                *
 *                                                                         *
 ***************************************************************************/
"""

__author__ = "Snowflake Inc."
__date__ = "2024-08-07"
__copyright__ = "(C) 2024 by Snowflake"

# This will get replaced with a git SHA1 when you do a git archive

__revision__ = "$Format:%H$"

import json
import typing
from qgis.PyQt.QtCore import QCoreApplication, QByteArray, QVariant
from qgis.core import (
    QgsProcessing,
    QgsProcessingAlgorithm,
    QgsProcessingParameterFeatureSource,
    QgsProcessingParameterString,
    QgsProcessingContext,
    QgsVectorLayer,
)

from .managers.sf_connection_manager import SFConnectionManager

from .helpers.data_base import (
    create_schema,
    create_table,
    get_count_schemas,
    get_count_tables,
)

from .entities.sf_dynamic_connection_combo_box_widget import (
    DynamicConnectionComboBoxWidget,
)

from .providers.sf_data_source_provider import SFDataProvider

from .helpers.utils import get_authentification_information, get_qsettings


class QGISSnowflakeConnectorAlgorithm(QgsProcessingAlgorithm):
    """
    This is an example algorithm that takes a vector layer and
    creates a new identical one.

    It is meant to be used as an example of how to create your own
    algorithms and explain methods and variables used to do it. An
    algorithm like this will be available in all elements, and there
    is not need for additional work.

    All Processing algorithms should extend the QgsProcessingAlgorithm
    class.
    """

    # Constants used to refer to parameters and outputs. They will be
    # used when calling the algorithm from another algorithm, or when
    # calling from the QGIS console.
    DATABASE = "DATABASE"
    OUTPUT = "OUTPUT"
    INPUT = "INPUT"
    FIRST_COMBO = "FIRST_COMBO"
    SECOND_COMBO = "SECOND_COMBO"
    CONNECTION_DYN_CB = "CONNECTION_DYN_CB"
    GEOMETRY_COLUMN = "GEOMETRY_COLUMN"

    def initAlgorithm(self, config):
        """
        Here we define the inputs and output of the algorithm, along
        with some other properties.
        """

        # We add the input vector features source. It can have any kind of
        # geometry.
        self.settings = get_qsettings()
        self.addParameter(
            QgsProcessingParameterFeatureSource(
                self.INPUT,
                self.tr("Input layer"),
                [QgsProcessing.TypeVectorAnyGeometry],
            )
        )

        param = QgsProcessingParameterString(
            self.CONNECTION_DYN_CB,
            "Connection (connection name)",
            optional=False,
            defaultValue="",
        )
        param.setMetadata(
            {
                "widget_wrapper": {"class": DynamicConnectionComboBoxWidget},
                "required": True,
            }
        )
        self.addParameter(param)

        self.addParameter(
            QgsProcessingParameterString(
                self.GEOMETRY_COLUMN,
                "Geometry Column",
                defaultValue="",  # Optional default value
            )
        )

        self.connection_manager: SFConnectionManager = (
            SFConnectionManager.get_instance()
        )

    def processAlgorithm(self, parameters, context, feedback):
        """
        Here is where the processing itself takes place.
        """
        source = self.parameterAsSource(parameters, self.INPUT, context)
        geom_column = self.parameterAsString(parameters, self.GEOMETRY_COLUMN, context)

        from urllib.parse import parse_qs, urlparse

        parsed_uri = urlparse(
            self.parameterAsVectorLayer(parameters, self.INPUT, context).source()
        )
        params = parse_qs(parsed_uri.query)

        is_snowflake_layer = (
            "internal_provider" in params
            and len(params["internal_provider"]) > 0
            and params["internal_provider"][0] == "snowflake"
        )

        selected_connection, selected_database, selected_schema, selected_table = (
            json.loads(
                self.parameterAsString(parameters, self.CONNECTION_DYN_CB, context)
            )
        )

        auth_information = get_authentification_information(
            self.settings, selected_connection
        )
        self.sf_data_provider = SFDataProvider(auth_information)
        if selected_schema == "":
            selected_schema = "PUBLIC"
            count_schemas = get_count_schemas(
                settings=self.settings,
                connection_name=selected_connection,
                data_base_name=selected_database,
                schema_name=selected_schema,
            )

            if count_schemas == 0:
                create_schema(
                    settings=self.settings,
                    connection_name=selected_connection,
                    schema_name=selected_schema,
                )

        if selected_table == "":
            selected_table = self.parameterAsSource(
                parameters, self.INPUT, context
            ).sourceName()

            count_table = get_count_tables(
                connection_name=selected_connection,
                database_name=selected_database,
                schema_name=selected_schema,
                table_name=selected_table,
            )
            if count_table == 0:
                query_create_table = self.get_create_table_query(
                    geom_column=geom_column,
                    source=source,
                    is_snowflake_layer=is_snowflake_layer,
                    database_name=selected_database,
                    schema_name=selected_schema,
                    table_name=selected_table,
                )

                create_table(
                    connection_name=selected_connection, query=query_create_table
                )

        # Retrieve the feature source and sink. The 'dest_id' variable is used
        # to uniquely identify the feature sink, and must be included in the
        # dictionary returned by the processAlgorithm function.
        # source = self.parameterAsSource(parameters, self.INPUT, context)
        # (sink, dest_id) = self.parameterAsSink(parameters, self.OUTPUT,
        #         context, source.fields(), source.wkbType(), source.sourceCrs())

        # Compute the number of steps to display within the progress bar and
        # get features from source
        total = 100.0 / source.featureCount() if source.featureCount() else 0
        features = source.getFeatures()

        if source.featureCount() == 0:
            feedback.setProgress(100)
            return {"Rows Inserted": 0}

        query_columns = f"{geom_column}"

        for field in source.fields():
            if field.name().lower() == geom_column.lower():
                continue
            query_columns += f",{field.name()}"

        query_base = f'INSERT INTO "{selected_database}"."{selected_schema}"."{selected_table}" ({query_columns}) VALUES '
        query = query_base
        first = True
        executed = False
        for current, feature in enumerate(features):
            if current != 0 and current % 5000 == 0:
                cur = self.sf_data_provider.execute_query(query, selected_connection)
                cur.close()
                query = query_base
                first = True
                executed = True
            executed = False
            # Stop the algorithm if cancel button has been clicked
            if feedback.isCanceled():
                break

            byte_array = QByteArray(feature.geometry().asWkb())

            # Convert QByteArray to hexadecimal string
            hex_string = byte_array.toHex().data().decode()
            if first:
                first = False
            else:
                query += ","

            query_values = f"('{hex_string}'"

            for field in source.fields():
                query_values += ","

                if field.name().lower() == geom_column.lower():
                    query_values += f"'{hex_string}'"
                else:
                    feat_index = feature.fieldNameIndex(field.name())
                    feat_val = feature.attribute(feat_index)
                    if is_snowflake_layer:
                        if feat_val is None:
                            query_values += "NULL"
                        elif field.subType() == QVariant.String:
                            feat_val = feat_val.replace("'", "\\'")
                            query_values += f"'{feat_val}'"
                        elif field.subType() in [
                            QVariant.Date,
                            QVariant.DateTime,
                            QVariant.Time,
                        ]:
                            query_values += f"'{feat_val}'"
                        else:
                            query_values += f"{feat_val}"
                    else:
                        if feat_val is None:
                            query_values += "NULL"
                        elif isinstance(feat_val, QVariant) and feat_val.isNull():
                            query_values += "NULL"
                        elif field.type() == QVariant.String:
                            feat_val = feat_val.replace("'", "\\'")
                            query_values += f"'{feat_val}'"
                        elif field.type() == QVariant.Date:
                            query_values += f"'{feat_val.toString('yyyy-MM-dd')}'"
                        elif field.type() == QVariant.Time:
                            query_values += f"'{feat_val.toString('hh:mm:ss')}'"
                        elif field.type() == QVariant.DateTime:
                            query_values += (
                                f"'{feat_val.toString('yyyy-MM-dd hh:mm:ss')}'"
                            )
                        else:
                            query_values += f"{feat_val}"
            query_values += ")"

            query += query_values

            # Update the progress bar
            feedback.setProgress(int(current * total))

        if not executed:
            cur = self.sf_data_provider.execute_query(query, selected_connection)
            cur.close()

        # Return the results of the algorithm. In this case our only result is
        # the feature sink which contains the processed features, but some
        # algorithms may return multiple feature sinks, calculated numeric
        # statistics, etc. These should all be included in the returned
        # dictionary, with keys matching the feature corresponding parameter
        # or output names.
        # return {self.OUTPUT: dest_id}
        return {"Rows Inserted": source.featureCount()}

    def get_field_type_from_code_type(self, code_type: int) -> str:
        """
        Get the field type from the code type.

        Args:
            code_type (int): The code type.

        Returns:
            str: The field type.
        """
        if code_type == QVariant.String:
            return "TEXT"
        if code_type == QVariant.Int:
            return "INTEGER"
        if code_type == QVariant.Double:
            return "DOUBLE"
        if code_type == QVariant.Date:
            return "DATE"
        if code_type == QVariant.Time:
            return "TIME"
        if code_type == QVariant.DateTime:
            return "TIMESTAMP"
        if code_type == QVariant.Bool:
            return "BOOLEAN"
        return "TEXT"

    def name(self):
        """
        Returns the algorithm name, used for identifying the algorithm. This
        string should be fixed for the algorithm, and must not be localised.
        The name should be unique within each provider. Names should contain
        lowercase alphanumeric characters only and no spaces or other
        formatting characters.
        """
        return "Export to Snowflake"

    def displayName(self):
        """
        Returns the translated algorithm name, which should be used for any
        user-visible display of the algorithm name.
        """
        return self.tr(self.name())

    def group(self):
        """
        Returns the name of the group this algorithm belongs to. This string
        should be localised.
        """
        return self.tr("Database")

    def groupId(self):
        """
        Returns the unique ID of the group this algorithm belongs to. This
        string should be fixed for the algorithm, and must not be localised.
        The group id should be unique within each provider. Group id should
        contain lowercase alphanumeric characters only and no spaces or other
        formatting characters.
        """
        return "database"

    def tr(self, string):
        return QCoreApplication.translate("Processing", string)

    def createInstance(self):
        return QGISSnowflakeConnectorAlgorithm()

    def checkParameterValues(
        self, parameters: typing.Dict[str, typing.Any], context: "QgsProcessingContext"
    ) -> typing.Tuple[bool, str]:
        """
        Check if the parameters are valid and return a tuple with a boolean and a message
        """
        try:
            geom_column = self.parameterAsString(
                parameters, self.GEOMETRY_COLUMN, context
            )
            selected_connection, selected_database, selected_schema, selected_table = (
                json.loads(
                    self.parameterAsString(parameters, self.CONNECTION_DYN_CB, context)
                )
            )
            selected_table_is_empty = False
            if selected_schema == "":
                selected_schema = "PUBLIC"
            if selected_table == "":
                selected_table_is_empty = True
                selected_table = self.parameterAsSource(
                    parameters, self.INPUT, context
                ).sourceName()
            if geom_column == "":
                return False, "Geometry Column can not be empty!"

            if selected_connection == "":
                return False, "Please select a connection!"

            if not selected_table_is_empty:
                # Check if the selected table exists
                source = self.parameterAsSource(parameters, self.INPUT, context)
                count_table = get_count_tables(
                    connection_name=selected_connection,
                    database_name=selected_database,
                    schema_name=selected_schema,
                    table_name=selected_table,
                )
                if count_table == 0:
                    query_create_table = self.get_create_table_query(
                        geom_column=geom_column,
                        source=source,
                        is_snowflake_layer=False,
                        database_name=selected_database,
                        schema_name=selected_schema,
                        table_name=selected_table,
                    )

                    create_table(
                        connection_name=selected_connection, query=query_create_table
                    )

                auth_information = get_authentification_information(
                    self.settings, selected_connection
                )
                self.sf_data_provider = SFDataProvider(auth_information)

                query_select_columns = f"""
                    SELECT DISTINCT COLUMN_NAME
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_CATALOG = '{selected_database}'
                    AND TABLE_SCHEMA ILIKE '{selected_schema}'
                    AND TABLE_NAME ILIKE '{selected_table}'
                """
                cur_select_columns = self.sf_data_provider.execute_query(
                    query_select_columns, selected_connection
                )
                available_columns = []
                column_found = False
                for row in cur_select_columns.fetchall():
                    if row[0].upper() == geom_column.upper():
                        cur_select_columns.close()
                        column_found = True
                        return True, ""
                    else:
                        available_columns.append(row[0])

                if not column_found:
                    cur_select_columns.close()
                    return (
                        False,
                        f"Given Geometry Column: {geom_column} does not exist! Available columns: {', '.join(available_columns)}",
                    )
                cur_select_columns.close()

            return True, ""
        except Exception as e:
            return (
                False,
                f"There was an error while checking the parameter values. Error: {str(e)}",
            )

    def get_create_table_query(
        self,
        geom_column: str,
        source: "QgsVectorLayer",
        is_snowflake_layer: bool,
        database_name: str,
        schema_name: str,
        table_name: str,
    ) -> str:
        """
        Generates a SQL query string to create a table in Snowflake with the specified columns and types.

        Args:
            geom_column (str): The name of the geometry column.
            source (QgsVectorLayer): The source vector layer containing the fields.
            is_snowflake_layer (bool): Flag indicating if the source is a Snowflake layer.
            database_name (str): The name of the database.
            schema_name (str): The name of the schema.
            table_name (str): The name of the table to be created.

        Returns:
            str: A SQL query string to create the table with the specified columns and types.
        """
        query_create_table_cols = f"{geom_column} GEOGRAPHY"
        for field in source.fields():
            if field.name().lower() == geom_column.lower():
                continue
            if is_snowflake_layer:
                field_to_use = field.subType()
            else:
                field_to_use = field.type()

            query_create_table_cols += (
                f",{field.name()} {self.get_field_type_from_code_type(field_to_use)}"
            )

        return f"""CREATE TABLE "{database_name}"."{schema_name}"."{table_name}"({query_create_table_cols})"""