libraries/export/sql.php

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