Adapts bin scripts to new mail chain.
[platal.git] / bin / cron / checkdb.php
1 #!/usr/bin/php5 -q
2 <?php
3 /***************************************************************************
4 * Copyright (C) 2003-2011 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 and 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 vérification : $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 checkCount($sql, $comment = '')
53 {
54 $count = XDB::rawFetchOneCell($sql);
55 if ($err = XDB::error()) {
56 echo $err;
57 }
58 if ($count > 0) {
59 echo "Erreur pour la vérification : $comment\n$sql\n\n";
60 echo "|\tTotal\t|\n|\t$count\t|\n\n";
61 }
62 }
63
64 function info($sql, $comment = '', $onlyCounts = false)
65 {
66 global $opt_verbose;
67 if ($opt_verbose) {
68 if ($onlyCounts) {
69 checkCount($sql, $comment);
70 } else {
71 check($sql, $comment);
72 }
73 }
74 }
75
76 function infoCountEmpty($table, $field, $nonEmpty = false)
77 {
78 $sql = "SELECT COUNT(*) FROM $table";
79 if ($nonEmpty) {
80 $sql .= " WHERE $field IS NOT NULL OR $field != ''";
81 $negation = ' non';
82 } else {
83 $sql .= " WHERE $field IS NULL OR $field = ''";
84 $negation = '';
85 }
86 $comment = "Nombre de champs '$field'$negation vides dans la table '$table'.";
87 info($sql, $comment, true);
88 }
89
90 /* Parses options. */
91 $opts = Console_GetOpt::getopt($argv, 'v');
92 $opt_verbose = false;
93
94 if (PEAR::isError($opts)) {
95 echo $opts->getMessage();
96 } else {
97 $opts = $opts[0];
98 foreach ($opts as $opt) {
99 switch ($opt[0]) {
100 case 'v':
101 $opt_verbose = true;
102 break;
103 }
104 }
105 }
106
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.");
114
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'
123 GROUP BY w.email
124 ORDER BY w.email",
125 "Donne la liste des emails douteux actuellement non traites par les administrateurs.");
126
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'
135 GROUP BY w.email
136 ORDER BY w.email",
137 "Donne la liste des emails dangereux ou douteux.");
138
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.");
148
149 // Counts empty profile fields that should never be empty.
150 infoCountEmpty('profile_addresses', 'text');
151 infoCountEmpty('profile_addresses', 'postalText');
152 infoCountEmpty('profile_education', 'eduid');
153 infoCountEmpty('profile_education', 'degreeid');
154 infoCountEmpty('profile_job', 'jobid');
155 infoCountEmpty('profile_mentor', 'expertise');
156 infoCountEmpty('profile_networking', 'address');
157 infoCountEmpty('profile_phones', 'search_tel');
158 infoCountEmpty('profile_phones', 'display_tel');
159
160 // XXX: counts the number of remaining issues due to the merge (to be removed once all merge related issues have been fixed)
161 infoCountEmpty('profile_merge_issues', 'issues', true);
162
163 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
164 ?>