Documented JavaScript variables on different templates made available for plugins

[simonw]

While working on simonw/datasette-leaflet-freedraw#10 I found myself writing this atrocity to figure out the SQL query used for a specific table page:

let innerSql = Array.from(document.getElementsByTagName("span")).filter(
    el => el.innerText == "View and edit SQL"
)[0].parentElement.getAttribute("title")

This is obviously bad - it's very brittle, and will break if I ever change the text on that link (like localizing it for example).

Instead, I think pages like that one should have a block of script at the bottom something like this:

window.datasette = window.datasette || {};
datasette.view_name = 'table';
datasette.table_sql = 'select * from ...';

[simonw]

This should aim to be as consistent as possible with the various arguments to hooks on https://docs.datasette.io/en/stable/plugin_hooks.html

[simonw]

The table page should expose the query both with and without the limit clause. The above gave me back:

select id, ACCESS_TYP, UNIT_ID, UNIT_NAME, SUID_NMA, AGNCY_ID, AGNCY_NAME, AGNCY_LEV,
  AGNCY_TYP, AGNCY_WEB, LAYER, MNG_AG_ID, MNG_AGENCY, MNG_AG_LEV, MNG_AG_TYP,
  PARK_URL, COUNTY, ACRES, LABEL_NAME, YR_EST, DES_TP, GAP_STS, geometry
from CPAD_2020a_Units where "AGNCY_LEV" = :p0 order by id limit 101

But I actually wanted to run a fetch() against a version of that without the order by id limit 101 bit (I wanted to figure out the Extent() of the geometry column) - so I need something like datasette.table_sql_no_order_no_limit.

[simonw]

I want to get this into Datasette 0.60 - #1566 - it's a small change that can unlock a lot of potential.

[simonw]

This should also expose the JSON API endpoints used to execute SQL against this database.

[simonw]

... huh, it could even expose a JavaScript function that can be called to execute a SQL query.

datasette.query("select * from blah").then(...)

Maybe it takes an optional second argument that specifies the database - defaulting to the one for the current page.

[simonw]

Or...

rows = await datasette.query`select * from searchable where id > ${id}`;

And it knows how to turn that into a parameterized call using tagged template literals.

[simonw]

Quick prototype of that tagged template query function:

function query(pieces, ...parameters) {
  var qs = new URLSearchParams();
  var sql = pieces[0];
  parameters.forEach((param, i) => {
    sql += `:p${i}${pieces[i + 1]}`;
    qs.append(`p${i}`, param);
  });
  qs.append("sql", sql);
  return qs.toString();
}

var id = 4;
console.log(query`select * from ids where id > ${id}`);

Outputs:

p0=4&sql=select+*+from+ids+where+id+%3E+%3Ap0

[simonw]

No way with a tagged template literal to pass an extra database name argument, so instead I need a method that returns a callable that can be used for the tagged template literal for a specific database - or the default database.

This could work (bit weird looking though):

var rows = await datasette.query("fixtures")`select * from foo`;