Translation update done using Pootle.
[phpmyadmin/madhuracj.git] / libraries / import.lib.php
blob3387406aea671494fefc9baa2ccc7d66d4b07d10
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * Library that provides common import functions that are used by import plugins
6 * @package PhpMyAdmin
7 */
8 if (! defined('PHPMYADMIN')) {
9 exit;
12 /**
13 * We need to know something about user
15 require_once './libraries/check_user_privileges.lib.php';
17 /**
18 * We do this check, DROP DATABASE does not need to be confirmed elsewhere
20 define('PMA_CHK_DROP', 1);
22 /**
23 * Check whether timeout is getting close
25 * @return boolean true if timeout is close
26 * @access public
28 function PMA_checkTimeout()
30 global $timestamp, $maximum_time, $timeout_passed;
31 if ($maximum_time == 0) {
32 return false;
33 } elseif ($timeout_passed) {
34 return true;
35 /* 5 in next row might be too much */
36 } elseif ((time() - $timestamp) > ($maximum_time - 5)) {
37 $timeout_passed = true;
38 return true;
39 } else {
40 return false;
44 /**
45 * Detects what compression filse uses
47 * @param string $filepath filename to check
48 * @return string MIME type of compression, none for none
49 * @access public
51 function PMA_detectCompression($filepath)
53 $file = @fopen($filepath, 'rb');
54 if (!$file) {
55 return false;
57 $test = fread($file, 4);
58 $len = strlen($test);
59 fclose($file);
60 if ($len >= 2 && $test[0] == chr(31) && $test[1] == chr(139)) {
61 return 'application/gzip';
63 if ($len >= 3 && substr($test, 0, 3) == 'BZh') {
64 return 'application/bzip2';
66 if ($len >= 4 && $test == "PK\003\004") {
67 return 'application/zip';
69 return 'none';
72 /**
73 * Runs query inside import buffer. This is needed to allow displaying
74 * of last SELECT, SHOW or HANDLER results and similar nice stuff.
76 * @param string $sql query to run
77 * @param string $full query to display, this might be commented
78 * @param bool $controluser whether to use control user for queries
79 * @access public
81 function PMA_importRunQuery($sql = '', $full = '', $controluser = false)
83 global $import_run_buffer, $go_sql, $complete_query, $display_query,
84 $sql_query, $my_die, $error, $reload,
85 $last_query_with_results,
86 $skip_queries, $executed_queries, $max_sql_len, $read_multiply,
87 $cfg, $sql_query_disabled, $db, $run_query, $is_superuser;
88 $read_multiply = 1;
89 if (isset($import_run_buffer)) {
90 // Should we skip something?
91 if ($skip_queries > 0) {
92 $skip_queries--;
93 } else {
94 if (!empty($import_run_buffer['sql']) && trim($import_run_buffer['sql']) != '') {
95 $max_sql_len = max($max_sql_len, strlen($import_run_buffer['sql']));
96 if (!$sql_query_disabled) {
97 $sql_query .= $import_run_buffer['full'];
99 if (!$cfg['AllowUserDropDatabase']
100 && !$is_superuser
101 && preg_match('@^[[:space:]]*DROP[[:space:]]+(IF EXISTS[[:space:]]+)?DATABASE @i', $import_run_buffer['sql'])
103 $GLOBALS['message'] = PMA_Message::error(__('"DROP DATABASE" statements are disabled.'));
104 $error = true;
105 } else {
106 $executed_queries++;
107 if ($run_query
108 && $GLOBALS['finished']
109 && empty($sql)
110 && !$error
111 && ((!empty($import_run_buffer['sql'])
112 && preg_match('/^[\s]*(SELECT|SHOW|HANDLER)/i', $import_run_buffer['sql']))
113 || ($executed_queries == 1))
115 $go_sql = true;
116 if (!$sql_query_disabled) {
117 $complete_query = $sql_query;
118 $display_query = $sql_query;
119 } else {
120 $complete_query = '';
121 $display_query = '';
123 $sql_query = $import_run_buffer['sql'];
124 // If a 'USE <db>' SQL-clause was found, set our current $db to the new one
125 list($db, $reload) = PMA_lookForUse($import_run_buffer['sql'], $db, $reload);
126 } elseif ($run_query) {
127 if ($controluser) {
128 $result = PMA_query_as_controluser($import_run_buffer['sql']);
129 } else {
130 $result = PMA_DBI_try_query($import_run_buffer['sql']);
132 $msg = '# ';
133 if ($result === false) { // execution failed
134 if (! isset($my_die)) {
135 $my_die = array();
137 $my_die[] = array('sql' => $import_run_buffer['full'], 'error' => PMA_DBI_getError());
139 if ($cfg['VerboseMultiSubmit']) {
140 $msg .= __('Error');
143 if (!$cfg['IgnoreMultiSubmitErrors']) {
144 $error = true;
145 return;
147 } elseif ($cfg['VerboseMultiSubmit']) {
148 $a_num_rows = (int)@PMA_DBI_num_rows($result);
149 $a_aff_rows = (int)@PMA_DBI_affected_rows();
150 if ($a_num_rows > 0) {
151 $msg .= __('Rows'). ': ' . $a_num_rows;
152 $last_query_with_results = $import_run_buffer['sql'];
153 } elseif ($a_aff_rows > 0) {
154 $message = PMA_Message::affected_rows($a_aff_rows);
155 $msg .= $message->getMessage();
156 } else {
157 $msg .= __('MySQL returned an empty result set (i.e. zero rows).');
160 if (!$sql_query_disabled) {
161 $sql_query .= $msg . "\n";
164 // If a 'USE <db>' SQL-clause was found and the query succeeded, set our current $db to the new one
165 if ($result != false) {
166 list($db, $reload) = PMA_lookForUse($import_run_buffer['sql'], $db, $reload);
169 if ($result != false
170 && preg_match('@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)?(TABLE|DATABASE)[[:space:]]+(.+)@im', $import_run_buffer['sql'])
172 $reload = true;
174 } // end run query
175 } // end if not DROP DATABASE
176 // end non empty query
177 } elseif (!empty($import_run_buffer['full'])) {
178 if ($go_sql) {
179 $complete_query .= $import_run_buffer['full'];
180 $display_query .= $import_run_buffer['full'];
181 } else {
182 if (!$sql_query_disabled) {
183 $sql_query .= $import_run_buffer['full'];
187 // check length of query unless we decided to pass it to sql.php
188 // (if $run_query is false, we are just displaying so show
189 // the complete query in the textarea)
190 if (! $go_sql && $run_query) {
191 if ($cfg['VerboseMultiSubmit'] && ! empty($sql_query)) {
192 if (strlen($sql_query) > 50000 || $executed_queries > 50 || $max_sql_len > 1000) {
193 $sql_query = '';
194 $sql_query_disabled = true;
196 } else {
197 if (strlen($sql_query) > 10000 || $executed_queries > 10 || $max_sql_len > 500) {
198 $sql_query = '';
199 $sql_query_disabled = true;
203 } // end do query (no skip)
204 } // end buffer exists
206 // Do we have something to push into buffer?
207 if (!empty($sql) || !empty($full)) {
208 $import_run_buffer = array('sql' => $sql, 'full' => $full);
209 } else {
210 unset($GLOBALS['import_run_buffer']);
215 * Looks for the presence of USE to possibly change current db
217 * @param string $buffer buffer to examine
218 * @param string $db current db
219 * @param bool $reload reload
220 * @return array (current or new db, whether to reload)
221 * @access public
223 function PMA_lookForUse($buffer, $db, $reload)
225 if (preg_match('@^[\s]*USE[[:space:]]+([\S]+)@i', $buffer, $match)) {
226 $db = trim($match[1]);
227 $db = trim($db, ';'); // for example, USE abc;
228 $reload = true;
230 return(array($db, $reload));
235 * Returns next part of imported file/buffer
237 * @param int $size size of buffer to read (this is maximal size function will return)
238 * @return string part of file/buffer
239 * @access public
241 function PMA_importGetNextChunk($size = 32768)
243 global $compression, $import_handle, $charset_conversion, $charset_of_file,
244 $read_multiply;
246 // Add some progression while reading large amount of data
247 if ($read_multiply <= 8) {
248 $size *= $read_multiply;
249 } else {
250 $size *= 8;
252 $read_multiply++;
254 // We can not read too much
255 if ($size > $GLOBALS['read_limit']) {
256 $size = $GLOBALS['read_limit'];
259 if (PMA_checkTimeout()) {
260 return false;
262 if ($GLOBALS['finished']) {
263 return true;
266 if ($GLOBALS['import_file'] == 'none') {
267 // Well this is not yet supported and tested, but should return content of textarea
268 if (strlen($GLOBALS['import_text']) < $size) {
269 $GLOBALS['finished'] = true;
270 return $GLOBALS['import_text'];
271 } else {
272 $r = substr($GLOBALS['import_text'], 0, $size);
273 $GLOBALS['offset'] += $size;
274 $GLOBALS['import_text'] = substr($GLOBALS['import_text'], $size);
275 return $r;
279 switch ($compression) {
280 case 'application/bzip2':
281 $result = bzread($import_handle, $size);
282 $GLOBALS['finished'] = feof($import_handle);
283 break;
284 case 'application/gzip':
285 $result = gzread($import_handle, $size);
286 $GLOBALS['finished'] = feof($import_handle);
287 break;
288 case 'application/zip':
289 $result = substr($GLOBALS['import_text'], 0, $size);
290 $GLOBALS['import_text'] = substr($GLOBALS['import_text'], $size);
291 $GLOBALS['finished'] = empty($GLOBALS['import_text']);
292 break;
293 case 'none':
294 $result = fread($import_handle, $size);
295 $GLOBALS['finished'] = feof($import_handle);
296 break;
298 $GLOBALS['offset'] += $size;
300 if ($charset_conversion) {
301 return PMA_convert_string($charset_of_file, 'utf-8', $result);
302 } else {
304 * Skip possible byte order marks (I do not think we need more
305 * charsets, but feel free to add more, you can use wikipedia for
306 * reference: <http://en.wikipedia.org/wiki/Byte_Order_Mark>)
308 * @todo BOM could be used for charset autodetection
310 if ($GLOBALS['offset'] == $size) {
311 // UTF-8
312 if (strncmp($result, "\xEF\xBB\xBF", 3) == 0) {
313 $result = substr($result, 3);
314 // UTF-16 BE, LE
315 } elseif (strncmp($result, "\xFE\xFF", 2) == 0 || strncmp($result, "\xFF\xFE", 2) == 0) {
316 $result = substr($result, 2);
319 return $result;
324 * Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
326 * This functions uses recursion to build the Excel column name.
328 * The column number (1-26) is converted to the responding ASCII character (A-Z) and returned.
330 * If the column number is bigger than 26 (= num of letters in alfabet),
331 * an extra character needs to be added. To find this extra character, the number is divided by 26
332 * and this value is passed to another instance of the same function (hence recursion).
333 * In that new instance the number is evaluated again, and if it is still bigger than 26, it is divided again
334 * and passed to another instance of the same function. This continues until the number is smaller than 26.
335 * Then the last called function returns the corresponding ASCII character to the function that called it.
336 * Each time a called function ends an extra character is added to the column name.
337 * When the first function is reached, the last character is addded and the complete column name is returned.
339 * @access public
341 * @param int $num
342 * @return string The column's "Excel" name
344 function PMA_getColumnAlphaName($num)
346 $A = 65; // ASCII value for capital "A"
347 $col_name = "";
349 if ($num > 26) {
350 $div = (int)($num / 26);
351 $remain = (int)($num % 26);
353 // subtract 1 of divided value in case the modulus is 0,
354 // this is necessary because A-Z has no 'zero'
355 if ($remain == 0) {
356 $div--;
359 // recursive function call
360 $col_name = PMA_getColumnAlphaName($div);
361 // use modulus as new column number
362 $num = $remain;
365 if ($num == 0) {
366 // use 'Z' if column number is 0,
367 // this is necessary because A-Z has no 'zero'
368 $col_name .= chr(($A + 26) - 1);
369 } else {
370 // convert column number to ASCII character
371 $col_name .= chr(($A + $num) - 1);
374 return $col_name;
378 * Returns the column number based on the Excel name.
379 * So "A" = 1, "Z" = 26, "AA" = 27, etc.
381 * Basicly this is a base26 (A-Z) to base10 (0-9) conversion.
382 * It iterates through all characters in the column name and
383 * calculates the corresponding value, based on character value
384 * (A = 1, ..., Z = 26) and position in the string.
386 * @access public
388 * @param string $name (i.e. "A", or "BC", etc.)
389 * @return int The column number
391 function PMA_getColumnNumberFromName($name)
393 if (!empty($name)) {
394 $name = strtoupper($name);
395 $num_chars = strlen($name);
396 $column_number = 0;
397 for ($i = 0; $i < $num_chars; ++$i) {
398 // read string from back to front
399 $char_pos = ($num_chars - 1) - $i;
401 // convert capital character to ASCII value
402 // and subtract 64 to get corresponding decimal value
403 // ASCII value of "A" is 65, "B" is 66, etc.
404 // Decimal equivalent of "A" is 1, "B" is 2, etc.
405 $number = (ord($name[$char_pos]) - 64);
407 // base26 to base10 conversion : multiply each number
408 // with corresponding value of the position, in this case
409 // $i=0 : 1; $i=1 : 26; $i=2 : 676; ...
410 $column_number += $number * pow(26, $i);
412 return $column_number;
413 } else {
414 return 0;
419 * Constants definitions
422 /* MySQL type defs */
423 define("NONE", 0);
424 define("VARCHAR", 1);
425 define("INT", 2);
426 define("DECIMAL", 3);
427 define("BIGINT", 4);
428 define("GEOMETRY", 5);
430 /* Decimal size defs */
431 define("M", 0);
432 define("D", 1);
433 define("FULL", 2);
435 /* Table array defs */
436 define("TBL_NAME", 0);
437 define("COL_NAMES", 1);
438 define("ROWS", 2);
440 /* Analysis array defs */
441 define("TYPES", 0);
442 define("SIZES", 1);
443 define("FORMATTEDSQL", 2);
446 * Obtains the precision (total # of digits) from a size of type decimal
448 * @access public
450 * @param string $last_cumulative_size
451 * @return int Precision of the given decimal size notation
453 function PMA_getM($last_cumulative_size)
455 return (int)substr($last_cumulative_size, 0, strpos($last_cumulative_size, ","));
459 * Obtains the scale (# of digits to the right of the decimal point) from a size of type decimal
461 * @access public
463 * @param string $last_cumulative_size
464 * @return int Scale of the given decimal size notation
466 function PMA_getD($last_cumulative_size)
468 return (int)substr($last_cumulative_size, (strpos($last_cumulative_size, ",") + 1), (strlen($last_cumulative_size) - strpos($last_cumulative_size, ",")));
472 * Obtains the decimal size of a given cell
474 * @access public
476 * @param string &$cell
477 * @return array Contains the precision, scale, and full size representation of the given decimal cell
479 function PMA_getDecimalSize(&$cell)
481 $curr_size = strlen((string)$cell);
482 $decPos = strpos($cell, ".");
483 $decPrecision = ($curr_size - 1) - $decPos;
485 $m = $curr_size - 1;
486 $d = $decPrecision;
488 return array($m, $d, ($m . "," . $d));
492 * Obtains the size of the given cell
494 * @todo Handle the error cases more elegantly
496 * @access public
498 * @param string $last_cumulative_size Last cumulative column size
499 * @param int $last_cumulative_type Last cumulative column type (NONE or VARCHAR or DECIMAL or INT or BIGINT)
500 * @param int $curr_type Type of the current cell (NONE or VARCHAR or DECIMAL or INT or BIGINT)
501 * @param string &$cell The current cell
502 * @return string Size of the given cell in the type-appropriate format
504 function PMA_detectSize($last_cumulative_size, $last_cumulative_type, $curr_type, &$cell)
506 $curr_size = strlen((string)$cell);
509 * If the cell is NULL, don't treat it as a varchar
511 if (! strcmp('NULL', $cell)) {
512 return $last_cumulative_size;
515 * What to do if the current cell is of type VARCHAR
517 elseif ($curr_type == VARCHAR) {
519 * The last cumulative type was VARCHAR
521 if ($last_cumulative_type == VARCHAR) {
522 if ($curr_size >= $last_cumulative_size) {
523 return $curr_size;
524 } else {
525 return $last_cumulative_size;
529 * The last cumulative type was DECIMAL
531 elseif ($last_cumulative_type == DECIMAL) {
532 $oldM = PMA_getM($last_cumulative_size);
534 if ($curr_size >= $oldM) {
535 return $curr_size;
536 } else {
537 return $oldM;
541 * The last cumulative type was BIGINT or INT
543 elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
544 if ($curr_size >= $last_cumulative_size) {
545 return $curr_size;
546 } else {
547 return $last_cumulative_size;
551 * This is the first row to be analyzed
553 elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
554 return $curr_size;
557 * An error has DEFINITELY occurred
559 else {
561 * TODO: Handle this MUCH more elegantly
564 return -1;
568 * What to do if the current cell is of type DECIMAL
570 elseif ($curr_type == DECIMAL) {
572 * The last cumulative type was VARCHAR
574 if ($last_cumulative_type == VARCHAR) {
575 /* Convert $last_cumulative_size from varchar to decimal format */
576 $size = PMA_getDecimalSize($cell);
578 if ($size[M] >= $last_cumulative_size) {
579 return $size[M];
580 } else {
581 return $last_cumulative_size;
585 * The last cumulative type was DECIMAL
587 elseif ($last_cumulative_type == DECIMAL) {
588 $size = PMA_getDecimalSize($cell);
590 $oldM = PMA_getM($last_cumulative_size);
591 $oldD = PMA_getD($last_cumulative_size);
593 /* New val if M or D is greater than current largest */
594 if ($size[M] > $oldM || $size[D] > $oldD) {
595 /* Take the largest of both types */
596 return (string)((($size[M] > $oldM) ? $size[M] : $oldM) . "," . (($size[D] > $oldD) ? $size[D] : $oldD));
597 } else {
598 return $last_cumulative_size;
602 * The last cumulative type was BIGINT or INT
604 elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
605 /* Convert $last_cumulative_size from int to decimal format */
606 $size = PMA_getDecimalSize($cell);
608 if ($size[M] >= $last_cumulative_size) {
609 return $size[FULL];
610 } else {
611 return ($last_cumulative_size.",".$size[D]);
615 * This is the first row to be analyzed
617 elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
618 /* First row of the column */
619 $size = PMA_getDecimalSize($cell);
621 return $size[FULL];
624 * An error has DEFINITELY occurred
626 else {
628 * TODO: Handle this MUCH more elegantly
631 return -1;
635 * What to do if the current cell is of type BIGINT or INT
637 elseif ($curr_type == BIGINT || $curr_type == INT) {
639 * The last cumulative type was VARCHAR
641 if ($last_cumulative_type == VARCHAR) {
642 if ($curr_size >= $last_cumulative_size) {
643 return $curr_size;
644 } else {
645 return $last_cumulative_size;
649 * The last cumulative type was DECIMAL
651 elseif ($last_cumulative_type == DECIMAL) {
652 $oldM = PMA_getM($last_cumulative_size);
653 $oldD = PMA_getD($last_cumulative_size);
654 $oldInt = $oldM - $oldD;
655 $newInt = strlen((string)$cell);
657 /* See which has the larger integer length */
658 if ($oldInt >= $newInt) {
659 /* Use old decimal size */
660 return $last_cumulative_size;
661 } else {
662 /* Use $newInt + $oldD as new M */
663 return (($newInt + $oldD) . "," . $oldD);
667 * The last cumulative type was BIGINT or INT
669 elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
670 if ($curr_size >= $last_cumulative_size) {
671 return $curr_size;
672 } else {
673 return $last_cumulative_size;
677 * This is the first row to be analyzed
679 elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
680 return $curr_size;
683 * An error has DEFINITELY occurred
685 else {
687 * TODO: Handle this MUCH more elegantly
690 return -1;
694 * An error has DEFINITELY occurred
696 else {
698 * TODO: Handle this MUCH more elegantly
701 return -1;
706 * Determines what MySQL type a cell is
708 * @access public
710 * @param int $last_cumulative_type Last cumulative column type (VARCHAR or INT or BIGINT or DECIMAL or NONE)
711 * @param string &$cell String representation of the cell for which a best-fit type is to be determined
712 * @return int The MySQL type representation (VARCHAR or INT or BIGINT or DECIMAL or NONE)
714 function PMA_detectType($last_cumulative_type, &$cell)
717 * If numeric, determine if decimal, int or bigint
718 * Else, we call it varchar for simplicity
721 if (! strcmp('NULL', $cell)) {
722 if ($last_cumulative_type === null || $last_cumulative_type == NONE) {
723 return NONE;
724 } else {
725 return $last_cumulative_type;
727 } elseif (is_numeric($cell)) {
728 if ($cell == (string)(float)$cell && strpos($cell, ".") !== false && substr_count($cell, ".") == 1) {
729 return DECIMAL;
730 } else {
731 if (abs($cell) > 2147483647) {
732 return BIGINT;
733 } else {
734 return INT;
737 } else {
738 return VARCHAR;
743 * Determines if the column types are int, decimal, or string
745 * @link http://wiki.phpmyadmin.net/pma/Import
747 * @todo Handle the error case more elegantly
749 * @access public
751 * @param &$table array(string $table_name, array $col_names, array $rows)
752 * @return array array(array $types, array $sizes)
754 function PMA_analyzeTable(&$table)
756 /* Get number of rows in table */
757 $numRows = count($table[ROWS]);
758 /* Get number of columns */
759 $numCols = count($table[COL_NAMES]);
760 /* Current type for each column */
761 $types = array();
762 $sizes = array();
764 /* Initialize $sizes to all 0's */
765 for ($i = 0; $i < $numCols; ++$i) {
766 $sizes[$i] = 0;
769 /* Initialize $types to NONE */
770 for ($i = 0; $i < $numCols; ++$i) {
771 $types[$i] = NONE;
774 /* Temp vars */
775 $curr_type = NONE;
776 $curr_size = 0;
778 /* If the passed array is not of the correct form, do not process it */
779 if (is_array($table) && ! is_array($table[TBL_NAME]) && is_array($table[COL_NAMES]) && is_array($table[ROWS])) {
780 /* Analyze each column */
781 for ($i = 0; $i < $numCols; ++$i) {
782 /* Analyze the column in each row */
783 for ($j = 0; $j < $numRows; ++$j) {
784 /* Determine type of the current cell */
785 $curr_type = PMA_detectType($types[$i], $table[ROWS][$j][$i]);
786 /* Determine size of the current cell */
787 $sizes[$i] = PMA_detectSize($sizes[$i], $types[$i], $curr_type, $table[ROWS][$j][$i]);
790 * If a type for this column has already been declared,
791 * only alter it if it was a number and a varchar was found
793 if ($curr_type != NONE) {
794 if ($curr_type == VARCHAR) {
795 $types[$i] = VARCHAR;
796 } else if ($curr_type == DECIMAL) {
797 if ($types[$i] != VARCHAR) {
798 $types[$i] = DECIMAL;
800 } else if ($curr_type == BIGINT) {
801 if ($types[$i] != VARCHAR && $types[$i] != DECIMAL) {
802 $types[$i] = BIGINT;
804 } else if ($curr_type == INT) {
805 if ($types[$i] != VARCHAR && $types[$i] != DECIMAL && $types[$i] != BIGINT) {
806 $types[$i] = INT;
813 /* Check to ensure that all types are valid */
814 $len = count($types);
815 for ($n = 0; $n < $len; ++$n) {
816 if (! strcmp(NONE, $types[$n])) {
817 $types[$n] = VARCHAR;
818 $sizes[$n] = '10';
822 return array($types, $sizes);
823 } else {
825 * TODO: Handle this better
828 return false;
832 /* Needed to quell the beast that is PMA_Message */
833 $import_notice = null;
836 * Builds and executes SQL statements to create the database and tables
837 * as necessary, as well as insert all the data.
839 * @link http://wiki.phpmyadmin.net/pma/Import
841 * @access public
843 * @param string $db_name Name of the database
844 * @param array &$tables Array of tables for the specified database
845 * @param array &$analyses Analyses of the tables
846 * @param array &$additional_sql Additional SQL statements to be executed
847 * @param array $options Associative array of options
848 * @return void
850 function PMA_buildSQL($db_name, &$tables, &$analyses = null, &$additional_sql = null, $options = null)
852 /* Take care of the options */
853 if (isset($options['db_collation'])&& ! is_null($options['db_collation'])) {
854 $collation = $options['db_collation'];
855 } else {
856 $collation = "utf8_general_ci";
859 if (isset($options['db_charset']) && ! is_null($options['db_charset'])) {
860 $charset = $options['db_charset'];
861 } else {
862 $charset = "utf8";
865 if (isset($options['create_db'])) {
866 $create_db = $options['create_db'];
867 } else {
868 $create_db = true;
871 /* Create SQL code to handle the database */
872 $sql = array();
874 if ($create_db) {
875 if (PMA_DRIZZLE) {
876 $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_backquote($db_name) . " COLLATE " . $collation;
877 } else {
878 $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_backquote($db_name) . " DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation;
883 * The calling plug-in should include this statement, if necessary, in the $additional_sql parameter
885 * $sql[] = "USE " . PMA_backquote($db_name);
888 /* Execute the SQL statements create above */
889 $sql_len = count($sql);
890 for ($i = 0; $i < $sql_len; ++$i) {
891 PMA_importRunQuery($sql[$i], $sql[$i]);
894 /* No longer needed */
895 unset($sql);
897 /* Run the $additional_sql statements supplied by the caller plug-in */
898 if ($additional_sql != null) {
899 /* Clean the SQL first */
900 $additional_sql_len = count($additional_sql);
903 * Only match tables for now, because CREATE IF NOT EXISTS
904 * syntax is lacking or nonexisting for views, triggers,
905 * functions, and procedures.
907 * See: http://bugs.mysql.com/bug.php?id=15287
909 * To the best of my knowledge this is still an issue.
911 * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)';
913 $pattern = '/CREATE .*(TABLE)/';
914 $replacement = 'CREATE \\1 IF NOT EXISTS';
916 /* Change CREATE statements to CREATE IF NOT EXISTS to support inserting into existing structures */
917 for ($i = 0; $i < $additional_sql_len; ++$i) {
918 $additional_sql[$i] = preg_replace($pattern, $replacement, $additional_sql[$i]);
919 /* Execute the resulting statements */
920 PMA_importRunQuery($additional_sql[$i], $additional_sql[$i]);
924 if ($analyses != null) {
925 $type_array = array(NONE => "NULL", VARCHAR => "varchar", INT => "int", DECIMAL => "decimal", BIGINT => "bigint", GEOMETRY => 'geometry');
927 /* TODO: Do more checking here to make sure they really are matched */
928 if (count($tables) != count($analyses)) {
929 exit();
932 /* Create SQL code to create the tables */
933 $tempSQLStr = "";
934 $num_tables = count($tables);
935 for ($i = 0; $i < $num_tables; ++$i) {
936 $num_cols = count($tables[$i][COL_NAMES]);
937 $tempSQLStr = "CREATE TABLE IF NOT EXISTS " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
938 for ($j = 0; $j < $num_cols; ++$j) {
939 $size = $analyses[$i][SIZES][$j];
940 if ((int)$size == 0) {
941 $size = 10;
944 $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$j]) . " " . $type_array[$analyses[$i][TYPES][$j]];
945 if ($analyses[$i][TYPES][$j] != GEOMETRY) {
946 $tempSQLStr .= "(" . $size . ")";
949 if ($j != (count($tables[$i][COL_NAMES]) - 1)) {
950 $tempSQLStr .= ", ";
953 $tempSQLStr .= ")"
954 . (PMA_DRIZZLE ? "" : " DEFAULT CHARACTER SET " . $charset)
955 . " COLLATE " . $collation . ";";
958 * Each SQL statement is executed immediately
959 * after it is formed so that we don't have
960 * to store them in a (possibly large) buffer
962 PMA_importRunQuery($tempSQLStr, $tempSQLStr);
967 * Create the SQL statements to insert all the data
969 * Only one insert query is formed for each table
971 $tempSQLStr = "";
972 $col_count = 0;
973 $num_tables = count($tables);
974 for ($i = 0; $i < $num_tables; ++$i) {
975 $num_cols = count($tables[$i][COL_NAMES]);
976 $num_rows = count($tables[$i][ROWS]);
978 $tempSQLStr = "INSERT INTO " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
980 for ($m = 0; $m < $num_cols; ++$m) {
981 $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$m]);
983 if ($m != ($num_cols - 1)) {
984 $tempSQLStr .= ", ";
988 $tempSQLStr .= ") VALUES ";
990 for ($j = 0; $j < $num_rows; ++$j) {
991 $tempSQLStr .= "(";
993 for ($k = 0; $k < $num_cols; ++$k) {
994 // If fully formatted SQL, no need to enclose with aphostrophes, add shalshes etc.
995 if ($analyses != null
996 && isset($analyses[$i][FORMATTEDSQL][$col_count])
997 && $analyses[$i][FORMATTEDSQL][$col_count] == true
999 $tempSQLStr .= (string) $tables[$i][ROWS][$j][$k];
1000 } else {
1001 if ($analyses != null) {
1002 $is_varchar = ($analyses[$i][TYPES][$col_count] === VARCHAR);
1003 } else {
1004 $is_varchar = !is_numeric($tables[$i][ROWS][$j][$k]);
1007 /* Don't put quotes around NULL fields */
1008 if (! strcmp($tables[$i][ROWS][$j][$k], 'NULL')) {
1009 $is_varchar = false;
1012 $tempSQLStr .= (($is_varchar) ? "'" : "");
1013 $tempSQLStr .= PMA_sqlAddSlashes((string)$tables[$i][ROWS][$j][$k]);
1014 $tempSQLStr .= (($is_varchar) ? "'" : "");
1017 if ($k != ($num_cols - 1)) {
1018 $tempSQLStr .= ", ";
1021 if ($col_count == ($num_cols - 1)) {
1022 $col_count = 0;
1023 } else {
1024 $col_count++;
1027 /* Delete the cell after we are done with it */
1028 unset($tables[$i][ROWS][$j][$k]);
1031 $tempSQLStr .= ")";
1033 if ($j != ($num_rows - 1)) {
1034 $tempSQLStr .= ",\n ";
1037 $col_count = 0;
1038 /* Delete the row after we are done with it */
1039 unset($tables[$i][ROWS][$j]);
1042 $tempSQLStr .= ";";
1045 * Each SQL statement is executed immediately
1046 * after it is formed so that we don't have
1047 * to store them in a (possibly large) buffer
1049 PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1052 /* No longer needed */
1053 unset($tempSQLStr);
1056 * A work in progress
1059 /* Add the viewable structures from $additional_sql to $tables so they are also displayed */
1061 $view_pattern = '@VIEW `[^`]+`\.`([^`]+)@';
1062 $table_pattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@';
1063 /* Check a third pattern to make sure its not a "USE `db_name`;" statement */
1065 $regs = array();
1067 $inTables = false;
1069 $additional_sql_len = count($additional_sql);
1070 for ($i = 0; $i < $additional_sql_len; ++$i) {
1071 preg_match($view_pattern, $additional_sql[$i], $regs);
1073 if (count($regs) == 0) {
1074 preg_match($table_pattern, $additional_sql[$i], $regs);
1077 if (count($regs)) {
1078 for ($n = 0; $n < $num_tables; ++$n) {
1079 if (!strcmp($regs[1], $tables[$n][TBL_NAME])) {
1080 $inTables = true;
1081 break;
1085 if (!$inTables) {
1086 $tables[] = array(TBL_NAME => $regs[1]);
1090 /* Reset the array */
1091 $regs = array();
1092 $inTables = false;
1095 $params = array('db' => (string)$db_name);
1096 $db_url = 'db_structure.php' . PMA_generate_common_url($params);
1097 $db_ops_url = 'db_operations.php' . PMA_generate_common_url($params);
1099 $message = '<br /><br />';
1100 $message .= '<strong>' . __('The following structures have either been created or altered. Here you can:') . '</strong><br />';
1101 $message .= '<ul><li>' . __("View a structure's contents by clicking on its name") . '</li>';
1102 $message .= '<li>' . __('Change any of its settings by clicking the corresponding "Options" link') . '</li>';
1103 $message .= '<li>' . __('Edit structure by following the "Structure" link') . '</li>';
1104 $message .= sprintf('<br /><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1105 $db_url,
1106 __('Go to database') . ': ' . htmlspecialchars(PMA_backquote($db_name)),
1107 htmlspecialchars($db_name),
1108 $db_ops_url,
1109 sprintf(__('Edit settings for %s'), htmlspecialchars(PMA_backquote($db_name))));
1111 $message .= '<ul>';
1113 unset($params);
1115 $num_tables = count($tables);
1116 for ($i = 0; $i < $num_tables; ++$i) {
1117 $params = array('db' => (string)$db_name, 'table' => (string)$tables[$i][TBL_NAME]);
1118 $tbl_url = 'sql.php' . PMA_generate_common_url($params);
1119 $tbl_struct_url = 'tbl_structure.php' . PMA_generate_common_url($params);
1120 $tbl_ops_url = 'tbl_operations.php' . PMA_generate_common_url($params);
1122 unset($params);
1124 if (! PMA_Table::isView($db_name, $tables[$i][TBL_NAME])) {
1125 $message .= sprintf('<li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __('Structure') . '</a>) (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1126 $tbl_url,
1127 __('Go to table') . ': ' . htmlspecialchars(PMA_backquote($tables[$i][TBL_NAME])),
1128 htmlspecialchars($tables[$i][TBL_NAME]),
1129 $tbl_struct_url,
1130 sprintf(__('Structure of %s'), htmlspecialchars(PMA_backquote($tables[$i][TBL_NAME]))),
1131 $tbl_ops_url,
1132 sprintf(__('Edit settings for %s'), htmlspecialchars(PMA_backquote($db_name))));
1133 } else {
1134 $message .= sprintf('<li><a href="%s" title="%s">%s</a></li>',
1135 $tbl_url,
1136 __('Go to view') . ': ' . htmlspecialchars(PMA_backquote($tables[$i][TBL_NAME])),
1137 htmlspecialchars($tables[$i][TBL_NAME]));
1141 $message .= '</ul></ul>';
1143 global $import_notice;
1144 $import_notice = $message;
1146 unset($tables);