Add plots to datasette using billboard.js

Continuing from a previous post, now it's time to draw the table filled with values.

As a showcase this post is available as a github project: FIXME.

For plotting I used billboard.js the first time.

First version: use the API directly with the table

Every table in datasette has a direct link to get the table as csv with specified fields. I used this before, but this time I cannot filter the columns. So the rowid which is a primary-key is plotted. I found no solution within billboard.js to remove rowid from the plot.

Second version: use a SQL query

This worked as expected, but only after I found out that I need to use LIMIT instead of a size parameter. There is even a ticket about it: datasette#1200 but I agree that LIMIT is good enough - when you know size doesn't work. On the positive side, some calculations are possible here, i.e. multiply a value by 1000 because no fractions are wanted in the y-axis. An example query looks like this: /demo.csv?sql=SELECT+created%2C+delta_sec*1000+FROM+reports+ORDER+BY+created+DESC+LIMIT+100

This version is clearly the preferred one because no addional Python code is needed.

Third version: use jinja template and a prepared query

The "I have a hammer"-solution. Here a query is prepared in the plugins/index_vars.py and the data is generated using a jinja-for-loop in the template. The multiplication needed could be done in the SQL query or in the template (as shown in the showcase project).