phpmyadmin/libraries/export/sql.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Set of functions used to build SQL dumps of tables
   5  *
   6  * @version $Id$
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  *
  14  */
  15 if (isset($plugin_list)) {
  16     $hide_sql       = false;
  17     $hide_structure = false;
  18     if ($plugin_param['export_type'] == 'table' && !$plugin_param['single_table']) {
  19         $hide_structure = true;
  20         $hide_sql       = true;
  21     }
  22     if (!$hide_sql) {
  23         $plugin_list['sql'] = array(
  24             'text' => 'strSQL',
  25             'extension' => 'sql',
  26             'mime_type' => 'text/x-sql',
  27             'options' => array(
  28                 array('type' => 'text', 'name' => 'header_comment', 'text' => 'strAddHeaderComment'),
  29                 array('type' => 'bool', 'name' => 'use_transaction', 'text' => 'strEncloseInTransaction'),
  30                 array('type' => 'bool', 'name' => 'disable_fk', 'text' => 'strDisableForeignChecks'),
  31                 ),
  32             'options_text' => 'strOptions',
  33             );
  34         $compats = PMA_DBI_getCompatibilities();
  35         if (count($compats) > 0) {
  36             $values = array();
  37             foreach($compats as $val) {
  38                 $values[$val] = $val;
  39             }
  40             $plugin_list['sql']['options'][] =
  41                 array('type' => 'select', 'name' => 'compatibility', 'text' => 'strSQLCompatibility', 'values' => $values, 'doc' => array('manual_MySQL_Database_Administration', 'Server_SQL_mode'));
  42             unset($values);
  43         }
  44
  45         /* Server export options */
  46         if ($plugin_param['export_type'] == 'server') {
  47             $plugin_list['sql']['options'][] =
  48                 array('type' => 'bgroup', 'text' => 'strDatabaseExportOptions');
  49             $plugin_list['sql']['options'][] =
  50                 array('type' => 'bool', 'name' => 'drop_database', 'text' => sprintf($GLOBALS['strAddClause'], 'DROP DATABASE'));
  51             $plugin_list['sql']['options'][] =
  52                 array('type' => 'egroup');
  53         }
  54
  55         /* Structure options */
  56         if (!$hide_structure) {
  57             $plugin_list['sql']['options'][] =
  58                 array('type' => 'bgroup', 'name' => 'structure', 'text' => 'strStructure', 'force' => 'data');
  59             if ($plugin_param['export_type'] == 'table') {
  60                 if (PMA_Table::_isView($GLOBALS['db'], $GLOBALS['table'])) {
  61                     $drop_clause = 'DROP VIEW';
  62                 } else {
  63                     $drop_clause = 'DROP TABLE';
  64                 }
  65             } elseif (PMA_MYSQL_INT_VERSION >= 50000) {
  66                 $drop_clause = 'DROP TABLE / VIEW / PROCEDURE / FUNCTION';
  67             } else {
  68                 $drop_clause = 'DROP TABLE';
  69             }
  70             $plugin_list['sql']['options'][] =
  71                 array('type' => 'bool', 'name' => 'drop_table', 'text' => sprintf($GLOBALS['strAddClause'], $drop_clause));
  72             $plugin_list['sql']['options'][] =
  73                 array('type' => 'bool', 'name' => 'if_not_exists', 'text' => sprintf($GLOBALS['strAddClause'], 'IF NOT EXISTS'));
  74             $plugin_list['sql']['options'][] =
  75                 array('type' => 'bool', 'name' => 'auto_increment', 'text' => 'strAddAutoIncrement');
  76             $plugin_list['sql']['options'][] =
  77                 array('type' => 'bool', 'name' => 'backquotes', 'text' => 'strUseBackquotes');
  78             $plugin_list['sql']['options'][] =
  79                 array('type' => 'bool', 'name' => 'procedure_function', 'text' => sprintf($GLOBALS['strAddClause'], 'CREATE PROCEDURE / FUNCTION'));
  80
  81             /* MIME stuff etc. */
  82             $plugin_list['sql']['options'][] =
  83                 array('type' => 'bgroup', 'text' => 'strAddIntoComments');
  84             $plugin_list['sql']['options'][] =
  85                 array('type' => 'bool', 'name' => 'dates', 'text' => 'strCreationDates');
  86             if (!empty($GLOBALS['cfgRelation']['relation'])) {
  87                 $plugin_list['sql']['options'][] =
  88                     array('type' => 'bool', 'name' => 'relation', 'text' => 'strRelations');
  89             }
  90             if (!empty($GLOBALS['cfgRelation']['commwork']) && PMA_MYSQL_INT_VERSION < 40100) {
  91                 $plugin_list['sql']['options'][] =
  92                     array('type' => 'bool', 'name' => 'comments', 'text' => 'strComments');
  93             }
  94             if (!empty($GLOBALS['cfgRelation']['mimework'])) {
  95                 $plugin_list['sql']['options'][] =
  96                     array('type' => 'bool', 'name' => 'mime', 'text' => 'strMIME_MIMEtype');
  97             }
  98             $plugin_list['sql']['options'][] =
  99                 array('type' => 'egroup');
 100
 101             $plugin_list['sql']['options'][] =
 102                 array('type' => 'egroup');
 103         }
 104
 105         /* Data */
 106         $plugin_list['sql']['options'][] =
 107             array('type' => 'bgroup', 'name' => 'data', 'text' => 'strData', 'force' => 'structure');
 108         $plugin_list['sql']['options'][] =
 109             array('type' => 'bool', 'name' => 'columns', 'text' => 'strCompleteInserts');
 110         $plugin_list['sql']['options'][] =
 111             array('type' => 'bool', 'name' => 'extended', 'text' => 'strExtendedInserts');
 112         $plugin_list['sql']['options'][] =
 113             array('type' => 'text', 'name' => 'max_query_size', 'text' => 'strMaximalQueryLength');
 114         $plugin_list['sql']['options'][] =
 115             array('type' => 'bool', 'name' => 'delayed', 'text' => 'strDelayedInserts');
 116         $plugin_list['sql']['options'][] =
 117             array('type' => 'bool', 'name' => 'ignore', 'text' => 'strIgnoreInserts');
 118         $plugin_list['sql']['options'][] =
 119             array('type' => 'bool', 'name' => 'hex_for_blob', 'text' => 'strHexForBLOB');
 120         $plugin_list['sql']['options'][] =
 121             array('type' => 'select', 'name' => 'type', 'text' => 'strSQLExportType', 'values' => array('INSERT' => 'INSERT', 'UPDATE' => 'UPDATE', 'REPLACE' => 'REPLACE'));
 122         $plugin_list['sql']['options'][] =
 123             array('type' => 'egroup');
 124     }
 125 } else {
 126
 127 /**
 128  * Avoids undefined variables, use NULL so isset() returns false
 129  */
 130 if (! isset($sql_backquotes)) {
 131     $sql_backquotes = null;
 132 }
 133
 134 /**
 135  * Outputs comment
 136  *
 137  * @param   string      Text of comment
 138  *
 139  * @return  string      The formatted comment
 140  */
 141 function PMA_exportComment($text = '')
 142 {
 143     // see http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-comments.html
 144     return '--' . (empty($text) ? '' : ' ') . $text . $GLOBALS['crlf'];
 145 }
 146
 147 /**
 148  * Outputs export footer
 149  *
 150  * @return  bool        Whether it suceeded
 151  *
 152  * @access  public
 153  */
 154 function PMA_exportFooter()
 155 {
 156     global $crlf;
 157     global $mysql_charset_map;
 158
 159     $foot = '';
 160
 161     if (isset($GLOBALS['sql_disable_fk'])) {
 162         $foot .=  $crlf . 'SET FOREIGN_KEY_CHECKS=1;' . $crlf;
 163     }
 164
 165     if (isset($GLOBALS['sql_use_transaction'])) {
 166         $foot .=  $crlf . 'COMMIT;' . $crlf;
 167     }
 168
 169     // restore connection settings
 170     // (not set if $cfg['AllowAnywhereRecoding'] is false)
 171     $charset_of_file = isset($GLOBALS['charset_of_file']) ? $GLOBALS['charset_of_file'] : '';
 172     if (!empty($GLOBALS['asfile']) && isset($mysql_charset_map[$charset_of_file])) {
 173         $foot .=  $crlf
 174                . '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;' . $crlf
 175                . '/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;' . $crlf
 176                . '/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;' . $crlf;
 177     }
 178
 179     return PMA_exportOutputHandler($foot);
 180 }
 181
 182 /**
 183  * Outputs export header
 184  *
 185  * @return  bool        Whether it suceeded
 186  *
 187  * @access  public
 188  */
 189 function PMA_exportHeader()
 190 {
 191     global $crlf;
 192     global $cfg;
 193     global $mysql_charset_map;
 194
 195     if (PMA_MYSQL_INT_VERSION >= 40100 && isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] != 'NONE') {
 196         PMA_DBI_try_query('SET SQL_MODE="' . $GLOBALS['sql_compatibility'] . '"');
 197     }
 198     $head  =  PMA_exportComment('phpMyAdmin SQL Dump')
 199            .  PMA_exportComment('version ' . PMA_VERSION)
 200            .  PMA_exportComment('http://www.phpmyadmin.net')
 201            .  PMA_exportComment();
 202     $head .= empty($cfg['Server']['port']) ? PMA_exportComment($GLOBALS['strHost'] . ': ' . $cfg['Server']['host']) : PMA_exportComment($GLOBALS['strHost'] . ': ' .  $cfg['Server']['host'] . ':' . $cfg['Server']['port']);
 203     $head .=  PMA_exportComment($GLOBALS['strGenTime']
 204            . ': ' .  PMA_localisedDate())
 205            .  PMA_exportComment($GLOBALS['strServerVersion'] . ': ' . substr(PMA_MYSQL_INT_VERSION, 0, 1) . '.' . (int) substr(PMA_MYSQL_INT_VERSION, 1, 2) . '.' . (int) substr(PMA_MYSQL_INT_VERSION, 3))
 206            .  PMA_exportComment($GLOBALS['strPHPVersion'] . ': ' . phpversion());
 207
 208     if (isset($GLOBALS['sql_header_comment']) && !empty($GLOBALS['sql_header_comment'])) {
 209         // '\n' is not a newline (like "\n" would be), it's the characters
 210         // backslash and n, as explained on the export interface
 211         $lines = explode('\n', $GLOBALS['sql_header_comment']);
 212         $head .= PMA_exportComment();
 213         foreach($lines as $one_line) {
 214             $head .= PMA_exportComment($one_line);
 215         }
 216         $head .= PMA_exportComment();
 217     }
 218
 219     if (isset($GLOBALS['sql_disable_fk'])) {
 220         $head .=  $crlf . 'SET FOREIGN_KEY_CHECKS=0;' . $crlf;
 221     }
 222
 223     /* We want exported AUTO_INCREMENT fields to have still same value, do this only for recent MySQL exports */
 224     if (!isset($GLOBALS['sql_compatibility']) || $GLOBALS['sql_compatibility'] == 'NONE') {
 225         $head .=  $crlf . (PMA_MYSQL_INT_VERSION >= 40101 ? 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' . $crlf : '');
 226     }
 227
 228     if (isset($GLOBALS['sql_use_transaction'])) {
 229         $head .=  $crlf .'SET AUTOCOMMIT=0;' . $crlf
 230                 . 'START TRANSACTION;' . $crlf;
 231     }
 232
 233     $head .= $crlf;
 234
 235     if (! empty($GLOBALS['asfile'])) {
 236         // we are saving as file, therefore we provide charset information
 237         // so that a utility like the mysql client can interpret
 238         // the file correctly
 239         if (isset($GLOBALS['charset_of_file']) && isset($mysql_charset_map[$GLOBALS['charset_of_file']])) {
 240             // $cfg['AllowAnywhereRecoding'] was true so we got a charset from
 241             // the export dialog
 242             $set_names = $mysql_charset_map[$GLOBALS['charset_of_file']];
 243         } else {
 244             // by default we use the connection charset
 245             $set_names = $mysql_charset_map[$GLOBALS['charset']];
 246         }
 247         $head .=  $crlf
 248                . '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;' . $crlf
 249                . '/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;' . $crlf
 250                . '/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;' . $crlf
 251                . '/*!40101 SET NAMES ' . $set_names . ' */;' . $crlf . $crlf;
 252     }
 253
 254     return PMA_exportOutputHandler($head);
 255 }
 256
 257 /**
 258  * Outputs CREATE DATABASE database
 259  *
 260  * @param   string      Database name
 261  *
 262  * @return  bool        Whether it suceeded
 263  *
 264  * @access  public
 265  */
 266 function PMA_exportDBCreate($db)
 267 {
 268     global $crlf;
 269     if (isset($GLOBALS['sql_drop_database'])) {
 270         if (!PMA_exportOutputHandler('DROP DATABASE ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : $db) . ';' . $crlf)) {
 271             return FALSE;
 272         }
 273     }
 274     $create_query = 'CREATE DATABASE ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : $db);
 275     if (PMA_MYSQL_INT_VERSION >= 40101) {
 276         $collation = PMA_getDbCollation($db);
 277         if (strpos($collation, '_')) {
 278             $create_query .= ' DEFAULT CHARACTER SET ' . substr($collation, 0, strpos($collation, '_')) . ' COLLATE ' . $collation;
 279         } else {
 280             $create_query .= ' DEFAULT CHARACTER SET ' . $collation;
 281         }
 282     }
 283     $create_query .= ';' . $crlf;
 284     if (!PMA_exportOutputHandler($create_query)) {
 285         return FALSE;
 286     }
 287     if (isset($GLOBALS['sql_backquotes']) && PMA_MYSQL_INT_VERSION >= 40100 && isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] == 'NONE') {
 288         return PMA_exportOutputHandler('USE ' . PMA_backquote($db) . ';' . $crlf);
 289     }
 290     return PMA_exportOutputHandler('USE ' . $db . ';' . $crlf);
 291 }
 292
 293 /**
 294  * Outputs database header
 295  *
 296  * @param   string      Database name
 297  *
 298  * @return  bool        Whether it suceeded
 299  *
 300  * @access  public
 301  */
 302 function PMA_exportDBHeader($db)
 303 {
 304     $head = PMA_exportComment()
 305           . PMA_exportComment($GLOBALS['strDatabase'] . ': ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : '\'' . $db . '\''))
 306           . PMA_exportComment();
 307     return PMA_exportOutputHandler($head);
 308 }
 309
 310 /**
 311  * Outputs database footer
 312  *
 313  * @param   string      Database name
 314  *
 315  * @return  bool        Whether it suceeded
 316  *
 317  * @access  public
 318  */
 319 function PMA_exportDBFooter($db)
 320 {
 321     global $crlf;
 322
 323     $result = TRUE;
 324     if (isset($GLOBALS['sql_constraints'])) {
 325         $result = PMA_exportOutputHandler($GLOBALS['sql_constraints']);
 326         unset($GLOBALS['sql_constraints']);
 327     }
 328
 329     if (PMA_MYSQL_INT_VERSION >= 50000 && isset($GLOBALS['sql_structure']) && isset($GLOBALS['sql_procedure_function'])) {
 330         $procs_funcs = '';
 331         $delimiter = '$$';
 332
 333         $procedure_names = PMA_DBI_get_procedures_or_functions($db, 'PROCEDURE');
 334         $function_names = PMA_DBI_get_procedures_or_functions($db, 'FUNCTION');
 335
 336         if ($procedure_names || $function_names) {
 337             $procs_funcs .= $crlf
 338               . 'DELIMITER ' . $delimiter . $crlf;
 339         }
 340
 341         if ($procedure_names) {
 342             $procs_funcs .=
 343                 PMA_exportComment()
 344               . PMA_exportComment($GLOBALS['strProcedures'])
 345               . PMA_exportComment();
 346
 347             foreach($procedure_names as $procedure_name) {
 348                 if (! empty($GLOBALS['sql_drop_table'])) {
 349                     $procs_funcs .= 'DROP PROCEDURE IF EXISTS ' . PMA_backquote($procedure_name) . $delimiter . $crlf;
 350                 }
 351                 $procs_funcs .= PMA_DBI_get_procedure_or_function_def($db, 'PROCEDURE', $procedure_name) . $delimiter . $crlf . $crlf;
 352             }
 353         }
 354
 355         if ($function_names) {
 356             $procs_funcs .=
 357                 PMA_exportComment()
 358               . PMA_exportComment($GLOBALS['strFunctions'])
 359               . PMA_exportComment();
 360
 361             foreach($function_names as $function_name) {
 362                 if (! empty($GLOBALS['sql_drop_table'])) {
 363                     $procs_funcs .= 'DROP FUNCTION IF EXISTS ' . PMA_backquote($function_name) . $delimiter . $crlf;
 364                 }
 365                 $procs_funcs .= PMA_DBI_get_procedure_or_function_def($db, 'FUNCTION', $function_name) . $delimiter . $crlf . $crlf;
 366             }
 367         }
 368
 369         if ($procedure_names || $function_names) {
 370             $procs_funcs .= 'DELIMITER ;' . $crlf;
 371         }
 372
 373         if (!empty($procs_funcs)) {
 374             $result = PMA_exportOutputHandler($procs_funcs);
 375         }
 376     }
 377     return $result;
 378 }
 379
 380
 381 /**
 382  * Returns a stand-in CREATE definition to resolve view dependencies
 383  *
 384  * @param   string   the database name
 385  * @param   string   the vew name
 386  * @param   string   the end of line sequence
 387  *
 388  * @return  string   resulting definition
 389  *
 390  * @access  public
 391  */
 392 function PMA_getTableDefStandIn($db, $view, $crlf) {
 393
 394     $create_query = '';
 395     if (! empty($GLOBALS['sql_drop_table'])) {
 396         $create_query .= 'DROP VIEW IF EXISTS ' . PMA_backquote($view) . ';' . $crlf;
 397     }
 398
 399     $create_query .= 'CREATE TABLE ';
 400     if (isset($GLOBALS['sql_if_not_exists']) && $GLOBALS['sql_if_not_exists']) {
 401         $create_query .= 'IF NOT EXISTS ';
 402     }
 403     $create_query .= PMA_backquote($view) . ' (' . $crlf;
 404     $tmp = array();
 405     $columns = PMA_DBI_get_columns_full($db, $view);
 406     foreach($columns as $column_name => $definition) {
 407         $tmp[] = PMA_backquote($column_name) . ' ' . $definition['Type'] . $crlf;
 408     }
 409     $create_query .= implode(',', $tmp) . ');';
 410     return($create_query);
 411 }
 412
 413 /**
 414  * Returns $table's CREATE definition
 415  *
 416  * @param   string   the database name
 417  * @param   string   the table name
 418  * @param   string   the end of line sequence
 419  * @param   string   the url to go back in case of error
 420  * @param   boolean  whether to include creation/update/check dates
 421  * @param   boolean  whether to add semicolon and end-of-line at the end
 422  *
 423  * @return  string   resulting schema
 424  *
 425  * @global  boolean  whether to add 'drop' statements or not
 426  * @global  boolean  whether to use backquotes to allow the use of special
 427  *                   characters in database, table and fields names or not
 428  *
 429  * @access  public
 430  */
 431 function PMA_getTableDef($db, $table, $crlf, $error_url, $show_dates = false, $add_semicolon = true)
 432 {
 433     global $sql_drop_table;
 434     global $sql_backquotes;
 435     global $cfgRelation;
 436     global $sql_constraints;
 437     global $sql_constraints_query; // just the text of the query
 438
 439     $schema_create = '';
 440     $auto_increment = '';
 441     $new_crlf = $crlf;
 442
 443     // need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli
 444     $result = PMA_DBI_query('SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \'' . PMA_sqlAddslashes($table) . '\'', null, PMA_DBI_QUERY_STORE);
 445     if ($result != FALSE) {
 446         if (PMA_DBI_num_rows($result) > 0) {
 447             $tmpres        = PMA_DBI_fetch_assoc($result);
 448             // Here we optionally add the AUTO_INCREMENT next value,
 449             // but starting with MySQL 5.0.24, the clause is already included
 450             // in SHOW CREATE TABLE so we'll remove it below
 451             if (isset($GLOBALS['sql_auto_increment']) && !empty($tmpres['Auto_increment'])) {
 452                 $auto_increment .= ' AUTO_INCREMENT=' . $tmpres['Auto_increment'] . ' ';
 453             }
 454
 455             if ($show_dates && isset($tmpres['Create_time']) && !empty($tmpres['Create_time'])) {
 456                 $schema_create .= PMA_exportComment($GLOBALS['strStatCreateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Create_time'])));
 457                 $new_crlf = PMA_exportComment() . $crlf;
 458             }
 459
 460             if ($show_dates && isset($tmpres['Update_time']) && !empty($tmpres['Update_time'])) {
 461                 $schema_create .= PMA_exportComment($GLOBALS['strStatUpdateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Update_time'])));
 462                 $new_crlf = PMA_exportComment() . $crlf;
 463             }
 464
 465             if ($show_dates && isset($tmpres['Check_time']) && !empty($tmpres['Check_time'])) {
 466                 $schema_create .= PMA_exportComment($GLOBALS['strStatCheckTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Check_time'])));
 467                 $new_crlf = PMA_exportComment() . $crlf;
 468             }
 469         }
 470         PMA_DBI_free_result($result);
 471     }
 472
 473     $schema_create .= $new_crlf;
 474
 475     // no need to generate a DROP VIEW here, it was done earlier
 476     if (! empty($sql_drop_table) && ! PMA_Table::isView($db,$table)) {
 477         $schema_create .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table, $sql_backquotes) . ';' . $crlf;
 478     }
 479
 480     // Steve Alberty's patch for complete table dump,
 481     // Whether to quote table and fields names or not
 482     if ($sql_backquotes) {
 483         PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 1');
 484     } else {
 485         PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 0');
 486     }
 487
 488     // I don't see the reason why this unbuffered query could cause problems,
 489     // because SHOW CREATE TABLE returns only one row, and we free the
 490     // results below. Nonetheless, we got 2 user reports about this
 491     // (see bug 1562533) so I remove the unbuffered mode.
 492     //$result = PMA_DBI_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table), null, PMA_DBI_QUERY_UNBUFFERED);
 493     //
 494     // Note: SHOW CREATE TABLE, at least in MySQL 5.1.23, does not
 495     // produce a displayable result for the default value of a BIT
 496     // field, nor does the mysqldump command. See MySQL bug 35796
 497     $result = PMA_DBI_try_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table));
 498
 499     // an error can happen, for example the table is crashed
 500     $tmp_error = PMA_DBI_getError();
 501     if ($tmp_error) {
 502         return PMA_exportComment($GLOBALS['strInUse'] . '(' . $tmp_error . ')');
 503     }
 504
 505     if ($result != FALSE && ($row = PMA_DBI_fetch_row($result))) {
 506         $create_query = $row[1];
 507         unset($row);
 508
 509         // Convert end of line chars to one that we want (note that MySQL doesn't return query it will accept in all cases)
 510         if (strpos($create_query, "(\r\n ")) {
 511             $create_query = str_replace("\r\n", $crlf, $create_query);
 512         } elseif (strpos($create_query, "(\n ")) {
 513             $create_query = str_replace("\n", $crlf, $create_query);
 514         } elseif (strpos($create_query, "(\r ")) {
 515             $create_query = str_replace("\r", $crlf, $create_query);
 516         }
 517
 518         // Should we use IF NOT EXISTS?
 519         if (isset($GLOBALS['sql_if_not_exists'])) {
 520             $create_query     = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query);
 521         }
 522
 523         // are there any constraints to cut out?
 524         if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $create_query)) {
 525
 526             // Split the query into lines, so we can easily handle it. We know lines are separated by $crlf (done few lines above).
 527             $sql_lines = explode($crlf, $create_query);
 528             $sql_count = count($sql_lines);
 529
 530             // lets find first line with constraints
 531             for ($i = 0; $i < $sql_count; $i++) {
 532                 if (preg_match('@^[\s]*(CONSTRAINT|FOREIGN[\s]+KEY)@', $sql_lines[$i])) {
 533                     break;
 534                 }
 535             }
 536
 537             // If we really found a constraint
 538             if ($i != $sql_count) {
 539
 540                 // remove , from the end of create statement
 541                 $sql_lines[$i - 1] = preg_replace('@,$@', '', $sql_lines[$i - 1]);
 542
 543                 // prepare variable for constraints
 544                 if (!isset($sql_constraints)) {
 545                     if (isset($GLOBALS['no_constraints_comments'])) {
 546                         $sql_constraints = '';
 547                     } else {
 548                         $sql_constraints = $crlf
 549                                          . PMA_exportComment()
 550                                          . PMA_exportComment($GLOBALS['strConstraintsForDumped'])
 551                                          . PMA_exportComment();
 552                     }
 553                 }
 554
 555                 // comments for current table
 556                 if (!isset($GLOBALS['no_constraints_comments'])) {
 557                     $sql_constraints .= $crlf
 558                                      . PMA_exportComment()
 559                                      . PMA_exportComment($GLOBALS['strConstraintsForTable'] . ' ' . PMA_backquote($table))
 560                                      . PMA_exportComment();
 561                 }
 562
 563                 // let's do the work
 564                 $sql_constraints_query .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf;
 565                 $sql_constraints .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf;
 566
 567                 $first = TRUE;
 568                 for ($j = $i; $j < $sql_count; $j++) {
 569                     if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $sql_lines[$j])) {
 570                         if (!$first) {
 571                             $sql_constraints .= $crlf;
 572                         }
 573                         if (strpos($sql_lines[$j], 'CONSTRAINT') === FALSE) {
 574                             $str_tmp = preg_replace('/(FOREIGN[\s]+KEY)/', 'ADD \1', $sql_lines[$j]);
 575                             $sql_constraints_query .= $str_tmp;
 576                             $sql_constraints .= $str_tmp;
 577                         } else {
 578                             $str_tmp = preg_replace('/(CONSTRAINT)/', 'ADD \1', $sql_lines[$j]);
 579                             $sql_constraints_query .= $str_tmp;
 580                             $sql_constraints .= $str_tmp;
 581                         }
 582                         $first = FALSE;
 583                     } else {
 584                         break;
 585                     }
 586                 }
 587                 $sql_constraints .= ';' . $crlf;
 588                 $sql_constraints_query .= ';';
 589
 590                 $create_query = implode($crlf, array_slice($sql_lines, 0, $i)) . $crlf . implode($crlf, array_slice($sql_lines, $j, $sql_count - 1));
 591                 unset($sql_lines);
 592             }
 593         }
 594         $schema_create .= $create_query;
 595     }
 596
 597     // remove a possible "AUTO_INCREMENT = value" clause
 598     // that could be there starting with MySQL 5.0.24
 599     $schema_create = preg_replace('/AUTO_INCREMENT\s*=\s*([0-9])+/', '', $schema_create);
 600
 601     $schema_create .= $auto_increment;
 602
 603     PMA_DBI_free_result($result);
 604     return $schema_create . ($add_semicolon ? ';' . $crlf : '');
 605 } // end of the 'PMA_getTableDef()' function
 606
 607
 608 /**
 609  * Returns $table's comments, relations etc.
 610  *
 611  * @param   string   the database name
 612  * @param   string   the table name
 613  * @param   string   the end of line sequence
 614  * @param   boolean  whether to include relation comments
 615  * @param   boolean  whether to include column comments
 616  * @param   boolean  whether to include mime comments
 617  *
 618  * @return  string   resulting comments
 619  *
 620  * @access  public
 621  */
 622 function PMA_getTableComments($db, $table, $crlf, $do_relation = false, $do_comments = false, $do_mime = false)
 623 {
 624     global $cfgRelation;
 625     global $sql_backquotes;
 626     global $sql_constraints;
 627
 628     $schema_create = '';
 629
 630     // triggered only for MySQL < 4.1.x (pmadb-style comments)
 631     if ($do_comments && $cfgRelation['commwork']) {
 632         if (!($comments_map = PMA_getComments($db, $table))) {
 633             unset($comments_map);
 634         }
 635     }
 636
 637     // Check if we can use Relations (Mike Beck)
 638     if ($do_relation && !empty($cfgRelation['relation'])) {
 639         // Find which tables are related with the current one and write it in
 640         // an array
 641         $res_rel = PMA_getForeigners($db, $table);
 642
 643         if ($res_rel && count($res_rel) > 0) {
 644             $have_rel = TRUE;
 645         } else {
 646             $have_rel = FALSE;
 647         }
 648     } else {
 649            $have_rel = FALSE;
 650     } // end if
 651
 652     if ($do_mime && $cfgRelation['mimework']) {
 653         if (!($mime_map = PMA_getMIME($db, $table, true))) {
 654             unset($mime_map);
 655         }
 656     }
 657
 658     if (isset($comments_map) && count($comments_map) > 0) {
 659         $schema_create .= $crlf
 660                        . PMA_exportComment()
 661                        . PMA_exportComment($GLOBALS['strCommentsForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':');
 662         foreach ($comments_map AS $comment_field => $comment) {
 663             $schema_create .= PMA_exportComment('  ' . PMA_backquote($comment_field, $sql_backquotes))
 664                             . PMA_exportComment('      ' . PMA_backquote($comment, $sql_backquotes));
 665         }
 666         $schema_create .= PMA_exportComment();
 667     }
 668
 669     if (isset($mime_map) && count($mime_map) > 0) {
 670         $schema_create .= $crlf
 671                        . PMA_exportComment()
 672                        . PMA_exportComment($GLOBALS['strMIMETypesForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':');
 673         @reset($mime_map);
 674         foreach ($mime_map AS $mime_field => $mime) {
 675             $schema_create .= PMA_exportComment('  ' . PMA_backquote($mime_field, $sql_backquotes))
 676                             . PMA_exportComment('      ' . PMA_backquote($mime['mimetype'], $sql_backquotes));
 677         }
 678         $schema_create .= PMA_exportComment();
 679     }
 680
 681     if ($have_rel) {
 682         $schema_create .= $crlf
 683                        . PMA_exportComment()
 684                        . PMA_exportComment($GLOBALS['strRelationsForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':');
 685         foreach ($res_rel AS $rel_field => $rel) {
 686             $schema_create .= PMA_exportComment('  ' . PMA_backquote($rel_field, $sql_backquotes))
 687                             . PMA_exportComment('      ' . PMA_backquote($rel['foreign_table'], $sql_backquotes)
 688                             . ' -> ' . PMA_backquote($rel['foreign_field'], $sql_backquotes));
 689         }
 690         $schema_create .= PMA_exportComment();
 691     }
 692
 693     return $schema_create;
 694
 695 } // end of the 'PMA_getTableComments()' function
 696
 697 /**
 698  * Outputs table's structure
 699  *
 700  * @param   string   the database name
 701  * @param   string   the table name
 702  * @param   string   the end of line sequence
 703  * @param   string   the url to go back in case of error
 704  * @param   boolean  whether to include relation comments
 705  * @param   boolean  whether to include column comments
 706  * @param   boolean  whether to include mime comments
 707  * @param   string   'stand_in', 'create_table', 'create_view'
 708  * @param   string   'server', 'database', 'table'
 709  *
 710  * @return  bool     Whether it suceeded
 711  *
 712  * @access  public
 713  */
 714 function PMA_exportStructure($db, $table, $crlf, $error_url, $relation = FALSE, $comments = FALSE, $mime = FALSE, $dates = FALSE, $export_mode, $export_type)
 715 {
 716     $formatted_table_name = (isset($GLOBALS['sql_backquotes']))
 717                           ? PMA_backquote($table)
 718                           : '\'' . $table . '\'';
 719     $dump = $crlf
 720           . PMA_exportComment(str_repeat('-', 56))
 721           . $crlf
 722           . PMA_exportComment();
 723
 724     switch($export_mode) {
 725         case 'create_table':
 726             $dump .=  PMA_exportComment($GLOBALS['strTableStructure'] . ' ' . $formatted_table_name)
 727                   . PMA_exportComment();
 728             $dump .= PMA_getTableDef($db, $table, $crlf, $error_url, $dates);
 729             $triggers = PMA_DBI_get_triggers($db, $table);
 730             if ($triggers) {
 731                 $dump .=  $crlf
 732                       . PMA_exportComment()
 733                       . PMA_exportComment($GLOBALS['strTriggers'] . ' ' . $formatted_table_name)
 734                       . PMA_exportComment();
 735                 $delimiter = '//';
 736                 foreach ($triggers as $trigger) {
 737                     $dump .= $trigger['drop'] . ';' . $crlf;
 738                     $dump .= 'DELIMITER ' . $delimiter . $crlf;
 739                     $dump .= $trigger['create'];
 740                     $dump .= 'DELIMITER ;' . $crlf;
 741                 }
 742             }
 743             break;
 744         case 'create_view':
 745             $dump .= PMA_exportComment($GLOBALS['strStructureForView'] . ' ' . $formatted_table_name)
 746                   .  PMA_exportComment();
 747             // delete the stand-in table previously created (if any)
 748             if ($export_type != 'table') {
 749                 $dump .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table) . ';' . $crlf;
 750             }
 751             $dump .= PMA_getTableDef($db, $table, $crlf, $error_url, $dates);
 752             break;
 753         case 'stand_in':
 754             $dump .=  PMA_exportComment($GLOBALS['strStandInStructureForView'] . ' ' . $formatted_table_name)
 755                   .  PMA_exportComment();
 756             // export a stand-in definition to resolve view dependencies
 757             $dump .= PMA_getTableDefStandIn($db, $table, $crlf);
 758     } // end switch
 759
 760     $dump .= PMA_getTableComments($db, $table, $crlf, $relation, $comments, $mime);
 761     // this one is built by PMA_getTableDef() to use in table copy/move
 762     // but not in the case of export
 763     unset($GLOBALS['sql_constraints_query']);
 764
 765     return PMA_exportOutputHandler($dump);
 766 }
 767
 768 /**
 769  * Dispatches between the versions of 'getTableContent' to use depending
 770  * on the php version
 771  *
 772  * @param   string      the database name
 773  * @param   string      the table name
 774  * @param   string      the end of line sequence
 775  * @param   string      the url to go back in case of error
 776  * @param   string      SQL query for obtaining data
 777  *
 778  * @return  bool        Whether it suceeded
 779  *
 780  * @global  boolean  whether to use backquotes to allow the use of special
 781  *                   characters in database, table and fields names or not
 782  * @global  integer  the number of records
 783  * @global  integer  the current record position
 784  *
 785  * @access  public
 786  *
 787  * @see     PMA_getTableContentFast(), PMA_getTableContentOld()
 788  *
 789  * @author  staybyte
 790  */
 791 function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
 792 {
 793     global $sql_backquotes;
 794     global $rows_cnt;
 795     global $current_row;
 796
 797     $formatted_table_name = (isset($GLOBALS['sql_backquotes']))
 798                           ? PMA_backquote($table)
 799                           : '\'' . $table . '\'';
 800
 801     // Do not export data for a VIEW
 802     // (For a VIEW, this is called only when exporting a single VIEW)
 803     if (PMA_Table::_isView($db, $table)) {
 804         $head = $crlf
 805           . PMA_exportComment()
 806           . PMA_exportComment('VIEW ' . ' ' . $formatted_table_name)
 807           . PMA_exportComment($GLOBALS['strData'] . ': ' . $GLOBALS['strNone'])
 808           . PMA_exportComment()
 809           . $crlf;
 810
 811         if (! PMA_exportOutputHandler($head)) {
 812             return FALSE;
 813         }
 814         return true;
 815     }
 816
 817     // it's not a VIEW
 818     $head = $crlf
 819           . PMA_exportComment()
 820           . PMA_exportComment($GLOBALS['strDumpingData'] . ' ' . $formatted_table_name)
 821           . PMA_exportComment()
 822           . $crlf;
 823
 824     if (! PMA_exportOutputHandler($head)) {
 825         return FALSE;
 826     }
 827
 828     $buffer = '';
 829
 830     // analyze the query to get the true column names, not the aliases
 831     // (this fixes an undefined index, also if Complete inserts
 832     //  are used, we did not get the true column name in case of aliases)
 833     $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
 834
 835     $result      = PMA_DBI_try_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
 836     // a possible error: the table has crashed
 837     $tmp_error = PMA_DBI_getError();
 838     if ($tmp_error) {
 839         return PMA_exportOutputHandler(PMA_exportComment($GLOBALS['strInUse'] . ' (' . $tmp_error . ')'));
 840     }
 841     if ($result != FALSE) {
 842         $fields_cnt     = PMA_DBI_num_fields($result);
 843
 844         // Get field information
 845         $fields_meta    = PMA_DBI_get_fields_meta($result);
 846         $field_flags    = array();
 847         for ($j = 0; $j < $fields_cnt; $j++) {
 848             $field_flags[$j] = PMA_DBI_field_flags($result, $j);
 849         }
 850
 851         for ($j = 0; $j < $fields_cnt; $j++) {
 852             if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
 853                 $field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $sql_backquotes);
 854             } else {
 855                 $field_set[$j] = PMA_backquote($fields_meta[$j]->name, $sql_backquotes);
 856             }
 857         }
 858
 859         if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
 860             // update
 861             $schema_insert  = 'UPDATE ';
 862             if (isset($GLOBALS['sql_ignore'])) {
 863                 $schema_insert .= 'IGNORE ';
 864             }
 865             // avoid EOL blank
 866             $schema_insert .= PMA_backquote($table, $sql_backquotes) . ' SET';
 867         } else {
 868             // insert or replace
 869             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') {
 870                 $sql_command    = 'REPLACE';
 871             } else {
 872                 $sql_command    = 'INSERT';
 873             }
 874
 875             // delayed inserts?
 876             if (isset($GLOBALS['sql_delayed'])) {
 877                 $insert_delayed = ' DELAYED';
 878             } else {
 879                 $insert_delayed = '';
 880             }
 881
 882             // insert ignore?
 883             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) {
 884                 $insert_delayed .= ' IGNORE';
 885             }
 886
 887             // scheme for inserting fields
 888             if (isset($GLOBALS['sql_columns'])) {
 889                 $fields        = implode(', ', $field_set);
 890                 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $sql_backquotes)
 891             // avoid EOL blank
 892                                . ' (' . $fields . ') VALUES';
 893             } else {
 894                 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $sql_backquotes)
 895                                . ' VALUES';
 896             }
 897         }
 898
 899         $search       = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
 900         $replace      = array('\0', '\n', '\r', '\Z');
 901         $current_row  = 0;
 902         $query_size   = 0;
 903         if (isset($GLOBALS['sql_extended']) && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) {
 904             $separator    = ',';
 905             $schema_insert .= $crlf;
 906         } else {
 907             $separator    = ';';
 908         }
 909
 910         while ($row = PMA_DBI_fetch_row($result)) {
 911             $current_row++;
 912             for ($j = 0; $j < $fields_cnt; $j++) {
 913                 // NULL
 914                 if (!isset($row[$j]) || is_null($row[$j])) {
 915                     $values[]     = 'NULL';
 916                 // a number
 917                 // timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
 918                 } elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp'
 919                         && ! $fields_meta[$j]->blob) {
 920                     $values[] = $row[$j];
 921                 // a true BLOB
 922                 // - mysqldump only generates hex data when the --hex-blob
 923                 //   option is used, for fields having the binary attribute
 924                 //   no hex is generated
 925                 // - a TEXT field returns type blob but a real blob
 926                 //   returns also the 'binary' flag
 927                 } elseif (stristr($field_flags[$j], 'BINARY')
 928                         && $fields_meta[$j]->blob
 929                         && isset($GLOBALS['sql_hex_for_blob'])) {
 930                     // empty blobs need to be different, but '0' is also empty :-(
 931                     if (empty($row[$j]) && $row[$j] != '0') {
 932                         $values[] = '\'\'';
 933                     } else {
 934                         $values[] = '0x' . bin2hex($row[$j]);
 935                     }
 936                 // detection of 'bit' works only on mysqli extension
 937                 } elseif ($fields_meta[$j]->type == 'bit') {
 938                     $values[] = "b'" . PMA_sqlAddslashes(PMA_printable_bit_value($row[$j], $fields_meta[$j]->length)) . "'";
 939                 // something else -> treat as a string
 940                 } else {
 941                     $values[] = '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\'';
 942                 } // end if
 943             } // end for
 944
 945             // should we make update?
 946             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
 947
 948                 $insert_line = $schema_insert;
 949                 for ($i = 0; $i < $fields_cnt; $i++) {
 950                     if (0 == $i) {
 951                         $insert_line .= ' ';
 952                     }
 953                     if ($i > 0) {
 954                         // avoid EOL blank
 955                         $insert_line .= ',';
 956                     }
 957                     $insert_line .= $field_set[$i] . ' = ' . $values[$i];
 958                 }
 959
 960                 $insert_line .= ' WHERE ' . PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $row);
 961
 962             } else {
 963
 964                 // Extended inserts case
 965                 if (isset($GLOBALS['sql_extended'])) {
 966                     if ($current_row == 1) {
 967                         $insert_line  = $schema_insert . '(' . implode(', ', $values) . ')';
 968                     } else {
 969                         $insert_line  = '(' . implode(', ', $values) . ')';
 970                         if (isset($GLOBALS['sql_max_query_size']) && $GLOBALS['sql_max_query_size'] > 0 && $query_size + strlen($insert_line) > $GLOBALS['sql_max_query_size']) {
 971                             if (!PMA_exportOutputHandler(';' . $crlf)) {
 972                                 return FALSE;
 973                             }
 974                             $query_size = 0;
 975                             $current_row = 1;
 976                             $insert_line = $schema_insert . $insert_line;
 977                         }
 978                     }
 979                     $query_size += strlen($insert_line);
 980                 }
 981                 // Other inserts case
 982                 else {
 983                     $insert_line      = $schema_insert . '(' . implode(', ', $values) . ')';
 984                 }
 985             }
 986             unset($values);
 987
 988             if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) {
 989                 return FALSE;
 990             }
 991
 992         } // end while
 993         if ($current_row > 0) {
 994             if (!PMA_exportOutputHandler(';' . $crlf)) {
 995                 return FALSE;
 996             }
 997         }
 998     } // end if ($result != FALSE)
 999     PMA_DBI_free_result($result);
1000
1001     return TRUE;
1002 } // end of the 'PMA_exportData()' function
1003 }
1004 ?>