[sql-web.git] / mitsql.lib.php

<?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();
		}
	}
}
Commit	Line	Data
b43ab1a6 JP	1	<?php
	2	/* mitsql.lib.php
	3	(c) 2005 Joe Presbrey
	4	written for beta SIPB/MIT SQL service in conjunction with scripts.mit.edu
	5	*/
	6
	7	define('DEBUG', 0);
	8	define('VERSION', '0.1beta');
	9
	10	define('DEFAULT_MAX_DBS', 5);
	11	define('DEFAULT_MAX_SIZE', 26214400);
	12	define('DEFAULT_MAX_DB_SIZE', 0);
	13
	14	define('FORM_METHOD', 'GET');
	15
	16	define('PASS_MIN_LEN', 4);
	17	define('DELIMETER', '+');
	18
	19	define('TEXT_NOREG', 'Your account is not registered with this utility.');
	20	define('TEXT_DBLIMIT', 'You\'ve reached your database limit.');
	21	define('TEXT_FIXREG', 'Fixing database registration for: ');
	22	define('TEXT_NOTREG', 'Database operations are allowed only for those registered to your account.');
	23	define('TEXT_REGPASSTOOSHORT', 'Your SQL password must be at least '.PASS_MIN_LEN.' characters.');
	24
	25	define('HOST', 'sql.mit.edu');
	26	define('ADMINUSER', 'root');
	27	//define('ADMINPASS', 'b6808ded2');
	28	define('ADMINPASS', 'MyBeatsPost@');
	29	//define('ADMINPASS', base64_decode('YjY4MDhkZWQy'));
	30	define('MANAGEDB', 'mitsql');
	31	define('MANAGEDBTABLE', 'db');
	32	define('MANAGEOWNERTABLE', 'owner');
	33
	34	define('SQLSELECT', 'SELECT * FROM %s');
	35	define('SQLSELECTWHERE', 'SELECT * FROM %s WHERE %s');
	36	define('SQLSELECT1WHERE', 'SELECT * FROM %s WHERE %s LIMIT 1');
	37	define('SQLINSERT', 'INSERT INTO %s VALUES (%s)');
	38	define('SQLDELETE', 'DELETE FROM %s WHERE %s');
	39	define('SQLDELETE1', 'DELETE FROM %s WHERE %s LIMIT 1');
	40
	41	$cxn = mysql_connect(HOST, ADMINUSER, ADMINPASS);
	42
	43	function redirectLocal($target) {
	44	$dir = dirname($_SERVER['PHP_SELF'])=='/'?'':dirname($_SERVER['PHP_SELF']);
	45	redirectFull($dir.$target);
	46	}
	47	function redirectFull($target) {
	48	$ssl = $_SERVER['SERVER_PORT'] == 443 ? true : false;
	49	redirect(($ssl?'https://':'http://').$_SERVER['SERVER_NAME'].$target);
	50	}
	51	function redirect($target) {
	52	if (ob_get_contents()) ob_end_clean();
	53	header('Location: '.$target);
	54	exit;
	55	}
	56
	57	function sprintSize($bytes, $float=2) {
	58	if (is_null($bytes)) return null;
	59	$kb = round($bytes / 1024, $float);
	60	$mb = round($bytes / 1024 / 1024, $float);
	61	$gb = round($bytes / 1024 / 1024 / 1024, $float);
	62
	63	return ($bytes<1\|\|$kb<1?$bytes.' B':($mb<1?$kb.' KB':($gb<1?$mb.' MB':$gb.' GB')));
	64	}
65
66	function sprintTS($timestamp) {
67	return substr($timestamp,0,4).
68	'-'.substr($timestamp,4,2).
69	'-'.substr($timestamp,6,2).
70	' '.substr($timestamp,8,2).
71	':'.substr($timestamp,10,2).
72	':'.substr($timestamp,12,2);
73	}
74
75	function getDBSize($tdb) {
76	mysql_select_db($tdb);
77
78	$sql_result = "SHOW TABLE STATUS FROM `" .mysql_escape_string($tdb)."`";
79	$result = @mysql_query($sql_result);
80
81	if($result) {
82	$size = 0;
83	while ($data = mysql_fetch_array($result)) {
84	$size += $data["Data_length"] + $data["Index_length"];
85	}
86	return $size;
87	}
88	else {
89	return null;
90	}
91	}
92
93	function getSSLIdent() {
94	if (isset($_SERVER['SSL_CLIENT_S_DN_Email'])) {
95	$sslemail = $_SERVER['SSL_CLIENT_S_DN_Email'];
96	$ssluser = explode('@',$sslemail);
97	$ssluser = $ssluser[0];
98	return $ssluser;
99	} else {
100	return null;
101	}
102	}
103
104	class DBManage {
105	var $username = "", $dbs;
106	var $r_owner;
107	var $sizeNow = 0;
108
109	function DBManage($username = "") {
110	if (!empty($username)) {
111	$this->username = $username;
112	$this->load();
113	if ($this->fix())
114	$this->load();
115	}
116	}
117	function load() {
118	$rs = mysql_db_query(MANAGEDB,
119	sprintf(SQLSELECT1WHERE,
120	MANAGEOWNERTABLE,
121	'name="'.mysql_escape_string($this->username).'"'));
122	//or exit(mysql_error());
123	while($r = mysql_fetch_assoc($rs)) {
124	$this->r_owner = $r;
125	}
126	mysql_free_result($rs);
127
128	$dbs = array();
129
130	$rs = mysql_db_query(MANAGEDB,
131	sprintf(SQLSELECTWHERE,
132	MANAGEDBTABLE,
133	'owner="'.mysql_escape_string($this->username).'" ORDER BY name')) or exit(mysql_error());
134	while($r = mysql_fetch_assoc($rs)) {
135	$t = $r;
136	$t['registered'] = 1;
137	$t['granted'] = 0;
138	$dbs[$r['name']] = $t;
139	}
140	mysql_free_result($rs);
141
142	$rs = mysql_db_query('mysql',
143	sprintf(SQLSELECTWHERE,
144	MANAGEDBTABLE,
145	'User="'.mysql_escape_string($this->username).'" ORDER BY Db'));
146	while($r = mysql_fetch_assoc($rs)) {
147	if (!isset($dbs[$r['Db']])) {
148	$dbs[$r['Db']] = array();
149	$dbs[$r['Db']]['registered'] = 0;
150	}
151	$dbs[$r['Db']]['granted'] = 1;
152	$dbs[$r['Db']]['name'] = $r['Db'];
153	}
154	mysql_free_result($rs);
155
156	foreach($dbs as $d1=>$d2) {
157	$size = getDBSize($d1);
158	if (is_null($size)) {
159	$dbs[$d1]['sizeNow'] = 0;
160	$dbs[$d1]['exists'] = 0;
161	} else {
162	$dbs[$d1]['sizeNow'] = $size;
163	$this->sizeNow += $size;
164	$dbs[$d1]['exists'] = 1;
165	}
166	}
167	$this->dbs = $dbs;
168	}
169	function isInit() {
170	return !is_null($this->r_owner);
171	}
172	function grantDB($dbname) {
173	mysql_query('GRANT ALL PRIVILEGES ON '
174	.'`'.mysql_escape_string($dbname).'` . * '
175	.'TO \''.mysql_escape_string($this->username).'\'@\'%\'') or exit(mysql_error());
176	$this->flushPriv();
177	}
178	function flushPriv() {
179	mysql_query('FLUSH PRIVILEGES') or exit(mysql_error());
180	}
181	function revokeDB($dbname) {
182	mysql_db_query('mysql',
183	'DELETE FROM `db` WHERE '
184	.'User = \''.mysql_escape_string($this->username).'\' '
185	.'AND Db = \''.mysql_escape_string($dbname).'\'') or exit(mysql_error());
186	$this->flushPriv();
187	}
188	function setPassword($password) {
189	mysql_query('SET PASSWORD FOR '.
190	'\''.mysql_escape_string($this->username).'\'@\'%\'='.
191	'PASSWORD(\''.mysql_escape_string($password).'\')') or exit(mysql_error());
192	}
193	function init($password, $maxDBs = DEFAULT_MAX_DBS, $maxSize = DEFAULT_MAX_SIZE) {
194	mysql_query('GRANT USAGE ON * . * TO '.
195	'\''.mysql_escape_string($this->username).'\'@\'%\' '.
196	'IDENTIFIED BY \''.mysql_escape_string($password).'\'') or exit(mysql_error());
197	$this->flushPriv();
198	mysql_db_query(MANAGEDB,
199	sprintf(SQLINSERT,
200	MANAGEOWNERTABLE,
201	sprintf("'%s','%s','%s',NOW(),NOW()",
202	mysql_escape_string($this->username),
203	$maxDBs,
204	$maxSize))) or exit(mysql_error());
205	}
206	function uninit() {
207	mysql_db_query('mysql',
208	'DELETE FROM `user` WHERE '
209	.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
210	mysql_db_query('mysql',
211	'DELETE FROM `db` WHERE '
212	.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
213	mysql_db_query('mysql',
214	'DELETE FROM `tables_priv` WHERE '
215	.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
216	mysql_db_query('mysql',
217	'DELETE FROM `columns_priv` WHERE '
218	.'User = \''.mysql_escape_string($this->username).'\'') or exit(mysql_error());
219	mysql_query('FLUSH PRIVILEGES') or exit(mysql_error());
220
221	mysql_db_query(MANAGEDB,
222	sprintf(SQLDELETE,
223	MANAGEDBTABLE,
224	'owner=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error());
225
226	foreach($this->dbs as $db)
227	$this->dropDB($db['name']);
228
229	mysql_db_query(MANAGEDB,
230	sprintf(SQLDELETE1,
231	MANAGEOWNERTABLE,
232	'name=\''.mysql_escape_string($this->username).'\'')) or exit(mysql_error());
233	}
234	function registerDB($name, $maxSize = DEFAULT_MAX_DB_SIZE) {
235	mysql_db_query(MANAGEDB,
236	sprintf(SQLINSERT,
237	MANAGEDBTABLE,
238	sprintf("'%s','%s','%s','',NOW(),NOW()",
239	mysql_escape_string($name),
240	mysql_escape_string($this->username),
241	$maxSize))) or exit(mysql_error());
242	}
243	function unregisterDB($name) {
244	mysql_db_query(MANAGEDB,
245	sprintf(SQLDELETE1,
246	MANAGEDBTABLE,
247	'name="'.mysql_escape_string($name).'"')) or exit(mysql_error());
248	}
249	function createDB($name) {
250	@mysql_create_db($name);
251	}
252	function dropDB($name) {
253	@mysql_drop_db($name);
254	}
255	function addDB($name) {
256	if (!$this->isExists($name))
257	$this->createDB($name);
258	if (!$this->isRegistered($name))
259	$this->registerDB($name);
260	if (!$this->isGranted($name))
261	$this->grantDB($name);
262	}
263	function delDB($name) {
264	$this->revokeDB($name);
265	$this->unregisterDB($name);
266	$this->dropDB($name);
267	}
268	function getNumDBs() {
269	return count($this->dbs);
270	}
271	function getTotalSize() {
272	return $this->sizeNow;
273	}
274	function isRegistered($name) {
275	if (isset($this->dbs[$name]))
276	return $this->dbs[$name]['registered'];
277	else return false;
278	}
279	function isExists($name) {
280	if (isset($this->dbs[$name]))
281	return $this->dbs[$name]['exists'];
282	else return false;
283	}
284	function isGranted($name) {
285	if (isset($this->dbs[$name]))
286	return $this->dbs[$name]['granted'];
287	else return false;
288	}
289
290	function printOwnerHeader() {
291	echo '<th><tr>';
292	echo '<td class="header2"><p></p></td>';
293	echo '<td class="header1"><p>databases:</p></td>';
294	echo '<td class="header2"><p>storage:</p></td>';
295	echo '<td class="header1"><p>modified:</p></td>';
296	// echo '<td class="header2"><p>created:</p></td>';
297	echo '</tr></th>';
298	}
299	function printOwner() {
300	$obj_owner = $this->r_owner;
301	echo '<table>';
302	$this->printOwnerHeader();
303	echo '<td><p>status:</p></td>';
304	echo '<td><p>',$this->getNumDBs(),'</p></td>';
305	echo '<td><p>',sprintSize($this->getTotalSize()),'</p></td>';
306	echo '</tr><tr>';
307	echo '<td><p>limits:</p></td>';
308	echo '<td><p>',$obj_owner['maxDB'],'</p></td>';
309	echo '<td><p>',sprintSize($obj_owner['maxSize']),'</p></td>';
310	echo '<td><p>',sprintTS($obj_owner['modified']),'</p></td>';
311	// echo '<td><p>',sprintTS($obj_owner['created']),'</p></td>';
312	echo '</tr>';
313	echo '</table>';
314	}
315	function printOwnerDBs($showOwner = false) {
316	$rows = $this->dbs;
317	echo '<th><tr>';
318	echo '<td class="header1"><p>name:</p></td>';
319	echo '<td class="header2"><p>owner:</p></td>';
320	echo '<td class="header1"><p>last size:</p></td>';
321	echo '<td class="header2"><p>size:</p></td>';
322	echo '<td class="header1"><p>modified:</p></td>';
323	// echo '<td class="header2"><p>created:</p></td>';
324	echo '</tr></th>';
325	foreach($rows as $name=>$r) {
326	echo '<tr>';
327	echo '<td><p>',$name,'</p></td>';
328	echo '<td><p>',$r['owner'],'</p></td>';
329	/* echo '<td><p>',sprintSize($r['sizeMax']),'</p></td>';*/
330	echo '<td><p>',sprintSize($r['sizeLast']),'</p></td>';
331	echo '<td><p>',sprintSize($r['sizeNow']),'</p></td>';
332	echo '<td><p>',sprintTS($r['modified']),'</p></td>';
333	// echo '<td><p>',sprintTS($r['created']),'</p></td>';
334
335	echo '<form method="',FORM_METHOD,'"><td>';
336	echo '<input type=hidden name="op" value="',($r['exists']?'dropdb':'createdb'),'"><input type=hidden name="name" value="',$name,'">';
337	echo '<input type=submit value="',($r['exists']?'empty':'create'),'"></td></form>';
338
339	echo '<form method="',FORM_METHOD,'"><td>';
340	echo '<input type=hidden name="op" value="',($r['granted']?'revokedb':'grantdb'),'"><input type=hidden name="name" value="',$name,'">';
341	echo '<input type=submit value="',($r['granted']?'disable':'enable'),'"></td></form>';
342
343	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>';
344	echo '</tr>';
345	flush();
346	}
347	}
348	function fix() {
349	$fixed = false;
350	foreach($this->dbs as $db) {
351	if (!$db['registered']) {
352	echo '<p class="err">',TEXT_FIXREG,$db['name'],'</p>';
353	flush();
354	$this->registerDB($db['name']);
355	$fixed = true;
356	}
357	}
358	return $fixed;
359	}
360	function getNumMaxDBs() {
361	return $this->r_owner['maxDB'];
362	}
363	}
364
365	class DBAdmin {
366	function mysqlDBList() {
367	$rs = mysql_query('SHOW DATABASES');
368	$dbs = array();
369	while($r = mysql_fetch_row($rs)) {
370	$dbs[$r[0]] = array();
371	$dbs[$r[0]]['registered'] = 0;
372	$dbs[$r[0]]['granted'] = 0;
373	$dbs[$r[0]]['exists'] = 1;
374	$dbs[$r[0]]['owner'] = '';
375	$dbs[$r[0]]['sizeLast'] = 0;
376	$dbs[$r[0]]['sizeNow'] = '';
377	$dbs[$r[0]]['modified'] = '';
378	$dbs[$r[0]]['created'] = '';
379	}
380	return $dbs;
381	}
382	function fullDBList() {
383	//$dbs = array();
384	$dbs = $this->mysqlDBList();
385
386	$rs = mysql_db_query(MANAGEDB,
387	sprintf(SQLSELECT,
388	MANAGEDBTABLE)) or exit(mysql_error());
389	while($r = mysql_fetch_assoc($rs)) {
390	$t = $r;
391	$t['registered'] = 1;
392	$t['granted'] = 0;
393	$dbs[$r['name']] = $t;
394	}
395	mysql_free_result($rs);
396
397	$rs = mysql_db_query('mysql',
398	sprintf(SQLSELECT,
399	MANAGEDBTABLE));
400	while($r = mysql_fetch_assoc($rs)) {
401	if (!isset($dbs[$r['Db']])) {
402	$dbs[$r['Db']] = array();
403	$dbs[$r['Db']]['registered'] = 0;
404	}
405	$dbs[$r['Db']]['granted'] = 1;
406	$dbs[$r['Db']]['name'] = $r['Db'];
407	}
408	mysql_free_result($rs);
409
410	foreach($dbs as $d1=>$d2) {
411	$size = getDBSize($d1);
412	if (is_null($size)) {
413	$dbs[$d1]['sizeNow'] = 0;
414	$dbs[$d1]['exists'] = 0;
415	} else {
416	$dbs[$d1]['sizeNow'] = $size;
417	//$this->sizeNow += $size;
418	$dbs[$d1]['exists'] = 1;
419	}
420	}
421	return $dbs;
422	}
423	function printDBs($rows) {
424	if (ob_get_contents()) ob_end_flush();
425	echo '<th><tr>';
426	echo '<td class="header1"><p>name:</p></td>';
427	echo '<td class="header2"><p>owner:</p></td>';
428	echo '<td class="header1"><p>last size:</p></td>';
429	echo '<td class="header2"><p>size:</p></td>';
430	echo '<td class="header1"><p>modified:</p></td>';
431	echo '<td class="header2"><p>created:</p></td>';
432	echo '</tr></th>';
433	foreach($rows as $name=>$r) {
434	echo '<tr>';
435	echo '<td><p>',$name,'</p></td>';
436	// echo '<td><p>',$r['owner'],'</p></td>';
437	echo '<td><p><a href="?op=admin&owner=',$r['owner'],'">',$r['owner'],'</a></p></td>';
438	/* echo '<td><p>',sprintSize($r['sizeMax']),'</p></td>';*/
439	echo '<td><p>',sprintSize($r['sizeLast']),'</p></td>';
440	echo '<td><p>',sprintSize($r['sizeNow']),'</p></td>';
441	echo '<td><p>',sprintTS($r['modified']),'</p></td>';
442	echo '<td><p>',sprintTS($r['created']),'</p></td>';
443
444	echo '<td><pre>';
445	echo $r['exists']?'E':'';
446	echo $r['granted']?'G':'';
447	echo $r['registered']?'R':'';
448	echo '</pre></td>';
449
450	echo '<form method="',FORM_METHOD,'"><td>';
451	echo '<input type=hidden name="op" value="',($r['exists']?'dropdb':'createdb'),'"><input type=hidden name="name" value="',$name,'">';
452	echo '<input type=submit value="',($r['exists']?'empty':'create'),'"></td></form>';
453
454	echo '<form method="',FORM_METHOD,'"><td>';
455	echo '<input type=hidden name="op" value="',($r['granted']?'revokedb':'grantdb'),'"><input type=hidden name="name" value="',$name,'">';
456	echo '<input type=submit value="',($r['granted']?'disable':'enable'),'"></td></form>';
457
458	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>';
459	echo '</tr>';
460	flush();
461	}
462	}
463	}