Initial PostgreSQL support

author: Robin Appelman <icewind1991@gmail.com> 2010-06-20 17:58:39 +0200
committer: Robin Appelman <icewind1991@gmail.com> 2010-06-20 17:58:39 +0200
commit: a9fd0c67b44ea56f1941b49ec5453f6bf82d9f19 (patch)
tree: b212e4ac3bdced11f70f63eb08f473a6c82b78fe
parent: 7503ad139b53de41e3567d723419e49166de574f (diff)
download: nextcloud-server-a9fd0c67b44ea56f1941b49ec5453f6bf82d9f19.tar.gz
nextcloud-server-a9fd0c67b44ea56f1941b49ec5453f6bf82d9f19.zip
6 files changed, 101 insertions, 53 deletions
diff --git a/db_structure.xml b/db_structure.xml
index 72c63761bc9..f5d0f24505e 100755
--- a/db_structure.xml
+++ b/db_structure.xml
@@ -304,18 +304,18 @@
 
    <field>
     <name>user_id</name>
-    <type>text</type>
+	<type>integer</type>
     <default></default>
     <notnull>true</notnull>
-    <length>64</length>
+	<length>4</length>
    </field>
 
    <field>
     <name>group_id</name>
-    <type>text</type>
+	<type>integer</type>
     <default></default>
     <notnull>true</notnull>
-    <length>64</length>
+	<length>4</length>
    </field>
 
   </declaration>
diff --git a/inc/lib_base.php b/inc/lib_base.php
index 5fc95b66aee..cfd9d4a567b 100755
--- a/inc/lib_base.php
+++ b/inc/lib_base.php
@@ -353,6 +353,14 @@ class OC_DB {
 					'hostspec' => $CONFIG_DBHOST,
 					'database' => $CONFIG_DBNAME,
 				);
+			}elseif($CONFIG_DBTYPE=='pgsql'){
+				$dsn = array(
+					'phptype'  => 'pgsql',
+					'username' => $CONFIG_DBUSER,
+					'password' => $CONFIG_DBPASSWORD,
+					'hostspec' => $CONFIG_DBHOST,
+					'database' => $CONFIG_DBNAME,
+				);
 			}
 			self::$DBConnection=&MDB2::factory($dsn,$options);
 			if (@PEAR::isError(self::$DBConnection)) {
@@ -378,6 +386,8 @@ class OC_DB {
 		OC_DB::connect();
 		if($CONFIG_DBTYPE=='sqlite'){//fix differences between sql versions
 			$cmd=str_replace('`','',$cmd);
+		}elseif($CONFIG_DBTYPE=='pgsql'){
+			$cmd=str_replace('`','"',$cmd);
 		}
 		$result=self::$DBConnection->query($cmd);
 		if (PEAR::isError($result)) {
@@ -396,7 +406,19 @@ class OC_DB {
    */
 	static function select($cmd){
 		OC_DB::connect();
-		return self::$DBConnection->queryAll($cmd);
+		global $CONFIG_DBTYPE;
+		if($CONFIG_DBTYPE=='sqlite'){//fix differences between sql versions
+			$cmd=str_replace('`','',$cmd);
+		}elseif($CONFIG_DBTYPE=='pgsql'){
+			$cmd=str_replace('`','"',$cmd);
+		}
+		$result=self::$DBConnection->queryAll($cmd);
+		if (PEAR::isError($result)) {
+			$entry='DB Error: "'.$result->getMessage().'"<br />';
+			$entry.='Offending command was: '.$cmd.'<br />';
+			die($entry);
+		}
+		return $result;
 	} 
 	
 	/**
diff --git a/inc/lib_config.php b/inc/lib_config.php
index 6ac83db951f..4110e77cf49 100755
--- a/inc/lib_config.php
+++ b/inc/lib_config.php
@@ -184,13 +184,13 @@ class OC_CONFIG{
 						//create/fill database
 						$CONFIG_DBTYPE=$dbtype;
 						$CONFIG_DBNAME=$_POST['dbname'];
-						if($dbtype=='mysql'){
+						if($dbtype!='sqlite'){
 							$CONFIG_DBHOST=$_POST['dbhost'];
 							$CONFIG_DBUSER=$_POST['dbuser'];
 							$CONFIG_DBPASSWORD=$_POST['dbpassword'];
 						}
 						try{
-							if(isset($_POST['createdatabase']) and $CONFIG_DBTYPE=='mysql'){
+							if(isset($_POST['createdatabase']) and $CONFIG_DBTYPE!='sqlite'){
 								self::createdatabase($_POST['dbadminuser'],$_POST['dbadminpwd']);
 							}
 						}catch(Exception $e){
@@ -241,7 +241,7 @@ class OC_CONFIG{
 					$config.='$CONFIG_DATEFORMAT=\''.$_POST['dateformat']."';\n";
 					$config.='$CONFIG_DBTYPE=\''.$dbtype."';\n";
 					$config.='$CONFIG_DBNAME=\''.$_POST['dbname']."';\n";
-					if($dbtype=='mysql'){
+					if($dbtype!='sqlite'){
 						$config.='$CONFIG_DBHOST=\''.$_POST['dbhost']."';\n";
 						$config.='$CONFIG_DBUSER=\''.$_POST['dbuser']."';\n";
 						$config.='$CONFIG_DBPASSWORD=\''.$_POST['dbpassword']."';\n";
@@ -290,34 +290,49 @@ class OC_CONFIG{
 		global $CONFIG_DBNAME;
 		global $CONFIG_DBUSER;
 		global $CONFIG_DBPWD;
+		global $CONFIG_DBTYPE;
 		//we cant user OC_BD functions here because we need to connect as the administrative user.
-		$connection = @new mysqli($CONFIG_DBHOST, $adminUser, $adminPwd);
-		if (mysqli_connect_errno()) {
-			@ob_end_clean();
-			echo('<html><head></head><body bgcolor="#F0F0F0"><br /><br /><center><b>can not connect to database as administrative user.</center></body></html>');
-			exit();
-		}
-		$query="SELECT user FROM mysql.user WHERE user='{$_POST['dbuser']}';";
-		$result = @$connection->query($query);
-		if (!$result) {
-			$entry='DB Error: "'.$connection->error.'"<br />';
-			$entry.='Offending command was: '.$query.'<br />';
-			echo($entry);
-		}
-		if($result->num_rows==0){
-			$query="CREATE USER '{$_POST['dbuser']}' IDENTIFIED BY  '{$_POST['dbpassword']}';";
-		}else{
-			$query='';
-		}
-		$query.="CREATE DATABASE IF NOT EXISTS  `{$_POST['dbname']}`;
-		GRANT ALL PRIVILEGES ON  `{$_POST['dbname']}` . * TO  '{$_POST['dbuser']}';";
-		$result = @$connection->multi_query($query);
-		if (!$result) {
-			$entry='DB Error: "'.$connection->error.'"<br />';
-			$entry.='Offending command was: '.$query.'<br />';
-			echo($entry);
+		if($CONFIG_DBTYPE=='mysql'){
+			$connection = @new mysqli($CONFIG_DBHOST, $adminUser, $adminPwd);
+			if (mysqli_connect_errno()) {
+				@ob_end_clean();
+				echo('<html><head></head><body bgcolor="#F0F0F0"><br /><br /><center><b>can not connect to database as administrative user.</center></body></html>');
+				exit();
+			}
+			$query="SELECT user FROM mysql.user WHERE user='{$_POST['dbuser']}';";
+			$result = @$connection->query($query);
+			if (!$result) {
+				$entry='DB Error: "'.$connection->error.'"<br />';
+				$entry.='Offending command was: '.$query.'<br />';
+				echo($entry);
+			}
+			if($result->num_rows==0){
+				$query="CREATE USER '{$_POST['dbuser']}' IDENTIFIED BY  '{$_POST['dbpassword']}';";
+			}else{
+				$query='';
+			}
+			$query.="CREATE DATABASE IF NOT EXISTS  `{$_POST['dbname']}`;";
+			$query.="GRANT ALL PRIVILEGES ON  `{$_POST['dbname']}` . * TO  '{$_POST['dbuser']}';";
+			$result = @$connection->multi_query($query);
+			if (!$result) {
+				$entry='DB Error: "'.$connection->error.'"<br />';
+				$entry.='Offending command was: '.$query.'<br />';
+				echo($entry);
+			}
+			$connection->close();
+		}elseif($CONFIG_DBTYPE=='pgsql'){
+			$connection = pg_connect("user='$adminUser' host='$CONFIG_DBHOST' password='$adminPwd'");
+			$query="CREATE USER {$_POST['dbuser']} WITH PASSWORD '{$_POST['dbpassword']}' CREATEDB;";
+			$result = pg_exec($connection, $query);
+			$query="select count(*) from pg_catalog.pg_database where datname = '{$_POST['dbname']}';";
+			$result = pg_exec($connection, $query);
+			if(pg_result($result,0,0)==0){
+				$query="CREATE DATABASE {$_POST['dbname']};";
+				$result = pg_exec($connection, $query);
+				$query="ALTER DATABASE {$_POST['dbname']} OWNER TO {$_POST['dbuser']};";
+				$result = pg_exec($connection, $query);
+			}
 		}
-		$connection->close();
 	}
 }
 ?>
diff --git a/inc/lib_log.php b/inc/lib_log.php
index 3e0d3d16092..f002733acd0 100755
--- a/inc/lib_log.php
+++ b/inc/lib_log.php
@@ -48,7 +48,7 @@ class OC_LOG {
    * @param message $message
    */
   public static function event($user,$type,$message){
-    $result = OC_DB::query('insert into log (timestamp,user,type,message) values ("'.time().'","'.addslashes($user).'","'.addslashes($type).'","'.addslashes($message).'")');
+    $result = OC_DB::query('INSERT INTO `log` (`timestamp`,`user`,`type`,`message`) VALUES ('.time().',\''.addslashes($user).'\','.addslashes($type).',\''.addslashes($message).'\');');
     OC_DB::free_result($result);
   }
 
@@ -62,10 +62,10 @@ class OC_LOG {
     echo('<div class="center"><table cellpadding="6" cellspacing="0" border="0" class="log">');
 	
 	if(OC_USER::ingroup($_SESSION['username_clean'],'admin')){
-		$result = OC_DB::select('select timestamp,user,type,message from log order by timestamp desc limit 20');
+		$result = OC_DB::select('select `timestamp`,`user`,`type`,`message` from log order by timestamp desc limit 20');
 	}else{
 		$user=$_SESSION['username_clean'];
-		$result = OC_DB::select('select timestamp,user,type,message from log where user=\''.$user.'\' order by timestamp desc limit 20');
+		$result = OC_DB::select('select `timestamp`,`user`,`type`,`message` from log where user=\''.$user.'\' order by timestamp desc limit 20');
 	}
     foreach($result as $entry){
       echo('<tr class="browserline">');
diff --git a/inc/lib_user.php b/inc/lib_user.php
index e5d994dd02f..99909a33213 100755
--- a/inc/lib_user.php
+++ b/inc/lib_user.php
@@ -58,11 +58,11 @@ class OC_USER {
 		if(OC_USER::getuserid($username)!=0){
 			return false;
 		}else{
-			$password=sha1($password);
 			$usernameclean=strtolower($username);
+			$password=sha1($password);
 			$username=OC_DB::escape($username);
 			$usernameclean=OC_DB::escape($usernameclean);
-			$query="INSERT INTO  `users` (`user_id` ,`user_name` ,`user_name_clean` ,`user_password`) VALUES (NULL ,  '$username',  '$usernameclean',  '$password')";
+			$query="INSERT INTO  `users` (`user_name` ,`user_name_clean` ,`user_password`) VALUES ('$username',  '$usernameclean',  '$password')";
 			$result=OC_DB::query($query);
 			return ($result)?true:false;
 		}
@@ -118,7 +118,7 @@ class OC_USER {
 	public static function creategroup($groupname){
 		if(OC_USER::getgroupid($groupname)==0){
 			$groupname=OC_DB::escape($groupname);
-			$query="INSERT INTO  `groups` (`group_id` ,`group_name`) VALUES (NULL ,  '$groupname')";
+			$query="INSERT INTO  `groups` (`group_name`) VALUES ('$groupname')";
 			$result=OC_DB::query($query);
 			return ($result)?true:false;
 		}else{
@@ -132,7 +132,6 @@ class OC_USER {
 	*/
 	public static function getuserid($username){
 		$usernameclean=strtolower($username);
-		$username=OC_DB::escape($username);
 		$usernameclean=OC_DB::escape($usernameclean);
 		$query="SELECT user_id FROM  users WHERE user_name_clean = '$usernameclean'";
 		$result=OC_DB::select($query);
@@ -187,7 +186,7 @@ class OC_USER {
 		$userid=OC_USER::getuserid($username);
 		$groupid=OC_USER::getgroupid($groupname);
 		if($groupid>0 and $userid>0){
-			$query="SELECT user_group_id FROM  user_group WHERE  group_id = $groupid  AND user_id = $userid LIMIT 1";
+			$query="SELECT * FROM  user_group WHERE group_id = '$groupid'  AND user_id = '$userid';";
 			$result=OC_DB::select($query);
 			if(isset($result[0]) && isset($result[0]['user_group_id'])){
 				return true;
@@ -208,7 +207,7 @@ class OC_USER {
 			$userid=OC_USER::getuserid($username);
 			$groupid=OC_USER::getgroupid($groupname);
 			if($groupid!=0 and $userid!=0){
-				$query="INSERT INTO `user_group` (`user_group_id` ,`user_id` ,`group_id`) VALUES (NULL ,  '$userid',  '$groupid');";
+				$query="INSERT INTO `user_group` (`user_id` ,`group_id`) VALUES ('$userid',  '$groupid');";
 				$result=OC_DB::query($query);
 				if($result){
 					return true;
diff --git a/inc/templates/adminform.php b/inc/templates/adminform.php
index c22c78768b0..c94334466af 100755
--- a/inc/templates/adminform.php
+++ b/inc/templates/adminform.php
@@ -37,7 +37,7 @@ function dbtypechange(){
                 element.style.display='none';
             }
         }
-    }else if(type=='mysql'){
+    }else if(type=='mysql' || type=='pgsql'){
         for(i in inputs){
             id=inputs[i];
             element=document.getElementById(id);
@@ -80,24 +80,36 @@ if($FIRSTRUN){?>
 <select id='dbtype' name="dbtype" onchange='dbtypechange()'>
 <?php
 global $CONFIG_DBTYPE;
-$dbtypes=array();
 if($CONFIG_DBTYPE=='sqlite'){
 	if(is_callable('sqlite_open')){
-		$dbtypes[]='SQLite';
+		echo "<option value='sqlite'>SQLite</option>";
 	}
 	if(is_callable('mysql_connect')){
-		$dbtypes[]='MySQL';
+		echo "<option value='mysql'>MySQL</option>";
 	}
-}else{
+	if(is_callable('pg_connect')){
+		echo "<option value='pgsql'>PostgreSQL</option>";
+	}
+}elseif($CONFIG_DBTYPE=='mysql'){
 	if(is_callable('mysql_connect')){
-		$dbtypes[]='MySQL';
+		echo "<option value='mysql'>MySQL</option>";
 	}
 	if(is_callable('sqlite_open')){
-		$dbtypes[]='SQLite';
+		echo "<option value='sqlite'>SQLite</option>";
+	}
+	if(is_callable('pg_connect')){
+		echo "<option value='pgsql'>PostgreSQL</option>";
+	}
+}elseif($CONFIG_DBTYPE=='pgsql'){
+	if(is_callable('pg_connect')){
+		echo "<option value='pgsql'>PostgreSQL</option>";
+	}
+	if(is_callable('mysql_connect')){
+		echo "<option value='mysql'>MySQL</option>";
+	}
+	if(is_callable('sqlite_open')){
+		echo "<option value='sqlite'>SQLite</option>";
 	}
-}
-foreach($dbtypes as $dbtype){
-	echo "<option value='".strtolower($dbtype)."'>$dbtype</option>";
 }
 ?>
 </select>
author	Robin Appelman <icewind1991@gmail.com>	2010-06-20 17:58:39 +0200
committer	Robin Appelman <icewind1991@gmail.com>	2010-06-20 17:58:39 +0200
commit	a9fd0c67b44ea56f1941b49ec5453f6bf82d9f19 (patch)
tree	b212e4ac3bdced11f70f63eb08f473a6c82b78fe
parent	7503ad139b53de41e3567d723419e49166de574f (diff)
download	nextcloud-server-a9fd0c67b44ea56f1941b49ec5453f6bf82d9f19.tar.gz nextcloud-server-a9fd0c67b44ea56f1941b49ec5453f6bf82d9f19.zip