| import os |
| import requests |
| from fastapi import FastAPI, HTTPException, Query |
| from fastapi.responses import HTMLResponse |
| from pydantic import BaseModel |
|
|
| app = FastAPI(title="SQLite Multi-Project on HF Space") |
|
|
| SQLITE_URL = "http://localhost:8000" |
|
|
| class QueryRequest(BaseModel): |
| sql: str |
| project: str = "default" |
|
|
| class ExecuteRequest(BaseModel): |
| sql: str |
| project: str = "default" |
|
|
| class ProjectRequest(BaseModel): |
| name: str |
| description: str = "" |
|
|
| @app.get("/", response_class=HTMLResponse) |
| def read_root(): |
| return """ |
| <html> |
| <head> |
| <title>SQLite Multi-Project Server</title> |
| <style> |
| body { font-family: Arial; margin: 40px; background: #f5f5f5; } |
| h1 { color: #333; } |
| .info { background: white; padding: 20px; border-radius: 8px; margin: 10px 0; } |
| code { background: #e0e0e0; padding: 2px 6px; border-radius: 3px; } |
| ul { line-height: 1.8; } |
| </style> |
| </head> |
| <body> |
| <h1>✅ SQLite Multi-Project Server</h1> |
| <div class="info"> |
| <p><strong>Persistent Storage:</strong> /data/ (organized by project)</p> |
| <p><strong>API Docs:</strong> <a href="/docs">Swagger UI</a></p> |
| </div> |
| |
| <h2>Project Management</h2> |
| <ul> |
| <li><code>GET /api/v1/projects</code> - List all projects</li> |
| <li><code>POST /api/v1/projects</code> - Create new project</li> |
| <li><code>DELETE /api/v1/projects/{name}</code> - Delete project</li> |
| </ul> |
| |
| <h2>Database Operations</h2> |
| <ul> |
| <li><code>GET /api/v1/tables?project=myproject</code> - List tables</li> |
| <li><code>GET /api/v1/schema?project=myproject</code> - Get schema</li> |
| <li><code>POST /api/v1/query</code> - Execute SELECT</li> |
| <li><code>POST /api/v1/execute</code> - Execute INSERT/UPDATE/DELETE</li> |
| </ul> |
| |
| <h2>Example: Multiple Projects</h2> |
| <pre> |
| 📁 /data/ |
| ├── project1.db (users, orders tables) |
| ├── project2.db (products, inventory tables) |
| └── project3.db (logs, analytics tables) |
| </pre> |
| </body> |
| </html> |
| """ |
|
|
| @app.get("/portal", response_class=HTMLResponse) |
| def portal(): |
| return """ |
| <!doctype html> |
| <html lang="en"> |
| <head> |
| <meta charset="utf-8" /> |
| <meta name="viewport" content="width=device-width, initial-scale=1" /> |
| <title>SQLite Portal</title> |
| <style> |
| body { |
| font-family: Arial, sans-serif; |
| margin: 24px; |
| color: #1f2328; |
| background: #f6f8fa; |
| } |
| h1 { margin: 0 0 8px; } |
| .meta { color: #57606a; margin-bottom: 16px; } |
| .grid { |
| display: grid; |
| gap: 16px; |
| grid-template-columns: 280px 1fr; |
| } |
| section { |
| background: #ffffff; |
| border: 1px solid #d0d7de; |
| border-radius: 8px; |
| padding: 16px; |
| } |
| textarea { |
| width: 100%; |
| min-height: 160px; |
| font-family: ui-monospace, SFMono-Regular, Menlo, Monaco, Consolas, |
| "Liberation Mono", "Courier New", monospace; |
| border: 1px solid #d0d7de; |
| border-radius: 6px; |
| padding: 8px; |
| box-sizing: border-box; |
| } |
| button { |
| background: #0969da; |
| color: #ffffff; |
| border: none; |
| border-radius: 6px; |
| padding: 8px 12px; |
| cursor: pointer; |
| } |
| button.secondary { background: #6e7781; } |
| #tables { list-style: none; padding: 0; margin: 12px 0 0; } |
| #tables li { padding: 6px 8px; border-bottom: 1px solid #d8dee4; } |
| #tables li:last-child { border-bottom: none; } |
| .actions { display: flex; gap: 8px; margin-top: 8px; } |
| #status { margin-top: 12px; color: #57606a; white-space: pre-wrap; } |
| table { width: 100%; border-collapse: collapse; margin-top: 12px; } |
| th, td { |
| border: 1px solid #d0d7de; |
| padding: 6px 8px; |
| text-align: left; |
| vertical-align: top; |
| font-size: 14px; |
| } |
| th { background: #f6f8fa; } |
| pre { |
| background: #f6f8fa; |
| border: 1px solid #d0d7de; |
| border-radius: 6px; |
| padding: 12px; |
| overflow: auto; |
| } |
| @media (max-width: 900px) { |
| .grid { grid-template-columns: 1fr; } |
| } |
| </style> |
| </head> |
| <body> |
| <h1>SQLite Portal</h1> |
| <div class="meta" id="meta">Loading database info...</div> |
| <div class="grid"> |
| <section> |
| <h2>Projects</h2> |
| <select id="project"></select> |
| <div class="actions"> |
| <button id="refresh">Refresh</button> |
| </div> |
| <h3>Tables</h3> |
| <ul id="tables"></ul> |
| </section> |
| <section> |
| <h2>SQL Editor</h2> |
| <textarea id="sql">SELECT * FROM products LIMIT 50;</textarea> |
| <div class="actions"> |
| <button id="run">Run</button> |
| <button id="clear" class="secondary">Clear Output</button> |
| </div> |
| <div id="status"></div> |
| <div id="results"></div> |
| </section> |
| </div> |
| |
| <script> |
| const projectEl = document.getElementById("project"); |
| const tablesEl = document.getElementById("tables"); |
| const metaEl = document.getElementById("meta"); |
| const statusEl = document.getElementById("status"); |
| const resultsEl = document.getElementById("results"); |
| const sqlEl = document.getElementById("sql"); |
| |
| function setStatus(message) { |
| statusEl.textContent = message || ""; |
| } |
| |
| function clearResults() { |
| resultsEl.innerHTML = ""; |
| setStatus(""); |
| } |
| |
| async function fetchJson(url, options) { |
| const response = await fetch(url, options); |
| const data = await response.json(); |
| if (!response.ok) { |
| const errorMessage = data.detail || data.error || data.message || response.statusText; |
| throw new Error(errorMessage); |
| } |
| return data; |
| } |
| |
| function renderTable(rows) { |
| if (!Array.isArray(rows) || rows.length === 0) { |
| resultsEl.innerHTML = "<em>No rows returned.</em>"; |
| return; |
| } |
| const columns = Object.keys(rows[0]); |
| const table = document.createElement("table"); |
| const thead = document.createElement("thead"); |
| const headerRow = document.createElement("tr"); |
| columns.forEach((col) => { |
| const th = document.createElement("th"); |
| th.textContent = col; |
| headerRow.appendChild(th); |
| }); |
| thead.appendChild(headerRow); |
| table.appendChild(thead); |
| |
| const tbody = document.createElement("tbody"); |
| rows.forEach((row) => { |
| const tr = document.createElement("tr"); |
| columns.forEach((col) => { |
| const td = document.createElement("td"); |
| const value = row[col] === null || row[col] === undefined ? "" : row[col]; |
| td.textContent = value; |
| tr.appendChild(td); |
| }); |
| tbody.appendChild(tr); |
| }); |
| table.appendChild(tbody); |
| resultsEl.innerHTML = ""; |
| resultsEl.appendChild(table); |
| } |
| |
| function currentProject() { |
| return projectEl.value || "default"; |
| } |
| |
| async function loadInfo() { |
| try { |
| metaEl.textContent = `Server: ${window.location.origin} | Project: ${currentProject()}`; |
| } catch (err) { |
| metaEl.textContent = `Failed to load info: ${err.message}`; |
| } |
| } |
| |
| async function loadProjects() { |
| projectEl.innerHTML = ""; |
| try { |
| const data = await fetchJson("/api/v1/projects"); |
| const projects = Array.isArray(data) |
| ? data |
| : data.projects || data.data || []; |
| const names = projects |
| .map((p) => (typeof p === "string" ? p : p.name)) |
| .filter(Boolean); |
| const uniqueNames = names.length ? names : ["default"]; |
| uniqueNames.forEach((name) => { |
| const option = document.createElement("option"); |
| option.value = name; |
| option.textContent = name; |
| projectEl.appendChild(option); |
| }); |
| } catch (err) { |
| const option = document.createElement("option"); |
| option.value = "default"; |
| option.textContent = "default"; |
| projectEl.appendChild(option); |
| setStatus(`Failed to load projects: ${err.message}`); |
| } |
| } |
| |
| async function loadTables() { |
| tablesEl.innerHTML = ""; |
| try { |
| const project = currentProject(); |
| const data = await fetchJson(`/api/v1/tables?project=${project}`); |
| const tables = data.tables || data.data || []; |
| const names = tables |
| .map((t) => (typeof t === "string" ? t : t.name)) |
| .filter(Boolean); |
| if (!names.length) { |
| tablesEl.innerHTML = "<li>No tables found.</li>"; |
| return; |
| } |
| names.forEach((name) => { |
| const li = document.createElement("li"); |
| li.textContent = name; |
| li.addEventListener("click", () => { |
| sqlEl.value = `SELECT * FROM ${name} LIMIT 50;`; |
| }); |
| tablesEl.appendChild(li); |
| }); |
| } catch (err) { |
| tablesEl.innerHTML = `<li>Error: ${err.message}</li>`; |
| } |
| } |
| |
| async function runSql() { |
| clearResults(); |
| const sql = sqlEl.value.trim(); |
| if (!sql) { |
| setStatus("Enter SQL to run."); |
| return; |
| } |
| setStatus("Running..."); |
| const verb = sql.split(/\s+/)[0]?.toUpperCase(); |
| const isQuery = ["SELECT", "PRAGMA", "WITH", "EXPLAIN"].includes(verb); |
| const endpoint = isQuery ? "/api/v1/query" : "/api/v1/execute"; |
| const project = currentProject(); |
| try { |
| const result = await fetchJson(endpoint, { |
| method: "POST", |
| headers: { "Content-Type": "application/json" }, |
| body: JSON.stringify({ sql, project }), |
| }); |
| if (isQuery) { |
| const rows = result.data || []; |
| setStatus(`Query returned ${rows.length} rows.`); |
| renderTable(rows); |
| } else { |
| setStatus(`Statement executed.`); |
| resultsEl.innerHTML = `<pre>${JSON.stringify(result, null, 2)}</pre>`; |
| } |
| } catch (err) { |
| setStatus(`Error: ${err.message}`); |
| } |
| } |
| |
| document.getElementById("refresh").addEventListener("click", () => { |
| loadProjects().then(loadTables).then(loadInfo); |
| }); |
| projectEl.addEventListener("change", () => { |
| loadTables(); |
| loadInfo(); |
| }); |
| document.getElementById("run").addEventListener("click", runSql); |
| document.getElementById("clear").addEventListener("click", clearResults); |
| |
| loadProjects().then(loadTables).then(loadInfo); |
| </script> |
| </body> |
| </html> |
| """ |
|
|
| @app.get("/health") |
| def health_check(): |
| try: |
| response = requests.get(f"{SQLITE_URL}/api/v1/heartbeat", timeout=5) |
| if response.status_code == 200: |
| return {"status": "healthy", "sqlite": "up"} |
| else: |
| return {"status": "unhealthy", "detail": response.text} |
| except Exception as e: |
| return {"status": "error", "detail": str(e)} |
|
|
| @app.get("/api/v1/heartbeat") |
| def heartbeat(): |
| try: |
| response = requests.get(f"{SQLITE_URL}/api/v1/heartbeat", timeout=5) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"SQLite server error: {str(e)}") |
|
|
| @app.get("/api/v1/projects") |
| def list_projects(): |
| try: |
| response = requests.get(f"{SQLITE_URL}/api/v1/projects", timeout=5) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Projects error: {str(e)}") |
|
|
| @app.post("/api/v1/projects") |
| def create_project(request: ProjectRequest): |
| try: |
| response = requests.post(f"{SQLITE_URL}/api/v1/projects", json={"name": request.name, "description": request.description}, timeout=5) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Create project error: {str(e)}") |
|
|
| @app.delete("/api/v1/projects/{project_name}") |
| def delete_project(project_name: str): |
| try: |
| response = requests.delete(f"{SQLITE_URL}/api/v1/projects/{project_name}", timeout=5) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Delete project error: {str(e)}") |
|
|
| @app.post("/api/v1/query") |
| def query(request: QueryRequest): |
| try: |
| response = requests.post(f"{SQLITE_URL}/api/v1/query", json={"sql": request.sql, "project": request.project}, timeout=10) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Query error: {str(e)}") |
|
|
| @app.post("/api/v1/execute") |
| def execute(request: ExecuteRequest): |
| try: |
| response = requests.post(f"{SQLITE_URL}/api/v1/execute", json={"sql": request.sql, "project": request.project}, timeout=10) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Execute error: {str(e)}") |
|
|
| @app.get("/api/v1/tables") |
| def list_tables(project: str = Query("default")): |
| try: |
| response = requests.get(f"{SQLITE_URL}/api/v1/tables?project={project}", timeout=5) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Tables error: {str(e)}") |
|
|
| @app.get("/api/v1/schema") |
| def get_schema(project: str = Query("default")): |
| try: |
| response = requests.get(f"{SQLITE_URL}/api/v1/schema?project={project}", timeout=5) |
| if response.status_code != 200: |
| raise HTTPException(status_code=response.status_code, detail=response.text) |
| return response.json() |
| except Exception as e: |
| raise HTTPException(status_code=500, detail=f"Schema error: {str(e)}") |
|
|
| if __name__ == "__main__": |
| import uvicorn |
| port = int(os.getenv("PORT", 7860)) |
| uvicorn.run(app, host="0.0.0.0", port=port) |
|
|
