Skip to content

INTEGRATION_GUIDE.md

Latest commit

 

History

History
303 lines (222 loc) · 7.32 KB

INTEGRATION_GUIDE.md

File metadata and controls

303 lines (222 loc) · 7.32 KB

✅ Query History Integration — Complete Implementation Guide

What Was Done

1. Backend Changes ✅

/backend/main.py

  • Added root-level GET /history endpoint
  • This returns recent query history (latest first)
  • Imports query_history deque from execute.py

/backend/api/execute.py (Already Implemented)

  • Tracks all executed queries in a thread-safe deque (max 50 entries)
  • Records: query, source, mode, time, timestamp, cached flag
  • Every call to /api/execute auto-appends to history
  • API endpoint: GET /api/history (available at /api/history with prefix)

2. Frontend Changes ✅

/frontend/src/pages/QueryPlan.tsx (Rewritten)

  • Sidebar: Shows recent queries with metadata (source, execution time, cache status)
  • Main Panel: Query editor with source selector (DuckDB/PostgreSQL/MySQL)
  • Features:
    • "Analyze Plan" button → parses and visualizes query plan
    • "Execute" button → runs query and fetches plan automatically
    • Shows plan explanation text
    • Displays plan tree as JSON
    • Renders interactive graph visualization with ReactFlow
    • Hover effects on history items
    • Error handling with user-friendly messages
    • Active history item highlighting
    • History auto-refresh every 5 seconds
    • Clear history button

How to Use

Start Backend

cd /Users/nehadamani/Argus
source .venv/bin/activate
python -m uvicorn backend.main:app --reload --port 8093

Start Frontend

cd frontend
npm run dev

Access the App

  1. Main App (side-by-side exact vs approx):
    http://localhost:5173

  2. Plan Analyzer Page (NEW!):
    Currently embedded in the app — to use as standalone page, you need routing setup

Testing the Integration

Test 1: Check History Endpoint

curl http://127.0.0.1:8093/history

Should return [] initially, then populate after queries are executed.

Test 2: Execute a Query

  1. Open the app at http://localhost:5173
  2. Write a query: SELECT COUNT(*) FROM table_abc;
  3. Click "Run Query" in the main panel

Test 3: Check History Populated

curl http://127.0.0.1:8093/history

Should now show:

[
  {
    "query": "SELECT COUNT(*) FROM table_abc;",
    "source": "duckdb",
    "mode": "exact",
    "time": 0.012345,
    "timestamp": 1711800000.0,
    "cached": false,
    "cache_key": "abc123..."
  }
]

API Endpoints Reference

GET /history

Returns recent query history (latest first)

Response:

[
  {
    "query": "SELECT * FROM table LIMIT 10",
    "source": "duckdb",
    "mode": "exact",
    "time": 0.053456,
    "result_rows": 10,
    "timestamp": 1711800000.0,
    "cached": false
  }
]

Sidebar Features in QueryPlan Page

Feature Description
Recent Queries List Click any query to auto-load and analyze
Query Metadata Shows source, execution time, and cache status
Active Highlight Currently selected query is highlighted in blue
Auto Refresh History updates every 5 seconds
Clear Button ✕ button clears all history (with confirmation)
Hover Effects Visual feedback when hovering over queries

Frontend Components Used

  • QueryPlanPage (pages/QueryPlan.tsx): Main page layout
  • PlanGraph (components/PlanGraph.tsx): ReactFlow visualization
  • planToFlow (utils/planToFlow.ts): Tree-to-graph converter

Data Flow

User types query in QueryPlan page
        ↓
User clicks "Analyze Plan"
        ↓
Frontend POST /api/sql/parse-plan
        ↓
Backend: route_query() → execute query
        ↓
Backend: append_history() → save to deque  ← KEY STEP
        ↓
Backend returns plan_tree
        ↓
Frontend: setPlan() → displays tree
        ↓
ReactFlow: renders visualization
        ↓
Sidebar: shows query in history list (refreshes every 5s)

Next Steps (Optional Upgrades)

1. Add Routing to App

Currently, QueryPlan.tsx is not routed. To make it a separate page:

npm install react-router-dom

Then in main.tsx:

import { BrowserRouter, Routes, Route } from "react-router-dom";
import App from "./App";
import QueryPlanPage from "./pages/QueryPlan";

ReactDOM.createRoot(document.getElementById("root")!).render(
  <BrowserRouter>
    <Routes>
      <Route path="/" element={<App />} />
      <Route path="/plan" element={<QueryPlanPage />} />
    </Routes>
  </BrowserRouter>,
);

2. Add Navbar to Switch Between Pages

<nav style={{ padding: "10px", background: "#1a1a1a" }}>
  <Link to="/">Executor</Link>
  <Link to="/plan">Plan Analyzer</Link>
</nav>

3. Persist History to LocalStorage

Add to QueryPlanPage.tsx:

// Save history to localStorage
useEffect(() => {
  localStorage.setItem("queryHistory", JSON.stringify(history));
}, [history]);

// Load from localStorage on mount
useEffect(() => {
  const saved = localStorage.getItem("queryHistory");
  if (saved) setHistory(JSON.parse(saved));
}, []);

4. Add Query Statistics

Track in backend and display in sidebar:

  • Total queries
  • Average execution time
  • Most frequently used source
  • Slow queries (>1s)

5. Export History as JSON

const exportHistory = () => {
  const json = JSON.stringify(history, null, 2);
  const blob = new Blob([json], { type: "application/json" });
  const url = URL.createObjectURL(blob);
  const a = document.createElement("a");
  a.href = url;
  a.download = "query-history.json";
  a.click();
};

Troubleshooting

❌ History not showing

  • Verify backend is running: curl http://127.0.0.1:8093/
  • Check history endpoint: curl http://127.0.0.1:8093/history
  • Check browser console for fetch errors

❌ Plan visualization is empty

  • Verify query is valid SQL for your database
  • Check browser console for errors
  • Try simple query: SELECT 1

❌ CORS errors

  • Backend already has CORS middleware configured
  • If still issues, restart backend server

❌ QueryPlan page not accessible

  • It's a component, not routed yet
  • To use standalone, set up react-router-dom (see upgrade #1)

File Changes Summary

File Change Status
backend/main.py Added /history endpoint ✅ Done
backend/api/execute.py Already had history tracking ✅ Already working
frontend/src/pages/QueryPlan.tsx Complete rewrite ✅ Done
frontend/src/App.tsx No changes needed ✅ OK
frontend/src/components/PlanGraph.tsx No changes ✅ OK

Summary

✅ What You Can Do Now:

  1. Run queries in the main app
  2. View all query history at /history endpoint
  3. Use QueryPlan page to analyze individual queries
  4. See execution plans visualized as graphs
  5. Click history items to quickly re-run queries

🎯 Next Priority: Set up routing if you want QueryPlan as a separate page in your UI navigation.