issue_comments: 973635157

This exercise is proving so useful in getting my head around how the enormous and complex TableView class works again.

Here's where I've got to now - I'm systematically working through the variables that are returned for HTML and for JSON copying across code to get it to work:

```python from datasette.database import QueryInterrupted from datasette.utils import escape_sqlite from datasette.utils.asgi import Response, NotFound, Forbidden from datasette.views.base import DatasetteError from datasette import hookimpl from asyncinject import AsyncInject, inject from pprint import pformat

class Table(AsyncInject): @inject async def database(self, request, datasette): # TODO: all that nasty hash resolving stuff can go here db_name = request.url_vars["db_name"] try: db = datasette.databases[db_name] except KeyError: raise NotFound(f"Database '{db_name}' does not exist") return db

@inject
async def table_and_format(self, request, database, datasette):
    table_and_format = request.url_vars["table_and_format"]
    # TODO: be a lot smarter here
    if "." in table_and_format:
        return table_and_format.split(".", 2)
    else:
        return table_and_format, "html"

@inject
async def main(self, request, database, table_and_format, datasette):
    # TODO: if this is actually a canned query, dispatch to it

    table, format = table_and_format

    is_view = bool(await database.get_view_definition(table))
    table_exists = bool(await database.table_exists(table))
    if not is_view and not table_exists:
        raise NotFound(f"Table not found: {table}")

    await check_permissions(
        datasette,
        request,
        [
            ("view-table", (database.name, table)),
            ("view-database", database.name),
            "view-instance",
        ],
    )

    private = not await datasette.permission_allowed(
        None, "view-table", (database.name, table), default=True
    )

    pks = await database.primary_keys(table)
    table_columns = await database.table_columns(table)

    specified_columns = await columns_to_select(datasette, database, table, request)
    select_specified_columns = ", ".join(
        escape_sqlite(t) for t in specified_columns
    )
    select_all_columns = ", ".join(escape_sqlite(t) for t in table_columns)

    use_rowid = not pks and not is_view
    if use_rowid:
        select_specified_columns = f"rowid, {select_specified_columns}"
        select_all_columns = f"rowid, {select_all_columns}"
        order_by = "rowid"
        order_by_pks = "rowid"
    else:
        order_by_pks = ", ".join([escape_sqlite(pk) for pk in pks])
        order_by = order_by_pks

    if is_view:
        order_by = ""

    nocount = request.args.get("_nocount")
    nofacet = request.args.get("_nofacet")

    if request.args.get("_shape") in ("array", "object"):
        nocount = True
        nofacet = True

    # Next, a TON of SQL to build where_params and filters and suchlike
    # skipping that and jumping straight to...
    where_clauses = []
    where_clause = ""
    if where_clauses:
        where_clause = f"where {' and '.join(where_clauses)} "

    from_sql = "from {table_name} {where}".format(
        table_name=escape_sqlite(table),
        where=("where {} ".format(" and ".join(where_clauses)))
        if where_clauses
        else "",
    )
    from_sql_params ={}
    params = {}
    count_sql = f"select count(*) {from_sql}"
    sql_no_order_no_limit = (
        "select {select_all_columns} from {table_name} {where}".format(
            select_all_columns=select_all_columns,
            table_name=escape_sqlite(table),
            where=where_clause,
        )
    )

    page_size = 100
    offset = " offset 0"

    sql = "select {select_specified_columns} from {table_name} {where}{order_by} limit {page_size}{offset}".format(
        select_specified_columns=select_specified_columns,
        table_name=escape_sqlite(table),
        where=where_clause,
        order_by=order_by,
        page_size=page_size + 1,
        offset=offset,
    )

    # Fetch rows
    results = await database.execute(sql, params, truncate=True)
    columns = [r[0] for r in results.description]
    rows = list(results.rows)

    # Fetch count
    filtered_table_rows_count = None
    if count_sql:
        try:
            count_rows = list(await database.execute(count_sql, from_sql_params))
            filtered_table_rows_count = count_rows[0][0]
        except QueryInterrupted:
            pass


    vars = {
        "json": {
            # THIS STUFF is from the regular JSON
            "database": database.name,
            "table": table,
            "is_view": is_view,
            # "human_description_en": human_description_en,
            "rows": rows[:page_size],
            "truncated": results.truncated,
            "filtered_table_rows_count": filtered_table_rows_count,
            # "expanded_columns": expanded_columns,
            # "expandable_columns": expandable_columns,
            "columns": columns,
            "primary_keys": pks,
            # "units": units,
            "query": {"sql": sql, "params": params},
            # "facet_results": facet_results,
            # "suggested_facets": suggested_facets,
            # "next": next_value and str(next_value) or None,
            # "next_url": next_url,
            "private": private,
            "allow_execute_sql": await datasette.permission_allowed(
                request.actor, "execute-sql", database, default=True
            ),
        },
        "html": {
            # ... this is the HTML special stuff
            # "table_actions": table_actions,
            # "supports_search": bool(fts_table),
            # "search": search or "",
            "use_rowid": use_rowid,
            # "filters": filters,
            # "display_columns": display_columns,
            # "filter_columns": filter_columns,
            # "display_rows": display_rows,
            # "facets_timed_out": facets_timed_out,
            # "sorted_facet_results": sorted(
            #     facet_results.values(),
            #     key=lambda f: (len(f["results"]), f["name"]),
            #     reverse=True,
            # ),
            # "show_facet_counts": special_args.get("_facet_size") == "max",
            # "extra_wheres_for_ui": extra_wheres_for_ui,
            # "form_hidden_args": form_hidden_args,
            # "is_sortable": any(c["sortable"] for c in display_columns),
            # "path_with_replaced_args": path_with_replaced_args,
            # "path_with_removed_args": path_with_removed_args,
            # "append_querystring": append_querystring,
            "request": request,
            # "sort": sort,
            # "sort_desc": sort_desc,
            "disable_sort": is_view,
            # "custom_table_templates": [
            #     f"_table-{to_css_class(database)}-{to_css_class(table)}.html",
            #     f"_table-table-{to_css_class(database)}-{to_css_class(table)}.html",
            #     "_table.html",
            # ],
            # "metadata": metadata,
            # "view_definition": await db.get_view_definition(table),
            # "table_definition": await db.get_table_definition(table),
        },
    }

    # I'm just trying to get HTML to work for the moment
    if format == "json":
        return Response.json(dict(vars, locals=locals()), default=repr)
    else:
        return Response.html(repr(vars["html"]))

async def view(self, request, datasette):
    return await self.main(request=request, datasette=datasette)

@hookimpl def register_routes(): return [ (r"/t/(?P<db_name>[^/]+)/(?P<table_and_format>[^/]+?$)", Table().view), ]

async def check_permissions(datasette, request, permissions): """permissions is a list of (action, resource) tuples or 'action' strings""" for permission in permissions: if isinstance(permission, str): action = permission resource = None elif isinstance(permission, (tuple, list)) and len(permission) == 2: action, resource = permission else: assert ( False ), "permission should be string or tuple of two items: {}".format( repr(permission) ) ok = await datasette.permission_allowed( request.actor, action, resource=resource, default=None, ) if ok is not None: if ok: return else: raise Forbidden(action)

async def columns_to_select(datasette, database, table, request): table_columns = await database.table_columns(table) pks = await database.primary_keys(table) columns = list(table_columns) if "_col" in request.args: columns = list(pks) _cols = request.args.getlist("_col") bad_columns = [column for column in _cols if column not in table_columns] if bad_columns: raise DatasetteError( "_col={} - invalid columns".format(", ".join(bad_columns)), status=400, ) # De-duplicate maintaining order: columns.extend(dict.fromkeys(_cols)) if "_nocol" in request.args: # Return all columns EXCEPT these bad_columns = [ column for column in request.args.getlist("_nocol") if (column not in table_columns) or (column in pks) ] if bad_columns: raise DatasetteError( "_nocol={} - invalid columns".format(", ".join(bad_columns)), status=400, ) tmp_columns = [ column for column in columns if column not in request.args.getlist("_nocol") ] columns = tmp_columns return columns ```