3 /***************************************************************************
4 * Copyright (C) 2003-2011 Polytechnique.org *
5 * http://opensource.polytechnique.org/ *
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. *
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. *
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 *
20 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
21 ***************************************************************************/
23 require './connect.db.inc.php';
26 * Discards from the logger database session details for users.
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,
33 function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) {
53 FROM log_sessions AS s
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);
67 $affectedRows = XDB
::affectedRows();
68 echo "Users with permission '$userPerms': removed $affectedRows sessions.\n";
72 * Checks for sessions without a valid associated user id.
74 function checkOrphanedSessions() {
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";
86 * Purges session events without a valid session.
88 function purgeOrphanedEvents() {
92 LEFT JOIN log_sessions AS s ON (s.id = e.session)
94 $affectedRows = XDB
::affectedRows();
95 echo "Orphaned events: removed $affectedRows events.\n";
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);
107 // Purge orphaned entries; events are purged automatically, sessions require explicit
108 // action from the administrator.
109 checkOrphanedSessions();
110 purgeOrphanedEvents();
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");
116 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: