Ability to default to hiding the SQL for a canned query

[simonw]

I'm working on a project with some HUGE (400+ lines of SQL) canned queries right now.

Any time you land on the canned query page you have to scroll down a long distance to get to the results!

Would be useful to be able to default to https://latest.datasette.io/fixtures/magic_parameters?_hide_sql=1 without needing the parameter.

[simonw]

If you do this it should still be possible to view the SQL - which means we need a new parameter. I propose ?_show_sql=1 to over-ride the hidden default.

I think the configuration should use hide_sql: true - looking like this:

databases:
  fixtures:
    queries:
      neighborhood_search:
        hide_sql: true
        sql: |-
          select neighborhood, facet_cities.name, state
          from facetable
            join facet_cities on facetable.city_id = facet_cities.id
          where neighborhood like '%' || :text || '%'
          order by neighborhood
        title: Search neighborhoods

[simonw]

The logic for this is a little bit fiddly, due to the need to switch to using ?_show_sql=1 on the link depending on the context.

• If metadata says hide and there's no query string, hide and link to ?_show_sql=1
• If metadata says hide but query string says ?_show_sql=1, show and have hide link linking to URL without ?_show_sql=1
• Otherwise, show and link to ?_hide_sql=1
• ... or if that query string is there then hide and link to URL without ?_hide_sql=1

[simonw]

Also need to consider this hidden field - it should pass the _hide_sql or _show_sql parameters depending on the same logic:

datasette/datasette/templates/query.html

Lines 47 to 49 in acc2243

	{% if not canned_query %}<input type="hidden" name="sql" value="{% if query and query.sql %}{{ query.sql }}{% else %}select * from {{ tables[0].name|escape_sqlite }}{% endif %}">{% endif %}
	<input type="hidden" name="_hide_sql" value="1">
	{% endif %}

[simonw]

Demo: https://latest.datasette.io/fixtures/neighborhood_search

Documentation: https://docs.datasette.io/en/latest/sql_queries.html#additional-canned-query-options