3 /***************************************************************************
4 * Copyright (C) 2003-2013 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() {
78 FROM log_sessions AS s
79 LEFT JOIN #x5dat#.accounts AS a ON (a.uid = s.uid)
80 WHERE a.uid IS NULL");
81 if (($count = $res->fetchOneCell())) {
82 $duration = time() - $begin;
83 echo "Orphaned sessions: found $count orphaned sessions in $duration seconds. Please fix that.\n";
88 * Purges session events without a valid session.
90 function purgeOrphanedEvents() {
95 LEFT JOIN log_sessions AS s ON (s.id = e.session)
97 $affectedRows = XDB
::affectedRows();
98 $duration = time() - $begin;
99 echo "Orphaned events: removed $affectedRows events in $duration seconds.\n";
102 // Remove expired sessions.
103 // For normal user, we only keep 12 months of data (and at least the last two sessions).
104 // For administrator, we also keep data for 12 months, but with a backlog of at least 20 sessions.
105 // For disabled users, we keep data for 5 years, and with a backlog of at least 2 sessions.
106 // For other users, no data are discarded.
107 discardExpiredSessions('user', 12, 2);
108 discardExpiredSessions('admin', 12, 20);
109 discardExpiredSessions('disabled', 60, 2);
111 // Purge orphaned entries; events are purged automatically, sessions require explicit
112 // action from the administrator.
113 checkOrphanedSessions();
114 purgeOrphanedEvents();
116 // Many rows have been removed from the two logger tables. Let's optimize them.
117 XDB
::execute("OPTIMIZE TABLE log_events");
118 XDB
::execute("OPTIMIZE TABLE log_sessions");
120 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: