コード例 #1
0
ファイル: functions_sql.php プロジェクト: robmat/samplebator
/**
 * 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;
}
コード例 #2
0
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;
}
コード例 #3
0
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);
}