Awesome
sqlite-path
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)
Usage
.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 │ **** │
└──────────────────────┴──────────┴────────────────────────────────────┘
*/
Documentation
See docs.md
for a full API reference.
Installing
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.
As a loadable extension
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
0
in the filename (path0.dylib
/path0.so
/path0.dll
) denotes the major version ofsqlite-path
. Currentlysqlite-path
is 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.1
Or 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
See also
- 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