Repository: mindsdb/mindsdb
Pull request: 💯💯 Added Monetdb Handler
Fixes #2729 

## Please describe what changes you made, in as much detail as possible
  - Added MonetDB Handler And Tested .
  - you can test and Provide your feedback
  - [Installation](https://www.monetdb.org/documentation-Jan2022/admin-guide/installation/)

mindsdb

Problem statement:
[New Integration]: Integration with MonetDB
### Is there an existing integration?

- [X] I have searched the existing integrations.

### Use Case

**MonetDB** is an open-source column-oriented relational database management system. It is designed to provide high performance on complex queries against large databases, such as combining tables with hundreds of columns and millions of rows.
It  is used worldwide for _education_, _research_, and _businesses_.

### Motivation

As MonetDB is very fast in executing complex queries and it is open source as well.
It is used in education, research and businesses as Data warehouse.

It can be very nice to integrate with mindsdb's amazing ML platforms.

### Implementation

_No response_

### Anything else?

_No response_

Hints from issue discussion:
@ZoranPandovski can i work on this.

Commit message: Merge branch 'mindsdb:staging' into monetdb

Patch to review:
diff --git a/mindsdb/integrations/handlers/monetdb_handler/README.md b/mindsdb/integrations/handlers/monetdb_handler/README.md
new file mode 100644
index 00000000000..92f5a59a39b
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/README.md
@@ -0,0 +1,38 @@
+# MonetDB Handler
+
+This is the implementation of the MonetDB handler for MindsDB.
+
+## MonetDB
+MonetDB is an open-source column-oriented relational database management system originally developed at the Centrum Wiskunde & Informatica in the Netherlands. It is designed to provide high performance on complex queries against large databases, such as combining tables with hundreds of columns and millions of rows.
+
+## Implementation
+This handler was implemented using the `pymonetdb`, a Python library that allows you to use Python code to run SQL commands on MonetDB Database.
+
+The required arguments to establish a connection are,
+* `user`: username asscociated with database
+* `password`: password to authenticate your access
+* `host`: host to server IP Address or hostname
+* `port`: port through which TCPIP connection is to be made
+* `database`: Database name to be connected
+* `schema_name `: schema name to get tables. (_Optional_) **Default it will select current schema.**
+
+## Usage
+In order to make use of this handler and connect to DB2 in MindsDB, the following syntax can be used,
+~~~~sql
+CREATE DATABASE monetdb_datasource
+WITH
+engine='monetdb',
+parameters={
+    "user":"monetdb",
+    "password":"monetdb",
+    "host":"127.0.0.1",
+    "port":50000,
+    "schema_name":"sys",
+    "database":"demo"
+};
+~~~~
+
+Now, you can use this established connection to query your database as follows,
+~~~~sql
+SELECT * FROM monetdb_datasource.demo;
+~~~~
diff --git a/mindsdb/integrations/handlers/monetdb_handler/__about__.py b/mindsdb/integrations/handlers/monetdb_handler/__about__.py
new file mode 100644
index 00000000000..bb303237d9a
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/__about__.py
@@ -0,0 +1,9 @@
+__title__ = 'MindsDB MonetDB handler'
+__package_name__ = 'mindsdb_monetdb_handler'
+__version__ = '0.0.1'
+__description__ = "MindsDB handler for MonetDB"
+__author__ = 'Parthiv Makwana'
+__github__ = 'https://github.com/mindsdb/mindsdb'
+__pypi__ = 'https://pypi.org/project/mindsdb/'
+__license__ = 'GPL-3.0'
+__copyright__ = 'Copyright 2022- mindsdb'
diff --git a/mindsdb/integrations/handlers/monetdb_handler/__init__.py b/mindsdb/integrations/handlers/monetdb_handler/__init__.py
new file mode 100644
index 00000000000..52766f3081d
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/__init__.py
@@ -0,0 +1,23 @@
+from mindsdb.integrations.libs.const import HANDLER_TYPE
+
+from .__about__ import __version__ as version, __description__ as description
+try:
+    from .monetdb_handler import (
+        MonetDBHandler as Handler,
+        connection_args_example,
+        connection_args
+        )
+    import_error = None
+except Exception as e:
+    Handler = None
+    import_error = e
+
+title = 'MonetDB'
+name = 'monetdb'
+type = HANDLER_TYPE.DATA
+icon_path = 'icon.png'
+
+__all__ = [
+    'Handler', 'version', 'name', 'type', 'title', 'description',
+    'connection_args', 'connection_args_example', 'import_error', 'icon_path'
+]
diff --git a/mindsdb/integrations/handlers/monetdb_handler/icon.png b/mindsdb/integrations/handlers/monetdb_handler/icon.png
new file mode 100644
index 00000000000..8e216d64b9d
Binary files /dev/null and b/mindsdb/integrations/handlers/monetdb_handler/icon.png differ
diff --git a/mindsdb/integrations/handlers/monetdb_handler/monetdb_handler.py b/mindsdb/integrations/handlers/monetdb_handler/monetdb_handler.py
new file mode 100644
index 00000000000..150fd7ea16b
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/monetdb_handler.py
@@ -0,0 +1,273 @@
+from collections import OrderedDict
+from typing import Optional
+from mindsdb_sql.parser.ast.base import ASTNode
+from mindsdb.integrations.libs.base_handler import DatabaseHandler
+from mindsdb.utilities.log import log
+from mindsdb_sql import parse_sql
+from mindsdb_sql.render.sqlalchemy_render import SqlalchemyRender
+from mindsdb.integrations.libs.response import (
+    HandlerStatusResponse as StatusResponse,
+    HandlerResponse as Response,
+    RESPONSE_TYPE
+)
+from mindsdb.integrations.libs.const import HANDLER_CONNECTION_ARG_TYPE as ARG_TYPE
+
+
+
+import pandas as pd
+import pymonetdb as mdb
+from .utils.monet_get_id import *
+from sqlalchemy_monetdb.dialect import MonetDialect 
+
+
+
+
+class MonetDBHandler(DatabaseHandler):
+
+
+    name= 'monetdb'
+
+    def __init__(self, name: str, connection_data: Optional[dict], **kwargs):
+        """ Initialize the handler
+        Args:
+            name (str): name of particular handler instance
+            connection_data (dict): parameters for connecting to the database
+            **kwargs: arbitrary keyword arguments.
+        """
+        super().__init__(name)
+        
+        self.kwargs = kwargs
+        self.parser = parse_sql
+        self.database = connection_data['database']
+        self.user = connection_data['user']
+        self.password = connection_data['password']
+        self.schemaName = connection_data['schema_name'] if 'schema_name' in connection_data else None
+        self.host = connection_data['host']
+        self.port = connection_data['port']
+        
+
+        self.connection = None
+        self.is_connected = False
+          
+    
+    def connect(self):
+        """ Set up any connections required by the handler
+        Should return output of check_connection() method after attempting
+        connection. Should switch self.is_connected.
+        Returns:
+            Connection Object
+        """
+        if self.is_connected is True:
+            return self.connection
+
+        try:
+            self.connection = mdb.connect(
+                database=self.database, 
+                hostname=self.host, 
+                port=self.port, 
+                username=self.user,
+                password=self.password,
+            )
+
+  
+            self.is_connected= True
+        except Exception as e:
+            log.error(f"Error while connecting to {self.database}, {e}")
+
+
+        return self.connection
+
+
+    def disconnect(self):
+        """ Close any existing connections
+        Should switch self.is_connected.
+        """
+        if self.is_connected is False:
+            return
+        try:
+            self.connection.close()
+            self.is_connected=False
+        except Exception as e:
+            log.error(f"Error while disconnecting to {self.database}, {e}")
+
+        return 
+
+
+    def check_connection(self) -> StatusResponse:
+        """ Check connection to the handler
+        Returns:
+            HandlerStatusResponse
+        """
+        responseCode = StatusResponse(False)
+        need_to_close = self.is_connected is False
+
+        try:
+            self.connect()
+            responseCode.success = True
+        except Exception as e:
+            log.error(f'Error connecting to database {self.database}, {e}!')
+            responseCode.error_message = str(e)
+        finally:
+            if responseCode.success is True and need_to_close:
+                self.disconnect()
+            if responseCode.success is False and self.is_connected is True:
+                self.is_connected = False
+
+        return responseCode
+
+
+    def native_query(self, query: str) -> StatusResponse:
+        """Receive raw query and act upon it somehow.
+        Args:
+            query (Any): query in native format (str for sql databases,
+                dict for mongo, etc)
+        Returns:
+            HandlerResponse
+        """
+        need_to_close = self.is_connected is False
+        conn = self.connect()
+        cur=conn.cursor() 
+        try:
+            cur.execute(query)
+            print(query , cur.rowcount,query.strip().upper().startswith('SELECT') )
+                   
+            if cur.rowcount>0  and query.strip().upper().startswith('SELECT') :
+                result = cur.fetchall() 
+                response = Response(
+                    RESPONSE_TYPE.TABLE,
+                    data_frame=pd.DataFrame(
+                        result,
+                        columns=[x[0] for x in cur.description]
+                    )
+                )
+            else:
+                response = Response(RESPONSE_TYPE.OK)
+            self.connection.commit()
+        except Exception as e:
+            log.error(f'Error running query: {query} on {self.database}!')
+            response = Response(
+                RESPONSE_TYPE.ERROR,
+                error_message=str(e)
+            )
+            self.connection.rollback()
+
+        cur.close()    
+
+        if need_to_close is True:
+            self.disconnect()
+
+        return response
+
+    
+    def query(self, query: ASTNode) -> StatusResponse:
+        """Receive query as AST (abstract syntax tree) and act upon it somehow.
+        Args:
+            query (ASTNode): sql query represented as AST. May be any kind
+                of query: SELECT, INTSERT, DELETE, etc
+        Returns:
+            HandlerResponse
+        """
+        
+
+
+        renderer = SqlalchemyRender(MonetDialect)
+        query_str = renderer.get_string(query, with_failback=True)
+        return self.native_query(query_str)
+
+
+    def get_tables(self) -> StatusResponse:
+        """ Return list of entities
+        Return list of entities that will be accesible as tables.
+        Returns:
+            HandlerResponse: shoud have same columns as information_schema.tables
+                (https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html)
+                Column 'TABLE_NAME' is mandatory, other is optional.
+        """
+        self.connect()
+        schema = schema_id(connection=self.connection,schema_name=self.schemaName)
+
+
+
+        q = f"""
+            SELECT name as TABLE_NAME
+            FROM sys.tables
+            WHERE system = False
+            AND type = 0
+            AND schema_id = {schema}
+        """
+ 
+
+        return self.query(q)
+
+    
+    def get_columns(self, table_name: str) -> StatusResponse:
+        """ Returns a list of entity columns
+        Args:
+            table_name (str): name of one of tables returned by self.get_tables()
+        Returns:
+            HandlerResponse: shoud have same columns as information_schema.columns
+                (https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html)
+                Column 'COLUMN_NAME' is mandatory, other is optional. Hightly
+                recomended to define also 'DATA_TYPE': it should be one of
+                python data types (by default it str).
+        """
+        self.connect()
+        table=table_id(
+            connection=self.connection,
+            table_name=table_name,
+            schema_name=self.schemaName
+        )
+
+        q = f"""
+            SELECT  
+            name as COLUMN_NAME,
+            type as DATA_TYPE
+            FROM sys.columns
+            WHERE table_id = {table}
+        """
+        return self.query(q)
+
+        
+
+
+
+
+
+connection_args = OrderedDict(
+    host={
+        'type': ARG_TYPE.STR,
+        'description': 'The host name or IP address of the MonetDB server/database.'
+    },
+    database={
+        'type': ARG_TYPE.STR,
+        'description': """
+            The database name to use when connecting with the MonetDB server.
+        """
+    },
+    user={
+        'type': ARG_TYPE.STR,
+        'description': 'The user name used to authenticate with the MonetDB server.'
+    },
+    password={
+        'type': ARG_TYPE.STR,
+        'description': 'The password to authenticate the user with the MonetDB server.'
+    },
+    port={
+        'type': ARG_TYPE.INT,
+        'description': 'Specify port to connect MonetDB through TCP/IP'
+    },
+    schema_name={
+        'type': ARG_TYPE.STR,
+        'description': 'Specify the schema name for Listing Table '
+    },
+
+)
+
+connection_args_example = OrderedDict(
+    host='127.0.0.1',
+    port=50000,
+    password='monetdb',
+    user='monetdb',
+    schemaName="sys",
+    database="demo",
+)
diff --git a/mindsdb/integrations/handlers/monetdb_handler/requirements.txt b/mindsdb/integrations/handlers/monetdb_handler/requirements.txt
new file mode 100644
index 00000000000..89834499eff
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/requirements.txt
@@ -0,0 +1,2 @@
+pymonetdb
+sqlalchemy-monetdb
\ No newline at end of file
diff --git a/mindsdb/integrations/handlers/monetdb_handler/tests/__init__.py b/mindsdb/integrations/handlers/monetdb_handler/tests/__init__.py
new file mode 100644
index 00000000000..e69de29bb2d
diff --git a/mindsdb/integrations/handlers/monetdb_handler/tests/test_monetdb_handler.py b/mindsdb/integrations/handlers/monetdb_handler/tests/test_monetdb_handler.py
new file mode 100644
index 00000000000..19ed85174b5
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/tests/test_monetdb_handler.py
@@ -0,0 +1,52 @@
+import unittest
+from mindsdb.integrations.handlers.monetdb_handler.monetdb_handler import MonetDBHandler
+from mindsdb.api.mysql.mysql_proxy.libs.constants.response_type import RESPONSE_TYPE
+
+
+class MonetDBHandlerTest(unittest.TestCase):
+    @classmethod
+    def setUpClass(cls):
+        cls.kwargs = {
+            "host": "127.0.0.1",
+            "port": 50000,
+            "user": "monetdb",
+            "password": "monetdb",
+            "database": "demo",
+            
+        }
+        cls.handler = MonetDBHandler('test_monet_handler', cls.kwargs)
+
+    def test_0_connect(self):
+         self.handler.connect()
+
+    
+
+    def test_1_drop_table(self):
+        res = self.handler.query("DROP TABLE IF EXISTS PREM;")
+        assert res.type is  not RESPONSE_TYPE.ERROR 
+
+
+    def test_2_create_table(self):
+        res = self.handler.query("CREATE TABLE IF NOT EXISTS PREM (Premi varchar(50));")
+        assert res.type is not  RESPONSE_TYPE.ERROR 
+
+    def test_3_insert_table(self):
+        res = self.handler.query("INSERT INTO PREM VALUES('Radha <3 Krishna');")
+        assert res.type is not  RESPONSE_TYPE.ERROR 
+    
+
+    def test_4_get_tables(self):
+        tables = self.handler.get_tables()
+        assert tables.type is not  RESPONSE_TYPE.ERROR
+
+ 
+    def test_5_select_query(self):
+        query = "SELECT * FROM PREM;"
+        result = self.handler.native_query(query)
+        assert result.type is RESPONSE_TYPE.TABLE or RESPONSE_TYPE.OK
+
+    def test_6_check_connection(self):
+         self.handler.check_connection()
+
+if __name__ == '__main__':
+    unittest.main()
\ No newline at end of file
diff --git a/mindsdb/integrations/handlers/monetdb_handler/utils/__init__.py b/mindsdb/integrations/handlers/monetdb_handler/utils/__init__.py
new file mode 100644
index 00000000000..e69de29bb2d
diff --git a/mindsdb/integrations/handlers/monetdb_handler/utils/monet_get_id.py b/mindsdb/integrations/handlers/monetdb_handler/utils/monet_get_id.py
new file mode 100644
index 00000000000..e03f0847bd6
--- /dev/null
+++ b/mindsdb/integrations/handlers/monetdb_handler/utils/monet_get_id.py
@@ -0,0 +1,50 @@
+from sqlalchemy import  exc
+
+def schema_id(connection, schema_name=None):
+    """Fetch the id for schema"""
+    cur=connection.cursor()
+    if schema_name is None:
+        cur.execute("SELECT current_schema")
+        schema_name=cur.fetchall()[0][0]
+
+    query = f"""
+                SELECT id
+                FROM sys.schemas
+                WHERE name = '{schema_name}'
+            """
+    
+    cur.execute(query)
+
+    try:
+        schema_id = cur.fetchall()[0][0]
+    except:
+        raise exc.InvalidRequestError(schema_name)
+        
+    return schema_id
+
+    
+def table_id(connection, table_name, schema_name=None):
+    """Fetch the id for schema.table_name, defaulting to current schema if
+       schema is None
+    """
+
+    schema_idm = schema_id(connection=connection,schema_name=schema_name)
+
+    q = f"""
+        SELECT id
+        FROM sys.tables
+        WHERE name = '{table_name}'
+        AND schema_id = {schema_idm}
+        """
+    
+    cur = connection.cursor()
+    cur.execute(q)
+
+    try:
+        table_id = cur.fetchall()[0][0] 
+    except:
+        raise exc.NoSuchTableError(table_name)
+
+    
+
+    return table_id


Code review comments:
diff_hunk: @@ -0,0 +1,273 @@
+from collections import OrderedDict
+from typing import Optional
+from mindsdb_sql.parser.ast.base import ASTNode
+from mindsdb.integrations.libs.base_handler import DatabaseHandler
+from mindsdb.utilities.log import log
+from mindsdb_sql import parse_sql
+from mindsdb_sql.render.sqlalchemy_render import SqlalchemyRender
+from mindsdb.integrations.libs.response import (
+    HandlerStatusResponse as StatusResponse,
+    HandlerResponse as Response,
+    RESPONSE_TYPE
+)
+from mindsdb.integrations.libs.const import HANDLER_CONNECTION_ARG_TYPE as ARG_TYPE
+
+
+
+import pandas as pd
+import pymonetdb as mdb
+from .utils.monet_get_id import *
+from sqlalchemy_monetdb.dialect import MonetDialect 
+
+
+
+
+class MonetDBHandler(DatabaseHandler):
+
+
+    name= 'monetdb'
+
+    def __init__(self, name: str, connection_data: Optional[dict], **kwargs):
+        """ Initialize the handler
+        Args:
+            name (str): name of particular handler instance
+            connection_data (dict): parameters for connecting to the database
+            **kwargs: arbitrary keyword arguments.
+        """
+        super().__init__(name)
+        
+        self.kwargs = kwargs
+        self.parser = parse_sql
+        self.database = connection_data['database']
+        self.user = connection_data['user']
+        self.password = connection_data['password']
+        self.schemaName = connection_data['schema_name'] if 'schema_name' in connection_data else None
+        self.host = connection_data['host']
+        self.port = connection_data['port']
+        
+
+        self.connection = None
+        self.is_connected = False
+          
+    
+    def connect(self):
+        """ Set up any connections required by the handler
+        Should return output of check_connection() method after attempting
+        connection. Should switch self.is_connected.
+        Returns:
+            Connection Object
+        """
+        if self.is_connected is True:
+            return self.connection
+
+        try:
+            self.connection = mdb.connect(
+                database=self.database, 
+                hostname=self.host, 
+                port=self.port, 
+                username=self.user,
+                password=self.password,
+            )
+
+  
+            self.is_connected= True
+        except Exception as e:
+            log.error(f"Error while connecting to {self.database}, {e}")
+
+
+        return self.connection
+
+
+    def disconnect(self):
+        """ Close any existing connections
+        Should switch self.is_connected.
+        """
+        if self.is_connected is False:
+            return
+        try:
+            self.connection.close()
+            self.is_connected=False
+        except Exception as e:
+            log.error(f"Error while disconnecting to {self.database}, {e}")
+
+        return 
+
+
+    def check_connection(self) -> StatusResponse:
+        """ Check connection to the handler
+        Returns:
+            HandlerStatusResponse
+        """
+        responseCode = StatusResponse(False)
+        need_to_close = self.is_connected is False
+
+        try:
+            self.connect()
+            responseCode.success = True
+        except Exception as e:
+            log.error(f'Error connecting to database {self.database}, {e}!')
+            responseCode.error_message = str(e)
+        finally:
+            if responseCode.success is True and need_to_close:
+                self.disconnect()
+            if responseCode.success is False and self.is_connected is True:
+                self.is_connected = False
+
+        return responseCode
+
+
+    def native_query(self, query: str) -> StatusResponse:
+        """Receive raw query and act upon it somehow.
+        Args:
+            query (Any): query in native format (str for sql databases,
+                dict for mongo, etc)
+        Returns:
+            HandlerResponse
+        """
+        need_to_close = self.is_connected is False
+        conn = self.connect()
+        cur=conn.cursor() 
+        try:
+            cur.execute(query)
+            print(query , cur.rowcount,query.strip().upper().startswith('SELECT') )
path: mindsdb/integrations/handlers/monetdb_handler/monetdb_handler.py
text: @user1:
Please remove print