Where a dirsql table's columns come from depends on which kind of table it is.
The rule that governs both is the same: dirsql never injects a column your
table did not produce. There is no automatic path/size/mtime merge, and glob
{name} segments capture nothing.
A named [[table]] — or an SDK
Table — has exactly the columns its
on-file hook emits, narrowed to the columns the DDL
declares. dirsql adds nothing on top: no path, no size, no value derived
from the filename. A hook that wants any of those computes them itself. The
hook receives every matched file's path as an argument (an SDK on_file
callback receives one path) and may stat or read the files however it likes.
The hook is required. A [[table]] with no on-file is a
config-load error: with nothing supplying columns,
every row would be all-NULL. The error points at the fix — add a hook, or, for
plain stat columns with no code, query the path directly with a path-table.
A path-table (FROM './') is the one place dirsql supplies
columns for you. Each matched file becomes one row carrying seven stat
columns — derived from the file's path and its stat metadata — plus a
lazily-read hidden content column.
| Column | Type | Value |
|---|---|---|
path |
TEXT | The file's path relative to the scan root (e.g. posts/hello.md); as written for a ../ path-table, absolute for a / or ~/ one. |
basename |
TEXT | The filename, including extension (hello.md). |
dir |
TEXT | The parent directory relative to the root (posts); the empty string for files directly under the root. |
ext |
TEXT | The file extension without the leading dot (md). Original case is preserved — Photo.JPG yields JPG; use LOWER(ext) for case-insensitive matching. NULL when the file has no extension. |
size |
INTEGER | File size in bytes. |
mtime |
INTEGER | Last-modified time, Unix seconds. |
ctime |
INTEGER | Creation (birth) time, Unix seconds. NULL when the platform or filesystem cannot supply it. |
These are ordinary stored TEXT/INTEGER values, computed once per file at
scan time — not SQLite GENERATED ... VIRTUAL columns and not part of a
CREATE VIRTUAL TABLE. "Stat" describes where the value comes from — the
file's path and stat metadata, as opposed to its content — not how it is
stored.
A stat value that cannot be computed (an unreadable file's size, a missing
extension's ext) is NULL.
SELECT basename, size
FROM './posts'
WHERE mtime > strftime('%s', '2024-01-01')
ORDER BY mtime DESC;Attaching a parser to a
path-table with --on-file replaces these stat columns with the parser's
own output — the two modes stay cleanly separate, exactly as for a named
table. A parser that wants the path emits it; it has the paths.
To turn path segments (an author, a year, a thread id) into columns, a hook
splits the path and emits the pieces — the same as any other column it
produces. dirsql does not do this for you: a {name} segment in a glob is
rewritten to * and matches a single path segment, but captures no value.
Derive columns from file paths walks a
worked example.