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

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

CREATE VIEW parent_of
AS
SELECT id
     , ancestor AS parent_id
     , isdir
     , name
FROM nodes
JOIN closures ON descendant = id AND pathlength = 1;

CREATE TABLE orphans (
      id         INTEGER PRIMARY KEY
    , parent_id  INTEGER
    , isdir      INTEGER
    , name       TEXT
);

CREATE TRIGGER build_closures
INSTEAD OF INSERT ON parent_of
FOR EACH ROW
BEGIN
    CASE WHEN (SELECT id FROM nodes WHERE id = NEW.parent_id LIMIT 1) IS NULL
    THEN
        INSERT INTO orphans (id, parent_id, isdir, name) VALUES (NEW.id, NEW.parent_id, NEW.isdir, NEW.name);
    ELSE
        INSERT INTO nodes (id, isdir, name) VALUES (NEW.id, NEW.isdir, NEW.name);

        INSERT INTO closures (ancestor,descendant,pathlength)
        SELECT ancestor, NEW.id, pathlength+1
        FROM closures
        WHERE descendant = NEW.parent_id

        UNION ALL

        SELECT NEW.id,NEW.id,0;

        DELETE FROM orphans WHERE id = NEW.id;
    END
    INSERT INTO parent_of
    SELECT * FROM orphans
    WHERE parent_id IN (SELECT id FROM nodes);
END;

-- Just insert everything into `parent_of` !