Hugging Face's logo

Spaces:
amaye15
/
DuckDB-UI
Sleeping

App Files Community
DuckDB-UI / index.html
amaye15's picture
amaye15
UI Improved
2de1f9e
raw
history blame
31.9 kB
 <!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DuckDB Explorer</title>
<style>
/* --- THEME VARIABLES --- */
:root {
--bg-color: #f4f7f6;
--text-color: #333;
--header-bg: #4CAF50;
--header-text: white;
--sidebar-bg: #e9ecef;
--sidebar-text: #495057;
--sidebar-hover-bg: #d4dadf;
--sidebar-active-bg: #007bff;
--sidebar-active-text: white;
--content-bg: #fff;
--border-color: #dee2e6;
--border-color-light: #e9ecef;
--table-header-bg: #f8f9fa;
--table-row-even-bg: #fdfdfe;
--button-primary-bg: #28a745;
--button-primary-hover-bg: #218838;
--button-primary-text: white;
--status-success-bg: #d1e7dd;
--status-success-text: #0f5132;
--status-success-border: #badbcc;
--status-error-bg: #f8d7da;
--status-error-text: #842029;
--status-error-border: #f5c2c7;
--loader-border: #f3f3f3;
--loader-spinner: #007bff; /* Use primary blue for spinner */
--input-border: #ced4da;
--input-bg: #fff;
--link-color: #007bff;
}
@media (prefers-color-scheme: dark) {
:root {
--bg-color: #1a1a1a; /* Darker background */
--text-color: #e8e8e8; /* Lighter text */
--header-bg: #2a622d; /* Darker header */
--header-text: #e8e8e8;
--sidebar-bg: #2c2c2c; /* Dark sidebar */
--sidebar-text: #adb5bd;
--sidebar-hover-bg: #444;
--sidebar-active-bg: #0056b3; /* Darker blue */
--sidebar-active-text: white;
--content-bg: #212121; /* Dark content area */
--border-color: #444;
--border-color-light: #333;
--table-header-bg: #2c2c2c;
--table-row-even-bg: #252525;
--button-primary-bg: #218838; /* Keep green accessible */
--button-primary-hover-bg: #1e7e34;
--button-primary-text: white;
--status-success-bg: #143620;
--status-success-text: #a3cfbb;
--status-success-border: #2a622d;
--status-error-bg: #58151c;
--status-error-text: #f5c6cb;
--status-error-border: #842029;
--loader-border: #444;
--loader-spinner: #4dabf7; /* Lighter blue for dark */
--input-border: #555;
--input-bg: #333;
--link-color: #4dabf7;
}
/* Ensure inputs inherit text color */
input[type="text"], textarea {
color: var(--text-color);
background-color: var(--input-bg);
}
/* Ensure table cells inherit text color */
th, td {
color: var(--text-color);
}
/* Placeholder text color for dark mode */
::placeholder {
color: #888;
opacity: 1; /* Firefox */
}
:-ms-input-placeholder { /* Internet Explorer 10-11 */
color: #888;
}
::-ms-input-placeholder { /* Microsoft Edge */
color: #888;
}
}
/* --- General Styles --- */
body {
font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
margin: 0;
padding: 0;
display: flex;
flex-direction: column;
height: 100vh;
background-color: var(--bg-color);
color: var(--text-color);
transition: background-color 0.3s, color 0.3s; /* Smooth theme transition */
}
a {
color: var(--link-color);
}
/* --- Header --- */
header {
background-color: var(--header-bg);
color: var(--header-text);
padding: 15px 20px;
display: flex;
align-items: center;
box-shadow: 0 2px 4px rgba(0,0,0,0.1);
font-size: 1.2em;
font-weight: bold;
transition: background-color 0.3s, color 0.3s;
}
header .loader {
border: 3px solid var(--loader-border);
border-top: 3px solid var(--header-text); /* Match header text */
border-radius: 50%;
width: 18px;
height: 18px;
animation: spin 1s linear infinite;
display: none;
margin-left: 15px;
}
/* --- Container --- */
.container {
display: flex;
flex: 1;
overflow: hidden;
}
/* --- Sidebar --- */
#sidebar {
width: 220px;
background-color: var(--sidebar-bg);
padding: 15px;
overflow-y: auto;
border-right: 1px solid var(--border-color);
transition: background-color 0.3s;
}
#sidebar h3 {
margin-top: 0;
margin-bottom: 15px;
color: var(--sidebar-text);
border-bottom: 1px solid var(--border-color);
padding-bottom: 10px;
}
#tableList {
list-style: none;
padding: 0;
margin: 0;
}
#tableList li {
padding: 8px 10px;
cursor: pointer;
border-radius: 4px;
margin-bottom: 5px;
transition: background-color 0.2s, color 0.2s;
font-size: 0.95em;
color: var(--sidebar-text); /* Use variable */
}
#tableList li:hover {
background-color: var(--sidebar-hover-bg);
}
#tableList li.active {
background-color: var(--sidebar-active-bg);
color: var(--sidebar-active-text);
font-weight: bold;
}
/* --- Main Content --- */
#mainContent {
flex: 1;
display: flex;
flex-direction: column;
padding: 20px;
overflow: hidden;
}
.content-area {
flex: 1;
display: flex;
flex-direction: column;
overflow: hidden;
gap: 15px;
}
#schemaDisplay, #dataDisplayContainer, #queryResultContainer {
background-color: var(--content-bg);
border: 1px solid var(--border-color);
border-radius: 5px;
padding: 15px;
margin-bottom: 0;
overflow: auto;
box-shadow: 0 1px 3px rgba(0,0,0,0.05);
transition: background-color 0.3s, border-color 0.3s;
}
#schemaDisplay h4, #dataDisplayContainer h4, #queryResultContainer h4 {
margin-top: 0;
color: var(--sidebar-text); /* Match sidebar heading color */
border-bottom: 1px solid var(--border-color-light);
padding-bottom: 10px;
margin-bottom: 15px;
}
#dataDisplayContainer {
flex: 1;
display: flex;
flex-direction: column;
}
#dataDisplay {
flex: 1;
overflow: auto;
min-height: 100px;
}
#schemaDisplay p, #dataDisplayContainer p {
color: #888; /* Placeholder text color */
}
/* --- Query Area --- */
#queryArea {
padding-top: 20px;
border-top: 1px solid var(--border-color);
background-color: var(--content-bg); /* Match content bg */
padding: 15px;
border-radius: 5px;
box-shadow: 0 -1px 3px rgba(0,0,0,0.05);
margin-top: 15px; /* Add margin instead of relying on flex */
transition: background-color 0.3s, border-color 0.3s;
}
#queryArea h4 {
margin-top: 0;
margin-bottom: 10px;
color: var(--sidebar-text);
}
#queryArea textarea {
width: 100%;
min-height: 80px;
padding: 10px;
border: 1px solid var(--input-border);
border-radius: 4px;
box-sizing: border-box;
font-family: monospace;
margin-bottom: 10px;
resize: vertical;
background-color: var(--input-bg); /* Input background */
color: var(--text-color); /* Input text color */
transition: background-color 0.3s, border-color 0.3s, color 0.3s;
}
#queryArea button {
padding: 10px 20px;
background-color: var(--button-primary-bg);
color: var(--button-primary-text);
border: none;
border-radius: 4px;
cursor: pointer;
transition: background-color 0.2s;
font-weight: bold;
}
#queryArea button:hover {
background-color: var(--button-primary-hover-bg);
}
/* --- Tables --- */
table {
width: 100%;
border-collapse: collapse;
margin-top: 0;
font-size: 0.9em;
}
th, td {
border: 1px solid var(--border-color-light);
padding: 10px 12px;
text-align: left;
white-space: nowrap;
max-width: 250px;
overflow: hidden;
text-overflow: ellipsis;
color: var(--text-color); /* Inherit text color */
transition: border-color 0.3s;
}
th {
background-color: var(--table-header-bg);
font-weight: 600;
position: sticky;
top: 0;
z-index: 1;
transition: background-color 0.3s;
}
tr:nth-child(even) {
background-color: var(--table-row-even-bg);
transition: background-color 0.3s;
}
tr:nth-child(odd) {
background-color: var(--content-bg); /* Match content background */
transition: background-color 0.3s;
}
/* --- Status Message --- */
#statusMessage {
padding: 10px 15px;
margin-top: 15px;
border-radius: 4px;
display: none;
font-size: 0.9em;
transition: background-color 0.3s, color 0.3s, border-color 0.3s;
}
#statusMessage.success {
background-color: var(--status-success-bg);
color: var(--status-success-text);
border: 1px solid var(--status-success-border);
}
#statusMessage.error {
background-color: var(--status-error-bg);
color: var(--status-error-text);
border: 1px solid var(--status-error-border);
}
/* --- Loader --- */
.loader {
border: 4px solid var(--loader-border);
border-top: 4px solid var(--loader-spinner);
border-radius: 50%;
width: 20px;
height: 20px;
animation: spin 1s linear infinite;
display: none;
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
</style>
</head>
<body>
<header>
<span>🦆 DuckDB Explorer</span>
<div class="loader" id="loadingIndicator"></div>
</header>
<div class="container">
<aside id="sidebar">
<h3>Tables</h3>
<ul id="tableList">
<li>Loading...</li>
</ul>
</aside>
<main id="mainContent">
<div class="content-area">
<div id="schemaDisplay">
<h4>Schema</h4>
<p id="schemaPlaceholder">Select a table from the list.</p>
<table id="schemaTable"></table>
</div>
<div id="dataDisplayContainer">
<h4>Data <span id="tableDataHeader"></span></h4>
<p id="dataPlaceholder">Select a table from the list.</p>
<div id="dataDisplay">
<table id="dataTable"></table>
</div>
</div>
<div id="queryResultContainer" style="display: none;">
<h4>Query Result</h4>
<div id="queryResultDisplay">
<table id="queryResultTable"></table>
</div>
</div>
</div>
<div id="queryArea">
<h4>Custom SQL Query (SELECT/SHOW/PRAGMA only)</h4>
<textarea id="sqlInput" placeholder="Enter your SELECT query here... e.g., SELECT * FROM table_name LIMIT 10"></textarea>
<button id="runSqlButton">Run SQL</button>
</div>
<div id="statusMessage"></div>
</main>
</div>
<script>
// --- Element Variables ---
const tableList = document.getElementById('tableList');
const schemaDisplay = document.getElementById('schemaDisplay');
const schemaTable = document.getElementById('schemaTable');
const schemaPlaceholder = document.getElementById('schemaPlaceholder');
const dataDisplayContainer = document.getElementById('dataDisplayContainer');
const dataDisplay = document.getElementById('dataDisplay');
const dataTable = document.getElementById('dataTable');
const dataPlaceholder = document.getElementById('dataPlaceholder');
const tableDataHeader = document.getElementById('tableDataHeader');
const sqlInput = document.getElementById('sqlInput');
const runSqlButton = document.getElementById('runSqlButton');
const queryResultContainer = document.getElementById('queryResultContainer');
const queryResultDisplay = document.getElementById('queryResultDisplay');
const queryResultTable = document.getElementById('queryResultTable');
const statusMessage = document.getElementById('statusMessage');
const loadingIndicator = document.getElementById('loadingIndicator');
// --- State Variables ---
const API_BASE_URL = '';
let currentTables = [];
let selectedTable = null;
let currentCustomSQL = ''; // Keep track of the last run custom query
let pollingIntervalId = null;
const POLLING_INTERVAL_MS = 5000; // Refresh every 5 seconds
// --- Utility Functions ---
function showLoader(show, isPolling = false) {
// Only show loader for non-polling actions
if (!isPolling) {
loadingIndicator.style.display = show ? 'inline-block' : 'none';
}
}
function showStatus(message, isError = false) {
statusMessage.textContent = message;
statusMessage.className = isError ? 'error' : 'success';
statusMessage.style.display = 'block';
setTimeout(() => { statusMessage.style.display = 'none'; }, 5000);
}
function clearStatus() {
statusMessage.textContent = '';
statusMessage.style.display = 'none';
}
async function fetchAPI(endpoint, options = {}, isPolling = false) { // Pass isPolling flag
showLoader(true, isPolling); // Pass isPolling flag
clearStatus();
const url = `${API_BASE_URL}${endpoint}`;
try {
const response = await fetch(url, options);
if (!response.ok) {
let errorDetail = `HTTP error! status: ${response.status}`;
try {
const errorJson = await response.json();
errorDetail += ` - ${errorJson.detail || JSON.stringify(errorJson)}`;
} catch (e) { /* Ignore */ }
throw new Error(errorDetail);
}
const contentType = response.headers.get("content-type");
if (contentType && contentType.includes("application/json")) {
return await response.json();
}
return await response.text();
} catch (error) {
console.error('API Fetch Error:', error);
// Only show status for non-polling errors or make polling errors less prominent
if (!isPolling) {
showStatus(`Error: ${error.message}`, true);
} else {
console.warn(`Polling Error: ${error.message}`); // Log quietly
}
throw error;
} finally {
showLoader(false, isPolling); // Pass isPolling flag
}
}
function renderTable(data, tableElement) {
tableElement.innerHTML = '';
if (!data || !Array.isArray(data)) {
tableElement.innerHTML = '<tbody><tr><td>Invalid data format received.</td></tr></tbody>';
console.error("Invalid data format for renderTable:", data);
return;
}
if (data.length === 0) {
tableElement.innerHTML = '<tbody><tr><td>No data available.</td></tr></tbody>';
return;
}
const headers = Object.keys(data[0]);
const thead = tableElement.createTHead();
const headerRow = thead.insertRow();
headers.forEach(headerText => {
const th = document.createElement('th');
th.textContent = headerText;
headerRow.appendChild(th);
});
const tbody = tableElement.createTBody();
data.forEach(rowData => {
const row = tbody.insertRow();
headers.forEach(header => {
const cell = row.insertCell();
const value = rowData[header];
cell.textContent = (value === null || value === undefined) ? 'NULL' : String(value);
});
});
}
function renderSchema(schemaData) {
schemaTable.innerHTML = '';
schemaPlaceholder.style.display = 'none';
if (!schemaData || !schemaData.columns || schemaData.columns.length === 0) {
schemaTable.innerHTML = '<tbody><tr><td colspan="2">No schema information available.</td></tr></tbody>';
return;
}
// schemaDisplay.innerHTML = '<h4>Schema</h4>'; // Header is already there
const thead = schemaTable.createTHead();
const headerRow = thead.insertRow();
['Name', 'Type'].forEach(headerText => {
const th = document.createElement('th');
th.textContent = headerText;
headerRow.appendChild(th);
});
const tbody = schemaTable.createTBody();
schemaData.columns.forEach(column => {
const row = tbody.insertRow();
row.insertCell().textContent = column.name;
row.insertCell().textContent = column.type;
});
}
// --- Core Logic Functions ---
async function loadTables(isPolling = false) {
if (!isPolling) {
tableList.innerHTML = '<li>Loading tables...</li>';
resetDisplays();
}
try {
const newTables = await fetchAPI('/tables', {}, isPolling); // Pass polling flag
if (JSON.stringify(newTables) !== JSON.stringify(currentTables)) {
console.log("Table list changed, updating UI.");
currentTables = newTables;
displayTables(currentTables);
// If the currently selected table disappeared, clear the displays
if (selectedTable && !currentTables.includes(selectedTable)) {
console.log(`Selected table "${selectedTable}" removed.`);
resetToInitialState();
}
}
// Update placeholders based on current state
if (currentTables.length === 0 && !isPolling) {
tableList.innerHTML = '<li>No tables found.</li>';
schemaPlaceholder.textContent = 'No tables found in the database.';
schemaPlaceholder.style.display = 'block';
dataPlaceholder.textContent = 'No tables found in the database.';
dataPlaceholder.style.display = 'block';
} else if (currentTables.length > 0 && !selectedTable && !currentCustomSQL && !isPolling) {
schemaPlaceholder.style.display = 'block';
dataPlaceholder.style.display = 'block';
}
} catch (error) {
if (!isPolling) {
tableList.innerHTML = '<li>Error loading tables.</li>';
schemaPlaceholder.textContent = 'Error loading tables.';
schemaPlaceholder.style.display = 'block';
dataPlaceholder.textContent = 'Error loading tables.';
dataPlaceholder.style.display = 'block';
} else {
console.error("Polling error (loadTables):", error);
}
// Stop polling on error to avoid spamming
stopPolling();
showStatus("Could not update table list. Polling stopped.", true);
}
}
function displayTables(tables) {
tableList.innerHTML = '';
if (tables.length === 0) {
tableList.innerHTML = '<li>No tables found.</li>';
return;
}
tables.sort().forEach(tableName => {
const li = document.createElement('li');
li.textContent = tableName;
li.dataset.tableName = tableName;
li.onclick = () => handleTableSelection(li);
if (tableName === selectedTable) {
li.classList.add('active');
}
tableList.appendChild(li);
});
}
async function loadTableSchemaAndData(tableName, isPolling = false) {
if (!tableName) return;
// Only show placeholders/loading text on initial load, not polling refresh
if (!isPolling) {
dataDisplayContainer.style.display = 'flex';
queryResultContainer.style.display = 'none';
dataPlaceholder.style.display = 'none';
schemaPlaceholder.style.display = 'none';
tableDataHeader.textContent = `for table "${tableName}"`;
schemaTable.innerHTML = '<tbody><tr><td colspan="2">Loading schema...</td></tr></tbody>';
dataTable.innerHTML = '<tbody><tr><td>Loading data...</td></tr></tbody>';
}
try {
// Fetch concurrently
const [schemaResponse, tableDataResponse] = await Promise.all([
fetchAPI(`/tables/${tableName}/schema`, {}, isPolling), // Pass polling flag
fetchAPI(`/tables/${tableName}?limit=100`, {}, isPolling) // Pass polling flag
]);
// Only render if the table is still the selected one (polling might finish after user clicks away)
if (selectedTable === tableName) {
renderSchema(schemaResponse);
renderTable(tableDataResponse, dataTable);
}
} catch (error) {
// Error shown by fetchAPI
// If the table disappeared during polling, loadTables will handle cleanup
if (selectedTable === tableName) { // Check if still selected
if (!isPolling) { // Don't overwrite placeholders if polling fails
schemaTable.innerHTML = '<tbody><tr><td colspan="2">Error loading schema.</td></tr></tbody>';
dataTable.innerHTML = '<tbody><tr><td>Error loading data.</td></tr></tbody>';
} else {
console.error(`Polling error for table ${tableName}:`, error);
// Optionally show a less intrusive error or just log it
// showStatus(`Could not refresh data for ${tableName}`, true);
}
}
}
}
function handleTableSelection(listItem) {
const currentActive = tableList.querySelector('.active');
if (currentActive) {
currentActive.classList.remove('active');
}
listItem.classList.add('active');
selectedTable = listItem.dataset.tableName;
currentCustomSQL = ''; // Clear custom query when selecting a table
sqlInput.value = ''; // Clear the textarea
loadTableSchemaAndData(selectedTable, false); // Initial load, not polling
}
async function runCustomQuery(isPolling = false) {
const sql = sqlInput.value.trim();
if (!sql) {
if (!isPolling) showStatus("SQL query cannot be empty.", true);
return;
}
// Only update UI text/visibility on explicit run, not polling refresh
if (!isPolling) {
// Clear active selection in sidebar
const currentActive = tableList.querySelector('.active');
if (currentActive) {
currentActive.classList.remove('active');
}
selectedTable = null; // Deselect table
resetDisplays();
dataDisplayContainer.style.display = 'none';
queryResultContainer.style.display = 'block';
queryResultTable.innerHTML = '<tbody><tr><td>Running query...</td></tr></tbody>';
currentCustomSQL = sql; // Store the ran query
}
try {
const resultData = await fetchAPI('/query', {
method: 'POST',
headers: { 'Content-Type': 'application/json', },
body: JSON.stringify({ sql: sql }),
}, isPolling); // Pass polling flag
// Only render if this is still the active custom query
if (currentCustomSQL === sql) {
renderTable(resultData, queryResultTable);
if (!isPolling) showStatus("Query executed successfully.", false);
}
} catch (error) {
// Error shown by fetchAPI
if (currentCustomSQL === sql) { // Only update if still relevant
if (!isPolling) {
queryResultTable.innerHTML = '<tbody><tr><td>Error executing query. See status message.</td></tr></tbody>';
} else {
console.error(`Polling error for query "${sql}":`, error);
}
}
}
}
function resetDisplays() {
// Reset schema display
schemaDisplay.innerHTML = '<h4>Schema</h4><p id="schemaPlaceholder">Select a table or run a query.</p><table id="schemaTable"></table>';
// Reset data display
dataDisplayContainer.innerHTML = '<h4>Data <span id="tableDataHeader"></span></h4><p id="dataPlaceholder">Select a table or run a query.</p><div id="dataDisplay"><table id="dataTable"></table></div>';
// Reset query display
queryResultContainer.style.display = 'none';
queryResultTable.innerHTML = '';
// Re-assign potentially overwritten elements after innerHTML reset
schemaTable = document.getElementById('schemaTable');
dataTable = document.getElementById('dataTable');
schemaPlaceholder = document.getElementById('schemaPlaceholder');
dataPlaceholder = document.getElementById('dataPlaceholder');
tableDataHeader = document.getElementById('tableDataHeader');
}
function resetToInitialState() {
selectedTable = null;
currentCustomSQL = '';
sqlInput.value = '';
resetDisplays();
// Make sure placeholders are visible if needed
if (currentTables.length > 0) {
schemaPlaceholder.style.display = 'block';
dataPlaceholder.style.display = 'block';
}
}
// --- Polling Function ---
async function pollForUpdates() {
console.log("Polling for updates...");
try {
await loadTables(true); // Refresh table list (quietly)
if (selectedTable) {
// If a table is selected, refresh its data
await loadTableSchemaAndData(selectedTable, true);
} else if (currentCustomSQL) {
// If a custom query was last run, re-run it
await runCustomQuery(true);
}
} catch (error) {
// Errors are logged within the called functions if isPolling is true
console.error("Error during polling cycle:", error);
// Optionally stop polling on error: stopPolling(); showStatus("Polling stopped due to error.", true);
}
}
function startPolling() {
stopPolling();
console.log(`Starting polling every ${POLLING_INTERVAL_MS / 1000} seconds.`);
// Run immediately once, then set interval
pollForUpdates();
pollingIntervalId = setInterval(pollForUpdates, POLLING_INTERVAL_MS);
}
function stopPolling() {
if (pollingIntervalId) {
console.log("Stopping polling.");
clearInterval(pollingIntervalId);
pollingIntervalId = null;
}
}
// --- Initial Setup ---
runSqlButton.onclick = () => runCustomQuery(false); // Explicit run is not polling
document.addEventListener('DOMContentLoaded', () => {
loadTables().then(() => {
if (currentTables.length >= 0) { // Start polling even if empty initially
startPolling();
}
});
});
// Optional: Pause polling when tab is hidden
document.addEventListener("visibilitychange", () => {
if (document.visibilityState === 'hidden') {
stopPolling();
} else {
// Refresh immediately when tab becomes visible, then restart polling
loadTables().then(() => { // Refresh tables first
if (selectedTable) {
loadTableSchemaAndData(selectedTable); // Refresh data if table selected
} else if (currentCustomSQL) {
runCustomQuery(); // Refresh query if that was active
}
startPolling(); // Restart regular polling
});
}
});
</script>
</body>
</html>