28:null 29:["$","title",null,{"children":"Introducing sqlite-path: A SQLite extension for parsing and generating paths | Alex Garcia | Observable"}] 2e:T577,


create table carmack_plans as
  select
   -- "john-carmack-plan-master/archive/1996-02-18.md" -> "1996-02-18"
   substr(
      name, 
      length("john-carmack-plan-master/archive/") + 1, 
      length("YYYY-MM-DD")
    ) as date,
    cast(data as text) as contents
  from zipfile(
    http_get_body('https://github.com/oliverbenns/john-carmack-plan/archive/refs/heads/master.zip')
  )
  where name like "%archive/%.md";

2f:[{"id":"bb38983953c8c58370561fe092afcb3e500e5e31334d02395a64e73317447098530c38cb74d441f1f7d4e7722c2153c6780e94885aa9bc27453af989402d3946","href":"https://static.observableusercontent.com/files/bb38983953c8c58370561fe092afcb3e500e5e31334d02395a64e73317447098530c38cb74d441f1f7d4e7722c2153c6780e94885aa9bc27453af989402d3946","name":"react-archive.db","type":"application/x-sqlite3","lastModified":1661208873348.482,"size":237568},{"id":"cf9ac27f43464ccccf66c74883c6583ab1808496a8ea23271d590597b61a7b4aaa213bd125d8af5a3a98c27821bbc70bb686a879744494d1cacd9cfc3a878dae","href":"https://static.observableusercontent.com/files/cf9ac27f43464ccccf66c74883c6583ab1808496a8ea23271d590597b61a7b4aaa213bd125d8af5a3a98c27821bbc70bb686a879744494d1cacd9cfc3a878dae","name":"DALL·E 2022-08-25 19.05.00 - wooden bridge over a river, animation, playful.png","type":"image/png","lastModified":1661479533399.294,"size":1560277},{"id":"30e254740797c2bd2cd21153fb7af0d4ffa71d8d986dd721dd7fff7029e2cbb8f25f5c55cca663e489043827d39bb7005cfb45ecb91300d638f4c6d46fef7010","href":"https://static.observableusercontent.com/files/30e254740797c2bd2cd21153fb7af0d4ffa71d8d986dd721dd7fff7029e2cbb8f25f5c55cca663e489043827d39bb7005cfb45ecb91300d638f4c6d46fef7010","name":"DALL·E 2022-08-25 19.04.52 - Incredible, wooden bridge over a river, matte painting, dramatic lighting.png","type":"image/png","lastModified":1661479531979.566,"size":1878200},{"id":"6d2d01574817a733133e92048e74f77046f19555400e1431b57e4d38b1302cfb505387daa82db1afd61c25364cfd87fbb9381f7ab0c1986b3abc36bb264a7993","href":"https://static.observableusercontent.com/files/6d2d01574817a733133e92048e74f77046f19555400e1431b57e4d38b1302cfb505387daa82db1afd61c25364cfd87fbb9381f7ab0c1986b3abc36bb264a7993","name":"path0-sqljs.js","type":"application/javascript","lastModified":1661535871551.549,"size":292558},{"id":"56becb31f119d7b4ce5a8b0c7c48f121b620636e5a79648f508f38930ca917beb68fc3e41d10032584dcc824999d453e214211e669be9022674a4ec9761666ad","href":"https://static.observableusercontent.com/files/56becb31f119d7b4ce5a8b0c7c48f121b620636e5a79648f508f38930ca917beb68fc3e41d10032584dcc824999d453e214211e669be9022674a4ec9761666ad","name":"path0-sqljs.wasm","type":"application/wasm","lastModified":1661535862936.935,"size":569694},{"id":"3cd5070c7b607e4e8173919e6a315d82650130c850842be6b1b26fb59eeda41ece13995316b9e0a8aa9295f9031061822e15186522794913edd1b01cabbde5f0","href":"https://static.observableusercontent.com/files/3cd5070c7b607e4e8173919e6a315d82650130c850842be6b1b26fb59eeda41ece13995316b9e0a8aa9295f9031061822e15186522794913edd1b01cabbde5f0","name":"DALL·E 2022-08-25 19.04.40 - wooden bridge over a river, animation, playful.png","type":"image/png","lastModified":1661479530308.88,"size":1291583}] 2c:{"id":"8a731011dc79e12c","slug":"introducing-sqlite-path","title":"Introducing sqlite-path: A SQLite extension for parsing and generating paths","thumbnail":"bdfabc88f46def80b92828764ca65e445645aaff3eba4768df39fc13a27bf927","access_level":"public","create_time":"$D2022-08-22T06:13:22.500Z","update_time":"$D2022-08-26T18:25:41.432Z","view_time":null,"first_public_time":"$D2022-08-26T18:17:43.618Z","has_unpublished_edits":null,"fork":false,"stars":2,"starred":false,"owner":{"type":"team","id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":"starter_2024"},"version":319,"publish_version":319,"role":null,"authored":false,"authors":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null}],"members":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":{"id":"ec34b19c7b7564c5","slug":"sqlite-blog","title":"SQLite Blog","owner":{"id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia"}},"type":"notebook","body":{"title":"Introducing sqlite-path: A SQLite extension for parsing and generating paths","cells":[{"id":0,"value":"# Introducing sqlite-path: A SQLite extension for parsing and generating paths\n\n[sqlite-path](https://github.com/asg017/sqlite-path) is a new SQLite extension based on [cwalk](https://github.com/likle/cwalk). It allows you to extract information from filepaths for easier analysis than using builtin functions like `substr()`. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":282,"value":"```sql\n.load ./path0\nselect path_dirname('foo/bar.txt'); -- 'foo/'\nselect path_basename('foo/bar.txt'); -- 'bar.txt'\nselect path_extension('foo/bar.txt'); -- '.txt'\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":207,"value":"

\n ${pics.map(d=>htl.html`

`)}\n

\n ","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":212,"value":" DALL·E attempts for this post's thumbnail","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":200,"value":"
","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":10,"value":"## Demo\n\nsqlite-path can be compiled as WASM for use in the browser. Similar to [sqlite-lines](https://observablehq.com/@asg017/introducing-sqlite-lines), we can use it with [Observable's SQL Cells](https://observablehq.com/@observablehq/sql) and their [SQLite support](https://observablehq.com/@observablehq/sqlite) to use `sqlite-path` functions natively in Observable notebooks.\n\n\nSee the [sqlite-lines's WASM section](https://observablehq.com/@asg017/introducing-sqlite-lines?collection=@asg017/sqlite-blog#cell-8) for a full walkthrough, but TL;DR the [sqlite-path Releases page](https://github.com/asg017/sqlite-lines/releases) has pre-compiled JavaScript and WASM builds of [sql.js](https://github.com/sql-js/sql.js) with sqlite-path functions built-in. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":33,"value":"db = DemoSQLitePathDatabaseClient(FileAttachment(\"react-archive.db\").blob())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":38,"value":"select path_version(), path_debug();","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":157,"value":"Voila! We have a small SQLite database loaded in this notebook, and all [sqlite-path functions](https://github.com/asg017/sqlite-path/blob/main/docs.md) are available. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":41,"value":"select * from pragma_function_list where name like 'path%' order by 1;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":220,"value":"Let's play with a test dataset - I downloaded the source code for [React](https://github.com/facebook/react) and made a small demo table with the following code:\n\n```sql\ncreate table react_files as\n select \n name, \n length(data) as size \n from zipfile(readfile('react-main.zip'));\n```\n\nAnd here's the resulting table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":223,"value":"select * from react_files limit 20;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":295,"value":"Let's do some analysis! sqlite-path can make this easy, even with deeply nested paths.\n\nWhich top-level directories take up the most space?","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":69,"value":"select \n path_at(name, 1) top_directory, \n sum(size) sum_bytes\nfrom react_files\n-- filter out top-level files\nwhere (select count(*) from path_parts(name)) > 2\ngroup by 1\norder by 2 desc;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":300,"value":"What are the largest files under the `packages` directory?","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":90,"value":"select name, size\nfrom react_files\nwhere path_at(name, 1) = 'packages'\norder by 2 desc;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":306,"value":"How about the largest files that aren't JavaScript, yarn.lock, or JSON files?","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":302,"value":"select \n name,\n size\nfrom react_files\nwhere path_extension(name) not in ('.js', '.lock', '.json')\norder by 2 desc;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":314,"value":"Try it yourself! Which top-level directories have the most Markdown (`.md`) files?","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":311,"value":"select name\nfrom react_files;","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:db"},{"id":161,"value":"## Use-Cases\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":11,"value":"### With the SQLite CLI's fileio functions (readfile, writefile, fsdir)\n\nThe SQLite CLI comes with [I/O functions](https://www.sqlite.org/cli.html#file_i_o_functions) built-in, with the following functions:\n\n- `readfile(path)` - Read the file at _path_ and return a BLOB of its contents\n- `writefile(path, data)` - Write _data_ to the given file at _path_\n- `fsdir(path)` - Table function to iterate over all files in a directory recursively\n\nWe can use sqlite-path to easily and safely work with these functions. For example, if we had a table `photos` and wanted to write them to the `./photos` directory, we could run:\n\n```sql\nselect \n writefile(\n path_join('photos', printf('%s.png', name)), \n data\n )\nfrom photos;\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":264,"value":"\n\nOr create a histogram of the count of file extensions in the current directory, using fsdir().\n\n\n```sql\nselect\n path_extension(name),\n count(*),\n printf('%.*c', count(*), '*') as bar\nfrom fsdir('.')\nwhere path_extension(name) is not null\ngroup by 1\norder by 2 desc\nlimit 6;\n\n/*\n┌──────────────────────┬──────────┬────────────────────────────────────┐\n│ path_extension(name) │ count(*) │ bar │\n├──────────────────────┼──────────┼────────────────────────────────────┤\n│ .md │ 34 │ ********************************** │\n│ .sample │ 26 │ ************************** │\n│ .c │ 21 │ ********************* │\n│ .css │ 5 │ ***** │\n│ .yml │ 4 │ **** │\n│ .h │ 4 │ **** │\n└──────────────────────┴──────────┴────────────────────────────────────┘\n*/\n```\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":129,"value":"### With SQLite's ZIP support\n\nSQLite's [ZIP file support](https://www.sqlite.org/zipfile.html) can be extremely useful when extracting datasets from ZIP files. The resulting table has a `name` column, which is the path of individual files in the archive.\n\nWorking with the `name` column with bare SQLite text functions isn't great, however. Take last week's [*Scraping JSON, HTML, and ZIP Files with Pure SQLite\n*](https://observablehq.com/@asg017/scrape-json-html-zip-with-sqlite) post: this example was used when creating a table from a ZIP file archive:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":243,"value":"$2e","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":272,"value":"Calculating that `date` column is painful! I tried using `length()` to make it a bit clearer on what exactly was getting parsed (which I find better than `substr(name, 34, 10)`), but the SQLite substring() function is awkward.\n\nLet's see what that would look like using sqlite-path:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":252,"value":"
\n\ncreate table carmack_plans as\n select\n path_name(name) as date,\n cast(data as text) as contents\n from zipfile(\n http_get_body('https://github.com/oliverbenns/john-carmack-plan/archive/refs/heads/master.zip')\n )\n where name like \"%archive/%.md\";\n
","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":279,"value":"Much simpler!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":131,"value":"### With sqlite-url\n\nWith the soon-to-be-released sqlite-url extension, you can create URLs from SQLite! sqlite-path can help generate those URLs with well-formed paths like so:\n\n\n```sql\nselect url(\n 'http://github.com/',\n 'path', path_join('asg017', 'sqlite-path', 'blob', 'main', 'docs.md'),\n 'fragment', 'path_join'\n);\n-- 'http://github.com/asg017/sqlite-path/blob/main/docs.md#path_join'\n```\n\nsqlite-url will be published next week!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"## Stay in the loop!\n\nThis is a post in my [SQLite blog series](https://observablehq.com/@asg017/a-new-sqlite-blog-series), where I talk about new tools and SQLite extensions that I am making, guides, and how-to's. Consider subscribing to the [email newsletter](https://buttondown.email/alexgarcia)! **No more than one (1) post a week**.\n\n\n\nAlternatively, there is an [RSS feed](https://buttondown.email/alexgarcia/rss), or you can [follow me on Twitter](https://twitter.com/agarcia_me).\n\nNext up: sqlite-url for parsing/generating URLs, Building SQLite extensions in Go, new SQLite extensions for querying Excel spreadsheets","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":196,"value":"---\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":26,"value":"sqljs = require(await FileAttachment(\"path0-sqljs.js\").url())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":28,"value":"wasm = FileAttachment(\"path0-sqljs.wasm\").url()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"DemoSQLitePathDatabaseClient = async (source) => {\n //Adapted from https://github.com/observablehq/stdlib/blob/main/src/sqlite.js\n function load(source) {\n return typeof source === \"string\"\n ? fetch(source).then(load)\n : source instanceof Response || source instanceof Blob\n ? source.arrayBuffer().then(load)\n : source instanceof ArrayBuffer\n ? new Uint8Array(source)\n : source;\n }\n\n const [SQL, buffer] = await Promise.all([\n sqljs({ locateFile: (filename, prefix) => wasm }),\n Promise.resolve(source).then(load)\n ]);\n\n return new SQLiteDatabaseClient(new SQL.Database(buffer));\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":202,"value":"pics = Promise.all([\n FileAttachment(\n \"DALL·E 2022-08-25 19.04.40 - wooden bridge over a river, animation, playful.png\"\n ).url(),\n FileAttachment(\n \"DALL·E 2022-08-25 19.04.52 - Incredible, wooden bridge over a river, matte painting, dramatic lighting.png\"\n ).url(),\n FileAttachment(\n \"DALL·E 2022-08-25 19.05.00 - wooden bridge over a river, animation, playful.png\"\n ).url()\n])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":246,"value":"