Detecting cycles in a MySQL database using PHP

Question

I have a table in MySQL with two (important) columns, A and B, with value referring to a package. A row is in the table if and only if package A requires on package B.

I was hoping to (1) generate a graph in php, then (2) determine if the graph is acyclic (a DAG), and if not, print (3) all the cycles in the graph.

So 3 is easy enough, in theory, (Johnson's algorithm: http://dutta.csc.ncsu.edu/csc791_spring07/wrap/circuits_johnson.pdf ).

(2) can be done by (3) listing no cycles, but I was wondering if there was any faster algorithms.

I'm unsure of (1) - efficiently pulling data from a table and making a graph in php that lends itself to implementing (2) and (3). How should I do so?

---

As an aside, I also have a second table, also with two columns, having a row if and only if A conflicts with B. I also wanted to (4) find cases (or verify that there are none) where: A requires B, B requires C, but A conflicts with C

Answer 1

In the interests of anyone who finds this topic in a search

(1)

$pkgList = array();
$result = mysqli_query($conn, 'SELECT * FROM `Packages`');
while (($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) != NULL) {
    $pkgList[] = $row['idPackages'];
}

$reqEdgeList = array();
$conEdgeList = array();

$result = mysqli_query($conn, "SELECT * FROM `Dependancies` WHERE `Relationship` = 'Requires'");
while (($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) != NULL) {
    switch ($row['Relationship']) {
        case 'Requires':
            $reqEdgeList[] = array($row["DependerPackage"], $row["DependeePackage"]);
            break;
        case 'Conflicts':
            $conEdgeList[] = array($row["DependerPackage"], $row["DependeePackage"]);
            break;
    }
}

(2) and (3)

I ended up using the algorithm here. Basically by removing leaf nodes, you are either left with a (set of) loop(s) or an empty graph.

$allReqs = $reqEdgeList;

$noDependanciesCycle = true;

$searching = true;
while ($searching) {
    if (empty($pkgList)) {
        $searching = false;
        echo "Req is a DAG\n<br />";
    } else {
        $foundleaf = false;
        $leaf = null;
        foreach ($pkgList as $key => $l) {
            $isLeaf = true;
            foreach ($reqEdgeList as $k => $edge) {
                if ($edge[0] == $l) {
                    $isLeaf = false;
                }
            }

            if ($isLeaf) {
                $foundleaf = true;
                $leaf = $l;
            }
        }
        if ($foundleaf) {
            $pkgList = array_diff($pkgList, array($leaf));
            foreach ($reqEdgeList as $key => $value) {
                if ($value[1] == $leaf) {
                    unset($reqEdgeList[$key]);
                }
            }
            $reqEdgeList = array_values($reqEdgeList);
        } else {
            $searching = false;
            echo "Req cycle detected\n<br />";
            $noDependanciesCycle = false;
            print_r($reqEdgeList);
            echo "<br />\n";
        }
    }
}

(4)

For finding A requires B requires C, but A conflicts with C, I used a depth first search for each conflict, starting from A, looking for C (the conflict).

$reqEdgeList = $allReqs;
echo "<br />\n";

$anyReqConfError = false;
foreach ($conEdgeList as $endpoints) {
    for ($i = 0; $i < 2; $i++) {
        if ($i == 0) {
            $startPkg = $endpoints[0];
            $endPkg = $endpoints[1];
        } else {
            $startPkg = $endpoints[1];
            $endPkg = $endpoints[0];
        }

        $marked = array();
        foreach ($allPkgs as $pkg) {
            $marked[$pkg] = false;
        }

        $queue = array();
        $queue[] = $startPkg; // enque
        $marked[$startPkg] = true;

        $searching = true;
        $found = false;
        while ($searching) {
            $v = array_shift($queue); // deque (use array_pop for stack (dfs))
            if ($v == $endPkg) {
                $searching = false;
                $found = true;
            } else {
                foreach ($reqEdgeList as $edge) {
                    if ($edge[0] == $v) {
                        $w = $edge[1];
                        if (!$marked[$w]) {
                            $marked[$w] = true;
                            $queue[] = $w;
                        }
                    }
                }
            }

            if($searching) {
                $searching = !empty($queue);
            }
        }

        if($found) {
            echo "$startPkg requires $endPkg, but are conflicting [$endpoints[0] $endpoints[1]]\n<br />";
            $anyReqConfError = true;
            $noDependanciesCycle = false;
        }
    }
}

I'd appreciate any code review in the comments for this answer

Answer 2

Sounds suspiciously like 'please help me with my homework'.

In terms of rendering the graph - have a look at graphviz - this will generate all sorts of graphs. Here's an example which parses PHP source code to get a call graph.

Sorry, but I'm busy enough without reading the reference you provided (but well done for providing a link to your source material) and working out the algorithm they've used then thinking about whether it is optimal.

You might want to have a look at the source code for the new PHP garbage collector - which does the same thing. (really you just need to walk the tree - if you come to a node you've already visited then you've got a loop).