<?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;
}
}
?>