navigatable database schema in one sql query

[lovasoa]

I recently stumbled upon sqlite-schema-diagram, which was posted on hacker news. I wondered whether the same could be done more easily with SQLPage... Here is the result:

index.sql

select 'list' as component;
select
  name as title,
  printf('%d columns', ncol) as description,
  'table.sql?table='||name as link
from pragma_table_list()

table.sql

select 'table' as component, 'Foreign keys' as markdown;

select *, (
  select
    group_concat(
      printf('[%s.%s](?table=%s)', fk."table",fk."to",fk."table"),
      ', '
  )
  from pragma_foreign_key_list($table) fk
  where col.name = fk."from"
) as "Foreign keys"
from pragma_table_info($table) col;