44:["$","title",null,{"children":"Spirit Analysis via WebSQL | Wolfgang Maehr | Observable"}] 4c:T422,function createTable(data, tableName, numericFields) { const sqldata = data, table = tableName || "table_name", placeholders = Object.keys(sqldata[0]).fill('?').join(", "); // replace colons var hCols = Object.keys(sqldata[0]).map(d=> (`${sqlify(d)}${(numericFields.indexOf(sqlify(d)) > -1) ? " numeric" : ""}`)).join(", "); hCols = hCols.replace(/(:|_)/g," "); hCols = hCols.capitalize(); const headerCols = hCols.replace(/ /g,""); var iCols = Object.keys(sqldata[0]).map(d=> (sqlify(d))).join(", "), iCols = iCols.replace(/(:|_)/g," "); iCols = iCols.capitalize(); const insertCols = iCols.replace(/ /g,""); let sqlCreateTable = `CREATE TABLE ${table} (${headerCols});`; //console.log(sqlCreateTable, numericFields) db.run(sqlCreateTable) // create table let sqlInsertTemplate = `INSERT INTO ${table} (${insertCols}) VALUES (${placeholders});`; sqldata.forEach(insert => { //console.log(sqlInsertTemplate); db.run(sqlInsertTemplate, Object.values(insert)); // insert rows }) return db }4d:[] 48:{"id":"efd7c37e5d45f05c","slug":"spirit-analysis-via-websql","title":"Spirit Analysis via WebSQL","thumbnail":"7281266e124f717de06d0dd4a9d7dfb9ae53adadf88c1e2be867792a4c57369a","access_level":"public","create_time":"$D2019-11-25T11:10:29.785Z","update_time":"$D2019-12-06T13:27:12.423Z","view_time":null,"first_public_time":"$D2019-11-27T19:03:47.040Z","has_unpublished_edits":null,"fork":{"id":"0d68b0e90d504f6b","slug":"2018-spirit-ranking","title":"2019 Spirit Ranking","owner":{"id":"d77e44d4351a24a4","login":"njyo","name":"Wolfgang Maehr"}},"stars":0,"starred":false,"owner":{"type":"team","id":"d77e44d4351a24a4","login":"njyo","name":"Wolfgang Maehr","avatar_url":"https://avatars-next.observableusercontent.com/5ceede5ac925e657e6e1d0e4b96574d43f1a698867dc5b0c0bb30eb5660b7dcc","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":1040,"publish_version":1040,"role":null,"authored":false,"authors":[{"type":"individual","id":"10c93fd584ec23ef","login":"njyo","name":"Wolfgang Maehr","avatar_url":"https://avatars-next.observableusercontent.com/5ceede5ac925e657e6e1d0e4b96574d43f1a698867dc5b0c0bb30eb5660b7dcc","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"10c93fd584ec23ef","login":"njyo","name":"Wolfgang Maehr","avatar_url":"https://avatars-next.observableusercontent.com/5ceede5ac925e657e6e1d0e4b96574d43f1a698867dc5b0c0bb30eb5660b7dcc","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Spirit Analysis via WebSQL","cells":[{"id":0,"value":"md`\n# Spirit Analysis via WebSQL\n\nThis workbook loads [WFDF tournament SOTG data](https://github.com/njyo/sotg-data) and creates a WebSQL database to query interactively. You can also pass a SQL query in the URL via \\`?q=\\`. \nBased on [@nolan-ga's SQLite workbook](https://observablehq.com/@nolan-ga/sqlite-in-observable).\n\n### Todo\n- Add more event data\n- Add a column listing\n- Add a few simple visuals\n- Add raw game data to repo, to allow more in-depth analysis\n\nFollowing tables are available:`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":941,"value":"renderSqlQueryAsHtmlTable(\"SELECT Name AS `Table Name` FROM sqlite_master;\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":902,"value":"md`\n## Interactive querying:`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":904,"value":"// use this: https://observablehq.com/@tmcw/codemirror-inside-of-observable\nviewof sqlStatement = textarea({\n title: \"Write your own SQL query to execute\", \n placeholder: \"Type SQL statement here\",\n value: q,\n rows: 9,\n submit: \"Query\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":928,"value":"renderSqlQueryAsHtmlTable(sqlStatement,12);","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":934,"value":"{ \n var output = renderSqlQueryAsCsvTable(sqlStatement);\n if(output !== undefined) {\n return DOM.download(\n new Blob([d3.csvFormat(output)], {type: \"text/csv\"}),\n \"output.csv\"\n )\n } else {\n return md`Run a query`\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":295,"value":"md`\n## Appendix\n### Setup …\nImporting d3.js, sql.js, etc.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":910,"value":"import {text, textarea, checkbox} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":915,"value":"html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":6,"value":"d3 = require(\"d3@5\");","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":749,"value":"sql = require('sql.js@0.5.0/js/sql.js')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":757,"value":"function sqlify(str) { return str.replace(/ /g,\"_\").toLowerCase(); }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":886,"value":"db = new sql.Database();","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":818,"value":"md`### Support functions …`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":957,"value":"// helper function to capitalise strings\nString.prototype.capitalize = function() {\n return this.replace(/(?:^|\\s)\\S/g, function(a) { return a.toUpperCase(); });\n};","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":893,"value":"$4c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":820,"value":"// renders a table - code from @rumkin/tableview\nfunction tableView({columns, values}) {\n const table = this || html`

`;\n \n const {tHead, tBodies} = table;\n while(tHead.firstChild) {\n tHead.removeChild(tHead.firstChild);\n }\n tHead.appendChild(html`${columns.map(h => html`${cellView(h)}`)}`);\n \n const tBody = tBodies[0];\n \n while(tBody.firstChild) {\n tBody.removeChild(tBody.firstChild);\n }\n \n values.forEach((row, i) => {\n tBody.appendChild(html`${row.map(cell => html`${cellView(cell)}`)}`);\n });\n \n return table;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":823,"value":"// renders a cell\nfunction cellView(value) {\n if (value instanceof HTMLElement)\n return value;\n else\n return DOM.text(value);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":830,"value":"// returns the top k records\nfunction head(records, k=10) {\n if (records.values.length < k) { return records };\n let data = records\n data.values = data.values.filter((_, i) => i < k)\n return data;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":828,"value":"// renders an SQL statement as a HTML table\nfunction renderSqlQueryAsHtmlTable(sql, nRows=8) {\n const records = db.exec(sql)[0];\n \n if (records === undefined) { return md`*No records*`}\n \n const recordCount = records.values.length; \n \n if (records.values.length > nRows) {\n return html`${tableView(head(records, nRows))}
...showing first ${nRows} of ${recordCount} rows`\n } else {\n return tableView(head(records, nRows))\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":846,"value":"// returns an SQL query as a table\nfunction renderSqlQueryAsCsvTable(sql) {\n let resultSet = db.exec(sql)[0];\n \n if(!resultSet) { return undefined }\n \n let columns = resultSet.columns, \n rows=resultSet.values, \n n=columns.length,\n records = [];\n // return rows\n rows.map(row => { \n let obj={};\n for (let i=0; i