dbPrompt/GEMINI.md

Gemini Project Analysis: dbPrompt

Overview

The dbPrompt project is a web-based application that provides a user interface for executing SQL queries against a MySQL database. It keeps a history of all executed queries and their results, which are displayed on the main page.

Project Structure

• dbPrompt.go: The main application file, written in Go. It contains the web server and all the backend logic.
• index.html: A single HTML file that contains the entire frontend UI, including CSS and JavaScript.
• go.mod / go.sum: Go module files that define the project's dependencies. The only external dependency is the MySQL driver for Go.
• config.json: A configuration file for the database connection details (hostname, username, password, etc.).
• history/: A directory where the history of executed queries is stored as JSON files.
• README.md: The project's README file.
• LICENSE: The project's license file.

Backend (Go)

The backend is a simple web server built with the standard net/http package in Go.

• Database Connection: It connects to a MySQL database using the go-sql-driver/mysql driver. Connection parameters are read from config.json.
• Endpoints:
  • GET /: Serves the index.html file.
  • POST /query: Receives a SQL query from the frontend, executes it on the database, and returns the result as JSON. It handles both queries that return rows (e.g., SELECT) and statements that modify data (e.g., INSERT, UPDATE). Each executed query and its result are saved to a JSON file in the history/ directory.
  • DELETE /history/{id}: Deletes a specific query history item (JSON file) based on its ID from the history/ directory.
  • GET /history/: Reads all query history files from the history/ directory, sorts them chronologically, and returns them as a single JSON array.
• Query Execution Metadata: The backend now captures and includes the duration (in milliseconds) of each query execution in the QueryResult object before saving it to history and sending it to the frontend.

Frontend (HTML, CSS, JavaScript)

The frontend is a single-page application contained entirely within index.html.

• User Interface: The UI is composed of "query blocks". Each block contains a textarea for writing a SQL query, a "Run Query" button, and a result area.
• History: On page load, the application fetches the query history from the /history/ endpoint and populates the page with query blocks for each historical query. One empty query block is always present for entering new queries.
• Interaction:
  • Users can type a SQL query into a textarea and click "Run Query".
  • The JavaScript code sends the query to the /query endpoint.
  • The result (a table of data, a message with rows affected, or an error) is displayed in the result area of the corresponding query block.
  • Successfully running a query in the "new query" block will automatically add a new empty block below it and equip the executed block with a "Delete" button.
• Delete Functionality:
  • Historical query blocks now include a "Delete" button.
  • When clicked, a confirmation dialog appears. If confirmed, a DELETE request is sent to the backend (/history/{id}), and the block is removed from the UI.
  • The designated "new query" block (for new input) does not initially have a delete button.
• Query Result Collapsibility:
  • Tabular query results are now wrapped in a collapsible section, allowing users to expand and collapse them.
  • "Collapse All" and "Expand All" buttons have been added to the header, enabling users to manage the visibility of all result tables simultaneously.
  • By default, all result tables are expanded.
• Query Execution Metadata Display: Each query result now displays the exact date and time of execution and its processing duration (in milliseconds).

How it Works

1. The user opens the web page, and the browser loads index.html.
2. The JavaScript in index.html makes a GET request to /history/ to load past queries.
3. The Go backend reads the JSON files from the history/ directory and returns them.
4. The frontend dynamically creates a "query block" for each past query (with a delete button) and one new empty block (without a delete button).
5. The user enters a new SQL query into the designated "new query" block and clicks "Run Query".
6. The frontend sends a POST request to /query with the query string.
7. The Go backend executes the query against the MySQL database, measures its duration, and saves the query, result, timestamp, and duration to a new JSON file in the history/ directory.
8. The backend returns the result (including duration) to the frontend.
9. The frontend displays the result in the corresponding query block, along with the execution date/time and duration. If it was a "new query" block, it gets a delete button, and a new empty "new query" block is added below it.
10. Users can click the "Delete" button on historical queries to remove them.
11. Users can collapse/expand individual result tables or use the global "Collapse All" / "Expand All" buttons.