simonw/datasette-timeline.html Secret

## datasette-timeline.html
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Datasette timeline</title>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/marked/12.0.2/marked.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/dompurify/3.1.6/purify.min.js"></script>
  <style>
    body {
      font-family: Helvetica, Arial, sans-serif;
      margin: 0 auto;
      max-width: 46rem;
      padding: 1rem;
      color: #222;
    }
    h1 { margin: 0 0 0.75rem; }
    .controls {
      display: flex;
      gap: 0.5rem;
      flex-wrap: wrap;
      align-items: center;
      margin-bottom: 1rem;
    }
    #q {
      font: inherit;
      padding: 0.45rem 0.6rem;
      flex: 1 1 16rem;
      border: 1px solid #bbb;
      border-radius: 4px;
    }
    .filters { display: flex; gap: 0.75rem; font-size: 0.9rem; }
    .filters label { user-select: none; cursor: pointer; }
    #count { color: #666; font-size: 0.85rem; margin: 0 0 1rem; }
    .item {
      border-bottom: 1px solid #e3e3e3;
      padding: 0.9rem 0;
    }
    .meta { font-size: 0.8rem; color: #666; margin-bottom: 0.25rem; }
    .type {
      display: inline-block;
      padding: 0.05rem 0.45rem;
      border-radius: 999px;
      font-weight: bold;
      font-size: 0.75rem;
      text-transform: uppercase;
      letter-spacing: 0.03em;
    }
    .type-news { background: #fff2cc; color: #7a5b00; }
    .type-blog { background: #d8eefe; color: #0b5394; }
    .type-release { background: #ddf2dd; color: #1d6b2a; }
    .item h2 { font-size: 1.05rem; margin: 0 0 0.3rem; }
    .item h2 a { color: #0b5394; text-decoration: none; }
    .item h2 a:hover { text-decoration: underline; }
    .body { font-size: 0.9rem; line-height: 1.45; overflow-wrap: break-word; }
    .body :is(h1,h2,h3,h4) { font-size: 0.95rem; margin: 0.6rem 0 0.3rem; }
    .body p { margin: 0.4rem 0; }
    .body pre {
      background: #f6f6f6;
      padding: 0.6rem;
      overflow-x: auto;
      border-radius: 4px;
      font-size: 0.8rem;
    }
    .body code { background: #f2f2f2; padding: 0 0.2em; border-radius: 3px; }
    .body pre code { background: none; padding: 0; }
    .body img { max-width: 100%; }
    .body ul, .body ol { padding-left: 1.4rem; margin: 0.4rem 0; }
    details.more summary {
      cursor: pointer;
      color: #0b5394;
      font-size: 0.85rem;
      margin-top: 0.3rem;
    }
    .permalink { font-size: 0.8rem; }
    #status { color: #a00; white-space: pre-wrap; }
    .empty { color: #666; padding: 2rem 0; text-align: center; }
  </style>
</head>
<body>
  <h1>Datasette timeline</h1>
  <div class="controls">
    <input id="q" type="search" placeholder="Search news, blog posts and releases…">
    <div class="filters">
      <label><input type="checkbox" data-type="news" checked> News</label>
      <label><input type="checkbox" data-type="blog" checked> Blog</label>
      <label><input type="checkbox" data-type="release" checked> Releases</label>
    </div>
  </div>
  <p id="count"></p>
  <div id="results">Loading…</div>
  <pre id="status" hidden></pre>

  <script>
  const TRUNCATE_AT = 600; // characters of markdown before collapsing into <details>

  // Rows for the timeline, filtered by type booleans and search, newest first
  const TIMELINE_SQL = `
with combined as (
  select
    'news' as item_type,
    date as item_date,
    null as title,
    body,
    'https://datasette.io/news/' || date as url
  from news
  where cast(:news as text) = '1'
  union all
  select
    'blog' as item_type,
    substr(datetime_utc, 1, 10) as item_date,
    title,
    coalesce(summary, '') as body,
    'https://datasette.io' || path as url
  from blog_posts
  where cast(:blog as text) = '1'
  union all
  select
    'release' as item_type,
    substr(releases.published_at, 1, 10) as item_date,
    repos.name || ' ' || releases.tag_name as title,
    coalesce(releases.body, '') as body,
    releases.html_url as url
  from releases
    join repos on releases.repo = repos.id
  where releases.draft = 0
    and cast(:release as text) = '1'
)
select item_type, item_date, title, body, url
from combined
where coalesce(:q, '') = ''
   or title like '%' || :q || '%'
   or body like '%' || :q || '%'
order by item_date desc
limit 200
`;

  // Same filtering, but counts everything instead of stopping at the limit
  const COUNT_SQL = `
with combined as (
  select
    'news' as item_type,
    null as title,
    body
  from news
  where cast(:news as text) = '1'
  union all
  select
    'blog' as item_type,
    title,
    coalesce(summary, '') as body
  from blog_posts
  where cast(:blog as text) = '1'
  union all
  select
    'release' as item_type,
    repos.name || ' ' || releases.tag_name as title,
    coalesce(releases.body, '') as body
  from releases
    join repos on releases.repo = repos.id
  where releases.draft = 0
    and cast(:release as text) = '1'
)
select count(*) as total
from combined
where coalesce(:q, '') = ''
   or title like '%' || :q || '%'
   or body like '%' || :q || '%'
`;

  marked.setOptions({ gfm: true, breaks: false });

  function renderMarkdown(md) {
    const html = marked.parse(md || "");
    return DOMPurify.sanitize(html, { ADD_ATTR: ["target", "rel"] });
  }

  // Make all links in rendered markdown open in a new tab
  DOMPurify.addHook("afterSanitizeAttributes", node => {
    if (node.tagName === "A") {
      node.setAttribute("target", "_blank");
      node.setAttribute("rel", "noopener");
    }
  });

  let rows = [];
  let realCount = null; // true total from COUNT_SQL, not capped by the limit

  function currentParams() {
    const types = new Set(
      [...document.querySelectorAll(".filters input:checked")].map(cb => cb.dataset.type)
    );
    return {
      q: document.getElementById("q").value || "",
      news: types.has("news") ? "1" : "0",
      blog: types.has("blog") ? "1" : "0",
      release: types.has("release") ? "1" : "0"
    };
  }

  function display() {
    const container = document.getElementById("results");
    container.innerHTML = "";

    let countText;
    if (realCount === null) {
      // Count query unavailable; fall back to what we actually have
      countText = rows.length + " item" + (rows.length === 1 ? "" : "s");
    } else if (realCount > rows.length) {
      countText = "Showing " + rows.length.toLocaleString() + " of " +
        realCount.toLocaleString() + " items";
    } else {
      countText = realCount.toLocaleString() + " item" + (realCount === 1 ? "" : "s");
    }
    document.getElementById("count").textContent = countText;

    if (!rows.length) {
      const p = document.createElement("p");
      p.className = "empty";
      p.textContent = "No matching items.";
      container.appendChild(p);
      return;
    }

    for (const row of rows) {
      const item = document.createElement("article");
      item.className = "item";

      const meta = document.createElement("div");
      meta.className = "meta";
      const badge = document.createElement("span");
      badge.className = "type type-" + row.item_type;
      badge.textContent = row.item_type;
      meta.append(badge, " " + row.item_date);
      item.appendChild(meta);

      if (row.title) {
        const h2 = document.createElement("h2");
        const a = document.createElement("a");
        a.href = row.url;
        a.target = "_blank";
        a.rel = "noopener";
        a.textContent = row.title;
        h2.appendChild(a);
        item.appendChild(h2);
      }

      const md = row.body || "";
      const bodyDiv = document.createElement("div");
      bodyDiv.className = "body";

      if (md.length > TRUNCATE_AT) {
        // Cut at a paragraph or line break near the limit for a cleaner preview
        let cut = md.lastIndexOf("\n\n", TRUNCATE_AT);
        if (cut < TRUNCATE_AT / 2) cut = md.lastIndexOf("\n", TRUNCATE_AT);
        if (cut < TRUNCATE_AT / 2) cut = TRUNCATE_AT;
        bodyDiv.innerHTML = renderMarkdown(md.slice(0, cut));
        const details = document.createElement("details");
        details.className = "more";
        const summary = document.createElement("summary");
        summary.textContent = "Show more";
        const rest = document.createElement("div");
        rest.className = "body";
        rest.innerHTML = renderMarkdown(md.slice(cut));
        details.append(summary, rest);
        item.append(bodyDiv, details);
      } else {
        bodyDiv.innerHTML = renderMarkdown(md);
        item.appendChild(bodyDiv);
      }

      // News items have no title; give them a permalink line
      if (!row.title) {
        const p = document.createElement("p");
        p.className = "permalink";
        const a = document.createElement("a");
        a.href = row.url;
        a.target = "_blank";
        a.rel = "noopener";
        a.textContent = "News for " + row.item_date + " →";
        p.appendChild(a);
        item.appendChild(p);
      }

      container.appendChild(item);
    }
  }

  // Increments on every load(); responses from stale requests are discarded
  // so a slow earlier query can't overwrite the result of a newer one.
  let requestId = 0;

  async function load() {
    const id = ++requestId;
    const params = currentParams();
    const [result, countResult] = await Promise.all([
      datasette.query("content", TIMELINE_SQL, params),
      // If the count query fails for any reason, fall back to rendered count
      datasette.query("content", COUNT_SQL, params).catch(() => null)
    ]);
    if (id !== requestId) return; // a newer request has superseded this one
    rows = result.rows;
    realCount = countResult && countResult.rows.length
      ? countResult.rows[0].total
      : null;
    display();
  }

  function showError(error) {
    const el = document.getElementById("status");
    el.hidden = false;
    el.textContent = error.message || String(error);
    document.getElementById("results").textContent = "";
  }

  let timer;
  document.getElementById("q").addEventListener("input", () => {
    clearTimeout(timer);
    timer = setTimeout(() => load().catch(showError), 250);
  });

  // Type checkboxes re-run both queries; filtering happens in SQL
  document.querySelectorAll(".filters input").forEach(cb =>
    cb.addEventListener("change", () => load().catch(showError))
  );

  load().catch(showError);
  </script>
</body>
</html>
	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Datasette timeline</title>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/marked/12.0.2/marked.min.js"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/dompurify/3.1.6/purify.min.js"></script>
	<style>
	body {
	font-family: Helvetica, Arial, sans-serif;
	margin: 0 auto;
	max-width: 46rem;
	padding: 1rem;
	color: #222;
	}
	h1 { margin: 0 0 0.75rem; }
	.controls {
	display: flex;
	gap: 0.5rem;
	flex-wrap: wrap;
	align-items: center;
	margin-bottom: 1rem;
	}
	#q {
	font: inherit;
	padding: 0.45rem 0.6rem;
	flex: 1 1 16rem;
	border: 1px solid #bbb;
	border-radius: 4px;
	}
	.filters { display: flex; gap: 0.75rem; font-size: 0.9rem; }
	.filters label { user-select: none; cursor: pointer; }
	#count { color: #666; font-size: 0.85rem; margin: 0 0 1rem; }
	.item {
	border-bottom: 1px solid #e3e3e3;
	padding: 0.9rem 0;
	}
	.meta { font-size: 0.8rem; color: #666; margin-bottom: 0.25rem; }
	.type {
	display: inline-block;
	padding: 0.05rem 0.45rem;
	border-radius: 999px;
	font-weight: bold;
	font-size: 0.75rem;
	text-transform: uppercase;
	letter-spacing: 0.03em;
	}
	.type-news { background: #fff2cc; color: #7a5b00; }
	.type-blog { background: #d8eefe; color: #0b5394; }
	.type-release { background: #ddf2dd; color: #1d6b2a; }
	.item h2 { font-size: 1.05rem; margin: 0 0 0.3rem; }
	.item h2 a { color: #0b5394; text-decoration: none; }
	.item h2 a:hover { text-decoration: underline; }
	.body { font-size: 0.9rem; line-height: 1.45; overflow-wrap: break-word; }
	.body :is(h1,h2,h3,h4) { font-size: 0.95rem; margin: 0.6rem 0 0.3rem; }
	.body p { margin: 0.4rem 0; }
	.body pre {
	background: #f6f6f6;
	padding: 0.6rem;
	overflow-x: auto;
	border-radius: 4px;
	font-size: 0.8rem;
	}
	.body code { background: #f2f2f2; padding: 0 0.2em; border-radius: 3px; }
	.body pre code { background: none; padding: 0; }
	.body img { max-width: 100%; }
	.body ul, .body ol { padding-left: 1.4rem; margin: 0.4rem 0; }
	details.more summary {
	cursor: pointer;
	color: #0b5394;
	font-size: 0.85rem;
	margin-top: 0.3rem;
	}
	.permalink { font-size: 0.8rem; }
	#status { color: #a00; white-space: pre-wrap; }
	.empty { color: #666; padding: 2rem 0; text-align: center; }
	</style>
	</head>
	<body>
	<h1>Datasette timeline</h1>
	<div class="controls">
	<input id="q" type="search" placeholder="Search news, blog posts and releases…">
	<div class="filters">
	<label><input type="checkbox" data-type="news" checked> News</label>
	<label><input type="checkbox" data-type="blog" checked> Blog</label>
	<label><input type="checkbox" data-type="release" checked> Releases</label>
	</div>
	</div>
	<p id="count"></p>
	<div id="results">Loading…</div>
	<pre id="status" hidden></pre>

	<script>
	const TRUNCATE_AT = 600; // characters of markdown before collapsing into <details>

	// Rows for the timeline, filtered by type booleans and search, newest first
	const TIMELINE_SQL = `
	with combined as (
	select
	'news' as item_type,
	date as item_date,
	null as title,
	body,
	'https://datasette.io/news/' \|\| date as url
	from news
	where cast(:news as text) = '1'
	union all
	select
	'blog' as item_type,
	substr(datetime_utc, 1, 10) as item_date,
	title,
	coalesce(summary, '') as body,
	'https://datasette.io' \|\| path as url
	from blog_posts
	where cast(:blog as text) = '1'
	union all
	select
	'release' as item_type,
	substr(releases.published_at, 1, 10) as item_date,
	repos.name \|\| ' ' \|\| releases.tag_name as title,
	coalesce(releases.body, '') as body,
	releases.html_url as url
	from releases
	join repos on releases.repo = repos.id
	where releases.draft = 0
	and cast(:release as text) = '1'
	)
	select item_type, item_date, title, body, url
	from combined
	where coalesce(:q, '') = ''
	or title like '%' \|\| :q \|\| '%'
	or body like '%' \|\| :q \|\| '%'
	order by item_date desc
	limit 200
	`;

	// Same filtering, but counts everything instead of stopping at the limit
	const COUNT_SQL = `
	with combined as (
	select
	'news' as item_type,
	null as title,
	body
	from news
	where cast(:news as text) = '1'
	union all
	select
	'blog' as item_type,
	title,
	coalesce(summary, '') as body
	from blog_posts
	where cast(:blog as text) = '1'
	union all
	select
	'release' as item_type,
	repos.name \|\| ' ' \|\| releases.tag_name as title,
	coalesce(releases.body, '') as body
	from releases
	join repos on releases.repo = repos.id
	where releases.draft = 0
	and cast(:release as text) = '1'
	)
	select count(*) as total
	from combined
	where coalesce(:q, '') = ''
	or title like '%' \|\| :q \|\| '%'
	or body like '%' \|\| :q \|\| '%'
	`;

	marked.setOptions({ gfm: true, breaks: false });

	function renderMarkdown(md) {
	const html = marked.parse(md \|\| "");
	return DOMPurify.sanitize(html, { ADD_ATTR: ["target", "rel"] });
	}

	// Make all links in rendered markdown open in a new tab
	DOMPurify.addHook("afterSanitizeAttributes", node => {
	if (node.tagName === "A") {
	node.setAttribute("target", "_blank");
	node.setAttribute("rel", "noopener");
	}
	});

	let rows = [];
	let realCount = null; // true total from COUNT_SQL, not capped by the limit

	function currentParams() {
	const types = new Set(
	[...document.querySelectorAll(".filters input:checked")].map(cb => cb.dataset.type)
	);
	return {
	q: document.getElementById("q").value \|\| "",
	news: types.has("news") ? "1" : "0",
	blog: types.has("blog") ? "1" : "0",
	release: types.has("release") ? "1" : "0"
	};
	}

	function display() {
	const container = document.getElementById("results");
	container.innerHTML = "";

	let countText;
	if (realCount === null) {
	// Count query unavailable; fall back to what we actually have
	countText = rows.length + " item" + (rows.length === 1 ? "" : "s");
	} else if (realCount > rows.length) {
	countText = "Showing " + rows.length.toLocaleString() + " of " +
	realCount.toLocaleString() + " items";
	} else {
	countText = realCount.toLocaleString() + " item" + (realCount === 1 ? "" : "s");
	}
	document.getElementById("count").textContent = countText;

	if (!rows.length) {
	const p = document.createElement("p");
	p.className = "empty";
	p.textContent = "No matching items.";
	container.appendChild(p);
	return;
	}

	for (const row of rows) {
	const item = document.createElement("article");
	item.className = "item";

	const meta = document.createElement("div");
	meta.className = "meta";
	const badge = document.createElement("span");
	badge.className = "type type-" + row.item_type;
	badge.textContent = row.item_type;
	meta.append(badge, " " + row.item_date);
	item.appendChild(meta);

	if (row.title) {
	const h2 = document.createElement("h2");
	const a = document.createElement("a");
	a.href = row.url;
	a.target = "_blank";
	a.rel = "noopener";
	a.textContent = row.title;
	h2.appendChild(a);
	item.appendChild(h2);
	}

	const md = row.body \|\| "";
	const bodyDiv = document.createElement("div");
	bodyDiv.className = "body";

	if (md.length > TRUNCATE_AT) {
	// Cut at a paragraph or line break near the limit for a cleaner preview
	let cut = md.lastIndexOf("\n\n", TRUNCATE_AT);
	if (cut < TRUNCATE_AT / 2) cut = md.lastIndexOf("\n", TRUNCATE_AT);
	if (cut < TRUNCATE_AT / 2) cut = TRUNCATE_AT;
	bodyDiv.innerHTML = renderMarkdown(md.slice(0, cut));
	const details = document.createElement("details");
	details.className = "more";
	const summary = document.createElement("summary");
	summary.textContent = "Show more";
	const rest = document.createElement("div");
	rest.className = "body";
	rest.innerHTML = renderMarkdown(md.slice(cut));
	details.append(summary, rest);
	item.append(bodyDiv, details);
	} else {
	bodyDiv.innerHTML = renderMarkdown(md);
	item.appendChild(bodyDiv);
	}

	// News items have no title; give them a permalink line
	if (!row.title) {
	const p = document.createElement("p");
	p.className = "permalink";
	const a = document.createElement("a");
	a.href = row.url;
	a.target = "_blank";
	a.rel = "noopener";
	a.textContent = "News for " + row.item_date + " →";
	p.appendChild(a);
	item.appendChild(p);
	}

	container.appendChild(item);
	}
	}

	// Increments on every load(); responses from stale requests are discarded
	// so a slow earlier query can't overwrite the result of a newer one.
	let requestId = 0;

	async function load() {
	const id = ++requestId;
	const params = currentParams();
	const [result, countResult] = await Promise.all([
	datasette.query("content", TIMELINE_SQL, params),
	// If the count query fails for any reason, fall back to rendered count
	datasette.query("content", COUNT_SQL, params).catch(() => null)
	]);
	if (id !== requestId) return; // a newer request has superseded this one
	rows = result.rows;
	realCount = countResult && countResult.rows.length
	? countResult.rows[0].total
	: null;
	display();
	}

	function showError(error) {
	const el = document.getElementById("status");
	el.hidden = false;
	el.textContent = error.message \|\| String(error);
	document.getElementById("results").textContent = "";
	}

	let timer;
	document.getElementById("q").addEventListener("input", () => {
	clearTimeout(timer);
	timer = setTimeout(() => load().catch(showError), 250);
	});

	// Type checkboxes re-run both queries; filtering happens in SQL
	document.querySelectorAll(".filters input").forEach(cb =>
	cb.addEventListener("change", () => load().catch(showError))
	);

	load().catch(showError);
	</script>
	</body>
	</html>
No results found