/**
* Reads extened MySQL field information
*
* Reads extened field information for each field of a MySQL table
* and fills an array like
* array(
* [Fieldname][attribut]=value,
* ['primary_key']=keys
* )
*
* @param $db Database
* @param $table Table
* @return array Field infos
*/
function getExtendedFieldInfo($db, $table)
{
global $config;
$fields_infos = array();
$t = GetCreateTable($db, $table);
$sqlf = "SHOW FULL FIELDS FROM `{$db}`.`{$table}`;";
$res = MSD_query($sqlf);
$num_fields = mysql_num_rows($res);
$f = array();
//will hold all info
for ($x = 0; $x < $num_fields; $x++) {
$row = mysql_fetch_array($res, MYSQL_ASSOC);
//v($row);
$i = $row['Field'];
// define name of field as index of array
//define field defaults - this way the index of the array is defined anyway
$f[$i]['field'] = '';
$f[$i]['collation'] = '';
$f[$i]['comment'] = '';
$f[$i]['type'] = '';
$f[$i]['size'] = '';
$f[$i]['attributes'] = '';
$f[$i]['null'] = '';
$f[$i]['default'] = '';
$f[$i]['extra'] = '';
$f[$i]['privileges'] = '';
$f[$i]['primary_keys'] = array();
if (isset($row['Collation'])) {
$f[$i]['collate'] = $row['Collation'];
}
if (isset($row['COLLATE'])) {
$f[$i]['collate'] = $row['COLLATE'];
}
// MySQL <4.1
if (isset($row['Comment'])) {
$f[$i]['comment'] = $row['Comment'];
}
if (isset($row['Type'])) {
$f[$i]['type'] = $row['Type'];
}
if (isset($row['Field'])) {
$f[$i]['field'] = $row['Field'];
}
$f[$i]['size'] = get_attribut_size_from_type($f[$i]['type']);
// remove size from type for readability in output
$f[$i]['type'] = str_replace('(' . $f[$i]['size'] . ')', '', $f[$i]['type']);
// look for attributes, everthing behind the first space is an atribut
$attributes = explode(' ', $f[$i]['type'], 2);
if (isset($attributes[1])) {
// we found attributes
unset($attributes[0]);
// delete type
$f[$i]['attributes'] = trim(implode(' ', $attributes));
//merge all other attributes
// remove attributes from type
$f[$i]['type'] = trim(str_replace($f[$i]['attributes'], '', $f[$i]['type']));
}
if (isset($row['NULL'])) {
$f[$i]['null'] = $row['NULL'];
}
if (isset($row['Null'])) {
$f[$i]['null'] = $row['Null'];
}
if (isset($row['Default'])) {
$f[$i]['default'] = $row['Default'];
}
if (isset($row['Extra'])) {
$f[$i]['extra'] = $row['Extra'];
}
if (isset($row['Privileges'])) {
$f[$i]['privileges'] = $row['Privileges'];
}
if (isset($row['privileges'])) {
$f[$i]['privileges'] = $row['privileges'];
}
}
// now get key definitions of the table and add info to field-array
$sql = 'SHOW KEYS FROM `' . $db . '`.`' . $table . '`';
$res = MSD_query($sql);
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
//echo "<br>Keys of $table: ";v($row);
$key_name = isset($row['Key_name']) ? $row['Key_name'] : '';
$index_type = isset($row['Index_type']) ? $row['Index_type'] : '';
$column_name = isset($row['Column_name']) ? $row['Column_name'] : '';
// to do: add other info about index etc.
if ($key_name == 'PRIMARY') {
$f['primary_keys'][] = $column_name;
}
}
return $f;
}
function FillFieldinfos($db, $tabelle)
{
global $config;
$fields_infos = array();
$t = GetCreateTable($db, $tabelle);
$sqlf = "SHOW FULL FIELDS FROM `{$db}`.`{$tabelle}`;";
$res = MSD_query($sqlf) or die(SQLError($sqlf, mysql_error()));
$anz_fields = mysql_num_rows($res);
for ($i = 0; $i < $anz_fields; $i++) {
$row = mysql_fetch_array($res);
if (MSD_NEW_VERSION) {
$fields_infos[$i]['collate'] = $row['Collation'];
}
if (MSD_NEW_VERSION) {
$fields_infos[$i]['comment'] = $row['Comment'];
}
$fields_infos[$i]['privileges'] = $row['Privileges'];
}
$fields_infos['_primarykey_'] = "";
$flds = $keys = $ukeys = $fkeys = 0;
$fields_infos['_createtable_'] = $t;
$tmp = explode("\n", $t);
for ($i = 1; $i < count($tmp) - 1; $i++) {
$t = trim($tmp[$i]);
if (substr($t, -1) == ",") {
$t = substr($t, 0, strlen($t) - 1);
}
//echo $t."<br>";
if (substr($t, 0, 12) == "PRIMARY KEY ") {
$t = str_replace("`", "", $t);
$fields_infos['_primarykey_'] = substr($t, strpos($t, "(") + 1, strpos($t, ")") - strpos($t, "(") - 1);
} elseif (substr($t, 0, 4) == "KEY ") {
$t = str_replace("`", "", $t);
$att = explode(" ", $t);
$fields_infos['_key_'][$keys]['name'] = $att[1];
$att[2] = str_replace("(", "", $att[2]);
$att[2] = str_replace(")", "", $att[2]);
$fields_infos['_key_'][$keys]['columns'] = $att[2];
$keys++;
} elseif (substr($t, 0, 11) == "UNIQUE KEY ") {
$t = str_replace("`", "", $t);
$att = explode(" ", $t);
$fields_infos['_uniquekey_'][$ukeys]['name'] = $att[2];
$att[3] = str_replace("(", "", $att[3]);
$att[3] = str_replace(")", "", $att[3]);
$fields_infos['_uniquekey_'][$ukeys]['columns'] = $att[3];
$ukeys++;
} elseif (substr($t, 0, 13) == "FULLTEXT KEY ") {
$t = str_replace("`", "", $t);
$att = explode(" ", $t);
$fields_infos['_fulltextkey_'][$fkeys]['name'] = $att[2];
$att[3] = str_replace("(", "", $att[3]);
$att[3] = str_replace(")", "", $att[3]);
$fields_infos['_fulltextkey_'][$fkeys]['columns'] = $att[3];
$fkeys++;
} else {
$att = explode(" ", $t);
if (substr($att[0], 0, 1) == "`" && substr($att[0], -1) == "`") {
$fields_infos[$flds]['name'] = str_replace("`", "", $att[0]);
$s = 1;
} else {
$fields_infos[$flds]['name'] = str_replace("`", "", $att[0]) . " ";
for ($ii = 1; $i < count($att); $i++) {
if (substr($att[$ii], -1) != "`") {
$fields_infos[$flds]['name'] .= $att[$ii];
} else {
$fields_infos[$flds]['name'] .= str_replace("`", "", $att[$ii]);
$s = $ii + 1;
break;
}
}
}
$fields_infos[$flds]['type'] = strpos($att[$s], "(") ? substr($att[$s], 0, strpos($att[$s], "(")) : $att[$s];
$fields_infos[$flds]['size'] = strpos($att[$s], "(") ? substr($att[$s], strpos($att[$s], "(") + 1, strpos($att[$s], ")") - strpos($att[$s], "(") - 1) : "";
$fields_infos[$flds]['default'] = "";
$fields_infos[$flds]['null'] = "";
$fields_infos[$flds]['extra'] = "";
$fields_infos[$flds]['attribut'] = "";
$s++;
while ($s < count($att)) {
if (isset($att[$s + 1]) && strtolower($att[$s] . $att[$s + 1]) == "unsignedzerofill") {
$fields_infos[$flds]['attribut'] = "unsigned zerofill";
$s += 2;
} elseif (strtolower($att[$s]) == "unsigned") {
$fields_infos[$flds]['attribut'] = "unsigned";
$s++;
} elseif (isset($att[$s + 1]) && strtolower($att[$s] . $att[$s + 1]) == "notnull") {
$fields_infos[$flds]['null'] = "NOT NULL";
$s += 2;
} elseif (strtolower($att[$s]) == "null") {
$fields_infos[$flds]['null'] = "NULL";
$s++;
} elseif (strtolower($att[$s]) == "auto_increment") {
$fields_infos[$flds]['extra'] = "auto_increment";
$s++;
} elseif (strtolower($att[$s]) == "collate") {
$fields_infos[$flds]['collate'] = $att[$s + 1];
$s += 2;
} elseif (strtolower($att[$s]) == "character") {
$fields_infos[$flds]['character set'] = $att[$s + 2];
$s += 3;
} elseif (strtolower($att[$s]) == "default") {
//if(strpos(strtolower($fields_infos[$flds]['type']),"text")===false && strtolower($fields_infos[$flds]['type'])!="varchar")
$d = "";
$ii = $s + 1;
if (substr($att[$ii], 0, 1) == "'" && substr($att[$ii], -1) == "'") {
$fields_infos[$flds]['default'] = $att[$ii];
$s += 2;
} else {
if (substr($att[$ii], 0, 1) == "'") {
$fields_infos[$flds]['default'] = $att[$ii] . " " . $att[$ii + 1];
$s += 3;
} else {
$fields_infos[$flds]['default'] = $att[$ii];
$s += 2;
}
}
} else {
//echo "not identified: $att[$s]<br>";
$s++;
}
}
$flds++;
}
}
//echo substr($tmp[count($tmp)-1],2)."<br>";
$ext = explode(" ", substr($tmp[count($tmp) - 1], 2));
$s = "";
$haltchar = "=";
for ($i = 0; $i < count($ext); $i++) {
if (!strpos($ext[$i], $haltchar)) {
$s .= $ext[$i] . " ";
} else {
if ($haltchar == "'") {
$fields_infos['_tableinfo_']['COMMENT'] = $s . $ext[$i];
$s = "";
$haltchar = "=";
} else {
$s .= substr($ext[$i], 0, strpos($ext[$i], $haltchar));
if (strtoupper($s) == "COMMENT") {
$s = substr($ext[$i], strpos($ext[$i], $haltchar) + 1);
if (substr($s, -1) == "'") {
$fields_infos['_tableinfo_']['COMMENT'] = $s;
$s = "";
} else {
$s .= " ";
$haltchar = "'";
}
} else {
$fields_infos['_tableinfo_'][strtoupper($s)] = substr($ext[$i], strpos($ext[$i], $haltchar) + 1);
$s = "";
}
}
}
}
return $fields_infos;
}
function get_sqlbefehl()
{
global $restore, $config, $databases, $lang;
//Init
$restore['fileEOF'] = false;
$restore['EOB'] = false;
$complete_sql = '';
$sqlparser_status = 0;
if (!isset($restore['eintraege_ready'])) {
$restore['eintraege_ready'] = 0;
}
//Parsen
while ($sqlparser_status != 100 && !$restore['fileEOF'] && !$restore['EOB']) {
//nächste Zeile lesen
$zeile = $restore['compressed'] ? gzgets($restore['filehandle']) : fgets($restore['filehandle']);
/******************* Setzen des Parserstatus *******************/
// herausfinden um was für einen Befehl es sich handelt
if ($sqlparser_status == 0) {
//Vergleichszeile, um nicht bei jedem Vergleich strtoupper ausführen zu müssen
$zeile2 = strtoupper(trim($zeile));
// Am Ende eines MySQLDumper-Backups angelangt?
if (substr($zeile2, 0, 6) == '-- EOB' || substr($zeile2, 0, 5) == '# EOB') {
$restore['EOB'] = true;
$zeile = '--';
$zeile2 = '--';
$sqlparser_status = 100;
}
// Kommenatr?
if (substr($zeile2, 0, 2) == '--' || substr($zeile2, 0, 1) == '#') {
$zeile = '';
$zeile2 = '';
$sqlparser_status = 0;
}
//Einfache Anweisung finden die mit Semikolon beendet werden
if (substr($zeile2, 0, 11) == 'LOCK TABLES') {
$sqlparser_status = 5;
}
if (substr($zeile2, 0, 6) == 'COMMIT') {
$sqlparser_status = 5;
}
if (substr($zeile2, 0, 5) == 'BEGIN') {
$sqlparser_status = 5;
}
if (substr($zeile2, 0, 13) == 'UNLOCK TABLES') {
$sqlparser_status = 5;
}
if (substr($zeile2, 0, 4) == 'SET ') {
$sqlparser_status = 5;
}
if (substr($zeile2, 0, 6) == 'START ') {
$sqlparser_status = 5;
}
if (substr($zeile2, 0, 3) == '/*!') {
$sqlparser_status = 5;
}
//MySQL-Condition oder Kommentar
if (substr($zeile2, 0, 12) == 'CREATE TABLE') {
$sqlparser_status = 2;
}
//Createaktion
if (substr($zeile2, 0, 12) == 'CREATE INDEX') {
$sqlparser_status = 4;
}
//Indexaktion
//Kommentar
if ($sqlparser_status != 5 && substr($zeile2, 0, 2) == '/*') {
$sqlparser_status = 6;
}
// Befehle, die nicht ausgeführt werden sollen
if (substr($zeile2, 0, 4) == 'USE ') {
$sqlparser_status = 7;
}
if (substr($zeile2, 0, 14) == 'DROP DATABASE ') {
$sqlparser_status = 7;
}
if (substr($zeile2, 0, 10) == 'DROP TABLE') {
$sqlparser_status = 1;
}
//Löschaktion
if (substr($zeile2, 0, 7) == 'INSERT ') {
$sqlparser_status = 3;
//Datensatzaktion
//$restore['actual_table']=strtolower(get_tablename_aus_insert($zeile));
$restore['actual_table'] = get_tablename_aus_insert($zeile);
// Pruefen ob die Spaltenanzahl bekannt ist und wenn nicht setzen
if (!isset($restore['num_table_fields'][$restore['actual_table']])) {
$restore['num_table_fields'][$restore['actual_table']] = get_num_rows($restore['actual_table']);
}
}
// Fortsetzung von erweiterten Inserts
if ($restore['flag'] == 1) {
$sqlparser_status = 3;
}
if ($sqlparser_status == 0 && trim($complete_sql) > '' && $restore['flag'] == -1) {
// Unbekannten Befehl entdeckt
v($restore);
echo "<br>Sql: " . $complete_sql;
echo "<br>Erweiterte Inserts: " . $restore['erweiterte_inserts'];
die('<br>' . $lang['unknown_sqlcommand'] . ': ' . $zeile);
}
/******************* Ende von Setzen des Parserstatus *******************/
}
$last_char = substr(rtrim($zeile), -1);
// Zeilenumbrüche erhalten - sonst werden Schlüsselwörter zusammengefügt
// z.B. 'null' und in der nächsten Zeile 'check' wird zu 'nullcheck'
$complete_sql .= $zeile . "\n";
if ($sqlparser_status == 1) {
//Löschaktion
if ($last_char == ';') {
$sqlparser_status = 100;
}
//Befehl komplett
}
if ($sqlparser_status == 2) {
// Createanweisung ist beim Finden eines ; beendet
if ($last_char == ';') {
if ($config['minspeed'] > 0) {
$restore['anzahl_zeilen'] = $config['minspeed'];
}
//Spaltenanzahl fuer diese Tabelle merken
$restore['actual_table'] = get_tablename($complete_sql);
$complete_sql = del_inline_comments($complete_sql);
//Inline-Kommentare entfernen
//Create ausfuehren und Anzahl der Spalten speichern
$restore['actual_fieldcount'] = submit_create_action($complete_sql);
$restore['num_table_fields'][$restore['actual_table']] = $restore['actual_fieldcount'];
// Zeile verwerfen und naechsten Befehl suchen
$complete_sql = '';
$sqlparser_status = 0;
}
}
if ($sqlparser_status == 3) {
//INSERT
// Anzahl der Felder ermitteln wenn unbekannt
if (!isset($restore['num_table_fields'][$restore['actual_table']])) {
$restore['num_table_fields'][$restore['actual_table']] = get_num_rows($restore['actual_table']);
}
$AnzahlFelder = SQL_Is_Complete($complete_sql, $restore['num_table_fields'][$restore['actual_table']], DEBUG);
// einen vollständigen Insert ermittelt
if ($AnzahlFelder == $restore['num_table_fields'][$restore['actual_table']]) {
$sqlparser_status = 100;
$complete_sql = trim($complete_sql);
// letzter Ausdruck des erweiterten Inserts erreicht?
if (substr($complete_sql, -2) == ');') {
$restore['flag'] = -1;
}
// Wenn am Ende der Zeile ein Klammer Komma -> erweiterter Insert-Modus -> Steuerflag setzen
if (substr($complete_sql, -2) == '),') {
// letztes Komme gegen Semikolon tauschen
$complete_sql = substr($complete_sql, 0, -1) . ';';
$restore['erweiterte_inserts'] = 1;
$restore['flag'] = 1;
}
if (substr($complete_sql, 0, 7) != 'INSERT ') {
$complete_sql = $restore['insert_syntax'] . ' VALUES ' . $complete_sql . ';';
} else {
// INSERT Syntax ermitteln und merken
$ipos = strpos(strtoupper($complete_sql), 'VALUES');
if (!$ipos === false) {
$restore['insert_syntax'] = substr($complete_sql, 0, $ipos);
} else {
$restore['insert_syntax'] = 'INSERT INTO `' . $restore['actual_table'] . '`';
}
}
if ($AnzahlFelder > $restore['num_table_fields'][$restore['actual_table']] && $restore['erweiterte_inserts'] == 0) {
if (!isset($restore['table_create'][$restore['actual_table']])) {
global $lang;
include './inc/functions_sql.php';
$restore['table_create'][$restore['actual_table']] = GetCreateTable($databases['db_actual'], $restore['actual_table']);
}
echo '<form action="main.php?action=extinfo" method="post">';
echo '<p class="warnung">Parser-Fehler : zuviele gezählt in Tabelle ' . $restore["actual_table"] . ' (' . $AnzahlFelder . ' statt ' . $restore["num_table_fields"][$restore["actual_table"]] . ')';
echo '<br>Aktuelle Tabelle: ' . $restore['actual_table'];
echo '<h4>CREATE-Anweisung</h4><textarea name="create_sql" style="width:90%;height:200px;">' . $restore["table_create"][$restore["actual_table"]] . '</textarea>';
echo '<h4>INSERT-Anweisung</h4><textarea name="insert_sql" style="width:90%;height:200px;">' . $complete_sql . '</textarea></p><br>';
echo '<br><br><input type="submit" name="tell_error" value="Fehlerbericht"></form>' . $zeile;
die;
}
}
}
// Index
if ($sqlparser_status == 4) {
//Createindex
if ($last_char == ';') {
if ($config['minspeed'] > 0) {
$restore['anzahl_zeilen'] = $config['minspeed'];
}
$complete_sql = del_inline_comments($complete_sql);
$sqlparser_status = 100;
//Befehl komplett
}
}
// Kommentar oder Condition
if ($sqlparser_status == 5) {
//Anweisung
if ($last_char == ';') {
if ($config['minspeed'] > 0) {
$restore['anzahl_zeilen'] = $config['minspeed'];
}
$complete_sql = del_inline_comments($complete_sql);
$sqlparser_status = 100;
//Befehl komplett
}
}
// Mehrzeiliger oder Inline-Kommentar
if ($sqlparser_status == 6) {
if (!strrpos($zeile, '*/') === false) {
$complete_sql = trim(del_inline_comments($complete_sql));
$sqlparser_status = 0;
}
}
// Befehle, die verworfen werden sollen
if ($sqlparser_status == 7) {
//Anweisung
if ($last_char == ';') {
if ($config["minspeed"] > 0) {
$restore["anzahl_zeilen"] = $config["minspeed"];
}
$complete_sql = '';
$sqlparser_status = 0;
}
}
if ($restore['compressed'] && gzeof($restore['filehandle'])) {
$restore['fileEOF'] = true;
}
if (!$restore['compressed'] && feof($restore['filehandle'])) {
$restore['fileEOF'] = true;
}
}
return trim($complete_sql);
}