3 /***************************************************************************
4 * Copyright (C) 2003-2010 Polytechnique.org *
5 * http://opensource.polytechnique.org/ *
7 * This program is free software; you can redistribute it and/or modify *
8 * it under the terms of the GNU General Public License as published by *
9 * the Free Software Foundation; either version 2 of the License, or *
10 * (at your option) any later version. *
12 * This program is distributed in the hope that it will be useful, *
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
15 * GNU General Public License for more details. *
17 * You should have received a copy of the GNU General Public License *
18 * along with this program; if not, write to the Free Software *
20 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
21 ***************************************************************************/
22 /* Checks inconsistances in tables ans joins. */
24 require './connect.db.inc.php';
25 require 'Console/Getopt.php';
27 function check($sql, $comment = '')
29 $it = XDB
::iterRow($sql);
30 if ($err = XDB
::error()) {
33 if ($it->total() > 0) {
34 echo "Erreur pour la verification : $comment\n$sql\n\n";
36 while($col = $it->nextField()) {
37 echo "\t" . $col->name
. "\t|";
41 while ($arr = $it->next()) {
43 foreach ($arr as $val) {
52 function info($sql, $comment = '') {
55 check($sql, $comment);
60 $opts = Console_GetOpt
::getopt($argv, 'v');
63 if (PEAR
::isError($opts)) {
64 echo $opts->getMessage();
67 foreach ($opts as $opt) {
76 /* hrid correctness */
77 check("SELECT uid, full_name
79 WHERE hruid IS NULL OR hruid = ''",
80 "Utilisateur n'ayant pas de hruid.");
81 check("SELECT p.pid, pd.public_name, pd.promo
83 LEFT JOIN profile_display AS pd ON (p.pid = pd.pid)
84 WHERE p.hrpid IS NULL OR p.hrpid = ''",
85 "Profil n'ayant pas de hrpid.");
87 /* No alumni is allowed to have empty names. */
88 check("SELECT p.pid, p.hrpid
90 INNER JOIN profile_name AS pn ON (p.pid = pn.pid)
92 "Liste des personnes qui ont un de leur nom de recherche vide.");
94 /* Checks rewriting on deleted aliases. */
95 check("SELECT a.alias, e.email, e.rewrite AS broken
97 INNER JOIN emails AS e ON (a.uid = e.uid AND rewrite != '')
98 LEFT JOIN aliases AS b ON (b.uid = a.uid AND rewrite LIKE CONCAT(b.alias, '@%') AND b.type != 'homonyme')
99 WHERE a.type = 'a_vie' AND b.type IS NULL",
100 "Personnes qui ont des rewrite sur un alias perdu.");
102 /* Publicity flags correctness */
103 check("SELECT p.pid, p.hrpid, p.freetext_pub, p.medals_pub, p.alias_pub,
104 pa.pub, pc.corps_pub, pj.pub, pj.email_pub, pn.pub, pp.pub, ph.pub
106 LEFT JOIN profile_addresses AS pa ON (p.pid = pa.pid)
107 LEFT JOIN profile_corps AS pc ON (p.pid = pc.pid)
108 LEFT JOIN profile_job AS pj ON (p.pid = pj.pid)
109 LEFT JOIN profile_networking AS pn ON (p.pid = pn.pid)
110 LEFT JOIN profile_phones AS pp ON (p.pid = pp.pid)
111 LEFT JOIN profile_photos AS ph ON (p.pid = ph.pid)
112 WHERE (p.freetext_pub != 'public' AND p.freetext_pub != 'private')
113 OR (p.medals_pub != 'public' AND p.medals_pub != 'private')
114 OR (p.alias_pub != 'public' AND p.alias_pub != 'private')
115 OR (pa.pub != 'public' AND pa.pub != 'ax' AND pa.pub != 'private')
116 OR (pc.corps_pub != 'public' AND pc.corps_pub != 'ax' AND pc.corps_pub != 'private')
117 OR (pj.pub != 'public' AND pj.pub != 'ax' AND pj.pub != 'private')
118 OR (pj.email_pub != 'public' AND pj.email_pub != 'ax' AND pj.email_pub != 'private')
119 OR (pn.pub != 'public' AND pn.pub != 'private')
120 OR (pp.pub != 'public' AND pp.pub != 'ax' AND pp.pub != 'private')
121 OR (ph.pub != 'public' AND ph.pub != 'private')",
122 'Profil ayant des flags de publicité manquant.');
124 /* Checks profile_*_enum all have a name to describe them. */
126 FROM profile_binet_enum
127 WHERE text IS NULL OR text = ''");
129 FROM profile_corps_enum
130 WHERE name IS NULL OR name = ''");
132 FROM profile_corps_rank_enum
133 WHERE name IS NULL OR name = ''");
135 FROM profile_education_degree_enum
136 WHERE degree IS NULL OR degree = ''");
138 FROM profile_education_enum
139 WHERE name IS NULL OR name = ''");
141 FROM profile_education_field_enum
142 WHERE field IS NULL OR field = ''");
144 FROM profile_job_enum
145 WHERE name IS NULL OR name = ''");
147 FROM profile_job_sector_enum
148 WHERE name IS NULL OR name = ''");
150 FROM profile_job_subsector_enum
151 WHERE name IS NULL OR name = ''");
153 FROM profile_job_subsubsector_enum
154 WHERE name IS NULL OR name = ''");
156 FROM profile_langskill_enum
157 WHERE langue_fr IS NULL OR langue_fr = ''");
159 FROM profile_medal_enum
160 WHERE text IS NULL OR text = ''");
162 FROM profile_name_enum
163 WHERE name IS NULL OR name = ''");
165 FROM profile_networking_enum
166 WHERE name IS NULL OR name = ''");
168 FROM profile_section_enum
169 WHERE text IS NULL OR text = ''");
171 FROM profile_skill_enum
172 WHERE text_fr IS NULL OR text_fr = ''");
174 /* Checks some other tables all have a name to describe them. */
175 check("SELECT id, nom, diminutif
177 WHERE nom IS NULL OR nom = ''");
178 check("SELECT fid, name
180 WHERE name IS NULL OR name = ''");
182 /* Checks phone formating. */
183 check("SELECT DISTINCT g.phonePrefix
184 FROM geoloc_countries AS g
185 WHERE EXISTS (SELECT h.phonePrefix
186 FROM geoloc_countries AS h
187 WHERE h.phonePrefix = g.phonePrefix
188 AND h.phoneFormat != (SELECT i.phoneFormat
189 FROM geoloc_countries AS i
190 WHERE i.phonePrefix = g.phonePrefix
192 "Préfixes téléphoniques qui ont des formats de numéros de téléphones différents selon les pays.");
194 /* Checks correctness of countries in the profiles. */
195 check("SELECT pa.pid, pa.countryId
196 FROM profile_addresses AS pa
197 LEFT JOIN geoloc_countries AS gc ON (a.countryId = gc.iso_3166_1_a2)
198 WHERE gc.countryFR IS NULL OR gc.countryFR = ''",
199 "Donne la liste des pays dans les profils qui n'ont pas d'entrée correspondante dans geoloc_countries.");
201 /* Lists unsound emails that remain unprocessed by the administrators. */
202 check("SELECT a1.alias, a2.alias, e1.email, e2.flags
204 INNER JOIN emails AS e2 ON (e1.email = e2.email AND e1.uid != e2.uid AND
205 (e1.uid < e2.uid OR NOT FIND_IN_SET('active', e2.flags)))
206 INNER JOIN email_watch AS w ON (w.email = e1.email AND w.state = 'pending')
207 INNER JOIN aliases AS a1 ON (a1.uid = e1.uid AND a1.type = 'a_vie')
208 INNER JOIN aliases AS a2 ON (a2.uid = e2.uid AND a2.type = 'a_vie')
209 WHERE FIND_IN_SET('active', e1.flags)
211 "Donne la liste des emails douteux actuellement non traites par les administrateurs.");
213 /* Lists dangerous and unsound emails. */
214 info("SELECT a1.alias, a2.alias, e1.email, e2.flags, w.state
216 INNER JOIN emails AS e2 ON (e1.email = e2.email AND e1.uid != e2.uid AND
217 (e1.uid < e2.uid OR NOT FIND_IN_SET('active', e2.flags)))
218 INNER JOIN email_watch AS w ON (w.email = e1.email AND w.state != 'safe')
219 INNER JOIN aliases AS a1 ON (a1.uid = e1.uid AND a1.type = 'a_vie')
220 INNER JOIN aliases AS a2 ON (a2.uid = e2.uid AND a2.type = 'a_vie')
221 WHERE FIND_IN_SET('active', e1.flags)
223 "Donne la liste des emails dangereux ou douteux.");
225 /* Lists homonyms who have an alias equals to their loginbis for more than a month. */
226 check("SELECT a.alias AS username, b.alias AS loginbis, b.expire
228 INNER JOIN aliases AS b ON (a.uid=b.uid AND b.type != 'homonyme' and b.expire < NOW())
229 WHERE a.type = 'a_vie'",
230 "Donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois, il est temps de supprimer leur alias.");
232 /* Correctness of ax_id in profiles table. */
233 check("SELECT pid, hrpid, ax_id, COUNT(ax_id) AS c
238 "À chaque personne de l'annuaire de l'AX doit correspondre AU PLUS UNE personne de notre annuaire -> si ce n'est pas le cas il faut regarder en manuel ce qui ne va pas !");
240 /* Checks there is no user with a disactivated Google Apps account and an active redirection towards Google Apps. */
241 check("SELECT a.alias, g.g_status, eo.storage
242 FROM email_options AS eo
243 INNER JOIN aliases AS a ON (a.uid = eo.uid AND a.type = 'a_vie')
244 INNER JOIN gapps_accounts AS g ON (g.l_userid = eo.uid)
245 WHERE FIND_IN_SET('googleapps', eo.storage) > 0 AND g.g_status != 'active'",
246 "Utilisateurs ayant une redirection vers Google Apps alors que leur compte GApps n'est pas actif.");
248 /* TODO: add check on foreign keys for every table! */
250 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: