protected function check_table(xmldb_table $xmldb_table, array $metacolumns)
{
global $DB;
$dbman = $DB->get_manager();
$o = '';
$missing_indexes = array();
// Keys
if ($xmldb_keys = $xmldb_table->getKeys()) {
$o .= ' <ul>';
foreach ($xmldb_keys as $xmldb_key) {
$o .= ' <li>' . $this->str['key'] . ': ' . $xmldb_key->readableInfo() . ' ';
// Primaries are skipped
if ($xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
$o .= '<font color="green">' . $this->str['ok'] . '</font></li>';
continue;
}
// If we aren't creating the keys or the key is a XMLDB_KEY_FOREIGN (not underlying index generated
// automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
if (!$dbman->generator->getKeySQL($xmldb_table, $xmldb_key) || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
// Create the interim index
$xmldb_index = new xmldb_index('anyname');
$xmldb_index->setFields($xmldb_key->getFields());
switch ($xmldb_key->getType()) {
case XMLDB_KEY_UNIQUE:
case XMLDB_KEY_FOREIGN_UNIQUE:
$xmldb_index->setUnique(true);
break;
case XMLDB_KEY_FOREIGN:
$xmldb_index->setUnique(false);
break;
}
// Check if the index exists in DB
if ($dbman->index_exists($xmldb_table, $xmldb_index)) {
$o .= '<font color="green">' . $this->str['ok'] . '</font>';
} else {
$o .= '<font color="red">' . $this->str['missing'] . '</font>';
// Add the missing index to the list
$obj = new stdClass();
$obj->table = $xmldb_table;
$obj->index = $xmldb_index;
$missing_indexes[] = $obj;
}
}
$o .= '</li>';
}
$o .= ' </ul>';
}
// Indexes
if ($xmldb_indexes = $xmldb_table->getIndexes()) {
$o .= ' <ul>';
foreach ($xmldb_indexes as $xmldb_index) {
$o .= ' <li>' . $this->str['index'] . ': ' . $xmldb_index->readableInfo() . ' ';
// Check if the index exists in DB
if ($dbman->index_exists($xmldb_table, $xmldb_index)) {
$o .= '<font color="green">' . $this->str['ok'] . '</font>';
} else {
$o .= '<font color="red">' . $this->str['missing'] . '</font>';
// Add the missing index to the list
$obj = new stdClass();
$obj->table = $xmldb_table;
$obj->index = $xmldb_index;
$missing_indexes[] = $obj;
}
$o .= '</li>';
}
$o .= ' </ul>';
}
return array($o, $missing_indexes);
}
protected function check_table(xmldb_table $xmldb_table, array $metacolumns)
{
global $DB;
$dbman = $DB->get_manager();
$strictchecks = optional_param('strict', false, PARAM_BOOL);
$o = '';
$violatedkeys = array();
// Keys
if ($xmldb_keys = $xmldb_table->getKeys()) {
$o .= ' <ul>';
foreach ($xmldb_keys as $xmldb_key) {
// We are only interested in foreign keys.
if (!in_array($xmldb_key->getType(), array(XMLDB_KEY_FOREIGN, XMLDB_KEY_FOREIGN_UNIQUE))) {
continue;
}
$o .= ' <li>' . $this->str['key'] . ': ' . $xmldb_key->readableInfo() . ' ';
// Work out the SQL to find key violations.
$keyfields = $xmldb_key->getFields();
$reffields = $xmldb_key->getRefFields();
$joinconditions = array();
$nullnessconditions = array();
$params = array();
foreach ($keyfields as $i => $field) {
$joinconditions[] = 't1.' . $field . ' = t2.' . $reffields[$i];
$xmldb_field = $xmldb_table->getField($field);
$default = $xmldb_field->getDefault();
if (!$xmldb_field->getNotNull()) {
$nullnessconditions[] = 't1.' . $field . ' IS NOT NULL';
} else {
if (!$strictchecks && ($default == '0' || !$default)) {
// We have a default of 0 or '' or something like that.
// These generate a lot of false-positives, so ignore them
// for now.
$nullnessconditions[] = 't1.' . $field . ' <> ?';
$params[] = $xmldb_field->getDefault();
}
}
}
$nullnessconditions[] = 't2.id IS NULL';
$sql = 'SELECT count(1) FROM {' . $xmldb_table->getName() . '} t1 LEFT JOIN {' . $xmldb_key->getRefTable() . '} t2 ON ' . implode(' AND ', $joinconditions) . ' WHERE ' . implode(' AND ', $nullnessconditions);
// Check there are any problems in the database.
$violations = $DB->count_records_sql($sql, $params);
if ($violations == 0) {
$o .= '<font color="green">' . $this->str['ok'] . '</font>';
} else {
$o .= '<font color="red">' . $this->str['violations'] . '</font>';
// Add the missing index to the list
$violation = new stdClass();
$violation->table = $xmldb_table;
$violation->key = $xmldb_key;
$violation->numviolations = $violations;
$violation->numrows = $DB->count_records($xmldb_table->getName());
$violation->sql = str_replace('count(1)', '*', $sql);
if (!empty($params)) {
$violation->sqlparams = '(' . implode(', ', $params) . ')';
} else {
$violation->sqlparams = '';
}
$violatedkeys[] = $violation;
}
$o .= '</li>';
}
$o .= ' </ul>';
}
return array($o, $violatedkeys);
}
