CREATE TABLE nodes (
      id        INTEGER PRIMARY KEY
    , parent_id INTEGER
    , isdir     INTEGER
    , name      TEXT
);
CREATE INDEX index_name ON nodes (name);

CREATE TABLE closures (
      ancestor   INTEGER -- NULL allowed
    , descendant INTEGER NOT NULL
    , pathlength INTEGER NOT NULL
    , PRIMARY KEY (ancestor, descendant)
    , FOREIGN KEY (ancestor)   REFERENCES nodes(id)
    , FOREIGN KEY (descendant) REFERENCES nodes(id)
);
CREATE INDEX index_descendant_ancestor ON closures (descendant, ancestor);

-- insert all root nodes into closures table
INSERT INTO closures (ancestor, descendant, pathlength)
SELECT id, id, 0
FROM nodes WHERE parent_id IS NULL
UNION ALL
SELECT NULL, id, -1
FROM nodes WHERE parent_id IS NULL;

-- run this query repeatedly to incrementally insert one more level at a time
INSERT OR REPLACE INTO closures (ancestor, descendant, pathlength)
SELECT COALESCE(ancestor, id), id, pathlength+1
FROM closures
JOIN nodes ON parent_id = descendant;