Commit | Line | Data |
---|---|---|
3eecf0af VZ |
1 | #!/usr/bin/php5 -q |
2 | <?php | |
3 | /*************************************************************************** | |
9f5bd98e | 4 | * Copyright (C) 2003-2010 Polytechnique.org * |
3eecf0af VZ |
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 | ||
7be5c7d5 | 23 | require './connect.db.inc.php'; |
3eecf0af VZ |
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) { | |
7be5c7d5 SJ |
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 | ||
3eecf0af | 51 | XDB::execute( |
6586a74f FB |
52 | "DELETE s |
53 | FROM log_sessions AS s | |
7be5c7d5 | 54 | JOIN (SELECT a.uid, |
3eecf0af | 55 | (SELECT us.start |
6586a74f | 56 | FROM log_sessions AS us |
7587a63f | 57 | WHERE us.uid = a.uid AND (us.suid IS NULL OR us.suid = 0) |
3eecf0af VZ |
58 | ORDER BY us.start DESC |
59 | LIMIT {?}, 1) AS no_discard_limit | |
7be5c7d5 SJ |
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) | |
3eecf0af VZ |
63 | WHERE s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH) |
64 | AND s.start < ut.no_discard_limit", | |
7be5c7d5 | 65 | $minimalBacklog - 1, $state, $isAdmin, $retentionPeriod); |
3eecf0af VZ |
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(*) | |
7be5c7d5 SJ |
77 | FROM log_sessions AS s |
78 | LEFT JOIN #x5dat#.accounts AS a ON (a.uid = s.uid) | |
79 | WHERE a.uid IS NULL"); | |
3eecf0af VZ |
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( | |
6586a74f FB |
90 | "DELETE e |
91 | FROM log_events AS e | |
92 | LEFT JOIN log_sessions AS s ON (s.id = e.session) | |
3eecf0af VZ |
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. | |
6586a74f FB |
113 | XDB::execute("OPTIMIZE TABLE log_events"); |
114 | XDB::execute("OPTIMIZE TABLE log_sessions"); | |
3eecf0af VZ |
115 | |
116 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: | |
117 | ?> |