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: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.
-
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 copilot.stop
and./gradlew copilot.start
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 usedocker 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.
Can you execute the sum of the orders in the last month of 2016?