General developer forum

Thanks for the feedback, Martin. I'm new to SQL programming, and I don't have much experience with SQL optimization or very large Moodles.

I think that even if the database-intensive checks are split out, it is still more useful to an administrator if he can in one click initiate a comprehensive automatic analysis of the integrity of his database. What about moving the database checks to a separate page, health_db.php or something. The health.php page would then present a list of all the tests that could be run, with a checkbox next to each. When the user submits that form, all the checked tests will be run automatically. The health_db.php page would then do something like this:
$n = count($tests);
for ($i=0;$i<$n;$i++) {
if (!isset($_GET['run'.$i])) {
echo 'Skipping test '.$i.'...';
}
echo 'Test '.$i.'/'.$n.': Checking '.$tests[$i]->name.'...<br/>';
$tests[$i]->run(); // DB intensive
// You could even sleep() here, or break the loop
// if too many tests have found errors
}
for ($i=0;$i<$n;$i++) {
if ($tests[$i]->has_problem() /* read cached result */) {
echo 'Problem found.<br/>'.$tests[$i]->description();
echo 'link to fix just this one problem.';
}
}

That way, the administrator doesn't have to manually initiate numerous different tests individually just to know that there are no major database problems in any of the 10K courses on his Moodle.

As for the efficiency of the find_duplicates() function, I think you can make it less scary by checking each table in chunks. e.g.

$all_dupes = array();
foreach ($courses as $courseid) {
$dupes = health_find_duplicates('a_table', 'the_column_to_check', 'courseid='.$courseid);
foreach($dupes as $d) { $all_dupes[] = $d; }
}
Because doing this will only consider rows in the table WHERE courseid=x.

Anyhow, I don't really have the time right now to take this much further myself, but I wanted to throw the code out there in case anybody else finds it useful, or was stuck in the same situation I was with a horribly broken gradebook.