Add writable canned query demo to latest.datasette.io

[simonw]

This would be useful while working on:

• Implement canned queries against new query JSON work #2114

[simonw]

Here's a simple plugin that can do this:

from datasette import hookimpl

@hookimpl
def startup(datasette):
    db = datasette.add_memory_database("counters")

    async def inner():
        await db.execute_write("create table if not exists counters (name text primary key, value integer)")
        await db.execute_write("insert or ignore into counters (name, value) values ('counter', 0)")

    return inner


@hookimpl
def canned_queries(database):
    if database == "counters":
        return {
            "increment": {
                "sql": "update counters set value = value + 1 where name = 'counter'",
                "write": True,
            },
            "decrement": {
                "sql": "update counters set value = value - 1 where name = 'counter'",
                "write": True,
            },
        }

[simonw]

Annoying thing about this plugin is that you don't see the new counter value when you submit the increment or decrement query.

Maybe canned queries should support SQL multiple statements? Could return the result of the last one.

[simonw]

Alternatively, what about if there was a custom SQL function available during canned write queries for setting the output message? Then the fact that canned queries don't return a table view wouldn't be a problem. Bit weird though.

[simonw]

Or... how about if the on_success_message option could define a SQL query to be executed to generate that message? Maybe on_success_message_sql.

[simonw]

Now live at https://latest.datasette.io/counters