问SQL文件的多查询PHP解析器

EN

//DB connection values
$sHost = "localhost";
$sName = "test";
$sUser = "";
$sPass = "";
$sPort = 3307;


//The following could be retrieved using file_get_contents, or a file streamer
$sFileContents = <<

//Variant 1: Run a local SQL file. Since we stored our SQL contents in a 
//variable (could have been retrieved before using eg. file_get_contents), 
//we need to temporarily create a file for this

$sTempFile = tempnam(sys_get_temp_dir(), 'Sql');

//Create the temp file
if(!file_put_contents($sTempFile, $sFileContents)) {
    trigger_error("Failed to create temporary file", E_USER_ERROR);
}

//Assemble the command
$sCommand = 'mysql'
        . ' --host=' . $sHost
        . ' --port=' . $sPort
        . ' --user=' . $sUser
        . ' --password=' . $sPass
        . ' --database=' . $sName
        . ' --execute="SOURCE ' . $sTempFile . '"'
                ;
$sOutput = shell_exec($sCommand);

//Cleanup: remove the temp file
if(!unlink($sTempFile)) {
    trigger_error("Failed to remove temporary file", E_USER_ERROR);
}

//Variant 2: Run a parser

//Connect to the database
$rMysqlI = new mysqli("localhost", "", "", "test", $sPort);
if ($rMysqlI->connect_errno) {
    trigger_error("Failed to connect to MySQL: (" . $rMysqlI->connect_errno . ") " . $rMysqlI->connect_error, E_USER_ERROR);
}



//START_OF_PARSER

$iCur = 0;            //Current character pointer inside the SQL content
$iInside = 0;         //The context, in which the pointer is currently located (is the pointer inside a 
                      //comment, an SQL query, or deeper into an SQL query value?)
$sBuffer = "";        //The buffer of the next individual query
$aQueries = array();  //The list of queries
while($iCur < strlen($sFileContents)) {

    switch ($iInside) {
        case 0: //Inside query-context
            //Change context: Comments beginning with --
            if(substr($sFileContents, $iCur, 2) === "--") {
                $iCur++;
                $iInside = 2;

                //Change context: Comments beginning with /*
            } elseif(substr($sFileContents, $iCur, 2) === "/*") {
                $iCur++;
                $iInside = 3;

                //Change context: Comments beginning with #
            } elseif(substr($sFileContents, $iCur, 1) === "#") {
                $iInside = 2;

                //Separator for a new query
            } elseif(substr($sFileContents, $iCur, 1) === ";") {
                $aQueries[] = trim($sBuffer); //$sBuffer;  //Add current buffer to a unique array query item
                $sBuffer = "";  //Start a new buffer

                //Change context: query values opened with '
            } elseif(substr($sFileContents, $iCur, 1) === "'") {
                $sBuffer .= substr($sFileContents, $iCur, 1);
                $iInside = 1;

                //Change context: query values opened with "
            } elseif(substr($sFileContents, $iCur, 1) === '"') {
                $sBuffer .= substr($sFileContents, $iCur, 1);
                $iInside = 4;

                //Not a special character
            } else {
                $sBuffer .= substr($sFileContents, $iCur, 1);
            }
            break;

        case 1: //Inside value-context, ending with '

            //Escaping character found within the query-value
            if(substr($sFileContents, $iCur, 1) === "\\") {
                $sBuffer .= substr($sFileContents, $iCur, 2);
                $iCur++;  //Skip next char

                //The ending character for the query-value is found
            } elseif(substr($sFileContents, $iCur, 1) === "'") {
                $sBuffer .= substr($sFileContents, $iCur, 1);
                $iInside = 0;

                //Not a special character
            } else {
                $sBuffer .= substr($sFileContents, $iCur, 1);
            }
            break;

        case 4: //Inside value-context, ending with "

            //Escaping character found within the query-value
            if(substr($sFileContents, $iCur, 1) === "\\") {
                $sBuffer .= substr($sFileContents, $iCur, 2);
                $iCur = $iCur + 1;  //Skip next char

                //The ending character for the query-value is found
            } elseif(substr($sFileContents, $iCur, 1) === '"') {
                $sBuffer .= substr($sFileContents, $iCur, 1);
                $iInside = 0;

                //Not a special character
            } else {
                $sBuffer .= substr($sFileContents, $iCur, 1);
            }
            break;

        case 2: //Inside comment-context, ending with newline

            //A two-character newline is found, signalling the end of the comment
            if(substr($sFileContents, $iCur, 2) === "\r\n") {
                $iCur++;
                $iInside = 0;

                //A single-character newline is found, signalling the end of the comment
            } elseif(substr($sFileContents, $iCur, 1) === "\n" || substr($sFileContents, $iCur, 1) === "\r") {
                $iInside = 0;
            }
            break;

        case 3: //Inside comment-context, ending with */

            //A two-character */ is found, signalling the end of the comment
            if(substr($sFileContents, $iCur, 2) === "*/") {
                $iCur++;
                $iInside = 0;
            }
            break;

        default:
            break;
    }
    $iCur++;
}

//END_OF_PARSER

//Preview our results
foreach($aQueries as $sQuery) {
    if (!$rMysqlI->query($sQuery)) {
        echo "ERROR \"{$sQuery}\": (" . $rMysqlI->errno . ") " . $rMysqlI->error . "
", E_USER_ERROR;
    } else {
        echo "SUCCESS \"{$sQuery}\"
", E_USER_ERROR;
    }
}