ut/checkdb.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2010 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22 require_once dirname(__FILE__) . '/../include/test.inc.php';
  23
  24 class CheckDB extends PlTestCase
  25 {
  26     private static function checkPlatal()
  27     {
  28         global $platal;
  29         if ($platal == null)
  30         {
  31             $platal = new Xorg();
  32         }
  33     }
  34
  35     public static function dbConsistancyProvider()
  36     {
  37         $testcases = array(
  38             'hruid' =>
  39                 array('SELECT  uid, full_name
  40                          FROM  accounts
  41                         WHERE  hruid IS NULL OR hruid = \'\''),
  42
  43             'hrpid' =>
  44                 array('SELECT  p.pid, pd.public_name, pd.promo
  45                          FROM  profiles        AS p
  46                     LEFT JOIN  profile_display AS pd ON (p.pid = pd.pid)
  47                         WHERE  p.hrpid IS NULL OR p.hrpid = \'\''),
  48
  49             'name' =>
  50                 array('SELECT  p.pid, p.hrpid
  51                          FROM  profiles     AS p
  52                    INNER JOIN  profile_name AS pn ON (p.pid = pn.pid)
  53                         WHERE  name = \'\''),
  54
  55             'phone formatting' =>
  56                 array('SELECT DISTINCT  g.phonePrefix
  57                                   FROM  geoloc_countries AS g
  58                           WHERE EXISTS  (SELECT  h.phonePrefix
  59                                            FROM  geoloc_countries AS h
  60                                           WHERE  h.phonePrefix = g.phonePrefix
  61                                                  AND h.phoneFormat != (SELECT  i.phoneFormat
  62                                                                          FROM  geoloc_countries AS i
  63                                                                         WHERE  i.phonePrefix = g.phonePrefix
  64                                                                         LIMIT  1))'),
  65
  66             'missing countries' =>
  67                 array('SELECT  pa.pid, pa.countryId
  68                          FROM  profile_addresses AS pa
  69                     LEFT JOIN  geoloc_countries  AS gc ON (pa.countryId = gc.iso_3166_1_a2)
  70                         WHERE  gc.countryFR IS NULL OR gc.countryFR = \'\''),
  71
  72             'ax_id' =>
  73                 array('SELECT  pid, hrpid, ax_id, COUNT(ax_id) AS c
  74                          FROM  profiles
  75                         WHERE  ax_id != \'0\'
  76                      GROUP BY  ax_id
  77                        HAVING  c > 1'),
  78
  79             'google apps' =>
  80                 array('SELECT  a.alias, g.g_status, eo.storage
  81                          FROM  email_options  AS eo
  82                    INNER JOIN  aliases        AS a ON (a.uid = eo.uid AND a.type = \'a_vie\')
  83                    INNER JOIN  gapps_accounts AS g ON (g.l_userid = eo.uid)
  84                         WHERE  FIND_IN_SET(\'googleapps\', eo.storage) > 0 AND g.g_status != \'active\'')
  85         );
  86
  87         $tests = array(
  88             'profile_binet_enum'            => 'text',
  89             'profile_corps_enum'            => 'name',
  90             'profile_corps_rank_enum'       => 'name',
  91             'profile_education_degree_enum' => 'degree',
  92             'profile_education_enum'        => 'name',
  93             'profile_education_field_enum'  => 'field',
  94             'profile_job_enum'              => 'name',
  95             'profile_job_sector_enum'       => 'name',
  96             'profile_job_subsector_enum'    => 'name',
  97             'profile_job_subsubsector_enum' => 'name',
  98             'profile_langskill_enum'        => 'langue_fr',
  99             'profile_medal_enum'            => 'text',
 100             'profile_name_enum'             => 'name',
 101             'profile_networking_enum'       => 'name',
 102             'profile_section_enum'          => 'text',
 103             'profile_skill_enum'            => 'text_fr',
 104             'groups'                        => 'nom',
 105             'forums'                        => 'name',
 106         );
 107
 108         foreach ($tests as $table => $field) {
 109             $testcases[$table . ' description'] =
 110                 array("SELECT  *
 111                          FROM  $table
 112                         WHERE  $field IS NULL OR $field = ''");
 113         }
 114
 115         $tests = array(
 116             'profiles' => array(
 117                 'freetext_pub' => array('public', 'private'),
 118                 'medals_pub'   => array('public', 'private'),
 119                 'alias_pub'    => array('public', 'private')
 120             ),
 121             'profile_addresses' => array(
 122                 'pub' => array('public', 'ax', 'private')
 123             ),
 124             'profile_corps' => array(
 125                 'corps_pub' => array('public', 'ax', 'private')
 126             ),
 127             'profile_job' => array(
 128                 'pub'       => array('public', 'ax', 'private'),
 129                 'email_pub' => array('public', 'ax', 'private')
 130             ),
 131             'profile_networking' => array(
 132                 'pub' => array('public', 'private')
 133             ),
 134             'profile_phones' => array(
 135                 'pub' => array('public', 'ax', 'private')
 136             ),
 137             'profile_photos' => array(
 138                 'pub' => array('public', 'private')
 139             ),
 140         );
 141
 142         foreach ($tests as $table => $test) {
 143             $select = 'p.pid, p.hrpid';
 144             $where  = array();;
 145             foreach ($test as $field => $pubs) {
 146                 $select .= ", t.$field";
 147                 $condition = array();
 148                 foreach ($pubs as $pub) {
 149                     $condition[] = "t.$field != '$pub'";
 150                 }
 151                 $where[] = '(' . implode(' AND ', $condition) . ')';
 152             }
 153             $testcases[$table . ' publicity'] =
 154                 array("SELECT  $select
 155                          FROM  $table   AS t
 156                    INNER JOIN  profiles AS p ON (t.pid = p.pid)
 157                         WHERE  " . implode(' OR ', $where));
 158         }
 159
 160         return $testcases;
 161     }
 162
 163     /**
 164      * @dataProvider dbConsistancyProvider
 165      */
 166     public function testDbConsistancy($query)
 167     {
 168         self::checkPlatal();
 169         $res = XDB::query($query);
 170         $count = $res->numRows();
 171         foreach ($res->fetchAllAssoc() as $key => $item) {
 172             echo "\n" . $key . " => {\n";
 173             foreach ($item as $field => $value) {
 174                 echo $field . ' => ' . $value . "\n";
 175             }
 176             echo "}\n";
 177         }
 178         $this->assertEquals(0, $count);
 179     }
 180
 181 /* TODO: add check on foreign keys for every table! */
 182
 183 }
 184
 185 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 186 ?>