async def display_columns_and_rows( datasette, database_name, table_name, description, rows, link_column=False, truncate_cells=0, sortable_columns=None, ): """Returns columns, rows for specified table - including fancy foreign key treatment""" sortable_columns = sortable_columns or set() db = datasette.databases[database_name] table_metadata = datasette.table_metadata(database_name, table_name) column_descriptions = table_metadata.get("columns") or {} column_details = { col.name: col for col in await db.table_column_details(table_name) } pks = await db.primary_keys(table_name) pks_for_display = pks if not pks_for_display: pks_for_display = ["rowid"] columns = [] for r in description: if r[0] == "rowid" and "rowid" not in column_details: type_ = "integer" notnull = 0 else: type_ = column_details[r[0]].type notnull = column_details[r[0]].notnull columns.append({ "name": r[0], "sortable": r[0] in sortable_columns, "is_pk": r[0] in pks_for_display, "type": type_, "notnull": notnull, "description": column_descriptions.get(r[0]), }) column_to_foreign_key_table = { fk["column"]: fk["other_table"] for fk in await db.foreign_keys_for_table(table_name) } cell_rows = [] base_url = datasette.setting("base_url") for row in rows: cells = [] # Unless we are a view, the first column is a link - either to the rowid # or to the simple or compound primary key if link_column: is_special_link_column = len(pks) != 1 pk_path = path_from_row_pks(row, pks, not pks, False) cells.append({ "column": pks[0] if len(pks) == 1 else "Link", "value_type": "pk", "is_special_link_column": is_special_link_column, "raw": pk_path, "value": markupsafe.Markup( '<a href="{table_path}/{flat_pks_quoted}">{flat_pks}</a>'. format( base_url=base_url, table_path=datasette.urls.table( database_name, table_name), flat_pks=str(markupsafe.escape(pk_path)), flat_pks_quoted=path_from_row_pks(row, pks, not pks), )), }) for value, column_dict in zip(row, columns): column = column_dict["name"] if link_column and len(pks) == 1 and column == pks[0]: # If there's a simple primary key, don't repeat the value as it's # already shown in the link column. continue # First let the plugins have a go # pylint: disable=no-member plugin_display_value = None for candidate in pm.hook.render_cell( value=value, column=column, table=table_name, database=database_name, datasette=datasette, ): candidate = await await_me_maybe(candidate) if candidate is not None: plugin_display_value = candidate break if plugin_display_value: display_value = plugin_display_value elif isinstance(value, bytes): formatted = format_bytes(len(value)) display_value = markupsafe.Markup( '<a class="blob-download" href="{}"{}><Binary: {:,} byte{}></a>' .format( datasette.urls.row_blob( database_name, table_name, path_from_row_pks(row, pks, not pks), column, ), ' title="{}"'.format(formatted) if "bytes" not in formatted else "", len(value), "" if len(value) == 1 else "s", )) elif isinstance(value, dict): # It's an expanded foreign key - display link to other row label = value["label"] value = value["value"] # The table we link to depends on the column other_table = column_to_foreign_key_table[column] link_template = LINK_WITH_LABEL if ( label != value) else LINK_WITH_VALUE display_value = markupsafe.Markup( link_template.format( database=database_name, base_url=base_url, table=tilde_encode(other_table), link_id=tilde_encode(str(value)), id=str(markupsafe.escape(value)), label=str(markupsafe.escape(label)) or "-", )) elif value in ("", None): display_value = markupsafe.Markup(" ") elif is_url(str(value).strip()): display_value = markupsafe.Markup( '<a href="{url}">{url}</a>'.format( url=markupsafe.escape(value.strip()))) elif column in table_metadata.get("units", {}) and value != "": # Interpret units using pint value = value * ureg(table_metadata["units"][column]) # Pint uses floating point which sometimes introduces errors in the compact # representation, which we have to round off to avoid ugliness. In the vast # majority of cases this rounding will be inconsequential. I hope. value = round(value.to_compact(), 6) display_value = markupsafe.Markup(f"{value:~P}".replace( " ", " ")) else: display_value = str(value) if truncate_cells and len(display_value) > truncate_cells: display_value = display_value[:truncate_cells] + "\u2026" cells.append({ "column": column, "value": display_value, "raw": value, "value_type": "none" if value is None else str(type(value).__name__), }) cell_rows.append(Row(cells)) if link_column: # Add the link column header. # If it's a simple primary key, we have to remove and re-add that column name at # the beginning of the header row. first_column = None if len(pks) == 1: columns = [col for col in columns if col["name"] != pks[0]] first_column = { "name": pks[0], "sortable": len(pks) == 1, "is_pk": True, "type": column_details[pks[0]].type, "notnull": column_details[pks[0]].notnull, } else: first_column = { "name": "Link", "sortable": False, "is_pk": False, "type": "", "notnull": 0, } columns = [first_column] + columns return columns, cell_rows
def test_format_bytes(bytes, expected): assert expected == utils.format_bytes(bytes)
async def extra_template(): display_rows = [] for row in results.rows if results else []: display_row = [] for column, value in zip(results.columns, row): display_value = value # Let the plugins have a go # pylint: disable=no-member plugin_display_value = None for candidate in pm.hook.render_cell( row=row, value=value, column=column, table=None, database=database, datasette=self.ds, ): candidate = await await_me_maybe(candidate) if candidate is not None: plugin_display_value = candidate break if plugin_display_value is not None: display_value = plugin_display_value else: if value in ("", None): display_value = Markup(" ") elif is_url(str(display_value).strip()): display_value = Markup( '<a href="{url}">{url}</a>'.format( url=escape(value.strip()))) elif isinstance(display_value, bytes): blob_url = path_with_format( request=request, format="blob", extra_qs={ "_blob_column": column, "_blob_hash": hashlib.sha256(display_value).hexdigest(), }, ) formatted = format_bytes(len(value)) display_value = markupsafe.Markup( '<a class="blob-download" href="{}"{}><Binary: {:,} byte{}></a>' .format( blob_url, ' title="{}"'.format(formatted) if "bytes" not in formatted else "", len(value), "" if len(value) == 1 else "s", )) display_row.append(display_value) display_rows.append(display_row) # Show 'Edit SQL' button only if: # - User is allowed to execute SQL # - SQL is an approved SELECT statement # - No magic parameters, so no :_ in the SQL string edit_sql_url = None is_validated_sql = False try: validate_sql_select(sql) is_validated_sql = True except InvalidSql: pass if allow_execute_sql and is_validated_sql and ":_" not in sql: edit_sql_url = (self.ds.urls.database(database) + "?" + urlencode({ **{ "sql": sql, }, **named_parameter_values, })) show_hide_hidden = "" if metadata.get("hide_sql"): if bool(params.get("_show_sql")): show_hide_link = path_with_removed_args( request, {"_show_sql"}) show_hide_text = "hide" show_hide_hidden = ( '<input type="hidden" name="_show_sql" value="1">') else: show_hide_link = path_with_added_args( request, {"_show_sql": 1}) show_hide_text = "show" else: if bool(params.get("_hide_sql")): show_hide_link = path_with_removed_args( request, {"_hide_sql"}) show_hide_text = "show" show_hide_hidden = ( '<input type="hidden" name="_hide_sql" value="1">') else: show_hide_link = path_with_added_args( request, {"_hide_sql": 1}) show_hide_text = "hide" hide_sql = show_hide_text == "show" return { "display_rows": display_rows, "custom_sql": True, "named_parameter_values": named_parameter_values, "editable": editable, "canned_query": canned_query, "edit_sql_url": edit_sql_url, "metadata": metadata, "settings": self.ds.settings_dict(), "request": request, "show_hide_link": self.ds.urls.path(show_hide_link), "show_hide_text": show_hide_text, "show_hide_hidden": markupsafe.Markup(show_hide_hidden), "hide_sql": hide_sql, }