bin/cron/compliance.php

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