PHP SQL Import Class to read an SQL dump file and run it as SQL statements over a PDO connection

sql_import_class.php

<?php
/**
 * Sql_import class.
 * 
 * Reads a SQL file (e.g. a dump from phpmyadmin) and executes it programmatically.
 * 
 * Handles procedures, triggers etc. and can load large dumps. Great for
 * installer scripts to load up a db schema and populate with initial data.
 * 
 * Came from: http://stackoverflow.com/questions/147821/loading-sql-files-from-within-php
 * by user "gromo". Turned into a class, improved a bit and modifed to use PDO.
 * 
 */
class Sql_import{

    protected $db_conn;
    protected $test; //if test mode dumps SQL to screen.
    public $statements = []; //if no db connection saves statements to array
    public $statement_count;
    
    public function __construct(PDO $db_conn, $test = false){
        $this->db_conn = $db_conn;
        $this->test = $test;
    }
    
    /**
     * Import SQL from file
     *
     * @param string path to sql file
     */
    public function sqlImport($filename)
    {
        $this->statements = [];
        $this->statement_count = 0;
        
        $delimiter = ';';
        $file = @fopen($filename, 'r');
        if(!$file){
            throw new Exception("Error: Cannot open file {$filename}\n");
        }
        $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($this->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 ($this->isQuoted($delimiterOffset, $row)) {
                    $offset = $delimiterOffset + strlen($delimiter);
                } else {
                    $sql = trim($sql . ' ' . trim(substr($row, 0, $delimiterOffset)));
                    $this->query($sql);

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

        fclose($file);
    }
    
    /**
     * Remove comments from sql
     *
     * @param string sql
     * @param boolean is multicomment line
     * @return string
     */
    protected 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 ($this->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
     */
    protected 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;
    }
    
    protected function query($sql) {
        $this->statement_count++;
    
        if($this->test){
            echo '#<strong style="color:blue">SQL CODE TO RUN ('.$this->statement_count.'):</strong><br>' . htmlspecialchars($sql) . ';<br><br>';
        }else{
            if($this->db_conn){
                $this->db_conn->exec($sql); //PDO
            }else{
                $this->statements[] = $sql;
            }
        }
    }
    
} //eoc

//Settings to run long jobs
set_time_limit(600);
ini_set('memory_limit','500M');
error_reporting(E_ALL);
ini_set("display_errors", 1);

$startTime = time();

define('DB_SERVER', "localhost");
define('DB_USERNAME', "xxxx");
define('DB_PASSWORD', "xxxxxx");
define('DB_NAME', "test");

try{
    $db = @new PDO("mysql:host=".DB_SERVER.";port=3306;dbname=".DB_NAME.";charset=utf8", DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
    echo "DB open failed: " . $e->getMessage();
    exit();
}

// -------------------------------------------------------------------------- //

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

$import = new Sql_import($db, false);

try{
    $import->sqlImport('test.sql');
 
}catch(Exception $e){
    echo "SQL import failed: " . $e->getMessage();
    exit();
    
}

$endTime = time();

echo "Import SQL to db took h:m:s " . gmdate('H:i:s', $endTime - $startTime) . ".<br>";
echo "Peak MB: ", memory_get_peak_usage(true)/1024/1024, "<br>";
echo $import->statement_count . " SQL statements executed.<br>";

exit();

/* end */