bin/cron/compliance.php

   1 #!/usr/bin/php5 -q
   2 <?php
   3 /***************************************************************************
   4 *  Copyright (C) 2003-2011 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     switch ($userPerms) {
  35       case 'user':
  36         $state   = 'active';
  37         $isAdmin = 0;
  38         break;
  39       case 'admin':
  40         $state   = 'active';
  41         $isAdmin = 1;
  42         break;
  43       case 'disabled':
  44         $state   = 'disabled';
  45         $isAdmin = 0;
  46         break;
  47       default:
  48         return;
  49     }
  50
  51     XDB::execute(
  52         "DELETE  s
  53            FROM  log_sessions AS s
  54            JOIN  (SELECT  a.uid,
  55                           (SELECT  us.start
  56                              FROM  log_sessions AS us
  57                             WHERE  us.uid = a.uid AND (us.suid IS NULL OR us.suid = 0)
  58                          ORDER BY  us.start DESC
  59                             LIMIT  {?}, 1) AS no_discard_limit
  60                     FROM  #x5dat#.accounts AS a
  61                    WHERE  a.state = {?} AND a.is_admin = {?}
  62                 ORDER BY  a.uid ASC) AS ut ON (ut.uid = s.uid)
  63           WHERE  s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH)
  64                  AND s.start < ut.no_discard_limit",
  65         $minimalBacklog - 1, $state, $isAdmin, $retentionPeriod);
  66
  67     $affectedRows = XDB::affectedRows();
  68     echo "Users with permission '$userPerms': removed $affectedRows sessions.\n";
  69 }
  70
  71 /**
  72  * Checks for sessions without a valid associated user id.
  73  */
  74 function checkOrphanedSessions() {
  75     $res = XDB::query(
  76         "SELECT  COUNT(*)
  77            FROM  log_sessions     AS s
  78       LEFT JOIN  #x5dat#.accounts AS a ON (a.uid = s.uid)
  79           WHERE  a.uid IS NULL");
  80     if (($count = $res->fetchOneCell())) {
  81         echo "Orphaned sessions: found $count orphaned sessions. Please fix that.\n";
  82     }
  83 }
  84
  85 /**
  86  * Purges session events without a valid session.
  87  */
  88 function purgeOrphanedEvents() {
  89     XDB::execute(
  90         "DELETE  e
  91            FROM  log_events AS e
  92       LEFT JOIN  log_sessions AS s ON (s.id = e.session)
  93           WHERE  s.id IS NULL");
  94     $affectedRows = XDB::affectedRows();
  95     echo "Orphaned events: removed $affectedRows events.\n";
  96 }
  97
  98 // Remove expired sessions.
  99 //  For normal user, we only keep 12 months of data (and at least the last two sessions).
 100 //  For administrator, we also keep data for 12 months, but with a backlog of at least 20 sessions.
 101 //  For disabled users, we keep data for 5 years, and with a backlog of at least 2 sessions.
 102 //  For other users, no data are discarded.
 103 discardExpiredSessions('user', 12, 2);
 104 discardExpiredSessions('admin', 12, 20);
 105 discardExpiredSessions('disabled', 60, 2);
 106
 107 // Purge orphaned entries; events are purged automatically, sessions require explicit
 108 // action from the administrator.
 109 checkOrphanedSessions();
 110 purgeOrphanedEvents();
 111
 112 // Many rows have been removed from the two logger tables. Let's optimize them.
 113 XDB::execute("OPTIMIZE TABLE log_events");
 114 XDB::execute("OPTIMIZE TABLE log_sessions");
 115
 116 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 117 ?>