From 21c641cb28510fe775b2f11c17744d74e972b018 Mon Sep 17 00:00:00 2001
From: Florian Schaal <florian@schaal-24.de>
Date: Mon, 21 Oct 2013 10:34:41 -0400
Subject: [PATCH] add database-size monitoring. the results stored in the database may be used to add the db-size to the web-quota, too.

---
 server/lib/classes/db_mysql.inc.php                         |   27 ++++++++
 server/lib/classes/cron.d/100-monitor_database_size.inc.php |  120 ++++++++++++++++++++++++++++++++++++++++
 interface/lib/classes/functions.inc.php                     |    8 ++
 3 files changed, 153 insertions(+), 2 deletions(-)

diff --git a/interface/lib/classes/functions.inc.php b/interface/lib/classes/functions.inc.php
index 50b3e6c..5b98c20 100644
--- a/interface/lib/classes/functions.inc.php
+++ b/interface/lib/classes/functions.inc.php
@@ -276,7 +276,13 @@
             return intval($string);
         }
     }
-
+    
+    /**
+    * Function to change bytes to kB, MB, GB or TB
+    * @param int $size - size in bytes
+    * @param int precicion - after-comma-numbers (default: 2)
+    * @return string - formated bytes
+    */
     public function formatBytes($size, $precision = 2) {
         $base=log($size)/log(1024);
         $suffixes=array('','k','M','G','T');
diff --git a/server/lib/classes/cron.d/100-monitor_database_size.inc.php b/server/lib/classes/cron.d/100-monitor_database_size.inc.php
new file mode 100644
index 0000000..4a248ad
--- /dev/null
+++ b/server/lib/classes/cron.d/100-monitor_database_size.inc.php
@@ -0,0 +1,120 @@
+<?php
+
+/**
+Copyright (c) 2013, Marius Cramer, pixcept KG
+Copyright (c) 2013, Florian Schaal, info@schaal-24.de
+All rights reserved.
+
+Redistribution and use in source and binary forms, with or without modification,
+are permitted provided that the following conditions are met:
+
+    * Redistributions of source code must retain the above copyright notice,
+      this list of conditions and the following disclaimer.
+    * Redistributions in binary form must reproduce the above copyright notice,
+      this list of conditions and the following disclaimer in the documentation
+      and/or other materials provided with the distribution.
+    * Neither the name of ISPConfig nor the names of its contributors
+      may be used to endorse or promote products derived from this software without
+      specific prior written permission.
+
+THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
+ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
+IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
+INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
+BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
+DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
+OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
+NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
+EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+*/
+
+class cronjob_monitor_database_size extends cronjob {
+    
+    // job schedule
+    protected $_schedule = '*/5 * * * *';
+    protected $_run_at_new = true;
+	
+    private $_tools = null;
+    
+    /**
+    * this function is optional if it contains no custom code 
+    */
+    public function onPrepare() {
+        global $app;
+        
+        parent::onPrepare();
+    }
+    
+    /** 
+    * this function is optional if it contains no custom code 
+    */
+    public function onBeforeRun() {
+        global $app;
+        
+        return parent::onBeforeRun();
+    }
+    
+    public function onRunJob() {
+        global $app, $conf;
+        
+        /* used for all monitor cronjobs */
+        $app->load('monitor_tools');
+        $this->_tools = new monitor_tools();
+        /* end global section for monitor cronjobs */
+        
+		/* the id of the server as int */
+		$server_id = intval($conf['server_id']);
+
+		/** The type of the data */
+		$type = 'database_size';
+
+		/** The state of the database-usage */
+		$state = 'ok';
+
+		/** Fetch the data of ALL databases into an array */
+		$records = $app->db->queryAllRecords("SELECT database_name, sys_groupid FROM web_database WHERE server_id = $server_id");
+		if(is_array($records) && !empty($records)) {
+			$data = array();
+			for ($i = 0; $i < sizeof($records); $i++) {
+				$data[$i]['name'] = $records[$i]['database_name'];
+				$data[$i]['size'] = $app->db->getDatabaseSize($data[$i]['name']);
+				$data[$i]['client_id'] = $records[$i]['sys_groupid'];
+			}
+		}
+
+		$res = array();
+		$res['server_id'] = $server_id;
+		$res['type'] = $type;
+		$res['data'] = $data;
+		$res['state'] = $state;
+
+		/*
+		 * Insert the data into the database
+		 */
+		$sql = 'REPLACE INTO monitor_data (server_id, type, created, data, state) ' .
+				'VALUES (' .
+				$res['server_id'] . ', ' .
+				"'" . $app->dbmaster->quote($res['type']) . "', " .
+				'UNIX_TIMESTAMP(), ' .
+				"'" . $app->dbmaster->quote(serialize($res['data'])) . "', " .
+				"'" . $res['state'] . "'" .
+				')';
+		$app->dbmaster->query($sql);
+
+		/* The new data is written, now we can delete the old one */
+		$this->_tools->delOldRecords($res['type'], $res['server_id']);
+        
+        parent::onRunJob();
+    }
+    
+    /* this function is optional if it contains no custom code */
+    public function onAfterRun() {
+        global $app;
+        
+        parent::onAfterRun();
+    }
+
+}
+
+?>
diff --git a/server/lib/classes/db_mysql.inc.php b/server/lib/classes/db_mysql.inc.php
index 23ba8a6..acd86aa 100644
--- a/server/lib/classes/db_mysql.inc.php
+++ b/server/lib/classes/db_mysql.inc.php
@@ -265,6 +265,31 @@
 
     }
 
+        /**
+        * Function to get the database-size
+        * @param string $database_name
+        * @return int - database-size in bytes
+        */
+        public function getDatabaseSize($database_name) {
+                global $app;
+                include('lib/mysql_clientdb.conf');
+                /* Connect to the database */
+                $link = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password);
+                if (!$link) {
+                        $app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_DEBUG);
+                        return;
+                }
+                /* Get database-size from information_schema */
+                $result=mysql_query("SELECT SUM(data_length+index_length) FROM information_schema.TABLES WHERE table_schema='".$database_name."';",$link);
+                $this->close;
+                if (!$result) {
+                        $app->log('Unable to get the database-size'.mysql_error($link),LOGLEVEL_DEBUG);
+                        return;
+                }
+                $database_size = mysql_fetch_row($result);
+                return $database_size[0];
+        }
+
     //** Function to fill the datalog with a full differential record.
     public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) {
       global $app,$conf;
@@ -645,4 +670,4 @@
 
 	      }
 
-	      ?>
\ No newline at end of file
+	      ?>

--
Gitblit v1.9.1