/** @jsxImportSource https://esm.sh/react */ import React, { useEffect, useState } from "https://esm.sh/react"; import { createRoot } from "https://esm.sh/react-dom/client"; import { vtTokenSessionAuth } from "https://esm.town/v/stevekrouse/vtTokenSessionAuthSafe"; function App() { const [tables, setTables] = useState([]); const [selectedTable, setSelectedTable] = useState(null); const [tableData, setTableData] = useState([]); const [tableSchema, setTableSchema] = useState([]); const [error, setError] = useState(null); const [editingRow, setEditingRow] = useState(null); const [newRow, setNewRow] = useState({}); const [page, setPage] = useState(1); const [totalPages, setTotalPages] = useState(1); const pageSize = 10; // Number of rows per page useEffect(() => { fetchTables(); }, []); const fetchTables = async () => { try { const response = await fetch("/tables"); const data = await response.json(); setTables(data); } catch (err) { setError("Failed to fetch tables"); } }; const fetchTableData = async (tableName, pageNumber = 1) => { try { const response = await fetch(`/tableData/${tableName}?page=${pageNumber}&pageSize=${pageSize}`); const { data, schema, totalRows } = await response.json(); setTableData(data); setTableSchema(schema); setSelectedTable(tableName); setTotalPages(Math.ceil(totalRows / pageSize)); setPage(pageNumber); // Initialize newRow with empty values for each column const emptyRow = schema.reduce((acc, col) => { acc[col.name] = ""; return acc; }, {}); setNewRow(emptyRow); } catch (err) { setError(`Failed to fetch data for table ${tableName}`); } }; const handleEdit = (index) => { setEditingRow(index); }; const handleSave = async (index) => { try { const response = await fetch(`/update/${selectedTable}`, { method: "POST", headers: { "Content-Type": "application/json", }, body: JSON.stringify(tableData[index]), }); if (!response.ok) { throw new Error("Failed to update row"); } setEditingRow(null); await fetchTableData(selectedTable, page); } catch (err) { setError(`Failed to update row: ${err.message}`); } }; const handleInputChange = (index, key, value) => { const newData = [...tableData]; newData[index][key] = value; setTableData(newData); }; const handleNewRowInputChange = (key, value) => { setNewRow((prev) => ({ ...prev, [key]: value })); }; const handleAddRow = async () => { try { const response = await fetch(`/insert/${selectedTable}`, { method: "POST", headers: { "Content-Type": "application/json", }, body: JSON.stringify(newRow), }); if (!response.ok) { throw new Error("Failed to add new row"); } await fetchTableData(selectedTable, totalPages); // Go to the last page where the new row might be added // Reset newRow to empty values const emptyRow = Object.keys(newRow).reduce((acc, key) => { acc[key] = ""; return acc; }, {}); setNewRow(emptyRow); } catch (err) { setError(`Failed to add new row: ${err.message}`); } }; const handlePageChange = (newPage) => { fetchTableData(selectedTable, newPage); }; return (

Tables

fetchTableData(table)} className={selectedTable === table ? "active" : ""} > {table}

SQLite Dashboard

{error &&

{error}

} {selectedTable && (

{selectedTable}

{tableSchema.map((col) => ( ))} {tableData.map((row, index) => ( {tableSchema.map((col) => ( ))} ))} {tableSchema.map((col) => ( ))}

{col.name} ({col.type})	Actions
{editingRow === index ? ( handleInputChange(index, col.name, e.target.value)} /> ) : ( row[col.name] )}	{editingRow === index ? : }
handleNewRowInputChange(col.name, e.target.value)} placeholder={`New ${col.name}`} />

Page {page} of {totalPages}

)}

View Source

); } function client() { createRoot(document.getElementById("root")).render(); } if (typeof document !== "undefined") { client(); } async function server(request: Request): Promise { try { const { sqlite } = await import("https://esm.town/v/stevekrouse/sqlite"); const url = new URL(request.url); const params = Object.fromEntries(url.searchParams.entries()); if (url.pathname === "/tables") { const result = await sqlite.execute("SELECT name FROM sqlite_master WHERE type='table'"); return new Response(JSON.stringify(result.rows.map((row) => row.name)), { headers: { "Content-Type": "application/json" }, }); } if (url.pathname.startsWith("/tableData/")) { const tableName = decodeURIComponent(url.pathname.split("/").pop()); const page = parseInt(params.page) || 1; const pageSize = parseInt(params.pageSize) || 10; const offset = (page - 1) * pageSize; // Get total row count const countResult = await sqlite.execute(`SELECT COUNT(*) as count FROM "${tableName}"`); const totalRows = countResult.rows[0].count; // Get table data with pagination const dataResult = await sqlite.execute( `SELECT rowid, * FROM "${tableName}" LIMIT ? OFFSET ?`, [pageSize, offset], ); // Get table schema const schemaResult = await sqlite.execute(`PRAGMA table_info("${tableName}")`); const schema = schemaResult.rows.map((row) => ({ name: row.name, type: row.type, })); return new Response( JSON.stringify({ data: dataResult.rows, schema, totalRows, }), { headers: { "Content-Type": "application/json" }, }, ); } if (url.pathname.startsWith("/update/")) { if (request.method !== "POST") { return new Response("Method Not Allowed", { status: 405 }); } const tableName = decodeURIComponent(url.pathname.split("/").pop()); const data = await request.json(); const columns = Object.keys(data).filter((col) => col !== "rowid"); const values = columns.map((col) => data[col]); const setClause = columns.map((col) => `"${col}" = ?`).join(", "); if ("rowid" in data) { const query = `UPDATE "${tableName}" SET ${setClause} WHERE rowid = ?`; const queryParams = [...values, data.rowid]; await sqlite.execute(query, queryParams); return new Response("Updated successfully", { status: 200 }); } else { return new Response("rowid is required to update a row", { status: 400 }); } } if (url.pathname.startsWith("/insert/")) { if (request.method !== "POST") { return new Response("Method Not Allowed", { status: 405 }); } const tableName = decodeURIComponent(url.pathname.split("/").pop()); const data = await request.json(); const columns = Object.keys(data); const values = columns.map((col) => data[col]); const placeholders = columns.map(() => "?").join(", "); const query = `INSERT INTO "${tableName}" (${ columns.map((col) => `"${col}"`).join(", ") }) VALUES (${placeholders})`; await sqlite.execute(query, values); return new Response("Inserted successfully", { status: 200 }); } return new Response( ` SQLite Dashboard

`, { headers: { "Content-Type": "text/html", }, }, ); } catch (error) { return new Response(`Server error: ${error.message}`, { status: 500 }); } } const css = ` body { font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f0f0f0; } .container { display: flex; height: 100vh; } .sidebar { width: 200px; background-color: #333; color: white; padding: 20px; overflow-y: auto; } .sidebar h2 { margin-top: 0; } .sidebar ul { list-style-type: none; padding: 0; } .sidebar li { cursor: pointer; padding: 10px; margin-bottom: 5px; border-radius: 3px; } .sidebar li:hover { background-color: #444; } .sidebar li.active { background-color: #555; } .main-content { flex-grow: 1; padding: 20px; overflow-y: auto; } h1, h2 { color: #333; } table { width: 100%; border-collapse: collapse; margin-top: 20px; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } .column-type { font-size: 0.8em; color: #666; } .error { color: red; margin-bottom: 10px; } .source-link { margin-top: 20px; text-align: right; } .source-link a { color: #666; text-decoration: none; } .source-link a:hover { text-decoration: underline; } button { background-color: #4CAF50; border: none; color: white; padding: 5px 10px; text-align: center; text-decoration: none; display: inline-block; font-size: 12px; margin: 2px 2px; cursor: pointer; } input { width: 100%; padding: 5px; box-sizing: border-box; } .pagination { margin-top: 10px; display: flex; align-items: center; } .pagination button { margin-right: 10px; } .pagination span { margin-right: 10px; } `; export default vtTokenSessionAuth(server);

scientificCyanHarrier