2
/* $Id: sql.php 10002 2007-02-17 18:06:11Z lem9 $ */
3
// vim: expandtab sw=4 ts=4 sts=4:
5
* Set of functions used to build SQL dumps of tables
8
if (isset($plugin_list)) {
10
$hide_structure = false;
11
if ($plugin_param['export_type'] == 'table' && !$plugin_param['single_table']) {
12
$hide_structure = true;
16
$plugin_list['sql'] = array(
19
'mime_type' => 'text/x-sql',
21
array('type' => 'text', 'name' => 'header_comment', 'text' => 'strAddHeaderComment'),
22
array('type' => 'bool', 'name' => 'use_transaction', 'text' => 'strEncloseInTransaction'),
23
array('type' => 'bool', 'name' => 'disable_fk', 'text' => 'strDisableForeignChecks'),
25
'options_text' => 'strSQLOptions',
27
$compats = PMA_DBI_getCompatibilities();
28
if (count($compats) > 0) {
30
foreach($compats as $val) {
33
$plugin_list['sql']['options'][] =
34
array('type' => 'select', 'name' => 'compatibility', 'text' => 'strSQLCompatibility', 'values' => $values, 'doc' => array('manual_MySQL_Database_Administration', 'Server_SQL_mode'));
38
/* Server export options */
39
if ($plugin_param['export_type'] == 'server') {
40
$plugin_list['sql']['options'][] =
41
array('type' => 'bgroup', 'text' => 'strDatabaseExportOptions');
42
$plugin_list['sql']['options'][] =
43
array('type' => 'bool', 'name' => 'drop_database', 'text' => sprintf($GLOBALS['strAddClause'], 'DROP DATABASE'));
44
$plugin_list['sql']['options'][] =
45
array('type' => 'egroup');
48
/* Structure options */
49
if (!$hide_structure) {
50
$plugin_list['sql']['options'][] =
51
array('type' => 'bgroup', 'name' => 'structure', 'text' => 'strStructure', 'force' => 'data');
52
if ($plugin_param['export_type'] == 'table') {
53
if (PMA_Table::_isView($GLOBALS['db'], $GLOBALS['table'])) {
54
$drop_clause = 'DROP VIEW';
56
$drop_clause = 'DROP TABLE';
58
} elseif (PMA_MYSQL_INT_VERSION >= 50000) {
59
$drop_clause = 'DROP TABLE / DROP VIEW';
61
$drop_clause = 'DROP TABLE';
63
$plugin_list['sql']['options'][] =
64
array('type' => 'bool', 'name' => 'drop_table', 'text' => sprintf($GLOBALS['strAddClause'], $drop_clause));
65
$plugin_list['sql']['options'][] =
66
array('type' => 'bool', 'name' => 'if_not_exists', 'text' => sprintf($GLOBALS['strAddClause'], 'IF NOT EXISTS'));
67
$plugin_list['sql']['options'][] =
68
array('type' => 'bool', 'name' => 'auto_increment', 'text' => 'strAddAutoIncrement');
69
$plugin_list['sql']['options'][] =
70
array('type' => 'bool', 'name' => 'backquotes', 'text' => 'strUseBackquotes');
73
$plugin_list['sql']['options'][] =
74
array('type' => 'bgroup', 'text' => 'strAddIntoComments');
75
$plugin_list['sql']['options'][] =
76
array('type' => 'bool', 'name' => 'dates', 'text' => 'strCreationDates');
77
if (!empty($GLOBALS['cfgRelation']['relation'])) {
78
$plugin_list['sql']['options'][] =
79
array('type' => 'bool', 'name' => 'relation', 'text' => 'strRelations');
81
if (!empty($GLOBALS['cfgRelation']['commwork']) && PMA_MYSQL_INT_VERSION < 40100) {
82
$plugin_list['sql']['options'][] =
83
array('type' => 'bool', 'name' => 'comments', 'text' => 'strComments');
85
if (!empty($GLOBALS['cfgRelation']['mimework'])) {
86
$plugin_list['sql']['options'][] =
87
array('type' => 'bool', 'name' => 'mime', 'text' => 'strMIME_MIMEtype');
89
$plugin_list['sql']['options'][] =
90
array('type' => 'egroup');
92
$plugin_list['sql']['options'][] =
93
array('type' => 'egroup');
97
$plugin_list['sql']['options'][] =
98
array('type' => 'bgroup', 'name' => 'data', 'text' => 'strData', 'force' => 'structure');
99
$plugin_list['sql']['options'][] =
100
array('type' => 'bool', 'name' => 'columns', 'text' => 'strCompleteInserts');
101
$plugin_list['sql']['options'][] =
102
array('type' => 'bool', 'name' => 'extended', 'text' => 'strExtendedInserts');
103
$plugin_list['sql']['options'][] =
104
array('type' => 'text', 'name' => 'max_query_size', 'text' => 'strMaximalQueryLength');
105
$plugin_list['sql']['options'][] =
106
array('type' => 'bool', 'name' => 'delayed', 'text' => 'strDelayedInserts');
107
$plugin_list['sql']['options'][] =
108
array('type' => 'bool', 'name' => 'ignore', 'text' => 'strIgnoreInserts');
109
$plugin_list['sql']['options'][] =
110
array('type' => 'bool', 'name' => 'hex_for_binary', 'text' => 'strHexForBinary');
111
$plugin_list['sql']['options'][] =
112
array('type' => 'select', 'name' => 'type', 'text' => 'strSQLExportType', 'values' => array('INSERT', 'UPDATE', 'REPLACE'));
113
$plugin_list['sql']['options'][] =
114
array('type' => 'egroup');
119
* Marker for comments, -- is needed for ANSI SQL.
121
$GLOBALS['comment_marker'] = '-- ';
124
* Avoids undefined variables, use NULL so isset() returns false
126
if ( ! isset( $sql_backquotes ) ) {
127
$sql_backquotes = null;
133
* @param string Text of comment
135
* @return bool Whether it suceeded
137
function PMA_exportComment($text)
139
return PMA_exportOutputHandler($GLOBALS['comment_marker'] . $text . $GLOBALS['crlf']);
143
* Outputs export footer
145
* @return bool Whether it suceeded
149
function PMA_exportFooter()
152
global $mysql_charset_map;
156
if (isset($GLOBALS['sql_disable_fk'])) {
157
$foot .= $crlf . 'SET FOREIGN_KEY_CHECKS=1;' . $crlf;
160
if (isset($GLOBALS['sql_use_transaction'])) {
161
$foot .= $crlf . 'COMMIT;' . $crlf;
164
// restore connection settings
165
// (not set if $cfg['AllowAnywhereRecoding'] is false)
166
$charset_of_file = isset($GLOBALS['charset_of_file']) ? $GLOBALS['charset_of_file'] : '';
167
if (!empty($GLOBALS['asfile']) && isset($mysql_charset_map[$charset_of_file])) {
169
. '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;' . $crlf
170
. '/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;' . $crlf
171
. '/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;' . $crlf;
174
return PMA_exportOutputHandler($foot);
178
* Outputs export header
180
* @return bool Whether it suceeded
184
function PMA_exportHeader()
188
global $mysql_charset_map;
190
if (PMA_MYSQL_INT_VERSION >= 40100 && isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] != 'NONE') {
191
PMA_DBI_try_query('SET SQL_MODE="' . $GLOBALS['sql_compatibility'] . '"');
194
$head = $GLOBALS['comment_marker'] . 'phpMyAdmin SQL Dump' . $crlf
195
. $GLOBALS['comment_marker'] . 'version ' . PMA_VERSION . $crlf
196
. $GLOBALS['comment_marker'] . 'http://www.phpmyadmin.net' . $crlf
197
. $GLOBALS['comment_marker'] . $crlf
198
. $GLOBALS['comment_marker'] . $GLOBALS['strHost'] . ': ' . $cfg['Server']['host'];
199
if (!empty($cfg['Server']['port'])) {
200
$head .= ':' . $cfg['Server']['port'];
203
. $GLOBALS['comment_marker'] . $GLOBALS['strGenTime'] . ': ' . PMA_localisedDate() . $crlf
204
. $GLOBALS['comment_marker'] . $GLOBALS['strServerVersion'] . ': ' . substr(PMA_MYSQL_INT_VERSION, 0, 1) . '.' . (int) substr(PMA_MYSQL_INT_VERSION, 1, 2) . '.' . (int) substr(PMA_MYSQL_INT_VERSION, 3) . $crlf
205
. $GLOBALS['comment_marker'] . $GLOBALS['strPHPVersion'] . ': ' . phpversion() . $crlf;
207
if (isset($GLOBALS['sql_header_comment']) && !empty($GLOBALS['sql_header_comment'])) {
208
$lines = explode('\n', $GLOBALS['sql_header_comment']);
209
$head .= $GLOBALS['comment_marker'] . $crlf
210
. $GLOBALS['comment_marker'] . implode($crlf . $GLOBALS['comment_marker'], $lines) . $crlf
211
. $GLOBALS['comment_marker'] . $crlf;
214
if (isset($GLOBALS['sql_disable_fk'])) {
215
$head .= $crlf . 'SET FOREIGN_KEY_CHECKS=0;' . $crlf;
218
/* We want exported AUTO_INCREMENT fields to have still same value, do this only for recent MySQL exports */
219
if (!isset($GLOBALS['sql_compatibility']) || $GLOBALS['sql_compatibility'] == 'NONE') {
220
$head .= $crlf . 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' . $crlf;
223
if (isset($GLOBALS['sql_use_transaction'])) {
224
$head .= $crlf .'SET AUTOCOMMIT=0;' . $crlf
225
. 'START TRANSACTION;' . $crlf;
230
$charset_of_file = isset($GLOBALS['charset_of_file']) ? $GLOBALS['charset_of_file'] : '';
231
if (!empty($GLOBALS['asfile']) && isset($mysql_charset_map[$charset_of_file])) {
233
. '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;' . $crlf
234
. '/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;' . $crlf
235
. '/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;' . $crlf
236
. '/*!40101 SET NAMES ' . $mysql_charset_map[$charset_of_file] . ' */;' . $crlf . $crlf;
239
return PMA_exportOutputHandler($head);
243
* Outputs CREATE DATABASE database
245
* @param string Database name
247
* @return bool Whether it suceeded
251
function PMA_exportDBCreate($db)
254
if (isset($GLOBALS['sql_drop_database'])) {
255
if (!PMA_exportOutputHandler('DROP DATABASE ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : $db) . ';' . $crlf)) {
259
$create_query = 'CREATE DATABASE ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : $db);
260
if (PMA_MYSQL_INT_VERSION >= 40101) {
261
$collation = PMA_getDbCollation($db);
262
if (strpos($collation, '_')) {
263
$create_query .= ' DEFAULT CHARACTER SET ' . substr($collation, 0, strpos($collation, '_')) . ' COLLATE ' . $collation;
265
$create_query .= ' DEFAULT CHARACTER SET ' . $collation;
268
$create_query .= ';' . $crlf;
269
if (!PMA_exportOutputHandler($create_query)) {
272
if (isset($GLOBALS['sql_backquotes']) && PMA_MYSQL_INT_VERSION >= 40100 && isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] == 'NONE') {
273
return PMA_exportOutputHandler('USE ' . PMA_backquote($db) . ';' . $crlf);
275
return PMA_exportOutputHandler('USE ' . $db . ';' . $crlf);
279
* Outputs database header
281
* @param string Database name
283
* @return bool Whether it suceeded
287
function PMA_exportDBHeader($db)
290
$head = $GLOBALS['comment_marker'] . $crlf
291
. $GLOBALS['comment_marker'] . $GLOBALS['strDatabase'] . ': ' . (isset($GLOBALS['sql_backquotes']) ? PMA_backquote($db) : '\'' . $db . '\''). $crlf
292
. $GLOBALS['comment_marker'] . $crlf;
293
return PMA_exportOutputHandler($head);
297
* Outputs database footer
299
* @param string Database name
301
* @return bool Whether it suceeded
305
function PMA_exportDBFooter($db)
307
global $crlf, $comment_marker;
310
if (isset($GLOBALS['sql_constraints'])) {
311
$result = PMA_exportOutputHandler($GLOBALS['sql_constraints']);
312
unset($GLOBALS['sql_constraints']);
315
if (PMA_MYSQL_INT_VERSION >= 50000 && isset($GLOBALS['sql_structure'])) {
318
$procedure_names = PMA_DBI_get_procedures_or_functions($db, 'PROCEDURE');
319
if ($procedure_names) {
322
. $comment_marker . $crlf
323
. $comment_marker . $GLOBALS['strProcedures'] . $crlf
324
. $comment_marker . $crlf
325
. 'DELIMITER ' . $delimiter . $crlf
326
. $comment_marker . $crlf;
328
foreach($procedure_names as $procedure_name) {
329
$procs_funcs .= PMA_DBI_get_procedure_or_function_def($db, 'PROCEDURE', $procedure_name) . $delimiter . $crlf . $crlf;
332
$procs_funcs .= $comment_marker . $crlf
333
. 'DELIMITER ;' . $crlf
334
. $comment_marker . $crlf;
337
$function_names = PMA_DBI_get_procedures_or_functions($db, 'FUNCTION');
339
if ($function_names) {
340
$procs_funcs .= $comment_marker . $GLOBALS['strFunctions'] . $crlf
341
. $comment_marker . $crlf . $crlf;
343
foreach($function_names as $function_name) {
344
$procs_funcs .= PMA_DBI_get_procedure_or_function_def($db, 'FUNCTION', $function_name) . $crlf . $crlf;
347
if ( !empty($procs_funcs)) {
348
$result = PMA_exportOutputHandler($procs_funcs);
356
* Returns a stand-in CREATE definition to resolve view dependencies
358
* @param string the database name
359
* @param string the vew name
360
* @param string the end of line sequence
362
* @return string resulting definition
366
function PMA_getTableDefStandIn($db, $view, $crlf) {
367
$create_query = 'CREATE TABLE ' . PMA_backquote($view) . ' (' . $crlf;
369
$columns = PMA_DBI_get_columns_full($db, $view);
370
foreach($columns as $column_name => $definition) {
371
$tmp[] = PMA_backquote($column_name) . ' ' . $definition['Type'] . $crlf;
373
$create_query .= implode(',', $tmp) . ');';
374
return($create_query);
378
* Returns $table's CREATE definition
380
* @param string the database name
381
* @param string the table name
382
* @param string the end of line sequence
383
* @param string the url to go back in case of error
384
* @param boolean whether to include creation/update/check dates
386
* @return string resulting schema
388
* @global boolean whether to add 'drop' statements or not
389
* @global boolean whether to use backquotes to allow the use of special
390
* characters in database, table and fields names or not
394
function PMA_getTableDef($db, $table, $crlf, $error_url, $show_dates = false)
396
global $sql_drop_table;
397
global $sql_backquotes;
399
global $sql_constraints;
400
global $sql_constraints_query; // just the text of the query
403
$auto_increment = '';
406
// need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli
407
$result = PMA_DBI_query('SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \'' . PMA_sqlAddslashes($table) . '\'', null, PMA_DBI_QUERY_STORE);
408
if ($result != FALSE) {
409
if (PMA_DBI_num_rows($result) > 0) {
410
$tmpres = PMA_DBI_fetch_assoc($result);
411
// Here we optionally add the AUTO_INCREMENT next value,
412
// but starting with MySQL 5.0.24, the clause is already included
413
// in SHOW CREATE TABLE so we'll remove it below
414
if (isset($GLOBALS['sql_auto_increment']) && !empty($tmpres['Auto_increment'])) {
415
$auto_increment .= ' AUTO_INCREMENT=' . $tmpres['Auto_increment'] . ' ';
418
if ($show_dates && isset($tmpres['Create_time']) && !empty($tmpres['Create_time'])) {
419
$schema_create .= $GLOBALS['comment_marker'] . $GLOBALS['strStatCreateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Create_time'])) . $crlf;
420
$new_crlf = $GLOBALS['comment_marker'] . $crlf . $crlf;
423
if ($show_dates && isset($tmpres['Update_time']) && !empty($tmpres['Update_time'])) {
424
$schema_create .= $GLOBALS['comment_marker'] . $GLOBALS['strStatUpdateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Update_time'])) . $crlf;
425
$new_crlf = $GLOBALS['comment_marker'] . $crlf . $crlf;
428
if ($show_dates && isset($tmpres['Check_time']) && !empty($tmpres['Check_time'])) {
429
$schema_create .= $GLOBALS['comment_marker'] . $GLOBALS['strStatCheckTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Check_time'])) . $crlf;
430
$new_crlf = $GLOBALS['comment_marker'] . $crlf . $crlf;
433
PMA_DBI_free_result($result);
436
$schema_create .= $new_crlf;
438
if (!empty($sql_drop_table)) {
439
if (PMA_Table::_isView($db,$table)) {
440
$drop_clause = 'DROP VIEW';
442
$drop_clause = 'DROP TABLE';
444
$schema_create .= $drop_clause . ' IF EXISTS ' . PMA_backquote($table, $sql_backquotes) . ';' . $crlf;
448
// Steve Alberty's patch for complete table dump,
449
// Whether to quote table and fields names or not
450
if ($sql_backquotes) {
451
PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 1');
453
PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 0');
456
// I don't see the reason why this unbuffered query could cause problems,
457
// because SHOW CREATE TABLE returns only one row, and we free the
458
// results below. Nonetheless, we got 2 user reports about this
459
// (see bug 1562533) so I remove the unbuffered mode.
460
//$result = PMA_DBI_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table), null, PMA_DBI_QUERY_UNBUFFERED);
461
$result = PMA_DBI_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table));
462
if ($result != FALSE && ($row = PMA_DBI_fetch_row($result))) {
463
$create_query = $row[1];
466
// Convert end of line chars to one that we want (note that MySQL doesn't return query it will accept in all cases)
467
if (strpos($create_query, "(\r\n ")) {
468
$create_query = str_replace("\r\n", $crlf, $create_query);
469
} elseif (strpos($create_query, "(\n ")) {
470
$create_query = str_replace("\n", $crlf, $create_query);
471
} elseif (strpos($create_query, "(\r ")) {
472
$create_query = str_replace("\r", $crlf, $create_query);
475
// Should we use IF NOT EXISTS?
476
if (isset($GLOBALS['sql_if_not_exists'])) {
477
$create_query = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query);
480
// are there any constraints to cut out?
481
if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $create_query)) {
483
// Split the query into lines, so we can easily handle it. We know lines are separated by $crlf (done few lines above).
484
$sql_lines = explode($crlf, $create_query);
485
$sql_count = count($sql_lines);
487
// lets find first line with constraints
488
for ($i = 0; $i < $sql_count; $i++) {
489
if (preg_match('@^[\s]*(CONSTRAINT|FOREIGN[\s]+KEY)@', $sql_lines[$i])) {
494
// If we really found a constraint
495
if ($i != $sql_count) {
497
// remove , from the end of create statement
498
$sql_lines[$i - 1] = preg_replace('@,$@', '', $sql_lines[$i - 1]);
500
// prepare variable for constraints
501
if (!isset($sql_constraints)) {
502
if (isset($GLOBALS['no_constraints_comments'])) {
503
$sql_constraints = '';
505
$sql_constraints = $crlf . $GLOBALS['comment_marker'] .
506
$crlf . $GLOBALS['comment_marker'] . $GLOBALS['strConstraintsForDumped'] .
507
$crlf . $GLOBALS['comment_marker'] . $crlf;
511
// comments for current table
512
if (!isset($GLOBALS['no_constraints_comments'])) {
513
$sql_constraints .= $crlf . $GLOBALS['comment_marker'] .
514
$crlf . $GLOBALS['comment_marker'] . $GLOBALS['strConstraintsForTable'] . ' ' . PMA_backquote($table) .
515
$crlf . $GLOBALS['comment_marker'] . $crlf;
519
$sql_constraints_query .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf;
520
$sql_constraints .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf;
523
for ($j = $i; $j < $sql_count; $j++) {
524
if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $sql_lines[$j])) {
526
$sql_constraints .= $crlf;
528
if (strpos($sql_lines[$j], 'CONSTRAINT') === FALSE) {
529
$str_tmp = preg_replace('/(FOREIGN[\s]+KEY)/', 'ADD \1', $sql_lines[$j]);
530
$sql_constraints_query .= $str_tmp;
531
$sql_constraints .= $str_tmp;
533
$str_tmp = preg_replace('/(CONSTRAINT)/', 'ADD \1', $sql_lines[$j]);
534
$sql_constraints_query .= $str_tmp;
535
$sql_constraints .= $str_tmp;
542
$sql_constraints .= ';' . $crlf;
543
$sql_constraints_query .= ';';
545
$create_query = implode($crlf, array_slice($sql_lines, 0, $i)) . $crlf . implode($crlf, array_slice($sql_lines, $j, $sql_count - 1));
549
$schema_create .= $create_query;
552
// remove a possible "AUTO_INCREMENT = value" clause
553
// that could be there starting with MySQL 5.0.24
554
$schema_create = preg_replace('/AUTO_INCREMENT\s*=\s*([0-9])+/', '', $schema_create);
556
$schema_create .= $auto_increment;
558
PMA_DBI_free_result($result);
559
return $schema_create;
560
} // end of the 'PMA_getTableDef()' function
564
* Returns $table's comments, relations etc.
566
* @param string the database name
567
* @param string the table name
568
* @param string the end of line sequence
569
* @param boolean whether to include relation comments
570
* @param boolean whether to include column comments
571
* @param boolean whether to include mime comments
573
* @return string resulting comments
577
function PMA_getTableComments($db, $table, $crlf, $do_relation = false, $do_comments = false, $do_mime = false)
580
global $sql_backquotes;
581
global $sql_constraints;
585
// triggered only for MySQL < 4.1.x (pmadb-style comments)
586
if ($do_comments && $cfgRelation['commwork']) {
587
if (!($comments_map = PMA_getComments($db, $table))) {
588
unset($comments_map);
592
// Check if we can use Relations (Mike Beck)
593
if ($do_relation && !empty($cfgRelation['relation'])) {
594
// Find which tables are related with the current one and write it in
596
$res_rel = PMA_getForeigners($db, $table);
598
if ($res_rel && count($res_rel) > 0) {
607
if ($do_mime && $cfgRelation['mimework']) {
608
if (!($mime_map = PMA_getMIME($db, $table, true))) {
613
if (isset($comments_map) && count($comments_map) > 0) {
614
$schema_create .= $crlf . $GLOBALS['comment_marker'] . $crlf
615
. $GLOBALS['comment_marker'] . $GLOBALS['strCommentsForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':' . $crlf;
616
foreach ($comments_map AS $comment_field => $comment) {
617
$schema_create .= $GLOBALS['comment_marker'] . ' ' . PMA_backquote($comment_field, $sql_backquotes) . $crlf
618
. $GLOBALS['comment_marker'] . ' ' . PMA_backquote($comment, $sql_backquotes) . $crlf;
620
$schema_create .= $GLOBALS['comment_marker'] . $crlf;
623
if (isset($mime_map) && count($mime_map) > 0) {
624
$schema_create .= $crlf . $GLOBALS['comment_marker'] . $crlf
625
. $GLOBALS['comment_marker'] . $GLOBALS['strMIMETypesForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':' . $crlf;
627
foreach ($mime_map AS $mime_field => $mime) {
628
$schema_create .= $GLOBALS['comment_marker'] . ' ' . PMA_backquote($mime_field, $sql_backquotes) . $crlf
629
. $GLOBALS['comment_marker'] . ' ' . PMA_backquote($mime['mimetype'], $sql_backquotes) . $crlf;
631
$schema_create .= $GLOBALS['comment_marker'] . $crlf;
635
$schema_create .= $crlf . $GLOBALS['comment_marker'] . $crlf
636
. $GLOBALS['comment_marker'] . $GLOBALS['strRelationsForTable']. ' ' . PMA_backquote($table, $sql_backquotes) . ':' . $crlf;
637
foreach ($res_rel AS $rel_field => $rel) {
638
$schema_create .= $GLOBALS['comment_marker'] . ' ' . PMA_backquote($rel_field, $sql_backquotes) . $crlf
639
. $GLOBALS['comment_marker'] . ' ' . PMA_backquote($rel['foreign_table'], $sql_backquotes)
640
. ' -> ' . PMA_backquote($rel['foreign_field'], $sql_backquotes) . $crlf;
642
$schema_create .= $GLOBALS['comment_marker'] . $crlf;
645
return $schema_create;
647
} // end of the 'PMA_getTableComments()' function
650
* Outputs table's structure
652
* @param string the database name
653
* @param string the table name
654
* @param string the end of line sequence
655
* @param string the url to go back in case of error
656
* @param boolean whether to include relation comments
657
* @param boolean whether to include column comments
658
* @param boolean whether to include mime comments
659
* @param string 'stand_in', 'create_table', 'create_view'
661
* @return bool Whether it suceeded
665
function PMA_exportStructure($db, $table, $crlf, $error_url, $relation = FALSE, $comments = FALSE, $mime = FALSE, $dates = FALSE, $export_mode)
667
$formatted_table_name = (isset($GLOBALS['sql_backquotes']))
668
? PMA_backquote($table)
669
: '\'' . $table . '\'';
671
. $GLOBALS['comment_marker'] . '--------------------------------------------------------' . $crlf
672
. $crlf . $GLOBALS['comment_marker'] . $crlf;
674
switch($export_mode) {
676
$dump .= $GLOBALS['comment_marker'] . $GLOBALS['strTableStructure'] . ' ' . $formatted_table_name . $crlf
677
. $GLOBALS['comment_marker'] . $crlf;
678
$dump .= PMA_getTableDef($db, $table, $crlf, $error_url, $dates) . ';' . $crlf;
681
$dump .= $GLOBALS['comment_marker'] . $GLOBALS['strStructureForView'] . ' ' . $formatted_table_name . $crlf
682
. $GLOBALS['comment_marker'] . $crlf;
683
// delete the stand-in table previously created
684
$dump .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table) . ';' . $crlf;
685
$dump .= PMA_getTableDef($db, $table, $crlf, $error_url, $dates) . ';' . $crlf;
688
$dump .= $GLOBALS['comment_marker'] . $GLOBALS['strStandInStructureForView'] . ' ' . $formatted_table_name . $crlf
689
. $GLOBALS['comment_marker'] . $crlf;
690
// export a stand-in definition to resolve view dependencies
691
$dump .= PMA_getTableDefStandIn($db, $table, $crlf);
694
$dump .= PMA_getTableComments($db, $table, $crlf, $relation, $comments, $mime);
695
// this one is built by PMA_getTableDef() to use in table copy/move
696
// but not in the case of export
697
unset($GLOBALS['sql_constraints_query']);
699
return PMA_exportOutputHandler($dump);
703
* Dispatches between the versions of 'getTableContent' to use depending
706
* @param string the database name
707
* @param string the table name
708
* @param string the end of line sequence
709
* @param string the url to go back in case of error
710
* @param string SQL query for obtaining data
712
* @return bool Whether it suceeded
714
* @global boolean whether to use backquotes to allow the use of special
715
* characters in database, table and fields names or not
716
* @global integer the number of records
717
* @global integer the current record position
721
* @see PMA_getTableContentFast(), PMA_getTableContentOld()
725
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
727
global $sql_backquotes;
731
$formatted_table_name = (isset($GLOBALS['sql_backquotes']))
732
? PMA_backquote($table)
733
: '\'' . $table . '\'';
735
. $GLOBALS['comment_marker'] . $crlf
736
. $GLOBALS['comment_marker'] . $GLOBALS['strDumpingData'] . ' ' . $formatted_table_name . $crlf
737
. $GLOBALS['comment_marker'] . $crlf .$crlf;
739
if (!PMA_exportOutputHandler($head)) {
745
// analyze the query to get the true column names, not the aliases
746
// (this fixes an undefined index, also if Complete inserts
747
// are used, we did not get the true column name in case of aliases)
748
$analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
750
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
751
if ($result != FALSE) {
752
$fields_cnt = PMA_DBI_num_fields($result);
754
// Get field information
755
$fields_meta = PMA_DBI_get_fields_meta($result);
756
$field_flags = array();
757
for ($j = 0; $j < $fields_cnt; $j++) {
758
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
761
for ($j = 0; $j < $fields_cnt; $j++) {
762
if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
763
$field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $sql_backquotes);
765
$field_set[$j] = PMA_backquote($fields_meta[$j]->name, $sql_backquotes);
769
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
771
$schema_insert = 'UPDATE ';
772
if (isset($GLOBALS['sql_ignore'])) {
773
$schema_insert .= 'IGNORE ';
775
$schema_insert .= PMA_backquote($table, $sql_backquotes) . ' SET ';
778
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') {
779
$sql_command = 'REPLACE';
781
$sql_command = 'INSERT';
785
if (isset($GLOBALS['sql_delayed'])) {
786
$insert_delayed = ' DELAYED';
788
$insert_delayed = '';
792
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) {
793
$insert_delayed .= ' IGNORE';
796
// scheme for inserting fields
797
if (isset($GLOBALS['sql_columns'])) {
798
$fields = implode(', ', $field_set);
799
$schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $sql_backquotes)
800
. ' (' . $fields . ') VALUES ';
802
$schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $sql_backquotes)
807
$search = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
808
$replace = array('\0', '\n', '\r', '\Z');
811
if (isset($GLOBALS['sql_extended']) && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) {
813
$schema_insert .= $crlf;
818
while ($row = PMA_DBI_fetch_row($result)) {
820
for ($j = 0; $j < $fields_cnt; $j++) {
822
if (!isset($row[$j]) || is_null($row[$j])) {
825
// timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
826
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp'
827
&& ! $fields_meta[$j]->blob) {
828
$values[] = $row[$j];
830
// Note: with mysqli, under MySQL 4.1.3, we get the flag
831
// "binary" for those field types (I don't know why)
832
} elseif (stristr($field_flags[$j], 'BINARY')
833
&& isset($GLOBALS['sql_hex_for_binary'])
834
&& $fields_meta[$j]->type != 'datetime'
835
&& $fields_meta[$j]->type != 'date'
836
&& $fields_meta[$j]->type != 'time'
837
&& $fields_meta[$j]->type != 'timestamp'
839
// empty blobs need to be different, but '0' is also empty :-(
840
if (empty($row[$j]) && $row[$j] != '0') {
843
$values[] = '0x' . bin2hex($row[$j]);
845
// something else -> treat as a string
847
$values[] = '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\'';
851
// should we make update?
852
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
854
$insert_line = $schema_insert;
855
for ($i = 0; $i < $fields_cnt; $i++) {
857
$insert_line .= ', ';
859
$insert_line .= $field_set[$i] . ' = ' . $values[$i];
862
$insert_line .= ' WHERE ' . PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $row);
866
// Extended inserts case
867
if (isset($GLOBALS['sql_extended'])) {
868
if ($current_row == 1) {
869
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
871
$insert_line = '(' . implode(', ', $values) . ')';
872
if (isset($GLOBALS['sql_max_query_size']) && $GLOBALS['sql_max_query_size'] > 0 && $query_size + strlen($insert_line) > $GLOBALS['sql_max_query_size']) {
873
if (!PMA_exportOutputHandler(';' . $crlf)) {
878
$insert_line = $schema_insert . $insert_line;
881
$query_size += strlen($insert_line);
883
// Other inserts case
885
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
890
if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) {
895
if ($current_row > 0) {
896
if (!PMA_exportOutputHandler(';' . $crlf)) {
900
} // end if ($result != FALSE)
901
PMA_DBI_free_result($result);
904
} // end of the 'PMA_exportData()' function