A loadable SQLite extension for parsing, generating, and querying paths. Based on cwalk
Try it out in your browser and learn more in Introducing sqlite-path: A SQLite extension for parsing and generating file paths (August 2022)
.load ./path0 select path_dirname('foo/bar.txt'); -- 'foo/' select path_basename('foo/bar.txt'); -- 'bar.txt' select path_extension('foo/bar.txt'); -- '.txt' select path_part_at('foo/bar/baz.txt', 0); -- 'foo' select path_part_at('foo/bar/baz.txt', 1); -- 'bar' select path_part_at('foo/bar/baz.txt', -1); -- 'baz.txt'Iterate through all parts in a path.
select * from path_parts('/usr/bin/sqlite3'); /* ┌────────┬─────────┐ │ type │ part │ ├────────┼─────────┤ │ normal │ usr │ │ normal │ bin │ │ normal │ sqlite3 │ └────────┴─────────┘ */Inside a ZIP archive of the SQLite source code, find the top 5 deepest .c source code files under the ext/ directory (using SQLite's ZIP support).
select name, path_length(name) as depth from zipfile('sqlite.archive.master.zip') where -- under the ext/ directory path_part_at(name, 1) == 'ext' -- ends in ".c" and path_extension(name) == '.c' order by 2 desc limit 5; /* ┌────────────────────────────────────────────┬───────┐ │ name │ depth │ ├────────────────────────────────────────────┼───────┤ │ sqlite-master/ext/fts3/tool/fts3view.c │ 5 │ │ sqlite-master/ext/lsm1/lsm-test/lsmtest1.c │ 5 │ │ sqlite-master/ext/lsm1/lsm-test/lsmtest2.c │ 5 │ │ sqlite-master/ext/lsm1/lsm-test/lsmtest3.c │ 5 │ │ sqlite-master/ext/lsm1/lsm-test/lsmtest4.c │ 5 │ └────────────────────────────────────────────┴───────┘ */Make a histogram of the count of file extensions in the current directory, using fsdir().
select path_extension(name), count(*), printf('%.*c', count(*), '*') as bar from fsdir('.') where path_extension(name) is not null group by 1 order by 2 desc limit 6; /* ┌──────────────────────┬──────────┬────────────────────────────────────┐ │ path_extension(name) │ count(*) │ bar │ ├──────────────────────┼──────────┼────────────────────────────────────┤ │ .md │ 34 │ ********************************** │ │ .sample │ 26 │ ************************** │ │ .c │ 21 │ ********************* │ │ .css │ 5 │ ***** │ │ .yml │ 4 │ **** │ │ .h │ 4 │ **** │ └──────────────────────┴──────────┴────────────────────────────────────┘ */See docs.md for a full API reference.
| Language | Install | |
|---|---|---|
| Python | pip install sqlite-path |  |
| Datasette | datasette install datasette-sqlite-path |  |
| Node.js | npm install sqlite-path |  |
| Deno | deno.land/x/sqlite_path |  |
| Ruby | gem install sqlite-path |  |
| Github Release |  |
The Releases page contains pre-built binaries for Linux amd64, MacOS amd64 (no arm), and Windows.
If you want to use sqlite-path as a Runtime-loadable extension, Download the path0.dylib (for MacOS), path0.so (Linux), or path0.dll (Windows) file from a release and load it into your SQLite environment.
Note: The
0in the filename (path0.dylib/path0.so/path0.dll) denotes the major version ofsqlite-path. Currentlysqlite-pathis pre v1, so expect breaking changes in future versions.
For example, if you are using the SQLite CLI, you can load the library like so:
.load ./path0 select path_version(); -- v0.0.1Or in Python, using the builtin sqlite3 module:
import sqlite3 con = sqlite3.connect(":memory:") con.enable_load_extension(True) con.load_extension("./path0") print(con.execute("select path_version()").fetchone()) # ('v0.0.1',)Or in Node.js using better-sqlite3:
const Database = require("better-sqlite3"); const db = new Database(":memory:"); db.loadExtension("./path0"); console.log(db.prepare("select path_version()").get()); // { 'html_version()': 'v0.0.1' }Or with Datasette:
datasette data.db --load-extension ./path0 - sqlite-url, for parsing and generating URLs (pairs well with this library)
- sqlite-http, for making HTTP requests in SQLite
- sqlite-html, for parsing HTML documents
- sqlite-lines, for reading large files line-by-line
- nalgeon/sqlean, several pre-compiled handy SQLite functions, in C