Laden von .sql-Dateien aus PHP heraus

Question

Ich erstelle ein Installationsskript für eine Anwendung, die ich gerade entwickle, und muss Datenbanken dynamisch aus PHP heraus erstellen. Ich habe es geschafft, die Datenbank zu erstellen, aber jetzt muss ich mehrere .sql-Dateien laden. Ich hatte geplant, die Datei zu öffnen und mysql_query eine Zeile nach der anderen abzufragen - bis ich mir die Schemadateien ansah und feststellte, dass sie nicht nur eine Abfrage pro Zeile sind.

Wie lade ich also eine SQL-Datei aus PHP heraus (wie phpMyAdmin es mit seinem Import-Befehl tut)?

Answer 1

$db = new PDO($dsn, $user, $password);

$sql = file_get_contents('file.sql');

$qr = $db->exec($sql);

Answer 2

PhpBB verwendet einige Funktionen, um seine Dateien zu analysieren. Sie sind ziemlich gut kommentiert (was für eine Ausnahme!), so dass Sie leicht wissen können, was sie tun (ich habe diese Lösung von http://www.frihost.com/forums/vt-8194.html ). Hier ist die Lösung und ich habe sie schon oft benutzt:

<?php
ini_set('memory_limit', '5120M');
set_time_limit ( 0 );
/***************************************************************************
*                             sql_parse.php
*                              -------------------
*     begin                : Thu May 31, 2001
*     copyright            : (C) 2001 The phpBB Group
*     email                : support@phpbb.com
*
*     $Id: sql_parse.php,v 1.8 2002/03/18 23:53:12 psotfx Exp $
*
****************************************************************************/

/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/

/***************************************************************************
*
*   These functions are mainly for use in the db_utilities under the admin
*   however in order to make these functions available elsewhere, specifically
*   in the installation phase of phpBB I have seperated out a couple of
*   functions into this file.  JLH
*
\***************************************************************************/

//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
function remove_comments(&$output)
{
$lines = explode("\n", $output);
$output = "";

// try to keep mem. use down
$linecount = count($lines);

$in_comment = false;
for($i = 0; $i < $linecount; $i++)
{
    if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
    {
        $in_comment = true;
    }

    if( !$in_comment )
    {
        $output .= $lines[$i] . "\n";
    }

    if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
    {
        $in_comment = false;
    }
}

unset($lines);
return $output;
}

//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
function remove_remarks($sql)
{
$lines = explode("\n", $sql);

// try to keep mem. use down
$sql = "";

$linecount = count($lines);
$output = "";

for ($i = 0; $i < $linecount; $i++)
{
    if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
    {
        if (isset($lines[$i][0]) && $lines[$i][0] != "#")
        {
            $output .= $lines[$i] . "\n";
        }
        else
        {
            $output .= "\n";
        }
        // Trading a bit of speed for lower mem. use here.
        $lines[$i] = "";
    }
}

return $output;

}

//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
function split_sql_file($sql, $delimiter)
{
// Split up our string into "possible" SQL statements.
$tokens = explode($delimiter, $sql);

// try to save mem.
$sql = "";
$output = array();

// we don't actually care about the matches preg gives us.
$matches = array();

// this is faster than calling count($oktens) every time thru the loop.
$token_count = count($tokens);
for ($i = 0; $i < $token_count; $i++)
{
    // Don't wanna add an empty string as the last thing in the array.
    if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
    {
        // This is the total number of single quotes in the token.
        $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
        // Counts single quotes that are preceded by an odd number of backslashes,
        // which means they're escaped quotes.
        $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);

        $unescaped_quotes = $total_quotes - $escaped_quotes;

        // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
        if (($unescaped_quotes % 2) == 0)
        {
            // It's a complete sql statement.
            $output[] = $tokens[$i];
            // save memory.
            $tokens[$i] = "";
        }
        else
        {
            // incomplete sql statement. keep adding tokens until we have a complete one.
            // $temp will hold what we have so far.
            $temp = $tokens[$i] . $delimiter;
            // save memory..
            $tokens[$i] = "";

            // Do we have a complete statement yet?
            $complete_stmt = false;

            for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
            {
            // This is the total number of single quotes in the token.
            $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
            // Counts single quotes that are preceded by an odd number of backslashes,
            // which means they're escaped quotes.
            $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);

            $unescaped_quotes = $total_quotes - $escaped_quotes;

            if (($unescaped_quotes % 2) == 1)
            {
                // odd number of unescaped quotes. In combination with the previous incomplete
                // statement(s), we now have a complete statement. (2 odds always make an even)
                $output[] = $temp . $tokens[$j];

                // save memory.
                $tokens[$j] = "";
                $temp = "";

                // exit the loop.
                $complete_stmt = true;
                // make sure the outer loop continues at the right point.
                $i = $j;
            }
            else
            {
                // even number of unescaped quotes. We still don't have a complete statement.
                // (1 odd and 1 even always make an odd)
                $temp .= $tokens[$j] . $delimiter;
                // save memory.
                $tokens[$j] = "";
            }

            } // for..
        } // else
    }
}

return $output;
}

$dbms_schema = 'yourfile.sql';

$sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema)) or die('problem ');
$sql_query = remove_remarks($sql_query);
$sql_query = split_sql_file($sql_query, ';');

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'database_name';

// mysql_* is deprecated, prefer using mysqli_* instead
// mysql_connect($host,$user,$pass) or die('error connection');
// mysql_select_db($db) or die('error database selection');
$connection = mysqli_connect($host,$user,$pass) or die('error connection');
mysqli_select_db($connection, $db) or die('error database selection');

$i=1;
foreach($sql_query as $sql){
    echo $i++;
    echo "<br />";
    // mysql_* is deprecated, prefer using mysqli_* instead
    // mysql_query($sql) or die('error in query');
    mysqli_query($connection, $sql) or die('error in query');
}

Answer 3

Ich habe das Gefühl, dass alle, die hier auf diese Frage geantwortet haben, nicht wissen, wie es ist, ein Webanwendungsentwickler zu sein, der anderen erlaubt, die Anwendung auf ihren eigenen Servern zu installieren. Insbesondere bei Shared Hosting ist es nicht möglich, SQL wie die bereits erwähnte "LOAD DATA"-Abfrage zu verwenden. Die meisten Shared Hosts erlauben auch nicht die Verwendung von shell_exec.

Nun, um die OP zu beantworten, ist es am besten, eine PHP-Datei zu erstellen, die Ihre Abfragen in einer Variable enthält, und diese einfach auszuführen. Wenn du entschlossen bist, .sql-Dateien zu parsen, solltest du dir phpMyAdmin ansehen und dir ein paar Ideen holen, wie man auf diese Weise Daten aus .sql-Dateien herausbekommt. Schauen Sie sich andere Webanwendungen an, die Installationsprogramme haben, und Sie werden sehen, dass sie statt .sql-Dateien für ihre Abfragen zu verwenden, diese einfach in PHP-Dateien verpacken und jede Zeichenkette durch mysql_query oder was auch immer sie tun müssen, laufen lassen.

Answer 4

Die einfachste Lösung ist die Verwendung von shell_exec(), um den mysql-Client mit dem SQL-Skript als Eingabe auszuführen. Das läuft zwar etwas langsamer, weil es sich gabeln muss, aber Sie können den Code in ein paar Minuten schreiben und sich dann wieder mit etwas Nützlichem beschäftigen. Das Schreiben eines PHP-Skripts zur Ausführung eines beliebigen SQL-Skripts könnte Wochen dauern.

Die Unterstützung von SQL-Skripten ist komplexer als das, was hier beschrieben wird, es sei denn, Sie sind sicher, dass Ihr Skript nur eine Teilmenge der Funktionalität von Skripten enthält. Im Folgenden finden Sie einige Beispiele für Dinge, die in einem gewöhnlichen SQL-Skript vorkommen können und die es komplex machen, ein Skript zu programmieren, das es Zeile für Zeile interpretiert.

-- Comment lines cannot be prepared as statements
-- This is a MySQL client tool builtin command.  
-- It cannot be prepared or executed by server.
USE testdb;

-- This is a multi-line statement.
CREATE TABLE foo (
  string VARCHAR(100)
);

-- This statement is not supported as a prepared statement.
LOAD DATA INFILE 'datafile.txt' INTO TABLE foo;

-- This statement is not terminated with a semicolon.
DELIMITER //

-- This multi-line statement contains a semicolon 
-- but not as the statement terminator.
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM foo;
END
// 

Wenn Sie nur eine Teilmenge von SQL-Skripten unterstützen und einige Eckfälle wie die oben genannten ausschließen, ist es relativ einfach, ein PHP-Skript zu schreiben, das eine Datei liest und die SQL-Anweisungen in der Datei ausführt. Wenn Sie aber jedes gültige SQL-Skript unterstützen wollen, ist das sehr viel komplexer.

---

Siehe auch meine Antworten auf diese Fragen:

• Ausführen von MySQL *.sql-Dateien in PHP
• Ist es möglich, ein Sql-Skript aus einer gespeicherten Prozedur in einem anderen Sql-Skript aufzurufen?
• PHP: Mehrere SQL-Abfragen in einer mysql_query-Anweisung

Answer 5

In meinen Projekten habe ich die folgende Lösung verwendet:

<?php

/**
 * Import SQL from file
 *
 * @param string path to sql file
 */
function sqlImport($file)
{

    $delimiter = ';';
    $file = fopen($file, 'r');
    $isFirstRow = true;
    $isMultiLineComment = false;
    $sql = '';

    while (!feof($file)) {

        $row = fgets($file);

        // remove BOM for utf-8 encoded file
        if ($isFirstRow) {
            $row = preg_replace('/^\x{EF}\x{BB}\x{BF}/', '', $row);
            $isFirstRow = false;
        }

        // 1. ignore empty string and comment row
        if (trim($row) == '' || preg_match('/^\s*(#|--\s)/sUi', $row)) {
            continue;
        }

        // 2. clear comments
        $row = trim(clearSQL($row, $isMultiLineComment));

        // 3. parse delimiter row
        if (preg_match('/^DELIMITER\s+[^ ]+/sUi', $row)) {
            $delimiter = preg_replace('/^DELIMITER\s+([^ ]+)$/sUi', '$1', $row);
            continue;
        }

        // 4. separate sql queries by delimiter
        $offset = 0;
        while (strpos($row, $delimiter, $offset) !== false) {
            $delimiterOffset = strpos($row, $delimiter, $offset);
            if (isQuoted($delimiterOffset, $row)) {
                $offset = $delimiterOffset + strlen($delimiter);
            } else {
                $sql = trim($sql . ' ' . trim(substr($row, 0, $delimiterOffset)));
                query($sql);

                $row = substr($row, $delimiterOffset + strlen($delimiter));
                $offset = 0;
                $sql = '';
            }
        }
        $sql = trim($sql . ' ' . $row);
    }
    if (strlen($sql) > 0) {
        query($row);
    }

    fclose($file);
}

/**
 * Remove comments from sql
 *
 * @param string sql
 * @param boolean is multicomment line
 * @return string
 */
function clearSQL($sql, &$isMultiComment)
{
    if ($isMultiComment) {
        if (preg_match('#\*/#sUi', $sql)) {
            $sql = preg_replace('#^.*\*/\s*#sUi', '', $sql);
            $isMultiComment = false;
        } else {
            $sql = '';
        }
        if(trim($sql) == ''){
            return $sql;
        }
    }

    $offset = 0;
    while (preg_match('{--\s|#|/\*[^!]}sUi', $sql, $matched, PREG_OFFSET_CAPTURE, $offset)) {
        list($comment, $foundOn) = $matched[0];
        if (isQuoted($foundOn, $sql)) {
            $offset = $foundOn + strlen($comment);
        } else {
            if (substr($comment, 0, 2) == '/*') {
                $closedOn = strpos($sql, '*/', $foundOn);
                if ($closedOn !== false) {
                    $sql = substr($sql, 0, $foundOn) . substr($sql, $closedOn + 2);
                } else {
                    $sql = substr($sql, 0, $foundOn);
                    $isMultiComment = true;
                }
            } else {
                $sql = substr($sql, 0, $foundOn);
                break;
            }
        }
    }
    return $sql;
}

/**
 * Check if "offset" position is quoted
 *
 * @param int $offset
 * @param string $text
 * @return boolean
 */
function isQuoted($offset, $text)
{
    if ($offset > strlen($text))
        $offset = strlen($text);

    $isQuoted = false;
    for ($i = 0; $i < $offset; $i++) {
        if ($text[$i] == "'")
            $isQuoted = !$isQuoted;
        if ($text[$i] == "\\" && $isQuoted)
            $i++;
    }
    return $isQuoted;
}

function query($sql)
{
    global $mysqli;
    //echo '#<strong>SQL CODE TO RUN:</strong><br>' . htmlspecialchars($sql) . ';<br><br>';
    if (!$query = $mysqli->query($sql)) {
        throw new Exception("Cannot execute request to the database {$sql}: " . $mysqli->error);
    }
}

set_time_limit(0);

$mysqli = new mysqli('localhost', 'root', '', 'test');
$mysqli->set_charset("utf8");

header('Content-Type: text/html;charset=utf-8');
sqlImport('import.sql');

echo "Peak MB: ", memory_get_peak_usage(true)/1024/1024;

Bei einer Test-SQL-Datei (41 MB) wird der Speicher am stärksten beansprucht: 3.25Mb