Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PHP SQL Import Class to read an SQL dump file and run it as SQL statements over a PDO connection
<?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 */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.