Recursive tree structure in SQLite allowing to get all descendants of an element via storing "intervals" for each node.

tree.sql

PRAGMA recursive_triggers=on;

CREATE TABLE nodes (
    id INTEGER NOT NULL,
    parent_id INTEGER,
    interval_start REAL,
    interval_end REAL,
    name TEXT NOT NULL,
    PRIMARY KEY (id ASC),
    FOREIGN KEY (parent_id)
      REFERENCES nodes(id)
      ON UPDATE CASCADE );

CREATE TABLE temp_childs (parent INTEGER, id INTEGER);

CREATE TRIGGER update_intervals
    AFTER UPDATE OF interval_end ON nodes
    FOR EACH ROW
    BEGIN
        INSERT INTO temp_childs(parent, id) SELECT NEW.id, nodes.id FROM nodes WHERE nodes.parent_id=NEW.id;
        UPDATE nodes SET
            interval_start = NEW.interval_start + (NEW.interval_end - NEW.interval_start)
                * ((SELECT rowid FROM temp_childs WHERE id=nodes.id) - (SELECT min(rowid) FROM temp_childs WHERE parent=NEW.id))
                / (SELECT count(id) FROM temp_childs WHERE parent=NEW.id),
            interval_end = NEW.interval_start + (NEW.interval_end - NEW.interval_start)
                * ((SELECT rowid+1 FROM temp_childs WHERE id=nodes.id) - (SELECT min(rowid) FROM temp_childs WHERE parent=NEW.id))
                / (SELECT count(id) FROM temp_childs WHERE parent=NEW.id)
            WHERE nodes.parent_id=NEW.id;
        DELETE FROM temp_childs WHERE parent=NEW.id;
    END;

CREATE VIEW node_summary AS
    SELECT nodes.id, nodes.name, group_concat(node.name) AS descendants FROM nodes, nodes AS node
    WHERE node.interval_start >= nodes.interval_start AND node.interval_end <= nodes.interval_end GROUP BY nodes.id;