Skip to content

Derive columns from file paths ​

Directory layouts often encode real data — an author, a year, a thread ID — as path segments. An on-file hook receives each file's path and can split it into columns, so a query can group and filter on those segments.

1. Split the path in a hook ​

Suppose photos are filed by year and month:

photos/2024/05/beach.jpg
photos/2024/11/hike.jpg
photos/2025/01/snow.jpg

Write a small parser, pathcols.py, that turns each path into a row. It receives the matched files' absolute paths as its arguments and prints one JSON object per line, one row each:

python
#!/usr/bin/env python3
import json, os, sys

for path in sys.argv[1:]:
    parts = path.split(os.sep)
    # .../photos/<year>/<month>/<file>
    print(json.dumps({"year": parts[-3], "month": parts[-2],
                     "basename": os.path.basename(path)}))

Point a table at it in .dirsql.toml:

toml
[[table]]
name = "photos"
ddl     = "CREATE TABLE photos (year TEXT, month TEXT, basename TEXT)"
glob    = "photos/*/*/*.jpg"
on-file = "python3 pathcols.py"

The hook emits every column the table has — dirsql injects nothing. The matched files' absolute paths arrive as the command's trailing arguments, per the command hook contract. A column appears only because the DDL declares it and the hook emits it.

2. Query the derived columns ​

Pass the config with -c (dirsql does not auto-load a .dirsql.toml from the current directory):

bash
dirsql query "SELECT year, month, basename FROM photos ORDER BY year, month" -c ./.dirsql.toml
json
[{"basename":"beach.jpg","month":"05","year":"2024"},{"basename":"hike.jpg","month":"11","year":"2024"},{"basename":"snow.jpg","month":"01","year":"2025"}]

They are real SQL columns, so aggregation works:

bash
dirsql query "SELECT year, COUNT(*) AS photos FROM photos GROUP BY year" -c ./.dirsql.toml
json
[{"photos":2,"year":"2024"},{"photos":1,"year":"2025"}]

Going further ​

  • Only need the plain filesystem stat columns (path, basename, dir, ext, size, …) with no code? Query the path directly — a path-table gives them for free, no config.
  • The tutorial walks the same idea, deriving an author from the folder name, starting from zero.
  • When the value you need lives inside the file rather than in its path, see Extract rows from file contents.

Released under the MIT License.