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             'missing nationalities' =>
  73                 array('SELECT  p.pid, p.nationality1, p.nationality2, p.nationality3
  74                          FROM  profiles         AS p
  75                     LEFT JOIN  geoloc_countries AS g1 ON (p.nationality1 = g1.iso_3166_1_a2)
  76                     LEFT JOIN  geoloc_countries AS g2 ON (p.nationality2 = g2.iso_3166_1_a2)
  77                     LEFT JOIN  geoloc_countries AS g3 ON (p.nationality3 = g3.iso_3166_1_a2)
  78                         WHERE  (p.nationality1 IS NOT NULL AND (g1.nationalityFR IS NULL OR g1.nationalityFR = \'\'))
  79                                OR (p.nationality2 IS NOT NULL AND (g2.nationalityFR IS NULL OR g2.nationalityFR = \'\'))
  80                                OR (p.nationality3 IS NOT NULL AND (g3.nationalityFR IS NULL OR g3.nationalityFR = \'\'))'),
  81
  82             'ax_id' =>
  83                 array('SELECT  pid, hrpid, ax_id, COUNT(ax_id) AS c
  84                          FROM  profiles
  85                         WHERE  ax_id != \'0\'
  86                      GROUP BY  ax_id
  87                        HAVING  c > 1'),
  88
  89             'google apps' =>
  90                 array('SELECT  a.alias, g.g_status, eo.storage
  91                          FROM  email_options  AS eo
  92                    INNER JOIN  aliases        AS a ON (a.uid = eo.uid AND a.type = \'a_vie\')
  93                    INNER JOIN  gapps_accounts AS g ON (g.l_userid = eo.uid)
  94                         WHERE  FIND_IN_SET(\'googleapps\', eo.storage) > 0 AND g.g_status != \'active\'')
  95         );
  96
  97         $tests = array(
  98             'profile_binet_enum'            => 'text',
  99             'profile_corps_enum'            => 'name',
 100             'profile_corps_rank_enum'       => 'name',
 101             'profile_education_degree_enum' => 'degree',
 102             'profile_education_enum'        => 'name',
 103             'profile_education_field_enum'  => 'field',
 104             'profile_job_enum'              => 'name',
 105             'profile_job_sector_enum'       => 'name',
 106             'profile_job_subsector_enum'    => 'name',
 107             'profile_job_subsubsector_enum' => 'name',
 108             'profile_langskill_enum'        => 'langue_fr',
 109             'profile_medal_enum'            => 'text',
 110             'profile_name_enum'             => 'name',
 111             'profile_networking_enum'       => 'name',
 112             'profile_section_enum'          => 'text',
 113             'profile_skill_enum'            => 'text_fr',
 114             'groups'                        => 'nom',
 115             'forums'                        => 'name',
 116         );
 117
 118         foreach ($tests as $table => $field) {
 119             $testcases[$table . ' description'] =
 120                 array("SELECT  *
 121                          FROM  $table
 122                         WHERE  $field IS NULL OR $field = ''");
 123         }
 124
 125         $tests = array(
 126             'profiles' => array(
 127                 'freetext_pub' => array('public', 'private'),
 128                 'medals_pub'   => array('public', 'private'),
 129                 'alias_pub'    => array('public', 'private')
 130             ),
 131             'profile_addresses' => array(
 132                 'pub' => array('public', 'ax', 'private')
 133             ),
 134             'profile_corps' => array(
 135                 'corps_pub' => array('public', 'ax', 'private')
 136             ),
 137             'profile_job' => array(
 138                 'pub'       => array('public', 'ax', 'private'),
 139                 'email_pub' => array('public', 'ax', 'private')
 140             ),
 141             'profile_networking' => array(
 142                 'pub' => array('public', 'private')
 143             ),
 144             'profile_phones' => array(
 145                 'pub' => array('public', 'ax', 'private')
 146             ),
 147             'profile_photos' => array(
 148                 'pub' => array('public', 'private')
 149             ),
 150         );
 151
 152         foreach ($tests as $table => $test) {
 153             $select = 'p.pid, p.hrpid';
 154             $where  = array();;
 155             foreach ($test as $field => $pubs) {
 156                 $select .= ", t.$field";
 157                 $condition = array();
 158                 foreach ($pubs as $pub) {
 159                     $condition[] = "t.$field != '$pub'";
 160                 }
 161                 $where[] = '(' . implode(' AND ', $condition) . ')';
 162             }
 163             $testcases[$table . ' publicity'] =
 164                 array("SELECT  $select
 165                          FROM  $table   AS t
 166                    INNER JOIN  profiles AS p ON (t.pid = p.pid)
 167                         WHERE  " . implode(' OR ', $where));
 168         }
 169
 170         return $testcases;
 171     }
 172
 173     /**
 174      * @dataProvider dbConsistancyProvider
 175      */
 176     public function testDbConsistancy($query)
 177     {
 178         self::checkPlatal();
 179         $res = XDB::query($query);
 180         $count = $res->numRows();
 181         foreach ($res->fetchAllAssoc() as $key => $item) {
 182             echo "\n" . $key . " => {\n";
 183             foreach ($item as $field => $value) {
 184                 echo $field . ' => ' . $value . "\n";
 185             }
 186             echo "}\n";
 187         }
 188         $this->assertEquals(0, $count);
 189     }
 190
 191 /* TODO: add check on foreign keys for every table! */
 192
 193 }
 194
 195 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 196 ?>