From 997305cf93cd27c51f44a2e882238f190ad17606 Mon Sep 17 00:00:00 2001
From: Joe Presbrey
Date: Sat, 16 Jul 2005 14:44:16 +0000
Subject: [PATCH] first stab at 0.2 better DB structure uses classes instead
git-svn-id: svn://presbrey.mit.edu/sql/mitsql@34 a142d4bd-2cfb-0310-9673-cb33a7e74f58
---
.htaccess | 69 ++++++
db/mitsql.sql | 127 +++++++++++
defaults.cfg.php | 24 +++
global.act.php | 20 ++
global.done.php | 9 +
index.php | 42 +---
lib/dbaccess.lib.php | 45 ++++
lib/display.lib.php | 23 ++
lib/joe.lib.php | 203 ++++++++++++++++++
lib/mitsql.lib.php | 28 +++
lib/security.lib.php | 113 ++++++++++
login.php | 26 +++
main.php | 0
mitsql.cfg.php | 34 +++
style.inc => mitsql.css | 0
mitsql.lib.php | 463 ----------------------------------------
test.php | 19 +-
tpl/foot.inc | 4 +
tpl/head.inc | 7 +
tpl/index.php | 7 +
tpl/login_ssl.php | 13 ++
21 files changed, 775 insertions(+), 501 deletions(-)
create mode 100644 .htaccess
create mode 100644 db/mitsql.sql
create mode 100644 defaults.cfg.php
create mode 100644 global.act.php
create mode 100644 global.done.php
create mode 100644 lib/dbaccess.lib.php
create mode 100644 lib/display.lib.php
create mode 100755 lib/joe.lib.php
create mode 100755 lib/mitsql.lib.php
create mode 100644 lib/security.lib.php
create mode 100644 login.php
create mode 100644 main.php
create mode 100755 mitsql.cfg.php
rename style.inc => mitsql.css (100%)
delete mode 100755 mitsql.lib.php
create mode 100644 tpl/foot.inc
create mode 100644 tpl/head.inc
create mode 100644 tpl/index.php
create mode 100644 tpl/login_ssl.php
diff --git a/.htaccess b/.htaccess
new file mode 100644
index 0000000..98979d6
--- /dev/null
+++ b/.htaccess
@@ -0,0 +1,69 @@
+#php_value auto_prepend_file "prepend.php"
+php_value auto_append_file "global.done.php"
+#php_flag xdebug.remote_enable on
+#php_flag xdebug.remote_autostart on
+#php_value xdebug.remote_host "localhost"
+#php_value xdebug.remote_port "9000"
+
+RewriteEngine On
+RewriteBase /~administrator/mitsql/
+
+#RewriteOptions MaxRedirects=1
+
+#RewriteCond %{REQUEST_FILENAME}.php -f
+#RewriteRule ^.* do/%{REQUEST_FILENAME}
+#RewriteCond %{REQUEST_URI} !^/~administrator/mitsql/go/.+
+
+##RewriteCond %{REQUEST_URI}
+##RewriteCond %{REQUEST_FILENAME} !-s
+#RewriteRule ^do/.+$ - [L]
+
+#RewriteCond %{IS_SUBREQ} !="true"
+#RewriteRule ^.*$ do/index [R,L]
+
+##RewriteCond %{REQUEST_URI} ^.*/do/.+$
+##RewriteRule ^do/.+$ - [C]
+#RewriteRule ^do/(.+)$ $1.php [PT,L]
+
+#RewriteRule ^do/(.+)$ $1.php [L]
+
+## attempt 2
+#RewriteCond %{REQUEST_URI} !^.*/do/.+$
+#RewriteRule ^.*$ do/index [R,L]
+#
+#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP
+#RewriteRule \.php$ - [F]
+#
+##RewriteCond do/%{REQUEST_FILENAME}.php -f
+#RewriteRule ^do/(.+)$ $1.php [L]
+
+# attempt 3
+
+#RewriteRule \.php$ - [F,C]
+
+#RewriteCond %{REQUEST_URI} !^.*/do/.+$
+#RewriteRule ^.*$ do/index [R,L]
+
+#RewriteCond do/%{REQUEST_FILENAME}.php -f
+#RewriteRule ^do/(.+)$ $1.php [L]
+
+#RewriteCond %{THE_REQUEST} !^(GET|HEAD)\ /.+\.php\ HTTP
+#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+mitsql/do/.+\ HTTP
+
+## REVISION 4
+
+RewriteCond %{REQUEST_URI} ^.*/mitsql/do/(.+)$
+RewriteRule ^do/(.+)$ $1.php [QSA]
+
+RewriteCond %{REQUEST_FILENAME} !-f
+RewriteRule .* do/index [R,L,QSA]
+
+# now redundant after following conditions
+#RewriteCond %{THE_REQUEST} ^(GET|HEAD)\ /.+\.php\ HTTP
+#RewriteRule .* do/index [R,L,QSA]
+
+RewriteCond %{THE_REQUEST} !^(GET|HEAD)\ /.+mitsql/do/.+\ HTTP
+RewriteCond %{REQUEST_FILENAME} !\.html$
+RewriteCond %{REQUEST_FILENAME} !\.css$
+RewriteCond %{REQUEST_FILENAME} !\.jpg$
+RewriteRule .* do/index [R,L,QSA]
diff --git a/db/mitsql.sql b/db/mitsql.sql
new file mode 100644
index 0000000..0c16cd4
--- /dev/null
+++ b/db/mitsql.sql
@@ -0,0 +1,127 @@
+-- MySQL dump 10.9
+--
+-- Host: sql.mit.edu Database: mitsql
+-- ------------------------------------------------------
+-- Server version 4.1.11-Debian_4-log
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Current Database: `mitsql`
+--
+
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mitsql` /*!40100 DEFAULT CHARACTER SET latin1 */;
+
+USE `mitsql`;
+
+--
+-- Table structure for table `db`
+--
+
+DROP TABLE IF EXISTS `db`;
+CREATE TABLE `db` (
+ `name` varchar(255) NOT NULL default '',
+ `owner` varchar(255) NOT NULL default '',
+ `sizeMax` bigint(20) NOT NULL default '0',
+ `sizeLast` bigint(20) NOT NULL default '0',
+ `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ `created` timestamp NOT NULL default '0000-00-00 00:00:00',
+ PRIMARY KEY (`name`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `db`
+--
+
+
+/*!40000 ALTER TABLE `db` DISABLE KEYS */;
+LOCK TABLES `db` WRITE;
+INSERT INTO `db` VALUES ('presbrey+123','presbrey',0,0,'2005-03-01 10:50:25','2005-03-01 10:50:25'),('presbrey+12','presbrey',0,0,'2005-03-01 10:50:26','2005-03-01 10:50:26'),('bgelb+db','bgelb',0,0,'2005-01-18 23:39:43','2005-01-18 23:39:43'),('presbrey+12345','presbrey',0,0,'2005-03-01 10:50:31','2005-03-01 10:50:31'),('presbrey+forum','presbrey',0,0,'2005-03-11 12:28:26','2005-03-11 12:28:26'),('test+1','test',0,0,'2005-01-25 03:25:08','2005-01-25 03:25:08'),('test+2','test',0,0,'2005-01-25 03:25:11','2005-01-25 03:25:11'),('test+3','test',0,0,'2005-01-25 03:25:14','2005-01-25 03:25:14'),('test+4','test',0,0,'2005-01-25 03:25:16','2005-01-25 03:25:16'),('test+5','test',0,0,'2005-01-25 03:25:20','2005-01-25 03:25:20'),('presbrey+1234','presbrey',0,0,'2005-03-01 10:50:23','2005-03-01 10:50:23'),('andriko+phpBB','andriko',0,0,'2005-03-11 13:27:28','2005-03-11 13:27:28'),('mrivas03+blog','mrivas03',0,0,'2005-07-10 13:25:18','2005-07-10 13:25:18');
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `db` ENABLE KEYS */;
+
+--
+-- Table structure for table `groups`
+--
+
+DROP TABLE IF EXISTS `groups`;
+CREATE TABLE `groups` (
+ `owner` varchar(255) NOT NULL default '',
+ `group` varchar(255) NOT NULL default '',
+ PRIMARY KEY (`owner`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `groups`
+--
+
+
+/*!40000 ALTER TABLE `groups` DISABLE KEYS */;
+LOCK TABLES `groups` WRITE;
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `groups` ENABLE KEYS */;
+
+--
+-- Table structure for table `owner`
+--
+
+DROP TABLE IF EXISTS `owner`;
+CREATE TABLE `owner` (
+ `name` varchar(255) NOT NULL default '',
+ `maxDB` bigint(20) NOT NULL default '0',
+ `maxSize` bigint(20) NOT NULL default '0',
+ `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+ `created` timestamp NOT NULL default '0000-00-00 00:00:00',
+ PRIMARY KEY (`name`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `owner`
+--
+
+
+/*!40000 ALTER TABLE `owner` DISABLE KEYS */;
+LOCK TABLES `owner` WRITE;
+INSERT INTO `owner` VALUES ('bgelb',5,26214400,'2005-01-18 23:39:18','2005-01-18 23:39:18'),('presbrey',5,26214400,'2005-03-01 10:50:21','2005-03-01 10:50:21'),('test',5,1048576,'2005-01-25 03:25:03','2005-01-25 03:25:03'),('jtwang',5,26214400,'2005-03-01 12:32:30','2005-03-01 12:32:30'),('andriko',5,26214400,'2005-03-11 12:32:49','2005-03-11 12:32:49'),('melissa2',5,26214400,'2005-05-19 01:19:44','2005-05-19 01:19:44'),('mrivas03',5,26214400,'2005-07-10 12:06:24','2005-07-10 12:06:24');
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `owner` ENABLE KEYS */;
+
+--
+-- Table structure for table `passwd`
+--
+
+DROP TABLE IF EXISTS `passwd`;
+CREATE TABLE `passwd` (
+ `user` varchar(255) NOT NULL default '',
+ `crypt` varchar(255) NOT NULL default '',
+ `manages` text NOT NULL,
+ `admin` tinyint(1) NOT NULL default '0',
+ PRIMARY KEY (`user`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+--
+-- Dumping data for table `passwd`
+--
+
+
+/*!40000 ALTER TABLE `passwd` DISABLE KEYS */;
+LOCK TABLES `passwd` WRITE;
+INSERT INTO `passwd` VALUES ('test','x4','',0);
+UNLOCK TABLES;
+/*!40000 ALTER TABLE `passwd` ENABLE KEYS */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
diff --git a/defaults.cfg.php b/defaults.cfg.php
new file mode 100644
index 0000000..3e42eea
--- /dev/null
+++ b/defaults.cfg.php
@@ -0,0 +1,24 @@
+
diff --git a/global.act.php b/global.act.php
new file mode 100644
index 0000000..6c4b540
--- /dev/null
+++ b/global.act.php
@@ -0,0 +1,20 @@
+getUserId();
+ $Name = $Login->getName();
+ $Email = $Login->getEmail();
+ $UL = $Login->getUL();
+}
+
+?>
diff --git a/global.done.php b/global.done.php
new file mode 100644
index 0000000..65c8248
--- /dev/null
+++ b/global.done.php
@@ -0,0 +1,9 @@
+';
+print_r($_SESSION);
+print_r($timings);
+
+?>
diff --git a/index.php b/index.php
index 1cdf976..6d810e0 100755
--- a/index.php
+++ b/index.php
@@ -7,45 +7,17 @@
- to support group sql "lockers"
*/
-if (file_exists('/mit/presbrey/web_tmp'))
- ini_set('session.save_path','/mit/presbrey/web_tmp');
-ini_set('display_errors', 1);
-ignore_user_abort(1);
-error_reporting(E_ALL);
-set_time_limit(0);
-session_start();
-if (isset($_GET['reset'])) { session_destroy(); session_start(); }
require_once('mitsql.lib.php');
-if (!isset($_SESSION['owner'])) {
- if (isset($_GET['owner'])) {
- $_SESSION['owner'] = $_GET['owner'];
- redirectLocal('/');
- }
- if (isset($_POST['owner'])) {
- $_SESSION['owner'] = $_POST['owner'];
- redirectLocal('/');
- }
- if (is_null(getSSLIdent())) {
- redirect('https://'.$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']);
- echo 'Your SSL certificate could not be verified, please authenticate manually below.
';
-// echo '';
- echo '(This will be fixed when I do group locker SQL support)
';
- } else {
- $_SESSION['owner'] = getSSLIdent();
- }
-}
-if (isset($_SESSION['realuser'])) {
- $realuser = $_SESSION['realuser'];
-} else $realuser = null;
+isLoggedIn() || redirect('login');
+
+redirect('main');
-ob_start();
-include('style.inc');
+exit;
if (isset($_SESSION['owner'])) {
$owner = $_SESSION['owner'];
$dbm = new DBManage($owner);
-} else exit;
?>SQL Databases:
MIT SQL Service Management v
Direct comments and bugs to: presbrey@mit.edu
diff --git a/lib/dbaccess.lib.php b/lib/dbaccess.lib.php
new file mode 100644
index 0000000..e106698
--- /dev/null
+++ b/lib/dbaccess.lib.php
@@ -0,0 +1,45 @@
+
diff --git a/lib/display.lib.php b/lib/display.lib.php
new file mode 100644
index 0000000..b1d6d2d
--- /dev/null
+++ b/lib/display.lib.php
@@ -0,0 +1,23 @@
+
diff --git a/lib/joe.lib.php b/lib/joe.lib.php
new file mode 100755
index 0000000..2484237
--- /dev/null
+++ b/lib/joe.lib.php
@@ -0,0 +1,203 @@
+ 0) {
+ $arr = array();
+ if (is_null($key)) {
+ while ($r = mysql_fetch_assoc($rs)) {
+ $arr[] = $r;
+ }
+ } elseif (is_numeric($key)) {
+ while ($r = mysql_fetch_row($rs)) {
+ $arr[$r[$key]] = $r;
+ }
+ } else {
+ while ($r = mysql_fetch_assoc($rs)) {
+ $arr[$r[$key]] = $r;
+ }
+ }
+ mysql_free_result($rs);
+ return $arr;
+ } else {
+ mysql_free_result($rs);
+ return array();
+ }
+}
+
+function printErrors($errArray) {
+ if (isset($errArray) && count($errArray)) {
+ echo '';
+ foreach($errArray as $err) {
+ echo '',$err,'
';
+ }
+ echo '
';
+ }
+}
+
+function buildSQLSet($fields, $values=null) {
+ $ex = array('NOW()','NULL');
+ $sql = 'SET';
+ $c = 0;
+ if (!is_null($values)) {
+ foreach($fields as $field) {
+ if ($c++) $sql .= ',';
+ $sql .= " `$field`='".mysql_escape_string(array_shift($values))."'";
+ }
+ } else {
+ foreach($fields as $field=>$value) {
+ if ($c++) $sql .= ',';
+ if (in_array($value,$ex)) {
+ $sql .= " `$field`= $value";
+ } else {
+ $sql .= " `$field`='".mysql_escape_string($value)."'";
+ }
+ }
+ }
+ return $sql;
+}
+
+function buildSQLInsert($array, $table=null) {
+ $ex = array('NOW()','NULL');
+ $sql = '(';
+ $c = 0;
+ foreach($array as $field=>$value) {
+ if ($c++) $sql .= ',';
+ $sql .= " `$field` ";
+ }
+ $sql .= ') VALUES (';
+ $c = 0;
+ foreach($array as $field=>$value) {
+ $v = mysql_escape_string($value);
+ if ($c++) $sql .= ',';
+ if (in_array($v, $ex))
+ $sql .= " $v ";
+ else
+ $sql .= " '$v' ";
+ }
+ $sql .= ')';
+ return (is_null($table)?$sql:('INSERT INTO `'.$table.'` '.$table));
+}
+
+function build_str($query_array) {
+ $query_string = array();
+ foreach ($query_array as $k => $v) {
+ $new = $k;
+ if (strlen($v))
+ $new .= '='.$v;
+ $query_string[] = $new;
+ }
+ return join('&', $query_string);
+}
+
+function newQS($key, $val=null) {
+ /*
+ parse_str($_SERVER['QUERY_STRING'], $arr);
+ $arr[$key] = $val;
+ return '?'.build_str($arr);
+ */
+ return newQSA(array($key=>$val));
+}
+
+function newQSA($array=array()) {
+ parse_str($_SERVER['QUERY_STRING'], $arr);
+ $s = count($arr);
+ foreach($array as $key=>$val) {
+ $arr[$key] = $val;
+ if (is_null($val))
+ unset($arr[$key]);
+ }
+ return (count($arr)||$s)?'?'.build_str($arr):'';
+}
+
+function formQSA($array=array()) {
+ if (!count($array)) $array = $_SERVER['QUERY_STRING'];
+ parse_str($array, $arr);
+ $text = '';
+ foreach($arr as $key=>$val) {
+ $text .= sprintf('', $key, $val);
+ }
+ return $text;
+}
+
+?>
diff --git a/lib/mitsql.lib.php b/lib/mitsql.lib.php
new file mode 100755
index 0000000..b275c30
--- /dev/null
+++ b/lib/mitsql.lib.php
@@ -0,0 +1,28 @@
+
diff --git a/lib/security.lib.php b/lib/security.lib.php
new file mode 100644
index 0000000..ee7847e
--- /dev/null
+++ b/lib/security.lib.php
@@ -0,0 +1,113 @@
+info = count($r)?array_shift($r):null;
+ }
+ function exists() {
+ return !is_null($this->info);
+ }
+ function isValid() {
+ return $this->isEnabled() && $this->getUL()>0;
+ }
+ function isEnabled() {
+ return $this->exists() && $this->info['bEnabled']==1;
+ }
+ function getUserId() {
+ return $this->exists() && $this->info['UserId'];
+ }
+ function getUsername() {
+ return $this->exists() && $this->info['Username'];
+ }
+ function getName() {
+ return $this->exists() && $this->info['Name'];
+ }
+ function getEmail() {
+ return $this->exists() && $this->info['Email'];
+ }
+ function getUL() {
+ return $this->exists() && $this->info['UL'];
+ }
+ function expire() {
+ $this->info = null;
+ }
+ function refresh() {
+ $this->Login($this->getUsername());
+ }
+ function update($name=null,$email=null) {
+ if (!$this->exists()) return;
+ $arr = array();
+ is_null($name) || $arr['Name'] = $name;
+ is_null($email) || $arr['Email'] = $email;
+ $sql = sprintf("UPDATE User %s WHERE UserId = '%s'",
+ buildSQLSet($arr), mysql_escape_string($this->getUserId()));
+ DBUpdate($sql);
+ }
+}
+
+
+function isLoggedIn($aLogin=null) {
+ if (is_null($aLogin)) {
+ global $Login;
+ $aLogin = $Login;
+ }
+ return !empty($aLogin) && is_a($aLogin, 'Login') && $aLogin->isValid();
+}
+
+function isSSL() {
+ return $_SERVER['SERVER_PORT'] == 443;
+}
+
+function getSSLCert() {
+ if (DEVEL && file_exists('.forceauth')) {
+ $fu = explode('|',file_get_contents('.forceauth'));
+ $name = $fu[0];
+ $email = $fu[1];
+ } else {
+ $name = isset($_SERVER['SSL_CLIENT_S_DN_CN'])?$_SERVER['SSL_CLIENT_S_DN_CN']:null;
+ $email = isset($_SERVER['SSL_CLIENT_S_DN_Email'])?$_SERVER['SSL_CLIENT_S_DN_Email']:null;
+ }
+ if (!is_null($email)) {
+ $user = explode('@',$email);
+ $user = $user[0];
+ return array('Username'=>$user, 'Name'=>$name, 'Email'=>$email);
+ } else {
+ return null;
+ }
+}
+
+## 302 REDIRECTS
+
+function redirect($target=NULL) {
+ $base = (is_null($target)||substr($target,0,1)=='?')?$_SERVER['REDIRECT_URL']:(dirname($_SERVER['REDIRECT_URL']).'/');
+ redirectFull(is_null($target)?$base:($base.$target));
+}
+function redirectFull($target) {
+ redirect2((isSSL()?'https://':'http://').$_SERVER['SERVER_NAME'].$target);
+}
+function redirect2($target) {
+ header('Location: '.$target);
+ exit;
+}
+
+## USER SCRIPTS
+
+function addUser($sslCredentials) {
+ global $_NEW_USER;
+ $arr = array_merge($sslCredentials, $_NEW_USER);
+ $sql = sprintf("INSERT INTO User %s",
+ buildSQLInsert($arr));
+ return DBInsert($sql);
+}
+
+?>
diff --git a/login.php b/login.php
new file mode 100644
index 0000000..fa91e4e
--- /dev/null
+++ b/login.php
@@ -0,0 +1,26 @@
+exists()) {
+ addUser($cred);
+ $Login->refresh();
+ }
+ } else {
+ $err[] = 'Your SSL certificate failed to identify you.';
+ }
+} elseif (isPost()) {
+} elseif (isSSL()) {
+ require('tpl/login_ssl.php');
+} else {
+}
+
+if (isLoggedIn())
+ redirect('main');
+
+?>
diff --git a/main.php b/main.php
new file mode 100644
index 0000000..e69de29
diff --git a/mitsql.cfg.php b/mitsql.cfg.php
new file mode 100755
index 0000000..ad086c0
--- /dev/null
+++ b/mitsql.cfg.php
@@ -0,0 +1,34 @@
+
diff --git a/style.inc b/mitsql.css
similarity index 100%
rename from style.inc
rename to mitsql.css
diff --git a/mitsql.lib.php b/mitsql.lib.php
deleted file mode 100755
index fbdba3c..0000000
--- a/mitsql.lib.php
+++ /dev/null
@@ -1,463 +0,0 @@
-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 ' | ';
- echo '';
- echo '';
- echo '';
- echo '';
-// echo '';
- echo '
';
- }
- function printOwner() {
- $obj_owner = $this->r_owner;
- echo '';
- $this->printOwnerHeader();
- echo 'status: | ';
- echo '',$this->getNumDBs(),' | ';
- echo '',sprintSize($this->getTotalSize()),' | ';
- echo '';
- echo 'limits: | ';
- echo '',$obj_owner['maxDB'],' | ';
- echo '',sprintSize($obj_owner['maxSize']),' | ';
- echo '',sprintTS($obj_owner['modified']),' | ';
-// echo '',sprintTS($obj_owner['created']),' | ';
- echo '
';
- echo '
';
- }
- function printOwnerDBs($showOwner = false) {
- $rows = $this->dbs;
- echo ' | ';
- echo '';
- echo '';
- echo '';
- echo '';
- echo '';
-// echo '';
- echo '
';
- foreach($rows as $name=>$r) {
- echo '';
- echo '',$name,' | ';
- echo '',$r['owner'],' | ';
- /* echo '',sprintSize($r['sizeMax']),' | ';*/
- echo '',sprintSize($r['sizeLast']),' | ';
- echo '',sprintSize($r['sizeNow']),' | ';
- echo '',sprintTS($r['modified']),' | ';
-// echo '',sprintTS($r['created']),' | ';
-
- echo '';
-
- echo '';
-
- echo '';
- echo '
';
- flush();
- }
- }
- function fix() {
- $fixed = false;
- foreach($this->dbs as $db) {
- if (!$db['registered']) {
- echo '',TEXT_FIXREG,$db['name'],'
';
- 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 ' | ';
- echo '';
- echo '';
- echo '';
- echo '';
- echo '';
- echo '';
- echo '
';
- foreach($rows as $name=>$r) {
- echo '';
- echo '',$name,' | ';
-// echo '',$r['owner'],' | ';
- echo '',$r['owner'],' | ';
- /* echo '',sprintSize($r['sizeMax']),' | ';*/
- echo '',sprintSize($r['sizeLast']),' | ';
- echo '',sprintSize($r['sizeNow']),' | ';
- echo '',sprintTS($r['modified']),' | ';
- echo '',sprintTS($r['created']),' | ';
-
- echo '';
- echo $r['exists']?'E':'';
- echo $r['granted']?'G':'';
- echo $r['registered']?'R':'';
- echo ' | ';
-
- echo '';
-
- echo '';
-
- echo '';
- echo '
';
- flush();
- }
- }
-}
diff --git a/test.php b/test.php
index 7eb69e2..bbea2a4 100755
--- a/test.php
+++ b/test.php
@@ -1,3 +1,20 @@
MIT SQL Service Management v
+Direct comments and bugs to: presbrey@mit.edu
+