sql = ` with content as ( select id, 'entry' as type, title, created, slug, '

' || title || ' - ' || date(created) || '

' || body as html, 'null' as json, '' as external_url from blog_entry union all select id, 'blogmark' as type, link_title, created, slug, '

Link ' || date(created) || ' ' || link_title || ':

' || ' ' || replace(commentary, '\n', '
') || '

' as html, json_object( 'created', date(created), 'link_url', link_url, 'link_title', link_title, 'commentary', commentary, 'use_markdown', use_markdown ) as json, link_url as external_url from blog_blogmark union all select id, 'quotation' as type, source, created, slug, 'Quote ' || date(created) || '

' || replace(quotation, ' ', '
') || '

' || source || '

' as html, 'null' as json, source_url as external_url from blog_quotation union all select rowid, 'til' as type, title, created, 'null' as slug, '

TIL ' || date(created) || ' ' || title || ':' || ' ' || substr(html, 1, instr(html, '

') - 1) || ' …

' as html, 'null' as json, 'https://til.simonwillison.net/' || topic || '/' || slug as external_url from til ), collected as ( select id, type, title, case when type = 'til' then external_url else 'https://simonwillison.net/' || strftime('%Y/', created) || substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) || '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/' end as url, created, html, json, external_url, case when type = 'entry' then ( select json_group_array(tag) from blog_tag join blog_entry_tags on blog_tag.id = blog_entry_tags.tag_id where blog_entry_tags.entry_id = content.id ) when type = 'blogmark' then ( select json_group_array(tag) from blog_tag join blog_blogmark_tags on blog_tag.id = blog_blogmark_tags.tag_id where blog_blogmark_tags.blogmark_id = content.id ) when type = 'quotation' then ( select json_group_array(tag) from blog_tag join blog_quotation_tags on blog_tag.id = blog_quotation_tags.tag_id where blog_quotation_tags.quotation_id = content.id ) else '[]' end as tags from content where created >= date('now', '-' || :numdays || ' days') order by created desc ) select id, type, title, url, created, html, json, external_url, tags from collected order by case type when 'entry' then 0 else 1 end, case type when 'entry' then created else -strftime('%s', created) end desc; `