<?php
/*	mitsql.lib.php
	(c) 2005 Joe Presbrey
	written for beta SIPB/MIT SQL service in conjunction with scripts.mit.edu
*/

define('DEBUG', 0);
define('VERSION', '0.1beta');

define('DEFAULT_MAX_DBS', 5);
define('DEFAULT_MAX_SIZE', 26214400);
define('DEFAULT_MAX_DB_SIZE', 0);

define('FORM_METHOD', 'GET');

define('PASS_MIN_LEN', 4);
define('DELIMETER', '+');

define('TEXT_NOREG', 'Your account is not registered with this utility.');
define('TEXT_DBLIMIT', 'You\'ve reached your database limit.');
define('TEXT_FIXREG', 'Fixing database registration for: ');
define('TEXT_NOTREG', 'Database operations are allowed only for those registered to your account.');
define('TEXT_REGPASSTOOSHORT', 'Your SQL password must be at least '.PASS_MIN_LEN.' characters.');

define('HOST', 'sql.mit.edu');
define('ADMINUSER', 'root');
//define('ADMINPASS', 'b6808ded2');
define('ADMINPASS', 'MyBeatsPost@');
//define('ADMINPASS', base64_decode('YjY4MDhkZWQy'));
define('MANAGEDB', 'mitsql');
define('MANAGEDBTABLE', 'db');
define('MANAGEOWNERTABLE', 'owner');

define('SQLSELECT', 'SELECT * FROM %s');
define('SQLSELECTWHERE', 'SELECT * FROM %s WHERE %s');
define('SQLSELECT1WHERE', 'SELECT * FROM %s WHERE %s LIMIT 1');
define('SQLINSERT', 'INSERT INTO %s VALUES (%s)');
define('SQLDELETE', 'DELETE FROM %s WHERE %s');
define('SQLDELETE1', 'DELETE FROM %s WHERE %s LIMIT 1');

$cxn = mysql_connect(HOST, ADMINUSER, ADMINPASS);

function redirectLocal($target) {
	$dir = dirname($_SERVER['PHP_SELF'])=='/'?'':dirname($_SERVER['PHP_SELF']);
	redirectFull($dir.$target);
}
function redirectFull($target) {
	$ssl = $_SERVER['SERVER_PORT'] == 443 ? true : false;
	redirect(($ssl?'https://':'http://').$_SERVER['SERVER_NAME'].$target);
}
function redirect($target) {
	if (ob_get_contents()) ob_end_clean();
	header('Location: '.$target);
	exit;
}

function sprintSize($bytes, $float=2) {
	if (is_null($bytes)) return null;
	$kb = round($bytes / 1024, $float);
	$mb = round($bytes / 1024 / 1024, $float);
	$gb = round($bytes / 1024 / 1024 / 1024, $float);
	
	return ($bytes<1||$kb<1?$bytes.' B':($mb<1?$kb.' KB':($gb<1?$mb.' MB':$gb.' GB')));
}

function sprintTS($timestamp) {	
	return substr($timestamp,0,4).
		'-'.substr($timestamp,4,2).
		'-'.substr($timestamp,6,2).
		' '.substr($timestamp,8,2).
		':'.substr($timestamp,10,2).
		':'.substr($timestamp,12,2);
}

function getDBSize($tdb) {
   mysql_select_db($tdb);

   $sql_result = "SHOW TABLE STATUS FROM `" .mysql_escape_string($tdb)."`";
   $result = @mysql_query($sql_result);

   if($result) {
       $size = 0;
       while ($data = mysql_fetch_array($result)) {
             $size += $data["Data_length"] + $data["Index_length"];
       }
       return $size;
   }
   else {
       return null;
   }
}

function getSSLIdent() {
	if (isset($_SERVER['SSL_CLIENT_S_DN_Email'])) {
		$sslemail = $_SERVER['SSL_CLIENT_S_DN_Email'];
		$ssluser = explode('@',$sslemail);
		$ssluser = $ssluser[0];
		return $ssluser;
	} else {
		return null;
	}
}

class DBManage {
	var $username = "", $dbs;
	var $r_owner;
	var $sizeNow = 0;
	
	function DBManage($username = "") {
		if (!empty($username)) {
			$this->username = $username;
			$this->load();
			if ($this->fix())
				$this->load();
		}
	}
	function load() {
		$rs = mysql_db_query(MANAGEDB,
			sprintf(SQLSELECT1WHERE,
				MANAGEOWNERTABLE,
				'name="'.mysql_escape_string($this->username).'"'));
			//or exit(mysql_error());
		while($r = mysql_fetch_assoc($rs)) {
			$this->r_owner = $r;
		}
		mysql_free_result($rs);

		$dbs = array();

		$rs = mysql_db_query(MANAGEDB,
			sprintf(SQLSELECTWHERE,
				MANAGEDBTABLE,
				'owner="'.mysql_escape_string($this->username).'" ORDER BY name')) or exit(mysql_error());
		while($r = mysql_fetch_assoc($rs)) {
			$t = $r;
			$t['registered'] = 1;
			$t['granted'] = 0;
			$dbs[$r['name']] = $t;
		}
		mysql_free_result($rs);

		$rs = mysql_db_query('mysql',
			sprintf(SQLSELECTWHERE,
				MANAGEDBTABLE,
				'User="'.mysql_escape_string($this->username).'" ORDER BY Db'));
		while($r = mysql_fetch_assoc($rs)) {
			if (!isset($dbs[$r['Db']])) {
				$dbs[$r['Db']] = array();
				$dbs[$r['Db']]['registered'] = 0;
			}
			$dbs[$r['Db']]['granted'] = 1;
			$dbs[$r['Db']]['name'] = $r['Db'];
		}
		mysql_free_result($rs);
		
		foreach($dbs as $d1=>$d2) {
			$size = getDBSize($d1);
			if (is_null($size)) {
				$dbs[$d1]['sizeNow'] = 0;
				$dbs[$d1]['exists'] = 0;
			} else {
				$dbs[$d1]['sizeNow'] = $size;
				$this->sizeNow += $size;
				$dbs[$d1]['exists'] = 1;
			}
		}
		$this->dbs = $dbs;
	}
	function isInit() {
		return !is_null($this->r_owner);
	}
	function grantDB($dbname) {
		mysql_query('GRANT ALL PRIVILEGES ON '
				.'`'.mysql_escape_string($dbname).'` . * '
				.'TO \''.mysql_escape_string($this->username).'\'@\'%\'') or exit(mysql_error());
		$this->flushPriv();
	}
	function flushPriv() {
		mysql_query('FLUSH PRIVILEGES') or exit(mysql_error());
	}
	function revokeDB($dbname) {
		mysql_db_query('mysql',
			'DELETE FROM `db` WHERE '
			.'User = \''.mysql_escape_string($this->username).'\' '
			.'AND Db = \''.mysql_escape_string($dbname).'\'') or exit(mysql_error());
		$this->flushPriv();
	}
	function setPassword($password) {
		mysql_query('SET PASSWORD FOR '.
		'\''.mysql_escape_string($this->username).'\'@\'%\'='.
		'PASSWORD(\''.mysql_escape_string($password).'\')') or exit(mysql_error());
	}
	function init($password, $maxDBs = DEFAULT_MAX_DBS, $maxSize = DEFAULT_MAX_SIZE) {
		mysql_query('GRANT USAGE ON * . * TO '.
			'\''.mysql_escape_string($this->username).'\'@\'%\' '.
			'IDENTIFIED BY \''.mysql_escape_string($password).'\'') or exit(mysql_error());
		$this->flushPriv();
		mysql_db_query(MANAGEDB,
			sprintf(SQLINSERT,
					MANAGEOWNERTABLE,
					sprintf("'%s','%s','%s',NOW(),NOW()",
						mysql_escape_string($this->username),
						$maxDBs,
						$maxSize))) or exit(mysql_error());
	}
	function uninit() {
		mysql_db_query('mysql',
			'DELETE FROM `user` WHERE '
			.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
		mysql_db_query('mysql',
			'DELETE FROM `db` WHERE '
			.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
		mysql_db_query('mysql',
			'DELETE FROM `tables_priv` WHERE '
			.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
		mysql_db_query('mysql',
			'DELETE FROM `columns_priv` WHERE '
			.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
		mysql_query('FLUSH PRIVILEGES') or exit(mysql_error());

		mysql_db_query(MANAGEDB,
			sprintf(SQLDELETE,
					MANAGEDBTABLE,
					'owner=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error());

		foreach($this->dbs as $db)
			$this->dropDB($db['name']);

		mysql_db_query(MANAGEDB,
			sprintf(SQLDELETE1,
					MANAGEOWNERTABLE,
					'name=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error());
	}
	function registerDB($name, $maxSize = DEFAULT_MAX_DB_SIZE) {
		mysql_db_query(MANAGEDB,
			sprintf(SQLINSERT,
					MANAGEDBTABLE,
					sprintf("'%s','%s','%s','',NOW(),NOW()",
						mysql_escape_string($name),
						mysql_escape_string($this->username),
						$maxSize))) or exit(mysql_error());
	}
	function unregisterDB($name) {
		mysql_db_query(MANAGEDB,
			sprintf(SQLDELETE1,
					MANAGEDBTABLE,
					'name="'.mysql_escape_string($name).'"')) or exit(mysql_error());
	}
	function createDB($name) {
		@mysql_create_db($name);
	}
	function dropDB($name) {
		@mysql_drop_db($name);
	}
	function addDB($name) {
		if (!$this->isExists($name))
			$this->createDB($name);
		if (!$this->isRegistered($name))
			$this->registerDB($name);
		if (!$this->isGranted($name))
			$this->grantDB($name);
	}
	function delDB($name) {
		$this->revokeDB($name);
		$this->unregisterDB($name);
		$this->dropDB($name);
	}
	function getNumDBs() {
		return count($this->dbs);
	}
	function getTotalSize() {
		return $this->sizeNow;
	}
	function isRegistered($name) {
		if (isset($this->dbs[$name]))
			return $this->dbs[$name]['registered'];
		else return false;
	}
	function isExists($name) {
		if (isset($this->dbs[$name]))
			return $this->dbs[$name]['exists'];
		else return false;
	}
	function isGranted($name) {
		if (isset($this->dbs[$name]))
			return $this->dbs[$name]['granted'];
		else return false;
	}

	function printOwnerHeader() {
		echo '<th><tr>';
		echo '<td class="header2"><p></p></td>';
		echo '<td class="header1"><p>databases:</p></td>';
		echo '<td class="header2"><p>storage:</p></td>';
		echo '<td class="header1"><p>modified:</p></td>';
//		echo '<td class="header2"><p>created:</p></td>';
		echo '</tr></th>';
	}
	function printOwner() {
		$obj_owner = $this->r_owner;
		echo '<table>';
		$this->printOwnerHeader();
		echo '<td><p>status:</p></td>';
		echo '<td><p>',$this->getNumDBs(),'</p></td>';
		echo '<td><p>',sprintSize($this->getTotalSize()),'</p></td>';
		echo '</tr><tr>';
		echo '<td><p>limits:</p></td>';
		echo '<td><p>',$obj_owner['maxDB'],'</p></td>';
		echo '<td><p>',sprintSize($obj_owner['maxSize']),'</p></td>';
		echo '<td><p>',sprintTS($obj_owner['modified']),'</p></td>';
//		echo '<td><p>',sprintTS($obj_owner['created']),'</p></td>';
		echo '</tr>';
		echo '</table>';
	}
	function printOwnerDBs($showOwner = false) {
		$rows = $this->dbs;
		echo '<th><tr>';
		echo '<td class="header1"><p>name:</p></td>';
		echo '<td class="header2"><p>owner:</p></td>';
		echo '<td class="header1"><p>last size:</p></td>';
		echo '<td class="header2"><p>size:</p></td>';
		echo '<td class="header1"><p>modified:</p></td>';
//		echo '<td class="header2"><p>created:</p></td>';
		echo '</tr></th>';
		foreach($rows as $name=>$r) {
			echo '<tr>';
			echo '<td><p>',$name,'</p></td>';
			echo '<td><p>',$r['owner'],'</p></td>';
	/*		echo '<td><p>',sprintSize($r['sizeMax']),'</p></td>';*/
			echo '<td><p>',sprintSize($r['sizeLast']),'</p></td>';
			echo '<td><p>',sprintSize($r['sizeNow']),'</p></td>';
			echo '<td><p>',sprintTS($r['modified']),'</p></td>';
//			echo '<td><p>',sprintTS($r['created']),'</p></td>';
			
			echo '<form method="',FORM_METHOD,'"><td>';
			echo '<input type=hidden name="op" value="',($r['exists']?'dropdb':'createdb'),'"><input type=hidden name="name" value="',$name,'">';
			echo '<input type=submit value="',($r['exists']?'empty':'create'),'"></td></form>';
			
			echo '<form method="',FORM_METHOD,'"><td>';
			echo '<input type=hidden name="op" value="',($r['granted']?'revokedb':'grantdb'),'"><input type=hidden name="name" value="',$name,'">';
			echo '<input type=submit value="',($r['granted']?'disable':'enable'),'"></td></form>';
			
			echo '<form method="',FORM_METHOD,'"><td><input type=hidden name="op" value="deldb"><input type=hidden name="name" value="',$name,'"><input type=submit value="X"></td></form>';
			echo '</tr>';
			flush();
		}
	}
	function fix() {
		$fixed = false;
		foreach($this->dbs as $db) {
			if (!$db['registered']) {
				echo '<p class="err">',TEXT_FIXREG,$db['name'],'</p>';
				flush();
				$this->registerDB($db['name']);
				$fixed = true;
			}
		}
		return $fixed;
	}
	function getNumMaxDBs() {
		return $this->r_owner['maxDB'];
	}
}

class DBAdmin {
	function mysqlDBList() {
		$rs = mysql_query('SHOW DATABASES');
		$dbs = array();
		while($r = mysql_fetch_row($rs)) {
			$dbs[$r[0]] = array();
			$dbs[$r[0]]['registered'] = 0;
			$dbs[$r[0]]['granted'] = 0;
			$dbs[$r[0]]['exists'] = 1;
			$dbs[$r[0]]['owner'] = '';
			$dbs[$r[0]]['sizeLast'] = 0;
			$dbs[$r[0]]['sizeNow'] = '';
			$dbs[$r[0]]['modified'] = '';
			$dbs[$r[0]]['created'] = '';
		}
		return $dbs;
	}
	function fullDBList() {
		//$dbs = array();
		$dbs = $this->mysqlDBList();

		$rs = mysql_db_query(MANAGEDB,
			sprintf(SQLSELECT,
				MANAGEDBTABLE)) or exit(mysql_error());
		while($r = mysql_fetch_assoc($rs)) {
			$t = $r;
			$t['registered'] = 1;
			$t['granted'] = 0;
			$dbs[$r['name']] = $t;
		}
		mysql_free_result($rs);

		$rs = mysql_db_query('mysql',
			sprintf(SQLSELECT,
				MANAGEDBTABLE));
		while($r = mysql_fetch_assoc($rs)) {
			if (!isset($dbs[$r['Db']])) {
				$dbs[$r['Db']] = array();
				$dbs[$r['Db']]['registered'] = 0;
			}
			$dbs[$r['Db']]['granted'] = 1;
			$dbs[$r['Db']]['name'] = $r['Db'];
		}
		mysql_free_result($rs);
		
		foreach($dbs as $d1=>$d2) {
			$size = getDBSize($d1);
			if (is_null($size)) {
				$dbs[$d1]['sizeNow'] = 0;
				$dbs[$d1]['exists'] = 0;
			} else {
				$dbs[$d1]['sizeNow'] = $size;
				//$this->sizeNow += $size;
				$dbs[$d1]['exists'] = 1;
			}
		}
		return $dbs;
	}
	function printDBs($rows) {
		if (ob_get_contents()) ob_end_flush();
		echo '<th><tr>';
		echo '<td class="header1"><p>name:</p></td>';
		echo '<td class="header2"><p>owner:</p></td>';
		echo '<td class="header1"><p>last size:</p></td>';
		echo '<td class="header2"><p>size:</p></td>';
		echo '<td class="header1"><p>modified:</p></td>';
		echo '<td class="header2"><p>created:</p></td>';
		echo '</tr></th>';
		foreach($rows as $name=>$r) {
			echo '<tr>';
			echo '<td><p>',$name,'</p></td>';
//			echo '<td><p>',$r['owner'],'</p></td>';
			echo '<td><p><a href="?op=admin&owner=',$r['owner'],'">',$r['owner'],'</a></p></td>';
	/*		echo '<td><p>',sprintSize($r['sizeMax']),'</p></td>';*/
			echo '<td><p>',sprintSize($r['sizeLast']),'</p></td>';
			echo '<td><p>',sprintSize($r['sizeNow']),'</p></td>';
			echo '<td><p>',sprintTS($r['modified']),'</p></td>';
			echo '<td><p>',sprintTS($r['created']),'</p></td>';
			
			echo '<td><pre>';
			echo $r['exists']?'E':'';
			echo $r['granted']?'G':'';
			echo $r['registered']?'R':'';
			echo '</pre></td>';
			
			echo '<form method="',FORM_METHOD,'"><td>';
			echo '<input type=hidden name="op" value="',($r['exists']?'dropdb':'createdb'),'"><input type=hidden name="name" value="',$name,'">';
			echo '<input type=submit value="',($r['exists']?'empty':'create'),'"></td></form>';
			
			echo '<form method="',FORM_METHOD,'"><td>';
			echo '<input type=hidden name="op" value="',($r['granted']?'revokedb':'grantdb'),'"><input type=hidden name="name" value="',$name,'">';
			echo '<input type=submit value="',($r['granted']?'disable':'enable'),'"></td></form>';
			
			echo '<form method="',FORM_METHOD,'"><td><input type=hidden name="op" value="deldb"><input type=hidden name="name" value="',$name,'"><input type=submit value="X"></td></form>';
			echo '</tr>';
			flush();
		}
	}
}