issue_comments: 973635157

This data as json

html_url	issue_url	id	node_id	user	created_at	updated_at	author_association	body	reactions	issue	performed_via_github_app
https://github.com/simonw/datasette/issues/878#issuecomment-973635157	https://api.github.com/repos/simonw/datasette/issues/878	973635157	IC_kwDOBm6k_c46CH5V	9599	2021-11-19T01:07:08Z	2021-11-19T01:07:08Z	OWNER	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 ```	{"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0}	648435885