3 /***************************************************************************
4 * Copyright (C) 2003-2013 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 and 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 vérification : $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 checkCount($sql, $comment = '')
54 $count = XDB
::rawFetchOneCell($sql);
55 if ($err = XDB
::error()) {
59 echo "Erreur pour la vérification : $comment\n$sql\n\n";
60 echo "|\tTotal\t|\n|\t$count\t|\n\n";
64 function info($sql, $comment = '', $onlyCounts = false
)
69 checkCount($sql, $comment);
71 check($sql, $comment);
76 function infoCountEmpty($table, $field, $nonEmpty = false
)
78 $sql = "SELECT COUNT(*) FROM $table";
80 $sql .= " WHERE $field IS NOT NULL OR $field != ''";
83 $sql .= " WHERE $field IS NULL OR $field = ''";
86 $comment = "Nombre de champs '$field'$negation vides dans la table '$table'.";
87 info($sql, $comment, true
);
91 $opts = Console_GetOpt
::getopt($argv, 'v');
94 if (PEAR
::isError($opts)) {
95 echo $opts->getMessage();
98 foreach ($opts as $opt) {
107 /* Checks rewriting on deleted aliases. */
108 check("SELECT s1.email, r.redirect, r.rewrite AS broken
109 FROM email_redirect_account AS r
110 INNER JOIN email_source_account AS s1 ON (r.uid = s1.uid AND s1.type = 'forlife')
111 LEFT JOIN email_source_account AS s2 ON (r.uid = s2.uid AND r.rewrite LIKE CONCAT(s2.email, '@%'))
112 WHERE r.rewrite != '' AND s2.uid IS NULL",
113 "Personnes qui ont des rewrite sur un alias perdu.");
115 /* Lists unsound emails that remain unprocessed by the administrators. */
116 check("SELECT s1.email, s2.email, w.email
117 FROM email_watch AS w
118 INNER JOIN email_redirect_account AS r1 ON (w.email = r1.redirect)
119 LEFT JOIN email_redirect_account AS r2 ON (w.email = r2.redirect AND r1.uid != r2.uid)
120 INNER JOIN email_source_account AS s1 ON (s1.uid = r1.uid AND s1.type = 'forlife')
121 LEFT JOIN email_source_account AS s2 ON (s2.uid = r2.uid AND s2.type = 'forlife')
122 WHERE w.state = 'pending'
125 "Donne la liste des emails douteux actuellement non traites par les administrateurs.");
127 /* Lists dangerous and unsound emails. */
128 info("SELECT s1.email, s2.email, w.email, w.state
129 FROM email_watch AS w
130 INNER JOIN email_redirect_account AS r1 ON (w.email = r1.redirect)
131 LEFT JOIN email_redirect_account AS r2 ON (w.email = r2.redirect AND r1.uid != r2.uid)
132 INNER JOIN email_source_account AS s1 ON (s1.uid = r1.uid AND s1.type = 'forlife')
133 LEFT JOIN email_source_account AS s2 ON (s2.uid = r2.uid AND s2.type = 'forlife')
134 WHERE w.state != 'safe'
137 "Donne la liste des emails dangereux ou douteux.");
139 /* Lists homonyms who have an alias equals to their loginbis for more than a month. */
140 check("SELECT e.email AS homonym, f.email AS forlife, e.expire
141 FROM email_source_account AS e
142 INNER JOIN homonyms_list AS l ON (e.uid = l.uid)
143 INNER JOIN homonyms_list AS h ON (l.hrmid = h.hrmid)
144 INNER JOIN email_source_account AS f ON (h.uid = f.uid AND f.type = 'forlife')
145 WHERE e.expire < NOW()
146 ORDER BY homonym, forlife",
147 "Donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois, il est temps de supprimer leur alias.");
149 // Check foreign keys manually when not possible because of local key beeing a primary key, and thus can not be null.
151 FROM profile_addresses AS a
152 WHERE a.type = 'home' AND NOT EXISTS (SELECT *
154 WHERE a.pid = p.pid)",
155 "Adresses de type 'home' reliées à un profil inexsitant.");
157 FROM profile_addresses AS a
158 WHERE a.type = 'job' AND NOT EXISTS (SELECT *
159 FROM profile_job AS j
160 WHERE a.pid = j.pid AND a.id = j.id)",
161 "Adresses de type 'job' reliées à un emploi inexsitant.");
163 FROM profile_addresses AS a
164 WHERE a.type = 'hq' AND NOT EXISTS (SELECT *
165 FROM profile_job_enum AS je
166 WHERE a.jobid = je.id)",
167 "Adresses de type 'hq' reliées à une entreprise inexsitante.");
169 FROM profile_addresses AS a
170 WHERE a.type = 'group' AND NOT EXISTS (SELECT *
172 WHERE a.groupid = g.id)",
173 "Adresses de type 'group' reliées à un groupe inexsitant.");
176 FROM profile_medals AS m
177 WHERE m.gid != 0 AND NOT EXISTS (SELECT *
178 FROM profile_medal_grade_enum AS mg
179 WHERE m.mid = mg.mid AND m.gid = mg.gid)",
180 "Médailles avec grade sans correspondance dans la liste des grades.");
183 FROM profile_phones AS p
184 WHERE p.link_type = 'address' AND NOT EXISTS (SELECT *
185 FROM profile_addresses AS a
186 WHERE p.pid = a.pid AND p.link_id = a.id)",
187 "Téléphones de type 'address' reliés à une adresses inexistante.");
189 FROM profile_phones AS p
190 WHERE p.link_type = 'pro' AND NOT EXISTS (SELECT *
191 FROM profile_job AS j
192 WHERE p.pid = j.pid AND p.link_id = j.id)",
193 "Téléphones de type 'pro' reliés à un emploi inexistant.");
195 FROM profile_phones AS p
196 WHERE p.link_type = 'user' AND NOT EXISTS (SELECT *
198 WHERE p.pid = pf.pid)",
199 "Téléphones de type 'user' reliés à un profil inexistant.");
201 FROM profile_phones AS p
202 WHERE p.link_type = 'hq' AND NOT EXISTS (SELECT *
203 FROM profile_job_enum AS je
204 WHERE p.link_id = je.id)",
205 "Téléphones de type 'hq' reliés à une entreprise inexistante.");
207 FROM profile_phones AS p
208 WHERE p.link_type = 'group' AND NOT EXISTS (SELECT *
210 WHERE p.link_id = g.id)",
211 "Téléphones de type 'group' reliés à un groupe inexistant.");
213 // List domain aliasing with depth higher than 1: they will not be found by postfix.
214 check("SELECT evd.name
215 FROM email_virtual_domains AS evd
216 INNER JOIN email_virtual_domains AS evd2 ON (evd.aliasing = evd2.id)
217 WHERE evd2.id != evd2.aliasing",
218 "Domaines aliasés de niveau 2 ou plus qui ne sont pas vu par postfix.");
220 // Account viewing statistics
221 info("SELECT nb_profiles, hruid
223 SELECT a.hruid AS hruid, COUNT(DISTINCT le.data) AS nb_profiles
224 FROM log_events AS le
225 LEFT JOIN log_sessions AS ls ON (ls.id = le.session)
226 LEFT JOIN accounts AS a ON (a.uid = ls.uid)
227 WHERE le.action = 30 AND ls.start >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
230 WHERE nb_profiles >= 100
231 ORDER BY nb_profiles DESC",
232 "Camarades ayant consulté plus de 100 fiches au cours des 7 derniers jours.");
234 // Counts empty profile fields that should never be empty.
235 infoCountEmpty('profile_addresses', 'type');
236 infoCountEmpty('profile_phones', 'link_type');
237 infoCountEmpty('profile_addresses', 'text');
238 infoCountEmpty('profile_addresses', 'postalText');
239 infoCountEmpty('profile_education', 'eduid');
240 infoCountEmpty('profile_education', 'degreeid');
241 infoCountEmpty('profile_job', 'jobid');
242 infoCountEmpty('profile_mentor', 'expertise');
243 infoCountEmpty('profile_networking', 'address');
244 infoCountEmpty('profile_phones', 'search_tel');
245 infoCountEmpty('profile_phones', 'display_tel');
247 // XXX: counts the number of remaining issues due to the merge (to be removed once all merge related issues have been fixed)
248 infoCountEmpty('profile_merge_issues', 'issues', true
);
250 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: