libraries/import.lib.php

   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
   5  *
   6  * @package phpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * We need to know something about user
  14  */
  15 require_once './libraries/check_user_privileges.lib.php';
  16
  17 /**
  18  * We do this check, DROP DATABASE does not need to be confirmed elsewhere
  19  */
  20 define('PMA_CHK_DROP', 1);
  21
  22 /**
  23  *  Check whether timeout is getting close
  24  *
  25  *  @return boolean true if timeout is close
  26  *  @access public
  27  */
  28 function PMA_checkTimeout()
  29 {
  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;
  41     }
  42 }
  43
  44 /**
  45  *  Detects what compression filse uses
  46  *
  47  *  @param string  $filepath  filename to check
  48  *  @return string MIME type of compression, none for none
  49  *  @access public
  50  */
  51 function PMA_detectCompression($filepath)
  52 {
  53     $file = @fopen($filepath, 'rb');
  54     if (!$file) {
  55         return false;
  56     }
  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';
  62     }
  63     if ($len >= 3 && substr($test, 0, 3) == 'BZh') {
  64         return 'application/bzip2';
  65     }
  66     if ($len >= 4 && $test == "PK\003\004") {
  67         return 'application/zip';
  68     }
  69     return 'none';
  70 }
  71
  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.
  75  *
  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
  80  */
  81 function PMA_importRunQuery($sql = '', $full = '', $controluser = false)
  82 {
  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'];
  98                 }
  99                 if (!$cfg['AllowUserDropDatabase']
 100                  && !$is_superuser
 101                  && preg_match('@^[[:space:]]*DROP[[:space:]]+(IF EXISTS[[:space:]]+)?DATABASE @i', $import_run_buffer['sql'])) {
 102                     $GLOBALS['message'] = PMA_Message::error(__('"DROP DATABASE" statements are disabled.'));
 103                     $error = true;
 104                 } else {
 105                     $executed_queries++;
 106                     if ($run_query && $GLOBALS['finished'] && empty($sql) && !$error && (
 107                             (!empty($import_run_buffer['sql']) && preg_match('/^[\s]*(SELECT|SHOW|HANDLER)/i', $import_run_buffer['sql'])) ||
 108                             ($executed_queries == 1)
 109                             )) {
 110                         $go_sql = true;
 111                         if (!$sql_query_disabled) {
 112                             $complete_query = $sql_query;
 113                             $display_query = $sql_query;
 114                         } else {
 115                             $complete_query = '';
 116                             $display_query = '';
 117                         }
 118                         $sql_query = $import_run_buffer['sql'];
 119                         // If a 'USE <db>' SQL-clause was found, set our current $db to the new one
 120                         list($db, $reload) = PMA_lookForUse($import_run_buffer['sql'], $db, $reload);
 121                     } elseif ($run_query) {
 122                         if ($controluser) {
 123                             $result = PMA_query_as_controluser($import_run_buffer['sql']);
 124                         } else {
 125                             $result = PMA_DBI_try_query($import_run_buffer['sql']);
 126                         }
 127                         $msg = '# ';
 128                         if ($result === false) { // execution failed
 129                             if (! isset($my_die)) {
 130                                 $my_die = array();
 131                             }
 132                             $my_die[] = array('sql' => $import_run_buffer['full'], 'error' => PMA_DBI_getError());
 133
 134                             if ($cfg['VerboseMultiSubmit']) {
 135                                 $msg .= __('Error');
 136                             }
 137
 138                             if (!$cfg['IgnoreMultiSubmitErrors']) {
 139                                 $error = true;
 140                                 return;
 141                             }
 142                         } elseif ($cfg['VerboseMultiSubmit']) {
 143                             $a_num_rows = (int)@PMA_DBI_num_rows($result);
 144                             $a_aff_rows = (int)@PMA_DBI_affected_rows();
 145                             if ($a_num_rows > 0) {
 146                                 $msg .= __('Rows'). ': ' . $a_num_rows;
 147                                 $last_query_with_results = $import_run_buffer['sql'];
 148                             } elseif ($a_aff_rows > 0) {
 149                                 $message = PMA_Message::affected_rows($a_aff_rows);
 150                                 $msg .= $message->getMessage();
 151                             } else {
 152                                 $msg .= __('MySQL returned an empty result set (i.e. zero rows).');
 153                             }
 154                         }
 155                         if (!$sql_query_disabled) {
 156                             $sql_query .= $msg . "\n";
 157                         }
 158
 159                         // If a 'USE <db>' SQL-clause was found and the query succeeded, set our current $db to the new one
 160                         if ($result != false) {
 161                             list($db, $reload) = PMA_lookForUse($import_run_buffer['sql'], $db, $reload);
 162                         }
 163
 164                         if ($result != false && preg_match('@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)?(TABLE|DATABASE)[[:space:]]+(.+)@im', $import_run_buffer['sql'])) {
 165                             $reload = true;
 166                         }
 167                     } // end run query
 168                 } // end if not DROP DATABASE
 169             } // end non empty query
 170             elseif (!empty($import_run_buffer['full'])) {
 171                 if ($go_sql) {
 172                     $complete_query .= $import_run_buffer['full'];
 173                     $display_query .= $import_run_buffer['full'];
 174                 } else {
 175                     if (!$sql_query_disabled) {
 176                         $sql_query .= $import_run_buffer['full'];
 177                     }
 178                 }
 179             }
 180             // check length of query unless we decided to pass it to sql.php
 181             // (if $run_query is false, we are just displaying so show
 182             // the complete query in the textarea)
 183             if (! $go_sql && $run_query) {
 184                 if ($cfg['VerboseMultiSubmit'] && ! empty($sql_query)) {
 185                     if (strlen($sql_query) > 50000 || $executed_queries > 50 || $max_sql_len > 1000) {
 186                         $sql_query = '';
 187                         $sql_query_disabled = true;
 188                     }
 189                 } else {
 190                     if (strlen($sql_query) > 10000 || $executed_queries > 10 || $max_sql_len > 500) {
 191                         $sql_query = '';
 192                         $sql_query_disabled = true;
 193                     }
 194                 }
 195             }
 196         } // end do query (no skip)
 197     } // end buffer exists
 198
 199     // Do we have something to push into buffer?
 200     if (!empty($sql) || !empty($full)) {
 201         $import_run_buffer = array('sql' => $sql, 'full' => $full);
 202     } else {
 203         unset($GLOBALS['import_run_buffer']);
 204     }
 205 }
 206
 207 /**
 208  * Looks for the presence of USE to possibly change current db
 209  *
 210  * @param string  $buffer  buffer to examine
 211  * @param string  $db      current db
 212  * @param bool    $reload  reload
 213  * @return array (current or new db, whether to reload)
 214  * @access public
 215  */
 216 function PMA_lookForUse($buffer, $db, $reload)
 217 {
 218     if (preg_match('@^[\s]*USE[[:space:]]+([\S]+)@i', $buffer, $match)) {
 219         $db = trim($match[1]);
 220         $db = trim($db, ';'); // for example, USE abc;
 221         $reload = true;
 222     }
 223     return(array($db, $reload));
 224 }
 225
 226
 227 /**
 228  * Returns next part of imported file/buffer
 229  *
 230  * @param int  $size  size of buffer to read (this is maximal size function will return)
 231  * @return string part of file/buffer
 232  * @access public
 233  */
 234 function PMA_importGetNextChunk($size = 32768)
 235 {
 236     global $compression, $import_handle, $charset_conversion, $charset_of_file,
 237         $read_multiply;
 238
 239     // Add some progression while reading large amount of data
 240     if ($read_multiply <= 8) {
 241         $size *= $read_multiply;
 242     } else {
 243         $size *= 8;
 244     }
 245     $read_multiply++;
 246
 247     // We can not read too much
 248     if ($size > $GLOBALS['read_limit']) {
 249         $size = $GLOBALS['read_limit'];
 250     }
 251
 252     if (PMA_checkTimeout()) {
 253         return false;
 254     }
 255     if ($GLOBALS['finished']) {
 256         return true;
 257     }
 258
 259     if ($GLOBALS['import_file'] == 'none') {
 260         // Well this is not yet supported and tested, but should return content of textarea
 261         if (strlen($GLOBALS['import_text']) < $size) {
 262             $GLOBALS['finished'] = true;
 263             return $GLOBALS['import_text'];
 264         } else {
 265             $r = substr($GLOBALS['import_text'], 0, $size);
 266             $GLOBALS['offset'] += $size;
 267             $GLOBALS['import_text'] = substr($GLOBALS['import_text'], $size);
 268             return $r;
 269         }
 270     }
 271
 272     switch ($compression) {
 273         case 'application/bzip2':
 274             $result = bzread($import_handle, $size);
 275             $GLOBALS['finished'] = feof($import_handle);
 276             break;
 277         case 'application/gzip':
 278             $result = gzread($import_handle, $size);
 279             $GLOBALS['finished'] = feof($import_handle);
 280             break;
 281         case 'application/zip':
 282             $result = substr($GLOBALS['import_text'], 0, $size);
 283             $GLOBALS['import_text'] = substr($GLOBALS['import_text'], $size);
 284             $GLOBALS['finished'] = empty($GLOBALS['import_text']);
 285             break;
 286         case 'none':
 287             $result = fread($import_handle, $size);
 288             $GLOBALS['finished'] = feof($import_handle);
 289             break;
 290     }
 291     $GLOBALS['offset'] += $size;
 292
 293     if ($charset_conversion) {
 294         return PMA_convert_string($charset_of_file, 'utf-8', $result);
 295     } else {
 296         /**
 297          * Skip possible byte order marks (I do not think we need more
 298          * charsets, but feel free to add more, you can use wikipedia for
 299          * reference: <http://en.wikipedia.org/wiki/Byte_Order_Mark>)
 300          *
 301          * @todo BOM could be used for charset autodetection
 302          */
 303         if ($GLOBALS['offset'] == $size) {
 304             // UTF-8
 305             if (strncmp($result, "\xEF\xBB\xBF", 3) == 0) {
 306                 $result = substr($result, 3);
 307             // UTF-16 BE, LE
 308             } elseif (strncmp($result, "\xFE\xFF", 2) == 0 || strncmp($result, "\xFF\xFE", 2) == 0) {
 309                 $result = substr($result, 2);
 310             }
 311         }
 312         return $result;
 313     }
 314 }
 315
 316 /**
 317  * Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
 318  *
 319  * This functions uses recursion to build the Excel column name.
 320  *
 321  * The column number (1-26) is converted to the responding ASCII character (A-Z) and returned.
 322  *
 323  * If the column number is bigger than 26 (= num of letters in alfabet),
 324  * an extra character needs to be added. To find this extra character, the number is divided by 26
 325  * and this value is passed to another instance of the same function (hence recursion).
 326  * In that new instance the number is evaluated again, and if it is still bigger than 26, it is divided again
 327  * and passed to another instance of the same function. This continues until the number is smaller than 26.
 328  * Then the last called function returns the corresponding ASCII character to the function that called it.
 329  * Each time a called function ends an extra character is added to the column name.
 330  * When the first function is reached, the last character is addded and the complete column name is returned.
 331  *
 332  * @access  public
 333  *
 334  * @param int $num
 335  * @return  string The column's "Excel" name
 336  */
 337 function PMA_getColumnAlphaName($num)
 338 {
 339     $A = 65; // ASCII value for capital "A"
 340     $col_name = "";
 341
 342     if ($num > 26) {
 343         $div = (int)($num / 26);
 344         $remain = (int)($num % 26);
 345
 346         // subtract 1 of divided value in case the modulus is 0,
 347         // this is necessary because A-Z has no 'zero'
 348         if ($remain == 0) {
 349             $div--;
 350         }
 351
 352         // recursive function call
 353         $col_name = PMA_getColumnAlphaName($div);
 354         // use modulus as new column number
 355         $num = $remain;
 356     }
 357
 358     if ($num == 0) {
 359         // use 'Z' if column number is 0,
 360         // this is necessary because A-Z has no 'zero'
 361         $col_name .= chr(($A + 26) - 1);
 362     } else {
 363         // convert column number to ASCII character
 364         $col_name .= chr(($A + $num) - 1);
 365     }
 366
 367     return $col_name;
 368 }
 369
 370 /**
 371  * Returns the column number based on the Excel name.
 372  * So "A" = 1, "Z" = 26, "AA" = 27, etc.
 373  *
 374  * Basicly this is a base26 (A-Z) to base10 (0-9) conversion.
 375  * It iterates through all characters in the column name and
 376  * calculates the corresponding value, based on character value
 377  * (A = 1, ..., Z = 26) and position in the string.
 378  *
 379  * @access  public
 380  *
 381  * @param string $name (i.e. "A", or "BC", etc.)
 382  * @return  int The column number
 383  */
 384 function PMA_getColumnNumberFromName($name) {
 385     if (!empty($name)) {
 386         $name = strtoupper($name);
 387         $num_chars = strlen($name);
 388         $column_number = 0;
 389         for ($i = 0; $i < $num_chars; ++$i) {
 390         // read string from back to front
 391         $char_pos = ($num_chars - 1) - $i;
 392
 393         // convert capital character to ASCII value
 394         // and subtract 64 to get corresponding decimal value
 395         // ASCII value of "A" is 65, "B" is 66, etc.
 396         // Decimal equivalent of "A" is 1, "B" is 2, etc.
 397         $number = (ord($name[$char_pos]) - 64);
 398
 399         // base26 to base10 conversion : multiply each number
 400         // with corresponding value of the position, in this case
 401         // $i=0 : 1; $i=1 : 26; $i=2 : 676; ...
 402         $column_number += $number * pow(26,$i);
 403         }
 404         return $column_number;
 405     } else {
 406         return 0;
 407     }
 408 }
 409
 410 /**
 411  * Constants definitions
 412  */
 413
 414 /* MySQL type defs */
 415 define("NONE",      0);
 416 define("VARCHAR",   1);
 417 define("INT",       2);
 418 define("DECIMAL",   3);
 419 define("BIGINT",    4);
 420
 421 /* Decimal size defs */
 422 define("M",         0);
 423 define("D",         1);
 424 define("FULL",      2);
 425
 426 /* Table array defs */
 427 define("TBL_NAME",  0);
 428 define("COL_NAMES", 1);
 429 define("ROWS",      2);
 430
 431 /* Analysis array defs */
 432 define("TYPES",     0);
 433 define("SIZES",     1);
 434
 435 /**
 436  * Obtains the precision (total # of digits) from a size of type decimal
 437  *
 438  * @access  public
 439  *
 440  * @param string $last_cumulative_size
 441  * @return  int Precision of the given decimal size notation
 442  */
 443 function PMA_getM($last_cumulative_size) {
 444     return (int)substr($last_cumulative_size, 0, strpos($last_cumulative_size, ","));
 445 }
 446
 447 /**
 448  * Obtains the scale (# of digits to the right of the decimal point) from a size of type decimal
 449  *
 450  * @access  public
 451  *
 452  * @param string $last_cumulative_size
 453  * @return  int Scale of the given decimal size notation
 454  */
 455 function PMA_getD($last_cumulative_size) {
 456     return (int)substr($last_cumulative_size, (strpos($last_cumulative_size, ",") + 1), (strlen($last_cumulative_size) - strpos($last_cumulative_size, ",")));
 457 }
 458
 459 /**
 460  * Obtains the decimal size of a given cell
 461  *
 462  * @access  public
 463  *
 464  * @param string &$cell
 465  * @return  array Contains the precision, scale, and full size representation of the given decimal cell
 466  */
 467 function PMA_getDecimalSize(&$cell) {
 468     $curr_size = strlen((string)$cell);
 469     $decPos = strpos($cell, ".");
 470     $decPrecision = ($curr_size - 1) - $decPos;
 471
 472     $m = $curr_size - 1;
 473     $d = $decPrecision;
 474
 475     return array($m, $d, ($m . "," . $d));
 476 }
 477
 478 /**
 479  * Obtains the size of the given cell
 480  *
 481  * @todo    Handle the error cases more elegantly
 482  *
 483  * @access  public
 484  *
 485  * @param string $last_cumulative_size    Last cumulative column size
 486  * @param int    $last_cumulative_type    Last cumulative column type  (NONE or VARCHAR or DECIMAL or INT or BIGINT)
 487  * @param int    $curr_type               Type of the current cell     (NONE or VARCHAR or DECIMAL or INT or BIGINT)
 488  * @param string &$cell                   The current cell
 489  * @return  string                          Size of the given cell in the type-appropriate format
 490  */
 491 function PMA_detectSize($last_cumulative_size, $last_cumulative_type, $curr_type, &$cell) {
 492     $curr_size = strlen((string)$cell);
 493
 494     /**
 495      * If the cell is NULL, don't treat it as a varchar
 496      */
 497     if (! strcmp('NULL', $cell)) {
 498         return $last_cumulative_size;
 499     }
 500     /**
 501      * What to do if the current cell is of type VARCHAR
 502      */
 503     elseif ($curr_type == VARCHAR) {
 504         /**
 505          * The last cumulative type was VARCHAR
 506          */
 507         if ($last_cumulative_type == VARCHAR) {
 508             if ($curr_size >= $last_cumulative_size) {
 509                 return $curr_size;
 510             } else {
 511                 return $last_cumulative_size;
 512             }
 513         }
 514         /**
 515          * The last cumulative type was DECIMAL
 516          */
 517         elseif ($last_cumulative_type == DECIMAL) {
 518             $oldM = PMA_getM($last_cumulative_size);
 519
 520             if ($curr_size >= $oldM) {
 521                 return $curr_size;
 522             } else {
 523                 return $oldM;
 524             }
 525         }
 526         /**
 527          * The last cumulative type was BIGINT or INT
 528          */
 529         elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
 530             if ($curr_size >= $last_cumulative_size) {
 531                 return $curr_size;
 532             } else {
 533                 return $last_cumulative_size;
 534             }
 535         }
 536         /**
 537          * This is the first row to be analyzed
 538          */
 539         elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
 540             return $curr_size;
 541         }
 542         /**
 543          * An error has DEFINITELY occurred
 544          */
 545         else {
 546             /**
 547              * TODO: Handle this MUCH more elegantly
 548              */
 549
 550             return -1;
 551         }
 552     }
 553     /**
 554      * What to do if the current cell is of type DECIMAL
 555      */
 556     elseif ($curr_type == DECIMAL) {
 557         /**
 558          * The last cumulative type was VARCHAR
 559          */
 560         if ($last_cumulative_type == VARCHAR) {
 561             /* Convert $last_cumulative_size from varchar to decimal format */
 562             $size = PMA_getDecimalSize($cell);
 563
 564             if ($size[M] >= $last_cumulative_size) {
 565                 return $size[M];
 566             } else {
 567                 return $last_cumulative_size;
 568             }
 569         }
 570         /**
 571          * The last cumulative type was DECIMAL
 572          */
 573         elseif ($last_cumulative_type == DECIMAL) {
 574             $size = PMA_getDecimalSize($cell);
 575
 576             $oldM = PMA_getM($last_cumulative_size);
 577             $oldD = PMA_getD($last_cumulative_size);
 578
 579             /* New val if M or D is greater than current largest */
 580             if ($size[M] > $oldM || $size[D] > $oldD) {
 581                 /* Take the largest of both types */
 582                 return (string)((($size[M] > $oldM) ? $size[M] : $oldM) . "," . (($size[D] > $oldD) ? $size[D] : $oldD));
 583             } else {
 584                 return $last_cumulative_size;
 585             }
 586         }
 587         /**
 588          * The last cumulative type was BIGINT or INT
 589          */
 590         elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
 591             /* Convert $last_cumulative_size from int to decimal format */
 592             $size = PMA_getDecimalSize($cell);
 593
 594             if ($size[M] >= $last_cumulative_size) {
 595                 return $size[FULL];
 596             } else {
 597                 return ($last_cumulative_size.",".$size[D]);
 598             }
 599         }
 600         /**
 601          * This is the first row to be analyzed
 602          */
 603         elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
 604             /* First row of the column */
 605             $size = PMA_getDecimalSize($cell);
 606
 607             return $size[FULL];
 608         }
 609         /**
 610          * An error has DEFINITELY occurred
 611          */
 612         else {
 613             /**
 614              * TODO: Handle this MUCH more elegantly
 615              */
 616
 617             return -1;
 618         }
 619     }
 620     /**
 621      * What to do if the current cell is of type BIGINT or INT
 622      */
 623     elseif ($curr_type == BIGINT || $curr_type == INT) {
 624         /**
 625          * The last cumulative type was VARCHAR
 626          */
 627         if ($last_cumulative_type == VARCHAR) {
 628             if ($curr_size >= $last_cumulative_size) {
 629                 return $curr_size;
 630             } else {
 631                 return $last_cumulative_size;
 632             }
 633         }
 634         /**
 635          * The last cumulative type was DECIMAL
 636          */
 637         elseif ($last_cumulative_type == DECIMAL) {
 638             $oldM = PMA_getM($last_cumulative_size);
 639             $oldD = PMA_getD($last_cumulative_size);
 640             $oldInt = $oldM - $oldD;
 641             $newInt = strlen((string)$cell);
 642
 643             /* See which has the larger integer length */
 644             if ($oldInt >= $newInt) {
 645                 /* Use old decimal size */
 646                 return $last_cumulative_size;
 647             } else {
 648                 /* Use $newInt + $oldD as new M */
 649                 return (($newInt + $oldD) . "," . $oldD);
 650             }
 651         }
 652         /**
 653          * The last cumulative type was BIGINT or INT
 654          */
 655         elseif ($last_cumulative_type == BIGINT || $last_cumulative_type == INT) {
 656             if ($curr_size >= $last_cumulative_size) {
 657                 return $curr_size;
 658             } else {
 659                 return $last_cumulative_size;
 660             }
 661         }
 662         /**
 663          * This is the first row to be analyzed
 664          */
 665         elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
 666             return $curr_size;
 667         }
 668         /**
 669          * An error has DEFINITELY occurred
 670          */
 671         else {
 672             /**
 673              * TODO: Handle this MUCH more elegantly
 674              */
 675
 676             return -1;
 677         }
 678     }
 679     /**
 680      * An error has DEFINITELY occurred
 681      */
 682     else {
 683         /**
 684          * TODO: Handle this MUCH more elegantly
 685          */
 686
 687         return -1;
 688     }
 689 }
 690
 691 /**
 692  * Determines what MySQL type a cell is
 693  *
 694  * @access  public
 695  *
 696  * @param int    $last_cumulative_type  Last cumulative column type  (VARCHAR or INT or BIGINT or DECIMAL or NONE)
 697  * @param string &$cell                 String representation of the cell for which a best-fit type is to be determined
 698  * @return  int                           The MySQL type representation (VARCHAR or INT or BIGINT or DECIMAL or NONE)
 699  */
 700 function PMA_detectType($last_cumulative_type, &$cell) {
 701     /**
 702      * If numeric, determine if decimal, int or bigint
 703      * Else, we call it varchar for simplicity
 704      */
 705
 706     if (! strcmp('NULL', $cell)) {
 707         if ($last_cumulative_type === null || $last_cumulative_type == NONE) {
 708             return NONE;
 709         } else {
 710             return $last_cumulative_type;
 711         }
 712     } elseif (is_numeric($cell)) {
 713         if ($cell == (string)(float)$cell && strpos($cell, ".") !== false && substr_count($cell, ".") == 1) {
 714             return DECIMAL;
 715         } else {
 716         if (abs($cell) > 2147483647) {
 717             return BIGINT;
 718         } else {
 719             return INT;
 720         }
 721         }
 722     } else {
 723         return VARCHAR;
 724     }
 725 }
 726
 727 /**
 728  * Determines if the column types are int, decimal, or string
 729  *
 730  * @link http://wiki.phpmyadmin.net/pma/Import
 731  *
 732  * @todo    Handle the error case more elegantly
 733  *
 734  * @access  public
 735  *
 736  * @param &$table  array(string $table_name, array $col_names, array $rows)
 737  * @return  array    array(array $types, array $sizes)
 738  */
 739 function PMA_analyzeTable(&$table) {
 740     /* Get number of rows in table */
 741     $numRows = count($table[ROWS]);
 742     /* Get number of columns */
 743     $numCols = count($table[COL_NAMES]);
 744     /* Current type for each column */
 745     $types = array();
 746     $sizes = array();
 747
 748     /* Initialize $sizes to all 0's */
 749     for ($i = 0; $i < $numCols; ++$i) {
 750         $sizes[$i] = 0;
 751     }
 752
 753     /* Initialize $types to NONE */
 754     for ($i = 0; $i < $numCols; ++$i) {
 755         $types[$i] = NONE;
 756     }
 757
 758     /* Temp vars */
 759     $curr_type = NONE;
 760     $curr_size = 0;
 761
 762     /* If the passed array is not of the correct form, do not process it */
 763     if (is_array($table) && ! is_array($table[TBL_NAME]) && is_array($table[COL_NAMES]) && is_array($table[ROWS])) {
 764         /* Analyze each column */
 765         for ($i = 0; $i < $numCols; ++$i) {
 766             /* Analyze the column in each row */
 767             for ($j = 0; $j < $numRows; ++$j) {
 768                 /* Determine type of the current cell */
 769                 $curr_type = PMA_detectType($types[$i], $table[ROWS][$j][$i]);
 770                 /* Determine size of the current cell */
 771                 $sizes[$i] = PMA_detectSize($sizes[$i], $types[$i], $curr_type, $table[ROWS][$j][$i]);
 772
 773                 /**
 774                  * If a type for this column has already been declared,
 775                  * only alter it if it was a number and a varchar was found
 776                  */
 777                 if ($curr_type != NONE) {
 778                     if ($curr_type == VARCHAR) {
 779                         $types[$i] = VARCHAR;
 780                     } else if ($curr_type == DECIMAL) {
 781                         if ($types[$i] != VARCHAR) {
 782                             $types[$i] = DECIMAL;
 783                         }
 784                     } else if ($curr_type == BIGINT) {
 785                         if ($types[$i] != VARCHAR && $types[$i] != DECIMAL) {
 786                             $types[$i] = BIGINT;
 787                         }
 788                     } else if ($curr_type == INT) {
 789                         if ($types[$i] != VARCHAR && $types[$i] != DECIMAL && $types[$i] != BIGINT) {
 790                             $types[$i] = INT;
 791                         }
 792                     }
 793                 }
 794             }
 795         }
 796
 797         /* Check to ensure that all types are valid */
 798         $len = count($types);
 799         for ($n = 0; $n < $len; ++$n) {
 800             if (! strcmp(NONE, $types[$n])) {
 801                 $types[$n] = VARCHAR;
 802                 $sizes[$n] = '10';
 803             }
 804         }
 805
 806         return array($types, $sizes);
 807     }
 808     else
 809     {
 810         /**
 811          * TODO: Handle this better
 812          */
 813
 814         return false;
 815     }
 816 }
 817
 818 /* Needed to quell the beast that is PMA_Message */
 819 $import_notice = null;
 820
 821 /**
 822  * Builds and executes SQL statements to create the database and tables
 823  * as necessary, as well as insert all the data.
 824  *
 825  * @link http://wiki.phpmyadmin.net/pma/Import
 826  *
 827  * @access  public
 828  *
 829  * @param string  $db_name          Name of the database
 830  * @param array   &$tables          Array of tables for the specified database
 831  * @param array   &$analyses        Analyses of the tables
 832  * @param array   &$additional_sql  Additional SQL statements to be executed
 833  * @param array   $options          Associative array of options
 834  * @return  void
 835  */
 836 function PMA_buildSQL($db_name, &$tables, &$analyses = null, &$additional_sql = null, $options = null) {
 837     /* Take care of the options */
 838     if (isset($options['db_collation'])&& ! is_null($options['db_collation'])) {
 839         $collation = $options['db_collation'];
 840     } else {
 841         $collation = "utf8_general_ci";
 842     }
 843
 844     if (isset($options['db_charset']) && ! is_null($options['db_charset'])) {
 845         $charset = $options['db_charset'];
 846     } else {
 847         $charset = "utf8";
 848     }
 849
 850     if (isset($options['create_db'])) {
 851         $create_db = $options['create_db'];
 852     } else {
 853         $create_db = true;
 854     }
 855
 856     /* Create SQL code to handle the database */
 857     $sql = array();
 858
 859     if ($create_db) {
 860         if (PMA_DRIZZLE) {
 861             $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_backquote($db_name) . " COLLATE " . $collation;
 862         } else {
 863             $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_backquote($db_name) . " DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation;
 864         }
 865     }
 866
 867     /**
 868      * The calling plug-in should include this statement, if necessary, in the $additional_sql parameter
 869      *
 870      * $sql[] = "USE " . PMA_backquote($db_name);
 871      */
 872
 873     /* Execute the SQL statements create above */
 874     $sql_len = count($sql);
 875     for ($i = 0; $i < $sql_len; ++$i) {
 876         PMA_importRunQuery($sql[$i], $sql[$i]);
 877     }
 878
 879     /* No longer needed */
 880     unset($sql);
 881
 882     /* Run the $additional_sql statements supplied by the caller plug-in */
 883     if ($additional_sql != null) {
 884         /* Clean the SQL first */
 885         $additional_sql_len = count($additional_sql);
 886
 887         /**
 888          * Only match tables for now, because CREATE IF NOT EXISTS
 889          * syntax is lacking or nonexisting for views, triggers,
 890          * functions, and procedures.
 891          *
 892          * See: http://bugs.mysql.com/bug.php?id=15287
 893          *
 894          * To the best of my knowledge this is still an issue.
 895          *
 896          * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)';
 897          */
 898         $pattern = '/CREATE .*(TABLE)/';
 899         $replacement = 'CREATE \\1 IF NOT EXISTS';
 900
 901         /* Change CREATE statements to CREATE IF NOT EXISTS to support inserting into existing structures */
 902         for ($i = 0; $i < $additional_sql_len; ++$i) {
 903             $additional_sql[$i] = preg_replace($pattern, $replacement, $additional_sql[$i]);
 904             /* Execute the resulting statements */
 905             PMA_importRunQuery($additional_sql[$i], $additional_sql[$i]);
 906         }
 907     }
 908
 909     if ($analyses != null) {
 910         $type_array = array(NONE => "NULL", VARCHAR => "varchar", INT => "int", DECIMAL => "decimal", BIGINT => "bigint");
 911
 912         /* TODO: Do more checking here to make sure they really are matched */
 913         if (count($tables) != count($analyses)) {
 914             exit();
 915         }
 916
 917         /* Create SQL code to create the tables */
 918         $tempSQLStr = "";
 919         $num_tables = count($tables);
 920         for ($i = 0; $i < $num_tables; ++$i) {
 921             $num_cols = count($tables[$i][COL_NAMES]);
 922             $tempSQLStr = "CREATE TABLE IF NOT EXISTS " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
 923             for ($j = 0; $j < $num_cols; ++$j) {
 924                 $size = $analyses[$i][SIZES][$j];
 925                 if ((int)$size == 0) {
 926                     $size = 10;
 927                 }
 928
 929                 $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$j]) . " " . $type_array[$analyses[$i][TYPES][$j]] . "(" . $size . ")";
 930
 931                 if ($j != (count($tables[$i][COL_NAMES]) - 1)) {
 932                     $tempSQLStr .= ", ";
 933                 }
 934             }
 935             $tempSQLStr .= ") DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation . ";";
 936
 937             /**
 938              * Each SQL statement is executed immediately
 939              * after it is formed so that we don't have
 940              * to store them in a (possibly large) buffer
 941              */
 942             PMA_importRunQuery($tempSQLStr, $tempSQLStr);
 943         }
 944     }
 945
 946     /**
 947      * Create the SQL statements to insert all the data
 948      *
 949      * Only one insert query is formed for each table
 950      */
 951     $tempSQLStr = "";
 952     $col_count = 0;
 953     $num_tables = count($tables);
 954     for ($i = 0; $i < $num_tables; ++$i) {
 955         $num_cols = count($tables[$i][COL_NAMES]);
 956         $num_rows = count($tables[$i][ROWS]);
 957
 958         $tempSQLStr = "INSERT INTO " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
 959
 960         for ($m = 0; $m < $num_cols; ++$m) {
 961             $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$m]);
 962
 963             if ($m != ($num_cols - 1)) {
 964                 $tempSQLStr .= ", ";
 965             }
 966         }
 967
 968         $tempSQLStr .= ") VALUES ";
 969
 970         for ($j = 0; $j < $num_rows; ++$j) {
 971             $tempSQLStr .= "(";
 972
 973             for ($k = 0; $k < $num_cols; ++$k) {
 974                 if ($analyses != null) {
 975                     $is_varchar = ($analyses[$i][TYPES][$col_count] === VARCHAR);
 976                 } else {
 977                     $is_varchar = !is_numeric($tables[$i][ROWS][$j][$k]);
 978                 }
 979
 980                 /* Don't put quotes around NULL fields */
 981                 if (! strcmp($tables[$i][ROWS][$j][$k], 'NULL')) {
 982                     $is_varchar = false;
 983                 }
 984
 985                 $tempSQLStr .= (($is_varchar) ? "'" : "");
 986                 $tempSQLStr .= PMA_sqlAddSlashes((string)$tables[$i][ROWS][$j][$k]);
 987                 $tempSQLStr .= (($is_varchar) ? "'" : "");
 988
 989                 if ($k != ($num_cols - 1)) {
 990                     $tempSQLStr .= ", ";
 991                 }
 992
 993                 if ($col_count == ($num_cols - 1)) {
 994                     $col_count = 0;
 995                 } else {
 996                     $col_count++;
 997                 }
 998
 999                 /* Delete the cell after we are done with it */
1000                 unset($tables[$i][ROWS][$j][$k]);
1001             }
1002
1003             $tempSQLStr .= ")";
1004
1005             if ($j != ($num_rows - 1)) {
1006                 $tempSQLStr .= ",\n ";
1007             }
1008
1009             $col_count = 0;
1010             /* Delete the row after we are done with it */
1011             unset($tables[$i][ROWS][$j]);
1012         }
1013
1014         $tempSQLStr .= ";";
1015
1016         /**
1017          * Each SQL statement is executed immediately
1018          * after it is formed so that we don't have
1019          * to store them in a (possibly large) buffer
1020          */
1021         PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1022     }
1023
1024     /* No longer needed */
1025     unset($tempSQLStr);
1026
1027     /**
1028      * A work in progress
1029      */
1030
1031     /* Add the viewable structures from $additional_sql to $tables so they are also displayed */
1032
1033     $view_pattern = '@VIEW `[^`]+`\.`([^`]+)@';
1034     $table_pattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@';
1035     /* Check a third pattern to make sure its not a "USE `db_name`;" statement */
1036
1037     $regs = array();
1038
1039     $inTables = false;
1040
1041     $additional_sql_len = count($additional_sql);
1042     for ($i = 0; $i < $additional_sql_len; ++$i) {
1043         preg_match($view_pattern, $additional_sql[$i], $regs);
1044
1045         if (count($regs) == 0) {
1046             preg_match($table_pattern, $additional_sql[$i], $regs);
1047         }
1048
1049         if (count($regs)) {
1050             for ($n = 0; $n < $num_tables; ++$n) {
1051                 if (!strcmp($regs[1], $tables[$n][TBL_NAME])) {
1052                     $inTables = true;
1053                     break;
1054                 }
1055             }
1056
1057             if (!$inTables) {
1058                 $tables[] = array(TBL_NAME => $regs[1]);
1059             }
1060         }
1061
1062         /* Reset the array */
1063         $regs = array();
1064         $inTables = false;
1065     }
1066
1067     $params = array('db' => (string)$db_name);
1068     $db_url = 'db_structure.php' . PMA_generate_common_url($params);
1069     $db_ops_url = 'db_operations.php' . PMA_generate_common_url($params);
1070
1071     $message = '<br /><br />';
1072     $message .= '<strong>' . __('The following structures have either been created or altered. Here you can:') . '</strong><br />';
1073     $message .= '<ul><li>' . __('View a structure`s contents by clicking on its name') . '</li>';
1074     $message .= '<li>' . __('Change any of its settings by clicking the corresponding "Options" link') . '</li>';
1075     $message .= '<li>' . __('Edit its structure by following the "Structure" link') . '</li>';
1076     $message .= sprintf('<br /><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1077                         $db_url,
1078                         __('Go to database') . ': ' . PMA_backquote($db_name),
1079                         $db_name,
1080                         $db_ops_url,
1081                         __('Edit') . ' ' . PMA_backquote($db_name) . ' ' . __('settings'));
1082
1083     $message .= '<ul>';
1084
1085     unset($params);
1086
1087     $num_tables = count($tables);
1088     for ($i = 0; $i < $num_tables; ++$i)
1089     {
1090         $params = array('db' => (string)$db_name, 'table' => (string)$tables[$i][TBL_NAME]);
1091         $tbl_url = 'sql.php' . PMA_generate_common_url($params);
1092         $tbl_struct_url = 'tbl_structure.php' . PMA_generate_common_url($params);
1093         $tbl_ops_url = 'tbl_operations.php' . PMA_generate_common_url($params);
1094
1095         unset($params);
1096
1097         if (! PMA_isView($db_name, $tables[$i][TBL_NAME])) {
1098             $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>',
1099                                 $tbl_url,
1100                                 __('Go to table') . ': ' . PMA_backquote($tables[$i][TBL_NAME]),
1101                                 $tables[$i][TBL_NAME],
1102                                 $tbl_struct_url,
1103                                 PMA_backquote($tables[$i][TBL_NAME]) . ' ' . __('structure'),
1104                                 $tbl_ops_url,
1105                                 __('Edit') . ' ' . PMA_backquote($tables[$i][TBL_NAME]) . ' ' . __('settings'));
1106         } else {
1107             $message .= sprintf('<li><a href="%s" title="%s">%s</a></li>',
1108                                 $tbl_url,
1109                                 __('Go to view') . ': ' . PMA_backquote($tables[$i][TBL_NAME]),
1110                                 $tables[$i][TBL_NAME]);
1111         }
1112     }
1113
1114     $message .= '</ul></ul>';
1115
1116     global $import_notice;
1117     $import_notice = $message;
1118
1119     unset($tables);
1120 }
1121
1122 ?>