[Summary view]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * @package    tool_xmldb
  19   * @copyright  2003 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
  20   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  21   */
  22  
  23  /**
  24   * This class will look for data in the database that violates the foreign
  25   * key definitions found in the XMLDB definitions.
  26   *
  27   * Note that by default, this check does not complain about foreign key
  28   * violations from, say, a userid column defined as NOT NULL DEFAULT '0'.
  29   * Each 0 in that column will violate the foreign key, but we ignore them.
  30   * If you want a strict check performed, then add &strict=1 to the URL.
  31   *
  32   * @package    tool_xmldb
  33   * @copyright  2003 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
  34   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  35   */
  36  class check_foreign_keys extends XMLDBCheckAction {
  37  
  38      /**
  39       * Init method, every subclass will have its own
  40       */
  41      function init() {
  42          $this->introstr = 'confirmcheckforeignkeys';
  43          parent::init();
  44  
  45          // Set own core attributes
  46  
  47          // Set own custom attributes
  48  
  49          // Get needed strings
  50          $this->loadStrings(array(
  51              'key' => 'tool_xmldb',
  52              'violatedforeignkeys' => 'tool_xmldb',
  53              'noviolatedforeignkeysfound' => 'tool_xmldb',
  54              'violatedforeignkeysfound' => 'tool_xmldb',
  55              'violations' => 'tool_xmldb',
  56              'unknowntable' => 'tool_xmldb',
  57              'unknownfield' => 'tool_xmldb',
  58          ));
  59      }
  60  
  61      protected function check_table(xmldb_table $xmldb_table, array $metacolumns) {
  62          global $DB;
  63          $dbman = $DB->get_manager();
  64  
  65          $strictchecks = optional_param('strict', false, PARAM_BOOL);
  66  
  67          $o = '';
  68          $violatedkeys = array();
  69  
  70          // Keys
  71          if ($xmldb_keys = $xmldb_table->getKeys()) {
  72              $o.='        <ul>';
  73              foreach ($xmldb_keys as $xmldb_key) {
  74                  // We are only interested in foreign keys.
  75                  if (!in_array($xmldb_key->getType(), array(XMLDB_KEY_FOREIGN, XMLDB_KEY_FOREIGN_UNIQUE))) {
  76                      continue;
  77                  }
  78                  $o.='            <li>' . $this->str['key'] . ': ' . $xmldb_key->readableInfo() . ' ';
  79  
  80                  $reftable = $xmldb_key->getRefTable();
  81                  if (!$dbman->table_exists($reftable)) {
  82                      $o.='<font color="red">' . $this->str['unknowntable'] . '</font>';
  83                      // Add the missing index to the list
  84                      $violation = new stdClass();
  85                      $violation->string = 'fkunknowntable';
  86                      $violation->table = $xmldb_table;
  87                      $violation->key = $xmldb_key;
  88                      $violation->reftable = $reftable;
  89                      $violatedkeys[] = $violation;
  90                      continue;
  91                  }
  92  
  93                  // Work out the SQL to find key violations.
  94                  $keyfields = $xmldb_key->getFields();
  95                  $reffields = $xmldb_key->getRefFields();
  96                  $joinconditions = array();
  97                  $nullnessconditions = array();
  98                  $params = array();
  99                  foreach ($keyfields as $i => $field) {
 100                      if (!$dbman->field_exists($reftable, $reffields[$i])) {
 101                          $o.='<font color="red">' . $this->str['unknownfield'] . '</font>';
 102                          // Add the missing index to the list
 103                          $violation = new stdClass();
 104                          $violation->string = 'fkunknownfield';
 105                          $violation->table = $xmldb_table;
 106                          $violation->key = $xmldb_key;
 107                          $violation->reftable = $reftable;
 108                          $violation->reffield = $reffields[$i];
 109                          $violatedkeys[] = $violation;
 110                          continue 2;
 111                      }
 112  
 113                      $joinconditions[] = 't1.' . $field . ' = t2.' . $reffields[$i];
 114                      $xmldb_field = $xmldb_table->getField($field);
 115                      $default = $xmldb_field->getDefault();
 116                      if (!$xmldb_field->getNotNull()) {
 117                          $nullnessconditions[] = 't1.' . $field . ' IS NOT NULL';
 118                      } else if (!$strictchecks && ($default == '0' || !$default)) {
 119                          // We have a default of 0 or '' or something like that.
 120                          // These generate a lot of false-positives, so ignore them
 121                          // for now.
 122                          $nullnessconditions[] = 't1.' . $field . ' <> ?';
 123                          $params[] = $xmldb_field->getDefault();
 124                      }
 125                  }
 126                  $nullnessconditions[] = 't2.id IS NULL';
 127                  $sql = 'SELECT count(1) FROM {' . $xmldb_table->getName() .
 128                          '} t1 LEFT JOIN {' . $reftable . '} t2 ON ' .
 129                          implode(' AND ', $joinconditions) . ' WHERE ' .
 130                          implode(' AND ', $nullnessconditions);
 131  
 132                  // Check there are any problems in the database.
 133                  $violations = $DB->count_records_sql($sql, $params);
 134                  if ($violations == 0) {
 135                      $o.='<font color="green">' . $this->str['ok'] . '</font>';
 136                  } else {
 137                      $o.='<font color="red">' . $this->str['violations'] . '</font>';
 138                      // Add the missing index to the list
 139                      $violation = new stdClass;
 140                      $violation->string = 'fkviolationdetails';
 141                      $violation->table = $xmldb_table;
 142                      $violation->key = $xmldb_key;
 143                      $violation->numviolations = $violations;
 144                      $violation->numrows = $DB->count_records($xmldb_table->getName());
 145                      $violation->sql = str_replace('count(1)', '*', $sql);
 146                      if (!empty($params)) {
 147                          $violation->sqlparams = '(' . implode(', ', $params) . ')';
 148                      } else {
 149                          $violation->sqlparams = '';
 150                      }
 151                      $violatedkeys[] = $violation;
 152                  }
 153                  $o.='</li>';
 154              }
 155              $o.='        </ul>';
 156          }
 157  
 158          return array($o, $violatedkeys);
 159      }
 160  
 161      protected function display_results(array $violatedkeys) {
 162          $r = '<table class="generaltable boxaligncenter boxwidthwide" border="0" cellpadding="5" cellspacing="0" id="results">';
 163          $r.= '  <tr><td class="generalboxcontent">';
 164          $r.= '    <h2 class="main">' . $this->str['searchresults'] . '</h2>';
 165          $r.= '    <p class="centerpara">' . $this->str['violatedforeignkeys'] . ': ' . count($violatedkeys) . '</p>';
 166          $r.= '  </td></tr>';
 167          $r.= '  <tr><td class="generalboxcontent">';
 168  
 169          // If we have found wrong integers inform about them
 170          if (count($violatedkeys)) {
 171              $r.= '    <p class="centerpara">' . $this->str['violatedforeignkeysfound'] . '</p>';
 172              $r.= '        <ul>';
 173              foreach ($violatedkeys as $violation) {
 174                  $violation->tablename = $violation->table->getName();
 175                  $violation->keyname = $violation->key->getName();
 176  
 177                  $r.= '            <li>' .get_string($violation->string, 'tool_xmldb', $violation);
 178                  if (!empty($violation->sql)) {
 179                      $r.= '<pre>' . s($violation->sql) . '; ' . s($violation->sqlparams) . '</pre>';
 180                  }
 181                  $r.= '</li>';
 182              }
 183              $r.= '        </ul>';
 184          } else {
 185              $r.= '    <p class="centerpara">' . $this->str['noviolatedforeignkeysfound'] . '</p>';
 186          }
 187          $r.= '  </td></tr>';
 188          $r.= '  <tr><td class="generalboxcontent">';
 189          // Add the complete log message
 190          $r.= '    <p class="centerpara">' . $this->str['completelogbelow'] . '</p>';
 191          $r.= '  </td></tr>';
 192          $r.= '</table>';
 193  
 194          return $r;
 195      }
 196  }