Commit | Line | Data |
---|---|---|
3eecf0af VZ |
1 | #!/usr/bin/php5 -q |
2 | <?php | |
3 | /*************************************************************************** | |
ba6ae046 | 4 | * Copyright (C) 2003-2013 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) { | |
59ebdf2f | 34 | $begin = time(); |
7be5c7d5 SJ |
35 | switch ($userPerms) { |
36 | case 'user': | |
37 | $state = 'active'; | |
38 | $isAdmin = 0; | |
39 | break; | |
40 | case 'admin': | |
41 | $state = 'active'; | |
42 | $isAdmin = 1; | |
43 | break; | |
44 | case 'disabled': | |
45 | $state = 'disabled'; | |
46 | $isAdmin = 0; | |
47 | break; | |
48 | default: | |
49 | return; | |
50 | } | |
51 | ||
59ebdf2f RB |
52 | list($low, $high) = XDB::fetchOneRow( |
53 | "SELECT MIN(uid), MAX(uid) | |
54 | FROM #x5dat#.accounts | |
55 | WHERE state = {?} AND is_admin = {?}", | |
56 | $state, $isAdmin); | |
3eecf0af | 57 | |
59ebdf2f RB |
58 | $batchSize = 500; |
59 | $nbBatches = 0; | |
60 | $affectedRows = 0; | |
61 | ||
62 | // Run in batches. | |
63 | for ($lowUID = $low; $lowUID <= $high; $lowUID += $batchSize) { | |
64 | ||
65 | // Slight optimization for last loop: adjust to exactly what's necessary. | |
66 | $highUID = min($high + 1, $lowUID + $batchSize); | |
67 | ||
68 | XDB::execute( | |
69 | "DELETE s | |
70 | FROM log_sessions AS s | |
71 | JOIN (SELECT a.uid, | |
72 | (SELECT us.start | |
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); | |
84 | ||
85 | $nbBatches += 1; | |
86 | $affectedRows += XDB::affectedRows(); | |
87 | } | |
88 | ||
89 | $duration = time() - $begin; | |
90 | echo "Users with permission '$userPerms': removed $affectedRows sessions in $duration seconds ($nbBatches batches).\n"; | |
3eecf0af VZ |
91 | } |
92 | ||
93 | /** | |
94 | * Checks for sessions without a valid associated user id. | |
95 | */ | |
96 | function checkOrphanedSessions() { | |
cece5392 | 97 | $begin = time(); |
3eecf0af VZ |
98 | $res = XDB::query( |
99 | "SELECT COUNT(*) | |
7be5c7d5 SJ |
100 | FROM log_sessions AS s |
101 | LEFT JOIN #x5dat#.accounts AS a ON (a.uid = s.uid) | |
102 | WHERE a.uid IS NULL"); | |
3eecf0af | 103 | if (($count = $res->fetchOneCell())) { |
cece5392 RB |
104 | $duration = time() - $begin; |
105 | echo "Orphaned sessions: found $count orphaned sessions in $duration seconds. Please fix that.\n"; | |
3eecf0af VZ |
106 | } |
107 | } | |
108 | ||
109 | /** | |
110 | * Purges session events without a valid session. | |
111 | */ | |
112 | function purgeOrphanedEvents() { | |
cece5392 | 113 | $begin = time(); |
3eecf0af | 114 | XDB::execute( |
6586a74f FB |
115 | "DELETE e |
116 | FROM log_events AS e | |
117 | LEFT JOIN log_sessions AS s ON (s.id = e.session) | |
3eecf0af VZ |
118 | WHERE s.id IS NULL"); |
119 | $affectedRows = XDB::affectedRows(); | |
cece5392 RB |
120 | $duration = time() - $begin; |
121 | echo "Orphaned events: removed $affectedRows events in $duration seconds.\n"; | |
3eecf0af VZ |
122 | } |
123 | ||
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); | |
132 | ||
133 | // Purge orphaned entries; events are purged automatically, sessions require explicit | |
134 | // action from the administrator. | |
135 | checkOrphanedSessions(); | |
136 | purgeOrphanedEvents(); | |
137 | ||
138 | // Many rows have been removed from the two logger tables. Let's optimize them. | |
6586a74f FB |
139 | XDB::execute("OPTIMIZE TABLE log_events"); |
140 | XDB::execute("OPTIMIZE TABLE log_sessions"); | |
3eecf0af VZ |
141 | |
142 | // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: | |
143 | ?> |