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                 ) {
 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))
 114                     ) {
 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 = '';
 122                         }
 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']);
 131                         }
 132                         $msg = '# ';
 133                         if ($result === false) { // execution failed
 134                             if (! isset($my_die)) {
 135                                 $my_die = array();
 136                             }
 137                             $my_die[] = array('sql' => $import_run_buffer['full'], 'error' => PMA_DBI_getError());
 138
 139                             if ($cfg['VerboseMultiSubmit']) {
 140                                 $msg .= __('Error');
 141                             }
 142
 143                             if (!$cfg['IgnoreMultiSubmitErrors']) {
 144                                 $error = true;
 145                                 return;
 146                             }
 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).');
 158                             }
 159                         }
 160                         if (!$sql_query_disabled) {
 161                             $sql_query .= $msg . "\n";
 162                         }
 163
 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);
 167                         }
 168
 169                         if ($result != false
 170                             && preg_match('@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)?(TABLE|DATABASE)[[:space:]]+(.+)@im', $import_run_buffer['sql'])
 171                         ) {
 172                             $reload = true;
 173                         }
 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'];
 184                     }
 185                 }
 186             }
 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;
 195                     }
 196                 } else {
 197                     if (strlen($sql_query) > 10000 || $executed_queries > 10 || $max_sql_len > 500) {
 198                         $sql_query = '';
 199                         $sql_query_disabled = true;
 200                     }
 201                 }
 202             }
 203         } // end do query (no skip)
 204     } // end buffer exists
 205
 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']);
 211     }
 212 }
 213
 214 /**
 215  * Looks for the presence of USE to possibly change current db
 216  *
 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
 222  */
 223 function PMA_lookForUse($buffer, $db, $reload)
 224 {
 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;
 229     }
 230     return(array($db, $reload));
 231 }
 232
 233
 234 /**
 235  * Returns next part of imported file/buffer
 236  *
 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
 240  */
 241 function PMA_importGetNextChunk($size = 32768)
 242 {
 243     global $compression, $import_handle, $charset_conversion, $charset_of_file,
 244         $read_multiply;
 245
 246     // Add some progression while reading large amount of data
 247     if ($read_multiply <= 8) {
 248         $size *= $read_multiply;
 249     } else {
 250         $size *= 8;
 251     }
 252     $read_multiply++;
 253
 254     // We can not read too much
 255     if ($size > $GLOBALS['read_limit']) {
 256         $size = $GLOBALS['read_limit'];
 257     }
 258
 259     if (PMA_checkTimeout()) {
 260         return false;
 261     }
 262     if ($GLOBALS['finished']) {
 263         return true;
 264     }
 265
 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;
 276         }
 277     }
 278
 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;
 297     }
 298     $GLOBALS['offset'] += $size;
 299
 300     if ($charset_conversion) {
 301         return PMA_convert_string($charset_of_file, 'utf-8', $result);
 302     } else {
 303         /**
 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>)
 307          *
 308          * @todo BOM could be used for charset autodetection
 309          */
 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);
 317             }
 318         }
 319         return $result;
 320     }
 321 }
 322
 323 /**
 324  * Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
 325  *
 326  * This functions uses recursion to build the Excel column name.
 327  *
 328  * The column number (1-26) is converted to the responding ASCII character (A-Z) and returned.
 329  *
 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.
 338  *
 339  * @access  public
 340  *
 341  * @param int $num
 342  * @return  string The column's "Excel" name
 343  */
 344 function PMA_getColumnAlphaName($num)
 345 {
 346     $A = 65; // ASCII value for capital "A"
 347     $col_name = "";
 348
 349     if ($num > 26) {
 350         $div = (int)($num / 26);
 351         $remain = (int)($num % 26);
 352
 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--;
 357         }
 358
 359         // recursive function call
 360         $col_name = PMA_getColumnAlphaName($div);
 361         // use modulus as new column number
 362         $num = $remain;
 363     }
 364
 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);
 372     }
 373
 374     return $col_name;
 375 }
 376
 377 /**
 378  * Returns the column number based on the Excel name.
 379  * So "A" = 1, "Z" = 26, "AA" = 27, etc.
 380  *
 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.
 385  *
 386  * @access  public
 387  *
 388  * @param string $name (i.e. "A", or "BC", etc.)
 389  * @return  int The column number
 390  */
 391 function PMA_getColumnNumberFromName($name)
 392 {
 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;
 400
 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);
 406
 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);
 411         }
 412         return $column_number;
 413     } else {
 414         return 0;
 415     }
 416 }
 417
 418 /**
 419  * Constants definitions
 420  */
 421
 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);
 429
 430 /* Decimal size defs */
 431 define("M",         0);
 432 define("D",         1);
 433 define("FULL",      2);
 434
 435 /* Table array defs */
 436 define("TBL_NAME",  0);
 437 define("COL_NAMES", 1);
 438 define("ROWS",      2);
 439
 440 /* Analysis array defs */
 441 define("TYPES",        0);
 442 define("SIZES",        1);
 443 define("FORMATTEDSQL", 2);
 444
 445 /**
 446  * Obtains the precision (total # of digits) from a size of type decimal
 447  *
 448  * @access  public
 449  *
 450  * @param string $last_cumulative_size
 451  * @return  int Precision of the given decimal size notation
 452  */
 453 function PMA_getM($last_cumulative_size)
 454 {
 455     return (int)substr($last_cumulative_size, 0, strpos($last_cumulative_size, ","));
 456 }
 457
 458 /**
 459  * Obtains the scale (# of digits to the right of the decimal point) from a size of type decimal
 460  *
 461  * @access  public
 462  *
 463  * @param string $last_cumulative_size
 464  * @return  int Scale of the given decimal size notation
 465  */
 466 function PMA_getD($last_cumulative_size)
 467 {
 468     return (int)substr($last_cumulative_size, (strpos($last_cumulative_size, ",") + 1), (strlen($last_cumulative_size) - strpos($last_cumulative_size, ",")));
 469 }
 470
 471 /**
 472  * Obtains the decimal size of a given cell
 473  *
 474  * @access  public
 475  *
 476  * @param string &$cell
 477  * @return  array Contains the precision, scale, and full size representation of the given decimal cell
 478  */
 479 function PMA_getDecimalSize(&$cell)
 480 {
 481     $curr_size = strlen((string)$cell);
 482     $decPos = strpos($cell, ".");
 483     $decPrecision = ($curr_size - 1) - $decPos;
 484
 485     $m = $curr_size - 1;
 486     $d = $decPrecision;
 487
 488     return array($m, $d, ($m . "," . $d));
 489 }
 490
 491 /**
 492  * Obtains the size of the given cell
 493  *
 494  * @todo    Handle the error cases more elegantly
 495  *
 496  * @access  public
 497  *
 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
 503  */
 504 function PMA_detectSize($last_cumulative_size, $last_cumulative_type, $curr_type, &$cell)
 505 {
 506     $curr_size = strlen((string)$cell);
 507
 508     /**
 509      * If the cell is NULL, don't treat it as a varchar
 510      */
 511     if (! strcmp('NULL', $cell)) {
 512         return $last_cumulative_size;
 513     }
 514     /**
 515      * What to do if the current cell is of type VARCHAR
 516      */
 517     elseif ($curr_type == VARCHAR) {
 518         /**
 519          * The last cumulative type was VARCHAR
 520          */
 521         if ($last_cumulative_type == VARCHAR) {
 522             if ($curr_size >= $last_cumulative_size) {
 523                 return $curr_size;
 524             } else {
 525                 return $last_cumulative_size;
 526             }
 527         }
 528         /**
 529          * The last cumulative type was DECIMAL
 530          */
 531         elseif ($last_cumulative_type == DECIMAL) {
 532             $oldM = PMA_getM($last_cumulative_size);
 533
 534             if ($curr_size >= $oldM) {
 535                 return $curr_size;
 536             } else {
 537                 return $oldM;
 538             }
 539         }
 540         /**
 541          * The last cumulative type was BIGINT or INT
 542          */
 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;
 548             }
 549         }
 550         /**
 551          * This is the first row to be analyzed
 552          */
 553         elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
 554             return $curr_size;
 555         }
 556         /**
 557          * An error has DEFINITELY occurred
 558          */
 559         else {
 560             /**
 561              * TODO: Handle this MUCH more elegantly
 562              */
 563
 564             return -1;
 565         }
 566     }
 567     /**
 568      * What to do if the current cell is of type DECIMAL
 569      */
 570     elseif ($curr_type == DECIMAL) {
 571         /**
 572          * The last cumulative type was VARCHAR
 573          */
 574         if ($last_cumulative_type == VARCHAR) {
 575             /* Convert $last_cumulative_size from varchar to decimal format */
 576             $size = PMA_getDecimalSize($cell);
 577
 578             if ($size[M] >= $last_cumulative_size) {
 579                 return $size[M];
 580             } else {
 581                 return $last_cumulative_size;
 582             }
 583         }
 584         /**
 585          * The last cumulative type was DECIMAL
 586          */
 587         elseif ($last_cumulative_type == DECIMAL) {
 588             $size = PMA_getDecimalSize($cell);
 589
 590             $oldM = PMA_getM($last_cumulative_size);
 591             $oldD = PMA_getD($last_cumulative_size);
 592
 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;
 599             }
 600         }
 601         /**
 602          * The last cumulative type was BIGINT or INT
 603          */
 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);
 607
 608             if ($size[M] >= $last_cumulative_size) {
 609                 return $size[FULL];
 610             } else {
 611                 return ($last_cumulative_size.",".$size[D]);
 612             }
 613         }
 614         /**
 615          * This is the first row to be analyzed
 616          */
 617         elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
 618             /* First row of the column */
 619             $size = PMA_getDecimalSize($cell);
 620
 621             return $size[FULL];
 622         }
 623         /**
 624          * An error has DEFINITELY occurred
 625          */
 626         else {
 627             /**
 628              * TODO: Handle this MUCH more elegantly
 629              */
 630
 631             return -1;
 632         }
 633     }
 634     /**
 635      * What to do if the current cell is of type BIGINT or INT
 636      */
 637     elseif ($curr_type == BIGINT || $curr_type == INT) {
 638         /**
 639          * The last cumulative type was VARCHAR
 640          */
 641         if ($last_cumulative_type == VARCHAR) {
 642             if ($curr_size >= $last_cumulative_size) {
 643                 return $curr_size;
 644             } else {
 645                 return $last_cumulative_size;
 646             }
 647         }
 648         /**
 649          * The last cumulative type was DECIMAL
 650          */
 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);
 656
 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);
 664             }
 665         }
 666         /**
 667          * The last cumulative type was BIGINT or INT
 668          */
 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;
 674             }
 675         }
 676         /**
 677          * This is the first row to be analyzed
 678          */
 679         elseif (! isset($last_cumulative_type) || $last_cumulative_type == NONE) {
 680             return $curr_size;
 681         }
 682         /**
 683          * An error has DEFINITELY occurred
 684          */
 685         else {
 686             /**
 687              * TODO: Handle this MUCH more elegantly
 688              */
 689
 690             return -1;
 691         }
 692     }
 693     /**
 694      * An error has DEFINITELY occurred
 695      */
 696     else {
 697         /**
 698          * TODO: Handle this MUCH more elegantly
 699          */
 700
 701         return -1;
 702     }
 703 }
 704
 705 /**
 706  * Determines what MySQL type a cell is
 707  *
 708  * @access  public
 709  *
 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)
 713  */
 714 function PMA_detectType($last_cumulative_type, &$cell)
 715 {
 716     /**
 717      * If numeric, determine if decimal, int or bigint
 718      * Else, we call it varchar for simplicity
 719      */
 720
 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;
 726         }
 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;
 735         }
 736         }
 737     } else {
 738         return VARCHAR;
 739     }
 740 }
 741
 742 /**
 743  * Determines if the column types are int, decimal, or string
 744  *
 745  * @link http://wiki.phpmyadmin.net/pma/Import
 746  *
 747  * @todo    Handle the error case more elegantly
 748  *
 749  * @access  public
 750  *
 751  * @param &$table  array(string $table_name, array $col_names, array $rows)
 752  * @return  array    array(array $types, array $sizes)
 753  */
 754 function PMA_analyzeTable(&$table)
 755 {
 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();
 763
 764     /* Initialize $sizes to all 0's */
 765     for ($i = 0; $i < $numCols; ++$i) {
 766         $sizes[$i] = 0;
 767     }
 768
 769     /* Initialize $types to NONE */
 770     for ($i = 0; $i < $numCols; ++$i) {
 771         $types[$i] = NONE;
 772     }
 773
 774     /* Temp vars */
 775     $curr_type = NONE;
 776     $curr_size = 0;
 777
 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]);
 788
 789                 /**
 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
 792                  */
 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;
 799                         }
 800                     } else if ($curr_type == BIGINT) {
 801                         if ($types[$i] != VARCHAR && $types[$i] != DECIMAL) {
 802                             $types[$i] = BIGINT;
 803                         }
 804                     } else if ($curr_type == INT) {
 805                         if ($types[$i] != VARCHAR && $types[$i] != DECIMAL && $types[$i] != BIGINT) {
 806                             $types[$i] = INT;
 807                         }
 808                     }
 809                 }
 810             }
 811         }
 812
 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';
 819             }
 820         }
 821
 822         return array($types, $sizes);
 823     } else {
 824         /**
 825          * TODO: Handle this better
 826          */
 827
 828         return false;
 829     }
 830 }
 831
 832 /* Needed to quell the beast that is PMA_Message */
 833 $import_notice = null;
 834
 835 /**
 836  * Builds and executes SQL statements to create the database and tables
 837  * as necessary, as well as insert all the data.
 838  *
 839  * @link http://wiki.phpmyadmin.net/pma/Import
 840  *
 841  * @access  public
 842  *
 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
 849  */
 850 function PMA_buildSQL($db_name, &$tables, &$analyses = null, &$additional_sql = null, $options = null)
 851 {
 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";
 857     }
 858
 859     if (isset($options['db_charset']) && ! is_null($options['db_charset'])) {
 860         $charset = $options['db_charset'];
 861     } else {
 862         $charset = "utf8";
 863     }
 864
 865     if (isset($options['create_db'])) {
 866         $create_db = $options['create_db'];
 867     } else {
 868         $create_db = true;
 869     }
 870
 871     /* Create SQL code to handle the database */
 872     $sql = array();
 873
 874     if ($create_db) {
 875         $sql[] = "CREATE DATABASE IF NOT EXISTS " . PMA_backquote($db_name) . " DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation;
 876     }
 877
 878     /**
 879      * The calling plug-in should include this statement, if necessary, in the $additional_sql parameter
 880      *
 881      * $sql[] = "USE " . PMA_backquote($db_name);
 882      */
 883
 884     /* Execute the SQL statements create above */
 885     $sql_len = count($sql);
 886     for ($i = 0; $i < $sql_len; ++$i) {
 887         PMA_importRunQuery($sql[$i], $sql[$i]);
 888     }
 889
 890     /* No longer needed */
 891     unset($sql);
 892
 893     /* Run the $additional_sql statements supplied by the caller plug-in */
 894     if ($additional_sql != null) {
 895         /* Clean the SQL first */
 896         $additional_sql_len = count($additional_sql);
 897
 898         /**
 899          * Only match tables for now, because CREATE IF NOT EXISTS
 900          * syntax is lacking or nonexisting for views, triggers,
 901          * functions, and procedures.
 902          *
 903          * See: http://bugs.mysql.com/bug.php?id=15287
 904          *
 905          * To the best of my knowledge this is still an issue.
 906          *
 907          * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)';
 908          */
 909         $pattern = '/CREATE .*(TABLE)/';
 910         $replacement = 'CREATE \\1 IF NOT EXISTS';
 911
 912         /* Change CREATE statements to CREATE IF NOT EXISTS to support inserting into existing structures */
 913         for ($i = 0; $i < $additional_sql_len; ++$i) {
 914             $additional_sql[$i] = preg_replace($pattern, $replacement, $additional_sql[$i]);
 915             /* Execute the resulting statements */
 916             PMA_importRunQuery($additional_sql[$i], $additional_sql[$i]);
 917         }
 918     }
 919
 920     if ($analyses != null) {
 921         $type_array = array(NONE => "NULL", VARCHAR => "varchar", INT => "int", DECIMAL => "decimal", BIGINT => "bigint", GEOMETRY => 'geometry');
 922
 923         /* TODO: Do more checking here to make sure they really are matched */
 924         if (count($tables) != count($analyses)) {
 925             exit();
 926         }
 927
 928         /* Create SQL code to create the tables */
 929         $tempSQLStr = "";
 930         $num_tables = count($tables);
 931         for ($i = 0; $i < $num_tables; ++$i) {
 932             $num_cols = count($tables[$i][COL_NAMES]);
 933             $tempSQLStr = "CREATE TABLE IF NOT EXISTS " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
 934             for ($j = 0; $j < $num_cols; ++$j) {
 935                 $size = $analyses[$i][SIZES][$j];
 936                 if ((int)$size == 0) {
 937                     $size = 10;
 938                 }
 939
 940                 $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$j]) . " " . $type_array[$analyses[$i][TYPES][$j]];
 941                 if ($analyses[$i][TYPES][$j] != GEOMETRY) {
 942                     $tempSQLStr .= "(" . $size . ")";
 943                 }
 944
 945                 if ($j != (count($tables[$i][COL_NAMES]) - 1)) {
 946                     $tempSQLStr .= ", ";
 947                 }
 948             }
 949             $tempSQLStr .= ") DEFAULT CHARACTER SET " . $charset . " COLLATE " . $collation . ";";
 950
 951             /**
 952              * Each SQL statement is executed immediately
 953              * after it is formed so that we don't have
 954              * to store them in a (possibly large) buffer
 955              */
 956             PMA_importRunQuery($tempSQLStr, $tempSQLStr);
 957         }
 958     }
 959
 960     /**
 961      * Create the SQL statements to insert all the data
 962      *
 963      * Only one insert query is formed for each table
 964      */
 965     $tempSQLStr = "";
 966     $col_count = 0;
 967     $num_tables = count($tables);
 968     for ($i = 0; $i < $num_tables; ++$i) {
 969         $num_cols = count($tables[$i][COL_NAMES]);
 970         $num_rows = count($tables[$i][ROWS]);
 971
 972         $tempSQLStr = "INSERT INTO " . PMA_backquote($db_name) . '.' . PMA_backquote($tables[$i][TBL_NAME]) . " (";
 973
 974         for ($m = 0; $m < $num_cols; ++$m) {
 975             $tempSQLStr .= PMA_backquote($tables[$i][COL_NAMES][$m]);
 976
 977             if ($m != ($num_cols - 1)) {
 978                 $tempSQLStr .= ", ";
 979             }
 980         }
 981
 982         $tempSQLStr .= ") VALUES ";
 983
 984         for ($j = 0; $j < $num_rows; ++$j) {
 985             $tempSQLStr .= "(";
 986
 987             for ($k = 0; $k < $num_cols; ++$k) {
 988                 // If fully formatted SQL, no need to enclose with aphostrophes, add shalshes etc.
 989                 if ($analyses != null
 990                     && isset($analyses[$i][FORMATTEDSQL][$col_count])
 991                     && $analyses[$i][FORMATTEDSQL][$col_count] == true
 992                 ) {
 993                     $tempSQLStr .= (string) $tables[$i][ROWS][$j][$k];
 994                 } else {
 995                     if ($analyses != null) {
 996                         $is_varchar = ($analyses[$i][TYPES][$col_count] === VARCHAR);
 997                     } else {
 998                         $is_varchar = !is_numeric($tables[$i][ROWS][$j][$k]);
 999                     }
1000
1001                     /* Don't put quotes around NULL fields */
1002                     if (! strcmp($tables[$i][ROWS][$j][$k], 'NULL')) {
1003                         $is_varchar = false;
1004                     }
1005
1006                     $tempSQLStr .= (($is_varchar) ? "'" : "");
1007                     $tempSQLStr .= PMA_sqlAddSlashes((string)$tables[$i][ROWS][$j][$k]);
1008                     $tempSQLStr .= (($is_varchar) ? "'" : "");
1009                 }
1010
1011                 if ($k != ($num_cols - 1)) {
1012                     $tempSQLStr .= ", ";
1013                 }
1014
1015                 if ($col_count == ($num_cols - 1)) {
1016                     $col_count = 0;
1017                 } else {
1018                     $col_count++;
1019                 }
1020
1021                 /* Delete the cell after we are done with it */
1022                 unset($tables[$i][ROWS][$j][$k]);
1023             }
1024
1025             $tempSQLStr .= ")";
1026
1027             if ($j != ($num_rows - 1)) {
1028                 $tempSQLStr .= ",\n ";
1029             }
1030
1031             $col_count = 0;
1032             /* Delete the row after we are done with it */
1033             unset($tables[$i][ROWS][$j]);
1034         }
1035
1036         $tempSQLStr .= ";";
1037
1038         /**
1039          * Each SQL statement is executed immediately
1040          * after it is formed so that we don't have
1041          * to store them in a (possibly large) buffer
1042          */
1043         PMA_importRunQuery($tempSQLStr, $tempSQLStr);
1044     }
1045
1046     /* No longer needed */
1047     unset($tempSQLStr);
1048
1049     /**
1050      * A work in progress
1051      */
1052
1053     /* Add the viewable structures from $additional_sql to $tables so they are also displayed */
1054
1055     $view_pattern = '@VIEW `[^`]+`\.`([^`]+)@';
1056     $table_pattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@';
1057     /* Check a third pattern to make sure its not a "USE `db_name`;" statement */
1058
1059     $regs = array();
1060
1061     $inTables = false;
1062
1063     $additional_sql_len = count($additional_sql);
1064     for ($i = 0; $i < $additional_sql_len; ++$i) {
1065         preg_match($view_pattern, $additional_sql[$i], $regs);
1066
1067         if (count($regs) == 0) {
1068             preg_match($table_pattern, $additional_sql[$i], $regs);
1069         }
1070
1071         if (count($regs)) {
1072             for ($n = 0; $n < $num_tables; ++$n) {
1073                 if (!strcmp($regs[1], $tables[$n][TBL_NAME])) {
1074                     $inTables = true;
1075                     break;
1076                 }
1077             }
1078
1079             if (!$inTables) {
1080                 $tables[] = array(TBL_NAME => $regs[1]);
1081             }
1082         }
1083
1084         /* Reset the array */
1085         $regs = array();
1086         $inTables = false;
1087     }
1088
1089     $params = array('db' => (string)$db_name);
1090     $db_url = 'db_structure.php' . PMA_generate_common_url($params);
1091     $db_ops_url = 'db_operations.php' . PMA_generate_common_url($params);
1092
1093     $message = '<br /><br />';
1094     $message .= '<strong>' . __('The following structures have either been created or altered. Here you can:') . '</strong><br />';
1095     $message .= '<ul><li>' . __("View a structure's contents by clicking on its name") . '</li>';
1096     $message .= '<li>' . __('Change any of its settings by clicking the corresponding "Options" link') . '</li>';
1097     $message .= '<li>' . __('Edit structure by following the "Structure" link') . '</li>';
1098     $message .= sprintf('<br /><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1099                         $db_url,
1100                         __('Go to database') . ': ' . htmlspecialchars(PMA_backquote($db_name)),
1101                         htmlspecialchars($db_name),
1102                         $db_ops_url,
1103                         sprintf(__('Edit settings for %s'), htmlspecialchars(PMA_backquote($db_name))));
1104
1105     $message .= '<ul>';
1106
1107     unset($params);
1108
1109     $num_tables = count($tables);
1110     for ($i = 0; $i < $num_tables; ++$i) {
1111         $params = array('db' => (string)$db_name, 'table' => (string)$tables[$i][TBL_NAME]);
1112         $tbl_url = 'sql.php' . PMA_generate_common_url($params);
1113         $tbl_struct_url = 'tbl_structure.php' . PMA_generate_common_url($params);
1114         $tbl_ops_url = 'tbl_operations.php' . PMA_generate_common_url($params);
1115
1116         unset($params);
1117
1118         if (! PMA_Table::isView($db_name, $tables[$i][TBL_NAME])) {
1119             $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>',
1120                                 $tbl_url,
1121                                 __('Go to table') . ': ' . htmlspecialchars(PMA_backquote($tables[$i][TBL_NAME])),
1122                                 htmlspecialchars($tables[$i][TBL_NAME]),
1123                                 $tbl_struct_url,
1124                                 sprintf(__('Structure of %s'), htmlspecialchars(PMA_backquote($tables[$i][TBL_NAME]))),
1125                                 $tbl_ops_url,
1126                                 sprintf(__('Edit settings for %s'), htmlspecialchars(PMA_backquote($db_name))));
1127         } else {
1128             $message .= sprintf('<li><a href="%s" title="%s">%s</a></li>',
1129                                 $tbl_url,
1130                                 __('Go to view') . ': ' . htmlspecialchars(PMA_backquote($tables[$i][TBL_NAME])),
1131                                 htmlspecialchars($tables[$i][TBL_NAME]));
1132         }
1133     }
1134
1135     $message .= '</ul></ul>';
1136
1137     global $import_notice;
1138     $import_notice = $message;
1139
1140     unset($tables);
1141 }
1142
1143 ?>