From 93b53499adec78fef5d823110e954c4fcc6c5a7c Mon Sep 17 00:00:00 2001 From: =?utf8?q?St=C3=A9phane=20Jacob?= Date: Sun, 30 May 2010 18:45:37 +0200 Subject: [PATCH] Moves database verification to ut. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Stéphane Jacob --- bin/cron/checkdb.php | 135 ------------------------------------- ut/checkdb.php | 186 +++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 186 insertions(+), 135 deletions(-) create mode 100644 ut/checkdb.php diff --git a/bin/cron/checkdb.php b/bin/cron/checkdb.php index 689fe78..a82dfbc 100755 --- a/bin/cron/checkdb.php +++ b/bin/cron/checkdb.php @@ -73,24 +73,6 @@ if (PEAR::isError($opts)) { } } -/* hrid correctness */ -check("SELECT uid, full_name - FROM accounts - WHERE hruid IS NULL OR hruid = ''", - "Utilisateur n'ayant pas de hruid."); -check("SELECT p.pid, pd.public_name, pd.promo - FROM profiles AS p - LEFT JOIN profile_display AS pd ON (p.pid = pd.pid) - WHERE p.hrpid IS NULL OR p.hrpid = ''", - "Profil n'ayant pas de hrpid."); - -/* No alumni is allowed to have empty names. */ -check("SELECT p.pid, p.hrpid - FROM profiles AS p - INNER JOIN profile_name AS pn ON (p.pid = pn.pid) - WHERE name = ''", - "Liste des personnes qui ont un de leur nom de recherche vide."); - /* Checks rewriting on deleted aliases. */ check("SELECT a.alias, e.email, e.rewrite AS broken FROM aliases AS a @@ -99,105 +81,6 @@ check("SELECT a.alias, e.email, e.rewrite AS broken WHERE a.type = 'a_vie' AND b.type IS NULL", "Personnes qui ont des rewrite sur un alias perdu."); -/* Publicity flags correctness */ -check("SELECT p.pid, p.hrpid, p.freetext_pub, p.medals_pub, p.alias_pub, - pa.pub, pc.corps_pub, pj.pub, pj.email_pub, pn.pub, pp.pub, ph.pub - FROM profiles AS p - LEFT JOIN profile_addresses AS pa ON (p.pid = pa.pid) - LEFT JOIN profile_corps AS pc ON (p.pid = pc.pid) - LEFT JOIN profile_job AS pj ON (p.pid = pj.pid) - LEFT JOIN profile_networking AS pn ON (p.pid = pn.pid) - LEFT JOIN profile_phones AS pp ON (p.pid = pp.pid) - LEFT JOIN profile_photos AS ph ON (p.pid = ph.pid) - WHERE (p.freetext_pub != 'public' AND p.freetext_pub != 'private') - OR (p.medals_pub != 'public' AND p.medals_pub != 'private') - OR (p.alias_pub != 'public' AND p.alias_pub != 'private') - OR (pa.pub != 'public' AND pa.pub != 'ax' AND pa.pub != 'private') - OR (pc.corps_pub != 'public' AND pc.corps_pub != 'ax' AND pc.corps_pub != 'private') - OR (pj.pub != 'public' AND pj.pub != 'ax' AND pj.pub != 'private') - OR (pj.email_pub != 'public' AND pj.email_pub != 'ax' AND pj.email_pub != 'private') - OR (pn.pub != 'public' AND pn.pub != 'private') - OR (pp.pub != 'public' AND pp.pub != 'ax' AND pp.pub != 'private') - OR (ph.pub != 'public' AND ph.pub != 'private')", - 'Profil ayant des flags de publicité manquant.'); - -/* Checks profile_*_enum all have a name to describe them. */ -check("SELECT * - FROM profile_binet_enum - WHERE text IS NULL OR text = ''"); -check("SELECT * - FROM profile_corps_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_corps_rank_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_education_degree_enum - WHERE degree IS NULL OR degree = ''"); -check("SELECT * - FROM profile_education_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_education_field_enum - WHERE field IS NULL OR field = ''"); -check("SELECT * - FROM profile_job_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_job_sector_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_job_subsector_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_job_subsubsector_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_langskill_enum - WHERE langue_fr IS NULL OR langue_fr = ''"); -check("SELECT * - FROM profile_medal_enum - WHERE text IS NULL OR text = ''"); -check("SELECT * - FROM profile_name_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_networking_enum - WHERE name IS NULL OR name = ''"); -check("SELECT * - FROM profile_section_enum - WHERE text IS NULL OR text = ''"); -check("SELECT * - FROM profile_skill_enum - WHERE text_fr IS NULL OR text_fr = ''"); - -/* Checks some other tables all have a name to describe them. */ -check("SELECT id, nom, diminutif - FROM groups - WHERE nom IS NULL OR nom = ''"); -check("SELECT fid, name - FROM forums - WHERE name IS NULL OR name = ''"); - -/* Checks phone formating. */ -check("SELECT DISTINCT g.phonePrefix - FROM geoloc_countries AS g - WHERE EXISTS (SELECT h.phonePrefix - FROM geoloc_countries AS h - WHERE h.phonePrefix = g.phonePrefix - AND h.phoneFormat != (SELECT i.phoneFormat - FROM geoloc_countries AS i - WHERE i.phonePrefix = g.phonePrefix - LIMIT 1))", - "Préfixes téléphoniques qui ont des formats de numéros de téléphones différents selon les pays."); - -/* Checks correctness of countries in the profiles. */ -check("SELECT pa.pid, pa.countryId - FROM profile_addresses AS pa - LEFT JOIN geoloc_countries AS gc ON (a.countryId = gc.iso_3166_1_a2) - WHERE gc.countryFR IS NULL OR gc.countryFR = ''", - "Donne la liste des pays dans les profils qui n'ont pas d'entrée correspondante dans geoloc_countries."); - /* Lists unsound emails that remain unprocessed by the administrators. */ check("SELECT a1.alias, a2.alias, e1.email, e2.flags FROM emails AS e1 @@ -229,23 +112,5 @@ check("SELECT a.alias AS username, b.alias AS loginbis, b.expire WHERE a.type = 'a_vie'", "Donne la liste des homonymes qui ont un alias égal à leur loginbis depuis plus d'un mois, il est temps de supprimer leur alias."); -/* Correctness of ax_id in profiles table. */ -check("SELECT pid, hrpid, ax_id, COUNT(ax_id) AS c - FROM profiles - WHERE ax_id != '0' - GROUP BY ax_id - HAVING c > 1", - "À chaque personne de l'annuaire de l'AX doit correspondre AU PLUS UNE personne de notre annuaire -> si ce n'est pas le cas il faut regarder en manuel ce qui ne va pas !"); - -/* Checks there is no user with a disactivated Google Apps account and an active redirection towards Google Apps. */ -check("SELECT a.alias, g.g_status, eo.storage - FROM email_options AS eo - INNER JOIN aliases AS a ON (a.uid = eo.uid AND a.type = 'a_vie') - INNER JOIN gapps_accounts AS g ON (g.l_userid = eo.uid) - WHERE FIND_IN_SET('googleapps', eo.storage) > 0 AND g.g_status != 'active'", - "Utilisateurs ayant une redirection vers Google Apps alors que leur compte GApps n'est pas actif."); - -/* TODO: add check on foreign keys for every table! */ - // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: ?> diff --git a/ut/checkdb.php b/ut/checkdb.php new file mode 100644 index 0000000..fb2c25f --- /dev/null +++ b/ut/checkdb.php @@ -0,0 +1,186 @@ + + array('SELECT uid, full_name + FROM accounts + WHERE hruid IS NULL OR hruid = \'\''), + + 'hrpid' => + array('SELECT p.pid, pd.public_name, pd.promo + FROM profiles AS p + LEFT JOIN profile_display AS pd ON (p.pid = pd.pid) + WHERE p.hrpid IS NULL OR p.hrpid = \'\''), + + 'name' => + array('SELECT p.pid, p.hrpid + FROM profiles AS p + INNER JOIN profile_name AS pn ON (p.pid = pn.pid) + WHERE name = \'\''), + + 'phone formatting' => + array('SELECT DISTINCT g.phonePrefix + FROM geoloc_countries AS g + WHERE EXISTS (SELECT h.phonePrefix + FROM geoloc_countries AS h + WHERE h.phonePrefix = g.phonePrefix + AND h.phoneFormat != (SELECT i.phoneFormat + FROM geoloc_countries AS i + WHERE i.phonePrefix = g.phonePrefix + LIMIT 1))'), + + 'missing countries' => + array('SELECT pa.pid, pa.countryId + FROM profile_addresses AS pa + LEFT JOIN geoloc_countries AS gc ON (pa.countryId = gc.iso_3166_1_a2) + WHERE gc.countryFR IS NULL OR gc.countryFR = \'\''), + + 'ax_id' => + array('SELECT pid, hrpid, ax_id, COUNT(ax_id) AS c + FROM profiles + WHERE ax_id != \'0\' + GROUP BY ax_id + HAVING c > 1'), + + 'google apps' => + array('SELECT a.alias, g.g_status, eo.storage + FROM email_options AS eo + INNER JOIN aliases AS a ON (a.uid = eo.uid AND a.type = \'a_vie\') + INNER JOIN gapps_accounts AS g ON (g.l_userid = eo.uid) + WHERE FIND_IN_SET(\'googleapps\', eo.storage) > 0 AND g.g_status != \'active\'') + ); + + $tests = array( + 'profile_binet_enum' => 'text', + 'profile_corps_enum' => 'name', + 'profile_corps_rank_enum' => 'name', + 'profile_education_degree_enum' => 'degree', + 'profile_education_enum' => 'name', + 'profile_education_field_enum' => 'field', + 'profile_job_enum' => 'name', + 'profile_job_sector_enum' => 'name', + 'profile_job_subsector_enum' => 'name', + 'profile_job_subsubsector_enum' => 'name', + 'profile_langskill_enum' => 'langue_fr', + 'profile_medal_enum' => 'text', + 'profile_name_enum' => 'name', + 'profile_networking_enum' => 'name', + 'profile_section_enum' => 'text', + 'profile_skill_enum' => 'text_fr', + 'groups' => 'nom', + 'forums' => 'name', + ); + + foreach ($tests as $table => $field) { + $testcases[$table . ' description'] = + array("SELECT * + FROM $table + WHERE $field IS NULL OR $field = ''"); + } + + $tests = array( + 'profiles' => array( + 'freetext_pub' => array('public', 'private'), + 'medals_pub' => array('public', 'private'), + 'alias_pub' => array('public', 'private') + ), + 'profile_addresses' => array( + 'pub' => array('public', 'ax', 'private') + ), + 'profile_corps' => array( + 'corps_pub' => array('public', 'ax', 'private') + ), + 'profile_job' => array( + 'pub' => array('public', 'ax', 'private'), + 'email_pub' => array('public', 'ax', 'private') + ), + 'profile_networking' => array( + 'pub' => array('public', 'private') + ), + 'profile_phones' => array( + 'pub' => array('public', 'ax', 'private') + ), + 'profile_photos' => array( + 'pub' => array('public', 'private') + ), + ); + + foreach ($tests as $table => $test) { + $select = 'p.pid, p.hrpid'; + $where = array();; + foreach ($test as $field => $pubs) { + $select .= ", t.$field"; + $condition = array(); + foreach ($pubs as $pub) { + $condition[] = "t.$field != '$pub'"; + } + $where[] = '(' . implode(' AND ', $condition) . ')'; + } + $testcases[$table . ' publicity'] = + array("SELECT $select + FROM $table AS t + INNER JOIN profiles AS p ON (t.pid = p.pid) + WHERE " . implode(' OR ', $where)); + } + + return $testcases; + } + + /** + * @dataProvider dbConsistancyProvider + */ + public function testDbConsistancy($query) + { + self::checkPlatal(); + $res = XDB::query($query); + $count = $res->numRows(); + foreach ($res->fetchAllAssoc() as $key => $item) { + echo "\n" . $key . " => {\n"; + foreach ($item as $field => $value) { + echo $field . ' => ' . $value . "\n"; + } + echo "}\n"; + } + $this->assertEquals(0, $count); + } + +/* TODO: add check on foreign keys for every table! */ + +} + +// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: +?> -- 2.1.4