root/trunk/sql/db2/shortestPath.sql

DROP PROCEDURE shortestPath@

CREATE PROCEDURE shortestPath()
BEGIN
  DECLARE maxLength INT DEFAULT 1;
  DECLARE oldSize INT;
  DECLARE newSize INT DEFAULT 0;
  
  INSERT INTO paths SELECT head AS startNode,tail AS endNode,1 FROM edges;
  
  REPEAT
    INSERT INTO paths
      SELECT DISTINCT p.startNode AS startNode,tail AS endNode,maxLength+1 AS length
      FROM paths p
      JOIN edges ON p.endNode=head
      WHERE p.length=maxLength
        AND p.startNode!=tail
        AND NOT EXISTS (
          SELECT * FROM paths q
          WHERE q.startNode=p.startNode AND q.endNode=tail);
    COMMIT;
    SET maxLength=maxLength+1;
    SET oldSize=newSize;
    SET newSize=(SELECT COUNT(*) FROM paths);
  UNTIL oldSize=newSize
  END REPEAT;
END@

DELETE FROM paths@

CALL shortestPath()@