Enhance Web Demo: Show Real Column Names

Have you ever run a SQL query in a web demo and been frustrated by the output displaying generic column names like col0, col1, etc., instead of the actual column names? This article delves into a proposed solution to this problem, focusing on enhancing the user experience by displaying real column names in a web demo environment.

The Problem: Generic Column Names in Web Demos

When using web demos to execute SQL queries, a common issue arises: the results are often displayed with generic column names such as col0, col1, and so on. This can make it difficult to understand the output quickly, as you have to refer back to the query to understand what each column represents. Let's illustrate this with an example:

Imagine you run the following query:

SELECT id, name, salary FROM employees;

Instead of seeing a clear output like this:

id    name          salary
--    ----          ------
1     John Smith    75000
2     Jane Doe      82000

You might encounter something like this:

col0  col1          col2
----  ----          ----
1     John Smith    75000
2     Jane Doe      82000

This lack of clarity forces users to cross-reference the query and the output, hindering a smooth and intuitive experience. The primary goal is to bridge this gap by ensuring that web demos display the actual column names from the SQL query, making the results immediately understandable and user-friendly. This enhancement significantly improves the usability of the web demo, especially for those who are new to SQL or unfamiliar with the database schema.

Root Cause: Lack of Column Metadata

The root of this problem lies in how the query results are processed and displayed. Typically, the SelectExecutor, the component responsible for executing SELECT queries, only returns the row data without including column metadata. This means that the web demo lacks the information necessary to display the actual column names and resorts to generic placeholders. To effectively address this, the backend needs to be modified to include column names along with the row data, providing the frontend with the necessary information to present a clear and informative output.

Proposed Solution: A Comprehensive Approach

To tackle this issue, a multi-faceted approach is required, involving changes in both the backend (Rust) and frontend (TypeScript) components. This ensures a seamless flow of information from the query execution to the display of results. Let's break down the proposed solution into its key components:

Backend Changes (Rust)

1. Update SelectExecutor to Return Column Metadata

The first step is to modify the SelectExecutor in the Rust backend to return column names along with the row data. This involves updating the SelectResult struct and the execute function within the crates/executor/src/select/mod.rs file. The updated code should look something like this:

pub struct SelectResult {
    pub columns: Vec<String>,  // Column names
    pub rows: Vec<storage::Row>,
}

impl SelectExecutor {
    pub fn execute(&mut self) -> Result<SelectResult, ExecutorError> {
        // ... existing logic ...
        
        // Derive column names from SELECT list
        let columns = self.derive_column_names?();
        
        Ok(SelectResult {
            columns,
            rows,
        })
    }
    
    fn derive_column_names(&self) -> Result<Vec<String>, ExecutorError> {
        // For each item in SELECT list:
        // - If it has an alias (AS name), use the alias
        // - If it's a column reference, use the column name
        // - If it's an expression, generate a name like "expr_0"
        // - If it's *, expand to all column names from the table
    }
}

This modification introduces a columns field in the SelectResult struct, which holds a vector of column names. The execute function is updated to derive these column names and include them in the SelectResult. The crucial part here is the derive_column_names function, which is responsible for determining the column names based on the SQL query.

2. Update WASM Bindings

Next, the WASM bindings need to be updated to include column names in the query result. This involves modifying the crates/wasm-bindings/src/lib.rs file to include the column names in the QueryResult struct and the execute_query function. The updated code will look like this:

#[wasm_bindgen]
pub struct QueryResult {
    columns: Vec<String>,
    rows: Vec<JsValue>,  // Array of arrays
}

#[wasm_bindgen]
impl Database {
    pub fn execute_query(&mut self, sql: &str) -> Result<QueryResult, JsValue> {
        // ... parse and execute ...
        let result = executor.execute?();
        
        Ok(QueryResult {
            columns: result.columns,
            rows: convert_rows(result.rows),
        })
    }
}

This update ensures that the column names are passed from the Rust backend to the JavaScript frontend via WASM. This is essential for the frontend to access and display the actual column names.

Frontend Changes (TypeScript)

3. Update Results Component

Finally, the frontend needs to be updated to utilize the column names received from the backend. This involves modifying the web-demo/src/components/Results.ts file to render the actual column names in the table headers. The updated code should include the following:

interface QueryResult {
  columns: string[]
  rows: any[][]
}

private renderResults(result: QueryResult): string {
  const headers = result.columns
    .map(col => `<div class=\