topology_schema.sql

-- modified adjacency list
-- 162ms for 30,000 records in a tree

create table topology (
  id serial primary key,
  parent_id integer,
  second_parent_id integer,
  name varchar
);
create index on topology (id);
create index on topology (parent_id);
create index on topology (second_parent_id);
create index on topology (name);


insert into topology values (1, null, null, 'Substation 1');

INSERT INTO topology (id, parent_id, second_parent_id, name)
SELECT x.id, x.id - 1, null, 'article #' || x.id
  FROM generate_series(2,10001) AS x(id);

INSERT INTO topology values (10002, 1, null, 'second line');

INSERT INTO topology (id, parent_id, second_parent_id, name)
SELECT x.id, x.id - 1, null, 'article #' || x.id
  FROM generate_series(10003,20001) AS x(id);

-- connect the two lines previously built
INSERT INTO topology values (20002, 10001, 20001, 'hub');

INSERT INTO topology (id, parent_id, second_parent_id, name)
SELECT x.id, x.id - 1, null, 'article #' || x.id
  FROM generate_series(20003,30001) AS x(id);

INSERT INTO topology (id, parent_id, second_parent_id, name)
SELECT x.id, 10002, null, 'article #' || x.id
FROM generate_series(30002,1000000) AS x(id);


WITH RECURSIVE q AS (
  SELECT h.id, h.parent_id, h.second_parent_id, h.name
  FROM topology h
  WHERE id = 19999
  UNION ALL
  SELECT hc.id, hc.parent_id, hc.second_parent_id, hc.name
  FROM q
  JOIN topology hc
  ON q.id = hc.parent_id
  OR q.id = hc.second_parent_id
)
SELECT count(DISTINCT id) FROM q;

-- or use a view
CREATE OR REPLACE VIEW topo_heirarchy AS
  WITH RECURSIVE q AS (
    SELECT h.id, h.parent_id, h.second_parent_id, h.name
    FROM topology h
    WHERE id = 1
    UNION ALL
    SELECT hc.id, hc.parent_id, hc.second_parent_id, hc.name
    FROM q
    JOIN topology hc
    ON q.id = hc.parent_id
    OR hc.name = 'hub' AND q.id = hc.second_parent_id
  )
  SELECT q.id, q.parent_id, q.second_parent_id, q.name FROM q;

select * from topo_heirarchy where id = 2;