repos: 142967347

This data as json

id	node_id	name	full_name	private	owner	html_url	description	fork	created_at	updated_at	pushed_at	homepage	size	stargazers_count	watchers_count	language	has_issues	has_projects	has_downloads	has_wiki	has_pages	forks_count	archived	disabled	open_issues_count	license	topics	forks	open_issues	watchers	default_branch	permissions	temp_clone_token	organization	network_count	subscribers_count	readme	readme_html	allow_forking	visibility	is_template	template_repository	web_commit_signoff_required	has_discussions
142967347	MDEwOlJlcG9zaXRvcnkxNDI5NjczNDc=	datasette-json-html	simonw/datasette-json-html	0	9599	https://github.com/simonw/datasette-json-html	Datasette plugin for rendering HTML based on JSON values	0	2018-07-31T05:41:39Z	2022-03-15T04:54:15Z	2022-03-22T01:43:59Z		46	19	19	Python	1	1	1	1	0	1	0	0	0	apache-2.0	["datasette", "datasette-io", "datasette-plugin", "plugin"]	1	0	19	main	{"admin": false, "maintain": false, "push": false, "triage": false, "pull": false}			1	4	# datasette-json-html [![PyPI](https://img.shields.io/pypi/v/datasette-json-html.svg)](https://pypi.org/project/datasette-json-html/) [![Changelog](https://img.shields.io/github/v/release/simonw/datasette-json-html?include_prereleases&label=changelog)](https://github.com/simonw/datasette-json-html/releases) [![Tests](https://github.com/simonw/datasette-json-html/workflows/Test/badge.svg)](https://github.com/simonw/datasette-remote-metadata/actions?query=workflow%3ATest) [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/datasette-json-html/blob/main/LICENSE) Datasette plugin for rendering HTML based on JSON values, using the [render_cell plugin hook](https://docs.datasette.io/en/stable/plugin_hooks.html#render-cell-value-column-table-database-datasette). This plugin looks for cell values that match a very specific JSON format and converts them into HTML when they are rendered by the Datasette interface. ## Links { "href": "https://simonwillison.net/", "label": "Simon Willison" } Will be rendered as an `<a href="">` link: <a href="https://simonwillison.net/">Simon Willison</a> You can set a tooltip on the link using a `"title"` key: { "href": "https://simonwillison.net/", "label": "Simon Willison", "title": "My blog" } Produces: <a href="https://simonwillison.net/" title="My blog">Simon Willison</a> You can also include a description, which will be displayed below the link. If descriptions include newlines they will be converted to `<br>` elements: select json_object( "href", "https://simonwillison.net/", "label", "Simon Willison", "description", "This can contain" \|\| x'0a' \|\| "newlines" ) Produces: <strong><a href="https://simonwillison.net/">Simon Willison</a></strong><br>This can contain<br>newlines * [Literal JSON link demo](https://datasette-json-html.datasette.io/demo?sql=select+%27%7B%0D%0A++++%22href%22%3A+%22https%3A%2F%2Fsimonwillison.net%2F%22%2C%0D%0A++++%22label%22%3A+%22Simon+Willison%22%2C%0D%0A++++%22title%22%3A+%22My+blog%22%0D%0A%7D%27) ## List of links [ { "href": "https://simonwillison.net/", "label": "Simon Willison" }, { "href": "https://github.com/simonw/datasette", "label": "Datasette" } ] Will be rendered as a comma-separated list of `<a href="">` links: <a href="https://simonwillison.net/">Simon Willison</a>, <a href="https://github.com/simonw/datasette">Datasette</a> The `href` property must begin with `https://` or `http://` or `/`, to avoid potential XSS injection attacks (for example URLs that begin with `javascript:`). Lists of links cannot include `"description"` keys. * [Literal list of links demo](https://datasette-json-html.datasette.io/demo?sql=select+%27%5B%0D%0A++++%7B%0D%0A++++++++%22href%22%3A+%22https%3A%2F%2Fsimonwillison.net%2F%22%2C%0D%0A++++++++%22label%22%3A+%22Simon+Willison%22%0D%0A++++%7D%2C%0D%0A++++%7B%0D%0A++++++++%22href%22%3A+%22https%3A%2F%2Fgithub.com%2Fsimonw%2Fdatasette%22%2C%0D%0A++++++++%22label%22%3A+%22Datasette%22%0D%0A++++%7D%0D%0A%5D%27) ## Images The image tag is more complex. The most basic version looks like this: { "img_src": "https://placekitten.com/200/300" } This will render as: <img src="https://placekitten.com/200/300"> But you can also include one or more of `alt`, `caption`, `width` and `href`. If you include width or alt, they will be added as attributes: { "img_src": "https://placekitten.com/200/300", "alt": "Kitten", "width": 200 } Produces: <img src="https://placekitten.com/200/300" alt="Kitten" width="200"> * [Literal image demo](https://datasette-json-html.datasette.io/demo?sql=select+%27%7B%0D%0A++++%22img_src%22%3A+%22https%3A%2F%2Fplacekitten.com%2F200%2F300%22%2C%0D%0A++++%22alt%22%3A+%22Kitten%22%2C%0D%0A++++%22width%22%3A+200%0D%0A%7D%27) The `href` key will cause the image to be wrapped in a link: { "img_src": "https://placekitten.com/200/300", "href": "http://www.example.com" } Produces: <a href="http://www.example.com"> <img src="https://placekitten.com/200/300"> </a> The `caption` key wraps everything in a fancy figure/figcaption block: { "img_src": "https://placekitten.com/200/300", "caption": "Kitten caption" } Produces: <figure> <img src="https://placekitten.com/200/300"></a> <figcaption>Kitten caption</figcaption> </figure> ## Preformatted text You can use `{"pre": "text"}` to render text in a `<pre>` HTML tag: { "pre": "This\nhas\nnewlines" } Produces: <pre>This has newlines</pre> If the value attached to the `"pre"` key is itself a JSON object, that JSON will be pretty-printed: { "pre": { "this": { "object": ["is", "nested"] } } } Produces: <pre>{ "this": { "object": [ "is", "nested" ] } }</pre> * [Preformatted text with JSON demo](https://datasette-json-html.datasette.io/demo?sql=select+%27%7B%0D%0A++++%22pre%22%3A+%7B%0D%0A++++++++%22this%22%3A+%7B%0D%0A++++++++++++%22object%22%3A+%5B%22is%22%2C+%22nested%22%5D%0D%0A++++++++%7D%0D%0A++++%7D%0D%0A%7D%27) * [Preformatted text demo showing the Mandelbrot Set](https://datasette-json-html.datasette.io/demo?sql=WITH+RECURSIVE%0D%0A++xaxis%28x%29+AS+%28VALUES%28-2.0%29+UNION+ALL+SELECT+x%2B0.05+FROM+xaxis+WHERE+x%3C1.2%29%2C%0D%0A++yaxis%28y%29+AS+%28VALUES%28-1.0%29+UNION+ALL+SELECT+y%2B0.1+FROM+yaxis+WHERE+y%3C1.0%29%2C%0D%0A++m%28iter%2C+cx%2C+cy%2C+x%2C+y%29+AS+%28%0D%0A++++SELECT+0%2C+x%2C+y%2C+0.0%2C+0.0+FROM+xaxis%2C+yaxis%0D%0A++++UNION+ALL%0D%0A++++SELECT+iter%2B1%2C+cx%2C+cy%2C+xx-yy+%2B+cx%2C+2.0xy+%2B+cy+FROM+m+%0D%0A+++++WHERE+%28xx+%2B+yy%29+%3C+4.0+AND+iter%3C28%0D%0A++%29%2C%0D%0A++m2%28iter%2C+cx%2C+cy%29+AS+%28%0D%0A++++SELECT+max%28iter%29%2C+cx%2C+cy+FROM+m+GROUP+BY+cx%2C+cy%0D%0A++%29%2C%0D%0A++a%28t%29+AS+%28%0D%0A++++SELECT+group_concat%28+substr%28%27+.%2B%23%27%2C+1%2Bmin%28iter%2F7%2C4%29%2C+1%29%2C+%27%27%29+%0D%0A++++FROM+m2+GROUP+BY+cy%0D%0A++%29%0D%0ASELECT+json_object%28%27pre%27%2C+group_concat%28rtrim%28t%29%2Cx%270a%27%29%29+FROM+a%3B) using [this example](https://www.sqlite.org/lang_with.html#outlandish_recursive_query_examples) from the SQLite documentation ## Using these with SQLite JSON functions The most powerful way to make use of this plugin is in conjunction with SQLite's [JSON functions](https://www.sqlite.org/json1.html). For example: select json_object( "href", "https://simonwillison.net/", "label", "Simon Willison" ); [json_object() link demo](https://datasette-json-html.datasette.io/demo?sql=select+json_object%28%0D%0A++++%22href%22%2C+%22https%3A%2F%2Fsimonwillison.net%2F%22%2C%0D%0A++++%22label%22%2C+%22Simon+Willison%22%0D%0A%29%3B) You can use these functions to construct JSON objects that work with the plugin from data in a table: select id, json_object( "href", url, "label", text ) from mytable; * [Demo that builds links against a table](https://datasette-json-html.datasette.io/demo?sql=select+json_object%28%22href%22%2C+url%2C+%22label%22%2C+package%2C+%22title%22%2C+package+%7C%7C+%22+%22+%7C%7C+url%29+as+package+from+packages) The `json_group_array()` function is an aggregate function similar to `group_concat()` - it allows you to construct lists of JSON objects in conjunction with a `GROUP BY` clause. This means you can use it to construct dynamic lists of links, for example: select substr(package, 0, 12) as prefix, json_group_array( json_object( "href", url, "label", package ) ) as package_links from packages group by prefix * [Demo of json_group_array()](https://datasette-json-html.datasette.io/demo?sql=select%0D%0A++++substr%28package%2C+0%2C+12%29+as+prefix%2C%0D%0A++++json_group_array%28%0D%0A++++++++json_object%28%0D%0A++++++++++++%22href%22%2C+url%2C%0D%0A++++++++++++%22label%22%2C+package%0D%0A++++++++%29%0D%0A++++%29+as+package_links%0D%0Afrom+packages%0D%0Agroup+by+prefix) ## The `urllib_quote_plus()` SQL function Since this plugin is designed to be used with SQL that constructs the underlying JSON structure, it is likely you will need to construct dynamic URLs from results returned by a SQL query. This plugin registers a custom SQLite function called `urllib_quote_plus()` to help you do that. It lets you use Python's [urllib.parse.quote\_plus() function](https://docs.python.org/3/library/urllib.parse.html#urllib.parse.quote_plus) from within a SQL query. Here's an example of how you might use it: select id, json_object( "href", "/mydatabase/other_table?_search=" \|\| urllib_quote_plus(text), "label", text ) from mytable;	<div id="readme" class="md" data-path="README.md"><article class="markdown-body entry-content container-lg" itemprop="text"><h1 dir="auto"><a id="user-content-datasette-json-html" class="anchor" aria-hidden="true" href="#user-content-datasette-json-html"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>datasette-json-html</h1> <p dir="auto"><a href="https://pypi.org/project/datasette-json-html/" rel="nofollow"><img src="https://camo.githubusercontent.com/6781ecd1fa88174e5228eff74ade944f8f69a051d14df112d9dbc18bd3543551/68747470733a2f2f696d672e736869656c64732e696f2f707970692f762f6461746173657474652d6a736f6e2d68746d6c2e737667" alt="PyPI" data-canonical-src="https://img.shields.io/pypi/v/datasette-json-html.svg" style="max-width: 100%;"></a> <a href="https://github.com/simonw/datasette-json-html/releases"><img src="https://camo.githubusercontent.com/56a671c6595b71bd6d225b1c6d2ee0c5dbadd9bbbb44e4074ad4afe4c18905ce/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f762f72656c656173652f73696d6f6e772f6461746173657474652d6a736f6e2d68746d6c3f696e636c7564655f70726572656c6561736573266c6162656c3d6368616e67656c6f67" alt="Changelog" data-canonical-src="https://img.shields.io/github/v/release/simonw/datasette-json-html?include_prereleases&label=changelog" style="max-width: 100%;"></a> <a href="https://github.com/simonw/datasette-remote-metadata/actions?query=workflow%3ATest"><img src="https://github.com/simonw/datasette-json-html/workflows/Test/badge.svg" alt="Tests" style="max-width: 100%;"></a> <a href="https://github.com/simonw/datasette-json-html/blob/main/LICENSE"><img src="https://camo.githubusercontent.com/1698104e976c681143eb0841f9675c6f802bb7aa832afc0c7a4e719b1f3cf955/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d417061636865253230322e302d626c75652e737667" alt="License" data-canonical-src="https://img.shields.io/badge/license-Apache%202.0-blue.svg" style="max-width: 100%;"></a></p> <p dir="auto">Datasette plugin for rendering HTML based on JSON values, using the <a href="https://docs.datasette.io/en/stable/plugin_hooks.html#render-cell-value-column-table-database-datasette" rel="nofollow">render_cell plugin hook</a>.</p> <p dir="auto">This plugin looks for cell values that match a very specific JSON format and converts them into HTML when they are rendered by the Datasette interface.</p> <h2 dir="auto"><a id="user-content-links" class="anchor" aria-hidden="true" href="#user-content-links"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Links</h2> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "href": "https://simonwillison.net/", "label": "Simon Willison" }"><pre><code>{ "href": "https://simonwillison.net/", "label": "Simon Willison" } </code></pre></div> <p dir="auto">Will be rendered as an <code><a href=""></code> link:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<a href="https://simonwillison.net/">Simon Willison</a>"><pre><code><a href="https://simonwillison.net/">Simon Willison</a> </code></pre></div> <p dir="auto">You can set a tooltip on the link using a <code>"title"</code> key:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "href": "https://simonwillison.net/", "label": "Simon Willison", "title": "My blog" }"><pre><code>{ "href": "https://simonwillison.net/", "label": "Simon Willison", "title": "My blog" } </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<a href="https://simonwillison.net/" title="My blog">Simon Willison</a>"><pre><code><a href="https://simonwillison.net/" title="My blog">Simon Willison</a> </code></pre></div> <p dir="auto">You can also include a description, which will be displayed below the link. If descriptions include newlines they will be converted to <code><br></code> elements:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="select json_object( "href", "https://simonwillison.net/", "label", "Simon Willison", "description", "This can contain" \|\| x'0a' \|\| "newlines" )"><pre><code>select json_object( "href", "https://simonwillison.net/", "label", "Simon Willison", "description", "This can contain" \|\| x'0a' \|\| "newlines" ) </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<strong><a href="https://simonwillison.net/">Simon Willison</a></strong><br>This can contain<br>newlines"><pre><code><strong><a href="https://simonwillison.net/">Simon Willison</a></strong><br>This can contain<br>newlines </code></pre></div> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select+%27%7B%0D%0A++++%22href%22%3A+%22https%3A%2F%2Fsimonwillison.net%2F%22%2C%0D%0A++++%22label%22%3A+%22Simon+Willison%22%2C%0D%0A++++%22title%22%3A+%22My+blog%22%0D%0A%7D%27" rel="nofollow">Literal JSON link demo</a></li> </ul> <h2 dir="auto"><a id="user-content-list-of-links" class="anchor" aria-hidden="true" href="#user-content-list-of-links"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>List of links</h2> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="[ { "href": "https://simonwillison.net/", "label": "Simon Willison" }, { "href": "https://github.com/simonw/datasette", "label": "Datasette" } ]"><pre><code>[ { "href": "https://simonwillison.net/", "label": "Simon Willison" }, { "href": "https://github.com/simonw/datasette", "label": "Datasette" } ] </code></pre></div> <p dir="auto">Will be rendered as a comma-separated list of <code><a href=""></code> links:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<a href="https://simonwillison.net/">Simon Willison</a>, <a href="https://github.com/simonw/datasette">Datasette</a>"><pre><code><a href="https://simonwillison.net/">Simon Willison</a>, <a href="https://github.com/simonw/datasette">Datasette</a> </code></pre></div> <p dir="auto">The <code>href</code> property must begin with <code>https://</code> or <code>http://</code> or <code>/</code>, to avoid potential XSS injection attacks (for example URLs that begin with <code>javascript:</code>).</p> <p dir="auto">Lists of links cannot include <code>"description"</code> keys.</p> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select+%27%5B%0D%0A++++%7B%0D%0A++++++++%22href%22%3A+%22https%3A%2F%2Fsimonwillison.net%2F%22%2C%0D%0A++++++++%22label%22%3A+%22Simon+Willison%22%0D%0A++++%7D%2C%0D%0A++++%7B%0D%0A++++++++%22href%22%3A+%22https%3A%2F%2Fgithub.com%2Fsimonw%2Fdatasette%22%2C%0D%0A++++++++%22label%22%3A+%22Datasette%22%0D%0A++++%7D%0D%0A%5D%27" rel="nofollow">Literal list of links demo</a></li> </ul> <h2 dir="auto"><a id="user-content-images" class="anchor" aria-hidden="true" href="#user-content-images"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Images</h2> <p dir="auto">The image tag is more complex. The most basic version looks like this:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "img_src": "https://placekitten.com/200/300" }"><pre><code>{ "img_src": "https://placekitten.com/200/300" } </code></pre></div> <p dir="auto">This will render as:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<img src="https://placekitten.com/200/300">"><pre><code><img src="https://placekitten.com/200/300"> </code></pre></div> <p dir="auto">But you can also include one or more of <code>alt</code>, <code>caption</code>, <code>width</code> and <code>href</code>.</p> <p dir="auto">If you include width or alt, they will be added as attributes:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "img_src": "https://placekitten.com/200/300", "alt": "Kitten", "width": 200 }"><pre><code>{ "img_src": "https://placekitten.com/200/300", "alt": "Kitten", "width": 200 } </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<img src="https://placekitten.com/200/300" alt="Kitten" width="200">"><pre><code><img src="https://placekitten.com/200/300" alt="Kitten" width="200"> </code></pre></div> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select+%27%7B%0D%0A++++%22img_src%22%3A+%22https%3A%2F%2Fplacekitten.com%2F200%2F300%22%2C%0D%0A++++%22alt%22%3A+%22Kitten%22%2C%0D%0A++++%22width%22%3A+200%0D%0A%7D%27" rel="nofollow">Literal image demo</a></li> </ul> <p dir="auto">The <code>href</code> key will cause the image to be wrapped in a link:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "img_src": "https://placekitten.com/200/300", "href": "http://www.example.com" }"><pre><code>{ "img_src": "https://placekitten.com/200/300", "href": "http://www.example.com" } </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<a href="http://www.example.com"> <img src="https://placekitten.com/200/300"> </a>"><pre><code><a href="http://www.example.com"> <img src="https://placekitten.com/200/300"> </a> </code></pre></div> <p dir="auto">The <code>caption</code> key wraps everything in a fancy figure/figcaption block:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "img_src": "https://placekitten.com/200/300", "caption": "Kitten caption" }"><pre><code>{ "img_src": "https://placekitten.com/200/300", "caption": "Kitten caption" } </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<figure> <img src="https://placekitten.com/200/300"></a> <figcaption>Kitten caption</figcaption> </figure>"><pre><code><figure> <img src="https://placekitten.com/200/300"></a> <figcaption>Kitten caption</figcaption> </figure> </code></pre></div> <h2 dir="auto"><a id="user-content-preformatted-text" class="anchor" aria-hidden="true" href="#user-content-preformatted-text"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Preformatted text</h2> <p dir="auto">You can use <code>{"pre": "text"}</code> to render text in a <code><pre></code> HTML tag:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "pre": "This\nhas\nnewlines" }"><pre><code>{ "pre": "This\nhas\nnewlines" } </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<pre>This has newlines</pre>"><pre><code><pre>This has newlines</pre> </code></pre></div> <p dir="auto">If the value attached to the <code>"pre"</code> key is itself a JSON object, that JSON will be pretty-printed:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="{ "pre": { "this": { "object": ["is", "nested"] } } }"><pre><code>{ "pre": { "this": { "object": ["is", "nested"] } } } </code></pre></div> <p dir="auto">Produces:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="<pre>{ &#34;this&#34;: { &#34;object&#34;: [ &#34;is&#34;, &#34;nested&#34; ] } }</pre>"><pre><code><pre>{ &#34;this&#34;: { &#34;object&#34;: [ &#34;is&#34;, &#34;nested&#34; ] } }</pre> </code></pre></div> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select+%27%7B%0D%0A++++%22pre%22%3A+%7B%0D%0A++++++++%22this%22%3A+%7B%0D%0A++++++++++++%22object%22%3A+%5B%22is%22%2C+%22nested%22%5D%0D%0A++++++++%7D%0D%0A++++%7D%0D%0A%7D%27" rel="nofollow">Preformatted text with JSON demo</a></li> <li><a href="https://datasette-json-html.datasette.io/demo?sql=WITH+RECURSIVE%0D%0A++xaxis%28x%29+AS+%28VALUES%28-2.0%29+UNION+ALL+SELECT+x%2B0.05+FROM+xaxis+WHERE+x%3C1.2%29%2C%0D%0A++yaxis%28y%29+AS+%28VALUES%28-1.0%29+UNION+ALL+SELECT+y%2B0.1+FROM+yaxis+WHERE+y%3C1.0%29%2C%0D%0A++m%28iter%2C+cx%2C+cy%2C+x%2C+y%29+AS+%28%0D%0A++++SELECT+0%2C+x%2C+y%2C+0.0%2C+0.0+FROM+xaxis%2C+yaxis%0D%0A++++UNION+ALL%0D%0A++++SELECT+iter%2B1%2C+cx%2C+cy%2C+xx-yy+%2B+cx%2C+2.0xy+%2B+cy+FROM+m+%0D%0A+++++WHERE+%28xx+%2B+yy%29+%3C+4.0+AND+iter%3C28%0D%0A++%29%2C%0D%0A++m2%28iter%2C+cx%2C+cy%29+AS+%28%0D%0A++++SELECT+max%28iter%29%2C+cx%2C+cy+FROM+m+GROUP+BY+cx%2C+cy%0D%0A++%29%2C%0D%0A++a%28t%29+AS+%28%0D%0A++++SELECT+group_concat%28+substr%28%27+.%2B*%23%27%2C+1%2Bmin%28iter%2F7%2C4%29%2C+1%29%2C+%27%27%29+%0D%0A++++FROM+m2+GROUP+BY+cy%0D%0A++%29%0D%0ASELECT+json_object%28%27pre%27%2C+group_concat%28rtrim%28t%29%2Cx%270a%27%29%29+FROM+a%3B" rel="nofollow">Preformatted text demo showing the Mandelbrot Set</a> using <a href="https://www.sqlite.org/lang_with.html#outlandish_recursive_query_examples" rel="nofollow">this example</a> from the SQLite documentation</li> </ul> <h2 dir="auto"><a id="user-content-using-these-with-sqlite-json-functions" class="anchor" aria-hidden="true" href="#user-content-using-these-with-sqlite-json-functions"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Using these with SQLite JSON functions</h2> <p dir="auto">The most powerful way to make use of this plugin is in conjunction with SQLite's <a href="https://www.sqlite.org/json1.html" rel="nofollow">JSON functions</a>. For example:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="select json_object( "href", "https://simonwillison.net/", "label", "Simon Willison" );"><pre><code>select json_object( "href", "https://simonwillison.net/", "label", "Simon Willison" ); </code></pre></div> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select+json_object%28%0D%0A++++%22href%22%2C+%22https%3A%2F%2Fsimonwillison.net%2F%22%2C%0D%0A++++%22label%22%2C+%22Simon+Willison%22%0D%0A%29%3B" rel="nofollow">json_object() link demo</a></li> </ul> <p dir="auto">You can use these functions to construct JSON objects that work with the plugin from data in a table:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="select id, json_object( "href", url, "label", text ) from mytable;"><pre><code>select id, json_object( "href", url, "label", text ) from mytable; </code></pre></div> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select+json_object%28%22href%22%2C+url%2C+%22label%22%2C+package%2C+%22title%22%2C+package+%7C%7C+%22+%22+%7C%7C+url%29+as+package+from+packages" rel="nofollow">Demo that builds links against a table</a></li> </ul> <p dir="auto">The <code>json_group_array()</code> function is an aggregate function similar to <code>group_concat()</code> - it allows you to construct lists of JSON objects in conjunction with a <code>GROUP BY</code> clause.</p> <p dir="auto">This means you can use it to construct dynamic lists of links, for example:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="select substr(package, 0, 12) as prefix, json_group_array( json_object( "href", url, "label", package ) ) as package_links from packages group by prefix"><pre><code>select substr(package, 0, 12) as prefix, json_group_array( json_object( "href", url, "label", package ) ) as package_links from packages group by prefix </code></pre></div> <ul dir="auto"> <li><a href="https://datasette-json-html.datasette.io/demo?sql=select%0D%0A++++substr%28package%2C+0%2C+12%29+as+prefix%2C%0D%0A++++json_group_array%28%0D%0A++++++++json_object%28%0D%0A++++++++++++%22href%22%2C+url%2C%0D%0A++++++++++++%22label%22%2C+package%0D%0A++++++++%29%0D%0A++++%29+as+package_links%0D%0Afrom+packages%0D%0Agroup+by+prefix" rel="nofollow">Demo of json_group_array()</a></li> </ul> <h2 dir="auto"><a id="user-content-the-urllib_quote_plus-sql-function" class="anchor" aria-hidden="true" href="#user-content-the-urllib_quote_plus-sql-function"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>The <code>urllib_quote_plus()</code> SQL function</h2> <p dir="auto">Since this plugin is designed to be used with SQL that constructs the underlying JSON structure, it is likely you will need to construct dynamic URLs from results returned by a SQL query.</p> <p dir="auto">This plugin registers a custom SQLite function called <code>urllib_quote_plus()</code> to help you do that. It lets you use Python's <a href="https://docs.python.org/3/library/urllib.parse.html#urllib.parse.quote_plus" rel="nofollow">urllib.parse.quote_plus() function</a> from within a SQL query.</p> <p dir="auto">Here's an example of how you might use it:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="select id, json_object( "href", "/mydatabase/other_table?_search=" \|\| urllib_quote_plus(text), "label", text ) from mytable;"><pre><code>select id, json_object( "href", "/mydatabase/other_table?_search=" \|\| urllib_quote_plus(text), "label", text ) from mytable; </code></pre></div> </article></div>	1	public	0

Links from other tables

6 rows from repo in releases