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