Adapts ut to new mail chain.
[platal.git] / bin / cron / checkdb.php
CommitLineData
ee68ddc1 1#!/usr/bin/php5 -q
0337d704 2<?php
3/***************************************************************************
5e1513f6 4 * Copyright (C) 2003-2011 Polytechnique.org *
0337d704 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 ***************************************************************************/
b209d2bb 22/* Checks inconsistances in tables and joins. */
0337d704 23
5ecaa68e
SJ
24require './connect.db.inc.php';
25require 'Console/Getopt.php';
0337d704 26
5ecaa68e 27function check($sql, $comment = '')
0380bf85 28{
29 $it = XDB::iterRow($sql);
5ecaa68e
SJ
30 if ($err = XDB::error()) {
31 echo $err;
32 }
0380bf85 33 if ($it->total() > 0) {
b209d2bb 34 echo "Erreur pour la vérification : $comment\n$sql\n\n";
0337d704 35 echo "|";
0380bf85 36 while($col = $it->nextField()) {
5ecaa68e 37 echo "\t" . $col->name . "\t|";
0380bf85 38 }
0337d704 39 echo "\n";
40
0380bf85 41 while ($arr = $it->next()) {
0337d704 42 echo "|";
5ecaa68e
SJ
43 foreach ($arr as $val) {
44 echo "\t$val\t|";
45 }
0337d704 46 echo "\n";
eaf30d86 47 }
0337d704 48 echo "\n";
49 }
50}
51
b209d2bb
SJ
52function 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
243cdb7b
SJ
64function info($sql, $comment = '', $onlyCounts = false)
65{
0337d704 66 global $opt_verbose;
5ecaa68e 67 if ($opt_verbose) {
b209d2bb
SJ
68 if ($onlyCounts) {
69 checkCount($sql, $comment);
70 } else {
71 check($sql, $comment);
72 }
5ecaa68e 73 }
0337d704 74}
75
243cdb7b
SJ
76function 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
5ecaa68e
SJ
90/* Parses options. */
91$opts = Console_GetOpt::getopt($argv, 'v');
92$opt_verbose = false;
0337d704 93
5ecaa68e 94if (PEAR::isError($opts)) {
0337d704 95 echo $opts->getMessage();
96} else {
97 $opts = $opts[0];
5ecaa68e 98 foreach ($opts as $opt) {
0337d704 99 switch ($opt[0]) {
5ecaa68e
SJ
100 case 'v':
101 $opt_verbose = true;
0337d704 102 break;
103 }
104 }
105}
106
5ecaa68e
SJ
107/* Checks rewriting on deleted aliases. */
108check("SELECT a.alias, e.email, e.rewrite AS broken
109 FROM aliases AS a
110 INNER JOIN emails AS e ON (a.uid = e.uid AND rewrite != '')
111 LEFT JOIN aliases AS b ON (b.uid = a.uid AND rewrite LIKE CONCAT(b.alias, '@%') AND b.type != 'homonyme')
112 WHERE a.type = 'a_vie' AND b.type IS NULL",
113 "Personnes qui ont des rewrite sur un alias perdu.");
114
5ecaa68e 115/* Lists unsound emails that remain unprocessed by the administrators. */
e39d6c88 116check("SELECT a1.alias, a2.alias, e1.email, e2.flags
5ecaa68e
SJ
117 FROM emails AS e1
118 INNER JOIN emails AS e2 ON (e1.email = e2.email AND e1.uid != e2.uid AND
119 (e1.uid < e2.uid OR NOT FIND_IN_SET('active', e2.flags)))
120 INNER JOIN email_watch AS w ON (w.email = e1.email AND w.state = 'pending')
121 INNER JOIN aliases AS a1 ON (a1.uid = e1.uid AND a1.type = 'a_vie')
122 INNER JOIN aliases AS a2 ON (a2.uid = e2.uid AND a2.type = 'a_vie')
123 WHERE FIND_IN_SET('active', e1.flags)
124 ORDER BY a1.alias",
125 "Donne la liste des emails douteux actuellement non traites par les administrateurs.");
126
127/* Lists dangerous and unsound emails. */
e39d6c88 128info("SELECT a1.alias, a2.alias, e1.email, e2.flags, w.state
5ecaa68e
SJ
129 FROM emails AS e1
130 INNER JOIN emails AS e2 ON (e1.email = e2.email AND e1.uid != e2.uid AND
131 (e1.uid < e2.uid OR NOT FIND_IN_SET('active', e2.flags)))
132 INNER JOIN email_watch AS w ON (w.email = e1.email AND w.state != 'safe')
133 INNER JOIN aliases AS a1 ON (a1.uid = e1.uid AND a1.type = 'a_vie')
134 INNER JOIN aliases AS a2 ON (a2.uid = e2.uid AND a2.type = 'a_vie')
135 WHERE FIND_IN_SET('active', e1.flags)
136 ORDER BY a1.alias",
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. */
0337d704 140check("SELECT a.alias AS username, b.alias AS loginbis, b.expire
5ecaa68e
SJ
141 FROM aliases AS a
142 INNER JOIN aliases AS b ON (a.uid=b.uid AND b.type != 'homonyme' and b.expire < NOW())
0337d704 143 WHERE a.type = 'a_vie'",
5ecaa68e
SJ
144 "Donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois, il est temps de supprimer leur alias.");
145
243cdb7b
SJ
146// Counts empty profile fields that should never be empty.
147infoCountEmpty('profile_addresses', 'text');
148infoCountEmpty('profile_addresses', 'postalText');
149infoCountEmpty('profile_education', 'eduid');
150infoCountEmpty('profile_education', 'degreeid');
151infoCountEmpty('profile_job', 'jobid');
152infoCountEmpty('profile_mentor', 'expertise');
153infoCountEmpty('profile_networking', 'address');
154infoCountEmpty('profile_phones', 'search_tel');
155infoCountEmpty('profile_phones', 'display_tel');
156
16ab1e34 157// XXX: counts the number of remaining issues due to the merge (to be removed once all merge related issues have been fixed)
243cdb7b 158infoCountEmpty('profile_merge_issues', 'issues', true);
16ab1e34 159
a7de4ef7 160// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
0337d704 161?>