UI on SQL query page for saving a query

[CharlesNepote]

Saved queries are a great idea. But it's hard to use, because we have to make several operations (clicks, copy/pasting, etc.) to make it work. It could be great to ease the use of it:

• when editing a query that have already been saved, show an "Update saved query" button
• when editing a query that haven't been saved before, show a "Save query" button

[simonw]

Currently the plugin works by registering its own save_query writable canned query to save these, which isn't a great experience - for one thing the input is a single line:

I'm going to switch to having dedicated interfaces instead.

I want this page to grow a new "Save this query" button: https://latest.datasette.io/fixtures?sql=select+pk1%2C+pk2%2C+content+from+compound_primary_key+order+by+pk1%2C+pk2+limit+101

I'll likely use JavaScript to add this. I'll start by using a prompt("Name for this query") input to ask the user what they would like to call the query.

Then any time you view a query like on https://latest.datasette.io/fixtures/neighborhood_search that was previously saved there will be a "delete query" button.

I'm not sure how to handle edit yet - at first I think I'll let people delete the query and then save a new version of it. You can get to "Edit SQL" from the canned query page in one click so that should hopefully be an OK flow.

Can reconsider that once I have the new UI working.

[simonw]

Prototype - paste this in browser devtools on a query results page:

var button = document.createElement("button");
button.style.marginLeft = "2em";
button.setAttribute("id", "save-query");
button.setAttribute("type", "button");
button.appendChild(document.createTextNode("Save query"));
button.addEventListener("click", (ev) => {
  ev.preventDefault();
  var sql = document.getElementById("sql-editor").value;
  var name = prompt("Name to use for saved query");
  if (name) {
    var csrftoken = document.cookie.split("; ").find(row => row.startsWith("ds_csrftoken=")).split("=")[1];
    // Check if query already exists
    fetch(`/data/${name}.json`).then((response) => {
      if (response.status == 404) {
        // POST to /data/save_query with JSON name= and sql=
        fetch("/data/save_query.json", {
          method: "POST",
          headers: {
            "Content-Type": "application/x-www-form-urlencoded"
          },
          credentials: "include",
          // Send body as form-encoded name=/sql=/csrf_token=
          body: new URLSearchParams({
            name: name,
            sql: sql,
            csrftoken: csrftoken,
          })
        }).then((response) => {
          if (response.status == 200) {
            alert("Query saved");
          }
        });
      } else {
        alert("A query with that name already exists");
      }
    });
  }
});
document.getElementById("sql-format").parentNode.appendChild(button);

[simonw]

Simpler version - this one tries to run the query directly and spots if an integrity error is returned:

var button = document.createElement("button");
button.style.marginLeft = "2em";
button.setAttribute("id", "save-query");
button.setAttribute("type", "button");
button.appendChild(document.createTextNode("Save query"));
button.addEventListener("click", (ev) => {
  ev.preventDefault();
  var sql = document.getElementById("sql-editor").value;
  var name = prompt("Name to use for saved query");
  if (name) {
    var csrftoken = document.cookie.split("; ").find(row => row.startsWith("ds_csrftoken=")).split("=")[1];
    fetch("/data/save_query.json", {
      method: "POST",
      headers: {
        "Content-Type": "application/x-www-form-urlencoded",
        "Accept": "application/json"
      },
      credentials: "include",
      body: new URLSearchParams({
        name: name,
        sql: sql,
        csrftoken: csrftoken,
      })
    }).then((response) => response.json()).then((data) => {
      if (data.ok) {
        alert("Query saved");
      } else {
        alert(data.message);
      }
    });
  }
});
document.getElementById("sql-format").parentNode.appendChild(button);

Things I need to fix:

• It should redirect to the saved query when it succeeds
• Need to figure out the URL including both the database name (currently hard-coded to data/) and if Datasette is running against a prefix
• Lilkewise, if the name they entered has surprising characters need to redirect to the right tilde-encoded URL
• The CSRF token stuff was a bit gnarly. Would be nice if that was easier.

The tilde-encoding thing makes me think I'd still be better off implementing a custom route for this rather than trying to use that canned query.

[simonw]

• The CSRF token stuff was a bit gnarly. Would be nice if that was easier.

Maybe with this:

• Option to skip checks if custom header present asgi-csrf#22

[CharlesNepote]

Wahoo, great!

Need to figure out the URL including both the database name (currently hard-coded to data/) and if Datasette is running against a prefix

Should it be possible to put these data in the HTML. The <head> could be an interesting place for it, using:

• <base> tag to express the prefix
• and different properties to express the database name, the licence, the table name, etc. I would have a look to:
  • either DCAT
  • either schema.org
  • or Frictionless Data resource

Wouldn't adding these data in the HTML code of each page allow more usages for plugins?

[CharlesNepote]

Thinking about the usages, I think there are several types of queries.

1. Stable and popular/generic queries.
These queries don't change at all. They are mainly built by the publisher. Canned queries, facets or views are good solutions for it.
Eg.

• the split of a table by countries
• the split of a table by dates

2. Stable and specific queries.
These queries are focusing on more specific usages. They need to be stable but they don't need to be "first order" queries. Users who don't understand well SQL need to trust them, this is why they should never change at all IMHO. An evolution means a new name.
Saved queries is probably a good place for it. And "saved query" is maybe a good name.

3. Evolving queries
These queries are used by data explorers or data managers that need to change their queries due to the data updates or the environment (contextual changes, legal changes, etc.).
These queries should be updated by the time. Their name shouldn't change between two updates. Maybe a good practice would be to store each change, as a wiki page history, to let people see their evolution.
Eg.

• a query identifying spam/vandalism in a crowdsourced database, while excluding false positives (I personally save the different version of my query in a private gist)
• a query identifying the progression of a new virus variant

"saved query" might not be a good name for them because it could leads to confusion with the "saved queries" that are never changing.

Maybe these different queries could be stored in different places with different names, eg:

• /prefix/db/table/app-queries/france for stable and generic queries
• /prefix/db/table/saved-queries-const/france-yogurt for saved queries that don't evolve
• /prefix/db/table/saved-queries-variable/spam-detection for saved queries that evolve

But maybe I'm complicating things...

[simonw]

OK I really like the idea of revision tracking for queries. That could be super-useful.

I actually have that as a very basic feature of datasette-edit-templates right now - the same template can be saved multiple times and only the one with the most recent created date gets used: https://github.com/simonw/datasette-edit-templates/blob/087f6a6cabc20020f2b0524f11aa3a7836320848/datasette_edit_templates/__init__.py#L24-L26