Commit | Line | Data |
---|---|---|
3eecf0af VZ |
1 | #!/usr/bin/php5 -q |
2 | <?php | |
3 | /*************************************************************************** | |
4 | * Copyright (C) 2003-2009 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 | XDB::execute( | |
00112b2e VZ |
35 | "DELETE #logger#.s |
36 | FROM #logger#.sessions AS s | |
3eecf0af VZ |
37 | JOIN (SELECT u.user_id, |
38 | (SELECT us.start | |
00112b2e | 39 | FROM #logger#.sessions AS us |
3eecf0af VZ |
40 | WHERE us.uid = u.user_id |
41 | ORDER BY us.start DESC | |
42 | LIMIT {?}, 1) AS no_discard_limit | |
00112b2e | 43 | FROM #x4dat#.auth_user_md5 AS u |
3eecf0af VZ |
44 | WHERE u.perms = {?} |
45 | ORDER BY u.user_id ASC) AS ut ON (ut.user_id = s.uid) | |
46 | WHERE s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH) | |
47 | AND s.start < ut.no_discard_limit", | |
48 | $minimalBacklog - 1, $userPerms, $retentionPeriod); | |
49 | ||
50 | $affectedRows = XDB::affectedRows(); | |
51 | echo "Users with permission '$userPerms': removed $affectedRows sessions.\n"; | |
52 | } | |
53 | ||
54 | /** | |
55 | * Checks for sessions without a valid associated user id. | |
56 | */ | |
57 | function checkOrphanedSessions() { | |
58 | $res = XDB::query( | |
59 | "SELECT COUNT(*) | |
00112b2e VZ |
60 | FROM #logger#.sessions AS s |
61 | LEFT JOIN #x4dat#.auth_user_md5 AS u ON (u.user_id = s.uid) | |
3eecf0af VZ |
62 | WHERE u.user_id IS NULL"); |
63 | if (($count = $res->fetchOneCell())) { | |
64 | echo "Orphaned sessions: found $count orphaned sessions. Please fix that.\n"; | |
65 | } | |
66 | } | |
67 | ||
68 | /** | |
69 | * Purges session events without a valid session. | |
70 | */ | |
71 | function purgeOrphanedEvents() { | |
72 | XDB::execute( | |
00112b2e VZ |
73 | "DELETE #logger#.e |
74 | FROM #logger#.events AS e | |
75 | LEFT JOIN #logger#.sessions AS s ON (s.id = e.session) | |
3eecf0af VZ |
76 | WHERE s.id IS NULL"); |
77 | $affectedRows = XDB::affectedRows(); | |
78 | echo "Orphaned events: removed $affectedRows events.\n"; | |
79 | } | |
80 | ||
81 | // Remove expired sessions. | |
82 | // For normal user, we only keep 12 months of data (and at least the last two sessions). | |
83 | // For administrator, we also keep data for 12 months, but with a backlog of at least 20 sessions. | |
84 | // For disabled users, we keep data for 5 years, and with a backlog of at least 2 sessions. | |
85 | // For other users, no data are discarded. | |
86 | discardExpiredSessions('user', 12, 2); | |
87 | discardExpiredSessions('admin', 12, 20); | |
88 | discardExpiredSessions('disabled', 60, 2); | |
89 | ||
90 | // Purge orphaned entries; events are purged automatically, sessions require explicit | |
91 | // action from the administrator. | |
92 | checkOrphanedSessions(); | |
93 | purgeOrphanedEvents(); | |
94 | ||
95 | // Many rows have been removed from the two logger tables. Let's optimize them. | |
00112b2e VZ |
96 | XDB::execute("OPTIMIZE TABLE #logger#.events"); |
97 | XDB::execute("OPTIMIZE TABLE #logger#.sessions"); | |
3eecf0af VZ |
98 | |
99 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: | |
100 | ?> |