From 9c70b48184fc9dc34b9725daa6b3b050fd49e6d0 Mon Sep 17 00:00:00 2001
From: Joe Presbrey <presbrey@mit.edu>
Date: Fri, 9 Sep 2005 08:33:50 +0000
Subject: [PATCH] check quotas function dont let overquota'd users create
 databases BASE_URL points just above ~/do/

git-svn-id: svn://presbrey.mit.edu/sql/mitsql@55 a142d4bd-2cfb-0310-9673-cb33a7e74f58
---
 admin/main.php       |  2 +-
 global.act.php       |  4 ++++
 lib/dbaccess.lib.php | 28 ++++++++++++++++++++++++++++
 lib/display.lib.php  | 30 +++++++++++++++++++++++-------
 lib/security.lib.php |  6 +++++-
 main.php             |  9 +++++----
 mitsql.cfg.php       |  2 +-
 test.php             | 13 -------------
 tpl/head.php         |  3 ++-
 tpl/main.php         | 15 ++++++++++-----
 tpl/menu.php         | 19 ++++++++++---------
 11 files changed, 89 insertions(+), 42 deletions(-)

diff --git a/admin/main.php b/admin/main.php
index 4a7a749..5f6535b 100755
--- a/admin/main.php
+++ b/admin/main.php
@@ -17,7 +17,7 @@ $users = fetchRows(DBSelect($sql),'UserId');
 include 'tpl/head.php';
 
 foreach($users as $id=>$user) {
-	echo '<a href="',newQSA(array('impersonate'=>$id)),'">',$user['Username'],'</a> (',$user['Name'],')<br />';
+	echo '<a href="do/admin/main',newQSA(array('impersonate'=>$id)),'">',$user['Username'],'</a> (',$user['Name'],')<br />';
 }
 
 include 'tpl/foot.php';
diff --git a/global.act.php b/global.act.php
index 8fe4a4c..94e662d 100644
--- a/global.act.php
+++ b/global.act.php
@@ -44,4 +44,8 @@ if (isSSL() || !isLoggedIn()) {
 	unset($_SESSION['LoginSSL']);
 }
 
+if (isPost() || isset($i_refresh)) {
+	checkQuotas($UserId);
+	isset($i_refresh) && redirect('main');
+}
 ?>
diff --git a/lib/dbaccess.lib.php b/lib/dbaccess.lib.php
index bcbc894..d58bef8 100644
--- a/lib/dbaccess.lib.php
+++ b/lib/dbaccess.lib.php
@@ -49,4 +49,32 @@ function calcDBSize($tdb) {
    }
 }
 
+function checkQuotas($userId=null) {
+	if (empty($userId)) {
+		$sql = 'SELECT DatabaseId,Name FROM DB WHERE bEnabled=1';
+	} else {
+		$sql = sprintf("SELECT DB.DatabaseId,Name FROM DB INNER JOIN DBOwner ON DB.DatabaseId = DBOwner.DatabaseId WHERE bEnabled=1 AND UserId = '%s'", mysql_escape_string($userId));
+	}
+	$databases = fetchRows(DBSelect($sql),'Name');
+	foreach($databases as $db) {
+		$DBId = $db['DatabaseId'];
+		$arr['dLastCheck'] = 'NOW()';
+		$arr['nBytes'] = calcDBSize($db['Name']);
+		$sql = sprintf("UPDATE DB %s WHERE DatabaseId = '%s'",
+						buildSQLSet($arr),
+						mysql_escape_string($DBId));
+		DBUpdate($sql);
+	}
+	$sql = "UPDATE UserStat SET nBytes = (
+				SELECT SUM(nBytes)
+				FROM DB
+				INNER JOIN DBOwner ON DBOwner.DatabaseId = DB.DatabaseId
+				WHERE DBOwner.UserId = UserStat.UserId
+				  AND DB.bEnabled=1
+				GROUP BY UserId
+				), dLastCheck = NOW()";
+	if (!empty($userId)) $sql .= sprintf(" WHERE UserId = '%s'", mysql_escape_string($userId));
+	DBUpdate($sql);
+}
+
 ?>
diff --git a/lib/display.lib.php b/lib/display.lib.php
index ecb4e49..4dfb81c 100644
--- a/lib/display.lib.php
+++ b/lib/display.lib.php
@@ -25,23 +25,39 @@ function printBar($percent, $txt1 = '', $txt2 = '') {
 	$color2 = 'white';
 	$color3 = 'white';
 	$color4 = 'black';
-	if ($percent>1) {
+	/* if ($percent>1) {
 		$percent = $percent/100;
-	} /*
+	}
 	} elseif (!is_integer($percent) && $percent<2) {
 		$percent = $percent*100;
 	} elseif ($percent == 1) {
 		$percent = 100;
 	} */
-	$per1 = $per2 = ceil($percent*100);
-	$per2 = 100 - $per2;
+	$per1 = $per2 = floor($percent*100);
+	if ($per1>100) {
+		$per1 = 100;
+		$per2 = 0;
+	} else {
+		$per2 = 100 - $per2;
+	}
 	$per1 .= "%";
 	$per2 .= "%";
-	return '<table cellpadding=0 cellspacing=0 width="100%" class="bargraph">
+/*	return '<table cellpadding=0 cellspacing=0 width="100%" class="bargraph">
 				<tr>
 				<td class="bar" align="left" width="'.$per1.'"><div style="position: relative;"><div style="left: 0; display: inline; position: absolute;">&nbsp;'.$txt1.'&nbsp;</div>&nbsp;</div></td>
-				<td class="fill" width="'.$per2.'"><div style="position: relative;">&nbsp;<div style="right: 0; display: inline; position: absolute;">'.$txt2.'&nbsp;<em>'.$per1.'</em></div></div></td>
-				</tr></table>';
+				<td class="fill" align="left" width="'.$per2.'"><div style="position: relative;">&nbsp;<div style="right: 0; display: inline; position: absolute;">'.$txt2.'&nbsp;<em>'.$per1.'</em></div></div></td>
+				</tr></table>';*/
+	return '<table cellpadding=0 cellspacing=0 width="100%" class="bargraph">
+				<tr>
+				<td class="bar" align="left" width="'.$per1.'"><div style="position: relative;"></div></td>
+				<td class="fill" align="right" width="'.$per2.'"><div style="position: relative;"></div></td>
+				</tr>
+			</table><table cellpadding=0 cellspacing=0 width="100%">
+				<tr style="height: 1px;">
+				<td class="bar" align="left" width="50%"><div style="position: relative;"><div style="bottom: 3px; left: 5px; position: absolute;">'.$txt1.'</div></div></td>
+				<td class="fill" align="right" width="50%"><div style="position: relative;"><div style="bottom: 3px; right: 3px; position: absolute;">'.$txt2.'&nbsp;&nbsp;<em>'.$per1.'</em></div></div></td>
+				</tr>
+				</table>';
 }
 
 ?>
diff --git a/lib/security.lib.php b/lib/security.lib.php
index 03121c4..fca7a0f 100644
--- a/lib/security.lib.php
+++ b/lib/security.lib.php
@@ -87,7 +87,7 @@ class User {
 	var $dblist;
     function User($userId) {
 		$this->userId = $userId;
-        $sql = sprintf("SELECT User.UserId, Username, Password, Name, Email, UL, bEnabled, nBytesSoft, nBytesHard, nBytes, nDatabases, nDatabasesHard
+        $sql = sprintf("SELECT User.UserId, Username, Password, Name, Email, UL, bEnabled, nBytesSoft, nBytesHard, nBytes, nDatabases, nDatabasesHard, IF(nBytes>nBytesHard,1,0) AS bOverQuota
                         FROM User
 						INNER JOIN UserQuota ON User.UserId = UserQuota.UserId
 						INNER JOIN UserStat ON User.UserId = UserStat.UserId
@@ -121,6 +121,9 @@ class User {
     function getUsername() {
         return $this->exists()?$this->info['Username']:'';
     }
+    function isOverQuota() {
+        return $this->exists()?($this->info['bOverQuota']>0?true:false):'';
+    }
     function getBytes() {
         if($this->exists()) {
 			$arr['nBytes'] = $this->info['nBytes'];
@@ -195,6 +198,7 @@ class User {
 						mysql_escape_string($this->getUserId()));
 //			$r = fetchRows(DBSelect($sql),'DatabaseId');
 			$r = fetchRows(DBSelect($sql),'Name');
+			ksort($r);
 			return $r;
 		}
 	}
diff --git a/main.php b/main.php
index 6d0e19b..90bf2ea 100644
--- a/main.php
+++ b/main.php
@@ -10,10 +10,11 @@ $User = new User($Login->getUserID());
 if (isPost()) {
 	if (isset($i_newdb)) {
 		$dbname = $User->getUsername().DELIMETER.$i_newdb;
-		if ($User->addDB($dbname)==false) {
+		if ($User->isOverQuota()) {
+			$err[] = 'You are over your quota. You may not add databases.';
+		} elseif ($User->addDB($dbname)==false) {
 			$err[] = mysql_error();
 		} else {
-			$User->refresh();
 			$msg[] = 'Database `'.$dbname.'` created.';
 		}
 	}
@@ -26,9 +27,9 @@ if (isPost()) {
 				$err[] = mysql_error();
 			}
 		}
-		if (!count($err))
-			$User->refresh();
 	}
+	if (!count($err))
+		$User->refresh();
 }
 
 $myDBs = $User->getDBList();
diff --git a/mitsql.cfg.php b/mitsql.cfg.php
index 0d37ea8..79c60fa 100755
--- a/mitsql.cfg.php
+++ b/mitsql.cfg.php
@@ -27,7 +27,7 @@ defined('ADMINPASS') || define('ADMINPASS', base64_decode('TXlCZWF0c1Bvc3RA'));
 defined('ADMINDB') || define('ADMINDB', 'mitsql');
 
 $BASE_PATH = dirname(__FILE__).'/';
-$BASE_URL = BASE_URL;
+$BASE_URL = 'http://'.$_SERVER['SERVER_NAME'].BASE_URL;
 //$BASE_URL = isset($_SERVER['SCRIPT_NAME'])?dirname($_SERVER['SCRIPT_NAME']).'/':'';
 
 set_time_limit(0);
diff --git a/test.php b/test.php
index 6109019..7876ba8 100755
--- a/test.php
+++ b/test.php
@@ -20,19 +20,6 @@ function getManagedDBs($owner=null) {
 	return $owners;
 }
 
-function checkQuotas() {
-	$databases = fetchRows(DBSelect('SELECT DatabaseId,Name FROM DB WHERE bEnabled=1'),'Name');
-	foreach($databases as $db) {
-		$DBId = $db['DatabaseId'];
-		$arr['dLastCheck'] = 'NOW()';
-		$arr['nBytes'] = calcDBSize($db['Name']);
-		$sql = sprintf("UPDATE DB %s WHERE DatabaseId = '%s'",
-						buildSQLSet($arr),
-						mysql_escape_string($DBId));
-		DBUpdate($sql);
-	}
-}
-
 /*
 $g = getManagedDBs();
 print_r($g);
diff --git a/tpl/head.php b/tpl/head.php
index ee20655..1341f61 100644
--- a/tpl/head.php
+++ b/tpl/head.php
@@ -4,10 +4,11 @@
 <head>
     <title>MIT SQL Services for Athena</title>
     <link rel=stylesheet href="<?=$BASE_URL?>mitsql.css" type="text/css">
+	<base href="<?=$BASE_URL?>">
 </head>
 
 <body>
 <div id="body">
-<div id="content0"><span id="sitename">MIT SQL Services</span><span id="contact"><a href="contact">Contact/Help</a></span></div>
+<div id="content0"><span id="sitename">MIT SQL Services</span><span id="contact"><a href="do/contact">Contact/Help</a></span></div>
 <?php include 'menu.php'; ?>
 <div id="content2">
diff --git a/tpl/main.php b/tpl/main.php
index be19499..2c019d4 100644
--- a/tpl/main.php
+++ b/tpl/main.php
@@ -27,20 +27,25 @@ if (isset($i_dropask)) {
 	$total = $bytes['nBytesHard'];
 	foreach($myDBs as $db) {
 		echo '<tr><td width="100%">';
-		$usage += $db['nBytes'];
 		if ($total>0)
 			$percentage = $db['nBytes']/$total;
 		else $percentage = 0;
-		echo printBar($percentage, $db['Name'], sprintSize($db['nBytes']));
+		echo printBar($percentage, $db['Name'], str_replace(' ', '&nbsp;', sprintSize($db['nBytes'])));
 		echo '</td><td>';
 		echo '<input type="submit" name="dropask[',$db['Name'],']" value="drop">';
 		echo '</td></tr>';
 	}
-	if ($total>0)
+	if ($total>0) {
 		$percentage = $usage/$total;
-	else
+	} else {
 		$percentage = 0;
-	echo printBar($percentage, '<b>TOTAL</b>', sprintSize($usage).' / '.sprintSize($total));
+	}
+	echo '<tr><td colspan=2>';
+	echo '<hr />';
+	echo '</td></tr>';
+	echo '<tr><td colspan=2>';
+	echo printBar($percentage, '<b>TOTAL&nbsp;USED</b>', str_replace(' ', '&nbsp;', sprintSize($usage).' of '.sprintSize($total)));
+	echo '</td></tr>';
 ?>
 </form>
 </table>
diff --git a/tpl/menu.php b/tpl/menu.php
index 1ab1ded..177f8f9 100644
--- a/tpl/menu.php
+++ b/tpl/menu.php
@@ -1,24 +1,25 @@
 <div id="content1">
 <?php if (isLoggedIn()): ?>
 <div style="float:left">
-<a href="main">Main</a> |
-<a href="logout">Logout</a>
+<a href="do/main">Main</a> |
+<a href="do/main?refresh">Refresh</a> |
+<?php if (isAdmin()) { ?>
+<a href="do/admin/main">Admin</a> |
+<?php } ?>
+<a href="do/logout">Logout</a>
 </div>&nbsp;
 <?php
 	$loggedInText = $Login->getUsername();
 	if (isImpersonating()) {
-		$loggedInText = '<a href="admin/main?impersonate">'.$loggedInText.'</a>';
+		$loggedInText = '<a href="do/admin/main?impersonate">'.$loggedInText.'</a>';
 	}
 ?>
 Logged in as <em><?=$loggedInText?></em> on <?=DBHOST?>
 <?php else: ?>
 <div style="float:left">
-<a href="index">Home</a> |
-<?php if (isAdmin()) { ?>
-<a href="admin/main">Admin</a> |
-<?php } ?>
-<a href="signup">Signup</a> |
-Login via <a href="login?ssl=0">SQL</a> or <a href="login?ssl=1">SSL</a>
+<a href="do/index">Home</a> |
+<a href="do/signup">Signup</a> |
+Login via <a href="do/login?ssl=0">SQL</a> or <a href="do/login?ssl=1">SSL</a>
 </div>&nbsp;
 <?php endif; ?>
 </div>
-- 
2.45.0