Translation update done using Pootle.
[phpmyadmin-themes.git] / libraries / import.lib.php
blob79a43b7e69695b472688d67bdd860180e32df701
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 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 * @uses $GLOBALS['finished'] read and write
77 * @param string query to run
78 * @param string query to display, this might be commented
79 * @param bool whether to use control user for queries
80 * @access public
82 function PMA_importRunQuery($sql = '', $full = '', $controluser = false)
84 global $import_run_buffer, $go_sql, $complete_query, $display_query,
85 $sql_query, $my_die, $error, $reload,
86 $last_query_with_results,
87 $skip_queries, $executed_queries, $max_sql_len, $read_multiply,
88 $cfg, $sql_query_disabled, $db, $run_query, $is_superuser;
89 $read_multiply = 1;
90 if (isset($import_run_buffer)) {
91 // Should we skip something?
92 if ($skip_queries > 0) {
93 $skip_queries--;
94 } else {
95 if (!empty($import_run_buffer['sql']) && trim($import_run_buffer['sql']) != '') {
96 $max_sql_len = max($max_sql_len, strlen($import_run_buffer['sql']));
97 if (!$sql_query_disabled) {
98 $sql_query .= $import_run_buffer['full'];
100 if (!$cfg['AllowUserDropDatabase']
101 && !$is_superuser
102 && 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 && $GLOBALS['finished'] && empty($sql) && !$error && (
108 (!empty($import_run_buffer['sql']) && preg_match('/^[\s]*(SELECT|SHOW|HANDLER)/i', $import_run_buffer['sql'])) ||
109 ($executed_queries == 1)
110 )) {
111 $go_sql = TRUE;
112 if (!$sql_query_disabled) {
113 $complete_query = $sql_query;
114 $display_query = $sql_query;
115 } else {
116 $complete_query = '';
117 $display_query = '';
119 $sql_query = $import_run_buffer['sql'];
120 // If a 'USE <db>' SQL-clause was found, set our current $db to the new one
121 list($db, $reload) = PMA_lookForUse($import_run_buffer['sql'], $db, $reload);
122 } elseif ($run_query) {
123 if ($controluser) {
124 $result = PMA_query_as_controluser($import_run_buffer['sql']);
125 } else {
126 $result = PMA_DBI_try_query($import_run_buffer['sql']);
128 $msg = '# ';
129 if ($result === FALSE) { // execution failed
130 if (!isset($my_die)) {
131 $my_die = array();
133 $my_die[] = array('sql' => $import_run_buffer['full'], 'error' => PMA_DBI_getError());
135 if ($cfg['VerboseMultiSubmit']) {
136 $msg .= __('Error');
139 if (!$cfg['IgnoreMultiSubmitErrors']) {
140 $error = TRUE;
141 return;
143 } elseif ($cfg['VerboseMultiSubmit']) {
144 $a_num_rows = (int)@PMA_DBI_num_rows($result);
145 $a_aff_rows = (int)@PMA_DBI_affected_rows();
146 if ($a_num_rows > 0) {
147 $msg .= __('Rows'). ': ' . $a_num_rows;
148 $last_query_with_results = $import_run_buffer['sql'];
149 } elseif ($a_aff_rows > 0) {
150 $message = PMA_Message::affected_rows($a_aff_rows);
151 $msg .= $message->getMessage();
152 } else {
153 $msg .= __('MySQL returned an empty result set (i.e. zero rows).');
156 if (!$sql_query_disabled) {
157 $sql_query .= $msg . "\n";
160 // If a 'USE <db>' SQL-clause was found and the query succeeded, set our current $db to the new one
161 if ($result != FALSE) {
162 list($db, $reload) = PMA_lookForUse($import_run_buffer['sql'], $db, $reload);
165 if ($result != FALSE && preg_match('@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)?(TABLE|DATABASE)[[:space:]]+(.+)@im', $import_run_buffer['sql'])) {
166 $reload = TRUE;
168 } // end run query
169 } // end if not DROP DATABASE
170 } // end non empty query
171 elseif (!empty($import_run_buffer['full'])) {
172 if ($go_sql) {
173 $complete_query .= $import_run_buffer['full'];
174 $display_query .= $import_run_buffer['full'];
175 } else {
176 if (!$sql_query_disabled) {
177 $sql_query .= $import_run_buffer['full'];
181 // check length of query unless we decided to pass it to sql.php
182 // (if $run_query is false, we are just displaying so show
183 // the complete query in the textarea)
184 if (! $go_sql && $run_query) {
185 if ($cfg['VerboseMultiSubmit'] && ! empty($sql_query)) {
186 if (strlen($sql_query) > 50000 || $executed_queries > 50 || $max_sql_len > 1000) {
187 $sql_query = '';
188 $sql_query_disabled = TRUE;
190 } else {
191 if (strlen($sql_query) > 10000 || $executed_queries > 10 || $max_sql_len > 500) {
192 $sql_query = '';
193 $sql_query_disabled = TRUE;
197 } // end do query (no skip)
198 } // end buffer exists
200 // Do we have something to push into buffer?
201 if (!empty($sql) || !empty($full)) {
202 $import_run_buffer = array('sql' => $sql, 'full' => $full);
203 } else {
204 unset($GLOBALS['import_run_buffer']);
209 * Looks for the presence of USE to possibly change current db
211 * @param string buffer to examine
212 * @param string current db
213 * @param boolean reload
214 * @return array (current or new db, whether to reload)
215 * @access public
217 function PMA_lookForUse($buffer, $db, $reload)
219 if (preg_match('@^[\s]*USE[[:space:]]*([\S]+)@i', $buffer, $match)) {
220 $db = trim($match[1]);
221 $db = trim($db,';'); // for example, USE abc;
222 $reload = TRUE;
224 return(array($db, $reload));
229 * Returns next part of imported file/buffer
231 * @uses $GLOBALS['offset'] read and write
232 * @uses $GLOBALS['import_file'] read only
233 * @uses $GLOBALS['import_text'] read and write
234 * @uses $GLOBALS['finished'] read and write
235 * @uses $GLOBALS['read_limit'] read only
236 * @param integer size of buffer to read (this is maximal size
237 * 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 $charset, $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, $charset, $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 * @uses chr()
342 * @param int $num
343 * @return string The column's "Excel" name
345 function PMA_getColumnAlphaName($num)
347 $A = 65; // ASCII value for capital "A"
348 $col_name = "";
350 if ($num > 26) {
351 $div = (int)($num / 26);
352 $remain = (int)($num % 26);
354 // subtract 1 of divided value in case the modulus is 0,
355 // this is necessary because A-Z has no 'zero'
356 if ($remain == 0) {
357 $div--;
360 // recursive function call
361 $col_name = PMA_getColumnAlphaName($div);
362 // use modulus as new column number
363 $num = $remain;
366 if ($num == 0) {
367 // use 'Z' if column number is 0,
368 // this is necessary because A-Z has no 'zero'
369 $col_name .= chr(($A + 26) - 1);
370 } else {
371 // convert column number to ASCII character
372 $col_name .= chr(($A + $num) - 1);
375 return $col_name;
379 * Returns the column number based on the Excel name.
380 * So "A" = 1, "Z" = 26, "AA" = 27, etc.
382 * Basicly this is a base26 (A-Z) to base10 (0-9) conversion.
383 * It iterates through all characters in the column name and
384 * calculates the corresponding value, based on character value
385 * (A = 1, ..., Z = 26) and position in the string.
387 * @access public
389 * @uses strtoupper()
390 * @uses strlen()
391 * @uses ord()
392 * @param string $name (i.e. "A", or "BC", etc.)
393 * @return int The column number
395 function PMA_getColumnNumberFromName($name) {
396 if (!empty($name)) {
397 $name = strtoupper($name);
398 $num_chars = strlen($name);
399 $column_number = 0;
400 for ($i = 0; $i < $num_chars; ++$i) {
401 // read string from back to front
402 $char_pos = ($num_chars - 1) - $i;
404 // convert capital character to ASCII value
405 // and subtract 64 to get corresponding decimal value
406 // ASCII value of "A" is 65, "B" is 66, etc.
407 // Decimal equivalent of "A" is 1, "B" is 2, etc.
408 $number = (ord($name[$char_pos]) - 64);
410 // base26 to base10 conversion : multiply each number
411 // with corresponding value of the position, in this case
412 // $i=0 : 1; $i=1 : 26; $i=2 : 676; ...
413 $column_number += $number * pow(26,$i);
415 return $column_number;
416 } else {
417 return 0;
422 * Constants definitions
425 /* MySQL type defs */
426 define("NONE", 0);
427 define("VARCHAR", 1);
428 define("INT", 2);
429 define("DECIMAL", 3);
430 define("BIGINT", 4);
432 /* Decimal size defs */
433 define("M", 0);
434 define("D", 1);
435 define("FULL", 2);
437 /* Table array defs */
438 define("TBL_NAME", 0);
439 define("COL_NAMES", 1);
440 define("ROWS", 2);
442 /* Analysis array defs */
443 define("TYPES", 0);
444 define("SIZES", 1);
447 * Obtains the precision (total # of digits) from a size of type decimal
450 * @access public
452 * @uses substr()
453 * @uses strpos()
454 * @param string $last_cumulative_size
455 * @return int Precision of the given decimal size notation
457 function PMA_getM($last_cumulative_size) {
458 return (int)substr($last_cumulative_size, 0, strpos($last_cumulative_size, ","));
462 * Obtains the scale (# of digits to the right of the decimal point) from a size of type decimal
465 * @access public
467 * @uses substr()
468 * @uses strpos()
469 * @uses strlen()
470 * @param string $last_cumulative_size
471 * @return int Scale of the given decimal size notation
473 function PMA_getD($last_cumulative_size) {
474 return (int)substr($last_cumulative_size, (strpos($last_cumulative_size, ",") + 1), (strlen($last_cumulative_size) - strpos($last_cumulative_size, ",")));
478 * Obtains the decimal size of a given cell
481 * @access public
483 * @uses strlen()
484 * @uses strpos()
485 * @param string &$cell
486 * @return array Contains the precision, scale, and full size representation of the given decimal cell
488 function PMA_getDecimalSize(&$cell) {
489 $curr_size = strlen((string)$cell);
490 $decPos = strpos($cell, ".");
491 $decPrecision = ($curr_size - 1) - $decPos;
493 $m = $curr_size - 1;
494 $d = $decPrecision;
496 return array($m, $d, ($m . "," . $d));
500 * Obtains the size of the given cell
503 * @todo Handle the error cases more elegantly
505 * @access public
507 * @uses M
508 * @uses D
509 * @uses FULL
510 * @uses VARCHAR
511 * @uses DECIMAL
512 * @uses BIGINT
513 * @uses INT
514 * @uses NONE
515 * @uses strcmp()
516 * @uses strlen()
517 * @uses PMA_getM()
518 * @uses PMA_getD()
519 * @uses PMA_getDecimalSize()
520 * @param string $last_cumulative_size Last cumulative column size
521 * @param int $last_cumulative_type Last cumulative column type (NONE or VARCHAR or DECIMAL or INT or BIGINT)
522 * @param int $curr_type Type of the current cell (NONE or VARCHAR or DECIMAL or INT or BIGINT)
523 * @param string &$cell The current cell
524 * @return string Size of the given cell in the type-appropriate format
526 function PMA_detectSize($last_cumulative_size, $last_cumulative_type, $curr_type, &$cell) {
527 $curr_size = strlen((string)$cell);
530 * If the cell is NULL, don't treat it as a varchar
532 if (! strcmp('NULL', $cell)) {
533 return $last_cumulative_size;
536 * What to do if the current cell is of type VARCHAR
538 elseif ($curr_type == VARCHAR) {
540 * The last cumulative type was VARCHAR
542 if ($last_cumulative_type == VARCHAR) {
543 if ($curr_size >= $last_cumulative_size) {
544 return $curr_size;
545 } else {
546 return $last_cumulative_size;
550 * The last cumulative type was DECIMAL
552 elseif ($last_cumulative_type == DECIMAL) {
553 $oldM = PMA_getM($last_cumulative_size);
555 if ($curr_size >= $oldM) {
556 return $curr_size;
557 } else {
558 return $oldM;
562 * The last cumulative type was BIGINT or INT
564 elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
565 if ($curr_size >= $last_cumulative_size) {
566 return $curr_size;
567 } else {
568 return $last_cumulative_size;
572 * This is the first row to be analyzed
574 elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
575 return $curr_size;
578 * An error has DEFINITELY occurred
580 else {
582 * TODO: Handle this MUCH more elegantly
585 return -1;
589 * What to do if the current cell is of type DECIMAL
591 elseif ($curr_type == DECIMAL) {
593 * The last cumulative type was VARCHAR
595 if ($last_cumulative_type == VARCHAR) {
596 /* Convert $last_cumulative_size from varchar to decimal format */
597 $size = PMA_getDecimalSize($cell);
599 if ($size[M] >= $last_cumulative_size) {
600 return $size[M];
601 } else {
602 return $last_cumulative_size;
606 * The last cumulative type was DECIMAL
608 elseif ($last_cumulative_type == DECIMAL) {
609 $size = PMA_getDecimalSize($cell);
611 $oldM = PMA_getM($last_cumulative_size);
612 $oldD = PMA_getD($last_cumulative_size);
614 /* New val if M or D is greater than current largest */
615 if ($size[M] > $oldM || $size[D] > $oldD) {
616 /* Take the largest of both types */
617 return (string)((($size[M] > $oldM) ? $size[M] : $oldM) . "," . (($size[D] > $oldD) ? $size[D] : $oldD));
618 } else {
619 return $last_cumulative_size;
623 * The last cumulative type was BIGINT or INT
625 elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
626 /* Convert $last_cumulative_size from int to decimal format */
627 $size = PMA_getDecimalSize($cell);
629 if ($size[M] >= $last_cumulative_size) {
630 return $size[FULL];
631 } else {
632 return ($last_cumulative_size.",".$size[D]);
636 * This is the first row to be analyzed
638 elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
639 /* First row of the column */
640 $size = PMA_getDecimalSize($cell);
642 return $size[FULL];
645 * An error has DEFINITELY occurred
647 else {
649 * TODO: Handle this MUCH more elegantly
652 return -1;
656 * What to do if the current cell is of type BIGINT or INT
658 elseif ($curr_type == BIGINT || $curr_type == INT) {
660 * The last cumulative type was VARCHAR
662 if ($last_cumulative_type == VARCHAR) {
663 if ($curr_size >= $last_cumulative_size) {
664 return $curr_size;
665 } else {
666 return $last_cumulative_size;
670 * The last cumulative type was DECIMAL
672 elseif ($last_cumulative_type == DECIMAL) {
673 $oldM = PMA_getM($last_cumulative_size);
674 $oldD = PMA_getD($last_cumulative_size);
675 $oldInt = $oldM - $oldD;
676 $newInt = strlen((string)$cell);
678 /* See which has the larger integer length */
679 if ($oldInt >= $newInt) {
680 /* Use old decimal size */
681 return $last_cumulative_size;
682 } else {
683 /* Use $newInt + $oldD as new M */
684 return (($newInt + $oldD) . "," . $oldD);
688 * The last cumulative type was BIGINT or INT
690 elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
691 if ($curr_size >= $last_cumulative_size) {
692 return $curr_size;
693 } else {
694 return $last_cumulative_size;
698 * This is the first row to be analyzed
700 elseif (!isset($last_cumulative_type) || $last_cumulative_type == NONE) {
701 return $curr_size;
704 * An error has DEFINITELY occurred
706 else {
708 * TODO: Handle this MUCH more elegantly
711 return -1;
715 * An error has DEFINITELY occurred
717 else {
719 * TODO: Handle this MUCH more elegantly
722 return -1;
727 * Determines what MySQL type a cell is
730 * @access public
732 * @uses DECIMAL
733 * @uses BIGINT
734 * @uses INT
735 * @uses VARCHAR
736 * @uses NONE
737 * @uses is_numeric()
738 * @uses strcmp()
739 * @uses strpos()
740 * @uses substr_count()
741 * @param int $last_cumulative_type Last cumulative column type (VARCHAR or INT or BIGINT or DECIMAL or NONE)
742 * @param string &$cell String representation of the cell for which a best-fit type is to be determined
743 * @return int The MySQL type representation (VARCHAR or INT or BIGINT or DECIMAL or NONE)
745 function PMA_detectType($last_cumulative_type, &$cell) {
747 * If numeric, determine if decimal, int or bigint
748 * Else, we call it varchar for simplicity
751 if (! strcmp('NULL', $cell)) {
752 if ($last_cumulative_type === NULL || $last_cumulative_type == NONE) {
753 return NONE;
754 } else {
755 return $last_cumulative_type;
757 } elseif (is_numeric($cell)) {
758 if ($cell == (string)(float)$cell && strpos($cell, ".") !== false && substr_count($cell, ".") == 1) {
759 return DECIMAL;
760 } else {
761 if (abs($cell) > 2147483647) {
762 return BIGINT;
763 } else {
764 return INT;
767 } else {
768 return VARCHAR;
773 * Determines if the column types are int, decimal, or string
776 * @link http://wiki.phpmyadmin.net/pma/Devel:Import
778 * @todo Handle the error case more elegantly
780 * @access public
782 * @uses TBL_NAME
783 * @uses COL_NAMES
784 * @uses ROWS
785 * @uses VARCHAR
786 * @uses DECIMAL
787 * @uses BIGINT
788 * @uses INT
789 * @uses NONE
790 * @uses count()
791 * @uses is_array()
792 * @uses PMA_detectType()
793 * @uses PMA_detectSize()
794 * @param &$table array(string $table_name, array $col_names, array $rows)
795 * @return array array(array $types, array $sizes)
797 function PMA_analyzeTable(&$table) {
798 /* Get number of rows in table */
799 $numRows = count($table[ROWS]);
800 /* Get number of columns */
801 $numCols = count($table[COL_NAMES]);
802 /* Current type for each column */
803 $types = array();
804 $sizes = array();
806 /* Initialize $sizes to all 0's */
807 for ($i = 0; $i < $numCols; ++$i) {
808 $sizes[$i] = 0;
811 /* Initialize $types to NONE */
812 for ($i = 0; $i < $numCols; ++$i) {
813 $types[$i] = NONE;
816 /* Temp vars */
817 $curr_type = NONE;
818 $curr_size = 0;
820 /* If the passed array is not of the correct form, do not process it */
821 if (is_array($table) && ! is_array($table[TBL_NAME]) && is_array($table[COL_NAMES]) && is_array($table[ROWS])) {
822 /* Analyze each column */
823 for ($i = 0; $i < $numCols; ++$i) {
824 /* Analyze the column in each row */
825 for ($j = 0; $j < $numRows; ++$j) {
826 /* Determine type of the current cell */
827 $curr_type = PMA_detectType($types[$i], $table[ROWS][$j][$i]);
828 /* Determine size of the current cell */
829 $sizes[$i] = PMA_detectSize($sizes[$i], $types[$i], $curr_type, $table[ROWS][$j][$i]);
832 * If a type for this column has already been declared,
833 * only alter it if it was a number and a varchar was found
835 if ($curr_type != NONE) {
836 if ($curr_type == VARCHAR) {
837 $types[$i] = VARCHAR;
838 } else if ($curr_type == DECIMAL) {
839 if ($types[$i] != VARCHAR) {
840 $types[$i] = DECIMAL;
842 } else if ($curr_type == BIGINT) {
843 if ($types[$i] != VARCHAR && $types[$i] != DECIMAL) {
844 $types[$i] = BIGINT;
846 } else if ($curr_type == INT) {
847 if ($types[$i] != VARCHAR && $types[$i] != DECIMAL && $types[$i] != BIGINT) {
848 $types[$i] = INT;
855 /* Check to ensure that all types are valid */
856 $len = count($types);
857 for ($n = 0; $n < $len; ++$n) {
858 if (! strcmp(NONE, $types[$n])) {
859 $types[$n] = VARCHAR;
860 $sizes[$n] = '10';
864 return array($types, $sizes);
866 else
869 * TODO: Handle this better
872 return false;
876 /* Needed to quell the beast that is PMA_Message */
877 $import_notice = NULL;
880 * Builds and executes SQL statements to create the database and tables
881 * as necessary, as well as insert all the data.
884 * @link http://wiki.phpmyadmin.net/pma/Devel:Import
886 * @access public
888 * @uses TBL_NAME
889 * @uses COL_NAMES
890 * @uses ROWS
891 * @uses TYPES
892 * @uses SIZES
893 * @uses strcmp()
894 * @uses count()
895 * @uses preg_match()
896 * @uses preg_replace()
897 * @uses PMA_isView()
898 * @uses PMA_backquote()
899 * @uses PMA_importRunQuery()
900 * @uses PMA_generate_common_url()
901 * @uses PMA_Message::notice()
902 * @param string $db_name Name of the database
903 * @param array &$tables Array of tables for the specified database
904 * @param array &$analyses = NULL Analyses of the tables
905 * @param array &$additional_sql = NULL Additional SQL statements to be executed
906 * @param array $options = NULL Associative array of options
907 * @return void
909 function PMA_buildSQL($db_name, &$tables, &$analyses = NULL, &$additional_sql = NULL, $options = NULL) {
910 /* Take care of the options */
911 if (isset($options['db_collation'])) {
912 $collation = $options['db_collation'];
913 } else {
914 $collation = "utf8_general_ci";
917 if (isset($options['db_charset'])) {
918 $charset = $options['db_charset'];
919 } else {
920 $charset = "utf8";
923 if (isset($options['create_db'])) {
924 $create_db = $options['create_db'];
925 } else {
926 $create_db = true;
929 /* Create SQL code to handle the database */
930 $sql = array();
932 if ($create_db) {
933 $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_backquote($db_name) . " DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation;
937 * The calling plug-in should include this statement, if necessary, in the $additional_sql parameter
939 * $sql[] = "USE " . PMA_backquote($db_name);
942 /* Execute the SQL statements create above */
943 $sql_len = count($sql);
944 for ($i = 0; $i < $sql_len; ++$i) {
945 PMA_importRunQuery($sql[$i], $sql[$i]);
948 /* No longer needed */
949 unset($sql);
951 /* Run the $additional_sql statements supplied by the caller plug-in */
952 if ($additional_sql != NULL) {
953 /* Clean the SQL first */
954 $additional_sql_len = count($additional_sql);
957 * Only match tables for now, because CREATE IF NOT EXISTS
958 * syntax is lacking or nonexisting for views, triggers,
959 * functions, and procedures.
961 * See: http://bugs.mysql.com/bug.php?id=15287
963 * To the best of my knowledge this is still an issue.
965 * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)';
967 $pattern = '/CREATE .*(TABLE)/';
968 $replacement = 'CREATE \\1 IF NOT EXISTS';
970 /* Change CREATE statements to CREATE IF NOT EXISTS to support inserting into existing structures */
971 for ($i = 0; $i < $additional_sql_len; ++$i) {
972 $additional_sql[$i] = preg_replace($pattern, $replacement, $additional_sql[$i]);
973 /* Execute the resulting statements */
974 PMA_importRunQuery($additional_sql[$i], $additional_sql[$i]);
978 if ($analyses != NULL) {
979 $type_array = array(NONE => "NULL", VARCHAR => "varchar", INT => "int", DECIMAL => "decimal", BIGINT => "bigint");
981 /* TODO: Do more checking here to make sure they really are matched */
982 if (count($tables) != count($analyses)) {
983 exit();
986 /* Create SQL code to create the tables */
987 $tempSQLStr = "";
988 $num_tables = count($tables);
989 for ($i = 0; $i < $num_tables; ++$i) {
990 $num_cols = count($tables[$i][COL_NAMES]);
991 $tempSQLStr = "CREATE TABLE IF NOT EXISTS " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
992 for ($j = 0; $j < $num_cols; ++$j) {
993 $size = $analyses[$i][SIZES][$j];
994 if ((int)$size == 0) {
995 $size = 10;
998 $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$j]) . " " . $type_array[$analyses[$i][TYPES][$j]] . "(" . $size . ")";
1000 if ($j != (count($tables[$i][COL_NAMES]) - 1)) {
1001 $tempSQLStr .= ", ";
1004 $tempSQLStr .= ") ENGINE=MyISAM DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation . ";";
1007 * Each SQL statement is executed immediately
1008 * after it is formed so that we don't have
1009 * to store them in a (possibly large) buffer
1011 PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1016 * Create the SQL statements to insert all the data
1018 * Only one insert query is formed for each table
1020 $tempSQLStr = "";
1021 $col_count = 0;
1022 $num_tables = count($tables);
1023 for ($i = 0; $i < $num_tables; ++$i) {
1024 $num_cols = count($tables[$i][COL_NAMES]);
1025 $num_rows = count($tables[$i][ROWS]);
1027 $tempSQLStr = "INSERT INTO " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
1029 for ($m = 0; $m < $num_cols; ++$m) {
1030 $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$m]);
1032 if ($m != ($num_cols - 1)) {
1033 $tempSQLStr .= ", ";
1037 $tempSQLStr .= ") VALUES ";
1039 for ($j = 0; $j < $num_rows; ++$j) {
1040 $tempSQLStr .= "(";
1042 for ($k = 0; $k < $num_cols; ++$k) {
1043 if ($analyses != NULL) {
1044 $is_varchar = ($analyses[$i][TYPES][$col_count] === VARCHAR);
1045 } else {
1046 $is_varchar = !is_numeric($tables[$i][ROWS][$j][$k]);
1049 /* Don't put quotes around NULL fields */
1050 if (! strcmp($tables[$i][ROWS][$j][$k], 'NULL')) {
1051 $is_varchar = false;
1054 $tempSQLStr .= (($is_varchar) ? "'" : "");
1055 $tempSQLStr .= PMA_sqlAddslashes((string)$tables[$i][ROWS][$j][$k]);
1056 $tempSQLStr .= (($is_varchar) ? "'" : "");
1058 if ($k != ($num_cols - 1)) {
1059 $tempSQLStr .= ", ";
1062 if ($col_count == ($num_cols - 1)) {
1063 $col_count = 0;
1064 } else {
1065 $col_count++;
1068 /* Delete the cell after we are done with it */
1069 unset($tables[$i][ROWS][$j][$k]);
1072 $tempSQLStr .= ")";
1074 if ($j != ($num_rows - 1)) {
1075 $tempSQLStr .= ",\n ";
1078 $col_count = 0;
1079 /* Delete the row after we are done with it */
1080 unset($tables[$i][ROWS][$j]);
1083 $tempSQLStr .= ";";
1086 * Each SQL statement is executed immediately
1087 * after it is formed so that we don't have
1088 * to store them in a (possibly large) buffer
1090 PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1093 /* No longer needed */
1094 unset($tempSQLStr);
1097 * A work in progress
1100 /* Add the viewable structures from $additional_sql to $tables so they are also displayed */
1102 $view_pattern = '@VIEW `[^`]+`\.`([^`]+)@';
1103 $table_pattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@';
1104 /* Check a third pattern to make sure its not a "USE `db_name`;" statement */
1106 $regs = array();
1108 $inTables = false;
1110 $additional_sql_len = count($additional_sql);
1111 for ($i = 0; $i < $additional_sql_len; ++$i) {
1112 preg_match($view_pattern, $additional_sql[$i], $regs);
1114 if (count($regs) == 0) {
1115 preg_match($table_pattern, $additional_sql[$i], $regs);
1118 if (count($regs)) {
1119 for ($n = 0; $n < $num_tables; ++$n) {
1120 if (!strcmp($regs[1], $tables[$n][TBL_NAME])) {
1121 $inTables = true;
1122 break;
1126 if (!$inTables) {
1127 $tables[] = array(TBL_NAME => $regs[1]);
1131 /* Reset the array */
1132 $regs = array();
1133 $inTables = false;
1136 $params = array('db' => (string)$db_name);
1137 $db_url = 'db_structure.php' . PMA_generate_common_url($params);
1138 $db_ops_url = 'db_operations.php' . PMA_generate_common_url($params);
1140 $message = '<br /><br />';
1141 $message .= '<strong>' . __('The following structures have either been created or altered. Here you can:') . '</strong><br />';
1142 $message .= '<ul><li>' . __('View a structure`s contents by clicking on its name') . '</li>';
1143 $message .= '<li>' . __('Change any of its settings by clicking the corresponding "Options" link') . '</li>';
1144 $message .= '<li>' . __('Edit its structure by following the "Structure" link') . '</li>';
1145 $message .= sprintf('<br /><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1146 $db_url,
1147 __('Go to database') . ': ' . PMA_backquote($db_name),
1148 $db_name,
1149 $db_ops_url,
1150 __('Edit') . ' ' . PMA_backquote($db_name) . ' ' . __('settings'));
1152 $message .= '<ul>';
1154 unset($params);
1156 $num_tables = count($tables);
1157 for ($i = 0; $i < $num_tables; ++$i)
1159 $params = array('db' => (string)$db_name, 'table' => (string)$tables[$i][TBL_NAME]);
1160 $tbl_url = 'sql.php' . PMA_generate_common_url($params);
1161 $tbl_struct_url = 'tbl_structure.php' . PMA_generate_common_url($params);
1162 $tbl_ops_url = 'tbl_operations.php' . PMA_generate_common_url($params);
1164 unset($params);
1166 if (! PMA_isView($db_name, $tables[$i][TBL_NAME])) {
1167 $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>',
1168 $tbl_url,
1169 __('Go to table') . ': ' . PMA_backquote($tables[$i][TBL_NAME]),
1170 $tables[$i][TBL_NAME],
1171 $tbl_struct_url,
1172 PMA_backquote($tables[$i][TBL_NAME]) . ' ' . __('structure'),
1173 $tbl_ops_url,
1174 __('Edit') . ' ' . PMA_backquote($tables[$i][TBL_NAME]) . ' ' . __('settings'));
1175 } else {
1176 $message .= sprintf('<li><a href="%s" title="%s">%s</a></li>',
1177 $tbl_url,
1178 __('Go to view') . ': ' . PMA_backquote($tables[$i][TBL_NAME]),
1179 $tables[$i][TBL_NAME]);
1183 $message .= '</ul></ul>';
1185 global $import_notice;
1186 $import_notice = $message;
1188 unset($tables);