2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Library that provides common import functions that are used by import plugins
6 * @package PhpMyAdmin-Import
8 if (! defined('PHPMYADMIN')) {
13 * We need to know something about user
15 require_once './libraries/check_user_privileges.lib.php';
18 * We do this check, DROP DATABASE does not need to be confirmed elsewhere
20 define('PMA_CHK_DROP', 1);
23 * Checks whether timeout is getting close
25 * @return boolean true if timeout is close
28 function PMA_checkTimeout()
30 global $timestamp, $maximum_time, $timeout_passed;
31 if ($maximum_time == 0) {
33 } elseif ($timeout_passed) {
35 /* 5 in next row might be too much */
36 } elseif ((time() - $timestamp) > ($maximum_time - 5)) {
37 $timeout_passed = true;
45 * Detects what compression the file uses
47 * @param string $filepath filename to check
49 * @return string MIME type of compression, none for none
52 function PMA_detectCompression($filepath)
54 $file = @fopen
($filepath, 'rb');
58 return PMA_Util
::getCompressionMimeType($file);
62 * Runs query inside import buffer. This is needed to allow displaying
63 * of last SELECT, SHOW or HANDLER results and similar nice stuff.
65 * @param string $sql query to run
66 * @param string $full query to display, this might be commented
67 * @param bool $controluser whether to use control user for queries
68 * @param array &$sql_data SQL parse data storage
73 function PMA_importRunQuery($sql = '', $full = '', $controluser = false,
76 global $import_run_buffer, $go_sql, $complete_query, $display_query,
77 $sql_query, $my_die, $error, $reload,
78 $last_query_with_results, $result, $msg,
79 $skip_queries, $executed_queries, $max_sql_len, $read_multiply,
80 $cfg, $sql_query_disabled, $db, $run_query, $is_superuser;
82 if (!isset($import_run_buffer)) {
83 // Do we have something to push into buffer?
84 $import_run_buffer = PMA_ImportRunQuery_post(
85 $import_run_buffer, $sql, $full
90 // Should we skip something?
91 if ($skip_queries > 0) {
93 // Do we have something to push into buffer?
94 $import_run_buffer = PMA_ImportRunQuery_post(
95 $import_run_buffer, $sql, $full
100 if (! empty($import_run_buffer['sql'])
101 && trim($import_run_buffer['sql']) != ''
104 // USE query changes the database, son need to track
105 // while running multiple queries
107 = /*overload*/mb_stripos($import_run_buffer['sql'], "use ") !== false;
111 /*overload*/mb_strlen($import_run_buffer['sql'])
113 if (! $sql_query_disabled) {
114 $sql_query .= $import_run_buffer['full'];
116 $pattern = '@^[[:space:]]*DROP[[:space:]]+(IF EXISTS[[:space:]]+)?'
118 if (! $cfg['AllowUserDropDatabase']
120 && preg_match($pattern, $import_run_buffer['sql'])
122 $GLOBALS['message'] = PMA_Message
::error(
123 __('"DROP DATABASE" statements are disabled.')
129 $pattern = '/^[\s]*(SELECT|SHOW|HANDLER)/i';
131 && $GLOBALS['finished']
134 && ((! empty($import_run_buffer['sql'])
135 && preg_match($pattern, $import_run_buffer['sql']))
136 ||
($executed_queries == 1))
139 if (! $sql_query_disabled) {
140 $complete_query = $sql_query;
141 $display_query = $sql_query;
143 $complete_query = '';
146 $sql_query = $import_run_buffer['sql'];
147 $sql_data['valid_sql'][] = $import_run_buffer['sql'];
148 if (! isset($sql_data['valid_queries'])) {
149 $sql_data['valid_queries'] = 0;
151 $sql_data['valid_queries']++
;
153 // If a 'USE <db>' SQL-clause was found,
154 // set our current $db to the new one
155 list($db, $reload) = PMA_lookForUse(
156 $import_run_buffer['sql'],
160 } elseif ($run_query) {
163 $result = PMA_queryAsControlUser(
164 $import_run_buffer['sql']
167 $result = $GLOBALS['dbi']
168 ->tryQuery($import_run_buffer['sql']);
172 if ($result === false) { // execution failed
173 if (! isset($my_die)) {
177 'sql' => $import_run_buffer['full'],
178 'error' => $GLOBALS['dbi']->getError()
183 if (! $cfg['IgnoreMultiSubmitErrors']) {
188 $a_num_rows = (int)@$GLOBALS['dbi']->numRows($result);
189 $a_aff_rows = (int)@$GLOBALS['dbi']->affectedRows();
190 if ($a_num_rows > 0) {
191 $msg .= __('Rows') . ': ' . $a_num_rows;
192 $last_query_with_results = $import_run_buffer['sql'];
193 } elseif ($a_aff_rows > 0) {
194 $message = PMA_Message
::getMessageForAffectedRows(
197 $msg .= $message->getMessage();
200 'MySQL returned an empty result set (i.e. zero '
205 $sql_data = updateSqlData(
206 $sql_data, $a_num_rows, $is_use_query, $import_run_buffer
209 if (! $sql_query_disabled) {
210 $sql_query .= $msg . "\n";
213 // If a 'USE <db>' SQL-clause was found and the query
214 // succeeded, set our current $db to the new one
215 if ($result != false) {
216 list($db, $reload) = PMA_lookForUse(
217 $import_run_buffer['sql'],
223 $pattern = '@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)'
224 . '?(TABLE|DATABASE)[[:space:]]+(.+)@im';
226 && preg_match($pattern, $import_run_buffer['sql'])
231 } // end if not DROP DATABASE
232 // end non empty query
233 } elseif (! empty($import_run_buffer['full'])) {
235 $complete_query .= $import_run_buffer['full'];
236 $display_query .= $import_run_buffer['full'];
238 if (! $sql_query_disabled) {
239 $sql_query .= $import_run_buffer['full'];
243 // check length of query unless we decided to pass it to sql.php
244 // (if $run_query is false, we are just displaying so show
245 // the complete query in the textarea)
246 if (! $go_sql && $run_query) {
247 if (! empty($sql_query)) {
248 if (/*overload*/mb_strlen($sql_query) > 50000
249 ||
$executed_queries > 50
250 ||
$max_sql_len > 1000
253 $sql_query_disabled = true;
258 // Do we have something to push into buffer?
259 $import_run_buffer = PMA_ImportRunQuery_post($import_run_buffer, $sql, $full);
261 // In case of ROLLBACK, notify the user.
262 if (isset($_REQUEST['rollback_query'])) {
263 $msg .= __('[ROLLBACK occurred.]');
270 * @param array $sql_data SQL data
271 * @param int $a_num_rows Number of rows
272 * @param bool $is_use_query Query is used
273 * @param array $import_run_buffer Import buffer
277 function updateSqlData($sql_data, $a_num_rows, $is_use_query, $import_run_buffer)
279 if (($a_num_rows > 0) ||
$is_use_query) {
280 $sql_data['valid_sql'][] = $import_run_buffer['sql'];
281 if (!isset($sql_data['valid_queries'])) {
282 $sql_data['valid_queries'] = 0;
284 $sql_data['valid_queries']++
;
290 * Return import run buffer
292 * @param array $import_run_buffer Buffer of queries for import
293 * @param string $sql SQL query
294 * @param string $full Query to display
296 * @return array Buffer of queries for import
298 function PMA_ImportRunQuery_post($import_run_buffer, $sql, $full)
300 if (!empty($sql) ||
!empty($full)) {
301 $import_run_buffer = array('sql' => $sql, 'full' => $full);
302 return $import_run_buffer;
304 unset($GLOBALS['import_run_buffer']);
305 return $import_run_buffer;
310 * Looks for the presence of USE to possibly change current db
312 * @param string $buffer buffer to examine
313 * @param string $db current db
314 * @param bool $reload reload
316 * @return array (current or new db, whether to reload)
319 function PMA_lookForUse($buffer, $db, $reload)
321 if (preg_match('@^[\s]*USE[[:space:]]+([\S]+)@i', $buffer, $match)) {
322 $db = trim($match[1]);
323 $db = trim($db, ';'); // for example, USE abc;
325 // $db must not contain the escape characters generated by backquote()
326 // ( used in PMA_buildSQL() as: backquote($db_name), and then called
327 // in PMA_importRunQuery() which in turn calls PMA_lookForUse() )
328 $db = PMA_Util
::unQuote($db);
332 return(array($db, $reload));
337 * Returns next part of imported file/buffer
339 * @param int $size size of buffer to read
340 * (this is maximal size function will return)
342 * @return string part of file/buffer
345 function PMA_importGetNextChunk($size = 32768)
347 global $compression, $import_handle, $charset_conversion, $charset_of_file,
350 // Add some progression while reading large amount of data
351 if ($read_multiply <= 8) {
352 $size *= $read_multiply;
358 // We can not read too much
359 if ($size > $GLOBALS['read_limit']) {
360 $size = $GLOBALS['read_limit'];
363 if (PMA_checkTimeout()) {
366 if ($GLOBALS['finished']) {
370 if ($GLOBALS['import_file'] == 'none') {
371 // Well this is not yet supported and tested,
372 // but should return content of textarea
373 if (/*overload*/mb_strlen($GLOBALS['import_text']) < $size) {
374 $GLOBALS['finished'] = true;
375 return $GLOBALS['import_text'];
377 $r = /*overload*/mb_substr($GLOBALS['import_text'], 0, $size);
378 $GLOBALS['offset'] +
= $size;
379 $GLOBALS['import_text'] = /*overload*/
380 mb_substr($GLOBALS['import_text'], $size);
385 switch ($compression) {
386 case 'application/bzip2':
387 $result = bzread($import_handle, $size);
388 $GLOBALS['finished'] = feof($import_handle);
390 case 'application/gzip':
391 $result = gzread($import_handle, $size);
392 $GLOBALS['finished'] = feof($import_handle);
394 case 'application/zip':
395 $result = /*overload*/mb_substr($GLOBALS['import_text'], 0, $size);
396 $GLOBALS['import_text'] = /*overload*/mb_substr(
397 $GLOBALS['import_text'],
400 $GLOBALS['finished'] = empty($GLOBALS['import_text']);
403 $result = fread($import_handle, $size);
404 $GLOBALS['finished'] = feof($import_handle);
407 $GLOBALS['offset'] +
= $size;
409 if ($charset_conversion) {
410 return PMA_convertString($charset_of_file, 'utf-8', $result);
414 * Skip possible byte order marks (I do not think we need more
415 * charsets, but feel free to add more, you can use wikipedia for
416 * reference: <http://en.wikipedia.org/wiki/Byte_Order_Mark>)
418 * @todo BOM could be used for charset autodetection
420 if ($GLOBALS['offset'] == $size) {
422 if (strncmp($result, "\xEF\xBB\xBF", 3) == 0) {
423 $result = /*overload*/mb_substr($result, 3);
425 } elseif (strncmp($result, "\xFE\xFF", 2) == 0
426 ||
strncmp($result, "\xFF\xFE", 2) == 0
428 $result = /*overload*/mb_substr($result, 2);
435 * Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
437 * This functions uses recursion to build the Excel column name.
439 * The column number (1-26) is converted to the responding
440 * ASCII character (A-Z) and returned.
442 * If the column number is bigger than 26 (= num of letters in alphabet),
443 * an extra character needs to be added. To find this extra character,
444 * the number is divided by 26 and this value is passed to another instance
445 * of the same function (hence recursion). In that new instance the number is
446 * evaluated again, and if it is still bigger than 26, it is divided again
447 * and passed to another instance of the same function. This continues until
448 * the number is smaller than 26. Then the last called function returns
449 * the corresponding ASCII character to the function that called it.
450 * Each time a called function ends an extra character is added to the column name.
451 * When the first function is reached, the last character is added and the complete
452 * column name is returned.
454 * @param int $num the column number
456 * @return string The column's "Excel" name
459 function PMA_getColumnAlphaName($num)
461 $A = 65; // ASCII value for capital "A"
465 $div = (int)($num / 26);
466 $remain = (int)($num %
26);
468 // subtract 1 of divided value in case the modulus is 0,
469 // this is necessary because A-Z has no 'zero'
474 // recursive function call
475 $col_name = PMA_getColumnAlphaName($div);
476 // use modulus as new column number
481 // use 'Z' if column number is 0,
482 // this is necessary because A-Z has no 'zero'
483 $col_name .= /*overload*/mb_chr(($A +
26) - 1);
485 // convert column number to ASCII character
486 $col_name .= /*overload*/mb_chr(($A +
$num) - 1);
493 * Returns the column number based on the Excel name.
494 * So "A" = 1, "Z" = 26, "AA" = 27, etc.
496 * Basically this is a base26 (A-Z) to base10 (0-9) conversion.
497 * It iterates through all characters in the column name and
498 * calculates the corresponding value, based on character value
499 * (A = 1, ..., Z = 26) and position in the string.
501 * @param string $name column name(i.e. "A", or "BC", etc.)
503 * @return int The column number
506 function PMA_getColumnNumberFromName($name)
512 $name = /*overload*/mb_strtoupper($name);
513 $num_chars = /*overload*/mb_strlen($name);
515 for ($i = 0; $i < $num_chars; ++
$i) {
516 // read string from back to front
517 $char_pos = ($num_chars - 1) - $i;
519 // convert capital character to ASCII value
520 // and subtract 64 to get corresponding decimal value
521 // ASCII value of "A" is 65, "B" is 66, etc.
522 // Decimal equivalent of "A" is 1, "B" is 2, etc.
523 $number = (int)(/*overload*/mb_ord($name[$char_pos]) - 64);
525 // base26 to base10 conversion : multiply each number
526 // with corresponding value of the position, in this case
527 // $i=0 : 1; $i=1 : 26; $i=2 : 676; ...
528 $column_number +
= $number * PMA_Util
::pow(26, $i);
530 return $column_number;
534 * Constants definitions
537 /* MySQL type defs */
539 define("VARCHAR", 1);
541 define("DECIMAL", 3);
543 define("GEOMETRY", 5);
545 /* Decimal size defs */
550 /* Table array defs */
551 define("TBL_NAME", 0);
552 define("COL_NAMES", 1);
555 /* Analysis array defs */
558 define("FORMATTEDSQL", 2);
561 * Obtains the precision (total # of digits) from a size of type decimal
563 * @param string $last_cumulative_size Size of type decimal
565 * @return int Precision of the given decimal size notation
568 function PMA_getDecimalPrecision($last_cumulative_size)
571 $last_cumulative_size,
573 strpos($last_cumulative_size, ",")
578 * Obtains the scale (# of digits to the right of the decimal point)
579 * from a size of type decimal
581 * @param string $last_cumulative_size Size of type decimal
583 * @return int Scale of the given decimal size notation
586 function PMA_getDecimalScale($last_cumulative_size)
589 $last_cumulative_size,
590 (strpos($last_cumulative_size, ",") +
1),
591 (strlen($last_cumulative_size) - strpos($last_cumulative_size, ","))
596 * Obtains the decimal size of a given cell
598 * @param string $cell cell content
600 * @return array Contains the precision, scale, and full size
601 * representation of the given decimal cell
604 function PMA_getDecimalSize($cell)
606 $curr_size = /*overload*/mb_strlen((string)$cell);
607 $decPos = /*overload*/mb_strpos($cell, ".");
608 $decPrecision = ($curr_size - 1) - $decPos;
613 return array($m, $d, ($m . "," . $d));
617 * Obtains the size of the given cell
619 * @param string $last_cumulative_size Last cumulative column size
620 * @param int $last_cumulative_type Last cumulative column type
621 * (NONE or VARCHAR or DECIMAL or INT or BIGINT)
622 * @param int $curr_type Type of the current cell
623 * (NONE or VARCHAR or DECIMAL or INT or BIGINT)
624 * @param string $cell The current cell
626 * @return string Size of the given cell in the type-appropriate format
629 * @todo Handle the error cases more elegantly
631 function PMA_detectSize($last_cumulative_size, $last_cumulative_type,
634 $curr_size = /*overload*/mb_strlen((string)$cell);
637 * If the cell is NULL, don't treat it as a varchar
639 if (! strcmp('NULL', $cell)) {
640 return $last_cumulative_size;
641 } elseif ($curr_type == VARCHAR
) {
643 * What to do if the current cell is of type VARCHAR
646 * The last cumulative type was VARCHAR
648 if ($last_cumulative_type == VARCHAR
) {
649 if ($curr_size >= $last_cumulative_size) {
652 return $last_cumulative_size;
654 } elseif ($last_cumulative_type == DECIMAL
) {
656 * The last cumulative type was DECIMAL
658 $oldM = PMA_getDecimalPrecision($last_cumulative_size);
660 if ($curr_size >= $oldM) {
665 } elseif ($last_cumulative_type == BIGINT ||
$last_cumulative_type == INT) {
667 * The last cumulative type was BIGINT or INT
669 if ($curr_size >= $last_cumulative_size) {
672 return $last_cumulative_size;
674 } elseif (! isset($last_cumulative_type) ||
$last_cumulative_type == NONE
) {
676 * This is the first row to be analyzed
681 * An error has DEFINITELY occurred
684 * TODO: Handle this MUCH more elegantly
689 } elseif ($curr_type == DECIMAL
) {
691 * What to do if the current cell is of type DECIMAL
694 * The last cumulative type was VARCHAR
696 if ($last_cumulative_type == VARCHAR
) {
697 /* Convert $last_cumulative_size from varchar to decimal format */
698 $size = PMA_getDecimalSize($cell);
700 if ($size[M
] >= $last_cumulative_size) {
703 return $last_cumulative_size;
705 } elseif ($last_cumulative_type == DECIMAL
) {
707 * The last cumulative type was DECIMAL
709 $size = PMA_getDecimalSize($cell);
711 $oldM = PMA_getDecimalPrecision($last_cumulative_size);
712 $oldD = PMA_getDecimalScale($last_cumulative_size);
714 /* New val if M or D is greater than current largest */
715 if ($size[M
] > $oldM ||
$size[D
] > $oldD) {
716 /* Take the largest of both types */
717 return (string) ((($size[M
] > $oldM) ?
$size[M
] : $oldM)
718 . "," . (($size[D
] > $oldD) ?
$size[D
] : $oldD));
720 return $last_cumulative_size;
722 } elseif ($last_cumulative_type == BIGINT ||
$last_cumulative_type == INT) {
724 * The last cumulative type was BIGINT or INT
726 /* Convert $last_cumulative_size from int to decimal format */
727 $size = PMA_getDecimalSize($cell);
729 if ($size[M
] >= $last_cumulative_size) {
732 return ($last_cumulative_size . "," . $size[D
]);
734 } elseif (! isset($last_cumulative_type) ||
$last_cumulative_type == NONE
) {
736 * This is the first row to be analyzed
738 /* First row of the column */
739 $size = PMA_getDecimalSize($cell);
744 * An error has DEFINITELY occurred
747 * TODO: Handle this MUCH more elegantly
752 } elseif ($curr_type == BIGINT ||
$curr_type == INT) {
754 * What to do if the current cell is of type BIGINT or INT
757 * The last cumulative type was VARCHAR
759 if ($last_cumulative_type == VARCHAR
) {
760 if ($curr_size >= $last_cumulative_size) {
763 return $last_cumulative_size;
765 } elseif ($last_cumulative_type == DECIMAL
) {
767 * The last cumulative type was DECIMAL
769 $oldM = PMA_getDecimalPrecision($last_cumulative_size);
770 $oldD = PMA_getDecimalScale($last_cumulative_size);
771 $oldInt = $oldM - $oldD;
772 $newInt = /*overload*/mb_strlen((string)$cell);
774 /* See which has the larger integer length */
775 if ($oldInt >= $newInt) {
776 /* Use old decimal size */
777 return $last_cumulative_size;
779 /* Use $newInt + $oldD as new M */
780 return (($newInt +
$oldD) . "," . $oldD);
782 } elseif ($last_cumulative_type == BIGINT ||
$last_cumulative_type == INT) {
784 * The last cumulative type was BIGINT or INT
786 if ($curr_size >= $last_cumulative_size) {
789 return $last_cumulative_size;
791 } elseif (! isset($last_cumulative_type) ||
$last_cumulative_type == NONE
) {
793 * This is the first row to be analyzed
798 * An error has DEFINITELY occurred
801 * TODO: Handle this MUCH more elegantly
808 * An error has DEFINITELY occurred
811 * TODO: Handle this MUCH more elegantly
819 * Determines what MySQL type a cell is
821 * @param int $last_cumulative_type Last cumulative column type
822 * (VARCHAR or INT or BIGINT or DECIMAL or NONE)
823 * @param string $cell String representation of the cell for which
824 * a best-fit type is to be determined
826 * @return int The MySQL type representation
827 * (VARCHAR or INT or BIGINT or DECIMAL or NONE)
830 function PMA_detectType($last_cumulative_type, $cell)
833 * If numeric, determine if decimal, int or bigint
834 * Else, we call it varchar for simplicity
837 if (! strcmp('NULL', $cell)) {
838 if ($last_cumulative_type === null ||
$last_cumulative_type == NONE
) {
842 return $last_cumulative_type;
845 if (!is_numeric($cell)) {
849 if ($cell == (string)(float)$cell
850 && /*overload*/mb_strpos($cell, ".") !== false
851 && /*overload*/mb_substr_count($cell, ".") == 1
856 if (abs($cell) > 2147483647) {
864 * Determines if the column types are int, decimal, or string
866 * @param array &$table array(string $table_name, array $col_names, array $rows)
868 * @return array array(array $types, array $sizes)
871 * @link http://wiki.phpmyadmin.net/pma/Import
873 * @todo Handle the error case more elegantly
875 function PMA_analyzeTable(&$table)
877 /* Get number of rows in table */
878 $numRows = count($table[ROWS
]);
879 /* Get number of columns */
880 $numCols = count($table[COL_NAMES
]);
881 /* Current type for each column */
885 /* Initialize $sizes to all 0's */
886 for ($i = 0; $i < $numCols; ++
$i) {
890 /* Initialize $types to NONE */
891 for ($i = 0; $i < $numCols; ++
$i) {
895 /* If the passed array is not of the correct form, do not process it */
896 if (!is_array($table)
897 ||
is_array($table[TBL_NAME
])
898 ||
!is_array($table[COL_NAMES
])
899 ||
!is_array($table[ROWS
])
902 * TODO: Handle this better
908 /* Analyze each column */
909 for ($i = 0; $i < $numCols; ++
$i) {
910 /* Analyze the column in each row */
911 for ($j = 0; $j < $numRows; ++
$j) {
912 /* Determine type of the current cell */
913 $curr_type = PMA_detectType($types[$i], $table[ROWS
][$j][$i]);
914 /* Determine size of the current cell */
915 $sizes[$i] = PMA_detectSize(
923 * If a type for this column has already been declared,
924 * only alter it if it was a number and a varchar was found
926 if ($curr_type != NONE
) {
927 if ($curr_type == VARCHAR
) {
928 $types[$i] = VARCHAR
;
929 } else if ($curr_type == DECIMAL
) {
930 if ($types[$i] != VARCHAR
) {
931 $types[$i] = DECIMAL
;
933 } else if ($curr_type == BIGINT
) {
934 if ($types[$i] != VARCHAR
&& $types[$i] != DECIMAL
) {
937 } else if ($curr_type == INT) {
938 if ($types[$i] != VARCHAR
939 && $types[$i] != DECIMAL
940 && $types[$i] != BIGINT
949 /* Check to ensure that all types are valid */
950 $len = count($types);
951 for ($n = 0; $n < $len; ++
$n) {
952 if (! strcmp(NONE
, $types[$n])) {
953 $types[$n] = VARCHAR
;
958 return array($types, $sizes);
961 /* Needed to quell the beast that is PMA_Message */
962 $import_notice = null;
965 * Builds and executes SQL statements to create the database and tables
966 * as necessary, as well as insert all the data.
968 * @param string $db_name Name of the database
969 * @param array &$tables Array of tables for the specified database
970 * @param array &$analyses Analyses of the tables
971 * @param array &$additional_sql Additional SQL statements to be executed
972 * @param array $options Associative array of options
977 * @link http://wiki.phpmyadmin.net/pma/Import
979 function PMA_buildSQL($db_name, &$tables, &$analyses = null,
980 &$additional_sql = null, $options = null
982 /* Take care of the options */
983 if (isset($options['db_collation'])&& ! is_null($options['db_collation'])) {
984 $collation = $options['db_collation'];
986 $collation = "utf8_general_ci";
989 if (isset($options['db_charset']) && ! is_null($options['db_charset'])) {
990 $charset = $options['db_charset'];
995 if (isset($options['create_db'])) {
996 $create_db = $options['create_db'];
1001 /* Create SQL code to handle the database */
1006 $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_Util
::backquote($db_name)
1007 . " COLLATE " . $collation . ";";
1009 $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_Util
::backquote($db_name)
1010 . " DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation
1016 * The calling plug-in should include this statement,
1017 * if necessary, in the $additional_sql parameter
1019 * $sql[] = "USE " . backquote($db_name);
1022 /* Execute the SQL statements create above */
1023 $sql_len = count($sql);
1024 for ($i = 0; $i < $sql_len; ++
$i) {
1025 PMA_importRunQuery($sql[$i], $sql[$i]);
1028 /* No longer needed */
1031 /* Run the $additional_sql statements supplied by the caller plug-in */
1032 if ($additional_sql != null) {
1033 /* Clean the SQL first */
1034 $additional_sql_len = count($additional_sql);
1037 * Only match tables for now, because CREATE IF NOT EXISTS
1038 * syntax is lacking or nonexisting for views, triggers,
1039 * functions, and procedures.
1041 * See: http://bugs.mysql.com/bug.php?id=15287
1043 * To the best of my knowledge this is still an issue.
1045 * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)';
1047 $pattern = '/CREATE [^`]*(TABLE)/';
1048 $replacement = 'CREATE \\1 IF NOT EXISTS';
1050 /* Change CREATE statements to CREATE IF NOT EXISTS to support
1051 * inserting into existing structures
1053 for ($i = 0; $i < $additional_sql_len; ++
$i) {
1054 $additional_sql[$i] = preg_replace(
1059 /* Execute the resulting statements */
1060 PMA_importRunQuery($additional_sql[$i], $additional_sql[$i]);
1064 if ($analyses != null) {
1065 $type_array = array(
1067 VARCHAR
=> "varchar",
1069 DECIMAL
=> "decimal",
1071 GEOMETRY
=> 'geometry'
1074 /* TODO: Do more checking here to make sure they really are matched */
1075 if (count($tables) != count($analyses)) {
1079 /* Create SQL code to create the tables */
1080 $num_tables = count($tables);
1081 for ($i = 0; $i < $num_tables; ++
$i) {
1082 $num_cols = count($tables[$i][COL_NAMES
]);
1083 $tempSQLStr = "CREATE TABLE IF NOT EXISTS "
1084 . PMA_Util
::backquote($db_name)
1085 . '.' . PMA_Util
::backquote($tables[$i][TBL_NAME
]) . " (";
1086 for ($j = 0; $j < $num_cols; ++
$j) {
1087 $size = $analyses[$i][SIZES
][$j];
1088 if ((int)$size == 0) {
1092 $tempSQLStr .= PMA_Util
::backquote($tables[$i][COL_NAMES
][$j]) . " "
1093 . $type_array[$analyses[$i][TYPES
][$j]];
1094 if ($analyses[$i][TYPES
][$j] != GEOMETRY
) {
1095 $tempSQLStr .= "(" . $size . ")";
1098 if ($j != (count($tables[$i][COL_NAMES
]) - 1)) {
1099 $tempSQLStr .= ", ";
1103 . (PMA_DRIZZLE ?
"" : " DEFAULT CHARACTER SET " . $charset)
1104 . " COLLATE " . $collation . ";";
1107 * Each SQL statement is executed immediately
1108 * after it is formed so that we don't have
1109 * to store them in a (possibly large) buffer
1111 PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1116 * Create the SQL statements to insert all the data
1118 * Only one insert query is formed for each table
1122 $num_tables = count($tables);
1123 for ($i = 0; $i < $num_tables; ++
$i) {
1124 $num_cols = count($tables[$i][COL_NAMES
]);
1125 $num_rows = count($tables[$i][ROWS
]);
1127 $tempSQLStr = "INSERT INTO " . PMA_Util
::backquote($db_name) . '.'
1128 . PMA_Util
::backquote($tables[$i][TBL_NAME
]) . " (";
1130 for ($m = 0; $m < $num_cols; ++
$m) {
1131 $tempSQLStr .= PMA_Util
::backquote($tables[$i][COL_NAMES
][$m]);
1133 if ($m != ($num_cols - 1)) {
1134 $tempSQLStr .= ", ";
1138 $tempSQLStr .= ") VALUES ";
1140 for ($j = 0; $j < $num_rows; ++
$j) {
1143 for ($k = 0; $k < $num_cols; ++
$k) {
1144 // If fully formatted SQL, no need to enclose
1145 // with apostrophes, add slashes etc.
1146 if ($analyses != null
1147 && isset($analyses[$i][FORMATTEDSQL
][$col_count])
1148 && $analyses[$i][FORMATTEDSQL
][$col_count] == true
1150 $tempSQLStr .= (string) $tables[$i][ROWS
][$j][$k];
1152 if ($analyses != null) {
1153 $is_varchar = ($analyses[$i][TYPES
][$col_count] === VARCHAR
);
1155 $is_varchar = ! is_numeric($tables[$i][ROWS
][$j][$k]);
1158 /* Don't put quotes around NULL fields */
1159 if (! strcmp($tables[$i][ROWS
][$j][$k], 'NULL')) {
1160 $is_varchar = false;
1163 $tempSQLStr .= (($is_varchar) ?
"'" : "");
1164 $tempSQLStr .= PMA_Util
::sqlAddSlashes(
1165 (string) $tables[$i][ROWS
][$j][$k]
1167 $tempSQLStr .= (($is_varchar) ?
"'" : "");
1170 if ($k != ($num_cols - 1)) {
1171 $tempSQLStr .= ", ";
1174 if ($col_count == ($num_cols - 1)) {
1180 /* Delete the cell after we are done with it */
1181 unset($tables[$i][ROWS
][$j][$k]);
1186 if ($j != ($num_rows - 1)) {
1187 $tempSQLStr .= ",\n ";
1191 /* Delete the row after we are done with it */
1192 unset($tables[$i][ROWS
][$j]);
1198 * Each SQL statement is executed immediately
1199 * after it is formed so that we don't have
1200 * to store them in a (possibly large) buffer
1202 PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1205 /* No longer needed */
1209 * A work in progress
1212 /* Add the viewable structures from $additional_sql
1213 * to $tables so they are also displayed
1215 $view_pattern = '@VIEW `[^`]+`\.`([^`]+)@';
1216 $table_pattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@';
1217 /* Check a third pattern to make sure its not a "USE `db_name`;" statement */
1223 $additional_sql_len = count($additional_sql);
1224 for ($i = 0; $i < $additional_sql_len; ++
$i) {
1225 preg_match($view_pattern, $additional_sql[$i], $regs);
1227 if (count($regs) == 0) {
1228 preg_match($table_pattern, $additional_sql[$i], $regs);
1232 for ($n = 0; $n < $num_tables; ++
$n) {
1233 if (! strcmp($regs[1], $tables[$n][TBL_NAME
])) {
1240 $tables[] = array(TBL_NAME
=> $regs[1]);
1244 /* Reset the array */
1249 $params = array('db' => (string)$db_name);
1250 $db_url = 'db_structure.php' . PMA_URL_getCommon($params);
1251 $db_ops_url = 'db_operations.php' . PMA_URL_getCommon($params);
1253 $message = '<br /><br />';
1254 $message .= '<strong>' . __(
1255 'The following structures have either been created or altered. Here you can:'
1256 ) . '</strong><br />';
1257 $message .= '<ul><li>' . __(
1258 "View a structure's contents by clicking on its name."
1260 $message .= '<li>' . __(
1261 'Change any of its settings by clicking the corresponding "Options" link.'
1263 $message .= '<li>' . __('Edit structure by following the "Structure" link.')
1265 $message .= sprintf(
1266 '<br /><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">'
1267 . __('Options') . '</a>)</li>',
1270 __('Go to database: %s'),
1271 htmlspecialchars(PMA_Util
::backquote($db_name))
1273 htmlspecialchars($db_name),
1276 __('Edit settings for %s'),
1277 htmlspecialchars(PMA_Util
::backquote($db_name))
1285 $num_tables = count($tables);
1286 for ($i = 0; $i < $num_tables; ++
$i) {
1288 'db' => (string) $db_name,
1289 'table' => (string) $tables[$i][TBL_NAME
]
1291 $tbl_url = 'sql.php' . PMA_URL_getCommon($params);
1292 $tbl_struct_url = 'tbl_structure.php' . PMA_URL_getCommon($params);
1293 $tbl_ops_url = 'tbl_operations.php' . PMA_URL_getCommon($params);
1297 $_table = new PMA_Table($tables[$i][TBL_NAME
], $db_name);
1298 if (! $_table->isView()) {
1299 $message .= sprintf(
1300 '<li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __(
1302 ) . '</a>) (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1305 __('Go to table: %s'),
1307 PMA_Util
::backquote($tables[$i][TBL_NAME
])
1310 htmlspecialchars($tables[$i][TBL_NAME
]),
1313 __('Structure of %s'),
1315 PMA_Util
::backquote($tables[$i][TBL_NAME
])
1320 __('Edit settings for %s'),
1322 PMA_Util
::backquote($tables[$i][TBL_NAME
])
1327 $message .= sprintf(
1328 '<li><a href="%s" title="%s">%s</a></li>',
1331 __('Go to view: %s'),
1333 PMA_Util
::backquote($tables[$i][TBL_NAME
])
1336 htmlspecialchars($tables[$i][TBL_NAME
])
1341 $message .= '</ul></ul>';
1343 global $import_notice;
1344 $import_notice = $message;
1351 * Stops the import on (mostly upload/file related) error
1353 * @param PMA_Message $error_message The error message
1359 function PMA_stopImport( PMA_Message
$error_message )
1361 global $import_handle, $file_to_unlink;
1363 // Close open handles
1364 if ($import_handle !== false && $import_handle !== null) {
1365 fclose($import_handle);
1368 // Delete temporary file
1369 if ($file_to_unlink != '') {
1370 unlink($file_to_unlink);
1372 $msg = $error_message->getDisplay();
1373 $_SESSION['Import_message']['message'] = $msg;
1375 $response = PMA_Response
::getInstance();
1376 $response->isSuccess(false);
1377 $response->addJSON('message', PMA_Message
::error($msg));
1383 * Handles request for Simulation of UPDATE/DELETE queries.
1387 function PMA_handleSimulateDMLRequest()
1389 $response = PMA_Response
::getInstance();
1391 $error_msg = __('Only single-table UPDATE and DELETE queries can be simulated.');
1392 $sql_delimiter = $_REQUEST['sql_delimiter'];
1393 $sql_data = array();
1394 $queries = explode($sql_delimiter, $GLOBALS['sql_query']);
1395 foreach ($queries as $sql_query) {
1396 if (empty($sql_query)) {
1400 // Parsing the query.
1401 $parser = new SqlParser\
Parser($sql_query);
1403 if (empty($parser->statements
[0])) {
1407 $statement = $parser->statements
[0];
1409 $analyzed_sql_results = array(
1410 'query' => $sql_query,
1411 'parser' => $parser,
1412 'statement' => $statement,
1415 if ((!(($statement instanceof SqlParser\Statements\UpdateStatement
)
1416 ||
($statement instanceof SqlParser\Statements\DeleteStatement
)))
1417 ||
(!empty($statement->join
))
1419 $error = $error_msg;
1423 $tables = SqlParser\Utils\Query
::getTables($statement);
1424 if (count($tables) > 1) {
1425 $error = $error_msg;
1429 // Get the matched rows for the query.
1430 $result = PMA_getMatchedRows($analyzed_sql_results);
1431 if (! $error = $GLOBALS['dbi']->getError()) {
1432 $sql_data[] = $result;
1439 $message = PMA_Message
::rawError($error);
1440 $response->addJSON('message', $message);
1441 $response->addJSON('sql_data', false);
1443 $response->addJSON('sql_data', $sql_data);
1448 * Find the matching rows for UPDATE/DELETE query.
1450 * @param array $analyzed_sql_results Analyzed SQL results from parser.
1454 function PMA_getMatchedRows($analyzed_sql_results = array())
1456 $statement = $analyzed_sql_results['statement'];
1458 $matched_row_query = '';
1459 if ($statement instanceof SqlParser\Statements\DeleteStatement
) {
1460 $matched_row_query = PMA_getSimulatedDeleteQuery($analyzed_sql_results);
1461 } elseif ($statement instanceof SqlParser\Statements\UpdateStatement
) {
1462 $matched_row_query = PMA_getSimulatedUpdateQuery($analyzed_sql_results);
1465 // Execute the query and get the number of matched rows.
1466 $matched_rows = PMA_executeMatchedRowQuery($matched_row_query);
1468 // URL to matched rows.
1469 $_url_params = array(
1470 'db' => $GLOBALS['db'],
1471 'sql_query' => $matched_row_query
1473 $matched_rows_url = 'sql.php' . PMA_URL_getCommon($_url_params);
1476 'sql_query' => PMA_Util
::formatSql($analyzed_sql_results['query']),
1477 'matched_rows' => $matched_rows,
1478 'matched_rows_url' => $matched_rows_url
1483 * Transforms a UPDATE query into SELECT statement.
1485 * @param array $analyzed_sql_results Analyzed SQL results from parser.
1487 * @return string SQL query
1489 function PMA_getSimulatedUpdateQuery($analyzed_sql_results)
1491 $table_references = SqlParser\Utils\Query
::getTables(
1492 $analyzed_sql_results['statement']
1495 $where = SqlParser\Utils\Query
::getClause(
1496 $analyzed_sql_results['statement'],
1497 $analyzed_sql_results['parser']->list,
1501 if (empty($where)) {
1507 foreach ($analyzed_sql_results['statement']->set
as $set) {
1508 $columns[] = $set->column
;
1509 $diff[] = $set->column
. ' <> ' . $set->value
;
1511 if (!empty($diff)) {
1512 $where .= ' AND (' . implode(' OR ', $diff) . ')';
1515 $order_and_limit = '';
1517 if (!empty($analyzed_sql_results['statement']->order
)) {
1518 $order_and_limit .= ' ORDER BY ' . SqlParser\Utils\Query
::getClause(
1519 $analyzed_sql_results['statement'],
1520 $analyzed_sql_results['parser']->list,
1525 if (!empty($analyzed_sql_results['statement']->limit
)) {
1526 $order_and_limit .= ' LIMIT ' . SqlParser\Utils\Query
::getClause(
1527 $analyzed_sql_results['statement'],
1528 $analyzed_sql_results['parser']->list,
1533 return 'SELECT ' . implode(', ', $columns) .
1534 ' FROM ' . implode(', ', $table_references) .
1535 ' WHERE ' . $where . $order_and_limit;
1539 * Transforms a DELETE query into SELECT statement.
1541 * @param array $analyzed_sql_results Analyzed SQL results from parser.
1543 * @return string SQL query
1545 function PMA_getSimulatedDeleteQuery($analyzed_sql_results)
1547 $table_references = SqlParser\Utils\Query
::getTables(
1548 $analyzed_sql_results['statement']
1551 $where = SqlParser\Utils\Query
::getClause(
1552 $analyzed_sql_results['statement'],
1553 $analyzed_sql_results['parser']->list,
1557 if (empty($where)) {
1561 $order_and_limit = '';
1563 if (!empty($analyzed_sql_results['statement']->order
)) {
1564 $order_and_limit .= ' ORDER BY ' . SqlParser\Utils\Query
::getClause(
1565 $analyzed_sql_results['statement'],
1566 $analyzed_sql_results['parser']->list,
1571 if (!empty($analyzed_sql_results['statement']->limit
)) {
1572 $order_and_limit .= ' LIMIT ' . SqlParser\Utils\Query
::getClause(
1573 $analyzed_sql_results['statement'],
1574 $analyzed_sql_results['parser']->list,
1579 return 'SELECT * FROM ' . implode(', ', $table_references) .
1580 ' WHERE ' . $where . $order_and_limit;
1584 * Executes the matched_row_query and returns the resultant row count.
1586 * @param string $matched_row_query SQL query
1588 * @return integer Number of rows returned
1590 function PMA_executeMatchedRowQuery($matched_row_query)
1592 $GLOBALS['dbi']->selectDb($GLOBALS['db']);
1593 // Execute the query.
1594 $result = $GLOBALS['dbi']->tryQuery($matched_row_query);
1595 // Count the number of rows in the result set.
1596 $result = $GLOBALS['dbi']->numRows($result);
1602 * Handles request for ROLLBACK.
1604 * @param string $sql_query SQL query(s)
1608 function PMA_handleRollbackRequest($sql_query)
1610 $sql_delimiter = $_REQUEST['sql_delimiter'];
1611 $queries = explode($sql_delimiter, $sql_query);
1614 'Only INSERT, UPDATE, DELETE and REPLACE '
1615 . 'SQL queries containing transactional engine tables can be rolled back.'
1617 foreach ($queries as $sql_query) {
1618 if (empty($sql_query)) {
1622 // Check each query for ROLLBACK support.
1623 if (! PMA_checkIfRollbackPossible($sql_query)) {
1624 $global_error = $GLOBALS['dbi']->getError();
1625 if ($global_error) {
1626 $error = $global_error;
1628 $error = $error_msg;
1635 unset($_REQUEST['rollback_query']);
1636 $response = PMA_Response
::getInstance();
1637 $message = PMA_Message
::rawError($error);
1638 $response->addJSON('message', $message);
1641 // If everything fine, START a transaction.
1642 $GLOBALS['dbi']->query('START TRANSACTION');
1647 * Checks if ROLLBACK is possible for a SQL query or not.
1649 * @param string $sql_query SQL query
1653 function PMA_checkIfRollbackPossible($sql_query)
1655 $parser = new SqlParser\
Parser($sql_query);
1657 if (empty($parser->statements
[0])) {
1661 $statement = $parser->statements
[0];
1663 // Check if query is supported.
1664 if (!(($statement instanceof SqlParser\Statements\InsertStatement
)
1665 ||
($statement instanceof SqlParser\Statements\UpdateStatement
)
1666 ||
($statement instanceof SqlParser\Statements\DeleteStatement
)
1667 ||
($statement instanceof SqlParser\Statements\ReplaceStatement
))
1672 // Get table_references from the query.
1673 $tables = SqlParser\Utils\Query
::getTables($statement);
1675 // Check if each table is 'InnoDB'.
1676 foreach ($tables as $table) {
1677 if (! PMA_isTableTransactional($table)) {
1686 * Checks if a table is 'InnoDB' or not.
1688 * @param string $table Table details
1692 function PMA_isTableTransactional($table)
1694 $table = explode('.', $table);
1695 if (count($table) == 2) {
1696 $db = PMA_Util
::unQuote($table[0]);
1697 $table = PMA_Util
::unQuote($table[1]);
1699 $db = $GLOBALS['db'];
1700 $table = PMA_Util
::unQuote($table[0]);
1703 // Query to check if table exists.
1704 $check_table_query = 'SELECT * FROM ' . PMA_Util
::backquote($db)
1705 . '.' . PMA_Util
::backquote($table) . ' '
1708 $result = $GLOBALS['dbi']->tryQuery($check_table_query);
1714 // List of Transactional Engines.
1715 $transactional_engines = array(
1726 // Query to check if table is 'Transactional'.
1727 $check_query = 'SELECT `ENGINE` FROM `information_schema`.`tables` '
1728 . 'WHERE `table_name` = "' . $table . '" '
1729 . 'AND `table_schema` = "' . $db . '" '
1730 . 'AND UPPER(`engine`) IN ("'
1731 . implode('", "', $transactional_engines)
1734 $result = $GLOBALS['dbi']->tryQuery($check_query);
1736 if ($GLOBALS['dbi']->numRows($result) == 1) {