SQL Expert

Javapackage: com.etendoerp.copilot.dbquerytool

Overview

This Assistant is designed to help users read information from the database. It allows users to ask questions in natural language and get the SQL query that retrieves the information they need. The tool uses the DB Query Tool to infer the necessary information to generate the SQL query. The user can ask questions about the database, and the Assistant will determine whether to return a SQL query or execute the query and return the obtained result.

Components

The SQL Expert Assistant is composed of the following components:

DBQueryGenerator Tool: This tool allows querying Etendo for available tables in the database and the columns of each table. It also enables the Assistant to execute SQL queries in the database that are generated by the Assistant.
DBQueryExec Webhook: This Webhook allows the Assistant to execute SQL queries in the database. The DBQueryGenerator tool sends the SQL query to the Webhook, and the Webhook executes the query in the database and returns the result to the Assistant. This is a secure way to execute SQL queries in the database, as Etendo handles the security of the database connection and query execution.

Installation

This module is included in the Copilot Extensions Bundle

Info

To be able to include this assistant, the Copilot Extensions Bundle must be installed. To do that, follow the instructions from the marketplace: Copilot Extensions Bundle. For more information about the available versions, core compatibility and new features, visit Copilot Extensions - Release notes.

In addition, you can install only the module containing the SQL Expert by following the guide on How to install modules in Etendo, looking for the GitHub Package com.etendoerp.copilot.dbquerytool.

After install the module:

It is necessary to add the gradle.properties file with the following configuration:
```
ETENDO_HOST=http://localhost:8080/etendo
```
Warning

Replace http://localhost:8080/etendo with the real url of the Etendo system.
Do a compilation with a database update, to register the tool and the assistant in the database.
Terminal
```
./gradlew update.database smartbuild --info
```
Restart Tomcat and check that a new assistant named SQL Expert has been created in the Application > Service > Copilot > Assistant window.
Run Sync Assistant process.
Restart Docker image using ./gradlew resources.down and ./gradlew resources.up tasks.

Note

If you have problems with shutting down the container, you can use the docker ps command to see the container id and then use docker stop <container_id> to stop it manually.
You can use the SQL Expert assistant as a System Administrator. In case of using this assistant with other roles, first, you need to configure the coplilot app access in the Role window as described in the initial assistant configuration and also configure access to the role in the WebHooks window for the WebHook DBQueryExec, this webhook is automatically added when the module is installed.

Examples

Examples of questions for information retrieval

What is the amount of the last F&B España - Región Sur organization sales invoice?
Can you tell me which is the invoice with the highest registered amount?

3.

Examples of SQL query generation

Can you execute the sum of the orders in the last month?
Query to know the name of the best 5 customer of junuary 2011?
Query to obtain which is the invoice with the highest registered amount?