Can it be used on non frappe database?

[reach2rv]

Hi, as this has support for both MySQL and PostgreSql. Can it be directly connected to any of these db type which are not used for any frappe app.

[nextchamp-saqib]

Yes it can be. Reopen the issue if you cannot connect with any non-frappe database

[reach2rv]

Hi @nextchamp-saqib - I tried it with postgres database unfortunately it only loads tables from public schema whereas my db has multiple schemas.

Request you to please look into it.

[reach2rv]

Hi @nextchamp-saqib - I tried changing get_db_tables method from postgresql.py in sources.

def get_db_tables(self, table_names=None):
        inspector = inspect(self.db_conn)
        
        # Fetch tables from all schemas
        all_schemas = set(inspector.get_schema_names())
        all_tables = set()
        for schema in all_schemas:
            # Set the search path to the current schema
            self.db_conn.execute(text(f'SET search_path TO {schema}'))
            
            # Fetch tables for the current schema
            schema_tables = inspector.get_table_names(schema=schema)
            all_tables.update(schema_tables)
        
        if table_names:
            all_tables = [table for table in all_tables if table in table_names]
        
        return [self.get_table(table) for table in all_tables]

the only issue remains is it gets only 100 tables. it would be great if you can look into it.

Thanks you

[reach2rv]

update. it returns all tables, grid does not have pagination and therefore I can only see first 100 tables. please add pagination to the grid.

[nextchamp-saqib]

@reach2rv Does query builder work after making the change?

[reach2rv]

@reach2rv Does query builder work after making the change?

Haven't checked as I was not able to see desired tables. Let me check if I can see all tables in query builder and update.

[reach2rv]

@nextchamp-saqib - I get to select all tables in query builder but visual query builder fails to execute query.

[nextchamp-saqib]

Can you post the error here please. Check the browser console for the error

[reach2rv]

Traceback (most recent call last):
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.SyntaxError: syntax error at or near "."
LINE 1: WITH limited AS (SELECT `production.wip_movement`.* 
                                                         ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "apps/insights/insights/insights/doctype/insights_data_source/sources/utils.py", line 265, in execute_and_log
    result = conn.exec_driver_sql(sql)
             ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1778, in exec_driver_sql
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "."
LINE 1: WITH limited AS (SELECT `production.wip_movement`.* 
                                                         ^

[SQL: WITH limited AS (SELECT `production.wip_movement`.* 
FROM "production.wip_movement" AS "production.wip_movement" 
 LIMIT 100) SELECT * FROM limited LIMIT 1000;]
(Background on this error at: https://sqlalche.me/e/20/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 110, in application
    response = frappe.api.handle(request)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
           ^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
           ^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/__init__.py", line 1796, in call
    return fn(*args, **newargs)
           ^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 326, in run_doc_method
    response = doc.run_method(method)
               ^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/model/document.py", line 959, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/model/document.py", line 1319, in composer
    return composed(self, method, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/model/document.py", line 1301, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
                              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/model/document.py", line 956, in fn
    return method_object(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "apps/insights/insights/insights/doctype/insights_query/insights_query_client.py", line 61, in run
    self.fetch_results()
  File "apps/insights/insights/insights/doctype/insights_query/insights_query.py", line 189, in fetch_results
    self._results = self.variant_controller.fetch_results(additional_filters)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/insights/insights/insights/doctype/insights_query/insights_assisted_query.py", line 154, in fetch_results
    return InsightsDataSource.get_doc(self.doc.data_source).run_query(query)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/insights/insights/insights/doctype/insights_data_source/insights_data_source.py", line 163, in run_query
    return self._db.run_query(query)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/insights/insights/insights/doctype/insights_data_source/sources/base_database.py", line 74, in run_query
    return self.execute_query(sql, return_columns=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/insights/insights/insights/doctype/insights_data_source/sources/base_database.py", line 102, in execute_query
    res = execute_and_log(connection, sql, self.data_source)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/insights/insights/insights/doctype/insights_data_source/sources/utils.py", line 267, in execute_and_log
    handle_query_execution_error(e)
  File "apps/insights/insights/insights/doctype/insights_data_source/sources/utils.py", line 277, in handle_query_execution_error
    frappe.throw(
  File "apps/frappe/frappe/__init__.py", line 678, in throw
    msgprint(
  File "apps/frappe/frappe/__init__.py", line 643, in msgprint
    _raise_exception()
  File "apps/frappe/frappe/__init__.py", line 594, in _raise_exception
    raise exc
frappe.exceptions.ValidationError: Syntax error in the query. Please check the browser console for more details.

[reach2rv]

Here is the updated code to save schema name with table to avoid missing schema in query

def get_db_tables(self, table_names=None):
        inspector = inspect(self.db_conn)
        
        # Fetch tables from all schemas
        all_schemas = set(inspector.get_schema_names())
        all_tables = set()
        for schema in all_schemas:
            # Set the search path to the current schema
            self.db_conn.execute(text(f'SET search_path TO {schema}'))
            
            # Fetch tables for the current schema
            schema_tables = inspector.get_table_names(schema=schema)
            all_tables.update({f'{schema}.{table}' for table in schema_tables})
        
        if table_names:
            # Filter tables based on the provided table_names
            all_tables = [table for table in all_tables if table in table_names]
        
        return [self.get_table(table) for table in all_tables]

[reach2rv]

Hi @nextchamp-saqib - by any chance have you been able to take a look at it?

[nextchamp-saqib]

It will require a significant effort to support multiple schemas in a single data source.

However, I have a potential solution in mind. I can add a schema field in the database connection form. This way, each data source can support one schema. In this case, you would need to create multiple data sources with the same credentials but different schemas.

[reach2rv]

@nextchamp-saqib thats sounds good, though I have one question what if we need to join data between two tables from different schema? will it be possible

[reach2rv]

@nextchamp-saqib - as I totally understand it was built for frappe products. can you please guide which modules need changes to support this requirement so I can try something at my end as well

[gavindsouza]

https://github.com/frappe/insights/issues/171#issuecomment-1972929272 should be addressed by #234

[reach2rv]

@gavindsouza - Thank you! It doesn't seem to be working. I have pulled your repo and fired up docker. it is still only loading public schema tables.

[gavindsouza]

We came to face with a similar-looking traceback when we plugged our old postgres database with Insights - the error I'm referring to fixing is related to query building (the quotes used), not specific to the lack of support for schemas in the visual builder. Perhaps my comment was ambiguous - partly addressed might be more apt, unless it doesn't change anything at all! (even a different class of error)

[reach2rv]

Oh! I get it my bad

I guess I will try your changes with the change I have made to see if I am able to run query. I am still new to python.

[reach2rv]

@nextchamp-saqib - will this get addressed in v3?