#!/usr/bin/php
<pre>

/*  MySQL Quota Tool(mysqlquota.php)
    Write by Gonçalo Silva 
    gngs(at)paradigma.co.pt  

   ---------------------------------------------------------------------
    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
    ---------------------------------------------------------------------- 
    
    This script was based on Sebastian Marsching's idea (Thank´s)
    It provides you the ability to limit the on-disk size of any MySql 
    Database. It works by checking the size of each database and revoking 
    the INSERT and CREATE priveleges for the databases which exceed the 
    given size limit. Also, an email will be sent to the owner, informing 
    him of the condition.
    When the size of the database falls below the given limit, the INSERT and 
    CREATE priveleges are granted again.
   
    You have to create a quota Database (quotadb) and the user must have 
    full access to all databases. Normally we are talking about root@localhost
    Create this table:

    CREATE TABLE Quota (
    Db char(64) NOT NULL default '',
    Max bigint(20) NOT NULL default '0',
    Exceeded enum('Y','N') NOT NULL default 'N',
    Mail char(100) default NULL,
    PRIMARY KEY  (Db),
    UNIQUE KEY Db (Db)
  );
   
  This script uses de php CLI.
  
  chmod 700 mysqlquota.php
  ./mysqlquota.php
  
*/
//Settings
 
$mysql_host  = 'localhost'; 
$mysql_user  = 'root';
$mysql_pass  = '';	  //put your password here
$mysql_db    = 'quotadb'; //Database's name which has Quota table
$mysql_table = 'Quota';
$mail_msg    = 'Your Database exceeded the limit quota. Please contact The administration';
//--------

function connect_Bd (){

        global $mysql_host, $mysql_user, $mysql_pass, $mysql_db;
	mysql_connect($mysql_host, $mysql_user, $mysql_pass);
        mysql_select_db($mysql_db);

	}


function getInput(){
  
       	$input = fgets(STDIN, 255); 
       	$input = rtrim($input); 
      	return $input; 
        } 


function inserirDb() {
   
	global $mysql_table;
   	echo "\nWhat's the DataBase name?: ";
   	$db_name= getInput();
   	echo "What's the Limit in Mb you want?: ";
   	$db_limitm=(Int)getInput();
        $db_limit=$db_limitm*1048576;
   	echo "What's the email to notify the quota?: ";
   	$db_email= getInput();
   	connect_Bd();
   	mysql_query ("insert into $mysql_table values 
                ('$db_name','$db_limit','N','$db_email')");

   	mysql_close();
   	echo "\nDataBase $db_name added with quota= $db_limitm (Mb)\n\n";
   	exit(0);  
    
	}	    	

function updateDb() {
	global $mysql_table;
        echo "\nWhat's the DataBase name to update quota?: ";
	$db_name= getInput();
        echo "\nWhat's the new quota limit in Mb?: ";
        $db_limitm=(Int)getInput();
        $db_limit=$db_limitm*1048576;
        connect_Bd();
        mysql_query ("update $mysql_table set Max='$db_limit' where Db='$db_name'");
        mysql_close();
   	echo "\nDataBase $db_name with new quota= $db_limitm (Mb)\n";
        checkDb(1,$db_name);        
	
	}

function deleteDb() {
       
	global $mysql_table;
        echo "\nWhat's the DataBase name to delete quota?: ";
        $db_name= getInput();
        connect_Bd();
        mysql_query("delete from $mysql_table where Db='$db_name'");
        mysql_close();
   	echo "\nQuota for DataBase $db_name deleted\n\n";
        exit(1);  
	
	}

function checkDbone(){

        echo "\nWhat's the DataBase name to see the quota?: ";
        $db_name= getInput();
        checkDb(1,$db_name);
         
	}
	    	
function checkDb($control,$db_name ) {
        
        global $mysql_table, $mail_msg;	
        connect_Bd();
        if ($control)
		$result = mysql_query("select * from $mysql_table where Db='$db_name'");
        else
		$result = mysql_query("select * from $mysql_table");
        while ($row = mysql_fetch_array($result)){
 		$quota_db = $row['Db'];
 		$quota_limit = $row['Max'];
 		$quota_exceeded = ($row['Exceeded']=='Y') ? 1 : 0;
                $user_mail = $row['Mail'];
  		echo "\nChecking quota for $quota_db DataBase...\n";
                
 		$qresult = mysql_query("show table status from $quota_db");
                $quota_size = 0;
                while ($qrow = mysql_fetch_array($qresult)) {
			  $quota_size += $qrow['Data_length'] + $qrow['Index_length'];
 		}
                $quota_sizem=($quota_size/1048576);
                $quota_sizem= round($quota_sizem, 2);
                $quota_limitm=($quota_limit/1048576);
                echo "Size is $quota_sizem (Mb), limit is $quota_limitm (Mb)\n";
                if ($quota_size <= $quota_limit && !$quota_exceeded)
			echo "DataBase UnLocked\n";
                if ($quota_size >= $quota_limit && $quota_exceeded)
			echo "DataBase Locked\n";

 		if (($quota_size > $quota_limit) && !$quota_exceeded){
                        echo "LOCKING DATABASE...\n";
  			mysql_query("update $mysql_table set exceeded='Y' where db='$quota_db'");
  			// taken off CREATE and INSERT privelege for database
  			mysql_select_db('mysql');
 			mysql_query("update db set Insert_priv='N', Create_priv='N'  
                        		where Db='$quota_db'");
  			mysql_select_db($mysql_db);
                        //sending mail to notify the user
                        mail($user_mail,"Quota MySQL Exceeded",$mail_msg);
 		}
		if (($quota_size <= $quota_limit) && $quota_exceeded){
   			echo "UNLOCKING database...\n";
  			mysql_query("update $mysql_table set exceeded='N' where 
					db='$quota_db'");
  			// Grant CREATE and INSERT privelege for database
  			mysql_select_db('mysql');
  			mysql_query("update db set Insert_priv='Y', Create_priv='Y' WHERE 
				Db='$quota_db'");
  			mysql_select_db($mysql_db);
 		}

     }

        exit(1);
	}//fim da funcao

if ($argc!=2){
	echo "\nusage: $argv[0] <num>\n\n";
        echo " '1' - Insert quota limit to a new DataBase\n"; 
        echo " '2' - Update new quota limit to a DataBase\n"; 
        echo " '3' - Delete quota limit to a DataBase\n";
        echo " '4' - Check one DataBase quota\n";
        echo " '5' - Check All DataBases quotas\n\n";
        exit(-1); 
   }
 
switch ($argv[1]) {
   case 1:
       inserirDb();
       break;
   case 2:
       updateDb();
       break;
   case 3:
       deleteDb();
       break;
   case 4:
       checkDbone();
       break;
   case 5:
       checkDb(0,null);
       break;
   default:
       echo "What kind of number is that?\n";
  }

 exit(0);