/** * 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); }