31:Tdd7,DatasetteClient = { async function DatasetteClient(baseUrl, options = {}) { const { fetchInit = {} } = options; // sanity check the baseUrl, make sure it // points to a Datasette db, not the entire instance const meta = await fetch(`${baseUrl}.json`, fetchInit) .then(r => r.json()) .catch(() => null); if (!meta) throw Error( 'Error connecting to Datasette instance. Make sure you are passing in a valid URL, and that CORS is enabled on the Datasette instance (with the --cors flag).' ); if (typeof meta.database !== 'string') throw Error( 'DatasetteClient url must point to a specific database, not the entire Datasette instance.' ); async function db(strings, ...values) { const { sql, params } = parseTemplate(strings, ...values); const fetchUrl = getFetchURL(baseUrl, sql, params); const data = await fetch(fetchUrl, fetchInit).then(r => r.json()); return data; } db.info = async () => { return fetch(`${baseUrl}.json`, fetchInit).then(r => r.json()); }; db.dry = (strings, ...values) => { return parseTemplate(strings, ...values); }; function tableRenderer(options = {}) { const { renderCell, extraColumns = [] } = options; return async function(a, ...b) { const { sql, params } = parseTemplate(a, ...b); const fetchUrl = getFetchURL(baseUrl, sql, params, 'objects'); const data = await fetch(fetchUrl, fetchInit).then(r => r.json()); const dsLink = `${baseUrl}?${(() => { const sp = new URLSearchParams(); sp.append('sql', sql); for (const key in params) { sp.append(key, params[key]); } return sp.toString(); })()}`; if (data.ok === false) return _renderError(data.title, data.status, data.error); if (data.rows.length === 0) return Object.assign(html`

No results!`, { value: data.rows }); const table = html`

${data.columns.map(c => html` ${data.rows.map( row => html` ${data.columns.map(column => { const result = renderCell && renderCell(row[column], column); return html`` )}

${DOM.text(c)}`)} ${extraColumns.map(c => html`	${DOM.text(c.name)}`)}
${result \|\| DOM.text(row[column])}`; })} ${extraColumns.map(c => html`	${c.render(row)}`)}

${_renderTableFooter( data.rows, data.columns, data.truncated, data.query_ms, dsLink )} `; const value = Object.assign(data.rows, { columns: data.columns }); return Object.assign(table, { value }); }; } db.table = tableRenderer(); db.table.options = tableRenderer; db.render = function(render) { return async function(a, ...b) { const { sql, params } = parseTemplate(a, ...b); const fetchUrl = getFetchURL(baseUrl, sql, params, 'objects'); const data = await fetch(fetchUrl, fetchInit).then(r => r.json()); if (data.ok === false) return _renderError(data.title, data.status, data.error); return Object.assign(render(data.rows, data.columns), { value: Object.assign(data.rows, { columns: data.columns }) }); }; }; return db; } return DatasetteClient; }32:[] 2f:{"title":"Datasette Client - Core","cells":[{"id":0,"value":"md`# Datasette Client - Core\n\nThis notebook has the core implementation of the DatasetteClient library, uses to query data from [Datasette instances](https://github.com/simonw/datasette) from Observable notebooks, with SQL. Contains docs and the API reference.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":172,"value":"md`~~~js\nimport { DatasetteClient } from '@asg017/datasette-client-core'\n~~~`","pinned":false,"hidden":false,"mode":"ojs"},{"id":109,"value":"md`### **DatasetteClient**(*url*)\n\nReturns a DatasetteClient object, \\`db\\`, used to query data from the given URL. Make sure your Datasette instance has CORS enabled (\\`--cors\\`), because this library uses the Datasette JSON API from a different origin. Also ensure that the URL points to a specific SQLite database, and not the entire Datasette instance. \n\nIn this example, the \\`congress-members\\` at the end of the URL is needed, since that is the name of the SQLite database we are querying from.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":178,"value":"db = DatasetteClient('https://ds.agarcia.dev/congress/congress-members')","pinned":true,"hidden":false,"mode":"ojs"},{"id":111,"value":"md`### db.**info**()\n\nReturns a JSON object containing data about the specified database. This comes from the \\`.json\\` view of the passed in URL, which for this example is from https://ds.agarcia.dev/congress/congress-members.json .`","pinned":false,"hidden":false,"mode":"ojs"},{"id":185,"value":"db.info()","pinned":true,"hidden":false,"mode":"ojs"},{"id":99,"value":"md`### **db**${\"\\\\`*string*\\\\`\"}\n\nReturns a JSON array of the query results, where \\`string\\` in the SQL code that is executed.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":194,"value":"db`SELECT * FROM members LIMIT 5`","pinned":true,"hidden":false,"mode":"ojs"},{"id":198,"value":"md`Parameters can be safely passed in with \\`\\${}\\`, which are sent as separate query parameters to limit SQL injections.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":202,"value":"{\n const party = \"Democrat\";\n const after = \"2019-01-01\";\n return db`SELECT * FROM terms WHERE party = ${party} and start > ${after}`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":92,"value":"md`### db.**table**${\"\\\\`*string*\\\\`\"}\n\nExecutes the SQL defined in \\`string\\`, and returns an HTML table representation of the data. Can be used alongside Observable's \\`viewof\\` to both display the table and access the raw values programmatically. `","pinned":false,"hidden":false,"mode":"ojs"},{"id":209,"value":"viewof currentCASenators = db.table`\n SELECT \n members.full_name,\n start,\n end\n FROM terms \n LEFT JOIN members ON members.bioguide = terms.member\n WHERE state = \"CA\" \n AND type = \"sen\" \n AND end > date('now')`","pinned":true,"hidden":false,"mode":"ojs"},{"id":262,"value":"currentCASenators","pinned":true,"hidden":false,"mode":"ojs"},{"id":347,"value":"viewof empty = db.table`select 1 limit 0;`","pinned":true,"hidden":false,"mode":"ojs"},{"id":349,"value":"empty","pinned":true,"hidden":false,"mode":"ojs"},{"id":102,"value":"md`### db.**render**(*fn*)\n\n\\`render\\`: \\`(rows, columns) => html\\`\n\nReturns a new tagged template literal that renders the results of a given SQL query based on the \\`fn\\` function. Use this to render new views of a given SQL query, if the default table isn't enough. For example, use this to display more customized tables, maps, visualizations, etc.\n\n\\`fn\\` should be a function that takes 2 parameters, the first being the returns rows from a SQL query (array of JS objects), and the second being an array of column name as string. `","pinned":false,"hidden":false,"mode":"ojs"},{"id":289,"value":"db.render(\n (rows, columns) => md`\n\nRows:\n~~~js\n${JSON.stringify(rows, null, 2)}\n~~~\n\nColumns:\n~~~js\n${JSON.stringify(columns, null, 2)}\n~~~\n\n`\n)`select * from members where gender = \"F\" limit 2`","pinned":true,"hidden":false,"mode":"ojs"},{"id":106,"value":"md`### db.**table.options**(*params*)\n\nAdd extra functionality to the default HTML table. Returns a tagged template literal that can be used for SQL queries.\n\n- \\`params\\`\n - \\`renderCell\\`: \\`(value, column => html\\`) A function that returns an HTML element to render a cell in a table. first parameter \\`value\\` is the value of the cell as returned by the DB query, \\`column\\` is the string name of the column of the cell. Return false-y value to allow for default rendering.\n - \\`extraColumns\\`: \\`{name:string, render: row => html}[]\\` An array of objects that specific new columns to add to the right of the original query's columns. \\`name\\` specifies the name of the new column (added as a \\`\\` element in the table's \\`\\`), \\`render\\` is a function that returns a new HTML representation for each cell in the column. The sole parameter, \\`row\\`, is a JavaScript Object where the keys are the column names, and values the values of the DB query for that row.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":338,"value":"md`Example: \\`renderCell\\`, add the column name to the cell.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"db.table.options({\n renderCell: (value, column) => html`

${value} (${column})`\n})`SELECT bioguide, first_name, last_name FROM members WHERE first_name LIKE \"ALEX%\" LIMIT 10;`","pinned":true,"hidden":false,"mode":"ojs"},{"id":342,"value":"md`Example: \\`extraColumns\\`, add one extra column with a JSON representation of the entire row.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":309,"value":"db.table.options({\n extraColumns: [\n { name: \"json\", render: row => html`

${JSON.stringify(row)}` }\n  ]\n})`SELECT bioguide, first_name, last_name FROM members WHERE first_name LIKE \"ALEX%\" LIMIT 5;`","pinned":true,"hidden":false,"mode":"ojs"},{"id":311,"value":"md`## Testing\n\nFeel free to ignore this section, just testing out all the different features.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":317,"value":"DatasetteClient('notexist')","pinned":true,"hidden":false,"mode":"ojs"},{"id":319,"value":"DatasetteClient('https://ds.agarcia.dev/congress')","pinned":true,"hidden":false,"mode":"ojs"},{"id":323,"value":"db`select not_exist from members;`","pinned":true,"hidden":false,"mode":"ojs"},{"id":325,"value":"db`select * from not_exist;`","pinned":true,"hidden":false,"mode":"ojs"},{"id":327,"value":"db`select syntax error`","pinned":true,"hidden":false,"mode":"ojs"},{"id":329,"value":"db``","pinned":true,"hidden":false,"mode":"ojs"},{"id":331,"value":"db.table`select not`","pinned":true,"hidden":false,"mode":"ojs"},{"id":272,"value":"md`---`","pinned":false,"hidden":false,"mode":"ojs"},{"id":314,"value":"md`## Implementation`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2,"value":"$31","pinned":true,"hidden":false,"mode":"ojs"},{"id":21,"value":"function _renderTableRows(rows = [], columns = [], { renderCell } = {}) {\n  return rows.map(\n    row =>\n      html`${columns.map(column => {\n        const result = renderCell && renderCell(row[column], column);\n        return html`${result || DOM.text(row[column])}`;\n      })}`\n  );\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":32,"value":"function _renderTableFooter(rows, columns, truncated, query_ms, datasetteLink) {\n  return html` \n      ${rows.length} rows, ${columns.length} columns ·\n      ${\n        truncated\n          ? html`Warning: Truncated results.`\n          : html``\n      } \n      Query took ${duration(query_ms)} ·\n      Open in Datasette `;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":13,"value":"function _renderError(title, status, error) {\n  return html` ${title} (${status})
${DOM.text(\n    error\n  )}`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":6,"value":"function parseTemplate(strings, ...values) {\n  let sql = \"\";\n  const params = {};\n  let paramsI = 0;\n  for (const [i, s] of strings.entries()) {\n    if (i < values.length) {\n      if (Array.isArray(values[i])) {\n        const sqlArrayParts = [];\n        for (const v of values[i]) {\n          const param = `p${paramsI++}`;\n          sqlArrayParts.push(`:${param}`);\n          params[param] = v;\n        }\n        sql += `${s}(${sqlArrayParts.join(',')})`;\n      } else {\n        const param = `p${paramsI++}`;\n        sql += `${s}:${param}`;\n        params[param] = values[i];\n      }\n    } else {\n      sql += s;\n    }\n  }\n\n  return { sql, params };\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":8,"value":"function getFetchURL(baseUrl, sql, params, _shape = \"array\") {\n  const searchParams = new URLSearchParams();\n  searchParams.append(\"sql\", sql);\n  for (const key in params) {\n    searchParams.append(key, params[key]);\n  }\n  searchParams.append(\"_shape\", _shape);\n  return `${baseUrl}.json?${searchParams.toString()}&_size=max&_stream=on`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":10,"value":"function duration(ms) {\n  if (ms < 1000) return `${Number.parseFloat(ms).toPrecision(3)}ms`;\n  const seconds = ms / 1000;\n  return `${Number.parseFloat(seconds).toPrecision(3)} seconds`;\n} ","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}