PHP – importSQL() und getQueriesFromSQLFile()

Die nachfolgenden Funktionen habe ich im Installationsprogramm einer Webanwendung eingesetzt. Sie sind nützlich, um SQL aus einem Databasedump mittels PHP einzuspielen, wenn man direkt mit PDO arbeitet. Durch die Umstellung des Installationsprogramms auf Doctrine2 werden beide nicht mehr benötigt. Daher archiviere ich beides hier und gebe es zum Reuse unter der GPLv2+ frei.

/**
 * Loads an SQL stream into the database one command at a time.
 *
 * @params $sqlfile The file containing the mysql-dump data.
 * @params $connection Instance of a PDO Connection Object.
 * @return boolean Returns true, if SQL was imported successfully.
 * @throws Exception
 */
function importSQL($sqlfile, $connection)
{
    $queries = getQueriesFromSQLFile($sqlfile);

    foreach($queries as $query)
    {
        try
        {
            $connection->exec($query);
        }
        catch (Exception $e)
        {
            echo $e->getMessage() . "<br /> <p>The sql is: $query</p>";
        }
    }

    return true;
}

/**
 * getQueriesFromSQLFile parses a sql file and extracts all queries
 * for further processing with pdo execute.
 *
 * - strips off all comments, sql notes, empty lines from an sql file
 * - trims white-spaces
 * - filters the sql-string for sql-keywords
 * - replaces the db_prefix
 *
 * @param $file sqlfile
 * @return array Trimmed array of sql queries, ready for insertion into db.
 */
function getQueriesFromSQLFile($sqlfile)
{
    if(is_readable($sqlfile) === false)
    {
        throw new Exception($sqlfile . 'does not exist or is not readable.');
    }

    # read file into array
    $file = file($sqlfile);

    # import file line by line
    # and filter (remove) those lines, beginning with an sql comment token
    $file = array_filter($file,
                    create_function('$line',
                            'return strpos(ltrim($line), "--") !== 0;'));

    # and filter (remove) those lines, beginning with an sql notes token
    $file = array_filter($file,
                    create_function('$line',
                            'return strpos(ltrim($line), "/*") !== 0;'));

    # this is a whitelist of SQL commands, which are allowed to follow a semicolon
    $keywords = array(
        'ALTER', 'CREATE', 'DELETE', 'DROP', 'INSERT',
        'REPLACE', 'SELECT', 'SET', 'TRUNCATE', 'UPDATE', 'USE'
    );

    # create the regular expression for matching the whitelisted keywords
    $regexp = sprintf('/\s*;\s*(?=(%s)\b)/s', implode('|', $keywords));

    # split there
    $splitter = preg_split($regexp, implode("\r\n", $file));

    # remove trailing semicolon or whitespaces
    $splitter = array_map(create_function('$line',
                            'return preg_replace("/[\s;]*$/", "", $line);'),
                          $splitter);

    # replace the default database prefix "your_prefix_"
    $table_prefix = $_POST['config']['database']['prefix'];
    $splitter = preg_replace("/`your_prefix_/", "`$table_prefix", $splitter);

    # remove empty lines
    return array_filter($splitter, create_function('$line', 'return !empty($line);'));
}

Comments are closed.