2c:null 2d:["$","title",null,{"children":"Datasette table diagram | Simon Willison | Observable"}] 32:T4e5,chart = { const height = 400; const links = data.links.map(d => Object.create(d)); const nodes = data.nodes.map(d => Object.create(d)); const simulation = d3.forceSimulation(nodes) .force("link", d3.forceLink(links).id(d => d.id)) .force("charge", d3.forceManyBody()) .force("x", d3.forceX()) .force("y", d3.forceY()); const svg = d3.create("svg") .attr("viewBox", [-width / 2, -height / 2, width, height]); const link = svg.append("g") .attr("stroke", "#999") .attr("stroke-opacity", 0.6) .selectAll("line") .data(links) .join("line") .attr("stroke-width", d => Math.sqrt(d.value)); const node = svg.append("g") .attr("stroke", "#fff") .attr("stroke-width", 1.5) .selectAll("circle") .data(nodes) .join("circle") .attr("r", 5) .call(drag(simulation)); node.append("title") .text(d => d.id); simulation.on("tick", () => { link .attr("x1", d => d.source.x) .attr("y1", d => d.source.y) .attr("x2", d => d.target.x) .attr("y2", d => d.target.y); node .attr("cx", d => d.x) .attr("cy", d => d.y); }); invalidation.then(() => simulation.stop()); return svg.node(); }33:[] 30:{"id":"d0e02d553044cb1d","slug":"datasette-table-diagram","title":"Datasette table diagram","thumbnail":"ed19740b3a930ec8a68608becad15e92254c321ddd000966351f6632c20dde01","access_level":"public","create_time":"$D2020-05-07T02:04:19.933Z","update_time":"$D2021-04-05T04:01:24.726Z","view_time":null,"first_public_time":"$D2020-05-07T02:47:08.704Z","has_unpublished_edits":null,"fork":false,"stars":22,"starred":false,"owner":{"type":"team","id":"3b974c2db4452cfc","login":"simonw","name":"Simon Willison","avatar_url":"https://avatars-next.observableusercontent.com/78a40181b372a172b86abe69479cab717df036b9cdcbaab5a197e45a76ffb4f3","tier":"starter_2024"},"version":158,"publish_version":158,"role":null,"authored":false,"authors":[{"type":"individual","id":"dc026342127b807b","login":"simonw","name":"Simon Willison","avatar_url":"https://avatars-next.observableusercontent.com/78a40181b372a172b86abe69479cab717df036b9cdcbaab5a197e45a76ffb4f3","tier":null}],"members":[{"type":"individual","id":"dc026342127b807b","login":"simonw","name":"Simon Willison","avatar_url":"https://avatars-next.observableusercontent.com/78a40181b372a172b86abe69479cab717df036b9cdcbaab5a197e45a76ffb4f3","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":14,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Datasette table diagram","cells":[{"id":0,"value":"md`# Datasette table diagram\n\nGiven a URL to a database within a Datasette instance, this will render a DOT diagram and a force-directed graph showing the foreign key relationships between all of the tables.\n\nBased on [@observablehq/graphviz](https://observablehq.com/@observablehq/graphviz) and [@d3/disjoint-force-directed-graph](https://observablehq.com/@d3/disjoint-force-directed-graph)\n\nThanks Thomas Ballinger for [sharing the code that renders](https://observablehq.com/d/a654b8756ea5061b) a DOT diagram for this!\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":44,"value":"viewof form_input = form(html``)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":145,"value":"html`You can share a link to this diagram`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":89,"value":"dot`digraph {\n rankdir=LR\n node [fontname=\"var(--sans-serif)\" fontsize=12 shape=record]\n ${tables\n .map(\n t =>\n `${t} [label=\"${t}|${columns\n .filter(c => c.table === t)\n .map(c => `<${c.column}> ${c.column} \\\\l`)\n .join(\"|\")}\"];`\n )\n .join(\"\\n \")}\n ${[\n ...new Set(\n fks.map(\n r => `${r.table_from}:${r.column_from} -> ${r.table_to}:${r.column_to}`\n )\n )\n ].join(\"\\n \")}\n}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"md`## Force directed graph`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":4,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":138,"value":"params = new URL(location).searchParams","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":140,"value":"initial_url = params.get('url') || 'https://github-to-sqlite.dogsheep.net/github'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":93,"value":"tables = [...new Set(fks.map(x => x.table_from))]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":95,"value":"columns = fks.map(x => ({ table: x.table_from, column: x.column_from }))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":55,"value":"sql = `\nselect\n sqlite_master.name as table_from,\n fk_info.[from] as column_from,\n fk_info.[table] as table_to,\n fk_info.[to] as column_to\nfrom\n sqlite_master\n join pragma_foreign_key_list(sqlite_master.name) as fk_info\norder by\n sqlite_master.name\n`;","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"fks = await fetch(`${form_input.database_url}.json?sql=${encodeURIComponent(sql)}&_shape=array`).then(r => r.json())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":20,"value":"data = {\n let nodes = new Set();\n let edges = new Set();\n fks.forEach(fk => {\n nodes.add(fk.table_from);\n nodes.add(fk.table_to);\n edges.add(`${fk.table_from}:${fk.table_to}`);\n });\n return {\n \"nodes\": Array.from(nodes).map(n => ({id: n})),\n \"links\": Array.from(edges).map(e => ({source: e.split(':')[0], target: e.split(':')[1]}))\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"drag = simulation => {\n \n function dragstarted(d) {\n if (!d3.event.active) simulation.alphaTarget(0.3).restart();\n d.fx = d.x;\n d.fy = d.y;\n }\n \n function dragged(d) {\n d.fx = d3.event.x;\n d.fy = d3.event.y;\n }\n \n function dragended(d) {\n if (!d3.event.active) simulation.alphaTarget(0);\n d.fx = null;\n d.fy = null;\n }\n \n return d3.drag()\n .on(\"start\", dragstarted)\n .on(\"drag\", dragged)\n .on(\"end\", dragended);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":8,"value":"d3 = require(\"d3@5\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":60,"value":"import {form} from \"@mbostock/form-input\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":91,"value":"dot = require(\"@observablehq/graphviz@0.2\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1"}} 31:"$30:body"