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