| 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 | ?> |