function generateScript($opt, $sel_tables, $targetdb, $sourcedb)
{
global $options, $backticks, $syntaxhighlight, $source_server, $target_server;
$s_id = $sourcedb->getMysqlConnection();
$t_id = $targetdb->getMysqlConnection();
$options = $opt;
$backticks = array("table" => isset($options['type']['backticks_table']), "field" => isset($options['type']['backticks_attribute']), "filter" => isset($options['type']['backticks_filter']));
$syntaxhighlight = $syntax;
$result_string = "";
if ($s_id) {
$source_server = fetchServerVersion($s_id);
if (@mysql_select_db($sourcedb->name, $s_id)) {
if ($t_id) {
$target_server = fetchServerVersion($t_id);
if (@mysql_select_db($targetdb->name, $t_id)) {
$s_tab = fetchTables($sourcedb->name, $s_id, $sel_tables);
$t_tab = fetchTables($targetdb->name, $t_id, $sel_tables);
if (is_array($t_tab)) {
foreach ($t_tab as $key => $value) {
if (!isset($s_tab[$key])) {
$item = highlightString("CREATE TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . translate("(") . "\n";
$idx = 1;
$max = count($value["fields"]);
foreach ($value["fields"] as $vf) {
$item .= " " . fieldString($vf) . ($idx < $max || count($value["idx"]) ? translate(",") : "") . "\n";
$idx++;
}
$idx = 1;
$max = count($value["idx"]);
if (isset($value["idx"])) {
foreach ($value["idx"] as $vx) {
$item .= " " . indexString($vx) . ($idx < $max ? "," : "") . "\n";
$idx++;
}
}
$item .= translate(")");
if (isset($options['type']['changetype'])) {
if (isset($t_tab[$key]["type"]) && $t_tab[$key]["type"] != "") {
$item .= " " . highlightString("TYPE") . highlightstring("=", HIGHLIGHT_SIGNS) . highlightstring($t_tab[$key]["type"], HIGHLIGHT_CONSTANTS);
}
}
if (isset($options['type']['alteroptions'])) {
if (isset($t_tab[$key]["options"]) && $t_tab[$key]["options"] != "") {
$item .= " " . $t_tab[$key]["options"];
}
}
if (!isset($options['type']['altercomments'])) {
if (isset($t_tab[$key]["comment"]) && $t_tab[$key]["comment"] != "") {
$item .= " " . highlightString("COMMENT") . highlightstring("=", HIGHLIGHT_SIGNS) . "'" . (function_exists("mysql_escape_string") ? mysql_escape_string($t_tab[$key]["comment"]) : addslashes($t_tab[$key]["comment"])) . translate("'");
}
}
$item .= translate(";") . "\n\n";
$result_string .= $item;
}
}
}
$added_fields = array();
if (is_array($s_tab)) {
foreach ($s_tab as $key => $value) {
if (isset($t_tab[$key])) {
$altered = 0;
$altering = "";
$alteredfields = NULL;
$lastfield = NULL;
$added_fields[$key] = array();
foreach ($t_tab[$key]["fields"] as $vk => $vf) {
if (!isset($s_tab[$key]["fields"][$vk])) {
if (isset($_SESSION["renamed"][$key]) && in_array($vk, $_SESSION["renamed"][$key])) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : ",\n") . " " . highlightString("CHANGE") . " " . fixFieldName(array_search($vk, $_SESSION["renamed"][$key]), $backticks['field'], $backticks['filter']) . " " . fieldString($t_tab[$key]["fields"][$vk]);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("CHANGE") . " " . fixFieldName($vk, $backticks['field'], $backticks['filter']) . " " . fieldString($t_tab[$key]["fields"][$vk]) . ";\n";
}
} else {
$added_fields[$key][] = $vk;
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("ADD") . " " . fieldString($t_tab[$key]["fields"][$vk]) . (isset($lastfield) ? " " . highlightString("AFTER") . " {$lastfield}" : " " . highlightString("FIRST"));
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("ADD") . " " . fieldString($t_tab[$key]["fields"][$vk]) . (isset($lastfield) ? " " . highlightString("AFTER") . " {$lastfield}" : " " . highlightString("FIRST")) . translate(";") . "\n";
}
}
$altered++;
}
$lastfield = $t_tab[$key]["fields"][$vk]["name"];
}
foreach ($value["fields"] as $vk => $vf) {
if (isset($t_tab[$key]["fields"][$vk])) {
if ($vf["type"] == $t_tab[$key]["fields"][$vk]["type"] && $vf["null"] == $t_tab[$key]["fields"][$vk]["null"] && $vf["default"] != $t_tab[$key]["fields"][$vk]["default"]) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : ",\n") . " " . highlightString("ALTER") . " " . fixFieldName($t_tab[$key]["fields"][$vk]["name"], $backticks['field'], $backticks['filter']) . " " . (isset($t_tab[$key]["fields"][$vk]["default"]) ? highlightString("SET DEFAULT") . " " . (is_numeric($t_tab[$key]["fields"][$vk]["default"]) ? $t_tab[$key]["fields"][$vk]["default"] : "'" . $t_tab[$key]["fields"][$vk]["default"] . "'") : " " . highlightString("DROP DEFAULT"));
$alterfields[] = array("name" => $key . "." . $t_tab[$key]["fields"][$vk]["name"], "from" => fieldString($s_tab[$key]["fields"][$vk], FALSE), "to" => fieldString($t_tab[$key]["fields"][$vk], FALSE));
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("ALTER") . " " . fixFieldName($t_tab[$key]["fields"][$vk]["name"], $backticks['field'], $backticks['filter']) . " " . (isset($t_tab[$key]["fields"][$vk]["default"]) ? highlightString("SET DEFAULT") . " " . (is_numeric($t_tab[$key]["fields"][$vk]["default"]) ? $t_tab[$key]["fields"][$vk]["default"] : "'" . $t_tab[$key]["fields"][$vk]["default"] . "'") : " " . highlight("DROP DEFAULT"));
$result_string .= "#\n# Fieldformat of '{$key}.{$vk}' changed from '" . fieldString($s_tab[$key]["fields"][$vk], FALSE) . " to " . fieldString($t_tab[$key]["fields"][$vk], FALSE) . ". Possibly data modifications needed!\n#\n\n";
}
} else {
if ($vf["type"] != $t_tab[$key]["fields"][$vk]["type"] || $vf["null"] != $t_tab[$key]["fields"][$vk]["null"] || $vf["default"] != $t_tab[$key]["fields"][$vk]["default"]) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : ",\n") . " " . highlightString("MODIFY") . " " . fieldString($t_tab[$key]["fields"][$vk]);
$alteredfields[] = array("name" => $key . "." . $t_tab[$key]["fields"][$vk]["name"], "from" => fieldString($s_tab[$key]["fields"][$vk], FALSE), "to" => fieldString($t_tab[$key]["fields"][$vk], FALSE));
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("MODIFY") . " " . fieldString($t_tab[$key]["fields"][$vk]) . ";\n";
$result_string .= "#\n# Fieldformat of '{$key}.{$vk}' changed from '" . fieldString($s_tab[$key]["fields"][$vk], FALSE) . " to " . fieldString($t_tab[$key]["fields"][$vk], FALSE) . ". Possibly data modifications needed!\n#\n\n";
}
$altered++;
}
}
} else {
if (!isset($_SESSION["renamed"][$key][$vk])) {
$addedfieldnames = "";
foreach ($added_fields[$key] as $addfld) {
$addedfieldnames .= ($addedfieldnames == "" ? "" : "&") . "fields[]=" . urlencode($addfld);
}
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("DROP") . " " . fixFieldName($vk, $backticks['field'], $backticks['filter']);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("DROP") . " " . fixFieldName($backticks, $vk) . translate(";") . "\n";
}
}
$altered++;
}
}
if (isset($t_tab[$key]["idx"])) {
foreach ($t_tab[$key]["idx"] as $vk => $vf) {
if (!isset($s_tab[$key]["idx"][$vk])) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("ADD") . " " . indexString($vf);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("ADD") . " " . indexString($vf) . translate(";") . "\n";
}
$altered++;
}
}
}
if (isset($value["idx"])) {
foreach ($value["idx"] as $vk => $vf) {
if (isset($t_tab[$key]["idx"][$vk])) {
if (fieldsdiff($vf["fields"], $t_tab[$key]["idx"][$vk]["fields"])) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("DROP") . " " . ($vf["unique"] && $vk == "PRIMARY" ? highlightString("PRIMARY KEY") : highlightString("INDEX") . " {$vk}") . translate(",") . "\n " . highlightString("ADD") . " " . indexString($t_tab[$key]["idx"][$vk]);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("DROP") . " " . ($vf["unique"] && $vk == "PRIMARY" ? highlightString("PRIMARY KEY") : highlightString("INDEX") . " {$vk}") . translate(";\n") . highlightString("ALTER TABLE") . " {$key} " . highlightString("ADD") . " " . indexString($t_tab[$key]["idx"][$vk]) . translate(";") . "\n";
}
}
} else {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("DROP") . " " . ($vf["unique"] && $vk == "PRIMARY" ? highlightString("PRIMARY KEY") : highlightString("INDEX") . " {$vk}");
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("DROP") . " " . ($vf["unique"] && $vk == "PRIMARY" ? highlightString("PRIMARY KEY") : highlightString("INDEX") . " {$vk}") . translate(";") . "\n";
}
$altered++;
}
}
}
// Constraints
if (isset($s_tab[$key]["constraints"])) {
foreach ($s_tab[$key]["constraints"] as $vk => $vf) {
if (!isset($t_tab[$key]["constraints"][$vk])) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . constraintString($vf, $targetdb->name, CONSTRAINT_DROP, $target_server);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . constraintString($vf, $targetdb->name, CONSTRAINT_DROP, $target_server) . translate(";") . "\n";
}
$altered++;
}
}
}
if (isset($t_tab[$key]["constraints"])) {
foreach ($t_tab[$key]["constraints"] as $vk => $vf) {
if (!isset($s_tab[$key]["constraints"][$vk])) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . constraintString($vf, $targetdb->name, CONSTRAINT_ADD);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . constraintString($vf, $targetdb->name, CONSTRAINT_ADD) . translate(";") . "\n";
}
$altered++;
}
}
}
// Tabellenoptionen
if (isset($options['type']['changetype'])) {
if ($value["type"] != $t_tab[$key]["type"]) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("TYPE") . highlightstring("=", HIGHLIGHT_SIGNS) . highlightstring($t_tab[$key]["type"], HIGHLIGHT_CONSTANTS);
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlighString("TYPE") . highlightstring("=", HIGHLIGHT_SIGNS) . highlightstring($t_tab[$key]["type"], HIGHLIGHT_CONSTANTS) . translate(";") . "\n";
}
$altered++;
}
}
if (isset($options['type']['alteroptions'])) {
if ($value["options"] != $t_tab[$key]["options"]) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . $t_tab[$key]["options"];
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString($t_tab[$key]["options"], HIGHLIGHT_VALUES) . translate(";") . "\n";
}
$altered++;
}
}
if (!isset($options['type']['altercomments'])) {
if ($value["comment"] != $t_tab[$key]["comment"]) {
if (!isset($options['type']['altercomments'])) {
$altering .= ($altering == "" ? "" : translate(",") . "\n") . " " . highlightString("COMMENT") . highlightstring("=", HIGHLIGHT_SIGNS) . "'" . (function_exists("mysql_escape_string") ? mysql_escape_string($t_tab[$key]["comment"]) : addslashes($t_tab[$key]["comment"])) . translate("'");
} else {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . " " . highlightString("COMMENT") . highlightstring("=", HIGHLIGHT_SIGNS) . "'" . (function_exists("mysql_escape_string") ? mysql_escape_string($t_tab[$key]["comment"]) : addslashes($t_tab[$key]["comment"])) . translate("';") . "\n";
}
$altered++;
}
}
// Abschluss ...
if ($altering != "") {
$result_string .= highlightString("ALTER TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . "\n{$altering};\n";
if (isset($alteredfields)) {
$result_string .= "#\n";
$result_string .= "# " . (count($alteredfields) == 1 ? "Fieldformat of" : "Fieldformats of") . "\n";
foreach ($alteredfields as $val) {
$result_string .= "# " . $val["name"] . " changed from " . $val["from"] . " to " . $val["to"] . "\n";
}
$result_string .= "# " . "Possibly data modifications needed" . "\n";
$result_string .= "#\n";
}
$result_string .= "\n";
} else {
if ($altered) {
$result_string .= "\n";
}
}
} else {
$result_string .= highlightString("DROP TABLE") . " " . fixTableName($key, $backticks['table'], $backticks['filter']) . translate(";") . "\n\n";
}
}
}
} else {
die("error1");
}
@mysql_close($t_id);
} else {
die("error2");
}
} else {
die("error3");
}
@mysql_close($s_id);
} else {
die("error4");
}
return $result_string;
}
public function createQuery($row, $pColumns, $keycnt, $tableName)
{
if ($this->fh == false) {
return false;
}
$keyvalues = array();
$query = "";
$diffType = $row->getDiffs();
$backticks = array("table" => isset($this->options['type']['backticks_table']), "field" => isset($this->options['type']['backticks_attribute']), "filter" => isset($this->options['type']['backticks_filter']));
// actually we can't collect updates, to implement checking by column
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_Value) {
$this->qCollection["update"][] = "UPDATE " . fixTableName($tableName, $backticks['table'], $backticks['filter']) . " SET ";
// INSERT VALUES PREFIX
} else {
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_New) {
++$this->cIns;
if ($this->cIns > 1) {
$this->qCollection["insert"][] = ",\n";
} else {
$insertPrefix = "INSERT INTO " . fixTableName($tableName, $backticks['table'], $backticks['filter']) . " (";
$first = true;
reset($row->data);
while (list($columnName, $data) = each($row->data)) {
if (!$first) {
$insertPrefix .= ",";
} else {
$first = false;
}
$insertPrefix .= fixFieldName($columnName, $backticks['field'], $backticks['filter']);
}
$insertPrefix .= ") VALUES \n";
$this->qCollection["insert"][] = $insertPrefix;
}
$this->qCollection["insert"][] = "(";
// DELETE PREFIX
} else {
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_Missing) {
if ($keycnt == 1) {
if ($this->cDel == 0) {
$this->qCollection["delete"][] = "DELETE FROM " . fixTableName($tableName, $backticks['table'], $backticks['filter']) . " WHERE " . $pColumns[1] . " IN (\n";
}
} else {
$this->qCollection["delete"][] = "DELETE FROM " . fixTableName($tableName, $backticks['table'], $backticks['filter']) . " WHERE ";
}
++$this->cDel;
}
}
}
$dc = 0;
// count all fields
$uc = 0;
// count different fields, for update
$pc = 0;
// count primary columns for delete when keycnt > 1
$totalCols = count($row->data);
// PROCESSING COLUMNS
reset($row->data);
while (list($columnName, $data) = each($row->data)) {
$isEnd = ++$dc == $totalCols;
// first inc then test
if ($pColumns != null && in_array($columnName, $pColumns)) {
$keyvalues[] = array($columnName, $data);
}
$diff = $row->getValueDiff($columnName);
if ($diff) {
++$uc;
if ($uc > 1) {
$query .= ", ";
}
$attribute = fixFieldName($columnName, $backticks['field'], $backticks['filter']);
if ($diff->compare != null && $diff->compare != "") {
$query .= $attribute . "='" . fixMysqlString($diff->compare, $this->t_id) . "'";
} else {
$query .= $attribute . (is_string($diff->compare) ? "=''" : "=(NULL)");
}
} else {
// DELETE SYNTAX
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_Missing) {
if ($keycnt == 1 && in_array($columnName, $pColumns)) {
if ($this->cDel > 1) {
$query .= ",\n";
}
$query .= "'" . fixMysqlString($data, $this->t_id) . "'";
// MULTI pKEY or NOT PRESENT ( in this case all fields will be used for better deleting )
} else {
if ($keycnt == 0 || $pColumns && in_array($columnName, $pColumns)) {
++$pc;
if ($pc > 1) {
$query .= " AND ";
}
$attribute = fixFieldName($columnName, $backticks['field'], $backticks['filter']);
if ($data != null && $data != "") {
$query .= $attribute . "='" . fixMysqlString($data, $this->t_id) . "'";
} else {
$query .= $attribute . (is_string($data) ? "=''" : " IS NULL");
}
if ($keycnt == 0 && $isEnd || $pc == $keycnt) {
$query .= ";\n";
}
}
}
// INSERT SYNTAX
} else {
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_New) {
if ($data != null && $data != "") {
$query .= "'" . fixMysqlString($data, $this->t_id) . "'";
} else {
$query .= is_string($data) ? "''" : "(NULL)";
}
if ($isEnd == false) {
$query .= ",";
} else {
$query .= ")";
}
}
}
}
}
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_Missing) {
$this->qCollection["delete"][] = $query;
} else {
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_New) {
$this->qCollection["insert"][] = $query;
} else {
if ($diffType[0] instanceof MyDiff_Diff_Table_Row_Value && $keyvalues != null) {
$query .= " WHERE ";
foreach ($keyvalues as $key) {
$query .= $key[0] . " = '" . $key[1] . "'";
if ($key != end($keyvalues)) {
$query .= " AND ";
}
}
$query .= ";\n";
$this->qCollection["update"][] = $query;
}
}
}
}