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