Commit | Line | Data |
---|---|---|
ee68ddc1 | 1 | #!/usr/bin/php5 -q |
0337d704 | 2 | <?php |
3 | /*************************************************************************** | |
5ddeb07c | 4 | * Copyright (C) 2003-2007 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]) { | |
67 | case "v": | |
68 | $opt_verbose=true; | |
69 | echo "Mode verbeux\n"; | |
70 | break; | |
71 | } | |
72 | } | |
73 | } | |
74 | ||
a552a255 | 75 | /* Validite des flags de transmission */ |
76 | check("SELECT u.user_id, nom, prenom, promo, | |
77 | profile_mobile_pub, emails_alias_pub, profile_web_pub, profile_freetext_pub, profile_medals_pub | |
78 | FROM auth_user_md5 AS u | |
79 | INNER JOIN auth_user_quick AS q USING(user_id) | |
80 | WHERE (profile_mobile_pub != 'private' AND profile_mobile_pub != 'ax' AND profile_mobile_pub != 'public') | |
81 | OR (emails_alias_pub != 'private' AND emails_alias_pub != 'public') | |
82 | OR (profile_web_pub != 'private' AND profile_web_pub != 'public') | |
83 | OR (profile_freetext_pub != 'private' AND profile_freetext_pub != 'public') | |
84 | OR (profile_medals_pub != 'private' AND profile_medals_pub != 'public')", | |
85 | "Utilisateur n'ayant pas de flag de publicite pour leurs donnees de profil"); | |
86 | check("select uid from adresses where pub != 'private' and pub !='ax' and pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour une adresse"); | |
87 | check("select uid from tels where tel_pub != 'private' and tel_pub !='ax' and tel_pub != 'public'", "Utiliseur n'ayant pas de flag de publicite pour un numero de telephone"); | |
88 | ||
0337d704 | 89 | /* validite de adresses */ |
7ce7fcc4 | 90 | check("select uid, count(adrid) from adresses group by uid having count(adrid) > 7", "Utilisateurs ayant trop d'adresses"); |
0337d704 | 91 | |
0337d704 | 92 | /* Validite des tables de langues, competences, mentoring*/ |
7ce7fcc4 | 93 | check("select uid, count(lid) from langues_ins group by uid having count(lid) > 10","Utilisateurs ayant trop de langues"); |
94 | check("select uid, count(cid) from competences_ins group by uid having count(cid) > 20","Utilisateurs ayant trop de competences"); | |
0337d704 | 95 | |
96 | /* validite de aliases */ | |
97 | check("SELECT a.* | |
98 | FROM aliases AS a | |
99 | LEFT JOIN auth_user_md5 AS u ON u.user_id=a.id | |
100 | WHERE (a.type='alias' OR a.type='a_vie') AND u.prenom is null"); | |
101 | ||
102 | /* validite de applis_ins */ | |
103 | check("select a.* from applis_ins as a left join auth_user_md5 as u on u.user_id=a.uid where u.prenom is null"); | |
104 | check("select a.* from applis_ins as a left join applis_def as ad on ad.id=a.aid where ad.text is null"); | |
105 | ||
106 | /* validite de binet_users */ | |
107 | check("select b.* from binets_ins as b left join auth_user_md5 as u on u.user_id=b.user_id where u.prenom is null"); | |
108 | check("select b.* from binets_ins as b left join binets_def as bd on bd.id=b.binet_id where bd.text is null"); | |
109 | ||
110 | /* validite de contacts */ | |
111 | 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"); | |
112 | 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"); | |
113 | ||
114 | /* validite de emails */ | |
115 | 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"); | |
116 | ||
117 | /* validite de forums */ | |
118 | check("select f.* from forums.abos as f left join auth_user_md5 as u on u.user_id=f.uid where u.prenom is null"); | |
119 | check("select f.* from forums.abos as f left join forums.list as fd on fd.fid=f.fid where fd.nom is null"); | |
120 | check("select f.* from forums.respofaqs as f left join forums.list as fd on fd.fid=f.fid where fd.nom is null"); | |
121 | check("select f.* from forums.respofaqs as f left join auth_user_md5 as u on u.user_id=f.uid where u.prenom is null"); | |
122 | ||
123 | /* validite de groupesx_ins */ | |
124 | 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"); | |
125 | check("select g.* from groupesx_ins as g left join groupesx_def as gd on g.gid=g.gid where gd.text is null"); | |
126 | ||
127 | /* validite de photo */ | |
128 | 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"); | |
129 | ||
130 | /* validite des champ pays et region */ | |
131 | check("SELECT a.uid, a.country FROM adresses AS a LEFT JOIN geoloc_pays AS gp ON a.country = gp.a2 WHERE gp.pays IS NULL","donne la liste des pays dans les profils qui n'ont pas d'entree correspondante dans geoloc_pays"); | |
a7de4ef7 | 132 | /* les régions ne sont valides que dans les adresses pros */ |
c8910046 | 133 | check("SELECT e.uid, e.country, e.region FROM entreprises AS e LEFT JOIN geoloc_region AS gr ON (e.country = gr.a2 AND e.region = gr.region) WHERE e.region != '' AND gr.name IS NULL","donne la liste des regions dans les profils pros qui n'ont pas d'entree correspondante dans geoloc_region"); |
0337d704 | 134 | |
a7de4ef7 | 135 | /* donne la liste des emails douteux que les administrateurs n'ont pas encore traité */ |
e39d6c88 | 136 | check("SELECT a1.alias, a2.alias, e1.email, e2.flags |
137 | FROM emails AS e1 | |
eaf30d86 | 138 | INNER JOIN emails AS e2 ON(e1.email = e2.email AND e1.uid!=e2.uid AND |
010268b2 | 139 | (e1.uid<e2.uid OR NOT FIND_IN_SET('active', e2.flags)) |
e39d6c88 | 140 | ) |
141 | INNER JOIN emails_watch AS w ON(w.email = e1.email AND w.state = 'pending') | |
142 | INNER JOIN aliases AS a1 ON(a1.id=e1.uid AND a1.type='a_vie') | |
143 | INNER JOIN aliases AS a2 ON(a2.id=e2.uid AND a2.type='a_vie') | |
144 | INNER JOIN auth_user_md5 AS u1 ON(a1.id=u1.user_id) | |
145 | INNER JOIN auth_user_md5 AS u2 ON(a2.id=u2.user_id) | |
010268b2 | 146 | WHERE FIND_IN_SET('active', e1.flags) AND u1.nom!=u2.nom_usage AND u2.nom!=u1.nom_usage |
e39d6c88 | 147 | ORDER BY a1.alias", |
148 | "donne la liste des emails douteux actuellement non traites par les administrateurs"); | |
149 | ||
150 | /* donne la liste des emails dangereux ou douteux*/ | |
151 | info("SELECT a1.alias, a2.alias, e1.email, e2.flags, w.state | |
0337d704 | 152 | FROM emails AS e1 |
eaf30d86 | 153 | INNER JOIN emails AS e2 ON(e1.email = e2.email AND e1.uid!=e2.uid AND |
010268b2 | 154 | (e1.uid<e2.uid OR NOT FIND_IN_SET('active', e2.flags)) |
0337d704 | 155 | ) |
866f1333 | 156 | INNER JOIN emails_watch AS w ON(w.email = e1.email AND w.state != 'safe') |
0337d704 | 157 | INNER JOIN aliases AS a1 ON(a1.id=e1.uid AND a1.type='a_vie') |
158 | INNER JOIN aliases AS a2 ON(a2.id=e2.uid AND a2.type='a_vie') | |
159 | INNER JOIN auth_user_md5 AS u1 ON(a1.id=u1.user_id) | |
160 | INNER JOIN auth_user_md5 AS u2 ON(a2.id=u2.user_id) | |
010268b2 | 161 | WHERE FIND_IN_SET('active', e1.flags) AND u1.nom!=u2.nom_usage AND u2.nom!=u1.nom_usage |
0337d704 | 162 | ORDER BY a1.alias", |
e39d6c88 | 163 | "donne la liste des emails dangereux ou douteux"); |
164 | ||
a7de4ef7 | 165 | /* donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois */ |
0337d704 | 166 | check("SELECT a.alias AS username, b.alias AS loginbis, b.expire |
167 | FROM aliases AS a | |
168 | INNER JOIN aliases AS b ON ( a.id=b.id AND b.type != 'homonyme' and b.expire < NOW() ) | |
169 | WHERE a.type = 'a_vie'", | |
a7de4ef7 | 170 | "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 | 171 | |
172 | /* verifie qu'il n'y a pas de gens qui recrivent sur un alias qu'ils n'ont plus */ | |
173 | ||
174 | check("SELECT a.alias AS a_un_pb, email, rewrite AS broken | |
175 | FROM aliases AS a | |
176 | INNER JOIN emails AS e ON (a.id=e.uid AND rewrite!='') | |
177 | LEFT JOIN aliases AS b ON (b.id=a.id AND rewrite LIKE CONCAT(b.alias,'@%') AND b.type!='homonyme') | |
178 | WHERE a.type='a_vie' AND b.type IS NULL","gens qui ont des rewrite sur un alias perdu"); | |
179 | ||
180 | /* validite du champ matricule_ax de la table auth_user_md5 */ | |
181 | check("SELECT matricule,nom,prenom,matricule_ax,COUNT(matricule_ax) AS c | |
182 | FROM auth_user_md5 | |
183 | WHERE matricule_ax != '0' | |
184 | GROUP BY matricule_ax | |
a7de4ef7 | 185 | 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 !"); |
186 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: | |
0337d704 | 187 | ?> |