Build HTML version of /content?sql=...

[simonw]

This will help make the hook as robust as possible.

• Plan for getting the new JSON format query views working #2109

[simonw]

I changed my mind, I'm not going to do this with a format hook.

Also: #2109 (comment)

I've decided to let HTML have its own special context. I'm going to try formalizing that as a @dataclass with fields with metadata={"help": ...} to see what that looks like.

[simonw]

Getting the query page working with all of the necessary HTML context variables is a slog.

I uploaded query.html to Claude and asked it "List of every template context variable with a detailed description of each one":

Based on the template code provided, here is a list of the template context variables and a description of each one:

• database - The name of the database being queried.
• query - The SQL query object containing the sql string.
• canned_query - The name of the canned query if this is a canned query.
• private - Boolean indicating if this is a private database.
• urls - Object containing URL helpers like database().
• canned_write - Boolean indicating if this canned query allows writes.
• db_is_immutable - Boolean indicating if this database is immutable.
• error - Any query error message.
• hide_sql - Boolean indicating if the SQL should be hidden.
• show_hide_link - The URL to toggle showing/hiding the SQL.
• show_hide_text - The text for the show/hide SQL link.
• editable - Boolean indicating if the SQL can be edited.
• allow_execute_sql - Boolean indicating if custom SQL can be executed.
• tables - List of table objects in the database.
• named_parameter_values - Dictionary of parameter names/values.
• csrftoken() - Function to generate a CSRF token.
• edit_sql_url - URL to edit the SQL for a canned query.
• display_rows - List of result rows to display.
• columns - List of column names.
• renderers - Dictionary of renderer name to URL.
• url_csv - URL for CSV export.
• metadata - Metadata about the query/database.
• crumbs - Breadcrumb navigation object.

So in summary, it includes variables for the database, query, results, URLs, permissions, and metadata to power the template.

[simonw]

Got Claude to generate this code:

from dataclasses import dataclass, field

@dataclass
class QueryContext:
    database: str = field(metadata={"help": "The name of the database being queried"})
    query: dict = field(metadata={"help": "The SQL query object containing the `sql` string"})
    canned_query: str = field(metadata={"help": "The name of the canned query if this is a canned query"})
    private: bool = field(metadata={"help": "Boolean indicating if this is a private database"})
    urls: dict = field(metadata={"help": "Object containing URL helpers like `database()`"})
    canned_write: bool = field(metadata={"help": "Boolean indicating if this canned query allows writes"})
    db_is_immutable: bool = field(metadata={"help": "Boolean indicating if this database is immutable"})
    error: str = field(metadata={"help": "Any query error message"})
    hide_sql: bool = field(metadata={"help": "Boolean indicating if the SQL should be hidden"})
    show_hide_link: str = field(metadata={"help": "The URL to toggle showing/hiding the SQL"})
    show_hide_text: str = field(metadata={"help": "The text for the show/hide SQL link"})
    editable: bool = field(metadata={"help": "Boolean indicating if the SQL can be edited"})
    allow_execute_sql: bool = field(metadata={"help": "Boolean indicating if custom SQL can be executed"})
    tables: list = field(metadata={"help": "List of table objects in the database"})
    named_parameter_values: dict = field(metadata={"help": "Dictionary of parameter names/values"})
    csrftoken: callable = field(metadata={"help": "Function to generate a CSRF token"})
    edit_sql_url: str = field(metadata={"help": "URL to edit the SQL for a canned query"})
    display_rows: list = field(metadata={"help": "List of result rows to display"})
    columns: list = field(metadata={"help": "List of column names"})
    renderers: dict = field(metadata={"help": "Dictionary of renderer name to URL"})
    url_csv: str = field(metadata={"help": "URL for CSV export"})
    metadata: dict = field(metadata={"help": "Metadata about the query/database"})

[simonw]

Maybe I teach datasette.render_template() to take a dataclass instance as an optional alternative to a dictionary, and if it gets one it turns it into something that works well in the template context.

[simonw]

Shipped: https://latest.datasette.io/fixtures?sql=select+*+from+facetable