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         $result = PMA_exportOutputHandler('USE ' . PMA_backquote($db) . ';' . $crlf);
 311     } else {
 312         $result = PMA_exportOutputHandler('USE ' . $db . ';' . $crlf);
 313     }
 314
 315     if ($result && isset($GLOBALS['sql_structure']) && isset($GLOBALS['sql_procedure_function'])) {
 316         $text = '';
 317         $delimiter = '$$';
 318
 319         $procedure_names = PMA_DBI_get_procedures_or_functions($db, 'PROCEDURE');
 320         $function_names = PMA_DBI_get_procedures_or_functions($db, 'FUNCTION');
 321
 322         if ($procedure_names || $function_names) {
 323             $text .= $crlf
 324               . 'DELIMITER ' . $delimiter . $crlf;
 325         }
 326
 327         if ($procedure_names) {
 328             $text .=
 329                 PMA_exportComment()
 330               . PMA_exportComment($GLOBALS['strProcedures'])
 331               . PMA_exportComment();
 332
 333             foreach($procedure_names as $procedure_name) {
 334                 if (! empty($GLOBALS['sql_drop_table'])) {
 335                     $text .= 'DROP PROCEDURE IF EXISTS ' . PMA_backquote($procedure_name) . $delimiter . $crlf;
 336                 }
 337                 $text .= PMA_DBI_get_definition($db, 'PROCEDURE', $procedure_name) . $delimiter . $crlf . $crlf;
 338             }
 339         }
 340
 341         if ($function_names) {
 342             $text .=
 343                 PMA_exportComment()
 344               . PMA_exportComment($GLOBALS['strFunctions'])
 345               . PMA_exportComment();
 346
 347             foreach($function_names as $function_name) {
 348                 if (! empty($GLOBALS['sql_drop_table'])) {
 349                     $text .= 'DROP FUNCTION IF EXISTS ' . PMA_backquote($function_name) . $delimiter . $crlf;
 350                 }
 351                 $text .= PMA_DBI_get_definition($db, 'FUNCTION', $function_name) . $delimiter . $crlf . $crlf;
 352             }
 353         }
 354
 355         if ($procedure_names || $function_names) {
 356             $text .= 'DELIMITER ;' . $crlf;
 357         }
 358
 359         if (! empty($text)) {
 360             $result = PMA_exportOutputHandler($text);
 361         }
 362     }
 363     return $result;
 364 }
 365
 366 /**
 367  * Outputs database header
 368  *
 369  * @param   string      Database name
 370  *
 371  * @return  bool        Whether it suceeded
 372  *
 373  * @access  public
 374  */
 375 function PMA_exportDBHeader($db)
 376 {
 377     $head = PMA_exportComment()
 378           . PMA_exportComment($GLOBALS['strDatabase'] . ': ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : '\'' . $db . '\''))
 379           . PMA_exportComment();
 380     return PMA_exportOutputHandler($head);
 381 }
 382
 383 /**
 384  * Outputs database footer
 385  *
 386  * @param   string      Database name
 387  *
 388  * @return  bool        Whether it suceeded
 389  *
 390  * @access  public
 391  */
 392 function PMA_exportDBFooter($db)
 393 {
 394     global $crlf;
 395
 396     $result = TRUE;
 397     if (isset($GLOBALS['sql_constraints'])) {
 398         $result = PMA_exportOutputHandler($GLOBALS['sql_constraints']);
 399         unset($GLOBALS['sql_constraints']);
 400     }
 401
 402     if (isset($GLOBALS['sql_structure']) && isset($GLOBALS['sql_procedure_function'])) {
 403         $text = '';
 404         $delimiter = '$$';
 405
 406         if (PMA_MYSQL_INT_VERSION > 50100) {
 407             $event_names = PMA_DBI_fetch_result('SELECT EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA= \'' . PMA_sqlAddslashes($db,true) . '\';');
 408         } else {
 409             $event_names = array();
 410         }
 411
 412         if ($event_names) {
 413             $text .= $crlf
 414               . 'DELIMITER ' . $delimiter . $crlf;
 415
 416             $text .=
 417                 PMA_exportComment()
 418               . PMA_exportComment($GLOBALS['strEvents'])
 419               . PMA_exportComment();
 420
 421             foreach($event_names as $event_name) {
 422                 if (! empty($GLOBALS['sql_drop_table'])) {
 423                     $text .= 'DROP EVENT ' . PMA_backquote($event_name) . $delimiter . $crlf;
 424                 }
 425                 $text .= PMA_DBI_get_definition($db, 'EVENT', $event_name) . $delimiter . $crlf . $crlf;
 426             }
 427
 428             $text .= 'DELIMITER ;' . $crlf;
 429         }
 430
 431         if (! empty($text)) {
 432             $result = PMA_exportOutputHandler($text);
 433         }
 434     }
 435     return $result;
 436 }
 437
 438
 439 /**
 440  * Returns a stand-in CREATE definition to resolve view dependencies
 441  *
 442  * @param   string   the database name
 443  * @param   string   the vew name
 444  * @param   string   the end of line sequence
 445  *
 446  * @return  string   resulting definition
 447  *
 448  * @access  public
 449  */
 450 function PMA_getTableDefStandIn($db, $view, $crlf) {
 451     $create_query = '';
 452     if (! empty($GLOBALS['sql_drop_table'])) {
 453         $create_query .= 'DROP VIEW IF EXISTS ' . PMA_backquote($view) . ';' . $crlf;
 454     }
 455
 456     $create_query .= 'CREATE TABLE ';
 457
 458     if (isset($GLOBALS['sql_if_not_exists']) && $GLOBALS['sql_if_not_exists']) {
 459         $create_query .= 'IF NOT EXISTS ';
 460     }
 461     $create_query .= PMA_backquote($view) . ' (' . $crlf;
 462     $tmp = array();
 463     $columns = PMA_DBI_get_columns_full($db, $view);
 464     foreach($columns as $column_name => $definition) {
 465         $tmp[] = PMA_backquote($column_name) . ' ' . $definition['Type'] . $crlf;
 466     }
 467     $create_query .= implode(',', $tmp) . ');';
 468     return($create_query);
 469 }
 470
 471 /**
 472  * Returns $table's CREATE definition
 473  *
 474  * @param   string   the database name
 475  * @param   string   the table name
 476  * @param   string   the end of line sequence
 477  * @param   string   the url to go back in case of error
 478  * @param   boolean  whether to include creation/update/check dates
 479  * @param   boolean  whether to add semicolon and end-of-line at the end
 480  * @param   boolean  whether we're handling view
 481  *
 482  * @return  string   resulting schema
 483  *
 484  * @global  boolean  whether to add 'drop' statements or not
 485  * @global  boolean  whether to use backquotes to allow the use of special
 486  *                   characters in database, table and fields names or not
 487  *
 488  * @access  public
 489  */
 490 function PMA_getTableDef($db, $table, $crlf, $error_url, $show_dates = false, $add_semicolon = true, $view = false)
 491 {
 492     global $sql_drop_table;
 493     global $sql_backquotes;
 494     global $cfgRelation;
 495     global $sql_constraints;
 496     global $sql_constraints_query; // just the text of the query
 497     global $sql_drop_foreign_keys;
 498
 499     $schema_create = '';
 500     $auto_increment = '';
 501     $new_crlf = $crlf;
 502
 503     // need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli
 504     $result = PMA_DBI_query('SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \'' . PMA_sqlAddslashes($table) . '\'', null, PMA_DBI_QUERY_STORE);
 505     if ($result != FALSE) {
 506         if (PMA_DBI_num_rows($result) > 0) {
 507             $tmpres        = PMA_DBI_fetch_assoc($result);
 508             // Here we optionally add the AUTO_INCREMENT next value,
 509             // but starting with MySQL 5.0.24, the clause is already included
 510             // in SHOW CREATE TABLE so we'll remove it below
 511             if (isset($GLOBALS['sql_auto_increment']) && !empty($tmpres['Auto_increment'])) {
 512                 $auto_increment .= ' AUTO_INCREMENT=' . $tmpres['Auto_increment'] . ' ';
 513             }
 514
 515             if ($show_dates && isset($tmpres['Create_time']) && !empty($tmpres['Create_time'])) {
 516                 $schema_create .= PMA_exportComment($GLOBALS['strStatCreateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Create_time'])));
 517                 $new_crlf = PMA_exportComment() . $crlf;
 518             }
 519
 520             if ($show_dates && isset($tmpres['Update_time']) && !empty($tmpres['Update_time'])) {
 521                 $schema_create .= PMA_exportComment($GLOBALS['strStatUpdateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Update_time'])));
 522                 $new_crlf = PMA_exportComment() . $crlf;
 523             }
 524
 525             if ($show_dates && isset($tmpres['Check_time']) && !empty($tmpres['Check_time'])) {
 526                 $schema_create .= PMA_exportComment($GLOBALS['strStatCheckTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Check_time'])));
 527                 $new_crlf = PMA_exportComment() . $crlf;
 528             }
 529         }
 530         PMA_DBI_free_result($result);
 531     }
 532
 533     $schema_create .= $new_crlf;
 534
 535     // no need to generate a DROP VIEW here, it was done earlier
 536     if (! empty($sql_drop_table) && ! PMA_Table::isView($db,$table)) {
 537         $schema_create .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table, $sql_backquotes) . ';' . $crlf;
 538     }
 539
 540     // Steve Alberty's patch for complete table dump,
 541     // Whether to quote table and fields names or not
 542     if ($sql_backquotes) {
 543         PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 1');
 544     } else {
 545         PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 0');
 546     }
 547
 548     // I don't see the reason why this unbuffered query could cause problems,
 549     // because SHOW CREATE TABLE returns only one row, and we free the
 550     // results below. Nonetheless, we got 2 user reports about this
 551     // (see bug 1562533) so I remove the unbuffered mode.
 552     //$result = PMA_DBI_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table), null, PMA_DBI_QUERY_UNBUFFERED);
 553     //
 554     // Note: SHOW CREATE TABLE, at least in MySQL 5.1.23, does not
 555     // produce a displayable result for the default value of a BIT
 556     // field, nor does the mysqldump command. See MySQL bug 35796
 557     $result = PMA_DBI_try_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table));
 558     // an error can happen, for example the table is crashed
 559     $tmp_error = PMA_DBI_getError();
 560     if ($tmp_error) {
 561         return PMA_exportComment($GLOBALS['strInUse'] . '(' . $tmp_error . ')');
 562     }
 563
 564     if ($result != FALSE && ($row = PMA_DBI_fetch_row($result))) {
 565         $create_query = $row[1];
 566         unset($row);
 567
 568         // Convert end of line chars to one that we want (note that MySQL doesn't return query it will accept in all cases)
 569         if (strpos($create_query, "(\r\n ")) {
 570             $create_query = str_replace("\r\n", $crlf, $create_query);
 571         } elseif (strpos($create_query, "(\n ")) {
 572             $create_query = str_replace("\n", $crlf, $create_query);
 573         } elseif (strpos($create_query, "(\r ")) {
 574             $create_query = str_replace("\r", $crlf, $create_query);
 575         }
 576
 577         /*
 578          * Drop database name from VIEW creation.
 579          *
 580          * This is a bit tricky, but we need to issue SHOW CREATE TABLE with
 581          * database name, but we don't want name to show up in CREATE VIEW
 582          * statement.
 583          */
 584         if ($view) {
 585             $create_query = preg_replace('/' . PMA_backquote($db) . '\./', '', $create_query);
 586         }
 587
 588         // Should we use IF NOT EXISTS?
 589         if (isset($GLOBALS['sql_if_not_exists'])) {
 590             $create_query     = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query);
 591         }
 592
 593         // are there any constraints to cut out?
 594         if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $create_query)) {
 595
 596             // Split the query into lines, so we can easily handle it. We know lines are separated by $crlf (done few lines above).
 597             $sql_lines = explode($crlf, $create_query);
 598             $sql_count = count($sql_lines);
 599
 600             // lets find first line with constraints
 601             for ($i = 0; $i < $sql_count; $i++) {
 602                 if (preg_match('@^[\s]*(CONSTRAINT|FOREIGN[\s]+KEY)@', $sql_lines[$i])) {
 603                     break;
 604                 }
 605             }
 606
 607             // If we really found a constraint
 608             if ($i != $sql_count) {
 609
 610                 // remove , from the end of create statement
 611                 $sql_lines[$i - 1] = preg_replace('@,$@', '', $sql_lines[$i - 1]);
 612
 613                 // prepare variable for constraints
 614                 if (!isset($sql_constraints)) {
 615                     if (isset($GLOBALS['no_constraints_comments'])) {
 616                         $sql_constraints = '';
 617                     } else {
 618                         $sql_constraints = $crlf
 619                                          . PMA_exportComment()
 620                                          . PMA_exportComment($GLOBALS['strConstraintsForDumped'])
 621                                          . PMA_exportComment();
 622                     }
 623                 }
 624
 625                 // comments for current table
 626                 if (!isset($GLOBALS['no_constraints_comments'])) {
 627                     $sql_constraints .= $crlf
 628                                      . PMA_exportComment()
 629                                      . PMA_exportComment($GLOBALS['strConstraintsForTable'] . ' ' . PMA_backquote($table))
 630                                      . PMA_exportComment();
 631                 }
 632
 633                 // let's do the work
 634                 $sql_constraints_query .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf;
 635                 $sql_constraints .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf;
 636                 $sql_drop_foreign_keys .= 'ALTER TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table) . $crlf;
 637
 638                 $first = TRUE;
 639                 for ($j = $i; $j < $sql_count; $j++) {
 640                     if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $sql_lines[$j])) {
 641                         if (!$first) {
 642                             $sql_constraints .= $crlf;
 643                         }
 644                         if (strpos($sql_lines[$j], 'CONSTRAINT') === FALSE) {
 645                             $str_tmp = preg_replace('/(FOREIGN[\s]+KEY)/', 'ADD \1', $sql_lines[$j]);
 646                             $sql_constraints_query .= $str_tmp;
 647                             $sql_constraints .= $str_tmp;
 648                         } else {
 649                             $str_tmp = preg_replace('/(CONSTRAINT)/', 'ADD \1', $sql_lines[$j]);
 650                             $sql_constraints_query .= $str_tmp;
 651                             $sql_constraints .= $str_tmp;
 652                             preg_match('/(CONSTRAINT)([\s])([\S]*)([\s])/', $sql_lines[$j], $matches);
 653                             if (! $first) {
 654                                 $sql_drop_foreign_keys .= ', ';
 655                             }
 656                             $sql_drop_foreign_keys .= 'DROP FOREIGN KEY ' . $matches[3];
 657                         }
 658                         $first = FALSE;
 659                     } else {
 660                         break;
 661                     }
 662                 }
 663                 $sql_constraints .= ';' . $crlf;
 664                 $sql_constraints_query .= ';';
 665
 666                 $create_query = implode($crlf, array_slice($sql_lines, 0, $i)) . $crlf . implode($crlf, array_slice($sql_lines, $j, $sql_count - 1));
 667                 unset($sql_lines);
 668             }
 669         }
 670         $schema_create .= $create_query;
 671     }
 672
 673     // remove a possible "AUTO_INCREMENT = value" clause
 674     // that could be there starting with MySQL 5.0.24
 675     $schema_create = preg_replace('/AUTO_INCREMENT\s*=\s*([0-9])+/', '', $schema_create);
 676
 677     $schema_create .= $auto_increment;
 678
 679     PMA_DBI_free_result($result);
 680     return $schema_create . ($add_semicolon ? ';' . $crlf : '');
 681 } // end of the 'PMA_getTableDef()' function
 682
 683
 684 /**
 685  * Returns $table's comments, relations etc.
 686  *
 687  * @param   string   the database name
 688  * @param   string   the table name
 689  * @param   string   the end of line sequence
 690  * @param   boolean  whether to include relation comments
 691  * @param   boolean  whether to include mime comments
 692  *
 693  * @return  string   resulting comments
 694  *
 695  * @access  public
 696  */
 697 function PMA_getTableComments($db, $table, $crlf, $do_relation = false,  $do_mime = false)
 698 {
 699     global $cfgRelation;
 700     global $sql_backquotes;
 701     global $sql_constraints;
 702
 703     $schema_create = '';
 704
 705     // Check if we can use Relations (Mike Beck)
 706     if ($do_relation && !empty($cfgRelation['relation'])) {
 707         // Find which tables are related with the current one and write it in
 708         // an array
 709         $res_rel = PMA_getForeigners($db, $table);
 710
 711         if ($res_rel && count($res_rel) > 0) {
 712             $have_rel = TRUE;
 713         } else {
 714             $have_rel = FALSE;
 715         }
 716     } else {
 717            $have_rel = FALSE;
 718     } // end if
 719
 720     if ($do_mime && $cfgRelation['mimework']) {
 721         if (!($mime_map = PMA_getMIME($db, $table, true))) {
 722             unset($mime_map);
 723         }
 724     }
 725
 726     if (isset($mime_map) && count($mime_map) > 0) {
 727         $schema_create .= PMA_possibleCRLF()
 728                        . PMA_exportComment()
 729                        . PMA_exportComment($GLOBALS['strMIMETypesForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':');
 730         @reset($mime_map);
 731         foreach ($mime_map AS $mime_field => $mime) {
 732             $schema_create .= PMA_exportComment('  ' . PMA_backquote($mime_field, $sql_backquotes))
 733                             . PMA_exportComment('      ' . PMA_backquote($mime['mimetype'], $sql_backquotes));
 734         }
 735         $schema_create .= PMA_exportComment();
 736     }
 737
 738     if ($have_rel) {
 739         $schema_create .= PMA_possibleCRLF()
 740                        . PMA_exportComment()
 741                        . PMA_exportComment($GLOBALS['strRelationsForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':');
 742         foreach ($res_rel AS $rel_field => $rel) {
 743             $schema_create .= PMA_exportComment('  ' . PMA_backquote($rel_field, $sql_backquotes))
 744                             . PMA_exportComment('      ' . PMA_backquote($rel['foreign_table'], $sql_backquotes)
 745                             . ' -> ' . PMA_backquote($rel['foreign_field'], $sql_backquotes));
 746         }
 747         $schema_create .= PMA_exportComment();
 748     }
 749
 750     return $schema_create;
 751
 752 } // end of the 'PMA_getTableComments()' function
 753
 754 /**
 755  * Outputs table's structure
 756  *
 757  * @param   string   the database name
 758  * @param   string   the table name
 759  * @param   string   the end of line sequence
 760  * @param   string   the url to go back in case of error
 761  * @param   boolean  whether to include relation comments
 762  * @param   boolean  whether to include the pmadb-style column comments
 763  *                   as comments in the structure; this is deprecated
 764  *                   but the parameter is left here because export.php
 765  *                   calls PMA_exportStructure() also for other export
 766  *                   types which use this parameter
 767  * @param   boolean  whether to include mime comments
 768  * @param   string   'stand_in', 'create_table', 'create_view'
 769  * @param   string   'server', 'database', 'table'
 770  *
 771  * @return  bool     Whether it suceeded
 772  *
 773  * @access  public
 774  */
 775 function PMA_exportStructure($db, $table, $crlf, $error_url, $relation = FALSE, $comments = FALSE, $mime = FALSE, $dates = FALSE, $export_mode, $export_type)
 776 {
 777     $formatted_table_name = (isset($GLOBALS['sql_backquotes']))
 778                           ? PMA_backquote($table)
 779                           : '\'' . $table . '\'';
 780     $dump = PMA_possibleCRLF()
 781           . PMA_exportComment(str_repeat('-', 56))
 782           . PMA_possibleCRLF()
 783           . PMA_exportComment();
 784
 785     switch($export_mode) {
 786         case 'create_table':
 787             $dump .=  PMA_exportComment($GLOBALS['strTableStructure'] . ' ' . $formatted_table_name)
 788                   . PMA_exportComment();
 789             $dump .= PMA_getTableDef($db, $table, $crlf, $error_url, $dates);
 790             $dump .= PMA_getTableComments($db, $table, $crlf, $relation, $mime);
 791             break;
 792         case 'triggers':
 793             $dump = '';
 794             $triggers = PMA_DBI_get_triggers($db, $table);
 795             if ($triggers) {
 796                 $dump .=  PMA_possibleCRLF()
 797                       . PMA_exportComment()
 798                       . PMA_exportComment($GLOBALS['strTriggers'] . ' ' . $formatted_table_name)
 799                       . PMA_exportComment();
 800                 $delimiter = '//';
 801                 foreach ($triggers as $trigger) {
 802                     $dump .= $trigger['drop'] . ';' . $crlf;
 803                     $dump .= 'DELIMITER ' . $delimiter . $crlf;
 804                     $dump .= $trigger['create'];
 805                     $dump .= 'DELIMITER ;' . $crlf;
 806                 }
 807             }
 808             break;
 809         case 'create_view':
 810             $dump .= PMA_exportComment($GLOBALS['strStructureForView'] . ' ' . $formatted_table_name)
 811                   .  PMA_exportComment();
 812             // delete the stand-in table previously created (if any)
 813             if ($export_type != 'table') {
 814                 $dump .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table) . ';' . $crlf;
 815             }
 816             $dump .= PMA_getTableDef($db, $table, $crlf, $error_url, $dates, true, true);
 817             break;
 818         case 'stand_in':
 819             $dump .=  PMA_exportComment($GLOBALS['strStandInStructureForView'] . ' ' . $formatted_table_name)
 820                   .  PMA_exportComment();
 821             // export a stand-in definition to resolve view dependencies
 822             $dump .= PMA_getTableDefStandIn($db, $table, $crlf);
 823     } // end switch
 824
 825     // this one is built by PMA_getTableDef() to use in table copy/move
 826     // but not in the case of export
 827     unset($GLOBALS['sql_constraints_query']);
 828
 829     return PMA_exportOutputHandler($dump);
 830 }
 831
 832 /**
 833  * Dispatches between the versions of 'getTableContent' to use depending
 834  * on the php version
 835  *
 836  * @param   string      the database name
 837  * @param   string      the table name
 838  * @param   string      the end of line sequence
 839  * @param   string      the url to go back in case of error
 840  * @param   string      SQL query for obtaining data
 841  *
 842  * @return  bool        Whether it suceeded
 843  *
 844  * @global  boolean  whether to use backquotes to allow the use of special
 845  *                   characters in database, table and fields names or not
 846  * @global  integer  the number of records
 847  * @global  integer  the current record position
 848  *
 849  * @access  public
 850  *
 851  * @see     PMA_getTableContentFast(), PMA_getTableContentOld()
 852  *
 853  * @author  staybyte
 854  */
 855 function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
 856 {
 857     global $sql_backquotes;
 858     global $rows_cnt;
 859     global $current_row;
 860
 861     $formatted_table_name = (isset($GLOBALS['sql_backquotes']))
 862                           ? PMA_backquote($table)
 863                           : '\'' . $table . '\'';
 864
 865     // Do not export data for a VIEW
 866     // (For a VIEW, this is called only when exporting a single VIEW)
 867     if (PMA_Table::isView($db, $table)) {
 868         $head = PMA_possibleCRLF()
 869           . PMA_exportComment()
 870           . PMA_exportComment('VIEW ' . ' ' . $formatted_table_name)
 871           . PMA_exportComment($GLOBALS['strData'] . ': ' . $GLOBALS['strNone'])
 872           . PMA_exportComment()
 873           . PMA_possibleCRLF();
 874
 875         if (! PMA_exportOutputHandler($head)) {
 876             return FALSE;
 877         }
 878         return true;
 879     }
 880
 881     // it's not a VIEW
 882     $head = PMA_possibleCRLF()
 883           . PMA_exportComment()
 884           . PMA_exportComment($GLOBALS['strDumpingData'] . ' ' . $formatted_table_name)
 885           . PMA_exportComment();
 886
 887     if (! PMA_exportOutputHandler($head)) {
 888         return FALSE;
 889     }
 890
 891     $buffer = '';
 892
 893     // analyze the query to get the true column names, not the aliases
 894     // (this fixes an undefined index, also if Complete inserts
 895     //  are used, we did not get the true column name in case of aliases)
 896     $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
 897
 898     $result      = PMA_DBI_try_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
 899     // a possible error: the table has crashed
 900     $tmp_error = PMA_DBI_getError();
 901     if ($tmp_error) {
 902         return PMA_exportOutputHandler(PMA_exportComment($GLOBALS['strInUse'] . ' (' . $tmp_error . ')'));
 903     }
 904
 905     if ($result != FALSE) {
 906         // emit a single CRLF before the first data statement (produces
 907         // an unintended CRLF when there is no data, but I don't see how it
 908         // can be avoided, as we are in UNBUFFERED mode)
 909         if (! PMA_exportOutputHandler($crlf)) {
 910             return FALSE;
 911         }
 912
 913         $fields_cnt     = PMA_DBI_num_fields($result);
 914
 915         // Get field information
 916         $fields_meta    = PMA_DBI_get_fields_meta($result);
 917         $field_flags    = array();
 918         for ($j = 0; $j < $fields_cnt; $j++) {
 919             $field_flags[$j] = PMA_DBI_field_flags($result, $j);
 920         }
 921
 922         for ($j = 0; $j < $fields_cnt; $j++) {
 923             if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
 924                 $field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $sql_backquotes);
 925             } else {
 926                 $field_set[$j] = PMA_backquote($fields_meta[$j]->name, $sql_backquotes);
 927             }
 928         }
 929
 930         if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
 931             // update
 932             $schema_insert  = 'UPDATE ';
 933             if (isset($GLOBALS['sql_ignore'])) {
 934                 $schema_insert .= 'IGNORE ';
 935             }
 936             // avoid EOL blank
 937             $schema_insert .= PMA_backquote($table, $sql_backquotes) . ' SET';
 938         } else {
 939             // insert or replace
 940             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') {
 941                 $sql_command    = 'REPLACE';
 942             } else {
 943                 $sql_command    = 'INSERT';
 944             }
 945
 946             // delayed inserts?
 947             if (isset($GLOBALS['sql_delayed'])) {
 948                 $insert_delayed = ' DELAYED';
 949             } else {
 950                 $insert_delayed = '';
 951             }
 952
 953             // insert ignore?
 954             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) {
 955                 $insert_delayed .= ' IGNORE';
 956             }
 957
 958             // scheme for inserting fields
 959             if (isset($GLOBALS['sql_columns'])) {
 960                 $fields        = implode(', ', $field_set);
 961                 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $sql_backquotes)
 962             // avoid EOL blank
 963                                . ' (' . $fields . ') VALUES';
 964             } else {
 965                 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $sql_backquotes)
 966                                . ' VALUES';
 967             }
 968         }
 969
 970         $search       = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
 971         $replace      = array('\0', '\n', '\r', '\Z');
 972         $current_row  = 0;
 973         $query_size   = 0;
 974         if (isset($GLOBALS['sql_extended']) && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) {
 975             $separator    = ',';
 976             $schema_insert .= $crlf;
 977         } else {
 978             $separator    = ';';
 979         }
 980
 981         while ($row = PMA_DBI_fetch_row($result)) {
 982             $current_row++;
 983             for ($j = 0; $j < $fields_cnt; $j++) {
 984                 // NULL
 985                 if (!isset($row[$j]) || is_null($row[$j])) {
 986                     $values[]     = 'NULL';
 987                 // a number
 988                 // timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
 989                 } elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp'
 990                         && ! $fields_meta[$j]->blob) {
 991                     $values[] = $row[$j];
 992                 // a true BLOB
 993                 // - mysqldump only generates hex data when the --hex-blob
 994                 //   option is used, for fields having the binary attribute
 995                 //   no hex is generated
 996                 // - a TEXT field returns type blob but a real blob
 997                 //   returns also the 'binary' flag
 998                 } elseif (stristr($field_flags[$j], 'BINARY')
 999                         && $fields_meta[$j]->blob
1000                         && isset($GLOBALS['sql_hex_for_blob'])) {
1001                     // empty blobs need to be different, but '0' is also empty :-(
1002                     if (empty($row[$j]) && $row[$j] != '0') {
1003                         $values[] = '\'\'';
1004                     } else {
1005                         $values[] = '0x' . bin2hex($row[$j]);
1006                     }
1007                 // detection of 'bit' works only on mysqli extension
1008                 } elseif ($fields_meta[$j]->type == 'bit') {
1009                     $values[] = "b'" . PMA_sqlAddslashes(PMA_printable_bit_value($row[$j], $fields_meta[$j]->length)) . "'";
1010                 // something else -> treat as a string
1011                 } else {
1012                     $values[] = '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\'';
1013                 } // end if
1014             } // end for
1015
1016             // should we make update?
1017             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
1018
1019                 $insert_line = $schema_insert;
1020                 for ($i = 0; $i < $fields_cnt; $i++) {
1021                     if (0 == $i) {
1022                         $insert_line .= ' ';
1023                     }
1024                     if ($i > 0) {
1025                         // avoid EOL blank
1026                         $insert_line .= ',';
1027                     }
1028                     $insert_line .= $field_set[$i] . ' = ' . $values[$i];
1029                 }
1030
1031                 list($tmp_unique_condition, $tmp_clause_is_unique) = PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $row);
1032                 $insert_line .= ' WHERE ' . $tmp_unique_condition;
1033                 unset($tmp_unique_condition, $tmp_clause_is_unique);
1034
1035             } else {
1036
1037                 // Extended inserts case
1038                 if (isset($GLOBALS['sql_extended'])) {
1039                     if ($current_row == 1) {
1040                         $insert_line  = $schema_insert . '(' . implode(', ', $values) . ')';
1041                     } else {
1042                         $insert_line  = '(' . implode(', ', $values) . ')';
1043                         if (isset($GLOBALS['sql_max_query_size']) && $GLOBALS['sql_max_query_size'] > 0 && $query_size + strlen($insert_line) > $GLOBALS['sql_max_query_size']) {
1044                             if (!PMA_exportOutputHandler(';' . $crlf)) {
1045                                 return FALSE;
1046                             }
1047                             $query_size = 0;
1048                             $current_row = 1;
1049                             $insert_line = $schema_insert . $insert_line;
1050                         }
1051                     }
1052                     $query_size += strlen($insert_line);
1053                 }
1054                 // Other inserts case
1055                 else {
1056                     $insert_line      = $schema_insert . '(' . implode(', ', $values) . ')';
1057                 }
1058             }
1059             unset($values);
1060
1061             if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) {
1062                 return FALSE;
1063             }
1064
1065         } // end while
1066         if ($current_row > 0) {
1067             if (!PMA_exportOutputHandler(';' . $crlf)) {
1068                 return FALSE;
1069             }
1070         }
1071     } // end if ($result != FALSE)
1072     PMA_DBI_free_result($result);
1073
1074     return TRUE;
1075 } // end of the 'PMA_exportData()' function
1076 }
1077 ?>