Table configuration settings should work in both metadata.yml and datasette.yml

[simonw]

The https://docs.datasette.io/en/latest/configuration.html page should gather together the various table settings, including:

https://docs.datasette.io/en/latest/facets.html#facets-in-metadata

databases:
  sf-trees:
    tables:
      Street_Tree_List:
        facets:
        - qLegalStatus
        facet_size: 10

https://docs.datasette.io/en/latest/full_text_search.html#configuring-full-text-search-for-a-table-or-view

databases:
  russian-ads:
    tables:
      display_ads:
        fts_table: ads_fts
        fts_pk: id
        searchmode: raw

And a bunch of stuff from metadata (has this moved to datasette.yaml yet?)

https://docs.datasette.io/en/latest/metadata.html#setting-a-default-sort-order

databases:
  mydatabase:
    tables:
      example_table:
        sort_desc: created
        size: 10
        sortable_columns:
        - height
        - weight
        label_column: title
        hidden: true

There's a full list here:

https://docs.datasette.io/en/latest/metadata.html#table-level-metadata

• hidden
• sort/sort_desc
• size
• sortable_columns
• label_column
• facets
• fts_table
• fts_pk
• searchmode

[simonw]

it looks like most (all?) of those things are still part of table metadata, e.g.

datasette/datasette/database.py

Lines 420 to 423 in b466749

	async def label_column_for_table(self, table):
	explicit_label_column = self.ds.table_metadata(self.name, table).get(
	"label_column"
	)

datasette/datasette/database.py

Lines 490 to 497 in b466749

	# Add any from metadata.json
	db_metadata = self.ds.metadata(database=self.name)
	if "tables" in db_metadata:
	hidden_tables += [
	t
	for t in db_metadata["tables"]
	if db_metadata["tables"][t].get("hidden")
	]

etc.

[simonw]

Changing this issue to be about deciding what to do with these. I'll bump it out of the 1.0a8 milestone for the moment. @asg017 did we talk about this before?

[simonw]

Made a decision on this: as with plugin settings and allow blocks (#2249) these will work in both metadata AND configuration, but will only be documented in configuration.

[simonw]

Reminder to move this section of the docs to configuration.rst instead: https://docs.datasette.io/en/latest/metadata.html#table-level-metadata

[simonw]

To be sure I'm getting all of the table metadata stuff, I tried rg:

rg table_metadata -A 15 datasette

datasette/filters.py:        table_metadata = datasette.table_metadata(database, table)
datasette/filters.py-        db = datasette.get_database(database)
datasette/filters.py-        fts_table = request.args.get("_fts_table")
datasette/filters.py:        fts_table = fts_table or table_metadata.get("fts_table")
datasette/filters.py-        fts_table = fts_table or await db.fts_table(table)
datasette/filters.py:        fts_pk = request.args.get("_fts_pk", table_metadata.get("fts_pk", "rowid"))
datasette/filters.py-        search_args = {
datasette/filters.py-            key: request.args[key]
datasette/filters.py-            for key in request.args
datasette/filters.py-            if key.startswith("_search") and key != "_searchmode"
datasette/filters.py-        }
datasette/filters.py-        search = ""
datasette/filters.py:        search_mode_raw = table_metadata.get("searchmode") == "raw"
datasette/filters.py-        # Or set search mode from the querystring
datasette/filters.py-        qs_searchmode = request.args.get("_searchmode")
datasette/filters.py-        if qs_searchmode == "escaped":
datasette/filters.py-            search_mode_raw = False
datasette/filters.py-        if qs_searchmode == "raw":
datasette/filters.py-            search_mode_raw = True
datasette/filters.py-
datasette/filters.py-        extra_context["supports_search"] = bool(fts_table)
datasette/filters.py-
datasette/filters.py-        if fts_table and search_args:
datasette/filters.py-            if "_search" in search_args:
datasette/filters.py-                # Simple ?_search=xxx
datasette/filters.py-                search = search_args["_search"]
datasette/filters.py-                where_clauses.append(
datasette/filters.py-                    "{fts_pk} in (select rowid from {fts_table} where {fts_table} match {match_clause})".format(
--
datasette/inspect.py:        table_metadata = database_metadata.get("tables", {}).get(table, {})
datasette/inspect.py-
datasette/inspect.py-        try:
datasette/inspect.py-            count = conn.execute(
datasette/inspect.py-                f"select count(*) from {escape_sqlite(table)}"
datasette/inspect.py-            ).fetchone()[0]
datasette/inspect.py-        except sqlite3.OperationalError:
datasette/inspect.py-            # This can happen when running against a FTS virtual table
datasette/inspect.py-            # e.g. "select count(*) from some_fts;"
datasette/inspect.py-            count = 0
datasette/inspect.py-
datasette/inspect.py-        column_names = table_columns(conn, table)
datasette/inspect.py-
datasette/inspect.py-        tables[table] = {
datasette/inspect.py-            "name": table,
datasette/inspect.py-            "columns": column_names,
--
datasette/inspect.py:            "hidden": table_metadata.get("hidden") or False,
datasette/inspect.py-            "fts_table": detect_fts(conn, table),
datasette/inspect.py-        }
datasette/inspect.py-
datasette/inspect.py-    foreign_keys = get_all_foreign_keys(conn)
datasette/inspect.py-    for table, info in foreign_keys.items():
datasette/inspect.py-        tables[table]["foreign_keys"] = info
datasette/inspect.py-
datasette/inspect.py-    # Mark tables 'hidden' if they relate to FTS virtual tables
datasette/inspect.py-    hidden_tables = [
datasette/inspect.py-        r["name"]
datasette/inspect.py-        for r in conn.execute(
datasette/inspect.py-            """
datasette/inspect.py-                select name from sqlite_master
datasette/inspect.py-                where rootpage = 0
datasette/inspect.py-                and sql like '%VIRTUAL TABLE%USING FTS%'
--
datasette/facets.py:def load_facet_configs(request, table_metadata):
datasette/facets.py-    # Given a request and the metadata configuration for a table, return
datasette/facets.py-    # a dictionary of selected facets, their lists of configs and for each
datasette/facets.py-    # config whether it came from the request or the metadata.
datasette/facets.py-    #
datasette/facets.py-    #   return {type: [
datasette/facets.py-    #       {"source": "metadata", "config": config1},
datasette/facets.py-    #       {"source": "request", "config": config2}]}
datasette/facets.py-    facet_configs = {}
datasette/facets.py:    table_metadata = table_metadata or {}
datasette/facets.py:    metadata_facets = table_metadata.get("facets", [])
datasette/facets.py-    for metadata_config in metadata_facets:
datasette/facets.py-        if isinstance(metadata_config, str):
datasette/facets.py-            type = "column"
datasette/facets.py-            metadata_config = {"simple": metadata_config}
datasette/facets.py-        else:
datasette/facets.py-            assert (
datasette/facets.py-                len(metadata_config.values()) == 1
datasette/facets.py-            ), "Metadata config dicts should be {type: config}"
datasette/facets.py-            type, metadata_config = list(metadata_config.items())[0]
datasette/facets.py-            if isinstance(metadata_config, str):
datasette/facets.py-                metadata_config = {"simple": metadata_config}
datasette/facets.py-        facet_configs.setdefault(type, []).append(
datasette/facets.py-            {"source": "metadata", "config": metadata_config}
datasette/facets.py-        )
datasette/facets.py-    qs_pairs = urllib.parse.parse_qs(request.query_string, keep_blank_values=True)
--
datasette/facets.py:            table_metadata = tables_metadata.get(self.table) or {}
datasette/facets.py:            if table_metadata:
datasette/facets.py:                table_facet_size = table_metadata.get("facet_size")
datasette/facets.py-        custom_facet_size = self.request.args.get("_facet_size")
datasette/facets.py-        if custom_facet_size:
datasette/facets.py-            if custom_facet_size == "max":
datasette/facets.py-                facet_size = max_returned_rows
datasette/facets.py-            elif custom_facet_size.isdigit():
datasette/facets.py-                facet_size = int(custom_facet_size)
datasette/facets.py-            else:
datasette/facets.py-                # Invalid value, ignore it
datasette/facets.py-                custom_facet_size = None
datasette/facets.py-        if table_facet_size and not custom_facet_size:
datasette/facets.py-            if table_facet_size == "max":
datasette/facets.py-                facet_size = max_returned_rows
datasette/facets.py-            else:
datasette/facets.py-                facet_size = table_facet_size
datasette/facets.py-        return min(facet_size, max_returned_rows)
--
datasette/app.py:            table_metadata = ((databases.get(database) or {}).get("tables") or {}).get(
datasette/app.py-                table
datasette/app.py-            ) or {}
datasette/app.py:            search_list.insert(0, table_metadata)
datasette/app.py-
datasette/app.py-        search_list.append(metadata)
datasette/app.py-        if not fallback:
datasette/app.py-            # No fallback allowed, so just use the first one in the list
datasette/app.py-            search_list = search_list[:1]
datasette/app.py-        if key is not None:
datasette/app.py-            for item in search_list:
datasette/app.py-                if key in item:
datasette/app.py-                    return item[key]
datasette/app.py-            return None
datasette/app.py-        else:
datasette/app.py-            # Return the merged list
datasette/app.py-            m = {}
datasette/app.py-            for item in search_list:
datasette/app.py-                m.update(item)
--
datasette/app.py:    def table_metadata(self, database, table):
datasette/app.py-        """Fetch table-specific metadata."""
datasette/app.py-        return (
datasette/app.py-            (self.metadata("databases") or {})
datasette/app.py-            .get(database, {})
datasette/app.py-            .get("tables", {})
datasette/app.py-            .get(table, {})
datasette/app.py-        )
datasette/app.py-
datasette/app.py-    def _register_renderers(self):
datasette/app.py-        """Register output renderers which output data in custom formats."""
datasette/app.py-        # Built-in renderers
datasette/app.py-        self.renderers["json"] = (json_renderer, lambda: True)
datasette/app.py-
datasette/app.py-        # Hooks
datasette/app.py-        hook_renderers = []
--
datasette/database.py:        explicit_label_column = self.ds.table_metadata(self.name, table).get(
datasette/database.py-            "label_column"
datasette/database.py-        )
datasette/database.py-        if explicit_label_column:
datasette/database.py-            return explicit_label_column
datasette/database.py-        column_names = await self.execute_fn(lambda conn: table_columns(conn, table))
datasette/database.py-        # Is there a name or title column?
datasette/database.py-        name_or_title = [c for c in column_names if c.lower() in ("name", "title")]
datasette/database.py-        if name_or_title:
datasette/database.py-            return name_or_title[0]
datasette/database.py-        # If a table has two columns, one of which is ID, then label_column is the other one
datasette/database.py-        if (
datasette/database.py-            column_names
datasette/database.py-            and len(column_names) == 2
datasette/database.py-            and ("id" in column_names or "pk" in column_names)
datasette/database.py-        ):
--
datasette/views/row.py:            "units": self.ds.table_metadata(database, table).get("units", {}),
datasette/views/row.py-        }
datasette/views/row.py-
datasette/views/row.py-        if "foreign_key_tables" in (request.args.get("_extras") or "").split(","):
datasette/views/row.py-            data["foreign_key_tables"] = await self.foreign_key_tables(
datasette/views/row.py-                database, table, pk_values
datasette/views/row.py-            )
datasette/views/row.py-
datasette/views/row.py-        return (
datasette/views/row.py-            data,
datasette/views/row.py-            template_data,
datasette/views/row.py-            (
datasette/views/row.py-                f"row-{to_css_class(database)}-{to_css_class(table)}.html",
datasette/views/row.py-                "row.html",
datasette/views/row.py-            ),
datasette/views/row.py-        )
--
datasette/views/table.py:    table_metadata = datasette.table_metadata(database_name, table_name)
datasette/views/table.py:    column_descriptions = table_metadata.get("columns") or {}
datasette/views/table.py-    column_details = {
datasette/views/table.py-        col.name: col for col in await db.table_column_details(table_name)
datasette/views/table.py-    }
datasette/views/table.py-    pks = await db.primary_keys(table_name)
datasette/views/table.py-    pks_for_display = pks
datasette/views/table.py-    if not pks_for_display:
datasette/views/table.py-        pks_for_display = ["rowid"]
datasette/views/table.py-
datasette/views/table.py-    columns = []
datasette/views/table.py-    for r in description:
datasette/views/table.py-        if r[0] == "rowid" and "rowid" not in column_details:
datasette/views/table.py-            type_ = "integer"
datasette/views/table.py-            notnull = 0
datasette/views/table.py-        else:
datasette/views/table.py-            type_ = column_details[r[0]].type
--
datasette/views/table.py:            elif column in table_metadata.get("units", {}) and value != "":
datasette/views/table.py-                # Interpret units using pint
datasette/views/table.py:                value = value * ureg(table_metadata["units"][column])
datasette/views/table.py-                # Pint uses floating point which sometimes introduces errors in the compact
datasette/views/table.py-                # representation, which we have to round off to avoid ugliness. In the vast
datasette/views/table.py-                # majority of cases this rounding will be inconsequential. I hope.
datasette/views/table.py-                value = round(value.to_compact(), 6)
datasette/views/table.py-                display_value = markupsafe.Markup(f"{value:~P}".replace(" ", " "))
datasette/views/table.py-            else:
datasette/views/table.py-                display_value = str(value)
datasette/views/table.py-                if truncate_cells and len(display_value) > truncate_cells:
datasette/views/table.py-                    display_value = display_value[:truncate_cells] + "\u2026"
datasette/views/table.py-
datasette/views/table.py-            cells.append(
datasette/views/table.py-                {
datasette/views/table.py-                    "column": column,
datasette/views/table.py-                    "value": display_value,
datasette/views/table.py-                    "raw": value,
--
datasette/views/table.py:    table_metadata = datasette.table_metadata(database_name, table_name)
datasette/views/table.py:    if "sortable_columns" in table_metadata:
datasette/views/table.py:        sortable_columns = set(table_metadata["sortable_columns"])
datasette/views/table.py-    else:
datasette/views/table.py-        sortable_columns = set(await db.table_columns(table_name))
datasette/views/table.py-    if use_rowid:
datasette/views/table.py-        sortable_columns.add("rowid")
datasette/views/table.py-    return sortable_columns
datasette/views/table.py-
datasette/views/table.py-
datasette/views/table.py:async def _sort_order(table_metadata, sortable_columns, request, order_by):
datasette/views/table.py-    sort = request.args.get("_sort")
datasette/views/table.py-    sort_desc = request.args.get("_sort_desc")
datasette/views/table.py-
datasette/views/table.py-    if not sort and not sort_desc:
datasette/views/table.py:        sort = table_metadata.get("sort")
datasette/views/table.py:        sort_desc = table_metadata.get("sort_desc")
datasette/views/table.py-
datasette/views/table.py-    if sort and sort_desc:
datasette/views/table.py-        raise DatasetteError(
datasette/views/table.py-            "Cannot use _sort and _sort_desc at the same time", status=400
datasette/views/table.py-        )
datasette/views/table.py-
datasette/views/table.py-    if sort:
datasette/views/table.py-        if sort not in sortable_columns:
datasette/views/table.py-            raise DatasetteError(f"Cannot sort table by {sort}", status=400)
datasette/views/table.py-
datasette/views/table.py-        order_by = escape_sqlite(sort)
datasette/views/table.py-
datasette/views/table.py-    if sort_desc:
datasette/views/table.py-        if sort_desc not in sortable_columns:
datasette/views/table.py-            raise DatasetteError(f"Cannot sort table by {sort_desc}", status=400)
--
datasette/views/table.py:    table_metadata = datasette.table_metadata(database_name, table_name)
datasette/views/table.py:    units = table_metadata.get("units", {})
datasette/views/table.py-
datasette/views/table.py-    # Arguments that start with _ and don't contain a __ are
datasette/views/table.py-    # special - things like ?_search= - and should not be
datasette/views/table.py-    # treated as filters.
datasette/views/table.py-    filter_args = []
datasette/views/table.py-    for key in request.args:
datasette/views/table.py-        if not (key.startswith("_") and "__" not in key):
datasette/views/table.py-            for v in request.args.getlist(key):
datasette/views/table.py-                filter_args.append((key, v))
datasette/views/table.py-
datasette/views/table.py-    # Build where clauses from query string arguments
datasette/views/table.py-    filters = Filters(sorted(filter_args), units, ureg)
datasette/views/table.py-    where_clauses, params = filters.build_where_clauses(table_name)
datasette/views/table.py-
datasette/views/table.py-    # Execute filters_from_request plugin hooks - including the default
--
datasette/views/table.py:        table_metadata, sortable_columns, request, order_by
datasette/views/table.py-    )
datasette/views/table.py-
datasette/views/table.py-    from_sql = "from {table_name} {where}".format(
datasette/views/table.py-        table_name=escape_sqlite(table_name),
datasette/views/table.py-        where=(
datasette/views/table.py-            ("where {} ".format(" and ".join(where_clauses))) if where_clauses else ""
datasette/views/table.py-        ),
datasette/views/table.py-    )
datasette/views/table.py-    # Copy of params so we can mutate them later:
datasette/views/table.py-    from_sql_params = dict(**params)
datasette/views/table.py-
datasette/views/table.py-    count_sql = f"select count(*) {from_sql}"
datasette/views/table.py-
datasette/views/table.py-    # Handle pagination driven by ?_next=
datasette/views/table.py-    _next = _next or request.args.get("_next")
--
datasette/views/table.py:    # page_size = _size or request.args.get("_size") or table_metadata.get("size")
datasette/views/table.py:    page_size = request.args.get("_size") or table_metadata.get("size")
datasette/views/table.py-    if page_size:
datasette/views/table.py-        if page_size == "max":
datasette/views/table.py-            page_size = datasette.max_returned_rows
datasette/views/table.py-        try:
datasette/views/table.py-            page_size = int(page_size)
datasette/views/table.py-            if page_size < 0:
datasette/views/table.py-                raise ValueError
datasette/views/table.py-
datasette/views/table.py-        except ValueError:
datasette/views/table.py-            raise BadRequest("_size must be a positive integer")
datasette/views/table.py-
datasette/views/table.py-        if page_size > datasette.max_returned_rows:
datasette/views/table.py-            raise BadRequest(f"_size must be <= {datasette.max_returned_rows}")
datasette/views/table.py-
datasette/views/table.py-        extra_args["page_size"] = page_size
--
datasette/views/table.py:                    metadata=table_metadata,
datasette/views/table.py-                    row_count=extra_count,
datasette/views/table.py-                )
datasette/views/table.py-            )
datasette/views/table.py-        return facet_instances
datasette/views/table.py-
datasette/views/table.py-    async def extra_facet_results(facet_instances):
datasette/views/table.py-        "Results of facets calculated against this data"
datasette/views/table.py-        facet_results = {}
datasette/views/table.py-        facets_timed_out = []
datasette/views/table.py-
datasette/views/table.py-        if not nofacet:
datasette/views/table.py-            # Run them in parallel
datasette/views/table.py-            facet_awaitables = [facet.facet_results() for facet in facet_instances]
datasette/views/table.py-            facet_awaitable_results = await run_sequential(*facet_awaitables)
datasette/views/table.py-            for (

[simonw]

And piping that through llm:

rg table_metadata -A 15 datasette | llm -m gpt-4-turbo --system \
  'List all of the keys that are looked up in the table_metadata dictionary, e.g. sort and sort_desc - add a bit of a description for each one'

Got a good result from that:

The keys looked up in the table_metadata dictionary across different contexts in the Datasette source code and their brief descriptions are as follows:

1. fts_table: Specifies the Full-Text Search (FTS) virtual table associated with a regular table, if any. This is used to support full-text search on the table's contents.

2. fts_pk: Specifies the primary key column of the FTS table. This is often "rowid" by default, but can be customized.

3. searchmode: Defines the mode for full-text search, such as "raw", indicating how search queries should be interpreted.

4. hidden: A boolean value indicating whether the table should be hidden from the Datasette interface. This is commonly used for tables that are used internally or are not meant to be directly exposed to end users.

5. facets: Specifies configurations for facets, which are ways to categorize and filter data within a table based on column values.

6. facet_size: Defines the maximum number of facet values to return.

7. label_column: Specifies a column in a table to use as a label for identifying rows, particularly in links or dropdowns where referencing the table.

8. units: A dictionary mapping column names to units (e.g., meters, dollars) so that Datasette can display these values with their appropriate units.

9. columns: A nested dictionary providing additional metadata about specific columns in the table, such as descriptions or custom display configurations.

10. sortable_columns: A list of columns that are allowed to be sorted by users. This can be used to restrict sorting capabilities for performance reasons or because some columns are not meaningful to sort.

11. sort: Specifies a default column to sort by when viewing the table’s data in Datasette.

12. sort_desc: Similar to "sort", but indicates that the default sorting should be in descending order.

13. size: Specifies the default page size or the number of rows to display when viewing the table's data.

Each of these keys allows for detailed customization of how tables are presented and interacted with in Datasette, ranging from search capabilities and faceting to display preferences like sorting, hiding tables, and managing pagination.

[simonw]

Next step: create a new Datasette method called table_config() that reads from both locations, then refactor everywhere to call that instead.

I could even see if that method could be an await datasette.table_config(database, table) awaitable - if it COULD then this is something which I could turn into a plugin hook later on, with the ability to look up configuration in databases or via remote HTTP calls.

Since this is a new method now might be a good time to do this - I have avoided making the existing datasette.metadata() method awaitable because there are already a bunch of plugins that use it.

[simonw]

It's going to be a bit weird to have await datasette.table_config() when it's just datasette.metadata() for other stuff. But I still think it's worth exploring, just to get an idea of if it would be feasible or not.

[simonw]

Next step to resolve the facets part of this is for this code here to reference table_config rather than table_metadata:

datasette/datasette/views/table.py

Lines 1264 to 1282 in 5d21057

	async def facet_instances(extra_count):
	facet_instances = []
	facet_classes = list(
	itertools.chain.from_iterable(pm.hook.register_facet_classes())
	)
	for facet_class in facet_classes:
	facet_instances.append(
	facet_class(
	datasette,
	request,
	database_name,
	sql=sql_no_order_no_limit,
	params=params,
	table=table_name,
	table_config=table_metadata,
	row_count=extra_count,
	)
	)
	return facet_instances

table_metadata came from here:

datasette/datasette/views/table.py

Line 965 in 5d21057

table_metadata = datasette.table_metadata(database_name, table_name)

Defined here in app.py:

datasette/datasette/app.py

Lines 1205 to 1212 in 5d21057

	def table_metadata(self, database, table):
	"""Fetch table-specific metadata."""
	return (
	(self.metadata("databases") or {})
	.get(database, {})
	.get("tables", {})
	.get(table, {})
	)

Added here in April 2019: 53bf875

[simonw]

Since datasette.table_metadata() was never documented I'm going to remove it, replaced with a new await datasette.table_config().

I ran a GitHub code search and couldn't spot any uses of it in plugins or anything that wasn't a clone of the Datasette repo: https://github.com/search?q=ds.table_metadata+-repo%3Asimonw%2Fdatasette&type=code&p=1

[simonw]

Turns out datasette-graphql DOES use table_metadata:

  File "/Users/simon/Dropbox/Development/datasette-graphql/datasette_graphql/utils.py", line 681, in introspect_tables
    datasette_table_metadata = datasette.table_metadata(
AttributeError: 'Datasette' object has no attribute 'table_metadata'

[simonw]

I'm going to finish this in a PR so I can iterate on the last remaining tests.