From 3eecf0af78053b349c716279ae0ba00a2af5fdb0 Mon Sep 17 00:00:00 2001
From: Vincent Zanotti <vincent.zanotti@m4x.org>
Date: Sun, 27 Sep 2009 19:13:08 +0200
Subject: [PATCH] Adds a monthly cron to remove unwanted logger table entries.
 In short: we won't keep more than 12 months of application logs for regular
 users.

Signed-off-by: Vincent Zanotti <vincent.zanotti@m4x.org>
---
 bin/cron/compliance.php | 100 ++++++++++++++++++++++++++++++++++++++++++++++++
 configs/platal.cron.in  |   3 ++
 2 files changed, 103 insertions(+)
 create mode 100755 bin/cron/compliance.php

diff --git a/bin/cron/compliance.php b/bin/cron/compliance.php
new file mode 100755
index 0000000..0a07a15
--- /dev/null
+++ b/bin/cron/compliance.php
@@ -0,0 +1,100 @@
+#!/usr/bin/php5 -q
+<?php
+/***************************************************************************
+*  Copyright (C) 2003-2009 Polytechnique.org                              *
+*  http://opensource.polytechnique.org/                                   *
+*                                                                         *
+*  This program is free software; you can redistribute it and/or modify   *
+*  it under the terms of the GNU General Public License as published by   *
+*  the Free Software Foundation; either version 2 of the License, or      *
+*  (at your option) any later version.                                    *
+*                                                                         *
+*  This program is distributed in the hope that it will be useful,        *
+*  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
+*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
+*  GNU General Public License for more details.                           *
+*                                                                         *
+*  You should have received a copy of the GNU General Public License      *
+*  along with this program; if not, write to the Free Software            *
+*  Foundation, Inc.,                                                      *
+*  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
+***************************************************************************/
+
+require('./connect.db.inc.php');
+
+/**
+ * Discards from the logger database session details for users.
+ *
+ * @param userPerms Permission level to use for that round of discards.
+ * @param retentionPeriod Minimal number of months to keep entries for.
+ * @param minimalBacklog Minimal number of entries to keep for any given user,
+ *      no matter what.
+ */
+function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) {
+    XDB::execute(
+        "DELETE  logger.s
+           FROM  logger.sessions AS s
+           JOIN  (SELECT  u.user_id,
+                          (SELECT  us.start
+                             FROM  logger.sessions AS us
+                            WHERE  us.uid = u.user_id
+                         ORDER BY  us.start DESC
+                            LIMIT  {?}, 1) AS no_discard_limit
+                    FROM  x4dat.auth_user_md5 AS u
+                   WHERE  u.perms = {?}
+                ORDER BY  u.user_id ASC) AS ut ON (ut.user_id = s.uid)
+          WHERE  s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH)
+                 AND s.start < ut.no_discard_limit",
+        $minimalBacklog - 1, $userPerms, $retentionPeriod);
+
+    $affectedRows = XDB::affectedRows();
+    echo "Users with permission '$userPerms': removed $affectedRows sessions.\n";
+}
+
+/**
+ * Checks for sessions without a valid associated user id.
+ */
+function checkOrphanedSessions() {
+    $res = XDB::query(
+        "SELECT  COUNT(*)
+           FROM  logger.sessions AS s
+      LEFT JOIN  x4dat.auth_user_md5 AS u ON (u.user_id = s.uid)
+          WHERE  u.user_id IS NULL");
+    if (($count = $res->fetchOneCell())) {
+        echo "Orphaned sessions: found $count orphaned sessions. Please fix that.\n";
+    }
+}
+
+/**
+ * Purges session events without a valid session.
+ */
+function purgeOrphanedEvents() {
+    XDB::execute(
+        "DELETE  logger.e
+           FROM  logger.events AS e
+      LEFT JOIN  logger.sessions AS s ON (s.id = e.session)
+          WHERE  s.id IS NULL");
+    $affectedRows = XDB::affectedRows();
+    echo "Orphaned events: removed $affectedRows events.\n";
+}
+
+// Remove expired sessions.
+//  For normal user, we only keep 12 months of data (and at least the last two sessions).
+//  For administrator, we also keep data for 12 months, but with a backlog of at least 20 sessions.
+//  For disabled users, we keep data for 5 years, and with a backlog of at least 2 sessions.
+//  For other users, no data are discarded.
+discardExpiredSessions('user', 12, 2);
+discardExpiredSessions('admin', 12, 20);
+discardExpiredSessions('disabled', 60, 2);
+
+// Purge orphaned entries; events are purged automatically, sessions require explicit
+// action from the administrator.
+checkOrphanedSessions();
+purgeOrphanedEvents();
+
+// Many rows have been removed from the two logger tables. Let's optimize them.
+XDB::execute("OPTIMIZE TABLE logger.events");
+XDB::execute("OPTIMIZE TABLE logger.sessions");
+
+// vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
+?>
diff --git a/configs/platal.cron.in b/configs/platal.cron.in
index a931d67..6923650 100644
--- a/configs/platal.cron.in
+++ b/configs/platal.cron.in
@@ -34,4 +34,7 @@ WD=/home/web/prod/platal/bin/cron
 # synchronisation google apps - plat/al
 */5 * * * *	web	cd $WD; ./google_apps.php
 
+# Clean-up of our logging tables
+45 0 1 * *	web	cd $WD; ./compliance.php | mail -e -s "Logging compliance" br@staff.m4x.org
+
 # vim:set noet syntax=crontab ts=8 sw=8 sts=8 enc=utf-8:
-- 
2.1.4