Location: PHPKode > projects > Content*Builder > contentbuilder/system/setup/update_table_structure.php
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>
	<link rel="stylesheet" href="templates//style.css" type="text/css">
    <title>Content*Builder Setup / Table structure update</title>
</head>
<body>
	<table cellpadding="3" cellspacing="0" width="800" align="center" border="0" class="tblBorder" style="margin-top:10px;">
		<tr valign="top" class="tbl_head_style">
			<td class="tdBorder" colspan="2"><b>Content*Builder Setup / Table structure update</b></td>
		</tr>
<?php
	$debug=0;
	// Sollen im Textfile gefundene Insert-Statements ausgefuehrt werden?
	if (!$doInserts && $_GET["doInserts"]){
		$doInserts=true;
	}

	// Gegenprobe: was ist in der aktiven Datenbank, aber nicht in der Dumpdatei?
	// Idee: damit Joerg / FoG nix vergisst ;-) , sollte im Normalfall sein "false"
	$right_hand_check = true;
	
	
	if ($_POST["Update"] == "Update"){
		$doUpdate=true;
	}else{
		$doUpdate=false;
	}
	$rel="../../";
	require_once($rel."root.inc");
	require_once($rel.$cbDirInclude."db.php");
 	require_once($rel.$cbDirLib."errorhandler.inc.php");

if (empty($db)){
	croak("no database connection found in ".$rel.$cbDirInclude."db.php");
	croak("exiting ...");
	exit;
}

	include_once($rel.$cbDirLib."CB_DBTable.class.php");
	include_once($rel.$cbDirLib."CB_DBTableCollection.class.php");

	if (! defined("TABLE")) define("TABLE","cms");
	define("TEMPTABLE","cbtemp");
	// welches File soll fuer den Vergleich gelesen werden?
	$fName="cb_dump.sql";

######## End init section ... ##############

//echo "<hr>Starting file parsing with $fName ..<br>";

// Einlesen des Dump-Files und "Zerhacken" in die einzelnen Tabellen-Definitionen ...
	$tColl = new DBTableCollection;

	if ($tColl->parseFromFile($fName,true)){
		$tmpTables = $tColl->tSQLtmp;
	}
	//echo "<hr>tmpTables:<p>";
	//print_r($tmpTables);
	//print_r($tColl->tSQLInserts);


//echo "<hr>";
//echo 'print_MysqlQuery_as_HTMLTable("show create table '.$tName.'");<br>';
//print_MysqlQuery_as_HTMLTable("show create table ".$tName);

// form anlegen fuer das Update der Tabellen ...
echo "<form action=\"".$PHP_SELF."?".$_SERVER["QUERY_STRING"]."\" method=\"POST\">";
$counter=0;  // count found differences, create list index for HTML form ...
// mit den aus dem Dump-File extrahierten Tabellen-Definitionen führen wir jetzt
// ein rekursives Diff-Checking durch ...
foreach ($tmpTables as $t => $s){
	// Ermitteln des Namens einer existierenden CB-Tabelle
	$tableName = str_replace(TEMPTABLE, TABLE, $t);
	echo '<tr valign="top" class="tbl_head_style">
			<td class="tdBorder" colspan="2"><b>'.$tableName.'</b>('.$t.')</td></tr>'."\n";
	//echo "<hr>Compare <font color=blue>$tableName</font> ... ";
	// Anlegen des Objekts fuer die existierende / zu bearbeitende Tabelle
	//croak($tableName);
	$tCreate = new DBTable($tableName);
	$sqlArr = array();
	if ($tCreate->tableName){
		// Anlegen eines Objekts fuer die temporaere Tabelle / neue Tabellenstruktur
		$tmpCreate = new DBTable();
		// Parsen der neuen Struktur in das Datenobjekt
		$tmpCreate->parse_CreateTable_statement($tmpTables[$t]);
		// Rueckgabe der neuen Struktur in Vorbereitung des Vergleichs ...
		$tmpData = $tmpCreate->tStructure;
		//echo "############ now goto part 2 ###############<p>";
		// der Vergleich wird auf das existierende Datenobjekt angewendet ...
		// .. und damit eine Liste von SQL-Updates fuer die Tabellenstruktur erzeugt
		$sqlArr = $tCreate->create_tablesync_sql($tmpData,true);
		//print_r($sqlArr);
		// Gegenprobe: welche Tabellenfelder gibt es in der "aktiven" Datenbank, die im
		// SQL-Dump nicht enthalten sind?
		if ($right_hand_check){
			$tData = $tCreate->tStructure;
  			$sqlArr2 = $tmpCreate->create_tablesync_sql($tData);
  		}

	}else{
		//croak("++++++++ Found new table ".$tableName." ++++++++++ :");
		echo '<tr><td colspan=2 class="tdBorder" >found new table '.$tableName.' with '.$tmpTables[$t].'</td></tr>';
		$sqlArr[] = str_replace(TEMPTABLE, TABLE,$tmpTables[$t]);
	}
	if ($sqlArr){
		//croak("++++++++ Changes found for table ".$tableName." ++++++++++ :");
		foreach($sqlArr as $v){
			$counter++;
			//croak($v);
			echo '<tr class="tbl_style"><td class="tdBorder">';
			if ($doUpdate){
				if (stripslashes(stripslashes($_POST["dbUpdate"][$counter])) == $v){
					//echo "Perform Update: ".$v."<br>\n";
					echo "<center><font color=green>!</font></center></td>";
					echo '<td class="tdBorder">Perform '.$v."<br>\n";
					if (mysql_query($v)){
						echo "ok";
					}else{
						echo "Error ".mysql_errno().": ".mysql_error();
					}
					echo '</td>';
				}else{
					//croak($v." not updated ...");
					echo "<center><font color=red>!</font></center></td>";
					echo '<td class="tdBorder">not updated '.$v."<br>";
					$tmpArr=preg_split('//', stripslashes(stripslashes($_POST["dbUpdate"][$counter])), -1, PREG_SPLIT_NO_EMPTY);
					foreach ($tmpArr as $char)  echo ord($char).":";
					echo "<hr>";
					$tmpArr=preg_split('//', $v, -1, PREG_SPLIT_NO_EMPTY);
					foreach ($tmpArr as $char)  echo ord($char).":";
					echo "<br>\n".stripslashes(stripslashes($_POST["dbUpdate"][$counter]))."<br>\n".$v;
					echo "</td>\n";
				}
			}else{
				//echo "<input type=\"checkbox\" name=\"dbUpdate[".$counter."]\" value=\"".addslashes($v)."\" checked>".$v."<br>\n";
				echo '<center><input type="Checkbox" name="dbUpdate['.$counter
				.']" class="htmlform" value="'.addslashes($v).'" checked></center></td>'."\n";
				echo '<td class="tdBorder">'.$v.'</td>'."\n";
			}
			echo '</tr>'."\n";
		}
	}else{
		//croak("no changes for table ".$tableName);
		echo '<tr><td colspan=2 class="tdBorder" >no changes for table '.$tableName.'</td></tr>';
	}

// optional Insert-Statements ...
	if ($doInserts && $tColl->tSQLInserts[$t]){
		//trigger_error($t);
		echo "<tr><td colspan=2>Insert-Statements found ...</td></tr>\n";
		foreach ($tColl->tSQLInserts[$t] as $vi){
			$counter++;
			$vi = str_replace(TEMPTABLE."_",TABLE."_", $vi);
			if (! preg_match("/insert\s+into\s+`*".$tableName."`*/i",$vi)){
				//croak($vi." not matched ..");
				continue;
			}
			if ($doUpdate){
				echo "<tr><td>";
				if (stripslashes(stripslashes($_POST["dbUpdate"][$counter])) == $vi){
					//echo "Perform Update: ".$v."<br>\n";
					echo "<center><font color=green>!</font></center></td>";
					echo '<td class="tdBorder">Perform '.$vi."<br>\n";
					if (mysql_query($vi)){
						echo "ok";
					}else{
						echo "Error ".mysql_errno().": ".mysql_error();
					}
					echo '</td>';
				}else{
					//croak($v." not updated ...");
					echo "<center><font color=red>!</font></center></td>\n";
					echo '<td class="tdBorder">not updated '.$vi."<br>";
					$tmpArr=preg_split('//', stripslashes(stripslashes($_POST["dbUpdate"][$counter])), -1, PREG_SPLIT_NO_EMPTY);
					foreach ($tmpArr as $char)  echo ord($char).":";
					echo "<hr>";
					$tmpArr=preg_split('//', $vi, -1, PREG_SPLIT_NO_EMPTY);
					foreach ($tmpArr as $char)  echo ord($char).":";
					echo "<br>\n".stripslashes(stripslashes($_POST["dbUpdate"][$counter]))."<br>\n".$vi;
					echo "</td>\n";
				}
				echo "</tr>";
			}else{
				echo '<tr><td><center><input type="Checkbox" name="dbUpdate['.$counter
					.']" class="htmlform" value="'.addslashes($vi).'" checked ></center></td>'."\n";
				echo '<td class="tbl_style">'.$vi.'</td></tr>'."\n";
			}
		}
		echo '<tr><td colspan=2 class="tdBorder" >&nbsp;&nbsp;&nbsp;</td></tr>';
	}

// optional right hand check of SQL data structure ...
	if ( ($right_hand_check) && ($sqlArr2) ){
		//croak("++++++++ Changes found for table ".$tableName." ++++++++++ :");
		echo '<tr><td colspan=2 class="tdBorder" ><font color="red">Achtung: </font>Gegenprobe gegen aktuelle Datenbankstruktur ergab folgende Treffer für diese Tabelle ...</td></tr>';
		foreach($sqlArr2 as $v){
			$counter++;
			//croak($v);
			echo '<tr class="tbl_style"><td class="tdBorder">';
			if ($doUpdate){
				if (stripslashes(stripslashes($_POST["dbUpdate"][$counter])) == $v){
					//echo "Perform Update: ".$v."<br>\n";
					echo "<center><font color=green>!</font></center></td>";
					echo '<td class="tdBorder">Perform '.$v."<br>\n";
					if (mysql_query($v)){
						echo "ok";
					}else{
						echo "Error ".mysql_errno().": ".mysql_error();
					}
					echo '</td>';
				}else{
					//croak($v." not updated ...");
					echo "<center><font color=red>!</font></center></td>";
					echo '<td class="tdBorder">not updated '.$v."<br>";
					$tmpArr=preg_split('//', stripslashes(stripslashes($_POST["dbUpdate"][$counter])), -1, PREG_SPLIT_NO_EMPTY);
					foreach ($tmpArr as $char)  echo ord($char).":";
					echo "<hr>";
					$tmpArr=preg_split('//', $v, -1, PREG_SPLIT_NO_EMPTY);
					foreach ($tmpArr as $char)  echo ord($char).":";
					echo "<br>\n".stripslashes(stripslashes($_POST["dbUpdate"][$counter]))."<br>\n".$v;
					echo "</td>\n";
				}
			}else{
				//echo "<input type=\"checkbox\" name=\"dbUpdate[".$counter."]\" value=\"".addslashes($v)."\" checked>".$v."<br>\n";
				echo '<center><input type="Checkbox" name="dbUpdate['.$counter
				.']" class="htmlform" value="'.addslashes($v).'"></center></td>'."\n";
				echo '<td class="tdBorder">'.$v.'</td>'."\n";
			}
			echo '</tr>'."\n";
		}
	}else{
		//croak("no changes for table ".$tableName);
		echo '<tr><td colspan=2 class="tdBorder" >&nbsp;&nbsp;&nbsp;no changes found for right-hand-check ...</td></tr>';
	}
	//echo "<hr>";
	//print_r($tCreate->tStructure);
}
echo "</table>";
echo '<table width="800" align="center" cellpadding="0" cellspacing="0" border="0">
		<tr class="tbl_style">
			<td align="right" style="background-color: #EEEEFF;">';

echo "\n<input type=\"submit\" name=\"CheckAgain\" value=\"CheckAgain\" class=\"htmlform\">&nbsp;&nbsp;&nbsp;&nbsp;";
echo "\n<input type=\"submit\" name=\"Update\" value=\"Update\" class=\"htmlform\">";

?>
			</td>
		</tr>
	</table>
	</form>
</body>
</html>
Return current item: Content*Builder