Row limit warning for charts

[rclement]

Context

As pointed out by @havef and discussed in #142, Datasette sets limits on the number of rows returned by SQL queries (setting max_returned_rows defaulting to 1000). This can have an impact on rendered charts when more than max_returned_rows are expected to be displayed.

Technical details

To be able to detect when a data truncation occurs, all charts should be using the JSON API with the _shape=objects query argument, allowing to get the truncated boolean value.

For instance, this query:

https://datasette-dashboards-demo.vercel.app/jobs.json?sql=SELECT date(date) as Date, title as Title, company as Company, source as Source, city as City, longitude, latitude FROM offers_view WHERE longitude IS NOT NULL AND latitude IS NOT NULL AND date >= date(:date_start) &date_start=2021-01-01&_shape=objects

yields the following JSON output:

{
    "ok": true,
    "database": "jobs",
    "query_name": null,
    "rows": [
        {
            "Date": "2021-04-21",
            "Title": "Devops Python H/F",
            "Company": "Sully Group",
            "Source": "RegionsJob",
            "City": "Aix-en-Provence",
            "longitude": 5.4451153,
            "latitude": 43.5227288
        },
        ...
    ],
    "truncated": true,
    "columns": [
        "Date",
        "Title",
        "Company",
        "Source",
        "City",
        "longitude",
        "latitude"
    ],
    "query": {
        "sql": "SELECT\n  date(date) as Date,\n  title as Title,\n  company as Company,\n  source as Source,\n  city as City,\n  longitude,\n  latitude\nFROM offers_view WHERE longitude IS NOT NULL AND latitude IS NOT NULL\n   AND date >= date(:date_start) \n  \n  \n  ",
        "params": {
            "date_start": "2021-01-01"
        }
    },
    "error": null,
    "private": false,
    "allow_execute_sql": true,
    "query_ms": 6.713455999943108
}

Design

As a reference, Here is how Metabase informs the user with a warning tooltip when the row limit is exceeded: