Adapts plat/al crons to the new database.
[platal.git] / bin / cron / checkdb.php
1 #!/usr/bin/php5 -q
2 <?php
3 /***************************************************************************
4 * Copyright (C) 2003-2010 Polytechnique.org *
5 * http://opensource.polytechnique.org/ *
6 * *
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. *
11 * *
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. *
16 * *
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 *
19 * Foundation, Inc., *
20 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
21 ***************************************************************************/
22 /* Checks inconsistances in tables ans joins. */
23
24 require './connect.db.inc.php';
25 require 'Console/Getopt.php';
26
27 function check($sql, $comment = '')
28 {
29 $it = XDB::iterRow($sql);
30 if ($err = XDB::error()) {
31 echo $err;
32 }
33 if ($it->total() > 0) {
34 echo "Erreur pour la verification : $comment\n$sql\n\n";
35 echo "|";
36 while($col = $it->nextField()) {
37 echo "\t" . $col->name . "\t|";
38 }
39 echo "\n";
40
41 while ($arr = $it->next()) {
42 echo "|";
43 foreach ($arr as $val) {
44 echo "\t$val\t|";
45 }
46 echo "\n";
47 }
48 echo "\n";
49 }
50 }
51
52 function info($sql, $comment = '') {
53 global $opt_verbose;
54 if ($opt_verbose) {
55 check($sql, $comment);
56 }
57 }
58
59 /* Parses options. */
60 $opts = Console_GetOpt::getopt($argv, 'v');
61 $opt_verbose = false;
62
63 if (PEAR::isError($opts)) {
64 echo $opts->getMessage();
65 } else {
66 $opts = $opts[0];
67 foreach ($opts as $opt) {
68 switch ($opt[0]) {
69 case 'v':
70 $opt_verbose = true;
71 break;
72 }
73 }
74 }
75
76 /* hrid correctness */
77 check("SELECT uid, full_name
78 FROM accounts
79 WHERE hruid IS NULL OR hruid = ''",
80 "Utilisateur n'ayant pas de hruid.");
81 check("SELECT p.pid, pd.public_name, pd.promo
82 FROM profiles AS p
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.");
86
87 /* No alumni is allowed to have empty names. */
88 check("SELECT p.pid, p.hrpid
89 FROM profiles AS p
90 INNER JOIN profile_name AS pn ON (p.pid = pn.pid)
91 WHERE name = ''",
92 "Liste des personnes qui ont un de leur nom de recherche vide.");
93
94 /* Checks rewriting on deleted aliases. */
95 check("SELECT a.alias, e.email, e.rewrite AS broken
96 FROM aliases AS a
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.");
101
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
105 FROM profiles AS p
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.');
123
124 /* Checks profile_*_enum all have a name to describe them. */
125 check("SELECT *
126 FROM profile_binet_enum
127 WHERE text IS NULL OR text = ''");
128 check("SELECT *
129 FROM profile_corps_enum
130 WHERE name IS NULL OR name = ''");
131 check("SELECT *
132 FROM profile_corps_rank_enum
133 WHERE name IS NULL OR name = ''");
134 check("SELECT *
135 FROM profile_education_degree_enum
136 WHERE degree IS NULL OR degree = ''");
137 check("SELECT *
138 FROM profile_education_enum
139 WHERE name IS NULL OR name = ''");
140 check("SELECT *
141 FROM profile_education_field_enum
142 WHERE field IS NULL OR field = ''");
143 check("SELECT *
144 FROM profile_job_enum
145 WHERE name IS NULL OR name = ''");
146 check("SELECT *
147 FROM profile_job_sector_enum
148 WHERE name IS NULL OR name = ''");
149 check("SELECT *
150 FROM profile_job_subsector_enum
151 WHERE name IS NULL OR name = ''");
152 check("SELECT *
153 FROM profile_job_subsubsector_enum
154 WHERE name IS NULL OR name = ''");
155 check("SELECT *
156 FROM profile_langskill_enum
157 WHERE langue_fr IS NULL OR langue_fr = ''");
158 check("SELECT *
159 FROM profile_medal_enum
160 WHERE text IS NULL OR text = ''");
161 check("SELECT *
162 FROM profile_name_enum
163 WHERE name IS NULL OR name = ''");
164 check("SELECT *
165 FROM profile_networking_enum
166 WHERE name IS NULL OR name = ''");
167 check("SELECT *
168 FROM profile_section_enum
169 WHERE text IS NULL OR text = ''");
170 check("SELECT *
171 FROM profile_skill_enum
172 WHERE text_fr IS NULL OR text_fr = ''");
173
174 /* Checks some other tables all have a name to describe them. */
175 check("SELECT id, nom, diminutif
176 FROM groups
177 WHERE nom IS NULL OR nom = ''");
178 check("SELECT fid, name
179 FROM forums
180 WHERE name IS NULL OR name = ''");
181
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
191 LIMIT 1))",
192 "Préfixes téléphoniques qui ont des formats de numéros de téléphones différents selon les pays.");
193
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.");
200
201 /* Lists unsound emails that remain unprocessed by the administrators. */
202 check("SELECT a1.alias, a2.alias, e1.email, e2.flags
203 FROM emails AS e1
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)
210 ORDER BY a1.alias",
211 "Donne la liste des emails douteux actuellement non traites par les administrateurs.");
212
213 /* Lists dangerous and unsound emails. */
214 info("SELECT a1.alias, a2.alias, e1.email, e2.flags, w.state
215 FROM emails AS e1
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)
222 ORDER BY a1.alias",
223 "Donne la liste des emails dangereux ou douteux.");
224
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
227 FROM aliases AS a
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.");
231
232 /* Correctness of ax_id in profiles table. */
233 check("SELECT pid, hrpid, ax_id, COUNT(ax_id) AS c
234 FROM profiles
235 WHERE ax_id != '0'
236 GROUP BY ax_id
237 HAVING c > 1",
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 !");
239
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.");
247
248 /* TODO: add check on foreign keys for every table! */
249
250 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
251 ?>