From 6586a74f007007be081110649a1d21893300ae9f Mon Sep 17 00:00:00 2001 From: Florent Bruneau Date: Sun, 14 Feb 2010 12:51:03 +0100 Subject: [PATCH] Move logger base in main data base (tables log_*). Signed-off-by: Florent Bruneau --- bin/cron/compliance.php | 18 +++++++++--------- classes/platallogger.php | 8 ++++---- classes/xorgsession.php | 4 ++-- modules/admin.php | 32 ++++++++++++++++---------------- modules/search/classes.inc.php | 2 +- upgrade/account/06_logs.sql | 25 +++++++++++++++++++++++++ 6 files changed, 57 insertions(+), 32 deletions(-) create mode 100644 upgrade/account/06_logs.sql diff --git a/bin/cron/compliance.php b/bin/cron/compliance.php index 4879770..97b8d47 100755 --- a/bin/cron/compliance.php +++ b/bin/cron/compliance.php @@ -32,11 +32,11 @@ require('./connect.db.inc.php'); */ function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) { XDB::execute( - "DELETE #logger#.s - FROM #logger#.sessions AS s + "DELETE s + FROM log_sessions AS s JOIN (SELECT u.user_id, (SELECT us.start - FROM #logger#.sessions AS us + FROM log_sessions AS us WHERE us.uid = u.user_id ORDER BY us.start DESC LIMIT {?}, 1) AS no_discard_limit @@ -57,7 +57,7 @@ function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) { function checkOrphanedSessions() { $res = XDB::query( "SELECT COUNT(*) - FROM #logger#.sessions AS s + FROM log_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())) { @@ -70,9 +70,9 @@ function checkOrphanedSessions() { */ function purgeOrphanedEvents() { XDB::execute( - "DELETE #logger#.e - FROM #logger#.events AS e - LEFT JOIN #logger#.sessions AS s ON (s.id = e.session) + "DELETE e + FROM log_events AS e + LEFT JOIN log_sessions AS s ON (s.id = e.session) WHERE s.id IS NULL"); $affectedRows = XDB::affectedRows(); echo "Orphaned events: removed $affectedRows events.\n"; @@ -93,8 +93,8 @@ 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"); +XDB::execute("OPTIMIZE TABLE log_events"); +XDB::execute("OPTIMIZE TABLE log_sessions"); // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: ?> diff --git a/classes/platallogger.php b/classes/platallogger.php index 000c09b..a970e79 100644 --- a/classes/platallogger.php +++ b/classes/platallogger.php @@ -46,7 +46,7 @@ class PlatalLogger extends PlLogger // retrieve available actions $this->actions = XDB::fetchAllAssoc('text', 'SELECT id, text - FROM #logger#.actions'); + FROM log_actions'); } /** Creates a new session entry in database and return its ID. @@ -71,7 +71,7 @@ class PlatalLogger extends PlLogger $proxy = 'proxy'; } - XDB::execute("INSERT INTO #logger#.sessions + XDB::execute("INSERT INTO log_sessions SET uid={?}, host={?}, ip={?}, forward_ip={?}, forward_host={?}, browser={?}, suid={?}, flags={?}", $uid, $host, ip_to_uint($ip), ip_to_uint($forward_ip), $forward_host, $browser, $suid, $proxy); if ($forward_ip) { @@ -88,7 +88,7 @@ class PlatalLogger extends PlLogger } public function saveLastSession() { - XDB::execute('REPLACE INTO #logger#.last_sessions (uid, id) + XDB::execute('REPLACE INTO log_last_sessions (uid, id) VALUES ({?}, {?})', $this->uid, $this->session); } @@ -106,7 +106,7 @@ class PlatalLogger extends PlLogger public function log($action, $data = null) { if (isset($this->actions[$action])) { - XDB::execute("INSERT INTO #logger#.events + XDB::execute("INSERT INTO log_events SET session={?}, action={?}, data={?}", $this->session, $this->actions[$action], $data); } else { diff --git a/classes/xorgsession.php b/classes/xorgsession.php index ab54814..f76046c 100644 --- a/classes/xorgsession.php +++ b/classes/xorgsession.php @@ -206,8 +206,8 @@ class XorgSession extends PlSession INNER JOIN watch AS w ON(w.uid = a.uid) LEFT JOIN forum_profiles AS fp ON(fp.uid = a.uid) LEFT JOIN gapps_accounts AS g ON(a.uid = g.l_userid AND g.g_status = 'active') - LEFT JOIN #logger#.last_sessions AS ls ON (ls.uid = a.uid) - LEFT JOIN #logger#.sessions AS s ON(s.id = ls.id) + LEFT JOIN log_last_sessions AS ls ON (ls.uid = a.uid) + LEFT JOIN log_sessions AS s ON(s.id = ls.id) WHERE a.uid = {?} AND a.state = 'active'", $user->id()); if ($res->numRows() != 1) { return false; diff --git a/modules/admin.php b/modules/admin.php index ce2eb0f..7281964 100644 --- a/modules/admin.php +++ b/modules/admin.php @@ -143,7 +143,7 @@ class AdminModule extends PLModule MONTH(MAX(start)), MONTH(MIN(start)), DAYOFMONTH(MAX(start)), DAYOFMONTH(MIN(start)) - FROM #logger#.sessions"); + FROM log_sessions"); list($ymax, $ymin, $mmax, $mmin, $dmax, $dmin) = $res->fetchOneRow(); if (($year < $ymin) || ($year == $ymin && $month < $mmin)) { @@ -181,7 +181,7 @@ class AdminModule extends PLModule if ($year) { $res = XDB::query("SELECT YEAR (MAX(start)), YEAR (MIN(start)), MONTH(MAX(start)), MONTH(MIN(start)) - FROM #logger#.sessions"); + FROM log_sessions"); list($ymax, $ymin, $mmax, $mmin) = $res->fetchOneRow(); if (($year < $ymin) || ($year > $ymax)) { @@ -211,7 +211,7 @@ class AdminModule extends PLModule $years[0] = "----"; // retrieve available years - $res = XDB::query("select YEAR(MAX(start)), YEAR(MIN(start)) FROM #logger#.sessions"); + $res = XDB::query("select YEAR(MAX(start)), YEAR(MIN(start)) FROM log_sessions"); list($max, $min) = $res->fetchOneRow(); for($i = intval($min); $i<=$max; $i++) { @@ -271,7 +271,7 @@ class AdminModule extends PLModule // we are viewing a session $res = XDB::query("SELECT ls.*, a.alias AS username, sa.alias AS suer - FROM #logger#.sessions AS ls + FROM log_sessions AS ls LEFT JOIN #x4dat#.aliases AS a ON (a.id = ls.uid AND a.type='a_vie') LEFT JOIN #x4dat#.aliases AS sa ON (sa.id = ls.suid AND sa.type='a_vie') WHERE ls.id = {?}", $arg); @@ -279,8 +279,8 @@ class AdminModule extends PLModule $page->assign('session', $a = $res->fetchOneAssoc()); $res = XDB::iterator('SELECT a.text, e.data, e.stamp - FROM #logger#.events AS e - LEFT JOIN #logger#.actions AS a ON e.action=a.id + FROM log_events AS e + LEFT JOIN log_actions AS a ON e.action=a.id WHERE e.session={?}', $arg); while ($myarr = $res->next()) { $page->append('events', $myarr); @@ -330,7 +330,7 @@ class AdminModule extends PLModule $where = $this->_makeWhere($year, $month, $day, $loguid); $select = "SELECT s.id, s.start, s.uid, a.alias as username - FROM #logger#.sessions AS s + FROM log_sessions AS s LEFT JOIN #x4dat#.aliases AS a ON (a.id = s.uid AND a.type='a_vie') $where ORDER BY start DESC"; @@ -345,9 +345,9 @@ class AdminModule extends PLModule // attach events $sql = "SELECT s.id, a.text - FROM #logger#.sessions AS s - LEFT JOIN #logger#.events AS e ON(e.session=s.id) - INNER JOIN #logger#.actions AS a ON(a.id=e.action) + FROM log_sessions AS s + LEFT JOIN log_events AS e ON(e.session=s.id) + INNER JOIN log_actions AS a ON(a.id=e.action) $where"; $res = XDB::iterator($sql); @@ -605,7 +605,7 @@ class AdminModule extends PLModule // Displays last login and last host information. $res = XDB::query("SELECT start, host - FROM #logger#.sessions + FROM log_sessions WHERE uid = {?} AND suid = 0 ORDER BY start DESC LIMIT 1", $user->id()); @@ -831,7 +831,7 @@ class AdminModule extends PLModule INNER JOIN #x4dat#.account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms)) INNER JOIN #x4dat#.profiles AS p ON (p.pid = ap.pid) INNER JOIN #x4dat#.profile_display AS pd ON (pd.pid = p.pid) - LEFT JOIN #logger#.sessions AS s ON (s.uid = a.uid AND suid = 0) + LEFT JOIN log_sessions AS s ON (s.uid = a.uid AND suid = 0) WHERE a.state = 'active' AND p.deathdate IS NOT NULL GROUP BY a.uid ORDER BY pd.promo, pd.sort_name"); @@ -951,7 +951,7 @@ class AdminModule extends PLModule { $page->setTitle('Administration - Actions'); $page->assign('title', 'Gestion des actions de logger'); - $table_editor = new PLTableEditor('admin/logger/actions','#logger#.actions','id'); + $table_editor = new PLTableEditor('admin/logger/actions','log_actions','id'); $table_editor->describe('text','intitulé',true); $table_editor->describe('description','description',true); $table_editor->apply($page, $action, $id); @@ -1094,8 +1094,8 @@ class AdminModule extends PLModule IF(w.ip = s.ip, s.host, s.forward_host)), w.mask, w.detection, w.state, a.hruid FROM #x4dat#.ip_watch AS w - LEFT JOIN #logger#.sessions AS s ON (s.ip = w.ip) - LEFT JOIN #logger#.sessions AS s2 ON (s2.forward_ip = w.ip) + LEFT JOIN log_sessions AS s ON (s.ip = w.ip) + LEFT JOIN log_sessions AS s2 ON (s2.forward_ip = w.ip) LEFT JOIN #x4dat#.accounts AS a ON (a.uid = s.uid) GROUP BY w.ip, a.hruid ORDER BY w.state, w.ip, a.hruid"; @@ -1129,7 +1129,7 @@ class AdminModule extends PLModule a1.hruid AS edit, a2.hruid AS hruid, s.host FROM #x4dat#.ip_watch AS w LEFT JOIN #x4dat#.accounts AS a1 ON (a1.uid = w.uid) - LEFT JOIN #logger#.sessions AS s ON (w.ip = s.ip) + LEFT JOIN log_sessions AS s ON (w.ip = s.ip) LEFT JOIN #x4dat#.accounts AS a2 ON (a2.uid = s.uid) WHERE w.ip = {?} GROUP BY a2.hruid diff --git a/modules/search/classes.inc.php b/modules/search/classes.inc.php index 1d6a047..569f040 100644 --- a/modules/search/classes.inc.php +++ b/modules/search/classes.inc.php @@ -379,7 +379,7 @@ class QuickSearch extends SField $join .= "LEFT JOIN emails AS ems ON (ems.uid = u.user_id)"; } if (!empty($this->ip)) { - $join .= "INNER JOIN #logger#.sessions AS ls ON (ls.uid = u.user_id)\n"; + $join .= "INNER JOIN log_sessions AS ls ON (ls.uid = u.user_id)\n"; } if (!empty($this->phone)) { if (!S::logged()) { diff --git a/upgrade/account/06_logs.sql b/upgrade/account/06_logs.sql new file mode 100644 index 0000000..4722f7f --- /dev/null +++ b/upgrade/account/06_logs.sql @@ -0,0 +1,25 @@ +CREATE TABLE log_actions + LIKE logger.actions; + INSERT INTO log_actions + SELECT * + FROM logger.actions; + +CREATE TABLE log_events + LIKE logger.events; + INSERT INTO log_events + SELECT * + FROM logger.events; + +CREATE TABLE log_last_sessions + LIKE logger.last_sessions; + INSERT INTO log_last_sessions + SELECT * + FROM logger.last_sessions; + +CREATE TABLE log_sessions + LIKE logger.sessions; + INSERT INTO log_sessions + SELECT * + FROM logger.sessions; + +# vim:set ft=mysql: -- 2.1.4