Location: PHPKode > projects > Content*Builder > contentbuilder/lib/CB_DBTable.class.php
<?php

class DBTable
{
/**
  * Name der Datenbank-Tabelle
  *
  * 
  *
  */
  var $tableName;

  /**
  * Datei, welche eine Mysql-Tabellenstruktur enthält 
  *
  * Unterstütztes Format: 
  * Output von command 'mysqlshow ...' bzw. mysql-Select "show fields from <table>" bzw. "show index from table"
  */
  var $tFile;
  
  /**
  * Datenobjekt, welches eine vergleichbare Tabellenstruktur enthält
  *
  * Format:
  * tStructure["columns"][$colname][$propertyname]=Eigenschaft
  * bzw.
  * tStructure["index"][$indexname][$propertyname]=Eigenschaft
  */
  var $tStructure = array();

  /**
  * Zeiger-Variable auf eine gültige DB-Connection
  *
  * DB-Connection muss vom aufrufenden Skript implementiert werden.
  */
  var $dbConn;

function DBTable($tableName="") {
	if ($tableName) {
		$res=mysql_query("show tables like '".$tableName."'");
		if (($row=mysql_fetch_row($res))&&($row[0]==$tableName)){
        	$this->tableName = $tableName;
        }
    }
	if (! $this->tableName) return false;

	if (! defined("TEMPTABLE")) define ("TEMPTABLE","cbtemp");
}

/**
* Create Table Statement $sql nehmen und in eine Datenstruktur $this->tStructure parsen ...
* Wichtig: nur Standard-Formatierung nach Art von "SHOW CREATE TABLE $tableName"
* bzw. mysql_dump
* wird erkannt (very important: Zeilenumbrüche sind NICHT optional ...),
* allerdings wird jedwedes "drumherum" herausgeworfen
*
* @param $sql - SQL-Statement, mit dem man eine Tabelle erzeugen kann ...
* @param $useTempTable - soll eine temporaere Tabelle fuer die Erzeugung einer
* "sauberen" und sicher plattformspezifischen Datenstruktur angelegt werden?
* !!! iin diesem Fall muss $sql mit einem ";" abschliessen !!!
* (default: false, haeufig wird aber für plattformspezifische Unterscheide "true"
* benoetigt, wenn SQL-Code von einer Textdatei stammt)
*
* @return false | erkanntes SQL-Create sauber formatiert ...
* @return member-var $this->tStructure wird mit den erkannten Strukturdaten "befuellt"
*/
function parse_CreateTable_statement($sql,$useTempTable=false){
	//$data=array();

// falls gewuenscht, erzeugen wir aus dem vorliegenden Dump eine temporaere Tabelle ..
	if ($useTempTable){
		$sqlTmp = str_replace("\n","",$sql);
		
		if (preg_match("/create table\s+(\S+)\s+\(.+?;/mi",$sqlTmp,$matches)){
			$tmpTableName = TEMPTABLE . "_" . $matches[1];
			$sqlTmp = str_replace( $matches[1] , $tmpTableName , $matches[0]);
			if (mysql_query($sqlTmp)){
				$showSql = "SHOW CREATE TABLE ".$tmpTableName;
				if ($res = mysql_query($showSql)){
					$row=mysql_fetch_array($res);
					if ($row["Create Table"]){
						$sql = str_replace(TEMPTABLE . "_" . TEMPTABLE . "_",TEMPTABLE . "_",$row["Create Table"]);
						//croak($sql);
					}
				}
				mysql_query("DROP TABLE ".$tmpTableName);
			}
			if (mysql_errno()){
				croak("Mysql-Error ".mysql_errno().": ".mysql_error());
				croak("SQL=".$matches[0]);
				return;
			}
		}else{
			croak("`Parse for tablename´ error .. exiting ...");
		}
	}

	$sqlCreate="";
	$arr=preg_split("/[\n\r]+/",$sql);
	$endCreate = false;
	foreach ($arr as $lineOrig){
		$line=trim($lineOrig," \t\r,");
		debug(7, "          line: [".str_replace("\n","<br>\n",$line)."]\n");

		if (! $tname){
			if (preg_match("/^\s*create\s+table\s+(\S+)\s+\(\s*$/i",$line,$matches)) {
    			$tname = $matches[1];
    			$sqlCreate=$lineOrig."\n";
    			debug(5, "        got table name `$tname'\n");
   			}else {
    			//croak ("couldn't figure out table name");
    			continue;
  			}
  			continue;
		}

		if (! $endCreate){  // Parsen des Create-Statements
			if (preg_match("/^PRIMARY\s+KEY\s+(.+)$/i",$line,$matches)) {
				if (! $this->tStructure["primary_key"]){
					$this->tStructure["primary_key"]=preg_replace("/[´`]/","",$matches[1]);
					$sqlCreate.=$lineOrig."\n";
					debug(5, "        got primary key `".$this->tStructure["primary_key"]."'\n");
				}else{
					croak("two primary keys in table `$tname': `".$this->tStructure["primary_key"]."', `");
				}
			} else if (preg_match("/^(KEY|UNIQUE(?: KEY)?)\s+(\S+?)\s*\((.*)\)$/i",$line,$matches)) {
				$key=preg_replace("/[´`]/","",$matches[2]);
				if ((! $this->tStructure["indices"][$key])&&(! $this->tStructure["unique_index"][$key])){
					//$this->tStructure["indices"][$key]=$matches[3];
					if (preg_match("/unique/i",$matches[1])){
						$this->tStructure["unique_index"][$key]=$matches[3];
						debug(6, " got unique index key `$key': (".$matches[3].")\n");
					}else{
						$this->tStructure["indices"][$key]=$matches[3];
					}
					$sqlCreate.=$lineOrig."\n";
					debug(6, " got index key `$key': (".$matches[3].")\n");
				}else{
					croak ("index `$key' duplicated in table `$tname'\n");
				}
			}else if (preg_match("/^(FULLTEXT(?: KEY|INDEX)?)\s+(\S+?)\s*\((.*)\)$/i",$line,$matches)) {
				$key=preg_replace("/[´`]/","",$matches[2]);
				if (! $this->tStructure["fulltext"][$key]){
					$this->tStructure["fulltext"][$key]=$matches[3];
					$sqlCreate.=$lineOrig."\n";
					debug(6, " got fulltext index key `$key': (".$matches[3].")\n");
				}else{
					croak ("fulltext index `$key' duplicated in table `$tname'\n");
				}
			}else if (preg_match("/^\)\s*(.*?)$/",$line,$matches)) {
				$line=rtrim($matches[1],"\x00..\x1F;");
				$sqlCreate.=$lineOrig."\n";
				if (preg_match("/TYPE=(\S+)\s*/i",$line,$matches)){
					$this->tStructure["type"]=$matches[1];
					debug(6, " got table TYPE `".$this->tStructure["type"]."'\n");
				}
				if (preg_match("/COMMENT=(\S+)\s*/i",$line,$matches)){
					$this->tStructure["comment"]=preg_replace("/['\"]/","",$matches[1]);
					debug(6, " got table COMMENT `".$this->tStructure["comment"]."'\n");
				}
				//return rtrim($sqlCreate,"\x00..\x1F;");
				// go forward through last lines for conditionally existing INSERT-Statements ...
				$endCreate=true;
			}else if (preg_match("/^(\S+)\s*(.*)/",$line,$matches)) {
				$field=preg_replace("/[´`]/","",$matches[1]);
				$def=$matches[2];
				if (! $this->tStructure["fields"][$field]){
					$this->tStructure["fields"][$field]=$def;
					$sqlCreate.=$lineOrig."\n";
					debug(6, "          got field def `$field': $def\n");
				}else{
					croak ("definition for field `$field' duplicated in table `$name'\n");
				}
			}else{
				croak ("unparsable line in definition for table `$name':\n$line");
			}
		}else{ // Parsen des Rests nach dem Create-Statement.. im Prinzip nach Insert-Statements ...
		    // INSERT INTO cb04_mm_folder VALUES ('General', 2, 0, NULL, 'system', 'protected', 10021);
		    trigger_error("Weiterparsen ...");
		    if (preg_match("/INSERT\s+INTO\s+/i",$line)){
		        if (preg_match("/INSERT\s+INTO\s+(\S+)\s+VALUES\s*\((.*)\)\s*;/i",$line,$matches)){
		    		$this->tStructure["insert"][] = $matches[2];
		    		trigger_error("\$matches[2]='".$matches[2]."'");
		    	}else if (preg_match("/INSERT\s+INTO\s+(\S+)\s+\((.*)\)\s*VALUES\s*\((.*)\)\s*;/i",$line,$matches)){
			        $this->tStructure["insert"][] = $matches[3];
			    }else{
			    	croak("Could not parse INSERT Statement '$line'");
			    }
			}else{
				croak("Could not parse additional line '$line'");
			}
		}
	}
	
	if (! $tname){
		croak ("Could not parse `".$sql."´\n");
		return false;
	}
	return rtrim($sqlCreate,"\x00..\x1F;");
}

function dropTable($tblName){
	if ($tblName){
		if (mysql_query("DROP TABLE IF EXISTS ".$tblName)){
			return true;
		}
	}
	return false;
}

function print_MysqlQuery_as_HTMLTable($sql){
	echo "<table>\n";

	$res=mysql_query($sql);
	if (mysql_errno()){
		echo "<tr><td>". mysql_errno() . ": " . mysql_error() . "</td></tr>\n";
	}

	while ($row=mysql_fetch_array($res,MYSQL_ASSOC)){
		if (! $tableHead){
			echo "<tr>";
			foreach ($row as $k => $v){
				echo "<th>".$k."</th>";
			}
			echo "</tr>\n";
			$tableHead=true;
		}
		echo "<tr>";
		foreach ($row as $k => $v){
			echo "<td>". str_replace("\n","<br>\n",$v) ."</td>";
		}
		echo "</tr>\n";
	}

	echo "</table>\n";
}

function print_CSVfile_as_HTMLTable($fname){
	if (is_readable($fname)){
		echo "<table>";
		$arr=file($fname);
		foreach ($arr as $line){
			$tmpArr=explode("\t",$line);
			if (($tmpArr[0]=="Field")||($tmpArr[0]=="Table")){
				echo "<tr><th>".implode("</th><th>",$tmpArr)."</th></tr>\n";
				$tableHead=true;
			}else{
				echo "<tr><td>".implode("</td><td>",$tmpArr)."</td></tr>\n";
			}
		}
		return true;
	}else{
		echo "Could not read $fname <br>\n";
		return false;
	}
}

/**
* function creates SQL-statements, useable for Syncing of SQL-Structure
* Wished Result: Structure of $this->tableName is updated based on $otherTStructure ...
*
* @param 1: tStructure array of another DBTable object
* @param implicit: $this->tStructure (will be created if not already filled
*				by function parse_CreateTable_statement("SHOW CREATE TABLE $this->tableName")
*
* @return array of useful ALTER and ADD SQL statements
*/
function create_tablesync_sql($otherTStructure,$includeInserts=false) {
	if (! count($otherTStructure)>0 ) return false;
	// fill $this->tStructure if not already set
	if (! $this->tStructure) {
		if (! $this->tableName) { return false; }
		if ($res=mysql_query("show create table ".$this->tableName)){
			$row=mysql_fetch_array($res);
			if ($row["Create Table"]){
				//croak("\$row[\"Create Table\"]=".$row["Create Table"]);
				$this->parse_CreateTable_statement($row["Create Table"]);
			}
		}
		if (! count($this->tStructure)>0) { return false; }
	}
	
	// now compare and return sql statements
	$tmpArr1 = $this->array_diff_keys($otherTStructure,$this->tStructure);
	//print_r($tmpArr1);
	// handle table fields
	if ($tmpArr1["fields"]){
		foreach ($tmpArr1["fields"] as $k => $v){
			if ($this->tStructure["fields"][$k]){
				$arrErg[]="ALTER TABLE ".$this->tableName." CHANGE ".$k." ".$k." ".$v;
			}else{
				$arrErg[]="ALTER TABLE ".$this->tableName." ADD ".$k." ".$v;
			}
		}
	}
// handle primary key ...
	if ($tmpArr1["primary_key"]){
		$arrErg[]="ALTER TABLE ".$this->tableName." DROP PRIMARY KEY";
		if (preg_match("/(.+?)/", $tmpArr1["primary_key"]) ) {
			$arrErg[]="ALTER TABLE ".$this->tableName." ADD PRIMARY KEY ".$tmpArr1["primary_key"];
		}else {
			$arrErg[]="ALTER TABLE ".$this->tableName." ADD PRIMARY KEY (".$tmpArr1["primary_key"].")";
		}
	}
// normale indices
	if ($tmpArr1["indices"]){
		foreach ($tmpArr1["indices"] as $k => $v){
			if (($this->tStructure["indices"][$k])||($this->tStructure["unique_index"][$k])||($this->tStructure["fulltext"][$k])){
				$arrErg[]="ALTER TABLE ".$this->tableName." DROP INDEX ".$k;
			}
			$arrErg[]="ALTER TABLE ".$this->tableName." ADD INDEX ".$k." (".$v.")";
		}
	}
// handle Unique Indizes ....
	if ($tmpArr1["unique_index"]){
		foreach ($tmpArr1["unique_index"] as $k => $v){
			if (($this->tStructure["indices"][$k])||($this->tStructure["unique_index"][$k])||($this->tStructure["fulltext"][$k])){
				$arrErg[]="ALTER TABLE ".$this->tableName." DROP INDEX ".$k;
			}
			$arrErg[]="ALTER TABLE ".$this->tableName." ADD UNIQUE INDEX ".$k." (".$v.")";
		}
	}
// handle fulltext declarations ...
	if ($tmpArr1["fulltext"]){
		foreach ($tmpArr1["fulltext"] as $k => $v){
			if (($this->tStructure["indices"][$k])||($this->tStructure["unique_index"][$k])||($this->tStructure["fulltext"][$k])){
				$arrErg[]="ALTER TABLE ".$this->tableName." DROP INDEX ".$k;
			}
			$arrErg[]="ALTER TABLE ".$this->tableName." ADD FULLTEXT INDEX ".$k." (".$v.")";
		}
	}
// handle comment ...
	if ($tmpArr1["comment"]){
		$arrErg[]="ALTER TABLE ".$this->tableName." COMMENT='".$tmpArr1["comment"]."'";
	}
// handle table type ...
	if ($tmpArr1["type"]){
		//$arrErg[]=array();
		//croak("All update definitions for table `".$this->tableName."´ cleared because of incompatible table type ".$tmpArr1["type"]
		//	." in Update-SQL ...");
		croak("false table type ".$tmpArr1["type"]. " for table `".$this->tableName."´.");
	}
// handle comment ...
	if ($includeInserts && $tmpArr1["insert"]){
		$arrErg[]="INSERT INTO ".$this->tableName." VALUES(".$tmpArr1["insert"].")";
	}


	return $arrErg;
}

 /**
 * Für den Vergleich der Mysql-Datenstrukturen ...
 *
 *
 *
 * @return array gibt Differenzen zweier Arrays als Array zurueck
 * @param otherArray = mit $this->tStructure zu vergleichendes Fremdarray
 * (aus anderem Objekt ...).
 */
function array_diff_keys($otherArray,$thisArray=array()) {
	if (! $thisArray) $thisArray = $this->tStructure;
	/* if (func_num_args() > 2) {
		$args = func_get_args();
		array_shift($args);
		array_shift($args);
		foreach ($args as $arg) {
			$sub = array_merge($sub, $arg);
		}
	} */
	if ((count($otherArray)>0) && (count($thisArray)>0)){
		foreach ($otherArray as $ak => $av) {
			/* foreach ($otherArray as $sk => $sv) {
				if ($ak == $sk && $av == $sv) unset($array[$ak]);
			} */
			if (is_array($otherArray[$ak])){
				//trigger_error("aktArr $ak =".implode(":",$otherArray[$ak]));
				$tmpArr=$this->array_diff_keys($otherArray[$ak],$thisArray[$ak]);
				if (count($tmpArr)>0){
					$otherArray[$ak]=$tmpArr;
				}else{
					unset($otherArray[$ak]);
				}
			}else{
				//trigger_error($thisArray[$ak]." ? ".$otherArray[$ak]);
				if ($thisArray[$ak] == $otherArray[$ak]) {
					unset($otherArray[$ak]);
				}
			}
		}
	}else{
		return false;
	}
	return $otherArray;
}

	
}


?>
Return current item: Content*Builder