31:T65a,async function diagram(db) { const tables = (await db.query("SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'")) .filter(({sql}) => sql) .map(({sql}) => sqlParser(sql)) .map((ast) => ast.statement[0]) .filter(statement => statement.format === 'table' && !statement.name?.name.includes('_fts_')) // filter out hidden system tables const tableNodes = tables.map((table) => `"${table.name.name}" [shape=none, label=< ${table.definition.filter(({variant}) => variant === 'column').map(({name, datatype}) => ` `).join('')}}

${table.name.name}
${name}	${datatype?.variant} ${datatype?.args?.expression?.[0]?.value ? `(${datatype?.args?.expression?.[0]?.value})` : ''}

>]`).join('\n'); const tableReferences = (await db.query(` SELECT sqlite_master.name as from_table, fk_info.[from] as from_column, fk_info.[table] as to_table, fk_info.[to] as to_column FROM sqlite_master JOIN pragma_foreign_key_list(sqlite_master.name) as fk_info ORDER BY sqlite_master.name `)).map((reference) => `${reference.from_table}:${reference.from_column} -> ${reference.to_table}:${reference.to_column}`).join('\n'); return dot`digraph "ERD" { node [fontsize=12] rankdir = LR ${tableNodes} ${tableReferences} }`; }32:[] 2f:{"title":"Datasette ER Diagram","cells":[{"id":0,"value":"# Datasette ER Diagram","pinned":false,"hidden":false,"mode":"md"},{"id":76,"value":"## Datasette Source\n\n**Enter [Datasette](https://datasette.io/examples) Url:**","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"viewof dataUrl = Inputs.text({\n label: html`Datasette Url`,\n placeholder: 'type datasette url and click fetch', \n value: dataUrlParam,\n submit: 'fetchData'\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":7,"value":"dataUrlParam = params.get('dataUrl') || 'https://github-to-sqlite.dogsheep.net/github'","pinned":false,"hidden":false,"mode":"ojs"},{"id":14,"value":"params = new URL(location).searchParams","pinned":false,"hidden":false,"mode":"ojs"},{"id":70,"value":"## Datasette Client\n\n[Datasette Client](https://observablehq.com/@ambassadors/datasette-client) notebook provides convenient [DatabaseClient](https://observablehq.com/@observablehq/database-client-specification) API implementation to load datasettes.","pinned":false,"hidden":false,"mode":"md"},{"id":82,"value":"import {DatasetteClient} from '@ambassadors/datasette-client'","pinned":false,"hidden":false,"mode":"ojs"},{"id":85,"value":"data = new DatasetteClient(dataUrl)","pinned":false,"hidden":false,"mode":"ojs"},{"id":51,"value":"## Datasette Diagram\n\nSee the original [Datasette Table Diagram](https://observablehq.com/@simonw/datasette-table-diagram) notebook for a simple datasette diagram example with only foreign key table column references.\n\n**Note**: Click on the `erDiagram` cell vertical **...** menu below to download it in SVG or PNG format for a full view of large Datasette ER diagrams.","pinned":false,"hidden":false,"mode":"md"},{"id":98,"value":"erDiagram = diagram(data)","pinned":false,"hidden":false,"mode":"ojs"},{"id":93,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":95,"value":"sqlParser = require('sqlite-parser')","pinned":false,"hidden":false,"mode":"ojs"},{"id":61,"value":"import {dot} from '@gordonsmith/graphviz'","pinned":false,"hidden":false,"mode":"ojs"},{"id":38,"value":"## Datasette Tables","pinned":false,"hidden":false,"mode":"md"},{"id":104,"value":"tables = await data.query(\"SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":111,"value":"tableStatements = tables.filter((table) => table.sql)\n .map((table) => sqlParser(table.sql))\n .map((ast) => ast.statement[0]);","pinned":false,"hidden":false,"mode":"ojs"},{"id":114,"value":"tableDefinitions = tableStatements.filter(statement => statement.format === 'table')\n .map(statement => ({table: statement.name?.name, columns: statement.definition}))\n .filter(definition => !definition.table.includes('_fts_')) // filter out hidden system table","pinned":false,"hidden":false,"mode":"ojs"},{"id":183,"value":"tableConstraints = tableDefinitions.map(table => ({\n table: table.table,\n constraints: table.columns.filter(column => column.definition.length > 0)\n .map(column => ({\n column: column.name,\n type: column.definition[0].variant,\n contstraint: column.definition[0]\n }))\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":199,"value":"primaryKeys = tableConstraints.map(table => ({\n table: table.table,\n primaryKey: table.constraints.filter(constraint => constraint.type === 'primary key')[0].column\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":203,"value":"foreignKeys = tableConstraints.map(table => ({\n table: table.table,\n foreignKeys: table.constraints.filter(constraint => constraint.type === 'foreign key')\n}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":122,"value":"Inputs.table(tableDefinitions, {\n format: {\n columns: d => d.length\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":208,"value":"SELECT\n sqlite_master.name as from_table,\n fk_info.[from] as from_column,\n fk_info.[table] as to_table,\n fk_info.[to] as to_column\nFROM\n sqlite_master\n JOIN pragma_foreign_key_list(sqlite_master.name) as fk_info\nORDER BY\n sqlite_master.name","pinned":false,"hidden":false,"mode":"sql","output":"foreignKeysInfo","database":"var:data"},{"id":41,"value":"data.describe(tableDefinitions[0].table)","pinned":true,"hidden":false,"mode":"ojs"},{"id":45,"value":"SELECT * FROM users","pinned":true,"hidden":false,"mode":"sql","output":"dataTable","database":"var:data"}],"files":"$W32","stdlib":"1","titling":"auto"}