d8e3ae49182c24a55f729c4b93beeb4a923f33d5
3 /***************************************************************************
4 * Copyright (C) 2003-2014 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) {
52 list($low, $high) = XDB
::fetchOneRow(
53 "SELECT MIN(uid), MAX(uid)
55 WHERE state = {?} AND is_admin = {?}",
63 for ($lowUID = $low; $lowUID <= $high; $lowUID +
= $batchSize) {
65 // Slight optimization for last loop: adjust to exactly what's necessary.
66 $highUID = min($high +
1, $lowUID +
$batchSize);
70 FROM log_sessions AS s
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);
86 $affectedRows +
= XDB
::affectedRows();
89 $duration = time() - $begin;
90 echo "Users with permission '$userPerms': removed $affectedRows sessions in $duration seconds ($nbBatches batches).\n";
94 * Checks for sessions without a valid associated user id.
96 function checkOrphanedSessions() {
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";
110 * Purges session events without a valid session.
112 function purgeOrphanedEvents() {
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";
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);
133 // Purge orphaned entries; events are purged automatically, sessions require explicit
134 // action from the administrator.
135 checkOrphanedSessions();
136 purgeOrphanedEvents();
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");
142 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: