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