bin/cron/compliance.php

   1 #!/usr/bin/php5 -q
   2 <?php
   3 /***************************************************************************
   4 *  Copyright (C) 2003-2009 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
  23 require('./connect.db.inc.php');
  24
  25 /**
  26  * Discards from the logger database session details for users.
  27  *
  28  * @param userPerms Permission level to use for that round of discards.
  29  * @param retentionPeriod Minimal number of months to keep entries for.
  30  * @param minimalBacklog Minimal number of entries to keep for any given user,
  31  *      no matter what.
  32  */
  33 function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) {
  34     XDB::execute(
  35         "DELETE  logger.s
  36            FROM  logger.sessions AS s
  37            JOIN  (SELECT  u.user_id,
  38                           (SELECT  us.start
  39                              FROM  logger.sessions AS us
  40                             WHERE  us.uid = u.user_id
  41                          ORDER BY  us.start DESC
  42                             LIMIT  {?}, 1) AS no_discard_limit
  43                     FROM  x4dat.auth_user_md5 AS u
  44                    WHERE  u.perms = {?}
  45                 ORDER BY  u.user_id ASC) AS ut ON (ut.user_id = s.uid)
  46           WHERE  s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH)
  47                  AND s.start < ut.no_discard_limit",
  48         $minimalBacklog - 1, $userPerms, $retentionPeriod);
  49
  50     $affectedRows = XDB::affectedRows();
  51     echo "Users with permission '$userPerms': removed $affectedRows sessions.\n";
  52 }
  53
  54 /**
  55  * Checks for sessions without a valid associated user id.
  56  */
  57 function checkOrphanedSessions() {
  58     $res = XDB::query(
  59         "SELECT  COUNT(*)
  60            FROM  logger.sessions AS s
  61       LEFT JOIN  x4dat.auth_user_md5 AS u ON (u.user_id = s.uid)
  62           WHERE  u.user_id IS NULL");
  63     if (($count = $res->fetchOneCell())) {
  64         echo "Orphaned sessions: found $count orphaned sessions. Please fix that.\n";
  65     }
  66 }
  67
  68 /**
  69  * Purges session events without a valid session.
  70  */
  71 function purgeOrphanedEvents() {
  72     XDB::execute(
  73         "DELETE  logger.e
  74            FROM  logger.events AS e
  75       LEFT JOIN  logger.sessions AS s ON (s.id = e.session)
  76           WHERE  s.id IS NULL");
  77     $affectedRows = XDB::affectedRows();
  78     echo "Orphaned events: removed $affectedRows events.\n";
  79 }
  80
  81 // Remove expired sessions.
  82 //  For normal user, we only keep 12 months of data (and at least the last two sessions).
  83 //  For administrator, we also keep data for 12 months, but with a backlog of at least 20 sessions.
  84 //  For disabled users, we keep data for 5 years, and with a backlog of at least 2 sessions.
  85 //  For other users, no data are discarded.
  86 discardExpiredSessions('user', 12, 2);
  87 discardExpiredSessions('admin', 12, 20);
  88 discardExpiredSessions('disabled', 60, 2);
  89
  90 // Purge orphaned entries; events are purged automatically, sessions require explicit
  91 // action from the administrator.
  92 checkOrphanedSessions();
  93 purgeOrphanedEvents();
  94
  95 // Many rows have been removed from the two logger tables. Let's optimize them.
  96 XDB::execute("OPTIMIZE TABLE logger.events");
  97 XDB::execute("OPTIMIZE TABLE logger.sessions");
  98
  99 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 100 ?>