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); }
/** * Validates the index restrictions. * * The error message should not be localised because it is intended for developers, * end users and admins should never see these problems! * * @param xmldb_table $xmldb_table optional when object is table * @return string null if ok, error message if problem found */ function validateDefinition(xmldb_table $xmldb_table=null) { if (!$xmldb_table) { return 'Invalid xmldb_index->validateDefinition() call, $xmldb_table si required.'; } $total = 0; foreach ($this->getFields() as $fieldname) { if (!$field = $xmldb_table->getField($fieldname)) { // argh, we do not have the fields loaded yet, this should not happen during install continue; } switch ($field->getType()) { case XMLDB_TYPE_INTEGER: $total += 8; // big int break; case XMLDB_TYPE_NUMBER: $total += 12; // this is just a guess break; case XMLDB_TYPE_FLOAT: $total += 8; // double precision break; case XMLDB_TYPE_CHAR: if ($field->getLength() > self::INDEX_MAX_BYTES / 3) { return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_CHAR field "'.$field->getName().'" can not be indexed because it is too long.' .' Limit is '.(self::INDEX_MAX_BYTES/3).' chars.'; } $total += ($field->getLength() * 3); // the most complex utf-8 chars have 3 bytes break; case XMLDB_TYPE_TEXT: return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_TEXT field "'.$field->getName().'" can not be indexed'; break; case XMLDB_TYPE_BINARY: return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_BINARY field "'.$field->getName().'" can not be indexed'; break; case XMLDB_TYPE_DATETIME: $total += 8; // this is just a guess break; case XMLDB_TYPE_TIMESTAMP: $total += 8; // this is just a guess break; } } if ($total > self::INDEX_COMPOSED_MAX_BYTES) { return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: the composed index on fields "'.implode(',', $this->getFields()).'" is too long.' .' Limit is '.self::INDEX_COMPOSED_MAX_BYTES.' bytes / '.(self::INDEX_COMPOSED_MAX_BYTES/3).' chars.'; } return null; }