Ability to serve content directly from the database

[simonw]

Put SQLite BLOBs to good use.

[simonw]

Maybe a configuration query that looks this:

select binary_content from files where key = :key

[simonw]

I tested this branch with a files.db database with the following table and it worked:

CREATE TABLE [photos] (
   [key] TEXT,
   [contents] BLOB
);

Then this in metadata.json:

{
    "plugins": {
        "datasette-media": {
            "photo_from_db": {
                "sql": "select contents as binary_content from photos where key=:key",
                "database": "files"
            }
        }
    }
}

Images were then served from http://127.0.0.1:8001/-/media/photo_from_db/key-for-image

[simonw]

This could also be made available as one or more output renderers, which you would enable in the config:

{
    "plugins": {
        "datasette-media": {
            "enable-output-renderer": true
        }
    }
}

This could add .raw which dumps out the raw data.

Needs to be done carefully - don't want to accidentally open up HTML injection attacks (people who request .raw for select '<b>evil_html</b>' as output).

[simonw]

This should be compatible with https://www.sqlite.org/sqlar.html - which can store files using DEFLATE compression.

[simonw]

sqlite-utils 2.12 is out now with the new insert-files command, useful for testing this issue.

[simonw]

Don't forget to update this paragraph at the top of the README:

Use this when you have a database table containing references to files on disk that you would like to be able to serve to your users.

[simonw]

One way to support sqlar: allow a should_deflate column to be returned which tells the plugin to run deflate on the content.

Custom queries against sqlar can then use length(content) < Szabo as should_deflate