diff options
Diffstat (limited to 'libraries/plugins/import/ImportSql.class.php')
-rw-r--r-- | libraries/plugins/import/ImportSql.class.php | 746 |
1 files changed, 409 insertions, 337 deletions
diff --git a/libraries/plugins/import/ImportSql.class.php b/libraries/plugins/import/ImportSql.class.php index 02a30f94b6..70662efbe6 100644 --- a/libraries/plugins/import/ImportSql.class.php +++ b/libraries/plugins/import/ImportSql.class.php @@ -21,6 +21,47 @@ require_once 'libraries/plugins/ImportPlugin.class.php'; */ class ImportSql extends ImportPlugin { + const BIG_VALUE = 2147483647; + const READ_MB_FALSE = 0; + const READ_MB_TRUE = 1; + + private $_delimiter; + private $_delimiterLength; + private $_delimiterPosition = false; + private $_queryBeginPosition = 0; + + private $_isInString = false; + + private $_quote = null; + + private $_isInComment = false; + + private $_openingComment = null; + + private $_isInDelimiter = false; + + private $_delimiterKeyword = 'DELIMITER '; + + private $_readMb = self::READ_MB_FALSE; + + private $_data = null; + private $_dataLength = 0; + + //@todo Move this part in string functions definition file. + private $_stringFunctions = array( + self::READ_MB_FALSE => array( + 'substr' => 'substr', + 'strlen' => 'strlen', + 'strpos' => 'strpos', + ), + self::READ_MB_TRUE => array( + 'substr' => 'mb_substr', + 'strlen' => 'mb_strlen', + 'strpos' => 'mb_strpos', + ), + ); + private $_stringFctToUse = false; + /** * Constructor */ @@ -91,6 +132,13 @@ class ImportSql extends ImportPlugin ); $generalOptions->addProperty($leaf); + $leaf = new BoolPropertyItem(); + $leaf->setName("read_as_multibytes"); + $leaf->setText( + __('Read as multibytes') + ); + $generalOptions->addProperty($leaf); + // add the main group to the root group $importSpecificOptions->addProperty($generalOptions); // set the options for the import plugin property item @@ -101,6 +149,185 @@ class ImportSql extends ImportPlugin } /** + * Look for end of string + * + * @return bool End of string found + */ + private function _searchStringEnd() + { + //Search for closing quote + $posClosingString = $this->_stringFctToUse['strpos']( + $this->_data, $this->_quote, $this->_delimiterPosition + ); + + if (false === $posClosingString) { + return false; + } + + //Quotes escaped by quote will be considered as 2 consecutive strings + //and won't pass in this loop. + $posEscape = $posClosingString-1; + while ($this->_stringFctToUse['substr']($this->_data, $posEscape, 1) == '\\' + ) { + $posEscape--; + } + + // Odd count means it was escaped + $quoteEscaped = (((($posClosingString - 1) - $posEscape) % 2) === 1); + + //Move after the escaped guote. + $this->_delimiterPosition = $posClosingString + 1; + + if ($quoteEscaped) { + return true; + } + + $this->_isInString = false; + $this->_quote = null; + return true; + } + + /** + * Return the position of first SQL delimiter or false if no SQL delimiter found. + * + * @return int|bool Delimiter position or false if no delimiter found + */ + private function _findDelimiterPosition() + { + $firstSearchChar = null; + $firstSqlDelimiter = null; + $matches = null; + + /* while not at end of line */ + while ($this->_delimiterPosition < $this->_dataLength) { + if ($this->_isInString) { + if (false === $this->_searchStringEnd()) { + return false; + } + + continue; + } + + if ($this->_isInComment) { + if (in_array($this->_openingComment, array('#', '-- '))) { + $posClosingComment = $this->_stringFctToUse['strpos']( + $this->_data, + "\n", + $this->_delimiterPosition + ); + if (false === $posClosingComment) { + return false; + } + //Move after the end of the line. + $this->_delimiterPosition = $posClosingComment + 1; + $this->_isInComment = false; + $this->_openingComment = null; + } elseif ('/*' === $this->_openingComment) { + //Search for closing comment + $posClosingComment = $this->_stringFctToUse['strpos']( + $this->_data, + '*/', + $this->_delimiterPosition + ); + if (false === $posClosingComment) { + return false; + } + //Move after closing comment. + $this->_delimiterPosition = $posClosingComment + 2; + $this->_isInComment = false; + $this->_openingComment = null; + } else { + //We shouldn't be able to come here. + //throw new Exception('Unknown case.'); + break; + } + continue; + } + + if ($this->_isInDelimiter) { + //Search for new line. + if (!preg_match( + "/^(.*)\n/", + $this->_stringFctToUse['substr']( + $this->_data, + $this->_delimiterPosition + ), + $matches, + PREG_OFFSET_CAPTURE + )) { + return false; + } + + $this->_setDelimiter($matches[1][0]); + //Start after delimiter and new line. + $this->_queryBeginPosition = $this->_delimiterPosition + + $matches[1][1] + $this->_delimiterLength + 1; + $this->_delimiterPosition = $this->_queryBeginPosition; + $this->_isInDelimiter = false; + $firstSqlDelimiter = null; + $firstSearchChar = null; + continue; + } + + list($matches, $firstSearchChar) = $this->_searchSpecialChars( + $this->_data, + $firstSearchChar, + $matches + ); + + $firstSqlDelimiter = $this->_searchSqlDelimiter( + $this->_data, + $firstSqlDelimiter + ); + + if (false === $firstSqlDelimiter && false === $firstSearchChar) { + return false; + } + + //If first char is delimiter. + if (false === $firstSearchChar + || (false !== $firstSqlDelimiter && $firstSqlDelimiter < $firstSearchChar) + ) { + $this->_delimiterPosition = $firstSqlDelimiter; + return true; + } + + //Else first char is result of preg_match. + + $specialChars = $matches[1][0]; + + //If string is opened. + if (in_array($specialChars, array('\'', '"', '`'))) { + $this->_isInString = true; + $this->_quote = $specialChars; + //Move after quote. + $this->_delimiterPosition = $firstSearchChar + 1; + continue; + } + + //If comment is opened. + if (in_array($specialChars, array('#', '-- ', '/*'))) { + $this->_isInComment = true; + $this->_openingComment = $specialChars; + //Move after comment opening. + $this->_delimiterPosition = $firstSearchChar + + $this->_stringFctToUse['strlen']($specialChars); + continue; + } + + //If DELIMITER is found. + if ($specialChars === $this->_delimiterKeyword) { + $this->_isInDelimiter = true; + $this->_delimiterPosition = $firstSearchChar + + $this->_stringFctToUse['strlen']($specialChars); + continue; + } + } + + return false; + } + + /** * Handles the whole import logic * * @param array &$sql_data 2-element array with sql data @@ -111,42 +338,23 @@ class ImportSql extends ImportPlugin { global $error, $timeout_passed; - /** @var PMA_String $pmaString */ - $pmaString = $GLOBALS['PMA_String']; - - $buffer = ''; - // Defaults for parser - $sql = ''; - $start_pos = 0; - $posInQueryString = 0; - $len= 0; - $big_value = 2147483647; - // include the space because it's mandatory - $delimiter_keyword = 'DELIMITER '; - $length_of_delimiter_keyword = $pmaString->strlen($delimiter_keyword); + //Manage multibytes or not + if (isset($_REQUEST['sql_read_as_multibytes'])) { + $this->_readMb = self::READ_MB_TRUE; + } + $this->_stringFctToUse = $this->_stringFunctions[$this->_readMb]; if (isset($_POST['sql_delimiter'])) { - $sql_delimiter = $_POST['sql_delimiter']; + $this->_setDelimiter($_POST['sql_delimiter']); } else { - $sql_delimiter = ';'; + $this->_setDelimiter(';'); } // Handle compatibility options - $sql_modes = array(); - if (isset($_REQUEST['sql_compatibility']) - && 'NONE' != $_REQUEST['sql_compatibility'] - ) { - $sql_modes[] = $_REQUEST['sql_compatibility']; - } - if (isset($_REQUEST['sql_no_auto_value_on_zero'])) { - $sql_modes[] = 'NO_AUTO_VALUE_ON_ZERO'; - } - if (count($sql_modes) > 0) { - $GLOBALS['dbi']->tryQuery( - 'SET SQL_MODE="' . implode(',', $sql_modes) . '"' - ); - } - unset($sql_modes); + $this->_setSQLMode($GLOBALS['dbi'], $_REQUEST); + + //Initialise data. + $this->_setData(null); /** * will be set in PMA_importGetNextChunk() @@ -154,332 +362,196 @@ class ImportSql extends ImportPlugin * @global boolean $GLOBALS['finished'] */ $GLOBALS['finished'] = false; + $delimiterFound = false; - while (! ($GLOBALS['finished'] && $posInQueryString >= $len) - && ! $error - && ! $timeout_passed - ) { - $data = PMA_importGetNextChunk(); - if ($data === false) { - // subtract data we didn't handle yet and stop processing - $GLOBALS['offset'] -= $pmaString->strlen($buffer); - break; - } elseif ($data === true) { - // Handle rest of buffer - } else { - // Append new data to buffer - $buffer .= $data; - // free memory - unset($data); - // Do not parse string when we're not at the end - // and don't have ; inside - if ($pmaString->strpos($buffer, $sql_delimiter, $posInQueryString) === false - && ! $GLOBALS['finished'] - ) { - continue; + while (!$error && !$timeout_passed) { + if (false === $delimiterFound) { + $newData = PMA_importGetNextChunk(200); + if ($newData === false) { + // subtract data we didn't handle yet and stop processing + $GLOBALS['offset'] -= $this->_dataLength; + break; } - } - // Convert CR (but not CRLF) to LF otherwise all queries - // may not get executed on some platforms - $buffer = preg_replace("/\r($|[^\n])/", "\n$1", $buffer); - - // Current length of our buffer - $len = $pmaString->strlen($buffer); - - // Grab some SQL queries out of it - while ($posInQueryString < $len) { - $found_delimiter = false; - // Find first interesting character - $old_i = $posInQueryString; - // this is about 7 times faster that looking for each sequence i - // one by one with strpos() - $posPattern = $pmaString->pregStrpos( - '/(\'|"|#|-- |\/\*|`|(?i)(?<![A-Z0-9_])' - . $delimiter_keyword . ')/', - $buffer, - $posInQueryString - ); - if (false !== $posPattern) { - // in $matches, index 0 contains the match for the complete - // expression but we don't use it - $first_position = $posPattern; - } else { - $first_position = $big_value; + if ($newData === true) { + $GLOBALS['finished'] = true; + break; } - // the cost of doing this one with preg_match() would be too high - $first_sql_delimiter = $pmaString->strpos( - $buffer, - $sql_delimiter, - $posInQueryString - ); - if ($first_sql_delimiter === false) { - $first_sql_delimiter = $big_value; - } else { - $found_delimiter = true; - } + //Convert CR (but not CRLF) to LF otherwise all queries + //may not get executed on some platforms + $this->_addData(preg_replace("/\r($|[^\n])/", "\n$1", $newData)); + unset($newData); + } - // set $i to the position of the first quote, - // comment.start or delimiter found - $posInQueryString = min($first_position, $first_sql_delimiter); + //Find quotes, comments, delimiter definition or delimiter itself. + $delimiterFound = $this->_findDelimiterPosition(); - if ($posInQueryString == $big_value) { - // none of the above was found in the string + //If no delimiter found, restart and get more data. + if (false === $delimiterFound) { + continue; + } - $posInQueryString = $old_i; - if (! $GLOBALS['finished']) { - break; - } - // at the end there might be some whitespace... - if (trim($buffer) == '') { - $buffer = ''; - $len = 0; - break; - } - // We hit end of query, go there! - $posInQueryString = $pmaString->strlen($buffer) - 1; - } + PMA_importRunQuery( + $this->_stringFctToUse['substr']( + $this->_data, + $this->_queryBeginPosition, + $this->_delimiterPosition - $this->_queryBeginPosition + ), //Query to execute + $this->_stringFctToUse['substr']( + $this->_data, + 0, + $this->_delimiterPosition + $this->_delimiterLength + ), //Query to display + false, + $sql_data + ); - // Grab current character - //$ch = $buffer[$i]; //Don't use this syntax, because of UTF8 strings - $ch = $pmaString->substr($buffer, $posInQueryString, 1); - - // Quotes - if ($pmaString->strpos('\'"`', $ch) !== false) { - $quote = $ch; - $endq = false; - while (! $endq) { - // Find next quote - $posQuote = $pmaString - ->strpos($buffer, $quote, $posInQueryString + 1); - /* - * Behave same as MySQL and accept end of query as end - * of backtick. - * I know this is sick, but MySQL behaves like this: - * - * SELECT * FROM `table - * - * is treated like - * - * SELECT * FROM `table` - */ - if ($posQuote === false && $quote == '`' - && $found_delimiter - ) { - $posQuote = $first_sql_delimiter - 1; - } elseif ($posQuote === false) {// No quote? Too short string - // We hit end of string => unclosed quote, - // but we handle it as end of query - list($endq, $posInQueryString) = $this - ->getEndQuoteAndPos($len, $endq, $posInQueryString); - $found_delimiter = false; - break; - } - // Was not the quote escaped? - $posEscape = $posQuote - 1; - while ($pmaString->substr($buffer, $posEscape, 1) == '\\') { - $posEscape--; - } - // Even count means it was not escaped - $endq = (((($posQuote - 1) - $posEscape) % 2) == 0); - // Skip the string - $posInQueryString = $posQuote; - - if ($first_sql_delimiter < $posQuote) { - $found_delimiter = false; - } - } - if (! $endq) { - break; - } - $posInQueryString++; - // Aren't we at the end? - if ($GLOBALS['finished'] && $posInQueryString == $len) { - $posInQueryString--; - } else { - continue; - } - } + $this->_setData( + $this->_stringFctToUse['substr']( + $this->_data, + $this->_delimiterPosition + $this->_delimiterLength + ) + ); + } - // Not enough data to decide - if ((($posInQueryString == ($len - 1) && ($ch == '-' || $ch == '/')) - || ($posInQueryString == ($len - 2) && (($ch == '-' - && $pmaString->substr($buffer, $posInQueryString + 1, 1) == '-') - || ($ch == '/' - && $pmaString->substr($buffer, $posInQueryString + 1, 1) == '*')))) - && ! $GLOBALS['finished'] - ) { - break; - } + //Commit any possible data in buffers + PMA_importRunQuery('', $this->_data, false, $sql_data); + PMA_importRunQuery('', '', false, $sql_data); + } - // Comments - if ($ch == '#' - || ($posInQueryString < ($len - 1) && $ch == '-' - && $pmaString->substr($buffer, $posInQueryString + 1, 1) == '-' - && (($posInQueryString < ($len - 2) - && $pmaString->substr($buffer, $posInQueryString + 2, 1) <= ' ') - || ($posInQueryString == ($len - 1) && $GLOBALS['finished']))) - || ($posInQueryString < ($len - 1) && $ch == '/' - && $pmaString->substr($buffer, $posInQueryString + 1, 1) == '*') - ) { - // Copy current string to SQL - if ($start_pos != $posInQueryString) { - $sql .= $pmaString->substr( - $buffer, - $start_pos, - $posInQueryString - $start_pos - ); - } - // Skip the rest - $start_of_comment = $posInQueryString; - // do not use PHP_EOL here instead of "\n", because the export - // file might have been produced on a different system - $posInQueryString = $pmaString->strpos( - $buffer, - $ch == '/' ? '*/' : "\n", - $posInQueryString - ); - // didn't we hit end of string? - if ($posInQueryString === false) { - if ($GLOBALS['finished']) { - $posInQueryString = $len - 1; - } else { - break; - } - } - // Skip * - if ($ch == '/') { - $posInQueryString++; - } - // Skip last char - $posInQueryString++; - // We need to send the comment part in case we are defining - // a procedure or function and comments in it are valuable - $sql .= $pmaString->substr( - $buffer, - $start_of_comment, - $posInQueryString - $start_of_comment - ); - // Next query part will start here - $start_pos = $posInQueryString; - // Aren't we at the end? - if ($posInQueryString == $len) { - $posInQueryString--; - } else { - continue; - } - } - // Change delimiter, if redefined, and skip it - // (don't send to server!) - if (($posInQueryString + $length_of_delimiter_keyword < $len) - && $pmaString->strtoupper( - $pmaString->substr($buffer, $posInQueryString, $length_of_delimiter_keyword) - ) == $delimiter_keyword - ) { - // look for EOL on the character immediately after 'DELIMITER ' - // (see previous comment about PHP_EOL) - $new_line_pos = $pmaString->strpos( - $buffer, - "\n", - $posInQueryString + $length_of_delimiter_keyword - ); - // it might happen that there is no EOL - if (false === $new_line_pos) { - $new_line_pos = $len; - } - $sql_delimiter = $pmaString->substr( - $buffer, - $posInQueryString + $length_of_delimiter_keyword, - $new_line_pos - $posInQueryString - $length_of_delimiter_keyword - ); - $posInQueryString = $new_line_pos + 1; - // Next query part will start here - $start_pos = $posInQueryString; - continue; - } + /** + * Handle compatibility options + * + * @param PMA_DatabaseInterface $dbi Database interface + * @param array $request Request array + * + * @return void + */ + private function _setSQLMode($dbi, $request) + { + $sql_modes = array(); + if (isset($request['sql_compatibility']) + && 'NONE' != $request['sql_compatibility'] + ) { + $sql_modes[] = $request['sql_compatibility']; + } + if (isset($request['sql_no_auto_value_on_zero'])) { + $sql_modes[] = 'NO_AUTO_VALUE_ON_ZERO'; + } + if (count($sql_modes) > 0) { + $dbi->tryQuery( + 'SET SQL_MODE="' . implode(',', $sql_modes) . '"' + ); + } + } - // End of SQL - if ($found_delimiter - || ($GLOBALS['finished'] - && ($posInQueryString == $len - 1)) - ) { - $tmp_sql = $sql; - if ($start_pos < $len) { - $length_to_grab = $posInQueryString - $start_pos; - - if (! $found_delimiter) { - $length_to_grab++; - } - $tmp_sql .= $pmaString->substr( - $buffer, - $start_pos, - $length_to_grab - ); - unset($length_to_grab); - } - // Do not try to execute empty SQL - if (! preg_match('/^([\s]*;)*$/', trim($tmp_sql))) { - $sql = $tmp_sql; - PMA_importRunQuery( - $sql, - $pmaString->substr( - $buffer, - 0, - $posInQueryString + $pmaString->strlen($sql_delimiter) - ), - false, - $sql_data - ); - $buffer = $pmaString->substr( - $buffer, - $posInQueryString + $pmaString->strlen($sql_delimiter) - ); - // Reset parser: - $len = $pmaString->strlen($buffer); - $sql = ''; - $posInQueryString = 0; - $start_pos = 0; - // Any chance we will get a complete query? - //if ((strpos($buffer, ';') === false) - //&& ! $GLOBALS['finished']) { - if ($pmaString->strpos($buffer, $sql_delimiter) === false - && ! $GLOBALS['finished'] - ) { - break; - } - } else { - $posInQueryString++; - $start_pos = $posInQueryString; - } - } - } // End of parser loop - } // End of import loop - // Commit any possible data in buffers - PMA_importRunQuery( - '', - $pmaString->substr($buffer, 0, $len), - false, - $sql_data - ); - PMA_importRunQuery('', '', false, $sql_data); + /** + * Look for special chars: comment, string or DELIMITER + * + * @param string $data Data to parse + * @param int $firstSearchChar First found char position + * @param array $matches Special chars found in $data + * + * @return array 0: matches, 1: first found char position + */ + private function _searchSpecialChars( + $data, + $firstSearchChar, + $matches + ) { + //Don't look for a string/comment/"DELIMITER" if not found previously + //or if it's still after current position. + if (null === $firstSearchChar + || (false !== $firstSearchChar && $firstSearchChar < $this->_delimiterPosition) + ) { + $bFind = preg_match( + '/(\'|"|#|-- |\/\*|`|(?i)(?<![A-Z0-9_])' + . $this->_delimiterKeyword . ')/', + $this->_stringFctToUse['substr']($data, $this->_delimiterPosition), + $matches, + PREG_OFFSET_CAPTURE + ); + + if (1 === $bFind) { + $firstSearchChar = $matches[1][1] + $this->_delimiterPosition; + } else { + $firstSearchChar = false; + } + } + return array($matches, $firstSearchChar); } /** - * Get end quote and position + * Look for SQL delimiter * - * @param int $len Length - * @param bool $endq End quote - * @param int $position Position + * @param string $data Data to parse + * @param int $firstSqlDelimiter First found char position * - * @return array End quote, position + * @return int */ - protected function getEndQuoteAndPos($len, $endq, $position) + private function _searchSqlDelimiter($data, $firstSqlDelimiter) { - if ($GLOBALS['finished']) { - $endq = true; - $position = $len - 1; + //Don't look for the SQL delimiter if not found previously + //or if it's still after current position. + if (null === $firstSqlDelimiter + || (false !== $firstSqlDelimiter && $firstSqlDelimiter < $this->_delimiterPosition) + ) { + // the cost of doing this one with preg_match() would be too high + $firstSqlDelimiter = $this->_stringFctToUse['strpos']( + $data, + $this->_delimiter, + $this->_delimiterPosition + ); } - return array($endq, $position); + + return $firstSqlDelimiter; + } + + /** + * Set new delimiter + * + * @param string $delimiter New delimiter + * + * @return int delimiter length + */ + private function _setDelimiter($delimiter) + { + $this->_delimiter = $delimiter; + $this->_delimiterLength = $this->_stringFctToUse['strlen']($delimiter); + + return $this->_delimiterLength; + } + + /** + * Set data to parse + * + * @param string $data Data to parse + * + * @return int Data length + */ + private function _setData($data) + { + $this->_data = ltrim($data); + $this->_dataLength = $this->_stringFctToUse['strlen']($this->_data); + $this->_queryBeginPosition = 0; + $this->_delimiterPosition = 0; + + return $this->_dataLength; + } + + /** + * Add data to parse + * + * @param string $data Data to add to data to parse + * + * @return int Data length + */ + private function _addData($data) + { + $this->_data .= $data; + $this->_dataLength += $this->_stringFctToUse['strlen']($data); + + return $this->_dataLength; } } |