. /** * @package tool_xmldb * @copyright 2003 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com} * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later */ /** * This class will look for data in the database that violates the foreign * key definitions found in the XMLDB definitions. * * Note that by default, this check does not complain about foreign key * violations from, say, a userid column defined as NOT NULL DEFAULT '0'. * Each 0 in that column will violate the foreign key, but we ignore them. * If you want a strict check performed, then add &strict=1 to the URL. * * @package tool_xmldb * @copyright 2003 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com} * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later */ class check_foreign_keys extends XMLDBCheckAction { /** * Init method, every subclass will have its own */ function init() { $this->introstr = 'confirmcheckforeignkeys'; parent::init(); // Set own core attributes // Set own custom attributes // Get needed strings $this->loadStrings(array( 'key' => 'tool_xmldb', 'violatedforeignkeys' => 'tool_xmldb', 'noviolatedforeignkeysfound' => 'tool_xmldb', 'violatedforeignkeysfound' => 'tool_xmldb', 'violations' => 'tool_xmldb', 'unknowntable' => 'tool_xmldb', 'unknownfield' => 'tool_xmldb', )); } 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.='

' . $this->str['key'] . ': ' . $xmldb_key->readableInfo() . ' '; $reftable = $xmldb_key->getRefTable(); if (!$dbman->table_exists($reftable)) { $o.='' . $this->str['unknowntable'] . ''; // Add the missing index to the list $violation = new stdClass(); $violation->string = 'fkunknowntable'; $violation->table = $xmldb_table; $violation->key = $xmldb_key; $violation->reftable = $reftable; $violatedkeys[] = $violation; continue; } // 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) { if (!$dbman->field_exists($reftable, $reffields[$i])) { $o.='' . $this->str['unknownfield'] . ''; // Add the missing index to the list $violation = new stdClass(); $violation->string = 'fkunknownfield'; $violation->table = $xmldb_table; $violation->key = $xmldb_key; $violation->reftable = $reftable; $violation->reffield = $reffields[$i]; $violatedkeys[] = $violation; continue 2; } $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 {' . $reftable . '} 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.='' . $this->str['ok'] . ''; } else { $o.='' . $this->str['violations'] . ''; // Add the missing index to the list $violation = new stdClass; $violation->string = 'fkviolationdetails'; $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.='

'; } return array($o, $violatedkeys); } protected function display_results(array $violatedkeys) { $r = ''; $r.= ' '; $r.= ' '; $r.= ' '; $r.= '

'; $r.= ' ' . $this->str['searchresults'] . ' '; $r.= ' ' . $this->str['violatedforeignkeys'] . ': ' . count($violatedkeys) . ' '; $r.= '
'; // If we have found wrong integers inform about them if (count($violatedkeys)) { $r.= ' ' . $this->str['violatedforeignkeysfound'] . ' '; $r.= ' '; foreach ($violatedkeys as $violation) { $violation->tablename = $violation->table->getName(); $violation->keyname = $violation->key->getName(); $r.= ' ' .get_string($violation->string, 'tool_xmldb', $violation); if (!empty($violation->sql)) { $r.= ' ' . s($violation->sql) . '; ' . s($violation->sqlparams) . ' '; } $r.= ' '; } $r.= ' '; } else { $r.= ' ' . $this->str['noviolatedforeignkeysfound'] . ' '; } $r.= '
'; // Add the complete log message $r.= ' ' . $this->str['completelogbelow'] . ' '; $r.= '

'; return $r; } }

' . $this->str['searchresults'] . '