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 of sqlite-path. Currently sqlite-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