Commit | Line | Data |
---|---|---|
ee68ddc1 | 1 | #!/usr/bin/php5 -q |
0337d704 | 2 | <?php |
3 | /*************************************************************************** | |
9f5bd98e | 4 | * Copyright (C) 2003-2010 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 | ***************************************************************************/ | |
22 | /* | |
23 | * verifie qu'il n'y a pas d'incoherences dans les tables de jointures | |
eaf30d86 PH |
24 | * |
25 | */ | |
0337d704 | 26 | |
27 | require('./connect.db.inc.php'); | |
28 | require("Console/Getopt.php"); | |
29 | ||
eaf30d86 | 30 | function check($sql, $commentaire='') |
0380bf85 | 31 | { |
32 | $it = XDB::iterRow($sql); | |
33 | if ($err = XDB::error()) echo $err; | |
34 | if ($it->total() > 0) { | |
0337d704 | 35 | echo "Erreur pour la verification : $commentaire\n$sql\n\n"; |
36 | echo "|"; | |
0380bf85 | 37 | while($col = $it->nextField()) { |
38 | echo "\t".$col->name."\t|"; | |
39 | } | |
0337d704 | 40 | echo "\n"; |
41 | ||
0380bf85 | 42 | while ($arr = $it->next()) { |
0337d704 | 43 | echo "|"; |
44 | foreach ($arr as $val) echo "\t$val\t|"; | |
45 | echo "\n"; | |
eaf30d86 | 46 | } |
0337d704 | 47 | echo "\n"; |
48 | } | |
49 | } | |
50 | ||
51 | function info($sql,$commentaire='') { | |
52 | global $opt_verbose; | |
53 | if ($opt_verbose) | |
54 | check($sql,$commentaire); | |
55 | } | |
56 | ||
57 | /* on parse les options */ | |
58 | $opts = Console_GetOpt::getopt($argv, "v"); | |
59 | $opt_verbose=false; | |
60 | ||
61 | if ( PEAR::isError($opts) ) { | |
62 | echo $opts->getMessage(); | |
63 | } else { | |
64 | $opts = $opts[0]; | |
65 | foreach ( $opts as $opt) { | |
66 | switch ($opt[0]) { | |
19800145 FB |
67 | case "v": |
68 | $opt_verbose=true; | |
0337d704 | 69 | break; |
70 | } | |
71 | } | |
72 | } | |
73 | ||
a552a255 | 74 | /* Validite des flags de transmission */ |
75 | check("SELECT u.user_id, nom, prenom, promo, | |
5c8a71f2 | 76 | emails_alias_pub, profile_freetext_pub, profile_medal_enum_pub |
a552a255 | 77 | FROM auth_user_md5 AS u |
78 | INNER JOIN auth_user_quick AS q USING(user_id) | |
5e4417a9 | 79 | WHERE (emails_alias_pub != 'private' AND emails_alias_pub != 'public') |
a552a255 | 80 | OR (profile_freetext_pub != 'private' AND profile_freetext_pub != 'public') |
5c8a71f2 | 81 | OR (profile_medal_enum_pub != 'private' AND profile_medal_enum_pub != 'public')", |
a552a255 | 82 | "Utilisateur n'ayant pas de flag de publicite pour leurs donnees de profil"); |
e4cd7a1f SJ |
83 | check("SELECT pid |
84 | FROM profile_addresses | |
85 | WHERE pub != 'private' AND pub !='ax' AND pub != 'public'", | |
86 | "Utiliseur n'ayant pas de flag de publicité pour une adresse."); | |
ce0b2c6f FB |
87 | check("select pid from profile_phones where pub != 'private' and pub != 'ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour un numero de téléphone"); |
88 | check("select pid from profile_networking where pub != 'private' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicité pour une adresse de networking"); | |
a552a255 | 89 | |
d293e17c VZ |
90 | /* validite des hruid */ |
91 | check("SELECT user_id, nom, prenom, promo FROM auth_user_md5 WHERE hruid IS NULL OR hruid = ''", | |
92 | "Utilisateur n'ayant pas de hruid."); | |
93 | ||
0337d704 | 94 | /* validite de aliases */ |
95 | check("SELECT a.* | |
96 | FROM aliases AS a | |
fe13bc1d | 97 | LEFT JOIN auth_user_md5 AS u ON u.user_id=a.uid |
0337d704 | 98 | WHERE (a.type='alias' OR a.type='a_vie') AND u.prenom is null"); |
99 | ||
043bbacf | 100 | /* validite de profile_education */ |
ce0b2c6f | 101 | check("select a.* from profile_education as a left join auth_user_md5 as u on u.user_id=a.pid where u.prenom is null"); |
043bbacf | 102 | check("select a.* from profile_education as a left join profile_education_enum as ad on ad.id=a.eduid where ad.name is null"); |
0337d704 | 103 | |
104 | /* validite de binet_users */ | |
5c8a71f2 FB |
105 | check("select b.* from profile_binets as b left join auth_user_md5 as u on u.user_id=b.user_id where u.prenom is null"); |
106 | check("select b.* from profile_binets as b left join profile_binet_enum as bd on bd.id=b.binet_id where bd.text is null"); | |
0337d704 | 107 | |
108 | /* validite de contacts */ | |
109 | check("select c.* from contacts as c left join auth_user_md5 as u on u.user_id=c.uid where u.prenom is null"); | |
110 | check("select c.* from contacts as c left join auth_user_md5 as u on u.user_id=c.contact where u.prenom is null"); | |
111 | ||
112 | /* validite de emails */ | |
113 | check("select e.* from emails as e left join auth_user_md5 as u on u.user_id=e.uid where e.uid and u.prenom is null"); | |
114 | ||
115 | /* validite de forums */ | |
00112b2e VZ |
116 | check("select f.* from #forums#.abos as f left join #x4dat#.auth_user_md5 as u on u.user_id=f.uid where u.prenom is null"); |
117 | check("select f.* from #forums#.abos as f left join #forums#.list as fd on fd.fid=f.fid where fd.nom is null"); | |
118 | check("select f.* from #forums#.respofaqs as f left join #forums#.list as fd on fd.fid=f.fid where fd.nom is null"); | |
119 | check("select f.* from #forums#.respofaqs as f left join #x4dat#.auth_user_md5 as u on u.user_id=f.uid where u.prenom is null"); | |
0337d704 | 120 | |
121 | /* validite de groupesx_ins */ | |
122 | check("select g.* from groupesx_ins as g left join auth_user_md5 as u on u.user_id=g.guid where u.prenom is null"); | |
123 | check("select g.* from groupesx_ins as g left join groupesx_def as gd on g.gid=g.gid where gd.text is null"); | |
124 | ||
125 | /* validite de photo */ | |
126 | check("select p.* from photo as p left join auth_user_md5 as u on u.user_id=p.uid where u.prenom is null"); | |
127 | ||
b235d980 | 128 | /* validite des formats téléphoniques */ |
e4cd7a1f SJ |
129 | check("SELECT DISTINCT g.phonePrefix |
130 | FROM geoloc_countries AS g | |
131 | WHERE EXISTS (SELECT h.phonePrefix | |
132 | FROM geoloc_countries AS h | |
133 | WHERE h.phonePrefix = g.phonePrefix | |
134 | AND h.phoneFormat != (SELECT i.phoneFormat | |
135 | FROM geoloc_countries AS i | |
136 | WHERE i.phonePrefix = g.phonePrefix | |
137 | LIMIT 1))", | |
b235d980 GB |
138 | "Préfixes téléphoniques qui ont des formats de numéros de téléphones différents selon les pays"); |
139 | ||
e4cd7a1f SJ |
140 | /* validite des champ pays */ |
141 | check("SELECT a.pid, a.countryId | |
142 | FROM profile_addresses AS a | |
143 | LEFT JOIN geoloc_countries AS gc ON (a.countryId = gc.iso_3166_1_a2) | |
144 | WHERE gc.countryFR IS NULL OR gc.countryFR = ''", | |
145 | "donne la liste des pays dans les profils qui n'ont pas d'entree correspondante dans geoloc_countries"); | |
0337d704 | 146 | |
a7de4ef7 | 147 | /* donne la liste des emails douteux que les administrateurs n'ont pas encore traité */ |
e39d6c88 | 148 | check("SELECT a1.alias, a2.alias, e1.email, e2.flags |
149 | FROM emails AS e1 | |
eaf30d86 | 150 | INNER JOIN emails AS e2 ON(e1.email = e2.email AND e1.uid!=e2.uid AND |
010268b2 | 151 | (e1.uid<e2.uid OR NOT FIND_IN_SET('active', e2.flags)) |
e39d6c88 | 152 | ) |
06f4daf9 | 153 | INNER JOIN email_watch AS w ON(w.email = e1.email AND w.state = 'pending') |
fe13bc1d FB |
154 | INNER JOIN aliases AS a1 ON(a1.uid=e1.uid AND a1.type='a_vie') |
155 | INNER JOIN aliases AS a2 ON(a2.uid=e2.uid AND a2.type='a_vie') | |
156 | INNER JOIN auth_user_md5 AS u1 ON(a1.uid=u1.user_id) | |
157 | INNER JOIN auth_user_md5 AS u2 ON(a2.uid=u2.user_id) | |
010268b2 | 158 | WHERE FIND_IN_SET('active', e1.flags) AND u1.nom!=u2.nom_usage AND u2.nom!=u1.nom_usage |
e39d6c88 | 159 | ORDER BY a1.alias", |
160 | "donne la liste des emails douteux actuellement non traites par les administrateurs"); | |
161 | ||
162 | /* donne la liste des emails dangereux ou douteux*/ | |
163 | info("SELECT a1.alias, a2.alias, e1.email, e2.flags, w.state | |
0337d704 | 164 | FROM emails AS e1 |
eaf30d86 | 165 | INNER JOIN emails AS e2 ON(e1.email = e2.email AND e1.uid!=e2.uid AND |
010268b2 | 166 | (e1.uid<e2.uid OR NOT FIND_IN_SET('active', e2.flags)) |
0337d704 | 167 | ) |
06f4daf9 | 168 | INNER JOIN email_watch AS w ON(w.email = e1.email AND w.state != 'safe') |
fe13bc1d FB |
169 | INNER JOIN aliases AS a1 ON(a1.uid=e1.uid AND a1.type='a_vie') |
170 | INNER JOIN aliases AS a2 ON(a2.uid=e2.uid AND a2.type='a_vie') | |
171 | INNER JOIN auth_user_md5 AS u1 ON(a1.uid=u1.user_id) | |
172 | INNER JOIN auth_user_md5 AS u2 ON(a2.uid=u2.user_id) | |
010268b2 | 173 | WHERE FIND_IN_SET('active', e1.flags) AND u1.nom!=u2.nom_usage AND u2.nom!=u1.nom_usage |
0337d704 | 174 | ORDER BY a1.alias", |
e39d6c88 | 175 | "donne la liste des emails dangereux ou douteux"); |
176 | ||
a7de4ef7 | 177 | /* donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois */ |
0337d704 | 178 | check("SELECT a.alias AS username, b.alias AS loginbis, b.expire |
179 | FROM aliases AS a | |
fe13bc1d | 180 | INNER JOIN aliases AS b ON ( a.uid=b.uid AND b.type != 'homonyme' and b.expire < NOW() ) |
0337d704 | 181 | WHERE a.type = 'a_vie'", |
a7de4ef7 | 182 | "donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois, il est temps de supprimer leur alias"); |
0337d704 | 183 | |
184 | /* verifie qu'il n'y a pas de gens qui recrivent sur un alias qu'ils n'ont plus */ | |
185 | ||
186 | check("SELECT a.alias AS a_un_pb, email, rewrite AS broken | |
187 | FROM aliases AS a | |
fe13bc1d FB |
188 | INNER JOIN emails AS e ON (a.uid=e.uid AND rewrite!='') |
189 | LEFT JOIN aliases AS b ON (b.uid=a.uid AND rewrite LIKE CONCAT(b.alias,'@%') AND b.type!='homonyme') | |
0337d704 | 190 | WHERE a.type='a_vie' AND b.type IS NULL","gens qui ont des rewrite sur un alias perdu"); |
191 | ||
192 | /* validite du champ matricule_ax de la table auth_user_md5 */ | |
193 | check("SELECT matricule,nom,prenom,matricule_ax,COUNT(matricule_ax) AS c | |
194 | FROM auth_user_md5 | |
195 | WHERE matricule_ax != '0' | |
196 | GROUP BY matricule_ax | |
a7de4ef7 | 197 | having c > 1", "à chaque personne de l'annuaire de l'AX (identification_ax) doit correspondre AU PLUS UNE personne de notre annuaire (auth_user_md5) -> si ce n'est pas le cas il faut regarder en manuel ce qui ne va pas !"); |
c4035ce6 PC |
198 | |
199 | /* no alumni is allowed to have empty names */ | |
dd6fce1e | 200 | check("SELECT s.uid, d.public_name |
97a98687 SJ |
201 | FROM profile_name AS s |
202 | INNER JOIN profile_display AS d ON (d.pid = s.uid) | |
dd6fce1e SJ |
203 | WHERE name = ''", "liste des personnes qui ont un de leur nom de recherche vide"); |
204 | ||
2af95f6f VZ |
205 | /* verifie qu'il n'y a pas d'utilisateurs ayant un compte Google Apps désactivé et une redirection encore active vers Google Apps */ |
206 | check("SELECT a.alias, g.g_status, u.mail_storage | |
207 | FROM auth_user_md5 AS u | |
fe13bc1d | 208 | INNER JOIN aliases AS a ON (a.uid = u.user_id AND a.type = 'a_vie') |
2af95f6f VZ |
209 | INNER JOIN gapps_accounts AS g ON (g.l_userid = u.user_id) |
210 | WHERE FIND_IN_SET('googleapps', u.mail_storage) > 0 AND g.g_status != 'active'", | |
211 | "utilisateurs ayant une redirection vers Google Apps alors que leur compte GApps n'est pas actif"); | |
212 | ||
a7de4ef7 | 213 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: |
0337d704 | 214 | ?> |