From c5c37fac1ab6c56d4879d74808a6ae9f44384bda Mon Sep 17 00:00:00 2001
From: Konrad Rosenbaum <konrad@silmor.de>
Date: Tue, 20 Mar 2012 09:08:08 +0100
Subject: [PATCH] fix restore, make mysql case sensitive by default

---
 phpbase/db.php       |    7 +++++--
 phpbase/db_mysql.php |   19 ++++++++++++++-----
 2 files changed, 19 insertions(+), 7 deletions(-)

diff --git a/phpbase/db.php b/phpbase/db.php
index 879bb55..fc6d2c4 100644
--- a/phpbase/db.php
+++ b/phpbase/db.php
@@ -571,13 +571,15 @@ abstract class DbEngine
 		if(count($res)>0){
 			if($overwrite){
 				$b=$this->update($table,$data,$q);
-				print("update data: ".($b===false?"failed":"success")."<br>\n");
+				print("update data: ".($b===false?"failed":"success")."<br/>\n");
+				if($b===false)print("<font color=\"red\">Error: ".htmlentities($this->lastError())."</font><br/>\n");
 			}else{
 				print("ignoring existing row<br>\n");
 			}
 		}else{
 			$b=$this->insert($table,$data);
-			print("insert data: ".($b===false?"failed":"success")."<br>\n");
+			print("insert data: ".($b===false?"failed":"success")."<br/>\n");
+			if($b===false)print("<font color=\"red\">Error: ".htmlentities($this->lastError())."</font><br/>\n");
 		}
 	}
 	
@@ -614,6 +616,7 @@ abstract class DbEngine
 						print("Switching to table ".htmlspecialchars($table)."<br>\n");
 					break;
 				case "value":
+					if(!isset($cmd[3]))$cmd[3]='';
 					$data[$cmd[1]]=$this->unescapeBackup($cmd[2],$cmd[3]);
 					break;
 				case "insert":
diff --git a/phpbase/db_mysql.php b/phpbase/db_mysql.php
index e43e53f..a36a6a4 100644
--- a/phpbase/db_mysql.php
+++ b/phpbase/db_mysql.php
@@ -16,6 +16,7 @@ class MysqlEngine extends DbEngine
 	private $dbname="";
 	private $engine="InnoDB";
 	private $charset="utf8";
+	private $collate="utf8_bin";
 	private $intrans=false;
 	//used for table creation to work around mysql bugs
 	private $tableappend;
@@ -54,10 +55,14 @@ class MysqlEngine extends DbEngine
 		$this->engine=$e;
 	}
 	
-	/**set the default charset for tables and connections*/
-	public function setCharacterSet($c)
+	/**set the default charset and collation for tables and connections
+	\param cs the character set - like 'latin1' or 'utf8', this must be a charset supported by MySQL, the default is 'utf8'
+	\param col the collation to be used, the default is to use the corresponding binary collation (if you specify an empty string the default of MySQL will be used, which is a case independent comparison!)
+	*/
+	public function setCharacterSet($cs,$col="%_bin")
 	{
-		$this->charset=$c;
+		$this->charset=$cs;
+		$this->collate=str_replace('%',$cs,$col);
 	}
 	
 	public function tryConnect()
@@ -68,8 +73,11 @@ class MysqlEngine extends DbEngine
 			die("Unable to connect to database system. Giving up.");
 		//select Unicode or whatever charset is configured
 		$cs=$this->escapeString($this->charset);
-		if(mysqli_query($this->dbhdl,"SET NAMES ".$cs)===false)
-			die("Cannot set DB character set to ".$cs.", aborting.");
+		$col=$this->escapeString($this->collate);
+		$q="SET NAMES ".$cs;
+		if($this->collate!="")$q.=" COLLATE ".$col;
+		if(mysqli_query($this->dbhdl,$q)===false)
+			die("Cannot set DB character set to ".$cs." and collation to ".$col.", aborting.");
 		//make sure the DB is transaction safe
 		if(mysqli_query($this->dbhdl,"SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE")===false)
 			die("Cannot make this database transaction safe, aborting");
@@ -157,6 +165,7 @@ class MysqlEngine extends DbEngine
 	{
 		$this->tableappend="";
 		$sql=parent::sqlCreateTable($tn,$t)." engine=".$this->engine." default charset=".$this->charset;
+		if($this->collate!="")$sql.=" collate=".$this->collate;
 // 		print("<pre>\n$sql\n</pre>\n");
 		return $sql;
 	}
-- 
1.7.2.5